Calculation Framework in Websphere Commerce.

-

 

OrderCalculateCmd

OrderCalculateCmd invokes the Promotion engine. PromotionEngineOrderCalculateCmdImpl implements OrderCalculateCmd class. OrderCalculateCmd is invoked when we add, delete, update or merge item/s in cart.

This command calculates the order  total based on the sequence in which calculationUsageId is passed into the command.

WebSphere Commerce provides the below predefined calculation usages:

Calusage_id    Description

-1                         Discount

-2                         Shipping

-3                         Sales Tax

-4                         Shipping Tax

-5                         Coupon

-6                         Surcharge

-7                         Shipping Adjustment

 

CalculateOrderCommand iterates over the array of Calusageids passed in requestProperties and invokes the below methods accordingly for each calusageid.

  • initialize,
  • apply and
  • summarize methods

As a part of InitializeAdjustment and InitializeCoupon commands, respective calmethod and calrule commands invoke promotionEngine separately to fetch the valid promotions and coupons.PromotionEngine call in calculateOrder (PromotionEngineOrderCalculateCmdImpl) is to fetch the freebies to be added to cart.

There is no direct relation between promotionengine and initialize/apply/summarize commands.When the calusageid is -1 or -5 or -7 i.e; discount/coupon/ shiping adjstmnts, its respective initialize/apply command invokes specific calculation commands by referring CALMETHOD table.

Eg:PromotionEngineDiscountCalculationCodeCombineCmdImpl, ShippingAdjustmentCalculationCodeCombineCmdImpl

These commands internally invoke Promotion engine to resolve or apply promotions for that specific calusage.
There are five major components to the Web Sphere Commerce calculation framework:

  1. Calculation methods
  2. Calculation usages
  3. Calculation codes
  4. Calculation rules
  5. Calculation scales and calculation ranges
  1. Calculation methods

Calculation methods reference task commands to implement the parts of the calculation framework.

Tables involved :

CALMETHOD

Each row of this table defines a CalculationMethod implementation.

  1. Calculation usages

Calculation usages are the categories of calculations that are performed using the calculation framework. These include discounts and sales tax.

Tables involved :

CALUSAGE

A row in this table represents a CalculationUsage, indicating what kind of calculation a CalculationCode or CalculationScale is used for. Examples of CalculationUsages include discounts, shipping charges, sales tax, and shipping tax. Web Sphere Commerce provides the following predefined calculation usages:
discounts (-1), shipping (-2), sales tax (-3), shipping tax, (-4) coupons (-5), surcharge (-6) and shipping adjustment (-7).

 

Calculation usages as defined in wcs.bootstrap.xml and are processed in the following order by default:

Coupon

Discount

Shipping

Sales tax

Shipping tax

Surcharge

Shipping adjustment

  1. Calculation codes

Calculation codes indicate the calculations to be performed for OrderItems.

Tables involved :

CALCODE

A row in this table represents a CalculationCode. CalculationCodes represent ways of determining monetary amounts associated with OrderItems. They are used to calculate discounts, shipping charges, sales tax, and shipping tax .

  1. Calculation rules

Calculation rules calculate the monetary amounts for the calculation codes associated with an OrderItem.

Tables  involved :

CALRULE

Each row in this table represents a CalculationRule, it defines how to arrive at a monetary amount for a set of OrderItems. For each CalculationCode, one or more CalculationRules can be defined. The CalculationRules which belong to one CalculationCode are responsible for doing the calculation.

  1. Calculation scales and calculation ranges

Calculation scales allow a calculation rule for determining monetary amounts in a manner that is similar to looking up a value from a table. Calculation ranges define the relationship between a look-up number and value to be located for a calculation scale.

Tables  involved :

1)CALSCALE

A row in this table represents a CalculationScale. It can be used to perform a scale lookup to calculate a monetary amount for a given set of OrderItems. CalculationScale defines what the charge is based on when calculating different kinds of charges. For example, the shipping charge can be charged by total price, weight, quantity, etc.

2)CRULESCALE

CalculationScale can be associated with the CalculationRule through table CRULESCALE.

3)CALRANGE

Each row of this table represents a CalculationRange, which conceptually represents a row in a CalculationScale.

4) CALRLOOKUP

Each row in this table represents a CalculationRangeLookupResult, which is part of a CalculationRange.

To understand how we assign the calculated shipping charge result to an order item, we should know the general flow of calculation methods:

  1. InitializeCalculationUsage
  2. ApplyCalculationUsage calls:
  3. CalculationCodeCombine calls:

CalculationCodeQualify

  1. CalculationCodeCalculate calls:

