Wednesday 23 December 2015

Oracle Payroll Run Results


Payroll generally runs for a consolidation set/assignment set OR payroll can be run for a particular employee assignment using quick pay. Whatever the process we follow to run the payroll, the elements attached to the employee assignment will be calculated based on the fast formula attached to it and returns values to the respective formula results. In this whole process of payroll run, the elements, database items, balances with its dimensions etc...are being assigned with the run result values. These run result values can be found in both run results after payroll run OR Statement of Earnings (SOE) report and dimension values in balances and so on.

Concurrent Program to RUN payroll:

This concurrent program is to run the payroll for a SET. Quick pay run is shown below.
The concept of calculating run result values/pay values is same for the SET/group payroll run and the quick pay. 

Once the ‘Payroll Run’ completes successfully, we can view run results/element pay values/balance dimension values/database item values.

Now, the question is ‘How to get the run result values of a payroll run using SQL queries’?

We will see how to check these values from the front end application before discussing the corresponding technical queries.

How to check run result values from the front end application?
Go to HRMS responsibility – View – Payroll Process results

To find all the payroll runs for a particular payroll in between the given range of period and with certain action type. (Table: pay_payroll_actions)

Note: Sensitive information in the forms is hidden


Assignment Process:


Balances: Click on this button to view calculated balance values
FYR: _ASG_PROC_YTD, _ASG_YTD, _ASG_RUN…etc are balance dimensions


View Results: Click on this button to view element pay values

Navigation to view results at assignment level: 
View – Assignment Process Results
Click on corresponding buttons to view respective run results for each assignment in the given period. (Table: pay_assignment_actions)


Quick Pay: Employee People screen – Assignment – Others – Quick Pay
You can run the quick pay and view results from this form. The data is same as above but specifically for a particular person

Quick Pay before processing:
Status is unprocessed and View results button is disabled



Quick Pay after processing:
Status is completed and View results button is enabled


Queries
Example 1:
If the requirement is to fetch run result values (like element pay values after the payroll run) for a particular payroll on particular month.

Get the Payroll ID using the payroll name using pay_payrolls_f
select * from pay_payrolls_f where payroll_name='XYZ'

Get payroll_action_id from pay_payroll_actions using the effective_date which is payroll run date
select * from pay_payroll_actions where payroll_id=64 order by effective_date desc (say 26-Nov-2015 is the effective date-payroll run)

Use the payroll action ID in the below query to get run result value of each element (Direct and Indirect) after the payroll run (Monthly/Weekly).

Query to get run results:
SELECT    papf.employee_number,
           papf.full_name,
           paaf.assignment_number,
           paaf.normal_hours,
           petf.element_name,
           piv.name,
           prrv.result_value
    FROM   pay_run_result_values prrv,
           pay_input_values_f piv,
           pay_run_results prr,
           pay_assignment_actions paa,
           per_all_assignments_f paaf,
           per_all_people_f papf,
           pay_element_types_f petf,
           pay_payrolls_f ppf
   WHERE   paa.payroll_action_id = 2942805 --Payroll action ID         
     AND prrv.input_value_id = piv.input_value_id
     AND prr.run_result_id = prrv.run_result_id
     AND prr.assignment_action_id = paa.assignment_action_id
     AND paa.assignment_id = paaf.assignment_id
     AND paaf.person_id = papf.person_id
     AND prr.element_type_id = petf.element_type_id
     --AND prr.element_type_id = 900  -- Particular element name
     --AND piv.name = 'Pay Value'
     AND paaf.payroll_id = ppf.payroll_id
     AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date
                           AND  ppf.effective_end_date
     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


Example 2:
Get any balance value (for example Balances from SOE report).


-- get defined balance ID
 SELECT   defined_balance_id
   FROM   pay_defined_balances pdb,
          pay_balance_dimensions pbd,
          pay_balance_types_tl pbt_tl,
          pay_balance_types pbt
  WHERE   PBT_TL.balance_type_id = pbt.balance_type_id
 -- AND userenv('LANG') = pbt.language
    AND PBT_TL.balance_name = :p_balance_type
    AND NVL (PBT.legislation_code, :l_legislation_code) =
                :l_legislation_code
    AND PDB.balance_type_id = pbt.balance_type_id
    AND PBD.balance_dimension_id = PDB.balance_dimension_id
    AND NVL (PDB.legislation_code, :l_legislation_code) =  :l_legislation_code
   AND PBD.database_item_suffix = :p_dimension_suffix

Parameters:
Balance Type: Gross Pay
Legislation Code: GB
Dimension: _ASG_YTD

    Change these values depends on your requirement. 
    Above example is to get ‘Gross_YTD’ – Gross Pay Year to Date.
    Get assignment action id from pay_assignment_actions using payroll_action_id (refer in Example1)

     Get Balance value using defined balance ID and assignment action ID:

     pay_balance_pkg.get_value(p_defined_balance_ID,p_assignment_action_id)

     -- Example to get balance value
     select
     pay_balance_pkg.get_value(241,108585390)
     from dual;

Refer Statement of Earnings post published in August 2013 for fetching other values of SOE report. 


to be contd…


Please do suggest or comment if you have any concerns regarding this post.
Thank you.

Regards,
Rajesh