2 RIB Publication Designs

This chapter provides an overview of the RIB publication APIs used in by Merchandising.

Allocations Publication API

This section describes the allocations publications API.

Functional Area

Allocations

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Merchandising is responsible for communicating allocation information with external systems such as a store inventory system (SIOCS, for example) and a warehouse management system, like Oracle WMS Cloud.

There are several ways in which allocation information can be created in Merchandising:

• Through integration with the Allocation Cloud Service

• Through Merchandising replenishment, where cross dock orders generate allocations

• Through the Allocation Subscription API, where a third-party system can create allocations and send to Merchandising for execution.

Allocations can be created from a virtual warehouse to any type of stockholding location in Merchandising, including other virtual warehouses, and to both company and franchise stores. Allocations include a store type and stockholding indicator at the detail level when allocating to stores, to allow the store and warehouse inventory management system to filter out the data irrelevant to their respective systems. When allocating to a franchise store, the linked franchise orders are not published; only the allocation itself is published. When allocating to another warehouse, the allocation quantities are summed up to the physical warehouse level and the physical warehouse is what is communicated in the integration.

An allocation and its details are not published from Merchandising until it is approved. Modified and deleted allocation information is also sent. Allocation header modification messages will be sent if the status of the allocation is changed to approved (A) or closed (C) or if the allocation release date is changed. Allocation detail modification messages will be sent if the allocated quantity is changed. A header delete message signifies that the completed allocation has be deleted.

Note:

Allocations, when published to external systems, are combined in the RIB with transfers (published in the Transfer Publication API) into a combined Stock Order Publication message.

New Allocations

When an allocation is created, an Allocation Create message request is queued. The Allocation Create message is a flat message containing a full snapshot of the allocation at the time the message is published. The message will not be sent until the allocation has been approved.

The allocation create message contains the following:

Allocation Header

Allocation Header

Message Element	Included?	Notes
Allocation Number	Always	Contains the number that uniquely identifies the allocation within the system.
Document Type	Always	This is the type of stock order. This value will be 'A' to signify an allocation. This will differentiate allocations and transfers in the Stock Order Publication API subscribed to by stores and warehouses.
Physical WH	Always	The physical warehouse location from which the allocation will originate.
Warehouse	Always	Contains the number of the virtual warehouse where the allocation will originate.
Item	Always	Specifies the item on the allocation.
Pick Not Before Date	Optional	Contains the earliest date on which the allocation should start being picked for the allocation. This will contain the release date on the allocation or the not before date on the associated purchase order.
Pick Not After Date	Optional	This value is calculated by adding x days to the release date on the allocation. The number of days to add is defined in the description column on code_detail for code DATE and code type DEFT.
Order Type	Optional	This field contains the type of order. Allocations created against Purchase Orders will be marked as PREDIST order types. Allocations created against Warehouse stock will be populated with the DEFAULT_ORDER_TYPE from the SYSTEM_OPTIONS table which can be AUTOMATIC, MANUAL or WAVE.;
Order No	Optional	Contains the order number to which the allocation applies. This is only populated for order-based allocations.
Order Document Type	Always	Always ‘P’.
Event	Optional	This field contains the event to which the promotion belongs to. This is an optional field that provides a method to group promotions together for tracking and reporting purposes.
Event Description	Optional	This field contains the description of the promotional event.
Priority	Always	A value which indicates the priority of an allocation. This value will always be 1.
Ticket Type ID	Optional	This field contains a character string which uniquely identifies the ticket type which is associated with the item. This is only populated if the ticket associated with the item on the allocation is configured to be printed upon the receipt of the purchase order for order-based allocations.
Context Type	Optional	This field holds the code for the reason for the allocation. For example, it could indicate the allocation was created for an upcoming promotion. Valid context codes are defined in Codes and Descriptions under the code type CNTX.
Context Value	Optional	Contains a character string relating to the context type.
Allocation Status	Always	Contains the code for the allocation status.
Allocation Details	Always	Child node

Allocation Detail

Message Element	Required?	Notes
Physical To Location	Always	This field contains the physical location to which the allocation is being sent. If the location type is a store, this will be the same as the To Location.
To Location	Always	This field contains the location to which the allocation is being sent. The location type field determines if the location is a store or a virtual warehouse.
Location Type	Always	This field contains the type of location in the To Location field. Valid values are: S - Store W - Warehouse
Store Type	Optional	If the To Location is a store, this field contains the type of store. Valid values are: C - company store F - franchise store
Stockholding	Optional	If the To Location is a store, this field indicates if the store is a stockholding location or not. Valid values are: yes (Y) or no (N).
Quantity Allocated	Always	Contains the total quantity of the item allocated to the to location.
Price	Optional	This field holds the unit retail in the selling unit of measure for the item/to location combination. This field is stored in the local currency of the to location.
Selling UOM	Optional	This field holds the selling unit of measure for the item's single-unit retail at the to location.
Priority	Always	A value which indicates the priority of an allocation detail. This value will always be 1.
Store Order Multiple	Always	This column contains the multiple in which the item needs to be shipped from a warehouse to the store.
In-Store Date	Optional	Indicates the date that the item needs to be at the store.
Rush Flag	Optional	Indicates if there is a rush on shipping this item to the destination location.
Allocation Detail Component Items	Optional	Child node

Allocation Detail Component Items

This node is only included if the item being allocated is a pack item.

Message Element	Required?	Notes
Component Item	Always	This field contains the alphanumeric identifier of an item within the pack.
Component Price	Always	This field holds the unit retail in local currency and in the component selling unit of measure for the component item at the destination location.
Component Selling UOM	Always	This field holds the selling unit of measure for the item's single-unit retail at the to location.

New Allocation Details

Creating new allocations details for an existing header triggers a message to be sent to notify external systems of the changes. The message for new allocation details contains the same information as the new allocation create message.

Updated Allocations

Allocation updates trigger a message to be sent to notify external systems based on updates made at the allocation header and/or detail level. The message for updated allocations contain the same information as the new allocation create message.

Header Only Updates

Allocations are published when updates at the header level are made to the release date or for certain allocation status updates. Only changed statuses where the final status is Approved (A) or Closed (C) and the old status is not Externally Closed (X) will trigger a header update to be published. When updates are made at the allocation header level, only header information is published.

Detail Only Updates

When updates are made at the allocation detail level without changes to the header information, the full header and detail information are published. A detail update will be published when the quantity allocated has changed.

Full Message Updates

In cases where the system receiving allocations cannot support just receiving the changes, another option is provided that can resend the full allocation details whenever there is a change. This will be published, along with the delta messages, in cases where the system option Publish Full Objects (PUB_FULL_OBJECTS_IND) is set to Y such as when Oracle WMS Cloud Integration is used.

Deleted Allocations

Allocations can be deleted when they are in Approved or Closed status. When an allocation delete message is triggered, a message is sent to external systems to notify them of the changes via an allocation header delete message.

Detail Only Deletes

When allocation details are deleted, a message is sent to external systems to notify them of the change. Both header and detail information are included in the published message.

The allocation delete message contains the following:

Table 2-1 Allocation Header

Message Element	Included?	Notes
Allocation Number	Always	Contains the numeric identifier that uniquely identifies the allocation within the system.
Document Type	Always	This is the type of stock order. This value will be 'A' to signify an allocation. This will differentiate allocations and transfers in the Stock Order Publication API subscribed to by stores and warehouses.
Physical Warehouse	Always	Contains the numeric identifier of the physical warehouse location where the allocation will originate.
Warehouse	Always	Contains the numeric identifier of the virtual warehouse location where the allocation will originate.
Item	Always	Unique alphanumeric value that identifies the item.
Allocation Detail	Optional	Child node

Table 2-2 Allocation Detail

Message Element	Required?	Notes
To Location	Always	This field contains the location to which the allocation is being sent. The location type field determines if the location is a store or a virtual warehouse.
Location Type	Optional	This field contains the type of location in the To Location field. Valid values are S - store and W - warehouse.
Store Type	Optional	If the To Location is a store, this field contains the type of store. Valid values are: C - company store, F - franchise store.
Stockholding Indicator	Optional	If the To Location is a store, this field indicates if the location is a stockholding store or not. Valid values are: yes (Y) or no (N).

Error Handling

When the publication encounters a non-fatal error, messages continue to be processed. For the particular message where the error was encountered, a status of Hospital (H) is sent to the RIB and the status of the message in the queue is set to H. These types of errors occur when no changes in the database have been made and a process to try to re-publish these messages is available. In case the error is a fatal error, for example, when changes to data have already been made, a status of Error (E) is sent to the RIB and the message status in the queue will be in Error status. The error message as well as the object containing the allocation number and details is returned to Merchandising.

Message XSD

Here are the filenames that correspond with each message type. Please consult the mapping documents for each message type in order to get a detailed picture of the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
AllocCre	Allocation Create Message	AllocDesc.xsd
AllocDtlCre	Allocation Detail Create Message	AllocDesc.xsd
AllocHdrMod	Allocation Header Modify Message	AllocDesc.xsd
AllocDtlMod	Allocation Detail Modify Message	AllocDesc.xsd
AllocFulRep	Allocation Full Replacement Message	AllocDesc.xsd
AllocDel	Allocation Delete Message	AllocRef.xsd
AllocDtlDel	Allocation Detail Delete Message	AllocRef.xsd

ASN Outbound Publication API

This section describes the ASNOUT Publication API.

Functional Area

Inventory

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

The Advanced Shipping Notification (ASN) outbound message is used to communicate the shipment of merchandise against transfers or allocations. This message is published by Merchandising to stores or warehouses.

Merchandising supports the following shipping functionality:

• On-line Shipping/Receiving.

• Franchise Order Shipment and Return.

On-line Shipping/Receiving

Two system options (ship_rcv_store and ship_rcv_wh) are used to control whether Merchandising on-line shipment/receiving functionality is enabled.

• Ship_rcv_store = ‘Y’ means a store inventory management application, such as Oracle Retail Store Inventory Operations Cloud Service (SIOCS), is NOT installed and shipping/receiving for stores will be done in RMS.

• Ship_rcv_wh = ‘Y’ means a warehouse management system, such as Oracle Cloud WMS, is NOT installed and shipping/receiving for warehouses will be done in Merchandising.

If either (but not both) of these indicators is set to ‘Y’, shipments created in Merchandising should be published to the RIB to allow the integration subsystem application to have visibility to the corporately created shipment.

The possible scenarios for on-line shipping/receiving:

Store Inventory System Installed (Yes/No)	Warehouse Management Installed (Yes/No)	System Options Settings	RMS Publishes Shipments (Yes/No)	Apps to subscribe to the message
Yes	Yes	Ship_rcv_store = N Ship_rcv_wh = N	No	No
No	No	Ship_rcv_store = Y Ship_rcv_wh = Y	No	No
Yes	No	Ship_rcv_store = N Ship_rcv_wh = Y	Yes – for warehouse-to-store shipments	Store Inventory System
No	Yes	Ship_rcv_store = Y Ship_rcv_wh = N	Yes – for store-to-warehouse shipments	Warehouse Management System

Merchandising on-line shipping can involve a customer order transfer . For a customer order transfer, customer order number and fulfillment order number are included in the published information.

Franchise Order Shipment and Return

Franchise stores are a special kind of stores that are not ‘owned’ by the company; therefore, any shipment to a franchise store is considered a sale. From Merchandising, franchise stores can order goods from company stores or warehouses; they can also return goods back to company stores or warehouses. These orders and returns are created as transfers in Merchandising.

Merchandising supports two kinds of franchise stores – stockholding franchise stores (in which Merchandising manages inventory and financials like regular stores) and non-stockholding franchise stores (in which Merchandising does NOT manage inventory and financials).

The Store Inventory Operations Cloud Service (SIOCS) manages transactions for stockholding franchise stores, but not for non-stockholding franchise stores. The Shipping and Receiving of non-stockholding franchise orders and returns are handled within Merchandising from the Store perspective even if SIOCS is installed.

For warehouses, if a franchise return from a non-stockholding franchise store is to be processed, the warehouse management system (WMS) will require an ASN against which to receive. Because Merchandising automatically creates the shipment for non-stockholding stores upon the approval of a franchise return, Merchandising needs to publish those shipments for the WMS. Similar to on-line Shipping/Receiving, Merchandising publishes shipments of non-stockholding Franchise Returns to warehouses as ASNOut messages.

Advance Shipment Notification

Shipments created in Merchandising cannot be modified. Upon saving a shipment, the entire shipment is published from Merchandising as one ASNOut message. As a result, Merchandising only needs to support the ASNOut create message type for shipment publishing.

Table 2-3 Advance Shipment Notification Header

Message Element	Included?	Notes
Schedule Number	No	Contains the Schedule identifier number. Not used by RMS.
Auto Receive	Always	Contains the flag to enable auto-receiving in the store inventory system (e.g. SIOCS). Defaulted to "No".
To Location	Always	Contains the location that the shipment will be delivered to.
To Location Type	Always	This field contains the location type of the to_location field. Valid values are 'S' = store, 'E' = finisher, and 'W' = warehouse.
To Store Type	Always	Indicates the store type of the to_location. This will only be populated if to_loc_type is 'S'. Valid values are: 'C' = company store, 'F' = franchise store.
To Stockholding Indicator	Optional	Indicates if the to_location is stockholding or not. Only populated if to_loc_type is 'S'. Valid values are: 'Y' = yes, 'N' = no.
From Location	Always	For transfer and allocation shipments, this field will hold the location from which the shipment was sourced.
From Location Type	Always	This field contains the location type of the from_location field. Valid values are 'S' = store and 'W' = warehouse.
From Store Type	Optional	Indicates the store type of the from_location. This will only be populated if from_location is 'S'. Valid values are: 'C' = company store, 'F' = franchise store. Publications from RWMS will always be NULL for this field.
From Stockholding Indicator	Optional	Indicates if the from_location is stockholding or not. Only populated if from_location is 'S'. Valid values are: 'Y' = yes, 'N' = no. Publications from RWMS will always be NULL for this field.
ASN Number	Always	Holds the bill of lading number associated with a shipment.
ASN Type	Always	Not used by RMS. Set to C for container when ship to location is a store, T for tare when ship to location is a warehouse.
Container Quantity	Always	Contains the number of boxes associated with the shipment.
BOL Number	Always	Holds the transaction sequence number on the message from the transfer shipment confirmation process.
Shipment Date	Always	This field contains the date the transfer or PO was shipped.
Estimated Arrival Date	Always	This field contains the estimated arrival date of a vendor PO shipment. It is updated by EDIUP856. It is used for vendor/lead time analysis.
Ship Address 1	Optional	Value of addr.add_1 for store/warehouse number in addr.key_value_1 and addr.module 'ST' (if to_loc_type is store) or 'WH' (if to_loc_type is warehouse).
Ship Address 2	Optional	Value of addr.add_2 for store/warehouse number in addr.key_value_2 and addr.module 'ST' (if to_loc_type is store) or 'WH' (if to_loc_type is warehouse).
Ship Address 3	No	Not used by RMS.
Ship Address 4	No	Not used by RMS.
Ship Address 5	No	Not used by RMS.
Ship City	Optional	Value of addr.city for store/warehouse number in addr.key_value_1 and addr.module 'ST' (if to_loc_type is store) or 'WH' (if to_loc_type is warehouse).
Ship State	Optional	Value of addr.state for store/warehouse number in addr.key_value_1 and addr.module 'ST' (if to_loc_type is store) or 'WH' (if to_loc_type is warehouse).
Ship ZIP	No	Not used by RMS.
Ship Country Id	Optional	Value of addr.country_id for store/warehouse number in addr.key_value_1 and addr.module 'ST' (if to_loc_type is store) or 'WH' (if to_loc_type is warehouse).
Trailer Number	Always	Not used by RMS. Defaulted to 1.
Seal Number	No	Not used by RMS.
Trans Shipment Number	No	Not used by RMS.
Comments	Optional	Contains any miscellaneous comments about the shipment.
Carrier Code	Always	Contains the courier that will deliver the shipment. Defaulted to "DC".
Carrier Service Code	No	Contains the service level code for the courier that will deliver the shipment.
System Code	No	The code identifying the system associated with the location.
From Location Warehouse	No	For transfer and allocation shipments, this field will hold the virtual warehouse from which the shipment was sourced.

Table 2-4 Advance Shipment Notification Distro

Message Element	Included?	Notes
Distro Number	Always	Specifies the transfer or allocation number associated with the shipment/item/carton.
Distro Doc Type	Always	Specifies what the distro_nbr field corresponds to in RMS: V, D, and T specify transfer, A specifies Allocation.
Customer Order Number	Optional	This is the customer order number that was generated by OMS and contains multiple fulfillment numbers.
Fulfill Order Number	Optional	Based on a customer order - OMS will generate fulfillments to specific locations based on availability. RMS will generate Transfers based on the fulfillment request.
Consumer Direct	No	Not used by RMS.
Comments	No	Not used by RMS.

Table 2-5 Advance Shipment Notification Carton

Message Element	Included?	Notes
Final Location	No	Not used by RMS.
Container Id	Always	Identifies the UCC-128 carton number for shipments originating from the Advance Shipment Notification process as carton shipments. This field will be zero for all shipments that are not at a carton level.
Container Weight	No	Not used by RMS.
Container Length	No	Not used by RMS.
Container Width	No	Not used by RMS.
Container Height	No	Not used by RMS.
Container Cube	No	Not used by RMS.
Expedite Flag	Always	Indicates if the shipment should be expedited. For transfers, the value will be 'Y' if the transfer's freight_code is 'E'; otherwise 'N'. For allocations, the field is mapped to alloc_detail.rush_flag.
In Store Date	Always	Contains the date to be included in the RMS publication to the RIB for communication to the warehouse. For transfers, the value is always NULL. For allocations, the value is alloc_detail.in_store_date.
Tracking Number	No	This is a unique tracking number that is used to track containers through carrier’s system.
Freight Charge	No	Not used by RMS.
Master Container Id	No	Not used by RMS.
Comment	No	Contains any comments about the shipment container.
Weight	No	Actual weight shipped for the container.
Weight Unit of Measure	No	Unit of measurement for weight (e.g. pounds, kilograms) that was shipped.
Carrier Shipment Number	No	This field represents the shipment number that manifest systems use to group multi-container shipments for a carrier. Typically a 1 of 4 type message.
Original Item Id	No	The ID of the item being replaced. Populated only when this record is for a substitute item on a customer order.

Table 2-6 Advance Shipment Notification Item

Message Element	Included?	Notes
Item Id	Always	Unique identifier for the item.
Unit Quantity	Always	Contains the number of items expected to be received based on the supplier's advance shipment notification for this item/shipment combination.
Gross Cost	No	Contains the gross cost.
Priority Level	No	Not used by RMS.
Order Line Number	No	Is used to carry the customer order line number value for customer orders. Is a derived value for non-customer orders.
Lot Number	No	Not used by RMS.
Final Location	Always	The destination location of the shipment.
From Disposition	No	This value is used to determine if the inventory is available or unavailable, based on the code's INV_STATUS value on the INV_STATUS_CODES table.
To Disposition	No	Not used by RMS.
Voucher Number	No	Contains the voucher number.
Voucher Expiration Date	No	Not used by RMS.
Container Quantity	Always	Not used by RMS. Defaulted to 1.
Comments	No	Contains any comment about the item in the shipment.
Unit Cost	Always	Contains the unit cost of the item in the shipment.
Base Cost	No	This value will be used to get the base cost (BC) from RFM for a transfer, which will flow into RMS.
Weight	No	Actual weight shipped.
Weight Unit of Measure	No	Unit of measurement for weight (e.g. pounds, kilograms) shipped.

Table 2-7 Advance Shipment Notification UIN

Message Element	Included?	Notes
UIN	No	Universal Identification Number

Message XSD

Here is the filename that corresponds with the message type. Please consult the RIB documentation for this message type to get a detailed picture of the composition of the message.

Message Types	Message Type Description	XML Schema Definition (XSD)
asnoutcre	ASN Out Create Message	ASNOutDesc.xsd

Available Inventory for Store and Warehouse Publication API

This section describes the Store and Warehouse Publication API.

Functional Area

Inventory

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Merchandising publishes store and warehouse available inventory information to support the order management system requirements for ordering. Internally, Oracle Retail Order Broker (OROB) subscribes to this information. The following criteria must be met in order to publish inventory information when the stock on hand on a store or warehouse is updated:

• Items must be sellable

• Items must be a customer orderable location (based on the flags at the store and virtual warehouse level)

• Locations must be stockholding

This API always publishes inventory for virtual warehouses but will publish store information only based on the setting of the system option Publish Store Available to Sell Updates. If checked, then store information will also be published.

Item-Location Level Available to Sell Message

Message Element	Included?	Notes
Item	Always	The transaction item whose inventory information is being communicated.
Location	Always	Store or virtual warehouse where the item is to be found.
Location Type	Always	This contains the type of location. Valid values are store (S) or warehouse (W).
Available to Sell Quantity	Always	Contains the updated quantity available to sell for the item-location in the standard unit of measure. Available to sell is calculated as: Stock on Hand - (Transfer Reserved + Customer Reservations + Non-sellable + return to vendor quantity) All values above include also the pack component quantity, where applicable.
System Code	Always	Identifies the originating application, which is required by some subscribing systems. This value is defaulted from the system option Integration System Code.

Error Handling

If any errors are encountered while publishing the message, a fatal error with status E (Error) is sent to RIB. This would bring down the RIB adapter thus preventing any further messages from being processed until this is resolved.

Message XSD

Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
COInvAvailMod	Customer Order Store Inventory Available	COInvAvailDesc.xsd

Available Inventory for Store Publication API

This section describes the Store Publication API.

Functional Area

Foundation Data

Business Overview

The existing bulk store inventory feed needs to be updated to better support OMS requirements for ordering. The current bulk inventory feed does not consider customer orderable locations and does not consider pack component inventory. The following updates are required:

• Consider only sellable items (item_master.sellable_ind = Y)

• Include only stores that are customer orderable locations (store.customer_order_loc_ind = Y) and stockholding (store.stockholding_ind = Y)

The revised calculation should be as follows:

• Additions:

  • Stock On Hand

• Subtractions:

  • Transfer Reserved Quantity

  • Customer Reserved Quantity

  • Non-Sellable Quantity

  • RTV Quantity

Package Impact

File name: rmsmfm_coinvavails/b.pls

Function Level Description - ADDTOQ

Function: ADDTOQ(
           O_error_message          OUT  VARCHAR2,
           I_message_type           IN   ITEM_LOC_SOH_MFQUEUE.MESSAGE_TYPE%TYPE,
           I_item_loc_soh_record    IN   ITEM_LOC_SOH%ROWTYPE)

This public function puts an item location stock on hand message on ITEM_LOC_SOH_MFQUEUE for publishing to the RIB. It is called from item_loc_soh trigger where loc_type = 'S'. It also checks if the item is a sellable item and it also checks if the store is a customer_order_loc_ind = 'Y'.

Function Level Description - GETNXT

Procedure: GETNXT
        (O_status_code      OUT  VARCHAR2,
         O_error_msg        OUT  VARCHAR2,
         O_message_type     OUT  VARCHAR2,
         O_message          OUT  RIB_OBJECT,
         O_bus_obj_id       OUT  RIB_BUSOBJID_TBL,
         O_routing_info     OUT  RIB_ROUTINGINFO_TBL,
         I_num_threads   IN      NUMBER DEFAULT 1,
         I_thread_val    IN      NUMBER DEFAULT 1)

This public procedure is called from the RIB to get the next messages. It performs a cursor loop on the unpublished records on the ITEM_LOC_SOH_MFQUEUE table (PUB_STATUS = 'U').

If any exception is raised in GETNXT, including the exception raised by an unsuccessful call to PROCESS_QUEUE_RECORD, HANDLE_ERRORS is called.

Function Level Description - PUB_RETRY

Procedure: PUB_RETRY
        (O_status_code      OUT     VARCHAR2,
         O_error_msg        OUT     VARCHAR2,
         O_message          OUT     RIB_OBJECT,
         O_message_type  IN OUT     VARCHAR2,
         O_bus_obj_id    IN OUT     RIB_BUSOBJID_TBL,
         O_routing_info  IN OUT     RIB_ROUTINGINFO_TBL)

This public procedure performs the same tasks as GETNXT except that it only loops for a specific row in the ITEM_LOC_SOH_MFQUEUE table. The record on ITEM_LOC_SOH_MFQUEUE must match the passed in sequence number (contained in the ROUTING_INFO).

Function Level Description - PROCESS_QUEUE_RECORD (local)

This private function controls the building of Oracle Objects (DESC or REF) given the business transaction's key values and a message type. It contains all of the shared processing between GETNXT and PUB_RETRY.

Function Level Description - HANDLE_ERRORS (local)

This private procedure is called from GETNXT and PUB_RETRY when an exception is raised. I_seq_no is the sequence number of the driving ITEM_LOC_SOH_MFQUEUE record.

If the error is a non-fatal error, HANDLE_ERRORS passes the sequence number of the driving ITEM_LOC_SOH_MFQUEUE record back to the RIB in the ROUTING_INFO. It sends back a status of 'H' - Hospital to the RIB as well. It then updates the status of the queue record to 'H', so that it will not get picked up again by the driving cursor in GETNXT.

If the error is a fatal error, a status of 'E' - Error is returned to the RIB. The error is considered non-fatal if no DML has occurred yet. Whenever DML has occurred, then the global variable LP_error_status is flipped from 'H' to 'E'.

Message XSD

Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
COInvAvailMod	Customer Order Store Inventory Available	COInvAvailDesc.xsd

Table Impact

TABLE	SELECT	INSERT	UPDATE	DELETE
ITEM_LOC_SOH_MFQUEUE	Yes	Yes	Yes	Yes

Design Assumptions

Push off all DML statements as late as possible. Once DML statements have taken place, any error becomes a fatal error rather than a hospital error.

Banner Publication API

This section describes the banner publication API.

Functional Area

Foundation

Business Overview

Merchandising publishes messages about banners and channels to the Oracle Retail Integration Bus (RIB). A banner provides a means of grouping channels thereby allowing the customer to link all brick and mortar stores, catalogs, and web stores. The BANNER table holds a banner identifier and name. The CHANNELS table shows all channels and any associated banner identifiers.

The following diagram shows a sample of the structure of banners and channels within a corporation.

Figure 2-1 Banners and Channels Within a Corporation

Banner/channel publication consists of a single flat message containing information from the tables BANNER and CHANNELS. One message is synchronously created and placed in the message queue each time a record is created, modified, or deleted. When a record is created or modified, the flat message contains several attributes of the banner/channel. When a record is deleted, the message contains the unique identifier of the banner/channel. Messages are retrieved from the message queue in the order they were created.

Package Impact

This section describes the package impact.

Create

1. Prerequisites: For channel creation, the associated banner must have been created.

2. Activity Detail: Once a banner/channel has been created, it is ready to be published. An initial publication message is made.

3. Messages: A "Banner Create" / "Channel Create" message is queued. This message is a flat message that contains a full snapshot of the attributes on the BANNER or CHANNEL table.

Modify

1. Prerequisites: banner/channel has been created.

2. Activity Detail: The user is allowed to change attributes of the banner/channel. These changes are of interest to other systems and so this activity results in the publication of a message.

3. Messages: Any modifications will cause a "banner modify" / channel modify" message to be queued. This message contains the same attributes as the "banner create" / "channel create" message.

Delete

1. Prerequisites: banner/channel has been created.

2. Activity Detail: Deleting a banner/channel removes it from the system. External systems are notified by a published message.

3. Messages: When a banner/channel is deleted, a "Banner Delete" / "Channel Delete" message, which is a flat notification message, is queued. The message contains the banner/channel identifier.

Package name: RMSMFM_banner

Spec file name: rmsmfm_banners.pls

Body file name: rmsmfm_bannerb.pls

Package Specification - Global Variables: None

Function Level Description - ADDTOQ

PROCEDURE ADDTOQ(O_status             OUT   VARCHAR2,
                 O_text               OUT   VARCHAR2,
                 I_banner_message  IN       BANNER_MFQUEUE%ROWTYPE)

This procedure is called by the trigger EC_TABLE_BAN_AIUDR and takes the message type, banner_id and channel_value if there is one in the message itself. It inserts a row into the BANNER_MFQUEUE, along with the passed-in values and the next sequence number from the BANNER_MFSEQUENCE, setting the status to ‘U'npublished. It returns a status code of API_CODES.SUCCESS if successful, and API_CODES.UNHANDLED_ERROR if not.

Function Level Description - GETNXT

