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_V1

Source

View Text - Preformatted


SELECT /*+ LEADING(paa ptp ppa pac pee prr) */ pab.rowid, paa.assignment_id assignment_id, ptp.time_period_id time_period_id, nvl(pet.reporting_name, pet.element_name) element_reporting_name, decode(pet.processing_type,'R',greatest(pab.date_start,PTP.START_DATE),pab.date_start) start_date1, decode(pet.processing_type,'R',least(pab.date_end,PTP.END_DATE),pab.date_end) end_date1, decode(pet.processing_type,'R',SUM(to_number(prv_i2.result_value)) ,decode(pat.hours_or_days,'H',pab.absence_hours, pab.absence_days)) absence_hours, /*Bug3887983*/ SUM(to_number(prv_i.result_value)) payment, ppa.action_type action_type from per_time_periods ptp, per_all_assignments_f paa, /* Added for bug 4731967 , 4731967 */ pay_payroll_actions ppa, pay_assignment_actions pac, pay_run_results prr, pay_element_types_f pet, pay_input_values_f piv, pay_run_results prr_i, pay_run_results prr_i2, pay_run_result_values prv_i, pay_run_result_values prv_i2, pay_input_values_f piv_i, pay_input_values_f piv_i2, pay_input_values_f piv_i3, per_absence_attendance_types pat, per_absence_attendances pab, pay_element_entries_f pee, pay_element_entry_values_f peev1 where ptp.payroll_id = ppa.payroll_id and ppa.payroll_action_id = pac.payroll_action_id and pac.assignment_action_id = prr.assignment_action_id /* Starts 8711506 - Added condition to fetch only non-time definition absences */ and prr.time_definition_id is null /* Ends 8711506 */ and pac.assignment_id = pee.assignment_id /* Added for Bug#2665475 */ and pee.assignment_id = paa.assignment_id /* Added for bug 4731967 */ and pac.assignment_id = paa.assignment_id /* Added for bug 4731967 */ and ppa.effective_date between paa.effective_start_date and paa.effective_end_date /* Added for bug 4731967, 9870944 */ and ppa.date_earned between ptp.start_date and ptp.end_date and pet.element_type_id = piv.element_type_id and pet.element_type_id = prr.element_type_id and piv.input_value_id = pat.input_value_id and pat.absence_attendance_type_id = pab.absence_attendance_type_id and pab.absence_attendance_id = pee.creator_id and not exists (select /*+UNNEST*/ null from pay_input_values_f piv_pv where piv_pv.element_type_id = pet.element_type_id and piv_pv.name = 'Pay Value' ) and prr_i.source_id = prr.source_id and prr_i2.source_id = prr.source_id and prr_i.source_type = 'I' and prr_i2.source_type = 'I' and prr_i.assignment_action_id = prr.assignment_action_id and prr_i2.assignment_action_id = prr.assignment_action_id and prr_i.run_result_id = prv_i.run_result_id and prr_i2.run_result_id = prv_i2.run_result_id /* Bug 10408503 */ and NVL(prr.start_date,ptp.start_date) = NVL(prr_i.start_date,ptp.start_date) and NVL(prr.end_date,ptp.end_date) = NVL(prr_i.end_date,ptp.end_date) and NVL(prr.start_date,ptp.start_date) = NVL(prr_i2.start_date,ptp.start_date) and NVL(prr.end_date,ptp.end_date) = NVL(prr_i2.end_date,ptp.end_date) /* Bug 10408503 */ and not exists /* Modified for bug 4731967 */(select /*+UNNEST*/ null from pay_element_types_f pet1 where element_name = 'Advance Outstanding' and pet1.element_type_id = prr_i.element_type_id and ppa.effective_date between pet1.effective_start_date and pet1.effective_end_date) and piv_i.input_value_id = prv_i.input_value_id and piv_i2.input_value_id = prv_i2.input_value_id and prv_i.run_result_id = prv_i2.run_result_id /* Added for Bug 12721091*/ and prv_i.result_value is not null /* Added for Bug 12721091 */ and piv_i.name = 'Pay Value' and not exists /* Bug#4688702 */ ( select /*+UNNEST*/ null from pay_element_classifications pec, pay_sub_classification_rules_f pscr where pscr.element_type_id in (piv_i.element_type_id,piv_i2.element_type_id) /*Bug 4731967 */ 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 exists /* Bug# 13686075 */ ( select /*+UNNEST*/ null from pay_element_types_f pet1 where pet1.element_type_id = piv_i.element_type_id and nvl(pet1.ELEMENT_INFORMATION3,'Y') = 'Y' and ppa.effective_date between pet1.effective_start_date and pet1.effective_end_date ) and piv_i2.name in ('Hours','Days') /*Bug3887983*/ and pee.creator_type = 'A' and prr.source_id = pee.element_entry_id /* Starts 8711506 - Added date track joins */ and pee.effective_start_date between pet.effective_start_date and pet.effective_end_date and pee.effective_start_date between piv.effective_start_date and piv.effective_end_date and pee.effective_start_date between piv_i.effective_start_date and piv_i.effective_end_date and pee.effective_start_date between piv_i2.effective_start_date and piv_i2.effective_end_date and pee.effective_start_date between nvl(piv_i3.effective_start_date,pee.effective_start_date) and nvl(piv_i3.effective_end_date,pee.effective_end_date) and pee.effective_start_date between peev1.effective_start_date and peev1.effective_end_date /* Ends 8711506 */ and piv_i3.name(+) = 'Pay Date' and piv_i3.element_type_id(+) = pet.element_type_id and pee.element_entry_id = peev1.element_entry_id and ( ( peev1.input_value_id = piv_i3.input_value_id and nvl(to_date(peev1.screen_entry_value,'YYYY/MM/DD hh24:mi:ss'),ptp.start_date) between ptp.start_date and ptp.end_date) or (piv_i3.input_value_id is null and piv.input_value_id = peev1.input_value_id)) /* Bug 10408503 */ GROUP BY pab.rowid, paa.assignment_id , ptp.time_period_id , nvl(pet.reporting_name, pet.element_name) , decode(pet.processing_type,'R',greatest(pab.date_start,PTP.START_DATE),pab.date_start) , decode(pet.processing_type,'R',least(pab.date_end,PTP.END_DATE),pab.date_end) , pet.processing_type, decode(pat.hours_or_days,'H',pab.absence_hours, pab.absence_days), ppa.action_type /*Bug 12532831 */ /* Bug 10408503 */

View Text - HTML Formatted

SELECT /*+ LEADING(PAA PTP PPA PAC PEE PRR) */ PAB.ROWID
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PTP.TIME_PERIOD_ID TIME_PERIOD_ID
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME) ELEMENT_REPORTING_NAME
, DECODE(PET.PROCESSING_TYPE
, 'R'
, GREATEST(PAB.DATE_START
, PTP.START_DATE)
, PAB.DATE_START) START_DATE1
, DECODE(PET.PROCESSING_TYPE
, 'R'
, LEAST(PAB.DATE_END
, PTP.END_DATE)
, PAB.DATE_END) END_DATE1
, DECODE(PET.PROCESSING_TYPE
, 'R'
, SUM(TO_NUMBER(PRV_I2.RESULT_VALUE))
, DECODE(PAT.HOURS_OR_DAYS
, 'H'
, PAB.ABSENCE_HOURS
, PAB.ABSENCE_DAYS)) ABSENCE_HOURS
, /*BUG3887983*/ SUM(TO_NUMBER(PRV_I.RESULT_VALUE)) PAYMENT
, PPA.ACTION_TYPE ACTION_TYPE
FROM PER_TIME_PERIODS PTP
, PER_ALL_ASSIGNMENTS_F PAA
, /* ADDED FOR BUG 4731967
, 4731967 */ PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_RUN_RESULTS PRR
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULTS PRR_I
, PAY_RUN_RESULTS PRR_I2
, PAY_RUN_RESULT_VALUES PRV_I
, PAY_RUN_RESULT_VALUES PRV_I2
, PAY_INPUT_VALUES_F PIV_I
, PAY_INPUT_VALUES_F PIV_I2
, PAY_INPUT_VALUES_F PIV_I3
, PER_ABSENCE_ATTENDANCE_TYPES PAT
, PER_ABSENCE_ATTENDANCES PAB
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_ENTRY_VALUES_F PEEV1
WHERE PTP.PAYROLL_ID = PPA.PAYROLL_ID
AND PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PAC.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID /* STARTS 8711506 - ADDED CONDITION TO FETCH ONLY NON-TIME DEFINITION ABSENCES */
AND PRR.TIME_DEFINITION_ID IS NULL /* ENDS 8711506 */
AND PAC.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID /* ADDED FOR BUG#2665475 */
AND PEE.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID /* ADDED FOR BUG 4731967 */
AND PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID /* ADDED FOR BUG 4731967 */
AND PPA.EFFECTIVE_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE /* ADDED FOR BUG 4731967
, 9870944 */
AND PPA.DATE_EARNED BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PIV.INPUT_VALUE_ID = PAT.INPUT_VALUE_ID
AND PAT.ABSENCE_ATTENDANCE_TYPE_ID = PAB.ABSENCE_ATTENDANCE_TYPE_ID
AND PAB.ABSENCE_ATTENDANCE_ID = PEE.CREATOR_ID
AND NOT EXISTS (SELECT /*+UNNEST*/ NULL
FROM PAY_INPUT_VALUES_F PIV_PV
WHERE PIV_PV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PIV_PV.NAME = 'PAY VALUE' )
AND PRR_I.SOURCE_ID = PRR.SOURCE_ID
AND PRR_I2.SOURCE_ID = PRR.SOURCE_ID
AND PRR_I.SOURCE_TYPE = 'I'
AND PRR_I2.SOURCE_TYPE = 'I'
AND PRR_I.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PRR_I2.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PRR_I.RUN_RESULT_ID = PRV_I.RUN_RESULT_ID
AND PRR_I2.RUN_RESULT_ID = PRV_I2.RUN_RESULT_ID /* BUG 10408503 */
AND NVL(PRR.START_DATE
, PTP.START_DATE) = NVL(PRR_I.START_DATE
, PTP.START_DATE)
AND NVL(PRR.END_DATE
, PTP.END_DATE) = NVL(PRR_I.END_DATE
, PTP.END_DATE)
AND NVL(PRR.START_DATE
, PTP.START_DATE) = NVL(PRR_I2.START_DATE
, PTP.START_DATE)
AND NVL(PRR.END_DATE
, PTP.END_DATE) = NVL(PRR_I2.END_DATE
, PTP.END_DATE) /* BUG 10408503 */
AND NOT EXISTS /* MODIFIED FOR BUG 4731967 */(SELECT /*+UNNEST*/ NULL
FROM PAY_ELEMENT_TYPES_F PET1
WHERE ELEMENT_NAME = 'ADVANCE OUTSTANDING'
AND PET1.ELEMENT_TYPE_ID = PRR_I.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PET1.EFFECTIVE_START_DATE
AND PET1.EFFECTIVE_END_DATE)
AND PIV_I.INPUT_VALUE_ID = PRV_I.INPUT_VALUE_ID
AND PIV_I2.INPUT_VALUE_ID = PRV_I2.INPUT_VALUE_ID
AND PRV_I.RUN_RESULT_ID = PRV_I2.RUN_RESULT_ID /* ADDED FOR BUG 12721091*/
AND PRV_I.RESULT_VALUE IS NOT NULL /* ADDED FOR BUG 12721091 */
AND PIV_I.NAME = 'PAY VALUE'
AND NOT EXISTS /* BUG#4688702 */ ( SELECT /*+UNNEST*/ NULL
FROM PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_SUB_CLASSIFICATION_RULES_F PSCR
WHERE PSCR.ELEMENT_TYPE_ID IN (PIV_I.ELEMENT_TYPE_ID
, PIV_I2.ELEMENT_TYPE_ID) /*BUG 4731967 */
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 EXISTS /* BUG# 13686075 */ ( SELECT /*+UNNEST*/ NULL
FROM PAY_ELEMENT_TYPES_F PET1
WHERE PET1.ELEMENT_TYPE_ID = PIV_I.ELEMENT_TYPE_ID
AND NVL(PET1.ELEMENT_INFORMATION3
, 'Y') = 'Y'
AND PPA.EFFECTIVE_DATE BETWEEN PET1.EFFECTIVE_START_DATE
AND PET1.EFFECTIVE_END_DATE )
AND PIV_I2.NAME IN ('HOURS'
, 'DAYS') /*BUG3887983*/
AND PEE.CREATOR_TYPE = 'A'
AND PRR.SOURCE_ID = PEE.ELEMENT_ENTRY_ID /* STARTS 8711506 - ADDED DATE TRACK JOINS */
AND PEE.EFFECTIVE_START_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PIV_I.EFFECTIVE_START_DATE
AND PIV_I.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PIV_I2.EFFECTIVE_START_DATE
AND PIV_I2.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN NVL(PIV_I3.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_START_DATE)
AND NVL(PIV_I3.EFFECTIVE_END_DATE
, PEE.EFFECTIVE_END_DATE)
AND PEE.EFFECTIVE_START_DATE BETWEEN PEEV1.EFFECTIVE_START_DATE
AND PEEV1.EFFECTIVE_END_DATE /* ENDS 8711506 */
AND PIV_I3.NAME(+) = 'PAY DATE'
AND PIV_I3.ELEMENT_TYPE_ID(+) = PET.ELEMENT_TYPE_ID
AND PEE.ELEMENT_ENTRY_ID = PEEV1.ELEMENT_ENTRY_ID
AND ( ( PEEV1.INPUT_VALUE_ID = PIV_I3.INPUT_VALUE_ID
AND NVL(TO_DATE(PEEV1.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS')
, PTP.START_DATE) BETWEEN PTP.START_DATE
AND PTP.END_DATE) OR (PIV_I3.INPUT_VALUE_ID IS NULL
AND PIV.INPUT_VALUE_ID = PEEV1.INPUT_VALUE_ID)) /* BUG 10408503 */ GROUP BY PAB.ROWID
, PAA.ASSIGNMENT_ID
, PTP.TIME_PERIOD_ID
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)
, DECODE(PET.PROCESSING_TYPE
, 'R'
, GREATEST(PAB.DATE_START
, PTP.START_DATE)
, PAB.DATE_START)
, DECODE(PET.PROCESSING_TYPE
, 'R'
, LEAST(PAB.DATE_END
, PTP.END_DATE)
, PAB.DATE_END)
, PET.PROCESSING_TYPE
, DECODE(PAT.HOURS_OR_DAYS
, 'H'
, PAB.ABSENCE_HOURS
, PAB.ABSENCE_DAYS)
, PPA.ACTION_TYPE /*BUG 12532831 */ /* BUG 10408503 */