PAY IE Payroll Submission Reconciliation

SELECT  paaf.person_id person_id
       ,paaf.assignment_number assignment_id
       ,pai.action_information1 ppsn
       ,pai.action_information2 first_name
       ,pai.action_information3 surname
       ,pai.action_information4
        || nvl2(pai.action_information5,' ','')
        || pai.action_information5
	|| nvl2(pai.action_information6,' ','') 
        || pai.action_information6
        || nvl2(pai.action_information13,' ','')
        || upper (pai.action_information13)
        || nvl2(pai.action_information12,' ','')
        || pai.action_information12
        || nvl2(pai.action_information14,' ','')
        || pai.action_information14 address_line
       ,pai.action_information8 emplyoment_id
       ,fnd_date.date_to_displaydt (fnd_date.canonical_to_date(pai.action_information7)) date_of_birth
       ,fnd_date.date_to_displaydt (fnd_date.canonical_to_date(pai.action_information9)) hire_date
	   ,decode (to_char (fnd_date.canonical_to_date (pai.action_information10)
                ,'YYYY-MM-DD')
                     ,'4712-12-31'
                     ,NULL
                     ,fnd_date.date_to_displaydt  (fnd_date.canonical_to_date (pai.action_information10)))term_date
       ,pai.action_information11 rpn_number
       ,pai.action_information12 country
       ,upper (pai.action_information13) county
       ,pai2.action_information1 paye_reference
       ,fnd_date.date_to_displaydt (fnd_date.canonical_to_date(pai2.action_information2)) pay_date
       ,decode (pai2.action_information3
               ,'0'
               ,'WEEKLY'
               ,'1'
               ,'BI_WEEKLY'
               ,'2'
               ,'MONTHLY'
               ,'3'
               ,'FOUR_WEEKLY'
               ,'4'
               ,'OTHER') pay_frequency
       ,pai3.action_information1 std_rate_cutoff
       ,pai3.action_information2 annuatax_credit
       ,pai3.action_information3
       ,decode (pai3.action_information3
               ,'1'
               ,'WEEK_1'
               ,'2'
               ,'EMERGENCY'
               ,'0'
               ,'CUMULATIVE'
               ,NULL
               ,'2') tax_deduction_basis
       ,decode (pai3.action_information4
               ,'0'
               ,'false'
               ,1
               ,'true') exclusion_order
       ,pai3.action_information5 gross_pay
       ,pai3.action_information6 pay_for_income_tax
       ,pai3.action_information7 income_tax_paid
       ,pai3.action_information8 gross_pay_prsi
       ,pai3.action_information9 gross_pay_prsi_dup
       ,decode (pai3.action_information10
               ,'0'
               ,'false'
               ,1
               ,'true') prsi_exempt_flag
       ,decode (pai3.action_information11
               ,'01'
               ,'A_1_PORTABLE_DOCUMENT'
               ,'02'
               ,'CERTIFICATE_OF_COVERAGE'
               ,'03'
               ,'POSTED_WORKER_OTHER_NOTIFICATION'
               ,'04'
               ,'FAMILY_MEMBER'
               ,'05'
               ,'UNDER_16'
               ,'06'
               ,'SOCIAL_WELFARE_SCHEME'
               ,'07'
               ,'OTHER') exempt_reason
       ,pay_ie_payroll_sub_report.get_initial_class (paa.assignment_action_id,'IE CONT CLASS DETAILS') initial_class
       ,pay_ie_payroll_sub_report.get_insurable_weeks(paa.assignment_action_id,'IE CONT CLASS DETAILS') insurable_weeks
       ,pai3.action_information14 emp_prsi_cont
       ,pai3.action_information15 employer_prsi_cont
       ,pai3.action_information16 gross_pay_usc
       ,decode (pai3.action_information17
               ,'1'
               ,'EXEMPT'
               ,'ORDINARY') usc_status
       ,pai3.action_information18 usc
       ,pai3.action_information19 medical_insurance
       ,pay_ie_payroll_sub_report.get_initial_class (paa.assignment_action_id,'IE PENSION TRACING DETAILS') pension_tracing_number
       ,pai3.action_information21 pen_empr_rbs
       ,pai3.action_information22 pen_emp_rbs
       ,pai3.action_information23 rch_pen_empr_prsa
       ,pai3.action_information24 rch_pen_emp_prsa
       ,pai3.action_information25 rch_pen_emp_rac
       ,pai3.action_information26 pen_emp_avc
       ,pai3.action_information27 share_based_rem
       ,pai3.action_information28 taxable_benefits
       ,pai3.action_information29 taxable_lump_sum
       ,pai3.action_information30 nontax_lumpsum
       ,pai4.action_information1 lpt
       ,decode (nvl (pai4.action_information2
                    ,'')
               ,'NP'
               ,'NON-PROPRIETARY'
               ,'P'
               ,'PROPRIETARY') dir_flg
       ,decode (nvl (pai4.action_information3
                    ,'N')
               ,'N'
               ,'false'
               ,'Y'
               ,'true') shadow_payment
       ,pai4.action_information4 pay_periods
       ,pai4.action_information5 employer_reference
FROM    pay_action_information pai3
       ,pay_action_information pai
       ,pay_action_information pai4
       ,pay_action_information pai2
       ,pay_assignment_actions paa
       ,per_all_assignments_f paaf
       ,pay_payroll_actions ppa
WHERE   paaf.business_group_id = :BG_ID
AND     paaf.assignment_id = pai.assignment_id
AND     ppa.payroll_action_id = paa.payroll_action_id
AND     ppa.effective_date BETWEEN paaf.effective_start_date
                           AND     paaf.effective_end_date
AND     paaf.assignment_type = 'E'
AND     paa.payroll_action_id = :PAYROLL_ACTION_ID
AND     paa.action_status = 'C'
AND     paa.assignment_id = paaf.assignment_id
AND     pai.action_context_id = paa.assignment_action_id
AND     pai.action_context_type = 'AAP'
AND     pai.action_information_category = 'IE PSUB EMPLOYEE DETAILS'
AND     pai3.action_information_category = 'IE PSUB ASG DETAILS'
AND     pai3.action_context_type = 'AAP'
AND     pai3.action_context_id = paa.assignment_action_id
AND     pai4.action_information_category = 'IE PSUB ASG ADDITIONAL DETAIL'
AND     pai4.action_context_type = 'AAP'
AND     pai4.action_context_id = paa.assignment_action_id
AND     pai2.action_information_category = 'IE PSUB PAYROLL DETAILS'
AND     pai2.action_context_type = 'PA'
AND     pai2.action_context_id = paa.payroll_action_id