Reducing Inventory

  • Article

The FlagInventory and LocalInventory components are strictly informational components. This means that while they provide notification that a given item is out of stock, they assume that _product_local_inventory for the line item has been maintained by some other process. The SQLItemADO pipeline component can be used to run an SQL query that updates the product inventory.

Ee783750.note(en-US,CS.20).gif Note

  • You must unpack a site before starting the following procedure.

To update your inventory using the SQLItemADO pipeline component

  1. Click Start, point to Programs, point to Microsoft Commerce Server 2002, and then click Pipeline Editor.

  2. In the Pipeline Editor, browse to the location of the file total.pcf (for example, <drive:>\Inetpub\wwwroot\<site name>\pipeline), and then double-click the file.

    The total.pcf pipeline appears.

  3. On the pipeline, select the Inventory stage, and then on the Edit menu, point to Insert Component, and click After.

  4. In the Choose a component dialog box, do the following:

    Use this To do this
    Stages Select All.
    Components Select SQLItemADO.

  5. Click OK. The SQLItemADO component appears as a valve on the pipeline.

  6. Double-click the SQLItemADO component.

  7. In the Component Properties dialog box, on the SQL Item (ADO) tab, do the following:

    Use this To do this
    Connection String Type an OLE DB connection string to the table storing the inventory data.
    Query Type the SQL query that will update the inventory in the database. The syntax required to replace values in the SQL query is:

    :<position>

    This will replace the items in the SQL query with items listed in the Parameter List box. For example, if you had to replace two values in an SQL query, the format would be:

    SELECT * FROM:1 WHERE Param2='':2''
    Parameter List Type the list of values you want to use in the SQL query that will be evaluated at run time. The Parameter List box uses a special format for replacement. To retrieve data from the order form, use the notation item.<orderformvalue>. For example, to retrieve the quantity of the items ordered from the order form, you would use item.quantity.

    Click OK.

  8. To save your changes, on the File menu, click Save.

Special Considerations for Inventory

You might have a situation in which two customers want an item of which there is only one in stock. As the OrderForm object for each customer passes through the Plan pipeline, the inventory components indicate to both customers that the product is in stock. This is correct behavior, because the number that indicates how many items of a given SKU are in stock has not yet been reduced to zero (0) by an Accept stage component.

However, when the first customer runs through the Purchase pipeline, the SQLItemADO component will reduce the in-stock value to zero (0). When the second customer runs through the Purchase pipeline, the SQLItemADO component will reduce this value to a negative integer. This scenario creates a backorder, even if the Plan pipeline for the site is configured to not allow back ordering.

There are two ways to address this problem:

  • One solution is to include at the beginning of the Accept stage a SQLItemADO component that checks to ensure that the number of items in stock for a given SKU is greater than zero (0). If no items for a given SKU are in stock, the SQLItemADO component can place the items on backorder (by initializing the _inventory_backorder name/value pair) or can raise the _pur_out_of_stock error.
  • A second solution is to include an instance of the appropriate inventory component, FlagInventory for example, in both the Inventory stage (Plan pipeline) and the Accept stage (Purchase pipeline). Following this component, you would include the SQLItemADO component. The inventory component would run the query to decrement the in-stock value for the item.

Copyright © 2005 Microsoft Corporation.
All rights reserved.