Fusion HCM – Query for Absence Accrual Balance

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
  • February 15, 2019 | 32 views
  • Comments