Most Important Payroll tables in Oracle Fusion HCM

Payroll tables in Oracle Fusion HCM

Hi friends, we are going to discuss about the Payroll tables in Oracle Fusion HCM. We will share the some of the important Payroll tables in oracle fusion. We will share some of those tables payroll tables which we generally uses in our day to day work. As a Fusion HCM consultant , We should have the complete awareness of the payroll tables and how front end application data do saves in the back end. We should have the understanding of Payroll tables if we want to develop the custom BIP reports in oracle fusion or wants to extract the payroll related data in Oracle Fusion HCM. In this post, we will also try to share one of the useful payroll sql query too using the Payroll tables which helps to extract the payroll related data in oracle fusion. Please find below the complete detail about Payroll tables in Oracle Fusion HCM.

Most Important Payroll tables in Oracle Fusion HCM

Top 7 Payroll tables in Oracle Fusion HCM

1.PAY_ALL_PAYROLLS_F

2.PAY_CONSOLIDATION_SETS

3.PAY_PAYROLL_REL_ACTIONS

4.PAY_TIME_PERIODS

5.PAY_REL_GROUPS_DN

6.PAY_PAYROLL_ACTIONS

7.PAY_ASSIGNED_PAYROLLS_DN 

Payroll SQL query to extract the Payroll data in Oracle Fusion HCM

Here below we are sharing one of the useful sql query used by some of the important payroll tables of Oracle fusion HCM. Using this sql query we can develop the BIP report and can extract the payroll data from the system.

SELECT    

PAPF.PERSON_NUMBER "Employee Number"

, py.payroll_name "Payroll Name"

, ptp.period_name "Payroll Period"

, PCS.CONSOLIDATION_SET_NAME "Payroll Set Name"

, PPA.CREATION_DATE "Payroll Processes Date"

, ptp.start_date "Payroll Period Start Date"

, ptp.end_date period_End_Date "Payroll Period End Date"

FROM

pay_payroll_actions ppa

, PAY_PAYROLL_REL_ACTIONS PRA

, pay_assigned_payrolls_dn apd

, PER_ALL_ASSIGNMENTS_M PAAM

, pay_time_periods ptp

, PAY_REL_GROUPS_DN PRG

,  PER_ALL_PEOPLE_F PAPF

, pay_all_payrolls_f py

, pay_consolidation_sets pcs

, pay_payroll_terms pt

WHERE   1=1     

AND PRA.PAYROLL_ACTION_ID  = PPA.PAYROLL_ACTION_ID

AND ppa.action_type IN ('R')

AND PAAM.ASSIGNMENT_ID = PRG.ASSIGNMENT_ID

AND PCS.CONSOLIDATION_SET_ID = NVL(PPA.CONSOLIDATION_SET_ID,PY.CONSOLIDATION_SET_ID)

AND PTP.PERIOD_CATEGORY = 'E'

AND PRG.PAYROLL_RELATIONSHIP_ID = PRA.PAYROLL_RELATIONSHIP_ID

AND pt.payroll_term_id = apd.payroll_term_id

AND py.payroll_id = apd.payroll_id

AND PRA.SOURCE_ACTION_ID IS NULL

AND pt.hr_term_id = PRG.term_id

AND PAAM.ASSIGNMENT_TYPE = 'E'

AND PAAM.PRIMARY_FLAG  ='Y'

AND PAPF.PERSON_ID = PAAM.PERSON_ID

AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE) BETWEEN PY.EFFECTIVE_START_DATE AND PY.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE

AND ptp.payroll_id = py.payroll_id

AND PPA.EARN_TIME_PERIOD_ID = PTP.TIME_PERIOD_ID

ORDER BY  1

Most Important Payroll tables in Oracle Fusion HCM