ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4715151A9E05362FB09050512 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 Y PAY PYAUAPL_XML US PAY Advance Pay Listing (Australasia) Imported from BI Publisher Description: Advance Pay Listing (Australasia) Application: Payroll Source: Advance Pay Listing (Australasia) (XML) Short Name: PYAUAPL_XML DB package: PAY_PYAUAPL_XMLP_PKG BI Publisher :cp_current :cp_future :cp_leave_end_date :cp_leave_start_date :cp_lv_adv :cp_lv_adv_m :cp_no :cp_pay_date :cp_pay_date1 :cp_yes :p_advance_period :p_business_group_id :p_conc_request_id :p_employee_name :p_employee_number :p_end_date :p_payroll_id :p_payroll_name :p_person_id :p_start_date :p_start_date1 1 -20 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group Id Business Group Id 2 10 :p_payroll_id LOV Oracle PY_SRS_PAYROLL Y select pay.payroll_id id, pay.payroll_name value, null description from pay_payrolls_f pay,fnd_sessions fnd where pay.business_group_id= fnd_global.per_business_group_id and fnd.session_id=userenv('sessionid') and fnd.effective_date between pay.effective_start_date and pay.effective_end_date order by pay.payroll_name Y US Payroll Payroll Name 3 20 :p_start_date Date SELECT FND_DATE.DATE_TO_DISPLAYDATE(EFFECTIVE_DATE) FROM FND_SESSIONS WHERE SESSION_ID=USERENV('SESSIONID') Y US START_DATE Start Date 4 30 :p_advance_period Number 90 Y US Advance Period (Days) Advance Period 5 40 :p_end_date Date SELECT FND_DATE.DATE_TO_DISPLAYDATE(EFFECTIVE_DATE) FROM FND_SESSIONS WHERE SESSION_ID=USERENV('SESSIONID') US End Date End Date 6 50 :p_person_id LOV Oracle PY_AU_ADV_PERSON Y select per.person_id id, employee_number value, null description from per_people_f per,per_assignments_f ppa where ppa.business_group_id=fnd_global.per_business_group_id and ppa.person_id=per.person_id and ppa.payroll_id= :$flex$.py_srs_payroll and ppa.effective_start_date between per.effective_start_date and per.effective_end_date and to_date(:$flex$.start_date,'YYYY/MM/DD HH24:MI:SS') between ppa.effective_start_date and ppa.effective_end_date order by per.employee_number US Employee Number Employee Number :$flex$.py_srs_payroll Payroll Employee Number :$flex$.start_date START_DATE Y Employee Number