Example: Sales tax and shipping tax calculations
This example covers how you can use the calculation framework to calculate sales and shipping taxes.
Example description
Your store ships products worldwide from one fulfillment center. Both geographical zone A and geographical zone B consist of a single country or region.
The following tables describes the taxes that must be collected:
Sales tax rates Geographical zone Tax rate A 15% B 7% The rest of the world 0%
Shipping tax rates Geographical zone Tax rate A 15% B 4% The rest of the world 0%
Sales tax and shipping tax calculation example implementation
To use the calculation framework to calculate taxes for this example, complete the following steps:
- Define the jurisdiction groups and jurisdictions.
- Define the tax categories.
- Define the calculation codes.
- Define the calculation rules.
- Define the calculation scales.
- Define the calculation ranges.
- Define the look-up results for the calculation ranges.
- Associate the calculation scales with the calculation rules.
- Attach the calculation codes to the all catalog entries.
- Associate the calculation rules with the fulfillment center.
- Allow the calculation usages to not calculate a value for an OrderItem.
- (optional) Set the default sales tax and shipping tax calculation codes for the store. You only complete this step if your store does not already have default calculation codes for sales tax or shipping tax.
Text identifiers are used in the following sections to make it easier to see the relationships between components.
Text identifiers are often not valid in the WebSphere Commerce database. You should convert the text identifiers to integers or allow WebSphere Commerce to generate the identifiers for you. Check the database schema in the online information for the type of value that is valid for identifiers in a particular database table.
Defining tax jurisdiction groups and tax jurisdictions
The first step in using the calculation framework for tax calculations is to define the jurisdiction groups in which taxes are to be collected. You must also specify which jurisdiction belongs to which jurisdiction groups.
Jurisdictions and jurisdiction groups are used by TaxCalculationRuleQualify calculation method to determine which calculation rule is used to calculate taxes based on the shipping address of the order.
The information for jurisdictions, jurisdiction groups, and the relationships between them are defined in the following database tables:
- JURSTGROUP
- This table defines jurisdiction groups.
- JURST
- This table defines jurisdictions.
- JURSTGPREL
- This table defines which jurisdictions belong to which jurisdiction groups.
For more information about these database tables, refer to the database schema information in the online help.
Based on the description in Sales tax and shipping tax calculation example description, the jurisdictions, jurisdiction groups and the relationships between them can be defined based on the following table:
Jurisdictions and jurisdiction groups for the tax calculations example Geographical zone Jurisdiction Jurisdiction group A A GroupA B B GroupB We later take advantage of the precedence property when we associate sales tax and shipping tax calculation rules with jurisdiction groups in associating tax calculation rules with fulfillment centers.
Defining tax categories
A tax category is required for each combination of tax type and jurisdiction group. Separate tax categories must be defined for sales taxes and shipping taxes because they are defined as different tax types in the TAXTYPE database table. Default values for these tax types are defined in the language-independent bootstrap file.
We do not need to define tax categories for the rest of the world because we will be handling the 0% tax rate differently in this example. For more information, see Allow sales tax and shipping tax calculation usages to not calculate a value for an OrderItem.
This example requires four tax categories, two for the GroupA jurisdiction group and two for the GroupB jurisdiction group. The following table shows the tax categories that can be defined based on the example description:
Tax categories for the tax calculations example Tax category Tax type Description GroupA_SalesTax -3 (sales tax) Geographical zone A, Sales tax (15%) GroupA_ShipTax -4 (shipping tax) Geographical zone A, Shipping tax (15%) GroupB_SalesTax -3 (sales tax) Geographical zone B, Sales tax (7%) GroupB_ShipTax -4 (shipping tax) Geographical zone B, Shipping tax (4%) Tax category information is defined in the following database tables:
- TAXCGRY
- This table defines tax categories.
- TAXCGRYDS
- This table allows descriptions in multiple languages to be associated with a tax category.
For more information about these database tables, refer to the database schema information in the online help.
Each tax category is assigned a unique identifier. The unique identifiers are used to associate calculation rules with tax categories.
Defining tax calculation codes
Only two calculation codes must be defined in this example because the taxes apply to all items in an order. One calculation code indicates sale taxes and the other calculation code indicates shipping taxes.
Both calculation codes should be associated with a CalculationCodeQualify calculation method.
Here are the key properties that define for each calculation code:
SalesTaxCalcCode (sales tax calculation code) properties Property Value Description of value Grouping method 0 No grouping of products is required Qualification flag 0 The CalculationCodeQualify calculation method specified for this calculation code will not be invoked Identifying string Sales tax example code This is a character string that uniquely identifies this calculation code, given a particular calculation usage and store or store group Published 1 The calculation code is active Calculation usage -3 This is the ID for sales tax calculation usage CalculationCodeQualify calculation method -42 This is the ID for CalculationCodeQualifyCmd CalculationCodeApply calculation method -44 This is the ID for SalesTaxCalculationCodeApplyCmd CalculationCodeCalculate calculation method -43 This is the ID for CalculationCodeCalculateCmd
ShipTaxCalcCode (shipping tax calculation code) properties Property Value Description of value Grouping method 0 No grouping of products is required Qualification flag 0 The CalculationCodeQualify calculation method specified for this calculation code will not be invoked Identifying string Shipping tax example code This is a character string that uniquely identifies this calculation code, given a particular calculation usage and store or store group Published 1 The calculation code is active Calculation usage -4 This is the ID for the calculation usage for shipping tax CalculationCodeQualify calculation method -62 This is the ID for CalculationCodeQualifyCmd CalculationCodeApply calculation method -64 This is the ID for ShippingTaxCalculationCodeApplyCmd CalculationCodeCalculate calculation method -63 This is the ID for CalculationCodeCalculateCmd Defining tax calculation rules
Generally, percentage tax calculations require one calculation rule for each calculation code and each tax category. For this example, you need four tax calculation rules:
- Sales tax calculation code and the GroupA_SalesTax tax category
- Shipping tax calculation code and the GroupA_ShipTax tax category
- Sales tax calculation code and the GroupB_SalesTax tax category
- Shipping tax calculation code and the GroupB_ShippingTax tax category
These rules are all tax calculation rules, so you should specify that the TaxCalculationRuleQualify calculation method be used with these calculation rules.
Here are the key properties that define for each calculation rule:
GroupASalesRule properties (calculate 15% sales taxes in the GroupA jurisdiction group) Property Value Description of value Calculation code SalesTaxCalcCode This is the sales tax calculation code defined earlier Identifying number 1 This is a number that, along with its calculation code, uniquely identifies this calculation rule Qualification flag 1 Invoke the qualify calculation method defined for this calculation rule to check that this calculation rule applies to each item in the order Combination 2 The results of this calculation rule can be combined with other calculation rules that have a 0 or 2 combination property Tax category GroupA_SalesTax This is the ID for the calculation usage for sales tax CalculationRuleQualify calculation method -46 This is the ID for TaxCalculationRuleQualifyCmd CalculationRuleCalculate calculation method -47 This is the ID for CalculationRuleCalculateCmd
GroupAShipRule properties (calculate 15% shipping taxes in the GroupA jurisdiction group) Property Value Description of value Calculation code ShipTaxCalcCode This is the shipping tax calculation code defined earlier Identifying string GroupA shipping tax example This is a character string that, along with its calculation code, uniquely identifies this calculation rule Qualification flag 1 Invoke the qualify calculation method defined for this calculation rule to check that this calculation rule applies to each item in the order Combination 2 The results of this calculation rule can be combined with other calculation rules that have a 0 or 2 combination property Tax category GroupA_ShipTax This is the ID for the calculation usage for shipping tax CalculationRuleQualify calculation method -66 This is the ID for TaxCalculationRuleQualifyCmd CalculationRuleCalculate calculation method -67 This is the ID for CalculationRuleCalculateCmd
GroupBSalesRule properties (calculate 7% sales taxes in the GroupB jurisdiction group) Property Value Description of value Calculation code SalesTaxCalcCode This is the sales tax calculation code defined earlier Identifying string GroupB sales tax example This is a character string that, along with its calculation code, uniquely identifies this calculation rule Qualification flag 1 Invoke the qualify calculation method defined for this calculation rule to check that this calculation rule applies to each item in the order Combination 2 The results of this calculation rule can be combined with other calculation rules that have a 0 or 2 combination property. Tax category GroupB_SalesTax This is the ID for the calculation usage for sales tax CalculationRuleQualify calculation method -46 This is the ID for TaxCalculationRuleQualifyCmd CalculationRuleCalculate calculation method -47 This is the ID for CalculationRuleCalculateCmd
GroupBShipRule properties (calculate 4% shipping taxes in the GroupB jurisdiction group) Property Value Description of value Calculation code ShipTaxCalcCode This is the shipping tax calculation code defined earlier Identifying string GroupB shipping tax example This is a character string that, along with its calculation code, uniquely identifies this calculation rule. Qualification flag 1 Check that this calculation rule applies to each item in the order Combination 2 The results of this calculation rule can be combined with other calculation rules having a 0 or 2 combination property Tax category GroupB_ShipTax This is the ID for the calculation usage for shipping tax CalculationRuleQualify calculation method -66 This is the ID for TaxCalculationRuleQualifyCmd CalculationRuleCalculate calculation method -67 Calculation method ID for CalculationRuleCalculateCmd Defining tax calculation scales
A calculation scale is required for each combination of calculation usage and jurisdiction group. You must define calculation scales to cover every taxation level. You do not have to specify any quantity unit or currency for this calculation scale. It is a percentage calculation scale and all its calculation ranges start with zero, which is convertible to all currencies. The calculation scale is used to find the percentage tax rate.
We do not need to define calculation scales for the rest of the world group because we handle the 0% tax rate differently in this example. For more information, see Allow sales tax and shipping tax calculation usages to not calculate a value for an OrderItem.
This example requires four calculation scales:
- GroupA sales tax
- GroupA shipping tax
- GroupB sales tax
- GroupB shipping tax
Here are the key properties that define for each calculation scale:
GroupASalesScale (GroupA sales tax calculation scale) Property Value Description of value Calculation usage -3 This is the calculation usage for sales tax Identifying string Taxation example This is a character string that uniquely identifies this calculation scale, given a particular calculation usage and store or store group CalculationScaleLookup calculation method -53 TaxableNetPriceCalculationScaleLookupCmd
GroupAShipScale (GroupA shipping tax calculation scale) Property Value Description of value Calculation usage -4 This is the calculation usage for shipping tax Identifying string Taxation example This is a character string that uniquely identifies this calculation scale, given a particular calculation usage and store or store group CalculationScaleLookup calculation method -73 NetShippingCalculationScaleLookupCmd
GroupBSalesScale (GroupB sales tax calculation scale) Property Value Description of value Calculation usage -3 This is the calculation usage for sales tax Identifying string Taxation example This is a character string that uniquely identifies this calculation scale, given a particular calculation usage and store or store group CalculationScaleLookup calculation method -53 TaxableNetPriceCalculationScaleLookupCmd
GroupBShipScale (GroupB shipping tax calculation scale) Property Value Description of value Calculation usage -4 This is the calculation usage for shipping tax Identifying string Taxation example This is a character string that uniquely identifies this calculation scale, given a particular calculation usage and store or store group CalculationScaleLookup calculation method -73 NetShippingCalculationScaleLookupCmd Defining tax calculation ranges
Because the tax rate applies to all items in an order regardless of the item price, weight, or quantity, only one calculation range is required for each calculation scale.
Here are the key properties define for each calculation range:
GroupASalesRange (GroupA sales tax calculation range) Property Value Description of value Calculation scale ID GroupASalesScale This is the calculation scale that was defined earlier Calculation range starting value 0 Any total net price for an order greater than or equal to zero is subject to this range Cumulative calculation range indicator 0 This calculation range is non-cumulative CalculationRange calculation method -59 PercentageCalculationRangeCmd
GroupAShippingRange (GroupA shipping tax calculation range) Property Value Description of value Calculation scale ID GroupAShipScale This is the calculation scale that was defined earlier Calculation range starting value 0 Any total net price for an order greater than or equal to zero is subject to this range Cumulative calculation range indicator 0 This calculation range is non-cumulative CalculationRange calculation method -59 PercentageCalculationRangeCmd
GroupBSalesRange (GroupB sales tax calculation range) Property Value Description of value Calculation scale ID GroupBSalesScale This is the calculation scale that was defined earlier Calculation range starting value 0 Any total net price for an order greater than or equal to zero is subject to this range Cumulative calculation range indicator 0 This calculation range is non-cumulative CalculationRange calculation method -59 PercentageCalculationRangeCmd
GroupBShippingRange (GroupA shipping tax calculation range) Property Value Description of value Calculation scale ID GroupBShipScale This is the calculation scale that was defined earlier Calculation range starting value 0 Any total net price for an order greater than or equal to zero is subject to this range Cumulative calculation range indicator 0 This calculation range is non-cumulative CalculationRange calculation method -59 PercentageCalculationRangeCmd Note: If no discounts that are applied to the OrderItems have tax exemptions, you can improve the performance of WebSphere Commerce by using NetPriceCalculationScaleLookupCmd (-51) instead of TaxableNetPriceCalculationScaleLookupCmd (-53) for GroupASalesRange and GroupBSalesRange.
Defining look-up results for tax calculation ranges
Each calculation range needs a set of look-up results. For this example, the look-up results are the percentage tax rates. Each calculation range has only one tax rate, so only one look-up result is needed for each range. The look-up result is a percentage value, so you do not need to associate currencies or quantity units with the look-up results.
Here are the key properties that define for each calculation range look-up result:
GroupASalesLookupResult (GroupA sales tax calculation range look-up result) Property Value Description of value Look-up result 15.0 This is the 15% sales tax rate for Group A Calculation range ID GroupASalesRange This is the identifier of the calculation range to which this look-up result belongs
GroupAShippingLookupResult (GroupA shipping tax calculation range look-up result) Property Value Description of value Look-up result 15.0 15% shipping tax rate for Group A Calculation range ID GroupAShippingRange The identifier of the calculation range to which this look-up result belongs
GroupBSalesLookupResult (Group B sales calculation range look-up result) Property Value Description of value Look-up result 7.0 This is the 7% sales tax rate for Group B Calculation range ID GroupBSalesRange This is the identifier of the calculation range to which this look-up result belongs
GroupBShippingLookupResult (GroupA shipping tax calculation range look-up result) Property Value Description of value Look-up result 4.0 This is the 4% shipping tax rate for Group B Calculation range ID GroupBShippingRange This is the identifier of the calculation range to which this look-up result belongs Associating tax calculation scales with tax calculation rules
Each of the calculation scales that you have defined must be associated with the defined calculation rules in the CRULESCALE database table. The relationship between the defined calculation scales and the defined calculation rules is shown in the following table:
Tax calculation scales and the associated calculation rules Calculation scale Calculation rule GroupASalesScale GroupASalesRule GroupAShipScale GroupAShipRule GroupBSalesScale GroupBSalesRule GroupBShipScale GroupBShipRule Attaching tax calculation codes
To use the newly defined sales tax and shipping tax calculation codes, attach the calculation codes to the order items. Attach them indirectly, because the calculation codes apply to all order items resulting from a customer purchasing a product from the store catalog, not just to one item.
Indirectly attach the calculation codes by creating two entries in the CATENCALCD database table. To associate the calculation code with all items in the store catalog, use a catalog entry ID of null. The identifier for the store will be called Store.
Populate the CATENCALCD database table with the following information:
Calculation code attachment information Store ID Catalog entry ID Calculation code Store null SalesTaxCalcCode Store null ShipTaxCalcCode Associating tax calculation rules with fulfillment centers
You must enter information associating a fulfillment center with a tax calculation rule in the TAXJCRULE database table. This ensures that the TaxCalculationRuleQualify calculation method chooses a calculation rule that matches one of the tax jurisdictions when shipping from a fulfillment center to a shipping address.
We have one fulfillment center, two tax jurisdictions, and four calculation rules. The relationships between these elements are defined in the following table:
Information relating calculation rules to fulfillment centers Fulfillment center Tax jurisdiction Precedence Tax calculation rule FulfillmentA GroupA 1 GroupASalesRule GroupAShipRule GroupB 1 GroupBSalesRule GroupBShipRule Allow sales tax and shipping tax calculation usages to not calculate a value for an OrderItem
By default, WebSphere Commerce throws an exception when a calculation usage does not return a value for an order item. You can prevent this by setting the calculation usage usage indicator (STENCALUSG.USAGEFLAG) to 1. When the indicator is set to 1 and the calculation usage does not return a value, WebSphere Commerce assumes that the calculated value is 0.
We take advantage of this behavior in this example to reduce the number of tax categories, calculation rules, and calculation scales that must be defined. If we did not set this column to 1, we would need to define a World jurisdiction group, tax categories, calculation rules, and calculation scale to calculate the 0% tax rate for shipment when the shipping address belongs to the World jurisdiction group.
The STENCALUSG database table is also used to define the default calculation codes for a store. Default calculation code information must appear in the same database row where the USAGEFLAG column will be set. For information, see Defining default tax calculation codes for a store (optional).
The following table contains the information required to allow sales tax and shipping tax calculation usages to not calculate a value for an OrderItem:
Information to allow sales tax and shipping tax calculation usages to not calculate a value for an OrderItem Store ID Calculation usage Calculation code Usage indicator Store -3 (sales tax) SalesTaxCalcCode 1 Store -4 (shipping tax) ShipTaxCalcCode 1 Defining default tax calculation codes for a store (optional)
If your store does not already have default calculation codes for taxes defined, you might want to make the calculation codes you defined earlier into your default calculation codes.
Default calculation codes for a store are defined in the STENCALUSG database table. The following table contains the information required to define the default sales and shipping tax calculation codes for the store:
Information to define default calculation codes for the store Store ID Calculation Usage Calculation Code Store -3 (sales tax) SalesTaxCalcCode Store -4 (shipping tax) ShipTaxCalcCode (C) Copyright IBM Corporation 1996, 2006. All Rights Reserved.