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
