Skip to main content

Oracle R12 EBTax SQL Queries for Functional Implementers for Troubleshooting




=========================================================================================================

-- EBTAX SETUP TABLES --

Following are the main E-Business tax tables that will contain the setup information that will help support in troubleshooting E-Business tax information.

a. Tax Regimes: ZX_REGIMES_B
b. Taxes: ZX_TAXES_B
c. Tax Status: ZX_STATUS_B
d. Tax Rates: ZX_RATES_B
e. Tax Jurisdictions: ZX_JURISDICTIONS_B
f. Tax Rules: ZX_RULES_B

To get a dump of the eTax setups, you can use the following set of queries.
Please provide the tax regime code when prompted. If the issue is limited to a tax then provide the tax name when prompted else please leave it blank.

SELECT *
FROM zx_regimes_b
WHERE tax_regime_code = '&tax_regime_code';

SELECT *
FROM zx_taxes_b
WHERE DECODE('&tax_name',null,'xxx',tax) = nvl('&tax_name','xxx')
AND tax_regime_code = '&tax_regime_code';

SELECT *
FROM zx_status_b
WHERE tax = '&tax_name'
AND tax_regime_code = '&tax_regime_code';

SELECT *
FROM zx_rates_b
WHERE tax = '&tax_name'
AND tax_regime_code = '&tax_regime_code';

SELECT *
FROM zx_jurisdictions_b
WHERE DECODE('&tax_name',null,'xxx',tax) = nvl('&tax_name','xxx')
AND tax_regime_code = '&tax_regime_code';

SELECT *
FROM zx_rules_b
WHERE tax = '&tax_name'
AND tax_regime_code = '&tax_regime_code';
=====================================================================

-- EBTAX TRANSACTION TABLES --

Following are the main E-Business tax tables that will contain the transaction information that will have the tax details after tax is calculated.

a. ZX_LINES: This table will have the tax lines for associated with PO/Release schedules.
TRX_ID: Transaction ID. This is linked to the
PO_HEADERS_ALL.PO_HEADER_ID
TRX_LINE_ID: Transaction Line ID. This is linked to the
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID

b. ZX_REC_NREC_DIST: This table will have the tax distributions for associated with PO/Release distributions.
TRX_ID: Transaction ID. This is linked to the
PO_HEADERS_ALL.PO_HEADER_ID
TRX_LINE_ID: Transaction Line ID. This is linked to the
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
TRX_LINE_DIST_ID: Transaction Line Distribution ID. This is linked to the
PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
RECOVERABLE_FLAG: Recoverable Flag. If the distribution is recoverable then the flag will be set to Y and there will be values in the RECOVERY_TYPE_CODE and RECOVERY_RATE_CODE.

c. PO_REQ_DISTRIBUTIONS_ALL: This table will have the tax distributions for associated with Requisition distribution.

RECOVERABLE_TAX: Recoverable tax amount
NONRECOVERABLE_TAX: Non Recoverable tax amount

d. ZX_LINES_DET_FACTORS: This table holds all the information of the tax line transaction for both the requisitions as well as the purchase orders/releases.

TRX_ID: Transaction ID. This is linked to the
PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID /
PO_HEADERS_ALL.PO_HEADER_ID
TRX_LINE_ID: Transaction Line ID. This is linked to the
PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID /
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID

=============================================================================================================

-- SQL FOR PARTY FISCAL CLASSIFICATION CODE --

SELECT HPP.PARTY_NAME,HP.PARTY_SITE_NAME ,HCA.*
    FROM ZX_PARTY_TAX_PROFILE ZP
        ,HZ_CODE_ASSIGNMENTS HCA
        ,HZ_PARTY_SITES HP
        ,HZ_PARTIES HPP
    WHERE ZP.PARTY_TAX_PROFILE_ID = HCA.OWNER_TABLE_ID
    --AND ZP.PARTY_ID = :PARTY_ID
    AND HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
    AND HP.PARTY_SITE_ID = ZP.PARTY_ID
    AND HPP.PARTY_ID= HP.PARTY_ID
    AND HCA.CLASS_CODE IS NOT NULL
    ORDER BY ZP.LAST_UPDATE_DATE DESC
   
   
SELECT   HP.PARTY_ID, HP.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 HP,
         HZ_CUST_ACCOUNTS_ALL CA
   WHERE HP.PARTY_ID = HPS.PARTY_ID
     AND HP.PARTY_ID = CA.PARTY_ID
     AND HPS.PARTY_SITE_ID = ZP.PARTY_ID
     AND CA.CUSTOMER_CLASS_CODE = 'WEB CUSTOMER'
     AND UPPER(HP.PARTY_NAME) LIKE 'CAROLE%FINCK%'
     AND EXISTS (
            SELECT 1
              FROM HZ_CODE_ASSIGNMENTS HCA
             WHERE HCA.OWNER_TABLE_ID = ZP.PARTY_TAX_PROFILE_ID
               AND HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
               AND HCA.CLASS_CODE IS NOT NULL)
