ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F3053869E053B46B63588994 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,9,2))) ,reporting_name Y PAY PAYSG21B_XML US PAY Inland Revenue IR21 Main (Singapore) Application: Payroll Source: Inland Revenue IR21 Main (Singapore) (XML) Short Name: PAYSG21B_XML DB package: PAY_PAYSGI21_XMLP_PKG BI Publisher :business_group_id :cp1_actual_av :cp1_actual_av1 :cp1_address_line1 :cp1_address_line2 :cp1_address_line3 :cp1_av :cp1_av1 :cp1_date_premise_from :cp1_date_premise_from1 :cp1_date_premise_to :cp1_date_premise_to1 :cp1_days_occupying_premise :cp1_days_occupying_premise1 :cp1_ee_paid :cp1_ee_paid1 :cp1_emps_sharing_premise :cp1_emps_sharing_premise1 :cp1_er_paid :cp1_er_paid1 :cp1_fun :cp1_fun1 :cp1_postal_code :cp1_taxable_av :cp1_taxable_av1 :cp2_actual_av :cp2_actual_av1 :cp2_address_line1 :cp2_address_line2 :cp2_address_line3 :cp2_av :cp2_av1 :cp2_date_premise_from :cp2_date_premise_from1 :cp2_date_premise_to :cp2_date_premise_to1 :cp2_days_occupying_premise :cp2_days_occupying_premise1 :cp2_ee_paid :cp2_ee_paid1 :cp2_emps_sharing_premise :cp2_emps_sharing_premise1 :cp2_er_paid :cp2_er_paid1 :cp2_fun :cp2_fun1 :cp2_postal_code :cp2_taxable_av :cp2_taxable_av1 :cp_child_seq :cp_cr_year_amount :cp_date_premise_from :cp_date_premise_from_1 :cp_date_premise_from_2 :cp_date_premise_to :cp_date_premise_to_1 :cp_date_premise_to_2 :cp_days_occupying_premise :cp_days_occupying_premise_1 :cp_days_occupying_premise_2 :cp_emps_sharing_premise :cp_emps_sharing_premise_1 :cp_emps_sharing_premise_2 :cp_ir21_cessation_from :cp_ir21_cessation_from_1 :cp_ir21_cessation_from_2 :cp_ir21_cessation_to :cp_ir21_cessation_to_1 :cp_ir21_cessation_to_2 :cp_ir21a_employ_from :cp_ir21a_employ_from_1 :cp_ir21a_employ_from_2 :cp_ir21a_employ_to :cp_ir21a_employ_to_1 :cp_ir21a_employ_to_2 :cp_ir21a_hotel_basis :cp_ir21a_hotel_basis1 :cp_ir21a_hotel_basis1_prev :cp_ir21a_hotel_basis_prev :cp_ir21a_hotel_taxable :cp_ir21a_hotel_taxable_prev :cp_outstanding_no :cp_outstanding_stocks :cp_outstanding_yes :cp_pr_year_amount :cp_taxable_residence_basis :cp_taxable_residence_basis_1 :cp_taxable_residence_basis_2 :debugflag :lp_basis_year :lp_business_group_id :lp_cr_year_amount :lp_ir21_mode :lp_person_id :lp_pr_year_amount :p_basis_year :p_business_group_id :p_conc_request_id :p_cr_year_amount :p_ir21_mode :p_person_id :p_pr_year_amount :person_id :t_run