PAY Inland Revenue IR21 Main (Singapore)
Description
Categories: BI Publisher
Application: Payroll
Source: Inland Revenue IR21 Main (Singapore) (XML)
Short Name: PAYSG21B_XML
DB package: PAY_PAYSGI21_XMLP_PKG
Source: Inland Revenue IR21 Main (Singapore) (XML)
Short Name: PAYSG21B_XML
DB package: PAY_PAYSGI21_XMLP_PKG
Run
PAY Inland Revenue IR21 Main (Singapore) and other Oracle EBS reports with Blitz Report™ on our demo environment
select --business_group_id, business_group_id business_group_id7, --tax_unit_id, /*removed for bug 13574362 */ --person_id, person_id person_id7, 1 balance_type_id, reporting_name, balance_name, decode(balance_name,'IR21A1_B1',1,'IR21A1_B5',2,'IR21A1_B12',2, 'IR21A1_B99',3,'IR21A1_B7',6,'IR21A1_B8A',7, 'IR21A1_B8B',8,'IR21A1_B100',9,'IR21A1_B9',10, 'IR21A1_B19',11,'IR21A1_B22',12,'IR21A1_B101',13,'IR21A1_B29',14,'IR21A1_B21',15, 'IR21A1_C5',16,'IR21A1_C98',17,'IR21A1_C2',18,'IR21A1_C3',19,'IR21A1_C4',20,21) item_name, sum(per_ytd_bal) per_ytd_bal, sum(per_ytd_bal_1) per_ytd_bal_1, sum( ir21_income_basis) ir21_income_basis, sum( ir21_income_basis_1) ir21_income_basis_1, sum( ir21_others_basis) ir21_others_basis, sum( ir21_others_basis_1) ir21_others_basis_1, sum(ir21_deductions_basis) ir21a_deductions_basis, sum(ir21_deductions_basis_1) ir21a_deductions_basis_1, sum(ir21a_furniture_basis) ir21a_furniture_basis, sum(ir21a_furniture_basis_1) ir21a_furniture_basis_1, sum(ir21a_furniture_basis_sub) ir21a_furniture_basis_sub, sum(ir21a_furniture_basis_sub1) ir21a_furniture_basis_sub1, sum( ir21a_hotel_basis) ir21a_hotel_basis, sum( ir21a_hotel_basis_1) ir21a_hotel_basis_1, sum( ir21a_others_basis) ir21a_others_basis, sum( ir21a_others_basis_1) ir21a_others_basis_1, sum(a8a_moa_501_basis) a8a_moa_501_basis, sum(a8a_moa_501_basis_1) a8a_moa_501_basis_1, sum(a8a_moa_502_basis) a8a_moa_502_basis, sum(a8a_moa_502_basis_1) a8a_moa_502_basis_1 from ( select eoy.business_group_id, -- eoy.tax_unit_id, /* removed for bug 14574362 */ eoy.person_id, 1 balance_type_id, decode(eoy.balance_name, 'IR21A1_B6','IR21A1_B99','IR21A1_B16','IR21A1_B99','IR21A1_B3','IR21A1_B100','IR21A1_B23','IR21A1_B100','IR21A1_B24','IR21A1_B101','IR21A1_B25','IR21A1_B101','IR21A1_B26','IR21A1_B101','IR21A1_B27','IR21A1_B101', 'IR21A1_B28','IR21A1_B101','IR21A1_C6','IR21A1_C98','IR21A1_C7','IR21A1_C98' ,eoy.balance_name) balance_name, decode(eoy.balance_name,'IR21A1_B6','Washing Machine/Dryer/Dish Washer', 'IR21A1_B16','Washing Machine/Dryer/Dish Washer','IR21A1_B3', 'TV/Radio/Amplifier/Hi-Fi/Electric Guitar', 'IR21A1_B23' ,'TV/Radio/Amplifier/Hi-Fi/Electric Guitar', 'IR21A1_B24', 'PUB/Telephone/Pager/Suitcase/Golf Bag & Acc/Camera/Servant', 'IR21A1_B25','PUB/Telephone/Pager/Suitcase/Golf Bag & Acc/Camera/Servant', 'IR21A1_B26','PUB/Telephone/Pager/Suitcase/Golf Bag & Acc/Camera/Servant', 'IR21A1_B27','PUB/Telephone/Pager/Suitcase/Golf Bag & Acc/Camera/Servant', 'IR21A1_B28','PUB/Telephone/Pager/Suitcase/Golf Bag & Acc/Camera/Servant', 'IR21A1_C6','Wife/Child > 20yrs','IR21A1_C7','Wife/Child > 20yrs', eoy.reporting_name) reporting_name, sum(decode(year, :lp_basis_year, per_ytd_bal, 0)) per_ytd_bal, sum(decode(year, :lp_basis_year - 1, per_ytd_bal, 0)) per_ytd_bal_1, sum(decode(year, :lp_basis_year, decode(balance_name, 'IR21_E1', per_ytd_bal, 'IR21_E2A', per_ytd_bal, 'IR21_E2B', per_ytd_bal, 'IR21_E3', per_ytd_bal, 0 ), 0 )) ir21_income_basis, sum(decode(year, :lp_basis_year - 1, decode(balance_name, 'IR21_E1', per_ytd_bal, 'IR21_E2A', per_ytd_bal, 'IR21_E2B', per_ytd_bal, 'IR21_E3', per_ytd_bal, 0 ), 0 )) ir21_income_basis_1, sum(decode(year, :lp_basis_year, decode(balance_name, 'IR21_E4A', per_ytd_bal, 'IR21_E4B', per_ytd_bal, 'IR21_E4C', per_ytd_bal, 'IR21_E4D', per_ytd_bal, 'IR21_E4E', per_ytd_bal, 'IR21_E4F', per_ytd_bal, 'IR21_E4G', per_ytd_bal, 'IR21_E4H', per_ytd_bal, 0 ), 0 )) ir21_others_basis, sum(decode(year, :lp_basis_year - 1, decode(balance_name, 'IR21_E4A', per_ytd_bal, 'IR21_E4B', per_ytd_bal, 'IR21_E4C', per_ytd_bal, 'IR21_E4D', per_ytd_bal, 'IR21_E4E', per_ytd_bal, 'IR21_E4F', per_ytd_bal, 'IR21_E4G', per_ytd_bal, 'IR21_E4H', per_ytd_bal, 0 ), 0 )) ir21_others_basis_1, sum(decode(year, :lp_basis_year, decode(balance_name, 'IR21_E5A', per_ytd_bal, 'IR21_E6', per_ytd_bal, 'IR21_E7', per_ytd_bal, 0 ), 0 )) ir21_deductions_basis, sum(decode(year, :lp_basis_year - 1, decode(balance_name, 'IR21_E5A', per_ytd_bal, 'IR21_E6', per_ytd_bal, 'IR21_E7',per_ytd_bal, 0 ), 0 )) ir21_deductions_basis_1, sum(decode(year, :lp_basis_year, decode(balance_name, 'IR21A1_B1', per_ytd_bal, 'IR21A1_B3', per_ytd_bal, 'IR21A1_B5', per_ytd_bal, 'IR21A1_B6', per_ytd_bal, 'IR21A1_B7', per_ytd_bal, 'IR21A1_B8A', per_ytd_bal, 'IR21A1_B8B', per_ytd_bal, 'IR21A1_B9', per_ytd_bal, 'IR21A1_B12', per_ytd_bal, 'IR21A1_B16', per_ytd_bal, 'IR21A1_B19', per_ytd_bal, 'IR21A1_B22', per_ytd_bal, 'IR21A1_B23',per_ytd_bal, 0 ), 0 )) ir21a_furniture_basis, sum(decode(year, :lp_basis_year - 1, decode(balance_name, 'IR21A1_B1', per_ytd_bal, 'IR21A1_B3', per_ytd_bal, 'IR21A1_B5', per_ytd_bal, 'IR21A1_B6', per_ytd_bal, 'IR21A1_B7', per_ytd_bal, 'IR21A1_B8A', per_ytd_bal, 'IR21A1_B8B', per_ytd_bal, 'IR21A1_B9', per_ytd_bal, 'IR21A1_B12', per_ytd_bal, 'IR21A1_B16', per_ytd_bal, 'IR21A1_B19', per_ytd_bal, 'IR21A1_B22', per_ytd_bal, 'IR21A1_B23',per_ytd_bal, 0 ), 0 )) ir21a_furniture_basis_1, sum(decode(year, :lp_basis_year , decode(balance_name, 'IR21A1_B21', per_ytd_bal, 'IR21A1_B24', per_ytd_bal, 'IR21A1_B25', per_ytd_bal, 'IR21A1_B26', per_ytd_bal, 'IR21A1_B27', per_ytd_bal, 'IR21A1_B28', per_ytd_bal, 'IR21A1_B29', per_ytd_bal, 0 ), 0 )) ir21a_furniture_basis_sub, sum(decode(year, :lp_basis_year - 1, decode(balance_name, 'IR21A1_B21', per_ytd_bal, 'IR21A1_B24', per_ytd_bal, 'IR21A1_B25', per_ytd_bal, 'IR21A1_B26', per_ytd_bal, 'IR21A1_B27', per_ytd_bal, 'IR21A1_B28', per_ytd_bal, 'IR21A1_B29', per_ytd_bal, 0 ), 0 )) ir21a_furniture_basis_sub1, sum(decode(year, :lp_basis_year, decode(balance_name, 'IR21A1_C2', per_ytd_bal, 'IR21A1_C3', per_ytd_bal, 'IR21A1_C4', per_ytd_bal, 'IR21A1_C5', per_ytd_bal, 'IR21A1_C6', per_ytd_bal, 'IR21A1_C7', per_ytd_bal, 'A8A_MOA_539', per_ytd_bal, 0 ), 0 )) ir21a_hotel_basis, sum(decode(year, :lp_basis_year - 1, decode(balance_name, 'IR21A1_C2', per_ytd_bal, 'IR21A1_C3', per_ytd_bal, 'IR21A1_C4', per_ytd_bal, 'IR21A1_C5', per_ytd_bal, 'IR21A1_C6', per_ytd_bal, 'IR21A1_C7', per_ytd_bal, 'A8A_MOA_539', per_ytd_bal, 0 ), 0 )) ir21a_hotel_basis_1, sum(decode(year, :lp_basis_year, decode(balance_name, 'IR21A1_D1', per_ytd_bal, 'IR21A1_D2', per_ytd_bal, 'IR21A1_D3', per_ytd_bal, 'IR21A1_D4', per_ytd_bal, 'IR21A1_D5', per_ytd_bal, 'IR21A1_D6', per_ytd_bal, 'IR21A1_D7', per_ytd_bal, 'IR21A1_D8', per_ytd_bal, 'IR21A1_D9', per_ytd_bal, 'IR21A1_D10', per_ytd_bal, 'IR21A1_D11', per_ytd_bal, 0 ), 0 )) ir21a_others_basis, sum(decode(year, :lp_basis_year - 1, decode(balance_name, 'IR21A1_D1', per_ytd_bal, 'IR21A1_D2', per_ytd_bal, 'IR21A1_D3', per_ytd_bal, 'IR21A1_D4', per_ytd_bal, 'IR21A1_D5', per_ytd_bal, 'IR21A1_D6', per_ytd_bal, 'IR21A1_D7', per_ytd_bal, 'IR21A1_D8', per_ytd_bal, 'IR21A1_D9', per_ytd_bal, 'IR21A1_D10', per_ytd_bal, 'IR21A1_D11', per_ytd_bal, 0 ), 0 )) ir21a_others_basis_1, sum(decode(year, :lp_basis_year, decode(balance_name, 'A8A_MOA_501', per_ytd_bal, 0 ), 0 )) a8a_moa_501_basis, sum(decode(year, :lp_basis_year - 1, decode(balance_name, 'A8A_MOA_501', per_ytd_bal, 0 ), 0 )) a8a_moa_501_basis_1, sum(decode(year, :lp_basis_year, decode(balance_name, 'A8A_MOA_502', per_ytd_bal, 0 ), 0 )) a8a_moa_502_basis, sum(decode(year, :lp_basis_year - 1, decode(balance_name, 'A8A_MOA_502', per_ytd_bal, 0 ), 0 )) a8a_moa_502_basis_1 from pay_sg_eoy_balances_v eoy where eoy.effective_date between to_date('01-01-'||to_char(:lp_basis_year - 1),'dd-mm-yyyy') and to_date('31-12-'||to_char(:lp_basis_year),'dd-mm-yyyy') group by eoy.business_group_id, -- eoy.tax_unit_id,/* removed for bug 13574362 */ eoy.person_id, eoy.balance_type_id, eoy.balance_name, eoy.reporting_name ) -- and business_group_id=:business_group_id WHERE business_group_id=:business_group_id and person_id=:person_id -- and tax_unit_id=:organization_id/* removed for bug 13574362 */ group by business_group_id, -- tax_unit_id, /* removed for bug 13574362 */ person_id, reporting_name, balance_name order by item_name , decode(instr(balance_name,'IR21A1_D'),1, to_number(substr(balance_name, |