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
            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
Download
Blitz Report™

Blitz Report™ provides multiple benefits: