Oracle R12 EBTax SQL Queries for Functional Implementers for Troubleshooting

Oracle R12 EBTax SQL Queries for Functional Implementers for Troubleshooting

Oracle R12 EBTax SQL Queries for Functional Implementers for Troubleshooting

Contents

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

-- 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