| Set up multiple shipping charges based on total price and jurisdiction |
| Problem(Abstract) |
| You want to set up shipping charges. Shipping charges are
configured based on jurisdiction, which means that you can have different
fixed prices based on the total cost of an order.
The following example will have two jurisdictions, Canada and USA, each with a different shipping charge--$20 and $30 respectively. In this scenario, shipping is free for orders over $250 in Canada, and over $300 in the USA. The example also shows the relationship between the shipping charges and jurisdictions, giving a background to the shipping charges and calculation framework, which may allow for other similar configurations. |
To set up the basic elements of shipping, refer to the following link to the information center:
http://publib.boulder.ibm.com/infocenter/wc56help/index.jsp?topic=/com.ibm.commerce.user.doc/tasks/tshsetsp.htm
To set up multiple shipping charges (for example, one for Canada and one for the USA):
- Set up two shipping jurisdictions, Canada and USA, using the instructions on the following page:http://publib.boulder.ibm.com/infocenter/wc56help/index.jsp?topic=/com.ibm.commerce.user.doc/tasks/tshadspj.htm
- Set up a shipping mode to associate to this shipping schema using the instructions on the following page (bonus):http://publib.boulder.ibm.com/infocenter/wc56help/index.jsp?topic=/com.ibm.commerce.user.doc/tasks/tshadspp.htm
- Define one shipping codes per jurisdiction (Canada_Bonus, USA_Bonus) using the following page:http://publib.boulder.ibm.com/infocenter/wc56help/index.jsp?topic=/com.ibm.commerce.user.doc/tasks/tshdfsc.htm
- Define the initial shipping charge, for example, a shipping charge of $20 for Canada_Bonus, and $30 for USA_Bonus. Select the Define fulfilment options check box, so that the shipping code/charge is associated with the shipping mode and shipping jurisdiction created in step 1. If this check box is not selected, the code or charge will not be associated with a shipping jurisdiction. Refer to the instructions listed on the following page:http://publib.boulder.ibm.com/infocenter/wc56help/index.jsp?topic=/com.ibm.commerce.user.doc/tasks/tshshch.htm
- To activate the shipping code, refer to instructions on the following page:http://publib.boulder.ibm.com/infocenter/wc56help/index.jsp?topic=/com.ibm.commerce.user.doc/tasks/tshactsh.htm
- To update the registry so the shipping charges will load in the store, refer to instructions on the following page:http://publib.boulder.ibm.com/infocenter/wc56help/index.jsp?topic=/com.ibm.commerce.admin.doc/tasks/tacregistry.htm
- Test your shipping charges for USA and Canada to ensure that your settings are working.
Now the free shipping charge will be set up once a customer spends over
$250 in Canada, or over $300 in the USA.
For each of the shipping charge that has been set up, one will have to use
these two data models. The relationship path of the entries must be
followed.
JURISTGROUP <-> SHPJCRULE <-> CALRULE <-> CRULESCALE
<-> CALSCALE <-> CALRANGE <-> CALRLOOKUP
http://publib.boulder.ibm.com/infocenter/wc56help/index.jsp?topic=/com.ibm.commerce.data.doc/refs/rdmcalsc.htmhttp://publib.boulder.ibm.com/infocenter/wc56help/index.jsp?topic=/com.ibm.commerce.data.doc/refs/rdmcalrules.htm
Hence, the relationship from the jurisdiction up to the shipping cost has
to be followed.
So, starting with JURSTGROUP, this should show something that looks
familiar in the CODE field. For example, 'Canada' is the shipping
jurisdiction.
select * from JURSTGROUP where
CODE='Canada'
| JURSTGROUP_ID | DESCRIPTION | SUBCLASS | STOREENT_ID | CODE | MARKFORDELETE |
| 10651 | NULL | 1 | 10001 | 'Canada ' | 0 |
Using CALRULE, the relationship based on JURSTGROUP_ID can be found.
select * from SHPJCRULE where JURSTGROUP_ID=10651
| CALRULE_ID | SHPJCRULE_ID | FFMCENTER_ID | JURSTGROUP_ID | PRECEDENCE | SHIPMODE_ID |
| 10003 | 11151 | 10001 | 10651 | 1.0 | 10001 |
The result is the CALRULE_ID.
Thus, in CALRULE,
select * from CALRULE where CALRULE_ID=10003
| CALRULE_ID | CALCODE_ID | STARTDATE | TAXCGRY_ID | ENDDATE | SEQUENCE | COMBINATION | CALMETHOD_ID | CALMETHOD_ID_QFY | FIELD1 | FIELD2 | FLAGS | IDENTIFIER |
| 10003 | 10004 | 1900-01-01 00:00:00.0 | NULL | 2100-01-01 00:00:00.0 | 0.0 | 2 | -27 | -26 | NULL | NULL | 1 | 1 |
By using the CRULESCALE table, you can see the relationship between CALRULE and CALSCALE.
select * from CRULESCALE where CALRULE_ID=10003
| CALSCALE_ID | CALRULE_ID |
| 11152 | 10003 |
Hence, the CALSCALE record contains some of the details, as they show up in WebSphere Commerce Accelerator. In this case, CALUSAGE_ID -2 means this is shipping. CALMETHOD_ID = -28, means the scale range is based on items purchased.
select * from CALSCALE where CALSCALE_ID=11152
| CALSCALE_ID | QTYUNIT_ID | CODE | DESCRIPTION | STOREENT_ID | CALUSAGE_ID | SETCCURR | CALMETHOD_ID | FIELD1 |
| 11152 | NULL | 'Regular Delivery ' | 'Regular Delivery per order charge' | 10001 | -2 | NULL | -28 | NULL |
The CALSCALE has to be based on the net price of the order, not the total number of items purchased. This can be updated in the database.
update CALSCALE set CALMETHOD_ID=-31 where CALSCALE_ID=11152
| CALSCALE_ID | QTYUNIT_ID | CODE | DESCRIPTION | STOREENT_ID | CALUSAGE_ID | SETCCURR | CALMETHOD_ID | FIELD1 |
| 11152 | NULL | 'Regular Delivery ' | 'Regular Delivery per order charge' | 10001 | -2 | NULL | -31 | NULL |
This CALSCALE is associated to a particular CALRANGE.
select * from CALRANGE where CALSCALE_ID=11152
| CALRANGE_ID | CALSCALE_ID | CALMETHOD_ID | RANGESTART | CUMULATIVE | FIELD1 | FIELD2 | FIELD3 | MARKFORDELETE |
| 11152 | 11152 | -33 | 0.00000 | 0 | NULL | NULL | NULL | 0 |
RANGESTART of 0.0000 indicates that this CALRANGE starts at 0, and applies to any order of $0 or higher, since the CALMETHOD_ID was changed to -31 in the related CALSCALE table, as per the previous step. If the CALMETHOD_ID was not changed from -28, this CALRANGE would apply to orders with 0 or more items.
In the CALRANGE table, CALMETHOD_ID=-33 is also there. This is a fixed amount, and the shipping cost is a fixed value of $20.00. You can change the value based on how the shipping cost is set up. For the example mentioned above, it will need to be -33.
The final shipping charge is defined in the CALRLOOKUP table, related to the CALRANGE.
select * from CALRLOOKUP where CALRANGE_ID=11152
| CALRLOOKUP_ID | SETCCURR | CALRANGE_ID | VALUE |
| 11152 | 'USD' | 11152 | 20.00000 |
The CALRLOOKUP table is where the shipping cost is defined. The example below has a shipping cost of $20. The cost is fixed, because it was confirmed earlier that the CALMETHOD_ID in the related CALRANGE entry, was set to -33.
From this information, you can trace each shipping jurisdiction up to the shipping code or charge, or backwards from the shipping charge (CALRLOOKUP) to the jurisdiction (JURSTGROUP).
This relationship can be followed in the database, and a new range needs to be added in the shipping price. This ensures that there is free shipping if the total order is over $250 for 'Canada'.
If the above relationship is satisfied, you can see from JURSTGROUP to CALRLOOKUP, and can charge $20 for each order in this jurisdiction.
A new range has to be defined. This means that two ranges will be there with one range for any order over $0 (charged $20), and another range for any order over $250 (charged $0).
An entry has to be added to CALRANGE, so that any order on the original CALSCALE (in the above example 11152), which has a value over $250 will use this code. The CUMULATIVE=0 has to be set, so that the other charges (compound) to this shipping charge are not included.
CALRANGE_ID is a unique key in this table
insert into CALRANGE values
(11154,11152,-33,250.00000,0,NULL,NULL,NULL,0)
| CALRANGE_ID | CALSCALE_ID | CALMETHOD_ID | RANGESTART | CUMULATIVE | FIELD1 | FIELD2 | FIELD3 | MARKFORDELETE |
| 11152 | 11152 | -33 | 0.00000 | 0 | NULL | NULL | NULL | 0 |
| 11154 | 11152 | -33 | 250.00000 | 0 | NULL | NULL | NULL | 0 |
Now that this is added, a charge based on this range has to be defined. To get a charge of $0 in this range, one has to add using the SQL below (note that this entry is associated to the newly created CALRANGE entry).
CALRLOOKUP_ID is a unique key in this table
insert into CALRLOOKUP values
(11154,'USD',11154,0)
| CALRLOOKUP_ID | SETCCURR | CALRANGE_ID | VALUE |
| 11152 | 'USD' | 11152 | 20.00000 |
| 11154 | 'USD' | 11154 | 0.00000 |
In such a scenario, if their order is over 250 (CALRANGE.RANGESTART), the charge will be 0 (CALRLOOKUP.VALUE).
Update the registry for the shipping charges to load in the store http://publib.boulder.ibm.com/infocenter/wc56help/index.jsp?topic=/com.ibm.commerce.admin.doc/tasks/tacregistry.htm
Test your shipping charges for Canada to ensure that your configurations work.
Repeat this formula to define a shipping charge for USA, which is free for orders over $300.
Notes:
The meaning of the CALMETHOD setting can be found in the database CALMETHOD.DESCRIPTION field for each CALMETHOD entry.
| Segment | Product | Component | Platform | Version | Edition |
|---|---|---|---|---|---|
| Commerce | WebSphere Commerce Enterprise | Configuration | AIX, i5/OS, Linux, Solaris, Windows | 6.0 | Enterprise |
| Commerce | WebSphere Commerce Business Edition | Configuration | AIX, i5/OS, Linux, Solaris, Windows | 5.6.1 | Business Edition |
| Commerce | WebSphere Commerce - Express | Configuration | AIX, i5/OS, Linux, Solaris, Windows | 5.6.1, 6.0 | Express |
| Commerce | WebSphere Commerce Developer Enterprise | Configuration | AIX, i5/OS, Linux, Solaris, Windows | 6.0 | Enterprise |
| Commerce | WebSphere Commerce Developer Business Edition | Configuration | AIX, i5/OS, Linux, Solaris, Windows | 5.6.1 | Developer Business Edition |
| Commerce | WebSphere Commerce Developer Professional Edition | Configuration | AIX, i5/OS, Linux, Solaris, Windows | 6.0 | Developer Professional Edition |
| Commerce | WebSphere Commerce Developer Express | Configuration | AIX, i5/OS, Linux, Solaris, Windows | 5.6.1, 6.0 | Developer Express |
| Document Information |
Current web document: http://www.ibm.com/support/docview.wss?uid=swg21216446