ACA Hours

I have been writing a view to pull hours for ACA Reporting.   To preface this we use TL and Payroll for North America, but since hours can be added to the paysheet, I thought it best not use the TL tables and get the hours directly from the Payroll.

First the Record Definition:

My view contains COMPANY, PAYGROUP, EMPLID, PAY_END_DT, ERNCD and TOT_HRS.  Below is my SQL for building this view:

WITH HOURS_WRK (COMPANY, PAYGROUP, EMPLID, PAY_END_DT, ERNCD , HOURS) AS (

 SELECT C.COMPANY

 , C.PAYGROUP

 , P.EMPLID

 , P.PAY_END_DT

 ,CASE P.REG_HRS WHEN 0 THEN P.ERNCD_REG_EARNS ELSE P.ERNCD_REG_HRS END

 , (P.REG_HRS + P.REG_EARN_HRS)

  FROM PS_PAY_EARNINGS P

  , PS_PAY_CHECK C

 WHERE P.COMPANY=C.COMPANY

   AND P.PAYGROUP=C.PAYGROUP

   AND P.PAY_END_DT=C.PAY_END_DT

   AND P.OFF_CYCLE=C.OFF_CYCLE

   AND P.PAGE_NUM=C.PAGE_NUM

   AND P.LINE_NUM=C.LINE_NUM

   AND P.SEPCHK=C.SEPCHK

   AND C.PAYCHECK_STATUS = 'F'

   AND P.OK_TO_PAY = 'Y'

  UNION ALL

 SELECT C.COMPANY

 , C.PAYGROUP

 ,c.emplid

 , c.pay_end_dt

 , ERNCD

 , a.oth_hrs

  FROM PS_PAY_OTH_EARNS A

  ,PS_PAY_EARNINGS B

  ,PS_PAY_CHECK C

 WHERE A.COMPANY=B.COMPANY

   AND A.PAYGROUP=B.PAYGROUP

   AND A.PAY_END_DT=B.PAY_END_DT

   AND A.OFF_CYCLE=B.OFF_CYCLE

   AND A.PAGE_NUM=B.PAGE_NUM

   AND A.LINE_NUM=B.LINE_NUM

   AND A.SEPCHK=B.SEPCHK

   AND A.ADDL_NBR=B.ADDL_NBR

   AND C.PAYCHECK_STATUS = 'F'

   AND B.OK_TO_PAY='Y'

   AND A.COMPANY=C.COMPANY

   AND A.PAYGROUP=C.PAYGROUP

   AND A.PAY_END_DT=C.PAY_END_DT

   AND A.OFF_CYCLE=C.OFF_CYCLE

   AND A.PAGE_NUM=C.PAGE_NUM

   AND A.LINE_NUM=C.LINE_NUM

   AND A.SEPCHK=C.SEPCHK)

 SELECT COMPANY

 , PAYGROUP

 , EMPLID

 , PAY_END_DT

 , ERNCD

 , HOURS

  FROM HOURS_WRK

After this view is built I can select my hours from the paycheck and sum them by emplid, paygroup, company and filter by earning code.  This is the sql we are using to pull last years hours:

SELECT EMPLID, SUM(TOT_HRS) FROM PS_ZZ_EE_PAY_C_HRS

WHERE ERNCD not in ('020', '030', '023') 

AND PAY_END_DT BETWEEN to_date('10/14/2013', 'mm/dd/yyyy')  

AND  to_date('10/12/2014', 'mm/dd/yyyy') 

group by emplid,paygroup;

If anyone thinks I am missing something or would add to this post leave a comment and we can start a discussion.