VIEW: APPS.PAY_REVERSE_PAYROLL_V

Source

View Text - Preformatted

SELECT /*+ INDEX(PPF PAY_PAYROLLS_F_PK) */ PPA.EFFECTIVE_DATE , PPA.ACTION_TYPE , PAA.ASSIGNMENT_ID , decode(PAA.source_action_id,null,HR_T.meaning, HR_T.MEANING||' ('||PRT.RUN_TYPE_NAME||')') ACT_TYP_MEANING , PAA.ACTION_STATUS , PCS.CONSOLIDATION_SET_NAME , PCS2.CONSOLIDATION_SET_NAME REVERSE_CONSOL_NAME , PPF.CONSOLIDATION_SET_ID REVERSE_CONSOL_ID , PPA.CONSOLIDATION_SET_ID , PPA.PAYROLL_ACTION_ID , PPA.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID , PAF.PAYROLL_ID , PAA.ASSIGNMENT_ACTION_ID , PAA.SOURCE_ACTION_ID , PAA.ACTION_SEQUENCE , PPA.LAST_UPDATE_DATE , PPA.LAST_UPDATED_BY , PPA.LAST_UPDATE_LOGIN , PPA.CREATED_BY , PPA.CREATION_DATE FROM PAY_PAYROLL_ACTIONS PPA, PAY_ASSIGNMENT_ACTIONS PAA, PAY_CONSOLIDATION_SETS PCS, PAY_CONSOLIDATION_SETS PCS2, PAY_RUN_TYPES_F_VL PRT, PER_ALL_ASSIGNMENTS_F PAF, PAY_PAYROLLS_F PPF, FND_SESSIONS FND, HR_LOOKUPS HR_T WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PPA.CONSOLIDATION_SET_ID = PCS.CONSOLIDATION_SET_ID AND PAF.PAYROLL_ID = PPF.PAYROLL_ID AND FND.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE AND PCS2.CONSOLIDATION_SET_ID = PPF.CONSOLIDATION_SET_ID AND PPA.ACTION_TYPE IN ('R','Q') AND PAA.ACTION_STATUS = 'C' AND PAA.RUN_TYPE_ID = PRT.RUN_TYPE_ID (+) AND PPA.ACTION_TYPE = HR_T.LOOKUP_CODE AND HR_T.LOOKUP_TYPE = 'ACTION_TYPE' AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND FND.SESSION_ID = USERENV('sessionid') AND PPA.EFFECTIVE_DATE <= FND.EFFECTIVE_DATE AND FND.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE AND FND.EFFECTIVE_DATE BETWEEN NVL(PRT.EFFECTIVE_START_DATE, FND.EFFECTIVE_DATE) AND NVL(PRT.EFFECTIVE_END_DATE, FND.EFFECTIVE_DATE) AND NOT EXISTS( SELECT 1 FROM PAY_ASSIGNMENT_ACTIONS PAA2, PAY_ACTION_INTERLOCKS PAI, PAY_PAYROLL_ACTIONS PPA2 WHERE PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PAI.LOCKING_ACTION_ID = PAA2.ASSIGNMENT_ACTION_ID AND PAA2.PAYROLL_ACTION_ID = PPA2.PAYROLL_ACTION_ID AND PPA2.ACTION_TYPE = 'V')

View Text - HTML Formatted

SELECT /*+ INDEX(PPF PAY_PAYROLLS_F_PK) */ PPA.EFFECTIVE_DATE
, PPA.ACTION_TYPE
, PAA.ASSIGNMENT_ID
, DECODE(PAA.SOURCE_ACTION_ID
, NULL
, HR_T.MEANING
, HR_T.MEANING||' ('||PRT.RUN_TYPE_NAME||')') ACT_TYP_MEANING
, PAA.ACTION_STATUS
, PCS.CONSOLIDATION_SET_NAME
, PCS2.CONSOLIDATION_SET_NAME REVERSE_CONSOL_NAME
, PPF.CONSOLIDATION_SET_ID REVERSE_CONSOL_ID
, PPA.CONSOLIDATION_SET_ID
, PPA.PAYROLL_ACTION_ID
, PPA.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PAF.PAYROLL_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.SOURCE_ACTION_ID
, PAA.ACTION_SEQUENCE
, PPA.LAST_UPDATE_DATE
, PPA.LAST_UPDATED_BY
, PPA.LAST_UPDATE_LOGIN
, PPA.CREATED_BY
, PPA.CREATION_DATE
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_CONSOLIDATION_SETS PCS
, PAY_CONSOLIDATION_SETS PCS2
, PAY_RUN_TYPES_F_VL PRT
, PER_ALL_ASSIGNMENTS_F PAF
, PAY_PAYROLLS_F PPF
, FND_SESSIONS FND
, HR_LOOKUPS HR_T
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.CONSOLIDATION_SET_ID = PCS.CONSOLIDATION_SET_ID
AND PAF.PAYROLL_ID = PPF.PAYROLL_ID
AND FND.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PCS2.CONSOLIDATION_SET_ID = PPF.CONSOLIDATION_SET_ID
AND PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PAA.ACTION_STATUS = 'C'
AND PAA.RUN_TYPE_ID = PRT.RUN_TYPE_ID (+)
AND PPA.ACTION_TYPE = HR_T.LOOKUP_CODE
AND HR_T.LOOKUP_TYPE = 'ACTION_TYPE'
AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND FND.SESSION_ID = USERENV('SESSIONID')
AND PPA.EFFECTIVE_DATE <= FND.EFFECTIVE_DATE
AND FND.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN NVL(PRT.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(PRT.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND NOT EXISTS( SELECT 1
FROM PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_ACTION_INTERLOCKS PAI
, PAY_PAYROLL_ACTIONS PPA2
WHERE PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAI.LOCKING_ACTION_ID = PAA2.ASSIGNMENT_ACTION_ID
AND PAA2.PAYROLL_ACTION_ID = PPA2.PAYROLL_ACTION_ID
AND PPA2.ACTION_TYPE = 'V')