Absence management stores absence requests in blocks on the GP_ABS_EVENT table, so if you request one week of PTO it will store the entire week on one record using a from and through date. Sometimes you need to have this block of PTO broken up into individual days and you have to take in account the employee's schedule. This is because someone may request two weeks off and that block might include weekends and other non scheduled time and Absence will not include time off in its calculations.
This query takes in account multiple absence takes, since we have a Volunteer Day where employee's can volunteer at a local charity, and usually these places have 4 hour shifts, so the employee will typically take the other half day as PTO, if they so choose.
So I created a view of the Absence Request to sum the end day hours and begin day hours on the absence request. This allows me to see if they are taking part of the day off or the whole day off without worrying about multiple takes, as I want my query to only pull back one duration per day. To do that
This is the ZZ_ABS_EVENT View in the SQL
SELECT A.EMPLID
, A.EMPL_RCD
, A.BGN_DT
, A.ALL_DAYS_IND
, A.END_DT
, SUM(A.END_DAY_HRS)
, SUM(A.BEGIN_DAY_HRS)
FROM Ps_Gp_Abs_event A
WHERE A.VOIDED_IND = 'N'
AND A.MANAGER_APPR_IND = 'Y'
GROUP BY A.EMPLID, A.EMPL_RCD, A.BGN_DT, A.ALL_DAYS_IND, A.END_DT
Here is my SQL to select all approved absence request, broken up by day.
SELECT E.EMPLID
, B.OPRID
, F.Dur
, WRKDAY_ID
, SUM(CASE all_days_ind WHEN 'Y' THEN a.begin_day_hrs ELSE CASE WHEN f.dur = a.bgn_dt THEN DECODE(a.begin_day_hrs
,0
,F.SCHED_HRS
,a.begin_day_hrs) ELSE CASE WHEN a.end_dt = f.dur THEN DECODE(a.end_day_hrs
,0
,F.SCHED_HRS
,a.end_day_hrs) ELSE f.sched_hrs END END END)
, F.Sched_Hrs
FROM PS_SCH_ASSIGN E LEFT OUTER JOIN PS_ZZ_ABS_EVENT A ON A.EMPLID = E.EMPLID AND A.EMPL_RCD = E.EMPL_RCD
, Ps_Sch_Clnd_Dtl_Vw F
, PSOPRDEFN B
WHERE F.DUR BETWEEN A.bgn_Dt AND a.end_dt
AND E.EMPLID = B.EMPLID
AND E.EFFDT = (
SELECT MAX(E_ED.EFFDT)
FROM PS_SCH_ASSIGN E_ED
WHERE E.EMPLID = E_ED.EMPLID
AND E.EMPL_RCD = E_ED.EMPL_RCD
AND E_ED.EFFDT <= to_date(sysdate))
AND F.SETID = E.SETID
AND F.SCH_ADHOC_IND = E.SCH_ADHOC_IND
AND F.SCHEDULE_ID = E.SCHEDULE_ID
AND F.ROTATION_ID = E.ROTATION_ID
AND F.EFFDT = (
SELECT MAX(F_ED.EFFDT)
FROM PS_SCH_CLND_DTL_VW F_ED
WHERE F.SETID = F_ED.SETID
AND F.SCH_ADHOC_IND = F_ED.SCH_ADHOC_IND
AND F.SCHEDULE_ID = F_ED.SCHEDULE_ID
AND F.ROTATION_ID = F_ED.ROTATION_ID
AND F.DUR = F_ED.DUR
AND F.SHIFTNUM = F_ED.SHIFTNUM
AND F_ED.EFFDT <= to_date(sysdate))
AND F.DUR >= A.BGN_DT
AND F.DUR <= A.END_DT
AND F.OFFDAY_IND = 'N'
And A.End_Dt >= To_Date(Sysdate)
group by E.EMPLID, B.OPRID, F.Dur, WRKDAY_ID, F.Sched_Hrs;
No comments:
Post a Comment