| Oracle® Retail Merchandising Foundation Cloud Service Operations Guide Volume 2 - Message Publication and Subscription Designs Release 16.0.029 F17091-01 |
|
 Previous |
 Next |
| Oracle® Retail Merchandising Foundation Cloud Service Operations Guide Volume 2 - Message Publication and Subscription Designs Release 16.0.029 F17091-01 |
|
Previous |
Next |
This chapter provides an overview of the Publication APIs used in the RMS environment and various functional attributes used in the APIs.
For more information on RIB_XML, see Appendix A, "RIB_XML".
This section describes the allocations publications API.
RMS is responsible for communicating allocation information with external systems such as Oracle Retail Store Inventory Management (SIM) or Oracle Retail Warehouse Management System (RWMS).
Allocation data enters RMS through the following ways:
Through the Oracle Retail Allocation product.
These allocations are written to the ALLOC_HEADER and ALLOC_DETAIL tables in 'R'- Reserved or 'A'- Approved status. Once a detail and a header message have been queued and approved, a message is published to the RIB.
Through the semi-automatic ordering option.
Using this replenishment method, allocations and orders are inserted into the ALLOC_HEADER and ALLOC_DETAIL tables in worksheet status to be manually approved. In order for allocation messages to be published to the RIB, the allocation must at least be in approved status. Worksheet messages remain on the queue and combined until they are approved. When it is approved, the created message is published to the RIB.
Through automatic replenishment allocations.
These allocations are initially set in worksheet status and are approved by the RPLAPPRV.PC batch program (Replenishment Approve). Only messages for approved allocations are published to the RIB.
Through the Allocation subscription RIB API.
Either a 3rd party Merchandise System or AIP can create allocations in RMS. Once approved, these allocations are published to the RIB.
Allocations can be created from a warehouse to any type of stockholding location in RMS, including both company and franchise stores. Allocations include a store type and stockholding indicator at the detail level when allocating to stores, to allow SIM and RWMS 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.
An allocation and its details are not published until it is approved. Modified and deleted allocation information is also sent to the RIB. Allocation header modification messages will be sent if the status of the allocation is changed to 'C' - closed 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 complete allocation can be deleted.
This section describes the package impact.
Prerequisites: Allocation can be created in one of the following manners: via the stand-alone allocations product, semi-automatic ordering, automatic ordering replenishment, or Allocation subscription API.
Activity Detail: Once an allocation exists in RMS it can be modified and details can be attached.
Messages: 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 detail records have been queued and the allocation has been approved.
Prerequisites: An allocation must exist before it can be modified.
Activity Detail: The user is allowed to change the status of the allocation to 'A'- Approved or 'C'- Closed. This change is of interest to other systems and so this activity results in the publication a message.
Messages: When an allocation is modified, an Allocation Header Modified message request is queued. The Allocation Header Modified message is a flat message containing a full snapshot of the allocation header at the time the message is published.
Prerequisites: An allocation header must exist before an allocation detail can be created or interfaced into RMS. Once in RMS, the allocation can only be modified by changing its allocated quantity.
Activity Detail: an Allocation Detail Create message is only queued if a Create Header message is also on the queue for the same allocation.
Messages: When an allocation detail is created, an Allocation Detail Created message request is queued. The Allocation Detail Create message is a flat message containing a full snapshot of the allocation detail at the time the message is published. If an Allocation Create message is also in the queue for the same allocation, the two messages are combined and sent as one message.
Prerequisites: An allocation detail must exist to be modified.
Activity Detail: The user is allowed to change allocation quantities provided they are not reduced below those already recorded as received. This change is of interest to other systems and so this activity results in the publication of a message.
Messages: When an allocation is modified an Allocation Detail Modified message request is queued. The Allocation Detail Modified message is a flat message containing a full snapshot of the allocation detail at the time the message is published.
Prerequisites: An allocation must exist in RMS before it can be approved. Those allocations created from other sources can be entered into RMS in approved status.
Activity Detail: Once an allocation as been approved, it it will be published from RMS.
Messages: When the allocation is approved an Allocation Header Modified message is queued. This message will be combined with any Allocation Create and Allocation Detail Create message to form the message that is sent to the RIB.
Prerequisites: An allocation must be approved before it can be closed.
Activity Detail: Closing an allocation changes the status, which prevents further receiving or modification of the allocation. When an allocation is closed, a message is published to update other systems regarding the status change.
Messages: Closing an allocation queues an Allocation Header Modified message request. This is a flat message containing a full snapshot of the allocation at the time that the message is published.
Prerequisites: An allocation can only be deleted when it is still in approved status or when it has been closed.
|
Note: If the allocation is in closed status, it still cannot be deleted if either create or a modify message are pending for the allocation, as they need to take full snapshots. |
Activity Detail: Deleting an allocation removes it from the system. External systems are notified by a published message.
Message: When an allocation is deleted, an Allocation Header Deleted message, which is a flat notification message, is queued.
Body File Name: rmsmfm_allocb.pls
FUNCTION ADDTOQ ( O_error_msg OUT VARCHAR2,
I_message_type IN ALLOC_MFQUEUE.MESSAGE_TYPE%TYPE,
I_alloc_no IN ALLOC_HEADER.ALLOC_NO%TYPE,
I_alloc_header_status IN ALLOC_HEADER.STATUS%TYPE,
I_to_loc IN ITEM_LOC.LOC%TYPE)
This function is called by the ALLOC_HEADER trigger and the ALLOC_DETAIL trigger, ec_table_alh_aiudr and ec_table_ald_aiudr, respectively.
For header level insert messages (HDR_ADD), insert a record in the ALLOC_PUB_INFO table. The published flag will be set to 'N'. The correct thread for the business transaction will be calculated and written. Call API_LIBRARY.RIB_SETTINGS to get the number of threads used for the publisher. The number of threads and the business object ID are used to calculate the thread value.
For all records except header level inserts (HDR_ADD), the thread_no and initial_approval_ind will be queried from the ALLOC_PUB_INFO table.
If the business transaction has not been approved (initial_approval_ind = 'N') and the triggering message is one of DTL_ADD, DTL_UPD, DTL_DEL, HDR_DEL, no processing will take place and the function will exit.
For detail level message deletes (DTL_DEL), we only need one (the most recent) record per detail in the ALLOC_MFQUEUE. Delete any previous records that exist on the ALLOC_MFQUEUE for the record that has been passed. If the publish_ind is 'N', do not add the DTL_DEL message to the queue.
For detail level message updates (DTL_UPD), we only need one DTL_UPD (the most recent) record per detail in the ALLOC_MFQUEUE. Delete any previous DTL_UPD records that exist on the ALLOC_MFQUEUE for the record that has been passed.
For header level delete messages (HDR_DEL), delete every record in the queue for that allocation.
For header level update message (HDR_UPD), update the ALLOC_PUB_INFO.INITIAL_APPROVAL_IND to 'Y' if the allocation is in approved status.
For all records except header level inserts (HDR_ADD), insert a record into the ALLOC_MFQUEUE.
It returns a status code of API_CODES.SUCCESS if successful, API_CODES.UNHANDLED_ERROR if not.
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)
The RIB calls GETNXT to get messages. It performs a cursor loop on the unpublished records on the ALLOC_MFQUEUE table (PUB_STATUS = 'U'). It will only need to execute one loop iteration in most cases. For each record retrieved, GETNXT does 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.
If the lock is successful, 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 ALLOC_MFQUEUE and ALLOC_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.
PROCEDURE PUB_RETRY
This procedure republishes the entity that failed to be published before. It is the same as GETNXT except that the record on ALLOC_MFQUEUE to be published must match the passed in sequence number contained in the ROUTING_INFO.
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.
Call API_LIBRARY.RIB_SETTINGS to get the number of threads used for the publisher. The number of threads and the business object ID are used to calculate the thread value.
For a header delete message (HDR_DEL) that has not been initially published, simply remove the header delete message from the queue and loop again.
For a header delete message (HDR_DEL) that has been initially published i.e. for AllocRef.
Build the Oracle Object to publish to RIB.
Build the ROUTING_INFO.
Delete the record from ALLOC_PUB_INFO.
Delete the record from ALLOC_DETAILS_PUBLISHED.
Remove the header delete message from the queue (ALLOC_MFQUEUE).
If the business object is being published for the first time i.e. published_ind on the pub_info table is 'N', the business object is being published for the first time. If so, call MAKE_CREATE.
Otherwise, For a header update message (HDR_UPD).
Call BUILD_HEADER_OBJECT to build the Oracle Object to publish to the RIB.
Update ALLOC_PUB_INFO with updated new header information.
Build the ROUTING_INFO.
Delete the header update message from the queue (ALLOC_MFQUEUE).
For a detail add (DTL_ADD) or detail update message (DTL_UPD).
Call BUILD_DETAIL_CHANGE_OBJECTS to build the Oracle Object to publish to the RIB. This will also take care of any ALLOC_MFQUEUE deletes and ROUTING_INFO logic.
For a detail delete message (DTL_DEL).
Call BUILD_DETAIL_DELETE_OBJECTS to build the Oracle Object to publish to the RIB. This will also take care of any ALLOC_MFQUEUE and ALLOC_DETAILS_PUBLISHED deletes and the ROUTING_INFO logic.
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.
Build some or all of the ROUTING_INFO Oracle Object.
Call BUILD_DETAIL_OBJECTS to get a table of detail level Oracle objects and a table of ALLOC_MFQUEUE rowids to delete.
Use the header level Oracle Object and functional holders to update the ALLOC_PUB_INFO.
Delete records from the ALLOC_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 ALLOC_MFQUEUE record that was picked up by GETNXT. If the entire business transaction was not published, we need to leave something on the ALLOC_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.
Accepts header key values, performs necessary lookups, builds and returns a header level Oracle Object.
Optionally can return needed Functional Holders for the ALLOC_PUB_INFO table.
The C_ALLOC_HEAD cursor selects the context fields (context and value) from the ALLOC_HEADER table.
The context fields will be passed along in the parameter list of the rib object constructor "RIB_AllocDesc_REC()".
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.
If the function is being called from MAKE_CREATE:
Select any unpublished detail records from the business transaction (use an indicator on the functional detail table itself or ALLOC_DETAILS_PUBLISHED). Create Oracle Objects for details that are selected by calling BUILD_SINGLE_DETAIL.
Ensure that the indicator in the functional detail table is updated as published as the detail info are placed into the Oracle Objects
Ensure that ALLOC_MFQUEUE is deleted as needed. If there is more than one ALLOC_MFQUEUE record for a detail level record, make sure they all get deleted. We only care about current state, not every change.
Ensure that ROUTING_INFO is constructed if routing information is stored at the detail level in the Business transaction.
Ensure that no more than MAX_DETAILS_TO_PUBLISH records are put into Oracle Objects.
Ensure that the detail records being added to the object have not already been published. This can happen if GETNXT was previously called for the current business object, and the MAX_DETAILS_TO_PUBLISH limit had been reached. We ensure these details do not get added again by looking at the indicator in the functional detail table.
If the function is not being called from MAKE_CREATE:
Select any details on the ALLOC_MFQUEUE that are for the same business transaction and for the same message type. Create Oracle Objects for details that are selected by calling BUILD_SINGLE_DETAIL.
If the message type is a detail create (DTL_ADD), ensure that records get inserted into ALLOC_DETAILS_PUBLISHED or the indicator in the functional detail table is updated as published because the detail info are placed into the Oracle Objects.
Ensure that ALLOC_MFQUEUE is deleted from as needed.
Ensure that ROUTING_INFO is constructed if routing information is stored at the detail level in the Business transaction.
Ensure that no more than MAX_DETAILS_TO_PUBLISH records are put into Oracle Objects.
The deletes are done by ROWID to make sure that records from the queue table that has not been published are not deleted.
Accept inputs and build a detail level Oracle Object. Perform any lookups needed to complete the Oracle Object.
Either pass in a header level Oracle Object or call BUILD_HEADER_OBJECT to build one.
Call BUILD_DETAIL_OBJECTS to get the detail level Oracle Objects.
Perform any BULK DML statements given the output from BUILD_ DETAIL_OBJECTS and update to ALLOC_DETAILS_PUBLISHED.
Build any ROUTING_INFO as needed.
Either pass in a header level delete Oracle Object or build a header level delete Oracle Object.
Perform a cursor for loop on ALLOC_MFQUEUE and build as many detail delete Oracle Objects as possible without exceeding the MAX_DETAILS_TO_PUBLISH.
Perform any BULK DML statements for deletion from ALLOC_MFQUEUE and update to ALLOC_DETAILS_PUBLISHED.
Build any ROUTING_INFO as needed.
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.
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 ALLOC_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'.
This function deletes a specific record on ALLOC_MFQUEUE table depending on the seq_no.
This function will get all the values of to_loc_vir from alloc_details_published table depending on a given allocation number.
Perform a cursor for loop that will populate the Oracle Object RIB_ROUTINGINFO_TBL.
This function checks if the variable (LP_nbf_days) has a value or not. If not, it will populate the variable based on code_detail and then assign this value to the variable O_days.
Trigger name: EC_TABLE_ALH_AIUDR
Trigger file name: ec_table_alh_aiudr.trg
Table: ALLOC_HEADER
Inserts: Send the allocation header level information to the ADDTOQ procedure in RMSMFM_ALLOC with the message type RMSMFM_ALLOC.HDR_ADD and the original message.
Updates: Send the allocation header level information to the ADDTOQ procedure in the RMSMFM_ALLOC with the message type RMSMFM_ALLOC.HDR_UPD and the original message.
Deletes: Send the allocation header level info to the ADDTOQ procedure in the RMSMFM_ALLOC with the message type RMSMFM_ALLOC.HDR_DEL and the original message.
Trigger name: EC_TABLE_ALD_AIUDR
Trigger file name: ec_table_ald_aiudr.trg
Table: ALLOC_DETAIL
Inserts: Send the allocation detail level information to the ADDTOQ procedure in RMSMFM_ALLOC with the message type RMSMFM_ALLOC.DTL_ADD and the original message.
Updates: Send the allocation detail level information to the ADDTOQ procedure in the RMSMFM_ALLOC with the message type RMSMFM_ALLOC.DTL_UPD and the original message.
Deletes: Send the allocation detail level info to the ADDTOQ procedure in the RMSMFM_ALLOC with the message type RMSMFM_ALLOC.DTL_DEL and the original message.
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 | SML Schema Definition (XSD) |
|---|---|---|
| AllocCre | Allocation Create Message | AllocDesc.xsd |
| AllocHdrMod | Allocation Header Modify Message | AllocDesc.xsd |
| AllocDel | Allocation Delete Message | AllocRef.xsd |
| AllocDtlCre | Allocation Detail Create Message | AllocDesc.xsd |
| AllocDtlMod | Allocation Detail Modify Message | AllocDesc.xsd |
| AllocDtlDel | Allocation Detail Delete Message | AllocRef.xsd |
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| ALLOC_PUB_INFO | Yes | Yes | Yes | No |
| ALLOC_MFQUEUE | Yes | Yes | No | Yes |
| ALLOC_DETAILS_PUBLISHED | Yes | Yes | Yes | Yes |
| ALLOC_HEADER | Yes | No | No | No |
| ALLOC_DETAIL | Yes | No | No | No |
| ITEM_MASTER | Yes | No | No | No |
| ITEM_TICKET | Yes | No | No | No |
| ITEM_LOC | Yes | No | No | No |
| WH | Yes | No | No | No |
| ORDHEAD | Yes | No | No | No |
| CODE_DETAIL | Yes | No | No | No |
| V_PACKSKU_QTY | Yes | No | No | No |
This section describes the ASNOUT Publication API.
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 RMS to stores or warehouses.
RMS supports the following shipping functionality:
On-line Shipping/Receiving.
Franchise Order Shipment and Return.
Two system options (ship_rcv_store and ship_rcv_wh) are used to control whether RMS 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 RMS.
Ship_rcv_wh = 'Y' means a warehouse management system, such as RWMS, is NOT installed and shipping/receiving for warehouses will be done in RMS.
If either (but not both) of these indicators is set to 'Y', shipments created in RMS 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 | RMS 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 |
RMS 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 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 RMS, 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 RMS.
RMS supports two kinds of franchise stores - stockholding franchise stores (which RMS manages inventory and financials like regular stores) and non-stockholding franchise stores (which RMS 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 RMS 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 RMS automatically creates the shipment for non-stockholding stores upon the approval of a franchise return, RMS needs to publish those shipments for RWMS. Similar to on-line Shipping/Receiving, RMS publishes shipments of non-stockholding Franchise Returns to warehouses as ASNOut messages.
This section describes the package impact.
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 RMS cannot be modified. Upon saving a shipment, the entire shipment is published from RMS as one ASNOut message. As a result, RMS 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.
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".
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.
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.
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 (RMS) 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, RMS sends 1 instead of NULL in SHIPMENT.NO_BOXES if it is NULL.
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, RMS will send "1" for container_qty and "0" for container_id instead of NULL.
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.
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 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.
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 |
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 RMS should NOT go back to RMS again.
ASNOut messages published from RMS 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.
This section describes the banner publication API.
RMS 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 diagram Banners and Channels within a Corporation shows a sample of the structure of banners and channels within a corporation.
Banner/channel publication consists of a single flat message containing information from the tables BANNER and CHANNELS. One message is synchronously created and placed in the message queue each time a record is created, modified, or deleted. When a record is created or modified, the flat message contains several attributes of the banner/channel. When a record is deleted, the message contains the unique identifier of the banner/channel. Messages are retrieved from the message queue in the order they were created.
This section describes the package impact.
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.
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.
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.
Procedure: ADDTOQ
(O_status OUT VARCHAR2,
O_text OUT VARCHAR2,
I_message_type IN BANNER_MFQUEUE.MESSAGE_TYPE%TYPE,
I_banner_id IN CHANNELS.BANNER_id%TYPE,
I_channel_id IN CHANNELS.CHANNEL_ID%TYPE,
I_message IN CLOB)
This procedure is called by the triggers EC_TABLE_BAN_AIUDR and EC_TABLE_CHN_AIUDR, and takes the message type, banner ID, channel ID (NULL if called from EC_TABLE_BAN_AIUDR) and the message itself. It inserts a row into the message family queue BANNER_MFQUEUE along with the passed in values and the next sequence number from the message family sequence, setting the status to unpublished. It returns a status code of API_CODES.SUCCESS if successful, API_CODES.UNHANDLED_ERROR if not.
Procedure: GETNXT
(O_STATUS_CODE OUT VARCHAR2,
O_ERROR_MSG OUT VARCHAR2,
O_MESSAGE_TYPE OUT VARCHAR2,
O_MESSAGE OUT CLOB,
O_banner_id OUT NUMBER,
O_channel_id OUT NUMBER)
This publicly exposed procedure is typically called by a RIB publication adaptor. Its parameters are well defined and arranged in a specific order. The message type is the RIB defined short message name. The message is the XML message. The family key consists of the banner ID, which will be populated for all message types, and the channel ID, which can be NULL.
The error text parameter contains application-generated information, such as the application's sequence number of the message that failed, and the Oracle or other error that occurred when the retrieval failed.
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 BANNER_XML.BUILD_MESSAGE to create the XML message, and then calls RMSMFM_BANNER.ADDTOQ to insert this message into the message queue.
Inserts: Sends banner info to the ADDTOQ procedure in the MFM with the message type RMSMFM_FAMILY.BannerDesc and the original message.
Updates: Sends banner info to the ADDTOQ procedure in the MFM with the message type RMSMFM_FAMILY.BannerDesc and the original message
Deletes: Sends banner info to the ADDTOQ procedure in the MFM with the message type RMSMFM_FAMILY.BannerRef and the original message.
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 CHANNEL_XML.BUILD_MESSAGE to create the XML message, and then calls RMSMFM_BANNER.ADDTOQ to insert this message into the message queue.
Inserts: Sends channel info to the ADDTOQ procedure in the MFM with the message type RMSMFM_FAMILY.ChannelDesc and the original message.
Updates: Sends channel info to the ADDTOQ procedure in the MFM with the message type RMSMFM_FAMILY.ChannelDesc and the original message.
Deletes: Sends channel info to the ADDTOQ procedure in the MFM with the message type RMSMFM_FAMILY.ChannelRef and the original message.
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) |
|---|---|---|
| BannerCre | Banner Create Message | BannerDesc.xsd |
| BannerMod | Banner Modify Message | BannerDesc.xsd |
| BannerDel | Banner Delete Message | BannerRef.xsd |
| ChannelsCre | Channels Create Message | ChannelDesc.xsd |
| ChannelsMod | Channels Modify Message | ChannelDesc.xsd |
| ChannelsDel | Channels Delete Message | ChannelRef.xsd |
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.
This section describes the customer order fulfillment confirmation publication API.
When RMS is integrated with an external OMS, one of the supported deployment methods is interfacing customer order fulfillment requests into RMS through the RIB JMS. When RMS processes customer order requests, it will also publish a confirmation message containing the following information:
Customer order number
Fulfillment order number
Confirm Type - 'C' (order fully created), 'P' (order partially created), or 'X' (order not created)
Confirm number - PO or Transfer in RMS
Item
Reference Item
Confirm quantity
Confirm quantity UOM
This section describes the package impact.
A customer order associated with an ordcust_no on ORDCUST is the business object to be published through this API.
Package name: RMSMFM_ORDCUST
Spec file name: rmsmfm_ordcusts.pls
Body file name: rmsmfm_ordcustb.pls
Package Specification - Global Variables
FAMILY RIB_SETTINGS.FAMILY%TYPE := 'fulfilordcfm'; LP_cre_type RIB_TYPE_SETTINGS.TYPE%TYPE := 'fulfilordcfmcre';
ADDTOQ(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message_type IN ORDCUST_PUB_INFO.MESSAGE_TYPE%TYPE,
I_ordcust_no IN ORDCUST.ORDCUST_NO%TYPE)
A trigger on the ORDCUST_PUB_TEMP table will call this function to add the customer order number to the ORDCUST_PUB_INFO table for publishing to the RIB. Only the create message type ('fulfilordcfmcre') is supported.
Validate all the input parameters to this function against NULL. If any has NULL value then return from the function with the appropriate error message.
Insert a record in the ORDCUST_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, customer order number) calculate the thread value.
GETNXT(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
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 ORDCUST_PUB_INFO table (pub_status = 'U'). It should only need to execute one loop iteration in most cases. For each record retrieved, GETNXT gets the following:
1. A lock of the queue table (ORDCUST_PUB_INFO) for the current business object. The lock is obtained by calling the function LOCK_THE_BLOCK. If the record for the current business object is locked, the current message is skipped.
2. The information from the ORDCUST_PUB_INFO table is passed to PROCESS_QUEUE_RECORD. PROCESS_QUEUE_RECORD will build the RIB Object message to pass back to the RIB. If PROCESS_QUEUE_RECORD does not run successfully, GETNXT raises an exception.
3. If any exception is raised in GETNXT, including the exception raised by an unsuccessful call to PROCESS_QUEUE_RECORD, HANDLE_ERRORS is called.
4. Unconditionally exit from the loop after the successful processing of PROCESS_QUEUE_RECORD function, assuming the confirmation message is published successfully.
The loop will need to execute more than once if the record is locked on the queue table for the current business object.
PUB_RETRY(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
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 ORDCUST_PUB_INFO to be published must match the passed in sequence number contained in the ROUTING_INFO.
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,
I_ordcust_no IN ORDCUST_PUB_INFO.ORDCUST_NO%TYPE,
I_seq_no IN ORDCUST_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, customer order number), calculate the thread value.
"Build the header and detail object by calling BUILD_MSG_OBJECT.
"Update the pub_status to 'P' for the current record in the ORDCUST_PUB_INFO table.
"Delete the current record in the ORDCUST_PUB_TEMP table.
"Set LP_error_status to API_CODES.UNHANDLED_ERROR before any DML statements.
No routing information will be included since all published messages will go to OMS and no other applications.
Take all necessary data from the ORDCUST, ORDCUST_DETAIL, ORDHEAD, ORDLOC, TSFHEAD, and TSFDETAIL tables and put into a "RIB_FulfilOrdCfmDesc_REC" object.
This function locks the record 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.
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 ORDCUST_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 name: EC_TABLE_ORP_AIR
Trigger file name: ec_table_orp_air.trg
Table: ORDCUST_PUB_TEMP
The trigger ORDCUST_PUB_TEMP table will capture inserts and send the appropriate column values to the ADDTOQ procedure in the MFM with message type RMSMFM_ORDCUST.LP_cre_type.
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) |
|---|---|---|
| fulfilordcfmcre | Customer Order Fulfillment Confirmation Create Message | FulfilOrdCfmDesc.xsd |
RMS will only publish confirmation 'create' messages associated to a PO or transfer.
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.
OMS is the only subscriber of this message family. Since all published customer order fulfillment confirmation messages will be routed to OMS, no routing info is needed.
This section describes the delivery slot publication API.
RMS provides retailers the option of creating store orders for items with multiple delivery instructions per day for the same item. RMS provides this multiple deliveries per day support by generating multiple purchase orders and/or transfers based on need day and delivery slot.
Since the replenishment batch can be run during the day time, it is necessary to lock the important transaction tables. The following tables are locked for the intraday replenishment:
TSFDETAIL
ITEM_LOC_SOH
ORD_IMV_MGMT
CONTRACT_DETAIL
CONTRACT_HEAD
DEAL_HEAD
ALLOC_CHRG
ALLOC_HEADER
ALLOC_DETAIL
ORDLOC
ORDLOC_REV
ORDLOC_WKSHT
ORDLOC_EXP
ORDCUST
ORDHEAD_REV
ORDSKU
REQ_DOC
TIMELINE
ORDLC
DEAL_ITEMLOC_DIV_GRP
DEAL_ITEMLOC_DCS
DEAL_ITEMLOC_ITEM
DEAL_ITEMLOC_PARENT_DIFF
DEAL_THRESHOLD
DEAL_DETAIL
DEAL_QUEUE
DEAL_CALC_QUEUE
REV_ORDERS
Delivery slot ID publication consists of a single flat message containing the delivery slot details from the table DELIVERY_SLOT. One message will be synchronously created and placed in the message queue each time a delivery_slot_id is created, updated or deleted from delivery_slot. When a delivery_slot_id is created or deleted, the flat message will contain 3 attributes i.e delivery_slot_id, deliver_slot_desc and delivery_slot_sequence. Messages are retrieved from the message queue in the order they were created.
This section describes the package impact.
Prerequisites: Delivery_slot does not already exist.
Activity Detail: Any insert to the DELIVERY_SLOT table inserts a 'dlvysltcre' message_type record on the DELIVERY_SLOT_MFQUEUE table.
Prerequisites: Delivery_slot does already exist.
Activity Detail: Any update to the DELIVERY_SLOT table inserts a 'dlvysltmod' message_type record on the DELIVERY_SLOT_MFQUEUE table.
Prerequisites: Delivery_slot already exist.
Activity Detail: Deleting a delivery_slot_id removes the record from the delivery_slot table and inserts a 'dlvysltdel' row to the DELIVERY_SLOT_MFQUEUE table.
Package name: RMSMFM_DLVYSLT
Spec file name: rmsmfm_dlvyslts.pls
Body file name: rmsmfm_dlvysltb.pls
Package Specification - Global Variables
FAMILY CONSTANT RIB_SETTINGS.FAMILY%TYPE: = 'dlvyslt'; SLT_ADD CONSTANT VARCHAR2 (15): = 'dlvysltcre'; SLT_UPD CONSTANT VARCHAR2 (15) := 'dlvysltmod'; SLT_DEL CONSTANT VARCHAR2 (15): = 'dlvysltdel';
Function:
ADDTOQ (O_status OUT VARCHAR2, O_text OUT VARCHAR2, I_message_type IN DELIVERY_SLOT_MFQUEUE.MESSAGE_TYPE%TYPE, I_delivery_slot_id IN DELIVERY_SLOT_MFQUEUE.DELIVERY_SLOT_ID%TYPE, I_delivery_slot_desc IN DELIVERY_SLOT_MFQUEUE.DELIVERY_SLOT_DESC%TYPE, I_delivery_sequence IN DELIVERY_SLOT_MFQUEUE.DELIVERY_SLOT_SEQUENCE%TYPE);
An event capture trigger calls this procedure with the message type for synchronously captured messages. It inserts a row into the message family queue along with the passed in values, the next sequence number from the message family sequence, and a status of unpublished. Due to the very small data volume of delivery slots, no multi-threading is supported for this publishing. Therefore, the thread_no is always set to 1. It returns the standard publishing API success or failure codes.
Procedure: GETNXT
(O_status_code IN OUT VARCHAR2,
O_error_msg IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
O_message_type IN OUT DELIVERY_SLOT_MFQUEUE.MESSAGE_TYPE%TYPE,
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);
This procedure is publically available and is typically called by a RIB publication adaptor. Its parameters are well defined and arranged in a specific order. The message type is the RIB defined short message name ('dlvyslt') and the message is a RIB object ("RIB_DeliverySlotDesc_REC" for a create and update message, "RIB_DeliverySlotRef_REC" for a delete message).
The error text parameter contains application-generated information, such as the application's sequence number of the message that failed, and the Oracle or other error that occurred when the retrieval failed.
This program loops through each message on the DELIVERY_SLOT_MFQUEUE table, and calls PROCESS_QUEUE_RECORD. When no messages are found, the program exits returning the 'N'o message found API code.
Procedure: 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);
Same as GETNXT except:
It only loops for a specific row in the DELIVERY_SLOT_MFQUEUE table. The record on DELIVERY_SLOT_MFQUEUE must match the sequence number passed in routing info data structure.
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 record from DELIVERY_SLOT_MFQUEUE is a create or update message then
Build and pass the RIB_DeliverySlotDesc_REC object
Delete the record from the delivery_slot_mfqueue table.
If the record from DELIVERY_SLOT_MFQUEUE table is a delete then
Build and pass the RIB_DeliverySlotRef_REC object.
Delete the record from the delivery_slot_mfqueue table.
Create a trigger on Delivery_Slot table to capture inserts and deletes.
Trigger name: EC_TABLE_DLVY_AIUDR.TRG
Trigger file name: ec_table_dlvy_aiudr.trg
Table: Delivery_Slot
Inserts: Send the I_delivery_slot_id, I_delivery_slot_desc, I_delivery_sequence and a message type of 'dlvysltcre' to the ADDTOQ procedure.
Updates: Send the I_delivery_slot_id, I_delivery_slot_desc, I_delivery_sequence and a message type of 'dlvysltmod' to the ADDTOQ procedure.
Deletes: Send the I_delivery_slot_id, I_delivery_slot_desc, I_delivery_sequence and a message type of 'dlvysltdel' to the ADDTOQ procedure-.
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) |
|---|---|---|
| Dlvysltcre | Delivery slot Create Message | DeliverySlotDesc.xsd |
| Dlvysltdel | Delivery slot delete Message | DeliverySlotRef.xsd |
It is not possible for the trigger to know the status of anything modified by GETNXT. If a 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). This also has to occur at the same time GETNXT is processing the current business object.
Delay all DML statements to as late a time as possible. Once DML statements have taken place, any error becomes a fatal error rather than a hospital error.
This section describes the differentiator groups publication API.
Differentiator (Diff) Group publication consists of a single flat message containing diff group attributes from the tables DIFF_GROUP_HEAD and DIFF_GROUP_DETAIL. A message is synchronously created and placed in the message queue each time a diff group (DIFF_GROUP_HEAD) is created, modified, or deleted or when a diff (DIFF_GROUP_DETAIL) is created, modified, or deleted from a diff group. When a diff group (DIFF_GROUP_HEAD) is created or modified, the flat message contains numerous attributes of the group. When a diff group is deleted, the message contains the both unique identifier of the group, and the diff_group_id. When a diff (diff_group_detail) is created or modified, the flat message contains numerous attributes of the diff. When a diff is deleted, the message contains the unique identifier of the diff group and the diff, diff_group_id and diff_id. A Message is retrieved from the message queue in the order they were created.
This section describes the package impact.
Prerequisites: Diff Group does not already exist.
Activity Detail: Any change to the DIFF_GROUP_HEAD table inserts a DiffGrpHdrCre message_type record on the DIFFGRP_MFQUEUE table.
Messages: The DiffGrpHdrDesc message is created. It is a flat, synchronous message containing a full snapshot of the diff group at the time the message is published.
Prerequisites: Diff Group exists.
Activity Detail: Any change to the DIFF_GROUP_HEAD table inserts a DiffGrpHdrMod message_type record on the DIFFGRP_MFQUEUE table.
Messages: The DiffGrpHdrDesc message is created. It is a flat, synchronous message containing a full snapshot of the diff group at the time the message is published.
Prerequisites: A Diff Group already exists, and the diff ID exists on diff_ids, but the diff ID does not exist within the diff group.
Activity Detail: Any Differentiators added to a diff group inserts a record to the DIFF_GROUP_HEAD table. A DiffGrpDtlCre message type record is also inserted on the DIFFGRP_MFQUEUE table. A foreign key to the DIFF_GROUP_HEAD table checks the existence of the diff group the value is created to supplement.
Messages: DiffGrpDtlDesc message type is created. It is a hierarchical, synchronous message containing a snapshot of the DIFF_GROUP_DETAIL table at the time the message is published.
Prerequisites: Diff Group and the Diff ID within the diff group (DIFF_GROUP_DETAIL record) exist.
Activity Detail: Any change to the diffs within a diff group modifies a record to the DIFF_GROUP_HEAD table. A DiffGrpDtlMod message type record is also inserted on the DIFFGRP_MFQUEUE table. A foreign key to the DIFF_GROUP_HEAD table checks the existence of the diff group the value is created to supplement.
Messages DiffGrpDtlDesc message is created. It is a flat, synchronous message containing a snapshot of the DIFF_GROUP_DETAIL table at the time the message is published.
Prerequisites: Diff Group and the Diff ID within the diff group (DIFF_GROUP_DETAIL record) exist.
Activity Detail: Deleting a diff from a Diff Group removes it from the DIFF_GROUP_DETAIL table and inserts a DiffGrpDtlDel row to the DIFFGRP_MFQUEUE table.
Message: A DiffGrpDtlRef message is created. It is a flat, synchronous message containing the primary key with which the external systems can remove it from their systems.
Prerequisites: Diff Group exists and a diff ID within the diff group (DIFF_GROUP_DETAIL record) may or may not exist.
Activity Detail: Deleting a Diff Group removes it from the DIFF_GROUP_HEAD table and inserts a DiffGrpDel row to the DIFFGRP_MFQUEUE table. Because the Diff Group Maintenance form in RMS automatically removes any child records on the DIFF_GROUP_DETAIL table when the diff group is removed, there will be a row inserted to the DIFFGRP_MFQUEUE table for each DIFF_GROUP_DETAIL record associated with the deleted diff group as well. These will receive the lower sequence numbers so that these will be acted upon first in the message queue. They will look like the DELETE DIFF_GROUP_DETAIL message detailed in the section above.
Message: A DiffGrpRef message is created for the diff group only. It is a flat, synchronous message containing the primary key with which the external systems can remove it from their systems.
Package name: RMSMFM_DIFFGRP
Spec file name: rmsmfm_diffgrps.pls
Body file name: rmsmfm_diffgrpb.pls
Function Level Description - ADDTOQ
Function: ADDTOQ
(O_status OUT VARCHAR2,
O_text OUT VARCHAR2,
I_message_type IN DIFFGRP_MFQUEUE.MESSAGE_TYPE%TYPE
I_diff_group_id IN DIFFGRP_MFQUEUE.DIFF_GROUP_ID%TYPE,
I_diff_id IN DIFFGRP_MFQUEUE.DIFF_ID%TYPE,
I_message IN CLOB);
This procedure is called by an event capture trigger, and takes the message type, family key values and, for synchronously captured messages, the message itself. It inserts a row into the message family queue along with the passed in values and the next sequence number from the message family sequence, setting the status to unpublished, or skips in the case of consolidation messages. It returns error codes and strings according to the standards of the application in which it is being implemented.
Procedure: GETNXT
(O_status_code OUT VARCHAR2,
O_error_msg OUT VARCHAR2,
O_message_type OUT VARCHAR2,
Message OUT CLOB,
O_diff_group_id OUT DIFFGRP_MFQUEUE.DIFF_GROUP_ID%TYPE,
O_diff_id OUT DIFFGRP_MFQUEUE.DIFF_ID%TYPE);
This publicly exposed procedure is typically called by a RIB publication adaptor. Its parameters are well defined and arranged in a specific order. The message type is the RIB defined short message name, the message is the xml message, and the family key(s) are the key for the message as pertains to the family, not all of which will necessarily be populated for all message types. Status code is the API_CODES which denotes the success or failure of processing the message.
The error text parameter contains application-generated information, such as the application's sequence number of the message that failed, and the Oracle or other error that occurred when the retrieval failed. The facility ID is only included in messages coming from RWMS.
A trigger exists on the DIFF_GROUP_HEAD and DIFF_GROUP_DETAIL table to capture inserts, updates, and deletes.
Trigger name: EC_TABLE_DGH_AIUDR.TRG
Trigger file name: ec_table_dgh_aiudr.trg
Table: DIFF_GROUP_HEAD
Inserts: Send the I_diff_group_id to the ADDTOQ procedure in the MFM with the message type RMSMFM_FAMILY.
Updates: Send the I_diff_group_id to the ADDTOQ procedure in the MFM with the message type RMSMFM_FAMILY. Any change to the DIFF_GROUP_HEAD table inserts a DiffGrpHdrCre message_type record on the DIFFGRP_MFQUEUE table.
Deletes: Send the I_diff_group_id to the ADDTOQ procedure in the MFM with the message type RMSMFM_FAMILY.
Trigger name: EC_TABLE_DGD_AIUDR.TRG
Trigger file name: ec_table_dgd_aiudr.trg
Table: DIFF_GROUP_DETAIL
Inserts: Send the I_diff_group_id, I_diff_id to the ADDTOQ procedure in the MFM with the message type RMSMFM_FAMILY.
Updates :
- Send the I_diff_group_id, I_diff_id to the ADDTOQ procedure in the MFM with the message type RMSMFM_FAMILY.
- Any Differentiators added to a diff group inserts a record to the DIFF_GROUP_HEAD table. A DiffGrpDtlCre message type record is also inserted on the DIFFGRP_MFQUEUE table. A foreign key to the DIFF_GROUP_HEAD table checks the existence of the diff group the value is created to supplement.
Deletes: Send the I_diff_group_id, I_diff_id to the ADDTOQ procedure in the MFM with the message type RMSMFM_FAMILY.
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) |
|---|---|---|
| DiffGrpHdrCre | Differentiator Header Create Message | DiffGrpHdrDesc.xsd |
| DiffGrpHdrMod | Differentiator Header Modify Message | DiffGrpHdrDesc.xsd |
| DiffGrpHdrDel | Differentiator Header Delete Message | DiffGrpHdrRef.xsd |
| DiffGrpDtlCre | Differentiator Detail Create Message | DiffGrpDtlDesc.xsd |
| DiffGrpDtlMod | Differentiator Detail Modify Message | DiffGrpDtlDesc.xsd |
| DiffGrpDtlDel | Differentiator Detail Delete Message | DiffGrpDtlRef.xsd |
It is not possible for a detail trigger to accurately know the status of a header table.
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 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.
Delay all DML statements to as late a time as possible. Once DML statements have taken place, any error becomes a fatal error rather than a hospital error.
This section describes the differentiator ID publication API.
RMS publishes messages for differentiator (diff) identifiers (diff IDs), and diff groups.
When diff are created in RMS and need to be sent to other systems, they are sent out via diff ID publication. When the external system receives information about an item that includes the new diff ID, that system understands what the diff ID refers to.
Diff message publication processes begin whenever a trigger 'fires' on one of the diff tables. When that occurs, the trigger extracts the affected row on the table and publishes the data to the corresponding message family queue staging table. A total of nine messages can be published; however, they group into these three categories:
Group Header
Group Details
Diff IDs
Diff ID publication consists of a single flat message containing diffattributes from the table DIFF_IDS. One message will be synchronously created and placed in the message queue each time a diff (diff_ids) is created, modified, or deleted. When a diff (diff_ids) is created or modified, the flat message will contain numerous attributes of the diff. When a diff is deleted, the message will simply contain the unique identifier of the diff, the diff_id. Messages are retrieved from the message queue in the order they were created.
Create Diff Id:
Prerequisites: Diff ID does not already exist.
Activity Detail: Any change to the DIFF_IDS table inserts a DiffCre message_type record on the DIFFID_MFQUEUE table.
Messages: The DiffDesc message is created. It is a flat, synchronous message containing a full snapshot of the diff ID at the time the message is published.
Modify Diff Id
Prerequisites: Diff ID exists.
Activity Detail: Any change to the DIFF_IDS table inserts a DiffMod message_type record on the DIFFID_MFQUEUE table.
Messages: The DiffDesc message is created. It is a flat, synchronous message containing a full snapshot of the diff ID at the time the message is published.
Delete Diff Id
Prerequisites: Diff ID exists.
Activity Detail: Deleting a Diff ID removes it from the DIFF_IDS table and inserts a DiffDel row to the DIFFID_MFQUEUE table.
Message: A DiffRef message is created. It is a flat, synchronous message containing the primary key with which the external systems can remove it from their systems.
Package name: RMSMFM_DIFFID
Spec file name: rmsmfm_diffids.pls
Body file name: rmsmfm_diffidb.pls
Package Specification - Global Variables
None
Function: ADDTOQ(O_status OUT VARCHAR2,
O_text OUT VARCHAR2,
I_message_type IN DIFFID_MFQUEUE.MESSAGE_TYPE%TYPE,
I_diff_id IN DIFFID_MFQUEUE.DIFF_ID%TYPE,
I_message IN CLOB)
This procedure called by EC_TABLE_DID_AIUDR , takes the message type, diff ID, and the message itself. It inserts a row into the message family queue DIFFID_MFQUEUE along with the passed in values and the next sequence number from the message family sequence, setting the status to unpublished. It returns a status code of API_CODES.SUCCESS if successful, API_CODES.UNHANDLED_ERROR if not.
Procedure: GETNXT(O_status_code OUT VARCHAR2,
O_error_msg OUT VARCHAR2,
O_message_type OUT VARCHAR2,
O_message OUT CLOB,
O_diff_id OUT DIFFGRP_MFQUEUE.DIFF_ID%TYPE)
This publicly exposed procedure is typically called by a RIB publication adaptor. Its parameters are well defined and arranged in a specific order. The message type is the RIB defined short message name, the message is the xml message, and the family key(s) are the key for the message as pertains to the family, not all of which will necessarily be populated for all message types.
The error text parameter contains application-generated information, such as the application's sequence number of the message that failed, and the Oracle or other error that occurred when the retrieval failed.
A trigger exists on the DIFF_IDS and DIFFID_MFQUEUE tables to capture Inserts, Updates, and Deletes.
Trigger name: EC_TABLE_DID_AIUDR.TRG
Trigger file name: ec_table_did_aiudr.trg
Table: DIFF_IDs
DIFFID_XML. BUILD_MESSAGE (O_status, O_text, O_message, I_record, I_action_type) - This function is called by the trigger EC_TABLE_DID_AIUDR on insert, update and delete of the DIFF_IDS table. This function gathers all the data necessary to build the message that needs to be sent to the Oracle Retail Integration Bus. It determines the proper message to build based on the action_type that is sent in the trigger. It builds DiffRef xml messages for delete statements or DiffDesc xml messages for updates or inserts.
Inserts: Sets action_type to 'A'dd and message_type to 'DiffCre'.
Updates: Sets action_type to 'M'odify and message_type to 'DiffMod'.
Deletes: Sets action_type to 'D'elete and message_type to 'DiffDel'.
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) |
|---|---|---|
| DiffCre | Diffid Create Message | DiffDesc.xsd |
| DiffMod | Diffid Modify Message | DiffDesc.xsd |
| DiffDel | Diffid Delete Message | DiffRef.xsd |
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 setup to call stored procedures, not stored functions. Any public program then needs to be a procedure.
This section describes the item publication API.
RMS publishes messages about items to the Oracle Retail Integration Bus (RIB). In situations where a retailer creates a new item in RMS, 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.
A deposit item is a product that has a portion which is returnable to the supplier and sold to the customer, with a deposit taken for the returnable portion. Because the contents portion of the item and the container portion of the item have to be managed in separate financial accounts (as the container item would be posted to a liabilities account) with different attributes, the retailer must set up two separate items. All returns of used deposit items (the returned item) are managed as a separate product, to track these products separately and as a generic item not linked to the actual deposit item (for example, bottles being washed and having no label).
The retailer can never put a container item on a transfer. Instead, the container item is added to returns to vendors (RTVs) automatically when the retailer adds the associated content item.
Deposit item attributes in RMS 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.
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 RMS, the following item attributes are available:
Cost UOM: All items in RMS 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).
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 RMS 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 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, RMS 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:
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 RMS 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
The creation of a new item in RMS 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.
As described in the preceding section, item messages can originate in a number of RMS 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).
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.
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.
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).
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 are published in the same way that modify messages are. For example, if an item-supplier-country relationship is deleted from RMS' 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.
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
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;
This section describes the package impact.
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
FAMILY CONSTANT RIB_SETTINGS.FAMILY%TYPE 'ITEM'; 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: 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.
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.
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).
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'.
If the message type is ITEM_DEL, delete the item's ITEM_PUB_INFO record.
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.
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: 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: 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).
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.
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.
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.
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.
This private function construct a CFA_BASE_TABLE_PRIMARY_KEY_REC object with the RMS 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.
This private function construct a CFA_BASE_TABLE_PRIMARY_KEY_REC object with the RMS 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.
This private function construct a CFA_BASE_TABLE_PRIMARY_KEY_REC object with the RMS 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 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.
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.
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.
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 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, 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 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, 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 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, 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.
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 | 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 |
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, RMS 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.
This section describes the item location publication API.
RMS defines and publishes item-location relationships. The details about item-location relationship creation, updation and de-activation are important for other systems for smooth functioning of several business processes. For example, when an new item-location relationship is created, the Point-Of-Sale system needs to be made aware of this information so that it can smoothly process subsequent sales and return activities at the Point-of-sale. The purpose of this API is to publish such information to be subscribed and consumed by other systems.
As and when item-location relationships are created or modified as part of various business processes, such events are captured as using triggers on the item location set of tables. The trigger then invokes methods from this API to successfully publish the captured information.
Package name: RMSMFM_ITEMLOC
Spec file name: rmsmfm_itemlocs.pls
Body file name: rmsmfm_itemlocb.pls
Package Specification - Global Variables
FAMILY CONSTANT VARCHAR2(64) 'ItemLoc'; ITEMLOC_ADD CONSTANT VARCHAR2(20) 'ItemLocCre'; ITEMLOC_UPD CONSTANT VARCHAR2(20) 'ItemLocMod'; ITEMLOC_DEL CONSTANT VARCHAR2(20) 'ItemLocDel'; REPL_UPD CONSTANT VARCHAR2(20) 'ItemLocReplMod';
Function: ADDTOQ
(O_error_message OUT VARCHAR2,
I_message_type IN ITEMLOC_MFQUEUE.MESSAGE_TYPE%TYPE,
I_itemloc_record IN ITEM_LOC%ROWTYPE,
I_prim_repl_supplier IN REPL_ITEM_LOC.PRIMARY_REPL_SUPPLIER%TYPE,
I_repl_method IN REPL_ITEM_LOC.REPL_METHOD%TYPE,
I_reject_store_ord_ind IN REPL_ITEM_LOC.REJECT_STORE_ORD_IND%TYPE,
I_next_delivery_date IN REPL_ITEM_LOC.NEXT_DELIVERY_DATE%TYPE,
I_mult_runs_per_day_ind IN REPL_ITEM_LOC.MULT_RUNS_PER_DAY_IND%TYPE)
This will call the API_LIBRARY.GET_RIB_SETTINGS if the LP_num_threads is NULL and insert the family record into ITEMLOC_MFQUEUE table. The call for HASH_ITEM will insert the I_itemloc_record.item information into ITEMLOC_MFQUEUE table.
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);
Make sure to initialize LP_error_status to API_CODES.HOSPITAL at the beginning of GETNXT.
The RIB calls GETNXT to get messages. The driving cursor will query for unpublished records on the ITEMLOC_MFQUEUE table (PUB_STATUS = 'U').
Because ITEMLOC records should not be published before ITEM records a clause is included in the driving cursor that checks for ITEM CREATE messages on the ITEM_MFQUEUE table. The ITEMLOC_MFQUEUE record will not be selected from the driving cursor if the ITEM CREATE message still exists on ITEM_MFQUEUE. Also, ITEMLOC_MFQUEUE cleanup is included in ITEM_MFQUEUE cleanup. When the item publisher RMSMFM_ITEMS encounters a DELETE message for an item that has never been published, it deletes all records for the item from the ITEM_MFQUEUE table. This is done in the program unit CLEAN_QUEUE. CLEAN_QUEUE also deletes from ITEMLOC_MFQUEUE when a DELETE message for a non-published item is encountered.
After retrieving a record from the queue table, GETNXT checks 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 information from the ITEMLOC_MFQUEUE 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 will raise 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.
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:
The record on ITEMLOC_MFQUEUE must match the passed in sequence number (contained in the ROUTING_INFO).
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 record from ITEMLOC_MFQUEUE table is an add or update (ITEMLOC_ADD, ITEMLOC_UPD) the function will call BUILD_DETAIL_OBJECTS to build the Oracle Object to publish to the RIB. This will also take care of any ITEMLOC_MFQUEUE deletes and ROUTING_INFO logic.
If the record from ITEMLOC_MFQUEUE table is a delete (ITEMLOC_DEL) the function will call BUILD_DETAIL_DELETE_OBJECTS to build the Oracle Object to publish to the RIB. This will also take care of any ITEMLOC_MFQUEUE deletes and the ROUTING_INFO logic.
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.
Selects all records on the ITEMLOC_MFQUEUE that are for the same item. The records are fetched in order of seq_no on the MFQUEUE table. The records are fetchee into a table using BULK COLLECT, with MAX_DETAILS_TO_PUBLISH as the LIMIT clause.
The records in the BULK COLLECT table are looped through. If the record's message_type differs from the message type passed into the function, it will exit from the loop. Otherwise, it will add the data from the record to the Oracle Object being used for publication. If the input message type is not REPL_UPD then the Purchase Type for the item's department is retrieved and it is added to the oracle object.
Ensures that ITEMLOC_MFQUEUE is deleted from as needed.
Ensures that ROUTING_INFO is constructed if routing information is stored at the detail level in the business transaction.
Make sure to set LP_error_status to API_CODES.UNHANDLED_ERROR before any DML statements.
A concern here is making sure that the system does not delete records from the queue table that have not been published. For this reason, the system performs deletes by ROWID. The system will also get everything in the same cursor. This should ensure that the message published matches the deletes performed from the ITEMLOC_MFQUEUE table regardless of trigger execution during GETNXT calls.
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.
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, 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.
This private function construct a CFA_BASE_TABLE_PRIMARY_KEY_REC object with the RMS base table item_loc and entity key values (item,loc). Calls CFA_API_SQL.BUILD_NAME_VALUE_PAIR to build and return the entity's customer attributes through RIB_CustFlexAttriVo_TBL.
A trigger exists on the ITEM_LOC to capture inserts, updates, and deletes.
Only transaction-level items should be processed. If the item is not transaction-level, the trigger will exit before calling ADDTOQ.
Trigger name: EC_TABLE_ITL_AIUDR.TRG (mod)
Trigger file name: ec_table_itl_aiudr.trg (mod)
Table: ITEMLOC
Inserts: Sends the L_record (I_item, I_loc, and the I_loc_type) to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEMLOC.ITEMLOC_ADD.
Updates: Sends the L_prim_repl_supplier, L_repl_method, L_reject_store_ord_ind, L_next_delivery_date to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEMLOC.ITEMLOC_UPD.
The only updates that need to be captured are updates to the columns receive_as_type, source_wh, store_price_ind, primary_supp, status, source_method, local_item_desc, primary_cntry, local_short_desc, and taxable_ind.
Deletes: Sends the L_record (I_item, I_loc, and the I_loc_type ) to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEMLOC.ITEMLOC_DEL.
The trigger will fire not only for stores (loc_type = 'S') but also for warehouses (loc_type = 'W').
Trigger name: EC_TABLE_RIL_AIUDR.TRG (mod)
Trigger file name: ec_table_ril_aiudr.trg (mod)
Table: REPL_ITEM_LOC
Create a trigger on the table REPL_ITEM_LOC to capture inserts, updates, and deletes.
Updates:
Sends the L_prim_repl_supplier, L_repl_method, L_reject_store_ord_ind, L_next_delivery_date and the L_record ( I_item, I_loc, and the I_loc_type ) to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEMLOC.REPL_UPD.
The only updates that need to be captured are updates to the columns primary_repl_supplier, repl_method, reject_store_ord_ind, and next_delivery_date.
Deletes: Sends the L_record ( I_item, I_loc, and the I_loc_type ) to the ADDTOQ procedure in the MFM with the message type RMSMFM_ITEMLOC.REPL_UPD.
Below 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) |
|---|---|---|
| ItemLocCre | Item Loc Create Message | ItemLocDesc.xsd |
| ItemLocMod | Item Loc Modify Message | ItemLocDesc.xsd |
| ItemLocDel | Item Loc Delete Message | ItemLocRef.xsd |
| ItemLocReplMod | Item Loc Replenishment Modify Message | ItemLocDesc.xsd |
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| ITEM_MFQUEUE | Yes | No | No | No |
| ITEMLOC_MFQUEUE | Yes | Yes | Yes | Yes |
| ITEM_MASTER | Yes | No | No | No |
| DEPS | Yes | No | No | No |
| ITEM_LOC_CFA_EXT | Yes | No | No | No |
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, RMS 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 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.
This section describes the merchandise hierarchy publication API.
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 RMS.
This section describes the package impact.
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.
If the message relates to divisons, the business object ID will be the division. If the message relates to groups, the business object ID will be the group number. If the message relates to a department, the department number is the business object ID. If the message relates to a class, the business object ID will be the department number and the class number. Finally, if the message relates to a subclass, the business object ID will be the department, class and subclass.
File name: rmsmfm_merchhiers/b.pls
Function: ADDTOQ
(O_error_msg OUT VARCHAR2,
I_message_type IN MERCHHIER_MFQUEUE.MESSAGE_TYPE%TYPE,
I_division IN DIVISION.DIVISION%TYPE,
I_division_rec IN DIVISION%ROWTYPE,
I_group_no IN GROUPS.GROUP_NO%TYPE,
I_groups_rec IN GROUPS%ROWTYPE,
I_dept IN DEPS.DEPT%TYPE,
I_deps_rec IN DEPS%ROWTYPE,
I_class IN CLASS.CLASS%TYPE,
I_class_rec IN CLASS%ROWTYPE,
I_subclass IN SUBCLASS.SUBCLASS%TYPE,
I_subclass_rec IN SUBCLASS%ROWTYPE)
I_group_idINMERCHHIER_MFQUEUE.GROUP_ID%TYPE DEFAULT NULL)
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 business object ID, calculate the thread value.
Insert a record into the MERCHHIER_MFQUEUE.
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)
The RIB calls GETNXT to get messages. The procedure will use the C_QUEUE cursor defined in the specification of the package body to find the next message on the MERCHHIER_MFQUEUE to be published to the RIB.
After retrieving a record from the queue table, GETNXT checks for records on the queue with a status of 'H' - Hospital. If there are any such records for the current business object, GETNXT should raise an exception to send the current message to the hospital.
The information from the MERCHHIER_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.
After PROCESS_QUEUE_RECORD returns an Oracle object to pass to the RIB, this procedure will delete the record on MERCHHIER_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.
Procedure: 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);
Same as GETNXT except the record on MERCHHIER_MFQUEUE must match the passed in sequence number (contained in the ROUTING_INFO).
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. In addition to building the Oracle Objects, this function will populate the business object ID. If the message is for a division, group or department, the business object ID will be the division, group, or department respectively. If the message is for a class, the business object will be the class and department combination. If the message is for a subclass, the business object ID will be the subclass, class and department combination.
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 MERCHHIER_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'.
BUILD_DEPS_CFA_EXT is called from PROCESS_QUEUE_RECORD. This private function will build and return entity's customer attributes from DEPS_CFA_EXT table through RIB_CustFlexAttriVo_TBL.
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) |
|---|---|---|
| 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 |
This section describes the order publication API.
Purchase order (PO) functionality in RMS consists of order messages published to the Oracle Retail Integration Bus (RIB), and batch modules that internally process purchase order data and uploads EDI transmitted order. This overview describes how both order messages and batch programs process this data.
A purchase order is created using the following:
Through online using the ordering dialog.
Replenishment processes.
When the supplier contract type is 'B'.
By a supplier, in a vendor managed inventory environment.
Direct store delivery (defined as delivery of merchandise or a service that does not result from the prior creation of a PO). For more information, see Oracle Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs, Chapter Purchase Order.
Buyer Worksheet dialog.
Truck splitting.
Customer Order webservice/RIB.
Franchise Order.
After purchase orders are published to the RIB, the following associated activity occurs:
Work orders associated with items on the PO are published to the RIB through the work order message process.
An allocation (also known as pre-distribution) of items on the PO is published to the RIB through the stock order message process.
A PO can be closed only after all appointments against the purchase order are closed. A closed appointment indicates that all merchandise has been received. RMS subscribes to appointment messages from the RIB.
'Version' refers to any change to a purchase order by a retailer's buyer; whereas 'Revision' refers to any change to a purchase order initiated by a supplier.
RMS publishes two sets of PO messages to RIB for two kinds of subscribing applications. The first set of messages contains only virtual locations in RMS. Applications that understand virtual locations subscribe to these messages.
RMS publishes a second set of PO messages for applications that can subscribe only to conventional, physical location data, such as a Warehouse Management System.
Ordering publication is primarily based off of the ORDHEAD, ORDSKU, and ORDLOC tables.
ORDHEAD is the parent table containing high level ordering information such as what supplier is being ordered from, when the order must take place, and so on. ORDSKU is a child of ORDHEAD and contains the item(s) that are ordered, the size of the pack being ordered.
ORDLOC is a child of ORDSKU that contains the location(s) each item on the order is going to and how much of each item is ordered. Based on this table hierarchy, two levels of messages exist for order publishing. A header message is primarily driven off of the ORDHEAD table and the detail message that is primarily driven off both the ORDSKU and ORDLOC tables.
If the purchase order is a customer order (order_type = 'CO' with a stockholding store), the Customer Order Number and Fulfillment Order Number retrieved from the ORDCUST table will be included in the header message and published.
Each message level contains three types of messages; Create, Modify, and Delete. The 'POCre' or 'POHdrMod' message is created when an insertion or modification to the ORDHEAD table is made respectively. The 'PODel' message is created when an order is deleted from the ORDHEAD table. 'PODtlCre' or 'PODtlMod' message is created when a record is inserted or modified on the ORDLOC table respectively. 'PODtlDel' is created when an ORDLOC record is deleted.
This section describes the package impact.
Prerequisites: Orders are created through various methods. Orders created manually by a user, through a replenishment process (order can be created in either worksheet or approved status), uploaded from a vendor, through a contract, through customer order creation or through a franchise order creation.
Activity Detail: At this point, the order is not seen externally from RMS.
Messages: When the order is created, a header message 'POCre' is written to the ordering queue table. Upon detail additions, each will have a 'PODtlCre' message written to the ordering queue. Ordering messages are added, updated, and removed from the queue as the order is modified prior to approval.
Prerequisites: Order is still in worksheet status and has not been approved and is set back to worksheet.
Activity Detail: At this point, items are modified, added or removed from the order. The order is split, scaled, and rounded in addition to having deals, brackets applied.
Messages: Each change causes a 'POHdrMod' or 'PODtlMod' message. These messages replaces previous create messages if there was a modification, delete a previous message if there was a delete, or add a new message to the queue for inserts.
Prerequisites: Line items must exist for the order to be approved. Relevant dates (not before, not after, pickup) must exist, plus certain other business validation rules based on system options.
Activity Detail: At this point, the order is initially approved which means external systems will now have constant visibility to all ordering transactions. The user can no longer delete line items: Instead, they are cancelled. Canceling decrements the order quantity by amount already received.
Messages: The approval message sets an indicator signifying the approval creates message must be built. This is a hierarchical snapshot synchronous message built in the family manager by attaching all of the 'PODtlDesc' messages with the 'POHdrDesc' message creates a 'POCre' message.
Prerequisites: Order must be currently approved.
Activity Detail: Numerous fields at the header level (none at the detail level) can be changed while the order is approved. This change creates a message.
Messages: A 'POHdrMod' message is created for order at the end of the session the order was modified. This message is published immediately as the order is already been published. If the order has not been published, then this message follows the create message sent out.
Prerequisites: Order must be in approved or worksheet status. Order must not be a contract order. No shipments/appointments may exist against the order. Items with allocations cannot be redistributed.
Activity Detail: User chooses which items to redistribute. Each chosen details are removed from the order. This creates delete messages for each one. A new location is then chosen to redistribute the items to. Each item/location record creates a message.
|
Note: If user chooses to redistribute records, then cancels out of redistribution, delete and create messages for the chosen records is inserted into the queue even though no changes were actually made online. |
Messages: A 'PODtlDel' message is created for each item/location removed from the order. If the order has not yet been approved, then these messages removes previous create messages. For already approved orders, then a message is published. For each redistributed item, a 'PODtlCre' message is created.
Prerequisites: Order must currently be in approved status. Shipments/Appoinments may exist against the order.
Activity Detail: This changes the status of the order back to worksheet. This creates a message. Existing details is modifiable. New records may be added to the order. Items may not be deleted from the order. However, the order quantity of the items can be canceled down to the received or appointment expected quantity.
Messages: A 'POHdrMod' message is created for order at the end of the session the order was modified. This message is published immediately as the order is already have been published. If the order has not been published, then this message follows the create message sent out.
Prerequisites: Order must be in worksheet status and have already been approved.
Activity Detail: If modification occurs at the header level, a header message is created. A detail message is created for each modified or added detail record. Detail records cannot be deleted; only their quantities can be canceled.
Message: A 'POHdrMod' message is created for order at the end of the session if the header was modified. A 'PODtlCre' or 'PODtlMod' message is created for each detail record added or modified respectively.
Prerequisites: Order must currently be in an approved status or in worksheet status and which is already approved. No outstanding shipments/appointments may exist against any line items of the order.
Activity Detail: The status changes to closed. This creates a message. Any outstanding unreceived quantity is canceled out. No detail is modifiable while the order is in this status.
Message: A 'POHdrMod' message is created for order at the end of the session the order was modified. A 'PODtlMod' message is created for each line item that had outstanding un-received quantity. These messages are published immediately as the order is already published. If the order has not been published, then this message follows the create message sent out.
Prerequisites: Order must be in closed status. Orders that have been fully received (closed through receiving dialogue) cannot be reinstated.
Activity Detail: The status changes to worksheet. This creates a header level message. All canceled quantities is added back to order quantities. Details are modifiable.
Message: A 'POHdrMod' message is created for order at the end of the session the order was modified. A 'PODtlMod' message is created for each line item that had outstanding canceled quantity. These messages are published immediately as the order is already published. If the order are not published, then this message follows the create message sent out.
Prerequisites: If the user deletes the order manually, then the order needs to be in worksheet status and never been approved. Else, for approved orders, the following explanation details the business validation for deleting orders. If the import indicator on the SYSTEM OPTIONS table (import_ind) is 'N' and if invoice matching is not installed, then all details associated with an order are deleted when the order has been closed for more months than specified in UNIT_OPTIONS (order_history_months). If invoice matching is installed, then all details associated with an order are deleted when the order has been closed for more months than specified in UNIT_OPTIONS (order_history_months). Orders are deleted only if shipments from the order have been completely matched to invoices or closed, and all those invoices have been posted. If the import indicator on the SYSTEM OPTIONS table (import_ind) is 'Y' and if invoice matching is not installed, then all details associated with the order are deleted when the order has been closed for more months than specified in UNIT_OPTIONS (order_history_months) , as long as all ALC records associated with an order are in 'Processed' status, specified in ALC_HEAD (status). If invoice matching is installed, then all details associated with an order are deleted when the order has been closed for more months than specified in UNIT_OPTIONS (order_history_months), as long as all ALC records associated with an order are in 'Processed' status, specified in ALC_HEAD (status), and as long as all shipments from the order have been completely matched to invoices or closed, and all those invoices have been posted.
Activity Detail: Deleting orders will create a message for each detail attached to the order plus the header record.
Messages: If the order has not been approved, then the 'PODel' and 'PODtlDel' messages created will remove all the previous messages on the ordering queue table. If the order has been approved, then a 'PODtlDel' message will be created for each detail record and a 'PODel' message for the header.
Filename: rmsmfm_orderb.pls
Function Level Description - ADDTOQ
Function: ADDTOQ
(O_error_message OUT VARCHAR2,
I_message_type IN ORDER_MFQUEUE.MESSAGE_TYPE%TYPE,
I_order_no IN ORDHEAD.ORDER_NO%TYPE,
I_order_type IN ORDHEAD.ORDER_TYPE%TYPE,
I_order_header_status IN ORDHEAD.STATUS%TYPE,
I_supplier IN ORDHEAD.SUPPLIER%TYPE,
I_item IN ORDLOC.ITEM%TYPE,
I_location IN ORDLOC.LOCATION%TYPE,
I_loc_type IN ORDLOC.LOC_TYPE%TYPE,
I_physical_location IN ORDLOC.LOCATION%TYPE)
This procedure is called by either the ORDHEAD or ORDLOC row trigger, and takes the message type, table primary key values (order_no for ORDHEAD table and order_no, item, location (virtual) and physical location for ORDLOC table) and the message itself. It inserts a row into the message family queue along with the passed in values and the next sequence number from the message family sequence. The pub status will always be 'U' except for PO create messages, then it will be 'N'. The approve indicator will always be 'N' except when the order is approved for the first time, then it will be 'Y'. It returns error codes and strings according to the standards of the application in which it is being implemented.
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 publicly exposed procedure is typically called by a RIB publication adaptor. Its parameters are well defined and arranged in a specific order. The message type is the RIB defined short message name, the message is the xml message, and the family key(s) (order_no for ORDHEAD table and order_no, item, location (virtual) and physical location for ORDLOC table) are the key for the message as pertains to the family, not all of which will necessarily be populated for all message types.
This program loops through each message on the ORDER_MFQUEUE table, and calls PROCESS_QUEUE_RECORD. When no messages are found, the program exits returning the 'N'o message found API code.
The error text parameter contains application-generated information, such as the application's sequence number of the message that failed, and the Oracle or other error that occurred when the retrieval failed.
Function Level Description - PUB_RETRY
Procedure: 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);
Same as GETNXT except:
It only loops for a specific row in the ORDER_MFQUEUE table. The record on ORDER_MFQUEUE 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.
Check to see if the business object is being published for the first time. If the published_ind on the pub_info table is 'N', then it is not yet published.
If the record from ORDER_MFQUEUE table is a header delete (HDR_DEL) and published_ind is 'N'.
Delete the record from the pub info table.
Call DELETE_QUEUE_REC.
If the record from ORDER_MFQUEUE table is a header delete (HDR_DEL).
Build and pass the RIB_PORef_REC object.
Call GET_ROUTING_TO_LOCS.
Delete the record from the pub info table.
Delete the record from the order_details_published table.
Call DELETE_QUEUE_REC.
If the published_ind is 'N' or 'I'.
If the publish_ind is 'N' call MAKE_CREATE with the message_type 'HDR_ADD'.
Otherwise, call MAKE_CREATE with the message_type 'DTL_ADD'.
If the record from ORDER_MFQUEUE table is a header update (HDR_UPD).
Call BUILD_HEADER_OBJECT.
Update order_pub_info by setting the published indicator to 'Y'.
Call GET_ROUTING_TO_LOCS.
Call DELETE_QUEUE_REC.
If the record from ORDER_MFQUEUE table is a detail insert (DTL_ADD) or detail update (DTL_UPD).
Call BUILD_DETAIL_CHANGE_OBJECTS.
If the record from ORDER_MFQUEUE table is a detail delete (DTL_DEL).
Call BUILD_DETAIL_DELETE .
Call ROUTING_INFO_ADD.
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 a table of ORDER_MFQUEUE rowids to delete.
Use the header level Oracle Object and functional holders to update the ORDER_PUB_INFO.
Delete records from the ORDER_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 ORDER_MFQUEUE record that was picked up by GETNXT. If the entire business transaction was not published we need to leave something on the ORDER_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)
Accepts header key values, performs necessary lookups, builds and returns a header level Oracle Object.
Call GET_MSG_HEADER.
Function Level Description - BUILD_DETAIL_OBJECTS (local)
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.
If the function is being called from MAKE_CREATE:
Select any unpublished detail records from the business transaction (use an indicator on the functional detail table itself or ORDER_DETAILS_PUBLISHED). Create Oracle Objects for details that are selected by calling BUILD_SINGLE_DETAIL.
If the function is not being called from MAKE_CREATE:
Select any details on the ORDER_DETAILS_PUBLISHED that are for the same business transaction and for the same message type. Create Oracle Objects for details that are selected by calling BUILD_SINGLE_DETAIL.
Create other necessary Oracle objects and insert into and update the ORDER_DETAILS_PUBLISHED table for details that were published.
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 - BUILD_DETAIL_CHANGE_OBJECTS (local)
Either pass in a header level Oracle Object or call BUILD_HEADER_OBJECT to build one.
Call BUILD_SINGLE_DETAIL to get the delete level Oracle Objects.
Perform any BULK DML statements given the output from BUILD_ DETAIL_OBJECTS.
Build any ROUTING_INFO as needed.
Function Level Description - BUILD_DETAIL_DELETE (local)
Either pass in a header level ref Oracle Object or build a header level ref Oracle Object.
Perform a cursor for loop on ORDER_MFQUEUE and build as many detail ref Oracle Objects as possible without exceeding the MAX_DETAILS_TO_PUBLISH.
Perform any BULK DML statements for deletion from ORDER_MFQUEUE and ORDER_DETAILS_PUBLISHED.
Call BUILD_DETAIL_DELETE_WH for Warehouses.
Function Level Description - DELETE_QUEUE_REC (local)
Delete the passed in data from the queue table.
Function Level Description - BUILD_DETAIL_DELETE_WH (local)
Builds Oracle objects based on the records found in the queue table that are from the ORDLOC table.
Function Level Description - ROUTING_INFO_ADD (local)
Build any ROUTING_INFO.
Function Level Description - GET_ROUTING_TO_LOCS (local)
Build the ROUTING_INFO by adding locations.
Function Level Description - GET_MSG_HEADER (local)
Perform any lookups to complete the header information.
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)
PROCEDURE HANDLE_ERRORS
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
O_message IN OUT nocopy RIB_OBJECT,
O_bus_obj_id IN OUT nocopy RIB_BUSOBJID_TBL,
O_routing_info IN OUT nocopy RIB_ROUTINGINFO_TBL,
I_seq_no IN order_mfqueue.seq_no%TYPE,
I_order_no IN order_mfqueue.order_no%TYPE,
I_item IN order_mfqueue.item%TYPE,
I_physical_location IN order_mfqueue.physical_location%TYPE,
I_loc_type IN order_mfqueue.loc_type%TYPE)
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 ORDER_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.
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) |
|---|---|---|
| POCre | Purchase Order Create Message | PODesc.xsd |
| POHdrMod | Purchase Order Modify Message | PODesc.xsd |
| PODel | Purchase Order Delete Message | PORef.xsd |
| PODtlCre | Purchase Order Detail Create Message | PODesc.xsd |
| PODtlMod | Purchase Order Detail Modify Message | PORef.xsd |
| PODtlDel | Purchase Order Detail Delete Message |
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 setup to call stored procedures, not stored functions. Any public program then needs to be a procedure.
This section describes the organization hierarchy publication API.
This API publishes the create, update, delete of all the levels of the organizational hierarchy (chain, area, region, and district) to the RIB such that all the downstream applications (including an external system) may subscribe to it and have organizational hierarchy information in sync with RMS.
This section describes the package impact.
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.
For the organizational hierarchy publishing API, the business object ID will contain two entries - the hierarchy level and the hierarchy id:
If the message relates to chain, the business object ID will contain a hierarchy level of 'CH' and the chain number.
If the message relates to area, the business object ID will contain a hierarchy level of 'AR' and the area number.
If the message relates to a region, the business object ID will contain a hierarchy level of 'RE' and the region number.
If the message relates to a district, the business object ID will contain a hierarchy level of 'DI' and the district number.
File name: rmsmfm_orghiers/b.pls
Function Level Description - ADDTOQ
FUNCTION ADDTOQ(O_error_msg OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message_type IN ORGHIER_MFQUEUE.MESSAGE_TYPE%TYPE,
I_hier_level IN VARCHAR2,
I_chain IN CHAIN.CHAIN%TYPE,
I_chain_rec IN CHAIN%ROWTYPE,
I_area IN AREA.AREA%TYPE,
I_area_rec IN AREA%ROWTYPE,
I_region IN REGION.REGION%TYPE,
I_region_rec IN REGION%ROWTYPE,
I_district IN DISTRICT.DISTRICT%TYPE,
I_district_rec IN DISTRICT%ROWTYPE,
I_parent_level IN VARCHAR2)
RETURN BOOLEAN;
The RIB publishing triggers on chain, area, region, district tables call ADDTOQ to insert a record into the ORGHIER_MFQUEUE based on the message type. Since multi-threading is NOT used for this publishing API, always set the thread_no to 1.
Function Level Description - GETNXT
Procedure: GETNXT
(O_status_code OUT VARCHAR2,
O_error_msg 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);
The RIB calls GETNXT to get messages. The procedure will use the C_QUEUE cursor to find the next message on the ORGHIER_MFQUEUE to be published to the RIB.
After retrieving a record from the queue table, GETNXT checks for records on the queue with a status of 'H' - Hospital. If there are any such records for the current business object, GETNXT should raise an exception to send the current message to the hospital.
The information from the ORGHIER_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.
After PROCESS_QUEUE_RECORD returns an Oracle object to pass to the RIB, this procedure will delete the record on ORGHIER_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 RTK_ERRORS.RTK_TEXT%TYPE,
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)
Same as GETNXT except the record on ORGHIER_MFQUEUE 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. In addition to building the Oracle Objects, this function will populate the business object ID with the organizational hierarchy level and id being published. Organizational hierarchy levels are: 'CH' for chain, 'AR' for area, 'RE' for region, and 'DI' for district.
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 ORGHIER_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'.
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 |
This section describes the partner publication API.
RMS publishes data about partners in messages to Retail Integration Bus (RIB). Other application that needs to keep their partner synchronized with RMS subscribe to these messages.
External finishers are created as partners in RMS, and given the Partner Type 'E', indicating that the partner is an External finisher. Once a new external finisher is set up in RMS, 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. RMS 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.
RMS 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.
Filename: rmsmfm_partnerb.pls
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.
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).
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.
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: 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.
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.
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).
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.
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).
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).
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.
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'.
This private function will delete the records from PARTNER_MFQUEUE table for the sequence no passed in as input parameter.
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 |
This section describes the receiver unit adjustment publication API.
When mistakes are made during the receiving process at the store or warehouse, receiver unit adjustments (RUAs) are made to correct the mistake. RMS 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 RMS 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. |
This section describes the package impact.
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 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.
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 |
This section describes the RTV request publication API.
A return to vendor (RTV) order is used to send merchandise back to the supplier. The RTV message is published by RMS 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 RMS 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 RMS so that RMS can correctly update the original RTV record.
This section describes the package impact.
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 clean up 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, and shipped_ind 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.
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.
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 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 to get a table of detail level Oracle objects and a table of RTVREQ_MFQUEUE rowids to delete.
Deletes records from the RTVREQ_MFQUEUE for all rowids returned by BUILD_DETAIL_OBJECTS. 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.
BUILD_DETAIL_OBJECTS creates a table of RTV_DETAIL ROWIDs to update. Updates the PUBLISH_IND to Y for 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:
Selects any unpublished detail records from the business transaction (RTV_DETAIL.PUBLISH_IND will be 'N'). Creates Oracle Objects for details that are selected by calling BUILD_SINGLE_DETAIL.
Ensures that the PUBLISH_IND gets set to Y for each RTV_DETAIL record placed into the Oracle Objects. A table of ROWIDs to update is created in BUILD_DETAIL_OBJECTS. The actual update statement occurs in BUILD_DETAIL_CHANGE_OBJECTS using this table of ROWIDS.
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.
Ensures that no more than MAX_DETAILS_TO_PUBLISH records are put into Oracle Objects.
Ensures that the detail records being added to the object have not already been published. This can happen if GETNXT was previously called for the current business object, and the MAX_DETAILS_TO_PUBLISH limit had been reached. The system ensures these details do not get added again by looking at each detail's PUBLISH_IND.
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.
If the message type is a detail create (DTL_ADD), ensures that the PUBLISH_IND gets set to Y for each RTV_DETAIL record placed into the Oracle Objects. A table of ROWIDs to update will be created in BUILD_DETAIL_OBJECTS. The actual update statement occur 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.
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.
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.
Updates: Sends the appropriate column values to the ADDTOQ procedure in the MFM with the message type RMSMFM_RTVREQ.DTL_UPD.
Deletes: Sends the appropriate column values to the ADDTOQ procedure in the MFM with the message type RMSMFM_RTVREQ.DTL_DEL.
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 |
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, RMS 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.
This section describes the season phase publicatoin API.
This API publishes the create, update, delete of seasons and phases to the RIB such that all the downstream applications (including an external system) may subscribe to it and have information in sync with RMS.
This section describes the package impact.
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.
When publishing the seasons data, the business object ID is the season id. When publishing the phases data, the business object id contains the compound key of the season id and the phase id.
File name: rmsmfm_seasons/b.pls
Function Level Description - ADDTOQ
FUNCTION ADDTOQ(O_error_msg OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message_type IN SEASON_MFQUEUE.MESSAGE_TYPE%TYPE,
I_season_id IN SEASONS.SEASON_ID%TYPE,
I_season_rec IN SEASONS%ROWTYPE,
I_phase_id IN PHASES.PHASE_ID%TYPE,
I_phase_rec IN PHASES%ROWTYPE)
RETURN BOOLEAN;
The RIB publishing triggers on seasons and phases tables call ADDTOQ to insert a record into the SEASON_MFQUEUE based on the message type. Since multi-threading is NOT used for this publishing API, always set the thread_no to 1.
Function Level Description - GETNXT
Procedure: GETNXT
(O_status_code OUT VARCHAR2,
O_error_msg 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)
The RIB calls GETNXT to get messages. The procedure will use the C_QUEUE cursor to find the next message on the SEASON_MFQUEUE to be published to the RIB.
After retrieving a record from the queue table, GETNXT checks for records on the queue with a status of 'H' - Hospital. If there are any such records for the current business object, GETNXT should raise an exception to send the current message to the hospital.
The information from the SEASON_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.
After PROCESS_QUEUE_RECORD returns an Oracle object to pass to the RIB, this procedure will delete the record on SEASON_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 RTK_ERRORS.RTK_TEXT%TYPE,
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)
Same as GETNXT except the record on SEASON_MFQUEUE 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. In addition to building the Oracle Objects, this function will populate the business object ID with the season id (for seasons) or the season id and phase id (for phases).
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 SEASON_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'.
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) |
|---|---|---|
| seasoncre | Seasons Create Message | SeasonDesc.xsd |
| seasonmod | Seasons Modify Message | SeasonDesc.xsd |
| seasondel | Seasons Delete Message | SeasonRef.xsd |
| seasondtlcre | Phases Create Message | SeasonDesc.xsd |
| seasondtlmod | Phases Modify Message | SeasonDesc.xsd |
| seasondtldel | Phases Delete Message | SeasonRef.xsd |
This section describes the seed data publication API.
Seed data publication to the RIB allows RMS to send some basic foundation data information to external systems to seed their database. The data contained in this API is usually fairly static and does not frequently change after initial implementation.
Some examples of seed data include diff types, item types, carriers, shipping methods, supplier types, location types, order types and return reasons.
File name: rmsmfm_seeddatas/b.pls
Function Level Description - ADDTOQ
PROCEDURE: ADDTOQ
(O_status OUT VARCHAR2,
O_text OUT VARCHAR2, I_message_type IN CODES_MFQUEUE.MESSAGE_TYPE%TYPE,
I_code_type IN CODES_MFQUEUE.CODE_TYPE%TYPE,
I_message IN OUT rib_sxw.SXWHandle)
This procedure is called by the table triggers EC_TABLE_CODEHD_AIUDR, EC_TABLE_CODEDTL_AIUDR and EC_TABLE_DIFF_TYPE_AIUDR. The procedure accepts a message variable that consists of the code or diff information in XML tags, a code type variable (this will be hard coded 'OOOO' for diff types) and one of the message types defined in the package specification. It inserts a row into the message family queue CODES_MFQUEUE along with the passed in values and the next sequence number from the message family sequence, and sets the status to unpublished. The procedure will then call API_LIBRARY.WRITE_DOCUMENT_STR which will return a status code of API_CODES.SUCCESS if successful, API_CODES.UNHANDLED_ERROR if not.
Function Level Description - GETNXT
PROCEDURE GETNXT
(O_status_code OUT VARCHAR2,
O_error_msg OUT VARCHAR2,
O_message_type OUT CODES_MFQUEUE.MESSAGE_TYPE%TYPE,
O_message OUT nocopy CLOB,
O_code_type OUT CODES_MFQUEUE.CODE_TYPE%TYPE)
This publicly exposed procedure is called by the RIB publication adaptor. The message type is the RIB defined short message name.
The error text parameter contains application-generated information, such as the application's sequence number of the message that failed, and the Oracle or other error that occurred when the retrieval failed.
This procedure will call the internal function DO_GETNXT which will actually retrieve the clob from the CODES_MFQUEUE table so that it may be published to the RIB.
Function Level Description - DO_GETNXT (local)
This internal procedure will select the record from the CODES_MFQUEUE table having the lowest sequence number and a pub_status of 'U'. It will return the clob, the message type and code type to the out parameters to be passed back to GETNXT. The procedure will then call the DELETE_QUEUE_REC function to delete the record that is being published.
Function Level Description - DELETE_QUEUE_REC (local)
This procedure will delete the record from the CODES_MFQUEUE table that has the sequence number corresponding to the I_seq_no parameter.
File name: code_head_xmls/b.pls
Function Level Description - BUILD_MESSAGE
If the I_action_type is 'D' (a record is being deleted), an internal variable holding the doc type will be set to RMSMFM_SEEDDATA.HDR_REF_MSG. The function will then call API_LIBRARY.CREATE_MESSAGE_STR to return a clob that has the appropriate structure for the code head delete message. The function then calls the DELETE_CODE_HEAD function to populate the clob that was created.
If the I_action_type is not 'D' (a record has been added or updated), an internal variable holding the doc type will be set to RMSMFM_SEEDDATA.HDR_DESC_MSG. The function will then call API_LIBRARY.CREATE_MESSAGE_STR to return a clob that has the appropriate structure for the code head desc message. The function then calls the ADD_UPDATE_CODE_HEAD function to populate the clob that was created.
Function Level Description - DELETE_CODE_HEAD
This function will accept a record that holds code_head values. The rib_sxw.addElement function will be called to add the code type from the I_code_head_rec to the clob (or root).
Function Level Description - ADD_UPDATE_CODE_HEAD
This function will accept a record that holds CODE_HEAD values. The rib_sxw.addElement function will be called to add the code type and code type description from the I_code_head_rec to the clob (or root).
File name: code_detail_xmls/b.pls
Function Level Description - BUILD_MESSAGE
If the I_action_type is 'D' (a record is being deleted), an internal variable holding the doc type should be set to RMSMFM_SEEDDATA.DTL_REF_MSG. The function will then call API_LIBRARY.CREATE_MESSAGE_STR to return a clob that has the appropriate structure for the code detail delete message. The function then calls the DELETE_CODE_DETAIL function to populate the clob that was created.
If the I_action_type is not 'D' (a record has been added or updated), an internal variable holding the doc type should be set to RMSMFM_SEEDDATA.DTL_DESC_MSG. The function will then call API_LIBRARY.CREATE_MESSAGE_STR to return a clob that has the appropriate structure for the code detail desc message. The function then calls the ADD_UPDATE_CODE_DETAIL function to populate the clob that was created.
Function Level Description - DELETE_CODE_DETAIL
FUNCTION DELETE_CODE_DETAIL
(O_status OUT VARCHAR2,
O_text OUT VARCHAR2,
I_code_detail_rec IN CODE_DETAIL%ROWTYPE,
Root IN OUT rib_sxw.SXWHandle)
This function will accept a record that holds code_detail values. The rib_sxw.addElement function will be called to add the code type and code from the I_code_detail_rec to the clob (or root).
Function Level Description - ADD_UPDATE_CODE_DETAIL
This function will accept a record that holds code_detail values. The rib_sxw.addElement function will be called to add the code type, code, code description, required indicator and code sequence from the I_code_detail_rec to the clob (or root).
File name: diff_type_xmls/b.pls
Function Level Description - BUILD_MESSAGE
If the I_action_type is 'D' (a record is being deleted), an internal variable holding the doc type will be set to RMSMFM_SEEDDATA.DIFF_TYPE_REF_MSG. The function will then call API_LIBRARY.CREATE_MESSAGE_STR to return a clob that has the appropriate structure for the diff type delete message. The function then calls the DELETE_DIFF_TYPE function to populate the clob that was created.
If the I_action_type is not 'D' (a record has been added or updated), an internal variable holding the doc type will be set to RMSMFM_SEEDDATA.DIFF_TYPE_DESC_MSG. The function will then call API_LIBRARY.CREATE_MESSAGE_STR to return a clob that has the appropriate structure for the diff type desc message. The function then calls the ADD_UPDATE_DIFF_TYPE function to populate the clob that was created.
Function Level Description - DELETE_DIFF_TYPE
FUNCTION DELETE_DIFF_TYPE
(O_status OUT VARCHAR2,
O_text OUT VARCHAR2,
I_diff_type_rec IN DIFF_TYPE%ROWTYPE,
Root IN OUT rib_sxw.SXWHandle)
This function will accept a record that holds diff_type values. The rib_sxw.addElement function will be called to add the diff type from the I_diff_type_rec to the clob (or root).
Function Level Description - ADD_UPDATE_CODE_DETAIL
FUNCTION ADD_UPDATE_DIFF_TYPE
(O_status OUT VARCHAR2,
O_text OUT VARCHAR2,
I_diff_type_rec IN DIFF_TYPE%ROWTYPE,
Root IN OUT rib_sxw.SXWHandle)
This function will accept a record that holds diff_type values. The rib_sxw.addElement function will be called to add the diff type and diff type description from the I_diff_type_rec to the clob (or root).
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) |
|---|---|---|
| CodeHdrCre | Code Head Create Message | CodeHdrDesc.xsd |
| CodeHdrMod | Code Head Modify Message | CodeHdrDesc.xsd |
| CodeHdrDel | Code Head Delete Message | CodeHdrRef.xsd |
| CodeDtlCre | Code Detail Create Message | CodeDtlDesc.xsd |
| CodeDtlMod | Code Detail Modify Message | CodeDtlDesc.xsd |
| CodeDtlDel | Code Detail Delete Message | CodeDtlRef.xsd |
| DiffTypeCre | Diff Type Create Message | DiffTypeDesc.xsd |
| DiffTypeMod | Diff Type Modify Message | DiffTypeDesc.xsd |
| DiffTypeDel | Diff Type Delete Message | DiffTypeRef.xsd |
This section describes the seed object publication API.
Seed object publication to the RIB allows RMS 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 RMS. 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.
File name: rmsmfm_seedobjs/b.pls
Function Level Description - ADDTOQ
PROCEDURE: ADDTOQ
(O_error_message IN OUT VARCHAR2,
I_message_type IN SEEDOBJ_MFQUEUE.MESSAGE_TYPE%TYPE,
I_country_id IN SEEDOBJ_MFQUEUE.COUNTRY_ID%TYPE,
I_currency_code IN SEEDOBJ_MFQUEUE.CURRENCY_CODE%TYPE,
I_country_desc IN SEEDOBJ_MFQUEUE.COUNTRY_DESC%TYPE,
I_effective_date IN SEEDOBJ_MFQUEUE.EFFECTIVE_DATE%TYPE,
I_exchange_type IN SEEDOBJ_MFQUEUE.EXCHANGE_TYPE%TYPE,
I_exchange_rate IN SEEDOBJ_MFQUEUE.EXCHANGE_RATE%TYPE)
RETURN BOOLEAN;
This function is called by either the COUNTRY or CURRENCY_RATES row trigger, and takes the message type and the table values (country_id for COUNTRY table and currency_code for CURRENCY_RATES table). It inserts a row into the message family queue along with the passed in values and the next sequence number from the message family sequence. The pub status will always be 'U' except for create messages, then it will be 'N'. It returns error codes and strings according to the standards of the application in which it is being implemented.
Function Level Description - GETNXT
PROCEDURE GETNXT
(O_status_code IN OUT VARCHAR2,
O_error_msg 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)
The RIB calls GETNXT to get messages. It performs a cursor loop on the unpublished records on the SEEDOBJ_MFQUEUE table (PUB_STATUS = 'U'). It will only execute one loop iteration in most cases. For each record retrieved, GETNXT checks 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 SEEDOBJ_MFQUEUE and 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
Procedure: 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);
Same as GETNXT except it only loops for a specific row in the SEEDOBJ_MFQUEUE table. The record on SEEDOBJ_MFQUEUE must match the passed in sequence number (contained in the ROUTING_INFO).
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 | Code Head Create Message | CountryDesc.xsd |
| countrymod | Code Head Modify Message | CountryDesc.xsd |
| countrydel | Code Head Delete Message | CountryRef.xsd |
| curratecre | Code Detail Create Message | CurrRateDesc.xsd |
| curratemod | Code Detail Modify Message | CurrRateDesc.xsd |
This section describes the store publication API.
RMS publishes data about stores in messages to the Oracle Retail Integration Bus (RIB) for other applications that needs to keep their locations synchronized with RMS. RMS 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 RMS 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.
File name: rmsmfm_stores/b.pls
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.
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.
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).
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.
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.
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.
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).
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.
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 (store).
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).
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.
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'.
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 |
This section describes the transfers publication API.
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.
This section describes the package impact.
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.
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 RMS 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 appr_ind on the queue 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.
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).
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.
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.
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.
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 RMS) 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.
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 approved (initial_approval_ind = 'N') and the triggering message is one of DTL_ADD, DTL_UPD, DTL_DEL, HDR_DEL, no processing will take place and the function exits.
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. If the publish_ind is 'N', the DTL_DEL message is not added to the queue.
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.
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.
If the message type is HDR_DEL and the record has been published:
Generates a "flat" file to be sent to the RIB. Delete from TRANSFER_PUB_INFO and calls 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 message type is HDR_ADD or DTL_ADD:
Calls MAKE_CREATE to publish the entire transfer.
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 appr_ind on the queue is 'Y'es (meaning the transfer has been approved, 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. Updates TSFDETAIL.publish_ind to 'Y' and 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 as many detail ref Oracle Objects as possible without exceeding the MAX_DETAILS_TO_PUBLISH.
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 - LOCK_DETAILS (local)
Locks the transfer details before updating the publish_ind on TSFDETAIL.
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.
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.
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.
Updates: Sends the tsf_no and item level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_Transfers.DTL_UPD.
Deletes: Sends the tsf_no and item level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_Transfers.DTL_DEL.
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.
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 |
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.
| 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 |
This section describes the UDA publication API.
RMS publishes messages about user-defined attributes (UDAs) to the Oracle Retail Integration Bus (RIB). UDA provides 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 RMS. The UDAs can be displayed in one or more of three formats: Dates, Freeform Text, or a List of Values (LOV).
The created messages in the XML builder adds the messages to the UDA_MFQUEUE table which must be published in the same order as they occur in the RMS database.
File name: rmsmfm_udas/b.pls
Function Level Description - ADDTOQ
Function: ADDTOQ
(O_status OUT VARCHAR2,
O_text OUT VARCHAR2,
I_message_type IN UDA_MFQUEUE.MESSAGE_TYPE%TYPE,
I_uda_id IN UDA.UDA_ID%TYPE,
I_uda_value IN UDA_VALUES.UDA_VALUE%TYPE,
I_display_taype IN UDA_MFQUEUE.DISPLAY_TYPE%TYPE,
I_message IN CLOB
)
This procedure is called by the triggers EC_TABLE_UDA_AIUDR and EC_TABLE_UDV_AIUDR and takes the message type, uda_id and uda_value if there is one and the message itself. It inserts a row into the UDA_MFQUEUE along with the passed in values and the next sequence number from the UDA_MFSEQUENCE, setting the status to 'U'npublished. It returns error codes and strings.
Function Level Description - GETNXT
Procedure: GETNXT
(O_status_code OUT VARCHAR2,
O_error_msg OUT VARCHAR2,
O_message_type OUT UDA_MFQUEUE.MESSAGE_TYPE%TYPE,
O_message OUT CLOB,
O_uda_id OUT UDA.UDA_ID%TYPE,
O_uda_value OUT UDA_VALUES.UDA_VALUE%TYPE,
O_display_type OUT UDA_MFQUEUE.DISPLAY_TYPE%TYPE
)
This publicly exposed procedure is typically called by a RIB publication adaptor. This procedure's parameters are well defined and arranged in a specific order. The message type is the RIB defined short message name; the message is the XML message; and the uda_id and uda_value are the keys for the message as pertains to the UDA family, not all of which will necessarily be populated for all message types. The status code is one of five values.
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) |
|---|---|---|
| UDAHdrCre | UDA Create Message | UDADesc.xsd |
| UDAHdrMod | UDA Modify Message | UDADesc.xsd |
| UDAHdrDel | UDA Delete Message | UDARef.xsd |
| UDAValCre | UDA_Values Create Message | UDAValDesc.xsd |
| UDAValMod | UDA_Values Modify Message | UDAValDesc.xsd |
| UDAValDel | UDA_Values Delete Message | UDAValRef.xsd |
This section describes the vendor publication API.
RMS publishes supplier and supplier address information to the RIB for RWMS and other integration subsystems. 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), and Invoice (5).
As suppliers and addresses are added in RMS, an event capture trigger creates a message that is added to the SUPPLIER_MFQUEUE table.
File name: rmsmfm_vendors/b.pls
Function: ADDTOQ
(I_message_type IN VARCHAR2,
I_supplier IN sups.supplier%TYPE,
I_addr_seq_no IN addr.seq_no%TYPE,
I_addr_type IN addr.addr_type%TYPE,
I_ret_allow_ind IN VARCHAR2,
I_invc_match_ind IN VARCHAR2,
I_org_unit IN VARCHAR2,
I_message IN CLOB,
O_status OUT VARCHAR2,
O_text OUT VARCHAR2)
This procedure is called by the triggers, and takes the message type, supplier, addr_seq_no, addr_type, ret_allow_ind, and invc_match_ind values, and org_unit and, the message itself. It inserts a row into the supplier message family queue along with the passed in values and the next sequence number from the supplier message family sequence, setting the status to unpublished. It returns error codes and strings.
Procedure: GETNXT
(O_status_code OUT VARCHAR2,
O_error_msg OUT VARCHAR2,
O_message_type OUT VARCHAR2,
O_message OUT CLOB,
O_supplier OUT sups.supplier%TYPE
O_addr_seq_no OUT addr.seq_no%TYPE
O_addr_type OUT addr.addr_type%TYPE
This publicly exposed procedure is called by a RIB publication adaptor. The message type is the RIB defined short message name, the message is the xml message, and the family key(s) are the key for the message as pertains to the family, not all of which will necessarily be populated for all message types. The keys for supplier are supplier, addr_seq_no, and addr_type.
This procedure determines if a supplier create already exists on the queue table for the same supplier and with a sequence number less than the current records sequence number.
This procedure cleans up the queue by eliminating modification messages. It is only called if CREATE_PREVIOUS returns true. For each address modification message type, it finds the previous address create message type. It then calls REPLACE_QUE_ADR to copy the modify message into the create message and calls DELETE_QUEUE_REC to delete the modify record. For each delete message type, it finds the previous corresponding create message type. It then calls DELETE_QUEUE_REC to delete the create message record. For each supplier modification message type, it finds the previous supplier create message type. It then calls REPLACE_QUE_SUP to copy the modify message into the create message and calls DELETE_QUEUE_REC to delete the modify record.
This procedure determines if a complete hierarchical supplier message can be created from the current address and prior address messages in the queue for the same supplier. It checks to see if there is a type 3, 4, or 5 address already in the queue. If the ret_allow_ind is 'Y' and there is a type 3 address, then a ret_flag is set to true. If the invc_match_ind is 'Y' and there is a type 5 address, then a invc_flag is set to true. If all the flags are true, then it returns true because the complete hierarchical message can be created.
This procedure combines the current message and all previous messages with the same supplier in the queue table to create the complete hierarchical message. It first creates a new message with the VendorDesc document type. It then gets the supplier create message and adds it to the new message. The remainder of this procedure gets each of the addresses 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 bus.
This procedure deletes a specific record from the queue. It deletes based on the sequence number passed in.
This procedure replaces the message in the create supplier record with the message from the modify supplier record.
This procedure replaces the message in the create address record with the message from the modify address record.
This procedure raises an exception if the status code is set to Error. This will be called immediately after calling a procedure that sets the status code. Any procedure that calls CHECK_STATUS must have its own exception handling section.
This procedure is called when message type is 'VendorOUCre' or 'VendorOUDel'. It first creates a new message with the VendorDesc document type. It then gets the Vendor OrgUnit create message and adds it to the new message. This new message is passed back to the bus.
This private function will build and return entity's customer attributes from SUPS_CFA_EXT table.
This private function will build and return address customer attributes of the entity from ADDR_CFA_EXT table
This procedure is called when message type is 'VendorAddrMod' or 'VendorHdrMod' from GETNXT function. It first creates a new message with the VendorHdrDesc document type. It then calls the BUILD_SUPS_CFA_EXT to build a sub node object of supplier's customer attributes. It also calls BUILD_SUPPLIER_ADDR_CFA_EXT, to build a sub node object of supplier's address customer attributes. Finally the two sub nodes created are attached to the main root node of supplier. This new node is passed back to the RIB.
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) |
|---|---|---|
| VendorCre | Vendor Create | VendorDesc.xsd |
| VendorHdrMod | Vendor Header Modify | VendorHdrDesc. xsd |
| VendorDel | VendorDelete | VendorRef.xsd |
| VendorAddrCre | Vendor Address Create | VendorAddrDesc.xsd |
| VendorAddrMod | Vendor Address Modify | VendorAddrDesc.xsd |
| VendorAddrDel | Vendor Address Delete | VendorAddrRef.xsd |
| VendorOUCre | Vendor OrgUnit Create | VendorOUDesc.xsd |
| VendorOUDel | Vendor OrgUnit Delete | VendorOURef.xsd |
The adaptor is only setup to call stored procedures, not stored functions. Any public program then needs to be a procedure.
Once all criteria are met for a valid create message, the messages will be combined and sent to the RIB.
Messages for supplier and address modifications and deletions will be sent as they are created. An address modification can be sent without the supplier information.
When multiple set of books is enabled in RMS, org units are required elements when creating a supplier. Addition and deletes from this table are sent either as standalone message or part of the supplier create message.
When Supplier Sites functionality is enabled, only supplier site data is published. The Supplier level data are not published.
This section describes the warehouse publication API.
RMS publishes data about warehouses in messages to the Oracle Retail Integration Bus (RIB). Other applications that need to keep their locations synchronized with RMS subscribe to these messages. RMS 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 RMS publishes. Those applications that do not have virtual location logic, such as SIM and RWMS, it depends on RIB to transform RMS warehouse messages for physical warehouses only.
These RIB messages are triggered on inserting, updating, and deleting of warehouse and warehouse address in the RMS 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.
File name: rmsmfm_whs/b.pls
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.
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.
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).
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). |
This private function deletes a record in WH_MFQUEUE table given the row ID.
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.
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.
This overloaded private function accepts warehouse header key value (wh), builds and returns a header level REF Oracle Object.
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.
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 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).
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).
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.
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).
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 |
This section describes the work order in publication API.
A work order provides direction to a warehouse management system (such as RWMS) about work that needs to be completed on items contained in a recent purchase order. RMS publishes work orders soon after it publishes the purchase order itself. This is referred to as a 'work order in' message. This message is not to be confused with a 'work order out' message, which pertains to transfers.
Work order publication consists of a message containing attributes from the WO_DETAIL table plus the order number from the WO_HEAD table. One message is created each time a WO_DETAIL record is created, modified, or deleted. The primary key for the WO_DETAIL consists of the work order ID, warehouse, item, location, and sequence number. Thus, one work order can have multiple Work Order Create messages. When a WO_DETAIL record is created or modified, the message contains a full snapshot of the WO_DETAIL record. When a WO_DETAIL record is deleted, the message contains a partial snapshot of the WO_DETAIL record. Messages are retrieved from the message queue in the order they were created.
Work orders attached to purchase orders will have their messages published after the order has been published. Work orders attached to previously published, approved orders will have their messages published immediately.
Work orders are defined at the physical location level. The message family manager will send 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.
This section describes the package impact.
Prerequisites: An order has been distributed by item and location.
Activity Detail: A work order is ready to be published as soon as the order it is attached has been published. An initial publication message is made.
Messages: A "Work Order Create" message is queued. This message contains a snapshot of the attributes on the WO_DETAIL table.
Prerequisites: Work order has been created.
Activity Detail: The user is allowed to change attributes of the work order detail record. These changes are of interest to other systems and so this activity results in the publication of a message. Work orders attached to purchase orders will have their messages published after the order has been published. Work orders attached to previously published, approved orders will have their messages published immediately.
Messages: Any modifications to a work order detail record will cause a "Work Order Modify" message to be queued. This message contains the same attributes as the "Work Order Create" message.
Prerequisites: Work order has been created.
Activity Detail: Deleting a work order detail record removes it from the system. External systems are notified by a published message.
Messages: When a work order detail record is deleted a "Work Order Delete" message is queued. The message contains a partial snapshot of the WO_DETAIL table.
Package name: RMSMFM_WOIN
Spec file name: rmsmfm_woins.pls
Body file name: rmsmfm_woinb.pls
Package Specification - Global Variables
FAMILY VARCHAR2(64) 'woin'; WO_ADD CONSTANT VARCHAR2(20) 'InBdWOCre'; WO_UPD CONSTANT VARCHAR2(20) 'InBdWOMod'; WO_DEL CONSTANT VARCHAR2(20) 'InBdWODel';
Function Level Description - ADDTOQ
Function: ADDTOQ
(O_error_msg OUT VARCHAR2,
I_queue_rec IN WOIN_MFQUEUE%ROWTYPE,
I_publish_ind IN WO_DETAIL.PUBLISH_IND%TYPE)
This procedure is called by EC_TABLE_WDL_AIUDR, and takes a record type variable that consists of columns from the WO_DETAIL table and message type. It inserts a row into the message family queue WOIN_MFQUEUE along with the passed in values and the next sequence number from the message family sequence, and sets the status to unpublished. It returns a status code of API_CODES.SUCCESS if successful, API_CODES.UNHANDLED_ERROR if not.
Function Level Description - GETNXT
Procedure: GETNXT
(O_status_code OUT VARCHAR2,
O_error_msg OU VARCHAR2,
O_message_type OU VARCHAR2,
O_message OU RIB_OBJECT,
O_bus_obj_id OU RIB_BUSOBJID_TBL,
O_routing_info OU 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 message type is the RIB defined short message name. Status code is one of five values. These codes are defined in the RIB_CODES package.
The error text parameter contains application-generated information, such as the application's sequence number of the message that failed, and the Oracle or other error that occurred when the retrieval failed.
Function Level Description - PUB_RETRY
Procedure: 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);
Same as GETNXT except:
It only loops for a specific row in the WOIN_MFQUEUE table. The record on WOIN_MFQUEUE 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 record from WOIN_QUEUE table is an insert or update (WO_ADD, WO_UPD):
Builds the header object that contains work order ID and order number.
Calls BUILD_DETAIL_OBJECTS to build the Oracle Object to publish to the RIB.
If the record from WOIN_QUEUE table is a delete (WO_DEL):
Builds the header object that contains work order ID and order number.
Calls BUILD_DETAIL_DELETE_OBJECTS to build the Oracle Object to publish to the RIB.
Function Level Description - BUILD_DETAIL_OBJECTS (local)
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 key (work order ID).
Select any details on the WOIN_MFQUEUE that are for the same work order ID and for the same message type.
WOIN_MFQUEUE records that contain information being published are deleted.
Each location represented in the published message is added to the ROUTING_INFO object.
No more than the MAX_DETAILS_TO_PUBLISH numbers of records are put into Oracle Objects.
To avoid deleting information from the queue table that has not been published, deletes are accomplished using ROWIDs. All information is fetched using the same cursor; this ensures that the published message matches the deletes from the WOIN_MFQUEUE table regardless of trigger execution during GETNXT calls.
Function Level Description - BUILD_DETAIL_DELETE_OBJECTS (local)
Perform a cursor for loop on WOIN_MFQUEUE and build as many detail ref Oracle Objects as possible without exceeding the MAX_DETAILS_TO_PUBLISH.
Perform any BULK DML statements for deletion from WOIN_MFQUEUE.
Each location represented in the published message will be added to the ROUTING_INFO 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 WO_DEL messages.
Function Level Description - ROUTING_INFO_ADD (local)
This function is called from within the BUILD_DETAIL_OBJECTS and BUILD_DETAIL_DELETE_OBJECTS. It will add the location from the message to the routing_info whenever a new location is added to the object being published.
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 WOIN_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.
Create a trigger on the WO_DETAIL to capture Inserts, Updates, and Deletes.
Trigger name: EC_TABLE_WDL_AIUDR.TRG
Trigger file name: ec_table_wdl_aiudr.trg
Table: WO_DETAIL
This trigger will capture inserts/updates/deletes to the WO_DETAIL table and write data into the WOIN_MFQUEUE message queue.
Inserts: Sends the header level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_WOIN.WO_ADD.
Updates: Sends the header level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ WOIN.WO_UPD.
Deletes: Sends the header level info to the ADDTOQ procedure in the MFM with the message type RMSMFM_ WOIN.WO_DEL.
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) |
|---|---|---|
| InBdWOCre | Work Order Create Message | WODesc.xsd |
| InBdWOMod | Work Order Modify Message | WODesc.xsd |
| InBdWODel | Work Order Delete Message | WORef.xsd |
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| WOIN_MFQUEUE | Yes | Yes | No | Yes |
| WO_DETAIL | Yes | No | No | No |
| WOIN_MFQUEUE | Yes | Yes | Yes | Yes |
| WO_DETAIL | Yes | No | Yes | No |
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.
The adaptor is only setup to call stored procedures, not stored functions. Any public program then needs to be a procedure.
This section describes the Work Orders out Publication API.
This publication API facilitates the transmission of outbound work orders (OWO) from RMS to external systems. Only transfers that pass through a finisher before reaching the final location may be associated with work orders. The work orders are published upon approval of their corresponding transfers. The work order provides instructions for one or more of the following tasks to be completed at the finisher location:
Perform some activity on an item, such as monogramming.
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 have their own message family because they cannot be bundled with transfer messages. This is because multi-legged transfers can be routed to either internal finishers (held as virtual warehouses) or external finishers (held as partners). Transfers to and from an internal finisher involve at least one book transfer. Because external systems may be unaware of virtual warehouses, book transfers are not communicated to external systems.
Outbound work order data is only published upon approval of the associated transfer. As such, all work order activity, transformation and packing data are contained in the same message. Because RMS does not allow users to modify work order activity, transformation or packing information for an approved transfer, 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 action has occurred, only the second leg is really set back to in progress. The first leg remains in shipped status.
This section describes the package impact.
Prerequisites: A multi-legged transfer must be approved and have work order details for each transfer detail.
Activity Detail: Approving a transfer changes the status of the transfer. This change in status signifies the first time systems external to RMS will have an interest in the existence of the transfer and work order.
Messages: When a transfer with finishing is approved, an "outbdwocre" message is inserted into the queue. The family manager creates a hierarchical message containing a full snapshot of the transfer work order details at the time the message is published.
Prerequisites: The associated transfer has finishing and is being deleted.
Activity Detail: Deleting a transfer removes it, and the associated work order from the system. External systems are notified by a published Delete message that contains the number of the transfer work order to be deleted.
Message: When a transfer with finishing is deleted, an "outbdwodel", which is a flat notification message, is queued.
Prerequisites: A transfer with finishing is unapproved
Activity Detail: Not approving a transfer changes the status to input, which allows modification to the work order, transformation, packing, and item details. External systems are notified by a published Delete message that contains the number of the transfer work order to be deleted.
Messages: Not approving a transfer queues an "outbdwounaprv" request. This results in an "outbdwodel" message being published, which is a flat notification message.
Package name: RMSMFM_WOOUT
Spec file name: rmsmfm_woouts.pls
Body file name: rmsmfm_wooutb.pls
Package Specification - Global Variables
None
Function Level Description - ADDTOQ
Function: ADDTOQ
(O_error_mesage OUT VARCHAR2,
I_message_type IN VARCHAR2,
I_tsf_wo_id IN tsf_wo_head.tsf_wo_id%TYPE)
There are some tasks relating to streamlining the queue clean up 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 WOOUT_PUB_INFO table. The work order number passed to the function should be inserted into the TSF_WO_ID column, and the published column should contain 'N'.
If the business transaction has not been approved (woout_pub_info.publish_ind = 'N') and the triggering message is one of HDR_DEL and HDR_ANAPPRV, the record is not added to queue.
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 function fetches a record from the WOOUT_MFQUEUE table. The function fetches the record that has the lowest sequence number among queue records that have a pub_status of 'U' and a thread_no that matches the I_thread_val.
The LOCK_THE_BLOCK function is called. If it determines that WOOUT_MFQUEUE is locked for a particular work order, set the sequence limit local variable to the current sequence number. This will prevent the GETNXT function from attempting to lock and process the same work order message over and over again in the loop.
The WOOUT_MFQUEUE table is queried to determine if any records for the work order have been sent to the error hospital. If so, produce the 'SEND_TO_HOSP' error message and halt processing.
|
Note: The only scenario in which a hospitalized record with the same tsf_wo_id as the message currently is processed would be found is if the initial HDR_ADD message had been hospitalized and a subsequent HDR_DEL or HDR_UNAPRV was being processed. |
The PROCESS_QUEUE_RECORD function is called. If the break loop indicator returned from process_queue_record is TRUE, set the O_message_type output parameter to the message type fetched from the queue and return TRUE.If the message type is null, the status code output parameter is set to API_CODES.NO_MSG. Otherwise, it is set to API_CODES.NEW_MSG and the O_bus_obj_id parameter is set to RIB_BUSOBJID_TBL(L_tsf_wo_id).
Function Level Description - PUB_RETRY
This procedure is called from the RIB for woout_mfqueue.seq_no's that have been placed in the RIB's error hospital. It functions similarly to GETNEXT, except that it only fetches the record from WOOUT_MFQUEUE that contains the sequence number passed by the RIB.
If the message's tsf_wo_id is null, an API_CODES.NO_MSG error is raised. Then LOCK_THE_BLOCK is called. If the queue record is locked by another process, the status code is set to API_CODES.HOSPITAL. If the queue record is not locked by another process, PROCESS_QUEUE_RECORD is called. If the message returned from process_queue_record is null, the API_CODES.NO_MSG error is raised. Otherwise, if the message object is populated, it populates the business object table with the current work order number.
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.
Check to see if the business object is being published for the first time. If the published_ind on the pub_info table is 'N', the business object is being published for the first time.
This function will set the O_break_loop parameter to FALSE in the following scenarios:
Processing a HDR_UNAPRV message for a work order that has a woout_pub_info.published of 'N'.
Processing a HDR_DEL message for a work order that has a woout_pub_info.published of 'N'.
The loop is not broken in these scenarios because they do not necessitate the publication of a message. Therefore, processing should continue so a message can be outputted.
If the message type is HDR_DEL and the work order has been published the function creates a work order ref object, and routing info object.
|
Note: WO out routing info requires a 'to_loc' string and value. |
If the message type is a HDR_UNAPRV and the work order has been published create a work order ref object and a routing info object. For all records associated with the work order on the tsf_wo_detail, tsf_xform_detail and tsf_packing tables, the publish_ind is set to 'N'.
|
Note: A published value of 'I'n progress indicates that the work order was being published but it had more detail records than allowed for a single message. The maximum detail per message value can be found on the rib_settings table for each message family. |
If the published indicator is 'N', the message type is set to HDR_ADD and the MAKE_CREATE function is called.
If the published indicator is 'I', the message type is set to DTL_ADD and the MAKE_CREATE function is called.
Function Level Description - MAKE_CREATE (local)
This function first calls the BUILD_HEADER_OBJECT function.
It then calls the BUILD_DETAIL_OBJECTS function and updates the woout_pub_info column.
It also updates the published_ind columns on TSF_WO_DETAIL, TSF_XFORM_DETAIL and TSF_PACKING.
Function Level Description - BUILD_HEADER_OBJECT (local)
This function fetches the transfer number and transfer parent number associated with the passed in work order number. It then calls the constructor for the rib_wooutdesc_rec, passing in the work order number, transfer number, and transfer parent number. Finally, it builds the routing info object.
Function Level Description - BUILD_DETAIL_OBJECTS (local)
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.
If the function is being called from MAKE_CREATE:
Selects any unpublished detail records from the business transaction (tsf_wo_detail, tsf_xfrom_detail, tsf_packing).
Ensures that WOOUT_MFQUEUE is deleted from as needed. If there is more than one WOOUT_MFQUEUE record for a detail level record, it makes sure they all get deleted. Current state should be considered, not every change.
Ensures that ROUTING_INFO is constructed if routing information is stored at the detail level in the business transaction.
Ensures that no more than MAX_DETAILS_TO_PUBLISH records are put into Oracle Objects.
Ensures that the detail records being added to the object have not already been published. This can happen if GETNXT was previously called for the current business object, and the MAX_DETAILS_TO_PUBLISH limit had been reached.
Function Level Description - DELETE_QUEUE_REC (local)
This function deletes a record from the outbound work order queue table based on a passed-in sequence number.
Function Level Description - BUILD_WODTL_OBJECT (local)
This function fetches the activity_id, unit_cost and comments for all records from tsf_wo_detail containing the passed in item and work order ID. For each record found:
Populates the wooutactivity record with the activity_id, unit_cost and comments. Then, adds the wooutactivity record to the wooutactivity table.
After all details are processed, the WOOUTACTIVITY table is added to the wooutdtl record that was passed into the function.
Function Level Description - BUILD_PACKING_OBJECT (local)
Procedure: BUILD_PACKING_OBJECT
(O_error_msg IN OUT VARCHAR2,
O_packing_message IN OUT nocopy RIB_WOOUTPACKING_TBL,
IO_rib_wooutpacking_rec IN OUT nocopy RIB_WOOUTPACKING_REC,
I_tsf_packing_id IN tsf_packing.tsf_packing_id%TYPE))
This function first constructs the "RIB_WOOutpackFrom_REC" object by fetching tsf_packing_detail.item where the tsf_packing_id matches that which was passed into the function and the record_type is 'F' (from). Once complete, adds the WOOUTPACKFROM table to the wooutpacking_rec passed to the function.
Next, the "RIB_WOOutpackTo_REC" object is constructed. Fetches the tsf_packing_detail.item where the tsf_packing_id matches that which was passed into the function and the record_type is 'R' (result). Once complete, adds the WOOUTPACKTO table to the wooutpacking_rec passed to the function.
Function Level Description - LOCK_THE_BLOCK (local)
The function locks all records on the queue table for the business object. It has an O_queue_locked output that specifies whether some process other than the current process has the queue locked.
Function Level Description - HANDLE_ERRORS (local)
This procedure handles error status values of 'H'ospital. If the LP_error_status value is 'H'ospital, it populates the business object table with the current work order number, then creates a routing info object and populates it with the sequence number of the queue record. Finally a WOOutRef object is created and added to the O_message object.
The woout_mfqueue is updated by setting the pub_status equal to API_CODES.HOSPITAL.
A trigger on the WO_DETAIL and TSFHEAD exists to capture Inserts, Updates, and Deletes.
Trigger file name: ec_table_thd_aiudr.trg
Table: TSFHEAD
Inserts: Sends the tsf_wo_id level info to the RMSMFM_WOOUT.ADDTOQ procedure in the MFM with the message type RMSMFM_WOOUT.HDR_ADD.
Updates:
Sends the tsf_wo_id level info to the RMSMFM_WOOUT.ADDTOQ procedure in the MFM with the message type RMSMFM_WOOUT.HDR_UNAPRV.
When a transfer is placed in 'A'pproved status the message type for this action will be outbdwocre. When a transfer's status is updated to 'D'eleted, the family manager inserts a record into the queue with a message_type = outbdwodel. When the status is set to 'I'nput from Approved, the family manager inserts a record into the queue with message type = outbdwounaprv.
Deletes: Sends the level info to the RMSMFM_WOOUT.ADDTOQ procedure in the MFM with the message type RMSMFM_WOOUT.HDR_DEL.
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) |
|---|---|---|
| OutBdWoCre | Work Order Create Message | WODesc.xsd |
| OutBdWoDel | Work Order Delete Message | WORef.xsd |
The order upon which transfer and work order messages arrive at locations participating in a multi-legged transfer does not need to be programmatically controlled.
Work order information is never published solely at a detail level. That is, insertions, deletions and updates to work order records may not happen once the work order has been approved. In order to modify work order information, the user will need to unapprove the associated transfer. This will cause a work order header delete message to be published.
When a work order is unapproved or deleted, header level reference information only can be published. Reference information at the detail level is not required to be published, because work order publication is never done at the individual detail level.
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| WOOUT_MFQUEUE | Yes | Yes | Yes | Yes |
| WOPUT_PUB_INFO | Yes | Yes | Yes | Yes |
| TSFHEAD | Yes | No | No | No |
| TSF_WO_HEAD | Yes | No | No | No |
| TSF_WO_DETAIL | Yes | No | Yes | No |
| TSF_XFORM | Yes | No | No | No |
| TSF_XFORM_DETAIL | Yes | No | Yes | No |
| TSF_PACKING | Yes | No | Yes | No |
| TSFDETAIL | Yes | No | No | No |
| TSF_PACKING_DETAIL | Yes | No | No | No |
