PAY A8A Summary
Description
Categories: BI Publisher
Application: Payroll
Source: A8A Summary Report
Short Name: PYSGA8A_XML
DB package:
Source: A8A Summary Report
Short Name: PYSGA8A_XML
DB package:
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) |