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.
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
Message Element | Required? | 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 |
Optional |
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 |
Optional |
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
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.
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 through 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:
Allocation Header
Message Element | Required? | 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 Details |
Optional |
Child node |
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 |
ASNOUT Publication API
This section describes the ASNOUT Publication API.
Business Overview
ASNOUT means the outbound message of Advanced Shipment Notification. The ASN out message is used to ship the 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 SIM, is NOT installed and shipping/receiving for stores will be done in Merchandising.
-
Ship_rcv_wh = 'Y' means a warehouse management system, such as RWMS, 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:
SIM Installed (Yes/No) | RWMS Installed (Yes/No) | System Options Settings | Merchandising Publishes Shipments (Yes/No) | Apps to subscribe to the message (SIM/RWMS) |
---|---|---|---|---|
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 |
SIM |
No |
Yes |
Ship_rcv_store = Y Ship_rcv_wh = N |
Yes - for store-to-warehouse shipments |
RWMS |
Merchandising on-line shipping can involve a customer order transfer (tsf_type = 'CO'). For a customer order transfer, customer order number, and fulfillment order number are pulled from the ORDCUST table and 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 (which Merchandising manages inventory and financials like regular stores) and non-stockholding franchise stores (which Merchandising does NOT manage inventory and financials).
SIM 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 SIM is installed.
For warehouses, if a franchise return from a non-stockholding franchise store is to be processed, RWMS will require an ASN against which to receive. Since Merchandising automatically creates the shipment for non-stockholding stores upon the approval of a franchise return, Merchandising needs to publish those shipments for RWMS. Similar to on-line Shipping/Receiving, Merchandising publishes shipments of non-stockholding Franchise Returns to warehouses as ASNOut messages.
Package Impact
This section describes the package impact.
Package name: RMSMFM_SHIPMENT
Function Level Description - ADDTOQ
ADDTOQ (O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE, I_message_type IN SHIPMENT_PUB_INFO.MESSAGE_TYPE%TYPE, I_shipment IN SHIPMENT.SHIPMENT%TYPE, I_to_loc IN SHIPMENT.TO_LOC%TYPE, I_to_loc_type IN SHIPMENT.TO_LOC_TYPE%TYPE)
-
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 ('asnoutcre') for shipment publishing.
-
Validate all the input parameters to this function against NULL. If any has a NULL value then return from the function with the appropriate error message.
-
Insert a record in the SHIPMENT_PUB_INFO table. The published flag will be set to 'U'. The correct thread for the business transaction will be calculated and written. Call API_LIBRARY. GET_RIB_SETTINGS to get the number of threads used for the publisher. Using the number of threads, and the business object ID (For example, shipment number), calculate the thread value.
Function Level Description - GETNXT
GETNXT (O_status_code IN OUT VARCHAR2, O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE, O_message_type IN OUT VARCHAR2, O_message IN OUT RIB_OBJECT, O_bus_obj_id IN OUT RIB_BUSOBJID_TBL, O_routing_info IN OUT RIB_ROUTINGINFO_TBL, I_num_threads IN NUMBER DEFAULT 1, I_thread_val IN NUMBER DEFAULT 1)
Initialize LP_error_status 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 SHIPMENT_PUB_INFO table (PUB_STATUS = 'U'). It will only execute one loop iteration in most cases. For each record retrieved, GETNXT gets the following:
-
A lock of the queue table for the current business objects (i.e. shipment number). 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.
-
A check for records on the queue with a status of 'H' -Hospital. If there are any such records for the current business object, GETNXT raises an exception to send the current message to the Hospital.
-
The information from the SHIPMENT_PUB_INFO 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 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.
-
Unconditionally exit from the loop after the successful processing of PROCESS_QUEUE_RECORD function, assuming the shipment is published successfully.
If the O_message from PROCESS_QUEUE_RECORD is NULL then, send NO_MSG in the status_code otherwise send the NEW_MSG in the status_code with the shipment number as business object Id. Also, send the message type as "asnoutcre".
Function Level Description - PUB_RETRY
PUB_RETRY (O_status_code IN OUT VARCHAR2, O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE, O_message_type IN OUT VARCHAR2, O_message IN 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 SHIPMENT_PUB_INFO to be published must match the passed in sequence number contained in the ROUTING_INFO.
Function Level Description - PROCESS_QUEUE_RECORD (local)
PROCESS_QUEUE_RECORD (O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE, O_message IN OUT NOCOPY RIB_OBJECT, O_routing_info IN OUT NOCOPY RIB_ROUTINGINFO_TBL, O_bus_obj_id IN OUT NOCOPY RIB_BUSOBJID_TBL, I_shipment IN SHIPMENT.SHIPMENT%TYPE, I_seq_no IN SHIPMENT_PUB_INFO.SEQ_NO%TYPE)
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 correct thread for the business transaction will be calculated and written. Call API_LIBRARY. GET_RIB_SETTINGS to get the number of threads used for the publisher. Using the number of threads, and the business object ID (for example, shipment number), calculate the thread value.
-
Build the header and detail object by calling BUILD_HEADER_OBJECT.
-
Delete the current record from the queue (i.e. shipment_pub_info table) by calling UPDATE_QUEUE_REC function.
Function Level Description - BUILD_HEADER_OBJECT (local)
BUILD_HEADER_OBJECT ( O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE, O_rib_asnoutdesc_rec IN OUT "RIB_ASNOutDesc_REC", O_routing_info IN OUT NOCOPY RIB_ROUTINGINFO_TBL, I_shipment IN SHIPMENT_PUB_INFO.SHIPMENT%TYPE)
-
Take all necessary data from the SHIPMENT table for the current shipment and put it into a "RIB_ASNOutDesc_REC" object. In addition, publish a schedule_number of NULL and auto_receive_ind of 'N' to the "RIB_ASNOutDesc_REC" object.
-
The routing information has to be sent to RIB through RIB_ROUTINGINFO_REC. This routing info is for FROM location, TO location and source application (Merchandising) from which RIB receives the information. The routing location type for the TO location will be set to 'V' for the non stockholding company stores (i.e. virtual stores). Else, it will be set to 'S'. This is to ensure that shipment to a virtual store is not routed to SIM.
-
If the destination location is Store then, set the asn_type as 'C' (Customer Store) and get the information about the store by calling STORE_ATTRIB_SQL.GET_INFO. Else, set the asn_type to 'T' (wh transfer) and get the information about WH by calling WH_ATTRIB_SQL.GET_WH_INFO function.
-
Call the BUILD_DETAIL_OBJECTS to get the details of the current shipment record.
-
The container_qty is a required field on the RIB object. So, Merchandising sends 1 instead of NULL in SHIPMENT.NO_BOXES if it is NULL.
Function Level Description - BUILD_DETAIL_OBJECTS (local)
BUILD_DETAIL_OBJECTS (O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE, O_rib_asnoutdistro_tbl IN OUT "RIB_ASNOutDistro_TBL", I_shipment_rec IN SHIPMENT%ROWTYPE)
The function is responsible for building detail level Oracle Objects. It builds as many detail Oracle Object as it can given the passed in message type and business object keys.
-
Fetch the detail records of the shipment from SHIPSKU for the given shipment number.
-
If the distro_type is 'T' then, get the transfer details by calling the TSF_ATTRIB_SQL.GET_TSFHEAD_INFO function. Else, get the corresponding allocation details from the alloc_detail table for the current distro_no and to_location.
-
If the freight_code is 'E'xpedite then, set the expedite flag to 'Y' otherwise 'N'.
-
When the transfer type is Customer Order "CO", the corresponding customer order number and fulfillment order number from the ORDCUST table will be published in the distro record.
-
Assign the above details into "RIB_ASNOutItem_REC", "RIB_ASNOutCtn_REC" and "RIB_ASNOutDistro_REC" records.
-
Because the container_qty and container_id are the mandatory fields, Merchandising will send "1" for container_qty and "0" for container_id instead of NULL.
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.
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 SHIPMENT_PUB_INFO 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'.
Trigger Impact
Trigger name: EC_TABLE_SPT_AIR
Trigger file name: ec_table_spt_air.trg
Table: SHIPMENT_PUB_TEMP
A trigger on the SHIPMENT_PUB_TEMP table will capture the inserts.
-
Send the appropriate column values to the ADDTOQ procedure in the MFM with the message type asnoutcre.
Message XSD
Here is the filename that corresponds with the message type. Please consult the RIB documentation for this message type in order 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 |
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.
-
ASNOut messages published from Merchandising should NOT go back to Merchandising again.
-
ASNOut messages published from Merchandising are intended for execution systems like SIM and RWMS. They are never routed to Order Management System (OMS). OMS is responsible for managing the order through its lifecycle from capture at the Online Order Capture (OOC) through fulfillment.
Available Inventory for Store and Warehouse Publication API
This section describes the Store and Warehouse Publication API.
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 | Required? | 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.
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 |
Banner Publication API
This section describes the banner publication API.
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
![Banners and channels Banners and channels](img/banners-and-channels.png)
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
-
Prerequisites: For channel creation, the associated banner must have been created.
-
Activity Detail: Once a banner/channel has been created, it is ready to be published. An initial publication message is made.
-
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
-
Prerequisites: banner/channel has been created.
-
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.
-
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
-
Prerequisites: banner/channel has been created.
-
Activity Detail: Deleting a banner/channel removes it from the system. External systems are notified by a published message.
-
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.
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.
Codes and Diff Types
This section describes the seed data publication API.
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.
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.
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-3 New Codes Code Detail Message Elements
Message Element | Required? | 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.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-5 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. |
Custom Flex Attributes (CFAS)
Table 2-6 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 includedError 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-8 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.
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.
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 |
Countries
This section describes the countries publication API.
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.
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.
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
This section describes the customer order fulfillment confirmation publication API.
Business Overview
When Merchandising is integrated with an external Order Management System (OMS), one of the supported deployment methods is interfacing customer order fulfillment requests into Merchandising through the Oracle Retail Integration Bus (RIB) on Oracle Retail Java Messaging Service (JMS). When Merchandising processes a customer order fulfillment request from OMS, it will also publish a confirmation message back to OMS indicating whether the order was created fully, partially or not created. The confirmation message contains the following information, much of which is a repeat back of the details on the original customer order:
-
Header Level
-
Customer order number
-
Fulfillment order number
-
Confirmation Type
-
'C' (order fully created)
-
'P' (order partially created)
-
'X' (order not created)
-
-
Confirmation number - Merchandising PO or Transfer
-
Source location number
-
Integration System Code - based on the system option
-
-
Detail Level
-
Item
-
Reference Item
-
Confirmed quantity
-
Confirmed quantity UOM
-
Item Line Number
-
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.
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.
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.
Add Delivery Slot
Delivery slot creation will result in publishing the ID, description, and a sequence to notify the integrating systems.
Delivery Slot Modification
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.
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 |
Diff Group Publication API
This section describes the differentiator groups publication API.
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.
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.
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 |
Differentiators Publication API
This section describes the differentiators publication API.
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.
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.
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 mod, packitem cre, packitem mod, packitem delete, reference item add, reference item mod and reference item del, a second full replacement message with message type 'itemfulrep' will be published from Merchandising if system options PUB_FULL_OBJECTS_IND is configured to be 'Y' in the PRODUCT_CONFIG_OPTIONS table. 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).
Only content items can be included on a transfer and container items are never allowed. While creating return to vendors (RTVs), if content items are added then associated container items are included automatically.
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 transformation item transformation](img/item-transformation.png)
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. Each of these tables holds a trigger, which fires each time an insert, update, or delete occurs on the table. The new item record itself is displayed on the ITEM_MASTER table. The trigger on this table creates a new message (in this case, a message of the type ItemHdrCre), then calls the message family manager RMSMFM_ITEMS and its ADDTOQ public procedure. ADDTOQ populates the message to the ITEM_MFQUEUE staging table by inserting the following:
-
Appropriate value into the message_type column.
-
Message itself to the message column. Messages are of the data type CLOB (character large object).
New Item Message Publication
The publication of a new item and its components to the RIB is done using a hierarchical message. Here is how the process works:
-
A new item is held on ITEM_MASTER in a status of W (Worksheet) until it is approved.
-
On the ITEM_MFQUEUE staging table, a Worksheet status item is displayed in the message_type column as a value of ItemCre.
-
As the item continues to be built on ITEM_MASTER, an ItemHdrMod value is inserted into the queue's message_type column.
-
After the item is approved (ITEM_MASTER's status column value of A [Approved], the trigger causes the insertion of a value of Y (Yes) in the approve_ind column on the queue table.
-
A message with a top-level XML tag of ItemDesc is created that serves as a message wrapper.
At the same time, a sub-message with an XML tag of ItemHdrDesc is also created. This subordinate tag holds a subset of data about the item, most of which is derived from the ITEM_MASTER table.
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, a new item-supplier record created on ITEM_SUPPLIER causes the trigger on this table to add an ItemSupCre value to the message_type column of the ITEM_MFQUEUE staging table. When the item is approved, a message with an XML tag of ItemSupDesc is added underneath the ItemDesc tag.
Similar processes occur with the other item components. Each component has its own Desc XML tag, for example: ItemSupCtyDesc, ISCDimDesc.
Modify and Delete Messages
Updates and deletions of item data can be included in a larger ItemDesc (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 record changes on the ITEM_MASTER table, for example, the trigger fires to create an ItemHdrMod message and message type on the queue table. In addition, an ItemHdrDesc message is created. If no ItemCre value already exists in the queue, the ItemHdrDesc message is published to the RIB.
Similarly, item components like item-supplier that are modified, result in an ItemSupMod message type inserted on the queue. If an ItemCre and an ItemSupCre already exist, the ItemSupMod is published as part of the larger ItemDesc message. Otherwise, the ItemSupMod is published as an ItemSupDesc message.
Delete messages
Delete messages are published in the same way that modify messages are. For example, if an item-supplier-country relationship is deleted from Merchandising' ITEM_SUP_COUNTRY table, the dependent record on ITEM_SUPP_COUNTRY_DIM is also deleted.
-
An ItemSupCtyDel message type is displayed on the item queue table.
-
If the queue already holds an ItemCre or ItemSupCtyCre message, any ItemSupCtyCre and ItemSupCtyMod messages are deleted.
Otherwise, ItemSupCtyDel is published by itself as an ItemSupCtyRef message to the RIB.
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:
-
Create a type for a table of rowids.
TYPE ROWID_TBL is TABLE OF ROWID;
-
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 );
-
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;
Package Impact
This section describes the package impact.
Business Object ID
The business object ID for item publisher is item, which uniquely identifies an item for publishing.
The RIB uses the business object ID to determine message dependencies when sending messages to a subscribing application. If a Create message has already failed in the subscribing application, and a Modify/Delete message is about to be sent from the RIB to the subscribing application, the RIB will not send the modify/delete message if it has the same business object ID as the failed Create message. Instead, the Modify/Delete message will go directly to the hospital.
Item type X, item A, message type 'ItemCre' fails in subscriber.
Item type X, item B, message type 'ItemCre' processes successfully in subscriber.
Item type X, item A, message type 'ItemMod' goes directly from RIB to hospital.
Item type X, item B, message type 'ItemMod' goes from RIB to subscriber.
Item type X, item A, message type 'ItemDel' goes directly from RIB to hospital.
Package name: RMSMFM_ITEMS
Spec file name: rmsmfm_itemss.pls
Body file name: rmsmfm_itemsb.pls
Package Specification - Global Variables
FAMILY CONSTANT RIB_SETTINGS.FAMILY%TYPE 'ITEM'; ITEM_FULREP CONSTANT VARCHAR2(30) 'ItemFulRep'; ITEM_ADD CONSTANT VARCHAR2(30) 'itemcre'; ITEM_UPD CONSTANT VARCHAR2(30) 'itemhdrmod'; ITEM_DEL CONSTANT VARCHAR2(30) 'itemdel'; ISUP_ADD CONSTANT VARCHAR2(30) 'itemsupcre'; ISUP_UPD CONSTANT VARCHAR2(30) 'itemsupmod'; ISUP_DEL CONSTANT VARCHAR2(30) 'itemsupdel'; ISC_ADD CONSTANT VARCHAR2(30) 'itemsupctycre'; ISC_UPD CONSTANT VARCHAR2(30) 'itemsupctymod'; ISC_DEL CONSTANT VARCHAR2(30) 'itemsupctydel'; ISCD_ADD CONSTANT VARCHAR2(30) 'iscdimcre'; ISCD_UPD CONSTANT VARCHAR2(30) 'iscdimmod'; ISCD_DEL CONSTANT VARCHAR2(30) 'iscdimdel'; UPC_ADD CONSTANT VARCHAR2(30) 'itemupccre'; UPC_UPD CONSTANT VARCHAR2(30) 'itemupcmod'; UPC_DEL CONSTANT VARCHAR2(30) 'itemupcdel'; BOM_ADD CONSTANT VARCHAR2(30) 'itembomcre'; BOM_UPD CONSTANT VARCHAR2(30) 'itembommod'; BOM_DEL CONSTANT VARCHAR2(30) 'itembomdel'; UDAF_ADD CONSTANT VARCHAR2(30) 'itemudaffcre'; UDAF_UPD CONSTANT VARCHAR2(30) 'itemudaffmod'; UDAF_DEL CONSTANT VARCHAR2(30) 'itemudaffdel'; UDAD_ADD CONSTANT VARCHAR2(30) 'itemudadatecre'; UDAD_UPD CONSTANT VARCHAR2(30) 'itemudadatemod'; UDAD_DEL CONSTANT VARCHAR2(30) 'itemudadatedel'; UDAL_ADD CONSTANT VARCHAR2(30) 'itemudalovcre'; UDAL_UPD CONSTANT VARCHAR2(30) 'itemudalovmod'; UDAL_DEL CONSTANT VARCHAR2(30) 'itemudalovdel'; IMG_ADD CONSTANT VARCHAR2(30) 'itemimagecre'; IMG_UPD CONSTANT VARCHAR2(30) 'itemimagemod'; IMG_DEL CONSTANT VARCHAR2(30) 'itemimagedel'; TCKT_ADD CONSTANT VARCHAR2(30) 'itemtcktcre'; TCKT_DEL CONSTANT VARCHAR2(30) 'itemtcktdel'; RIH_ADD CONSTANT VARCHAR2(30) 'relitemheadcre'; RIH_UPD CONSTANT VARCHAR2(30) 'relitemheadmod'; RIH_DEL CONSTANT VARCHAR2(30) 'relitemheaddel'; RID_ADD CONSTANT VARCHAR2(30) 'relitemdetcre'; RID_UPD CONSTANT VARCHAR2(30) 'relitemdetmod'; RID_DEL CONSTANT VARCHAR2(30) 'relitemdetdel'; bom_table bom_tabtype; empty_bom bom_tabtype;
Function Level Description - ADDTOQ
Function: ADDTOQ (O_error_message OUT VARCHAR2, I_queue_rec IN ITEM_MFQUEUE%ROWTYPE, I_sellable_ind IN ITEM_PUB_INFO.SELLABLE_IND%TYPE, I_tran_level_ind IN ITEM_PUB_INFO.TRAN_LEVEL_IND%TYPE)
This public function puts an item message on ITEM_MFQUEUE for publishing to the RIB. It is called from the item trigger and the detail triggers (ITEM_SUPPLIER, ITEM_SUPP_COUNTRY, ITEM_SUPP_COUNTRY_DIM, PACKITEM, UDA_ITEM, UDA_VALUES, ITEM_IMAGE, RELATED_ITEM_HEAD, RELATED_ITEM_DETAIL). The I_queue_rec contains item and, optionally, other detail keys.
For header level insert messages (HDR_ADD), insert a record in the ITEM_PUB_INFO table. The published flag should be set to 'N'. For all message types except header level inserts (HDR_ADD), insert a record into the ITEM_MFQUEUE.
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)
Modify the existing function as follows:
-
Change the signature of this package per this specification.
-
Replace the code that is in the current function with the functionality in this design.
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_MFQUEUE table (PUB_STATUS = 'U'). It should only need to execute single loop iteration in most cases. For each record retrieved, GETNXT gets the following:
-
A lock of the queue table for the current business object (item). 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 and picked up again in the next loop iteration.
-
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.
-
Get the published indicator from the ITEM_PUB_INFO table.
-
Call PROCESS_QUEUE_RECORD with the current business object.
The loop must be execute for more than one iteration in the following cases:
-
When a header delete message exists on the queue for a business object that has not been initially published. In this case, simply remove the header delete message from the queue and loop again.
-
The queue is locked for the current business object. 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.
The information from the ITEM_MFQUEUE and ITEM_PUB_INFO 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 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.
If PROCESS_QUEUE_RECORD fails, the record that keeps track of which mfqueue records to delete/update should be reset. Therefore, a snapshot of the struct is taken before the call to PROCESS_QUEUE_RECORD. If the function fails, the record is reset back to the snapshot.
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 NOCOPY RIB_BUSOBJID_TBL, O_routing_info IN OUT NOCOPY RIB_ROUTINGINFO_TBL)
This public procedure performs the same tasks as GETNXT except that it only loops for a specific row in the ITEM_MFQUEUE table. The record on ITEM_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.
Get relevant publishing info for the item in ITEM_PUB_INFO, including the published indicator and approved upon create indicator.
If I_hdr_published is either 'N' (not published)
-
If I_hdr_published is 'N', check to see if the current message should cause the item to be published. This will be true if the status has changed to 'A'pproved or if an ITEM_SUPP_COUNTRY record has been added to an item that was approved upon create. If the item is ready to be published for the first time, the message type is a header create (HDR_ADD). If it is not ready to be published, add the record's ROWID to the structure that keeps track of ROWIDs to delete.
-
Call MAKE_CREATE to build the DESC Oracle Object to publish to the RIB. This will also take care of any ITEM_MFQUEUE deletes, updating ITEM_PUB_INFO.PUBLISHED to 'Y' or 'I', and bulk updating the detail tables publish_ind column to 'Y' for those detail rows that have been published.
If the message type is an update or creates message type at any level (for example, ITEM_ADD, ISUP_ADD, ISUP_UPD, and others):
-
Call RMSMFM_ITEMS_BUILD.BUILD_MESSAGE to build the DESC Oracle Object to publish to the RIB.
-
RMSMFM_ITEMS_BUILD.BUILD_MESSAGE will return an indicator specifying if the record exists. The record in question is the record on the functional table corresponding to the current MFQUEUE record being processed. For example, for ITEM_ADD or ITEM_UPD message, the record exists indicator specifies whether or not the ITEM_MASTER record for the item still exists. For an ISUP_ADD or ISUP_UPD message, the record exists indicator specifies whether or not the ITEM_SUPPLIER record for the item/supplier combination still exists. If the record does not exist, the current message cannot be published.
-
If the record does not exist and the message type is an update, delete the current MFQUEUE record (that is, add the ROWID to the list of ROWIDs to be eventually deleted).
-
If the record does not exist and the message type is a create, update the current MFQUEUE record's pub_status to 'N' so that the record will be skipped but remain on the queue (that is, add the ROWID to the list of ROWIDs to be eventually updated).
-
If the message type is a delete message type at any level (for example, ITEM_DEL, ISUP_DEL, and others):
-
Call RMSMFM_ITEMS_BUILD.BUILD_DELETE_MESSAGE to build the REF Oracle Object to publish to the RIB.
-
For the current delete message, there could be a corresponding create message earlier on the queue if the create message could not be published (see update/create message type section above). If there is a corresponding create message earlier on the queue, delete both create and delete messages (that is, add the ROWIDs to the list of ROWIDs to be eventually deleted), and do not publish anything.
Finally, perform DML cleanup if a message is going to be published.
-
Call UPDATE_QUEUE_TABLE to perform DML using the global record that keeps track of QUEUE records to update/delete.
-
If the message type is ITEM_ADD, update the item's ITEM_PUB_INFO to published = 'Y'. Delete other records for the same item if message type is RMSMFM_ITEMS.ITEM_FULREP to ensure that this will not be published for ITEM_CRE messages.
-
If the message type is ITEM_DEL, delete the item's ITEM_PUB_INFO record.
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 item header key values (item). I_rowid is the rowid of the item_mfqueue row fetched from GETNXT.
-
Call BUILD_HEADER_OBJECT to get a header level Oracle Object.
-
Call BUILD_DETAIL_OBJECTS to get a table of detail level Oracle objects and a table of ITEM_MFQUEUE rowids to delete with and a table of detail table rowids to update publish_ind with.
-
Update ITEM_PUB_INFO.published to 'Y' or 'I' depending on if all details are published.
-
Delete records from the ITEM_MFQUEUE for all rowids returned by BUILD_DETAIL_OBJECTS. Deletes are done by rowids instead of business transaction keys to ensure that nothing is deleted off the queue that has not been published.
-
If the entire business transaction was added to the Oracle Object, also delete the ITEM_MFQUEUE record that was picked up by GETNXT. If the entire business transaction was not published, the system must leave something on the ITEM_MFQUEUE to ensure that the rest of it is picked up by the next call to GETNXT.
-
Update the detail tables publish_ind column to 'Y' by each detail table of rowids returned from BUILD_DETAIL_OBJECTS.
-
The header and detail level Oracle Objects are combined and returned.
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_MFQUEUE record. I_function_keys contains detail level key values (item and optional detail keys).
If the error is a non-fatal error, HANDLE_ERRORS passes the sequence number of the driving ITEM_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, 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.
Package name: RMSMFM_ITEMS_BUILD
Spec file name: rmsmfm_items.pls
Body file name: rmsmfm_itemb.pls
Function Level Description - BUILD_MESSAGE
Function: BUILD_MESSAGE (O_error_msg OUT VARCHAR2, O_message IN OUT NOCOPY "RIB_ItemDesc_REC", O_rowids_rec IN OUT NOCOPY ROWIDS_REC, O_record_exists IN OUT BOOLEAN, I_message_type IN ITEM_MFQUEUE.MESSAGE_TYPE%TYPE, I_tran_level_ind IN ITEM_PUB_INFO.TRAN_LEVEL_IND%TYPE, I_queue_rec IN ITEM_MFQUEUE%ROWTYPE)
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 (item).
Call the following:
-
BUILD_HEADER_DETAIL
-
BUILD_SUPPLIER_DETAIL
-
BUILD_COUNTRY_DETAIL
-
BUILD_DIM_DETAIL
-
BUILD_UDA_LOV_DETAIL
-
BUILD_UDA_FF_DETAIL
-
BUILD_UDA_DATE_DETAIL
-
BUILD_IMAGE_DETAIL
-
BUIILD_UPC_DETAIL
-
BUILD_BOM_DETAIL
-
BUILD_TICKET_DETAIL
-
BUILD_RELATED_ITEMS_HEAD
-
BUILD_RELATED_ITEMS_DETAIL (The object built in this function will be a child of the object built in the BUILD_ RELATED_ITEMS_HEAD function based on the relationship_id)
-
BUILD_ITEM_MASTER_CFA_EXT
-
BUILD_ITEM_SUPPLIER_CFA_EXT
-
BUILD_ITEM_SUPP_CTRY_CFA_EXT
Function Level Description - BUILD_DELETE_MESSAGE
Function: BUILD_DETAIL_CHANGE_OBJECTS (O_error_msg OUT VARCHAR2, O_message IN OUT NOCOPY "RIB_ItemDesc_REC", I_message_type IN ITEM_MFQUEUE.MESSAGE_TYPE%TYPE, I_business_obj IN ITEM_KEY_REC)
This function builds a REF Oracle Object to publish to the RIB for all delete message types (for example, ITEM_DEL, ISUP_DEL, ISC_DEL, and others).
The function also checks to see if there is a corresponding Create message for the current delete message. If so, O_create_rowid is set. This is used to determine if the Delete message should be published (see PROCESS_QUEUE_RECORD description above). If both Create and Delete messages are on the queue, neither are published.
Detail creates and detail update messages (DTL_ADD, DTL_UPD). I_business_obj contains the header level key values (item).
Function Level Description - BUILD_HEADER_OBJECT (local)
This private function accepts item header key values (item), builds and returns a header level DESC Oracle Object. Call GET_ITEM_INFO to retrieve data supplementary to ITEM_MASTER. If the item is not found on ITEM_MASTER, O_record_exists is set to FALSE.
Function Level Description - BUILD DETAIL functions (all local)
The following functions have the same format:
-
BUILD_SUPPLIER_DETAIL
-
BUILD_COUNTRY_DETAIL
-
BUILD_DIM_DETAIL
-
BUILD_UDA_LOV_DETAIL
-
BUILD_UDA_FF_DETAIL
-
BUILD_UDA_DATE_DETAIL
-
BUILD_IMAGE_DETAIL
-
BUIILD_UPC_DETAIL
-
BUILD_BOM_DETAIL
-
BUILD_TICKET_DETAIL
-
BUILD_RELATED_ITEMS_HEAD
-
BUILD_RELATED_ITEMS_DETAIL
They have the same specifications, except as noted below.
The functions for building detail nodes for the ITEMDESC message work in the same way. The functions build as many detail Oracle Objects as they can, given the passed in message type and business object keys.
The difference between the different detail functions lies in the data being accessed. BUILD_SUPPLIER_DETAIL retrieves information from ITEM_SUPPLIER, BUILD_COUNTRY_DETAIL retrieves information from ITEM_SUPP_COUNTRY, and so on.
BUILD_SUPPLlER_DETAIL and BUILD_COUNTRY_DETAIL are the only functions that have the input parameter I_orderable_item. This is used to validate orderable items. If an item is orderable, and the initial ITEM_ADD message is being created, at least one supplier node and one supplier/country node are required. This is the only business validation done by the item publisher.
The BUILD_ RELATED_ITEMS_HEAD function retrieves data (item relationship details) from the RELATED_ITEM_HEAD table and builds detail nodes for the ITEMDESC message. Each of these detail nodes has child nodes if the item relationship contains related items records in the RELATED_ITEM_DETAIL table. These child nodes are built by the BUILD_ RELATED_ITEMS_DETAIL function which is called within the BUILD_ RELATED_ITEM_HEAD function. These child nodes are optional for the detail nodes.
If the original create message is being published (I_message_type would be ITEM_ADD)
-
Select all detail records for the business transaction. Return a table of ITEM_MFQUEUE rowids for each message that is placed into the Oracle Object.
-
Since the message being published is ITEM_ADD, there may not be a record on the MFQUEUE table for each detail record that needs to be retrieved. Therefore, no inner join to the MFQUEUE table is done. However, if there are any MFQUEUE records for details, they should be deleted. Therefore, a UNION to a second query is done to select all relevant MFQUEUE records for deletion.
If the message being published is a detail add or detail update (for example, ISUP_ADD, ISUP_UPD, ISC_ADD, ISC_UPD)
-
Select all detail records for the business transaction. Return a table of ITEM_MFQUEUE rowids for each message that is placed into the Oracle Object.
-
Since the message being published is a detail create or update, the only details that should be added to the message are those details that have a record on the MFQUEUE table. Therefore, an inner join between the MFQUEUE table and the business detail table is performed. Any MFQUEUE records retrieved will have their ROWIDs added to the list of ROWIDs that will eventually be deleted.
-
If no records are retrieved for the detail record query, O_records_exist is set to FALSE.
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 does deletes by ROWID. The system also tries to get everything in the same cursor to ensure that the message published matches the deletes that are performed from the ITEM_MFQUEUE table regardless of trigger execution during GETNXT calls.
Function Level Description - GET_ITEM_INFO (local)
This private function gets ITEM_MASTER as input and retrieves supplementary data. For example, each item has a department, class, and subclass. GET_ITEM_INFO will retrieve the descriptions for these three fields. This function is called from BUILD_HEADER_OBJECT.
Function Level Description - BUILD_DIMENSION_DESCRIPTIONS (local)
This private function is similar to GET_ITEM_INFO in that it retrieves supplementary data. This function, however, is called when item/supplier/country/dimension message nodes are being populated. This function is called from BUILD_DIM_DETAIL.
Function Level Description - BUILD_ITEM_MASTER_CFA_EXT (local)
This private function construct a CFA_BASE_TABLE_PRIMARY_KEY_REC object with the Merchandising base table item_master and entity key value (item). Calls CFA_API_SQL.BUILD_NAME_VALUE_PAIR to build and return the entity's customer attributes through RIB_CustFlexAttriVo_TBL. Additionally, query and return the rowids and seq_nos of ITEM_MFQUEUE related to the CFAS change for the entity down the queue. These rows will be deleted by RMSMFM_ITEMS.PROCESS_QUEUE_RECORD.
Function Level Description - BUILD_ITEM_SUPPLIER_CFA_EXT (local)
This private function construct a CFA_BASE_TABLE_PRIMARY_KEY_REC object with the Merchandising base table item_supplier and entity key values (item,supplier). Calls CFA_API_SQL.BUILD_NAME_VALUE_PAIR to build and return the entity's customer attributes through RIB_CustFlexAttriVo_TBL. Additionally, query and return the rowids and seq_nos of ITEM_MFQUEUE related to the CFAS change for the entity down the queue. These rows will be deleted by RMSMFM_ITEMS.PROCESS_QUEUE_RECORD.
Function Level Description - BUILD_ITEM_SUPP_CTRY_CFA_EXT (local)
This private function construct a CFA_BASE_TABLE_PRIMARY_KEY_REC object with the Merchandising base table item_supp_country and entity key values (item,supplier,origin_country_id). Calls CFA_API_SQL.BUILD_NAME_VALUE_PAIR to build and return the entity's customer attributes through RIB_CustFlexAttriVo_TBL. Additionally, query and return the rowids and seq_nos of ITEM_MFQUEUE related to the CFAS change for the entity down the queue. These rows will be deleted by RMSMFM_ITEMS.PROCESS_QUEUE_RECORD.
Trigger Impact
Trigger name: EC_TABLE_IEM_AIUDR.TRG (mod)
Trigger file name: ec_table_iem_aiudr.trg (mod)
Table: ITEM_MASTER
Modify the trigger on the ITEM table to capture Inserts, Updates, and Deletes. Remove all of the code except the code that checks the item_level and tran_level. This is needed to determine which message type to send to the queue, item or UPC (reference item).
Inserts
-
Send the header level item info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.ITEM_ADD or RMSMFM_ITEM.UPC_ADD.
Updates
-
Send the header level item info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.ITEM_UPD or RMSMFM_ITEM.UPC_UPD.
-
Send another header level item info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEMS.ITEM_FULREP if SYSTEM_OPTIONS.PUB_FULL_OBJECTS_IND is 'Y' and current item message types ITEM_UPD, UPC_ADD, UPC_UPD
Deletes
-
Send the header level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.ITEM_DEL or RMSMFM_ITEM.UPC_DEL.
-
Send another header level item info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEMS.ITEM_FULREP if SYSTEM_OPTIONS.PUB_FULL_OBJECTS_IND is 'Y' and current item message type is UPC_DEL
In all these cases, build the function keys for ADDTOQ with item.
Trigger name: EC_TABLE_ISP_AIUDR.TRG (mod)
Trigger file name: ec_table_isp_aiudr.trg (mod)
Table: ITEM_SUPPLIER
Populate the ITEM_MFQUEUE table according to the message type. Make sure that only transaction level items are added to the ITEM_MFQUEUE table.
-
Inserts: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_ADD.
-
Updates: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_UPD.
-
Deletes: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_DEL.
In all these cases, build the function keys for ADDTOQ with item and supplier.
Trigger name: EC_TABLE_ISC_AIUDR.TRG (mod)
Trigger file name: ec_table_isc_aiudr.trg (mod)
Table: ITEM_SUPP_COUNTRY
Populate the ITEM_MFQUEUE table according to the message type. Make sure that only transaction level items are added to the ITEM_MFQUEUE table.
-
Inserts: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_ADD.
-
Updates: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_UPD.
-
Deletes: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_DEL.
In all these cases, build the function keys for ADDTOQ with item, supplier and origin_country_id.
Trigger name: EC_TABLE_ISD_AIUDR.TRG (mod)
Trigger file name: ec_table_isd_aiudr.trg (mod)
Table: ITEM_SUPP_COUNTRY_DIM
Populate the ITEM_MFQUEUE table according to the message type. Make sure that only transaction level items are added to the ITEM_MFQUEUE table.
-
Inserts: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_ADD.
-
Updates: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_UPD.
-
Deletes: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_DEL.
In all these cases, build the function keys for ADDTOQ with item, supplier, origin_country_id.
Trigger name: EC_TABLE_PKS_AIUDR.TRG (mod)
Trigger file name: ec_table_pks_aiudr.trg (mod)
Table: PACKITEM_BREAKOUT
This trigger captures inserts, updates and deletes on the table. It populates a PL/SQL table of records, RMSMFM_ITEMS.BOM_TABLE, which will be used in the statement trigger to build an XML message and place it on the item queue.
Trigger name: EC_TABLE_PKS_IUDS.TRG (mod)
Trigger file name: ec_table_pks_aiudr.trg (mod)
Table: PACKITEM_BREAKOUT
This trigger will group all of the data currently stored in the PL/SQL table of records populated by the EC_TABLE_PKS_AIUDR trigger, and call RMSMFM_ADDTOQ for every pack component in the table of records.
Send another detail level info to the ADDTOQ procedure in the MFM with an input mssage type RMSMFM_ITEM.ITEM_FULREP if SYSTEM_OPTIONS.PUB_FULL_OBJECTS_IND is 'Y' and current record's message type is BOM_CRE, BOM_MOD, BOM_DEL.
Trigger name: EC_TABLE_UIT_AIUDR.TRG (mod)
Trigger file name: ec_table_uit_aiudr.trg (mod)
Table: UDA_ITEM_DATE
Populate the ITEM_MFQUEUE table according to the message type. Make sure that transaction level items and above are added to the ITEM_MFQUEUE table.
-
Inserts: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_ADD.
-
Updates: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_UPD.
-
Deletes: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_DEL.
In all these cases, build the function keys for ADDTOQ with item, uda_id.
Trigger name: EC_TABLE_UIF_AIUDR.TRG (mod)
Trigger file name: ec_table_uif_aiudr.trg (mod)
Table: UDA_ITEM_FF
Populate the ITEM_MFQUEUE table according to the message type. Make sure that transaction level items and above are added to the ITEM_MFQUEUE table.
-
Inserts: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_ADD.
-
Updates: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_UPD.
-
Deletes: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_DEL.
In all these cases, build the function keys for ADDTOQ with item, uda_id.
Trigger name: EC_TABLE_UIL_AIUDR.TRG (mod)
Trigger file name: ec_table_uil_aiudr.trg (mod)
Table: UDA_ITEM_LOV
Populate the ITEM_MFQUEUE table according to the message type. Make sure that transaction level items and above are added to the ITEM_MFQUEUE table.
-
Inserts; Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_ADD.
-
Updates: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_UPD.
-
Deletes: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_DEL.
In all these cases, build the function keys for ADDTOQ with item, uda_id and uda_value.
Trigger name: EC_TABLE_RIH_AIUDR.TRG (mod)
Trigger file name: ec_table_rih_aiudr.trg (mod)
Table: RELATED_ITEM_HEAD
Populate the ITEM_MFQUEUE table according to the message type. Make sure that only transaction level items are added to the ITEM_MFQUEUE table.
-
Inserts: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_ADD.
-
Updates: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_UPD.
-
Deletes: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_DEL.
In all these cases, build the function keys for ADDTOQ with item and relationship_id.
Trigger name: EC_TABLE_RID_AIUDR.TRG (mod)
Trigger file name: ec_table_rid_aiudr.trg (mod)
Table: RELATED_ITEM_DETAIL
Populate the ITEM_MFQUEUE table according to the message type. Make sure that only transaction level items are added to the ITEM_MFQUEUE table.
-
Inserts: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_ADD.
-
Updates: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_UPD.
-
Deletes: Send the detail level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEM.DTL_DEL.
In all these cases, build the function keys for ADDTOQ with item, relationship_id and related_item.
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) |
---|---|---|
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 |
Table Impact
TABLE | SELECT | INSERT | UPDATE | DELETE |
---|---|---|---|---|
ITEM_MFQUEUE |
Yes |
Yes |
Yes |
Yes |
ITEM_PUB_INFO |
Yes |
Yes |
Yes |
Yes |
ITEMLOC_MFQUEUE |
Yes |
No |
No |
Yes |
ITEM_MASTER |
Yes |
No |
No |
No |
ITEM_SUPPLIER |
Yes |
No |
No |
No |
ITEM_SUPP_COUNTRY |
Yes |
No |
No |
No |
ITEM_SUPP_COUNTRY_DIM |
Yes |
No |
No |
No |
UDA_ITEM_LOV |
Yes |
No |
No |
No |
UDA_ITEM_DATE |
Yes |
No |
No |
No |
UDA_ITEM_FF |
Yes |
No |
No |
No |
ITEM_IMAGE |
Yes |
No |
No |
No |
PACKITEM_BREAKOUT |
Yes |
No |
No |
No |
V_PACKSKU_QTY |
Yes |
No |
No |
No |
ITEM_TICKET |
Yes |
No |
No |
No |
CODE_DETAIL |
Yes |
No |
No |
No |
DEPS |
Yes |
No |
No |
No |
CLASS |
Yes |
No |
No |
No |
SUBCLASS |
Yes |
No |
No |
No |
V_DIFF_ID_GROUP_TYPE |
Yes |
No |
No |
No |
ITEM_ZONE_PRICE |
Yes |
No |
No |
No |
PACKITEM |
Yes |
No |
No |
No |
RELATED_ITEM_HEAD |
Yes |
No |
No |
No |
RELATED_ITEM_DETAIL |
Yes |
No |
No |
No |
ITEM_MASTER_CFA_EXT |
Yes |
No |
No |
No |
ITEM_SUPPLIER_CFA_EXT |
Yes |
No |
No |
No |
ITEM_SUPP_COUNTRY_CFA_EXT |
Yes |
No |
No |
No |
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 removes 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, as 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.
Item Location
This section describes the item location publication API.
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 | Required? | 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 | Required? | 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 | Required? | 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:
|
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:
|
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:
|
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 | Required? | 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 | Required? | 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:
|
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.
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.
Deleted Division
When an existing division is deleted, this will also trigger a delete transaction which will 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.
New Group
When a new group is created in Merchandising, it triggers a message notifying 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.
Deleted Group
When an existing group is deleted, this will also trigger a delete transaction message to notify external systems that this group is no longer valid. The delete message will include only the group ID being deleted.
New Department
When a new department is created in Merchandising, it triggers a message notifying 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 calculation type, markup calculation type, OTB calculation 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.
Deleted Department
When an existing department is deleted, this will trigger a delete transaction message to notify external systems that this department is no longer valid. The delete message will include only the department ID being deleted.
New Class
When a new class is created in Merchandising, it triggers a message notifying 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.
Deleted Class
When an existing class is deleted, this will also trigger a delete transaction message to notify external systems that this class is no longer valid. The delete message will include only the class ID being deleted.
New Subclass
When a new subclass is created in Merchandising, it triggers a message notifying 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 details of the update. The update message, like create, will contain the full details in the message for all fields including custom flex attributes.
Deleted Subclass
When an existing subclass is deleted, this will trigger a delete transaction message notifying external systems that this class is no longer valid. The delete message will include only the subclass ID 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 (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 |
Organizational Hierarchy Publication API
This section describes the organization hierarchy publication API.
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 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. Parent ID and parent level will be null when creating a new chain.
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.
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.
Business Overview
Merchandising publishes data about partners in messages to Retail Integration Bus (RIB). Other application that needs to keep their partner synchronized with Merchandising subscribe to these messages.
External Finishers
External finishers are created as partners in Merchandising, and given the Partner Type 'E', indicating that the partner is an External finisher. Once a new external finisher is set up in Merchandising, a trigger on the partner table adds the external finisher to a new queue table. Information on that table is published via the RIB. A conversion of this RIB message converts the external finisher to a 'Location' so that it can be consumed by the location APIs of external systems such as RWMS.
RWMS and other integration subsystems subscribe to the external finisher through their location subscription APIs. A RIB TAFR parses the partner messages of partner type 'E' and returns location attributes for RWMS and other integration subsystems to subscribe to. Merchandising ensures that there will never be duplicates among the partner ID, store ID and warehouse ID.
The RWMS transfer subscription process does not check for location types. As a result, transfers involving an external finisher are treated like any other location types.
To facilitate the routing of external finisher and primary address of the primary address type, header level routing info will contain the name of 'partner_type' with value 'E'. Detail level routing info will contain the name of 'primary_addr_type_ind' with value of 'Y' or 'N' and the name of 'primary_addr_ind' with value of 'Y' or 'N'.
This will allow the RIB to route the external finishers and their addresses to the correct applications.
Merchandising will publish to the RIB create, mod and delete messages of partners along with their multiple addresses via a partner publishing message.
The insert/update/delete on the partner table and the addr table with module 'PTNR' (for partner) will be published. The output message will be in hierarchical structure, with partner information at the header level and the address information at the detail level. Because this is a low volume publisher, multi-threading capability is not supported. In addition, the system assumes that it only needs to publish the current state of the partner, not every change.
If multiple addresses are associated with a partner, this publisher is designed with the assumption that RWMS and other integration subsystems only subscribe to the primary address of the primary address type.
Package Impact
Filename: rmsmfm_partnerb.pls
Function Level Description - ADDTOQ
Function: ADDTOQ(O_error_mesage OUT VARCHAR2, I_message_type IN VARCHAR2, I_functional_keys IN PARTNER_KEY_REC)
This public function puts a partner message on PARTNER_MFQUEUE for publishing to the RIB. It is called from both partner trigger and address trigger. The I_functional_keys will contain partner_type, partner_id and optionally, addr_key.
The information from the PARTNER_MFQUEUE and PARTNER_PUB_INFO 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 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
This public procedure performs the same tasks as GETNXT except that it only loops for a specific row in the PARTNER_MFQUEUE table. The record on PARTNER_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 partner header key values (partner type and partner_id). I_rowid is the rowid of the partner_mfqueue row fetched from GETNXT.
Function Level Description - BUILD_HEADER_OBJECT (local)
Function: BUILD_HEADER_OBJECT(O_error_msg OUT VARCHAR2, O_rib_partnerdesc_rec IN OUT NOCOPY "RIB_PartnerDesc_REC", I_business_obj IN PARTNER_KEY_REC)
This private function accepts partner header key values (partner type and partner ID), builds and returns a header level DESC Oracle Object.
Function Level Description - BUILD_HEADER_OBJECT (local)
This overloaded private function accepts partner header key values (partner type and partner ID), builds and returns a header level REF Oracle Object.
This function calls the BUILD_PARTNER_CFA_EXT to build the RIB_CustFlexAttriVo_TBL for partner's customer attributes and attach it to the header level REF Oracle Object.
Function Level Description - BUILD_DETAIL_OBJECTS (local)
This private function is responsible for building detail level DESC Oracle Objects. It builds as many detail Oracle Objects as it can given the passed in message type and business object keys (partner type and partner ID).
Function Level Description - BUILD_SINGLE_DETAIL (local)
This private function takes in an address record and builds a detail level Oracle Object. Also it determines 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 partner's address 's 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 and detail update messages (DTL_ADD, DTL_UPD). I_business_obj contains the header level key values (partner type and partner ID).
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 (partner type and partner ID).
Function Level Description - LOCK_THE_BLOCK (local)
This private function locks all queue records for the current business object (partner type and partner ID). 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 PARTNER_MFQUEUE record. I_function_keys contains detail level key values (partner_type, partner_id, addr_key).
If the error is a non-fatal error, HANDLE_ERRORS passes the sequence number of the driving PARTNER_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 - DELETE_QUEUE_REC (local)
This private function will delete the records from PARTNER_MFQUEUE table for the sequence no passed in as input parameter.
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) |
---|---|---|
PartnerCre |
Partner Create Message |
PartnerDesc.xsd |
PartnerMod |
Partner Modify Message |
PartnerDesc.xsd |
PartnerDel |
Partner Delete Message |
PartnerRef.xsd |
PartnerDtlCre |
Partner Detail Create Message |
PartnerDtlDesc.xsd |
PartnerDtlMod |
Partner Detail Modify Message |
PartnerDtlDesc.xsd |
PartnerDtlDel |
Partner Detail Delete Message |
PartnerDtlRef.xsd |
Purchase Orders
This section describes the order publication API.
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 |
Receiver Unit Adjustment Publication API
This section describes the receiver unit adjustment publication API.
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.
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 |
RTV Request Publication API
This section describes the RTV request publication API.
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:
-
Adding one supplier.
-
Selecting the sending locations.
-
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:
-
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.
-
The published indicator from the RTVREQ_PUB_INFO table.
-
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:
-
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.
-
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
Seasons and Phases Publication API
This section describes the season phase publicatoin API.
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.
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.
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.
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.
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.
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.
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 |
Transfers Publication API
This section describes the transfers publication API.
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.
Create Header
-
Prerequisites: None.
-
Activity Detail: The first step to creating a transfer is creating the header level information.
-
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
-
Prerequisites: A transfer must exist and have at least one detail before it can be approved.
-
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.
-
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
-
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.
-
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.
-
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
-
Prerequisites: A transfer header record must exist before transfer details can be created.
-
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.
-
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
-
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.
-
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.
-
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
-
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.
-
Activity Detail: A user is allowed to delete details from a transfer but only if the item has not been shipped.
-
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
-
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.
-
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.
-
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
-
Prerequisites: A transfer can only be deleted when it is still in approved status or when it has been closed.
-
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.
-
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:
-
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.
-
The published indicator from the TRANSFERS_PUB_INFO table.
-
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:
-
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.
-
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.
-
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.
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.
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.
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 Management (SIM). Supplier information is published when new suppliers are created, updates are made to existing suppliers, or existing suppliers 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 of 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.
Vendor Detail 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 Detail 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.
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.
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.
Flex Attributes
If any custom flex attributes (CFAS) for the supplier have been added or modified, it will trigger a header 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 Vendor message.
Similarly, if any CFAS for the supplier address has been added or modified, it will trigger a vendor detail 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 Vendor Address message.
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. 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.
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).
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 |
Work Orders In Publication API
This section describes the work order in publication API.
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.
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.
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.
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 in order 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, which triggers a message notifying 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
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.
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 in order 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 |