CalculationRuleCombine calls:

CalculationRuleQualify

CalculationRuleCalculate calls:

CalculationScaleLookup

CalculationRange

  1. CalculationCodeApply
  2. SummarizeCalculationUsage
  3. FinalizeCalculationUsage

CalcodeCombine This method collect the promotions calcodes that are eligible for the given order items. The relationship between calculation code and promotion is defined in CLCDPROMO which is created during promotion creation. CalculationRuleCombine/CalculationRuleCalculate The data read from the CalcodeCombine is used to calculate the discounts/shipping charge etc. CalculationCodeApply The calcodes determined in CalcodeCombine and adjustments calculated in CalculationRuleCombine/CalculationRuleCalculate are finally applied to the order items in this step .

How many ways calcode can be attached

Cal codes can be attached in three ways

a)Direct attachment: Here, we can attach a calcode to order or orderitem directly.

SetOrderLevelParameterCmdImpl and SetOrderItemLevelParameterCmdImpl are responsible for populating the ORDCALCD, ORDICALCD tables.We can set the amount (setAmount()) and parameter type (setType())(fixed / Percentage) to this command.setOverride()sets whether the directly attached calculation codes will override the indirectly attached calculation codes and the default calculation code.ORDERITEMS Table  PREPAREFLAGS column value should be 8 . The default CalculationCodeCombineMethod will not look for direct attachments unless this flag is true(8).

ORDCALCD Table

This database table attaches a calculation code to an order. This table allows you to specify whether indirectly attached calculation codes should be ignored.

ORDICALCD Table

The database table attaches a calculation code to an OrderItem. This table allows you to specify whether indirectly attached calculation codes should be ignored.

b)Indirect attachment: Here, we attach a calcode to catalog or catalog group. Hence, every item in a particular catalog or catalog group is indirectly attached to calcode that is attached to that catalog or catalog group.

CATGPCALCD Table

This database table attaches a calculation code with catalog group in a specific store and, optionally, for a specific trading agreement. An example of a trading agreement is a contract. Information about contracts and trading agreements is provided in the online information. All catalog entries belonging to the catalog group are associated with the calculation code assigned to the catalog group.

CATENCALCD Table

This database table attaches a calculation code with one catalog entry or all catalog entries in a specific store and, optionally, for a specific trading agreement. An example of a trading agreement is a contract. Information about contracts and trading agreements is provided in the online information.

c)Default attachment: Here, we implicitly attach a calcode specifying a calcode to an orderitem in STENCALUSG table.

STENCALUSG Table

This database table defines the default calculation code for a calculation usage in a store or group of stores. This table also specifies the CalculationCodeCombine calculation method subclass that determines how multiple calculation codes for an OrderItem are combined.

Adjustment included discounts and surcharges. ORDADJSUT and ORDIADJSUT table holds the adjustment values.Discounts will be kept as negative values and surcharge will be kept as positive values.

Shipping Charge and Discount Calculation in Aurora Store

Shipping  Charge Calculation

For each of the shipping charge that we need to set up, we will have to use both the shipping and calculation data models. The relationship path listed below of the entries must be followed.
JURST <> JURSTGROUP <> JURSTGPREL <> CALCODE <> CALRULE<> SHPJCRULE <> CALSCALE <> CRULESCALE <> CALRANGE<> CALRLOOKUP

Shipping jurisdictions, rules and charges can be configured using calculation code data model within web sphere commerce.

SHIPMODE table consists of the shipping mode / method. SHIPMODEDESC table consists of the description of the shipping method defined above

JURST consists of a Jurisdiction definition and JURSTGROUP is the grouping of the Jurisdictions.

The JURST table also consists of the zip code range for which the shipping rule may apply.

JURSTGPREL table defines the relationship between JURST and JURSTGROUP.

The SUBCLASS field of JURSTGPREL defines whether it is a Shipping Jurisdiction (1) or Tax Jurisdiction (2)

SHPJCRULE contains the relationship between the Jurisdiction and the Shipping mode.

If multiple rows from the SHPJCRULE table qualify for the rule, then the row that has the highest precedence set will be applied.  If two rules have the same precedence then both the rules are applied.

Shipping Charge($16.93) Calculation Example in Aurora Store

Shipping Charge Calculation

Query: select orders_id,TOTALSHIPPING from ORDERS where orders_id =36002

ORDERS_IDTOTALSHIPPING
3600216.93000

Query: select orderitems_id ,SHIPCHARGE from ORDERITEMS where orders_id =36002

ORDERITEMS_IDSHIPCHARGE
1700028.46000
1700038.47000

