Store day tran_seq_no and Item wise Tax detail Query in India Localization

with resa as ( select /*+ materialize */ s.store,s.store_name,vat.vat_region_name, ssd.business_date, sth.tran_seq_no,ssd.day, sth.tran_no,trunc(sth.tran_datetime) tran_date,sti.item, im.item_desc,im.tax_category,tc.tax_category_desc, sti.qty,sti.item_seq_no,s.vat_region, tran_type, sth.status, store_format, unit_retail from store s,vat_region vat, sa_store_day ssd, sa_tran_head sth, sa_tran_item sti, item_master im, tax_category tc where store_format = 1001 and s.store between 999 and 9999 and trunc(sth.tran_datetime) = trunc(sysdate-1) and sth.status = 'P' and sth.tran_type in ('SALE','RETURN')and im.item_level = 2 and s.store = ssd.store and s.vat_region = vat.vat_region and sti.tran_seq_no = sth.tran_seq_no and sti.store = sth.store and sti.day = sth.day and sth.store_day_seq_no = ssd.store_day_seq_no and ssd.store = ssd.store and ssd.day = sth.day and sti.item = im.item and im.tax_category = tc.tax_category ) select store "Store", store_name "Store Name", tran_date "Tran Date", assignment_id "Assignment Id", item_no "Item No", item_desc "Item Desc", tax_category "Tax Category", tax_category_desc "Tax Category Desc", master_bas_tax_code "Master Basic Tax Code", master_bas_tax_rate "Master Basic Tax Rate", master_add_tax_code "Master Add Tax Code", master_add_tax_rate "Master Add Tax Rate", tran_bas_tax_code "Tran Base Tax Code" , tran_bas_tax_rate "Tran Base Tax Rate", tran_add_tax_code "Tran Add Tax Code", tran_add_tax_rate "Tran Add Tax Rate", vat_region "Vat Region", vat_region_name "Vat Region Name" from ( select rownum ro, nvl(a.store,b.store) store, nvl(a.store_name,b.store_name) store_name, nvl(a.transaction_date,b.transaction_date) tran_date, a.assignment_id, nvl(a.item,b.item)item_no, nvl(a.item_desc,b.item_desc)item_desc, nvl(a.tax_category,b.tax_category)tax_category, nvl(a.tax_category_desc,b.tax_category_desc) tax_category_desc, a.vat_code master_tax_code, a.vat_rate master_tax_rate, a.master_bas_tax_code, a.master_bas_tax_rate, a.master_add_tax_code, a.master_add_tax_rate, nvl(a.application_ord,b.igtax_seq_no) tax_application_order, a.application_ord, b.igtax_seq_no, b.igtax_code tran_tax_code, b.igtax_rate tran_tax_rate, b.tran_bas_tax_code, b.tran_bas_tax_rate, b.tran_add_tax_code, b.tran_add_tax_rate, nvl(a.vat_region,b.vat_region) vat_region, nvl(a.vat_region_name,b.vat_region_name) vat_region_name from ( select resa.store, resa.store_name, resa.tran_date transaction_date, resa.tran_no, resa.tran_seq_no, resa.item, resa.item_desc, resa.item_seq_no, resa.tax_category, resa.tax_category_desc, ta.vat_code, case when ((ta.vat_code between '100' and '135') or (ta.vat_code between '150' and '235')) then ta.vat_code end master_bas_tax_code, case when ((ta.vat_code between '100' and '135') or (ta.vat_code between '150' and '235')) then vr.vat_rate end master_bas_tax_rate, case when ((ta.vat_code between '136' and '149') or (ta.vat_code between '236' and '249')) then ta.vat_code end master_add_tax_code, case when ((ta.vat_code between '136' and '149') or (ta.vat_code between '236' and '249')) then vr.vat_rate end master_add_tax_rate, vr.vat_rate, --- ta.assignment_id, ta.application_ord, resa.vat_region, resa.vat_region_name from resa, tax_assignment ta ,vat_code_rates vr where ta.tax_category = resa.tax_category and ta.from_vat_region = resa.vat_region and ta.tax_type in ('31','32') and ta.to_taxpayer_type = 11 and ta.activate_date < resa.tran_date and (ta.inactivate_date is null or ta.inactivate_date > resa.tran_date) and ta.vat_code = vr.vat_code ) a full outer join ( select resa.store, resa.store_name, resa.business_date, resa.tran_date transaction_date, resa.tran_seq_no, resa.tran_no, resa.item_seq_no, resa.item item, resa.item_desc, resa.qty, resa.unit_retail, resa.tax_category, resa.tax_category_desc, stg.igtax_seq_no, stg.qty stg_qty, igtax_code, igtax_rate, case when ((stg.igtax_code between '100' and '135') or (stg.igtax_code between '150' and '235')) then stg.igtax_code end tran_bas_tax_code, case when ((stg.igtax_code between '100' and '135') or (stg.igtax_code between '150' and '235')) then stg.igtax_rate end tran_bas_tax_rate, case when ((stg.igtax_code between '136' and '149') or (stg.igtax_code between '236' and '249')) then stg.igtax_code end tran_add_tax_code, case when ((stg.igtax_code between '136' and '149') or (stg.igtax_code between '236' and '249')) then stg.igtax_rate end tran_add_tax_rate, unit_igtax_amt, resa.vat_region, resa.vat_region_name from resa, sa_tran_igtax stg where stg.tran_seq_no =resa.tran_seq_no and stg.item_seq_no = resa.item_seq_no and stg.store = resa.store and stg.day= resa.day ) b on a.tran_seq_no = b.tran_seq_no and a.tran_no = b.tran_no and a.store = b.store and a.item = b.item and a.item_seq_no = b.item_seq_no and a.application_ord = b.igtax_seq_no where nvl(a.vat_code,'0') <> nvl(b.igtax_code,'0') or (nvl(a.vat_code,'0') = nvl(b.igtax_code,'0') and nvl(a.vat_rate,0) <> nvl(b.igtax_rate,0)) ) group by store, store_name, tran_date, assignment_id, item_no, item_desc, tax_category, tax_category_desc, master_bas_tax_code, master_bas_tax_rate, master_add_tax_code, master_add_tax_rate, tran_bas_tax_code, tran_bas_tax_rate, tran_add_tax_code, tran_add_tax_rate, vat_region, vat_region_name