PAY A8A Summary

select assignment_id,employee_name,
decode(l_moa_500+l_moa_501, 0, 0, l_moa_500) moa_500,
decode(l_moa_500+l_moa_501, 0, 0, l_moa_501) moa_501,
moa_502n moa_502,
decode(l_moa_500+l_moa_501, 0, moa_502n, l_moa_500+l_moa_501) moa_503,
moa_504n moa_504,
decode(l_moa_500+l_moa_501, 0, moa_502n-moa_504n, l_moa_500+l_moa_501-moa_504n) moa_505,
l_moa_506 moa_506,
moa_507,
l_moa_508 moa_508,
l_moa_506+moa_507+l_moa_508 moa_509,
moa_510n moa_510,
moa_511n moa_511,
moa_510n-moa_511n moa_512,
moa_513n moa_513,
moa_514n moa_514,
moa_515n moa_515,
moa_516n moa_516,
moa_517n moa_517,
moa_518n moa_518,
moa_519n moa_519,
moa_520n moa_520,
moa_521n moa_521,
moa_522n moa_522,
moa_523n moa_523,
moa_524n moa_524,
qty_300,
qty_329
from (select assignment_id,employee_name,
moa_500n,
moa_502n,
moa_504n,
case 
when moa_500n <> 0 and QTY_300 <> 0 and QTY_329 <>0 then round(round(MOA_500n*qty_300/year_days,2)/qty_329,2)
when moa_500n <> 0 and QTY_300 <> 0 and QTY_329 = 0 then round(MOA_500n*qty_300/year_days,2)
else 0
end l_moa_500,
case 
when (moa_500n <> 0 and QTY_300 <> 0 and QTY_329 <>0) and premises='P' then trunc(round(round(MOA_500n*qty_300/year_days,2)/qty_329,2)*0.40,2)
when (moa_500n <> 0 and QTY_300 <> 0 and QTY_329 <>0) and premises='F' then trunc(round(round(MOA_500n*qty_300/year_days,2)/qty_329,2)*0.50,2)
when (moa_500n <> 0 and QTY_300 <> 0 and QTY_329 = 0) and premises='P' then trunc(round(MOA_500n*qty_300/year_days,2)*0.40,2)
when (moa_500n <> 0 and QTY_300 <> 0 and QTY_329 = 0) and premises='F' then trunc(round(MOA_500n*qty_300/year_days,2)*0.50,2)
else 0 
end l_moa_501,
moa_525 + moa_526 + moa_527 + moa_528 + moa_529 + moa_530 + moa_540n l_moa_506,
moa_507,
moa_531 + moa_533 l_moa_508,
moa_510n,
moa_511n,
moa_513n,
moa_514n,
moa_515n,
moa_516n,
moa_517n,
moa_518n,
moa_519n,
moa_520n,
moa_521n,
moa_522n,
moa_523n,
moa_524n,
qty_300,
qty_329,
premises
from 
(select 
paa.assignment_id, ff1.value employee_name,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_500_YA2015_PER_LE_YTD',ff.value),0)) MOA_500n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_502_YA2015_PER_LE_YTD',ff.value),0)) MOA_502n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_504_YA2015_PER_LE_YTD',ff.value),0)) MOA_504n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_525_PER_LE_YTD',ff.value),0)) MOA_525,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_526_PER_LE_YTD',ff.value),0)) MOA_526,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_527_PER_LE_YTD',ff.value),0)) MOA_527,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_528_PER_LE_YTD',ff.value),0)) MOA_528,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_529_PER_LE_YTD',ff.value),0)) MOA_529,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_530_PER_LE_YTD',ff.value),0)) MOA_530,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_540_YA2015_PER_LE_YTD',ff.value),0)) MOA_540n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_531_PER_LE_YTD',ff.value),0)) MOA_531,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_533_PER_LE_YTD',ff.value),0)) MOA_533,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_532_PER_LE_YTD',ff.value),0)) MOA_507,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_510_YA2015_PER_LE_YTD',ff.value),0)) MOA_510n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_511_YA2015_PER_LE_YTD',ff.value),0)) MOA_511n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_513_YA2015_PER_LE_YTD',ff.value),0)) MOA_513n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_514_YA2015_PER_LE_YTD',ff.value),0)) MOA_514n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_515_YA2015_PER_LE_YTD',ff.value),0)) MOA_515n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_516_YA2015_PER_LE_YTD',ff.value),0)) MOA_516n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_517_YA2015_PER_LE_YTD',ff.value),0)) MOA_517n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_518_YA2015_PER_LE_YTD',ff.value),0)) MOA_518n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_519_YA2015_PER_LE_YTD',ff.value),0)) MOA_519n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_520_YA2015_PER_LE_YTD',ff.value),0)) MOA_520n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_521_YA2015_PER_LE_YTD',ff.value),0)) MOA_521n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_MOA_522_YA2015_PER_LE_YTD',ff.value),0)) MOA_522n,
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_524_YA2015_PER_LE_YTD',ff.value),0)) MOA_524n,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_QTY_300_PER_LE_YTD',ff.value),0)) QTY_300,
sum(nvl(decode(fue.user_entity_name, 'X_A8A_QTY_329_PER_LE_YTD',ff.value),0)) QTY_329,
 to_date('31-Dec-'||to_char(:p_basis_year),'DD-Mon-YYYY') - to_date('01-Jan-'||to_char(:p_basis_year),'DD-Mon-YYYY')+1 year_days,
