PAY Inland Revenue Form IR21 (Singapore)

select distinct ppei2.information_type,
hslv.org_information4 RCB_NO,
hou.name STOCK_OPTION_COMPANY_NAME,
fnd_date.canonical_to_date(ppei1.pei_information3) DATE_OF_GRANT,
fnd_date.canonical_to_date(ppei2.pei_information5) DATE_OF_EXERCISE,
ppei2.pei_information3 EXERCISE_PRICE,
ppei1.pei_information2 MARKET_VALUE_GRANT,
ppei2.pei_information4 MARKET_VALUE_EXERCISE,
ppei2.pei_information6 NO_OF_SHARES_ACQ,
DECODE(ppei2.pei_information1, 'E',1,'EE',2,'C',3,null) STOCK_OPTION,
ppf.per_information1 employee_name,
ppf.national_identifier identification_no,
hoi1.org_information5 contact_name,
hoi1.org_information6 contact_number,
hoi2.org_information8 auth_per_name_new,
hoi2.org_information9 auth_per_desig_new,
	PAY_PAYSG21A_XMLP_PKG.cf_gross_amt_not_tax_exemptfor(DECODE ( ppei2.pei_information1 , 'E' , 1 , 'EE' , 2 , 'C' , 3 , null ), ppei2.pei_information4, ppei2.pei_information3, ppei2.pei_information6, ppei1.pei_information2) CF_1,
	PAY_PAYSG21A_XMLP_PKG.CP_1_p CP_1,
	PAY_PAYSG21A_XMLP_PKG.CP_2_p CP_2,
	PAY_PAYSG21A_XMLP_PKG.CP_3_p CP_3,
	PAY_PAYSG21A_XMLP_PKG.CP_4_p CP_4,
	PAY_PAYSG21A_XMLP_PKG.CP_5_p CP_5,
	PAY_PAYSG21A_XMLP_PKG.CP_6_p CP_6,
	PAY_PAYSG21A_XMLP_PKG.CP_7_p CP_7
from per_people_extra_info ppei1,
per_people_extra_info ppei2,
hr_organization_units hou,
hr_soft_coding_keyflex hsc,
hr_organization_information hoi1,
hr_organization_information hoi2,
per_people_f ppf,
per_assignments_f paf,
hr_sg_leg_ent_v hslv,
hr_lookups hl
where to_char(ppei1.person_extra_info_id) = ppei2.pei_information2
and hslv.org_information15=hl.lookup_code
and hou.organization_id = ppei1.pei_information1
and hl.lookup_type = 'SG_ER_PAYER_ID'
and ppei2.information_type = 'HR_STOCK_EXERCISE_SG'
and ppei1.information_type = 'HR_STOCK_GRANT_SG'
and ppei2.person_id = ppei1.person_id
and ppei1.person_id = ppf.person_id
and ppf.person_id = paf.person_id
and ppf.person_id = :p_person_id
and paf.effective_start_date = (select max(paf1.effective_start_date)
    from per_assignments_f paf1
    where paf1.person_id = paf.person_id
    and     paf1.assignment_id = paf.assignment_id
    and     paf1.assignment_type = 'E'
    and     paf1.effective_start_date <= to_date('31/12/'||:p_basis_year,'DD/MM/YYYY')
    and paf1.effective_end_date >= to_date('01/01/'||:p_basis_year,'DD/MM/YYYY')
    and paf1.primary_flag = 'Y')
and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1=hoi1.organization_id 
and hslv.organization_id = hou.organization_id
and hoi1.organization_id = hoi2.organization_id
and hoi1.org_information_context = 'SG_LEGAL_ENTITY'
and hoi2.org_information_context = 'SG_LE_IRAS'
and hl.meaning = 'ROC'
and hl.application_id = 800
and to_char(to_date(ppei2.pei_information5,'YYYY/MM/DD HH24:MI:SS'),'YYYY') = :p_basis_year
order by STOCK_OPTION