PAY RTI FPS Run Reconciliation

Description
Categories: BI Publisher, Human Resources
Application: Payroll
Source: RTI FPS Run Reconciliation Report
Short Name: PYGBFPSREC
DB package:
select
            decode(NVL(PAPF.PER_INFORMATION10,'N'),'Y','P'||PAPF.PERSON_ID,'A'||PAAF.assignment_ID) group_id,					
            max(decode(pai3.ACTION_INFORMATION3,NULL,0,paa.ASSIGNMENT_ACTION_ID)) ASSIGNMENT_ACTION_ID,	
            NVL(max(PAPF.PER_INFORMATION10),'N') AGGREGATED_PAYE,
            NVL(max(PAPF.PER_INFORMATION9),'N') AGGREGATED_NI,
						NVL(max(PAPF.PER_INFORMATION2),'N') DIRECTOR_IND,
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,PAPF.PERSON_ID)),
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,PAAF.assignment_ID)),
						max(pai3.ACTION_INFORMATION3) LAST_N,
						max(pai3.ACTION_INFORMATION7) NINO,
						max(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(pai3.ACTION_INFORMATION8),'DD-MON-YYYY')) DOB,
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION13)) PAYROLL_ID,
						max(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION1)),'DD-MON-YYYY')) START_DATE,
						max(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION2)),'DD-MON-YYYY')) END_DATE,		
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION23)) HOURS_WORKED,	
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION16)) IRREG_PAY, 
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION20)) PERIODS_COVERED,	
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai2.ACTION_INFORMATION25)) TAX_CODE,	
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,decode(nvl(pai2.ACTION_INFORMATION27,0),'Y','Non Cumulative','Cumulative'))) TAX_BASIS,
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,NVL(pai1.ACTION_INFORMATION18, nvl(pai1.ACTION_INFORMATION19,0)))) TAX_PERIOD,
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,TO_CHAR(FND_DATE.CANONICAL_TO_DATE(pai1.ACTION_INFORMATION3),'DD-MON-YYYY'))) PAYMENT_DATE,
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION6,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION6,0))*0.01, 0.01)))	TAXABLE_PAY_PTD,										
						sum(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai2.ACTION_INFORMATION1,0))*0.01)) TAXABLE_PAY_YTD,
						sum(to_number(nvl(pai2.ACTION_INFORMATION17,0))*0.01) TAX_PAID_PTD,						
						sum(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai2.ACTION_INFORMATION2,0))*0.01)) TAX_PAID_YTD,
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION10,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION10,0))*0.01, 0.01))) BEN_TAXED_PTD,					
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION11,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION11,0))*0.01, 0.01))) BEN_PROCESSED_PTD,
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION12,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION12,0))*0.01, 0.01))) NIC_FREE_BEN_YTD,				
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION8,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION8,0))*0.01, 0.01))) NET_PAY_DEDN_PTD,																
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION16,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION16,0))*0.01, 1))) 
						STUDENT_LOAN_PTD	,																				 						
						sum(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION3,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION3,0))*0.01, 1)))) STUDENT_LOAN_YTD,																				 						
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION13,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION13,0))*0.01, 0.01))) PRETAX_PEN_PTD,																
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION15,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION15,0))*0.01, 0.01))) POSTTAX_PEN_PTD,							 
						sum(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION18,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION18,0))*0.01, 0.01)))) SSP ,																
						sum(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION19,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION19,0))*0.01, 0.01)))) SMP ,																				 
						sum(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION20,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION20,0))*0.01, 0.01)))) OSPP,																
						sum(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION21,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION21,0))*0.01, 0.01)))) SAP,																				 
						sum(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION22,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION22,0))*0.01, 0.01)))) ASPP_SHPP ,
						max(paaf.assignment_number) ASSIGNMENT_NUMBER,
						max(papf.employee_number) EMPLOYEE_NUMBER,
						max(papf.full_name) FULL_NAME,
						NULL PAYMENT_NON_INDIV,
						NULL ON_STRIKE,
						NULL UNPAID_ABSENCE,
						0 TRIVIAL_AMOUNT_A,
						0 TRIVIAL_AMOUNT_B,
						0 TRIVIAL_AMOUNT_C,
						0 FLEXIBLE_TAXABLE_PAY,
						0 FLEXIBLE_NON_TAXABLE_PAY,
						max(NVL(pai3.ACTION_INFORMATION13, 'N')) FLEXIBLY_ACCESSING_PENSION,
						max(NVL(pai3.ACTION_INFORMATION15, 'N')) PENSION_DEATH_BENEFIT,
						max(NVL(pai3.ACTION_INFORMATION18, 'N')) SERIOUS_ILL_HEALTH_INDICATOR,
						max(pai3.ACTION_INFORMATION19) STUD_LOAN_PLAN_TYPE
						from 
						pay_assignment_actions paa,
						pay_action_information pai1,
						pay_action_information pai2,
						pay_action_information pai3,
                        PER_ALL_ASSIGNMENTS_F PAAF,
                        PER_ALL_PEOPLE_F PAPF
						where 
						paa.payroll_Action_id = :PAYROLL_ACTION_ID
						and pai1.action_context_id = paa.assignment_Action_id
						and pai1.ACTION_CONTEXT_TYPE = 'AAP'
						and pai1.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET1'
						and pai2.action_context_id = paa.assignment_Action_id
						and pai2.ACTION_CONTEXT_TYPE = 'AAP'
						and pai2.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET2'
						and pai2.assignment_id = pai1.assignment_id
						and pai3.action_context_id(+) = paa.assignment_Action_id
						and pai3.ACTION_CONTEXT_TYPE(+) = 'AAP'
						and pai3.ACTION_INFORMATION_CATEGORY(+) = 'GB RTI EMPLOYEE DETAILS'
                        AND PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
                        AND PAPF.PERSON_ID = PAAF.PERSON_ID
                        AND PAPF.EFFECTIVE_START_DATE = 
						(SELECT MAX(EFFECTIVE_START_DATE) 
						   FROM PER_ALL_PEOPLE_F 
						  WHERE PERSON_ID = PAPF.PERSON_ID
						  AND EFFECTIVE_START_DATE <= :DATE_RUN)
                        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 <= :DATE_RUN)
						AND :REP_TYPE = 'RTI_FPS_REP'
