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 |