In this session we are going to see how to fetch Oracle Cloud HCM Absence Accrual data using SQL.
SQL Text:
with plan_prd_tbl_vw1 as (
select name
,absence_plan_id
,plan_status
,effective_start_date
,effective_end_date
,case when to_date(to_char(:p_eff_dt,'yyyy')
|| case when length(calendar_start_month) = 1 then '0' end
|| calendar_start_month
|| case when length(calendar_start_day) = 1 then '0' end
|| calendar_start_day,'yyyymmdd') > :p_eff_dt
then to_date(to_char(add_months(:p_eff_dt,-12),'yyyy')
|| case when length(calendar_start_month) = 1 then '0' end
|| calendar_start_month
|| case when length(calendar_start_day) = 1 then '0' end
|| calendar_start_day,'yyyymmdd')
else to_date(to_char(:p_eff_dt,'yyyy')
|| case when length(calendar_start_month) = 1 then '0' end
|| calendar_start_month
|| case when length(calendar_start_day) = 1 then '0' end
|| calendar_start_day,'yyyymmdd') end plan_start_date
from anc_absence_plans_vl
where 1 = 1
and calendar_start_month is not null
and entl_method_cd = 'A'
)
,plan_prd_tbl_vw2 as (
select name
,absence_plan_id
,plan_status
,effective_start_date
,effective_end_date
,plan_start_date
,add_months(plan_start_date,12)-1 plan_end_date
from plan_prd_tbl_vw1
where 1 = 1
)
,people_tbl_vw as (
select distinct * from (
select papf.person_id
,papf.person_number
from per_all_people_f papf
where 1 = 1
and (coalesce(null,:p_pers_num) is null or papf.person_id in (:p_pers_num))
)
)
,accrual_tbl_vw as (
select ptv.person_number
,aapv.name plan_name
,appe.enrt_st_dt
,appe.last_accrual_run
,aapv.plan_start_date start_date
,aapv.plan_end_date end_date
,apae.plan_id
,apae.person_id
,apae.work_term_asg_id
,apae.end_bal
from anc_per_accrual_entries apae
,anc_per_plan_enrollment appe
,people_tbl_vw ptv
,plan_prd_tbl_vw2 aapv
where 1 = 1
and appe.per_plan_enrt_id = apae.per_plan_enrt_id
and appe.person_id = apae.person_id
and appe.plan_id = apae.plan_id
and appe.work_term_asg_id = apae.work_term_asg_id
and ptv.person_id = apae.person_id
and appe.last_accrual_run = apae.accrual_period
and aapv.absence_plan_id = apae.plan_id
and aapv.plan_status = 'A'
and (coalesce(null,:p_pl_id) is null or aapv.absence_plan_id in (:p_pl_id))
and :p_eff_dt between aapv.plan_start_date and aapv.plan_end_date
and :p_eff_dt between aapv.effective_start_date and aapv.effective_end_date
)
,accrual_entry_tbl_vw as (
select * from (
select atv.plan_name
,atv.person_number
,(select sum(value) from anc_per_acrl_entry_dtls apaed1
where 1 = 1
and apaed1.person_id = apaed.person_id
and apaed1.pl_id = apaed.pl_id
and apaed1.work_term_asg_id = apaed.work_term_asg_id
and apaed1.type in ('FLDR','ACRL','ADJOTH','ABS','COVR','COVREX')
and apaed1.procd_date between atv.start_date and :p_eff_dt) end_bal
,to_char(atv.last_accrual_run,'dd-Mon-yyyy','nls_date_language=english') balance_calc_date
,to_char(atv.enrt_st_dt,'dd-Mon-yyyy','nls_date_language=english') enrolment_start_date
,to_char(atv.start_date,'dd-Mon-yyyy','nls_date_language=english') plan_start_date
,to_char(atv.end_date,'dd-Mon-yyyy','nls_date_language=english') plan_end_date
,apaed.value
,apaed.type
from anc_per_acrl_entry_dtls apaed
,accrual_tbl_vw atv
where 1 = 1
and atv.person_id = apaed.person_id
and atv.plan_id = apaed.pl_id
and atv.work_term_asg_id = apaed.work_term_asg_id
and apaed.procd_date between atv.start_date and :p_eff_dt
)
pivot (sum(value) for type in (
'FLDR' annual_accrual
,'ACRL' periodic_accrual
,'ADJOTH' adjustments
,'ABS' taken_leaves
,'COVR' carry_over
,'COVREX' carry_over_forfeit
))
)
select * from accrual_entry_tbl_vw
order by person_number
Comments
Post a Comment