Friday, 4 July 2014

Websphere Commerce Calculation Framework Part 1







OrderCalculateCmd

OrderCalculateCmd invokes promotion engine. PromotionEngineOrderCalculateCmdImpl is the implementation class of OrderCalculateCmd. OrderCalculate is invoked when we add an item to cart, delete, update cart or merge cart. This command calculates the order  total based on the order in which calculationUsageId is passed into the command. Calculation usages can be discounts (-1), shipping (-2), sales tax (-3), shipping tax, (-4) coupons (-5), surcharge (-6) and shipping adjustment (-7).


 
CalculateOrderCommand iterates over the array of Calusageids passed in requestproperties and invokes initialize, apply and summarize methods accordingly for each calusageid.As a part of InitializeAdjustment and InitializeCoupon commands, respective calmethod and calrule commands invoke ptomotionEngine 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.

2. 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 below predefined calculation usages:

Calusage_id      Description
-1                         Discount
-2                         Shipping
-3                         Sales Tax
-4                         Shipping Tax
-5                         Coupon
-6                         Surcharge
-7                         Shipping Adjustment
 

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



3. 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 .


4. 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.

5. 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:
     a. CalculationCodeCombine calls:
               CalculationCodeQualify
      b. CalculationCodeCalculate calls:
                 CalculationRuleCombine calls:
                               CalculationRuleQualify
                 CalculationRuleCalculate calls:
                              CalculationScaleLookup
                              CalculationRange
       c. CalculationCodeApply
3. SummarizeCalculationUsage
4. 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

Calculation Framework
Shipping Charge Calculation





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


ORDERS_ID
TOTALSHIPPING
36002
16.93000

Query: select orderitems_id ,SHIPCHARGE from ORDERITEMS where orders_id =36002
ORDERITEMS_ID
SHIPCHARGE
170002
8.46000
170003
8.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_ID
CODE
CALUSAGE_ID
STOREENT_ID
CALMETHOD_ID
CALMETHOD_ID_APP
CALMETHOD_ID_QFY
10304
'Shipping Charge '
-2
11051
-23
-24
-22

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


Websphere Commerce CALMETHOD table





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

CALRULE_ID
CALCODE_ID
CALMETHOD_ID
CALMETHOD_ID_QFY
10253
10304
-27
-26
10254
10304
-27
-26
10255
10304
-27
-26
10256
10304
-27
-26
10257
10304
-27
-26
10258
10304
-27
-26
10259
10304
-27
-26
10260
10304
-27
-26
10261
10304
-27
-26
10262
10304
-27
-26

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

CALRULE_ID
SHPJCRULE_ID
FFMCENTER_ID
JURSTGROUP_ID
PRECEDENCE
SHIPMODE_ID
OPTCOUNTER
10255
10253
NULL
10252
1.0
11203
1
10260
10274
NULL
10252
1.0
11203
1

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

CALSCALE_ID
CALRULE_ID
OPTCOUNTER
10255
10255
1
10260
10260
1

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

CALRANGE_ID
CALSCALE_ID
CALMETHOD_ID
RANGESTART
CUMULATIVE
FIELD1
FIELD2
FIELD3
MARKFORDELETE
OPTCOUNTER
10255
10255
-33
0.00000
0
NULL
NULL
NULL
0
1
10260
10260
-34
0.00000
0
NULL
NULL
NULL
0
1

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

CALRLOOKUP_ID
SETCCURR
CALRANGE_ID
VALUE
OPTCOUNTER
10255
'USD'
10255
12.95000
1
10260
'USD'
10260
1.99000
1


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) 



Websphere Commerce CALMETHOD table






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

AuroraCalculationFrameWork
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_ID
STOREENT_ID
10000302
11051
10000306
11051
10000301
11051
10000307
11051
10000303
11051
10000305
11051
10000304
11051

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_ID
CALCODE_ID
OPTCOUNTER
10000305
10309
1

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_ID
CODE
CALUSAGE_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_ID
PX_PROMOTION_ID
NAME
TYPE
365
10000305
'TargetingCondition1'
'TargetingCondition'
366
10000305
'PurchaseCondition2'
'PurchaseCondition'
367
10000305
'DiscountRange3'
'DiscountRange'
368
10000305
'DiscountRange4'
'DiscountRange'
381
10000305
'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_ID
PX_ELEMENT_ID
NAME
VALUE
OPTCOUNTER
468
366
'Currency'
'USD'
1
469
367
'LowerBound'
'0'
1
470
367
'AmountOff'
'0'
1
471
368
'LowerBound'
'200'
1
472
368
'AmountOff'
'25'
1
486
381
'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 {
}

