Archive for February, 2013

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 – AGIS and tax/VAT calculation – what is going on?

Tax issues with Oracle intercompany including the Advanced Global Intercompany Solution (AGIS)

In my opinion, it should not matter what type of transaction is taking place, the tax should be independently calculated based on the information is has at the time and never be fed a tax rate to use! AGIS is a classic example of this. When the AR transaction is calculated, the tax should be calculated based on all the determining factors associated to the tax rules for the appropriate tax regime.
Indeed, this is what is happening but the problems start when the intercompany AP transaction is created because it does not appear that the Oracle intercompany solution can handle the processing of the tax in the right way! I have set up AGIS for many clients and the tax used to work fine, i.e. there was no tax rate from AR being forced through and why would I want this? if the AR transaction calculated French tax because it is a French entity, why would I want French VAT calculated on my AP invoice if it is a Germany entity and registered for VAT in Germany? I wouldn’t, I would want a German DE VAT tax rate. So this is what I am currently observing as being a specialist in eBTax, I am lucky to have multiple clients so I get to see multiple scenarios at the same time.

  • Some of my client (mainly those from a year ago backwards) had no issues with the tax calculation – the only issue was that Oracle was not bringing the tax classification code through from AR to AP but it was not bringing any other tax information – which is what we want.

 

  • A couple of my newer clients, one an upgrade and one a new install are seeing issues with AGIS in that the AP transaction is trying to pull the AR tax information through, not just the tax classification code but the actual tax rate even when the tax regimes are completely different! this is causing errors and I am in the process of trying to resolve things.

 

  • For those clients where the AGIS (and any intercompany) AP transaction is created without the system trying to force the tax rate from AR, the AP transaction is devoid of any ‘Ship-To’ information making it impossible to correctly calculate the tax is the operating unit has multiple ship to sites in different countries with different VAT registration numbers!

So to me, it seems that Oracle have got the entire intercompany solution wrong when it comes to VAT and rather than trying to be clever and bring the AR tax rate into AP they should back off and only bring the tax information that will allow the tax to be freshly calculated as a standalone transaction in AP. What do I mean by ‘tax information’? I am talking about the data that can be used by the determining factors such as the tax classification code, the intended use, the product type or the user defined fiscal classification code. Bring the information and the rules, if correctly setup can use that data to determine the tax rate for both AP and AR!

Oracle Fusion – is now the time to upgrade?

Without doubt, Oracle Fusion is on its way but is it here yet? Sure there are sites that have started to implement or even a couple have gone live but does that mean it’s the right fit for you to consider the upgrade or not from either Oracle R12 or even jumping directly from Oracle 11i straight to Fusion and skipping R12 completely?

Forget the hype and the rubbish the salesman tells you and definitely ignore the consultancy pushing you to go to Fusion because they just want the experience and to be able to put Oracle Fusion on their resume!

From what we have seen and experienced, Fusion is ready – but not like R12.

Cyprus 2% VAT rise to 19% by 2014

The VAT rate first increased by 1% to 18% on 14 Jan 2013 and a second rise of 1% on 13 Jan 2014 to 19%.  The reduced rate will also rise in 2014 from 8% to 9%.  There will be no change to the 8% VAT rate..

this post was originally from http://www.tmf-vat.com/tmf-in-the-media/cyprus-2-vat-rise-to-19-by-2014.html

Oracle – 11i to R12 Upgrade or Reimplement?

I am writing a white paper on whether to upgrade or reimplement from 11i to R12 but in the mean time, please add your comments with specific questions so I can address them and and to the Whitepaper

Need to find a US address from the Zip Code?

http://www.zip-info.com/cgi-local/zipsrch.exe?cnty=cnty&zip=90210&Go=Go

Oracle eBTax – when to use ‘Use Subscription of Legal Entity’

When creating your Oracle eBusiness Tax Party Tax Profiles, there is an option to choose ‘Use Subscription of Legal Entity’ and whilst the Oracle documentation says to use this – when is the correct time to do so?

There are 2 scenarios that will determine Continue Reading

Sales and Use Taxes: Exemptions and Exclusions California Revenue and Taxation Code Part 1, Division 2

Sales and
Use Taxes:
Exemptions and
Exclusions
California Revenue and Taxation Code
Part 1, Division 2
year – 2012

http://www.boe.ca.gov/pdf/pub61.pdf

R12: EBTax for Payables Search Helper

A Search Helper has been created in Payables for those issues arising with E-Business Tax while managing Invoices.

