Product: | PAY - Payroll |
---|---|
Description: | Used to support user interface |
Implementation/DBA Data: | APPS.PAY_PAYROLL_ACTIONS_V2 |
SELECT PAC.ROWID ROW_ID
, PAC.PAYROLL_ID
, PAC.PAYROLL_ACTION_ID
, PAC.ACTION_TYPE
, PAC.EFFECTIVE_DATE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PAID_PERIOD
, PAID.START_DATE PAID_START
, PAID.END_DATE PAID_END
, PAC.DATE_EARNED
, DECODE (PAC.DATE_EARNED
, NULL
, NULL
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID)) EARNED_PERIOD
, DECODE (PAC.DATE_EARNED
, NULL
, NULL
, EARNED.START_DATE) EARNED_START
, DECODE (PAC.DATE_EARNED
, NULL
, NULL
, EARNED.END_DATE) EARNED_END
, PAC.ACTION_STATUS
, PAC.ACTION_POPULATION_STATUS
, PAC.DISPLAY_RUN_NUMBER
, PAC.LEGISLATIVE_PARAMETERS
, PAC.RUN_TYPE_ID
, PAC.CONSOLIDATION_SET_ID
, PCS.CONSOLIDATION_SET_NAME CSNAME
, PAC.ASSIGNMENT_SET_ID
, HAS.ASSIGNMENT_SET_NAME ASNAME
, PAC.ELEMENT_SET_ID
, PES.ELEMENT_SET_NAME ESNAME
, PAC.PAY_ADVICE_DATE
, PAC.PAY_ADVICE_MESSAGE
, PAC.COMMENTS
, PAC.LAST_UPDATE_DATE
, PAC.LAST_UPDATED_BY
, PAC.LAST_UPDATE_LOGIN
, PAC.CREATED_BY
, PAC.CREATION_DATE
, PAC.ATTRIBUTE_CATEGORY
, PAC.ATTRIBUTE1
, PAC.ATTRIBUTE2
, PAC.ATTRIBUTE3
, PAC.ATTRIBUTE4
, PAC.ATTRIBUTE5
, PAC.ATTRIBUTE6
, PAC.ATTRIBUTE7
, PAC.ATTRIBUTE8
, PAC.ATTRIBUTE9
, PAC.ATTRIBUTE10
, PAC.ATTRIBUTE11
, PAC.ATTRIBUTE12
, PAC.ATTRIBUTE13
, PAC.ATTRIBUTE14
, PAC.ATTRIBUTE15
, PAC.ATTRIBUTE16
, PAC.ATTRIBUTE17
, PAC.ATTRIBUTE18
, PAC.ATTRIBUTE19
, PAC.ATTRIBUTE20
FROM PAY_ELEMENT_SETS PES
, PAY_CONSOLIDATION_SETS PCS
, HR_ASSIGNMENT_SETS HAS
, PER_TIME_PERIODS EARNED
, PER_TIME_PERIODS PAID
, PAY_PAYROLL_ACTIONS PAC
, FND_SESSIONS FND
WHERE PAC.ACTION_TYPE != 'I'
AND PAC.PAYROLL_ID = PAID.PAYROLL_ID /*********************************/ /* JOIN DATE PAID TO PAID PERIOD */ /*********************************/
AND PAC.EFFECTIVE_DATE BETWEEN PAID.START_DATE
AND PAID.END_DATE /*******************************/ /* EXCLUDE FUTURE PAYROLL RUNS */ /*******************************/
AND FND.EFFECTIVE_DATE > PAID.START_DATE
AND FND.SESSION_ID = USERENV('SESSIONID') /**************************************************************/ /* JOIN EARNED DATE TO EARNED PERIOD. THE CLAUSE WHICH */ /* INCLUDES EFFECTIVE_DATE ENSURES WE PICK UP A SINGLE ROW */ /* WHEN DATE_EARNED IS NULL - IT'S DECODED TO NULL IN THE*/ /* SELECT STATEMENT ABOVE.*/ /**************************************************************/
AND PAC.PAYROLL_ID = EARNED.PAYROLL_ID
AND ( PAC.DATE_EARNED BETWEEN EARNED.START_DATE
AND EARNED.END_DATE OR (PAC.DATE_EARNED IS NULL
AND PAC.EFFECTIVE_DATE BETWEEN EARNED.START_DATE
AND EARNED.END_DATE )) /**********************************************/ /* CONSOLIDATION
, ELEMENT
AND ASSIGNMENT SETS */ /**********************************************/
AND PAC.CONSOLIDATION_SET_ID = PCS.CONSOLIDATION_SET_ID (+)
AND PAC.ASSIGNMENT_SET_ID = HAS.ASSIGNMENT_SET_ID (+)
AND PAC.ELEMENT_SET_ID = PES.ELEMENT_SET_ID (+)