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.

VIEW: APPS.PAY_AU_ASG_LEAVE_TAKEN_V4

Source

View Text - Preformatted


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 */ 'R' 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_input_values_f piv1, pay_element_entry_values_f peev1, pay_element_entry_values_f peev2, pay_input_values_f piv2, pay_input_values_f piv3, pay_element_entry_values_f peev3, 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 piv1.element_type_id = pet.element_type_id /*Bug#12555430 */ 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 piv3.element_type_id = pet.element_type_id /*Bug#12555430 */ 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

View Text - HTML Formatted

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 */ 'R'
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_INPUT_VALUES_F PIV1
, PAY_ELEMENT_ENTRY_VALUES_F PEEV1
, PAY_ELEMENT_ENTRY_VALUES_F PEEV2
, PAY_INPUT_VALUES_F PIV2
, PAY_INPUT_VALUES_F PIV3
, PAY_ELEMENT_ENTRY_VALUES_F PEEV3
, 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 PIV1.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID /*BUG#12555430 */
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 PIV3.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID /*BUG#12555430 */
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