group by decode(NVL(PAPF.PER_INFORMATION10,'N'),'Y','P'||PAPF.PERSON_ID,'A'||PAAF.assignment_ID)
union
select
             decode(NVL(PAPF.PER_INFORMATION10,'N'),'Y','P'||PAPF.PERSON_ID,'A'||PAAF.assignment_ID) group_id,					
            max(decode(pai3.ACTION_INFORMATION3,NULL,0,paa.ASSIGNMENT_ACTION_ID)) ASSIGNMENT_ACTION_ID,	
            NVL(max(PAPF.PER_INFORMATION10),'N') AGGREGATED_PAYE,
            NVL(max(PAPF.PER_INFORMATION9),'N') AGGREGATED_NI,
						NVL(max(PAPF.PER_INFORMATION2),'N') DIRECTOR_IND,
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,PAPF.PERSON_ID)),
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,PAAF.assignment_ID)),
						max(pai3.ACTION_INFORMATION3) LAST_N,
						max(pai3.ACTION_INFORMATION7) NINO,
						max(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(pai3.ACTION_INFORMATION8),'DD-MON-YYYY')) DOB,
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION13)) PAYROLL_ID,
						max(((decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION1)))) START_DATE,
						max(((decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION2)))) END_DATE,		
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION23)) HOURS_WORKED,	
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION16)) IRREG_PAY, 
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION20)) PERIODS_COVERED,	
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai2.ACTION_INFORMATION25)) TAX_CODE,	
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,decode(nvl(pai2.ACTION_INFORMATION27,0),'Y','Non Cumulative','Cumulative'))) TAX_BASIS,
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,NVL(pai1.ACTION_INFORMATION18, nvl(pai1.ACTION_INFORMATION19,0)))) TAX_PERIOD,
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION3)) PAYMENT_DATE,
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION6,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION6,0))*0.01, 0.01)))	TAXABLE_PAY_PTD,										
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai2.ACTION_INFORMATION1,0))*0.01)) TAXABLE_PAY_YTD,
						sum(to_number(nvl(pai2.ACTION_INFORMATION17,0))*0.01) TAX_PAID_PTD,						
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai2.ACTION_INFORMATION2,0))*0.01)) TAX_PAID_YTD,
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION10,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION10,0))*0.01, 0.01))) BEN_TAXED_PTD,					
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION11,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION11,0))*0.01, 0.01))) BEN_PROCESSED_PTD,
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION12,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION12,0))*0.01, 0.01))) NIC_FREE_BEN_YTD	,				
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION8,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION8,0))*0.01, 0.01))) NET_PAY_DEDN_PTD	,																
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION16,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION16,0))*0.01, 1))) STUDENT_LOAN_PTD,																				 						
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION3,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION3,0))*0.01, 1)))) STUDENT_LOAN_YTD,																				 						
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION13,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION13,0))*0.01, 0.01))) PRETAX_PEN_PTD	,																
						sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION15,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION15,0))*0.01, 0.01))) POSTTAX_PEN_PTD	,																				 
						decode(:REP_TYPE,'RTI_FPS_REP_13',max(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION18,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION18,0))*0.01, 0.01)))),0) SSP	,																
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION19,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION19,0))*0.01, 0.01)))) SMP,																				 
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION20,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION20,0))*0.01, 0.01)))) OSPP	,																
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION21,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION21,0))*0.01, 0.01)))) SAP,																				 
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION22,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION22,0))*0.01, 0.01)))) ASPP_SHPP ,
						max(paaf.assignment_number) ASSIGNMENT_NUMBER,
						max(papf.employee_number) EMPLOYEE_NUMBER,
						max(papf.full_name) FULL_NAME,
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai4.ACTION_INFORMATION7)) PAYMENT_NON_INDIV,
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai4.ACTION_INFORMATION8)) ON_STRIKE,
						max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai4.ACTION_INFORMATION9)) UNPAID_ABSENCE,
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai4.ACTION_INFORMATION2,0))*0.01)) TRIVIAL_AMOUNT_A,
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai4.ACTION_INFORMATION4,0))*0.01)) TRIVIAL_AMOUNT_B,
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai4.ACTION_INFORMATION6,0))*0.01)) TRIVIAL_AMOUNT_C,
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai4.ACTION_INFORMATION16,0))*0.01)) FLEXIBLE_TAXABLE_PAY,
						max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai4.ACTION_INFORMATION17,0))*0.01)) FLEXIBLE_NON_TAXABLE_PAY,
						max(NVL(pai4.ACTION_INFORMATION13, 'N')) FLEXIBLY_ACCESSING_PENSION,
						max(NVL(pai4.ACTION_INFORMATION15, 'N')) PENSION_DEATH_BENEFIT,
						max(NVL(pai4.ACTION_INFORMATION18, 'N')) SERIOUS_ILL_HEALTH_INDICATOR,
						max(pai4.ACTION_INFORMATION19) STUD_LOAN_PLAN_TYPE
						from 
						pay_assignment_actions paa,
						(
select 			
            max(pai11.action_context_id) action_context_id,
						max(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(pai11.ACTION_INFORMATION1),'DD-MON-YYYY')) ACTION_INFORMATION1,
						max(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(pai11.ACTION_INFORMATION2),'DD-MON-YYYY')) ACTION_INFORMATION2,
						to_char(max(FND_DATE.CANONICAL_TO_DATE(pai11.ACTION_INFORMATION3)),'DD-MON-YYYY') ACTION_INFORMATION3,																					 						
						max(pai11.ACTION_INFORMATION13) ACTION_INFORMATION13,
						max(pai11.ACTION_INFORMATION16) ACTION_INFORMATION16,
						max(pai11.ACTION_INFORMATION18) ACTION_INFORMATION18,																
						max(pai11.ACTION_INFORMATION19) ACTION_INFORMATION19,																				 
						max(pai11.ACTION_INFORMATION20) ACTION_INFORMATION20,																
						max(pai11.ACTION_INFORMATION23) ACTION_INFORMATION23
