税务相关表关联示例

Following aresome SQL queries to tun to pull Oracle eBTax (Oracle eBusiness Tax) information directly from the tables.

a. Tax Regimes:ZX_REGIMES_B

b. Taxes:ZX_TAXES_B

c. Tax Status:ZX_STATUS_B

d. Tax Rates:ZX_RATES_B

e. TaxJurisdictions: ZX_JURISDICTIONS_B

f. Tax Rules:ZX_RULES_B

You will most likely need to refine your extracts based on the data you have, whetehr you have migrated data or multiple countries etc.

SELECT *

FROM zx_regimes_b

WHEREtax_regime_code = '&tax_regime_code';

SELECT *

FROM zx_taxes_b

WHEREDECODE('&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 *

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

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

-- TAX DETERMINING FACTORS--

 Select
dftt.DET_FACTOR_TEMPL_NAME,
dft.DETERMINING_FACTOR_CLASS_CODE,
dft.DETERMINING_FACTOR_CQ_CODE,
dft.DETERMINING_FACTOR_CODE,
dft.REQUIRED_FLAG--,
from zx_det_factor_templ_dtl dft, zx_det_factor_templ_tl dftt
WHERE dft.DET_FACTOR_TEMPL_ID = dftt.DET_FACTOR_TEMPL_ID

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

-- TAX CONDITIONS --

Select
zxc.CONDITION_GROUP_CODE,
zxcg.DET_FACTOR_TEMPL_CODE,
zxc.DETERMINING_FACTOR_CLASS_CODE,
zxc.DETERMINING_FACTOR_CODE,
zxc.DETERMINING_FACTOR_CQ_CODE,
zxc.OPERATOR_CODE,
zxc.value_low
from zx_conditions zxc, zx_condition_groups_b zxcg
where OPERATOR_CODE <> 'Y'
and zxc.CONDITION_GROUP_CODE = zxcg.CONDITION_GROUP_CODE
AND ZXC.IGNORE_FLAG = 'N'
order by zxcg.det_factor_templ_code, zxc.CONDITION_GROUP_CODE, zxc.condition_group_code, zxc.determining_factor_class_code

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

-- EBTAX TRANSACTION TABLES --

Following are themain E-Businesstax tables that will contain the transactioninformation that will have the tax details after tax is calculated.

a. ZX_LINES: Thistable will have the tax lines for associated with PO/Release schedules.

TRX_ID: TransactionID. 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 associatedwith PO/Release distributions.

TRX_ID: TransactionID. 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 setto 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 forassociated 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 linetransaction for both the requisitions as well as the purchase orders/releases.

TRX_ID: TransactionID. 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
===================================================================

-- TAX RULES AND CONDITIONS --

 
SELECT tax_regime_code
        ,tax
        ,DECODE(
           rul.service_type_code
          ,'DET_TAX_STATUS'
          ,'Determine Tax Status'
          ,'DET_RECOVERY_RATE'
          ,'Determine Tax Rate'
          ,'DET_APPLICABLE_TAXES'
          ,'Determine Applicability'
          ,'DET_PLACE_OF_SUPPLY'
          ,'Determine Place of supply'
          ,'DET_TAX_RATE'
          ,'Determine Tax Rate'
         )
           rule
        ,rul.priority
        ,det_factor_templ_code factor_set
        ,res.priority
        ,condition_group_code
        ,alphanumeric_result
        ,NVL(ou.name, 'Global Configuration Owner') owner
    FROM zx.zx_rules_b rul
        ,zx.zx_process_results res
        ,zx.zx_party_tax_profile pp
        ,hr_operating_units ou
   WHERE     rul.tax_rule_id = res.tax_rule_id
         AND rul.content_owner_id = pp.party_tax_profile_id
         AND pp.party_id = ou.organization_id(+)
ORDER BY rul.tax_regime_code
        ,rul.tax
        ,rul.service_type_code
        ,rul.priority
        ,res.priority
===================================================================

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

--Tax rates and the accounts associated to them--

 
SELECT rates.tax_regime_code regime
      ,rates.tax tax
      ,rates.tax_status_code status
      ,rates.tax_rate_code tax_rate
      ,rates.percentage_rate rate
      ,rates.default_rec_rate_code rec_rate
      ,rates.offset_tax_rate_code offset_rate
      ,ou.name org
      ,rate_acc.concatenated_segments ar_acc
      ,rec_acc.concatenated_segments ap_acc
  FROM zx.zx_rates_b rates
      ,zx.zx_taxes_b tax
      ,zx.zx_accounts rate_zx_acc
      ,gl_code_combinations_kfv rate_acc
      ,zx.zx_rates_b rec
      ,zx.zx_accounts rec_zx_acc
      ,gl_code_combinations_kfv rec_acc
      ,hr_operating_units ou
 WHERE     1 = 1
       AND rates.tax = tax.tax
       AND rates.default_rec_rate_code = rec.tax_rate_code
       AND rates.rate_type_code = 'PERCENTAGE'
       AND tax.tax_type_code <> 'OFFSET'
       AND(rates.effective_to IS NULL
           OR rates.effective_to >= TRUNC(SYSDATE))
       AND rates.active_flag = 'Y'
       AND rate_zx_acc.tax_account_entity_code(+) = 'RATES'
       AND rate_zx_acc.tax_account_entity_id(+) = rates.tax_rate_id
       AND rate_zx_acc.tax_account_ccid = rate_acc.code_combination_id(+)
       AND rec_zx_acc.tax_account_entity_code = 'RATES'
       AND rec_zx_acc.tax_account_entity_id = rec.tax_rate_id
       AND rec_zx_acc.tax_account_ccid = rec_acc.code_combination_id
       AND rate_zx_acc.internal_organization_id = ou.organization_id (+)
       AND rec_zx_acc.internal_organization_id = NVL(rate_zx_acc.internal_organization_id, rec_zx_acc.internal_organization_id)
       AND rates.tax <> 'DUMMY TAX'
       UNION
SELECT rates.tax_regime_code regime
      ,rates.tax tax
      ,rates.tax_status_code status
      ,rates.tax_rate_code tax_rate
      ,rates.percentage_rate rate
      ,rates.default_rec_rate_code rec_rate
      ,rates.offset_tax_rate_code offset_rate
      ,ou.name org
      ,rate_acc.concatenated_segments ar_acc
      ,NULL ap_acc
  FROM zx.zx_rates_b rates
      ,zx.zx_taxes_b tax
      ,zx.zx_accounts rate_zx_acc
      ,gl_code_combinations_kfv rate_acc
      ,hr_operating_units ou
 WHERE     1 = 1
       AND rates.tax = tax.tax
       AND rates.rate_type_code = 'PERCENTAGE'
       AND tax.tax_type_code <> 'OFFSET'
       AND(rates.effective_to IS NULL
           OR rates.effective_to >= TRUNC(SYSDATE))
       AND rates.active_flag = 'Y'
       AND rate_zx_acc.tax_account_entity_code(+) = 'RATES'
       AND rate_zx_acc.tax_account_entity_id(+) = rates.tax_rate_id
       AND rate_zx_acc.tax_account_ccid = rate_acc.code_combination_id(+)
       AND rate_zx_acc.internal_organization_id = ou.organization_id (+)
       AND rates.default_rec_rate_code IS NULL
       AND rates.tax <> 'DUMMY TAX'
     ORDER BY regime
        ,tax
        ,status
        ,tax_rate