Common tables for EBtax queries:

1) Tax Regimes  ZX_RATES_B

2)Taxes ZX_TAXES_B

3)Tax Statuses ZX_STATUSES_B

4)Tax Jurisdictions ZX_JURISDICTIONS_B

5)Tax Rates ZX_RATES_B--Base table(contains tax codes and rate ids) ZX_RATES_TL--(Tax Descriptions are stored here)

6)Tax Rules ZX_RULES_B

7)Table for tax accounts ZX_ACCOUNTS

8)table for factor sets ZX_FC_CODES_B

9)Table for tax conditions ZX_CONDITIONS

10)Table to identify taxes on Transactions or Invoices  ZX_LINES

Query for identifying VAT category of the items:

SELECT DISTINCT hop.NAME operating_unit, ogd.organization_code inv_org,  mib.segment1 item, mib.description, mib.organization_id,  mcs.category_set_name, mc.segment1 classification  FROM mtl_category_sets mcs,  mtl_item_categories mics,  mtl_system_items_b mib,  mtl_categories mc,  org_organization_definitions ogd,  hr_operating_units hop  WHERE 1 = 1  AND mcs.category_set_id = 1100000124  AND mcs.category_set_id = mics.category_set_id  AND mc.category_id = mics.category_id  AND mics.inventory_item_id = mib.inventory_item_id  AND mib.organization_id = ogd.organization_id  AND ogd.operating_unit = hop.organization_id  AND hop.organization_id = 3051

Query to identify the customer site PFCC based on Transaction number:

select c.party_site_number,b.CLASS_CATEGORY,b.class_code from ZX_PARTY_TAX_PROFI

 

LE a,HZ_CODE_ASSIGNMENTS b,HZ_PARTY_SITES cwhere a.PARTY_TAX_PROFILE_ID=b.OWNER_TABLE_ID  and c.party_site_id=a.PARTY_ID  and c.party_site_number in (select party_site_number from hz_party_sites where party_site_id in(select party_site_id from hz_cust_acct_sites_all where cust_acct_site_id in(select cust_acct_site_id from hz_cust_site_uses_all where site_use_id in (select BILL_TO_SITE_USE_ID from apps.ra_customer_trx_all where trx_number='494000647'))))

Query to identify tax rates,product classification details using Order number

For Items PRODUCT and PROD-INST:

select ohl.order_number "order number",oll.line_number "Order Line Number",oll.line_id "Order Line Id",hop.name "Ship Ou",ott.NAME "Transaction Type", --oll.SHIP_FROM_ORG_ID, --oll.INVENTORY_ITEM_ID,oll.FLOW_STATUS_CODE "Flow",oll.ORDERED_ITEM "Item Number",oll.PRICING_QUANTITY "Ordered Quantity",mcst.CATEGORY_SET_NAME "Item Classification",mc.segment1 "Classification",hp.PARTY_NAME "Customer",ord.ORGANIZATION_CODE "Warehouse",hou.COUNTRY "Ship From Country",hlc.COUNTRY "Ship To Country",hcsu.TAX_REFERENCE "Ship To Vat Id",--Bill To Vat Idzrb.TAX_RATE_CODE "Tax Code"--mc.segment1--Tax Descriptionfromoe_order_headers_all ohl,oe_order_lines_all oll,hr_operating_units hop,hz_parties hp,org_organization_definitions ord,hz_locations hlc,hz_cust_site_uses_all hcsu,hz_cust_accounts_all hca,hz_cust_acct_sites_all hcas,hz_party_sites hps,zx_rates_b zrb,oe_price_adjustments opa,HR_ORGANIZATION_UNITS_V hou,mtl_system_items_b mib,mtl_category_sets mcs,mtl_item_categories mics,mtl_categories_b mc,mtl_category_sets_tl mcst,oe_transaction_types_tl ottwhere1=1and ohl.order_number = &Order_numberand ohl.Header_id=oll.header_id

 

