PAY Advance Pay Listing (Australasia)

Select 	ppf.payroll_name Payroll_Name,pap.full_name Full_Name, paa.assignment_number Assignment_Number,
                pev1.screen_entry_value Pay_Date, pev2.screen_entry_value  Leave_Start_Date,
                pev3.screen_entry_value Leave_End_Date, hl4.meaning Leave_Category,pev5.screen_entry_value Leave_amount,
                hl6.meaning Units,
	PAY_PYAUAPL_XMLP_PKG.cf_current_future_flagformula() CF_Current_Future_Flag,
	PAY_PYAUAPL_XMLP_PKG.cf_process_flagformula(TO_DATE(pev2.screen_entry_value,'YYYY/MM/DD HH24:MI:SS'),TO_DATE(pev3.screen_entry_value,'YYYY/MM/DD HH24:MI:SS')) CF_Process_Flag,
	PAY_PYAUAPL_XMLP_PKG.cf_date_conversionformula(pev2.screen_entry_value, pev3.screen_entry_value, pev1.screen_entry_value) CF_Date_Conversion,
	PAY_PYAUAPL_XMLP_PKG.CP_Leave_Start_Date_p CP_Leave_Start_Date,
	--PAY_PYAUAPL_XMLP_PKG.CP_Pay_Date_p CP_Pay_Date,
	TO_DATE(pev1.screen_entry_value,'YYYY/MM/DD HH24:MI:SS') CP_Pay_Date,
	--PAY_PYAUAPL_XMLP_PKG.CP_Leave_End_Date_p CP_Leave_End_Date
	TO_DATE(pev3.screen_entry_value,'YYYY/MM/DD HH24:MI:SS') CP_Leave_End_Date
From	pay_payrolls_f ppf,per_people_f pap,per_assignments_f paa,pay_element_types_f pet,pay_element_entries_f pee, per_absence_attendance_types pat,pay_element_links_f pel,
                pay_element_entry_values_f pev1, pay_input_values_f piv1, hr_lookups hl1,
                pay_element_entry_values_f pev2,pay_input_values_f piv2,  hr_lookups hl2,
                pay_element_entry_values_f pev3, pay_input_values_f piv3, hr_lookups hl3,
                hr_lookups hl4,
                pay_element_entry_values_f pev5,
                hr_lookups hl6,pay_element_entry_values_f pev6,
                pay_element_entry_values_f pev7, pay_input_values_f piv7,hr_lookups hl7
Where	ppf.payroll_id = paa.payroll_id
and          (paa.payroll_id = :P_PAYROLL_ID or :P_PAYROLL_ID is NULL)
and	(pap.person_id = :P_PERSON_ID or :P_PERSON_ID is NULL)
and	:p_start_date between pap.effective_start_date and pap.effective_end_date
and	pap.person_id = paa.person_id
and          :p_start_date between paa.effective_start_date and paa.effective_end_date
and          :p_start_date between pet.effective_start_date and pet.effective_end_date
and          :p_start_date between ppf.effective_start_date and ppf.effective_end_date
and          pee.assignment_id = paa.assignment_id
and          pet.element_type_id = pel.element_type_id
and          pel.element_link_id = pee.element_link_id
and          pee.effective_start_date between pel.effective_start_date and pel.effective_end_date
and          pet.advance_indicator = 'Y'
and          pev1.input_value_id = piv1.input_value_id
and          pee.element_entry_id = pev1.element_entry_id
and          UPPER(TRANSLATE(piv1.name,' ','_')) = UPPER(TRANSLATE(hl1.meaning,' ','_'))
and          hl1.lookup_type = 'NAME_TRANSLATIONS'
and          hl1.lookup_code = 'PAY_DATE'
and          pev2.input_value_id = piv2.input_value_id
and          pee.element_entry_id = pev2.element_entry_id
and          UPPER(TRANSLATE(piv2.name,' ','_')) = UPPER(TRANSLATE(hl2.meaning,' ','_'))
and          hl2.lookup_type = 'NAME_TRANSLATIONS'
and          hl2.lookup_code = 'START_DATE'
and          pev3.input_value_id = piv3.input_value_id
and          pee.element_entry_id = pev3.element_entry_id
and          UPPER(TRANSLATE(piv3.name,' ','_')) = UPPER(TRANSLATE(hl3.meaning,' ','_'))
and          hl3.lookup_type = 'NAME_TRANSLATIONS'
and          hl3.lookup_code = 'END_DATE'
and          hl4.lookup_type = 'ABSENCE_CATEGORY'
and          hl4.lookup_code = pat.absence_category
and          pat.input_value_id = pev5.input_value_id
and          pee.element_entry_id = pev5.element_entry_id
and          pat.input_value_id = pev6.input_value_id
and          pee.element_entry_id = pev6.element_entry_id
and          hl6.lookup_code = pat.hours_or_days
and          hl6.lookup_type = 'HOURS_OR_DAYS'
and          pev7.input_value_id = piv7.input_value_id
and          pee.element_entry_id = pev7.element_entry_id
and          UPPER(TRANSLATE(piv7.name,' ','_')) = UPPER(TRANSLATE(hl7.meaning,' ','_'))
and          hl7.lookup_type = 'NAME_TRANSLATIONS'
and          hl7.lookup_code = 'ADV_OVERRIDE'
and          pev7.screen_entry_value <> 'Y'
and         exists (select 1 from per_absence_attendances  pab
                              where pab.absence_attendance_type_id=pat.absence_attendance_type_id
                              and pab.person_id=pap.person_id
                              and pab.absence_attendance_id=pee.creator_id
                           )
and          exists (select 'is date' from dual where trunc(to_date(pev2.screen_entry_value,'YYYY/MM/DD HH24:MI:SS')) between :p_start_date and :cp_lv_adv_m )
Order By  Payroll_Name,nvl(pap.order_name,Full_Name),Leave_Start_Date