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 |
