Generic Inventory Inquiry API

Generic Inventory Inquiry API

Purpose: Use the generic inventory inquiry integration to respond to requests from external systems for current inventory information.

What does the inventory inquiry request consist of? The request specifies an item/SKU within a company, and can restrict the request to a particular warehouse where you keep the item/SKU, or exclude non-allocatable or retail outlet warehouses. The external system can identify the item/SKU using the Order Management System item and SKU codes; or, it can use the short SKU number, retail reference number, or UPC information.

What information does the inventory inquiry response include? The response includes information about the item/SKU, the warehouse(s) where it is stored, and current inventory levels such as the quantity on-hand, available, backordered, and so on. See the Inventory Inquiry Response XML Message (CWInventoryInquiryResponse) for a complete list of information.

Note: You can also integrate Order Management System with Order Broker to support visibility into inventory availability across the enterprise. See Order Broker Integration Overview for more information.

In this chapter:

• Typical Flow

• When is the Response Message Empty?

• Resolving the Item and SKU

• Setup Requirements

• Inventory Inquiry Request XML Message (CWInventoryInquiry)

- Inventory Inquiry Request: Sample XML

• Inventory Inquiry Response XML Message (CWInventoryInquiryResponse)

- Inventory Inquiry Response: Sample XML

Typical Flow

A typical inventory inquiry flow might be:

1.	A customer at the web storefront inquires into the availability of an item.
2.	The web storefront generates an inventory inquiry request to Order Management System.
3.	The INV_INQURY process in Order Management System receives the request, generates the Inventory Inquiry Response XML Message (CWInventoryInquiryResponse), and sends it to the web storefront, where the availability information is presented to the customer.

When is the Response Message Empty?

The INV_INQURY process sends an “empty” response when the request does not include sufficient valid data. The empty response indicates to the external system that the request was not lost, but it was incomplete or invalid. For example, the process sends an empty response when the request includes:

• an invalid company code, or no company indicated

• the item cannot be resolved (see below), including:

- a SKUed item with no SKU information specified

- a SKU specified for a non-SKUed item

• a retail reference number that is assigned to more than one item/SKU

</Message>

Resolving the Item and SKU

The inventory inquiry request can indicate the correct item and SKU through any one of the following:

1. item_number and sku_code: The sku_code is required if the item has SKUs, but should not be included for a non-SKU’d item. Validated against the Item and SKU tables.

2. short_sku: Validated against the Short SKU field in the SKU table.

3. retail_reference_nbr: Validated against the Reference # in the SKU table.

4. upc_type and upc_code: Both values must be specified. Validated against the Item UPC table. Note: Include any leading zeros. For example, if the UPC code is 06012011, pass 06012011 and not 6012011.

In searching for an item and SKU, the system checks the values in the message in the order indicated above. If any value (or set of values, such as item_number and sku_code) is blank, it checks the next value, such as short_sku.

Once the system finds one of the values listed above in the message, it checks the related tables to find a match. For example, if the request does not specify an item_number, but does specify the short_sku, the system checks the Short SKU field in the SKU table. If there is a match, the system generates the inventory inquiry response. If a short SKU is specified but there is no match, the system sends an empty message. Alternatively, if the message includes an incorrect item code and a correct short SKU, the system sends an empty response message based on the item code; it never checks the short SKU.

For more information:

• item and SKU codes: Create Item ScreenCreate Item screen and Create SKU 1 of 2 (With Overrides) ScreenCreate SKU 1 of 2 (With Overrides) screen

• short SKU number: Short SKU

• retail reference number: Reference #

• upc type and code: Work with UPC Codes Screen

Setup Requirements

To use the inventory inquiry API, you need to configure the INV_INQURY process in Working with Integration Layer Processes (IJCT), and have the external system send a correctly formatted Inventory Inquiry Request XML Message (CWInventoryInquiry) to a queue specified for the job. Similarly, the external system must be prepared to receive the response described under Inventory Inquiry Response XML Message (CWInventoryInquiryResponse).

The INV_INQURY process will process inventory inquiry requests from any source as long as the XML files are correctly formatted and contain the required information.

CWMessageIn Web Service

You can use the CWMessageIn Web Service to route Inventory Inquiry messages. In this situation, the target for each inbound message must match the Inbound program name for the integration layer process queue, as specified at the Integration Layer Process Queue Screen.