PROCEDURE GETNXT(O_status_code        OUT   VARCHAR2,
                 O_error_message      OUT   RTK_ERRORS.RTK_TEXT%TYPE,
                 O_message_type       OUT   VARCHAR2,
                 O_message            OUT   RIB_OBJECT,
                 O_bus_obj_id         OUT   RIB_BUSOBJID_TBL,
                 O_routing_info       OUT   RIB_ROUTINGINFO_TBL,
                 I_num_threads     IN       NUMBER DEFAULT 1,
                 I_thread_val      IN       NUMBER DEFAULT 1)

This publicly exposed procedure is typically called by a RIB publication adaptor. Its parameters are well defined and arranged in a specific order.

The procedure will use the defined C_GET_MESSAGE cursor to retrieve the next message on the BANNER_MFQUEUE to be published to the RIB.

The information from BANNER_MFQUEUE table that is passed to PROCESS_QUEUE_RECORD.PROCESS_QUEUE_RECORD will build the Oracle Object message to pass back to the RIB. If PROCESS_QUEUE_RECORD does not run successfully, GETNXT will raise an exception.

After PROCESS_QUEUE_RECORD returns an Oracle object to pass to the RIB, this procedure will delete the record on BANNER_MFQUEUE that was just processed.

If any exception is raised in GETNXT, including the exception raised by an unsuccessful call to PROCESS_QUEUE_RECORD, HANDLE_ERRORS should be called.

Function Level Description - PUB_RETRY

Procedure: PUB_RETRY(O_status_code      OUT    VARCHAR2,
                     O_error_msg        OUT    VARCHAR2,
                     O_message          OUT    RIB_OBJECT,
                     O_message_type  IN OUT    VARCHAR2,
                     O_bus_obj_id    IN OUT    RIB_BUSOBJID_TBL,
                     O_routing_info  IN OUT    RIB_ROUTINGINFO_TBL,
                     I_REF_OBJECT    IN        RIB_OBJECT);

Same as GETNXT, except that the record on BANNER_MFQUEUE must match the passed-in sequence number (contained in the ROUTING_INFO).

Trigger Impact

Trigger exists on the banner and channels tables to capture inserts, updates, and deletes.

Trigger name: EC_TABLE_BAN_AIUDR.TRG

Trigger file name: ec_table_ban_aiudr.trg

Table: BANNER

This trigger captures inserts/updates/deletes to the BANNER table and writes data into the BANNER_MFQUEUE message queue. It calls RMSMFM_BANNER.ADDTOQ to insert this message into the message queue.

• Inserts: Sends banner_id to the ADDTOQ procedure with message type RMSMFM_FAMILY.BANNER_CRE.

• Updates: Sends banner_id to the ADDTOQ procedure with message type RMSMFM_FAMILY.BANNER_MOD.

• Deletes: Sends banner_id to the ADDTOQ procedure with message type RMSMFM_FAMILY.BANNER_DEL.

Trigger name: EC_TABLE_CHN_AIUDR.TRG

Trigger file name: ec_table_chn_aiudr.trg

Table: CHANNELS

This trigger captures inserts/updates/deletes to the CHANNELS table and writes data into the BANNER_MFQUEUE message queue. It calls RMSMFM_BANNER.ADDTOQ to insert this message into the message queue.

• Inserts: Sends banner_id and channel_id to the ADDTOQ procedure with message type RMSMFM_FAMILY.CHANNEL_CRE.

• Updates: Sends banner_id and channel_id to the ADDTOQ procedure with message type RMSMFM_FAMILY.CHANNEL_MOD.

• Deletes: Sends banner_id and channel_id to the ADDTOQ procedure with message type RMSMFM_FAMILY.CHANNEL_DEL.

Table Impact

TABLE	SELECT	INSERT	UPDATE	DELETE
BANNER_MFQUEUE	Yes	Yes	No	Yes

Design Assumptions

One of the primary assumptions in the current approach is that ease of code will outweigh performance considerations. It is hoped that the 'trickle' nature of the flow of data will decrease the need to dwell on performance issues and instead allow developers to code in the easiest and most straightforward manner.

Code and Diff Type Publication API

This section describes the seed data publication API.

Functional Area

Foundation Data

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Merchandising defines and publishes code types, codes, and their descriptions, along with differentiator (diff) type information for data seeding in external systems, as they are usually fairly static and do not frequently change after initial implementation. However, changes and deletes are also managed in this integration. Subscribing to this information in an external system allows it to interpret information included in other Merchandising integrations, such as diffs associated with an item or statuses and other codes associated with purchase orders, transfers, and so on.

Code types and codes published are defined in Merchandising and are mostly used to display lists in the Merchandising UI like item types, shipping methods, supplier types, location types, and so on. Most code types and codes are part of the base implementation of Merchandising, but it is also possible to add or update codes and descriptions to suit your implementation. Code types and codes defined in Merchandising are also used in Sales Audit and Pricing. All code types and codes are published by this integration, regardless of whether or not they are flagged as Used in your implementation.

Diff Types are used to qualify the diff IDs included in other integration. Examples of diff types are size, color, flavor, and so on.

New Code Types

The creation of a new code type triggers the generation of a code header creation message. All code types defined in the merchandising is published to the external systems. The table below summarize the details included in this message.

Code Header

Table 2-8 Code Header Message Elements

Message Element	Included?	Notes
Code Type	Always	This field contains the code type which serves as a grouping mechanism for the codes.
Code Type Description	Always	This field contains the description of the code type.

Updated Code Types

Modifying the description of a code type will trigger the creation of a code header modification message. The update message will contain the details for all fields that changed to inform subscribing applications of the changes made in Merchandising. See the New Code Types section for details on the message.

Deleted Code Types

When a code type is removed, it will trigger a code header delete transaction message to an external system.

Code Header

Table 2-9 Code Header Message Elements

Message Element	Included?	Notes
Code Type	Always	This field contains the code type being deleted.

New Codes

The creation of a new code triggers the generation of a code detail creation message. All codes defined in the merchandising is published to the external systems. The table below summarize the details included in this message.

Code Detail

Table 2-10 New Codes Code Detail Message Elements

Message Element	Included?	Notes
Code Type	Always	This field contains a valid code type for the code.
Code	Always	This field contains the new code that has been added.
Code Description	Always	This field contains the description associated with the code.
Required Indicator	Always	This field indicates whether or not the code is required by Merchandising. Valid values are Yes (Y) or No (N).
Code Sequence	Always	This number is used to order the codes for display purposes.

Updated Codes

Modifying the description, required indicator, or code sequence of a code will trigger the creation of code detail modification message. The update message will contain the details for all fields that changed to inform subscribing applications of the changes made in Merchandising. See the New Codes section for details on the message.

Deleted Codes

When a code is removed, it will trigger a code detail delete transaction message to an external system.

Note:

Setting a code's Used flag to No in Merchandising does not trigger a delete message to be published.

Code Detail

Table 2-11 Delete Codes Code Detail Message Elements

Message Element	Required?	Notes
Code Type	Always	This field contains the code type for the code being deleted.
Code	Always	This field contains the code being deleted.

New Diff Types

The creation of a new diff type triggers the generation of a diff type create message. The tables below summarize the details of the message.

Diff Type

Table 2-12 New Diff Types Diff Type Message Elements

Message Element	Required?	Notes
Diff Type	Always	Contains the value used to uniquely identify the diff type being added or updated.
Diff Type Description	Always	Contains the diff type description.

Child Nodes

• Custom Flex Attributes (optional)

Custom Flex Attributes (CFAS)

Table 2-13 Custom Flex Attributes

Message Element	Required?	Notes
Name	Always	Contains the attribute name configured for a diff type flex attribute.
Value	Optional	Contains the value of the attribute associated with the diff type for a character or number attribute.
Value Date	Optional	Contains the value of the attribute associated with the diff type, if the attribute is defined as a date type.

Updated Diff Types

Modifying diff type information will trigger the creation of a diff type modification message. The update message will contain the details for all fields that changed in the message in a format similar to that described above, to inform subscribing applications of the changes made in Merchandising.

Whenever a diff type is modified, all active CFAS defined for the diff type will be included in the message.

Note:

Only active CFAS will be included

Deleted Diff Types

When a diff type is removed, it will trigger a diff type delete transaction message to an external system. CFAS attributes are not included in the delete message, but a delete of the diff type implies that the flex attributes should also be deleted in the subscribing solution.

Diff Type

Table 2-14 Diff Type Message Elements

Message Element	Required?	Notes
Diff Type	Always	Contains the value used to uniquely identify the diff type being deleted.

Error Handling

When the publication encounters a non-fatal error, messages continue to be processed. For the particular message where the error was encountered, a status of Hospital (H) is sent to the RIB and the status of the message in the queue is set to H. These types of errors occur when no changes in the database have been made and a process to try to re-publish the messages is available. In case the error is a fatal error, a status of Error (E) is sent to the RIB and the next message in the queue is not retrieved until the error is resolved.

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Table 2-15 Message XSD

Message Types	Message Type Description	XML Schema Definition (XSD)
CodeHdrCre	Code Header Create	CodeHdrDesc.xsd
CodeHdrMod	Code Header Modify	CodeHdrDesc.xsd
CodeHdrDel	Code Header Delete	CodeHdrRef.xsd
CodeDtlCre	Code Detail Create	CodeDtlDesc.xsd
CodeDtlMod	Code Detail Modify	CodeDtlDesc.xsd
CodeDtlDel	Code Detail Delete	CodeDtlRef.xsd
difftypecre	Diff Type Create	DiffTypeDesc.xsd
difftypemod	Diff Type Modify	DiffTypeDesc.xsd
difftypedel	Diff Type Delete	DiffTypeRef.xsd

Company Closed Publication API

This section describes the company closed publication API.

Functional Area

Foundation Data

Business Overview

Merchandising publishes details about new and updated company-wide closings in order for external systems that use this information to be informed of the updates, including company closed exceptions.

New Closings

Creating a company closing date triggers a message to be sent to notify external systems. The close date and close description are sent as part of the create message.

Updated Closings

Updating the description for a company closing date triggers a message to be sent to notify external systems. The close date and modified close description are sent as part of the message.

Deleted Closings

When a company closing date is deleted, this will also trigger a delete transaction to be sent to notify external systems that this company close date is no longer valid. The delete message will include only the close date.

New Closing Exceptions

Creating a company closing exception triggers a message to be sent to notify external systems. The close date, exception location, location type, and an indicator for whether or not the location is open for sales, receiving, and/or shipping are sent as part of the create message.

Updated Closing Exceptions

Updating a company closing exception triggers a message to be sent to notify external systems. The close date, exception location, location type, and an indicator for whether or not the location is open for sales, receiving, and/or shipping are sent as part of the message.

Deleted Closing Exceptions

When a company closing exception is deleted, this will also trigger a delete transaction to be sent to notify external systems that this company closed exception is no longer valid. The delete message will include only the close date and location.

Error Handling

If any errors are encountered while publishing the message, a fatal error with status E (Error) is sent to RIB. This would bring down the RIB adapter thus preventing any further messages from being processed until this is resolved.

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Message Types	Message Type Description	XML Schema Definition
CoClosedCre	Company Closing Create	CompanyClosedDesc.xsd
CoClosedExcCre	Company Closing Exception Create	CompanyClosedExcepDesc.xsd
CoClosedMod	Company Closing Modify	CompanyClosedDesc.xsd
CoClosedExcMod	Company Closing Exception Modify	CompanyClosedExcepDesc.xsd
CoClosedDel	Company Closing Delete	CompanyClosedRef.xsd
CoClosedExcDel	Company Closing Exception Delete	CompanyClosedExcepRef.xsd

Country Publication API

This section describes the countries publication API.

Functional Area

Foundation Data

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Seed object publication to the RIB allows Merchandising to send country information as well as currency rates so that external systems will have all of the latest information regarding countries and currency rates.

Seed object publication consists of a message containing country and currency rate information from the tables COUNTRY and CURRENCY_RATES. One message will be synchronously created and placed in the message queue each time a COUNTRY and CURRENCY_RATES record is created, modified or deleted in Merchandising. When a COUNTRY or CURRENCY_RATES record is created or modified, the message will contain a full snapshot of the modified record. When a COUNTRY record is deleted, the message will contain a partial snapshot of the deleted record. Messages are retrieved from the message queue in the order they were created.

New Country

The data seeding of country information and creation of a new country triggers the generation of a country creation message. The country creation message publishes the country ID, along with the country's attributes.

Country

Table 2-16 Country

Message Element	Included?	Notes
Country ID	Always	This contains the unique country identifier that has been added or updated.
Country Description	Always	This contains the name of the country.

Updated Country

Modifying a country information as part of various business processes will trigger the creation of country modification message. The update message will contain the details for all fields that changed in the message to inform subscribing applications of the changes made in Merchandising.

Deleted Country

When a country is removed, it will trigger a country delete transaction message to an external system.

Table 2-17 Deleted Country

Message Elements	Required?	Notes
Country ID	Always	This contains the unique country identifier that has been deleted.

Country

Message Elements	Included?	Notes
Country ID	Always	This contains the unique country identifier that has been deleted.

Error Handling

When the publication encounters a non-fatal error, messages continue to be processed. For the particular message where the error was encountered, a status of Hospital (H) is sent to the RIB and the status of the message in the queue is set to H. These types of errors occur when no changes in the database have been made and a process to try to re-publish the messages is available. In case the error is a fatal error, a status of Error (E) is sent to the RIB and the next message in the queue is not retrieved until the error is resolved.

Message XSD

Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
countrycre	Country Create Message	CountryDesc.xsd
countrymod	Country Modify Message	CountryDesc.xsd
countrydel	Country Delete Message	CountryRef.xsd

Customer Order Fulfillment Confirmation Publication API

This section describes the customer order fulfillment confirmation publication API.

Functional Area

Customer Orders

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

When Merchandising is integrated with an order management system (OMS) or Oracle Retail Order Broker (OROB), one of the supported deployment methods is interfacing customer order fulfillment requests into Merchandising through the Oracle Retail Integration Bus (RIB). When Merchandising processes the customer order fulfillment request from OROB, it will also publish a confirmation message back indicating whether the order was created fully, partially, or not created. The confirmation message details are shown below.

Merchandising will only publish confirmation messages associated to customer orders that create a Purchase Order or Transfer, which is used for fulfillment from a supplier or a warehouse, or if the fulfillment requires movement between two locations, such as from warehouse to store for customer pickup.

Table 2-18 Fulfill Order Confirmation Message

Message Element	Included?	Notes
Customer Order Number	Always	This is the customer order number from OROB or OMS that is being confirmed.
Fulfillment Order Number	Always	This is the fulfillment order number from OROB or OMS.
Confirmation Type	Always	Contains the confirmation type. Valid values are: C - Order Completely Created P - Order Partially Created X - Order Could not be Created
Confirmation Number	Optional	This field contains the PO or Transfer number in Merchandising related to the fulfillment order line. This is populated if fulfillment status is P or C.
Confirmation Details	Optional	Child Node
Fulfillment Location ID	Optional	This field contains the fulfillment location for the order.
System Code	Always	Used by OROB to identify the system associated with this message. This is defaulted from the Merchandising system option.

Table 2-19 Fulfill Order Confirmation Detail Message

Message Element	Required?	Notes
Item	Always	This field indicates the item ordered by the customer.
Reference Item	Optional	This field indicates the reference item ordered by the customer. It is used only if a specific UPC is ordered.
Confirmed Quantity	Always	This field indicates the quantity of the item that can be sourced or fulfilled on the order.
Confirmed Quantity UOM	Always	This field indicates the unit of measure of the confirmation quantity.
Item Line Number	Optional	This field indicates the detail item line number on the order.

Error Handling

When the publication encounters a non-fatal error, messages continue to be processed. For the message where the error was encountered, a status of Hospital (H) is sent to the RIB and the status of the message in the queue is set to H. These types of errors occur when no changes in the database have been made and a process to try to re-publish the messages is available. In case the error is a fatal error, a status of Error (E) is sent to the RIB and the next message in the queue is not retrieved until the error is resolved.

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
fulfilordcfmcre	Customer Order Fulfillment Confirmation Create	FulfilOrdCfmDesc.xsd FulfilOrdCfmDtl.xsd

Delivery Slot Publication API

This section describes the delivery slot publication API.

Functional Area

Replenishment

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

This API publishes delivery slot details to external systems. This information is further subscribed by integrating systems like Oracle Retail Store Inventory Management (SIM). Within Merchandising, delivery slots are only used with the Store Order method of replenishment, allowing you the option to have multiple deliveries per day for the same store/day.

Create Delivery Slot

Delivery slot creation will result in publishing the ID, description, and a sequence to notify the integrating systems.

Update Delivery Slot

Modifying an existing delivery slot in Merchandising, will result in publishing a delivery slot modify message to notify the integrating systems. This includes the ID, description, and sequence value of delivery slot.

Table 2-20 Delivery Slot Create and Update

Message Element	Included?	Notes
Delivery Slot ID	Always	This field specifies a code indicating the delivery timeframe for stores that is being created or updated. For example, AM or PM.
Delivery Slot Description	Always	This field holds the delivery slot description. This could contain the time element for the delivery schedule (i.e., Afternoon Slot 3 PM).
Delivery Slot Sequence	Always	This column will specify the display sequence for the delivery slots.

Delete Delivery Slot

Deleting an existing delivery slot in Merchandising will result in publishing a delivery slot deletion message to notify the Integrating systems. Only the slot ID is included for deletes.

Table 2-21 Delivery Slot Delete

Message Element	Included?	Notes
Delivery Slot ID	Always	This field specifies a code indicating the delivery timeframe for stores that is being deleted.

Error Handling

If any errors are encountered while publishing the message, a fatal error with status E (Error) is sent to RIB. This would bring down the RIB adapter thus preventing any further messages from being processed until this is resolved.

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
Dlvysltcre	Delivery Slot Create Message	DeliverySlotDesc.xsd
Dlvysltmod	Delivery Slot Modify Message	DeliverySlotDesc.xsd
Dlvysltdel	Delivery Slot Delete Message	DeliverySlotRef.xsd

Differentiator Group Publication API

This section describes the differentiator groups publication API.

Functional Area

Items

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Merchandising publishes differentiator groups and its details through the RIB to external systems. Diff groups are published when a new diff group is created, updates are made to existing diff group, or an existing diff group is deleted.

New Diff Group Header

Creating a new diff group header triggers a message to be sent to notify external systems. The full details are also always sent for the new diff group.

New Diff Group Detail

Creating a new diff group detail record for an existing diff group also triggers a message to be sent to notify external systems. The details that are sent for the detail creation are the existing diff group ID, diff ID, and the display sequence, which indicates the order that diffs should be displayed within the group.

Updated Diff Group Header

When an existing diff group header is updated, an update message is triggered to provide the details of the update. The update message, like create, will contain the full details in the message for all fields at the header level.

Updated Diff Group Detail

When an existing detail for a diff group is updated, an update message is triggered to provide the details of the update. The update message, like create, will contain the full details in the message for all fields.

Table 2-22 Diff Group Header Create and Update

Message Element	Required?	Notes
Diff Group ID	Always	This field contains the ID of the diff group being added or updated.
Diff Group Type	Always	This field contains the code for the types of diffs contained in this group, such as S for size.
Diff Group Description	Always	This field contains the description of the diff group.
Diff Group Type Description	Optional	This field contains the description of the diff type, such as Size or Color.

Table 2-23 Diff Group Detail Create and Update

Message Element	Required?	Notes
Diff Group ID	Always	This field contains the ID of the diff group being added or updated.
Diff ID	Always	This field contains the ID of the diff that is included in the group.
Display Sequence	Optional	This field contains the display sequence for the diff in the group.

Deleted Diff Group

When an existing diff group is deleted, this will also trigger a delete transaction to be sent to notify external systems that this diff group is no longer valid. The delete message will include only the diff group ID. All the child records for the diff group should also be deleted when processing a diff group delete in an external system.

Deleted Diff Group Detail

When the existing details of diff group are deleted, this will also trigger a delete transaction to be sent to notify external systems that these details are no longer part of the diff group. The delete message will include the diff group ID and the diff ID. The diff ID would be the detail record from the diff group that has been deleted.

Table 2-24 Diff Group Header

Message Element	Required?	Notes
Diff Group ID	Always	This field contains the ID of the diff group being deleted.

Table 2-25 Diff Group Detail

Message Element	Required?	Notes
Diff Group ID	Always	This field contains the ID of the diff group whose detail is being deleted.
Diff ID	Always	This field contains diff being deleted from the group.

Error Handling

If any errors are encountered while publishing the message, a fatal error with status E (Error) is sent to RIB. This would bring down the RIB adapter thus preventing any further messages from being processed until this is resolved

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
DiffGrpHdrCre	Diff Group Create	DiffGrpHdrDesc.xsd
DiffGrpDtlCre	Diff Group Detail Create	DiffGrpDtlDesc.xsd
DiffGrpHdrMod	Diff Group Modify	DiffGrpHdrDesc.xsd
DiffGrpDtlMod	Diff Group Detail Modify	DiffGrpDtlDesc.xsd
DiffGrpDel	Diff Group Delete	DiffGrpRef.xsd
DiffGrpDtlDel	Diff Group Detail Delete	DiffGrpDtlRef.xsd

Differentiator Publication API

This section describes the differentiators publication API.

Functional Area

Items

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Merchandising publishes details about new and updated differentiator identifiers (diff IDs) in order that external systems that use this information can be informed of the updates. Updates are provided synchronously in a near-real time manner. When the external system receives information about an item that includes the new differentiator ID, that system understands what the differentiator ID refers to.

New Differentiator IDs

When a new differentiator ID is created in Merchandising, it triggers a message to be sent to notify external systems. The full details are sent for the new differentiator ID as part of the create message, the differentiator ID, differentiator type, differentiator description, industry code, industry sub code, and differentiator type description.

Updated Differentiator IDs

When an existing differentiator ID is updated, an update message is triggered to provide the details of the update. The update message, like create, will contain the full details in the message for all fields in the message.

Table 2-26 Differentiator Create and Update

Message Element	Required?	Notes
Differentiator ID	Always	This field contains a unique identifier of the differentiator.
Differentiator Type	Always	This field contains a code indicating the type of differentiator, for example C for Color.
Differentiator Description	Always	This field contains the description of the differentiator.
Industry Code	Optional	This field is used to hold the unique code used by industry standards to identify the differentiator.
Industry Sub Code	Optional	This field is used to hold a sub-grouping code used by industry standards to further identify the differentiator.
Differentiator Type Description	Optional	This field holds the description of the diff type (e.g., Color, Size, and so on).

Deleted Differentiator IDs

When an existing differentiator ID is deleted, this will also trigger a delete transaction to be sent to notify external systems that this differentiator ID is no longer valid. The delete message will include only the ID of the differentiator being deleted.

Table 2-27 Differentiator Delete

Message Element	Required?	Notes
Differentiator ID	Always	This field contains a unique identifier of the differentiator.

Error Handling

When the publication encounters a non-fatal error, messages continue to be processed. For the message where the error was encountered, a status of Hospital (H) is sent to the RIB and the status of the message in the queue is set to H. In case the error is a fatal error, a status of Error (E) is sent to the RIB and the next message in the queue is not retrieved until the error is resolved.

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
diffcre	Diff Create	DiffDesc.xsd
diffmod	Diff Modify	DiffDesc.xsd
diffdel	Diff Delete	DiffRef.xsd

Item Publication API

This section describes the item publication API.

Functional Area

Items

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Merchandising publishes messages about items to the Oracle Retail Integration Bus (RIB). In situations where a retailer creates a new item in Merchandising, the message that ultimately is published to the RIB contains a hierarchical structure of the item itself along with all components that are associated with that item. Items and item components make up what is called the Items message family.

After the item creation message has been published to the RIB for use by external applications, any modifications to the basic item or its components cause the publication of individual messages specific to that component. Deletion of an item and component records has similar effects on the message modification process, with the exception that the delete message holds only the key(s) for the record.

When publishing an item header modification, pack item creation, pack item modification, pack item delete, reference item creation, reference item modification, and reference item deletion, a second full replacement message from Merchandising will be published when the publish full objects indicator is Yes. This message payload will contain a full snapshot of the item. Based on the message type, RIB will route the full replacement message to appropriate applications.

Deposit items

A deposit item is a product that has a portion which is returnable to the supplier and sold to the customer, with a deposit taken for the returnable portion. Because the contents portion of the item and the container portion of the item have to be managed in separate financial accounts (as the container item would be posted to a liabilities account) with different attributes, the retailer must set up two separate items. All returns of used deposit items (the returned item) are managed as a separate product, to track these products separately and as a generic item not linked to the actual deposit item (for example, bottles being washed and having no label).

The retailer can never put a container item on a transfer. The container item is added to returns to vendors (RTVs) automatically when the retailer adds the associated content item.

Deposit item attributes in Merchandising enable contents, container and crate items to be distinguished from one another. Additionally, it is possible to link a contents item to a container item for the purposes of inventory management.

In addition to contents and container items, many deposit items are delivered in plastic crates, which are also given to the customer on a deposit basis. These crates are sold to a customer as an additional separate product. Individual crates are not linked with contents or container items. Crates are specified in the system with a deposit item attribute.

From a receiving perspective, only the content item can be received. The receipt of a PO shows the container item but the receipt of a transfer does not. Similar to RTV functionality, online purchase order functionality automatically adds the container. The system automatically replicates all transactions for the container item in the stock ledger. In sum, for POs and RTVs, the container item is included; for transfers, no replication occurs.

Catch-Weight Items

Retailers can order and manage products for the following types of catch-weight item:

• Type 1: Purchase in fixed weight simple packs: sell by variable weight (for example, bananas).

• Type 2: Purchase in variable weight simple packs: sell by variable weight (for example, ham on the bone sold on a delicatessen counter).

• Type 3: Purchase in fixed weight simple packs containing a fixed number of eaches: sell by variable weight eaches (for example, pre-packaged cheese).

• Type 4: Purchase in variable weight simple packs containing a fixed number of eaches: sell by variable weight eaches (for example, pre-packaged sirloin steak).

  Note:

  Oracle Retail suggests that catch-weight item cases be managed through the standard simple pack functionality.

In order for catch-weight items to be managed in Merchandising, the following item attributes are available:

• Cost UOM: All items in Merchandising will be able to have the cost of the item managed in a separate unit of measure (UOM) from the standard UOM. Where this is in a different UOM class from the standard UOM, case dimensions must be set up.

• Catch-weight item pack details: Tolerance values and average case weights are stored for catch-weight item cases to allow the retailer to report on the sizes of cases received from suppliers.

• Maximum catch-weight tolerance threshold.

• Minimum catch-weight tolerance threshold.

Retailers can set up the following properties for a catch-weight item:

• Order type

• Sale type

Retailers can also specify the following, at the item-supplier-country level:

• Cost unit of measure (CUOM).

Receiving and inventory movement impact on catch-weight items

Inventory transaction messages include purchase order receiving, stock order receiving, returns to vendor, direct store delivery receiving, inventory adjustments and bill of lading. These messages include attributes that represent, for catch-weight items, the actual weight of goods involved in a transaction. These attributes are weight and weight UOM.

When Merchandising subscribes to inventory transaction messages containing such weight data, the transaction weight will be used for two purposes:

• To update weighted average cost (WAC) using the weight rather than the number of units and to update the average weight value of simple packs.

  Note:

  The WAC calculation does not apply to return to vendors (RTVs).

Item Transformation

Item transformation allows retailers to manage items where the actual transformation of a product cannot be adequately recorded due to in-store processes.

With product transformation, new 'transform' items are set up as either sellable only or orderable only.

• Sellable only items: A sellable only item has no inventory in the system, so inventory records cannot be viewed from the item maintenance screens. Sellable only items do not hold any supplier links and therefore have no cost prices associated with them.

• Orderable only items: Orderable only items hold inventory, but are not sellable at the POS system. Therefore, no information is sent to the POS system for these items, and no unit retail prices by zone are held for these items.

To hold the relationship between the orderable items and the sellable items, Merchandising stores the transformation details. These details are used to process sales and inventory transactions for the items.

The following diagram shows how item transformation works:

Figure 2-2 Item Transformation

Item and Item Component Descriptions

The item message family is a logical grouping for all item data published to the RIB. The components of item messages and their base tables in Merchandising are:

• Item from the ITEM_MASTER table

• Item-supplier from ITEM_SUPPLIER

• Item-supplier-country from ITEM_SUPP_COUNTRY

• Item-supplier-country-dimension from ITEM_SUPP_COUNTRY_DIM (DIM is the each, inner, pallet, and case dimension for the item, as specified)

