PAY RTI - Earlier Year Update Output 2016/17

                        select
                        paa.ASSIGNMENT_ACTION_ID ASG_ACTION_ID,
                        :GENDER GENDER,
                        pai1.ACTION_INFORMATION1 S_DATE,
                        pai1.ACTION_INFORMATION2 E_DATE,
                        pai1.ACTION_INFORMATION3 STARTER_DEC,
                        pai1.ACTION_INFORMATION4 STUD_LOAN_IND,
                        pai1.ACTION_INFORMATION5 OVER_SIX,
                        pai1.ACTION_INFORMATION6 LESS_SIX,
                        pai1.ACTION_INFORMATION7 LIV_ABR,
                        pai1.ACTION_INFORMATION8 EEA_CWC,
                        pai1.ACTION_INFORMATION9 EPM6,
                        pai1.ACTION_INFORMATION10 PAYID,
                        pai1.ACTION_INFORMATION11 OCC_IND,
                        pai2.ACTION_INFORMATION7 PAY_NONIND,
						pai2.ACTION_INFORMATION9 FA_PENSION,
                        pai1.ACTION_INFORMATION12 ANNUAL_OCC,
                        pai1.ACTION_INFORMATION15 TAX_PAYD,
                        pai1.ACTION_INFORMATION17 STUD_LOAN_REC,
                        pai1.ACTION_INFORMATION16 TAX_DED,
                        pai1.ACTION_INFORMATION19 SMP,
                        pai1.ACTION_INFORMATION20 OSPP,
                        pai1.ACTION_INFORMATION21 SAP,
                        pai1.ACTION_INFORMATION22 SHPP,
                        pai1.ACTION_INFORMATION26 EMP_PEN_CONTRIB,
                        pai1.ACTION_INFORMATION28 EMP_CONTR_NPAID,
                        pai2.ACTION_INFORMATION8 BENEFIT_TAXED,
                        (pai2.ACTION_INFORMATION1) TRIV_COMM_PAYMENT_TYPE_A,
                        trunc(pai2.ACTION_INFORMATION2/100)*100 TRIV_COMM_PAYMENT_AMOUNT_A,
                        (pai2.ACTION_INFORMATION3) TRIV_COMM_PAYMENT_TYPE_B,
                        trunc(pai2.ACTION_INFORMATION4/100)*100 TRIV_COMM_PAYMENT_AMOUNT_B,
                        (pai2.ACTION_INFORMATION5) TRIV_COMM_PAYMENT_TYPE_C,
                        trunc(pai2.ACTION_INFORMATION6/100)*100 TRIV_COMM_PAYMENT_AMOUNT_C,
						pai1.ACTION_INFORMATION13 TAX_CODE,
						decode(upper(substr(pai1.ACTION_INFORMATION13,1,1)),'S','S','X') TAX_REGIME,						
                        substr(pai1.ACTION_INFORMATION13,2) TAX_CODE_SCOT,
                        pai1.ACTION_INFORMATION14 W1M1_IND,			
						pai2.ACTION_INFORMATION10 PENSION_DEATH_BENEFIT,
						(pai2.ACTION_INFORMATION11) FLEXIBLE_DRAWDOWN_TAXABLE,
						(pai2.ACTION_INFORMATION12) FLEXIBLE_DRAWDOWN_NONTAXABLE		
                        from
                        pay_assignment_actions paa,
                        pay_action_information pai1,
                        pay_action_information pai2,
                        per_all_Assignments_f paaf
                        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_EYU_ASG_DET'
                        and pai2.action_context_id(+) = paa.assignment_Action_id
                        and pai2.ACTION_CONTEXT_TYPE(+) = 'AAP'
                        and pai2.ACTION_INFORMATION_CATEGORY(+) = 'GB_RTI_EYU_ASG_DET1'
                        and pai1.assignment_id = pai2.assignment_id
                        and paaf.assignment_id  = paa.assignment_id
                        and paaf.person_id = :PERSON_ID
                        and paaf.effective_start_date =
                       (select max(effective_start_date) from per_all_assignments_f where assignment_id = paaf.assignment_id)
                        order by 1