Reports (BIP) – Query to get Adjustments in Absences

select papf.person_number,
ppnf.full_name,
absence_plan.name ,
apaed1.value,
apaed1.type,
to_char(apaed1.procd_date,’DD-MON-YYYY’) processed_date,
apaed1.created_by
from anc_per_acrl_entry_dtls apaed1,
ANC_PER_PLAN_ENROLLMENT apaed,
per_all_people_f papf,
per_person_names_f ppnf,
(SELECT aapf.absence_plan_id, aapft.NAME
FROM anc_absence_plans_f_tl aapft,
anc_absence_plans_f aapf
WHERE aapft.absence_plan_id = aapf.absence_plan_id
AND aapf.plan_status = ‘A’ — added to pick only Active Absence Plans
AND trunc(SYSDATE) BETWEEN aapf.effective_start_date AND aapf.effective_end_date
AND trunc(SYSDATE) BETWEEN aapft.effective_start_date AND aapft.effective_end_date
AND aapft.language = ‘US’
) absence_plan
where apaed.plan_id = absence_plan.absence_plan_id
and apaed.plan_id = apaed1.pl_id
AND ppnf.name_type = ‘GLOBAL’
AND ppnf.person_id = apaed.person_id
AND ppnf.person_id = apaed1.person_id
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date and ppnf.effective_end_date
AND apaed.person_id = papf.person_id
AND apaed1.person_id = papf.person_id
AND TRUNC(sysdate) BETWEEN papf.effective_start_date and papf.effective_end_date
–AND UPPER(absence_plan.name) NOT LIkE ‘%SICK%’
AND apaed1.type = ‘ADJOTH’
–and apaed1.created_by not like ‘FUSION_APPS_HCM_ESS_LOADER_APPID’
ORDER BY papf.person_number,absence_plan.name