ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F3533869E053B46B63588994 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) Y PAY PYSGIR8A_XML US PAY IR8A Summary Application: Payroll Source: IR8A Summary Report Short Name: PYSGIR8A_XML DB package: BI Publisher :p_archive_action_id :p_assignment_id :p_assignment_set_id :p_basis_year