Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Thursday, April 5, 2018

Queries to Oracle ETAX Details


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 DISTINCT 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 HP.PARTY_NAME LIKE '%Advantage%Corp%';


-- 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,
         TAX_REGIME_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 1 = 1 AND ZJ.TAX_REGIME_CODE = 'US-SALES-TAX-101'
         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 1 = 1 AND ZR.TAX_REGIME_CODE = 'US-SALES-TAX-101'
                            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-SALES-TAX-101'
                                    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-SALES-TAX-101'
                                    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-SALES-TAX-101'
                                    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


No comments:

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect