eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: PAY_AU_ASG_LEAVE_TAKEN_V4

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

View Text

SELECT /*+ORDERED*/ PEE.ROWID
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PTP.TIME_PERIOD_ID TIME_PERIOD_ID
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME) ELEMENT_REPORTING_NAME
, TO_DATE(PEEV1.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS') DATE_START
, TO_DATE(PEEV2.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS') DATE_END
, DECODE(PET.PROCESSING_TYPE
, 'R'
, SUM(TO_NUMBER(PEEV6.SCREEN_ENTRY_VALUE))
, TO_NUMBER(PEEV0.SCREEN_ENTRY_VALUE)) ABSENCE_HOURS
, SUM(TO_NUMBER(PEEV5.SCREEN_ENTRY_VALUE)) PAYMENT /* CHANGES ARE DONE FOR BUG 6277224 */
FROM PER_ALL_ASSIGNMENTS_F PAA
, /* BUG 4731967 - CHANGED TO PER_ALL_ASSIGNMENTS_F */ PER_TIME_PERIODS PTP
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_ENTRIES_F PEE1
, PAY_ELEMENT_LINKS_F PEL1
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F PET1
, PAY_ELEMENT_ENTRY_VALUES_F PEEV0
, /*0 TO 4 FOR ADVANCE ELEMENT ATTACHED*/ PAY_INPUT_VALUES_F PIV0
, PAY_ELEMENT_ENTRY_VALUES_F PEEV1
, PAY_INPUT_VALUES_F PIV1
, PAY_ELEMENT_ENTRY_VALUES_F PEEV2
, PAY_INPUT_VALUES_F PIV2
, PAY_ELEMENT_ENTRY_VALUES_F PEEV3
, PAY_INPUT_VALUES_F PIV3
, PAY_ELEMENT_ENTRY_VALUES_F PEEV4
, PAY_INPUT_VALUES_F PIV4
, PAY_ELEMENT_ENTRY_VALUES_F PEEV5
, /* 5
AND 6 FOR ADV PAY ELEMENT CREATED*/ PAY_INPUT_VALUES_F PIV5
, PAY_ELEMENT_ENTRY_VALUES_F PEEV6
, PAY_INPUT_VALUES_F PIV6
WHERE PEE.CREATOR_TYPE = 'A'
AND PEE.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAA.PAYROLL_ID = PTP.PAYROLL_ID /* COMMENTED FOR BUG 6277224
AND PEE.EFFECTIVE_START_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_END_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PEE1.EFFECTIVE_END_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE */
AND PEE1.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PEE1.EFFECTIVE_START_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE /* ADDED FOR BUG 4731967 */ /* ADDED FOR BUG 6277224 */
AND PEE.EFFECTIVE_START_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PIV0.EFFECTIVE_START_DATE
AND PIV0.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PIV1.EFFECTIVE_START_DATE
AND PIV1.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PIV2.EFFECTIVE_START_DATE
AND PIV2.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PIV3.EFFECTIVE_START_DATE
AND PIV3.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PIV4.EFFECTIVE_START_DATE
AND PIV4.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PEEV0.EFFECTIVE_START_DATE
AND PEEV0.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PEEV1.EFFECTIVE_START_DATE
AND PEEV1.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PEEV2.EFFECTIVE_START_DATE
AND PEEV2.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PEEV3.EFFECTIVE_START_DATE
AND PEEV3.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PEEV4.EFFECTIVE_START_DATE
AND PEEV4.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PEL.EFFECTIVE_START_DATE
AND PEL.EFFECTIVE_END_DATE
AND PEE1.EFFECTIVE_START_DATE BETWEEN PET1.EFFECTIVE_START_DATE
AND PET1.EFFECTIVE_END_DATE
AND PEE1.EFFECTIVE_START_DATE BETWEEN PIV5.EFFECTIVE_START_DATE
AND PIV5.EFFECTIVE_END_DATE
AND PEE1.EFFECTIVE_START_DATE BETWEEN PIV6.EFFECTIVE_START_DATE
AND PIV6.EFFECTIVE_END_DATE
AND PEE1.EFFECTIVE_START_DATE BETWEEN PEEV5.EFFECTIVE_START_DATE
AND PEEV5.EFFECTIVE_END_DATE
AND PEE1.EFFECTIVE_START_DATE BETWEEN PEEV6.EFFECTIVE_START_DATE
AND PEEV6.EFFECTIVE_END_DATE
AND PEE1.EFFECTIVE_START_DATE BETWEEN PEL1.EFFECTIVE_START_DATE
AND PEL1.EFFECTIVE_END_DATE /* END CHANGES 6277224 */
AND PEE.ELEMENT_ENTRY_ID = PEEV0.ELEMENT_ENTRY_ID
AND PEEV0.INPUT_VALUE_ID = PIV0.INPUT_VALUE_ID
AND PIV0.NAME IN ('HOURS'
, 'DAYS')
AND PEE.ELEMENT_ENTRY_ID = PEEV1.ELEMENT_ENTRY_ID
AND PEEV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND PIV1.NAME = 'START DATE'
AND PEE.ELEMENT_ENTRY_ID = PEEV2.ELEMENT_ENTRY_ID
AND PEEV2.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID
AND PIV2.NAME = 'END DATE'
AND PEE.ELEMENT_ENTRY_ID = PEEV3.ELEMENT_ENTRY_ID
AND PEEV3.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID
AND PIV3.NAME = 'PAY DATE'
AND PEE.ELEMENT_ENTRY_ID = PEEV4.ELEMENT_ENTRY_ID
AND PEEV4.INPUT_VALUE_ID = PIV4.INPUT_VALUE_ID
AND PIV4.NAME = 'ADVANCE OVERRIDE'
AND PEEV4.SCREEN_ENTRY_VALUE = 'N'
AND PEE1.SOURCE_ID = PEE.ELEMENT_ENTRY_ID
AND PEE1.CREATOR_TYPE = 'AE'
AND PEL1.ELEMENT_LINK_ID = PEE1.ELEMENT_LINK_ID
AND PET1.ELEMENT_TYPE_ID = PEL1.ELEMENT_TYPE_ID
AND PIV5.ELEMENT_TYPE_ID = PET1.ELEMENT_TYPE_ID
AND PIV5.NAME = 'PAY VALUE'
AND PEEV5.INPUT_VALUE_ID = PIV5.INPUT_VALUE_ID
AND PEEV5.ELEMENT_ENTRY_ID = PEE1.ELEMENT_ENTRY_ID
AND PIV6.ELEMENT_TYPE_ID = PET1.ELEMENT_TYPE_ID
AND ((PET.PROCESSING_TYPE = 'R'
AND PIV6.NAME IN ('HOURS'
, 'DAYS')) /*BUG3887983*/ OR (PET.PROCESSING_TYPE = 'N'
AND PIV6.NAME = 'PAY VALUE') )
AND PEEV6.INPUT_VALUE_ID = PIV6.INPUT_VALUE_ID
AND PEEV6.ELEMENT_ENTRY_ID = PEE1.ELEMENT_ENTRY_ID
AND PEL.ELEMENT_LINK_ID = PEE.ELEMENT_LINK_ID
AND PET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
AND PET.ADVANCE_INDICATOR = 'Y'
AND NOT EXISTS /* BUG#4688702 */ ( SELECT NULL
FROM PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_SUB_CLASSIFICATION_RULES_F PSCR
WHERE PSCR.ELEMENT_TYPE_ID = PET1.ELEMENT_TYPE_ID
AND PSCR.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PEC.CLASSIFICATION_NAME = 'LEAVE LOADING'
AND PEE1.EFFECTIVE_START_DATE BETWEEN PSCR.EFFECTIVE_START_DATE
AND PSCR.EFFECTIVE_END_DATE )
AND EXISTS ( SELECT 'EXISTS'
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA1
WHERE PPA.ACTION_TYPE = 'W'
AND PEE.EFFECTIVE_START_DATE BETWEEN PPA.START_DATE
AND PPA.END_DATE
AND PAA1.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID
AND PAA1.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND NVL(TO_DATE(PEEV3.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS')
, (TO_DATE(PEEV1.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS')-1)) BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND PTP.PAYROLL_ID = PPA.PAYROLL_ID ) GROUP BY PEE.ROWID
, PAA.ASSIGNMENT_ID
, PTP.TIME_PERIOD_ID
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)
, TO_DATE(PEEV1.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS')
, TO_DATE(PEEV2.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS')
, TO_NUMBER(PEEV0.SCREEN_ENTRY_VALUE)
, PEEV5.EFFECTIVE_START_DATE
, /* CHANGES ARE DONE FOR BUG 6277224 */ PET.PROCESSING_TYPE

Columns

Name
ROW_ID
ASSIGNMENT_ID
TIME_PERIOD_ID
ELEMENT_REPORTING_NAME
START_DATE
END_DATE
ABSENCE_HOURS
PAYMENT