• Item-image from ITEM_IMAGE

• Item-UDA identifier-UDA value from UDA_ITEM_LOV (UDA is a user-defined attribute and LOV is list of values)

• Item-UDA identifier from UDA_ITEM_DATE (for the item and UDA date)

• Item-UDA identifier from UDA_ITEM_FF (for UDA, free-format data beyond the values for LOV and date)

• Item-pack components (Bill of Material [BOM]) from PACKITEM_BREAKOUT

• Item UPC reference from ITEM_MASTER.ITEM_NUMBER_TYPE (values held as code type 'UPCT' on code_head and code_detail tables)

• Item ticket from ITEM_TICKET

• Item relationship details from RELATED_ITEM_HEAD

• Related Items details from RELATED_ITEM_DETAIL

New Item Message Processes

The creation of a new item in Merchandising begins with an item in a worksheet status on the ITEM_MASTER table. At the time an item is created, other relationships are being defined as well, including the item, supplier, and country relationships, user-defined attributes (UDAs), related items and others. These item relationship processes in effect become components of a new item message published to the RIB. This section describes the item creation message process and includes the basic item message itself along with the other component relationship messages that become part of the larger item message.

Basic Item Message

As described in the preceding section, item messages can originate in a number of Merchandising tables. Creating, modifying or deleting an item triggers a message to be sent through the RIB to notify external systems. Additions, modifications and deletions to item family records for existing approved items are published in the order that they are placed in the queue.

New Item Message Publication

The publication of a new item and its components to the RIB is done using a hierarchical message. All the components that comprise the creation of an item, the item/supplier for example, remain in the queue until the item approval modification message has been published. Any modifications or deletions that occur between item creation in “W”(worksheet) status and “A”(Approved) status are applied to the “create” messages or deleted from the queue as required.

For example, if an item UDA is added before item approval and then later deleted before item approval, the item UDA “create” message would be deleted from the queue before publishing the item. If an item/supplier record is updated for a new item before the item is approved, the “create” message for that item/supplier is updated with the new data before the item is published. When the “modify” message that contains the “A” (Approved) status is the next record on the queue, the procedure formats a hierarchical message that contains the item header information and all the child detail records to pass to RIB.

Subordinate Data and XML Tags

While a new item is being created, item components are also being created. Described earlier in this overview, these component item messages pertain to the item-supplier, item-supplier-country, UDAs, and so on.

For example, when a new item-supplier is created, this triggers a message to be created. When the item is approved, this item-supplier message will be added to the item creation message.

Table 2-28 Item Header

