1 Reply Latest reply: Feb 1, 2011 4:14 AM by Duncan Casemore RSS

    How to create a report which shows leave entitlement balances on a eff date

    Ruud Kienhuis
      Hi,

      customer would like to get a report, or sql statement, which shows employees net entitlements for an accrual plan. Because it is a calculated amount, I am not able to get my own quick paint or standard report.

      Who can help?


      ruud
        • 1. Re: How to create a report which shows leave entitlement balances on a eff date
          Duncan Casemore
          You can do this in 3 steps.

          1) First of all you'll need a package that wraps one of the Oracle-delivered PLSQL procedures into a SQL-callable function:

          create or replace package xx_pto_balance AS

          FUNCTION get_net_entitlement
          (p_assignment_id in number
          ,p_plan_id in number
          ,p_calculation_date in date) return number;

          end xx_pto_balance;
          /

          create or replace package body xx_pto_balance AS

          FUNCTION get_net_entitlement
          (p_assignment_id in number
          ,p_plan_id in number
          ,p_calculation_date in date) return number IS

          l_net_entitlement number;
          l_last_accrual_date date;

          BEGIN

          hr_pto_views.get_pto_ytd_net_entitlement
          (p_assignment_id => p_assignment_id
          ,p_plan_id => p_plan_id
          ,p_calculation_date => p_calculation_date
          ,p_net_entitlement => l_net_entitlement
          ,p_last_accrual_date => l_last_accrual_date);

          RETURN l_net_entitlement;

          END get_net_entitlement;

          end xx_pto_balance;
          /

          2) Next you'll need to initialize your apps session (if calling from SQL):

          exec fnd_global.apps_initialize(<user_id>, <responsibility_id>, <responsibility_application_id>, <security_group_id>);
          insert into fnd_sessions values (userenv('sessionid'), trunc(sysdate));

          3) Then you can you use a SQL statement like this one (noting the call to the above package function):

          SELECT papf.employee_number
          ,papf.full_name
          ,pap.accrual_plan_name
          ,xx_pto_balance.get_net_entitlement
          (paaf.assignment_id
          ,pap.accrual_plan_id
          ,trunc(sysdate)) net_entitlement
          FROM per_all_people_f papf
          ,per_all_assignments_f paaf
          ,pay_element_entries_f pee
          ,pay_accrual_plans pap
          WHERE papf.person_id = paaf.person_id
          AND nvl(papf.current_employee_flag, 'N') = 'Y'
          AND paaf.assignment_type = 'E'
          AND paaf.primary_flag = 'Y'
          AND paaf.assignment_id = pee.assignment_id
          AND pee.element_type_id = pap.accrual_plan_element_type_id
          AND trunc(sysdate) BETWEEN
          papf.effective_start_date AND papf.effective_end_date
          AND trunc(sysdate) BETWEEN
          paaf.effective_start_date AND paaf.effective_end_date
          AND trunc(sysdate) BETWEEN
          pee.effective_start_date AND pee.effective_end_date
          ORDER BY papf.full_name;

          This gets the net entitlement for all employees' accrual plans. You can of course tweak this as you like.

          Don't expect this to be fast! In fact, expect it to be painfully slow: because balances are calculated on-the-fly and run Fast Formula it takes a couple of seconds per person, depending on your Fast Formula code. To improve performance these are some things you can do:

          a) Put the above SQL into a materialized view and refresh it regularly (eg, daily)
          b) Build a Concurrent Program that populates a 'snapshot' table of balances and then report off that
          c) Use the Accrual Plan Payroll Balance architecture to store entitlements in payroll balances and then retrieve the payroll balances

          I hope that helps.