PAY RTI EYU Run Reconciliation

        SELECT  paa.assignment_action_id assignment_action_id
              , papf.last_name last_name
              , papf.national_identifier ni_number
              , to_char(papf.date_of_birth,'DD-Mon-YYYY') dob
              , papf.sex gender
              , pai1.action_information10 rti_payroll_id
              , paaf.assignment_number assignment_number
              , nvl (papf.per_information10, 'N') aggregated_paye
              , nvl (papf.per_information9, 'N') aggregated_ni
              , pai1.action_information1 start_date
              , pai1.action_information2 end_date
              , nvl (papf.per_information2, 'N') director_ind
              , pai2.action_information7 payment_non_indiv
			  , NVL(pai2.action_information9,'N') flexibly_accessing_pension
              , pai1.action_information8 eea_citizen
              , pai1.action_information9 epm6
              , pai1.action_information11 recently_bereaved
              , pai1.action_information12 annual_pension
              , pai1.action_information13 TAX_CODE
              , pai1.action_information14 tax_basis
              , pai1.action_information4 stud_loan_ind
              , pai1.action_information5 over_six
              , to_number (nvl (pai1.action_information15, 0) * 0.01) tax_payd
              , to_number (nvl (pai1.action_information16, 0) * 0.01) tax_ded
              , pai1.action_information7 live_abroad
              , pai1.action_information6 less_six
              , to_number (nvl (pai1.action_information28, 0) * 0.01) post_tax_pension
              , to_number (nvl (pai1.action_information26, 0) * 0.01) pre_tax_pension
              , to_number (nvl (pai2.action_information8, 0) * 0.01) benefits_taxed
              , to_number (nvl (pai1.action_information17, 0) * 0.01) stud_loan_repayment
              , to_number (nvl (pai1.action_information18, 0) * 0.01) ssp
              , to_number (nvl (pai1.action_information19, 0) * 0.01) smp
              , to_number (nvl (pai1.action_information20, 0) * 0.01) ospp
              , to_number (nvl (pai1.action_information21, 0) * 0.01) sap
              , to_number (nvl (pai1.action_information22, 0) * 0.01) aspp
              , trunc (nvl (pai2.action_information2, 0)  * 0.01) trivial_amount_a
              , trunc (nvl (pai2.action_information4, 0)  * 0.01)  trivial_amount_b
              , trunc (nvl (pai2.action_information6, 0)  * 0.01)  trivial_amount_c
			  , NVL(pai2.action_information10,'N') PENSION_DEATH_BENEFIT
			  , (nvl (pai2.ACTION_INFORMATION11, 0)  * 0.01) FLEXIBLE_DRAWDOWN_TAXABLE
			  , (nvl (pai2.ACTION_INFORMATION12, 0)  * 0.01) FLEXIBLE_DRAWDOWN_NONTAXABLE	
        FROM    pay_assignment_actions paa
              , pay_action_information pai1
              , pay_action_information pai2
              , 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_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     papf.person_id = paaf.person_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 <= :DATE_RUN
                )
        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
                )
        ORDER BY last_name, rti_payroll_id, assignment_number