Query to get payroll name in oracle fusion

Hi friends, we are going to discuss about the sql query which helps to extract the oracle fusion payroll details and the payroll name.This SQL query which helps to extract complete payroll details in oracle fusion. In Oracle Fusion , Payroll tables have been changed as compared to the Payroll tables structure in the Oracle Apps r12. So , we will also share some of the important tables too which helps to store the payroll complete details in oracle fusion. Using this SQL query , we can able to get the Employee wise oracle fusion payroll details with payroll name for the month or period. We have develop the custom BIP report related to Employee payroll using this shared sql query in oracle fusion. You can make the changes as per your requirement in the shared query. Please find below the complete Details about Query to get payroll name in oracle fusion.

Query to get payroll name in oracle fusion

Important Table to get payroll name in oracle fusion

Here below is the Tables which helps to store the Payroll name in oracle fusion.

PAY_ALL_PAYROLLS_F

7 Other Important Payroll Tables in Oracle Fusion

Here below is some of the important Tables related to Payroll.

1.PAY_ASSIGNED_PAYROLLS_DN

2.PAY_PAYROLL_TERMS 

3.PAY_REL_GROUPS_DN

4.PAY_PAYROLL_REL_ACTIONS

5.PAY_PAYROLL_ACTIONS

6.PAY_CONSOLIDATION_SETS

7.PAY_TIME_PERIODS

Details SQL Query to get the payroll name in oracle fusion

SELECT    

PAPF.PERSON_NUMBER "Employee Number"

, py.payroll_name "Payroll Name"

, ptp.start_date "Payroll Period Start Date"

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 PPA.EARN_TIME_PERIOD_ID = PTP.TIME_PERIOD_ID

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 ptp.payroll_id = py.payroll_id

AND PRA.SOURCE_ACTION_ID IS NULL

AND PAAM.PRIMARY_FLAG  ='Y'

AND pt.hr_term_id = PRG.term_id

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 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 PAPF.PERSON_ID = PAAM.PERSON_ID

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

Order by 1

Query to get payroll name in oracle fusion

Click To Know More

Query to get payroll name in oracle fusion