VIEW: APPS.PAY_NZ_ASG_LEAVE_TAKEN_V2

Source

View Text - Preformatted

SELECT pab.rowid , pac.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(prrv1.result_value),decode(pat.hours_or_days, 'D', pab.absence_days, pab.absence_hours))) absence_duration , sum(to_number(prrv2.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_element_entries_f pee , pay_assignment_actions pac1 , pay_run_results prr1 , pay_element_entries_f pee1 , pay_element_types_f pet1 , per_absence_attendances pab , per_absence_attendance_types pat , pay_run_result_values prrv1 , pay_input_values_f piv1 , pay_run_result_values prrv2 , pay_input_values_f piv2 WHERE ppa.payroll_action_id = pac.payroll_action_id and pac.assignment_action_id = prr.assignment_action_id and 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 prr.source_id = pee.element_entry_id and pee.creator_type in ('RR','EE') and prr.element_type_id = pet.element_type_id and prr.run_result_id = prrv1.run_result_id and prrv1.input_value_id = piv1.input_value_id and prr.element_type_id = piv1.element_type_id and piv1.name = 'Leave Taken' and prr.run_result_id = prrv2.run_result_id and prrv2.input_value_id = piv2.input_value_id and prr.element_type_id = piv2.element_type_id and piv2.name = 'Pay Value' and pee.source_asg_action_id = pac1.assignment_action_id and pac1.assignment_action_id = prr1.assignment_action_id and prr1.element_entry_id = pee1.element_entry_id and pee1.element_type_id = pet1.element_type_id and pee1.creator_type = 'A' and pab.absence_attendance_id = pee1.creator_id and pat.absence_attendance_type_id = pab.absence_attendance_type_id and ppa.date_earned between pet.effective_start_date and pet.effective_end_date and ppa.date_earned between pee.effective_start_date and pee.effective_end_date group by pab.rowid , pac.assignment_id , ptp.time_period_id , nvl(pet1.reporting_name, pet1.element_name) , pab.date_start , pab.date_end , ppa.date_earned

View Text - HTML Formatted

SELECT PAB.ROWID
, PAC.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(PRRV1.RESULT_VALUE)
, DECODE(PAT.HOURS_OR_DAYS
, 'D'
, PAB.ABSENCE_DAYS
, PAB.ABSENCE_HOURS))) ABSENCE_DURATION
, SUM(TO_NUMBER(PRRV2.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_ELEMENT_ENTRIES_F PEE
, PAY_ASSIGNMENT_ACTIONS PAC1
, PAY_RUN_RESULTS PRR1
, PAY_ELEMENT_ENTRIES_F PEE1
, PAY_ELEMENT_TYPES_F PET1
, PER_ABSENCE_ATTENDANCES PAB
, PER_ABSENCE_ATTENDANCE_TYPES PAT
, PAY_RUN_RESULT_VALUES PRRV1
, PAY_INPUT_VALUES_F PIV1
, PAY_RUN_RESULT_VALUES PRRV2
, PAY_INPUT_VALUES_F PIV2
WHERE PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PAC.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND 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 PRR.SOURCE_ID = PEE.ELEMENT_ENTRY_ID
AND PEE.CREATOR_TYPE IN ('RR'
, 'EE')
AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV1.RUN_RESULT_ID
AND PRRV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND PRR.ELEMENT_TYPE_ID = PIV1.ELEMENT_TYPE_ID
AND PIV1.NAME = 'LEAVE TAKEN'
AND PRR.RUN_RESULT_ID = PRRV2.RUN_RESULT_ID
AND PRRV2.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID
AND PRR.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID
AND PIV2.NAME = 'PAY VALUE'
AND PEE.SOURCE_ASG_ACTION_ID = PAC1.ASSIGNMENT_ACTION_ID
AND PAC1.ASSIGNMENT_ACTION_ID = PRR1.ASSIGNMENT_ACTION_ID
AND PRR1.ELEMENT_ENTRY_ID = PEE1.ELEMENT_ENTRY_ID
AND PEE1.ELEMENT_TYPE_ID = PET1.ELEMENT_TYPE_ID
AND PEE1.CREATOR_TYPE = 'A'
AND PAB.ABSENCE_ATTENDANCE_ID = PEE1.CREATOR_ID
AND PAT.ABSENCE_ATTENDANCE_TYPE_ID = PAB.ABSENCE_ATTENDANCE_TYPE_ID
AND PPA.DATE_EARNED BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE GROUP BY PAB.ROWID
, PAC.ASSIGNMENT_ID
, PTP.TIME_PERIOD_ID
, NVL(PET1.REPORTING_NAME
, PET1.ELEMENT_NAME)
, PAB.DATE_START
, PAB.DATE_END
, PPA.DATE_EARNED