ff2.value premises
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,
     per_all_assignments_f paaf
where fue.user_Entity_id= ff.user_entity_id
and fue.user_entity_name in ('X_A8A_MOA_500_YA2015_PER_LE_YTD',
'X_A8A_MOA_502_YA2015_PER_LE_YTD','X_A8A_MOA_504_YA2015_PER_LE_YTD','X_A8A_QTY_300_PER_LE_YTD','X_A8A_QTY_329_PER_LE_YTD','X_A8A_MOA_525_PER_LE_YTD',
'X_A8A_MOA_526_PER_LE_YTD','X_A8A_MOA_527_PER_LE_YTD','X_A8A_MOA_528_PER_LE_YTD','X_A8A_MOA_529_PER_LE_YTD','X_A8A_MOA_530_PER_LE_YTD','X_A8A_MOA_540_YA2015_PER_LE_YTD',
'X_A8A_MOA_531_PER_LE_YTD','X_A8A_MOA_533_PER_LE_YTD','X_A8A_MOA_532_PER_LE_YTD','X_A8A_MOA_510_YA2015_PER_LE_YTD','X_A8A_MOA_511_YA2015_PER_LE_YTD','X_A8A_MOA_513_YA2015_PER_LE_YTD',
'X_A8A_MOA_514_YA2015_PER_LE_YTD','X_A8A_MOA_515_YA2015_PER_LE_YTD','X_A8A_MOA_516_YA2015_PER_LE_YTD','X_A8A_MOA_517_YA2015_PER_LE_YTD','X_A8A_MOA_518_YA2015_PER_LE_YTD',
'X_A8A_MOA_519_YA2015_PER_LE_YTD','X_A8A_MOA_520_YA2015_PER_LE_YTD','X_A8A_MOA_521_YA2015_PER_LE_YTD','X_A8A_MOA_522_YA2015_PER_LE_YTD',
'X_A8A_MOA_523_YA2015_PER_LE_YTD','X_A8A_MOA_524_YA2015_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 ff2.context1=paa.assignment_action_id
and fue2.user_entity_id=ff2.user_entity_id
and fue2.user_entity_name = 'X_PER_PREMISES'
and ff1.context1=paa.assignment_action_id
and paa.payroll_action_id=:p_archive_action_id
and paa.assignment_id = paaf.assignment_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, ff2.value
order by paaf.person_id)
where moa_523n<>0)