Web service authentication? Use the Working with Web Service Authentication (WWSA) menu option to define a valid user for basic web service authentication. If you use Oracle Identity Cloud Service for password authentication, in addition to creating the web service authentication user in Work with Web Service Authentication (WWSA), you must also create a corresponding user profile in Oracle Identity Cloud Service and assign the user to the corresponding web service role defined for the Order Management application.

If you are using the CWMessageIn RESTful web service: You POST inbound CWMessageIn messages to the web service’s URI, or endpoint, of the RESTful service. The individual URL for the CWMessageIn RESTful service uses the following format: http://server:port/SerenadeSeam/sxrs/application/CWMessageIn, where server:port identifies the application server where the RESTful service is located and CWMessageIn is the name of the web service to call.

If you are using the CWMessageIn SOAP-based web service: The endpoint specified in the CWMessageIn.wsdl file is where you need to post messages in order for the CWMessageIn web service to process them. The endpoint is typically set to http://server:port/CWDirectCPService/services/CWMessageIn, where server:port identifies the application server where the wsdl is located and CWMessageIn is the name of the web service to call. The CWMessageIn SOAP-based web service requires that inbound messages be embedded in SOAP envelope tags.

Inventory Inquiry Request XML Message (CWInventoryInquiry)

Important: Please note the following rules for numeric fields:

- Do not pass an alphanumeric value in a numeric field; doing so will cause the INV_INQURY process to go into a message wait status.

- Do not exceed the specified length of any numeric field; doing so will cause the value to be interpreted incorrectly.

General rules for alphanumeric fields:

- case-sensitive; an a is not equivalent to A.

- will be truncated if they exceed the maximum length specified below; a transaction_code of Adjustment will be truncated to A and processed correctly

For more information: See:

• Typical Flow for a discussion of when you might use this message.

• Inventory Inquiry Request: Sample XML for a sample message.

• Generic Inventory Inquiry API for an overview.

Attribute Name	Type	Length	Comments
Message
source	alpha	25	Identifies the source of the XML message.
target	alpha	25	Identifies the target of the XML message. RDC indicates the XML message is sent to Order Management System.
type	alpha	25	Identifies the type of information in the XML message. Should be set to CWInventoryInquiry.
InventoryInquiry
company	numeric	3	Indicates the company associated with the item/SKU. Validated against the Company table; see Working with Companies (WCMP). See When is the Response Message Empty? for information on when the INV_INQURY process sends an “empty” response based on the company number indicated.
Note: See Resolving the Item and SKU for an overview of how the INV_INQURY process uses the item_number, sku_code, short_sku, retail_reference_nbr, upc_type and upc_code attributes.
item_number	alpha	12
sku_code	alpha	14
short_sku	numeric	7
retail_reference_nbr	numeric	15
upc_type	alpha	3
upc_code	alpha	14
warehouse	numeric	3	If a warehouse is specified, the Inventory Inquiry Response XML Message (CWInventoryInquiryResponse) includes information for just that warehouse; otherwise, all warehouses where you keep the item/SKU are included (depending, also, on the exclude_non_allocatable and exclude_retail_outlet attributes). Validated against the Warehouse table; see Creating and Maintaining Warehouses (WWHS). If the warehouse specified is invalid, the response does not include any warehouse information; however, it still includes the information on the item/SKU. Similarly, if there is no record of the item in the warehouse specified, the response does not include any warehouse information, even if the warehouse specified is valid.
exclude_non_ allocatable	alpha	1	Indicates whether the response should exclude any warehouse whose Allocatable flag is selected. This flag indicates whether merchandise in the warehouse can be reserved to fulfill consumer orders. A request might indicate to exclude non-allocatable warehouses because the inventory would not be available to a customer. If this attribute is set to Y, non-allocatable warehouses are excluded from the response; otherwise, the INV_INQURY process does not consider the setting of the Allocatable flag for a warehouse.

Inventory Inquiry Request: Sample XML

A sample of the Inventory Inquiry Request XML Message (CWInventoryInquiry) is presented below.

</Message>

Inventory Inquiry Response XML Message (CWInventoryInquiryResponse)

The INV_INQURY process generates an XML response laid out as follows.

Note:

• Attributes are included in the response only if they contain a value. Blank attributes are not passed.

• Numeric fields use implied decimals; for example, a value of $12.34 is presented as 1234.

See Inventory Inquiry Response: Sample XML for a sample message and see Generic Inventory Inquiry API for an overview.

