E-Commerce Item Availability Processing

Purpose: Your options in sending item availability information to the web storefront include:

• a periodic function to update an extract table in the CWSerenade database

• a stored procedure to directly update the table in the web storefront’s database

• an XML message; see the Generic Inventory Download API

Selecting an item availability processing method: The available options and some considerations for each are described below.

Option	Considerations	More Information
periodic function to update the EC Item Availability Table	• enables you to use the scheduling options in CWSerenade to run extracts of item availability according to your business requirements • supports sending updates as needed when the available quantity for an item falls below a specified threshold • includes rules for calculating the available quantity and expected purchase order dates to report for special types of items, such as sets and drop ships • limits the item selection to those already downloaded to the web • requires a mechanism to transfer the data from the EC Item Availability table in the CWSerenade database to the web storefront • requires you to use Downloading E-Commerce Offer Tables (EOFR)	Extracting Item Availability Information to the EC Item Availability Table
stored procedure to extract availability information	• runs quickly and can be run as often as needed • can extract updated information for a specific item and SKU, or for all items • does not include the ability to trigger updates based on availability threshold, or to restrict groups of items based on business rules • requires the web site to call a database stored procedure	Extracting Item Availability Information with Stored Procedures
generic inventory download API	• generates an XML message based on triggers created through a batch process using business rules, or interactively based on activity that affects current or projected availability • provides extensive options in setting up trigger rules based on information about the items • includes rules for excluding non-allocatable or frozen item warehouses • includes more extensive data elements in addition to availability information • requires a mechanism on the web storefront to process the XML message in order to update inventory information • requires processing time to generate and transmit XML messages	Generic Inventory Download API

In this topic:

• Extracting Item Availability Information to the EC Item Availability Table

• Item Availability Periodic Function

• Item Availability Web Threshold

• What Item Availability Data is Included?

• Extracting Item Availability Information with Stored Procedures

• What Item Availability Data is Included?

Extracting Item Availability Information to the EC Item Availability Table

The process that extracts information to the EC Item Availability table (EXAVAI) for retrieval by the web storefront is described below.

• Item Availability Periodic Function

• Item Availability Web Threshold

• What Item Availability Data is Included?

For more information: See E-Commerce Item Availability Processing for a summary of the other options available for extracting item availability information for the web storefront.

Item Availability Periodic Function

When is item availability data extracted? The information is extracted both dynamically (see below) and through a periodic function. The ECAVAIL periodic function (program name ECR0144) creates a record in the EC Item Availability table for every item and SKU for which you have downloaded information to your web storefront. Only items that have been already selected through the Downloading E-Commerce Offer Tables (EOFR) menu option and added to the EC Item table (EXITEM) are eligible for selection.

Important: You need to clear the EC Item Availability table before running the periodic function to correctly update the Default delivery date field.

Item Availability Web Threshold

For dynamic updates to the EC Item Availability table, the system determines the web threshold used to evaluate an item’s availability by checking the:

1. Avail thrshld (Item-level availability threshold) assigned to the item.

2. If there is no item threshold, the Availability threshold (item class-level) for the item class assigned to the item.

3. If there is no item or item class threshold, the Quantity Available Threshold for Inventory Downloads (G36) system control value setting.

4. If all of these thresholds are blank, the system does not automatically download item availability information to the web storefront.

Example: An items’s threshold is 20; its item class threshold is 15; and the system control value is set to 25. The interactive availability download takes place based on the item-level threshold of 20.

Interactive updates: The system updates the item’s or SKU’s available quantity in the EC Item Availability table:

• if an item or SKU’s available quantity is greater than or equal to this threshold, and then it falls below the threshold.

Example: Available quantity for item AB100 is 21, and the web threshold is 20. You enter an order line for a quantity of 2, reducing the available quantity to 19.

• once an item or SKU’s available quantity is below the threshold, each time the available quantity is reduced until it is zero.

