VIEW: APPS.PAY_NZ_ASG_LEAVE_TAKEN_V3

Source

View Text - Preformatted

SELECT pab.rowid , pac.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_date , decode(pet.processing_type,'R',least(pab.date_end,PTP.END_DATE),pab.date_end) end_date , decode(pet.processing_type,'R',to_number(prv_i2.result_value),decode(pat.hours_or_days, 'D', pab.absence_days, pab.absence_hours)) absence_duration , to_number(prv_i.result_value) payment , ppa.date_earned from per_time_periods ptp , pay_payroll_actions ppa , pay_assignment_actions pac , pay_run_results prr , pay_element_types_f pet , pay_input_values_f piv , per_absence_attendance_types pat , per_absence_attendances pab , pay_element_entries_f pee , pay_element_entry_values_f pev , 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 WHERE ptp.payroll_id = ppa.payroll_id AND ptp.time_period_id = ppa.time_period_id and ppa.date_earned between ptp.start_date and ptp.end_date and ppa.payroll_action_id = pac.payroll_action_id and pac.assignment_action_id = prr.assignment_action_id and prr.source_id = pee.element_entry_id and pac.assignment_id = pee.assignment_id and pee.creator_type = 'A' and pee.creator_id = pab.absence_attendance_id and pat.absence_attendance_type_id = pab.absence_attendance_type_id and piv.input_value_id = pat.input_value_id and pet.element_type_id = prr.element_type_id and pet.element_type_id = piv.element_type_id and piv.input_value_id = pat.input_value_id and piv.input_value_id = pev.input_value_id and pee.element_entry_id = pev.element_entry_id and prr_i.source_id = prr.source_id and prr_i2.source_id = prr.source_id and prr_i.source_type = 'E' and prr_i2.source_type = 'E' 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 and piv_i.input_value_id = prv_i.input_value_id and piv_i.name = 'Pay Value' and piv_i2.input_value_id = prv_i2.input_value_id and piv_i2.name = 'Leave Taken' and ppa.date_earned between pet.effective_start_date and pet.effective_end_date and ppa.date_earned between piv.effective_start_date and piv.effective_end_date and ppa.date_earned between pee.effective_start_date and pee.effective_end_date and ppa.date_earned between pev.effective_start_date and pev.effective_end_date and ppa.date_earned between piv_i.effective_start_date and piv_i.effective_end_date and ppa.date_earned between piv_i2.effective_start_date and piv_i2.effective_end_date

View Text - HTML Formatted

SELECT PAB.ROWID
, PAC.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_DATE
, DECODE(PET.PROCESSING_TYPE
, 'R'
, LEAST(PAB.DATE_END
, PTP.END_DATE)
, PAB.DATE_END) END_DATE
, DECODE(PET.PROCESSING_TYPE
, 'R'
, TO_NUMBER(PRV_I2.RESULT_VALUE)
, DECODE(PAT.HOURS_OR_DAYS
, 'D'
, PAB.ABSENCE_DAYS
, PAB.ABSENCE_HOURS)) ABSENCE_DURATION
, TO_NUMBER(PRV_I.RESULT_VALUE) PAYMENT
, PPA.DATE_EARNED
FROM PER_TIME_PERIODS PTP
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_RUN_RESULTS PRR
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PER_ABSENCE_ATTENDANCE_TYPES PAT
, PER_ABSENCE_ATTENDANCES PAB
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_ENTRY_VALUES_F PEV
, 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
WHERE PTP.PAYROLL_ID = PPA.PAYROLL_ID
AND PTP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
AND PPA.DATE_EARNED BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PAC.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PRR.SOURCE_ID = PEE.ELEMENT_ENTRY_ID
AND PAC.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID
AND PEE.CREATOR_TYPE = 'A'
AND PEE.CREATOR_ID = PAB.ABSENCE_ATTENDANCE_ID
AND PAT.ABSENCE_ATTENDANCE_TYPE_ID = PAB.ABSENCE_ATTENDANCE_TYPE_ID
AND PIV.INPUT_VALUE_ID = PAT.INPUT_VALUE_ID
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PIV.INPUT_VALUE_ID = PAT.INPUT_VALUE_ID
AND PIV.INPUT_VALUE_ID = PEV.INPUT_VALUE_ID
AND PEE.ELEMENT_ENTRY_ID = PEV.ELEMENT_ENTRY_ID
AND PRR_I.SOURCE_ID = PRR.SOURCE_ID
AND PRR_I2.SOURCE_ID = PRR.SOURCE_ID
AND PRR_I.SOURCE_TYPE = 'E'
AND PRR_I2.SOURCE_TYPE = 'E'
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
AND PIV_I.INPUT_VALUE_ID = PRV_I.INPUT_VALUE_ID
AND PIV_I.NAME = 'PAY VALUE'
AND PIV_I2.INPUT_VALUE_ID = PRV_I2.INPUT_VALUE_ID
AND PIV_I2.NAME = 'LEAVE TAKEN'
AND PPA.DATE_EARNED BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PEV.EFFECTIVE_START_DATE
AND PEV.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PIV_I.EFFECTIVE_START_DATE
AND PIV_I.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PIV_I2.EFFECTIVE_START_DATE
AND PIV_I2.EFFECTIVE_END_DATE