Attribute Name	Type	Length	Comments
Message
source	alpha	25	Identifies the source of the XML message. RDC indicates the XML message originates from Order Management System.
target	alpha	25	Identifies the target of the XML message.
type	alpha	25	Identifies the type of information in the XML message. This will be CWInventoryInquiryResponse.
date	numeric	8	The date the response is generated, in MMDDYYYY format.
time	numeric	6	The time the response is generated, in HH:MM:SS format.
Item
company	numeric	3	Indicates the company associated with the item/SKU; see Working with Companies (WCMP).
company_ description	alpha	30	The description from the company, as set up through Working with Companies (WCMP).
item_number	alpha	12	The item code, as set up through Performing Initial Item Entry (MITM).
item_description	alpha	40	The description of the item.
item_2nd_lang_desc	alpha	40	The information in the Second language description, if any.
item_long_sku_style	alpha	20	The item’s L/S style (Long SKU style), if any.
non_inventory	alpha	1	Indicates whether inventory levels are maintained for the item. Valid values are: Y = This is a non-inventory item. N (default) = This is a regular item that will be tracked. Because this flag defaults to N (unselected) in Performing Initial Item Entry (MITM), it is typically populated and included in the response message.
membership	alpha	1	Indicates that the item represents a membership program, which you can use to generate periodic orders to a customer. Valid values are: Y = This is a membership item N (default) = This is not a membership item Even when an item is flagged as a membership item, it is still possible to have an item warehouse record if, for example, you specified a default warehouse when creating the item. Because this flag defaults to N (unselected) in Performing Initial Item Entry (MITM), it is typically populated and included in the response message.
drop_ship_item	alpha	1	Indicates whether you ship the item from your warehouse, or have your vendor ship it to the customer directly. Valid values are: Y = When a customer orders the item, you order the item from your vendor and the vendor ships it directly to your customer. However, it is still possible to ship the item from the warehouse if it is available there. N (default) = You ship the item from your warehouse. If there is a record of an item in a warehouse, this information is still provided in the response message regardless of the setting of this flag. Because this flag defaults to N (unselected) in Performing Initial Item Entry (MITM), it is typically populated and included in the response message.
item_status	alpha	1	The item’s Status, if any. An optional code that represents an item's status, such as obsolete, discontinued, etc. This information is used for inventory reporting purposes. See Working with Item Status (WIST).
item_status_ description	alpha	30	The description of the item status, if any; see Working with Item Status (WIST).
kit_type	alpha	1	The Kit type setting, if any, for the item. Valid values are: S = Set F = Finished goods V = Variable set
long_sku_ department	numeric	4	The L/S dept (Long SKU department), if any, assigned to the item.
long_sku_ department_desc	alpha	30	The description of the long SKU department, if any, as specified through Working with Long SKU Departments (WLSD).
long_sku_division	alpha	4	The L/S division (Long SKU division), if any, assigned to the item’s long SKU department.
long_sku_division_desc	alpha	30	The description of the long SKU division, as set up through Creating and Maintaining Long SKU Divisions (WLDV).
long_sku_class	numeric	4	The L/S class (Long SKU class), if any, assigned to the item.
long_sku_class_desc	alpha	30	The description of the long SKU class, as set up through Working with Long SKU Classes (WLSC).
svc_type	alpha	1	Indicates the type of stored value card that the item is, if it is a stored value card. Valid values are: P = Physical card E = Physical card/early notification V = Virtual card See Stored Value Card Overview and Setup for more information.
SKU			This element includes attributes that apply to both SKU’ed and non-SKU’ed items, so it is always included in the response.
sku_code	alpha	14	The item’s unique characteristics; included if this is a SKU’ed item.
sku_description	alpha	40	The SKU’s description, if any.
sku_2nd_lang_desc	alpha	40	The SKU’s Second language description, if any.
sku_long_sku_style	alpha	20	The SKU’s L/S style (Long SKU style), if any. Can be different from the item_long_sku_style, if any.
short_sku	numeric	7	The item or SKU’s Short SKU code. This is a system-assigned code to uniquely identify each item/SKU.
retail_reference_nbr	numeric	15	The SKU’s Reference # (Reference number), if any. Typically, this is an alternate product ID.
subscription	alpha	1	The setting, if any, of the Subscription flag. Valid values are: Y = Item is a subscription. N = Item is not a subscription. Even when an item is flagged as a subscription, it is still possible to have an item warehouse record if, for example, you specified a default warehouse when creating the item. Because this flag is optional when you create an item/SKU, it might not contain a Y or N value; if not, it is not included in the response.
sku_status	alpha	1	The SKU’s status code, if any. Can be different from the item’s status. See Working with Item Status (WIST).
sku_status_ description	alpha	30	The description of the SKU’s status, if any; see Working with Item Status (WIST).
so_control	alpha	2	The SKU’s soldout control setting, if any. Validated against the Soldout Control table; see Working with Soldout Controls (WSLD).
so_control_ description	alpha	30	The description of the soldout control code, if any, as defined through Working with Soldout Controls (WSLD).
so_control_status	alpha	1	The Sts (Status) of the soldout control, if any. Possible statuses are: • 1 = Sellout the item/SKU immediately. • 2 = Sellout the item/SKU when the quantity available is zero, not including open purchase orders. • 3 = Sellout the item/SKU when the quantity available is equal to zero, including open purchase orders. (An item must be coded to sellout immediately in order to be picked up by Processing Auto Soldout Cancellations (MASO).)
UPC			The UPC element is included only if the item/SKU has one or more UPC codes. See the Work with UPC Codes Screen for more information.
upc	alpha	14	A UPC code defined for the item/SKU.
upc_type	alpha	3	The type of UPC code. The UPC type determines the type of validation the system performs against the UPC code. Valid values are: E13: EAN-13 E8: EAN-8 UA: UPC-A UE: UPC-E
upc_vendor	numeric	7	A code that identifies a supplier who sells you merchandise. Vendor codes are defined in and validated against the Vendor table; see Working with Vendors (WVEN).
Warehouse			The warehouse name and address information, if any, are defined in the Warehouse table; see Creating and Maintaining Warehouses (WWHS) for descriptions of this information. The system does not require you to specify address information when you create a warehouse.
warehouse	numeric	3	A code identifying the item/SKU’s primary warehouse, and each additional warehouse where you keep the item/SKU. Unless the request message specified a warehouse, the response includes each warehouse where you keep the item/SKU (depending, also, on the exclude_non_allocatable setting in the request).
warehouse_name	alpha	30
address_line_1	alpha	32
address_line_2	alpha	32
address_line_3	alpha	32
city	alpha	25
state	alpha	2
postal_code	alpha	10
country	alpha	3
drop_point	numeric	3	The drop point assigned to the warehouse, used to identify the warehouse’s geographical location and for zone skipping; see Working with Drop Points (WDPT) for an overview.
drop_point_ description	alpha	30	The description for the drop point, as set up through Working with Drop Points (WDPT).
manager	alpha	30	The Manager, if any, specified for the warehouse.
telephone_nbr	alpha	14	The Telephone #, if any, specified for the warehouse.
fax_nbr	alpha	14	The Fax #, if any, specified for the warehouse.
allocatable_flag	alpha	1	This flag indicates whether merchandise in the warehouse can be reserved to fulfill consumer orders. Valid values are: Y = Merchandise in this warehouse can be reserved to fulfill orders. N = Merchandise in the warehouse will not be used to fulfill orders. Examples of non-allocatable warehouses include non-saleable and defective item warehouses. If a warehouse is not flagged as allocatable, its on-hand quantity is not included in the availability calculation for items.
receive_restock_transfers	alpha	1	Not currently implemented.
value_inv_at_retail	alpha	1	Not currently implemented.
viewable_in_oe	alpha	1	This flag indicates whether item availability for the warehouse can be viewed in Order Entry. When you view item availability in Order Entry, a list of all viewable warehouses and the item quantity available in each warehouse displays. Valid values are: Y = Item quantities in this warehouse can be viewed in Order Entry. N = Item quantities in this warehouse cannot be viewed in Order Entry.
auto_restock_location	alpha	7	Not currently implemented.
retail_outlet	alpha	1	Indicates whether the warehouse represents a retail store rather than a true warehouse. Valid values are: Y = The warehouse represents a retail store location. N = The warehouse does not represent a retail store location.
retail_type	alpha	1	A setting of S indicates that the warehouse represents a retail outlet.
ItemWarehouse			You can review item warehouse information through Creating and Maintaining Item Warehouses and through Using Inventory Inquiry (DINI).
allocation_freeze	alpha	1	The setting of the Reservation freeze flag for the item in this warehouse. Valid values are: Y = The item warehouse is frozen for the purposes of pick slip generation and reservation. N = The item warehouse is not frozen. When you create an item location through Performing Initial Item Entry (MITM), this is the default setting.
economic_order_qty	numeric	7	From the Economic order qty, if any, for the item warehouse. Used for reporting purposes. Represents the recommended optimum ordering quantity for this item/SKU, based on a set of criteria such as cost.
max_qty	numeric	7	From the Max qty, if any, for the item warehouse. Represents the maximum amount of inventory on hand for this item/SKU.
min_qty	numeric	7	From the Min qty, if any, for the item warehouse. Represents the least amount of inventory to keep on-hand for this item/ SKU.
on_hand_qty	numeric	7	From the On-hand quantity, if any, for the item warehouse. Represents the number of units for this item across all locations in the warehouse. Calculated by the system.
backorder_qty	numeric	7	From the Backorder quantity, if any, for the item warehouse. Represents the quantity of the item/SKU in this warehouse that is currently on open orders but not available in your warehouse. Calculated by the system.
protected_qty	numeric	7	From the Protected qty, if any, for the item warehouse. Represents the amount of inventory that cannot be reserved (using immediate or batch reservation) for this item/ SKU across all locations in this warehouse. The system uses this inventory only in Interactive Reservation.
reorder_qty	numeric	7	From the Reorder qty, if any, for the item warehouse. Represents the quantity of this item/SKU that should be ordered when the item/SKU reaches the minimum stock level.
reserve_qty	numeric	7	From the Reserved quantity, if any, for the item warehouse. Represents the quantity of this item/SKU in this warehouse that is reserved for existing orders. This total increases through immediate, batch, and interactive reservation, and decreases through confirmation or pick void/reprint. Calculated by the system.
sh_reserve_qty	numeric	7	From the S/H reserved quantity, if any, for the item warehouse. Represents the quantity of this item/SKU in this warehouse that is reserved and flagged for some type of special handling, such as monogramming, hemming, etc. This total increases through reservation of items/SKUs assigned a special handling code in order entry or maintenance, and decreases through confirmation or pick void/reprint. Calculated by the system.
on_order_qty	numeric	7	From the On order quantity, if any, for the item warehouse. Represents the total quantity of this item in this warehouse that is due to be received through purchase orders. Calculated by the system.
reserve_transfer_qty	numeric	7	From the Reserve transfer quantity, if any, for the item warehouse. Represents the quantity of the item that is earmarked for an inventory transfer. This quantity is unavailable to the reservation program for new orders.
available_qty	numeric	7	From the Available quantity, if any, for the item warehouse. Calculated by the system as follows: On hand - Protected - Reserved - Reserve Transfer - Backordered = Quantity available Drop ship items: If the item is flagged for drop ship, the available quantity indicated is 9999, regardless of whether there is any quantity of the item actually on-hand in the warehouse.
next_po_date	numeric	8	Indicates the date when the next purchase order you can use to fulfill this order line is expected, based on purchase order layering. MMDDYYYY format. See Purchase Order Layering and Backorder Notifications for more information on purchase order layering. This date is included for drop ship items only if the Assign Drop Ship Expected Ship Date (I59) system control value is selected. See that system control value for more information.
next_expected_qty	numeric	7	The number of units of this item ordered on a purchase order that will be available for new orders.
original_retail_price	numeric	7.2	From the Original retail price, if any, for the item warehouse. Represents the original price of this item in the retail store. You can use this field to track the original retail price for the item if the warehouse represents a retail store. The Use Store File (G15)U system control value controls access to this field.
current_retail_price	numeric	7.2	From the Current retail price, if any, for the item warehouse. Represents the current price of this item in the retail store. You can use this field to track the current retail price for the item if the warehouse represents a retail store. The Use Store File (G15) system control value controls access to this field.
protect_current_ price	numeric	1	The setting of the Protect price (Protect current price) flag for the item warehouse. Valid values are: Y = The system does not update the Current retail price when you define a new retail price in Work with Warehouse Values, and instead, keeps the existing price. N = The system updates the Current retail price with the retail price from Work with Warehouse Values. The Use Store File (G15) system control value controls access to this field.
protect_min_max	numeric	1	From the Protect min/max (Protect minimum and maximum quantities) flag for the item warehouse. Valid values are: Y = The system does not update the minimum or maximum quantity for the item in this warehouse when you define a new minimum or maximum quantity in Work with Item Warehouse Values, and instead, keeps the existing quantity. N = The system updates the minimum or maximum quantity for the item in this warehouse with the minimum or maximum quantity you defined in Work with Item Warehouse Values. The Use Store File (G15) system control value controls access to this field.