3. 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

4. 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
http://www-01.ibm.com/support/docview.wss?uid=swg21216446 

Please read the post on Websphere Commerce Calculation Framework Part 2  for more details.



9 comments:

  1. Replies
    1. How did you get the calcode_id(10309) you used in the above calculation?

      Delete
  2. Get the CALCODE_ID of the the CALUSAGE_ID =-1 (That means discount) for the StoreId.

    Query Used.
    SELECT CALCODE_ID from calcode where calusgae_id=-1 and storeent_id =11051;

    ReplyDelete
  3. Hello Deepak,

    We are customizing OrderCalculateCmd where in CalcodeCalulate is customized for calusage -1.

    We are facing issue in retrieving ORDERITEMS SHIPCHARGE value (table have non-zero entry) in checkout flow(shopping cart to shipping billing page).Tried using OrderItemAccessBean/OrderItemDataBean/JDBC helper classes.The value comes as 0$.The value is readable when moving to shopping cart from minishopcart.
    Any insight to this issue?



    Thanks
    Vidya

    ReplyDelete
    Replies
    1. Hi Vidhya,
      Ideally it must come come in in the accessbean. When are you saving the ship charge? In case if you are saving it as a part of same transaction, you might have to commit the bean and call refreshcCopyHelper().

      Delete
  4. Hi, I have this sql which is taking too many cpu seconds in WC db2 db:
    SELECT T1.CALCODE_ID FROM CALCODE T1 WHERE T1.PUBLISHED=1 AND T1.STOREENT_ID IN(?,?,?) AND T1.CALUSAGE_ID=? AND ((T1.CALCODE_ID NOT IN (SELECT T4.CALCODE_ID FROM CALCODEMGP T4))
    OR (T1.CALCODE_ID IN (SELECT T5.CALCODE_ID FROM CALCODEMGP T5 WHERE T5.MBRGRP_ID IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)))) AND
    (T1.CALCODE_ID IN (SELECT T10.CALCODE_ID FROM CLCDPROMO T10, PX_PROMOTION T11 WHERE T10.PX_PROMOTION_ID = T11.PX_PROMOTION_ID AND T11.CDREQUIRED=0)) AND
    T1.CALCODE_ID IN (SELECT T2.CALCODE_ID FROM CATENCALCD T2 WHERE (T2.CATENTRY_ID = ? OR T2.CATENTRY_ID IN (SELECT T3.CATENTRY_ID_CHILD FROM CATENTREL T3
    WHERE T3.CATENTRY_ID_PARENT=? AND T3.CATRELTYPE_ID='PRODUCT_ITEM')) AND T2.STORE_ID IN(?,?) AND (T2.TRADING_ID IS NULL OR T2.TRADING_ID IN (?))) UNION
    SELECT T7.CALCODE_ID FROM CATGPCALCD T7, CALCODE T9 WHERE T7.CATGROUP_ID IN (?,?,?,?,?,?,?) AND T7.CALCODE_ID = T9.CALCODE_ID AND T9.PUBLISHED=1 AND
    T9.STOREENT_ID IN(?,?,?) AND T9.CALUSAGE_ID=? AND ( ( T7.CALCODE_ID NOT IN (SELECT distinct T8.CALCODE_ID FROM CALCODEMGP T8)) OR (T7.CALCODE_ID IN (SELECT T5.CALCODE_ID FROM CALCODEMGP T5 WHERE T5.MBRGRP_ID IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?))) ) AND (T7.CALCODE_ID IN (SELECT T13.CALCODE_ID FROM CLCDPROMO T13, PX_PROMOTION T12 WHERE T13.PX_PROMOTION_ID = T12.PX_PROMOTION_ID AND T12.CDREQUIRED=0))

    Can you please help here?

    ReplyDelete
  5. Hi,

    I have a requirement in which we have to prevent the particular product to sell in the particular region so can anyone let me know how can we achieve this requirement in WCS.

    ReplyDelete
    Replies
    1. You need to use multiple Sales Category region wise.

      Delete
  6. This comment has been removed by the author.

    ReplyDelete