-
-

Absence Balances

ABSENCE

Get current absence balances for all employees

SELECT T1.PER_MAILSTOP , T1.EMPLOYEE_NUMBER ,T1.EMPLOYEE_NAME ,T1.POSITION , t1.ASSIGNMENT_CATEGORY , t1.UNION_NAME , T1.FULLTIME_PARTTIME , T1.LOCATION , T1.DEPARTMENT , to_char(T1.DATE_START,'MM/DD/YYYY') hire_date , (case when T1.actual_termination_date is not null THEN 'TERMINATED' ELSE 'ACTIVE' end ) active_status , ( case when T1.DATE_START < ADD_MONTHS(SYSDATE,-6) then to_number(nvl((select round(nvl(a.end_bal,0),2) from anc_per_accrual_entries a , anc_per_plan_enrollment b where a.per_plan_enrt_id=b.per_plan_enrt_id and a.accrual_period=b.last_accrual_run and b.work_term_asg_id=T1.WORK_TERMS_ASSIGNMENT_ID and b.plan_id=(select absence_plan_id from ANC_ABSENCE_PLANS_F_TL where name='Floating Holiday') and b.person_id=T1.PERSON_ID and T2.DATE_EARNED between b.enrt_st_dt and b.enrt_end_dt ),0) ) else 0 end ) FLOATING_DAY_BALANCE , to_number(nvl((select round(nvl(a.end_bal,0),2) from anc_per_accrual_entries a , anc_per_plan_enrollment b where a.per_plan_enrt_id=b.per_plan_enrt_id and a.accrual_period=b.last_accrual_run and b.work_term_asg_id=T1.WORK_TERMS_ASSIGNMENT_ID and b.plan_id=(select absence_plan_id from ANC_ABSENCE_PLANS_F_TL where name='FTime') and b.person_id=T1.PERSON_ID and T2.DATE_EARNED between b.enrt_st_dt and b.enrt_end_dt ),0)) FTIME_BALANCE , (case when T1.ASSIGNMENT_CATEGORY = 'SE - Seasonal' then to_number( NVL((Select round(sum(A.value),2) from ANC_PER_ACRL_ENTRY_DTLS a, anc_absence_plans_f_tl d,anc_per_plan_enrollment b where a.person_id = t1.person_id and d.absence_plan_id = a.pl_id and d.name in ('SEIU Seasonal Personal Day','Personal Day Local 703') AND procd_date < sysdate and d.absence_plan_id=b.plan_id and a.person_id=b.person_id and sysdate between b.enrt_st_dt and b.enrt_end_dt group by a.person_id ),0) ) when T1.ASSIGNMENT_CATEGORY <> 'SE - Seasonal' and T1.DATE_START < ADD_MONTHS(SYSDATE,-6) then to_number( NVL((Select round(sum(A.value),2) from ANC_PER_ACRL_ENTRY_DTLS a, anc_absence_plans_f_tl d,anc_per_plan_enrollment b where a.person_id = t1.person_id and d.absence_plan_id = a.pl_id and d.name in ('Personal Day','SEIU Personal Day Hourly') AND procd_date < sysdate and d.absence_plan_id=b.plan_id and a.person_id=b.person_id and sysdate between b.enrt_st_dt and b.enrt_end_dt group by a.person_id ),0) ) else 0 end) PD_BALANCE , ( case when T1.ASSIGNMENT_CATEGORY = 'SE - Seasonal' then to_number( NVL((Select round(sum(A.value),2) from ANC_PER_ACRL_ENTRY_DTLS a, anc_absence_plans_f_tl d,anc_per_plan_enrollment b where a.person_id = t1.person_id and d.absence_plan_id = a.pl_id and d.name in ('SEIU Seasonal Sick') AND procd_date < sysdate and d.absence_plan_id=b.plan_id and a.person_id=b.person_id and sysdate between b.enrt_st_dt and b.enrt_end_dt group by a.person_id),0) ) when T1.ASSIGNMENT_CATEGORY <> 'SE - Seasonal' and T1.DATE_START < ADD_MONTHS(SYSDATE,-6) then to_number(NVL((Select round(sum(A.value),2) from ANC_PER_ACRL_ENTRY_DTLS a, anc_absence_plans_f_tl d,anc_per_plan_enrollment b where a.person_id = t1.person_id and d.absence_plan_id = a.pl_id and d.name in ('Sick','SEIU Sick Hourly') AND procd_date < sysdate and d.absence_plan_id=b.plan_id and a.person_id=b.person_id and sysdate between b.enrt_st_dt and b.enrt_end_dt group by a.person_id),0) ) else 0 end ) SICK_BALANCE , (case when T1.DATE_START < ADD_MONTHS(SYSDATE,-6) then to_number(NVL(( Select round(sum(A.value),2) from ANC_PER_ACRL_ENTRY_DTLS a, anc_absence_plans_f_tl d,anc_per_plan_enrollment b where a.person_id = t1.person_id and d.absence_plan_id = a.pl_id and d.name in ('Vacation','SEIU and Non Union Vacation Pre','SEIU and Non Union Vacation Post') AND procd_date < sysdate and d.absence_plan_id=b.plan_id and a.person_id=b.person_id and sysdate between b.enrt_st_dt and b.enrt_end_dt group by a.person_id ),0) ) else 0 end ) VACATION_BALANCE FROM ( SELECT REPLACE (PPNF.FULL_NAME, ',', '') EMPLOYEE_NAME , PAPF.PERSON_NUMBER EMPLOYEE_NUMBER , PAAF.INTERNAL_MAILSTOP PER_MAILSTOP , papf.person_id PERSON_ID , hapf.NAME POSITION , HLA.LOCATION_NAME LOCATION , HAOU.NAME DEPARTMENT , WORK_TERMS_ASSIGNMENT_ID , paaf.employment_category || ' - '|| (Select meaning from hr_lookups where lookup_type = 'EMP_CAT' and lookup_code = paaf.employment_category and rownum = 1) ASSIGNMENT_CATEGORY , PAAF.bargaining_unit_code UNION_NAME , PAAF.FULL_PART_TIME FULLTIME_PARTTIME , CASE WHEN PAAF.EMPLOYMENT_CATEGORY = 'SE' AND PAAF.BARGAINING_UNIT_CODE='Local 703' AND PPG.SEGMENT2='Y' THEN 'Personal Day Local 703' WHEN PAAF.EMPLOYMENT_CATEGORY = 'HR' THEN 'SEIU Personal Day Hourly' WHEN PAAF.EMPLOYMENT_CATEGORY = 'SE' THEN 'SEIU Seasonal Personal Day' ELSE 'Personal Day' END PD_BALANCE_PLAN , CASE WHEN PAAF.EMPLOYMENT_CATEGORY = 'SE' THEN 'SEIU Seasonal Sick' WHEN PAAF.EMPLOYMENT_CATEGORY = 'HR' THEN 'SEIU Sick Hourly' ELSE 'Sick' END SICK_BALANCE_PLAN , CASE WHEN PAAF.EMPLOYMENT_CATEGORY = 'HR' AND PAAF.BARGAINING_UNIT_CODE='SEIU-Local 73' THEN 'SEIU and Non Union Vacation Pre' WHEN PAAF.EMPLOYMENT_CATEGORY = 'HR' AND PAAF.BARGAINING_UNIT_CODE='Local 00' THEN 'SEIU and Non Union Vacation Pre' ELSE 'Vacation' END VACATION_BALANCE_PLAN , trunc(PPOS.DATE_START) date_start , ppos.actual_termination_date FROM PER_ALL_PEOPLE_F PAPF , PER_PERSON_NAMES_F PPNF , PER_ALL_ASSIGNMENTS_M PAAF , PAY_PEOPLE_GROUPS ppg , HR_ALL_POSITIONS_F_VL HAPF , HR_LOCATIONS_ALL HLA , HR_ALL_ORGANIZATION_UNITS HAOU , PER_PERIODS_OF_SERVICE PPOS WHERE PAAF.PERSON_ID = PAPF.PERSON_ID AND HAPF.POSITION_ID = PAAF.POSITION_ID AND PPOS.PERSON_ID = PAPF.PERSON_ID AND PPOS.PERIOD_OF_SERVICE_ID = PAAF.PERIOD_OF_SERVICE_ID AND PPOS.DATE_START IN (SELECT MAX(PPOS1.DATE_START) FROM PER_PERIODS_OF_SERVICE PPOS1 WHERE PPOS1.PERSON_ID = PPOS.PERSON_ID AND PPOS.DATE_START < SYSDATE) AND HAOU.ORGANIZATION_ID = PAAF.ORGANIZATION_ID AND HLA.LOCATION_ID = PAAF.LOCATION_ID AND PPNF.PERSON_ID = PAPF.PERSON_ID AND PAAF.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID(+) AND PAAF.PRIMARY_FLAG = 'Y' AND PAAF.ASSIGNMENT_TYPE = 'E' AND PPNF.NAME_TYPE = 'GLOBAL' AND PAAF.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' AND to_char(sysdate,'YYYYMMDD') BETWEEN to_char(PAPF.effective_start_date,'YYYYMMDD') AND to_char(PAPF.effective_end_date,'YYYYMMDD') AND to_char(sysdate,'YYYYMMDD') BETWEEN to_char(PAAF.effective_start_date,'YYYYMMDD') AND to_char(PAAF.effective_end_date,'YYYYMMDD') AND to_char(sysdate,'YYYYMMDD') BETWEEN to_char(PPNF.effective_start_date,'YYYYMMDD') AND to_char(PPNF.effective_end_date,'YYYYMMDD') AND to_char(sysdate,'YYYYMMDD') BETWEEN to_char(HAPF.effective_start_date,'YYYYMMDD') AND to_char(HAPF.effective_end_date,'YYYYMMDD') and papf.person_number not in ('1200001','1200002','1200003') ) T1 , (SELECT DISTINCT (CASE WHEN TRUNC (SYSDATE) <= PTP.END_DATE THEN PTP.END_DATE - 14 WHEN TRUNC (SYSDATE) > PTP.END_DATE THEN PTP.END_DATE END) DATE_EARNED FROM PAY_TIME_PERIODS PTP WHERE (to_char(sysdate,'YYYYMMDD') BETWEEN to_char(PTP.END_DATE,'YYYYMMDD') AND to_char(PTP.REGULAR_EARN_DATE,'YYYYMMDD') OR to_char(sysdate,'YYYYMMDD') BETWEEN to_char(PTP.START_DATE,'YYYYMMDD') AND to_char(PTP.END_DATE,'YYYYMMDD') ) AND to_char(PTP.END_DATE,'YYYYMMDD') < '47000000' ) T2 WHERE (case when T1.actual_termination_date is not null THEN 'TERMINATED' ELSE 'ACTIVE' end ) = 'ACTIVE' order by PER_MAILSTOP,trunc(to_date(to_char(T1.DATE_START,'MM/DD/YYYY'),'MM/DD/YYYY'))