Oracle Fusion (Cloud) Reusable SQL Queries

SQL Query to Get Employees Absence Plan Balance

Below SQL query will help you to get accrual balance for any absence plan, just change the plan name for which you need to get the accrual balance.

select papf.person_number,
ppnf.DISPLAY_NAME Person_Name,
houft.NAME LegalEmployer,
aapft.name as Balance_Name,
to_char(appe.enrt_st_dt,'DD/MM/YYYY') Enrollment_Start_Date,
to_char(appe.last_accrual_run,'DD/MM/YYYY') Balance_Calculation_Date,
apae.end_bal Absence_Plan_Balance

From per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_m paam,
per_periods_of_service ppos,
HR_ORGANIZATION_UNITS_F_TL houft,
anc_per_plan_enrollment appe,
anc_absence_plans_f_tl aapft,
anc_per_accrual_entries apae

Where 1=1
and ppnf.person_id = papf.person_id
and ppnf.name_type (+)= 'GLOBAL'
and papf.person_id = paam.person_id
and paam.primary_flag = 'Y'
and paam.assignment_type = 'E'
and paam.effective_latest_change ='Y'
and ppos.period_of_service_id = paam.period_of_service_id
AND ppos.date_start = (select max(date_start)
from per_periods_of_service ppos1
where 1=1
and ppos1.person_id = papf.person_id
and period_type = 'E')
and houft.organization_id = paam.legal_entity_id
and houft.LANGUAGE = 'US'
and appe.Person_id = papf.Person_id
and aapft.absence_plan_id = appe.plan_id
and aapft.LANGUAGE = 'US'
and apae.per_plan_enrt_id = appe.per_plan_enrt_id
and apae.accrual_period = appe.last_accrual_run

and SYSDATE between papf.effective_start_date and papf.effective_end_date
and SYSDATE between ppnf.effective_start_date and ppnf.effective_end_date
and SYSDATE between paam.effective_start_date and paam.effective_end_date
and SYSDATE BETWEEN houft.effective_start_date AND houft.effective_end_date
and SYSDATE between appe.enrt_st_dt and appe.enrt_end_dt

Query to Get Employee’s Default Expense Account

Below is the query to get employees default expense account available on Person management screen.