Oracle Payroll Tables and Payroll Queries

By | December 9, 2015

Important Tables

There is a big list of oracle payroll tables. But these are main tables which every technical consultant must know in detail. I have categorized these tables into following three categories.

Element Entry Related Tables
—————————
PAY_ELEMENT_TYPES_F
PAY_ELEMENT_ENTRIES_F
PAY_INPUT_VALUES_F
PAY_ELEMENT_LINKS_F

Payroll Run Related Tables
————————-
PAY_ALL_PAYROLLS_F
PAY_PAYROLL_ACTIONS
PAY_ASSIGNMENT_ACTIONS
PAY_RUN_RESULTS
PAY_RUN_RESULT_VALUES

Costing & Pre Payment Related Tables
————————-
PAY_COSTS
PAY_PAYMENT_COSTS
PAY_PRE_PAYMENTS

Query to Get the Element History for an Employee

———-Starts Here———
select ppf.person_id
,period_name
,pet.element_name
,prrv.result_value
,ppos.person_id
from
per_people_f ppf
, apps.pay_element_types_f pet
, apps.pay_input_values_f piv
, apps.pay_run_result_values prrv
, apps.pay_run_results prr
, apps.pay_payroll_actions ppa
, apps.pay_assignment_actions paa
, apps.pay_element_classifications pec
, apps.per_time_periods ptp
, apps.per_assignments_f paf
, apps.pay_payrolls_f pay
, apps.per_periods_of_service ppos
where ppf.person_id = paf.person_id
and paf.assignment_id = paa.assignment_id
and piv.name = ‘Pay Value’
and prrv.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id(+)
and prr.run_result_id = prrv.run_result_id
and paa.payroll_action_id = ppa.payroll_action_id
and prr.assignment_action_id = paa.assignment_action_id
and pet.element_type_id = prr.element_type_id
and ppa.action_type in (‘Q’, ‘R’)
and pet.classification_id = pec.classification_id
and ptp.time_period_id = ppa.time_period_id(+)
and pay.payroll_id = paf.payroll_id
AND ppos.person_id = ppf.person_id
and ptp.payroll_id = paf.payroll_id
and nvl (ppa.date_earned, ppa.effective_date)
between pet.effective_start_date
and pet.effective_end_date
and nvl (ppa.date_earned, ppa.effective_date)
between piv.effective_start_date
and piv.effective_end_date
and nvl (ppa.date_earned, ppa.effective_date)
between paf.effective_start_date
and paf.effective_end_date
and nvl (ppa.date_earned, ppa.effective_date)
between ppf.effective_start_date
and ppf.effective_end_date
and ppf.employee_number = ‘600099’
and upper(pet.element_name) like ‘%COMMISSION%’
and ptp.start_date between ’01-JAN-2014′ and ’28-NOV-2015′
–and ptp.start_date between last_day(add_months(ppos.actual_termination_date,-7))+1
— and last_day(add_months(ppos.actual_termination_date,-1))

———-Ends Here———–

Output Looks Like this

In the diagram, it shows the month wise earning for element Commission for selected employee

Oracle Payroll Tables

Month Wise Element History for an Employee

Related Article

Payroll Process and Relevant Tables
Payroll Mistakes Correction Methodology
Element Termination Rules
Get Free Evaluation of your HRMS Skills
9 Proven Steps to become Oracle Apps Consultant
XML Bursting Feature and its Solution files