Query: select CALCODE_ID ,CODE ,CALUSAGE_ID ,STOREENT_ID,CALMETHOD_ID, CALMETHOD_ID_APP ,CALMETHOD_ID_QFY from CALCODE where storeent_id =11051 and calcode_id in (10304)

CALCODE_IDCODECALUSAGE_IDSTOREENT_IDCALMETHOD_IDCALMETHOD_ID_APPCALMETHOD_ID_QFY
10304‘Shipping Charge ‘-211051-23-24-22

Query: select * from CALMETHOD where calmethod_id in (-22,-23,-24)

Query: select CALRULE_ID , CALCODE_ID , CALMETHOD_ID , CALMETHOD_ID_QFY from CALRULE where CALCODE_ID in (10304)

CALRULE_IDCALCODE_IDCALMETHOD_IDCALMETHOD_ID_QFY
1025310304-27-26
1025410304-27-26
1025510304-27-26
1025610304-27-26
1025710304-27-26
1025810304-27-26
1025910304-27-26
1026010304-27-26
1026110304-27-26
1026210304-27-26

Query: select * from SHPJCRULE where calrule_id in ( select calrule_id from CALRULE where CALCODE_ID =10304 and SHIPMODE_ID =11203)

CALRULE_IDSHPJCRULE_IDFFMCENTER_IDJURSTGROUP_IDPRECEDENCESHIPMODE_IDOPTCOUNTER
1025510253NULL102521.0112031
1026010274NULL102521.0112031

Query: select * from CRULESCALE where calrule_id in(10255,10260)

CALSCALE_IDCALRULE_IDOPTCOUNTER
10255102551
10260102601

Query: select * from CALRANGE where calscale_id in (10255,10260)

CALRANGE_IDCALSCALE_IDCALMETHOD_IDRANGESTARTCUMULATIVEFIELD1FIELD2FIELD3MARKFORDELETEOPTCOUNTER
1025510255-330.000000NULLNULLNULL01
1026010260-340.000000NULLNULLNULL01

Query: select * from CALRLOOKUP where calrange_id in (10255,10260)

CALRLOOKUP_IDSETCCURRCALRANGE_IDVALUEOPTCOUNTER
10255‘USD’1025512.950001
10260‘USD’102601.990001

As you can see shipping charge  in  the store is  displayed  as 16.93.

But the value column from CALRLOOKUP is 12.95+1.99 =14.94.

The reason for  this  is   calmethod_id  -34 is charge per unit shipping. And calmethod_id -33 is fixed shipping.

Here the actual shipping charge is   12.95(calmethod_id -33 ) +2*1.99(calemthod_id -34).

12.95+2*199 = 16.93

Query: select * from CALMETHOD where calmethod_id in (-33,-34)

Discount Calculation Example in Aurora Store  (25 $ off of purchase of 200$ and above)

Discount Calculation

Tables involved in Discount calculation is as follows

Query: select px_promotion_id,STOREENT_ID from px_promotion where storeent_id=11051

PX_PROMOTION_IDSTOREENT_ID
1000030211051
1000030611051
1000030111051
1000030711051
1000030311051
1000030511051
1000030411051

Query: select * from CLCDPROMO where px_promotion_id in (select px_promotion_id from px_promotion where storeent_id=11051) and calcode_id=10309

PX_PROMOTION_IDCALCODE_IDOPTCOUNTER
10000305103091

Query: select calcode_id,code,calusage_id from CALCODE where calcode_id in(select calcode_id from CLCDPROMO where px_promotion_id in (select px_promotion_id from px_promotion where storeent_id=11051))

CALCODE_IDCODECALUSAGE_ID
10305‘Free Ground Shipping ‘-1
10306‘Discount for Repeat Customers ‘-1
10307‘Register and Save ‘-1
10308‘Save 10% on all orders today ‘-1
10309‘Save $25 on all orders over $200 USD ‘-1
10310‘Flat Shipping ‘-1
10311‘Furniture Category Discount ‘-1

PX_ELEMENTNVP

This table contains promotion element values.

PX_ELEMENT

This table contains promotion element information.
Query:

select PX_ELEMENT_ID, PX_PROMOTION_ID,NAME,TYPE, SUBTYPE from PX_ELEMENT where PX_PROMOTION_ID in( select px_promotion_id from CLCDPROMO where px_promotion_id in (select px_promotion_id from px_promotion where storeent_id=11051 and px_promotion_id=10000305))