Example: After entering the order line in the above example, you enter another line on a different order for a quantity of 4, reducing the available quantity from 19 to 15.

• if an item or SKU’s available quantity is below this threshold, and then it increases to the threshold quantity or more.

Example: After entering the order line in the above example, you cancel an order for 5 units, bringing the available quantity for the item from 15 to 20.

What Item Availability Data is Included?

The information in the EC Item Availability table consists of:

• company, base item code, and short SKU number

• quantity available (if the available quantity is less than zero, the EC Item Availability table indicates an available quantity of zero)

• the next date when a purchase order receipt is expected

• default delivery date: a flag to indicate whether the date expected was based on an actual purchase order, or was defaulted based on the FTC -- # of Days for Items without Expected Ship Date (C71) system control value:

• 0 = actual purchase order date from the PO Layering table (POLAYR)

• 1 = date was defaulted based on the system control value

Examples:	Item AB100 has available quantity of 100 and an open purchase order that is due on May 15, 2013. Result: Quantity available = 100 Date expected = 051522013 Default delivery date = 0
	Item CB200 has no available quantity, and there are no open purchase orders. The current date is May 1, 2013, and the FTC - # Of Days for Items without Expected Ship Date system control value is set to 30 days. Result: Quantity available = 0 Date expected = 05312013 Default delivery date = 1

You can present this information about each item’s projected availability to your web customers if your storefront supports it. For example, if there was no expected delivery date for item AB100, and the delivery date downloaded through the periodic function was calculated based on this system control value, you might display a message on the web storefront indicating that no purchase order receipts are expected in the near future.

What if the item is sold out? If the item is assigned a soldout control code, the information downloaded varies depending on the soldout control code’s status. The soldout statuses and the resulting availability information included in the download are summarized in the table below:

Soldout control code	Availability download
1 = sell out immediately	Quantity available = 0 Date expected = blank Regardless of the actual quantity of the item you have available, or whether there are any open purchase orders, you will not be selling any more units. In this case, the setting of the Default delivery date is irrelevant.
2 = include on-order quantity in the soldout calculation	Open purchase order? • If there is an open purchase order, the information is the same as an item without a soldout control code, as described above. • If there is not an open purchase order, the Date expected is blank and does not default from the FTC - # Of Days for Items without Expected Ship Date system control value.
3 = exclude on-order quantity in the soldout calculation	Available quantity greater than zero? • If the current available quantity is greater than zero, that available quantity is indicated in the table, and: • if there is an open purchase order, the Date expected is from the purchase order, and the Default delivery date is 1 • if there is not an open purchase order, the Date expected is blank, and the Default delivery date is 0 • If the current available quantity is not greater than zero, the available quantity indicated in the table is zero, and the Date expected is blank, regardless of whether there is an open purchase order. In this case, the setting of the Default delivery date is irrelevant.

 

Set items: The availability of a set item is based on the availability of its components; in this case, the information in the availability download is consistent with the “worst case scenario” for the set based on its components, as described in the following table:

Set includes:	Availability download indicates:
six components; five components have several hundred available each, but one component has only five available	Quantity available = 5 Date expected = from the latest expected delivery date
five components: four are available, but one is backordered	Quantity available = 0 Date expected = from the backordered item (actual or default)
seven components: six are available, but one has a soldout control of 1 (sell out immediately)	Quantity available = 0 Date expected = blank

How to sell out the set, regardless of the availability of its components: To have the set item itself appear to be sold out, regardless of the availability of its components, assign the set item a soldout control status of sellout immediately.

Note: In order entry, the set item sells out if it is assigned any soldout control code; however, in the item availability download for e-commerce, only a soldout control status of sellout immediately will make the set item appear to be sold out.

Non-inventory items: Availability information is irrelevant for the following types of items, which will have the Quantity available set to 9999999:

• non-inventory items (based on the Non/inv (Non inventory) flag for the item)

