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 |
