Most Important Oracle Fusion Payroll Queries

Hi Friends, we are going to discuss about Oracle Fusion Payroll Queries. We will share the detail sql query which helps to extract the employees payroll data in oracle fusion. Using these Payroll queries , we can find out the complete details about payroll data. In this post , We will also share some of the important tables related to oracle fusion payroll. Using these Payroll queries we can develop the Custom BIP reports in Oracle Fusion which helps to extract the complete payroll details. The best part of this below shared query is , we will get the payroll data as per the employee. It means , we will get the employee wise payroll data using these queries. Please find below the complete detail about Oracle Fusion Payroll Queries.

Most Important Oracle Fusion Payroll Queries

8 Important Tables uses in Oracle Fusion Payroll Queries

1.PAY_PAYROLL_REL_ACTIONS

2.PAY_PAYROLL_ACTIONS

3.PAY_TIME_PERIODS

4.PAY_REL_GROUPS_DN

5.PAY_ALL_PAYROLLS_F

6.PAY_CONSOLIDATION_SETS

7.PAY_ASSIGNED_PAYROLLS_DN

8.PAY_PAYROLL_TERMS 

Detail Level of Oracle Fusion Payroll Queries

Here below we are sharing one of the important Oracle Fusion Payroll Query to extract the Employee Payroll data for the month.

SELECT    

PAPF.PERSON_NUMBER "Employee Number"

, py.payroll_name "Payroll Name"

, ptp.start_date "Payroll Period Start Date"

, ptp.end_date period_End_Date "Payroll Period End Date"

, PPA.CREATION_DATE "Payroll Processes Date"

, ptp.period_name "Payroll Period"

, PCS.CONSOLIDATION_SET_NAME "Payroll Set Name"

FROM

PER_ALL_ASSIGNMENTS_M PAAM

, PER_ALL_PEOPLE_F PAPF

, pay_all_payrolls_f py

, pay_consolidation_sets pcs

,pay_payroll_actions ppa

, PAY_PAYROLL_REL_ACTIONS PRA

, pay_assigned_payrolls_dn apd

, pay_payroll_terms pt

, pay_time_periods ptp

, PAY_REL_GROUPS_DN PRG

WHERE   1=1     

AND PRA.PAYROLL_ACTION_ID  = PPA.PAYROLL_ACTION_ID

AND ppa.action_type IN ('R')

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 PAAM.ASSIGNMENT_ID = PRG.ASSIGNMENT_ID

AND PCS.CONSOLIDATION_SET_ID = NVL(PPA.CONSOLIDATION_SET_ID,PY.CONSOLIDATION_SET_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

AND PRA.SOURCE_ACTION_ID IS NULL

AND pt.hr_term_id = PRG.term_id

ORDER BY  PAPF.PERSON_NUMBER

Most Important Oracle Fusion Payroll Queries