Document Properties
Kbid29E431
Last Modified29-Jun-2020
Added to KB12-Jun-2020
Public AccessEveryone
StatusOnline
Doc TypeSupport Articles
Product
  • ICM 7.6
  • ICM 7.7
  • ICM 7.8
  • ICM 7.9
  • ICM 7.10

Support Article - How to Adjust The Tax Rate For a Certain Time

1 Introduction

This article explains how to change the tax rate for your ICM system.

1.1 References

TitleDescription
Guide - Tax Data ConfigurationInformation on how to define the tax data.
Guide - Locales, Tax Data, Currencies and Exchange RatesConfiguration and administration options with respect to locales, tax data, currencies and exchange rates.

1.2 Glossary

TermDescription
PSPPayment service provider
ERPEnterprise resource planning

2 ICM - Change Tax Rates

2.1 Via taxdataset.properties File

The file $IS_HOME/share/system/config/cluster/taxdataset.properties is the central point where tax classes, tax rates and tax jurisdictions are defined in the system. The update mechanism for the tax data set is based on a set of pipelines which are executed on server startup. See attached file taxdataset.properties.

2.1.1 Tax Rate Syntax

The settings that were created by default can be completely overwritten by the settings in the file taxdataset.properties.

KeyDescription
Rate.<jid>.<cid>.valueSpecifies the tax rate in the format xx.yyy, e.g., for 16%, set 0.16
Rate.<jid>.<cid>.validfrom

Specifies a valid-from date in the dd.mm.yyyy, e.g., 01.07.2020

Optional when .previous rate becomes invalid and .value becomes valid.

Rate.<jid>.<cid>.previousSpecifies an optional tax rate valid previous to .validfrom (format xx.yyy).

It is possible to set the exact start date for using the new tax value with the validfrom key, but there is no key that can be used to set an end date. Therefore, the tax rate configuration must be adjusted again after the start date.

2.1.2 Example

The following examples help to adjust the full and reduced VAT values for a period from 01.07.2020 to 31.12.2020. Starting next year 2021, the old VAT values will be applied again.
VAT adjustment for Germany before start data e.g. (01.07.2020):

taxdataset.properties
# ### 4. Tax Rates ##################
# ###    -> Define tax rate matrix
Rate.GERMANY.FULLTAX.value=0.16
Rate.GERMANY.FULLTAX.validfrom=01.07.2020
Rate.GERMANY.FULLTAX.previous=0.19
Rate.GERMANY.REDUCEDTAX.value=0.05
Rate.GERMANY.REDUCEDTAX.validfrom=01.07.2020
Rate.GERMANY.REDUCEDTAX.previous=0.07
Rate.GERMANY.NOTAX.value=0.0
Rate.OTHER.FULLTAX.value=0.0

VAT adjustment for Germany after start date e.g. (01.07.2020):

taxdataset.properties
# ### 4. Tax Rates ##################
# ###    -> Define tax rate matrix
Rate.GERMANY.FULLTAX.value=0.19
Rate.GERMANY.FULLTAX.validfrom=01.01.2021
Rate.GERMANY.FULLTAX.previous=0.16
Rate.GERMANY.REDUCEDTAX.value=0.07
Rate.GERMANY.REDUCEDTAX.validfrom=01.01.2021
Rate.GERMANY.REDUCEDTAX.previous=0.05
Rate.GERMANY.NOTAX.value=0.0
Rate.OTHER.FULLTAX.value=0.0


For the changes to become effective, the server must be restarted.

2.2 Change Tax Rates via SQL

Another possibility is, to change tax rates via SQL statements. Following statements change tax rates for full (19% → 16%) and reduced taxes (7% → 5%) in jurisdiction Germany on 06/30/2020 at 11:59:59.999 pm .

It is strongly recommended to save data of database table TAXRATE  before executing the SQL statements.

The execution of SQL statements does not change tax rates used by a running server, because entries are cached by the ORM layer. Furthermore, other caches (e.g. page cache) may contain prices with previous tax rate. Hence, all caches should be refreshed or the servers are restarted (does not refresh page cache).

2.2.1 ORACLE

