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 |