I have had the pleasure of creating mass amounts of data for the State auditors this week. Here are some queries that I have coded for this data.
Note: You will need to change the plan types and dates for your own companies and institutions.
SELECT DISTINCT B.EMPLID, B.NAME, TO_CHAR(B.PAY_END_DT,'YYYY-MM-DD'), A.PLAN_TYPE, A.BENEFIT_PLAN, A.DED_CUR, A.DEDCD, A.DED_CLASS, TO_CHAR(C.EFFDT,'YYYY-MM-DD'), C.COVERAGE_ELECT, C.COVRG_CD, C.EMPLID
FROM PS_PAY_DEDUCTION A, PS_PAY_CHECK B, PS_HEALTH_BENEFIT 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.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR
AND A.PLAN_TYPE IN ('1X','1Y','1Z','10','11') /*** Health and Dental plan types ***/
AND C.EMPLID = B.EMPLID
AND C.EMPL_RCD = B.EMPL_RCD
AND C.COVERAGE_ELECT = 'E'
AND ( C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_HEALTH_BENEFIT C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.EMPL_RCD = C_ED.EMPL_RCD
AND C.COBRA_EVENT_ID = C_ED.COBRA_EVENT_ID
AND C.PLAN_TYPE = C_ED.PLAN_TYPE
AND C.BENEFIT_NBR = C_ED.BENEFIT_NBR
AND C_ED.EFFDT <= B.PAY_END_DT))
AND B.PAY_END_DT BETWEEN TO_DATE('2007-07-01','YYYY-MM-DD') AND TO_DATE('2008-06-30','YYYY-MM-DD')
AND A.PLAN_TYPE = C.PLAN_TYPE
ORDER BY 1, 3, 4
-- By month for Audits
select distinct
A.*,B.empl_rcd, B.empl_class,B.reg_temp, B.jobcode,B.comprate,B.annual_rt, B.grade, B.sal_admin_plan
,C.HIRE_DT,TO_CHAR(((SYSDATE - C.HIRE_DT) / 365),99.99) TOTAL_YEARS_WORKED
,TO_CHAR((SYSDATE - C.HIRE_DT),999999) TOTAL_DAYS_WORKED
,D.*, E.*
FROM (PS_PAY_CHECK A LEFT OUTER JOIN PS_JOB B ON A.COMPANY = B.COMPANY AND A.PAYGROUP = B.PAYGROUP AND B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD ), PS_EMPLOYMENT C, PS_PAY_EARNINGS D, PS_PAY_OTH_EARNS E
WHERE (B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.EMPL_RCD = B_ED.EMPL_RCD
AND B_ED.EFFDT <= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.EMPL_RCD = B_ES.EMPL_RCD
AND B.EFFDT = B_ES.EFFDT)
AND B.EMPLID = C.EMPLID
AND B.EMPL_RCD = C.EMPL_RCD
AND A.COMPANY = D.COMPANY
AND A.PAYGROUP = D.PAYGROUP
AND A.PAY_END_DT = D.PAY_END_DT
AND A.OFF_CYCLE = D.OFF_CYCLE
AND A.PAGE_NUM = D.PAGE_NUM
AND A.LINE_NUM = D.LINE_NUM
AND A.SEPCHK = D.SEPCHK
AND A.COMPANY = E.COMPANY
AND A.PAYGROUP = E.PAYGROUP
AND A.PAY_END_DT = E.PAY_END_DT
AND A.OFF_CYCLE = E.OFF_CYCLE
AND A.PAGE_NUM = E.PAGE_NUM
AND A.LINE_NUM = E.LINE_NUM
AND A.SEPCHK = E.SEPCHK
AND A.CHECK_DT BETWEEN TO_DATE('2007-07-01','YYYY-MM-DD') AND TO_DATE('2007-07-31','YYYY-MM-DD'))
order by a.emplid, a.paygroup