ORDER BY ZP.LAST_UPDATE_DATE DESC;
===============================================================================================================

-- BELOW QUERY  RETRIEVES CUSTOMER ADDRESSES THAT DOESNT HAVE ANY GEOGRAPHY REFERENCE --

SELECT    HCA.ACCOUNT_NUMBER
              ,HCA.ACCOUNT_NAME
              ,HCS_SHIP.SITE_USE_CODE
              ,HL_SHIP.ADDRESS1 ADDRESS
              ,HL_SHIP.STATE STATE
              ,HL_SHIP.COUNTY COUNTY
              ,HL_SHIP.CITY CITY
              ,HL_SHIP.POSTAL_CODE
          FROM  HZ_CUST_SITE_USES_ALL HCS_SHIP
              , HZ_CUST_ACCT_SITES_ALL HCA_SHIP
              , HZ_CUST_ACCOUNTS HCA
              , HZ_PARTY_SITES HPS_SHIP
              , HZ_LOCATIONS HL_SHIP
         WHERE HCA.CUST_ACCOUNT_ID=HCA_SHIP.CUST_ACCOUNT_ID(+)
           AND HCS_SHIP.CUST_ACCT_SITE_ID(+) = HCA_SHIP.CUST_ACCT_SITE_ID
          -- AND HCA.ACCOUNT_NUMBER='10001'
           AND HCA_SHIP.PARTY_SITE_ID = HPS_SHIP.PARTY_SITE_ID
           AND HPS_SHIP.LOCATION_ID = HL_SHIP.LOCATION_ID
           AND HCA.STATUS='A'
           AND HCS_SHIP.STATUS='A'
           AND HCA_SHIP.STATUS='A'
           AND HL_SHIP.COUNTRY='US'
           AND NOT EXISTS (SELECT 1 FROM HZ_GEOGRAPHIES HG
                             WHERE HG.GEOGRAPHY_ELEMENT2_CODE=HL_SHIP.STATE
                                AND UPPER(HL_SHIP.COUNTY)=UPPER(HG.GEOGRAPHY_ELEMENT3_CODE)
                                AND UPPER(HL_SHIP.CITY)=UPPER(HG.GEOGRAPHY_ELEMENT4_CODE)
                                AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE)

================================================================================================================

-- BELOW SQL QUERY RETRIEVES LIST OF JURISDICTIONS' FOR WHICH TAX RATES HAS BEEN DEFINED --

SELECT  TAX,
        TAX_JURISDICTION_CODE,
        GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
        GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
        GEOGRAPHY_ELEMENT4_CODE CITY_CODE
FROM ZX_JURISDICTIONS_B ZJ,
     HZ_GEOGRAPHIES HG
WHERE
    ZJ.TAX_REGIME_CODE='US_SALE_AND_USE_TAX'
    AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM AND NVL(ZJ.EFFECTIVE_TO,'31-DEC-4999')
    AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
    AND ZJ.ZONE_GEOGRAPHY_ID=HG.GEOGRAPHY_ID
    AND ZJ.TAX=HG.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=ZJ.TAX_JURISDICTION_CODE)
ORDER BY TAX,
        TAX_JURISDICTION_CODE,
        GEOGRAPHY_ELEMENT2_CODE ,
        GEOGRAPHY_ELEMENT3_CODE,
        GEOGRAPHY_ELEMENT4_CODE
=====================================================================================================================

-- BELOW QUERY RETRIEVES LIST OF GEOGRAPHY'S WITHOUT JURISDICTIONS--

