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_V5

Source

View Text - Preformatted


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 in /* bug 7217439*/ (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

View Text - HTML Formatted

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 IN /* BUG 7217439*/ (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