PAY RTI - Year End P60 Report Process

		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