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'))
