Queries after the Payroll is run in Oracle EBS R12

Which tables are affected when payroll is run

Following are some of the tables which are affected when payroll is run. 

Main Tables that are affected

1. PAY_PAYROLL_ACTIONS2. PAY_ASSIGNMENT_ACTIONS3. PAY_RUN_RESULTS 
4. PAY_RUN_RESULT_VALUES 

Linked tables

PAY_ELEMENT_TYPES_F
PAY_INPUT_VALUES
PER_TIME_PERIODS

 

PAY_PAYROLL_ACTIONS

It records each payroll actions identified by column ACTION_TYPE. Primary key of the table is PAYROLL_ACTION_ID. You can also know when the payroll was run and for which period through TIME_PERIOD_ID columns which can be linked with PER_TIME_PERIODS table.

 

PAY_ASSIGNMENT_ACTIONS

Through this table you can know which payroll has been run for which assignment. It links with PAY_PAYROLL_ACTION by PAYROLL_ACTION_ID and PER_ALL_ASSIGNMENTS_F through ASSIGNMENT_ID.

PAY_RUN_RESULTS

You can know how may elements have been used when payroll was run which can be known through ELEMENT_TYPE_ID column which can be linked with PAY_ELEMENT_TYPES_F.

PAY_RUN_RESULT_VALUES

This table tells you when payroll was run, which element carried what values. You can join this table with PAY_INPUT_VALUES though INPUT_VALUE_ID column in this table.

 

0. Run the quick pay for any employee and follow the below steps.

SELECT DISTINCT FULL_NAME, ASSIGNMENT_ID  
FROM PER_ALL_ASSIGNMENTS_F PAAF, 
PER_ALL_PEOPLE_F PAPF

WHERE PAAF.PERSON_ID = PAPF.PERSON_ID

--AND PAYROLL_ID = 106

AND ASSIGNMENT_ID IN (7658, 7661)
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

SELECT payroll_id,payroll_name

FROM PAY_ALL_PAYROLLS_F

ORDER BY PAYROLL_ID

1. PAY_PAYROLL_ACTIONS

SELECT PAYROLL_ACTION_ID, ACTION_TYPE, PPA.PAYROLL_ID,EFFECTIVE_DATE,

DISPLAY_RUN_NUMBER,PPA.CREATED_BY, PTP.TIME_PERIOD_ID, PTP.PERIOD_NAME,

PTP.END_DATE, TRIM(TO_CHAR(PTP.END_DATE,'MON-YYYY')) MONTH

 FROM PAY_PAYROLL_ACTIONS PPA, 
PER_TIME_PERIODS PTP

WHERE PPA.PAYROLL_ID = 106

AND ACTION_TYPE = 'Q'

AND DATE_EARNED = '27-AUG-2013'

AND PPA.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID

2.

SELECT * FROM PAY_ASSIGNMENT_ACTIONS

WHERE PAYROLL_ACTION_ID IN

(SELECT PAYROLL_ACTION_ID

 FROM PAY_PAYROLL_ACTIONS PPA, PER_TIME_PERIODS PTP

WHERE PPA.PAYROLL_ID = 106

AND ACTION_TYPE = 'Q'

AND DATE_EARNED = '27-AUG-2013'

AND PPA.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID)

SELECT * FROM PER_ALL_ASSIGNMENTS_F

WHERE  ASSIGNMENT_ID IN (7658, 7661)

3.

SELECT * FROM PAY_RUN_RESULTS

WHERE ASSIGNMENT_ACTION_ID IN

(SELECT ASSIGNMENT_ACTION_ID FROM PAY_ASSIGNMENT_ACTIONS

WHERE PAYROLL_ACTION_ID IN

(SELECT PAYROLL_ACTION_ID

 FROM PAY_PAYROLL_ACTIONS PPA, PER_TIME_PERIODS PTP

WHERE PPA.PAYROLL_ID = 106

AND ACTION_TYPE = 'Q'

AND DATE_EARNED = '27-AUG-2013'

AND PPA.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID

AND ASSIGNMENT_ID = 7658))-- FOR ONE EMPLOYEE ASSIGNMENT

SELECT ELEMENT_TYPE_ID,ELEMENT_NAME FROM PAY_ELEMENT_TYPES_F

WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE

SELECT PRR.RUN_RESULT_ID,PRR.ELEMENT_TYPE_ID,PRR.SOURCE_TYPE, PRR.STATUS,

PRR.ELEMENT_ENTRY_ID,PETF.ELEMENT_NAME

FROM PAY_RUN_RESULTS PRR, PAY_ELEMENT_TYPES_F PETF

