Oracle R12 EBTax SQL Queries for Functional Implementers for Troubleshooting

Contents

  1 -- EBTAX SETUP TABLES --
  2 -- EBTAX TRANSACTION TABLES --
  3 -- SQL FOR PARTY FISCAL CLASSIFICATION CODE --
  4 -- BELOW QUERY  RETRIEVES CUSTOMER ADDRESSES THAT DOESNT HAVE ANY   GEOGRAPHY REFERENCE --
  5 -- BELOW SQL QUERY RETRIEVES LIST OF JURISDICTIONS' FOR WHICH TAX RATES HAS BEEN DEFINED --
  6 -- BELOW QUERY RETRIEVES LIST OF GEOGRAPHY'S WITHOUT JURISDICTIONS--
  7 --SUPPLIER TAX REGISTRATION CREATION--
  8 --EXCLUDE FREIGHT FROM DISCOUNT--

 

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

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

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

    

Una diagnosi e una stadiazione accurate sono elementi di valutazione critici.

La diagnosi iniziale di disfunzione erettile veno occlusiva viene posta in presenza di mancata risposta all’erezione farmaco indotta da prostaglandine intracavernose in presenza di flusso arterioso regolare.

E dimostrato, infatti, che chi soffre di insonnia ha bassi livelli di melatonina nel sangue”.

Perche le donne di oggi subiscono cosi tanti cesarei?

Il risultato sembra andare contro ogni credere comune.

Da fonti multiple verso un registro centrale.

Non solo per la disfunzione erettile questi farmaci, oggi, vengono prescritti anche dopo un intervento dapoxetina prezzo di asportazione della prostata per un tumore, come terapia riabilitativa, e nei casi priligy on line originale di problemi urinari.

La piu appropriata sembra essere la cosiddetta “posizione di andromaca”, nella quale la donna sta sopra l’uomo.

Un 58enne pregiudicato, tornato da un viaggio a cuba, e stato fermato allaeroporto di firenze-peretola con una valigia stracolma di un farmaco con gli stessi principi attivi del viagra.

Il merito di questa scoperta e di un gruppo di ricercatori, guidati da due esperti italiani dell’istituto di candiolo, i professori alberto bardelli, direttore del laboratorio di genetica molecolare, e federico bussolino, direttore scientifico della fondazione piemontese per la ricerca sul cancro.

Sign up for our free Newsletter here:

Mailing Address

738 Main Street #229
Waltham MA 02451

Corporate Office

18 Lyman Street,
J&N Professional Bldg,
Suite #206,
Westborough ,MA -01581
©2006 Rover Resources.All rights reserved Privacy Policy | Terms of Use