Oracle Tax Tables Queries [PDF]

Citation preview

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 c where 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 wh ere 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='49400 0647')))) 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 Id zrb.TAX_RATE_CODE "Tax Code" --mc.segment1 --Tax Description from oe_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 ott where 1=1 and ohl.order_number = &Order_number and ohl.Header_id=oll.header_id



AND ord.organization_id = hou.organization_id and ohl.order_type_id=ott.TRANSACTION_TYPE_ID and hop.organization_id=ohl.org_id and ohl.SOLD_TO_ORG_ID=hca.CUST_ACCOUNT_ID and hp.party_id=hca.party_id and oll.SHIP_FROM_ORG_ID=ord.organization_id and hca.CUST_ACCOUNT_ID=hcas.CUST_ACCOUNT_ID and ohl.SHIP_TO_ORG_ID = hcsu.site_use_id and hcsu.CUST_ACCT_SITE_ID=hcas.CUST_ACCT_SITE_ID and hcas.PARTY_SITE_ID=hps.PARTY_SITE_ID and hps.LOCATION_ID=hlc.LOCATION_ID AND oll.INVENTORY_ITEM_ID=mib.inventory_item_id and oll.SHIP_FROM_ORG_ID=mib.organization_id and zrb.TAX_RATE_ID=opa.TAX_RATE_ID and oll.line_id=opa.line_id AND mics.inventory_item_id = mib.inventory_item_id and mics.organization_id = mib.organization_id and mics.category_set_id = mcs.category_set_id and mics.category_id = mc.category_id and 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 = 1100000124 and 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 Id zrb.TAX_RATE_CODE "Tax Code" --mc.segment1 --Tax Description from oe_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 ott where 1=1 and ohl.order_number = &Order_number and ohl.Header_id=oll.header_id AND ord.organization_id = hou.organization_id and ohl.order_type_id=ott.TRANSACTION_TYPE_ID and hop.organization_id=ohl.org_id and ohl.SOLD_TO_ORG_ID=hca.CUST_ACCOUNT_ID and hp.party_id=hca.party_id and oll.SHIP_FROM_ORG_ID=ord.organization_id and hca.CUST_ACCOUNT_ID=hcas.CUST_ACCOUNT_ID and ohl.INVOICE_TO_ORG_ID = hcsu.site_use_id and hcsu.CUST_ACCT_SITE_ID=hcas.CUST_ACCT_SITE_ID and hcas.PARTY_SITE_ID=hps.PARTY_SITE_ID and hps.LOCATION_ID=hlc.LOCATION_ID AND oll.INVENTORY_ITEM_ID=mib.inventory_item_id and oll.SHIP_FROM_ORG_ID=mib.organization_id and zrb.TAX_RATE_ID=opa.TAX_RATE_ID and oll.line_id=opa.line_id AND mics.inventory_item_id = mib.inventory_item_id and mics.organization_id = mib.organization_id and mics.category_set_id = mcs.category_set_id and mics.category_id = mc.category_id and 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 = 1100000124 and mcst.language = 'US' and mcst.category_set_name like 'VAT%'