Absence Management/Leave Queries

I frequently get requests to report absences.  Most Absences are administered using Absence Management.  However, extended leave requests/absences are typically administered via Job Data, which is more difficult to extract.

This is a relatively simple to extract Global Payroll absence events using the “PS_GP_ABS_EVENT” record.  For example the following SQL Script:

SELECT c.EMPLID,
c.EMPL_RCD,
c.NAME,
b.PIN_NM,
b.DESCR,
a.BGN_DT,
a.END_DT,
a.DURATION_HOURS,
a.COMMENTS
FROM PS_GP_ABS_EVENT a,
/*Include GP Element Table for Absence Take details.*/
PS_GP_PIN b,
/*Include employees with status of ‘A’, ‘L’, ‘P’, ‘S’*/
PS_EMPLOYEES c
WHERE a.EMPLID = c.EMPLID
AND a.EMPL_RCD = c.EMPL_RCD
AND a.PIN_TAKE_NUM = b.PIN_NUM
/*Exclude voided absence requests.*/
AND a.VOIDED_IND = ‘N’
/*Only include approved absence requests.*/
AND a.MANAGER_APPR_IND = ‘Y’;

However, it is less simple to query extended absences administered via Job Data.  The following SQL query extracts extended leave from Job data, using the “LEAD” SQL function to consolidate begin and end dates into a single row:

select r.*
from(
select a.emplid,
a.empl_rcd,
c.name,
a.action,
d.action_descr,
a.action_reason as reason,
b.descr as reason_descr,
a.effdt as bgn_dt,
/*Get extended leave end date from next row of results.*/
LEAD(A.EFFDT respect nulls) 
  OVER (partition by a.emplid, a.empl_rcd 
  ORDER BY a.emplid, a.empl_rcd, a.effdt) AS end_dt
/*Get Job Data*/
from ps_job a,
/*Get Action Reasons*/
(select * from PS_ACTN_REASON_TBL e
  WHERE e.effdt = (select max(f.effdt)
  from ps_actn_reason_tbl f
  where e.action = f.action
  and e.action_reason = f.action_reason)) b,
/*Get employees with status 'A', 'L', 'P', 'S'.*/
ps_employees c,
/*Get Actions*/
(select *
  from ps_action_tbl g
  where g.effdt = (select max(h.effdt)
  from ps_action_tbl h
  where h.action = g.action)) d
/*Select Actions for commencing and ending extended leave.*/
where a.action IN ('PLA', 'RFL')
and a.emplid = c.emplid
and a.empl_rcd = c.empl_rcd
and d.action = a.action
and b.action = a.action
and b.action_reason = a.action_reason
)r
/*Only report rows where extended leave commences.*/
where r.action = 'PLA';

Note: These queries were written for an Oracle database and may require tweaking for use with other databases.