Friday, 15 February 2019

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

3 comments:

  1. Hi Prathap,

    Its really very helpful. Actually,I need this accrual balance on a particular date for a particular employee. That means - parameter will be Date and Person number.

    Could you please send me that.
    Thanks in advance.

    ReplyDelete
    Replies
    1. Hello Krish, Did you get the report with date parameter as requirement ?

      Delete
    2. Does anyone have query for this balance as per date requirement?

      Delete