AND ord.organization_id = hou.organization_idand ohl.order_type_id=ott.TRANSACTION_TYPE_IDand hop.organization_id=ohl.org_idand ohl.SOLD_TO_ORG_ID=hca.CUST_ACCOUNT_IDand hp.party_id=hca.party_idand oll.SHIP_FROM_ORG_ID=ord.organization_idand hca.CUST_ACCOUNT_ID=hcas.CUST_ACCOUNT_IDand ohl.SHIP_TO_ORG_ID = hcsu.site_use_idand hcsu.CUST_ACCT_SITE_ID=hcas.CUST_ACCT_SITE_IDand hcas.PARTY_SITE_ID=hps.PARTY_SITE_IDand hps.LOCATION_ID=hlc.LOCATION_IDAND oll.INVENTORY_ITEM_ID=mib.inventory_item_idand oll.SHIP_FROM_ORG_ID=mib.organization_idand zrb.TAX_RATE_ID=opa.TAX_RATE_IDand oll.line_id=opa.line_idAND mics.inventory_item_id = mib.inventory_item_idand mics.organization_id = mib.organization_idand mics.category_set_id = mcs.category_set_idand mics.category_id = mc.category_idand mics.category_id = mc.category_id--and mc.segment1 like 'PROD%'and mcs.category_set_id=mcst.category_set_id--and mcs.category_set_id = 1100000124and mcst.language = 'US'and mcst.category_set_name like 'VAT%'

FOR Items SERVICE-CE and SERVICE-ST:

select ohl.order_number "order number",oll.line_number "Order Line Number",oll.line_id "Order Line Id",hop.name "Ship Ou",ott.NAME "Transaction Type", --oll.SHIP_FROM_ORG_ID, --oll.INVENTORY_ITEM_ID,oll.FLOW_STATUS_CODE "Flow",oll.ORDERED_ITEM "Item Number",oll.PRICING_QUANTITY "Ordered Quantity",mcst.CATEGORY_SET_NAME "Item Classification",mc.segment1 "Classification",hp.PARTY_NAME "Customer",ord.ORGANIZATION_CODE "Warehouse",hou.COUNTRY "Ship From Country",hlc.COUNTRY "Ship To Country",hcsu.TAX_REFERENCE "Bill To Vat Id",--Bill To Vat Idzrb.TAX_RATE_CODE "Tax Code"--mc.segment1--Tax Descriptionfromoe_order_headers_all ohl,oe_order_lines_all oll,hr_operating_units hop,hz_parties hp,org_organization_definitions ord,hz_locations hlc,hz_cust_site_uses_all hcsu,hz_cust_accounts_all hca,hz_cust_acct_sites_all hcas,hz_party_sites hps,

 

zx_rates_b zrb,oe_price_adjustments opa,HR_ORGANIZATION_UNITS_V hou,mtl_system_items_b mib,mtl_category_sets mcs,mtl_item_categories mics,mtl_categories_b mc,mtl_category_sets_tl mcst,oe_transaction_types_tl ottwhere1=1and ohl.order_number = &Order_numberand ohl.Header_id=oll.header_idAND ord.organization_id = hou.organization_idand ohl.order_type_id=ott.TRANSACTION_TYPE_IDand hop.organization_id=ohl.org_idand ohl.SOLD_TO_ORG_ID=hca.CUST_ACCOUNT_IDand hp.party_id=hca.party_idand oll.SHIP_FROM_ORG_ID=ord.organization_idand hca.CUST_ACCOUNT_ID=hcas.CUST_ACCOUNT_IDand ohl.INVOICE_TO_ORG_ID = hcsu.site_use_idand hcsu.CUST_ACCT_SITE_ID=hcas.CUST_ACCT_SITE_IDand hcas.PARTY_SITE_ID=hps.PARTY_SITE_IDand hps.LOCATION_ID=hlc.LOCATION_IDAND oll.INVENTORY_ITEM_ID=mib.inventory_item_idand oll.SHIP_FROM_ORG_ID=mib.organization_idand zrb.TAX_RATE_ID=opa.TAX_RATE_IDand oll.line_id=opa.line_idAND mics.inventory_item_id = mib.inventory_item_idand mics.organization_id = mib.organization_idand mics.category_set_id = mcs.category_set_idand mics.category_id = mc.category_idand mics.category_id = mc.category_id--and mc.segment1 like 'PROD%'and mcs.category_set_id=mcst.category_set_id--and mcs.category_set_id = 1100000124and mcst.language = 'US'and mcst.category_set_name like 'VAT%'