VIEW: APPS.PAY_PAYROLL_ACTIONS_V2

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)