WHERE

PRR.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID

AND ASSIGNMENT_ACTION_ID IN

(SELECT ASSIGNMENT_ACTION_ID FROM PAY_ASSIGNMENT_ACTIONS

WHERE PAYROLL_ACTION_ID IN

(SELECT PAYROLL_ACTION_ID

 FROM PAY_PAYROLL_ACTIONS PPA, PER_TIME_PERIODS PTP

WHERE PPA.PAYROLL_ID = 106

AND ACTION_TYPE = 'Q'

AND DATE_EARNED = '27-AUG-2013'

AND PPA.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID

AND ASSIGNMENT_ID = 7658))

4.

SELECT *

 FROM PAY_RUN_RESULT_VALUES PRRV

SELECT PRRV.INPUT_VALUE_ID, RUN_RESULT_ID, RESULT_VALUE, FORMULA_RESULT_FLAG

 FROM PAY_RUN_RESULT_VALUES PRRV

SELECT PRRV.INPUT_VALUE_ID, RUN_RESULT_ID, RESULT_VALUE, FORMULA_RESULT_FLAG

 FROM PAY_RUN_RESULT_VALUES PRRV

      WHERE RUN_RESULT_ID

      IN

( SELECT PRR.RUN_RESULT_ID

FROM PAY_RUN_RESULTS PRR, PAY_ELEMENT_TYPES_F PETF

WHERE

PRR.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID

AND ASSIGNMENT_ACTION_ID IN

(SELECT ASSIGNMENT_ACTION_ID FROM PAY_ASSIGNMENT_ACTIONS

WHERE PAYROLL_ACTION_ID IN

(SELECT PAYROLL_ACTION_ID

 FROM PAY_PAYROLL_ACTIONS PPA, PER_TIME_PERIODS PTP

WHERE PPA.PAYROLL_ID = 106

AND ACTION_TYPE = 'Q'

AND DATE_EARNED = '27-AUG-2013'

AND PPA.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID

AND ASSIGNMENT_ID = 7658)))

    

SELECT  PRR.RUN_RESULT_ID,

PETF.ELEMENT_NAME,PRRV.INPUT_VALUE_ID, PIV.NAME,FORMULA_RESULT_FLAG, RESULT_VALUE

FROM

PAY_RUN_RESULT_VALUES PRRV, PAY_INPUT_VALUES_F PIV,

PAY_RUN_RESULTS PRR, PAY_ELEMENT_TYPES_F PETF

WHERE

PRR.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID

AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID

AND PIV.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID

AND ASSIGNMENT_ACTION_ID IN

(SELECT ASSIGNMENT_ACTION_ID FROM PAY_ASSIGNMENT_ACTIONS

WHERE PAYROLL_ACTION_ID IN

(SELECT PAYROLL_ACTION_ID

 FROM PAY_PAYROLL_ACTIONS PPA, PER_TIME_PERIODS PTP

WHERE PPA.PAYROLL_ID = 106

AND ACTION_TYPE = 'Q'

AND DATE_EARNED = '27-AUG-2013'

AND PPA.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID

AND ASSIGNMENT_ID = 7658))

SELECT * FROM PAY_INPUT_VALUES_F

FINAL QUERY TO SEE ALL THE ELEMENTS AND THEIR VALUES IN THE PAYROLL RUN:

select papf.employee_number,papf.full_name,ppa.effective_date,pp.payroll_name,
pet.element_name,piv.name input_value,prrv.result_value,ppa.payroll_action_id
from apps.pay_payroll_actions ppa,
 pay_assignment_actions paa,
pay_payrolls_f pp,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_element_types_f pet,
apps.per_all_assignments_f paaf,
apps.per_all_people_f papf
--where ppa.payroll_action_id = :payroll_action_id -- give your payroll_action_id
where ppa.payroll_id = :payroll_id
--and paa.assignment_action_id = :assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_id = pp.payroll_id
and paa.assignment_action_id = prr.assignment_action_id
and prr.run_result_id= prrv.run_result_id
and prrv.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id
and paaf.assignment_id = paa.assignment_id
and paaf.person_id = papf.person_id
and trunc(sysdate) between pp.effective_start_date and pp.effective_end_date
and trunc(sysdate) between pet.effective_start_date and pet.effective_end_date
and trunc(sysdate) between piv.effective_start_date and piv.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and papf.employee_number = '3930'  --give your employee number here
--and ppa.effective_date = '27-JUN-2013'
and ELEMENT_NAME = 'Basic Salary' -- give your element name here
order by employee_number