The blog provides the commonly used SQL to retrieve the EBTAX Setup details ,  SQL to get EBTAX Transactional data ,  SQL to get Jurisdiction related Tax details , SQL to get Party Fiscal classification code details.  These SQL could be helpful to Functional / Consultants to understand the EBTAX related issues / errors.

EBTAX SETUP Tables in Oracle Apps R12 

The below given are the Oracle Apps R12 tables for storing Setup information related to EBTAX. The EBTAX Setup information can be retrieved using the below given SQL queries.

• ZX_REGIMES_B : Contains the Tax Regime Data ( Tax Name) 

SELECT *
FROM ZX_REGIMES_B
WHERE TAX_REGIME_CODE = '&TAX_REGIME_CODE';

• ZX_TAXES_B: Contains the Tax  setup information for a particular Tax Regime

SELECT *
FROM ZX_TAXES_B
WHERE DECODE('&TAX_NAME',NULL,'XXX',TAX) = NVL('&TAX_NAME','XXX')
AND TAX_REGIME_CODE = '&TAX_REGIME_CODE';

• ZX_STATUS_B: Contains the Tax Status related setup information

SELECT *
FROM ZX_STATUS_B
WHERE TAX = '&TAX_NAME'
AND TAX_REGIME_CODE = '&TAX_REGIME_CODE';

• ZX_RATES_B : Contains the Tax Rate related setup information

SELECT *
FROM ZX_RATES_B
WHERE TAX = '&TAX_NAME'
AND TAX_REGIME_CODE = '&TAX_REGIME_CODE';

• ZX_JURISDICTIONS_B: Contains the Jurisdiction level setup information

SELECT *
FROM ZX_JURISDICTIONS_B
WHERE DECODE('&TAX_NAME',NULL,'XXX',TAX) = NVL('&TAX_NAME','XXX')
AND TAX_REGIME_CODE = '&TAX_REGIME_CODE';

• ZX_RULES_B : Contains Tax Rule Setup information

SELECT *
FROM ZX_RULES_B
WHERE TAX = '&TAX_NAME'
AND TAX_REGIME_CODE = '&TAX_REGIME_CODE';

EBTAX Transactional Tables in Oracle Apps R12 

The below given are the commonly used EBTAX Transactional Tables 

• ZX_LINES: contains the tax lines for associated with PO/Release schedules. The TRX_ID ( Transaction Id) is mapped to the PO_HEADERS_ALL.PO_HEADER_ID.
  The TRX_LINE_ID ( Transaction Line Id) is mapped to the PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
• ZX_REC_NREC_DIST: contains the tax distributions for associated with PO/Release distributions.
  The TRX_ID ( Transaction Id) is mapped to the PO_HEADERS_ALL.PO_HEADER_ID.
  The TRX_LINE_ID ( Transaction Line Id) is mapped to the PO_LINE_LOCATIONS_ALL. LINE_LOCATION_ID.  The TRX_LINE_DIST_ID ( Transaction Line Distribution Id) is mapped to the PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID. If RECOVERABLE_FLAG =’Y’ , then the distribution is recoverable and columns RECOVERY_TYPE_CODE and RECOVERY_RATE_CODE will be populated also.
• PO_REQ_DISTRIBUTIONS_ALL: contains the tax distributions for associated with Requisition distribution Where RECOVERABLE_TAX indicates the Recoverable tax amount and NONRECOVERABLE_TAX indicates the Non Recoverable tax amount
• ZX_LINES_DET_FACTORS: contains the information of the tax line transaction for both the requisitions as well as the purchase orders/releases . The TRX_ID ( Transaction Id) is mapped to the PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID
  The TRX_LINE_ID ( Transaction Line Id) is mapped to the PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID or PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID

EBTAX Party Fiscal Classification Code SQL Query

SELECT   HZ_PARTY_SITES_TBL.PARTY_ID, HZ_PARTY_SITES_TBL.PARTY_NAME, HPS.PARTY_SITE_ID, HPS.PARTY_SITE_NAME, ZP.PARTY_TAX_PROFILE_ID
FROM     ZX_PARTY_TAX_PROFILE ZP,
         HZ_PARTY_SITES HPS,
         HZ_PARTIES HZ_PARTY_SITES_TBL,
         HZ_CUST_ACCOUNTS_ALL CA
   WHERE HZ_PARTY_SITES_TBL.PARTY_ID = HPS.PARTY_ID
     AND HZ_PARTY_SITES_TBL.PARTY_ID = CA.PARTY_ID
     AND HPS.PARTY_SITE_ID = ZP.PARTY_ID
     AND CA.CUSTOMER_CLASS_CODE = 'WEB CUSTOMER'
     AND UPPER(HZ_PARTY_SITES_TBL.PARTY_NAME) LIKE 'CAROLE%FINCK%'
     AND EXISTS (
            SELECT 1
              FROM HZ_CODE_ASSIGNMENTS HZ_CODE_ASSIGMNT_TBL
             WHERE HZ_CODE_ASSIGMNT_TBL.OWNER_TABLE_ID = ZP.PARTY_TAX_PROFILE_ID
               AND HZ_CODE_ASSIGMNT_TBL.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
               AND HZ_CODE_ASSIGMNT_TBL.CLASS_CODE IS NOT NULL)
ORDER BY ZP.LAST_UPDATE_DATE DESC;

EBTAX SQL for Tax Rate Denial for a particular Jurisdiction

SELECT  TAX,
        TAX_JURISDICTION_CODE,
        GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
        GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
        GEOGRAPHY_ELEMENT4_CODE CITY_CODE
FROM ZX_JURISDICTIONS_B ZX_JURISDICTIONS_TBL,
     HZ_GEOGRAPHIES HZ_GEOGRAPHIES_TBL
WHERE
    ZX_JURISDICTIONS_TBL.TAX_REGIME_CODE='US_SALE_AND_USE_TAX'
    AND SYSDATE BETWEEN ZX_JURISDICTIONS_TBL.EFFECTIVE_FROM AND NVL(ZX_JURISDICTIONS_TBL.EFFECTIVE_TO,'31-DEC-4999')
    AND SYSDATE BETWEEN HZ_GEOGRAPHIES_TBL.START_DATE AND HZ_GEOGRAPHIES_TBL.END_DATE
    AND ZX_JURISDICTIONS_TBL.ZONE_GEOGRAPHY_ID=HZ_GEOGRAPHIES_TBL.GEOGRAPHY_ID
    AND ZX_JURISDICTIONS_TBL.TAX=HZ_GEOGRAPHIES_TBL.GEOGRAPHY_TYPE
    AND NOT EXISTS (SELECT 1 FROM ZX_RATES_B ZR
                    WHERE
                        ZR.TAX_REGIME_CODE='US_SALE_AND_USE_TAX'
                        AND ZR.TAX_JURISDICTION_CODE=ZX_JURISDICTIONS_TBL.TAX_JURISDICTION_CODE)
ORDER BY TAX,
        TAX_JURISDICTION_CODE,
        GEOGRAPHY_ELEMENT2_CODE ,
        GEOGRAPHY_ELEMENT3_CODE,
        GEOGRAPHY_ELEMENT4_CODE