VIEW: APPS.PAY_AU_ASG_LEAVE_TAKEN_V7

Source

View Text - Preformatted

SELECT /*+ LEADING(paa ptp ppa pac pee prr) */ pee.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.element_name, 'Rec Statutory PPL Payment', greatest(to_date(peev1.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'), ptp.start_date), 'Statutory Paid Parental Leave Payment' ,to_date(peev1.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'))start_date, decode(pet.element_name, 'Rec Statutory PPL Payment',least(to_date(peev2.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'), ptp.end_date), 'Statutory Paid Parental Leave Payment' , to_date(peev2.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS')) end_date, to_number(prv.result_value)+NULL absence_hours, to_number(prv.result_value) payment, ppa.action_type action_type FROM per_time_periods ptp, per_all_assignments_f paa, pay_assignment_actions pac, pay_payroll_actions ppa, pay_run_results prr, pay_run_result_values prv, pay_element_entries_f pee, pay_element_entry_values_f peev1, pay_element_entry_values_f peev2, pay_input_values_f piv1, pay_input_values_f piv2, pay_input_values_f piv, pay_element_types_f pet 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 AND pee.assignment_id = paa.assignment_id AND pac.assignment_id = paa.assignment_id AND prr.element_entry_id = pee.element_entry_id AND prr.source_id = pee.element_entry_id AND pet.element_name In ('Rec Statutory PPL Payment','Statutory Paid Parental Leave Payment') AND pet.legislation_code = 'AU' AND pee.element_entry_id = peev1.element_entry_id AND pee.effective_start_date = peev1.effective_start_date AND pee.effective_end_date = peev1.effective_end_date AND peev1.input_value_id = piv1.input_value_id AND piv1.name = 'Start Date' AND pee.element_entry_id = peev2.element_entry_id AND pee.effective_start_date = peev2.effective_start_date AND pee.effective_end_date = peev2.effective_end_date AND peev2.input_value_id = piv2.input_value_id AND piv2.name = 'End Date' AND prr.run_result_id = prv.run_result_id AND prv.input_value_id = piv.input_value_id AND piv.name = 'Pay Value' and pet.element_type_id = pee.element_type_id and pet.element_type_id = prr.element_type_id and pet.element_type_id = piv.element_type_id and pet.element_type_id = piv1.element_type_id and pet.element_type_id = piv2.element_type_id AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date AND pee.effective_start_date BETWEEN paa.effective_start_date AND paa.effective_end_date AND ppa.date_earned BETWEEN piv1.effective_start_date AND piv1.effective_end_date AND ppa.date_earned BETWEEN piv2.effective_start_date AND piv2.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 peev1.effective_start_date AND peev1.effective_end_date AND ppa.date_earned BETWEEN peev2.effective_start_date AND peev2.effective_end_date AND ppa.date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date AND pee.creator_type NOT IN ('EE','RR') UNION ALL SELECT /*+ LEADING(paa ptp ppa pac pee prr) */ pee.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.element_name, 'Rec Statutory PPL Payment', greatest(to_date(peev1.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'),pee.SOURCE_START_DATE), 'Statutory Paid Parental Leave Payment' ,to_date(peev1.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'))start_date, decode(pet.element_name, 'Rec Statutory PPL Payment',least(to_date(peev2.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'),pee.SOURCE_END_DATE), 'Statutory Paid Parental Leave Payment' , to_date(peev2.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS')) end_date, to_number(prv.result_value)+NULL absence_hours, to_number(prv.result_value) payment, ppa.action_type action_type FROM per_time_periods ptp, per_all_assignments_f paa, pay_assignment_actions pac, pay_payroll_actions ppa, pay_run_results prr, pay_run_result_values prv, pay_element_entries_f pee, /* retro element */ pay_element_entries_f pee1, /* orig element */ pay_element_entry_values_f peev1, pay_element_entry_values_f peev2, pay_input_values_f piv1, pay_input_values_f piv2, pay_input_values_f piv, pay_element_types_f pet 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 AND pac.assignment_id = pee1.assignment_id AND pee.assignment_id = paa.assignment_id AND pee1.assignment_id = paa.assignment_id AND pac.assignment_id = paa.assignment_id AND prr.element_entry_id = pee.element_entry_id AND prr.source_id = pee.element_entry_id AND pee.creator_type IN ('EE','RR') AND (pee.source_id = pee1.element_entry_id OR pee.source_id IN (SELECT prr1.run_result_id FROM pay_run_results prr1 WHERE prr1.element_type_id=pee1.element_type_id AND prr1.source_id =pee1.element_entry_id )) AND pet.element_name IN ( 'Rec Statutory PPL Payment','Statutory Paid Parental Leave Payment') AND pet.legislation_code = 'AU' AND pee1.element_type_id = pet.element_type_id AND pee1.element_entry_id = peev1.element_entry_id AND pee1.effective_start_date = peev1.effective_start_date AND pee1.effective_end_date = peev1.effective_end_date AND peev1.input_value_id = piv1.input_value_id AND piv1.name = 'Start Date' AND pee1.element_entry_id = peev2.element_entry_id AND pee1.effective_start_date = peev2.effective_start_date AND pee1.effective_end_date = peev2.effective_end_date AND peev2.input_value_id = piv2.input_value_id AND piv2.name = 'End Date' AND prr.run_result_id = prv.run_result_id AND prv.input_value_id = piv.input_value_id AND piv.name = 'Pay Value' and pet.element_type_id = piv1.element_type_id and pet.element_type_id = piv2.element_type_id and pee.SOURCE_START_DATE between pee1.effective_start_date and pee1.effective_end_date and pee.SOURCE_END_DATE between pee1.effective_start_date and pee1.effective_end_date AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date AND pee.effective_start_date BETWEEN paa.effective_start_date AND paa.effective_end_date AND ppa.date_earned BETWEEN piv1.effective_start_date AND piv1.effective_end_date AND ppa.date_earned BETWEEN piv2.effective_start_date AND piv2.effective_end_date AND ppa.date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date AND ppa.date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date

View Text - HTML Formatted

SELECT /*+ LEADING(PAA PTP PPA PAC PEE PRR) */ PEE.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.ELEMENT_NAME
, 'REC STATUTORY PPL PAYMENT'
, GREATEST(TO_DATE(PEEV1.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS')
, PTP.START_DATE)
, 'STATUTORY PAID PARENTAL LEAVE PAYMENT'
, TO_DATE(PEEV1.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS'))START_DATE
, DECODE(PET.ELEMENT_NAME
, 'REC STATUTORY PPL PAYMENT'
, LEAST(TO_DATE(PEEV2.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS')
, PTP.END_DATE)
, 'STATUTORY PAID PARENTAL LEAVE PAYMENT'
, TO_DATE(PEEV2.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS')) END_DATE
, TO_NUMBER(PRV.RESULT_VALUE)+NULL ABSENCE_HOURS
, TO_NUMBER(PRV.RESULT_VALUE) PAYMENT
, PPA.ACTION_TYPE ACTION_TYPE
FROM PER_TIME_PERIODS PTP
, PER_ALL_ASSIGNMENTS_F PAA
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_PAYROLL_ACTIONS PPA
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRV
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_ENTRY_VALUES_F PEEV1
, PAY_ELEMENT_ENTRY_VALUES_F PEEV2
, PAY_INPUT_VALUES_F PIV1
, PAY_INPUT_VALUES_F PIV2
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F PET
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
AND PEE.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PRR.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PRR.SOURCE_ID = PEE.ELEMENT_ENTRY_ID
AND PET.ELEMENT_NAME IN ('REC STATUTORY PPL PAYMENT'
, 'STATUTORY PAID PARENTAL LEAVE PAYMENT')
AND PET.LEGISLATION_CODE = 'AU'
AND PEE.ELEMENT_ENTRY_ID = PEEV1.ELEMENT_ENTRY_ID
AND PEE.EFFECTIVE_START_DATE = PEEV1.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE = PEEV1.EFFECTIVE_END_DATE
AND PEEV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND PIV1.NAME = 'START DATE'
AND PEE.ELEMENT_ENTRY_ID = PEEV2.ELEMENT_ENTRY_ID
AND PEE.EFFECTIVE_START_DATE = PEEV2.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE = PEEV2.EFFECTIVE_END_DATE
AND PEEV2.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID
AND PIV2.NAME = 'END DATE'
AND PRR.RUN_RESULT_ID = PRV.RUN_RESULT_ID
AND PRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.NAME = 'PAY VALUE'
AND PET.ELEMENT_TYPE_ID = PEE.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = PIV1.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID
AND PPA.DATE_EARNED BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PIV1.EFFECTIVE_START_DATE
AND PIV1.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PIV2.EFFECTIVE_START_DATE
AND PIV2.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 PEEV1.EFFECTIVE_START_DATE
AND PEEV1.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PEEV2.EFFECTIVE_START_DATE
AND PEEV2.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PEE.CREATOR_TYPE NOT IN ('EE'
, 'RR') UNION ALL SELECT /*+ LEADING(PAA PTP PPA PAC PEE PRR) */ PEE.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.ELEMENT_NAME
, 'REC STATUTORY PPL PAYMENT'
, GREATEST(TO_DATE(PEEV1.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS')
, PEE.SOURCE_START_DATE)
, 'STATUTORY PAID PARENTAL LEAVE PAYMENT'
, TO_DATE(PEEV1.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS'))START_DATE
, DECODE(PET.ELEMENT_NAME
, 'REC STATUTORY PPL PAYMENT'
, LEAST(TO_DATE(PEEV2.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS')
, PEE.SOURCE_END_DATE)
, 'STATUTORY PAID PARENTAL LEAVE PAYMENT'
, TO_DATE(PEEV2.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS')) END_DATE
, TO_NUMBER(PRV.RESULT_VALUE)+NULL ABSENCE_HOURS
, TO_NUMBER(PRV.RESULT_VALUE) PAYMENT
, PPA.ACTION_TYPE ACTION_TYPE
FROM PER_TIME_PERIODS PTP
, PER_ALL_ASSIGNMENTS_F PAA
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_PAYROLL_ACTIONS PPA
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRV
, PAY_ELEMENT_ENTRIES_F PEE
, /* RETRO ELEMENT */ PAY_ELEMENT_ENTRIES_F PEE1
, /* ORIG ELEMENT */ PAY_ELEMENT_ENTRY_VALUES_F PEEV1
, PAY_ELEMENT_ENTRY_VALUES_F PEEV2
, PAY_INPUT_VALUES_F PIV1
, PAY_INPUT_VALUES_F PIV2
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F PET
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
AND PAC.ASSIGNMENT_ID = PEE1.ASSIGNMENT_ID
AND PEE.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PEE1.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PRR.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PRR.SOURCE_ID = PEE.ELEMENT_ENTRY_ID
AND PEE.CREATOR_TYPE IN ('EE'
, 'RR')
AND (PEE.SOURCE_ID = PEE1.ELEMENT_ENTRY_ID OR PEE.SOURCE_ID IN (SELECT PRR1.RUN_RESULT_ID
FROM PAY_RUN_RESULTS PRR1
WHERE PRR1.ELEMENT_TYPE_ID=PEE1.ELEMENT_TYPE_ID
AND PRR1.SOURCE_ID =PEE1.ELEMENT_ENTRY_ID ))
AND PET.ELEMENT_NAME IN ( 'REC STATUTORY PPL PAYMENT'
, 'STATUTORY PAID PARENTAL LEAVE PAYMENT')
AND PET.LEGISLATION_CODE = 'AU'
AND PEE1.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PEE1.ELEMENT_ENTRY_ID = PEEV1.ELEMENT_ENTRY_ID
AND PEE1.EFFECTIVE_START_DATE = PEEV1.EFFECTIVE_START_DATE
AND PEE1.EFFECTIVE_END_DATE = PEEV1.EFFECTIVE_END_DATE
AND PEEV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND PIV1.NAME = 'START DATE'
AND PEE1.ELEMENT_ENTRY_ID = PEEV2.ELEMENT_ENTRY_ID
AND PEE1.EFFECTIVE_START_DATE = PEEV2.EFFECTIVE_START_DATE
AND PEE1.EFFECTIVE_END_DATE = PEEV2.EFFECTIVE_END_DATE
AND PEEV2.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID
AND PIV2.NAME = 'END DATE'
AND PRR.RUN_RESULT_ID = PRV.RUN_RESULT_ID
AND PRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.NAME = 'PAY VALUE'
AND PET.ELEMENT_TYPE_ID = PIV1.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID
AND PEE.SOURCE_START_DATE BETWEEN PEE1.EFFECTIVE_START_DATE
AND PEE1.EFFECTIVE_END_DATE
AND PEE.SOURCE_END_DATE BETWEEN PEE1.EFFECTIVE_START_DATE
AND PEE1.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PIV1.EFFECTIVE_START_DATE
AND PIV1.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PIV2.EFFECTIVE_START_DATE
AND PIV2.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE