PAY RTI - Year End P60 Report Process (Multi Threaded)
Description
Categories: BI Publisher
Imported from BI Publisher
Description: RTI - Year End P60 Report Process
Application: Payroll
Source: RTI - Year End P60 Report Process (Multi Threaded)
Short Name: PYGBP60MULTI
DB package:
Description: RTI - Year End P60 Report Process
Application: Payroll
Source: RTI - Year End P60 Report Process (Multi Threaded)
Short Name: PYGBP60MULTI
DB package:
Run
PAY RTI - Year End P60 Report Process (Multi Threaded) and other Oracle EBS reports with Blitz Report™ on our demo environment
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, 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, asg.action_information16 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, pay_temp_object_actions tmp 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 tmp.CHUNK_NUMBER = :CHUNK_NUM and tmp.PAYROLL_ACTION_ID = ppa.payroll_action_id and tmp.OBJECT_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 paaf.person_id between :START_PERSON_ID and :END_PERSON_ID 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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Archive End Date |
|
LOV Oracle | |
Restrict By Output Option |
|
LOV Oracle | |
Assignment Set |
|
LOV Oracle | |
Sort Order 1 |
|
LOV Oracle | |
Sort Order 2 |
|
LOV Oracle | |
Sort Order 3 |
|
LOV Oracle | |
Sort Order 4 |
|
LOV Oracle | |
Sort Order 5 |
|
LOV Oracle | |
Sort Order 6 |
|
LOV Oracle | |
Sort Order 7 |
|
LOV Oracle | |
DebugFlag |
|
Char |