See the below detailed note:
  Note:1524117.1 – R12: E-Business Tax (EBTax) for Payables Search Helper (Guided Resolution)

A Search Helper is a document created to assist you in finding the notes you need to troubleshoot the arising issue.
Instead of having to think of the right search words or search method to use, after answering a few questions, the Search Helper will show you notes that are likely to help resolve your issue.

To get the desired results using the Search Helper troubleshooting notes you must choose a first statement of the task you’re trying to perform. For example “Create an Invoice” or “Validate an Invoice”.
After this first explanation, a second symptom will be requested to provide the specific notes and documents available for the issue you’re facing.

There are three Payables Search Helpers: Invoices, Suppliers and Payments. They all run in the same way to provide the most suitable solutions to your issues.

Here you can find the link to the specific search helpers:
   Note:1345557.1 – R12: Payables Invoices Search Helper (Guided Resolution)
   Note:1410773.1 – R12: Suppliers Search Helper (Guided Resolution)
   Note:1404627.1 – R12: Payables Payments (Funds Disbursement) Search Helper (Guided Resolution)

Oracle ebtax – Tax on Purchase orders or Requisitions for EU VAT


There are very few exceptions where I have had to leave VAT on for purchase orders for EU VAT setups. There are however several reasons why it is best practise to turn it off, including those listed below;
  • As you recover your VAT, VAT on purchases is immaterial to the actual purchase price of the goods as you will claim it back (for the majority of purchases). This can have issues with sign off limits and budgets if tax is included
  • When creating a purchase order for an item that is not in your inventory, you can never truly know whether that item is standard, reduced or exempt from VAT until the invoice arrives
  • VAT and any other type of transactional tax is always recalculated based on the AP invoice and not from the purchase order.
  • The supplier could be non registered for VAT and so none in due
  •  Offset tax cannot be calculated on Purchase Orders so you will in fact only see the positive tax amount and not the negative offset amount that can cause issues
  • No accounting is passed to the GL from purchasing with regards to the VAT calculated
  • If the purchase order VAT amount is different to the Supplier VAT amount then there can be matching issues with the invoice that comes in.
  • Legal Entity information is not added to a PO so any logic linked to the legal entity or legal entity establishment can not be used.

I would only consider Tax on Purchase orders if there are strict budgetary requirements or if tax is not recoverable, you need to pull in the Purchase orders for cash flow purposes or you do not recover VAT.

You can always turn the tax back on if you need it!

Oracle eBTax – Customer exemption only working against STATE?

If you have applied a customer exemption and set it to STATE but ticked the check box so that is should ‘Apply to lower levels’ but find that the tax still calculates for County and City then make sure the following setup is done for the County and City taxes.

Oracle eBTax – do purchasing categories drive tax in eb tax

You can definitely use Purchasing categories to drive your tax in the form of tax rules that use the determining factor of ‘Product – Inventory Linked’

First you need to establish a Product Classification for the Purchasing Categories. Do this by going to;
tax manager–>products–>product classification

then create a new value where you link the ‘Inventory Category Set’ of ‘Purchasing Category’ to your own names ‘Fiscal Classification Type Code’. One you have done this, you can use your newly created Purchasing Category product classification where the determining factor class of ‘Product – Inventory Linked’ (no Class Qualifier) and the determining factor name is the value you just created.

Oracle eBTax – If we are using European localization in 11i, will it go away with R12 E-Tax? or both serve different purpose?

Question –  If we are using European localisation in 11i, will it go away with R12 E-Tax? or both serve different purpose?

Answer – Pretty much every localisation I have worked with in R12 needs to be setup completely differently from 11i with many localisations being very different to those in 11i. Many of the localisations are related to ebusiness tax because of the many ‘local’ fiscal requirements around indirect taxation and particularly in Europe – disposing the myth that the EU has an harmonised tax solution.

So, to answer the question, it is unlikely that any localisations are replaced completely with standard functionality but there are many additional features that can be utilised by the eBTax solution that can make reporting easier and more detailed.

EU consumers hit with €440 VAT bill for tax competition

As European governments look to cut their business taxes and compete to win global industry, consumers are picking up the bill with more Value Added Tax (VAT) sales tax rises.  Many countries are following the UK and Irish lead on cutting corporation taxes, shifting the tax burden onto shoppers and retailers’ margins.  The 2013 TMF EU VAT Tracker is now predicting the average weighted EU VAT rate to hit over 21% in 2013 compared to 19% in 2010.  This translates into a rise of €440 per annum in the average household consumption bill since 2010.

see the full article here

12