PAY A8B Summary

➜ Back to Library
Description
Categories: BI Publisher
Application: Payroll
Source: A8B Summary Report
Short Name: PYSGA8B_XML
DB package:
Run PAY A8B Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
select employee_name,
moa_304,
moa_305,
moa_351,
moa_352,
moa_317,
moa_318,
moa_319,
moa_353,
moa_354,
moa_355,
moa_337,
moa_338,
moa_339,
moa_356,
moa_357,
moa_358,
moa_346,
moa_347,
moa_348,
MOA_305+MOA_319+MOA_339 MOA_601,
MOA_352+MOA_355+MOA_358+MOA_348 MOA_602,
MOA_304+MOA_318+MOA_338 MOA_603,
MOA_351+MOA_354+MOA_357+MOA_347 MOA_604
from (select employee_name,
assignment_id,
sum(case when stock_option='ESOP' and (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then not_qual_amount
else 0 end) moa_304,
sum(case when stock_option='ESOP' and (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then not_qual_amount
else 0 end) moa_305,
sum(case when stock_option='ESOP' and not (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then not_qual_amount
else 0 end) moa_351,
sum(case when stock_option='ESOP' and not (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then not_qual_amount
else 0 end) moa_352,
sum(case when stock_option='EESOP' and (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then qual_amount
else 0 end) moa_317,
sum(case when stock_option='EESOP' and (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then not_qual_amount
else 0 end) moa_318,
sum(case when stock_option='EESOP' and (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then gained
else 0 end) moa_319,
sum(case when stock_option='EESOP' and not (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then qual_amount
else 0 end) moa_353,
sum(case when stock_option='EESOP' and not (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then not_qual_amount
else 0 end) moa_354,
sum(case when stock_option='EESOP' and not (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then gained
else 0 end) moa_355,
sum(case when stock_option='CSOP' and (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then qual_amount
else 0 end) moa_337,
sum(case when stock_option='CSOP' and (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then not_qual_amount
else 0 end) moa_338,
sum(case when stock_option='CSOP' and (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then gained
else 0 end) moa_339,
sum(case when stock_option='CSOP' and not (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then qual_amount
else 0 end) moa_356,
sum(case when stock_option='CSOP' and not (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then not_qual_amount
else 0 end) moa_357,
sum(case when stock_option='CSOP' and not (to_date(grant_date,'YYYY/MM/DD')<to_date('2003/01/01','YYYY/MM/DD') and grant_type='ESOP') then gained
else 0 end) moa_358,
sum(case when stock_option='NSOP' then qual_amount
else 0 end) moa_346,
sum(case when stock_option='NSOP' then not_qual_amount
else 0 end) moa_347,
sum(case when stock_option='NSOP' then gained
else 0 end) moa_348
from 
(
select 
paa.assignment_id, paaf.person_id, ff1.value employee_name,
decode(pei.pei_information1,'E','ESOP','EE','EESOP','C','CSOP','NSOP') stock_option,
decode(pei2.pei_information5,'P','ESOP','ESOW') grant_type,
decode(pei2.pei_information3, null, null, to_char(fnd_date.canonical_to_date(pei2.pei_information3),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(pei2.pei_information3),'MM')||'/'||to_char(fnd_date.canonical_to_date(pei2.pei_information3),'DD')) grant_date,
to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(pei.pei_information5),'MM')||'/'||to_char(fnd_date.canonical_to_date(pei.pei_information5),'DD') exercise_date,
case 
when pei.pei_information1='E' then 0
else 
trunc((sum(nvl(decode(fue.user_entity_name,'X_A8B_MK_EXER_VALUE',ff.value),0))-sum(nvl(decode(fue.user_entity_name,'X_A8B_MK_GRANT_VALUE',ff.value),0)))*sum(nvl(decode(fue.user_entity_name,'X_A8B_SHARES_ACQUIRED',ff.value),0)),2)
end qual_amount,
case 
when pei.pei_information1='E' then 
trunc((sum(nvl(decode(fue.user_entity_name,'X_A8B_MK_EXER_VALUE',ff.value),0))-sum(nvl(decode(fue.user_entity_name,'X_A8B_EXER_PRICE',ff.value),0)))*sum(nvl(decode(fue.user_entity_name,'X_A8B_SHARES_ACQUIRED',ff.value),0)),2) 
else 
trunc((sum(nvl(decode(fue.user_entity_name,'X_A8B_MK_GRANT_VALUE',ff.value),0))-sum(nvl(decode(fue.user_entity_name,'X_A8B_EXER_PRICE',ff.value),0)))*sum(nvl(decode(fue.user_entity_name,'X_A8B_SHARES_ACQUIRED',ff.value),0)),2)
end not_qual_amount,
case
when pei.pei_information1='E' then 
trunc((sum(nvl(decode(fue.user_entity_name,'X_A8B_MK_EXER_VALUE',ff.value),0))-sum(nvl(decode(fue.user_entity_name,'X_A8B_MK_GRANT_VALUE',ff.value),0)))*sum(nvl(decode(fue.user_entity_name,'X_A8B_SHARES_ACQUIRED',ff.value),0)),2)+
trunc((sum(nvl(decode(fue.user_entity_name,'X_A8B_MK_EXER_VALUE',ff.value),0))-sum(nvl(decode(fue.user_entity_name,'X_A8B_EXER_PRICE',ff.value),0)))*sum(nvl(decode(fue.user_entity_name,'X_A8B_SHARES_ACQUIRED',ff.value),0)),2) 
else 
trunc((sum(nvl(decode(fue.user_entity_name,'X_A8B_MK_EXER_VALUE',ff.value),0))-sum(nvl(decode(fue.user_entity_name,'X_A8B_MK_GRANT_VALUE',ff.value),0)))*sum(nvl(decode(fue.user_entity_name,'X_A8B_SHARES_ACQUIRED',ff.value),0)),2)+
trunc((sum(nvl(decode(fue.user_entity_name,'X_A8B_MK_GRANT_VALUE',ff.value),0))-sum(nvl(decode(fue.user_entity_name,'X_A8B_EXER_PRICE',ff.value),0)))*sum(nvl(decode(fue.user_entity_name,'X_A8B_SHARES_ACQUIRED',ff.value),0)),2)
end gained
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 ac2,
     ff_archive_item_contexts ac3,
     per_people_extra_info  pei,
     per_people_extra_info  pei2
where fue.user_Entity_id= ff.user_entity_id
and fue.user_entity_name in (
'X_A8B_MK_EXER_VALUE',
'X_A8B_EXER_PRICE',
'X_A8B_SHARES_ACQUIRED',
'X_A8B_MK_GRANT_VALUE')
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.context1 =  ff1.context1
and ff.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
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 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) 
and pei.person_id=paaf.person_id
and pei.person_extra_info_id=ac3.context
and     pei.information_type = 'HR_STOCK_EXERCISE_SG'
and     pei.person_id = pei2.person_id
and     pei.pei_information2 = pei2.person_extra_info_id
and     pei2.information_type = 'HR_STOCK_GRANT_SG'
group by paa.assignment_id, paaf.person_id, ff1.value,
pei.pei_information1,
pei.pei_information5,
pei2.pei_information5,
pei2.pei_information3,
pei2.pei_information4,
ac3.context
order by paa.assignment_id, decode(pei.pei_information1,'E',1,'EE',2,'C',3,'N',4,0), ac3.context
)
group by employee_name,assignment_id)
where MOA_305+MOA_319+MOA_339<>0 or
MOA_352+MOA_355+MOA_358+MOA_348<>0 or
MOA_304+MOA_318+MOA_338 <>0 or
MOA_351+MOA_354+MOA_357+MOA_347<>0
order by assignment_id
Download
Blitz Report™

Blitz Report™ provides multiple benefits: