Oracle ERP

Adding Real Time Analytics to your Tax Department’s Indirect Tax Toolkit

eBiz Answers have recently added Real Time Tax Analytics to their indirect tax toolkit,  the application has designed specifically with the tax department in mind.   Real Time analytics is a user friendly web application which will inform the tax department of a transaction with a potential tax issue within seconds of it being entered.   This allows the tax department to identify and correct issues at source and empowering the tax department to be involved in the end to end process.

The ever increasing use of technology by Tax Authorities for tax compliance is directly impacting the inner workings of the tax department.     The VAT landscape is changing rapidly across the globe as Tax Authorities introduce technology to crackdown on tax avoidance and ensure corporate tax transparency.    From South American to Europe,  tax authorities are passing regulation to ensure companies provide accounting data in a standardised format, direct from their ERP applications on a monthly basis.  The tax authorities can then check the file, validate the data and ensure compliance.   Poland has introduced electronic filing with SAF-T files from the 1st July to join Portugal, Luxembourg, Austria and Lithuania who are already using the SAF-T filing format.

Continue Reading

Oracle Indirect Tax Toolset for Real Time Compliance

The indirect tax world is changing and with it are the requirements around tax compliance and reporting and the penalties that follow when you get things wrong.

Too many companies rely on reporting after the month end has been closed – often too late to make changes or any changes that can be made are done manually with minimal audit trail.

So how can you get more out of your tax engine to reach total compliance?

eBiz Answers are pleased to introduce their Oracle indirect tax auditor toolset that provides analysis of your data in real time. No longer do you need to wait until after the month end has closed to check the data for issues before the tax returns are submitted, our eBTax Real Time Analytics that is part of our Oracle indirect tax auditor toolset will identify issues within 5 seconds of a transaction being entered.

To arrange a demo of this ground breaking tool, contact us here

Oracle ebusiness tax – when to use a tax jurisdiction

Linking a tax jurisdiction to a tax rate.

Whether its R12 eTax, eBTax or Oracle Cloud (Fusion) Tax, with Oracle eBusiness Suite, every Tax created under a Tax Regime needs a ‘Tax Jurisdiction’ if it wants to be enabled for transactions. Under the Oracle tax engine, a tax jurisdiction only applies to certain types of taxes.

Continue Reading

What Exchange Rate To Use For Foreign Currency Invoices From Domestic Suppliers

Do I have to use the exchange rate on the supplier invoice when entering a domestic invoice with a foreign currency.

Most ERP systems, good ones at least such as Oracle and SAP, will allow you to enter and store an exchange rate to use for your foreign currency invoices so that the foreign currency will then be automatically converted to your ledger currency when posted. There are also times when you may receive a foreign currency invoice, USD is the most common scenario, from a domestic supplier and VAT is charged because the sale is within the same country.

The problem

Continue Reading

Oracle Cloud Tax updated for Release 11

Fusion Tax Release 11 Updates

Below is a high level overview of the new functionality available for the Oracle Cloud tax engine. As you can see, there are quite a few changes.

Estimated Tax on Sales Order now possible.

Create a rule using tax determinant which includes transaction business category = Sales Transaction

Create a UD.

Enhanced Rapid Implementation Spreadsheets:

Data related to tax rules moved from tax config workbook to tax implementation workbook.

The following worksheets are new in the implementation workbook:

  • Tax Exemptions: Create tax exemption configuration for third parties and third-party sites.
  • Party Classifications: Associate party fiscal classifications with the parties.
  • Tax Reporting Codes: Create tax reporting codes and tax reporting types for downstream use in tax reports.
  • Tax Payer Identifiers: Define specific taxpayer identifiers for third parties and third-party sites for purposes of reporting.
  • Product Fiscal Classifications: Create product fiscal classifications for subsequent use in tax rules having a product inventory linked determining factor class.
  • Tax Rules: Define rules that look for a result for a specific tax determination process, such as determining place of supply or tax registration, in relation to a tax on a transaction.

Tax registrations have moved out of party tax profiles task. Now have their own task lists of:

  • Manage Tax Registrations: Use this task to create or edit details related to Tax Registrations, Withholding Tax Registrations, and Taxpayer Identifiers.
  • Manage Tax Exemptions: Use this task to create or edit details related to Tax Exemptions and Withholding Tax Exemptions.

Continue Reading

How to Transfer Invoice Tax Variances in Average Costing Organization

Oracle Payables will calculate Invoice Tax Variances when there is a non-recoverable tax that has been calculated at AP invoice entry that was not included on the Purchase Order.    In an average costing organization this tax variance should be transferred to the inventory valuation to ensure that the correct item cost is calculated.     Please see attached video for a demonstration of this functionality in Oracle R12.

Oracle eBTax – The Best Return on Investment You Will Get

As with everything, ‘if you want a better solution, you need to first have a better understanding of the problem’ and this is so true when it comes to indirect tax. Oracle’s eBTax (e-Business Tax) and Fusion Tax (or Oracle Cloud as it is now known) are probably the most powerful and certainly the most versatile tax engines on the market, but their versatility is because they are Oracle modules and not a third party product.

As published in Issue 60 of the Oracle Scene from the UKOUG – Summer 2016.

One thing I learnt many years ago (whilst on my first Oracle project), when I was trying to work out how to correctly setup transaction types, was that the best thing about Oracle is how configurable it is. Conversely, the worst thing about Oracle is how configurable it is! It’s a double-edged sword and if you know how to control the power given to you then Oracle can be configured to achieve what you need. Continue Reading

Oracle – VAT applicability between a parent company and a permanent establishment in Italy

Really this can apply to any EU country and comes under the tax engine rules for Same VAT Group and can apply to any VAT or GST based tax regime.

In reference to the original article by the TMF group on VAT in Italy between a parent company and its child, I thought that i would try and explain this in terms of setting up the tax in Oracle R12 eBTax or Oracle Cloud Tax.

Continue Reading

Oracle R12 – SQL to delete unwanted tax rates and tax statuses

How to delete tax rates and statuses from the back end

Here is the thing, you have just set up a country in eBTax, the full regime to rate then realised you need to change the naming convention but have all these rates and statuses in the system. Rates can be deleted but statuses cannot be so whlst there won’t be any logical issues, having old data int eh system is not great from a cosmetic point of view. So, a simple way of clearing out old statuses and rates is to do the following.

Step 1.

End date all the statuses that you no longer need

Step 2.

run the following scripts, putting in the Tax Regime where you have made the changes. Make sure you run them in this order.

Continue Reading

Oracle R12 eBTax – EMEA VAT SELECTION PROCESS Finally faster!

This blog should affect every single entity using Oracle in the EU but as we usually find, the majority of tax configuration for Oracle R12 has been badly done by the various integration partners and the EU reporting functionality completely missed and nowhere to be seen! Oracle have provided an excellent solution with the European Tax Reporting Ledger but it is often completely missed. But for those of you who have set your solution up correctly, you will often be frustrated by how long the EMEA VAT: Selection Process takes to run and for some large companies we have done work for, the times can be more than 24 hours! Something completely unacceptable for busy tax departments.

But fear not, Oracle now have a fix.

Patch 17976021: MINIMUM TAX REPORTING DATE PARAMETER REQUIRED IN EMEA VAT SELECTION PROCESS TO AVOID PERFORMANCE ISSUE

Continue Reading

NATO does not have to pay VAT so we dont need a tax solution right? Wrong!

Surely they are registered somewhere for VAT?

It took me a while to get my head around the fact that even though NATO is exempt from VAT or rather VAT is not applicable, they don’t have a VAT registration number nor need to submit any VAT returns. Sure we have set up solutions for clients where they have exemption certificates both for the products and services that they buy and for items and services that they sell but they had a VAT registration number and had to submit a return. We also deal with suppliers and customers who don’t have tax registrations and set their tax profile as ‘NOT REGISTERED’ but this just means we still charge them standard VAT when we sell to them. So what do we do for NATO? Continue Reading

Oracle eBTax R12 SQL scripts

Oracle R12 eBTax SQL Queries

Following are some 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. Tax Jurisdictions: 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
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’;
===================================================

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

— 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

Oracle ebtax – Belgium VAT Allocations Report

This blog will help identify the order that you need to run the various steps in order to get the Belgium VAT allocations reports working.

Hopefully if you have got to this stage, you have successfully set up your Belgium indirect tax solution and created the allocations needed for the Belgium VAT reports. If so, you need to run the following requests int he right order to get the Belgium VAT report to work.

First, run the EMEA VAT: Selection Process to populate the JG_ZZ_VAT_TRX_DETAILS table, which is where the majority of your European VAT and localised reports are run from.
If you have not run the Belgium Allocations reports yet then it is best to see if there are any errors, so run the following; Continue Reading

Oracle eBTax – How to populate the Tax Date automatically

For some reports for European VAT, the Tax Date of a transaction needs to be populated. This is an unreasonable task to expect users to do as it can only be done manually meaning that for every order that is imported, the transaction would need to be uncompleted and the tax date would have to be manually updated!

Oracle offer a patch for the supported countries to populate the tax date from Orders but this does not work if you do not have the localisations turned on which could be the case if you are a German entity who has a site in Hungary and charges Hungarian VAT when they ship from there. The Operating unit is German and the localisation country would be Germany but the tax in this instance is Hungarian! Another reason why this does not always work is that for any manual transactions, these would also need to be manually updated.

So, how can we automate this?

Very simply!

Create a request that does the following “update jg_zz_vat_trx_details set tax_invoice_date = trx_date where tax_invoice_date is null“. Then create a request set which first has the EMEA VAT SELECTION PROCESS and then your request to update the tax date.

Now there is no need to populate any of the tax dates as these will automatically take the transaction date if the tax date is NULL but is the user does put in a tax date manually, then this will be chosen instead.

Oracle 11i to R12 – Upgrade or Re-implement?

It will be sold as the easy, fast and cheapest way to go from Oracle 11i to R12 but is upgrading the right choice?

Unless my client has an extremely well refined 11i ERP solution working without fault with almost no customisation nor any country localisations, I would always say that a re-implementation is the right way forward. Time and time again, when working on upgrade projects, things are always a lot harder than if the project was being implementeded. I liken an 11i to R12 project to a house renovation. Consider 11i to be your house now and R12 to be the super environmentally friendly completely modern house of the future. If you have a very basic house that has been well constructed and has a great layout then making the renovation is straight forward, however, if that house is old, of a poor design and construction that needs all the pipe work and wiring replaced, making it extremely hard to get the underfloor heating in that you wanted, surely it would be better to knock it down and start again? Of course this is where my comparisons end because the costs of knocking down a house are usually the biggest factor as are the costs of any IT project, the 11i to R12 upgrade being one of them. But will the cost be any greater by doing a reimplementation instead of an upgrade? In the long run, with the delays to the project or the late surge in additional resources to fix the issues, update the customisations and ensure that the localisations are working to meet local fiscal policy, the upgrade becomes as costly, if not a higher cost than a clean re-implementation!

I will be putting together a document with all the pitfalls of doing an upgrade over an implementation based on mine and other colleagues experiences. But pleae feel free to comment with issues that you may have faced that support either option.

12