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.
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
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