SELECT
paa.ASSIGNMENT_ACTION_ID ASG_ACTION_ID,
person.ACTION_INFORMATION6 PAYROLL_ID,
person.ACTION_INFORMATION5 ASG_NUMBER,
person.ACTION_INFORMATION2 NI_NUMBER,
person.ACTION_INFORMATION1 FULL_NAME,
TO_CHAR(FND_DATE.CANONICAL_TO_DATE(person.ACTION_INFORMATION7),'DD-MON-YYYY') START_DATE,
TO_CHAR(FND_DATE.CANONICAL_TO_DATE(person.ACTION_INFORMATION8),'DD-MON-YYYY') END_DATE,
NVL(person.ACTION_INFORMATION3,'N') AGGREGATED_PAYE,
NVL(person.ACTION_INFORMATION4,'N') AGGREGATED_NI,
--pai3.ACTION_INFORMATION1,
MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION2,0))*0.01)) TAXABLE_PAY_RUN,
MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION3,0))*0.01)) TAX_PAID_RUN,
MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION4,0))*0.01)) STUDENT_LOAN_RUN,
MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION5,0))*0.01)) SSP_RUN,
MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION6,0))*0.01)) SMP_RUN,
MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION7,0))*0.01)) OSPP_RUN,
MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION8,0))*0.01)) SAP_RUN,
MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION9,0))*0.01)) ASPP_RUN,
MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai3.ACTION_INFORMATION2,0))*0.01)) TAXABLE_PAY_P60,
MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai3.ACTION_INFORMATION3,0))*0.01)) TAX_PAID_P60,
MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai3.ACTION_INFORMATION4,0))*0.01)) STUDENT_LOAN_P60,
MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai3.ACTION_INFORMATION5,0))*0.01)) SSP_P60,
MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai3.ACTION_INFORMATION6,0))*0.01)) SMP_P60,
MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai3.ACTION_INFORMATION7,0))*0.01)) OSPP_P60,
MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai3.ACTION_INFORMATION8,0))*0.01)) SAP_P60,
MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number(nvl(pai3.ACTION_INFORMATION9,0))*0.01)) ASPP_P60,
decode(MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number(pai3.ACTION_INFORMATION2))),NULL,MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION2,0))*0.01)),NULL) TAXABLE_PAY_NO_P60,
decode(MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number(pai3.ACTION_INFORMATION3))),NULL,MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION3,0))*0.01)),NULL) TAX_PAID_NO_P60,
decode(MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number(pai3.ACTION_INFORMATION4))),NULL,MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION4,0))*0.01)),NULL) STUDENT_LOAN_NO_P60,
decode(MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number((pai3.ACTION_INFORMATION5)))),NULL,MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION5,0))*0.01)),NULL) SSP_NO_P60,
decode(MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number((pai3.ACTION_INFORMATION6)))),NULL,MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION6,0))*0.01)),NULL) SMP_NO_P60,
decode(MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number((pai3.ACTION_INFORMATION7)))),NULL,MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION7,0))*0.01)),NULL) OSPP_NO_P60,
decode(MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number((pai3.ACTION_INFORMATION8)))),NULL,MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION8,0))*0.01)),NULL) SAP_NO_P60,
decode(MAX(decode(pai3.ACTION_INFORMATION1,'P60 DATA',to_number((pai3.ACTION_INFORMATION9)))),NULL,MAX(decode(pai3.ACTION_INFORMATION1,'PAY DATA',to_number(nvl(pai3.ACTION_INFORMATION9,0))*0.01)),NULL) ASPP_NO_P60,
MAX(decode(pai3.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai3.ACTION_INFORMATION2,0))*0.01)) TAXABLE_PAY_FPS,
MAX(decode(pai3.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai3.ACTION_INFORMATION3,0))*0.01)) TAX_PAID_FPS,
MAX(decode(pai3.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai3.ACTION_INFORMATION4,0))*0.01)) STUDENT_LOAN_FPS,
MAX(decode(pai3.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai3.ACTION_INFORMATION5,0))*0.01)) SSP_FPS,
MAX(decode(pai3.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai3.ACTION_INFORMATION6,0))*0.01)) SMP_FPS,
MAX(decode(pai3.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai3.ACTION_INFORMATION7,0))*0.01)) OSPP_FPS,
MAX(decode(pai3.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai3.ACTION_INFORMATION8,0))*0.01)) SAP_FPS,
MAX(decode(pai3.ACTION_INFORMATION1,'FPS DATA',to_number(nvl(pai3.ACTION_INFORMATION9,0))*0.01)) ASPP_FPS,
MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION2,0))*0.01)) TAXABLE_PAY_EYU,
MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION3,0))*0.01)) TAX_PAID_EYU,
MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION4,0))*0.01)) STUDENT_LOAN_EYU,
MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION5,0))*0.01)) SSP_EYU,
MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION6,0))*0.01)) SMP_EYU,
MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION7,0))*0.01)) OSPP_EYU,
MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION8,0))*0.01)) SAP_EYU,
MAX(decode(pai3.ACTION_INFORMATION1,'EYU DATA',to_number(nvl(pai3.ACTION_INFORMATION9,0))*0.01)) ASPP_EYU
FROM
pay_assignment_actions paa,
pay_action_information pai3,
pay_action_information person
where
paa.payroll_Action_id = :PAYROLL_ACTION_ID
and pai3.action_context_id = paa.assignment_Action_id
and pai3.ACTION_CONTEXT_TYPE = 'AAP'
and pai3.ACTION_INFORMATION_CATEGORY = 'GB RTI REC ASG DET'
and pai3.ACTION_INFORMATION1 in( 'PAY DATA','FPS DATA','EYU DATA','P60 DATA')
and person.action_context_id = paa.assignment_action_id
and person.ACTION_CONTEXT_TYPE = 'AAP'
and person.ACTION_INFORMATION_CATEGORY = 'GB RTI REC EMP DET'
group by paa.ASSIGNMENT_ACTION_ID,
person.ACTION_INFORMATION6,
person.ACTION_INFORMATION5,
person.ACTION_INFORMATION2,
person.ACTION_INFORMATION1,
person.ACTION_INFORMATION7,
person.ACTION_INFORMATION8,
person.ACTION_INFORMATION3,
person.ACTION_INFORMATION4
order by 1 |
