When I started to analyze (summer 2015) the possibilities to migrate non-ATP stores to the ATP model the IBM Knowledge Center was claiming the migration was not supported. However, not supported does not mean not possible.
So, I dig it and found a "simple" way to accomplish the migration.
Now (since 2016), finally, the switch of an existing store to the ATP mode is supported again (old version of WCS had even a migration script).
In this brief article I'll report few tips about the "migration" of existing stores to the ATP inventory model.
WebSphere Commerce professional
A simple flag, STORE.INVENTORYSYSTEM, determines the type of inventory the store manages. Of course, this configuration has an huge impact of the whole order flow and so the handling of the stock.
I started with an extended site store which had the non-ATP model; STORE.INVENTORYSYSTEM was -2. The aim was to switch this store to the ATP model.
The switch to the ATP consist on update this property to -1. However, there are multiple things to take in account since an ATP model is not so trivial to handle.
The value of the property FFMCENTER.INVENTORYOPFLAGS should be determined answering the following questions:
- Will order accept multiple items? (multi-item)
- Do you want inventory check operation always appear to succeed? (no-check)
- Do you want the allocate operation to check inventory, but not allocate it (the allocate operation appears to succeed if the check succeeds)? (no-allocate)
- Do you want the backorder operation to always fail so no inventory is ever backordered? (no-backorder)
There are multiple properties should be filled in order to set properly the ATP behavior:
- What’s the expiration time of the inventory allocation? STORE.ALLOCATIONGOODFOR
- What’s the maximum number of days an item can be backordered? STORE.MAXBOOFFSET
- What’s the default value of days an item can be backordered (to use when an estimated availability time cannot be determined for a backordered order item)? STORE.DEFAULTBOOFFSET
- What’s the amount of time before the requested shipping date (ORDERITEMS.REQUESTEDSHIPDATE) when inventory allocation is performed? STORE.ALLOCATIONOFFSET
- What’s the maximum time that can be requested for a future shipping date after the date when the requested ship date (ORDERITEMS.REQUESTEDSHIPDATE) is entered? STORE.MAXFOOFFSET
For the ATP WCS provides a set of Scheduled Jobs could be key to deliver properly the customer needs.
Releases order item allocations that exceed the expiration time. The scheduler reverses inventory with expired allocation for order items with status 'P','I','W','N', and inventory status is in 'ALLC' or 'BO'.
Reconciles expected inventory receipts to existing backorders to determine current expected ship dates. The command evaluates all backordered order items against current expected inventory receipts. If there are order items with an inventory status of 'BO', it reconciles to determine the expected ship date.
For locked orders in states 'C', 'M', 'I', 'B', 'L', 'A', and have some order items whose inventory status in 'FUT', this scheduler command allocates inventory for these order items, re-prepares orders, then process the orders
For the given store, get the list of locked orders that have a status of ('C', 'M', 'I', 'B', 'L', 'A') in the ORDERS table and an INVENTORYSTATUS of BO in the ORDERITEMS table. The command allocates inventory for these order items, re-prepare orders and then process the orders.
For each of these orders:
- See if there are any order items that are not validly allocated. (An order item is validly allocated if it is allocated, the quantity, fulfillment center, and shipping address are the same as when it was allocated, and the allocation has not yet expired.)
- If there is at least one order item that is not validly allocated, call AllocateInventoryCmd for the order.
- If an item is allocated from a different fulfillment center than the one originally requested and AllocateInventoryCmd has unlocked the order, then call ReprepareOrderCmd to recalculate shipping charges and taxes.
- Call ProcessOrderCmd.
Queries orders in the following states: 'C', 'M', 'I', 'B', 'L', 'A'. It checks whether the order is ready for release. If the order is ready to release to fulfillment, it changes the order status to 'R'.
So, according to the requirements above scheduled jobs should be configured and enabled or merely disabled.
ATP data model and data load
One of the highly impacting component is the data load since the ATP data model contains new tables and relationships to deal with.
In particular, the catalog data load should properly fill the following tables- for the correct work of the ATP process-:
The stock have to be loaded now in the table RECEIPT and, according to the specific ATP model needed, there are new tables to fill
- ATP inventory adjustment (additional tables involved INVADJUST, INVADJDESC, INVADJCODE),
- ATP inventory expected (additional tables involved VENDOR, RA, RADETAIL, RABACKALLO),
- ATP inventory fulfillment (additional tables involved ORDRELEASE, PICKBATCH, MANIFEST, ORDPICKHST, ORDSHIPHST),
- ATP inventory orders (additional tables involved ORDRELEASE, PICKBATCH, MANIFEST, ORDPICKHST, ORDSHIPHST, BKORDALLOC, RABACKALLO, BKORDERITEM), and
- ATP inventory reservations (additional tables involved INVRSRVTYP, INVRSRVDSC).
So, the Inventory data load should be accurately planned and implemented.
Most of the effort will be probably spend of the configuration of the data load (to take in account for any eventual plan).
Front end and the expected date
Once a product have been configured as backorderable it will appear in the following way (in Aurora) if it's out of stock (no stock in RECEIPT):
Than, the checkout will show the customer when it will be available (the expected date):
In order to properly configure the expected date the data load should fill the tables RA and RADETAIL (as mentioned above).
If these tables are not filled the expected date is calculated according to the property STORE.DEFAULTBOOFFSET.
Some useful example of data load CSV files
Example of CSV non-ATP
CurrentStoreIdentifier,PartNumber,CatEntryStoreIdentifier,FulfillmentCenterId,FulfillmentCenterName,Quantity,QuantityUnit,Delete Sample B2B Store,Cords-Black-29W x 28L,Extended Sites Catalog Asset Store,10101,Madisons Home,201,C62,0
Example of CSV ATP
PartNumber,ParentPartNumber,Type,INVQuantityMeasure,INVQuantityMultiple,TrackingInventory,BackOrderable,ReleaseSeparately,Creditable,ForceBackorder,ReturnNotDesired,MinQTYForSplit,PickingMethod,Discontinued,Delete Cords-Black-29W x 28L,Cords,ItemBean,C62,1,Y,Y,N,Y,N,N,0,F,N,0
Example of CSV ATP (backorder enabled)
PartNumber,ParentPartNumber,Type,INVQuantityMeasure,INVQuantityMultiple,TrackingInventory,BackOrderable,ReleaseSeparately,Creditable,ForceBackorder,ReturnNotDesired,MinQTYForSplit,PickingMethod,Discontinued,Delete GSH020_2008,,ProductBean,C62,,,,,,,,,,, GSH020_200801,GSH020_2008,ItemBean,C62,1,Y,Y,N,Y,N,N,0,F,N,0
The ATP inventory configuration data are typically loaded together with catalog entry data in a single CSV file for initial loading, because each ATP inventory configuration data is related to a catalog entry record in the database. In this case, CatalogEntryMediator is used to load data.
In another case, catalog entry data are initially loaded by CatalogEntryMediator without ATP inventory configuration data, so default ATP inventory configuration data are loaded in to database. If user wants to modify the ATP inventory configuration data after that, FulfillmentPropertyMediator can be used to load only ATP inventory configuration data without impact to existing catalog entry data.
Example of CSV ATP (load the receipt)
PartNumber,CreateTime,ReceiptDate,Quantity,Cost,Currency,VendorName,Comment1,Comment2 GSH020_200801,2015-06-03 01:01:01.123456,2015-06-03 00:00:00.0,1,1,USD,eFEP8 Vendor,comment 1,comment 2
Example of CSV ATP (load the expected date)
VendorName,RACreateTime,PartNumber,ExpectedDate,Quantity,RADetailComment eFEP8 Vendor,2015-06-02 11:36:01.123456,GSH020_200801,2015-06-28 00:00:00.0,10,radetail comment
Some useful query to check the ATP data
Query RECEIPT knowing the CATALOG_ID, STORE_ID and the PARTNUMBER
SELECT RECEIPT_ID, VERSIONSPC_ID, QTYRECEIVED, QTYINPROCESS, QTYONHAND, QTYINKITS FROM RECEIPT WHERE VERSIONSPC_ID IN (SELECT VERSIONSPC_ID FROM VERSIONSPC WHERE ITEMSPC_ID IN (SELECT ITEMSPC_ID FROM ITEMSPC WHERE BASEITEM_ID IN (SELECT BASEITEM_ID FROM STOREITEM WHERE BASEITEM_ID IN (SELECT BASEITEM_ID FROM CATENTRY WHERE CATENTRY_ID IN (SELECT CATENTRY_ID FROM CATGPENREL WHERE CATENTRY_ID IN (SELECT CATENTRY_ID FROM CATENTRY WHERE PARTNUMBER = '<PARTNUMBER>') AND CATALOG_ID = <CATALOG_ID>) ) AND STOREENT_ID = <STORE_ID>) AND PARTNUMBER = '<PARTNUMBER>') )
Query ITEMFFMCTR knowing the CATALOG_ID, STORE_ID and the PARTNUMBER
SELECT * FROM ITEMFFMCTR WHERE ITEMSPC_ID IN (SELECT ITEMSPC_ID FROM ITEMSPC WHERE BASEITEM_ID IN (SELECT BASEITEM_ID FROM STOREITEM WHERE BASEITEM_ID IN (SELECT BASEITEM_ID FROM CATENTRY WHERE CATENTRY_ID IN (SELECT CATENTRY_ID FROM CATGPENREL WHERE CATENTRY_ID IN (SELECT CATENTRY_ID FROM CATENTRY WHERE PARTNUMBER = '<PARTNUMBER>') AND CATALOG_ID = <CATALOG_ID>) ) AND STOREENT_ID = <STORE_ID>) AND PARTNUMBER = '<PARTNUMBER>')