Search This Blog

Monday, April 6, 2015

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
 

No comments:

Post a Comment