I frequently get requests to report absences.\u00a0 Most Absences are administered using Absence Management.\u00a0 However, extended leave requests\/absences are typically administered via Job Data, which is more difficult to extract.<\/p>\n
This is a relatively simple to extract Global Payroll absence events using the \u201cPS_GP_ABS_EVENT\u201d record.\u00a0 For example the following SQL Script:<\/p>\n
\nSELECT c.EMPLID,
\nc.EMPL_RCD,
\nc.NAME,
\nb.PIN_NM,
\nb.DESCR,
\na.BGN_DT,
\na.END_DT,
\na.DURATION_HOURS,
\na.COMMENTS
\nFROM PS_GP_ABS_EVENT a,
\n\/*Include GP Element Table for Absence Take details.*\/
\nPS_GP_PIN b,
\n\/*Include employees with status of ‘A’, ‘L’, ‘P’, ‘S’*\/
\nPS_EMPLOYEES c
\nWHERE a.EMPLID = c.EMPLID
\nAND a.EMPL_RCD = c.EMPL_RCD
\nAND a.PIN_TAKE_NUM = b.PIN_NUM
\n\/*Exclude voided absence requests.*\/
\nAND a.VOIDED_IND = ‘N’
\n\/*Only include approved absence requests.*\/
\nAND a.MANAGER_APPR_IND = ‘Y’;<\/p>\n
However, it is less simple to query extended absences administered via Job Data.\u00a0 The following SQL query extracts extended leave from Job data, using the \u201cLEAD\u201d SQL function to consolidate begin and end dates into a single row:<\/p>\n
\r\nselect r.*\r\nfrom(\r\nselect a.emplid,\r\na.empl_rcd,\r\nc.name,\r\na.action,\r\nd.action_descr,\r\na.action_reason as reason,\r\nb.descr as reason_descr,\r\na.effdt as bgn_dt,\r\n\/*Get extended leave end date from next row of results.*\/\r\nLEAD(A.EFFDT respect nulls) \r\n OVER (partition by a.emplid, a.empl_rcd \r\n ORDER BY a.emplid, a.empl_rcd, a.effdt) AS end_dt\r\n\/*Get Job Data*\/\r\nfrom ps_job a,\r\n\/*Get Action Reasons*\/\r\n(select * from PS_ACTN_REASON_TBL e\r\n WHERE e.effdt = (select max(f.effdt)\r\n from ps_actn_reason_tbl f\r\n where e.action = f.action\r\n and e.action_reason = f.action_reason)) b,\r\n\/*Get employees with status 'A', 'L', 'P', 'S'.*\/\r\nps_employees c,\r\n\/*Get Actions*\/\r\n(select *\r\n from ps_action_tbl g\r\n where g.effdt = (select max(h.effdt)\r\n from ps_action_tbl h\r\n where h.action = g.action)) d\r\n\/*Select Actions for commencing and ending extended leave.*\/\r\nwhere a.action IN ('PLA', 'RFL')\r\nand a.emplid = c.emplid\r\nand a.empl_rcd = c.empl_rcd\r\nand d.action = a.action\r\nand b.action = a.action\r\nand b.action_reason = a.action_reason\r\n)r\r\n\/*Only report rows where extended leave commences.*\/\r\nwhere r.action = 'PLA';\r\n<\/pre>\nNote: These queries were written for an Oracle database and may require tweaking for use with other databases.<\/p>\n","protected":false},"excerpt":{"rendered":"
I frequently get requests to report absences.\u00a0 Most Absences are administered using Absence Management.\u00a0 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 \u201cPS_GP_ABS_EVENT\u201d record.\u00a0 For example the following SQL Script: SELECT c.EMPLID, c.EMPL_RCD, c.NAME,\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,8,38,37],"tags":[],"class_list":["post-248","post","type-post","status-publish","format-standard","hentry","category-absence-management","category-global-payroll","category-reporting","category-sql"],"_links":{"self":[{"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/posts\/248","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/comments?post=248"}],"version-history":[{"count":6,"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/posts\/248\/revisions"}],"predecessor-version":[{"id":254,"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/posts\/248\/revisions\/254"}],"wp:attachment":[{"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/media?parent=248"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/categories?post=248"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/tags?post=248"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}