• membership items (based on the Membership flag)

• gift certificates (based on the Gift cert (Gift certificate) flag)

• subscriptions (based on the Subscription flag)

• drop ship items

Calculating expected ship date for drop ship items: The Assign Drop Ship Expected Ship Date (I59) controls how to calculate the expected ship date for drop ship items. If this system control value is:

• unselected: the expected ship date is calculated using FTC -- # of Days for Items without Expected Ship Date (C71)

• selected:

• CWCollaborate drop ship items (the item’s default vendor has the Drop ship output field set to Collaborative Shipping): the expected ship date = the current date + CDC Drop Ship Lead Days (H36) + the Lead days for the vendor item

• other drop ship items: the expected ship date = the current date + FTC -- # of Days to Add for Drop Ships (C68) + the Lead days for the vendor item

Extracting Item Availability Information with Stored Procedures

Purpose: You can use stored procedures to request current inventory information from CWSerenade to update the web storefront. The request can specify a certain item and SKU, or include all items and SKU’s that have Item Warehouse records.

Set items: The availability of a standard set item is based on the availability of its components; in this case, the information in the availability download is consistent with the “worst case scenario” for the set based on its components, as described in the following table:

Set includes:	Availability download indicates:
six components; five components have several hundred available each, but one component has only five available	Quantity available = 5 Date expected = from the latest expected delivery date
five components: four are available, but one is backordered	Quantity available = 0 Date expected = from the backordered item (actual or default)
seven components: six are available, but one has a soldout control of 1 (sell out immediately)	Quantity available = 0 Date expected = blank

How to sell out the set, regardless of the availability of its components: To have the set item itself appear to be sold out, regardless of the availability of its components, assign the set item a soldout control status of sellout immediately.

For more information: See E-Commerce Item Availability Processing for a summary of the other options available for extracting item availability information for the web storefront.

What Item Availability Data is Included?

The sp_web_available stored procedure (for a specific item and SKU in a company) and the sp_web_inventory stored procedure (for all items and SKU’s in a company, provided they have Item Warehouse records) both return the same information:

Field	Description
Company Code	The code identifying the company. Numeric, 3 positions.
Product	The code identifying the item. Alphanumeric, 12 positions.
SKU	The item’s additional characteristics, such as its color and style. Included only if the item has SKU’s. Alphanumeric, 14 positions (three codes of 4 positions, each separated by a space.
Available Quantity	The total quantity available across all allocatable warehouses, calculated as: On hand - Protected - Reserved - Reserve Transfer - Backordered Different types of items: • drop ship, non-inventory, membership, gift certificate, or subscription: the available quantity is 9999; however, if there are any units of a drop ship item in a warehouse, the system uses the standard availability calculation based on the on-hand quantity. • sets: • standard sets: see Extracting Item Availability Information with Stored Procedures, above, for a discussion. • continuity items and variable sets: the available quantity for the set master item is 0. The component items indicate the actual availability. • finished goods: the system uses the standard availability calculation. • gift cards: • virtual cards: the available quantity is 0. • physical cards: the system uses the standard availability calculation.
Next PO Quantity	The total quantity expected on all purchase orders in all allocatable warehouses, less the quantity already accounted for by backorders. See Purchase Order Layering for more information. Numeric, 7 positions.
Next PO Date	The next date, after accounting for existing backorders, when you expect to receive a purchase order that would be able to fulfill a new order. See Purchase Order Layering for more information. Datetime format.
SKU Short	The short SKU number from the SKU table. Assigned by the system. Numeric, 7 positions.
Soldout Control Code	The soldout control status associated with the soldout control code, if any, assigned to the item or SKU: 1 = sell out immediately 2 = include on-order quantity in the soldout calculation 3 = exclude on-order quantity in the soldout calculation See Working with Soldout Controls (WSLD) for more information. Numeric, 1 position.