JURISTGROUP <-> SHPJCRULE <-> CALRULE <-> CRULESCALE <-> CALSCALE <-> CALRANGE <-> CALRLOOKUP
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'
Using CALRULE, the relationship based on JURSTGROUP_ID can be found.
select * from SHPJCRULE where JURSTGROUP_ID=10651
The result is the CALRULE_ID.
Thus, in CALRULE,
select * from CALRULE where CALRULE_ID=10003
By using the CRULESCALE table, you can see the relationship between CALRULE and CALSCALE.
select * from CRULESCALE where CALRULE_ID=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
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
This CALSCALE is associated to a particular CALRANGE.
select * from CALRANGE where CALSCALE_ID=11152
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
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.
Note: CALRANGE_ID is a unique key in this table
insert into CALRANGE values (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).
Note: CALRLOOKUP_ID is a unique key in this table
insert into CALRLOOKUP values (11154,'USD',11154,0)
In such a scenario, if their order is over 250 (CALRANGE.RANGESTART), the charge will be 0 (CALRLOOKUP.VALUE).
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.
Note: 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).
Note: 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).
Notes:
The meaning of the CALMETHOD setting can be found in the database CALMETHOD.DESCRIPTION field for each CALMETHOD entry.
The meaning of the CALMETHOD setting can be found in the database CALMETHOD.DESCRIPTION field for each CALMETHOD entry.
In case we want shipping charge based on category id or product..
There are two following table which has linking between product/category with CALCODE_ID
- CATENCALCD -- > For Product and CALCODE_ID linking
- CATGPCALCD --> For Category and CALCODE_ID linking