SELECT * FROM
(SELECT  GEOGRAPHY_TYPE,
        GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
        GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
        GEOGRAPHY_ELEMENT4_CODE CITY_CODE
    FROM
        HZ_GEOGRAPHIES HG
    WHERE HG.GEOGRAPHY_TYPE='STATE'
        AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
        AND GEOGRAPHY_ELEMENT1_CODE='US'
        AND NOT EXISTS (SELECT 1 FROM ZX_JURISDICTIONS_B ZJ
                        WHERE ZJ.ZONE_GEOGRAPHY_ID=HG.GEOGRAPHY_ID
                              AND ZJ.TAX_REGIME_CODE='US_SALE_AND_USE_TAX'
                              AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM AND NVL(ZJ.EFFECTIVE_TO,'31-DEC-4999')
                              AND ZJ.TAX=HG.GEOGRAPHY_TYPE)
UNION
SELECT  GEOGRAPHY_TYPE,
        GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
        GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
        GEOGRAPHY_ELEMENT4_CODE CITY_CODE
    FROM
        HZ_GEOGRAPHIES HG
    WHERE HG.GEOGRAPHY_TYPE='COUNTY'
        AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
        AND GEOGRAPHY_ELEMENT1_CODE='US'
        AND NOT EXISTS (SELECT 1 FROM ZX_JURISDICTIONS_B ZJ
                        WHERE ZJ.ZONE_GEOGRAPHY_ID=HG.GEOGRAPHY_ID
                                AND ZJ.TAX_REGIME_CODE='US_SALE_AND_USE_TAX'
                              AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM AND NVL(ZJ.EFFECTIVE_TO,'31-DEC-4999')
                              AND ZJ.TAX=HG.GEOGRAPHY_TYPE)
UNION
SELECT  GEOGRAPHY_TYPE,
        GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
        GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
        GEOGRAPHY_ELEMENT4_CODE CITY_CODE
    FROM
        HZ_GEOGRAPHIES HG
    WHERE HG.GEOGRAPHY_TYPE='CITY'
        AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
        AND GEOGRAPHY_ELEMENT1_CODE='US'
        AND NOT EXISTS (SELECT 1 FROM ZX_JURISDICTIONS_B ZJ
                        WHERE ZJ.ZONE_GEOGRAPHY_ID=HG.GEOGRAPHY_ID
                              AND ZJ.TAX_REGIME_CODE='_US_SALE_AND_USE_TAX'
                              AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM AND NVL(ZJ.EFFECTIVE_TO,'31-DEC-4999')
                              AND ZJ.TAX=HG.GEOGRAPHY_TYPE))
ORDER BY GEOGRAPHY_TYPE,STATE_CODE,
        COUNTY_CODE,
        CITY_CODE
===========================================================================================================

--SUPPLIER TAX REGISTRATION CREATION--

Use the below script to create Tax Registrations for suppliers - if you have defined any tax rule based on Tax Registrations

DECLARE X_RETURN_STATUS VARCHAR2(1);
BEGIN

     ZX_REGISTRATIONS_PKG.INSERT_ROW (       P_REQUEST_ID => NULL
                                            ,P_ATTRIBUTE1 => NULL
                                            ,P_ATTRIBUTE2 => NULL
                                            ,P_ATTRIBUTE3 => NULL
                                            ,P_ATTRIBUTE4 => NULL
                                            ,P_ATTRIBUTE5 => NULL
                                            ,P_ATTRIBUTE6 => NULL
                                            ,P_VALIDATION_RULE => NULL
                                            ,P_ROUNDING_RULE_CODE => 'UP'
                                            ,P_TAX_JURISDICTION_CODE => NULL
                                            ,P_SELF_ASSESS_FLAG => 'Y'
                                            ,P_REGISTRATION_STATUS_CODE => 'REGISTERED'
                                            ,P_REGISTRATION_SOURCE_CODE => 'IMPLICIT'
                                            ,P_REGISTRATION_REASON_CODE => NULL
                                            ,P_TAX => NULL
                                            ,P_TAX_REGIME_CODE => 'DAR'
                                            ,P_INCLUSIVE_TAX_FLAG => 'N'
                                            ,P_EFFECTIVE_FROM => TO_DATE('01-DEC-2007','DD-MON-YYYY')
                                            ,P_EFFECTIVE_TO => NULL
                                            ,P_REP_PARTY_TAX_NAME => NULL
                                            ,P_DEFAULT_REGISTRATION_FLAG => 'N'
                                            ,P_BANK_ACCOUNT_NUM => NULL
                                            ,P_RECORD_TYPE_CODE => NULL
                                            ,P_LEGAL_LOCATION_ID => NULL
                                            ,P_TAX_AUTHORITY_ID => NULL
                                            ,P_REP_TAX_AUTHORITY_ID => NULL
                                            ,P_COLL_TAX_AUTHORITY_ID => NULL
                                            ,P_REGISTRATION_TYPE_CODE => NULL
                                            ,P_REGISTRATION_NUMBER => NULL
                                            ,P_PARTY_TAX_PROFILE_ID => 812988
                                            ,P_LEGAL_REGISTRATION_ID => NULL
                                            ,P_BANK_ID => NULL
                                            ,P_BANK_BRANCH_ID => NULL
                                            ,P_ACCOUNT_SITE_ID => NULL
                                            ,P_ATTRIBUTE14 => NULL
                                            ,P_ATTRIBUTE15 => NULL
                                            ,P_ATTRIBUTE_CATEGORY => NULL
                                            ,P_PROGRAM_LOGIN_ID => NULL
                                            ,P_ACCOUNT_ID => NULL
                                            ,P_TAX_CLASSIFICATION_CODE => NULL
                                            ,P_ATTRIBUTE7 => NULL
                                            ,P_ATTRIBUTE8 => NULL
                                            ,P_ATTRIBUTE9 => NULL
                                            ,P_ATTRIBUTE10 => NULL
                                            ,P_ATTRIBUTE11 => NULL
                                            ,P_ATTRIBUTE12 => NULL
                                            ,P_ATTRIBUTE13 => NULL
                                            ,X_RETURN_STATUS => X_RETURN_STATUS
);