from 
pay_assignment_actions paa1,
pay_action_information pai11
where 
    paa1.payroll_action_id =  :PAYROLL_ACTION_ID
            and	pai11.action_context_id = paa1.assignment_Action_id
						and pai11.ACTION_CONTEXT_TYPE = 'AAP'
						and pai11.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET1'
group by decode(pai11.action_INFORMATION29,null,pai11.action_information_id,pai11.assignment_id)
) pai1,
(
select 			max(pai22.action_context_id) action_context_id,
            max(pai22.ACTION_INFORMATION25) ACTION_INFORMATION25,
						max(pai22.ACTION_INFORMATION27) ACTION_INFORMATION27,
						sum(nvl(pai22.ACTION_INFORMATION6,0))	ACTION_INFORMATION6,										
						max(to_number(nvl(pai22.ACTION_INFORMATION1,0))) ACTION_INFORMATION1,
						sum(to_number(nvl(pai22.ACTION_INFORMATION17,0))) ACTION_INFORMATION17,						
						max(to_number(nvl(pai22.ACTION_INFORMATION2,0))) ACTION_INFORMATION2,
						sum(to_number(nvl(pai22.ACTION_INFORMATION10,0))) ACTION_INFORMATION10,					
						sum(to_number(nvl(pai22.ACTION_INFORMATION11,0))) ACTION_INFORMATION11,
						sum(to_number(nvl(pai22.ACTION_INFORMATION12,0))) ACTION_INFORMATION12	,
						sum(to_number(nvl(pai22.ACTION_INFORMATION8,0))) ACTION_INFORMATION8,																
						sum(to_number(nvl(pai22.ACTION_INFORMATION16,0))) ACTION_INFORMATION16,																				 						
						max(to_number(nvl(pai22.ACTION_INFORMATION3,0))) ACTION_INFORMATION3,																				 						
						sum(to_number(nvl(pai22.ACTION_INFORMATION13,0))) ACTION_INFORMATION13,																
						sum(to_number(nvl(pai22.ACTION_INFORMATION15,0))) ACTION_INFORMATION15	,																				 
						max(to_number(nvl(pai22.ACTION_INFORMATION18,0))) ACTION_INFORMATION18,																
						max(to_number(nvl(pai22.ACTION_INFORMATION19,0))) ACTION_INFORMATION19,																				 
						max(to_number(nvl(pai22.ACTION_INFORMATION20,0))) ACTION_INFORMATION20	,																
						max(to_number(nvl(pai22.ACTION_INFORMATION21,0))) ACTION_INFORMATION21,																				 
						max(to_number(nvl(pai22.ACTION_INFORMATION22,0))) ACTION_INFORMATION22
from 
pay_assignment_actions paa2,
pay_action_information pai22
where 
    paa2.payroll_action_id =  :PAYROLL_ACTION_ID
            and	pai22.action_context_id = paa2.assignment_Action_id
						and pai22.ACTION_CONTEXT_TYPE = 'AAP'
						and pai22.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET2'
group by decode(pai22.action_INFORMATION29,null,pai22.action_information_id,pai22.assignment_id)
) pai2,
						pay_action_information pai3,
						pay_action_information pai4,
                        PER_ALL_ASSIGNMENTS_F PAAF,
                        PER_ALL_PEOPLE_F PAPF
						where 
						paa.payroll_Action_id = :PAYROLL_ACTION_ID
						and pai1.action_context_id = paa.assignment_Action_id
						and pai2.action_context_id = paa.assignment_Action_id
						and pai4.action_context_id(+) = paa.assignment_Action_id	
						and pai4.ACTION_CONTEXT_TYPE(+) = 'AAP'
						and pai4.ACTION_INFORMATION_CATEGORY(+) = 'GB_RTI_FPS_ASG_DET3'
						and pai3.action_context_id(+) = paa.assignment_Action_id
						and pai3.ACTION_CONTEXT_TYPE(+) = 'AAP'
						and pai3.ACTION_INFORMATION_CATEGORY(+) = 'GB RTI EMPLOYEE DETAILS'
                        AND PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
                        AND PAPF.PERSON_ID = PAAF.PERSON_ID
                        AND PAPF.EFFECTIVE_START_DATE = 
						(SELECT MAX(EFFECTIVE_START_DATE) 
						   FROM PER_ALL_PEOPLE_F 
						  WHERE PERSON_ID = PAPF.PERSON_ID
						  AND EFFECTIVE_START_DATE <= :DATE_RUN) 
                        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 <= :DATE_RUN) 
group by decode(NVL(PAPF.PER_INFORMATION10,'N'),'Y','P'||PAPF.PERSON_ID,'A'||PAAF.assignment_ID)
order by LAST_N, PAYROLL_ID, ASSIGNMENT_NUMBER
Parameter Name SQL text Validation
Archive Request ID
 
LOV Oracle