View: PAY_AU_ASG_LEAVE_TAKEN_V5

Product:	PAY - Payroll
Description:	Australian localisation view.
Implementation/DBA Data:	APPS.PAY_AU_ASG_LEAVE_TAKEN_V5

View Text

SELECT /*+ ORDERED */ PAB.ROWID
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PTP.TIME_PERIOD_ID TIME_PERIOD_ID
, NVL(PET1.REPORTING_NAME
, PET1.ELEMENT_NAME) ELEMENT_REPORTING_NAME
, PAB.DATE_START
, PAB.DATE_END
, SUM(DECODE(PET1.PROCESSING_TYPE
, 'R'
, TO_NUMBER( PAY_AU_SOE_PKG.GET_LEAVE_TAKEN_HOURS(PEE1.ELEMENT_TYPE_ID
, PRR.RUN_RESULT_ID
, PPA.EFFECTIVE_DATE))
, DECODE(PAT.HOURS_OR_DAYS
, 'H'
, PAB.ABSENCE_HOURS
, PAB.ABSENCE_DAYS)))
, /*BUG3887983
, 5597052 - GET VALUE OF HOURS
FROM FUNCTION */ SUM(TO_NUMBER(PRV_I.RESULT_VALUE)) PAYMENT
FROM PER_ALL_ASSIGNMENTS_F PAA
, PER_TIME_PERIODS PTP
, PAY_ELEMENT_ENTRIES_F PEE2
, PER_ABSENCE_ATTENDANCES PAB
, PER_ABSENCE_ATTENDANCE_TYPES PAT
, PAY_ELEMENT_LINKS_F PEL2
, PAY_ELEMENT_ENTRIES_F PEE1
, PAY_RUN_RESULTS PRR
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_PAYROLL_ACTIONS PPA
, PAY_RUN_RESULT_VALUES PRV_I
, PAY_INPUT_VALUES_F PIV_I
, PAY_ELEMENT_TYPES_F PET2
, PAY_ELEMENT_TYPES_F PET1
WHERE PEE1.CREATOR_TYPE IN ( 'EE'
, 'RR')
AND PEE1.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PEE2.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PEE2.EFFECTIVE_START_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PRR.SOURCE_ID=PEE1.ELEMENT_ENTRY_ID
AND PEE1.ELEMENT_ENTRY_ID = PRR.SOURCE_ID
AND PEE1.ELEMENT_TYPE_ID IS NOT NULL
AND PRV_I.RUN_RESULT_ID=PRR.RUN_RESULT_ID
AND PIV_I.INPUT_VALUE_ID=PRV_I.INPUT_VALUE_ID
AND PIV_I.NAME='PAY VALUE' /* COMMENTED FOR BUG 5597052 */
AND NOT EXISTS /* BUG#4688702 */ ( SELECT /*+UNNEST*/ NULL
FROM PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_SUB_CLASSIFICATION_RULES_F PSCR
WHERE PSCR.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PSCR.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PEC.CLASSIFICATION_NAME = 'LEAVE LOADING'
AND PPA.EFFECTIVE_DATE BETWEEN PSCR.EFFECTIVE_START_DATE
AND PSCR.EFFECTIVE_END_DATE )
AND NOT EXISTS (SELECT /*+UNNEST*/ NULL
FROM PAY_ELEMENT_TYPES_F PET1
WHERE ELEMENT_NAME = 'ADVANCE OUTSTANDING'
AND PET1.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PET1.EFFECTIVE_START_DATE
AND PET1.EFFECTIVE_END_DATE)
AND PET2.ELEMENT_TYPE_ID=PET1.ELEMENT_TYPE_ID
AND PEL2.ELEMENT_TYPE_ID=PET2.ELEMENT_TYPE_ID
AND PEE2.ELEMENT_LINK_ID=PEL2.ELEMENT_LINK_ID
AND (PEE1.SOURCE_ID = PEE2.ELEMENT_ENTRY_ID OR PEE1.SOURCE_ID = (SELECT PRR1.RUN_RESULT_ID
FROM PAY_RUN_RESULTS PRR1
WHERE PRR1.ELEMENT_TYPE_ID=PEE1.ELEMENT_TYPE_ID
AND PRR1.SOURCE_ID=PEE2.ELEMENT_ENTRY_ID)) /* MODIFIED FOR BUG 5922529*/
AND PEE2.CREATOR_TYPE='A'
AND PPA.DATE_EARNED BETWEEN PEE1.EFFECTIVE_START_DATE
AND PEE1.EFFECTIVE_END_DATE
AND PAB.ABSENCE_ATTENDANCE_ID=PEE2.CREATOR_ID
AND PAT.ABSENCE_ATTENDANCE_TYPE_ID=PAB.ABSENCE_ATTENDANCE_TYPE_ID
AND PPA.PAYROLL_ID=PTP.PAYROLL_ID
AND PAC.ASSIGNMENT_ID=PAA.ASSIGNMENT_ID
AND PAC.ASSIGNMENT_ID=PEE1.ASSIGNMENT_ID /* ADDED FOR BUG#2665475 */
AND PEE1.ASSIGNMENT_ID = PEE2.ASSIGNMENT_ID /* ADDED FOR BUG#2665475 */
AND PAC.ASSIGNMENT_ACTION_ID=PRR.ASSIGNMENT_ACTION_ID
AND PAC.PAYROLL_ACTION_ID=PPA.PAYROLL_ACTION_ID
AND PPA.DATE_EARNED BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND PPA.DATE_EARNED BETWEEN PET1.EFFECTIVE_START_DATE
AND PET1.EFFECTIVE_END_DATE /*BUG 4212914*/
AND PPA.DATE_EARNED BETWEEN PET2.EFFECTIVE_START_DATE
AND PET2.EFFECTIVE_END_DATE /*BUG 4212914*/
AND PPA.DATE_EARNED BETWEEN PIV_I.EFFECTIVE_START_DATE
AND PIV_I.EFFECTIVE_END_DATE /*BUG 4212914*/
AND PPA.DATE_EARNED BETWEEN PEL2.EFFECTIVE_START_DATE
AND PEL2.EFFECTIVE_END_DATE /*BUG 5036576 */ GROUP BY PAB.ROWID
, PAA.ASSIGNMENT_ID
, PTP.TIME_PERIOD_ID
, NVL(PET1.REPORTING_NAME
, PET1.ELEMENT_NAME)
, PAB.DATE_START
, PAB.DATE_END

Columns