select
paa.ASSIGNMENT_ACTION_ID ASG_ACTION_ID,
paa.ASSIGNMENT_ACTION_ID ASG_ACTION_ID,
null pre_pay_action_id,
null period_service_id,
'Y' NP,
:GENDER GENDER,
pai1.ACTION_INFORMATION1 S_DATE,
pai1.ACTION_INFORMATION2 E_DATE,
pai1.ACTION_INFORMATION3 PAY_D,
pai1.ACTION_INFORMATION4 STARTER_DEC,
pai1.ACTION_INFORMATION5 STUD_LOAN_IND,
pai1.ACTION_INFORMATION6 OVER_SIX,
pai1.ACTION_INFORMATION7 LESS_SIX,
pai1.ACTION_INFORMATION8 LIV_ABR,
pai1.ACTION_INFORMATION9 EEA_CWC,
pai1.ACTION_INFORMATION10 EPM6,
pai1.ACTION_INFORMATION11 OCC_PENSION_INDICATOR,
pai1.ACTION_INFORMATION12 OCC_IND,
pai1.ACTION_INFORMATION13 PAYID,
pai1.ACTION_INFORMATION14 PAY_CHAN_IND,
pai1.ACTION_INFORMATION15 OPAYID,
pai1.ACTION_INFORMATION16 IRR_IND,
pai1.ACTION_INFORMATION17 PAY_FRE,
pai1.ACTION_INFORMATION18 WEEK_NO,
pai1.ACTION_INFORMATION19 MONTH_NO,
pai1.ACTION_INFORMATION20 PER_COV,
pai1.ACTION_INFORMATION21 AGG_IND,
pai1.ACTION_INFORMATION22 PAY_LEAV_IND,
pai1.ACTION_INFORMATION23 HRS,
pai1.ACTION_INFORMATION24 NIC_CALC,
pai1.ACTION_INFORMATION25 TAX_WEEK,
pai1.ACTION_INFORMATION26 ANNUAL_OCC,
pai1.ACTION_INFORMATION28 BACS,
pai2.ACTION_INFORMATION1 TAX_PAYD,
pai2.ACTION_INFORMATION2 T_TAX,
trunc(pai2.ACTION_INFORMATION3/100)*100 STUD_LOAN_REC,
pai2.ACTION_INFORMATION6 TAX_PAY,
pai2.ACTION_INFORMATION7 PAY_NNIC,
pai2.ACTION_INFORMATION8 DED_PAY,
pai2.ACTION_INFORMATION9 STA_DED,
pai2.ACTION_INFORMATION10 B_PAY,
pai2.ACTION_INFORMATION12 B_PAY_YEAR,
pai2.ACTION_INFORMATION13 EMP_PAY,
pai2.ACTION_INFORMATION14 I_NIC,
pai2.ACTION_INFORMATION15 EMP_PEN_NOT,
trunc(pai2.ACTION_INFORMATION16/100)* 100 STUD_LOAN_R_REC,
pai2.ACTION_INFORMATION17 TAX_DED,
pai2.ACTION_INFORMATION18 SSP,
pai2.ACTION_INFORMATION19 SMP,
pai2.ACTION_INFORMATION20 SPP,
pai2.ACTION_INFORMATION21 SAP,
pai2.ACTION_INFORMATION22 ASPP_SHPP,
pai2.ACTION_INFORMATION25 TAX_CODE,
decode(upper(substr(pai2.ACTION_INFORMATION25,1,1)),'S','S','X') TAX_REGIME,
substr(pai2.ACTION_INFORMATION25,2) TAX_CODE_SCOT,
pai2.ACTION_INFORMATION27 W1M1_IND,
(pai3.ACTION_INFORMATION1) TRIV_COMM_PAYMENT_TYPE_A,
trunc(pai3.ACTION_INFORMATION2/100)*100 TRIV_COMM_PAYMENT_AMOUNT_A,
(pai3.ACTION_INFORMATION3) TRIV_COMM_PAYMENT_TYPE_B,
trunc(pai3.ACTION_INFORMATION4/100)*100 TRIV_COMM_PAYMENT_AMOUNT_B,
(pai3.ACTION_INFORMATION5) TRIV_COMM_PAYMENT_TYPE_C,
trunc(pai3.ACTION_INFORMATION6/100)*100 TRIV_COMM_PAYMENT_AMOUNT_C,
(pai3.ACTION_INFORMATION7) PAYMENT_TO_NON_INDIV_IND ,
(pai3.ACTION_INFORMATION8) ONSTRIKE_INDICATOR ,
(pai3.ACTION_INFORMATION9) UNPAID_ABSENCE_INDICATOR,
pai3.ACTION_INFORMATION10 B_PAY_YTD,
pai2.ACTION_INFORMATION26 EMP_PAY_YTD,
pai2.ACTION_INFORMATION28 EMP_PEN_NOT_YTD,
pai3.ACTION_INFORMATION11 LATE_PAYE,
(pai3.ACTION_INFORMATION13) FLEXIBLY_ACCESSING_PENSION,
(pai3.ACTION_INFORMATION15) PENSION_DEATH_BENEFIT,
(pai3.ACTION_INFORMATION16) FLEXIBLE_DRAWDOWN_TAXABLE,
(pai3.ACTION_INFORMATION17) FLEXIBLE_DRAWDOWN_NON_TAXABLE
from
pay_assignment_actions paa,
pay_action_information pai1,
pay_action_information pai2,
pay_action_information pai3,
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_FPS_ASG_DET1'
and pai2.action_context_id = paa.assignment_Action_id
and pai2.ACTION_CONTEXT_TYPE = 'AAP'
and pai2.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET2'
and pai3.action_context_id (+) = paa.assignment_Action_id
and pai3.ACTION_CONTEXT_TYPE (+) = 'AAP'
and pai3.ACTION_INFORMATION_CATEGORY (+) = 'GB_RTI_FPS_ASG_DET3'
and paaf.assignment_id = paa.assignment_id
and paaf.person_id = :PERSON_ID
and pai1.assignment_id = pai2.assignment_id
and paaf.effective_start_date =
(select max(effective_start_date) from per_all_assignments_f where assignment_id = paaf.assignment_id)
and (nvl(pai1.ACTION_INFORMATION21,'N') <> 'Y'
or (pai1.ACTION_INFORMATION21 = 'Y' and nvl(pai1.ACTION_INFORMATION27,'N') <> 'Y'))
union all
select
to_number(substr(max(pai2.ACTION_INFORMATION29), instr(max(pai2.ACTION_INFORMATION29),',')+1,(instr(max(pai2.ACTION_INFORMATION29),',',1,2))-instr(max(pai2.ACTION_INFORMATION29),',')-1)) act_id,
max(paa.ASSIGNMENT_ACTION_ID) ASG_ACTION_ID,
substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1) pre_pay_action_id,
substr(pai2.ACTION_INFORMATION29, instr(pai2.ACTION_INFORMATION29,',',1,2)+1,length(pai2.ACTION_INFORMATION29)) period_service_id,
'Y' NP,
max(:GENDER) GENDER,
max(pai1.ACTION_INFORMATION1) S_DATE,
max(pai1.ACTION_INFORMATION2) E_DATE,
max(pai1.ACTION_INFORMATION3) PAY_D,
max(pai1.ACTION_INFORMATION4) STARTER_DEC,
max(pai1.ACTION_INFORMATION5) STUD_LOAN_IND,
max(pai1.ACTION_INFORMATION6) OVER_SIX,
max(pai1.ACTION_INFORMATION7) LESS_SIX,
max(pai1.ACTION_INFORMATION8) LIV_ABR,
max(pai1.ACTION_INFORMATION9) EEA_CWC,
max(pai1.ACTION_INFORMATION10) EPM6,
max(pai1.ACTION_INFORMATION11) OCC_PENSION_INDICATOR,
max(pai1.ACTION_INFORMATION12) OCC_IND,
max(pai1.ACTION_INFORMATION13) PAYID,
max(pai1.ACTION_INFORMATION14) PAY_CHAN_IND,
max(pai1.ACTION_INFORMATION15) OPAYID,
max(pai1.ACTION_INFORMATION16) IRR_IND,
max(pai1.ACTION_INFORMATION17) PAY_FRE,
max(pai1.ACTION_INFORMATION18) WEEK_NO,
max(pai1.ACTION_INFORMATION19) MONTH_NO,
max(pai1.ACTION_INFORMATION20) PER_COV,
max(pai1.ACTION_INFORMATION21) AGG_IND,
max(pai1.ACTION_INFORMATION22) PAY_LEAV_IND,
max(pai1.ACTION_INFORMATION23) HRS,
max(pai1.ACTION_INFORMATION24) NIC_CALC,
max(pai1.ACTION_INFORMATION25) TAX_WEEK,
max(pai1.ACTION_INFORMATION26) ANNUAL_OCC,
max(pai1.ACTION_INFORMATION28) BACS,
max(pai2.ACTION_INFORMATION1) TAX_PAYD,
max(pai2.ACTION_INFORMATION2) T_TAX,
trunc(max(pai2.ACTION_INFORMATION3)/100)*100 STUD_LOAN_REC,
to_char(max(aggr_ptd.TAX_PAY)) TAX_PAY,
to_char(max(aggr_ptd.PAY_NNIC)) PAY_NNIC,
to_char(max(aggr_ptd.DED_PAY)) DED_PAY,
to_char(max(aggr_ptd.STA_DED)) STA_DED,
to_char(max(aggr_ptd.B_PAY)) B_PAY,
to_char(max(aggr_ptd.B_PAY_YEAR)) B_PAY_YEAR,
to_char(max(aggr_ptd.EMP_PAY)) EMP_PAY,
to_char(max(aggr_ptd.I_NIC)) I_NIC,
to_char(max(aggr_ptd.EMP_PEN_NOT)) EMP_PEN_NOT,
trunc(max(aggr_ptd.STUD_LOAN_R_REC)/100)*100 STUD_LOAN_R_REC,
to_char(max(aggr_ptd.TAX_DED)) TAX_DED,
max(pai2.ACTION_INFORMATION18) SSP,
max(pai2.ACTION_INFORMATION19) SMP,
max(pai2.ACTION_INFORMATION20) SPP,
max(pai2.ACTION_INFORMATION21) SAP,
max(pai2.ACTION_INFORMATION22) ASPP_SHPP,
max(pai2.ACTION_INFORMATION25) TAX_CODE,
decode(upper(substr(max(pai2.ACTION_INFORMATION25),1,1)),'S','S','X') TAX_REGIME,
max(substr(pai2.ACTION_INFORMATION25,2)) TAX_CODE_SCOT,
max(pai2.ACTION_INFORMATION27) W1M1_IND,
max(pai3.ACTION_INFORMATION1) TRIV_COMM_PAYMENT_TYPE_A,
trunc(max(pai3.ACTION_INFORMATION2)/100)*100 TRIV_COMM_PAYMENT_AMOUNT_A,
max(pai3.ACTION_INFORMATION3) TRIV_COMM_PAYMENT_TYPE_B,
trunc(max(pai3.ACTION_INFORMATION4)/100)*100 TRIV_COMM_PAYMENT_AMOUNT_B,
max(pai3.ACTION_INFORMATION5) TRIV_COMM_PAYMENT_TYPE_C,
trunc(max(pai3.ACTION_INFORMATION6)/100)*100 TRIV_COMM_PAYMENT_AMOUNT_C,
max(pai3.ACTION_INFORMATION7) PAYMENT_TO_NON_INDIV_IND ,
max(pai3.ACTION_INFORMATION8) ONSTRIKE_INDICATOR ,
max(pai3.ACTION_INFORMATION9) UNPAID_ABSENCE_INDICATOR,
max(pai3.ACTION_INFORMATION10) B_PAY_YTD,
max(pai2.ACTION_INFORMATION26) EMP_PAY_YTD,
max(pai2.ACTION_INFORMATION28) EMP_PEN_NOT_YTD,
max(pai3.ACTION_INFORMATION11) LATE_PAYE,
max(pai3.ACTION_INFORMATION13) FLEXIBLY_ACCESSING_PENSION,
max(pai3.ACTION_INFORMATION15) PENSION_DEATH_BENEFIT,
max(pai3.ACTION_INFORMATION16) FLEXIBLE_DRAWDOWN_TAXABLE,
max(pai3.ACTION_INFORMATION17) FLEXIBLE_DRAWDOWN_NON_TAXABLE
from
pay_assignment_actions paa,
pay_action_information pai1,
pay_action_information pai2,
pay_action_information pai3,
per_all_Assignments_f paaf,
(select sum(pai2.ACTION_INFORMATION6) TAX_PAY,
sum(pai2.ACTION_INFORMATION7) PAY_NNIC,
sum(pai2.ACTION_INFORMATION8) DED_PAY,
sum(pai2.ACTION_INFORMATION9) STA_DED,
sum(pai2.ACTION_INFORMATION10) B_PAY,
sum(pai2.ACTION_INFORMATION12) B_PAY_YEAR,
sum(pai2.ACTION_INFORMATION13) EMP_PAY,
sum(pai2.ACTION_INFORMATION14) I_NIC,
sum(pai2.ACTION_INFORMATION15) EMP_PEN_NOT,
trunc(sum(pai2.ACTION_INFORMATION16)/100)* 100 STUD_LOAN_R_REC,
sum(pai2.ACTION_INFORMATION17) TAX_DED,
sum(pai2.ACTION_INFORMATION23) dummy1,
sum(pai2.ACTION_INFORMATION24) dummy1,
max(pai2.ACTION_INFORMATION29) pre_pay_id,
substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1) pay_id,
substr(pai2.ACTION_INFORMATION29, instr(pai2.ACTION_INFORMATION29,',',1,2)+1,length(pai2.ACTION_INFORMATION29)) pos_id
from
pay_assignment_actions paa,
pay_action_information pai2,
per_all_Assignments_f paaf
where paa.payroll_Action_id = :PAYROLL_ACTION_ID
and pai2.action_context_id = paa.assignment_Action_id
and pai2.ACTION_CONTEXT_TYPE = 'AAP'
and pai2.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET2'
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)
group by substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1)
,substr(pai2.ACTION_INFORMATION29, instr(pai2.ACTION_INFORMATION29,',',1,2)+1,length(pai2.ACTION_INFORMATION29)) ) aggr_ptd
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_FPS_ASG_DET1'
and pai2.action_context_id = paa.assignment_Action_id
and pai2.ACTION_CONTEXT_TYPE = 'AAP'
and pai2.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET2'
and pai3.action_context_id(+) = paa.assignment_Action_id
and pai3.ACTION_CONTEXT_TYPE(+) = 'AAP'
and pai3.ACTION_INFORMATION_CATEGORY(+) = 'GB_RTI_FPS_ASG_DET3'
and paaf.assignment_id = paa.assignment_id
and paaf.person_id = :PERSON_ID
and pai1.assignment_id = pai2.assignment_id
and paaf.effective_start_date =
(select max(effective_start_date)
from per_all_assignments_f
where assignment_id = paaf.assignment_id)
and substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1) = aggr_ptd.pay_id
and substr(pai2.ACTION_INFORMATION29, instr(pai2.ACTION_INFORMATION29,',',1,2)+1,length(pai2.ACTION_INFORMATION29)) = aggr_ptd.pos_id
and pai1.ACTION_INFORMATION21 = 'Y'
and pai1.ACTION_INFORMATION27 = 'Y'
and pai1.ACTION_INFORMATION30 = 'Y'
and pai2.ACTION_INFORMATION30 = 'Y'
group by substr(pai2.ACTION_INFORMATION29,1,instr(pai2.ACTION_INFORMATION29,',')-1),
substr(pai2.ACTION_INFORMATION29, instr(pai2.ACTION_INFORMATION29,',',1,2)+1,length(pai2.ACTION_INFORMATION29))
order by 1 |
