PAY IR8S Summary

select 
paa.assignment_id, ff1.value employee_name,
to_char(fnd_date.canonical_to_date(ac3.context),'MON') month,
sum(nvl(decode(fue.user_entity_name, 'X_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0)) MOA_400, 
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0))+ sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0)) MOA_401,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0))+ sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0)) MOA_402,
sum(nvl(decode(fue.user_entity_name, 'X_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) MOA_404,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0))+sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) MOA_405,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0))+sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) MOA_406,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0))+ sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) CPF_LIAB,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0))+ sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) VOL_CPF_LIAB,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0))+ sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) CPF_WITH,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',ff.value),0))+ sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',ff.value),0)) VOL_CPF_WITH
from ff_user_entities fue, ff_archive_items ff,
     ff_user_entities fue1, ff_archive_items ff1,
     pay_assignment_actions paa,
     per_all_assignments_f paaf,
     ff_archive_item_contexts ac3
where fue.user_Entity_id= ff.user_entity_id
and fue.user_entity_name in ('X_EMPLOYER_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',
'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',
'X_EMPLOYEE_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',
'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH',
'X_EMPLOYER_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',
'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',
'X_EMPLOYEE_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',
'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH',
'X_ORDINARY_EARNINGS_PER_LE_MONTH',
'X_ADDITIONAL_EARNINGS_PER_LE_MONTH')
and ff.context1=paa.assignment_action_id
and fue1.user_entity_id=ff1.user_entity_id
and fue1.user_entity_name = 'X_PEOPLE_FLEXFIELD_SG_SG_LEGAL_NAME'
and ff1.context1=paa.assignment_action_id
and ff.archive_item_id = ac3.archive_item_id and ac3.sequence_no = 3
and paa.payroll_action_id=:p_archive_action_id
and paa.assignment_id = paaf.assignment_id
and paa.assignment_id in (
select assignment_id
from (select paa.assignment_id, ff1.value employee_name,
sum(nvl(decode(fue.user_entity_name, 'X_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_400,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_401_1,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_401_2,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0))+sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_401,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_402_1,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_402_2,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0))+sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_402,
sum(nvl(decode(fue.user_entity_name, 'X_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_404,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_405_1,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_405_2,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0))+sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_405,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_406_1,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_406_2,
sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0))+sum(nvl(decode(fue.user_entity_name, 'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',ff.value),0)) MOA_406,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_349_PER_LE_YTD',ff.value),0)) MOA_408,
sum(nvl(decode(fue.user_entity_name, 'X_IR8S_MOA_409_PER_LE_YTD',ff.value),0)) MOA_409,
sum(nvl(decode(fue.user_entity_name, 'X_MOA410',ff.value),0)) MOA_410,
sum(nvl(decode(fue.user_entity_name, 'X_MOA411',ff.value),0)) MOA_411,
sum(nvl(decode(fue.user_entity_name, 'X_MOA412',ff.value),0)) MOA_412,
sum(nvl(decode(fue.user_entity_name, 'X_MOA413',ff.value),0)) MOA_413,
sum(nvl(decode(fue.user_entity_name, 'X_MOA414',ff.value),0)) MOA_414
from ff_user_entities fue, ff_archive_items ff,
     ff_user_entities fue1, ff_archive_items ff1,
     ff_user_entities fue2, ff_archive_items ff2,
     pay_assignment_actions paa
where fue.user_Entity_id= ff.user_entity_id
and fue.user_entity_name in ('X_EMPLOYER_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',
'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',
'X_EMPLOYEE_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',
'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_YTD',
'X_EMPLOYER_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',
'X_EMPLOYER_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',
'X_EMPLOYEE_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',
'X_EMPLOYEE_VOL_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_YTD',
'X_ORDINARY_EARNINGS_PER_LE_YTD',
'X_ADDITIONAL_EARNINGS_PER_LE_YTD',
'X_IR8A_MOA_349_PER_LE_YTD',
'X_IR8S_MOA_409_PER_LE_YTD',
'X_MOA410','X_MOA411','X_MOA412','X_MOA413','X_MOA414'
)
and ff.context1=paa.assignment_action_id
and fue1.user_entity_id=ff1.user_entity_id
and fue1.user_entity_name = 'X_PEOPLE_FLEXFIELD_SG_SG_LEGAL_NAME'
and ff1.context1=paa.assignment_action_id
and fue2.user_entity_id=ff2.user_entity_id
and fue2.user_entity_name = 'X_HR_IR8A_INDICATORS_SG_PER_IR8S_APPLICABLE'
and ff2.context1=paa.assignment_action_id
and paa.payroll_action_id=:p_archive_action_id
and decode(:p_assignment_set_id,null,'Y',decode(hr_assignment_set.ASSIGNMENT_IN_SET(:p_assignment_set_id,paa.assignment_id),'Y','Y','N')) = 'Y'
and paa.assignment_id like decode(:p_assignment_id,null,'%',:p_assignment_id) 
and nvl(ff2.value,'N')='Y'
group by paa.assignment_id, ff1.value)
where (moa_400<>0 or moa_401<>0 or moa_402<>0 or moa_404<>0 or moa_405<>0 or moa_406<>0 or moa_408<>0 or moa_409<>0 or moa_410<>0))
group by paa.assignment_id, paaf.person_id, ff1.value, ac3.context
order by paaf.person_id