PX_ELEMENT_IDPX_PROMOTION_IDNAMETYPE
36510000305‘TargetingCondition1’‘TargetingCondition’
36610000305‘PurchaseCondition2’‘PurchaseCondition’
36710000305‘DiscountRange3’‘DiscountRange’
36810000305‘DiscountRange4’‘DiscountRange’
38110000305‘IncludePaymentTypeIdentifier1’‘IncludePaymentTypeIdentifier’

Query: select * from PX_ELEMENTNVP where PX_ELEMENT_ID in (select PX_element_id from PX_ELEMENT where PX_PROMOTION_ID=10000305)

PX_ELEMENTNVP_IDPX_ELEMENT_IDNAMEVALUEOPTCOUNTER
468366‘Currency’‘USD’1
469367‘LowerBound’‘0’1
470367‘AmountOff’‘0’1
471368‘LowerBound’‘200’1
472368‘AmountOff’’25’1
486381‘PaymentType’‘Any’1

1)Shipping Charge Customization scenario

In some cases you may need to customize your shipping calculation.For example, if an item and its parent product are under different categories and each category has a different shipping calculation code attached, then both shipping calculation codes will be picked up by the runtime, and the final result will be cumulative.

To avoid this, you can customize the code, by overriding CalculationCodeCombineCmdImpl.getIndirectlyAttachedCodes().

Steps

1.Create a task command that extends CalculationCodeCombineCmd.

public interface EXShippingCalculationCodeCombineCmd extends

CalculationCodeCombineCmd {

public static final String defaultCommandClassName =

“com.example.commerce.order.calculation.EXShippingCalculationCodeCombineCmdImpl”;

}

2.Create an implementation of the task command that extends CalculationCodeCombineCmdImpl.

EXShippingCalculationCodeCombineCmdImpl extends

CalculationCodeCombineCmdImpl implements

EGShippingCalculationCodeCombineCmd {

}

  1. Extend the default behavior of getIndirectlyAttachedCodes(Item) to filter the   Shipping

Caculation codes

protected CalculationCodeAccessBean[]

getIndirectlyAttachedCodes(Item

iItem) {

CalculationCodeAccessBean[] indirectCodes =  super.getIndirectlyAttachedCodes(iItem);

// From the   indirectCodes  Array  remove the product level ones

}

  1. Register the task command as a calculation method in the CALMETHOD table.

INSERT INTO CALMETHOD VALUES (some unique ID, -1, -2,

‘com.example.commerce.order.calculation.EXShippingCalculationCodeCombineCmd

‘, ‘EX custom CalculationCodeCombine for shipping’, 1,

‘EXShippingCalculationCodeCombine’)

5.Set the calculation method as the CalculationCodeCombine calculation method for shipping:

UPDATE STENCALUSG SET ACTCC_CALMETHOD_ID=calculation methodID WHERE

STOREENT_ID IN (store ID, -1) AND CALUSAGE_ID=-2

Note: Do not customize CalculationHelper.getProductAndItemIds(nCatalogEntryId).

2)Setting up multiple shipping charges based on total price and jurisdiction

Comments

Post a Comment

Popular posts from this blog

Websphere Commerce Data Load using CSV \Websphere Commerce Data Load using XML

-
Websphere Commerce Data Load using CSV \Websphere Commerce Data Load using XML Data can be loaded into WCS tables using data load utility. Data load utility works in the steps as mentioned below. 1.Data Reader The data reader reads the input file, and transforms the input into a data object. Data objects are typically map objects. Eg: CSV data reader \XML Data Reader 2.Business Object Builder The business object builder instantiates the business object and populates it from the data in the data object. Eg:The table object builder 3. Business Object Mediator The business object mediator converts the business object into physical object. The table object mediator is always used with the table object builder. Eg: table-based mediator 4. Data Writer The data writer writes the physical object into the database. This tutorial uses the default data writer to write the generated physical o...
Read more

Websphere Commerce Catalog Subsystem

-
  Websphere Commerce Catalog Subsystem The Catalog Subsystem contains all logic and data relevant to an online catalog, including catalog groups (or categories), catalog entries, and any associations or relationships among them.    Master Catalog It is the single catalog that contains all products, items, relationships, and standard prices for everything that is for sale in your store. Every store in the Web Sphere Commerce system must have a master catalog. It is possible to share the master catalog across stores. Master Catalog Restrictions 1)The master catalog must be a proper tree, which means that there are no cycles. e.g.The parent category "Women" has a subcategory "WomenDress". It is important that WomenDress and any of WomenDress's subcategories like tops,jeans etc are not the parent category of "Women" category. 2)A catalog entry or category cannot belong to multiple parent categories in the master...
Read more