Fusion HCM – Query for Absence Accrual Balance

Introduction

This Post illustrates the steps required to Absence Accrual Balance-Fusion Application

Script to Absence Accrual Balance-Fusion HCM

SELECT peo.person_number emp_id, aapft.NAME absence_plan_name,

acc_ent.end_bal balance

FROM per_all_people_f peo,

per_all_assignments_f asg,

per_person_names_f per_name,

per_periods_of_service ser,

per_person_types_tl per_typ,

per_legal_employers ple,

anc_absence_plans_f_tl aapft,

anc_absence_plans_f aapf,

anc_per_accrual_entries acc_ent

WHERE asg.person_id = peo.person_id

AND ser.person_id = peo.person_id

AND per_name.person_id = peo.person_id

AND acc_ent.person_id = peo.person_id

AND asg.period_of_service_id = ser.period_of_service_id

AND acc_ent.prd_of_svc_id = asg.period_of_service_id

AND asg.person_type_id = per_typ.person_type_id

AND asg.legal_entity_id = ple.organization_id

AND aapft.absence_plan_id = aapf.absence_plan_id

AND acc_ent.plan_id = aapf.absence_plan_id

AND acc_ent.accrual_period =

(SELECT MAX (acc_ent1.accrual_period)

FROM anc_per_accrual_entries acc_ent1

WHERE acc_ent1.accrual_period <=

TO_DATE (SUBSTR (TRUNC (SYSDATE), 1, 10))

AND acc_ent1.person_id = acc_ent.person_id

AND acc_ent1.prd_of_svc_id = acc_ent.prd_of_svc_id

AND acc_ent1.plan_id = acc_ent.plan_id)

AND asg.primary_flag = ‘Y’

AND asg.assignment_type IN (‘E’, ‘C’, ‘N’, ‘P’)

AND asg.assignment_status_type = ‘ACTIVE’

AND per_name.name_type = ‘GLOBAL’

AND ple.status = ‘A’

AND per_typ.LANGUAGE = USERENV (‘LANG’)

AND aapf.plan_status = ‘A’

AND aapft.LANGUAGE = ‘US’

–AND acc_ent.end_bal <> 0

AND TRUNC (SYSDATE) BETWEEN peo.effective_start_date AND peo.effective_end_date

AND TRUNC (SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date

AND TRUNC (SYSDATE) BETWEEN per_name.effective_start_date

AND per_name.effective_end_date

AND TRUNC (SYSDATE) BETWEEN ple.effective_start_date AND ple.effective_end_date

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

Summary

This Post described the script for Absence Accrual Balance-Fusion Application(HCM)

Queries

Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions