This discussion is archived
0 Replies Latest reply: Oct 1, 2013 4:41 AM by Swat RSS

Absence Net Accrual

Swat Newbie
Currently Being Moderated

Hi Guys,

      I have a requirement for which I would require Leave_Balance with respect to the Date given.

I have used the Package ROUND(per_utility_functions.get_net_accrual (paf.assignment_id , paf.payroll_id , paf.business_Group_id, -1 , pat.date_end , pap.accrual_plan_id ),3) Leave_Balance to calculate the same.

   But the Requirement is need to capture all the employee details along with the Leaves taken date as well.

So, have used this query


  SELECT  pesg.enterprise_id                                ,

      paf.business_group_id                                   ,

      paf.organization_id                                     ,

      paf.person_id                                           ,

      paf.supervisor_id                                       ,

      TO_CHAR(pat.absence_attendance_type_id) Absence_Type_Id ,

      paat.absence_category absence_category_id               ,

      pat.date_start                        ,

      pat.date_end                          ,


      paf.effective_start_date              ,

      paf.effective_end_date                ,

      'Approved' AS Status

       FROM per_all_assignments_f paf        ,

      per_absence_attendance_types paat,

      per_absence_attendances pat      ,

      per_enterprises pe               ,

      per_ent_security_groups pesg

      WHERE paat.business_group_id    = paf.business_group_id

    AND pat.business_group_id     = paf.business_group_id

    AND pat.person_id             = paf.person_id

    AND pe.enterprise_id          = pesg.enterprise_id

    AND paf.business_group_id     = pesg.business_group_id 

    AND paf.primary_flag          = 'Y'

   AND paf.assignment_number    IS NOT NULL

    AND paf.payroll_id           IS NOT NULL

   AND paf.business_group_id    IS NOT NULL

    and pat.person_id = 3991

    and pat.absence_attendance_type_id = paat.absence_attendance_type_id

    AND sysdate BETWEEN paf.effective_start_date AND paf.effective_end_date


The result of the query is actually correct. But when I merge the Utility Package along with the same query,

select pat.date_start,pat.date_end,pat.absence_hours,paat.absence_attendance_type_id,paat.absence_category

ROUND(per_utility_functions.get_net_accrual (paf.assignment_id , paf.payroll_id , paf.business_Group_id, -1 , pat.date_end , pap.accrual_plan_id ),3) Leave_Balance

from per_all_assignments_f paf,

pay_accrual_plans pap,

per_absence_attendances pat,

per_absence_attendance_types paat

where pap.business_group_id=paf.business_group_id

and pat.person_id = paf.person_id

and pat.business_group_id=paf.business_group_id

and pap.pto_input_value_id = paat.input_value_id

and sysdate between paf.effective_start_date and paf.effective_end_date

and paf.person_id = 3991

and paf.primary_flag = 'Y'

some rows are not getting retrieved and some rows are correlated. I have used the Pay_Accrual_Plans Table to get the Accrual_Plan_id.

Kindly suggest on this

Any help is highly appreciated.


  • Correct Answers - 10 points
  • Helpful Answers - 5 points