PAY A8B Summary
Description
- Categories
- BI Publisher
- Application
- Payroll
- Source
- A8B Summary Report
- Short Name
- PYSGA8B_XML
DB package:
SQL 138 lines
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 |