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