PAY IR8A Summary

select assignment_id, employee_name, 
trunc(moa_776) + trunc(moa_369) + trunc(moa_340) + moa_265 + moa_341 + moa_342 + moa_343 + moa_344 + moa_345 + moa_347 + moa_348 + moa_349 + moa_601+decode(sign(to_number(:p_basis_year)-2014),-1,decode(sign(moa_515-0),1,moa_351,moa_515),decode(sign(moa_523n-0),1,moa_351,moa_523n)) moa_128,
MOA_367,
MOA_368,
MOA_67,
MOA_776,
MOA_369,
MOA_340,
moa_265 + moa_341 + moa_342 + moa_343 + moa_344 + moa_345 + moa_347 + moa_348 + moa_349 + moa_601 + decode(sign(to_number(:p_basis_year)-2014),-1,decode(sign(moa_515-0),1,moa_351,moa_515),decode(sign(moa_523n-0),1,moa_351,moa_523n)) moa_275, 
MOA_265,
MOA_341,
MOA_342,
MOA_343,
MOA_344,
MOA_345,
MOA_346,
MOA_347,
MOA_348,
MOA_349,
MOA_350,
decode(sign(to_number(:p_basis_year)-2014),-1,decode(sign(moa_515-0),1,moa_351,moa_515),decode(sign(moa_523n-0),1,moa_351,moa_523n)) moa_351,
MOA_351,
MOA_352,
MOA_366,
MOA_370,
MOA_371,
MOA_372,
MOA_373,
MOA_602 MOA_374
from (select 
paa.assignment_id, ff1.value employee_name,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_367_PER_LE_YTD',ff.value),0)) MOA_367,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_368_PER_LE_YTD',ff.value),0)) MOA_368,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_67_PER_LE_YTD',ff.value),0)) MOA_67,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_776_PER_LE_YTD',ff.value),0)) MOA_776,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_369_PER_LE_YTD',ff.value),0)) MOA_369,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_340_PER_LE_YTD',ff.value),0)) MOA_340,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_275_PER_LE_YTD',ff.value),0)) MOA_275,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_265_PER_LE_YTD',ff.value),0)) MOA_265,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_341_PER_LE_YTD',ff.value),0)) MOA_341,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_342_PER_LE_YTD',ff.value),0)) MOA_342,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_343_PER_LE_YTD',ff.value),0)) MOA_343,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_344_PER_LE_YTD',ff.value),0)) MOA_344,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_345_PER_LE_YTD',ff.value),0)) MOA_345,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_346_PER_LE_YTD',ff.value),0)) MOA_346,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_347_PER_LE_YTD',ff.value),0)) MOA_347,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_348_PER_LE_YTD',ff.value),0)) MOA_348,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_349_PER_LE_YTD',ff.value),0)) MOA_349,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_350_PER_LE_YTD',ff.value),0)) MOA_350,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_351_PER_LE_YTD',ff.value),0)) MOA_351,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_352_PER_LE_YTD',ff.value),0)) MOA_352,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_366_PER_LE_YTD',ff.value),0)) MOA_366,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_370_PER_LE_YTD',ff.value),0)) MOA_370,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_371_PER_LE_YTD',ff.value),0)) MOA_371,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_372_PER_LE_YTD',ff.value),0)) MOA_372,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_373_PER_LE_YTD',ff.value),0)) MOA_373,
sum(nvl(decode(fue.user_entity_name, 'X_IR8A_MOA_374_PER_LE_YTD',ff.value),0)) MOA_374,
sum(nvl(decode(fue.user_entity_name, 'X_A8B_MOA_601',ff.value),0)) MOA_601,
sum(nvl(decode(fue.user_entity_name, 'X_A8B_MOA_602',ff.value),0)) MOA_602,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_523_YA2015_PER_LE_YTD', ff.value),0)) MOA_523n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_515_PER_LE_YTD',ff.value),0)) MOA_515
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
where fue.user_Entity_id= ff.user_entity_id
and (fue.user_entity_name like 'X_IR8A_MOA%PER_LE_YTD' or fue.user_entity_name in ('X_A8B_MOA_601','X_A8B_MOA_602','X_A8A_MOA_523_YA2015_PER_LE_YTD','X_A8A_MOA_515_PER_LE_YTD'))
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 paa.assignment_id = paaf.assignment_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,paaf.assignment_id),'Y','Y','N')) = 'Y'  and paa.assignment_id like decode(:p_assignment_id,null,'%',:p_assignment_id) 
group by paa.assignment_id, paaf.person_id, ff1.value
order by paaf.person_id)