VIEW: APPS.PAY_AU_ASG_LEAVE_TAKEN_V3

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, pab.date_start, pab.date_end, decode(pat.hours_or_days,'H',pab.absence_hours, pab.absence_days), /*Bug3887983*/ to_number(prv_p.result_value) payment, ppa.action_type action_type from per_time_periods ptp, per_all_assignments_f paa, /* Modified per_assignments_f to per_all_assignments_f Bug 4731967 */ pay_payroll_actions ppa, pay_assignment_actions pac, pay_run_results prr, pay_run_result_values prv_p, pay_element_types_f pet, pay_input_values_f piv_a, pay_input_values_f piv_p, per_absence_attendance_types pat, per_absence_attendances pab, pay_element_entries_f pee, pay_input_values_f piv_i3, 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 and pac.assignment_id = pee.assignment_id /* Added for Bug#2665475 */ and pee.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 */ and pac.assignment_id = paa.assignment_id /* Added for bug 4731967 */ /* Starts 8711506 - Added condition to fetch only non-time definition absences */ and prr.time_definition_id is null /* Ends 8711506 */ and ppa.date_earned between ptp.start_date and ptp.end_date and pat.absence_attendance_type_id = pab.absence_attendance_type_id and piv_a.input_value_id = pat.input_value_id and pet.element_type_id = piv_a.element_type_id and pet.element_type_id = piv_p.element_type_id and pet.element_type_id = prr.element_type_id and prr.run_result_id = prv_p.run_result_id and piv_p.input_value_id = prv_p.input_value_id and piv_p.name = 'Pay Value' and pab.absence_attendance_id = pee.creator_id and pee.creator_type = 'A' and prr.source_id = pee.element_entry_id /* Start 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_a.effective_start_date and piv_a.effective_end_date and pee.effective_start_date between piv_p.effective_start_date and piv_p.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_a.input_value_id = peev1.input_value_id))

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
, PAB.DATE_START
, PAB.DATE_END
, DECODE(PAT.HOURS_OR_DAYS
, 'H'
, PAB.ABSENCE_HOURS
, PAB.ABSENCE_DAYS)
, /*BUG3887983*/ TO_NUMBER(PRV_P.RESULT_VALUE) PAYMENT
, PPA.ACTION_TYPE ACTION_TYPE
FROM PER_TIME_PERIODS PTP
, PER_ALL_ASSIGNMENTS_F PAA
, /* MODIFIED PER_ASSIGNMENTS_F TO PER_ALL_ASSIGNMENTS_F BUG 4731967 */ PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRV_P
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV_A
, PAY_INPUT_VALUES_F PIV_P
, PER_ABSENCE_ATTENDANCE_TYPES PAT
, PER_ABSENCE_ATTENDANCES PAB
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_INPUT_VALUES_F PIV_I3
, 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
AND PAC.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID /* ADDED FOR BUG#2665475 */
AND PEE.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 */
AND PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID /* ADDED FOR BUG 4731967 */ /* STARTS 8711506 - ADDED CONDITION TO FETCH ONLY NON-TIME DEFINITION ABSENCES */
AND PRR.TIME_DEFINITION_ID IS NULL /* ENDS 8711506 */
AND PPA.DATE_EARNED BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND PAT.ABSENCE_ATTENDANCE_TYPE_ID = PAB.ABSENCE_ATTENDANCE_TYPE_ID
AND PIV_A.INPUT_VALUE_ID = PAT.INPUT_VALUE_ID
AND PET.ELEMENT_TYPE_ID = PIV_A.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = PIV_P.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRV_P.RUN_RESULT_ID
AND PIV_P.INPUT_VALUE_ID = PRV_P.INPUT_VALUE_ID
AND PIV_P.NAME = 'PAY VALUE'
AND PAB.ABSENCE_ATTENDANCE_ID = PEE.CREATOR_ID
AND PEE.CREATOR_TYPE = 'A'
AND PRR.SOURCE_ID = PEE.ELEMENT_ENTRY_ID /* START 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_A.EFFECTIVE_START_DATE
AND PIV_A.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PIV_P.EFFECTIVE_START_DATE
AND PIV_P.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_A.INPUT_VALUE_ID = PEEV1.INPUT_VALUE_ID))