To install and configure the tax integration interface, you need to register the new commands provided by the tax integration kit to the database. If you are using third-party tax software, sales tax and shipping tax are now separated into individual commands for the Tax Integration Kit. If you need to separate sales tax and shipping tax to allow separate auditing of sales and shipping tax, you must follow these steps instead.
For a store to use the new command provided by the tax integration kit for tax calculation, you need to register the new commands, ApplyCalculationUsageCmd and TaxCalculationUsageTIKCmd, to the WebSphere Commerce database, by updating the CALMETHOD and STENCALUSG tables, which you do by following these steps:
- Insert a new entry for ApplyCalculationUsageCmd in the CALMETHOD table.
Column Values CALMETHOD_ID The key, a unique ID that is assigned to the entry. STOREENT_ID -1 or the store_ID CALUSAGE_ID -3 TASKNAME com.ibm.commerce.isv.kit.tax.ApplyCalculationUsageTIKCmd DESCRIPTION This is a description of the sales tax method of the ISV. SUBCLASS 12 NAME ApplyCalculationUsageTIK Use the following sample SQL statements as your guide:
DELETE FROM calmethod WHERE TASKNAME = 'com.ibm.commerce.isv.kit.tax.ApplyCalculationUsageTIKCmd' AND STOREENT_ID = store_ID AND CALUSAGE_ID = -3 INSERT INTO CALMETHOD (CALMETHOD_ID, STOREENT_ID, CALUSAGE_ID, TASKNAME, DESCRIPTION, SUBCLASS, NAME) VALUES ((select coalesce((min(calmethod_id)-1),1) from calmethod), store_ID , -3 'com.ibm.commerce.isv.kit.tax.ApplyCalculationUsageTIKCmd', 'default method for calculation taxes using Taxcompany', 12, 'ApplyCalculationUsageTIK')
- Insert a new entry for TaxCalculationUsageTIKCmd in the CALMETHOD table.
Column Values CALMETHOD_ID The key, a unique ID that is assigned to the entry. STOREENT_ID -1 or the store_ID CALUSAGE_ID -3 TASKNAME com.ibm.commerce.isv.kit.tax.TaxCalculationUsageTIKCmd DESCRIPTION This is a description of the sales tax method of the ISV. SUBCLASS 14 NAME TaxCalculationUsageTIK Use the following sample SQL statements as your guide:
DELETE FROM calmethod WHERE TASKNAME = 'com.ibm.commerce.isv.kit.tax.TaxCalculationUsageTIKCmd' AND STOREENT_ID = store_ID AND CALUSAGE_ID = -3 INSERT INTO CALMETHOD (CALMETHOD_ID, STOREENT_ID, CALUSAGE_ID, TASKNAME, DESCRIPTION, SUBCLASS, NAME) VALUES ((select coalesce((min(calmethod_id)-1),1) from calmethod), store_ID, -3, 'com.ibm.commerce.isv.kit.tax.TaxCalculationUsageTIKCmd', 'default method for setting audit flag using Taxcompany', 14, 'TaxCalculationUsageTIK')
- Update the STENCALUSG table with the new CALMETHOD ID (obtained from above) for the store:
update STENCALUSG set CALMETHOD_ID_APP =CALMETHOD_ID whereCALUSAGE_ID = -3 and STOREENT_ID =store_IDUse the following sample SQL statements as your guide:
UPDATE STENCALUSG SET (CALMETHOD_ID_APP, CALMETHOD_ID_FIN) = ((SELECT CALMETHOD_ID FROM CALMETHOD WHERE TASKNAME = 'com.ibm.commerce.isv.kit.tax.ApplyCalculationUsageTIKCmd' AND STOREENT_ID = store_ID AND CALUSAGE_ID = -3), (SELECT CALMETHOD_ID FROM CALMETHOD WHERE TASKNAME = 'com.ibm.commerce.isv.kit.tax.TaxCalculationUsageTIKCmd' AND STOREENT_ID = store_ID AND CALUSAGE_ID = -3)) WHERE CALUSAGE_ID = -3 and STOREENT_ID = store_ID
- If the CMDREG contains an entry with this interface name, then remove the entry: com.ibm.commerce.taxation.commands.ApplyOrderTaxesCmd
The tax software vendor also would require you to register the TaxOrderCmd and TaxOrderItemCmd commands in the CMDREG table. Refer to the tax software vendor's documentation for details.
Use the following sample SQL statements as your guide:
DELETE FROM cmdreg WHERE storeent_id = store_ID AND interfacename = 'com.ibm.commerce.isv.kit.tax.TaxOrderCmd' AND classname = 'com.taxcompany.utl.TaxcompanyOrderCmdImpl' INSERT INTO cmdreg (storeent_id, interfacename, description, classname, properties, target) VALUES (store_ID, 'com.ibm.commerce.isv.kit.tax.TaxOrderCmd', 'Taxcompany Tax Integration Kit implementation', 'com.taxcompany.utl.TaxcompanyOrderCmdImpl', null, 'Local') DELETE FROM cmdreg WHERE storeent_id = store_ID AND interfacename = 'com.ibm.commerce.isv.kit.tax.TaxOrderItemCmd' AND classname = 'com.taxcompany.utl.TaxcompanyOrderItemCmdImpl' INSERT INTO cmdreg (storeent_id, interfacename, description, classname, properties, target) VALUES (store_ID, 'com.ibm.commerce.isv.kit.tax.TaxOrderItemCmd', 'Taxaware Tax Integration Kit implementation', 'com.taxcompany.utl.TaxcompanyOrderItemCmdImpl', null, 'Local')
Note: Use 0 (site) or store_ID.
Steps for separate sales tax and shipping tax when using third-party tax software
Sales tax and shipping tax can be separated into individual commands for the Tax Integration Kit (third-party tax software). This separation allows individual auditing of sales tax and shipping tax.
Complete the following steps to audit sales and shipping tax separately:
- Add the following SQL statement to update the CMDREG table:
db2 insert into CMDREG (STOREENT_ID, INTERFACENAME, DESCRIPTION, CLASSNAME, PROPERTIES, LASTUPDATE, TARGET) values (0,'com.ibm.commerce.isv.kit.tax.TaxCalculationUsageSalesTaxTIKCmd','Sales Tax calculation usage for third-party tax providers','com.ibm.commerce.isv.kit.tax.TaxCalculationUsageSalesTaxTIKCmdImpl',null,null,'Local') db2 insert into CMDREG (STOREENT_ID, INTERFACENAME, DESCRIPTION, CLASSNAME, PROPERTIES, LASTUPDATE, TARGET) values (0,'com.ibm.commerce.isv.kit.tax.TaxCalculationUsageShippingTaxTIKCmd','Shipping Tax calculation usage for third-party tax providers','com.ibm.commerce.isv.kit.tax.TaxCalculationUsageShippingTaxTIKCmdImpl',null,null,'Local')
- Add the following SQL statement to update the CALMETHOD table:
db2 insert into CALMETHOD (CALMETHOD_ID, STOREENT_ID, CALUSAGE_ID, TASKNAME, DESCRIPTION, SUBCLASS, NAME) VALUES ((select coalesce((min(calmethod_id)-1),1) from calmethod), store_ID, -3, 'com.ibm.commerce.isv.kit.tax.TaxCalculationUsageSalesTaxTIKCmd', 'default method for setting audit flag for sales tax using third-party tax software', 14, 'TaxCalculationUsageSalesTaxTIK') db2 insert into CALMETHOD (CALMETHOD_ID, STOREENT_ID, CALUSAGE_ID, TASKNAME, DESCRIPTION, SUBCLASS, NAME) VALUES ((select coalesce((min(calmethod_id)-1),1) from calmethod), store_ID, -4, 'com.ibm.commerce.isv.kit.tax.TaxCalculationUsageShippingTaxTIKCmd', 'default method for setting audit flag for shipping using third-party tax software', 14, 'TaxCalculationUsageShippingTaxTIK') insert into CALMETHOD (CALMETHOD_ID, STOREENT_ID, CALUSAGE_ID, TASKNAME, DESCRIPTION, SUBCLASS, NAME) VALUES ((select coalesce((min(calmethod_id)-1),1) from calmethod), store_ID, -3, 'com.ibm.commerce.isv.kit.tax.ApplyCalculationUsageSalesTaxTIKCmd', 'applies calculation usage sales tax', 14, 'ApplyCalculationUsageSalesTaxTIK') insert into CALMETHOD (CALMETHOD_ID, STOREENT_ID, CALUSAGE_ID, TASKNAME, DESCRIPTION, SUBCLASS, NAME) VALUES ((select coalesce((min(calmethod_id)-1),1) from calmethod), store_ID, -4, 'com.ibm.commerce.isv.kit.tax.ApplyCalculationUsageShippingTaxTIKCmd', 'applies calculation usage shipping tax', 14, 'ApplyCalculationUsageShippingTaxTIK')
where store_ID is the store or store group ID. - The STENCALUSG table must be changed so that auditing does not occur at OrderProcess. Any value in the CALMETHOD_ID_FIN column must be removed for sales tax and shipping tax, which prevents TaxCalculationUsageTIKCmd from running after order process. To do this, query the STENCALUSG table. If any value for the store is displayed in the CALMETHOD_ID_FIN column, add the following SQL statement to remove it:
update stencalusg set CALMETHOD_ID_FIN=NULL where STOREENT_ID=store_ID and CALUSAGE_ID=calusage_ID
where store_ID is the store or store group ID and calusage_ID is the calculation usage of the row that has the value in the CALMETHOD_ID_FIN column (by default, shipping = -4 and sales = -3). - The STENCALUSG table must be updated so that ApplyCalculationUsageSalesTaxTIKCmd and ApplyCalculationUsageShippingTaxTIKCmd are called for your store instead of ApplyCalculationUsageCmd and ApplyCalculationUsageTIKCmd. Query the CALMETHOD table to find the CALMETHOD_ID values for com.ibm.commerce.isv.kit.tax.ApplyCalculationUsageSalesTaxTIKCmd and com.ibm.commerce.isv.kit.tax.ApplyCalculationUsageShippingTaxTIKCmd. Using those values, update the STENCALUSG table so that CALMETHOD_ID_APP for shipping and sales is called instead of ApplyCalculationUsageCmd and ApplyCalculationUsageTIKCmd. Use the following SQL statements to update the STENCALUSG table:
update stencalusg set CALMETHOD_ID_APP=calmethod_ID_1 where STOREENT_ID=store_ID and CALUSAGE_ID=calusage_ID_1
update stencalusg set CALMETHOD_ID_APP=calmethod_ID_2 where STOREENT_ID=store_ID and CALUSAGE_ID=calusage_ID_2
where calmethod_ID_1 is the CALMETHOD_ID for ApplyCalculationUsageSalesTaxTIKCmd, calmethod_ID_2 is the CALMETHOD_ID for ApplyCalculationUsageShippingTaxTIKCmd, store_ID is the store or store group ID, calusage_ID_1 is the calculation usage ID for sales tax, and calusage_ID_2 is the calculation usage value for shipping tax. - Refresh the Calculation registry and Command registry from the Commerce Admin Console.
The tax software vendor also requires you to register the TaxOrderCmd and TaxOrderItemCmd commands in the CMDREG table. Refer to the tax software vendor's documentation for details.