Message Element	Included?	Notes
Item	Always	Unique alphanumeric value that identifies the item.
Item Number Type	Always	Indicates the type of the number, for example UPC-12. The values for this column are maintained in code type UPCT.
Format Id	Optional	This field will hold the format ID that corresponds to the item's variable UPC. This value is only used for items with variable UPCs.
Prefix	Optional	This column holds the prefix for variable weight UPCs. The prefix determines the format of the eventual UPC and will be used to decode variable weight UPCs that are uploaded from the POS. It is the clients responsibility to download this value to their POS system.
Item Parent	Optional	Alphanumeric value that uniquely identifies the item/group at the level above the item. This value must exist as an item in another row on the item_master table.
Item Grandparent	Optional	Alphanumeric value that uniquely identifies the item/group two levels above the item. This value must exist as both an item and an item parent in another row on the item_master table.
Pack Indicator	Always	Indicates if the item is a pack. A pack item is a collection of items that may be either ordered or sold as a unit. Packs require details (i.e. component items and qtys, etc.) that other items do not.
Item Level	Always	Number indicating which of the three levels the item resides. The item level determines if the item stands alone or if it is part of a family of related items. The item level also determines how the item may be used throughout the system.
Transaction Level	Always	Number indicating which of the three levels transactions occur for the item's group. The transaction level is the level at which the item's inventory is tracked in the system. The transaction level item will be counted, transferred, shipped, etc. The transaction level may be at the current item or up to 2 levels above or below the current item. Only one level of the hierarchy of an item family may contain transaction level items.
Diff 1	Optional	Diff_group or diff_id that differentiates the current item from its item_parent. For an item that is a parent, this field may be either a group (i.e. Men's pant sizes) or a value (6 oz). For an item that is not a parent, this field may contain a value (34X34, Red, etc.). Valid values are found on the diff_group and diff_id tables.
Diff 1 Type	Optional	This field will hold a value of all possible differential types. (for example, 'S' - size, 'C' - color, 'F' - flavor, 'E' - scent, 'P' - pattern). Valid values are stored on the code_detail table with the code_type 'DIFF' and on the diff_type table.
Diff 2	Optional	Diff_group or diff_id that differentiates the current item from its item_parent. For an item that is a parent, this field may be either a group (i.e. Men's pant sizes) or a value (6 oz). For an item that is not a parent, this field may contain a value (34X34, Red, etc.). Valid values are found on the diff_group and diff_id tables.
Diff 2 Type	Optional	This field will hold a value of all possible differential types. (for example, 'S' - size, 'C' - color, 'F' - flavor, 'E' - scent, 'P' - pattern). Valid values are stored on the code_detail table with the code_type 'DIFF' and on the diff_type table.
Diff 3	Optional	Diff_group or diff_id that differentiates the current item from its item_parent. For an item that is a parent, this field may be either a group (i.e. Men's pant sizes) or a value (6 oz). For an item that is not a parent, this field may contain a value (34X34, Red, etc.). Valid values are found on the diff_group and diff_id tables.
Diff 3 Type	Optional	This field will hold a value of all possible differential types. (for example, 'S' - size, 'C' - color, 'F' - flavor, 'E' - scent, 'P' - pattern). Valid values are stored on the code_detail table with the code_type 'DIFF' and on the diff_type table.
Diff 4	Optional	Diff_group or diff_id that differentiates the current item from its item_parent. For an item that is a parent, this field may be either a group (i.e. Men's pant sizes) or a value (6 oz). For an item that is not a parent, this field may contain a value (34X34, Red, etc.). Valid values are found on the diff_group and diff_id tables.
Diff 4 Type	Optional	This field will hold a value of all possible differential types. (for example, 'S' - size, 'C' - color, 'F' - flavor, 'E' - scent, 'P' - pattern). Valid values are stored on the code_detail table with the code_type 'DIFF' and on the diff_type table.
Department	Always	Number identifying the department to which the item is attached. The item's department will be the same as that of its parent (and, by transitivity, to that of its grandparent). Valid values for this field are located on the deps table.
Department Name	Always	Contains the name of the department.
Class	Always	Number identifying the class to which the item is attached. The item's class will be the same as that of its parent (and, by transitivity, to that of its grandparent). Valid values for this field are located on the class table.
Class Name	Always	Contains the name of the class.
Subclass	Always	Number identifying the subclass to which the item is attached. The item's subclass will be the same as that of its parent (and, by transitivity, to that of its grandparent). Valid values for this field are located on the subclass table.
Subclass Name	Always	Contains the name of the subclass.
Status	Always	Status of the item. Valid values are: W = Worksheet: item setup in progress, cannot be used in system; S = Submitted: item setup complete and awaiting approval, cannot be use in system; A = Approved: item is approved and can now be used throughout the system. An item is not published until it is Approved.
Item Description	Always	Long description of the item. This description is used throughout the system to help online users identify the item. For items that have parents, this description will default to the parent’s description plus any differentiators. For items without parents this description will default to null.
Short Description	Optional	Shortened description of the item. This description is the default for downloading to the POS. For items that have parents, this description will default to the parent's short description. For items without parents, this description will default to null.
Description Up	Optional	All uppercase description of the item (same as upper(item_desc)). This field is not displayed in the on-line item maintenance screen, but is used in item list of value search processing throughout the system.
Primary Reference Item Indicator	Optional	Indicates if the sub-transaction level item is designated as the primary sub-transaction level item. For transaction level items and above the value in this field will be 'N'o.
Cost Zone Group Id	Optional	Cost zone group associated with the item. This field is only required when elc_ind (landed cost indicator) is set to 'Y' on the system_options table.
Standard Unit of Measure	Always	Unit of measure in which stock of the item is tracked at a corporate level.
Unit of Measure Conv Factor	Optional	Conversion factor between an "Each" and the standard_uom when the standard_uom is not in the quantity class (e.g. if standard_uom = lb and 1 lb = 10 eaches, this factor will be 10). This factor will be used to convert sales and stock data when an item is retailed in eaches but does not have eaches as its standard unit of measure.
Package Size	Optional	Holds the size of the product printed on any packaging (for example, 24 ounces).
Package Unit of Measure	Optional	Holds the unit of measure associated with the package size.
Merchandise Indicator	Always	Indicates if the item is a merchandise item (Y, N).
Store Order Multiple	Always	Merchandise shipped from the warehouses to the stores must be specified in this unit type. Valid values are:C = Cases; I = Inner; E = Eaches.
Forecast Indicator	Always	Indicates if this item will be interfaced to an external forecasting system (Y, N).
Mfg Rec Retail	Optional	Manufacturer's recommended retail price for the item. Used for informational purposes only. This field is stored in the primary currency.
Retail Label Type	Optional	This field indicates any special label type associated with an item (i.e. pre-priced or cents off). This field is used for reporting purposes only. Values for this field are defined by the 'RTLT' code on code detail.
Retail Label Type Description	Optional	Contains the retail label type description corresponding to the retail_label_type value in item_master.
Retail Label Value	Optional	This field represents the value associated with the retail label type.
Handling Temp	Optional	Holds the temperature information associated with the item. Valid values for this field are in the code type 'HTMP' on the code_head and code_detail tables.
Handling Temp Description	Optional	Contains the Handling temperature description corresponding to the handling_temp value in item_master.
Handling Sensitivity Description	Optional	Contains the Handling sensitivity description corresponding to the handling_sensitivity value in item_master.
Catch Weight Indicator	Always	Indicates whether the item should be weighed when it arrives at a location. Valid values for this field are 'Y' and 'N'.
Waste Type	Optional	Identifies the wastage type as either sales or spoilage wastage. Sales wastage occurs during processes that make an item saleable (i.e. fat is trimmed off at customer request). Spoilage wastage occurs during the products shelf life (for example, evaporation causes the product to weigh less after a period of time). Valid values are: SP - spoilage; and SL - Sales.
Waste Type Description	Optional	Contains the Waste Type description corresponding to the Waste_Type value in item_master.
Waste Percent	Optional	Average percent of wastage for the item over its shelf life. Used in inflating the retail price for wastage items.
Default Waste Percent	Optional	Default daily wastage percent for spoilage type wastage items. This value will default to all item locations and represents the average amount of wastage that occurs on a daily basis.
Const Dimension Indicator	Optional	Indicates that the dimensions of the product are always the same, regardless of the supplier. If this field is Y, the dimensions for all suppliers will be defaulted to the primary supplier/primary country dimensions. Editing of dimensions for the item will only be allowed for the primary supplier/primary country.
Simple Pack Indicator	Always	Indicates if pack item is a simple pack or not. This field will only be available if the item is a pack item. A simple pack is an item whose components are all the same item (i.e. a six pack of cola, etc). Valid values are:Y = Yes, this item is a simple pack; N = No if this is not a simple pack.
Contains Inner Indicator	Always	Indicates if pack item contains inner packs. Vendor packs will never contain inner packs and this field will be defaulted to 'N'. This field will only be available if the item is a pack item. Valid values are:Y = Yes, this pack contains inner packs; N = No, this pack does not contain inner packs.
Sellable Indicator	Always	Indicates if pack item may be sold as a unit. If it is 'Y' then the pack will have its own unique unit retail. If it is 'N' then the pack's unit retail is the sum of each individual item's total retail within the pack. This field will only be available if the item is a pack item. Value values are: Y = Yes, this pack may be sold as a unit; N = No, this pack may not be sold as a unit.
Orderable Indicator	Always	Indicates if pack item is orderable. If it is 'Y' then the suppliers of the pack must supply all components in the pack. If it is 'N' then the components may have different suppliers. This field will only be available if the item is a pack item. Valid values are: Y = Yes, this pack may be ordered, N = No, this pack may not be ordered.
Pack Type	Optional	Indicates if pack item is a vendor pack or a buyer pack. A vendor pack is a pack that the vendor or supplier recognizes and sells to the retailer. If the pack item is a vendor pack, communication with the supplier will use the vendor pack number. A buyer pack is a pack that a buyer created for internal ease of use. If the pack item is a buyer pack communication with the supplier will explode the pack out to its component items. This field will only be used if the item is a pack item. If the pack item is not orderable this field must be null. Valid values are: V = Vendor; B = Buyer.
Order As Type	Optional	Indicates if pack item is receivable at the component level or at the pack level (for a buyer pack only). This field is required if pack item is an orderable buyer pack. This field must be NULL if the pack is sellable only or a vendor pack. This field will only be available if the item is a pack item. Valid Values are: E = Eaches (component level) P = Pack (buyer pack only).
Comments	Optional	Holds any comments associated with the item.
Unit Retail	Optional	This field holds the unit retail in the standard unit of measure for the item/zone combination. This field is stored in the local currency.
Item Service Level	Optional	Holds a value that restricts the type of shipment methods that RCOM can select for an item.
Gift Wrap Indicator	Always	This field will contain a value of 'Y' if the item is eligible to be gift wrapped.
Ship Alone Indicator	Always	This field will contain a value of 'Y' if the item should be shipped to the customer is a seperate package versus being grouped together in a box.
Vendor Style	No	Not used by RMS.
Standard Unit Weight	No	Not used by RMS.
Single Price Flag	No	Not used by RMS.
Pre Ticket Flag	No	Not used by RMS.
Planned Residual	No	Not used by RMS.
Sortable	No	Not used by RMS.
Item Master UDA1	No	Not used by RMS.
Item Master UDA2	No	Not used by RMS.
Item Master UDA3	No	Not used by RMS.
Item Master UDA4	No	Not used by RMS.
Item Master UDA5	No	Not used by RMS.
Item Master UDA6	No	Not used by RMS.
Item Master UDA7	No	Not used by RMS.
Item Master UDA8	No	Not used by RMS.
Item Master UDA9	No	Not used by RMS.
Item Master UDA10	No	Not used by RMS.
Item Master UDA11	No	Not used by RMS.
Item Master UDA12	No	Not used by RMS.
Item Master UDA13	No	Not used by RMS.
Item Master UDA14	No	Not used by RMS.
Item Master UDA15	No	Not used by RMS.
Ship Alone	No	Not used by RMS.
Slottable	No	Not used by RMS.
Freight Class	No	Not used by RMS.
New Item	No	Not used by RMS.
Brand	No	Not used by RMS.
Break Case Ups	No	Not used by RMS.
Rigid	No	Not used by RMS.
Fragile	No	Not used by RMS.
Container Type	No	Not used by RMS.
Conveyable Flag	No	Not used by RMS.
Hazard Matl Code	No	Not used by RMS.
Velocity	No	Not used by RMS.
High Value Indicator	No	Not used by RMS.
Ticket Type	No	Not used by RMS.
Color	No	Not used by RMS.
Size1	No	Not used by RMS.
Fit	No	Not used by RMS.
Shade	No	Not used by RMS.
Single Contain Bulk	No	Not used by RMS.
Unit Pick System Code	No	Not used by RMS.
Roundable Flag	No	Not used by RMS.
Perishable Indicator	No	Not used by RMS.
Division	No	Not used by RMS.
Vendor Number	No	Not used by RMS.
Knitting WIP Code	Optional	WIP code used to designate the Kitting process used in item_master
Unit Ticket Quantity	No	Not used by RMS.
Item Length	No	Not used by RMS.
Item Width	No	Not used by RMS.
Item Height	No	Not used by RMS.
Item Weight	No	Not used by RMS.
Item Cube	No	Not used by RMS.
Standard Unit Quantity	No	Not used by RMS.
Expiration Days	No	Not used by RMS.
Putaway By Volume	No	Not used by RMS.
Putaway Plan	No	Not used by RMS.
Item Type	No	Not used by RMS.
Sorter Group	No	Not used by RMS.
SKU Optimization	No	Not used by RMS.
Ext Source System	No	Not used by RMS.
Item Xform Indicator	Optional	This indicator will show that an item is associated with an item transformation. The item will be either the sellable item or orderable item in the transformation process.
Order Type	Optional	This determines how catch weight items are ordered. Valid values are: F - fixed weight V - Variable weight Valid values are held on the CODE_DETAIL table with a code type = ORDT
Sale Type	Optional	This indicates the method of how catch weight items are sold in store locations. Valid values are: V - variable weight each L - Loose weight Valid values are held on the CODE_DETAIL table with a code type = STPE
Deposit Item Type	Optional	This is the deposit item component type. A NULL value in this field indicates that this item is not part of a deposit item relationship. The possible values are - E - Contents A - Container Z - Crate T - Returned Item (Empty bottle) P - Complex pack (with deposit items) The Returned item is flagged only to enable these items to be mapped to a separate GL account if required.
Container Item	Optional	This holds the container item number for a contents item. This field is only populated and required if the DEPOSIT_ITEM_TYPE = E.
Deposit in Price Per UOM	Optional	This field indicates if the deposit amount is included in the price per UOM calculation for a contents item ticket. This value is only required if the DEPOSIT_ITEM_TYPE = E. Valid values are: I - Include deposit amount E - Exclude deposit amount
Transport Indicator	No	Not used by RMS.
Notional Pack Indicator	Always	Indicates to the Store Inventory System if a sellable simple pack needs to be broken down to its component item.
SOH Inquiry at Pack Indicator	Always	Indicates to the Store Inventory System if a notional simple pack item's inventory should be displayed in packs.
Purchase Type	Optional	Indicates if an item is a normal, consignment or concession item - 'N' for normal, 'S' for consignment, 'C' for concession.
Product Classification	Optional	This contains item combinability codes (with code type 'PCLA') which provide a way to define which items can be combined (packed or boxed) together.
Item Aggregate Indicator	Always	Indicator to aggregate inventory and sales for the item. Currently, this indiactor is used by allocation and MFP to aggregate inventory for items. For staple items this indictor should be N.
Diff 1 Aggregate Indicator	Always	Indicator for the corresponding diff. Indicator to aggregate inventory and sales for an item at Parent/Diff level (for example, Style/Color or Style/Size ).
Diff 2 Aggregate Indicator	Always	Indicator for the corresponding diff. Indicator to aggregate inventory and sales for an item at Parent/Diff level (for example, Style/Color or Style/Size ).
Diff 3 Aggregate Indicator	Always	Indicator for the corresponding diff. Indicator to aggregate inventory and sales for an item at Parent/Diff level (for example, Style/Color or Style/Size ).
Diff 4 Aggregate Indicator	Always	Indicator for the corresponding diff. Indicator to aggregate inventory and sales for an item at Parent/Diff level (for example, Style/Color or Style/Size ).
Item Description Secondary	Optional	Secondary descriptions of the item. This field can only be populated when system_options.secondary_desc_ind = Y.
Original Retail	Optional	The field indicated the original retail price of the item per unit. This field is stored in the primary currency.
Original Retail Currency Code	Optional	The field indicates the currency code of the original retail price.
Mfg Retail Currency Code	Optional	The field indicates the currency code of the Manufacturer's retail price.
Catch Weight Type	Optional	This column will hold catch weight type for a simple pack catch weight item. The value is based on the component items order_type and sale_type: 2 - order_type = Variable Weight, sale_type = Loose Weight 4 - order_type = Variable Weight, sale_type = Variable Weight Each The column will be set only at the time of Item approval.
Catch Weight UOM	Optional	Indicates the UOM for Catchweight Items.
Brand Name	Optional	This field contains the brand associated to an item.
Unique Class	Optional	This field uniquely identify the class which the item belongs to. Class is not unique ID the merchandise hierarchy. The combination of Dept/Class is unique, but requires use of a composite key. The composite key is generally used in user interfaces. The unique ID can be used in back end processing or in systems that cannot have a composite key in the merchandise hierarchy.
Unique Subclass	Optional	This field uniquely identify the subclass which the item belongs to. Sublass is not unique ID the merchandise hierarchy. The combination of Dept/Class/Subclass is unique, but requires use of a composite key. The composite key is generally used in user interfaces. The unique ID can be used in back end processing or in systems that cannot have a composite key in the merchandise hierarchy.
Diff 1 Level	Optional	This field will contain either ID or GROUP, based on whether the diff_1 is a group diff or a diff id.
Diff 1 Description	Optional	This field will hold the description of differentiator.
Diff 2 Level	Optional	This field will contain either ID or GROUP, based on whether the diff_1 is a group diff or a diff id.
Diff 2 Description	Optional	This field will hold the description of differentiator.
Diff 3 Level	Optional	This field will contain either ID or GROUP, based on whether the diff_1 is a group diff or a diff id.
Diff 3 Description	Optional	This field will hold the description of differentiator.
Diff 4 Level	Optional	This field will contain either ID or GROUP, based on whether the diff_1 is a group diff or a diff id.
Diff 4 Description	Optional	This field will hold the description of differentiator.
Prod Classification Description	Optional	This field will hold the description of the product classification.
Primary Image URL	Optional	This field will hold the primary item image url. It is only included for the ItemCre and ItemFulRep message types. Updates to the primary image for an item are published in the Item Image node.

Table 2-29 Item Supplier

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Supplier	Always	Unique identifier for the supplier.
Primary Supplier Indicator	Always	This field indicates whether this supplier is the primary supplier for the item. An item can have one and only one primary supplier.
VPN	Optional	This field contains the Vendor Product Number for the Item/Supplier.
Supp Label	Optional	This field contains the supplier label. It will only be populated if item is a parent item.
Consignment Rate	Optional	This field contains the consignment rate for this item for the supplier.
Supplier Diff 1	Optional	This field contains the first supplier differentiator and/or description. This field may only contain a value for items with an item_parent.
Supplier Diff 2	Optional	This field contains the second supplier differentiator and/or description. This field may only contain a value for items with an item_parent.
Supplier Diff 3	Optional	This field contains the second supplier differentiator and/or description. This field may only contain a value for items with an item_parent.
Supplier Diff 4	Optional	This field contains the second supplier differentiator and/or description. This field may only contain a value for items with an item_parent.
Pallet Name	Always	Code referencing the name used to refer to the pallet.
Case Name	Always	Code referencing the name used to refer to the case.
Inner Name	Always	Code referencing the name used to refer to the inner.
Supplier Discontinue Date	Optional	Date when the supplier discontinues an item.
Direct Ship Indicator	Always	A value of 'Y'es in this field indicates that any item associated with this supplier is eligible for a direct shipment from the supplier to the customer.
Primary Case Size	Optional	This field indicates the primary case size for the item supplier when an orderable item is configured for informal case types.

Table 2-30 Item Supplier Country

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Supplier	Always	Unique identifier for the supplier.
Origin Country Id	Always	The country where the item was manufactured or significantly altered.
Primary Supplier Indicator	Always	This field indicates whether this supplier is the primary supplier for the item. Each item can have one and only one primary supplier.
Primary Country Indicator	Always	This field indicates whether this country is the primary country for the item/supplier. Each item/supplier combination can have one and only one primary country.
Unit Cost	Always	This field contains the current corporate unit cost for the item from the supplier /origin country. This field is stored in the Supplier's currency.
Lead Time	Optional	This field contains the number of days that will elapse between the date an order is written and the delivery to the store or warehouse from the supplier.
Pick up Lead Time	Optional	Contains the time that will be taken to get the item from the supplier to the initial receiving location.
Supplier Pack Size	Always	This field contains the quantity that orders must be placed in multiples of for the supplier for the item.
Inner Pack Size	Always	This field contains the break pack size for this item from the supplier.
Round Level	Always	This column is used to determine how order quantities will be rounded to case, layer and pallet.
Minimum Order Quantity	Optional	This field contains the minimum quantity that can be ordered at once from the supplier for the item.
Max Order Quantity	Optional	This field contains the maximum quantity that can be ordered at once from the supplier for the item.
Packing Method	Optional	This field indicates whether the packing method for the item in the container is flat or hanging.
Default Unit of Purchase	Optional	Contains the default unit of purchase for the item/supplier/country. Valid values include 'C'ase and 'P'allet.
Ti	Always	Number of shipping units (cases) that make up one tier of the pallet. Multiply TI x HI to get total number of units (cases) in a Pallet.
Hi	Always	Number of tiers that make up a complete pallet. Multiply TI x HI to get total number of units (cases) in a Pallet.
Cost Unit of Measure	Always	A cost UOM is held to allow costs to be managed in a separate UOM to the standard UOM.
Tolerance Type	Optional	The unit of tolerance for catch weight simple packs. Valid values are: 'A' - actual 'P' - percent.
Max Tolerance	Optional	The maximum tolerance value for the catch weight simple pack.
Min Tolerance	Optional	The minimum tolerance value for a catch weight simple pack.
Round to Inner Pct	Always	This column will hold the Inner Rounding Threshold value. During rounding, this value is used to determine whether to round partial Inner quantities up or down. If the Inner-fraction in question is less than the Threshold proportion, it is rounded down; if not, it is rounded up.
Round to Case Pct	Always	This column will hold the Case Rounding Threshold value. During rounding, this value is used to determine whether to round partial Case quantities up or down. If the Case-fraction in question is less than the Threshold proportion, it is rounded down; if not, it is rounded up.
Round to Layer Pct	Always	This column will hold the Layer Rounding Threshold value. During rounding, this value is used to determine whether to round partial Layer quantities up or down. If the Layer-fraction in question is less than the Threshold proportion, it is rounded down; if not, it is rounded up.
Round to Pallet Pct	Always	This column will hold the Pallet Rounding Threshold value. During rounding, this value is used to determine whether to round partial Pallet quantities up or down. If the Pallet -fraction in question is less than the Threshold proportion, it is rounded down; if not, it is rounded up.
Supplier Hierarchy Type 1	Optional	This field identifies partner type of supplier hierarchy level 1. This field will always have the partner type S1 which indicates manufacturer.
Supplier Hier Level 1	Optional	Highest level of supplier hierarchy which is there to indicate a partner, such as a manufacturer, in the supply chain that gives rebates to the retailer.
Supplier Hierarchy Type 2	Optional	This field identifies partner type of supplier hierarchy level 2. This field will always have the partner type S2 which indicates distributor.
Supplier Hier Level 2	Optional	Second highest level of supplier hierarchy which is there to indicate a partner, such as a distributor, in the supply chain that gives rebates to the retailer.
Supplier Hierarchy Type 3	Optional	This field identifies partner type of supplier hierarchy level 3. This field will always have the partner type S3 which indicates wholesaler.
Supplier Hier Level 3	Optional	Third highest level of supplier hierarchy which is there to indicate a partner, such as a wholesaler, in the supply chain that gives rebates to the retailer.
Negotiated Item Cost	Optional	This will hold the supplier negotiated item cost for the primary delivery country of the item. Once a location is associated with the item, the primary locations negotiated item cost will be stored in this field.
Extended Base Cost	Optional	This will hold the extended base cost for the primary delivery country of the item. Once a location is associated with the item, the primary locations extended base cost will be stored in this field. Extended base cost is the cost inclusive of all the taxes that affect the WAC. In case of GTAX , Extended Base Cost = Base Cost + Non-recoverable taxes. In case of VAT, Extended Base Cost = Base Cost.
Inclusive Cost	Optional	This will hold the inclusive cost for the primary delivery country of the item. Once a location is associated with the item, the primary locations inclusive cost will be stored in this field. This cost will have both the recoverable and non recoverable taxes included. In case of GTAX , Inclusive Cost = Base Cost + Non-recoverable taxes + Recoverable Taxes. In case of VAT, Inclusive Cost = Base Cost + VAT.
Base Cost	Optional	This field will hold the tax exclusive cost of the item.
Purchase Type	Optional	This contains a code to indicate whether the item is normal merchandise (i.e. owned by the retailer), consignment stock or a concession item. Valid values are 0 - Owned, 1 - Consignment, 2 - Concession.
Calculation Basis	Optional	This indicates if the cost for the consignment/concession item will be managed either based on cost per unit or as a percentage of retail. Valid values are C - Cost per Unit, P - Purchase Rate.
Purchase Rate	Optional	This column contains the percentage of the retail price which will determnie the cost paid to the supplier for a consignment or concession item.

Table 2-31 Item Supplier Country Dimension

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Supplier	Always	Unique identifier for the supplier.
Origin Country Id	Always	The country where the item was manufactured or significantly altered.
DIM Object	Always	Specific object whose dimensions are specified in this record (e.g. case, pallet, each). Valid values for this field are in the code type DIMO on the codes tables.
Presentation Method	Optional	Describes the packaging (if any) being taken into consideration in the specified dimensions. Valid values for this field are in the code type PCKT on the codes tables.
Method Description	Optional	The description for the Presentation Method.
Length	Optional	Length of dim_object measured in units specified in terms of the LWH Unit of Measure.
Width	Optional	Width of dim_object measured in units specified in terms of the LWH Unit of Measure.
Height	Optional	Height of dim_object measured in units specified in terms of the LWH Unit of Measure.
LWH Unit of Measure	Optional	Unit of measurement for length, width, and height.
Weight	Optional	Weight of dim_object measured in units specified in terms of the Weight Unit of Measure.
Net Weight	Optional	Net weight of the dim_object (weight without packaging) measured in units specified in terms of the Weight Unit of Measure.
Weight Unit of Measure	Optional	Unit of measurement for Weight and Net Weight.
Liquid Volume Unit of Measure	Optional	Unit of measurement for liquid volume. Liquid volumes are only convertible to other liquid volumes.
Stat Cube	Optional	Statistical value of the dim_object's dimensions to be used for loading purposes.
Tare Weight	Optional	Amount of weight to be subtracted for packaging materials. Used to calculate the true net weight of the dim_object.
Tare Type	Optional	Indicates whether the tare weight for the dim_object is wet or dry.

Table 2-32 Item User Defined Attribute List of Value

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
UDA ID	Always	This field contains a number identifying the user-defined attribute.
UDA Value	Always	Contains the value for the user-defined attribute.
UDA Description	Always	This field contains a description of the User-Defined Attribute.
UDA Value Description	Always	This field contains a description of the UDA value.

Table 2-33 Item User Defined Attribute FF

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
UDA Id	Always	This field contains a number identifying the user-defined attribute.
UDA Text	Always	This field contains the text value of the user-defined attribute for the item.

Table 2-34 Item User Defined Date

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
UDA Id	Always	This field contains a number identifying the user-defined attribute.
UDA Date	Always	This field contains the effective date for the UDA ID.

Table 2-35 Item Image

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Image Name	Always	Name of the image.
Image Addr	Always	Path where the image file is stored.
Image Type	Always	This field contains the type of the image of the item. Valid values are defined as member of IITD code type.
Primary Indicator	Always	Field to specify the display sequence order of images associated to the item per priority.
Image Description	Always	This field contains the description associated with the image of the item.

Table 2-36 Item UPC

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Item Parent	Always	Unique identifier for the item/group at the level above the item.
Primary Ref Item Indicator	Always	Indicates if the sub-transaction level item is designated as the primary sub-transaction level item. For transaction level items and above, the value in this field will be 'N'o.
Format ID	Optional	This field will hold the format id that corresponds to the item's variable UPC. This value is only used for items with variable UPCs.
Prefix	Optional	This field holds the prefix for variable weight UPCs. The prefix determines the format of the eventual UPC and will be used to decode variable weight UPCs that are uploaded from the POS.
Item Number Type	Always	Code specifying what type the item is. Valid values for this field are in the code type UPCT on the code_head and code_detail tables.

Table 2-37 Item BOM

Message Element	Included?	Notes
Pack No	No	Unique identifier for the pack item.
Item	No	Unique identifier for the component item.
Pack Quantity	No	Contains the quantity of each item in the pack item.

Table 2-38 Item Ticket Header

Message Element	Included?	Notes
Item	Always	Unique identifier for the pack item.
Ticket Type Id	Always	This field uniquely identifies the ticket type which is associated with the item.
Ticket Type Description	Always	This field contains a description of the ticket or label type.
Shelf Edge Label Indicator	Always	Indicates if the ticket type is a shelf edge label.
PO Print Type	Optional	This field indicates when the ticket type for the given item should be printed by default, upon the approval or receipt of the purchase order. Valid values are: A and R.
Print on PC Indicator	Always	This field indicates whether this type of ticket should be printed for this item when a permanent price change goes into effect. Valid values in this field are:Y - Yes N - No.
Ticket Over Pct	Optional	This field contains a percent which indicates the number of tickets which should be printed for a given event. If the event causing tickets to be printed is approving or receiving a purchase order, then this field indicates the quantity of tickets which should be printed greater than the quantity of the purchase order. If the event causing the tickets to be printed is a permanent price change, this field would indicate the quantity of tickets which should be printed greater than the stock on hand.

Table 2-39 Item Ticket Detail

Message Element	Included?	Notes
Ticket Item Id	Optional	This field contains a character string which uniquely identifies an attribute which will appear on a ticket or label such as retail price or price per unit of measure.
Ticket Item Id Description	Optional	This field contains the description of the ticket_item_id.
UDA Id	Optional	This field contains a number which uniquely defines a user-defined attribute which is to be printed on this ticket type.

Table 2-40 Item Supplier Country Manufacturer

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Supplier	Always	Unique identifier for the supplier.
Manufacturer Country Id	Always	Country ID of manufacturer.
Primary Manufacturer Country Indicator	Always	Primary country of manufacturer indicator.

Table 2-41 Related Item Header

Message Element	Included?	Notes
Item	Always	The main item against which the relationship is defined.
Relationship Id	Always	A system generated numeric field that uniquely identifies the relationship record in related_item_head table.
Relationship Name	Always	This is the user entered name of the relationship.
Relationship Type	Always	Describes the type of relationship. Valid values are in the codes table under code_type 'IREL'.
Mandatory Indicator	Always	Indicates whether the relationship is mandatory or not. Valid values: 'Y' (yes), 'N' (no).

Table 2-42 Related Item Detail

Message Element	Included?	Notes
Related Item	Always	Item for which related items are defined. Valid transaction level/sellable item. Multiple items can be defined for a relationship.
Priority	Optional	Relationship priority. Applicable only for relationship type Substitute.
Effective Date	Optional	The related item can be used on transactions starting this date.
End Date	Optional	The related item can be used on transactions until this date. A value of null means that it is always effective.

Table 2-43 Vat Item

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Active Date	Always	This field holds the active date for record on future cost.
Vat Type	Always	This field indicates the type of vat either Retail or Cost or Both.
Vat Code	Always	This field indicates a code to uniquely identify a VAT rate.
Vat Rate	Always	This column contains vat rate associated with a given VAT code.
Reverse Vat Indicator	Always	This field indicates if the item is subject to reverse charge VAT at the vat region. Valid values are Y and N.
Vat Region	Always	Contains the number of the Value Added Tax region a location belongs to.

Table 2-44 Item Supplier Unit of Measure

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Supplier	Always	Unique identifier for the supplier.
UOM	Always	The unit of measure that the item is being measured in.
Value	Always	This field will store the equivalent value of the Item/Suppliers shipping carton in the associated unit of measure.

Modify and Delete Messages

Updates and deletions of item data can be included in a larger item creation message. If not part of a larger hierarchical message, they are published individually as a flat, non-hierarchical message. Update and delete messages are much smaller than the large hierarchy in a newly created item message (ItemDesc).

Modify Messages

If an existing item is modified, this will trigger an update message to provide details of the update through the RIB. The message will include any updated levels in the item hierarchy.

Delete Messages

Delete messages are published in the same way as modify messages. For example, if an item-supplier-country relationship is deleted from Merchandising, the dependent item-supplier-country dimension records are also deleted.

Table 2-45 Item

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.

Table 2-46 Item Supplier

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Supplier	Always	Unique identifier for the supplier.

Table 2-47 Item Supplier Country

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Supplier	Always	Unique identifier for the supplier.
Country Id	Always	The country where the item was manufactured or significantly altered.

Table 2-48 Item Supplier Country Dimension

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Supplier	Always	Unique identifier for the supplier.
Country Id	Always	The country where the item was manufactured or significantly altered.
DIM Object	Always	Dim_object description fetched from the code_detail table.

Table 2-49 Item UPC

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.

Table 2-50 Item User Defined Attribute FF

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
UDA Id	Always	This field contains a number identifying the user-defined attribute.
UDA Text	Always	This field contains the text value of the user-defined attribute for the item.

Table 2-51 Item User Defined Attribute Date

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
UDA Id	Always	This field contains a number identifying the user-defined attribute.
UDA Date	Always	This field contains the effective date for the Uda id.

Table 2-52 Item User Defined Attribute List of Value

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
UDA Id	Always	This field contains the unique identified of the Attribute Group being added or delete to the item list.
UDA Value	Always	Contains the value of the Attribute Group.

Table 2-53 Item Image

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Image Name	Always	Name of the image.

Table 2-54 Item Ticket

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Ticket Type Id	Always	This field uniquely identifies the ticket type which is associated with the item.

Table 2-55 Item Supplier Country Mfr

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Supplier	Always	This field uniquely identifies the ticket type which is associated with the item.
Country Id	Always	The country where the item was manufactured or significantly altered.

Table 2-56 Related Item

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Relationship Id	Always	A system generated numeric field that uniquely identifies the relationship record in related_item_head.
Related Item	Always	Item for which related items are defined. Valid transaction level/sellable item. Multiple items can be defined for a relationship.

Table 2-57 VAT Item

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Active Date	Always	This field holds the active date for record on future cost.
Vat Type	Always	This field indicates the type of vat either Retail or Cost or Both.
Vat Region	Always	Contains the number of the Value Added Tax region a location belongs to.

Table 2-58 Item Supplier Unit of Measure

Message Element	Included?	Notes
Item	Always	Unique identifier for the item.
Supplier	Always	Unique identifier for the supplier.
UOM	Always	The unit of measure that the item is being measured in.

Design Overview

The item message family manager is a package of procedures that adds item family messages to the item queue and publishes these messages for the integration bus to route. Triggers on all the item family tables call a procedure from this package to add a "create", "modify" or "delete" message to the queue. The integration bus calls a procedure in this package to retrieve the next publishable item message from the queue.

All the components that comprise the creation of an item, the item/supplier for example, remain in the queue until the item approval modification message has been published. Any modifications or deletions that occur between item creation in "W"(worksheet) status and "A"(Approved) status are applied to the "create" messages or deleted from the queue as required. For example, if an item UDA is added before item approval and then later deleted before item approval, the item UDA "create" message would be deleted from the queue before publishing the item. If an item/supplier record is updated for a new item before the item is approved, the "create" message for that item/supplier is updated with the new data before the item is published. When the "modify" message that contains the "A" (Approved) status is the next record on the queue, the procedure formats a hierarchical message that contains the item header information and all the child detail records to pass to the integration bus.

Additions, modifications, and deletions to item family records for existing approved items are published in the order that they are placed on the queue.

Unless otherwise noted, item publishing includes most of the columns from the item_master table and the entire item family child tables included in the publishing message. Sometimes only certain columns are published, and sometimes additional data is published with the column data from the table row. The item publishing message is built from the following tables:

Family Header
item_master  -  transaction level items only
descriptions for the code values
names for department, class and subclass
diff types
base retail price
Item Family Child Tables
item_supplier
item_supp_country
item_supp_country_dim
    descriptions for the code values
item_master  -  reference items
    item, item_number_type, item_parent, primary_ref_ind, format_id, prefix
packitem_breakout
    pack_no, item, packitem_qty
item_image
item_ticket
uda_item_ff
uda_item_lov
uda_item_date
related_item_head
related_item_detail

Business Object Records

Create the following business objects to assist the publishing process:

1. Create a type for a table of rowids.

   TYPE ROWID_TBL is TABLE OF ROWID;
   

2. Create a record of ROWID_TBL types for keeping track of rowids to update and delete. There should be a ROWID_TBL for ITEM_MFQUEUE deletion, ITEM_MFQUEUE updating, ITEM_PUB_INFO deletion, and ITEMLOC_MFQUEUE deletion.

   TYPE ITEM_ROWID_REC is RECORD
         (queue_rowid_tbl       ROWID_TBL,
          pub_info_rowid_tbl    ROWID_TBL,
          queue_upd_rowid_tbl   ROWID_TBL,
          itemloc_rowid_tbl     ROWID_TBL
         );
   

3. Create a record to assist in publishing the ItemBOM node. This record type was originally in ITEMBOM_XML, but since ITEMBOM_XML is being removed, it is being moved to RMSMFM_ITEMS.

   TYPE bom_rectype IS RECORD
      (pack_no                  VARCHAR2(25),
       seq_no                    NUMBER(4),
       item                      VARCHAR2(25),
       item_parent               VARCHAR2(25),
       pack_tmpl_id              NUMBER(8),
       comp_pack_no              VARCHAR2(25),
       item_qty                  NUMBER(12,4),
       item_parent_pt_qty        NUMBER(12,4),
       comp_pack_qty             NUMBER(12,4),
       pack_item_qty             NUMBER(12,4));
    
   TYPE bom_tabtype is TABLE of bom_rectype
       INDEX BY BINARY_INTEGER;

Message XSD

Here are the filenames that correspond with each message type. Please consult RIB documentation for each message type to get a detailed picture of the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
itemcre	Item Create Message	ItemDesc.xsd
itemmod	Item Modify Message	ItemDesc.xsd
itemdel	Item Delete Message	ItemRef.xsd
itemsupcre	Item Supplier Create Message	ItemSupDesc.xsd
itemsupmod	Item Supplier Modify Message	ItemSupDesc.xsd
itemsupdel	Item Supplier Delete Message	ItemSupRef.xsd
itemsupctycre	Item Supplier Country Create Message	ItemSupCtyDesc.xsd
itemsupctymod	Item Supplier Country Modify Message	ItemSupCtyDesc.xsd
itemsupctydel	Item Supplier Country Delete Message	ItemSupCtyRef.xsd
iscdimcre	Item Supplier Country Dimension Create Message	ISCDimDesc.xsd
iscdimmod	Item Supplier Country Dimension Modify Message	ISCDimDesc.xsd
iscdimdel	Item Supplier Country Dimension Delete Message	ISCDimRef.xsd
itemupccre	Item UPC Create Message	ItemUPCDesc.xsd
itemupcmod	Item UPC Modify Message	ItemUPCDesc.xsd
itemupcdel	Item UPC Delete Message	ItemUPCRef.xsd
itembomcre	Item BOM Create Message	ItemBOMDesc.xsd
itembommod	Item BOM Modify Message	ItemBOMDesc.xsd
itembomdel	Item BOM Delete Message	ItemBOMRef.xsd
itemudaffcre	Item UDA Free Form TextCreate Message	ItemUDAFFDesc.xsd
itemudaffmod	Item UDA Free Form Text Modify Message	ItemUDAFFDesc.xsd
itemudaffdel	Item UDA Free Form Text Delete Message	ItemUDAFFRef.xsd
itemudalovcre	Item UDA LOV Create Message	ItemUDALOVDesc.xsd
itemudalovmod	Item UDA LOV Modify Message	ItemUDALOVDesc.xsd
itemudalovdel	Item UDA LOV Delete Message	ItemUDALOVRef.xsd
itemudadatecre	Item UDA Date Create Message	ItemUDADateDesc.xsd
itemudadatemod	Item UDA Date Modify Message	ItemUDADateDesc.xsd
itemudadatedel	Item UDA Date Delete Message	ItemUDADateRef.xsd
itemimagecre	Item Image Create Message	ItemImageDesc.xsd
itemimagemod	Item Image Modify Message	ItemImageDesc.xsd
itemimagedel	Item Image Delete Message	ItemImageRef.xsd
relitemheadcre	Item Relationship Create Message	RelatedItemDesc.xsd
relitemheadmod	Item Relationship Modify Message	RelatedItemDesc.xsd
relitemheaddel	Item Relationship Delete Message	RelatedItemRef.xsd
relitemdetcre	Related Item Create Message	RelatedItemDesc.xsd
relitemdetmod	Related Item Modify Message	RelatedItemDesc.xsd
relitemdetdel	Related Item Delete Message	RelatedItemRef.xsd

Item Location Publication API

This section describes the item location publication API.

Functional Area

Items

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Merchandising defines and publishes item/location relationships. The details about item/location relation creation, modification, de-activation, and deletion are important for other systems for the smooth functioning of several business processes. Attributes included in this integration are pulled from both the Item Location and Item Location Traits tables in Merchandising.

To support multi-channel environments in Merchandising, a physical warehouse is divided into one or more virtual warehouses to better track and manage goods by channel. Stores and virtual warehouses are the locations that hold inventory and are associated to an item. Therefore, when creating, modifying, or deleting an item/location relationship where the location is a virtual warehouse, the physical warehouse information is also included in the message published. This is to cater to external systems that require the physical warehouse rather than the virtual warehouse.

In general, pricing updates are sent from Pricing to inform dependent solutions of a pending price change or clearance markdown. But, for solutions that do not subscribe to pricing events from Pricing, when the selling price for an item is changed, an item/location update will be published to inform other solutions of the updated price, including an update to the clearance flag.

New Item Location Relationship

The creation of a new item/location relationship triggers the generation of an item/location creation message. Please note though, that item/location records will not be published before the corresponding item record is published. Merchandising ensures that when an item creation message has not been published yet, the corresponding item/location creation message is not picked up for publication. The item/location relationship creation message publishes the item identifier, along with the location-specific attributes.

The message includes the following:

Item/Location Description

Message Element	Included?	Notes
Item	Always	This is the item ID the item/location change is related to.
Item Level	Always	Indicates the level of the item within an item family. Valid values are 1, 2, or 3.
Tran Level	Always	This is the level at which inventory is tracked for this item's family. Valid values are 1, 2, or 3. Items having Item Level > Tran Level (indicating it is a below-transaction-level item) will not have its changes published.

Child Nodes

• Item/Location Physical Location Details

Item/Location Physical Location Details

Message Element	Included?	Notes
Physical Location	Always	This is the Physical WH associated to the virtual warehouse location. If the location is a Store, this will contain the Store ID.
Location Type	Always	This is the type of location in the location field. Valid values are Store (S) or Warehouse (W).
Store Type	Optional	This is populated for store location types only. This will indicate whether a particular store is a franchise (F) or company store (C).
Stockholding Indicator	Optional	This is populated for store location types only. If the store is a non-stockholding store, message will not be subscribed to SIM/SIOCS.
Returnable Indicator	Always	This contains a value of Yes (Y) if the item can be returned to the location and No (N) if not.

Child Nodes

• Item/Location Virtual Location Details (required for item/locations update)

• Item/Location Virtual Location Replenishment Details (required for Replenishment item/location update)

Item/Location Virtual Location Details

Message Element	Included?	Notes
Location	Always	Contains the location ID.
Location Type	Always	This is the type of location in the location field. Valid values are S (store) and W (warehouse).
Local Item Description	Optional	This contains the local description of the item.
Local Item Short Description	Optional	This contains the local short description of the item.
Status	Optional	This contains the status of the item at the location. Valid values are: Active (A) - Item is valid and can be ordered and sold Inactive (I) - Item is valid but cannot be ordered or sold Discontinued (C) - Item is valid and sellable but no longer orderable Delete (D) - Item is invalid and cannot be ordered or sold
Primary Supplier	Optional	This contains the primary supplier site for the item/location.
Primary Country	Optional	This contains the primary country of sourcing for the item/location.
Receive As Type	Optional	This determines whether the stock on hand for a pack component item or the buyer pack itself will be updated when a buyer pack is received at a warehouse. Valid values are Each (E) or Pack (P).
Taxable Indicator	Optional	This field determines if the item is taxable at the store. Valid values are Yes (Y) or No (N).
Source Method	Optional	This field determines the primary sourcing for this item/location. Valid values are Supplier (S) or Warehouse (W).
Source WH	Optional	This determines which warehouse is the sourcing location for this item/location. This value is required if the sourcing method is Warehouse.
Unit Retail	Optional	This defines the unit retail price in the standard unit of measure for the item/location combination. This field will be stored in the local currency.
Selling Unit Retail	Optional	This defines the unit retail price in the selling unit of measure for the item/location combination. This field will be stored in the local currency.
Selling UOM	Optional	This defines the selling unit of measure for an item's single-unit retail at the location.
Store Price Indicator	Optional	This determines if an item at a particular store location can have the unit retail marked down by the store.
Purchase Type	Optional	This defines whether the item is owned, consignment stock, or a concession item at the location. Valid values are: 0 - Owned 1 - Consignment 2 - Concession
UIN Type	Optional	This contains the type of unique identification number (UIN) used to identify the instances of the item at the location. Valid values are found in code type UINT.
UIN Label	Optional	This contains the label for the unique identification number (UIN) when displayed. Valid values are found in code type ULBL.
Capture Time	Optional	This determines when the unique identification number (UIN) should be captured for an item during transaction processing. Valid values are found in code type CPTM.
External UIN Indicator	Always	This indicates if a unique identification number (UIN) is being generated in the external system. Valid values are Yes (Y) or No (N).
Ranged Indicator	Optional	This determines if the location is ranged intentionally by a user for replenishment/selling or incidentally ranged by other processes. Valid values are Yes (Y) or No (N).
Item Parent	Optional	This uniquely identifies the item/group at the level above the item.
Item Grandparent	Optional	This uniquely identifies the item/group two levels above the item.
TI	Optional	This determines the number of shipping units (cases) that make up one tier of a pallet. Multiply TI x HI to get total number of cases for a pallet.
HI	Optional	This determines the number of tiers that make up a complete pallet (height). Multiply TI x HI to get total number of cases for a pallet.
Store Order Multiple	Optional	This contains the multiple in which the item needs to be shipped from a warehouse to the location. Valid values are found in ORML.
Daily Wastage Percentage	Optional	This defines the average percentage lost from inventory on a daily basis due to natural wastage.
Ticket Price Units	Optional	This defines the units in the ticket price in terms of the Price UOM for ticketing.
Ticket Price	Optional	This defines the retail to be used on the ticket in terms of the Price UOM for ticketing.
Ticket UOM	Optional	Unit of measure used on the ticket for this item.
Primary Variant	Optional	This field is used to address sales of PLUs (that is, above transaction level items) when inventory is tracked at a lower level (that is, UPC). This field will only contain a value for items one level higher than the transaction level. Valid choices will be any transaction level item that is a child of this item.
Primary Cost Pack	Optional	This contains a simple pack item containing the item in the item column for this record.
Inbound Handling Days	Optional	This indicates the number of inbound handling days for an item at a warehouse type location.
Regular Unit Retail	Optional	This defines the unit retail in the standard unit of measure for the item/location when not on clearance. This field is stored in the local currency.
Multi-units	Optional	This defines the quantity to be purchased in order to get the multi-unit retail in the multi-selling unit of measure for the item/location.
Multi-unit Retail	Optional	This defines the multi-unit retail in the multi-selling unit of measure for the item/location. This field is stored in the local currency.
Multi-selling UOM	Optional	This defines the selling unit of measure for the item/location if a multi-unit retail has been defined.
Clearing Indicator	Optional	This determines if item is on clearance at the store. Valid values are Yes (Y) or No (N).
Costing Location	Optional	This contains the costing location for the franchise store. This field may contain a store or a warehouse.
Costing Location Type	Optional	This defines the type of location is in the costing location field. Valid values are Store (S) or Warehouse (W).
Currency Code	Always	This defines the currency code under which the location operates.
Launch Date	Optional	This holds the date when the item is initially sold at the location.
Quantity Key Options	Optional	This field determines whether the quantity key on a POS can be used for this item at the location. Valid values are found in code type RPO.
Manual Price Entry	Optional	This field determines whether the price for the item/location can be entered manually on a POS. Valid values are found in code type RPO.
Deposit Code	Optional	This determines whether a deposit is associated with this item at the location. Valid values are found in code DEPO.
Food Stamp Indicator	Optional	This determines whether the item is approved for food stamps at the location. Valid values are Yes (Y) or No (N).
WIC Indicator	Optional	This determines whether the item is approved for WIC at the location. Valid values are Yes (Y) or No (N).
Proportional Tare Percentage	Optional	This is the proportion of the total weight of a unit of an item that is the packaging. For example, if the tare item is bulk candy, this is the proportional of the total weight of one piece of candy that is the candy wrapper.
Fixed Tare Value	Optional	This is the tare of the packaging. For example, if the tare item is bulk candy, this is weight of the bag and twist tie.
Fixed Tare UOM	Optional	This contains the unit of measure value associated with the tare value.
Reward Eligible Indicator	Optional	This determines whether the item is legally valid for various types of bonus point/award programs at the location. Valid values are Yes (Y) or No (N).
National Brand Comparison Item	Optional	This contains the nationally branded item to which it will be compared to. Will contain a valid item ID.
Return Policy	Optional	This determines the return policy for the item at the location. Valid values are found in code type RETP.
Stop Sale Indicator	Optional	This defines if the sale of the item should be stopped immediately at the location (that is, in case of recall, or something similar). Valid values are Yes (Y) or No (N).
Elect Marketing Clubs	Optional	This contains the code that represents the marketing clubs to which the item belongs at the location. Valid values are found in code type MKTC.
Report Code	Optional	This determines to which reports the location should run. Valid values are found in code type REPC.
Required Shelf Life on Selection	Optional	This contains the required shelf life for an item on selection in days.
Required Shelf Life on Receipt	Optional	This contains the required shelf life for an item on receipt in days.
Store Re-orderable Indicator	Optional	This determines whether the store may re-order the item. Valid values are Yes (Y) or No (N).
Rack Size	Optional	This determines the rack size that should be used for the item.
Full Pallet Item	Optional	This determines whether a store must reorder an item in full pallets only. Valid values are Yes (Y) or No (N).
In-Store Market Basket	Optional	This contains the in-store market basket code for the item/location combination. Valid values are found in code type STMB.
Storage Location	Optional	This contains the current storage location or bin number for the item at the location.
Alternate Storage location	Optional	This contains the preferred alternate storage location or bin number for the item at the location.
Refundable Indicator	Optional	This determines if the item is refundable at the location or not. Valid values are Yes (Y) or No (N).
Back Order Indicator	Optional	This determines if the item can be back-ordered to the location. Valid values are Yes (Y) or No (N).
Promotable Indicator	Optional	This determines whether the retailer is allowed to specify if the item is promotable or not. Valid values are Yes (Y) or No (N).
Unit Cost	Optional	This contains the current unit cost of the item based on the primary supplier/country for the location in local currency.
Pickup Lead Time	Optional	This defines the time it takes to get the item from the supplier to the location.
Cost UOM	Optional	This is used to allow costs to be managed in a different UOM than the standard UOM.
Calculation Basis	Optional	This determines if the cost for the consignment/concession item will be managed either based on cost per unit or as a percentage of retail. Valid values are: C - Cost per Unit P - Purchase Rate
Purchase Rate	Optional	This contains the percentage of the retail price which will determine the cost paid to the supplier for a consignment or concession item, if the calculation basis for the item/location is Purchase Rate.
RFID Indicator	Optional	This allows the retailer to specify if the item should be RFID tagged or not.

Child Nodes

• Custom Flex Attributes (optional)

Custom Flex Attributes

Message Elements	Included?	Notes
Name	Always	The flex attribute defined by the business
Value	Optional	The value of the flex attribute defined by the business
Value Date	Optional	The date value of the flex attribute if the flex attribute is defined as a date.

Item/Location Virtual Location Replenishment Details

Message Element	Included?	Notes
Location	Always	This is the location ID where the item/location change is related to. This will contain the Virtual Warehouse if the location type is Warehouse or the Store ID if the location type is a Store.
Location Type	Always	This is the type of location in the location field. Valid values are Store (S) or Warehouse (W).
Primary Replenishment Supplier	Always	The supplier from which the specified location will source the replenishment demand for the specified item location.
Replenishment Method	Always	The code for the algorithm that will be used to calculate the recommended order quantity for the item location. Valid values are: C - Constant M - Min/Max F - Floating point T - Time Supply D - Dynamic SO - Store Orders
Reject Store Order Indicator	Optional	Contains an indicator that determines whether uploaded store orders can be rejected. If the indicator is No (N), then store orders for all need dates are valid. If Yes (Y), store orders with need dates on or after the next delivery date are valid.
Next Delivery Date	Optional	The next delivery date calculated for the next review cycle.
Multiple Runs Per Day Indicator	Always	Indicates if an item can be replenished multiple times per day at the location.

Modified Item Location Relationship

Modifying an item/location attribute as part of various business processes will trigger the creation of an item/location modification message. The update message will contain the details for all fields that changed in the message to inform subscribing applications of the changes made in Merchandising.

Deleted Item Location Relationship

When an item/location relationship is removed from an item (not just moved to Deleted status), it will trigger an item/location delete transaction message to an external system. The delete message contains the item/location to be deleted.

Item Replenishment Attributes

The creation, modification or deletion of item replenishment attributes for a location will trigger the creation of an item/location replenishment modification message. This will publish a message to subscribing systems to inform them of the replenishment attributes updates for the item on a particular location.

Custom Flex Attributes

If any custom flex attributes (CFAS) for the item/location has been added or modified, it will trigger an item/location modify message. All of the entity's active flex attributes from all attribute groups are published as key-value pairs based on the group set view. This CFAS object is embedded in the outbound Item/Location message.

Error Handling

When the publication encounters a non-fatal error, messages continue to be processed. For the particular message where the error was encountered, a status of Hospital (H) is sent to the RIB and the status of the message in the queue is set to H. These types of errors occur when no changes in the database have been made and a process to try to re-publish the messages is available. In case the error is a fatal error, a status of Error (E) is sent to the RIB and the next message in the queue is not retrieved until the error is resolved.

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
ItemLocCre	Item Location Create Message	ItemLocDesc.xsd
ItemLocMod	Item Location Modify Message	ItemLocDesc.xsd
ItemLocDel	Item Location Delete Message	ItemLocRef.xsd
ItemLocReplMod	Item Location Replenishment Modify Message	ItemLocDesc.xsd

Merchandise Hierarchy Publication API

This section describes the merchandise hierarchy publication API.

Functional Area

Merchandise Hierarchy

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

This API publishes information regarding all the levels of the merchandise hierarchy to the RIB such that all the downstream applications may subscribe to it and have merchandise hierarchy information in sync with Merchandising.

New Division

When a new division is created in Merchandising, it triggers a message to be sent to notify external systems. The full details are sent for the new division as part of the create message, the division ID, division name, buyer, merchandiser, and total market amount.

Updated Division

When an existing division is updated, an update message is triggered to provide the details of the update. The update message, like create, will contain the full details in the message for all fields.

The format used when creating or modifying a division is shown below.

Table 2-59 Division Create and Update

Message Element	Included?	Notes
Division	Always	Contains the number which uniquely identifies the division of the company.
Division Name	Always	Contains the name which, along with the division number, identifies the division of the company.
Buyer	Optional	Contains the number of the buyer associated with the division.
Merchandiser	Optional	Contains the number of the merchandiser associated with the division.
Total Market Amount	Optional	Contains the total market amount that is expected for the division. The field can be used for analytics or reporting.

Deleted Division

When an existing division is deleted, this will also trigger a delete transaction which to be sent to notify external systems that this division is no longer valid. The delete message will include only the ID of the division being deleted.

Table 2-60 Division Delete

Message Element	Included?	Notes
Division	Always	Contains the number which uniquely identifies the division of the company.

New Group

When a new group is created in Merchandising, it triggers a message to be sent to notify external systems. The full details are sent for the new group as part of the create message, the group ID, group name, buyer, merchandiser, and division.

Updated Group

When an existing group is updated, an update message is triggered to provide the details of the update. The update message, like create, will contain the full details in the message for all fields.

Table 2-61 Group Create and Update

Message Element	Included?	Notes
Group Number	Always	Contains the number which uniquely identifies the group.
Group Name	Always	Contains the description which, along with the group number, identifies the group.
Buyer	Optional	Contains the number of the buyer associated with the group.
Merchandiser	Optional	Contains the number of the merchandiser associated with the group.
Division	Always	Contains the number of the division of which the group is a member.

Deleted Group

When an existing group is deleted, this will also trigger a delete transaction which to be sent to notify external systems that this group is no longer valid. The delete message will include only the group ID being deleted.

Table 2-62 Group Delete

Message Element	Included?	Notes
Group Number	Always	Contains the number which uniquely identifies the group.

New Department

When a new department is created in Merchandising, it triggers a message to be sent to notify external systems. The full details are sent for the new department as part of the create message, the department ID, department name, buyer, purchase type, total market amount, merchandiser, group, budgeted markup, profit calc type, markup calc type, OTB calc type, budgeted intake percentage, and department-level VAT inclusive indicator. The custom flex attributes, if applicable, are also sent as a part of the message.

Updated Department

When an existing department is updated, an update message is triggered to provide the details of the update. The update message, like create, will contain the full details in the message for all fields, including custom flex attributes.

Table 2-63 Department Create and Update

Message Element	Included?	Notes
Department	Always	Contains the number which uniquely identifies the department.
Department Name	Always	Contains the description which identifies the department.
Buyer	Optional	Contains the number which uniquely identifies the buyer for that department.
Purchase Type	Always	Contains a code which indicates whether items in this department are, by default, owned (normal) merchandise, consignment, or concession. Items in the department can be a mix of these item types, this acts only as a default. Valid values are: 0 - Normal Merchandise 1 - Consignment Stock 2 - Concession Stock
Total Market Amount	Optional	Contains the total market amount that is expected for the department. The field can be used for analytics or reporting.
Merchandiser	Optional	Contains the number which uniquely describes the merchandiser for that department.
Group Number	Always	Contains the number of the group in which the department exists.
Budgeted Markup	Always	Contains the markup percent of cost that is a target for this department.
Profit Calc Type	Always	Contains a number which indicates whether profit will be calculated by cost or retail, indicating the accounting method to be used for the department. Valid values are: 1 = Direct Cost 2 = Retail Inventory
Markup Calc Type	Always	Contains the code which determines how markup is calculated in this department. Valid values are: C - Cost R - Retail
OTB Calc Type	Always	Contains the code letter which determines how open to buy is calculated in this department. Valid values are: C - Cost R - Retail
Maximum Average Counter	Optional	A maximum average counter will hold the maximum count of days with acceptable data to include in an average for items within the department.
Average Tolerance Percentage	Optional	A tolerance percentage value used in averaging for items within this value. This value will set up a range for appropriate data and constrain outliers. This column will hold 70% as 70 - not 0.70.
Budgeted Intake	Always	Contains the markup percent of retail that is a target for this department.
Department VAT Include Indicator	Always	This flag is no longer used by Merchandising and will always be defaulted to Y.
Custom Flex Attributes	Optional	Child Node

Deleted Department

When an existing department is deleted, this will also trigger a delete transaction which to be sent to notify external systems that this department is no longer valid. The delete message will include only the department ID being deleted.

Table 2-64 Department Delete

Message Element	Included?	Notes
Department	Always	Contains the number which uniquely identifies the department being deleted.

New Class

When a new class is created in Merchandising, it triggers a message to be sent to notify external systems. The full details are sent for the new class as part of the create message, the class ID, class name, class VAT indicator, and department. The custom flex attributes, if applicable, are also sent as a part of the message.

Updated Class

When an existing class is updated, an update message is triggered to provide the details of the update. The update message, like create, will contain the full details in the message for all fields including custom flex attributes.

Table 2-65 Class Create and Modify

Message Element	Included?	Notes
Class	Always	Contains the number which uniquely identifies the class within the department.
Class name	Always	Contains the name of the class which, along with the class number, identifies the class.
Class VAT Indicator	Always	This field is no longer used by Merchandising and will always default to N.
Department	Always	Contains the number of the department of which the class is a member.
Unique Class ID	Always	This field contains the number that uniquely identifies the department/class combination. This column is populated by the system and not displayed to end users. This will be used for integration to downstream systems as many downstream systems are unable to accommodate the composite dept/class key in their hierarchy and item structures.
Custom Flex Attributes	Optional	Child Node

Deleted Class

When an existing class is deleted, this will also trigger a delete transaction which to be sent to notify external systems that this class is no longer valid. The delete message will include only the class being deleted and its department.

Table 2-66 Class Delete

Message Element	Included?	Notes
Department	Always	Contains the number which uniquely identifies the department.
Class	Always	Contains the number which uniquely identifies the class within the department.

New Subclass

When a new subclass is created in Merchandising, it triggers a message to be sent to notify external systems. The full details are sent for the new subclass as part of the create message, the subclass ID, subclass name, department, and class. It also contains a unique ID for the subclass and class, which are not dependent on knowing the department and class IDs displayed in Merchandising. The custom flex attributes, if applicable, are also sent in as a part of the message.

Updated Subclass

When an existing subclass is updated, an update message is triggered to provide the details of the update. The update message, like create, will contain the full details in the message for all fields including custom flex attributes.

Table 2-67 Subclass Create and Update

Message Element	Included?	Notes
Subclass	Always	Contains the number which uniquely identifies the subclass within the department and class.
Subclass name	Always	Contains the name of the subclass which, along with the subclass number, uniquely identifies the subclass.
Class	Always	Contains the class number of which the subclass is a member.
Department	Always	Contains the department number of which the subclass is a member.
Custom Flex Attributes	Optional	Child Node
Unique Subclass ID	Always	This field contains the number that uniquely identifies the department/class/subclass combination. This column is populated by the system and not displayed to end users. This will be used for integration to downstream systems as many downstream systems are unable to accommodate the composite dept/class/subclass key in their hierarchy and item structures.
Unique Class ID	Always	This field contains the number that uniquely identifies the department/class combination. This column is populated by the system and not displayed to end users. This will be used for integration to downstream systems as many downstream systems are unable to accommodate the composite dept/class key in their hierarchy and item structures.

Deleted Subclass

When an existing subclass is deleted, this will also trigger a delete transaction which to be sent to notify external systems that this class is no longer valid. The delete message will include only the subclass being deleted, along with its class and department.

Table 2-68 Subclass Delete

Message Element	Included?	Notes
Department	Always	Contains the number which uniquely identifies the department.
Class	Always	Contains the number which uniquely identifies the class within the department.
Subclass	Always	Contains the number which uniquely identifies the subclass within the department and class.

Custom Flex Attributes

If any custom flex attributes (CFAS) for the department, class, or subclass have been added or modified, it will trigger an update message. The node of the integration that supports this will contain the name of the attribute as it is defined in the group set level view, the value of the custom attribute. If it is a date attribute, the date value is in a separate field. Flex attributes can only be added to or updated; they cannot be deleted.

Table 2-69 Flex Attributes

Message Element	Included?	Notes
Name	Always	Hold the attribute name.
Value	Optional	Holds the value of the attribute for number and character type attributes
Value Date	Optional	Holds the date for date type attributes.

Error Handling

When the publication encounters a non-fatal error, messages continue to be processed. For the message where the error was encountered, a status of Hospital (H) is sent to the RIB and the status of the message in the queue is set to H. In case the error is a fatal error, a status of Error (E) is sent to the RIB and the next message in the queue is not retrieved until the error is resolved.

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
divisoncre	Division Create Message	MrchHrDivDesc.xsd
divisonmod	Division Modify Message	MrchHrDivDesc.xsd
divisiondel	Division Delete Message	MrchHrDivRef.xsd
groupcre	Group Detail Create Message	MrchHrGrpDesc.xsd
groupmod	Group Detail Modify Message	MrchHrGrpDesc.xsd
groupdel	Group Detail Delete Message	MrchHrGrpRef.xsd
deptcre	Department Detail Create Message	MrchHrDeptDesc.xsd
deptmod	Department Detail Modify Message	MrchHrDeptDesc.xsd
deptdel	Department Detail Delete Message	MrchHrDeptRef.xsd
classcre	Class Detail Create Message	MrchHrClsDesc.xsd
classmod	Class Detail Modify Message	MrchHrClsDesc.xsd
classdel	Class Detail Delete Message	MrchHrClsRef.xsd
subclasscre	Subclass Detail Create Message	MrchHrSclsDesc.xsd
subclassmod	Subclass Detail Modify Message	MrchHrSclstDesc.xsd
subclassdel	Subclass Detail Delete Message	MrchHrSclsRef.xsd

Order Publication API

This section describes the order publication API.

Functional Area

Purchase Orders

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Merchandising publishes new and updated purchase orders to inform external subscribing systems, such as the warehouse management system or store inventory management (SIM/SIOCS), of the changes. Only orders that have been approved are published and will be visible to external systems. Updates are provided synchronously in a near-real-time manner.

There are several ways a purchase order can be created in Merchandising, such as through the purchase order screen, using spreadsheet upload, from customer orders and franchise orders fulfilled from a supplier, through item replenishment, by a supplier in a vendor-managed inventory environment, and so on. All purchase orders are published in a similar manner, regardless of their source.

Merchandising publishes two sets of order messages to the RIB for two kinds of subscribing applications. The primary order information is sent at the physical location level, which includes both physical warehouses and stores. This is intended to be used by the warehouse and store applications. The second set of information contains order quantity by virtual location. Applications that understand virtual warehouses subscribe to these messages.

New Purchase Orders

Creating a new purchase order triggers a message that notifies external systems of the changes. The full details in the message are sent for the new order as part of the create message. It includes details such as which supplier is being ordered from, order dates, the items that are ordered and the quantity, the size of the pack being ordered, the location that each item is going to be delivered to, and so on. If the order is for a customer order, (order_type = 'CO'), the customer order number and fulfillment order number retrieved from the ORDCUST table will be included in the header message and published.

New Purchase Order Details

Adding item/locations to an existing order triggers a detail create message to be created and published after the order is re-approved. This action will create one header modify message for the status change and each detail addition will create one detail create message. The detail create message will still contain both complete header and detail information.

Updated Purchase Orders

Purchase order updates trigger a message to notify external systems, based on updates made at the order header level and/or the detail level. The message that will be published will either contain the header information that changed in the message for header updates such as date changes, just the detail level information for detail level changes such as quantity changes or addition of items, or both.

Header Only Updates

When updates are made at the order header level such as updating dates or changing the status of the order, only header information is published. Information specific to items and locations are not included. Changes in header information, unapproving a purchase order, or any action that may change of status of a pre-approved order, such as closing or reinstating an order trigger header level publication. Creating and updating header level custom flex attributes (CFAS) also trigger header level update publishing.

Detail Only Updates

When updates are made at the order detail level without changes to the header information, only detail information is published. Detail level updates include changes in quantity ordered, unit cost, and estimated in stock date. When an order has already been approved, no deletes can be made at the detail level; but the quantity on the order can be cancelled. This action will trigger an update message to be sent. Creating and updating detail level custom flex attributes also trigger detail level publishing.

Full Message Updates

In cases where the solution receiving purchase orders cannot support just receiving the changes, another option is provided that can resend the full purchase order details whenever there is a change. This will be published, along with the delta messages, in cases where the system option Publish Full Objects (PUB_FULL_OBJECTS_IND) is set to Y. This is also used for Oracle WMS Cloud integration.

Deleted Purchase Orders

Deleting approved purchase orders can be done by cancelling all items in the order, which will set the order status to closed. The purchase order will not be removed from the system. Because both the order status and order quantities are changed, both header and detail information are published to external systems. The information included in the header and detail messages are the same as that described under the Updated Purchase Orders section.

Flex Attributes

If any custom flex attributes (CFAS) for the order have been added or modified, it will trigger an order header or detail update message, as described above. The node of the integration that supports this will contain the name of the attribute as it is defined in the group set level view, the value of the custom attribute. If it is a date attribute, the date value is in a separate field. You can define group set views at the header level (ORDHEAD), order/item level (ORDSKU) or at the order/item/location (ORDLOC) levels. Flex attributes can only be added to or updated on an order; they cannot be deleted.

Error Handling

When the publication encounters a non-fatal error, messages continue to be processed. For the particular message where the error was encountered, a status of Hospital (H) is sent to the RIB and the status of the message in the queue is set to H. These types of errors occur when no changes in the database have been made and a process to try to re-publish these messages is available. In case the error is a fatal error, for example, when changes to data have already been made, a status of Error (E) is sent to the RIB and the message status in the queue will be in Error status. The error message as well as the object containing the order number and order details is returned to Merchandising.

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
POCre	Purchase Order Create Message	PODesc.xsd
POCre (CustFlexAttriVo)	Purchase Order Flex Attribute Create Message	PODesc.xsd
PODtlCre	Purchase Order Detail Create Message	PODesc.xsd
PODtlCre (CustFlexAttriVo)	Purchase Order Detail Flex Attribute Create Message	PODesc.xsd
POHdrMod	Purchase Order Modify Message	PODesc.xsd
POHdrMod (CustFlexAttriVo)	Purchase Order Header Flex Attribute Modify Message	PODesc.xsd
PODtlMod	Purchase Order Detail Modify Message	PODesc.xsd
PODtlMod (CustFlexAttriVo)	Purchase Order Detail Flex Attribute Modify Message	PODesc.xsd
PODel	Purchase Order Delete Message for unpublished orders	PORef.xsd
PODtlDel	Purchase Order Detail Delete Message for unpublished orders	PORef.xsd
POFulRep	Purchase Order with Full payload Message	PODesc.xsd

Organizational Hierarchy Publication API

This section describes the organization hierarchy publication API.

Functional Area

Organizational Hierarchy

Business Overview

Merchandising publishes details about new, updated, and deleted levels of the organizational hierarchy (chain, area, region, and district) to external systems such that all the downstream applications and external systems may be informed of the updates. Updates are provided synchronously in a near-real-time manner.

The entities sent as part of the new, update, or delete messages will always include the hierarchy level and hierarchy value. The following values will be published:

• If the message relates to chain, the message will contain a hierarchy level of 'CH' and the chain number.

• If the message relates to area, the message will contain a hierarchy level of 'AR' and the area number.

• If the message relates to a region, the message will contain a hierarchy level of 'RE' and the region number.

• If the message relates to a district, the message will contain a hierarchy level of 'DI' and the district number.

New Chains/Areas/Regions/Districts

Creating a new organizational level triggers a message to be sent to notify external systems. The hierarchy level, hierarchy value, description, manager name, currency code, parent ID and parent level are sent for the new organizational level as part of the create message.

Updated Chains/Areas/Regions/Districts

Updating an organizational level triggers a message to notify external systems. The hierarchy level, hierarchy value, description, manager name, currency code, parent ID and parent level are sent for the updated organizational level as part of the update message.

Deleted Chains/Areas/Regions/Districts

Deleting an organizational level triggers a message to be sent to notify external systems. The hierarchy level, hierarchy value, parent ID, and parent level are sent for the deleted organizational level.

Error Handling

If any errors are encountered while publishing the message, a fatal error with status E (Error) is sent to RIB. This would bring down the RIB adapter thus preventing any further messages from being processed until this is resolved.

Message XSD

Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
chaincre	Chain Create Message	OrgHierDesc.xsd
chainmod	Chain Modify Message	OrgHierDesc.xsd
chaindel	Chain Delete Message	OrgHierRef.xsd
areacre	Area Create Message	OrgHierDesc.xsd
areamod	Area Modify Message	OrgHierDesc.xsd
areadel	Area Delete Message	OrgHierRef.xsd
regioncre	Region Create Message	OrgHierDesc.xsd
regionmod	Region Modify Message	OrgHierDesc.xsd
regiondel	Region Delete Message	OrgHierRef.xsd
districtcre	District Create Message	OrgHierDesc.xsd
districtmod	District Modify Message	OrgHierDesc.xsd
districtdel	District Delete Message	OrgHierRef.xsd

Partner Publication API

This section describes the partner publication API.

Functional Area

Partners

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

This API publishes partner, partner address information and custom flex attributes through the RIB to external systems. This information is further subscribed by integrating systems like Oracle Warehouse Management Cloud (WMS) and Store Inventory and Operations Cloud Service (SIOCS) that need to keep their partners synchronized with Merchandising. Partner information is published when new partners are created, updates are made to existing partners, or existing partners are deleted. Similarly, addresses are published when they are added, modified, or deleted.

Partner Creation

Partners can be setup in Merchandising though the application UI and it results in this message being published when a partner is created. The partner creation message is published from Merchandising only after the partner has been completely setup, including the mandatory addresses. Once all these criteria are met for a valid create message, the messages will be combined and sent to the RIB.

Table 2-70 Partner Header Create and Update

Message Element	Included?	Notes
Partner Type	Always	Specifies the type of the partner. Valid partner types are held on the code tables under code type PTNR.
Partner ID	Always	Unique number that identifies the partner within the system.
Partner Description	Always	Contains the partner’s name.
Currency Code	Always	The code identifying the currency the partner uses for business transactions.
Language	Optional	The partner's preferred language.
Status	Always	Indicates if the partner is currently active. Valid values are A = Active or I = Inactive.
Contact Name	Optional	Name of the partner's representative contact
Contact Phone	Optional	Phone number of the partner's representative contact.
Contact Fax	Optional	Fax number of the partner's representative contact.
Contact Telex	Optional	The telex number of the partner's representative contact.
Contact Email	Optional	The e-mail address of the partner's representative contact.
Manufacturer's ID	Optional	The manufacturer's tax identification number. Used for partner type Manufacturer.
Principal Country ID	Optional	The country id to which the partner is assigned.
Line of Credit	Optional	The line of credit the company has at the bank in the partner's currency. Used for partner type Bank.
Outstanding Credit	Optional	The total amount of credit that the company has used or has charged against in the partner's currency.
Open Credit	Optional	The total amount that the company can still charge against in the partner's currency.
Year to Date Credit	Optional	The total amount of credit the company has used this year to date in the partner's currency.
Year to Date Drawdowns	Optional	The year to date payments the bank has made on behalf of the company in the partner's currency.
Tax ID	Optional	The unique tax identification number of the partner. This will be used for reporting during the Customs Entry process.
Terms	Always	Payment terms for the partner.
Service Performed Required Indicator	Always	Indicates if the partner's services (e.g., snow removal) must be confirmed as performed before paying an invoice from that partner.
Invoice Pay Location	Optional	Indicates where invoices from this supplier are paid - at the store or centrally through corporate accounting.
Invoice Receive Location	Optional	Indicates where invoices from this supplier are received - at the store or centrally through corporate accounting.
Import Country ID	Optional	Import country of the Import Authority.
Primary Import Authority Indicator	Always	Indicates if an Import Authority is the primary Import Authority for an import country.
Comments	Optional	Comments associated with the partner.
Transfer Entity ID	Optional	The Transfer entity with which an external finisher is associated.
VAT Region	Optional	Tax region with which a partner is associated.
Org Unit ID	Optional	The org unit id with which partner is associated.
Partner Name Secondary	Optional	Secondary name of the partner.
Auto Receive Stock Indicator	Optional	Indicates whether the system will update the stock for the external finisher when the 1st leg of the transfer is shipped.
Partner Address 1	Optional	The partner's primary address line 1.
Partner Address 2	Optional	The partner's primary address line 2.
City	Optional	The city of the partner's primary address.
County	Optional	The county of the partner's primary address.
State	Optional	The state of the partner's primary address.
Country ID	Optional	The country of the partner's primary address.
Postal Code	Optional	The postal code of the partner's primary address.
Partner Org Unit Details	Optional	Child Node
Address Details	Optional	Child Node
Custom Flex Attributes	Optional	Child Node

Table 2-71 Partner Org Unit Detail

Message Element	Included?	Notes
Org Unit ID	Optional	Org unit ID of the partner.

Table 2-72 Address Detail

Message Element	Included?	Notes
State Name	Optional	The state name for the partner address of this type.
Country Name	Optional	The country name for the partner address of this type.
Address Key	Always	The unique address ID from Merchandising.
Address Type	Always	The type for the address. All address types are published for partners.
Primary Address Type Indicator	Optional	Indicates if the address type is primary.
Primary Address Indicator	Always	Indicates whether this address is the primary address for the address type.
Address Line 1	Always	The first line of the address.
Address Line 2	Optional	The second line of the address.
Address Line 3	Optional	The third line of the address.
City	Always	The name of the city that is associated with the address.
State	Optional	The state or province code for the address.
Country ID	Always	The country code for the address.
Post	Optional	The postal code for the address.
Contact Name	Optional	The name of the contact for the partner at this address.
Contact Phone	Optional	The phone number of the contact person at this address.
Contact Telex	Optional	The telex number of the partner representative contact.
Contact Fax	Optional	The fax number of the contact person at this address.
Contact Email	Optional	The email address of the partner representative contact.
Oracle Vendor Site ID	Optional	The unique identifier of this address in the Oracle Financials systems, if used.
County	Optional	The county name that is associated with the address.
Jurisdiction Code	Optional	ID associated to the tax jurisdiction of the country-state relationship.
Custom Flex Attributes	Optional	Child Node

Partner Header Modification

Updating an existing partner will trigger header modification message to notify the integrating systems. This message will contain the header level information, the primary address and flex attributes of the partner that changed.

Partner Detail Creation

Adding new addresses for an existing partner will trigger a partner detail creation message. This message is sent along with the header information.

Partner Detail Modification

Any updates made to the partner's address information will trigger a partner detail modification message. This message is sent along with the partner header information.

Deleting Partners

Deleting an existing partner will trigger a partner delete message to notify the integrating systems. This message will contain the partner type and partner ID that was deleted.

Deleting Partner Details

Partner addresses can be deleted only if they are not mandatory or if they are mandatory but are not marked as the primary address for that address type. The deletion will trigger a partner detail delete message. The message will contain the partner type, partner ID and the address key of the address that was deleted.

Table 2-73 Partner Delete

Message Element	Required?	Notes
Partner Type	Always	Specifies the type of the partner.
Partner ID	Always	Unique number that, with the partner type, identifies the partner being deleted.
Address Detail	Optional	Child Node

Table 2-74 Address Detail Delete

Message Element	Required?	Notes
Address Key	Always	The unique identifier of the address being deleted.

Custom Flex Attributes

If any custom flex attributes (CFAS) for the partner or partner address have been added or modified, it will trigger an update message.  The node of the integration that supports this will contain the name of the attribute as it is defined in the group set level view, the value of the custom attribute. If it is a date attribute, the date value is in a separate field.  Flex attributes can only be added to or updated; they cannot be deleted.

Table 2-75 Flex Attributes

Message Element	Required?	Notes
Name	Always	Hold the attribute name.
Value	Optional	Holds the value of the attribute for number and character type attributes
Value Date	Optional	Holds the date for date type attributes.

Error Handling

When the publication encounters a non-fatal error, messages continue to be processed. For the message where the error was encountered, a status of Hospital (H) is sent to the RIB and the status of the message in the queue is set to H. In case the error is a fatal error, a status of Error (E) is sent to the RIB and the next message in the queue is not retrieved until the error is resolved.

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
partnercre	Partner Create	PartnerDesc.xsd
partnermod	Partner Modify	PartnerDesc.xsd
partnerdel	Partner Delete	PartnerRef.xsd
partnerdtlcre	Partner Detail Create	PartnerDesc.xsd
partnerdtlmod	Partner Detail Modify	PartnerDesc.xsd
Partnerdtldel	Partner Detail Delete	PartnerRef.xsd

Receiver Unit Adjustment Publication API

This section describes the receiver unit adjustment publication API.

Functional Area

Receiver Unit Adjustment.

Business Overview

When mistakes are made during the receiving process at the store or warehouse, receiver unit adjustments (RUAs) are made to correct the mistake. Merchandising publishes messages about receiver unit adjustments to the Oracle Retail Integration Bus (RIB).

When RUAs are initiated through Oracle Retail Invoice Matching (ReIM) or created through Merchandising forms, a message is published to a store management system (such as SIM) and a warehouse management system.

Note:

Oracle Retail's warehouse management system RWMS does NOT subscribe to Receiver Unit Adjustment messages). Because these systems only have access to the original receipt, the message communicates the original receipt number and not the child receipt number.

Package Impact

This section describes the package impact.

Business object ID

None

Package name

RMSMFM_RCVUNITADJ

Spec file name: rmsmfm_rcvunitadjs.pls

Body file name: rmsmfm_rcvunitadjb.pls

Package Specification - Global Variables

FAMILY           CONSTANT  RIB_SETTINGS.FAMILY%TYPE  'rcvunitadj';
RCVUNITADJ_ADD   CONSTANT  VARCHAR2(15)              'rcvunitadjcre';

If multi-threading is being used, call API_LIBRARY.RIB_SETTINGS to get the number of threads used for the publisher. Using the number of threads and the location ID, calculate the thread value.

Insert a record into the RCVUNITADJ_MFQUEUE.

Function Level Description - GETNXT

GETNXT (O_status_code   OUT  VARCHAR2,
        O_error_msg     OUT  VARCHAR2,
        O_message_type  OUT  VARCHAR2,
        O_message       OUT  RIB_OBJECT,
        O_bus_obj_id    OUT  RIB_BUSOBJID_TBL,
        O_routing_info  OUT  RIB_ROUTINGINFO_TBL,
        I_num_threads   IN   NUMBER DEFAULT 1,
        I_thread_val    IN   NUMBER DEFAULT 1)

The RIB calls GETNXT to get messages. The driving cursor will query for unpublished records on the RCVUNITADJ_MFQUEUE table (PUB_STATUS = 'U').

GETNXT should check for records on the queue with a status of 'H'ospital for the current business object, GETNXT should raise an exception to send the current message to the Hospital.

The information from the RCVUNITADJ_MFQUEUE table is passed to PROCESS_QUEUE_RECORD. PROCESS_QUEUE_RECORD will build the Oracle Object message to pass back to the RIB. If PROCESS_QUEUE_RECORD does not run successfully, GETNXT should raise an exception.

If any exception is raised in GETNXT, including the exception raised by an unsuccessful call to PROCESS_QUEUE_RECORD, HANDLE_ERRORS should be called.

Function Level Description - PUB_RETRY

PUB_RETRY (O_status_code       OUT    VARCHAR2,
           O_error_msg         OUT    VARCHAR2,
           O_message_type  IN  OUT    VARCHAR2,
           O_message           OUT    RIB_OBJECT,
           O_bus_obj_id    IN  OUT    RIB_BUSOBJID_TBL,
           O_routing_info  IN  OUT    RIB_ROUTINGINFO_TBL,
           I_ref_object    IN         RIB_OBJECT)

This procedure republishes the entity that failed to be published before. It is the same as GETNXT except that the record on RCVUNITADJ_MFQUEUE to be published must match the passed in sequence number contained in the ROUTING_INFO.

Function Level Description - PROCESS_QUEUE_RECORD (local)

This function controls the building of Oracle Objects given the business transaction's key values and a message type. It contains all of the shared processing between GETNXT and PUB_RETRY.

The function first calls MAKE_CREATE to build the appropriate oracle object. It then calls the DELETE_QUEUE_REC to delete the RUA_MFQUEUE for the passed-in rowid.

Function Level Description - MAKE_CREATE (local)

This function is used to create the Oracle Object for the initial publication of a business transaction.

• Call BUILD_HEADER_OBJECT to get a header level Oracle Object plus any extra functional holders.

• Call BUILD_DETAIL_OBJECTS to get a table of detail level Oracle objects and add the detail level Oracle Objects to the header object.

Function Level Description - BUILD_HEADER_OBJECT (local)

Accepts header key values, performs necessary lookups, builds and returns a header level Oracle Object.

This function also builds the routing information object using the location.

Function Level Description - BUILD_DETAIL_OBJECTS (local)

The function is responsible for the Oracle Object used for a DESC message (inserts and updates). It adds as many mfqueue records to the message as it can given the passed in message type and business object keys.

• Call BUILD_SINGLE_DETAIL passing in the I_business_obj record.

• Ensure that ROUTING_INFO is constructed if routing information is stored at the detail level in the business transaction.

Function Level Description - BUILD_SINGLE_DETAIL (local)

Accept inputs and builds a detail level Oracle Object. If the adjustment quantity is negative, the from disposition should be 'ATS' and the to disposition should be NULL. If the adjustment quantity is positive, the to disposition should be NULL and the from disposition should be 'ATS'.

Function Level Description - LOCK_THE_BLOCK (local)

This function locks all queue records for the current business object. This is to ensure that GETNXT does not wait on any business processes that currently have the queue table locked and have not committed. This can occur because ADDTOQ, which is called from the triggers, deletes from the queue table for DTL_UPD, DTL_DEL, and HDR_DEL messages.

Function Level Description - HANDLE_ERRORS (local)

HANDLE_ERRORS is called from GETNXT and PUB_RETRY when an exception is raised.

If the error is a non-fatal error, GETNXT passes the sequence number of the driving RCVUNITADJ_MFQUEUE record back to the RIB in the ROUTING_INFO. It sends back a status of 'H'ospital to the RIB as well. It then updates the status of the queue record to 'H'ospital, so that it will not get picked up again by the driving cursor in GETNXT.

If the error is a fatal error, a status of 'E'rror is returned to the RIB.

The error is considered non-fatal if no DML has occurred yet. Whenever DML has occurred, then the global variable LP_error_status is flipped from 'H'ospital to 'E'rror.

Function Level Description - DELETE_QUEUE_REC (local)

This private function will delete the records from rcvunitadj_mfqueue table for the rowid passed in as input parameter.

Trigger Impact

Trigger name: EC_TABLE_RUA_AIR.TRG

Trigger file name: ec_table_rua_air.trg

Table: RAU_RIB_INTERFACE

Inserts:

• Send the appropriate column values to the ADDTOQ procedure in the MFM with the message type RMSMFM_RCVUNITADJ.RCVUNITADJ_ADD.

Message XSD

Here are the filenames that correspond with each message type. Please consult RIB documentation for each message type in order to get a detailed picture of the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
RcvUnitAdjCre	Receiver Unit Adjustment Create Message	RcvUnitAdjDesc.xsd

Design Assumptions

Each receiver unit adjustment contains the delta quantity to be adjusted. As such they can be processed in any order by the subscribing application. There is no dependency between different RUA messages.

Table Impact

TABLE	SELECT	INSERT	UPDATE	DELETE
RUA_MFQUEUE	Yes	Yes	Yes	Yes

RTV Request Publication API

This section describes the RTV request publication API.

Functional Area

Return to Vendor

Business Overview

A return to vendor (RTV) order is used to send merchandise back to the supplier. The RTV message is published by Merchandising to the store or warehouse. For an RTV, the initial transfer of stock to the store is a distinctly different step from the RTV itself. Once the transferred stock arrives at the store, the user then creates the RTV. RTVs are created by the following:

1. Adding one supplier.

2. Selecting the sending locations.

3. Adding the items, either individually or through the use of item lists.

In order to return items to a vendor from multiple stores as part of one operation, the items must go through a single warehouse. The transfer of items from several different stores to one warehouse is referred to as a mass return transfer (MRT). The items are subsequently returned to the vendor from the warehouse.

Return to vendor requests created in Merchandising should be published to the RIB to provide the integration subsystem application with visibility to the corporately created RTV. Consequently, when the integration subsystem application ships the RTV, it must communicate the original RTV order number back to Merchandising so that Merchandising can correctly update the original RTV record.

When publishing a header mod or a detail create, detail mod, detail delete message, a second full replacement message with message type 'RtvReqfulrep' will be published from Merchandising if system option PUB_FULL_OBJECTS_IND is configured to be Y on the PRODUCT_CONFIG_OPTIONS table. This message payload will contain a full snapshot of the RTV. Based on the message type, RIB will route the full replacement message to appropriate applications.

Package Impact

This section describes the package impact.

Business Object ID

RTV order number.

Package name: RMSMFM_RTVREQ

Spec file name: rmsmfm_rtvreqs.pls

Body file name: rmsmfm_rtvreqb.pls

Function Level Description - ADDTOQ

ADDTOQ (O_error_msg      IN OUT    VARCHAR2,
        I_message_type   IN        VARCHAR2,
        I_rtv_order_no   IN        RTV_HEAD.RTV_ORDER_NO%TYPE,
        I_status         IN        RTV_HEAD.STATUS_IND%TYPE,
        I_rtv_seq_no     IN        RTV_DETAIL.SEQ_NO%TYPE,
        I_item           IN        RTV_DETAIL.ITEM%TYPE,
        I_publish_ind    IN        RTV_DETAIL.PUBLISH_IND%TYPE)

There are some tasks relating to streamlining the queue cleanup process that need to occur in ADDTOQ. The goal is to have at most one record on the queue for business transactions up until their initial publication.

• For header level insert messages (HDR_ADD), inserts a record in the RTVREQ_PUB_INFO table. The published flag is set to 'N'. The correct thread for the business transaction is calculated and written. Calls API_LIBRARY.RIB_SETTINGS to get the number of threads used for the publisher. Using the number of threads, and the business object id, calculates the thread value.

• For all records except header level inserts (HDR_ADD), the thread_no, initial_approval_ind, shipped_ind, and published indicator are queried from the RTVREQ_PUB_INFO table.

• If the business transaction has not been approved (initial_approval_ind = 'N') or it has already been shipped (shipped_ind = 'Y') and the triggering message is one of DTL_ADD, DTL_UPD, DTL_DEL, HDR_DEL, no processing will take place and the function exits.

• If the business transaction has not been approved (initial_approval_ind = 'N') and if it has not been already published (published = 'N'), no processing will take place and the function exits.

• For detail level messages deletes (DTL_DEL), the system only needs one (the most recent) record per detail in the RTVREQ_MFQUEUE. Any previous records that exist on the RTVREQ_MFQUEUE for the record that has been passed are deleted. If the publish_ind is 'N', the DTL_DEL message is not added to the queue.

• For detail level message deletes (DTL_UPD), the system only needs one DTL_UPD (the most recent) record per detail in the RTVREQ_MFQUEUE. Any previous DTL_UPD records that exist on the RTVREQ_MFQUEUE for the record that has been passed are deleted. The system does not want to delete any detail inserts that exist on the queue for the detail. The system ensures subscribers are not passed a detail modification message for a detail that they do not yet have.

• For header level delete messages (HDR_DEL), deletes every record in the queue for the business transaction.

• For header level update message (HDR_UPD), updates the RTVREQ_PUB_INFO.INITIAL_APPROVAL_IND to 'Y' if the business transaction is in approved status (status of '10').

• For header level update message (HDR_UPD), updates the RTVREQ_PUB_INFO.SHIPPED_IND to 'Y' if the business transaction is in shipped status (status of '15').

• For all records except header level inserts (HDR_ADD), inserts a record into the RTVREQ_MFQUEUE.

• For a full replacement message (FUL_REP), any previous records that exist on the RTVREQ_MFQUEUE for the record can be deleted.

Function Level Description - GETNXT

GETNXT (O_status_code   OUT    VARCHAR2,
        O_error_msg     OUT    VARCHAR2,
        O_message_type  OUT    VARCHAR2,
        O_message       OUT    RIB_OBJECT,
        O_bus_obj_id    OUT    RIB_BUSOBJID_TBL,
        O_routing_info  OUT    RIB_ROUTINGINFO_TBL,
        I_num_threads   IN     NUMBER DEFAULT 1,
        I_thread_val    IN     NUMBER DEFAULT 1)

LP_error_status is initialized to API_CODES.HOSPITAL at the beginning of GETNXT.

The RIB calls GETNXT to get messages. It performs a cursor loop on the unpublished records on the RTVREQ_MFQUEUE table (PUB_STATUS = 'U'). It only needs to execute one loop iteration in most cases. For each record retrieved, GETNXT gets the following:

1. A lock of the queue table for the current business object. The lock is obtained by calling the function LOCK_THE_BLOCK. If there are any records on the queue for the current business object that are already locked, the current message is skipped.

2. The published indicator from the RTVREQ_PUB_INFO table.

3. A check for records on the queue with a status of 'H'ospital. If there are any such records for the current business object, GETNXT raises an exception to send the current message to the Hospital.

The loop executes more than one iteration in the following cases:

1. When a header delete message exists on the queue for a business object that has not been initially published. In this case, it removes the header delete message from the queue and loops again.

2. The queue is locked for the current business object.

The information from the RTVREQ_MFQUEUE and RTVREQ_PUB_INFO table is passed to PROCESS_QUEUE_RECORD. PROCESS_QUEUE_RECORD builds the Oracle Object message to pass back to the RIB. If PROCESS_QUEUE_RECORD does not run successfully, GETNXT raises an exception.

If any exception is raised in GETNXT, including the exception raised by an unsuccessful call to PROCESS_QUEUE_RECORD, HANDLE_ERRORS is called.

Function Level Description - PUB_RETRY

PUB_RETRY(O_status_code         OUT      VARCHAR2,
          O_error_msg           OUT      VARCHAR2,
          O_message_type    IN  OUT      VARCHAR2,
          O_message             OUT      RIB_OBJECT,
          O_bus_obj_id      IN  OUT      RIB_BUSOBJID_TBL,
          O_routing_info    IN  OUT      RIB_ROUTINGINFO_TBL,
          I_REF_OBJECT      IN           RIB_OBJECT)

This procedure republishes the entity that failed to be published before. It is the same as GETNXT except that the record on RTVREQ_MFQUEUE to be published must match the passed in sequence number contained in the ROUTING_INFO.

Function Level Description - PROCESS_QUEUE_RECORD (local)

This function controls the building of Oracle Objects given the business transaction's key values and a message type. It contains all of the shared processing between GETNXT and PUB_RETRY.

It checks to see if the business object is being published for the first time. If the published_ind on the PUB_INFO table is 'N' or 'I', the business object is being published for the first time. If so, calls MAKE_CREATE.

Otherwise,

If the record from RTVREQ_MFQUEUE table is a full replace (FUL_REP)

• Calls BUILD_HEADER_OBJECT to build the Oracle Object to publish to the RIB. This will also populate the ROUTING_INFO.

• Calls BUILD_DETAIL_CHANGE_OBJECTS to build the detail portion of the Oracle Object

• Deletes the record from the RTVREQ_MFQUEUE table.

If the record from RTVREQ_MFQUEUE table is a header update (HDR_UPD).

• Calls BUILD_HEADER_OBJECT to build the Oracle Object to publish to the RIB. This will also populate the ROUTING_INFO.

• Updates RTVREQ_PUB_INFO with updated new header information

• Deletes the record from the RTVREQ_MFQUEUE table.

If the record from RTVREQ_MFQUEUE table is a detail add or update (DTL_ADD, DTL_UPD).

• Calls BUILD_HEADER_OBJECT to build the header portion of the Oracle Object to publish to the RIB. This also populates the ROUTING_INFO.

• Calls BUILD_DETAIL_CHANGE_OBJECTS to build the detail portion of the Oracle Object. This also takes care of any RTVREQ_MFQUEUE deletes.

If the record from RTVREQ_MFQUEUE table is a detail delete (DTL_DEL).

• Calls BUILD_HEADER_OBJECT to build the header portion of the Oracle Object to publish to the RIB. This also populates the ROUTING_INFO.

• Calls BUILD_DETAIL_DELETE_OBJECTS to build the detail portion of the Oracle Object. This also takes care of any RTVREQ_MFQUEUE deletes.

Function Level Description - MAKE_CREATE (local)

This function is used to create the Oracle Object for the initial publication of a business transaction.

• Calls BUILD_HEADER_OBJECT to build the Oracle Object to publish to the RIB. This also populates the ROUTING_INFO.

• Calls BUILD_DETAIL_OBJECTS with a message type of HDR_ADD to get all detail-level Oracle objects.

• Deletes the current record (HDR_ADD) from the RTVREQ_MFQUEUE. Deletes are done by rowids instead of business transaction keys to ensure that noting is deleted off the queue that has not been published.

• If the entire business transaction was added to the Oracle Object, also deletes the RTVREQ_MFQUEUE record that was picked up by GETNXT. If the entire business transaction was not published we need to leave something on the RTVREQ_MFQUEUE to ensure that the rest of it is picked up by the next call to GETNXT.

• The header and detail level Oracle Objects are combined and returned.

Function Level Description - BUILD_HEADER_OBJECT (local)

Take all necessary data from RTV_HEAD table and put it into a "RIB_RTVReqDesc_REC" and "RIB_RTVReqRef_REC" object.

Puts the location into the ROUTING_INFO.

Function Level Description - BUILD_DETAIL_CHANGE_OBJECTS (local)

Calls BUILD_DETAIL_OBJECTS.

BUILD_DETAIL_OBJECTS creates a table of RTVREQ_MFQUEUE ROWIDs to delete. Deletes these records.

Make sure to set LP_error_status to API_CODES.UNHANDLED_ERROR before any DML statements.

Function Level Description - BUILD_DETAIL_OBJECTS (local)

The function is responsible for building the detail level Oracle Objects. It builds as many detail Oracle Object as it can given the passed in message type and business object keys.

If the function is being called from MAKE_CREATE (HDR_ADD or FUL_REP):

• Selects all detail records from the business transaction. Creates Oracle Objects for details that are selected by calling BUILD_SINGLE_DETAIL.

• Ensures that RTVREQ_MFQUEUE is deleted from as needed. If there is more than one RTVREQ_MFQUEUE record for a detail level record, makes sure they all get deleted. The system only cares about current state, not every change. A table of ROWIDs to delete is created in BUILD_DETAIL_OBJECTS. The actual delete statement occurs in BUILD_DETAIL_CHANGE_OBJECTS using this table of ROWIDS.

If the function is not being called from MAKE_CREATE:

• Selects any records on the RTVREQ_MFQUEUE that are for the same business object ID. Fetches the records in order of seq_no on the MFQUEUE table.

• Ensures that RTVREQ_MFQUEUE is deleted from as needed. A table of ROWIDs to delete will be created in BUILD_DETAIL_OBJECTS. The actual delete statement occurs in BUILD_DETAIL_CHANGE_OBJECTS using this table of ROWIDS.

A concern here is making sure that the system does not delete information from the queue table that has not been published. For this reason, the system performs deletes by ROWID. The system also attempts to get everything in the same cursor to ensure that the message we published matches the deletes we perform from the RTVREQ_MFQUEUE table regardless of trigger execution during GETNXT calls.

Function Level Description - BUILD_DETAIL_DELETE_OBJECTS (local)

This function works the same way as BUILD_DETAIL_OBJECTS, except for the fact that a REF object is being created instead of a DESC object.

Function Level Description - BUILD_SINGLE_DETAIL (local)

Puts the inputted information in a RIB_RTVREQDTL_TBL object.

Function Level Description - LOCK_THE_BLOCK (local)

This function locks all queue records for the current business object. This is to ensure that GETNXT does not wait on any business processes that currently have the queue table locked and have not committed. This can occur because ADDTOQ, which is called from the triggers, deletes from the queue table for DTL_UPD, DTL_DEL, and HDR_DEL messages.

Function Level Description - DELETE_QUEUE_REC (local)

Deletes a record from the RTVREQ_MFQUEUE table, using the passed in sequence number.

Function Level Description - HANDLE_ERRORS (local)

HANDLE_ERRORS is called from GETNXT and PUB_RETRY when an exception is raised.

If the error is a non-fatal error, GETNXT passes the sequence number of the driving ITEMLOC_MFQUEUE record back to the RIB in the ROUTING_INFO. It sends back a status of 'H'ospital to the RIB as well. It then updates the status of the queue record to 'H'ospital, so that it will not get picked up again by the driving cursor in GETNXT.

If the error is a fatal error, a status of 'E'rror is returned to the RIB.

The error is considered non-fatal if no DML has occurred yet. Whenever DML has occurred, then the global variable LP_error_status is flipped from 'H'ospital to 'E'rror.

Function Level Description - BUILD_RTV_HEAD_CFA_EXT (local)

BUILD_RTV_HEAD_CFA_EXT is called from BUILD_HEADER_OBJECT to build the CFAs name-value pair for HDR_ADD and HRD_UPD messages and attaches it to "RIB_RTVReqDesc_REC" object.

Trigger Impact

Trigger name: EC_TABLE_RHD_AIUDR.TRG

Trigger file name: ec_table_rhd_aiudr.trg

Table: RTV_HEAD

• Inserts: Sends the appropriate column values to the ADDTOQ procedure in the MFM with the message type RMSMFM_RTVREQ.HDR_ADD.

• Updates: Sends the appropriate column values to the ADDTOQ procedure in the MFM with the message type RMSMFM_RTVREQ.HDR_UPD and optionally, RMSMFM_RTVREQ.FUL_REP based on system configuration.

• Deletes: Sends the appropriate column values to the ADDTOQ procedure in the MFM with the message type RMSMFM_RTVREQ.HDR_DEL.

A trigger on the RTV_HEAD table captures Inserts, Updates, and Deletes.

Trigger name: EC_TABLE_RDT_AIUDR.TRG

Trigger file name: ec_table_rdt_aiudr.trg

Table: RTV_DETAIL

A trigger on the RTV_DETAIL table captures Inserts, Updates, and Deletes.

• Inserts: Sends the appropriate column values to the ADDTOQ procedure in the MFM with the message type RMSMFM_RTVREQ.DTL_ADD and optionally, RMSMFM_RTVREQ.FUL_REP based on system configuration.

• Updates: Sends the appropriate column values to the ADDTOQ procedure in the MFM with the message type RMSMFM_RTVREQ.DTL_UPD and optionally, RMSMFM_RTVREQ.FUL_REP based on system configuration.

• Deletes: Sends the appropriate column values to the ADDTOQ procedure in the MFM with the message type RMSMFM_RTVREQ.DTL_DEL and optionally, RMSMFM_RTVREQ.FUL_REP based on system configuration.

Message XSD

Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
RtvReqCre	RTV Request Create Message	RTVReqDesc.xsd
RtvReqMod	RTV Request Modify Message	RTVReqDesc.xsd
RtvReqDel	RTV Request Delete Message	RTVReqRef.xsd
RtvReqDtlCre	RTV Request Detail Create Message	RTVReqDesc.xsd
RtvReqDtlMod	RTV Request Detail Modify Message	RTVReqDesc.xsd
RtvReqDtlDel	RTV Request Detail Delete Message	RTVReqRef.xsd
RtvReqFulRep	RTV Request Full Replacement Message	RTVReqDesc.xsd

Design Assumptions

• It is not possible for a detail trigger to accurately know the status of a header table.

• In order for the detail triggers to accurately know when to add a message to the queue, Merchandising should not allow approval of a business object while detail modifications are being made.

• It is not possible for a header trigger or a detail trigger to know the status of anything modified by GETNXT. If a header trigger or detail trigger is trying to delete queue records that GETNXT currently has locked, it will have to wait until GETNXT is finished and remove the lock. It is assumed that this time will be fairly short (at most 2-3 seconds). It is also assumed that this will occur rarely because it involves updating/deleting detail records on a business object that has already been approved. This also has to occur at the same time GETNXT is processing the current business object.

• Push off all DML statements as late as possible. Once DML statements have taken place, any error becomes a fatal error rather than a hospital error.

• RTV_HEAD_CFA_EXT changes will NOT trigger a FUL_REP message

Table Impact

TABLE	SELECT	INSERT	UPDATE	DELETE
RTVREQ_MFQUEUE	Yes	Yes	Yes	Yes
RTVREQ_PUB_INFO	Yes	Yes	Yes	Yes
RTV_HEAD	Yes	No	No	No
RTV_DETAIL	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No

Season and Phase Publication API

This section describes the season phase publicatoin API.

Functional Area

Foundation Data

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Merchandising publishes details about new and updated seasons and phases in order that external systems that use this information can be informed of the updates. Updates are provided synchronously in a near-real time manner.

New Seasons

Creating a new season triggers a message to be sent to notify external systems. The full details are sent for the new season as part of the create message, the season ID, description, start date and end date.

Table 2-76 Seasons Header

Message Element	Included?	Notes
Season ID	Always	This field contains the season identifier.
Season Description	Always	This field contains the description associated with the season. This is populated when creating and updating seasons.
Start Date	Always	This field contains the starting date for the season. This is populated when creating and updating seasons.
End Date	Always	This field contains the end date for the season. This is populated when creating and updating seasons.
Season Detail	Optional	Child Node – only included when adding/updating phases

Table 2-77 Seasons Detail

Message Element	Included?	Notes
Phase ID	Always	This field contains the phase identifier.
Phase Description	Always	This field holds the description of the phase.
Start Date	Always	This field contains the starting date for the phase.
End Date	Always	This field contains the end date for the phase.

Updated Seasons

When an existing season is updated, an update message is triggered to provide the details of the update. The update message, like create, will contain the full details in the message for all fields in the message. See above for the format of an update message.

New Phases

Creating a new phase triggers a message to be sent to notify external systems. The full details are sent for the phase as part of the create message, including the ID, description, start and end date for the phase, the season in which the phase belongs.

Updated Phases

When an existing phase is updated, an update message is triggered to provide the details of the update. The update message, like create, will contain the full details in the message for all fields in the message.

Deleted Seasons

When an existing season is deleted, this will also trigger a delete transaction to be sent to notify external systems that this season is no longer valid. The delete message will include only the ID of the season being deleted.

Table 2-78 Seasons Header

Message Element	Included?	Notes
Season ID	Always	This field contains the season identifier.
Season Details	Optional	Child Node – only included when deleting phases

Table 2-79 Seasons Detail

Message Element	Included?	Notes
Phase ID	Always	This field contains the phase identifier.

Deleted Phases

When an existing phase is deleted, this will also trigger a delete transaction to be sent to notify external systems that this phase is no longer valid. The delete message will include the ID of the phase being deleted and the ID of the season in which the phase belongs. See above for the structure of this message.

Error Handling

When the publication encounters a non-fatal error, messages continue to be processed. For the message where the error was encountered, a status of Hospital (H) is sent to the RIB and the status of the message in the queue is set to H. In case the error is a fatal error, a status of Error (E) is sent to the RIB and the next message in the queue is not retrieved until the error is resolved

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
seasoncre	Season Create	SeasonDesc.xsd
seasondtlcre	Phase Create	SeasonDesc.xsd
seasonmod	Season Modify	SeasonDesc.xsd
seasondtlmod	Phase Modify	SeasonDesc.xsd
seasondel	Season Delete	SeasonRef.xsd
seasondtldel	Phase Delete	SeasonRef.xsd

Store Publication API

This section describes the store publication API.

Functional Area

Foundation Data

Business Overview

Merchandising publishes data about stores in messages to the Oracle Retail Integration Bus (RIB) for other applications that needs to keep their locations synchronized with Merchandising. Merchandising publishes messages to the RIB to create, modify, and delete store events for all store types. These messages are triggered by insert/update/delete on the Merchandising STORE table and/or the ADDR table with module 'ST' (for store). The system only publishes the current state of the store, not every change.

Only the primary address and primary address type are published through this message, as it is assumed that integration subsystems only require one address.

Package Impact

File name: rmsmfm_stores/b.pls

Package Specification - Global Variables

FAMILY   CONSTANT RIB_SETTINGS.FAMILY%TYPE := 'STORES';
HDR_ADD   CONSTANT VARCHAR2(15) := 'storecre';
HDR_UPD   CONSTANT VARCHAR2(15) := 'storemod';
HDR_DEL   CONSTANT VARCHAR2(15) := 'storedel';
DTL_ADD   CONSTANT VARCHAR2(15) := 'storedtlcre';
DTL_UPD   CONSTANT VARCHAR2(15) := 'storedtlmod';
DTL_DEL   CONSTANT VARCHAR2(15) := 'storedtldel';
SHR_ADD   CONSTANT VARCHAR2(15) := 'storehrcre';
SHR_UPD   CONSTANT VARCHAR2(15) := 'storehrmod';

Public Type

TYPE STORE_KEY_REC IS RECORD
(
  STORE              NUMBER,
  ADDR_KEY           NUMBER,
  STORE_TYPE         VARCHAR2(1),
  STOCKHOLDING_IND   VARCHAR2(1),
  GROUP_ID           NUMBER,
  DAY_NO             NUMBER
);

Function Level Description - ADDTOQ

Function: ADDTOQ(O_error_msg           OUT  VARCHAR2,
                 I_message_type      IN     VARCHAR2,
                 I_store_key_rec     IN     STORE_KEY_REC,
                 I_addr_publish_ind  IN     ADDR.PUBLISH_IND%TYPE)

This public function puts a store message on STORE_MFQUEUE for publishing to the RIB. It is called from both store trigger and address trigger. The I_functional_keys will contain store and, optionally, addr_key.

Function Level Description - GETNXT

Procedure: GETNXT(O_status_code      OUT  VARCHAR2,
                  O_error_msg        OUT  VARCHAR2,
                  O_message_type     OUT  VARCHAR2,
                  O_message          OUT  RIB_OBJECT,
                  O_bus_obj_id       OUT  RIB_BUSOBJID_TBL,
                  O_routing_info     OUT  RIB_ROUTINGINFO_TBL,
                  I_num_threads   IN      NUMBER DEFAULT 1,
                  I_thread_val    IN      NUMBER DEFAULT 1)

This public procedure is called from the RIB to get the next messages. It performs a cursor loop on the unpublished records on the STORE_MFQUEUE table (PUB_STATUS = 'U').

If any exception is raised in GETNXT, including the exception raised by an unsuccessful call to PROCESS_QUEUE_RECORD, HANDLE_ERRORS is called.

Function Level Description - PUB_RETRY

Procedure: PUB_RETRY(O_status_code      OUT   VARCHAR2,
                     O_error_msg        OUT   VARCHAR2,
                     O_message          OUT   RIB_OBJECT,
                     O_message_type  IN OUT   VARCHAR2,
                     O_bus_obj_id    IN OUT   RIB_BUSOBJID_TBL,
                     O_routing_info  IN OUT   RIB_ROUTINGINFO_TBL)

This public procedure performs the same tasks as GETNXT except that it only loops for a specific row in the STORE_MFQUEUE table. The record on STORE_MFQUEUE must match the passed in sequence number (contained in the ROUTING_INFO).

Function Level Description - PROCESS_QUEUE_RECORD (local)

This private function controls the building of Oracle Objects (DESC or REF) given the business transaction's key values and a message type. It contains all of the shared processing between GETNXT and PUB_RETRY.

Function Level Description - MAKE_CREATE (local)

This private function is used to create the Oracle Object for the initial publication of a business transaction. I_business_object contains the store header key values (store). I_rowid is the rowid of the store_mfqueue row fetched from GETNXT.

Function Level Description - BUILD_HEADER_OBJECT (local)

This private function accepts store header key value (store), builds and returns a header level DESC Oracle Object.

This overloaded private function accepts store header key value (store), builds and returns a header level REF Oracle Object.

This function calls the BUILD_STORE_CFA_EXT to build the RIB_CustFlexAttriVo_TBL for store customer attributes and attach it to the header level REF Oracle Object.

Function Level Description - BUILD_DETAIL_OBJECTS (local)

The private function is responsible for building detail level DESC Oracle Objects. It builds as many detail Oracle Object as it can given the passed in message type and business object keys (store).

Function Level Description - BUILD_SINGLE_DETAIL (local)

This private function takes in an address record and builds a detail level Oracle Object. Also find out if the address is the primary address of the primary address type and set the DESC Oracle Object accordingly.

This function calls the BUILD_ADDR_CFA_EXT to build the RIB_CustFlexAttriVo_TBL for store's address customer attributes and attach it to the detail level REF Oracle Object.

Function Level Description - BUILD_DETAIL_CHANGE_OBJECTS (local)

This private function builds a DESC Oracle Object to publish to the RIB for detail create, detail update, header update, store hour create, and store hour mod messages (DTL_ADD, DTL_UPD, HDR_UPD,SHR_ADD, SHR_UPD). I_business_obj contains the header level key values (store).

Function Level Description - BUILD_DETAIL_DELETE_OBJECTS (local)

This private function builds a REF Oracle Object to publish to the RIB for detail delete messages (DTL_DEL). I_business_obj contains the header level key values (store).

Function Level Description - LOCK_THE_BLOCK (local)

This private function locks all queue records for the current business object (store). This is to ensure that GETNXT and PUB_RETRY do not wait on any business processes that currently have the queue table locked and have not committed. This can occur because ADDTOQ, which is called from the triggers, deletes from the queue table for DTL_UPD, DTL_DEL, and HDR_DEL messages.

Function Level Description - HANDLE_ERRORS (local)

This private procedure is called from GETNXT and PUB_RETRY when an exception is raised. I_seq_no is the sequence number of the driving STORE_MFQUEUE record. I_function_keys contains detail level key values (store, addr_key).

If the error is a non-fatal error, HANDLE_ERRORS passes the sequence number of the driving STORE_MFQUEUE record back to the RIB in the ROUTING_INFO. It sends back a status of 'H' - Hospital to the RIB as well. It then updates the status of the queue record to 'H', so that it will not get picked up again by the driving cursor in GETNXT.

If the error is a fatal error, a status of 'E' - Error is returned to the RIB. The error is considered non-fatal if no DML has occurred yet. Whenever DML has occurred, then the global variable LP_error_status is flipped from 'H' to 'E'.

Function Level Description - BUILD_STORE_CFA_EXT (local)

This private function will build and return entity's customer attributes from STORE_CFA_EXT table.

Function Level Description - BUILD_ ADDR _CFA_EXT (local)

This private function will build and return store's address customer attributes of the entity from ADDR_CFA_EXT table for Store.

Function Level Description - BUILD_STORE_HOURS_OBJECT (local)

This private function is responsible for building store hour level DESC Oracle Objects. It builds as many store hour Oracle Object as it can, given the passed-in message type and business object keys (store).

Message XSD

Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
StoreCre	Store Create Message	StoreDesc.xsd
StoreMod	Store Modify Message	StoreDesc.xsd
StoreDel	Store Delete Message	StoreRef.xsd

Table Impact

TABLE	SELECT	INSERT	UPDATE	DELETE
STORE_PUB_INFO	Yes	Yes	Yes	Yes
ADDR	Yes	No	Yes	No
STORE_MFQUEUE	Yes	Yes	Yes	Yes
ADD_TYPE_MODULE	Yes	No	No	No
STORE	Yes	No	No	No
STORE_CFA_EXT	Yes	No	No	No
ADDR_CFA_EXT	Yes	No	No	No
STORE_HOURS	Yes	No	No	No

Design Assumptions

Push off all DML statements as late as possible. Once DML statements have taken place, any error becomes a fatal error rather than a hospital error.

Transfers Publication API

This section describes the transfers publication API.

Functional Area

Transfer

Business Overview

A transfer is a movement of stock on hand from one stockholding location within the company to another.

The transfer publication processing publishes transfers in 'Approved' status.

Transfers consist of header level information in which source and destination locations are specified, and detail information regarding what items and the quantity of each item is to be transferred. Both of the main transfer tables, TSFHEAD and TSFDETAIL, include triggers that track inserts, deletes, and modifications. These triggers insert or update into TSF_MFQUEUE or TRANSFERS_PUB_INFO tables. The transfer family manager is responsible for pulling transfer information from this queue and sending it to the external system(s) at the appropriate time and in the correct sequence.

The transfer messages that are published by the family manager vary. A complete message including header information, detail information, and component ticketing information (if applicable) is created when a transfer is approved. When the transfer is unapproved, the RIB processes it as a TransferDel message when publishing it to external systems. When the transfer is re-approved, the transfer is processed as a new transfer for publishing.

For a customer order transfer (tsf_type = 'CO'), customer related information is pulled from ORDCUST table. Additional trigger is put on ORDCUST to capture delivery and billing change for the customer order transfer through the transfer message family.

When publishing a header mod or a detail create, detail mod, detail delete message, a second full replacement message with message type 'transferfulrep' will be published from Merchandising if system option PUB_FULL_OBJECTS_IND is configured to be Y on the PRODUCT_CONFIG_OPTIONS table. This message payload will contain a full snapshot of the transfer. Based on the message type, RIB will route the full replacement message to appropriate applications.

Package Impact

This section describes the package impact.

Business Object ID

Transfer number

Create Header

1. Prerequisites: None.

2. Activity Detail: The first step to creating a transfer is creating the header level information.

3. Messages: When a transfer is created, a record is inserted into TRANSERS_PUB_INFO table and is not published onto the queue until the transfer has been approved.

Approve

1. Prerequisites: A transfer must exist and have at least one detail before it can be approved.

2. Activity Detail: Approving a transfer changes the status of the transfer. This change in status signifies the first time systems external to Merchandising will have an interest in the existence of the transfer, so this is the first part of the life cycle of a transfer that is published.

3. Messages: When a transfer is approved, a "TransferHdrMod" message is inserted into the queue with the initial_approval_ind on the TRANSFER_PUB_INFO table set to 'Y', signifying that the transfer was approved. The family manager uses this indicator to create a hierarchical message containing a full snapshot of the transfer at the time the message is published.

Modify Header

1. Prerequisites: The transfer header can only be modified when the status is not approved. Once the transfer is approved, the only fields that are modifiable are the status field and the comments field.

2. Activity Detail: The user is allowed to modify the header but only certain fields at certain times. If a transfer is in input status the 'to and from' locations may be modified until details have been added. Once details have been added, the locations are disabled. The freight code is modifiable until the transfer has been approved. Comments can be modified at any time.

3. Messages: When the status of the header is either changed to 'C'losed or 'A'pproved, a message (TransferHdrMod) is inserted into the queue. (Look above at Approve activity and below at Close activity for further details). For any TransferHdrMod other than approving or unapproving a transfer, a second full replacement message (TransferFulRep) is inserted into the queue if PUB_FULL_OBJECTS_IND on PRODUCT_CONFIG_OPTIONS is configured to be Y. Since approving and unapproving a transfer will result in publishing a transfer create (TransferCre) and transfer delete (TransferDel) message, a second full replacement message is not needed.

Create Details

1. Prerequisites: A transfer header record must exist before transfer details can be created.

2. Activity Detail: The user is allowed to add items to a transfer but only until it has been approved. Once a transfer has been approved, details can longer be added unless the transfer is set back to Input status.

3. Messages: No messages are created on the queue until the transfer is approved. When a detail create (TransferDtlCre) message is added to the queue, a second full replacement message (TransferFulRep) is inserted into the queue if PUB_FULL_OBJECTS_IND on PRODUCT_CONFIG_OPTIONS is configured to be Y.

Modify Details

1. Prerequisites: Only modifications to transfer quantities are sent to the queue, and only when the transfer quantity is decreased manually, and not because of an increase in cancelled quantity will it be sent to the queue.

2. Activity Detail: The user is allowed to change transfer quantities provided they are not reduced below those already shipped. The transfer quantity can also be decreased by an increase in the cancelled quantity, which is always initiated by the external system. This change, then, would be of no interest to the external system because it was driven by it.

3. Messages: No messages are created on the queue until the transfer is approved. When a detail create (TransferDtlCre) message is added to the queue, a second full replacement message (TransferFulRep) is inserted into the queue if PUB_FULL_OBJECTS_IND on PRODUCT_CONFIG_OPTIONS is configured to be Y.

Delete Details

1. Prerequisites: Only a detail that has not been shipped may be deleted, and it cannot be deleted if it is currently being worked on by an external system. A user is not allowed to delete details from a closed transfer.

2. Activity Detail: A user is allowed to delete details from a transfer but only if the item has not been shipped.

3. Messages: No messages are created on the queue until the transfer is approved. When a detail delete (TransferDtlDel) message is added to the queue, a second full replacement message (TransferFulRep) is inserted into the queue if PUB_FULL_OBJECTS_IND on PRODUCT_CONFIG_OPTIONS is configured to be Y.

Close

1. Prerequisites: A transfer must be in shipped status before it can be closed, and it cannot be in the process of being worked on by an external system.

2. Activity Detail: Closing a transfer changes the status, which prevents any further modifications to the transfer. When a transfer is closed, a message is published to update the external system(s) that the transfer has been closed and no further work (in Merchandising) is performed on it.

3. Messages: Closing a transfer queues a "TransferHdrMod" request. This is a flat message containing a snapshot of the transfer header information at the time the message is published. Additionally, a second full replacement message (TransferFulRep) is inserted into the queue if PUB_FULL_OBJECTS_IND on PRODUCT_CONFIG_OPTIONS is configured to be Y.

Delete

1. Prerequisites: A transfer can only be deleted when it is still in approved status or when it has been closed.

2. Activity Detail: Deleting a transfer removes it from the system. External systems are notified by a published Delete message that contains the number of the transfer to be deleted.

3. Message: When a transfer is deleted, a "TransferDel", which is a flat notification message, is queued.

Package name: RMSMFM_TRANSFERS

Spec file name: rmsmfm_transferss.pls

Body file name: rmsmfm_transfersb.pls

Package Specification - Global Variables

FAMILY      VARCHAR2(64) := 'transfers';

HDR_ADD     VARCHAR2(64) := 'TransferCre';
HDR_UPD     VARCHAR2(64) := 'TransferHdrMod';
HDR_DEL     VARCHAR2(64) := 'TransferDel';
HDR_UNAPRV  VARCHAR2(64) := 'TransferUnapp';
DTL_ADD     VARCHAR2(64) := 'TransferDtlCre';
DTL_UPD     VARCHAR2(64) := 'TransferDtlMod';
DTL_DEL     VARCHAR2(64) := 'TransferDtlDel';

Function Level Description - ADDTOQ

ADDTOQ (O_error_mesage     OUT    VARCHAR2,
        I_message_type     IN     VARCHAR2,
        I_tsf_no           IN     tsfhead.tsf_no%TYPE,
        I_tsf_type         IN     tsfhead.tsf_type%TYPE,
        I_tsf_head_status  IN     tsfdetail.status%TYPE,
        I_item             IN     tsfdetail.item%TYPE,
        I_publish_ind      IN     tsfdetail.publish_ind%TYPE)

This function is called by both the tsfhead trigger and the tsfdetail trigger, the EC_TABLE_THD_AIUDR and EC_TABLE_TDT_AIUDR respectively.

• Book transfers, non-sellable transfers and externally generated transfers (except for delete messages) are never published to external systems.

• For header level insert messages (HDR_ADD), inserts a record in the TRANSFERS_PUB_INFO table. The published flag is set to 'N'. The correct thread for the Business transaction is calculated and written. The functionAPI_LIBRARY.RIB_SETTINGS is called to get the number of threads used for the publisher. Using the number of threads, and the Business object ID, the thread value is calculated.

• For all records except header level inserts (HDR_ADD), the thread_no and initial_approval_ind are queried from the TRANSFERS_PUB_INFO table.

• If the Business transaction has not been published before (published = 'N') and the triggering message is one of DTL_ADD, DTL_UPD, DTL_DEL, HDR_DEL, HDR_UPD, HDR_UNAPPRV, FUL_REP, no processing will take place and the function exits. For a HDR_DEL message, the transfers_pub_info record is deleted.

• For detail level message deletes (DTL_DEL), only the most recent record per detail in the TSF_MFQUEUE is required. Any previous records that exist on the TSF_MFQUEUE for the record that has been passed are deleted.

• For detail level message updates (DTL_UPD), only the most recent DTL_UPD record per detail in the TSF_MFQUEUE is required. Any previous DTL_UPD records that exist on the TSF_MFQUEUE for the record that has been passed are deleted. The system does not want to delete any detail inserts that exist on the queue for the detail. It ensures subscribers have not passed a detail modification message for a detail that they do not yet have.

• For header level delete messages (HDR_DEL), deletes every record in the queue for the Business transaction.

• For header level update message (HDR_UPD), updates the TRANSFERS_PUB_INFO.INITIAL_APPROVAL_IND to 'Y' if the Business transaction is in approved status.

• For all records except header level inserts (HDR_ADD), inserts a record into the TSF_MFQUEUE.

• For a full replacement message (FUL_REP), any previous records that exist on the TSF_MFQUEUE for the record can be deleted.

It returns a status code of API_CODES.SUCCESS if successful, API_CODES.UNHANDLED_ERROR if not.

Function Level Description - GETNXT

GETNXT (O_status_code         OUT    VARCHAR2,
        O_error_msg           OUT    VARCHAR2,
        O_message_type        OUT    VARCHAR2,
        O_message             OUT    RIB_OBJECT,
        O_bus_obj_id          OUT    RIB_BUSOBJID_TBL,
        O_routing_info        OUT    RIB_ROUTINGINFO_TBL,
        I_num_threads         IN     NUMBER DEFAULT 1,
        I_thread_val          IN     NUMBER DEFAULT 1)

The RIB calls GETNXT to get messages. It performs a cursor loop on the unpublished records on the TSF_MFQUEUE table (PUB_STATUS = 'U'). It only needs to execute one loop iteration in most cases. For each record retrieved, GETNXT gets the following:

1. A lock of the queue table for the current Business object. The lock is obtained by calling the function LOCK_THE_BLOCK. If there are any records on the queue for the current Business object that are already locked, the current message is skipped.

2. The published indicator from the TRANSFERS_PUB_INFO table.

3. A check for records on the queue with a status of 'H'ospital. If there are any such records for the current Business object, GETNXT raises an exception to send the current message to the Hospital.

The loop executes more than one iteration for the following cases:

1. When a header delete message exists on the queue for a business object that has not been initially published. In this case, it removes the header delete message from the queue and loop again.

2. A detail delete message exists on the queue for a detail record that has not been initially published. In this case, it removes the detail delete message from the queue and loop again.

3. The queue is locked for the current Business object.

The information from the TSF_MFQUEUE and TRANSFERS_PUB_INFO table is passed to PROCESS_QUEUE_RECORD. PROCESS_QUEUE_RECORD builds the Oracle Object message to pass back to the RIB. If PROCESS_QUEUE_RECORD does not run successfully, GETNXT raises an exception.

If any exception is raised in GETNXT, including the exception raised by an unsuccessful call to PROCESS_QUEUE_RECORD, HANDLE_ERRORS is called.

Function Level Description - PUB_RETRY

PUB_RETRY(O_status_code         OUT      VARCHAR2,
          O_error_msg           OUT      VARCHAR2,
          O_message_type    IN  OUT      VARCHAR2,
          O_message             OUT      RIB_OBJECT,
          O_bus_obj_id      IN  OUT      RIB_BUSOBJID_TBL,
          O_routing_info    IN  OUT      RIB_ROUTINGINFO_TBL,
          I_REF_OBJECT      IN           RIB_OBJECT)

This procedure republishes the entity that failed to be published before. It is the same as GETNXT except that the record on TSF_MFQUEUE to be published must match the passed in sequence number contained in the ROUTING_INFO.

Function Level Description - PROCESS_QUEUE_RECORD (local)

This function controls the building of Oracle Objects given the business transaction's key values and a message type. It contains all of the shared processing between GETNXT and PUB_RETRY.

If the message type is HDR_DEL or HDR_UNAPRV and it has not been published:

• Calls DELETE_QUEUE_REC to delete the record from TSF_MFQUEUE. Also deletes from TRANSFER_PUB_INFO.

  If the message type is HDR_DEL and the record has been published:

• Generates a "flat" file to be sent to the RIB. Call DELETE_QUEUE_REC to delete from the queue.

If the message type is HDR_UNAPRV:

• Processes it just like a hdr_del except the published indicator on TRANSFERS_PUB_INFO is set to 'N'.

If the transfer has not been published:

• Calls MAKE_CREATE to publish the entire transfer. as a HDR_ADD message.

If the message type is FUL_REP:

• Calls BUILD_HEADER_OBJECT and BUILD_DETAIL_CHANGE_OBJECTS to publish the entire transfer. Call DELETE_QUEUE_REC to delete the record from TSF_MFQUEUE.

If the record from TSF_MFQUEUE table is HDR_UPD:

• Calls BUILD_HEADER_OBJECT to build the Oracle Object to publish to the RIB and deletes from the queue.

If the record from TSF_MFQUEUE table is DTL_ADD or DTL_UPD:

• Calls BUILD_HEADER_OBJECT and BUILD DETAIL_CHANGE_OBJECTS to build the Oracle Object to publish to the RIB.

If the record from TSF_MFQUEUE table is a detail delete (DTL_DEL):

• Calls BUILD HEADER_OBJECT and BUILD_DETAIL_DELETE_OBJECTS to build the Oracle Object to publish to the RIB.

This function puts the following in the routing info (RIB_ROUTING_INFO_TBL):

• 'from_phys_loc' - transfer from location. In case of warehouse, it's the physical warehouse.

• 'from_phys_loc_type' - transfer from location type - 'S' for store, 'W' for warehouse, 'E' for external finisher.

• 'to_phys_loc' - transfer to location. In case of warehouse, it's the physical warehouse.

• 'to_phys_loc_type' - transfer to location type. In case of store, 'S' for physical store (i.e. stockholding company store), 'V' for virtual store (i.e. non-stockholding company store).

Function Level Description - MAKE_CREATE (local)

This function is used to create the Oracle Object for the initial publication of a business transaction. It combines the current message and all previous messages with the same key in the queue table to create the complete hierarchical message. It first creates a new message with the hierarchical document type. It then gets the header create message and adds it to the new message. The remainder of this procedure gets each of the details grouped by their document type and adds them to the new message. When it is finished creating the new message, it deletes all the records from the queue with a sequence number less than or equal to the current records sequence number. This new message is passed back to the RIB. The MAKE_CREATE function will not be called unless the initial_approval_ind is 'Y'es and published is 'N'o on transfers_pub_info (meaning the transfer has been approved but not yet published, and it is ready to be published for the first time to the external system(s)).

Function Level Description - BUILD_HEADER_OBJECT (local)

Accepts header key values, performs necessary lookups, builds and returns a header level Oracle Object.

Function Level Description - BUILD_DETAIL_OBJECTS (local)

This function is responsible for fetching the detail info and ticket type to be sent to RWMS. The logic that gets the detail info as well as the ticket type was separated to remove the primary key constraint.

Function Level Description - BUILD_SINGLE_DETAIL (local)

Accept inputs and build a detail level Oracle Object. Perform any lookups needed to complete the Oracle Object.

Function Level Description - GET_RETAIL (local)

Gets the price and selling unit of measure (UOM) of the item.

Function Level Description - GET_GLOBALS (local)

Get all the system options and variables needed for processing.

Function Level Description - BUILD_DETAIL_CHANGE_OBJECTS (local)

Calls BUILD_DETAIL_OBJECT to publish the record. Deletes the record from TSF_MFQUEUE.

Function Level Description - BUILD_DETAIL_DELETE_OBJECTS (local)

Either pass in a header level ref Oracle Object or build a header level ref Oracle Object.

Performs a cursor for loop on TSF_MFQUEUE and builds detail ref Oracle Objects.

Deletes from TSF_MFQUEUE when done.

Function Level Description - LOCK_THE_BLOCK (local)

This function locks all queue records for the current business object. This is to ensure that GETNXT does not wait on any business processes that currently have the queue table locked and have not committed. This can occur because ADDTOQ, which is called from the triggers, deletes from the queue table for DTL_UPD, DTL_DEL, and HDR_DEL messages.

Function Level Description - DELETE_QUEUE_REC (local)

This procedure deletes a specific record from TSF_MFQUEUE. It deletes based on the sequence number passed in.

Function Level Description - HANDLE_ERRORS (local)

HANDLE_ERRORS is called from GETNXT and PUB_RETRY when an exception is raised. The function was updated to conform with the changes made to the ADDTOQ function.

Trigger Impact

A trigger on the TSFHEAD and TSFDETAIL exists to capture Inserts, Updates, and Deletes.

Trigger name: EC_TABLE_THD_AIUDR.TRG

Trigger file name: ec_table_thd_aiudr.trg

Table: TSFHEAD

• Inserts: Sends the tsf_no and tsf_type level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_Transfers.HDR_ADD.

• Updates: Sends the tsf_no and tsf_type level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_Transfers.HDR_UPD and optionally, RMSMFM_Transfers.FUL_REP based on system configuration.

• Deletes: Sends the tsf_no and tsf_type level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_Transfers.HDR_DEL.

Trigger name: EC_TABLE_TDT_AIUDR.TRG

Trigger file name: ec_table_tdt_aiudr.trg

Table: TSFDETAIL

• Inserts: Sends the tsf_no and item level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_Transfers.DTL_ADD and optionally, RMSMFM_Transfers.FUL_REP based on system configuration.

• Updates: Sends the tsf_no and item level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_Transfers.DTL_UPD and optionally, RMSMFM_Transfers.FUL_REP based on system configuration.

• Deletes: Sends the tsf_no and item level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_Transfers.DTL_DEL and optionally, RMSMFM_Transfers.FUL_REP based on system configuration.

Trigger name: EC_TABLE_ORC_AUR.TRG

Trigger file name: ec_table_orc_aur.trg

Table: ORDCUST

• Updates: For ORDCUST associated with a published 'CO' transfer, send the tsf_no and tsf_type level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_Transfers.HDR_UPD and optionally, RMSMFM_Transfers.FUL_REP based on system configuration.

Message XSD

Here are the filenames that correspond with each message type. See Oracle Retail Integration Bus documentation for each message type in order to get a detailed picture of the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
TransferCre	Transfer Create Message	TsfDesc.xsd
TransferHdrMod	Transfer Modify Message	TsfDesc.xsd
TransferDel	Transfer Delete Message	TsfRef.xsd
TransferDtlCre	Transfer Detail Create Message	TsfDesc.xsd
TransferDtlMod	Transfer Detail Modify Message	TsfDesc.xsd
TransferDtlDel	Transfer Detail Delete Message	TsfRef.xsd
transferfulrep	Transfer Full Replacement Message	TsfDesc.xsd

Design Assumptions

• After a transfer has been approved, Oracle Retail assumes the freight code of the transfer (on the TSFHEAD table) cannot be updated.

• One of the primary assumptions in the current approach is that ease of code will outweigh performance considerations. It is hoped that the 'trickle' nature of the flow of data will decrease the need to dwell on performance issues and instead allow developers to code in the easiest and most straight forward manner.

• The adaptor is only set up to call stored procedures, not stored functions. Any public program then needs to be a procedure.

• TSFHEAD_CFA_EXT changes will NOT trigger a FUL_REP message.

Table Impact

TABLE	SELECT	INSERT	UPDATE	DELETE
TRANSFERS_PUB_INFO	Yes	No	No	No
TSF_MFQUEUE	Yes	No	No	No
TSFDETAIL	Yes	No	No	No
TSFHEAD	Yes	No	No	No
WH	Yes	No	No	No
ORDCUST	Yes	No	No	No
ORDCUST_DETAIL	Yes	No	No	No
ITEM_LOC	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
ITEM_TICKET	Yes	No	No	No
V_PACKSKU_QTY	Yes	No	No	No
CODE_DETAIL	Yes	No	No	No
SYSTEM_OPTIIONS	Yes	No	No	No
RIB_SETTINGS	Yes	No	No	No

UDA Publication API

This section describes the UDA publication API.

Functional Area

Foundation Data

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Merchandising publishes details about user-defined attributes (UDAs) to the Oracle Retail Integration Bus (RIB). UDAs provide a method for defining attributes and associating the attributes with specific items, items on an item list, or items in a specific department, class, or subclass. UDAs are useful for information and reporting purposes. Unlike traits or indicators, UDAs are not interfaced with external systems. UDAs do not have any programming logic associated with them. UDA messages are specific to basic UDA identifiers and values defined in Merchandising. The UDAs can be displayed in one or more of three formats: Dates, Freeform Text, or a List of Values (LOV).

New UDAs

Creating a new UDAs triggers a message to be sent via the RIB to notify external systems. The full details are sent for the new UDA as part of the create message: the UDA ID, description, display type, data type, data length and single value indicator.

Updated UDAs

When an existing UDA is updated, an update message is triggered to provide the details of the update via the RIB. The update message, like create, will contain the full details in the message for all fields in the message.

Deleted UDAs

When an existing UDA is deleted, this will also trigger a delete transaction to be sent via the RIB to notify external systems that this UDA is no longer valid. The delete message will include only the UDA ID, UDA value and description of the UDA being deleted.

New UDA Details

Creating a new UDA details triggers a message to be sent via the RIB to notify external systems. The UDA value and description are sent for the existing UDA as part of the create message.

Updated UDAs

When an existing UDA detail is updated, an update message is triggered to provide the details of the update via the RIB. The update message, will contain the UDA value and description in the message.

Deleted UDAs

When an existing UDA detail is deleted, this will also trigger a delete transaction to be sent via the RIB to notify external systems that this UDA detail is no longer valid. The delete message will include only the UDA ID and UDA value being deleted.

Error Handling

When the publication encounters a non-fatal error, messages continue to be processed.

For the message where the error was encountered, a status of Hospital (H) is sent to the RIB and the status of the message in the queue is set to H. In case the error is a fatal error, a status of Error (E) is sent to the RIB and the next message in the queue is not retrieved until the error is resolved

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XML)
UdaHdrCre	UDA Header Create	UDADesc.xsd
UDAValCre	UDA Detail Create	UDAValDesc.xsd
UDAHdrMod	UDA Header Modify	UDADesc.xsd
UDAValMod	UDA Detail Modify	UDAValDesc.xsd
UDAHdrDel	UDA Header Delete	UDARef.xsd
UDAValDel	UDA Detail Delete	UDAValRef.xsd

Vendor Publication API

This section describes the vendor publication API.

Functional Area

Suppliers

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

This API publishes suppliers and supplier address information through the RIB to external systems. This information is further subscribed by integrating systems like Oracle Retail Store Inventory and Operations Cloud Service (SIOCS). Supplier information is published when new supplier sites are created, updates are made to existing sites, or existing supplier sites are deleted. Similarly, addresses are published when they are added, modified, or deleted. The address types that are published as part of this message are:

• Returns (3)

• Order (4)

• Invoice (5)

Only supplier site level information is published. The supplier level information will not be published.

Vendor Creation

Supplier sites can be set up in Merchandising though the application UI, or through subscription of vendor information from external systems. Both methods result in this message being published when a supplier is created. The vendor creation message is published from Merchandising only after the supplier site has been completely set up, including the mandatory addresses and its org unit. Once all these criteria are met for a valid create message, the messages will be combined and sent to RIB. Both active and inactive supplier sites are published.

Vendor Header Modification

Updating an existing supplier site will trigger a header modification message to notify the integrating systems. This message will contain only the header level information that changed.

Table 2-80 Create and Update

Message Element	Included?	Notes
Vendor Header	Always	Child node
Vendor Address	Optional	Child node
Vendor Org Unit	Optional	Child node

Table 2-81 Vendor Header Create and Update

Message Element	Included?	Notes
Supplier	Always	Unique identifying number for a supplier site within the system.
Supplier Name	Always	This field contains the supplier site name.
Supplier Name Secondary	Optional	This type can hold secondary name for the supplier site with a max length of 240 characters.
Contact Name	Always	This field contains the name of the supplier representative contact for this site.
Contact Phone	Always	This field contains a telephone number for the supplier's representative contact.
Contact Fax	Optional	This field contains a fax number for the supplier's representative contact.
Contact Pager	Optional	This field contains a pager number for the supplier's representative contact.
Supplier Status	Always	This field contains the status of the supplier site. Valid values are: A - Active I - Inactive
QC Ind	Always	This field determines whether orders from this supplier will require quality control.
QC Percentage	Optional	This field contains the percentage of items per receipt that will be marked for quality checking.
QC Frequency	Optional	This field contains the frequency for which items per receipt will be marked for quality checking.
VC Ind	Always	This field determines whether orders from this supplier will require vendor control.
VC Percentage	Optional	This field contains percentage of items per receipt that will be marked for vendor checking.
VC Frequency	Optional	This field contains the frequency for which items per receipt that will be marked for vendor checking.
Currency Code	Always	This field contains code identifying the currency the supplier site uses for business transactions.
Language	Optional	This field contains the supplier’s preferred language.
Terms	Always	This field contains an indicator identifying the purchase terms that will default when an order is created for the supplier site. These terms specify when payment is due and if any discounts exist for early payment.
Freight Terms	Always	This field contains code indicating what freight terms will default when an order is created for the supplier site.
Return Allow Indicator	Always	This field indicates whether the supplier site will accept returns. Valid values are Yes (Y) or No (N).
Return Authorization Required	Always	This field indicates if returns must be accompanied by an authorization number when sent back to the vendor.
Returns Minimum Amount	Optional	This field contains a value if the supplier site requires a minimum merchandise value to be returned to accept the return. Returns of less than this amount will not be processed by the system. This field is stored in the supplier's currency.
Return Courier	Optional	This field contains the name of the courier that should be used for returns to the supplier site.
Handling Percentage	Optional	This field contains the default percent to be multiplied by the return’s total cost to determine the handling cost for the return.
EDI PO Ind	Always	This field indicates whether purchase orders will be sent to the supplier via EDI.
EDI PO Change	Always	This field indicates whether purchase order changes will be sent to the supplier via EDI.
EDI PO Confirmation	Always	This field indicates whether acknowledgements of purchase orders will be sent from the supplier via EDI.
EDI ASN	Always	This field indicates whether the supplier will send Advance Shipment Notifications electronically.
EDI Sales Report Frequency	Optional	This field contains the EDI sales report frequency for the supplier. Valid values are weekly (W) or daily (D).
EDI Supplier Availability Indicator	Always	This field indicates whether the supplier will send availability via EDI.
EDI Contract Indicator	Always	This field indicates whether the supplier site supports contract ordering sent via EDI.
EDI Invoice Indicator	Always	This field indicates whether invoices, debit memos, and credit note requests will be sent to/from the supplier via EDI.
Cost Change Percentage Variance	Optional	This field contains a percent that determines whether a cost change can be auto approve via induction. If the cost change falls within these boundaries, it will be approved when uploaded.
Cost Change Amount Variance	Optional	This field contains an amount (in supplier currency) that determines whether a cost change can be auto approve via induction. If the cost change falls within these boundaries, it will be approved when uploaded.
Replenishment Approval Indicator	Always	This field indicates whether contract orders created via replenishment should be created in Approved status.
Ship Method	Optional	This field contains the default method used to ship the items on the purchase order from the supplier site. Valid values are held in code type SHPM.
Payment Method	Optional	This field indicates the default method for how purchase orders for this site will be paid. Valid options are: LC - Letter of Credit WT - Wire Transfer OA - Open Account
Contact Telex	Optional	This field contains a telex number for the supplier's representative contact.
Contact Email	Optional	This field contains an email address for the supplier's representative contact.
Settlement Code	Always	This field indicates which payment process method is used for the supplier. Valid values are N/A (N) or Evaluated Receipts Settlement (E).
Pre-Mark Indicator	Always	This field indicates whether the supplier site supports pre-marking containers for cross dock orders.
Auto Approved Invoice Indicator	Always	This field indicates whether the supplier's invoices can be automatically approved for payment.
Debit Memo Code	Optional	This field indicates when a debit memo will be sent to the supplier site to resolve a discrepancy. Valid values are: Y - if debit memos are always to be sent L - if debit memos are used only if a credit note is not sent by the invoice due date N - if debit memos are never sent
Freight Charge Indicator	Always	This field indicates whether a supplier site can charge freight costs.
Auto Approve Debit Memo Indicator	Always	This field indicates whether debit memos sent to the supplier site can be automatically approved on creation.
Inventory Management Level	Optional	This field indicates the level for managing supplier inventory information. Valid values are: supplier (S), supplier/location (L), supplier/department (D), or supplier/department/location (A).
Backorder Indicator	Always	This field indicates if backorders or partial shipments will be accepted.
VAT Region	Optional	This field contains the unique identifying number for the VAT region applicable for this site.
Prepay Invoice Indicator	Always	This field indicates whether all invoices for the supplier can be pre-paid.
Service Performed Required Indicator	Always	This field indicates if the supplier's services must be confirmed as performed before paying an invoice from that supplier site.
Invoice Payment Location	Optional	This field indicates where invoices from this supplier site are paid - at the store (S) or centrally through corporate accounting (C).
Invoice Received Location	Optional	This field indicates where invoices from this supplier site are received - at the store (S) or centrally through corporate accounting (C).
Invoice At	Optional	This field indicates if the supplier site invoice lists items at gross cost (G) or net cost (N).
Delivery Policy	Always	This field contains the default delivery policy of the supplier site. Valid values come from the DLVY code type.
Comments	Optional	This field contains any miscellaneous comments associated with the supplier.
Default Item Lead Time	Optional	This field holds the default lead time for the supplier site. The lead time is the time the supplier needs between receiving an order and having the order ready to ship. This value will be defaulted to item/supplier relationships.
DUNS Number	Optional	The Dun and Bradstreet number of the supplier.
DUNS Location	Optional	The Dun and Bradstreet number of the location of the supplier.
Bracket Costing Indicator	Always	This field will determine if the supplier site supports bracket costing pricing structures.
VMI Order Status	Optional	This field determines the status in which any inbound POs from this supplier will be created. A NULL value indicates that the supplier is not a VMI supplier.
End Active Date	No	Always null
DSD Supplier Indicator	Always	This field specifies whether the vendor supports DSD ordering, where the supplier replenishes the store directly, creating the PO and receipt at the same time.
Supplier Quantity Level	Always	This field indicates the supplier site order quantity level. Valid values are cases (CA) or eaches (EA).
Supplier Parent	Optional	This is the supplier number for the supplier sites.
Store Delivery Discrepancy	No	Always null
Final Destination Indicator	Always	This field indicates whether the supplier site can ship to final destination or not. Valid values are Yes (Y) or No (N).
External Reference Indicator	Optional	This column holds the ID for the supplier used in the external financial system.
Deal Upload Status	No	This field indicates the status in which the deal will be created when uploaded. The valid values are: W - Worksheet S - Submitted A - Approved
Tax ID	No	This field contains the unique tax identification number of the supplier site.
Custom Flex Attributes	Optional	Child Node

Vendor Address Creation

Adding new addresses of the types sent in this message for an existing supplier site will trigger an address detail creation message. This message is sent without the vendor header information. The 'key_value_1' field on this message will map to the supplier site.

Adding a new org unit to the supplier site will trigger an org unit create message. This message is also sent along with the header information.

Vendor Address Modification

Any updates made to the supplier site's address information of the types specified above will trigger an address modification message. This message is sent without the vendor header information. The 'key_value_1' field on this message will map to the supplier site. When detail changes are sent, only the values that changed are included in the message.

Table 2-82 Vendor Address Create or Update

Message Element	Included?	Notes
Module	Always	This field indicates the data type that the address is attached to. In this case, it will always be 'SUPP'.
Key Value 1	Always	This field holds the ID the address is attached to. In this case, it will be the supplier number.
Key Value 2	No	This is not used.
Sequence Number	Always	Number indicating the sequence that addresses within the same type were entered.
Address Type	Always	This field contains the address type. For suppliers, the following address types are sent: Returns (3) Order (4) Invoice (5)
Primary Address Indicator	Always	This field indicates whether the address is the primary address for the address type.
Address 1	Always	This field contains the first line of the address.
Address 2	Optional	This field contains the second line of the address.
Address 3	Optional	This field contains the third line of the address.
City	Always	This field contains the name of the city that is associated with the address.
State	Optional	This field contains the state abbreviation for the address.
Country ID	Always	This field contains the country where the address exists.
Jurisdiction Code	Optional	This field contains the ID associated to the tax jurisdiction of the country-state relationship.
Post	Optional	This field contains the zip code for the address.
Contact Name	Optional	This field contains the name of the contact for the supplier at this address.
Contact Phone	Optional	This field contains the phone number of the contact person at this address.
Contact Telex	Optional	This field contains the telex number of the contact person at this address.
Contact Fax	Optional	This field contains the fax number of the contact person at this address.
Contact Email	Optional	This field contains the email address of the supplier site's contact person.
Custom Flex Attributes	Optional	Child Node

Table 2-83 Vendor Org Unit Create or Update

Message Element	Included?	Notes
Org Unit ID	Always	This field contains org unit ID added or updated for the supplier site.
Primary Pay Site Indicator	Always	This field contains the primary pay site indicator.

Vendor Detail Deletion

Vendor addresses can be deleted only if they are not mandatory, or if they are mandatory but are not marked as the primary address for that address type. The deletion will trigger an address delete message.

Table 2-84 Vendor Address Delete

Message Element	Included?	Notes
Supplier	Always	This field contains the unique identifying number for a supplier within the system. The user determines this number when a new supplier is first added to the system.
Sequence Number	Always	This field contains the number indicating the sequence that addresses within the same type were entered.
Address Type	Always	This field contains the address type. Valid values (e.g. 01 - Business, 02 - Postal, etc.) are on the add_type table.

Deletion of an org unit will be allowed if there are no open purchase orders for the supplier site. This will trigger an org unit delete message.

Table 2-85 Vendor Org Unit Delete

Message Element	Included?	Notes
Org Unit ID	Always	This field contains org unit ID being removed for the supplier site.

Flex Attributes

If any custom flex attributes (CFAS) for the supplier have been added or modified, it will trigger a header modify message. All the entity's active flex attributes from all attribute groups are published as key-value pairs based on the group set view. This CFAS object is embedded in the outbound Vendor message.

Similarly, if any CFAS for the supplier address has been added or modified, it will trigger a vendor address modify message. All the entity's active flex attributes from all attribute groups are published as key-value pairs based on the group set view. This CFAS object is embedded in the outbound Vendor Address message.

Flex attributes can only be added to or updated; they cannot be deleted.

Table 2-86 Flex Attributes

Message Element	Required?	Notes
Name	Always	Hold the attribute name.
Value	Optional	Holds the value of the attribute for number and character type attributes
Value Date	Optional	Holds the date for date type attributes.

Full Message Updates

In cases where the integrating system is unable to receive only the changes, another option is provided that can resend the full supplier details whenever there is a change, in addition to the deltas. This will be published in cases where the system option Publish RIB Objects is set to Deltas and Full. The full message updates, like the vendor create message, will contain the full details in the message for all fields in the message. See above for the format of a vendor create message. This is used for Oracle WMS Cloud integration.

Error Handling

If any errors are encountered while publishing the message, a fatal error with status E (Error) is sent to RIB. This would bring down the RIB adapter, thus preventing any further messages from being processed until this is resolved

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
VendorCre	Vendor Create	VendorDesc.xsd
VendorAddrCre	Vendor Address Create	VendorAddrDesc.xsd
VendorOUCre	Vendor Org Unit Create	VendorOUDesc.xsd
VendorHdrMod	Vendor Header Modify	VendorHdrDesc.xsd
VendorAddrMod	Vendor Address Modify	VendorAddrDesc.xsd
VendorDel	Vendor Delete	VendorRef.xsd
VendorAddrDel	Vendor Address Delete	VendorAddrRef.xsd
VendorOUDel	Vendor Org Unit Delete	VendorOURef.xsd
VendorFulRep	Full message	VendorDesc.xsd

Warehouse Publication API

This section describes the warehouse publication API.

Functional Area

Foundation Data

Business Overview

Merchandising publishes data about warehouses in messages to the Oracle Retail Integration Bus (RIB). Other applications that need to keep their locations synchronized with Merchandising subscribe to these messages. Merchandising publishes information about all the warehouses, including both physical and virtual. Those applications on the RIB that understands virtual locations can subscribe to all warehouse messages that Merchandising publishes. Those applications that do not have virtual location logic, such as SIM and RWMS, it depends on RIB to transform Merchandising warehouse messages for physical warehouses only.

These RIB messages are triggered on inserting, updating, and deleting of warehouse and warehouse address in the Merchandising WH table, and the ADDR table with the module 'WH'. Only the primary address of the primary address type is included in this message. Oracle Retail publishes only the current state of the warehouse, not every change.

Package Impact

File name: rmsmfm_whs/b.pls

Function Level Description - ADDTOQ

Function: ADDTOQ(O_error_mesage         OUT   VARCHAR2,
                 I_message_type      IN       VARCHAR2,
                 I_wh_key_rec        IN       WH_KEY_REC,
                 I_addr_publish_ind  IN       ADDR.PUBLISH_IND%TYPE)

This public function puts a warehouse message on WH_MFQUEUE for publishing to the RIB. It is called from both wh trigger and address trigger. The I_functional_keys contains wh and, optionally, addr_key.

Function Level Description - GETNXT

Procedure: GETNXT(O_status_code     OUT  VARCHAR2,
                  O_error_msg       OUT  VARCHAR2,
                  O_message_type    OUT  VARCHAR2,
                  O_message         OUT  RIB_OBJECT,
                  O_bus_obj_id      OUT  RIB_BUSOBJID_TBL,
                  O_routing_info    OUT  RIB_ROUTINGINFO_TBL,
                  I_num_threads  IN      NUMBER DEFAULT 1,
                  I_thread_val   IN      NUMBER DEFAULT 1);

This public procedure is called from the RIB to get the next messages. It performs a cursor loop on the unpublished records on the WH_MFQUEUE table (PUB_STATUS = 'U'). If any exception is raised in GETNXT, including the exception raised by an unsuccessful call to PROCESS_QUEUE_RECORD, HANDLE_ERRORS is called.

Function Level Description - PUB_RETRY

This public procedure performs the same tasks as GETNXT except that it only loops for a specific row in the WH_MFQUEUE table. The record on WH_MFQUEUE must match the passed in sequence number (contained in the ROUTING_INFO).

Function Level Description - PROCESS_QUEUE_RECORD (local)

This private function controls the building of Oracle Objects (DESC or REF) given the business transaction's key values and a message type. It contains all of the shared processing between GETNXT and PUB_RETRY.

Note:

The message_type of HDR_ADD can potentially be changed to a DTL_ADD in PROCESS_QUEUE_RECORD).

Function Level Description - DELETE_QUEUE_REC (local)

This private function deletes a record in WH_MFQUEUE table given the row ID.

Function Level Description - MAKE_CREATE (local)

Procedure: MAKE_CREATE(O_error_msg       OUT         VARCHAR2,
                       O_message      IN OUT NOCOPY  RIB_OBJECT,
                       O_routing_info IN OUT NOCOPY  RIB_ROUTINGINFO_TBL,
                       I_wh_key_rec   IN             WH_KEY_REC,
                       I_rowid        IN             ROWID)

This private function is used to create the Oracle Object for the initial publication of a business transaction. I_business_object contains the warehouse header key values (wh). I_rowid is the rowid of the wh_mfqueue row fetched from GETNXT.

Function Level Description - BUILD_HEADER_OBJECT (local)

Procedure: BUILD_HEADER_OBJECT
                   (O_error_msg         OUT         VARCHAR2,
                    O_routing_info   IN OUT NOCOPY  RIB_ROUTINGINFO_TBL,
                    O_rib_whdesc_rec    OUT         RIB_WH_DESC,
                    I_wh_key_rec     IN             WH_KEY_REC)

This private function accepts warehouse header key values (wh), builds and returns a header level DESC Oracle Object.

This function calls the BUILD_WH_CFA_EXT to build the RIB_CustFlexAttriVo_TBL for warehouse's customer attributes and attach it to the header level REF Oracle Object.

Function Level Description - BUILD_HEADER_OBJECT (local)

This overloaded private function accepts warehouse header key value (wh), builds and returns a header level REF Oracle Object.

Function Level Description - BUILD_DETAIL_OBJECTS (local)

The private function is responsible for building detail level DESC Oracle Objects. It builds as many detail Oracle Object as it can given the passed in message type and business object keys (wh).

This function calls the BUILD_ADDR_CFA_EXT to build the RIB_CustFlexAttriVo_TBL for warehouse's address customer attributes and attach it to the detail level REF Oracle Object.

Function Level Description - BUILD_SINGLE_DETAIL (local)

This private function takes in an address record and builds a detail level Oracle Object. Also find out if the address is the primary address of the primary address type and set the DESC Oracle Object accordingly.

Function Level Description - BUILD_DETAIL_CHANGE_OBJECTS (local)

This private function builds a DESC Oracle Object to publish to the RIB for detail create and detail update messages (DTL_ADD, DTL_UPD). I_business_obj contains the header level key values (wh).

Function Level Description - BUILD_DETAIL_DELETE_OBJECTS (local)

This private function builds a REF Oracle Object to publish to the RIB for detail delete messages (DTL_DEL). I_business_obj contains the header level key values (wh).

Function Level Description - LOCK_THE_BLOCK (local)

This private function locks all queue records for the current business object (wh). This is to ensure that GETNXT and PUB_RETRY do not wait on any business processes that currently have the queue table locked and have not committed. This can occur because ADDTOQ, which is called from the triggers, deletes from the queue table for DTL_UPD, DTL_DEL, and HDR_DEL messages.

Function Level Description - HANDLE_ERRORS (local)

This private procedure is called from GETNXT and PUB_RETRY when an exception is raised. I_seq_no is the sequence number of the driving WH_MFQUEUE record. I_function_keys contains detail level key values (wh, addr_key).

Function Level Description - BUILD_WH_CFA_EXT (local)

This private function will build and return entity's customer attributes from WH_CFA_EXT table.

Function Level Description - BUILD_ ADDR _CFA_EXT (local)

This private function will build and return store's address customer attributes of the entity from ADDR_CFA_EXT table.

Message XSD

Here are the filenames that correspond with each message type. Please consult RIB documentation for each message type in order to get a detailed picture of the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
WHCre	WH Create Message	WHDesc.xsd
WHMod	WH Modify Message	WHDesc.xsd
WHDel	WH Delete Message	WHRef.xsd
WHDtlCre	WH Detail Create Message	WHDesc.xsd
WHDtlMod	WH Detail Modify Message	WHDesc.xsd
WHDtlDel	WH Detail Delete Message	WHRef.xsd
WHAddCre	WH Address Create	WHAddrDesc.xsd
WHAddMod	WH Address Modify	WHAddrDesc.xsd

Design Assumptions

Push off all DML statements as late as possible. Once DML statements have taken place, any error becomes a fatal error rather than a hospital error.

Table Impact

TABLE	SELECT	INSERT	UPDATE	DELETE
WH_MFQUEUE	Yes	Yes	Yes	Yes
WH_PUB_INFO	Yes	Yes	Yes	Yes
WH	Yes	No	No	No
ADDR	Yes	No	Yes	No
ADD_TYPE_MODULE	Yes	No	No	No
WH_CFA_EXT	Yes	No	No	No
ADDR_CFA_EXT	Yes	No	No	No

Work Orders In Publication API

This section describes the work order in publication API.

Functional Area

Purchase Orders

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

A work order provides direction to a warehouse, such as Oracle Retail Warehouse Management (RWMS), about work that needs to be completed on items contained in a purchase order prior to shipping them on to stores or other warehouses. Merchandising publishes work orders soon after it publishes the purchase order itself. This is referred to as a Work Order In message. Work orders are defined at the physical location level. The message family manager will send to the warehouse at which the work order will be done. This is used by the RIB publication adaptor for routing messages to the appropriate warehouse.

Note:

This integration is not used in Merchandising's integration to Oracle WMS Cloud.

New Work Order

Creating a new work order for an approved order triggers a message to be sent to notify the warehouse of additional activities that must be performed on the order upon receipt. The message includes the following: work order ID, order number, warehouse that will perform the work, the locations where the items on the order will be sent onto, and details on the items and work to be done, including a sequence and WIP (work in progress) code, which is stored in Merchandising codes table, under code type WWIP. It is assumed that the WIP codes used by Merchandising are coordinated with your warehouses that will be receiving the updates.

The format for creating and updating inbound work orders is shown below.

Table 2-87 Work Order Header

Message Element	Included?	Notes
Work Order ID	Always	This field contains the unique identifier for work in process associated with an order or transfer.
Order Number	Always	This field contains the order number associated with the work order.
Work Order Detail	Always	Child node

Table 2-88 Work Order Detail

Message Element	Included ?	Notes
Warehouse	Always	The physical warehouse where the work order will be done.
Item	Always	The item on which the work order will be done.
Location Type	Always	The location type of the final destination. Valid values are: S - Store W - Warehouse
Location	Always	The final destination for the item on the order. If the item is pre-distributed this location will be the store or warehouse on the allocation. If not, it will be the same warehouse as above.
Sequence Number	Always	The sequence number that is needed to ensure each record is unique, since the same work-in-progress code can be listed more than once on the same work order.
WIP Code	Always	The WIP code for the work to be done on the item. Valid values are in the codes table under the WWIP code type.
Instructions	No	Not used

Updated Work Order

When an existing work order for an approved order is updated, an update message is triggered to provide the details of the update. The update message, like create, will contain the full details in the message for all fields in the message.

Deleted Work Order

When a work order is deleted for a purchase order, this will also trigger a delete transaction to be sent to notify external systems. The delete message will include the work order ID, order number at the header level and the warehouse, item, location type, location, sequence number and WIP code at a detail level. It is also possible to delete just a detail from the work order.

The format for deleting inbound work orders or work order details is shown below.

Table 2-89 Work Order Header

Message Element	Included?	Notes
Work Order ID	Always	This field contains the unique identifier for work in process associated with an order or transfer.
Order Number	Always	This field contains the order number associated with the work order.
Work Order Detail	Optional	Child node

Table 2-90 Work Order Detail

Message Element	Included?	Notes
Warehouse	Always	The physical warehouse where the work order will be done.
Item	Always	The item on which the work order will be done.
Location Type	Always	The location type of the final destination. Valid values are: S - Store W - Warehouse
Location	Always	The final destination for the item on the order. If the item is pre-distributed this location will be the store or warehouse on the allocation. If not, it will be the same warehouse as above.
Sequence Number	Always	The sequence number that is needed to ensure each record is unique, since the same work-in-progress code can be listed more than once on the same work order.
WIP Code	Always	The WIP code for the work to be done on the item. Valid values are in the codes table under the WWIP code type.

Error Handling

If any errors are encountered while publishing the message, a fatal error with status E (Error) is sent to RIB. This would bring down the RIB adapter thus preventing any further messages from being processed until this is resolved.

Message XSD

Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
InBdWOCre	Work Order In Create	WOInDesc.xsd
InBdWOMod	Work Order In Modify	WOInDesc.xsd
InBdWODel	Work Order In Delete	WOInRef.xsd

Work Orders Out Publication API

This section describes the Work Orders out Publication API.

Functional Area

Transfers

Business Overview

This publication API facilitates the transmission of outbound work orders for the finisher from Merchandising to external systems. Only transfers that pass through a finisher before reaching the final location can be associated with work orders. The work order provides instructions for one or more of the following tasks to be completed at the finisher location:

• Perform an activity on an item, such as monogramming or ticketing.

• Transform an item from one thing into another, such as dyeing a white t-shirt black.

• Combine bulk items into a pack or break down a pack into its component items.

Outbound work orders are not bundled with transfer messages, because multi-legged transfers can be routed to either internal finishers (held as virtual warehouses) or external finishers (held as partners).

All activities, transformations, and packing details are contained in the same message. Because Merchandising does not allow users to modify work order activities, transformation or packing information for an approved transfer (it must be brought back to Input status), separate detail-level messages of any type (create, delete, update) are never published.

Outbound work order delete messages are published when the second leg of a multi-legged transfer is unapproved. This can be accomplished through the un-approval of an entire multi-legged transfer or the un-approval of the second leg only. A two-leg transfer that has had the first leg shipped can be set back to In Progress status to make changes to the work order activities and the final location. When the action has occurred, only the second leg is really set back to in progress. The first leg remains in shipped status.

New Work Order

A new work order is created when a transfer with finishing is approved. This triggers a message notifying the external system. The message includes the following:

• Transfer work order ID

• Finisher ID

• Transfer number

• Transfer parent number

• Inventory type (available or unavailable)

• Work order detail information - including item, destination location, item's inventory status, and the work order activity information (costs, comments)

• Transformation details - including the from and to item IDs

• Packing information - including the from items and to items

The format for creating and updating outbound work orders is shown below.

Table 2-91 Work Order Header

Message Element	Included?	Notes
Work Order ID	Always	This field contains the unique identifier for the work in process associated with a transfer.
Destination ID	Always	This field contains the source location of the transfer.
Distro Number	Always	This field contains the first leg transfer number of the 2-legged transfer.
Work Order Detail	Always	Child node
Distro Parent Number	Optional	This field contains the second leg transfer number of the 2-legged transfer.
Work Order Transformation	Optional	Child node
Work Order Packing	Optional	Child node
Inventory Type	Optional	This field contains the code to indicate whether the inventory on the transfer is available. Valid values are Available (A) and Unavailable (U).

Table 2-92 Work Order Detail

Message Element	Included?	Notes
Destination ID	Always	This field contains to the destination location for the transfer.
Item ID	Always	This field contains the item on which the work order will be performed.
Work In Process Sequence Number	No	Not used
Work In Process Code	No	Not used
Personalization	No	Not used
Instructions	No	Not Used
Order Line Number	No	Not Used
Auto Complete	No	Not Used
Work Order Activity	Optional	Child node
Inventory Status	Optional	This field contains the inventory status of the transfer detail.

Table 2-93 Work Order Activity

Message Element	Included?	Notes
Activity ID	Always	This field contains a code number indicating the activity that will be performed on the item.
Activity Cost	Optional	This field contains the per unit cost of the finishing activity.
Comments	Optional	This field contains any comments regarding this activity.

Table 2-94 Work Order Transformation

Message Element	Included?	Notes
From Item	Always	This field contains the original item on the transfer.
To Item	Always	This field contains the item which results from the finishing activity.

Table 2-95 Work Order Packing

Message Element	Included?	Notes
To Pack	Always	Child node
From Pack	Always	Child node

Table 2-96 Work Order Pack To

Message Element	Included?	Notes
To Item	Always	This contains the item resulting from the packing process.

Table 2-97 Work Order Pack From

Message Element	Included?	Notes
From Item	Always	This contains the component item used in the packing process.

Deleted Work Order

When a transfer with finishing is deleted or unapproved, this will trigger a delete transaction message to an external system. The delete message contains the work order ID to be deleted.

Table 2-98 Work Order Header

Message Element	Included?	Notes
Work Order ID	Always	This field contains the unique identifier of the work order being deleted.
Destination ID	Always	This field contains the source location of the transfer.
Distro Number	Always	This field contains the first leg transfer number of the 2-legged transfer.
Work Order Detail	Always	Child node
Distro Parent Number	Optional	This field contains the second leg transfer number of the 2-legged transfer.

Table 2-99 Work Order Detail

Message Element	Included?	Notes
Work Order ID	Always	This field contains the unique identifier of the work order being deleted.
Destination ID	Always	This field contains to the destination location for the transfer.
Item ID	Always	This field contains the item on which the work order will be performed.
Work In Process Sequence Number	No	Not used
Order Line Number	No	Not used

Error Handling

If any errors are encountered while publishing the message, a fatal error with status E (Error) is sent to RIB. This will bring down the RIB adapter, thus preventing any further messages from being processed until this is resolved.

Message XSD

Here are the filenames that correspond to each message type. Please consult RIB documentation for each message type to get a detailed picture of the composition of each message.

Message Types	Message Type Description	XML Schema Definition (XSD)
OutBdWoCre	Work Order Create Message	WOOutDesc.xsd
OutBdWoDel	Work Order Delete Message	WOOutRef.xsd