ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F3003869E053B46B63588994Y SELECT act.assignment_action_id, max(decode(fue.user_entity_name,'X_PAYROLL_ID', fnd_number.canonical_to_number(fai.value))) payroll_id, max(decode(fue.user_entity_name,'X_TERMINATION_DATE', fnd_date.canonical_to_date(fai.value))) termination_date, max(decode(fue.user_entity_name,'X_ISSUE_DATE', fnd_date.canonical_to_date(fai.value))) issue_date, max(decode(fue.user_entity_name,'X_DECEASED_FLAG', substr(fai.value,1,1))) deceased_flag, max(decode(fue.user_entity_name,'X_ASSIGNMENT_NUMBER', substr(fai.VALUE,1,30))) assignment_number, max(decode(fue.user_entity_name,'X_FIRST_NAME', replace(substr(UPPER(fai.value),1,20),',',' '))) first_name, max(decode(fue.user_entity_name,'X_LAST_NAME', substr(UPPER(fai.value),1,40))) last_name, max(decode(fue.user_entity_name,'X_MONTH_NUMBER', fnd_number.canonical_to_number(fai.value))) month_number, max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER', substr(UPPER(fai.value),1,20))) ni_number, max(decode(fue.user_entity_name,'X_ORGANIZATION_NAME', substr(fai.value,1,60))) organization_name, max(decode(fue.user_entity_name,'X_TAX_PAID', fnd_number.canonical_to_number(fai.value))) tax_paid, max(decode(fue.user_entity_name,'X_TAXABLE_PAY', fnd_number.canonical_to_number(fai.value))) taxable_pay, max(decode(fue.user_entity_name,'X_PREVIOUS_TAX_PAID', fnd_number.canonical_to_number(fai.value))) previous_tax_paid, max(decode(fue.user_entity_name,'X_PREVIOUS_TAXABLE_PAY', fnd_number.canonical_to_number(fai.value))) previous_taxable_pay, max(decode(fue.user_entity_name,'X_ADDRESS_LINE1', substr(fai.value,1,60))) address_line1, max(decode(fue.user_entity_name,'X_ADDRESS_LINE2', substr(fai.value,1,60))) address_line2, max(decode(fue.user_entity_name,'X_ADDRESS_LINE3', substr(fai.value,1,60))) address_line3, max(decode(fue.user_entity_name,'X_TOWN_OR_CITY', substr(fai.value,1,30))) town_or_city, max(decode(fue.user_entity_name,'X_COUNTY', substr(fai.value,1,30))) county, max(decode(fue.user_entity_name,'X_POSTAL_CODE', substr(fai.value,1,20))) post_code, max(decode(fue.user_entity_name,'X_STUDENT_LOAN_FLAG', substr(fai.value,1,1))) student_loan_flag, max(decode(fue.user_entity_name,'X_TAX_CODE', substr(UPPER(fai.value),1,10))) tax_code, max(decode(fue.user_entity_name,'X_TITLE', substr(UPPER(fai.value),1,10))) title, max(decode(fue.user_entity_name,'X_WEEK_NUMBER', fnd_number.canonical_to_number(fai.value))) week_number, max(decode(fue.user_entity_name,'X_W1_M1_INDICATOR', substr(fai.value,1,1))) w1_m1_indicator, max(decode(fue.user_entity_name,'X_COUNTRY', substr(fai.value,1,30))) country, PAY_PAYGBP45_XMLP_PKG.c_format_data_formula(max ( decode ( fue.user_entity_name , 'X_TITLE' , substr ( UPPER ( fai.value ) , 1 , 10 ) ) ), max ( decode ( fue.user_entity_name , 'X_ADDRESS_LINE1' , substr ( fai.value , 1 , 60 ) ) ), max ( decode ( fue.user_entity_name , 'X_ADDRESS_LINE2' , substr ( fai.value , 1 , 60 ) ) ), max ( decode ( fue.user_entity_name , 'X_ADDRESS_LINE3' , substr ( fai.value , 1 , 60 ) ) ), max ( decode ( fue.user_entity_name , 'X_TOWN_OR_CITY' , substr ( fai.value , 1 , 30 ) ) ), max ( decode ( fue.user_entity_name , 'X_COUNTY' , substr ( fai.value , 1 , 30 ) ) ), max ( decode ( fue.user_entity_name , 'X_COUNTRY' , substr ( fai.value , 1 , 30 ) ) ), max ( decode ( fue.user_entity_name , 'X_POSTAL_CODE' , substr ( fai.value , 1 , 20 ) ) ), max ( decode ( fue.user_entity_name , 'X_TAXABLE_PAY' , fnd_number.canonical_to_number ( fai.value ) ) ), max ( decode ( fue.user_entity_name , 'X_PREVIOUS_TAXABLE_PAY' , fnd_number.canonical_to_number ( fai.value ) ) ), max ( decode ( fue.user_entity_name , 'X_TAX_PAID' , fnd_number.canonical_to_number ( fai.value ) ) ), max ( decode ( fue.user_entity_name , 'X_PREVIOUS_TAX_PAID' , fnd_number.canonical_to_number ( fai.value ) ) ), max ( decode ( fue.user_entity_name , 'X_NATIONAL_INSURANCE_NUMBER' , substr ( UPPER ( fai.value ) , 1 , 20 ) ) ), max ( decode ( fue.user_entity_name , 'X_TERMINATION_DATE' , fnd_date.canonical_to_date ( fai.value ) ) ), :C_3_PART, max ( decode ( fue.user_entity_name , 'X_W1_M1_INDICATOR' , substr ( fai.value , 1 , 1 ) ) )) C_Format_data, PAY_PAYGBP45_XMLP_PKG.C_TITLE_p C_TITLE, PAY_PAYGBP45_XMLP_PKG.C_NI12_p C_NI12, PAY_PAYGBP45_XMLP_PKG.C_NI34_p C_NI34, PAY_PAYGBP45_XMLP_PKG.C_NI56_p C_NI56, PAY_PAYGBP45_XMLP_PKG.C_NI78_p C_NI78, PAY_PAYGBP45_XMLP_PKG.C_Ni9_p C_Ni9, PAY_PAYGBP45_XMLP_PKG.C_DATE_OF_LEAVING_DD_p C_DATE_OF_LEAVING_DD, PAY_PAYGBP45_XMLP_PKG.C_DATE_OF_LEAVING_MM_p C_DATE_OF_LEAVING_MM, PAY_PAYGBP45_XMLP_PKG.C_DATE_OF_LEAVING_YYYY_p C_DATE_OF_LEAVING_YYYY, PAY_PAYGBP45_XMLP_PKG.C_TOTAL_TAX_TD_p C_TOTAL_TAX_TD, PAY_PAYGBP45_XMLP_PKG.C_TOTAL_PAY_TD_p C_TOTAL_PAY_TD, PAY_PAYGBP45_XMLP_PKG.C_PER_ADDRESS_LINE1_p C_PER_ADDRESS_LINE1, PAY_PAYGBP45_XMLP_PKG.C_PER_ADDRESS_LINE2_p C_PER_ADDRESS_LINE2, PAY_PAYGBP45_XMLP_PKG.C_PER_ADDRESS_LINE3_p C_PER_ADDRESS_LINE3, PAY_PAYGBP45_XMLP_PKG.C_PER_ADDRESS_LINE4_p C_PER_ADDRESS_LINE4, PAY_PAYGBP45_XMLP_PKG.C_PAY_IN_EMP_POUNDS_p C_PAY_IN_EMP_POUNDS, PAY_PAYGBP45_XMLP_PKG.C_PAY_IN_EMP_PENCE_p C_PAY_IN_EMP_PENCE, PAY_PAYGBP45_XMLP_PKG.C_TAX_IN_EMP_POUNDS_p C_TAX_IN_EMP_POUNDS, PAY_PAYGBP45_XMLP_PKG.C_TAX_IN_EMP_PENCE_p C_TAX_IN_EMP_PENCE, PAY_PAYGBP45_XMLP_PKG.C_PAY_TD_POUNDS_p C_PAY_TD_POUNDS, PAY_PAYGBP45_XMLP_PKG.C_PAY_TD_PENCE_p C_PAY_TD_PENCE, PAY_PAYGBP45_XMLP_PKG.C_TAX_TD_POUNDS_p C_TAX_TD_POUNDS, PAY_PAYGBP45_XMLP_PKG.C_TAX_TD_PENCE_p C_TAX_TD_PENCE FROM pay_assignment_actions act, ff_archive_items fai, ff_user_entities fue WHERE act.assignment_action_id = fai.context1 &P_ACTION_RESTRICTION AND act.action_status = 'C' AND fue.legislation_code = 'GB' AND fue.business_group_id IS NULL AND fue.user_entity_id = fai.user_entity_id GROUP BY act.assignment_action_id &P_SORT_ORDER PAY PAYRPP45_XML US PAY P45 Application: Payroll Source: P45 Report (XML) Short Name: PAYRPP45_XML DB package: PAY_PAYGBP45_XMLP_PKG BI Publisher &p_action_restriction &p_sort_order :c_3_part :c_business_group_name :c_date_of_leaving_dd :c_date_of_leaving_mm :c_date_of_leaving_yyyy :c_ers_addr_line1 :c_ers_addr_line2 :c_ers_addr_line3 :c_ers_name :c_formula_id :c_message :c_ni12 :c_ni34 :c_ni56 :c_ni78 :c_ni9 :c_pay_in_emp_pence :c_pay_in_emp_pounds :c_pay_td_pence :c_pay_td_pounds :c_per_address_line1 :c_per_address_line2 :c_per_address_line3 :c_per_address_line4 :c_report_subtitle :c_tax_dist_no :c_tax_dist_ref :c_tax_in_emp_pence :c_tax_in_emp_pounds :c_tax_td_pence :c_tax_td_pounds :c_title :c_total_pay_td :c_total_tax_td :p_action_restriction :p_assignment_action_id :p_conc_request_id :p_date_today :p_payroll_action_id :p_report_title :p_session_date :p_sort_order 1 10 :p_payroll_action_id LOV Oracle PY_GB_SRS_P45_ARCH_ACTIONS_NUM Y select a.payroll_action_id id, a.request_id value, rtrim(pay_gb_eoy_archive.get_parameter(a.legislative_parameters,'TAX_REF')||', '||hr_general.decode_payroll(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYROLL_ID')),', ') description from pay_payroll_actions a where a.report_type='P45' and a.report_qualifier='GB' and a.report_category='P45' and a.action_status='C' and a.action_type='X' and a.business_group_id+0= fnd_global.per_business_group_id order by a.effective_date desc,a.request_id desc Y US Archive Request Id 2 20 :p_assignment_action_id LOV Oracle PY_GB_SRS_P45_ARCH_ASSIGNMENTS Y select assact.assignment_action_id id, asg.assignment_number value, peo.national_identifier description from pay_assignment_actions assact,pay_payroll_actions pact,per_assignments_f asg,per_people_f peo where pact.payroll_action_id=to_number(:$flex$.py_gb_srs_p45_arch_actions_num:null) and pact.payroll_action_id=assact.payroll_action_id and assact.assignment_id=asg.assignment_id and asg.person_id=peo.person_id and asg.effective_start_date=(select max(asg2.effective_start_date) from per_assignments_f asg2 where asg2.assignment_id=assact.assignment_id and asg2.effective_start_date<=pact.effective_date) and pact.effective_date between peo.effective_start_date and peo.effective_end_date order by asg.assignment_number US Assignment Number