select
decode(NVL(PAPF.PER_INFORMATION10,'N'),'Y','P'||PAPF.PERSON_ID,'A'||PAAF.assignment_ID) group_id,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,paa.ASSIGNMENT_ACTION_ID)) ASSIGNMENT_ACTION_ID,
NVL(max(PAPF.PER_INFORMATION10),'N') AGGREGATED_PAYE,
NVL(max(PAPF.PER_INFORMATION9),'N') AGGREGATED_NI,
NVL(max(PAPF.PER_INFORMATION2),'N') DIRECTOR_IND,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,PAPF.PERSON_ID)),
max(decode(pai3.ACTION_INFORMATION3,NULL,0,PAAF.assignment_ID)),
max(pai3.ACTION_INFORMATION3) LAST_N,
max(pai3.ACTION_INFORMATION7) NINO,
max(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(pai3.ACTION_INFORMATION8),'DD-MON-YYYY')) DOB,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION13)) PAYROLL_ID,
max(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION1)),'DD-MON-YYYY')) START_DATE,
max(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION2)),'DD-MON-YYYY')) END_DATE,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION23)) HOURS_WORKED,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION16)) IRREG_PAY,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION20)) PERIODS_COVERED,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai2.ACTION_INFORMATION25)) TAX_CODE,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,decode(nvl(pai2.ACTION_INFORMATION27,0),'Y','Non Cumulative','Cumulative'))) TAX_BASIS,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,NVL(pai1.ACTION_INFORMATION18, nvl(pai1.ACTION_INFORMATION19,0)))) TAX_PERIOD,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,TO_CHAR(FND_DATE.CANONICAL_TO_DATE(pai1.ACTION_INFORMATION3),'DD-MON-YYYY'))) PAYMENT_DATE,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION6,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION6,0))*0.01, 0.01))) TAXABLE_PAY_PTD,
sum(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai2.ACTION_INFORMATION1,0))*0.01)) TAXABLE_PAY_YTD,
sum(to_number(nvl(pai2.ACTION_INFORMATION17,0))*0.01) TAX_PAID_PTD,
sum(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai2.ACTION_INFORMATION2,0))*0.01)) TAX_PAID_YTD,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION10,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION10,0))*0.01, 0.01))) BEN_TAXED_PTD,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION11,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION11,0))*0.01, 0.01))) BEN_PROCESSED_PTD,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION12,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION12,0))*0.01, 0.01))) NIC_FREE_BEN_YTD,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION8,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION8,0))*0.01, 0.01))) NET_PAY_DEDN_PTD,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION16,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION16,0))*0.01, 1)))
STUDENT_LOAN_PTD ,
sum(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION3,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION3,0))*0.01, 1)))) STUDENT_LOAN_YTD,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION13,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION13,0))*0.01, 0.01))) PRETAX_PEN_PTD,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION15,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION15,0))*0.01, 0.01))) POSTTAX_PEN_PTD,
sum(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION18,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION18,0))*0.01, 0.01)))) SSP ,
sum(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION19,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION19,0))*0.01, 0.01)))) SMP ,
sum(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION20,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION20,0))*0.01, 0.01)))) OSPP,
sum(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION21,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION21,0))*0.01, 0.01)))) SAP,
sum(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION22,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION22,0))*0.01, 0.01)))) ASPP_SHPP ,
max(paaf.assignment_number) ASSIGNMENT_NUMBER,
max(papf.employee_number) EMPLOYEE_NUMBER,
max(papf.full_name) FULL_NAME,
NULL PAYMENT_NON_INDIV,
NULL ON_STRIKE,
NULL UNPAID_ABSENCE,
0 TRIVIAL_AMOUNT_A,
0 TRIVIAL_AMOUNT_B,
0 TRIVIAL_AMOUNT_C,
0 FLEXIBLE_TAXABLE_PAY,
0 FLEXIBLE_NON_TAXABLE_PAY,
max(NVL(pai3.ACTION_INFORMATION13, 'N')) FLEXIBLY_ACCESSING_PENSION,
max(NVL(pai3.ACTION_INFORMATION15, 'N')) PENSION_DEATH_BENEFIT,
max(NVL(pai3.ACTION_INFORMATION18, 'N')) SERIOUS_ILL_HEALTH_INDICATOR,
max(pai3.ACTION_INFORMATION19) STUD_LOAN_PLAN_TYPE
from
pay_assignment_actions paa,
pay_action_information pai1,
pay_action_information pai2,
pay_action_information pai3,
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_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 pai2.assignment_id = pai1.assignment_id
and pai3.action_context_id(+) = paa.assignment_Action_id
and pai3.ACTION_CONTEXT_TYPE(+) = 'AAP'
and pai3.ACTION_INFORMATION_CATEGORY(+) = 'GB RTI EMPLOYEE DETAILS'
AND PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAPF.PERSON_ID = PAAF.PERSON_ID
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)
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 :REP_TYPE = 'RTI_FPS_REP'
group by decode(NVL(PAPF.PER_INFORMATION10,'N'),'Y','P'||PAPF.PERSON_ID,'A'||PAAF.assignment_ID)
union
select
decode(NVL(PAPF.PER_INFORMATION10,'N'),'Y','P'||PAPF.PERSON_ID,'A'||PAAF.assignment_ID) group_id,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,paa.ASSIGNMENT_ACTION_ID)) ASSIGNMENT_ACTION_ID,
NVL(max(PAPF.PER_INFORMATION10),'N') AGGREGATED_PAYE,
NVL(max(PAPF.PER_INFORMATION9),'N') AGGREGATED_NI,
NVL(max(PAPF.PER_INFORMATION2),'N') DIRECTOR_IND,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,PAPF.PERSON_ID)),
max(decode(pai3.ACTION_INFORMATION3,NULL,0,PAAF.assignment_ID)),
max(pai3.ACTION_INFORMATION3) LAST_N,
max(pai3.ACTION_INFORMATION7) NINO,
max(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(pai3.ACTION_INFORMATION8),'DD-MON-YYYY')) DOB,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION13)) PAYROLL_ID,
max(((decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION1)))) START_DATE,
max(((decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION2)))) END_DATE,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION23)) HOURS_WORKED,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION16)) IRREG_PAY,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION20)) PERIODS_COVERED,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai2.ACTION_INFORMATION25)) TAX_CODE,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,decode(nvl(pai2.ACTION_INFORMATION27,0),'Y','Non Cumulative','Cumulative'))) TAX_BASIS,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,NVL(pai1.ACTION_INFORMATION18, nvl(pai1.ACTION_INFORMATION19,0)))) TAX_PERIOD,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai1.ACTION_INFORMATION3)) PAYMENT_DATE,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION6,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION6,0))*0.01, 0.01))) TAXABLE_PAY_PTD,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai2.ACTION_INFORMATION1,0))*0.01)) TAXABLE_PAY_YTD,
sum(to_number(nvl(pai2.ACTION_INFORMATION17,0))*0.01) TAX_PAID_PTD,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai2.ACTION_INFORMATION2,0))*0.01)) TAX_PAID_YTD,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION10,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION10,0))*0.01, 0.01))) BEN_TAXED_PTD,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION11,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION11,0))*0.01, 0.01))) BEN_PROCESSED_PTD,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION12,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION12,0))*0.01, 0.01))) NIC_FREE_BEN_YTD ,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION8,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION8,0))*0.01, 0.01))) NET_PAY_DEDN_PTD ,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION16,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION16,0))*0.01, 1))) STUDENT_LOAN_PTD,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION3,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION3,0))*0.01, 1)))) STUDENT_LOAN_YTD,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION13,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION13,0))*0.01, 0.01))) PRETAX_PEN_PTD ,
sum(decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION15,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION15,0))*0.01, 0.01))) POSTTAX_PEN_PTD ,
decode(:REP_TYPE,'RTI_FPS_REP_13',max(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION18,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION18,0))*0.01, 0.01)))),0) SSP ,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION19,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION19,0))*0.01, 0.01)))) SMP,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION20,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION20,0))*0.01, 0.01)))) OSPP ,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION21,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION21,0))*0.01, 0.01)))) SAP,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,decode( 'Y','Y', to_number(nvl(pai2.ACTION_INFORMATION22,0))*0.01, greatest(to_number(nvl(pai2.ACTION_INFORMATION22,0))*0.01, 0.01)))) ASPP_SHPP ,
max(paaf.assignment_number) ASSIGNMENT_NUMBER,
max(papf.employee_number) EMPLOYEE_NUMBER,
max(papf.full_name) FULL_NAME,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai4.ACTION_INFORMATION7)) PAYMENT_NON_INDIV,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai4.ACTION_INFORMATION8)) ON_STRIKE,
max(decode(pai3.ACTION_INFORMATION3,NULL,NULL,pai4.ACTION_INFORMATION9)) UNPAID_ABSENCE,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai4.ACTION_INFORMATION2,0))*0.01)) TRIVIAL_AMOUNT_A,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai4.ACTION_INFORMATION4,0))*0.01)) TRIVIAL_AMOUNT_B,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai4.ACTION_INFORMATION6,0))*0.01)) TRIVIAL_AMOUNT_C,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai4.ACTION_INFORMATION16,0))*0.01)) FLEXIBLE_TAXABLE_PAY,
max(decode(pai3.ACTION_INFORMATION3,NULL,0,to_number(nvl(pai4.ACTION_INFORMATION17,0))*0.01)) FLEXIBLE_NON_TAXABLE_PAY,
max(NVL(pai4.ACTION_INFORMATION13, 'N')) FLEXIBLY_ACCESSING_PENSION,
max(NVL(pai4.ACTION_INFORMATION15, 'N')) PENSION_DEATH_BENEFIT,
max(NVL(pai4.ACTION_INFORMATION18, 'N')) SERIOUS_ILL_HEALTH_INDICATOR,
max(pai4.ACTION_INFORMATION19) STUD_LOAN_PLAN_TYPE
from
pay_assignment_actions paa,
(
select
max(pai11.action_context_id) action_context_id,
max(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(pai11.ACTION_INFORMATION1),'DD-MON-YYYY')) ACTION_INFORMATION1,
max(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(pai11.ACTION_INFORMATION2),'DD-MON-YYYY')) ACTION_INFORMATION2,
to_char(max(FND_DATE.CANONICAL_TO_DATE(pai11.ACTION_INFORMATION3)),'DD-MON-YYYY') ACTION_INFORMATION3,
max(pai11.ACTION_INFORMATION13) ACTION_INFORMATION13,
max(pai11.ACTION_INFORMATION16) ACTION_INFORMATION16,
max(pai11.ACTION_INFORMATION18) ACTION_INFORMATION18,
max(pai11.ACTION_INFORMATION19) ACTION_INFORMATION19,
max(pai11.ACTION_INFORMATION20) ACTION_INFORMATION20,
max(pai11.ACTION_INFORMATION23) ACTION_INFORMATION23
from
pay_assignment_actions paa1,
pay_action_information pai11
where
paa1.payroll_action_id = :PAYROLL_ACTION_ID
and pai11.action_context_id = paa1.assignment_Action_id
and pai11.ACTION_CONTEXT_TYPE = 'AAP'
and pai11.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET1'
group by decode(pai11.action_INFORMATION29,null,pai11.action_information_id,pai11.assignment_id)
) pai1,
(
select max(pai22.action_context_id) action_context_id,
max(pai22.ACTION_INFORMATION25) ACTION_INFORMATION25,
max(pai22.ACTION_INFORMATION27) ACTION_INFORMATION27,
sum(nvl(pai22.ACTION_INFORMATION6,0)) ACTION_INFORMATION6,
max(to_number(nvl(pai22.ACTION_INFORMATION1,0))) ACTION_INFORMATION1,
sum(to_number(nvl(pai22.ACTION_INFORMATION17,0))) ACTION_INFORMATION17,
max(to_number(nvl(pai22.ACTION_INFORMATION2,0))) ACTION_INFORMATION2,
sum(to_number(nvl(pai22.ACTION_INFORMATION10,0))) ACTION_INFORMATION10,
sum(to_number(nvl(pai22.ACTION_INFORMATION11,0))) ACTION_INFORMATION11,
sum(to_number(nvl(pai22.ACTION_INFORMATION12,0))) ACTION_INFORMATION12 ,
sum(to_number(nvl(pai22.ACTION_INFORMATION8,0))) ACTION_INFORMATION8,
sum(to_number(nvl(pai22.ACTION_INFORMATION16,0))) ACTION_INFORMATION16,
max(to_number(nvl(pai22.ACTION_INFORMATION3,0))) ACTION_INFORMATION3,
sum(to_number(nvl(pai22.ACTION_INFORMATION13,0))) ACTION_INFORMATION13,
sum(to_number(nvl(pai22.ACTION_INFORMATION15,0))) ACTION_INFORMATION15 ,
max(to_number(nvl(pai22.ACTION_INFORMATION18,0))) ACTION_INFORMATION18,
max(to_number(nvl(pai22.ACTION_INFORMATION19,0))) ACTION_INFORMATION19,
max(to_number(nvl(pai22.ACTION_INFORMATION20,0))) ACTION_INFORMATION20 ,
max(to_number(nvl(pai22.ACTION_INFORMATION21,0))) ACTION_INFORMATION21,
max(to_number(nvl(pai22.ACTION_INFORMATION22,0))) ACTION_INFORMATION22
from
pay_assignment_actions paa2,
pay_action_information pai22
where
paa2.payroll_action_id = :PAYROLL_ACTION_ID
and pai22.action_context_id = paa2.assignment_Action_id
and pai22.ACTION_CONTEXT_TYPE = 'AAP'
and pai22.ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_ASG_DET2'
group by decode(pai22.action_INFORMATION29,null,pai22.action_information_id,pai22.assignment_id)
) pai2,
pay_action_information pai3,
pay_action_information pai4,
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 pai2.action_context_id = paa.assignment_Action_id
and pai4.action_context_id(+) = paa.assignment_Action_id
and pai4.ACTION_CONTEXT_TYPE(+) = 'AAP'
and pai4.ACTION_INFORMATION_CATEGORY(+) = 'GB_RTI_FPS_ASG_DET3'
and pai3.action_context_id(+) = paa.assignment_Action_id
and pai3.ACTION_CONTEXT_TYPE(+) = 'AAP'
and pai3.ACTION_INFORMATION_CATEGORY(+) = 'GB RTI EMPLOYEE DETAILS'
AND PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAPF.PERSON_ID = PAAF.PERSON_ID
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)
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)
group by decode(NVL(PAPF.PER_INFORMATION10,'N'),'Y','P'||PAPF.PERSON_ID,'A'||PAAF.assignment_ID)
order by LAST_N, PAYROLL_ID, ASSIGNMENT_NUMBER |