ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4716051A9E05362FB09050512Y select pay_gb_rti_p60.get_column_value(:P_SORT_ORDER1,emp.action_information19) SORT_ORDER1 , pay_gb_rti_p60.get_column_value(:P_SORT_ORDER2,emp.action_information19) SORT_ORDER2 , pay_gb_rti_p60.get_column_value(:P_SORT_ORDER3,emp.action_information19) SORT_ORDER3 , pay_gb_rti_p60.get_column_value(:P_SORT_ORDER4,emp.action_information19) SORT_ORDER4 , pay_gb_rti_p60.get_column_value(:P_SORT_ORDER5,emp.action_information19) SORT_ORDER5 , pay_gb_rti_p60.get_column_value(:P_SORT_ORDER6,emp.action_information19) SORT_ORDER6 , decode(:P_SORT_ORDER7,'ASSIGNMENT_NUMBER',asg.action_information17,emp.action_information3) SORT_ORDER7 , asg.action_context_id ASSIGNMENT_ACTION_ID, header.action_information1 TAX_DIST_REF , header.action_information2 TAX_DIST_NAME , header.action_information3 EMPLOYER_NAME , header.action_information4 EMPLOYER_ADDRESS, substr(emp.action_information1,1,2) NI_1, substr(emp.action_information1,3,2) NI_2, substr(emp.action_information1,5,2) NI_3, substr(emp.action_information1,7,2) NI_4, substr(emp.action_information1,9,2) NI_5, emp.action_information2 EMP_NUMBER, upper(emp.action_information3) LAST_NAME, emp.action_information4 NAME_AND_INITIALS, emp.action_information5 FORENAMES, pay_gb_rti_p60.remove_null_lines(emp.action_information6 ,:PAYROLL_ACTION_ID, asg.action_context_id, 1) l_ADDRESS1, pay_gb_rti_p60.remove_null_lines(emp.action_information7 ,:PAYROLL_ACTION_ID, asg.action_context_id, 2) l_ADDRESS2, pay_gb_rti_p60.remove_null_lines(emp.action_information8 ,:PAYROLL_ACTION_ID, asg.action_context_id, 3) l_ADDRESS3, pay_gb_rti_p60.remove_null_lines(emp.action_information9 ,:PAYROLL_ACTION_ID, asg.action_context_id, 4) l_town, pay_gb_rti_p60.remove_null_lines(emp.action_information11 ,:PAYROLL_ACTION_ID, asg.action_context_id, 5) l_REGION1, pay_gb_rti_p60.remove_null_lines(emp.action_information12 ,:PAYROLL_ACTION_ID, asg.action_context_id, 6) l_REGION2, '' l_region3, emp.action_information13 DOB_DD, emp.action_information14 DOB_MM, emp.action_information15 DOB_YY, emp.action_information16 SEX, emp.action_information17 TAX_YEAR, nvl(emp.action_information18,'O') EXPENSE_CHECK, emp.action_information19 PEOPLE_GRP_ID, emp.action_information20 C_BUSINESS_GROUP_ID, asg.action_information1 CURRENT_PAY, asg.action_information2 CURRENT_TAX, trunc(nvl(asg.action_information3,0)) STUDENT_LOANS, trunc(nvl(asg.action_information19,0)) POSTGRADUATE_LOANS, asg.action_information4 REFUND_FLAG, asg.action_information5 TAX_CODE, asg.action_information6 W1_M1_INDICATOR, nvl(asg.action_information7,'0.00') WAO_AMOUNT, asg.action_information8 TOTAL_PAY, asg.action_information9 TOTAL_TAX_DEDUCTED, asg.action_information10 PREVIOUS_PAY, asg.action_information11 PREVIOUS_TAX_PAID, asg.action_information12 SMP, asg.action_information13 SAP, asg.action_information14 SPP, asg.action_information15 ASPP, decode(asg.action_information16,'3','X','4','X','6','X',' ') WEEK_53_INDICATOR, asg.action_information17 EMPLOYEE_NUMBER, SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'RETURN_TYPE'),1,20) RETURN_TYPE from pay_action_information asg, pay_action_information emp, pay_action_information header, pay_assignment_actions paa, per_all_assignments_f paaf, pay_payroll_actions ppa where paa.payroll_action_id = :PAYROLL_ACTION_ID and ppa.payroll_action_id = paa.payroll_action_id and paa.assignment_id = paaf.assignment_id and paaf.effective_start_date = (select max(effective_start_date) from per_all_assignments_f where assignment_id = paaf.assignment_id and effective_start_date <= ppa.effective_date) and ( :P_ASSIGNMENT_SET_ID IS NULL OR EXISTS (SELECT 1 FROM hr_assignment_sets has1 WHERE has1.assignment_set_id = :P_ASSIGNMENT_SET_ID AND has1.business_group_id = paaf.business_group_id AND nvl(has1.payroll_id, paaf.payroll_id) = paaf.payroll_id AND (NOT EXISTS (SELECT 1 -- chk no amendmts FROM hr_assignment_set_amendments hasa1 WHERE hasa1.assignment_set_id = has1.assignment_set_id) OR EXISTS (SELECT 1 -- chk include amendmts FROM hr_assignment_set_amendments hasa2 WHERE hasa2.assignment_set_id = has1.assignment_set_id AND (hasa2.assignment_id = paaf.assignment_id OR hasa2.assignment_id in (select p1.assignment_id from per_all_assignments_f p1,per_all_people_f p2 where p1.person_id = paaf.person_id and p2.person_id = p1.person_id and p2.PER_INFORMATION9 = 'Y' and p2.PER_INFORMATION10 ='Y' and ppa.effective_date between p1.effective_start_date and p1.effective_end_date and ppa.effective_date between p2.effective_start_date and p2.effective_end_date )) AND nvl(hasa2.include_or_exclude,'I') = 'I' ) OR (NOT EXISTS (SELECT 1 --chk no exlude amendmts FROM hr_assignment_set_amendments hasa3 WHERE hasa3.assignment_set_id = has1.assignment_set_id AND (hasa3.assignment_id = paaf.assignment_id OR hasa3.assignment_id in (select p1.assignment_id from per_all_assignments_f p1,per_all_people_f p2 where p1.person_id = paaf.person_id and p2.person_id = p1.person_id and p2.PER_INFORMATION9 = 'Y' and p2.PER_INFORMATION10 ='Y' and ppa.effective_date between p1.effective_start_date and p1.effective_end_date and ppa.effective_date between p2.effective_start_date and p2.effective_end_date )) AND nvl(hasa3.include_or_exclude,'I') = 'E') AND NOT EXISTS (SELECT 1 --and chk no Inc amendmts FROM hr_assignment_set_amendments hasa4 WHERE hasa4.assignment_set_id = has1.assignment_set_id AND nvl(hasa4.include_or_exclude,'I') = 'I') ) -- end checking exclude amendmts ) -- done checking amendments ) -- done asg set check when not null ) -- end of asg set check -- and emp.action_context_id = paa.assignment_action_id and emp.action_context_type = 'AAP' and emp.action_information_category = 'GB RTI P60 EMP DET' and asg.action_context_id = paa.assignment_action_id and asg.action_context_type = 'AAP' and asg.action_information_category = 'GB RTI P60 ASG DET' and (asg.action_information20 is null or (asg.action_information20 = 'Y' and asg.action_information21 = 'Y')) and header.action_context_id = :PAYROLL_ACTION_ID and header.action_context_type = 'PA' and header.action_information_category = 'GB RTI P60 PAYROLL DET' and (exists( select null from per_assignment_extra_info where nvl(AEI_INFORMATION1,'D') ='P' and AEI_INFORMATION_CATEGORY='GB_P60_OUTPUT_OPTION' and assignment_id = paa.assignment_id union select 1 from hr_organization_information hoi where hoi.org_information1 =(select replace(header.action_information1,' ','/') from dual) AND hoi.organization_id = emp.ACTION_INFORMATION20 AND hoi.org_information_context = 'Tax Details References' AND nvl (hoi.org_information14,'P') = 'P' AND not exists ( select 1 from per_assignment_extra_info where nvl(AEI_INFORMATION1,'D')='E' and AEI_INFORMATION_CATEGORY='GB_P60_OUTPUT_OPTION' and assignment_id = paa.assignment_id ) ) or (nvl(:P_RESTRICT_OUT,'No') = 'No')) ORDER BY 1,2,3,4,5,6,7 --18418642 PAY PYGBRTIP60OP US PAY RTI - Year End P60 Report Process Imported from BI Publisher Application: Payroll Source: RTI - Year End P60 Report Process Short Name: PYGBRTIP60OP DB package: BI Publisher :p_assignment_set_id :p_restrict_out :p_sort_order1 :p_sort_order2 :p_sort_order3 :p_sort_order4 :p_sort_order5 :p_sort_order6 :p_sort_order7 :payroll_action_id 1 -50 :p_align LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N US Alignment Only Alignment Only 2 -40 :p_valid_template_list Char PYGBRTIP60OP US P_VALID_TEMPLATE_LIST 3 -30 :p_appl_short_name Char PAY US P_APPL_SHORT_NAME 4 -20 :p_template_name LOV Oracle GB_P60P_XDO_TEMPLATE Y select xt.template_code id, xt.template_name value, xt.description description from xdo_templates_vl xt,xdo_ds_definitions_b xds where xt.ds_app_short_name=xds.application_short_name and xt.data_source_code=xds.data_source_code and xt.ds_app_short_name=:$flex$.p_appl_short_name and xt.data_source_code=:$flex$.p_valid_template_list and sysdate between xt.start_date and nvl(xt.end_date,sysdate) order by xt.template_name P60 Report Type LE (PDF) US P_TEMPLATE_NAME 5 -10 :xdo_doc_repeat_property Char G_HEADER Y US XDO_DOC_REPEAT_PROPERTY 6 10 :payroll_action_id LOV Oracle PY_GB_RTI_P60_ARCH_ACTS Y select payroll_action_id id, fnd_date.date_to_displaydate(effective_date)||' ('||request_id||')' value, null description from pay_payroll_actions a where a.report_type='RTI_P60_REP' and a.report_qualifier='GB' and a.action_status='C' and a.action_type='X' and a.business_group_id= fnd_global.per_business_group_id order by a.effective_date desc, a.creation_date desc Y US Archive End Date Archive End Date 7 20 :p_restrict_out LOV Oracle HR_GB_YES_NO Y select meaning id, meaning value, null description from hr_lookups hrl where hrl.lookup_type='YES_NO' and hrl.enabled_flag='Y' order by hrl.meaning US Restrict By Output Option Restrict By Output Option 8 30 :p_assignment_set_id LOV Oracle PAY_GB_ASG_SET Y select assignment_set_id id, assignment_set_name value, null description from hr_assignment_sets a where business_group_id=fnd_global.per_business_group_id and not exists (select*from hr_assignment_set_criteria b where b.assignment_set_id=a.assignment_set_id) order by assignment_set_name US Assignment Set Assignment Set 9 40 :p_sort_order1 LOV Oracle PY_PEOPLE_GROUP_SORT Y select application_column_name id, segment_name value, null description from fnd_id_flex_segments where application_id between 801 and 899 and id_flex_code='GRP' and id_flex_num=(select people_group_structure from per_business_groups where business_group_id=fnd_global.per_business_group_id) order by value,description US Sort Order 1 Sort Order 1 10 50 :p_sort_order2 LOV Oracle PY_PEOPLE_GROUP_SORT Y select application_column_name id, segment_name value, null description from fnd_id_flex_segments where application_id between 801 and 899 and id_flex_code='GRP' and id_flex_num=(select people_group_structure from per_business_groups where business_group_id=fnd_global.per_business_group_id) order by value,description US Sort Order 2 Sort Order 2 11 60 :p_sort_order3 LOV Oracle PY_PEOPLE_GROUP_SORT Y select application_column_name id, segment_name value, null description from fnd_id_flex_segments where application_id between 801 and 899 and id_flex_code='GRP' and id_flex_num=(select people_group_structure from per_business_groups where business_group_id=fnd_global.per_business_group_id) order by value,description US Sort Order 3 Sort Order 3 12 70 :p_sort_order4 LOV Oracle PY_PEOPLE_GROUP_SORT Y select application_column_name id, segment_name value, null description from fnd_id_flex_segments where application_id between 801 and 899 and id_flex_code='GRP' and id_flex_num=(select people_group_structure from per_business_groups where business_group_id=fnd_global.per_business_group_id) order by value,description US Sort Order 4 Sort Order 4 13 80 :p_sort_order5 LOV Oracle PY_PEOPLE_GROUP_SORT Y select application_column_name id, segment_name value, null description from fnd_id_flex_segments where application_id between 801 and 899 and id_flex_code='GRP' and id_flex_num=(select people_group_structure from per_business_groups where business_group_id=fnd_global.per_business_group_id) order by value,description US Sort Order 5 Sort Order 5 14 90 :p_sort_order6 LOV Oracle PY_PEOPLE_GROUP_SORT Y select application_column_name id, segment_name value, null description from fnd_id_flex_segments where application_id between 801 and 899 and id_flex_code='GRP' and id_flex_num=(select people_group_structure from per_business_groups where business_group_id=fnd_global.per_business_group_id) order by value,description US Sort Order 6 Sort Order 6 15 100 :p_sort_order7 LOV Oracle PY_SRS_PAY_ADVICE_LAST_SORT Y select h.lookup_code id, h.meaning value, null description from hr_lookups h where h.lookup_type='PY_SRS_PAY_ADVICE_LAST_SORT' order by value,description ASSIGNMENT_NUMBER Y US Sort Order 7 Sort Order 7 16 110 :debugflag Char N US DebugFlag DebugFlag :$flex$.p_appl_short_name P_APPL_SHORT_NAME P_TEMPLATE_NAME :$flex$.p_valid_template_list P_VALID_TEMPLATE_LIST P_TEMPLATE_NAME