UPDATE TAXRATE
SET
RATE = 0.050000,
PREVIOUSRATE = 0.070000,
VALIDFROM =(SELECT SYS_EXTRACT_UTC(FROM_TZ(CAST(TO_DATE('2020-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), SESSIONTIMEZONE) AT TIME ZONE 'GMT') FROM DUAL),
OCA = OCA + 1
WHERE 
RATE = 0.070000
AND TAXJURISDICTIONID in  (select J.TAXJURISDICTIONID from JURISDICTIONMAPPING J where  J.COUNTRY = 'DE');

UPDATE TAXRATE
SET
RATE = 0.160000,
PREVIOUSRATE = 0.190000,
VALIDFROM =(SELECT SYS_EXTRACT_UTC(FROM_TZ(CAST(TO_DATE('2020-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), SESSIONTIMEZONE) AT TIME ZONE 'GMT') FROM DUAL),
OCA = OCA + 1
WHERE 
RATE = 0.190000
AND TAXJURISDICTIONID in  (select J.TAXJURISDICTIONID from JURISDICTIONMAPPING J where  J.COUNTRY = 'DE');

The correct value of SESSIONTIMEZONE must be checked before execution, e.g. by following statement in SQLPlus:

  SELECT SESSIONTIMEZONE FROM DUAL;

Alternatively, a correct time zone ID (e.g. 'Europe/Berlin') can also be used instead of function SESSIONTIMEZONE .

2.2.2 MS-SQL Server

MS-SQL Server
UPDATE TAXRATE
SET
RATE = 0.160000,
PREVIOUSRATE = 0.190000,
VALIDFROM = DATEADD(MINUTE, DATEPART(tz, SYSDATETIMEOFFSET())*(-1), CONVERT(datetimeoffset, '2020-06-30 23:59:59.9999999')),
OCA = R.OCA + 1
FROM TAXRATE R, JURISDICTIONMAPPING J
WHERE
R.TAXJURISDICTIONID = J.TAXJURISDICTIONID
AND J.COUNTRY = 'DE'
AND R.RATE = 0.190000;
 
UPDATE TAXRATE
SET
RATE = 0.050000,
PREVIOUSRATE = 0.070000,
VALIDFROM = DATEADD(MINUTE, DATEPART(tz, SYSDATETIMEOFFSET())*(-1), CONVERT(datetimeoffset, '2020-06-30 23:59:59.9999999')),
OCA = R.OCA + 1
FROM TAXRATE R, JURISDICTIONMAPPING J
WHERE R.TAXJURISDICTIONID = J.TAXJURISDICTIONID
AND J.COUNTRY = 'DE'
AND R.RATE = 0.070000;

These SQL statements can be used for local MS-SQL Server instances, but might not be correct for managed instances of MS-SQL Server in Azure, because these instances can be located in a time zone that differs from the one of the deployed Application server.

3 ICM - Capture Payment

The capture feature of authorized payments in ICM always collects the full authorized amount without any adjustments.

Please note the following with regard to the VAT changes:

  • If the amount was always debited manually via ICM from the customer, this can still be done. 
  • If the VAT reduction should be passed to the customer in part or in full, this can be done by a manual refund after the capturing. There is no need for software adjustments in this way.
    Alternatively, a reduction could be executed first and then the new total amount could be debited (capture)
    The options (Refund, Reduce, Capture) depend on the functions provided by the connector to the payment provider.
  • If the capturing is carried out by an ERP directly at the PSP, it is the responsibility of the PSP to debit the correct (possibly reduced) amount.
  • B2C: For orders that are older than 4 months (before 01.03.2020), but will only be fulfilled in July, the customer can request an adjustment of the purchase price. These offsetting entries must be made in the connected Order Management (IOM, ERP).

4 ICM - Pending Baskets

A basket is (re-)calculated at every basket step during checkout. Furthermore every basket (re-)calculation retrieves the tax rates based on the current time again. This way the tax rate is correct every time.
The only case, where the basket is not re-calculation again is the final checkout step (checkout review). In conjunction with a session that still is not timed-out, the previous tax rate is used for order creation.

5 ICM - Subscriptions

In dependence of the preference FixedPriceRecurringOrders either the original price of the subscription or the current product price is used for subscription orders:

FixedPriceRecurringOrdersPriceTypePriceDisplay
true
(Price of subscription is used.)

Net

NetNo effect
GrossProduct price changes, because different amount for VAT is added.
Gross

NetProduct price changes, because different amount for VAT is deducted.
GrossProduct price does not change, but amount of included VAT is different.
false
(Current product price is used.)



Net

NetNo effect
GrossProduct price changes, because different amount for VAT is added.
Gross

NetProduct price changes, because different amount for VAT is deducted.
GrossProduct price changes, if Shop Manager adjusted product price to changed VAT.

Disclaimer

The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.

Customer Support
Knowledge Base
Product Resources
Support Tickets