DBMS_OUTPUT.PUT_LINE('RETURN STATUS :' ||X_RETURN_STATUS);
COMMIT;
END;
===========================================================================================================

--EXCLUDE FREIGHT FROM DISCOUNT--

SELECT  APS.VENDOR_NAME,
             APS.EXCLUDE_FREIGHT_FROM_DISCOUNT VEND_EXCD,
            APSS.VENDOR_SITE_CODE,
            APSS.EXCLUDE_FREIGHT_FROM_DISCOUNT SITE_EXCD
 FROM   APPS.AP_SUPPLIERS APS,
            APPS.AP_SUPPLIER_SITES_ALL APSS
 WHERE    APS.VENDOR_ID = APSS.VENDOR_ID
        AND APS.VENDOR_ID NOT IN (1, 2, 3)
        AND APSS.EXCLUDE_FREIGHT_FROM_DISCOUNT IS NULL
        AND APS.EXCLUDE_FREIGHT_FROM_DISCOUNT  IS NULL
==============================================================================================================

Comments
You do not have permission to add comments.

Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

Public API’s for FA Transactions

Public API’s for FA Transactions So far Oracle FA is have all the good things except the lack on reporting.Oracle FA is now offer lot of public API's that can be used to interfacing with third party or Oracle application other modules. Here are some of transaction's API's:   Additions API if you have requirement to add assets directly via PL/SQL then use  FA_ADDITION_PUB.DO_ADDITION. If you have selected the Allow CIP Assets check box on the Book Controls window of a tax book when adding CIP assets using the Additions API, the this API automatically adds those CIP assets to that tax book at the same time that they are added to the corporate book. Adjustments API you can make cost adjustments to your assetsdirectly via PL/SQL using  FA_ADJUSTMENT_PUB.DO_ADJUSTMENT  for any  process adjustment. Detail can be found in appendix H) You can use this API if you have a custom interface that makes it difficult to use with the existing Oracle Assets interfaces for adjusti...
Post a Comment
Read more

Unbilled Receivables and Unearned Revenue Accounting in Oracle Projects

Unbilled Receivables and Unearned Revenue Accounting in Oracle Projects Introduction When it comes to contractual billing, invoice and revenue generation are two separate processes, which during the lifespan of a project may or may not always coincide with each other and so do the balances in revenue and receivables accounts.  This interim difference between revenue and invoice account balances is bridged using Unbilled Receivables (UBR) and Unearned Revenue (UER) Accounts. Unearned Revenue (UER) Unearned Revenue (also termed as deferred revenue or UER) signifies money received for the goods or services, which are yet to be delivered.  As per the principles of Revenue Recognition, UER is recorded as on the balance sheet unless it is converted to Revenue upon delivery of goods or services For Example XYZ Consulting Ltd. receives an annual maintenance contract of $ 12,000 on Dec 31, 2014 for the period of Jan 01, 2015 to Dec 31, 2015. At the start of the contract as of Dec 31, 2...
2 comments
Read more

Create Accounting for a Payment generates errors 95333 and 95359

Create Accounting for a Payment generates errors 95333 and 95359 Error: 95333: A conversion rate does not exist to convert USD to AUD for the conversion type Corporate and conversion date 20-MAR-09 for line -25. Please use the Daily Rates form in General Ledger to enter a conversion rate for these currencies, conversion date and conversion type. 95359: There is no accounted amount for the subledger journal entry line. Please inform your system administrator or support representative that: The source assigned to the accounting attribute Accounted Amount has no value for extract line number 88547. Please make sure the source assigned to the accounting attribute Accounted Amount has a valid value, or assign a different source to this accounting attribute. Solution: 1. Specify a conversion rate for the currencies and conversion date mentioned in the error message 95333 Navigation under the General Ledger responsibility: Setup > Currencies > Currency Rates Manager > Daily Rates ...
15 comments
Read more