| Oracle® Retail Merchandising Foundation Cloud Service Operations Guide Volume 2 - Message Publication and Subscription Designs Release 16.0.024 E90563-01 |
|
 Previous |
 Next |
| Oracle® Retail Merchandising Foundation Cloud Service Operations Guide Volume 2 - Message Publication and Subscription Designs Release 16.0.024 E90563-01 |
|
Previous |
Next |
This chapter provides an overview of the Subscription APIs used in the RMS environment and various functional attributes used in the APIs.
For more information on RIB_XML, see Appendix: RIB_XML.
This section describes the allocation subscription API.
The allocation subscription API allows an external application to create, update, and delete allocations within RMS. The main reason for doing so is to successfully interface and track all dependent bills of lading (BOL) and receipt messages into RMS, as well as to calculate stock on hand correctly.
The allocation subscription API can be used by a 3rd party merchandise system to create, update and delete allocations based on warehouse inventory or cross-dock. The Oracle Retail Allocation product does NOT use this API to interface allocations to RMS. From an Oracle Retail perspective, this API is used by AIP to support the creation of cross dock POs, based on POs sent to RMS using the Order Subscription API.
Allocations only involve stockholding locations. This includes the ability to process allocations to both company and franchise stores, as well as any stockholding warehouse location, excepting internal finishers. If an allocation for a franchise store is received, RMS will also create a corresponding franchise order. This API supports either warehouse-to-warehouse or warehouse-to-store allocations, but no mix-match in a single allocation.
Allocation details can be created, edited, or deleted within the allocation message. Detail line items must exist on an allocation header create message for an allocation to be created. New item location relationships will be created for allocation detail line items entering RMS that do not previously exist within RMS.
New locations can be added to existing allocations, or current locations can be modified on existing allocations. If modifying an existing location, RMS assumes the passed in quantity is an adjustment to the current quantity as opposed to an over write. For example, if the current qty_allocated on ALLOC_DETAIL is 10, and a detail modification message for the same item contains a qty_allocated of 8, ALLOC_DETAIL will be updated with qty_allocated of 10+8 =18.
Details can be individually removed from an allocation if the detail is not in-transit or received or in progress. An entire allocation can be deleted if none of details are in-transit or received or in progress.
In addition to RIB, RMS also exposes an Allocation web service to allow an external application to create, update, and delete allocations in RMS. The web service takes in a collection of allocations and will return success and failure through the service response object.
Filename: AllocationServiceProviderImplSpec.pls AllocationServiceProviderImplBody.pls
For a web service deployment, a new web service 'Allocation' with supported operations is available for an external system to send Allocation requests to RMS. Each supported operation will invoke the public interfaces in the AllocationServiceProviderImpl package as follows:
create - createXAllocDesc
createDetail - createDetailXAllocDesc
modifyHeader - modifyHeaderXAllocDesc
modifyDetail - modifyDetailXAllocDesc
delete- deleteXAllocColRef
deleteDetail - deleteDetailXAllocColDesc
These public interfaces will call the corresponding procedures in svcprov_xalloc, which will in turn call rmssub_xalloc.consume to do the major processing logic.
Filename: svcprov_xallocs/b.pls
Procedures called from Allocation web service public interfaces in the AllocationServiceProviderImpl package to perform major processing.
For delete messages, it loops through and calls RMSSUB_XALLOC.CONSUME for each "RIB_XAllocRef_REC" object in the input collection ("RIB_XAllocColRef_REC").
If error happens, it calls SVCPROV_UTLITY.BUILD_SERVICE_OP_STATUS to build and return "RIB_ServiceOpStatus_REC"with a failure message; if no errors, it builds and returns "RIB_InvocationSuccess_REC" with a success message.
Filename: rmssub_xallocs/b.pls
RMSSUB_XALLOC.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure needs to initially ensure that the passed in message type is a valid type for Allocation messages. If the message type is invalid, a status of "E" will be returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT needs to be downcast to the actual object using Oracle's treat function. If the downcast fails, a status of "E" will be returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume needs to verify that the message passes all of RMS's business validation. It calls the RMSSUB_XALLOC_VALIDATE.CHECK_MESSAGE function to determine whether the message is valid. If the message passed RMS business validation, the function returns true, otherwise it returns false. If the message has failed RMS business validation, a status of "E" will be returned to the external system along with the error message returned from the CHECK_MESSAGE function.
Once the message has passed RMS business validation, it will be persisted to the RMS database. It calls the RMSSUB_XALLOC_SQL.PERSIST_MESSAGE() function. If the database persistence fails, the function returns false. A status of "E" will be returned to the external system along with the error message returned from the PERSIST_MESSAGE() function.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success status, "S", will be returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
RMSSUB_XALLOC.HANDLE_ERROR() is the standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
Filename: rmssub_xallocvals/b.pls
RMSSUB_XALLOC_VALIDATE.CHECK_MESSAGE
(O_error_message IN OUT VARCHAR2,
O_alloc_rec OUT ALLOC_REC,
I_message IN RIB_XAllocDesc,
I_message_type IN VARCHAR2)
This function performs all business validation associated with message and builds the allocation record for persistence.
|
Note: Some of the business validation is referential or involves uniqueness. This validation is handled automatically by the referential integrity constraints and the unique indexes implemented on the database and is not described below. |
ALLOCATION CREATE
Check required fields
If item is a pack, verify receive as type is Pack for from location (warehouse).
Verify details exist
Default fields (status at header, qty pre-scaled, non scale ind)
Build allocation records
Perform following steps if allocation is not cross-docked from an order
Retrieve and build all to-locations that the item does not currently exist at.
Build price history records.
ALLOCATION MODIFY
Check required fields
Populate record.
ALLOCATION DELETE
Check required fields
Verify the allocation is not in-transit or received or in progress. An allocation in progress will have processed_ind equal to 'Y'. An allocation in-transit or received will have a value (other than zero) for any of the following fields: distro quantity, selected quantity, canceled quantity, received quantity, or PO received quantity.
ALLOCATION DETAIL CREATE
Check required fields
Verify details exist
Build allocation records.
Perform following steps if allocation is NOT cross-docked from an order
Retrieve and build all to-locations that the item does not currently exist at.
Build price history records.
ALLOCATION DETAIL MODIFY
Check required fields
If existing allocation records are being modified,
Verify the allocation is not in-transit or received or in progress
Verify modification to quantity does not fall to zero or below.
ALLOCATION DETAIL DELETE
Check required fields
Verify the allocation is not in-transit or received or in progress
Check if deleting detail(s) removes all records from allocation. If so, process message as allocation delete.
Filename: rmssub_xallocsqls/b.pls
RMSSUB_XALLOC_SQL.PERSIST
(O_error_message IN OUT VARCHAR2,
I_dml_rec IN ALLOC_RECTYPE ,
I_message IN RIB_XAllocDesc)
ALLOCATION CREATE
Insert a record into the allocation header table.
Insert a record into the allocation header table.
Insert a record into the allocation charge table.
Insert records into the franchise order tables, if allocating to franchise stores.
For an approved non-cross dock allocation, update transfer reserved for from-location. If a pack item is allocated from a warehouse with pack receive_as_type of 'P' - pack, also update pack component reserved qty for the from-location.
For an approved non-cross dock allocation, update transfer expected for to-location. If a pack item is allocated to a warehouse with pack receive_as_type of 'P' - pack, also update pack component expected qty for the to-location.
If item is not ranged to the to-location, call NEW_ITEM_LOC to create item-location on the fly with ranged_ind of 'Y'. This will insert a record into ITEM_LOC, ITEM_LOC_SOH, ITEM_SUPP_COUNTRY_LOC, PRICE_HIST tables and put a new item-loc event on the future cost event queue. For Brazil localized, item country relationship must exist for the item-location being created.
ALLOCATION MODIFY
Update header record (alloc desc and release date).
ALLOCATION DETAIL CREATE
Same as Allocation Create, except that there is no need to insert into ALLOC_HEADER table.
ALLOCATION DETAIL MODIFY
Update the allocation detail table by adjusting the existing allocated quantity using the passed in quantity. This can either increase or decrease the existing quantity.
Update franchise order quantity if allocating to franchise stores.
For an approved non-cross dock allocation, update transfer reserved for from-location. If a pack item is allocated from a warehouse with pack receive_as_type of 'P' - pack, also update pack component reserved qty for the from-location.
For an approved non-cross dock allocation, update transfer expected for to-location. If a pack item is allocated to a warehouse with pack receive_as_type of 'P' - pack, also update pack component expected qty for the to-location.
ALLOCATION DETAIL DELETE
Delete the record from the allocation detail table.
Delete the record from the allocation charge table.
Delete records from the franchise order tables if the details deleted involve franchise stores.
If deleting details from an approved non-cross dock allocation, update transfer reserved for from-location. If a pack item is allocated from a warehouse with pack receive_as_type of 'P' - pack, also update pack component reserved qty for the from-location.
If deleting details from an approved non-cross dock allocation, update transfer expected for to-location. If a pack item is allocated to a warehouse with pack receive_as_type of 'P' - pack, also update pack component expected qty for the to-location.
ALLOCATION DELETE
Update the allocation header to Cancelled ('C') status.
Update the linked franchise order to Cancelled ('C') status.
Delete all associated record from the allocation charge table.
If deleting an approved non-cross dock allocation, update transfer reserved for from-location. If a pack item is allocated from a warehouse with pack receive_as_type of 'P' - pack, also update pack component reserved qty for the from-location.
If deleting an approved non-cross dock allocation, update transfer expected for to-location. If a pack item is allocated to a warehouse with pack receive_as_type of 'P' - pack, also update pack component expected qty for the to-location
Here are the filenames that correspond with each message type. Refer to the mapping documents for each message type for details about the composition of each message.
| Message Type | Message Type Description | XML Schema Definition (XSD) |
|---|---|---|
| Create | Create Allocation Service Operation | XAllocDesc.xsd |
| CreateDetail | Create Allocation Detail Service Operation | XAllocDesc.Xsd |
| ModifyHeader | Modify Allocation Header Service Operation | XAllocDesc.xsd |
| ModifyDetail | Modify Allocation Detail Service Operation | XAllocDesc.xsd |
| Delete | Delete Allocation Service Operation | XAllocColRef.xsd |
| DeleteDetail | Create Allocation Service Operation | XAllocColRef.xsd |
| AllocCre | External Allocation Create via RIB | XAllocDesc.xsd |
| XAllocDel | External Allocation Delete via RIB | XAllocRef.xsd |
| XAllocDtlCre | External Allocation Detail Create via RIB | XAllocDesc.xsd |
| XAllocDtlDel | External Allocation Detail Delete Via RIB | XAllocRef.xsd |
| XAllocDtlMod | External Allocation Detail Modification Via RIB | XAllocDesc.xsd |
| XAllocMod | External Allocation Modification via RIB | XAllocDesc.xsd |
This API only applies to store level zone pricing.
This API does not currently handle inner packs when needing to create pack component location information.
Passed in item is at transaction level.
From location is a non-finisher stockholding warehouse (i.e. a virtual warehouse).
Because the allocation quantities are not generated based upon RMS inventory positions, RMS provides no stock on hand or inventory validation.
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| ALLOC_HEADER | Yes | Yes | Yes | No |
| ALLOC_DETAIL | Yes | Yes | Yes | Yes |
| ALLOC_CHRG | Yes | Yes | No | Yes |
| ITEM_SUPP_COUNTRY | Yes | No | No | No |
| ITEM_SUPP_COUNTRY_LOC | Yes | Yes | No | No |
| ITEM_LOC_SOH | Yes | Yes | No | No |
| STORE | Yes | No | No | No |
| WH | Yes | No | No | No |
| ITEM_LOC | Yes | Yes | No | No |
| SYSTEM_OPTIONS | Yes | No | No | No |
| ORDHEAD | Yes | No | No | No |
| PRICE_HIST | No | Yes | No | No |
| ITEM_MASTER | Yes | No | No | No |
| WF_ORDER_HEAD | Yes | Yes | Yes | No |
| WF_ORDER_DETAIL | Yes | Yes | Yes | No |
| WF_ORDER_EXP | Yes | Yes | Yes | No |
| WF_CUSTOMER | Yes | No | No | No |
| WF_CUSTOMER_GROUP | Yes | No | No | No |
| WF_COST_RELATIONSHIP | Yes | No | No | No |
| WF_COST_BUILDUP_TMPL_HEAD | Yes | No | No | No |
| WF_COST_BUILDUP_TMPL_DETAIL | Yes | No | No | No |
| FUTURE_COST | Yes | No | No | No |
This section describes the appointments subscription API.
An appointment is information about the arrival of merchandise at a location. From the RIB, RMS subscribes to appointment messages that are published by an external application, such as a warehouse management system (for example, RWMS). RMS processes these messages and attempts to receive against and close out the appointment. In addition, RMS attempts to close the document that is related to the appointment. A document can be a purchase order, a transfer, or an allocation.
Appointment messages cause the creation, update, and closure of an appointment in RMS. Typically the processing of a message results in updating the status of an appointment in the APPT_HEAD table's status column. Valid values for the status column include:
SC-Scheduled
MS-Modified Scheduled
AR-Arrived
AC-Closed
A description of appointment processing follows.
The general appointment message processes occur in this order:
An appointment is created for a location with a store or warehouse type from a scheduled appointment message. It indicates that merchandise is about to arrive at the location. Such a message results in a 'SC' status. At the same time, the APPT_DETAIL table is populated to reflect the purchase order, transfer, or allocation that the appointment corresponds to, along with the quantity of the item scheduled to be sent.
Messages that modify the earlier created appointment update the status to 'MS'.
Once the merchandise has arrived at the location, the appointment is updated to an 'AR' (arrived) status.
Another modification message that contains a receipt identifier prompts RMS to insert received quantities into the APPT_DETAIL table.
After all items are received, RMS attempts to close the appointment by updating it to an 'AC' status.
RMS will close the corresponding purchase order, transfer, or allocation 'document' if all appointments are closed.
Appointment records indicate the quantities of particular items sent to various locations within the system. The basic functional entity is the appointment record. It consists of a header and one or more detail records. The header is at the location level; the detail record is at the item-location level (with ASN as well, if applicable). Documents are stored at the detail level; a unique appointment ID is stored at the header level. In addition, a receipt number is stored at the detail level and is inserted during the receiving process within RMS.
Filename: rmssub_receivings/b.pls
RMSSUB_RECEIVING.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2,
O_rib_otbdesc_rec OUT "RIB_OTBDesc_REC",
O_rib_error_tbl OUT RIB_ERROR_TBL)
This is the procedure called by the RIB. This procedure will make calls to receiving or appointment functions based on the value of I_message_type. If I_message type is RECEIPT_ADD, RECEIPT_UPD, or RECEIPT_ORDADD, then a call is made to RMSSUB_RECEIPT.CONSUME, casting the message as a RIB_RECEIPTDESC_REC. If I_message_type is APPOINT_HDR_ADD, APPOINT_HDR_UPD, APPOINT_HDR_DEL, APPOINT_DTL_ADD, APPOINT_DTL_UPD, or APPOINT_DTL_DEL, then a call is made to RMSSUB_APPOINT.CONSUME.
|
Note: The receiving process RMSSUB_RECEIPT.CONSUME is described in a separate Receiving Subscription API document. |
RMSSUB_RECEIVING.HANDLE_ERRORS
(O_status_code IN OUT VARCHAR2,
IO_error_message IN OUT VARCHAR2,
I_cause IN VARCHAR2,
I_program
Standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
Filename: rmssub_appoints/b.pls
RMSSUB_APPOINT.CONSUME.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This function validates that the message type is valid for appointment subscription. If not, it returns a status of 'E' - Error along with an error message to the calling function.
If it is valid, it casts the message as "RIB_APPOINTDESC_REC" for create and modification message types (APPOINT_HDR_ADD, APPOINT_HDR_UPD, APPOINT_DTL_ADD, APPOINT_DTL_UPD), or "RIB_APPOINTREF_REC" for delete message types (APPOINT_HDR_DEL, APPOINT_DTL_DEL). It then calls local procedures HDR_ADD_CONSUME, HDR_UPD_CONSUME, HDR_DEL_CONSUME, DTL_ADD_CONSUME, DTL_UPD_CONSUME and DTL_DEL_CONSUME to perform the actual subscription logic.
Appointment Create
Location must be a valid store or warehouse.
Document must be valid based on document type ('P' for purchase order, 'T', 'D', 'V' for transfer, 'A' for allocations).
Item must be a valid item.
Insert header to APPT_HEAD if a record does not exist; otherwise, the header insert is skipped.
Insert details to APPT_DETAIL if records do not already exist. Details that already exist are skipped.
Appointment Modify
Location must be a valid store or warehouse.
Item must be a valid item.
Update or insert into APPT_HEAD. Call APPT_DOC_CLOSE_SQL.CLOSE_DOC to close the document if the new appointment status is 'AC'.
Appointment Delete
Location must be a valid store or warehouse.
Delete both header and detail records in APPT_HEAD and APPT_DETAIL.
Appointment Detail Create
Location must be a valid store or warehouse.
Document must be valid based on document type ('P' for purchase order, 'T', 'D', 'V' for transfer, 'A' for allocations).
Item must be a valid item.
Insert details to APPT_DETAIL if records do not already exist. Details that already exist are skipped.
Appointment Detail Modify
Location must be a valid store or warehouse.
Update or insert into APPT_DETAIL.
Appointment Detail Delete
Location must be a valid store or warehouse.
Delete from APPT_DETAIL.
Here are the filenames that correspond with each message type. Please see 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) |
|---|---|---|
| Appointcre | Appointment Create Message | AppointDesc.xsd |
| Appointhdrmod | Appointment Header Modify Message | AppointDesc.xsd |
| Appointdel | Appointment Delete Message | AppointRef.xsd |
| Appointdtlcre | Appointment Detail Create Message | AppointDesc.xsd |
| Appointdtlmod | Appointment Detail Modify Message | AppointDesc.xsd |
| Appointdtldel | Appointment Detail Delete Message | AppointRef.xsd |
The adaptor is only set up to call stored procedures, not stored functions. Any public program needs to be a procedure.
Detail records may contain the same PO/item combination, differentiated only by the ASN number; however, the ASN field will be NULL for detail records which are not associated with an ASN.
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| APPT_HEAD | Yes | Yes | Yes | Yes |
| APPT_DETAIL | Yes | Yes | Yes | Yes |
| ORDHEAD | Yes | No | Yes | No |
| TSFHEAD | Yes | No | Yes | No |
| ALLOC_HEADER | Yes | No | Yes | No |
| STORE | Yes | No | No | No |
| WH | Yes | No | No | No |
| ITEM_MASTER | Yes | No | No | No |
| ORDLOC | Yes | No | No | No |
| DEAL_CALC_QUEUE | Yes | No | No | Yes |
| OBLIGATION | Yes | No | No | No |
| OBLIGATION_COMP | Yes | No | No | No |
| ALC_HEAD | Yes | No | No | Yes |
| ALC_COMP_LOC | Yes | No | No | Yes |
| V_PACKSKU_QTY | Yes | No | No | No |
| TSFDETAIL | Yes | No | No | No |
| SHIPMENT | Yes | No | No | No |
| SHIPSKU | Yes | No | No | No |
| ITEM_LOC | Yes | No | No | No |
| ITEM_LOC_SOH | Yes | No | Yes | No |
| ALLOC_DETAIL | Yes | No | No | No |
This section describes the ASNIN subscription API.
A supplier or consolidator will send an advanced shipping notice (ASN) to RMS through the Oracle Retail Information Bus (RIB). RMS subscribes to the ASN information and places the information onto RMS tables depending upon the validity of the records enclosed within the ASN message.
The ASN message will consist of a header record, a series of order records, carton records, and item records. For each message, header, order and item record(s) will be required. The carton portion of the record is optional. If a carton record is present, however, then that carton record must contain items in it.
The header record will contain information about the shipment as a whole. The order records will identify which orders are associated with the merchandise being shipped. If the shipment is packed in cartons, carton records will identify which items are in which cartons. The item records will contain the items on the shipments, along with the quantity shipped. The items on the shipment should be on the ORDLOC table for the order and location specified in the header and order records.
The location that is contained on the ASN will represent the expected receiving location for the order. If the location is a non-stockholding store in RMS, then the shipment will also be automatically received when the ASN is processed. Two types of non-stockholding stores orders are supported in this integration - franchise stores and drop ship customer orders.
Filename: rmssub_asnins/b.pls
RMSSUB_ASNIN.CONSUME
(O_STATUS_CODE IN OUT VARCHAR2,
O_ERROR_MESSAGE IN OUT VARCHAR2,
I_MESSAGE IN RIB_OBJECT,
I_MESSAGE_TYPE IN VARCHAR2);
The following is a description of the RMSSUB_ASNIN.COMSUME procedure:
The public procedure checks if the message type is create (ASNINCRE), modify (ASNINMOD), or delete (ASNINDEL).
If the message type is ASNINDEL then,
It will cast the message to type "RIB_ASNInRef_REC".
If a message exists in the record then it will call the private function PROCESS_DELETE to delete the ASN record from the appropriate shipment and invoice database tables depending upon the success of the validation.
If no messages exist in the record then it will raise a program error that no message was deleted.
If the message type is ASNINCRE or ASNINMOD then:
It will cast the message to type "RIB_ASNInDesc_REC".
It will parse the message by calling the private function PARSE_ASN.
After parsing the message, it will check if the message contains a PO record. A program error will be raised if either the message type is invalid, or if there is no PO record.
If the records are valid after parsing, the detail records are retrieved and processed in a loop.
Inside the loop:
Records are passed on to the private function PARSE_ORDER.
Delete container and item records from the previous order.
Check if CARTON_IND is equal to 'C'.
If CARTON_IND equal to 'C', call private functions PARSE_CARTON and PARSE_ITEM to parse cartons and items within a carton.
If CARTON_IND is NOT equal to 'C', call private function PARSE_ITEM to parse items that are not part of a container.
Call private function PROCESS_ASN with parsed data on ASN, order, carton, and item records. The records are place in the appropriate shipment and ordering database tables depending upon the success of the validation.
If an error occurs in this procedure or any of the internal functions, this procedure places a call to HANDLE_ERRORS in order to parse a complete error message and pass back a status to the RIB.
HANDLE_ERRORS
(O_status IN OUT VARCHAR2,
IO_error_message IN OUT VARCHAR2,
I_cause IN VARCHAR2,
I_program IN VARCHAR2)
This function is used to put error handling in one place in order to make future error handling enhancements easier to implement. All error handling in the internal RMSSUB_ASNIN package and all errors that occur during subscription in the ASN_SQL package (and whatever packages it calls) will flow through this function.
The function should consist of a call to API_LIBRARY.HANDLE_ERRORS. API_LIBRARY.HANDLE_ERRORS accepts a program name, the cause of the error and potentially an unparsed error message if one has been created through a call to SQL_LIB.CREATE_MESSAGE. The function uses these input variables to parse a complete error message and pass back a status, depending upon the message and error type, back up through the consume function and up to the RIB.
PARSE_ASN
This function will be used to extract the header level information from "RIB_ASNInDesc_REC" and place that information onto an internal ASN header record.
TYPE asn_record IS RECORD( asn SHIPMENT.ASN%TYPE,
destination SHIPMENT.TO_LOC%TYPE,
ship_date SHIPMENT.SHIP_DATE%TYPE,
est_arr_date SHIPMENT.EST_ARR_DATE%TYPE,
carrier SHIPMENT.COURIER%TYPE,
ship_pay_method ORDHEAD.SHIP_PAY_METHOD%TYPE,
inbound_bol SHIPMENT.EXT_REF_NO_IN%TYPE,
supplier ORDHEAD.SUPPLIER%TYPE,
carton_ind VARCHAR2(1));
PARSE_ORDER
This function will be used to extract the order level information from "RIB_ASNInPO_REC" and ASN number from shipment table, and place that information onto an internal order record.
PARSE_CARTON
This function will be used to extract the carton level information from "RIB_ASNInCtn_REC" and ASN and ORDER number from shipment table, and place that information onto an internal carton record.
PARSE_ITEM
This function will be used to extract the item level information from "RIB_ASNInItem_REC", ASN and ORDER number in the shipment table, and CARTON number from carton table, and place that information onto an internal item record.
PROCESS_ASN
After the values are parsed for a particular order in an ASN record, RMSSUB_ASNIN.CONSUME will call this function, which will in turn call various functions inside ASN_SQL in order to validate the values and process the ASN depending upon the success of the validation.
Only one ASN and order record will be passed in at a time, whereas multiple cartons and items will be passed in as arrays into this function. If one order, carton or item value is rejected, then current functionality dictates that the entire ASN message will be rejected.
PROCESS_DELETE
In the event of a delete message, this function will be called rather than PROCESS_ASN. This function will take the asn_no from the parsing function and pass it into ASN_SQL in order to delete the ASN record from the appropriate shipment and invoice tables. A received shipment cannot be deleted.
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) |
|---|---|---|
| asnincre | ASN Inbound Create Message | ASNInDesc.xsd |
| asnindel | ASN Inbound Delete Message | ASNInRef.xsd |
| asninmod | ASN Inbound Modify Message | ASNInDesc.xsd |
This section describes the ASNOUT subscription API.
An internal advance shipment notification (ASN) message holds data that is used by RMS to create or modify a shipment record. Also known as a bill of lading (BOL), internal ASNs are published by an application that is external to RMS, such as a store system (SIM, for example) or a warehouse management system (RWMS, for example). In contrast to a BOL is the external ASN, which is generated by a supplier and shows merchandise movement from the supplier to a retailer location, like a warehouse or store. This overview describes the BOL type of advance shipment notification. For external ASN from suppliers, see ASNIN Subscription API.
Internal ASNs are a notification to RMS that inventory is moving from one location to another. RMS subscribes to BOL messages from the Oracle Retail Integration Bus (RIB).
The external application publishes these ASN messages for:
Pre-existing allocations.
Pre-existing transfers.
Externally generated transfers, created in the store or warehouse (created as transfer type of 'EG' within RMS).
Individual stock orders are held on the transfer and allocation header tables in RMS. A message may contain data about multiple transfers or allocations, and as a result, the shipment record in RMS would reflect these multiple movements of merchandise. A bill of lading number on the shipment record is a means of tracking one or more transfers and allocations back through the respective stock order records.
This API also supports shipment notification for customer order transfers. There are two special handlings of these shipment notifications:
When store inventory is used to fulfill a customer request, SIM will send an ASNOut message without a ship-to location. In that case, RMS will ignore these ASNOut messages, as these are not associated with a transfer or allocation in RMS.
When a warehouse directly ships to a customer, RWMS will send an ASNOut message with a virtual store as the ship-to location. In that case, RMS will auto-receive the shipment.
Additionally, this API supports shipment notifications for franchise order and return transactions. Shipping of franchise orders to a stockholding franchise store, as well as shipping of franchise returns from a stockholding franchise store, is managed in a similar way as a regular store transaction, except that different transaction codes are used for TRAN_DATA. Shipping of franchise orders to a non-stockholding franchise store from a warehouse or a company store will be auto-received in RMS when the ASN is processed.
This is a layer of code which enables decoupling of localization logic that is only required for certain country-specific configuration. This layer affects the RIB API flows including ASNOut subscription. This allows RMS to be installed without requiring customers to install or use this localization functionality, where not required.
Because RMS uses a BOL message only to create a new shipment record, there is one subscribed BOL message. The message consists of a header, a series of transfers or allocations (called 'Distro' records), carton records, and item records. Thus the structure of a BOL hierarchical message would be:
Message header - This is data about the entire shipment including all distro records, cartons, and items.
Distro record - The individual transfer or allocation being shipped.
Carton - Carton numbers and location, as well as carton records will identify which items are in which cartons.
Items - Details about all items in the carton, including shipped quantity.
When external locations (stores or warehouses) ship products, they send a BOL message (otherwise known as an outbound ASN message) to let RMS know that they are shipping the stock and to let the receiving locations know that the stock is on the way. The external locations can create BOL messages for three scenarios: a transfer was requested (RMS knows about it), an allocation was requested (RMS knows about it), and on their own volition (externally generated - EG). A single BOL message can contain records generated for any or all of these transactions.
RMS allows multiple transfers and allocations per shipment, which supports the operational process whereby a stock order shipment is often a group of transfers or allocations on one truck. These transfers or allocations are grouped together using a single BOL number. This number will be stored on the header record for the shipment. All shipments will be associated with a BOL number.
Filename: rmssub_asnouts/b.pls
RMSSUB_ASNOUT.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure will initially ensure that the passed in message type is a valid type for ASNOUT messages.
If the message type is invalid, a status of 'E' should be returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT is downcast to the actual object using the Oracle's treat function. If the downcast fails, a status of "E" is returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume will parse the message, verify that the message passes all of RMS's business validation and persist the information to the RMS database. It does this by calling CONSUME_SHIPMENT.
RMSSUB_ASNOUT.CONSUME_SHIPMENT
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2,
I_check_l10n_ind IN VARCHAR2)
Perform localization check. If localized, invoke RFM's logic through L10N_SQL decoupling layer for procedure key 'CONSUME_SHIPMENT'. If not localized, call CONSUME_SHIPMENT for normal processing.
RMSSUB_ASNOUT.CONSUME_SHIPMENT
(O_error_message IN OUT VARCHAR2,
IO_L10N_RIB_REC IN OUT L10N_OBJ)
Public function to call RMSSUB_ASNOUT.CONSUME_SHIPMENT_CORE.
RMSSUB_ASNOUT.CONSUME_SHIPMENT_CORE
(O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This function contains the main processing logic:
Calls PARSE_BOL to parse the shipment level information on the message. Insert or update shipment based on the bill of lading number (bol_nbr).
One shipment can contain multiple distros (transfers and allocations in RMS). Within each distro, call PARSE_DISTRO and PARSE_ITEM to parse and build a collection of items that are transferred or allocated.
For break-to-sell items, if the sellable item is on the message, call CHECK_ITEMS and GET_ORDERABLE_ITEMS to convert the sellable item(s) to the corresponding orderable item(s). The orderable items will be inserted or updated on transfer/allocation and shipment tables.
For catch weight items, validate and aggregate weight for the same item.
Call PROCESS_DISTRO to perform business logic associated with shipping a transfer or an allocation, including insert or update transfer/allocation header and detail, insert or update SHIPSKU, move inventory to in transit buckets on ITEM_LOC_SOH, write stock ledger.
Bulk inserts and updates are performed to improve performance.
If an error occurs in the process, a status of 'E' is returned to the external system along with the failure message. Otherwise, a success status, 'S', is returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
PARSE_BOL
This function parses the "RIB_ASNOutDesc_Rec" and builds an API bol_record for processing. It also calls RMSSUB_ASNOUT.PROCESS_BOL to check the existence of SHIPMENT based on the bol number.
PROCESS_BOL
This function calls BOL_SQL.PUT_BOL to check the existence of SHIPMENT based on the BOL number.
PARSE_DISTRO
This function parses the "RIB_ASNOutDesc_Rec" and builds an API distro_record for processing.
PARSE_ITEM
This function builds a collection of API item_table that contains item level information for the transfer or allocation. For a simple pack catch weight item, it also aggregates the weight for the same item.
PROCESS_DISTRO
Depending on the distro type (transfer or allocation), this function calls BOL_SQL.PUT_TSF, BOL_SQL.PUT_TSF_ITEM, and BOL_SQL.PROCESS_TSF, or BOL_SQL.PUT_ALLOC, BOL_SQL.PUT_ALLOC_ITEM and BOL_SQL.PROCESS_ALLOC to perform the bulk of the business logic for shipping a transfer or an allocation.
CHECK_ITEMS
This function separates the item details on the message into two groups: one contains sellable items and one contains non-sellable items. The sellable items will be converted into orderable items for shipment.
GET_ORDERABLE_ITEMS
This function builds a collection of orderable items based on the sellable items. Depending on the distro type, it calls ITEM_XFORM_SQL.TSF_ORDERABLE_ITEM_INFO (for transfers) or ITEM_XFORM_SQL.ALLOC_ORDERABLE_ITEM_INFO (for allocations) to distribute the sellable quantities among the orderable items.
HANDLE_ERRORS
This function calls API_LIBRARY.HANDLE_ERRORS to perform error handling.
Filename: bolsqls/b.pls
BOL_SQL.PUT_BOL
This function checks the existence of a shipment based on the BOL number, and creates a shipment if it does not exist.
BOL_SQL.PUT_TSF
This function checks the existence of a transfer in RMS based on the transfer number and does the following:
If the transfer exists, it updates the transfer to shipped status.
If the transfer does not exist, it creates a transfer of type 'EG' (externally generated). Since the sending location is already aware of the transfer, the new transfer will not be published to the RIB again.
BOL_SQL.PUT_TSF_ITEM
This function checks the existence of an item on a transfer based on the transfer number and the item number. It does the following:
If the input item is a referential item, fetch and use its transactional level item.
If the item exists on the transfer, update the quantity buckets on TSFDETAIL.
If the item does not exist on the transfer, create TSFDETAIL. However, new items cannot be added to a closed transfer.
If sending a pack from a warehouse, reject the message if the sending location does not stock packs, unless the sending location is a finisher.
For an 'EG' type of transfer to or from a warehouse, a physical warehouse is on the transfer instead of a virtual warehouse. Distribute the transferred quantity to virtual locations based on distribution rules by creating an inventory flow structure and save it on SHIPITEM_INV_FLOW.
BOL_SQL.PROCESS_TSF
This function calls BOL_SQL.SEND_TSF to perform the bulk of the transfer shipment business logic. The key updates performed by this function are:
If the sending location of the transfer is a finisher, this is the second leg of a multi-legged transfer. Call TSF_WO_COMP_SQL.WO_ITEM_COMP to perform any necessary item transformations, including adjusting inventory and average cost of the old and new items, and writing TRAN_DATA for the adjusted inventory.
Update inventory (stock_on_hand and tsf_reserved_qty) for the item transferred at the sending location.
Update inventory (in_transit_qty and tsf_expected_qty) and average cost for the item transferred at the receiving location.
|
Note: The average cost is never recalculated for a franchise return at the receiving location, as it is considered a customer return and the average cost of the receiving location is used. |
When the item shipped is a pack item, if the pack item is stocked as a pack at the sending and/or receiving location, inventory is updated for both the pack item (stock_on_hand, tsf_reserved_qty, in_transit_qty, tsf_expected_qty) and the pack component items (pack_comp_soh, pack_comp_resv, pack_comp_intran, pack_comp_exp). On the other hand, if the pack item is not stocked as a pack at the sending and/or receiving location, inventory is updated for the component items only (stock_on_hand, tsf_reserved_qty, in_transit_qty, tsf_expected_qty).
When the item shipped is a simple pack catch weight item, average weight on ITEM_LOC_SOH is updated.
When the item shipped is a simple pack catch weight item and the pack component's standard UOM is a mass UOM (for example, LBS), the component's inventory is updated by the actual weight shipped.
Call STKLEDGR_SQL.WRITE_FINANCIALS to write TRAN_DATA records for the sending and receiving locations if the transaction does NOT include a franchise location as the shipping OR receiving location, or if BOTH locations are franchise stores: :
30/32 - for intra-company transfer in/out, in which case the sending and receiving locations belong to the same transfer entity. The transfer is valued at the transfer cost on TSFDETAIL if defined. If not, it is valued at the sending location's WAC. WAC is dependent on the accounting method used, which could be retail accounting or standard cost accounting or average cost accounting. Both WAC and transfer cost are in the sending location's currency.
11/13 - for intra-company markup/markdown. It records the total retail difference between the sending and receiving locations. It is written against either the sending or the receiving location, depending on the settings on the system options (tsf_md_store_to_store_snd_rcv, tsf_md_wh_to_store_snd_rcv, tsf_md_store_to_wh_snd_rcv, tsf_md_wh_to_wh_snd_rcv).
71/72 - for intra-company cost variance. It records the total cost variance as a result of the difference between the sending location's WAC and the transfer cost. It is written against the sending location.
37/38 - for inter-company transfer in/out, in which case the sending and receiving locations belong to different transfer entities. The transfer is valued at the transfer price on TSFDETAIL. Transfer price is defined in the sending location's currency.
17/18 - for inter-company markup/markdown. It records the total retail difference between the transfer price and the sending location's unit retail. It is written against the sending location.
65 - for transfer restocking fees if a restocking percentage is defined on the transfer detail. It can be for an inter-company or an intra-company transfer. It is written against the sending locations.
28 - for up charges.
When a deposit content item is shipped, a TRAN_DATA record is also written for the container item for trans code 30/32 and 37/38. The total cost should be based on the cost of the container.
When a simple pack catch weight item is shipped, the total cost is evaluated at the weight shipped. As a result, TRAN_DATA.total_cost reflects the weight shipped for tran codes 37/38, 30/32, 71/72 and 65. However, all the retail calculation is not weight-based. As a result, TRAN_DATA.total_retail and tran codes 17/18, 11/13 do not reflect the actual weight.
Call STKLEDGR_SQL.WF_WRITE_FINANCIALS to write TRAN_DATA records for the sending and receiving locations if the transaction is a franchise transaction.
20/82 - for franchise order in/out, in which case the sending location is a company location and the receiving location is a franchise store. The transfer is valued at the pricing cost on WF_ORDER_DETAIL (fixed_cost if defined; customer_cost if fixed_cost is not defined). Tran-data 20 is only written if the franchise location is stockholding.
24/83 - for franchise return in/out, in which case the sending location is a franchise store and the receiving location is a company location. The transfer is valued at the return unit cost on WF_RETURN_DETAIL. Tran-data 24 is only written if the franchise location is stockholding.
84/85 - for franchise markup/markdown. It records the total retail difference between the pricing cost (for franchise orders) or return cost (for franchise returns) and the company location's VAT exclusive unit retail. It is written against the company location.
87 - for VAT-in cost, posted in the tran_data.total_cost column against the franchise location:
In case of a franchise order, it records the Total Cost in tran_code 20 * Cost VAT Rate at the franchise location.
In case of a franchise return, it records the Total Cost in tran_code 24 * Cost VAT Rate at the franchise location, with a negative value for total_cost but positive value for units.
88 - for VAT-out retail, posted in the tran_data.total_retail column against the company location:
In case of a franchise order, it records the vat-exclusive Total Retail in tran_code 82 * Retail VAT Rate at the company location.
In case of a franchise return, it records the vat-exclusive Total Retail in tran_code 83 * Retail VAT Rate at the company location, with a negative value for total_retail but positive value for units.
22/23 - for stock adjustment in case of a franchise return with destroy on site. It is only applicable to franchise returns and is written against the company location. If the reason code associated with franchise return destroy on site has a cogs_ind of 'Y', use tran_code 23; otherwise, use tran_code 22.
86 - for franchise restocking fees if a restocking percentage is defined on the franchise return detail. It is only applicable to franchise returns and is written against the company location.
65 - for franchise restocking fees if a restocking percentage is defined on the franchise return detail. It is only applicable to franchise returns and is written against stockholding franchise locations only.
71/72 - for cost variance retail/cost accounting. It records the total cost variance as a result of the difference between the franchise location's WAC and the return unit cost. It is written against the franchise location for franchise returns, if the franchise store is stockholding.
When a deposit content item is shipped on a franchise transaction, a TRAN_DATA record is also written for the container item. The total cost should be based on the pricing/return cost of the container as defined on wf_order_detail and wf_return_detail.
Creates shipsku for the item. For a simple pack catch weight item, weight_expected and weight_expected_uom are written along with the qty_expected.
For a non-franchise transaction, shipsku.unit_retail is the sending location's unit retail. When a break to sell orderable item is shipped, its unit retail is derived from its sellable items. Similarly, in a multi-legged transfer scenario, the sending location can be a finisher. Because a finisher does not have unit retail, the unit retail at the receiving location is used.
For a franchise order, shipsku.unit_cost contains the sending location's WAC at the time of shipment; shipsku.unit_retail contains the pricing cost. For a franchise return, shipsku.unit_cost is based on the return unit cost; shipsku.unit_retail contains the franchise location's unit retail if it's a stockholding location, or the return unit retail if it is a non-stockholding location.
For a customer order transfer that is shipped directly to the customer, call STOCK_ORDER_RCV_SQL.TSF_LINE_ITEM to receive the shipment.
For a franchise transaction, call WF_BOL_SQL.WRITE_WF_BILLING_SALES or WF_BOL_SQL.WF_BILLING_RETURNS to write franchise billing tables.
BOL_SQL.PUT_ALLOC
This function checks the existence of an allocation based on the allocation number, item number and warehouse. If the input item is a referential item, its transactional level item is used. Reject the message if the allocation does not exist.
BOL_SQL.PUT_ALLOC_ITEM
This function checks the existence of allocation detail based on the allocation number and the receiving location. It does the following:
If the store exists on allocation detail, update the quantity buckets on ALLOC_DETAIL.
If the store does not exist on allocation detail, create ALLOC_DETAIL.
If any virtual warehouse in the input physical warehouse does not exist on allocation detail, create ALLOC_DETAIL for the primary virtual warehouse.
If there are multiple virtual warehouses in the same physical warehouse that exist on allocation detail, distribute the transferred quantity to virtual locations based on distribution rules by creating an inventory flow structure.
BOL_SQL.PROCESS_ALLOC
This function calls BOL_SQL.SEND_ALLOC to perform the bulk of the allocation shipment business logic. It does the following:
Update inventory (stock_on_hand and tsf_reserved_qty) for the item allocated at the sending location.
Update inventory (in_transit_qty and tsf_expected_qty) and average cost for the item allocated at the receiving location.
When the item shipped is a pack item, if the pack item is stocked as a pack at the sending/receiving location, inventory is updated for both the pack item (stock_on_hand, tsf_reserved_qty, in_transit_qty, tsf_expected_qty) and the pack component items (pack_comp_soh, pack_comp_resv, pack_comp_intran, pack_comp_exp). On the other hand, if the pack item is not stocked as a pack at the sending/receiving location, inventory is updated for the pack component items only (stock_on_hand, tsf_reserved_qty, in_transit_qty, tsf_expected_qty).
When the item shipped is a simple pack catch weight item, average weight on ITEM_LOC_SOH is updated if the pack is stocked as a pack at the sending/receiving location.
When the item shipped is a simple pack catch weight item and the pack component's standard UOM is a mass UOM (for example, OZ), component's inventory is updated by the actual weight shipped.
Call STKLEDGR_SQL.WRITE_FINANCIALS to write TRAN_DATA records for the sending and receiving locations if the transaction does not include NOT a franchise transaction:
37/38 - for inter-company allocation in/out, in which case the sending and receiving locations belong to different transfer entities. Allocations are valued at the sending location's WAC.
30/32 - for intra-company allocation in/out, in which case the sending and receiving locations belong to the same transfer entity. Allocations are valued at the sending location's WAC.
11/13 - for intra-company markup/markdown. It records the total retail difference between the sending and receiving locations. It is written against either the sending or the receiving location, depending on the settings on the system options (tsf_md_store_to_store_snd_rcv, tsf_md_wh_to_store_snd_rcv, tsf_md_store_to_wh_snd_rcv, tsf_md_wh_to_wh_snd_rcv).
8 - for up charges.
When a deposit content item is shipped, a TRAN_DATA record is also written for the container item for tran codes 30/32 and 37/38. The total cost should be based on the cost of the container.
|
Note: Similar to shipping a transfer, the retail values are not weight-based for a simple pack catch weight item. |
Call STKLEDGR_SQL.WF_WRITE_FINANCIALS to write TRAN_DATA records for the sending and receiving locations if the transaction is a franchise transaction:
|
Note: Check the PROCESS_TSF for tran-codes posted for a franchise transaction. Since allocation is always from a warehouse, it is only possible to have allocations linked to a franchise order, not a franchise return. |
Creates shipsku for the item. For a simple pack catch weight item, weight_expected and weight_expected_uom are written along with the qty_expected. For an allocation with multiple virtual warehouses in the same physical warehouse on allocation detail, only one shipsku record is written with the qty_expected equal to the ship quantity for the item.
For an allocation linked to a franchise order, call WF_BOL_SQL.WRITE_WF_BILLING_SALES to write franchise billing tables.
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) |
|---|---|---|
| asnoutcre | ASN Outbound Create Message | ASNOutDesc.xsd |
The ASNOut subscription process supports the break to sell functionality. Transfers, allocations and shipments in RMS will only contain break to sell orderable items. Inventory adjustment and stock ledger will be performed on the orderable only, not the sellable.
The ASNOut subscription process supports the catch weight functionality. It is assumed that a break to sell sellable item cannot be a simple pack catch weight item.
Catch weight functionality is not completely rounded out in this release. For instance, it is not applied to the following areas:
Any of the retail calculations (including total_retail on TRAN_DATA and retail markup/markdown);
Open to buy buckets;
When a catch weight component item's standard UOM is a MASS UOM, TRAN_DATA.units is based on V_PACKSKU_QTY.qty instead of the actual weight.
An externally generated transfer will contain physical locations. When system options INTERCOMPANY_TSF_IND = 'Y', the stock order receiving process currently does not support the receiving of an externally generated transfer that involves a warehouse to warehouse transfer. This is because a physical location does not have transfer entities.
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| TSFHEAD | Yes | Yes | Yes | No |
| TSFDETAIL | Yes | Yes | Yes | No |
| TRANSFERS_PUB_INFO | No | Yes | No | No |
| ALLOC_HEADER | Yes | Yes | Yes | No |
| ALLOC_DETAIL | Yes | Yes | Yes | No |
| SHIPMENT | Yes | Yes | Yes | No |
| SHIPSKU | Yes | Yes | Yes | No |
| TRAN_DATA | No | Yes | No | No |
| ITEM_LOC_HIST | No | Yes | No | No |
| ITEM_LOC_SOH | Yes | Yes | Yes | No |
| ITEM_LOC | Yes | Yes | No | No |
| ITEM_ZONE_PRICE | Yes | Yes | No | No |
| PRICE_HIST | No | Yes | No | No |
| SHIPITEM_INV_FLOW | No | Yes | No | No |
| STORE | Yes | No | No | No |
| WH | Yes | No | no | No |
| ITEM_MASTER | Yes | No | No | No |
| V_PACKSKU_QTY | Yes | No | No | No |
| ITEM_XFORM_HEAD | Yes | No | No | No |
| ITEM_XFORM_DETAIL | Yes | No | No | No |
| TSF_XFORM | Yes | No | No | No |
| TSF_XFORM_DETAIL | Yes | No | Yes | No |
| TSF_ITEM_COST | Yes | No | Yes | No |
| TSF_ITEM_WO_COST | Yes | No | No | No |
| WO_ACTIVITY | Yes | No | No | No |
| INV_ADJ_REASON | Yes | No | No | No |
| INV_ADJ | Yes | No | No | No |
| INV_STATUS_QTY | Yes | Yes | Yes | Yes |
| DEPS | Yes | No | No | No |
| CURRENCIES | Yes | No | No | No |
| CURRENCY_RATES | Yes | No | No | No |
| PERIOD | Yes | No | No | No |
| SYSTEM_OPTIONS | Yes | No | No | No |
| WEEK_DATA | Yes | No | No | No |
| MONTH_DATA | Yes | No | No | No |
| ITEM_SUPP_COUNTRY | Yes | No | No | No |
| ITEM_SUPP_COUNTRY_DIM | Yes | No | No | No |
| UOM_CLASS | Yes | No | No | No |
| WF_ORDER_HEAD | Yes | No | No | No |
| WF_ORDER_DETAIL | Yes | No | Yes | No |
| WF_RETURN_HEAD | Yes | No | No | No |
| WF_RETURN_DETAIL | Yes | No | Yes | No |
| WF_BILLING_SALES | No | Yes | No | No |
| WF_BILLING_RETURNS | No | Yes | No | No |
This section describes the COGS subscription API.
The Cost Of Goods Sold (COGS) interface lets a retailer make replacements, which is similar to exchanges. However, replacements involve a different accounting process than exchanges. In a replacement, a retailer replaces a previously purchased item with an equivalent unit. To make this replacement, retailer first places the request and ships the undesirable unit out and later the replacement unit is shipped to the retailer. In RMS, the cost of goods sold interface allows the retailer to make this replacement despite the fact that the exchange is not made simultaneously.
The interface writes the value of the transaction to the transaction data tables. An external system (such as Oracle Retail Data Warehouse) can then extract that data.
The subscription process for COGS adjustment involves an interface which contains item, location, quantity, date, order header media, order line media, and a reason code. These records are inserted into the TRAN_DATA table to affect the stock ledger. Message processing includes a call to STKLEDGER_SQL.TRAN_DATA_INSERT to insert the new transaction to the TRAN_DATA table.
RMS subscribes to integration subsystem COGS messages. This process records the inventory and financial transactions associated with a cost of goods sold message.
Filename: rmssub_cogsb/s.pls
PROCEDURE CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
CONSUME simply calls different functions within the corresponding VALIDATE and SQL packages.
Before calling any functions, CONSUME narrows I_message down to the specific object being used, depending on the message_type. For example, a 'Cre' or 'Mod' message type usually means a 'Desc' object is being used. A 'Del' message usually means a 'Ref' object is being used. Object narrowing is done using the TREAT function. If the narrowing fails, then the CONSUME function should return an error message to the RIB stating that the object is not valid for this message family.
CONSUME first calls the family's VALIDATE package to validate the contents of the message. The family's SQL package is then called to perform DML.
Filename: rmssub_cogsvalb/s.pls
This function first calls the CHECK_FIELDS function to make sure all required fields are not NULL. Then, the function calls other function as needed to validate all of the information that has been passed to it from the RIB.
Filename: rmssub_cogssqlb/s.pls
PERSIST
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message_type IN VARCHAR2,
I_cogs_rec IN RMSSUB_COGS.COGS_REC_TYPE)
This function performs the inventory and financial transactions associated with the COGS transaction. The inventory is adjusted at the store location based on the reason code (replacement in/out) provided in the message. In addition a net sale and permanent markdown financial transaction is written to the stock ledger.
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) |
|---|---|---|
| CogsCre | COGS Create Message | CogsDesc.xsd |
The subscriber makes some assumptions about the publisher's ability to maintain data integrity. The subscriber does not check for duplicate Create messages. It will not check for missing messages because it has no way of knowing what would be missing. It also assumes that messages are sent in the correct sequence.
This section describes the cost change subscription API.
A cost change is performed at the following levels of the organization hierarchy: chain, area, region, district, and store. Unit cost is updated for all stores within the location group. Because warehouses are not part of the organization hierarchy, they are only impacted by cost changes applied at the warehouse level.
The subscription does not create cost change events; it updates the cost of an item in real time. It is intended for use only when RMS is not the system of record for cost changes.
The cost change subscription updates unit costs for item/locations that already exist in RMS. It does not create or delete item/locations in RMS tables.
RMS exposes an API that allows external systems to update unit cost within RMS.
This RMS API subscribes to external cost change modify messages for the purpose of integrating external cost changes maintained in an external system into RMS. It updates unit costs in RMS and writes cost history.
In addition to RIB, RMS also exposes a Cost Change web service to allow an external application to create cost changes in RMS. The web service takes in a collection of cost changes and will return success and failure through the service response object.
The RIB_XCostChgDesc_REC message is modified to include RIB_CustFlexAttriVo_TBL message to enable the subscription of the custom flex attributes.
Filename: CostChangeServiceProviderImplSpec.pls, CostChangeServiceProviderImplBody.pls
For a web service deployment, a new web service 'Cost Change' is available for an external system to send Cost Change create requests to RMS. The supported operation will invoke the public interface in the CostChangeServiceProviderImpl package as follows:
create - createXCostChgColDesc
This public interface will call the corresponding procedure in svcprov_xcostchg, which will in turn call rmssub_xcostchg.consume to do the major processing logic.
Filename: svcprov_xcostchgs/b.pls
Procedure called from Cost Change web service public interface in the CostChangeServiceProviderImpl package to perform major processing.
For create messages, it loops through and calls RMSSUB_XCOSTCHG.CONSUME for each RIB_XCostChgDesc_REC object in the input collection (RIB_XCostChgColDesc_REC).
If error happens, it calls SVCPROV_UTILITY.BUILD_SERVICE_OP_STATUS to build and return RIB_ServiceOpStatus_REC with a failure message; if no errors, it builds and returns RIB_InvocationSuccess_REC with a success message.
Filename: rmssub_xcostchgs/b.pls
RMSSUB_XCOSTCHG.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure initially ensures that the passed-in message type is a valid type for cost change messages. There is only one valid message type for Cost change messages, XCostchgMod. If the message type is invalid, a status of "E" is returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT is downcast to the actual object using the Oracle treat function. If the downcast fails, a status of "E" is returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then the consume verifies that the message passes all of RMS's business validation by calling the RMSSUB_XCOSTCHG_VALIDATE.CHECK_MESSAGE function. If the message passed RMS business validation, then the function returns true; otherwise, it returns false. If the message has failed RMS business validation, a status of "E" is returned to the external system along with the error message returned from the CHECK_MESSAGE function.
Once the message has passed RMS business validation, it is persisted to the RMS database by calling the RMSSUB_XCOSTCHG_SQL.PERSIST_MESSAGE() function. If the database persistence fails, the function returns false. A status of "E" is returned to the external system along with the error message returned from the PERSIST_MESSAGE() function.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success status, "S", is returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
The package RMSSUB_XCOSTCHG_CFA enables the subscription of the custom flex attributes. RMSSUB_XCOSTCHG_CFA.CONSUME is called to process the custom flex attributes.
RMSSUB_XCOSTCHG.HANDLE_ERROR() - This is the standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
It should be noted that some of the business validation is referential or involves uniqueness. This validation is handled automatically by the referential integrity constraints and the unique indexes implemented on the database.
Filename: rmssub_xcostchgvals/b.pls
RMSSUB_XCOSTCHG_VALIDATE.CHECK_MESSAGE
(O_error_message IN OUT VARCHAR2,
O_cost_change_rec OUT COST_CHANGE_REC,
I_message IN RIB_XCostChgDesc,
I_message_type IN VARCHAR2)
This function performs all business validation associated with message and builds the cost change record for persistence.
Checks required fields.
Verifies supplier's currency.
Verifies item status.
If diff IDs are passed in, verifies they are valid for passed in item.
Verifies item passed in is not a buyer pack.
Retrieves the item's transaction level children if the passed-in item is a parent.
Retrieves all locations based on passed in hierarchy type and value.
Determines if a location to be updated is the primary location; if so, retrieves the item-supplier-country record to be updated.
Retrieves all item/location combinations where passed-in supplier/country is the primary supplier/country at an item location.
Retrieves all orderable buyers pack that the passed-in item, or its children if above transaction level.
If the recalculate order indicator is 'Y', retrieves all item/locations on approved (and worksheet) orders.
Populates record with message data.
Filename: rmssub_xcchgsqls/b.pls
RMSSUB_XCOSTCHG_SQL.PERSIST
(O_error_message IN OUT VARCHAR2,
I_dml_rec IN COST_CHANGE_RECTYPE ,
I_message IN RIB_XCostChgDesc)
Updates the unit cost on item supplier country location table for all item/locations.
If one of the locations was a primary location, updates the item supplier country table. Inserts into price history all records for all item/locations related to the supplier/country as the primary supplier/country.
If average cost method is not used (system option ECL_IND = N), updates the unit cost on item location stock on hand table for all item/locations related to the supplier/country as the primary supplier/country (packs do not have cost updated).
If the recalculate order indicator is 'Y', updates all relevant order/item/locations unit cost.
If pack processing is necessary, repeats the above steps except updating item location stock on hand.
Here are the filenames that correspond with the message type. Please consult the RIB documentation to get a detailed picture of the composition of the message.
| Message Type | Message Type Description | XML Schema Definition (XSD) |
|---|---|---|
| create | Create Cost Change Service Operation | XCostChgDesc.xsd |
| Xcostchgmod | External Cost Change Modify | XCostChgDesc.xsd |
Required fields are shown in the RIB documentation.
Updating the order cost does not take into account any aspects of building the order cost (estimated landed cost, deals, bracket cost, and so on) and will not work for a base solution.
This API does not take into account estimated landed cost.
This API assumes 'A'verage cost accounting. Hence no logic exists for 'S'tandard (last received) cost accounting.
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| ITEM_SUPP_COUNTRY | Yes | No | Yes | No |
| ITEM_SUPP_COUNTRY_LOC | Yes | No | Yes | No |
| ITEM_LOC_SOH | Yes | No | Yes | No |
| STORE | Yes | No | No | No |
| WH | Yes | No | No | No |
| ITEM_MASTER | Yes | No | No | No |
| DIFF_GROUP_HEAD | Yes | No | No | No |
| DIFF_GROUP_DETAIL | Yes | No | No | No |
| CHAIN | Yes | No | No | No |
| AREA | Yes | No | No | No |
| REGION | Yes | No | No | No |
| DISTRICT | Yes | No | No | No |
| ITEM_LOC | Yes | No | No | No |
| ORDLOC | Yes | No | Yes | No |
| ORDHEAD | Yes | No | No | No |
| PRICE_HIST | No | Yes | No | No |
| SYSTEM_OPTIONS | Yes | No | No | No |
| COST_SUPS_SUP_HEAD_CFA_EXT | No | Yes | No | No |
This section describes the currency exchange rates subscription API.
Currency exchange rates constitute financial information that is published to the Oracle Retail Integration Bus (RIB). A currency exchange rate is the price of one country's currency expressed in another country's currency.
|
Note: When the RMS and the financial system are initially set up, identical currency information (3-letter codes, exchange rate values) is entered into both. If a new currency needs to be used, it must be entered into both the financial system and RMS before a rate change is possible. No functionality currently exists to bridge this data. |
An external system will publish a currency exchange rate, thereby placing the currency exchange rate information onto the RIB. RMS will subscribe to the currency exchange rate information as published from the RIB and place the information onto RMS tables depending upon the validity of the records enclosed within the message.
Filename: rmssub_curratecres/b.pls
Subscribing to a currency exchange rate message entails the uses of one public consume procedure. This procedure corresponds to the type of activity that can be done to currency exchange rate record (in this case create/update).
Public API Procedures:
RMSSUB_CURRATECRE.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN CLOB)
This procedure accepts a XML file in the form of an Oracle CLOB data type from the RIB (I_message). This message contains a currency exchange rate message consisting of the aforementioned record. The procedure calls the main RMSSUB_CUR_RATES.CONSUME function in order to validate the XML file format and, if successful, parses the values within the file through a series of calls to RIB_XML. The values extracted from the file are then passed on to private internal functions, which validate the values and place them on the currency exchange rate table depending upon the success of the validation.
Private Internal Functions and Procedures (rmssub_curratecre.pls)
Error Handling:
If an error occurs in this procedure, a call is placed to HANDLE_ERRORS in order to parse a complete error message and pass back a status to the RIB.
HANDLE_ERRORS
(O_status IN OUT VARCHAR2,
IO_error_message IN OUT VARCHAR2,
I_cause IN VARCHAR2,
I_program IN VARCHAR2))
This function is used to put error handling in one place in order to make future error handling enhancements easier to implement. All error handling in the internal RMSSUB_CUR_RATES package and all errors that occur during subscription in the RMSSUB_CURRATECRE package (and whatever packages it calls) flow through this function.
The function consists of a call to API_LIBRARY.HANDLE_ERRORS. API_LIBRARY.HANDLE_ERRORS accepts a program name, the cause of the error and potentially an unparsed error message if one has been created through a call to SQL_LIB.CREATE_MESSAGE. The function uses these input variables to parse a complete error message and pass back a status, depending upon the message and error type, back up through the consume function and up to the RIB.
Private Internal Functions and Procedures (other):
All of the following functions exist within RMSSUB_CUR_RATES.
Main Consume Function:
RMSSUB_CUR_RATES.CONSUME
(O_error_message OUT VARCHAR2,
I_message IN CLOB)
This procedure accepts a XML file in the form of an Oracle CLOB data type from the RIB (I_message) from the aforementioned public curratecre procedure whenever a message is made available by the RIB. This message consists of the aforementioned record.
The procedure then validates the XML file format and, if successful, parses the values within the file through a series of calls to RIB_XML. The values extracted from the file are then passed on to private internal functions, which validate the values and place them on the appropriate currency exchange rate database table depending upon the success of the validation.
XML Parsing:
PARSE_HEADER : This function is used to extract the currency exchange rate level information from the currency exchange rate xml file and place that information onto an internal currency exchange rate record.
Validation:
PROCESS_HEADER: After the values are parsed for a particular currency exchange rate record, RMSSUB_CUR_RATES.CONSUME calls this function, which in turn calls various functions inside RMSSUB_CUR_RATES in order to validate the values and place them on the appropriate currency exchange rate table depending upon the success of the validation. CONVERT TYPE is called to validate the passed in currency rate if it exists in the FIF_CURRENCY_XREF table. PROCESS_RATES is called to actually insert or update the currency exchange rate table.
CONVERT_TYPE: This function takes in the current record's exchange rate type and returns the RMS exchange type from the table FIF_CURRENCY_XREF. If no data is found, it should return an error message.
PROCESS_RATES: This function calls VALIDATE_RATES to ensure that the values passed from the message are valid. If all the values are valid, it checks if the currency code exists in the currency exchange rate table. If the currency code does not exist yet, the function INTEREST RATES is called. If not, UPDATE RATES is called.
VALIDATE_RATES: This function passes each value from the record to the function CHECK_NULLS. CHECK_SYSTEM is used for conversion date.
CHECK_NULLS: This function checks if the values passed are NULL. If the passed value is NULL, then an invalid parameter error message is returned.
CHECK_SYSTEM: This function fetches the vdate and the currency code from the period and system options table respectively. If the vdate is greater than the conversion date, an error message is returned. If the passed in currency rate is not the same as the currency rate fetched from the system options table, an error message is returned.
DML Module:
INSERT_RATES: This function inserts into the currency exchange rate table after all of the validations of the values are done.
UPDATE_RATES: This function locks the CURRENCY_RATES table first. After that the table is locked it updates the record in the currency exchange rate table.
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) |
|---|---|---|
| CurrateCre | Currency Rate Create Message | CurrRateDesc.xsd |
| CurrateCre | Currency Rate Modify Message | CurrRateDesc.xsd |
One of the primary assumptions in the current API 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 Diff group subscription API.
Differentiator subscriptions come into RMS from an external system. With a differentiator group subscription, you create the differentiator group in the external system, and then send that information to RMS. Once the subscription has been received, RMS users can now use the differentiator group that comes from the external system. The group is always sent first; its IDs are sent second.
Differentiators augment RMS' item level structure by allowing you to define more discrete characteristics of an item. You attach differentiators to items to distinguish one item from another. Differentiators (diffs) give you the means to further track merchandise sales transactions. Common types of diffs are size, color, flavor, scent, or pattern.
Diffs consist of:
Diff types; Generic categories of diff IDs such as Size, Color, or Flavor.
Diff IDs: Specific attributes such as black, white, red; small, medium; strawberry, blueberry.
Diff groups; Logical groupings of related diff IDs such as: Women's Pant Sizes, Shirt Colors, or Yogurt Flavors.
This API allows external systems to create, edit, and delete diff groups within RMS. The transaction will be performed immediately upon message receipt so success or failure can be communicated to the calling application.
Diff ID details can be created, edited, or deleted within the diff group message. Diff ID details must be created within a diff group on a diff group create message, they can also be passed in with their own specific message type. Diff ID detail create and modify messages will send a snapshot of the diff group record. Diff ID detail delete messages will be processed separately from the diff group delete because they have their own message types.
Filename: rmssub_xdiffgrps/b.pls
RMSSUB_XDIFFGRP.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure will need to initially ensure that the passed in message type is a valid type for diff IDs messages. If the message type is invalid, a status of "E" should be returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT need to be downcast to the actual object using the Oracle's treat function. If the downcast fails, a status of "E" should be returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume needs to verify that the message passes all of RMS's business validation. It calls the RMSSUB_XDIFFGRP_VALIDATE.CHECK_MESSAGE function to determine whether the message is valid. If the message passed RMS business validation, then the function will return true; otherwise, it will return false. If the message has failed RMS business validation, a status of "E" should be returned to the external system along with the error message returned from the CHECK_MESSAGE function.
Once the message has passed RMS business validation, it can be persisted to the RMS database. It calls the RMSSUB_XDIFFGRP_SQL.PERSIST_MESSAGE() function. If the database persistence fails, the function will return false. A status of "E" should be returned to the external system along with the error message returned from the PERSIST_MESSAGE() function.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success, "S", status should be returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
RMSSUB_XDIFFGRP.HANDLE_ERROR() - This is the standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
It should be noted that some of the business validation is referential or involves uniqueness. This validation is handled automatically by the referential integrity constraints and the unique indexes implemented on the database.
Filename: rmssub_xdiffgrpvals/b.pls
RMSSUB_XDIFFGRP_VALIDATE.CHECK_MESSAGE
(O_error_message IN OUT VARCHAR2,
O_diffgroup_rec OUT DIFF_GROUP_REC,
I_message IN RIB_XdiffgrpDesc,
I_message_type IN VARCHAR2)
This function performs all business validation associated with the messages and builds the diff group record for persistence.
DIFF GROUP CREATE
Check required fields.
Verify diff group ID not used in diff ID table.
Verify diff ID detail node is populated.
Verify diff ID details are on diff ID table (not diff group table).
Populate record with message data.
DIFF GROUP MODIFY
Check required fields.
Verify the Diff group exists.
Verify diff group is not attached to any items or pack templates.
Populate record with message data.
DIFF GROUP DELETE
Check required fields.
Verify the Diff group exists.
Verify diff group is not attached to any items or pack templates.
Populate record with message data.
DIFF ID CREATE
Check required fields.
Verify diff ID detail node is populated.
Verify diff ID details are on diff ID table (not diff group table).
Populate record with message data.
DIFF ID MODIFY
Check required fields.
Verify diff group exists.
Verify diff ID detail node is populated.
Verify diff ID details are on diff ID table (not diff group table).
Verify diff ID details on diff group detail table.
DIFF ID DELETE
Check required fields.
Verify diff group exists.
Verify the diff ID exists on diff group table.
Verify no items or pack templates are using that diff group detail diff ID.
Populate record with message data.
Bulk or Single DML Module
All insert, update and delete SQL statements are located in the family package. This package is DIFF_GROUP_SQL. The private functions will call this package.
Filename: rmssub_xdiffgrpsqls/b.pls
RMSSUB_XDIFFGRP_SQL.PERSIST_MESSAGE
(O_error_message IN OUT VARCHAR2,
I_diff_group_rec IN DIFF_GROUP_REC,
I_message_type IN VARCHAR2,)
This function determines what type of database transaction it will call based on the message type.
DIFF GROUP CREATE
Create messages get added to the Diff group head table.
Diff group details get added to the diff group detail table.
DIFF GROUP MODIFY
Modify messages directly update the Diff group head table with changes.
DIFF GROUP DELETE
Delete messages directly remove Diff group head records.
DIFF GROUP DETAIL CREATE
Create messages get added to the Diff group detail table.
DIFF GROUP DETAIL MODIFY
Modify messages directly update the Diff group detail table with changes.
DIFF GROUP DETAIL DELETE
Delete messages directly remove Diff group detail records.
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 Type | Message Type Description | XML Schema Definition (XSD) |
|---|---|---|
| Xdiffgrpdtlcre | Create a diff group detail | XDiffGrpDesc.xsd |
| Xdiffgrpdtldel | Delete a diff group detail | XDiffGrpRef.xsd |
| xdiffgrpdtlmod | Modify a diff group detail | XDiffGrpDesc.xsd |
| xdiffgrpcre | Create a diff group header | XDiffGrpDesc.xsd |
| xdiffgrpdel | Delete an entire diff group | XDiffGrpRef.xsd |
| xdiffgrpmod | Modify a diff group header | XDiffGrpDesc.xsd |
This section describes the Diff ID subscription API.
The diff ID subscription API provides a means to keep RMS in sync with an external system.
This API allows an external system to create, edit, and delete Diff Ids within RMS. These transactions are performed immediately upon message receipt so success or failure can be communicated to the calling application.
Filename: rmssub_xdiffids/b.pls
RMSSUB_XDIFFID.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure initially ensures that the passed in message type is a valid type for diff IDs messages. If the message type is invalid, a status of "E" is returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT is downcast to the actual object using the Oracle treat function. If the downcast fails, a status of "E" is returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume verifies that the message passes all of RMS's business validation calling the RMSSUB_XDIFFID_VALIDATE.CHECK_MESSAGE function. If the message passes RMS business validation, then the function returns true; otherwise it returns false. If the message has failed RMS business validation, a status of "E" is returned to the external system along with the error message returned from the CHECK_MESSAGE function.
Once the message has passed RMS business validation, it is persisted to the RMS database by calling the RMSSUB_XDIFFID_SQL.PERSIST_MESSAGE() function. If the database persistence fails, the function returns false. A status of "E" is returned to the external system along with the error message returned from the PERSIST_MESSAGE() function.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success, "S", status is returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
RMSSUB_XDIFFID.HANDLE_ERROR() - This is the standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
It should be noted that some of the business validation is referential or involves uniqueness. This validation is handled automatically by the referential integrity constraints and the unique indexes implemented on the database.
Filename: rmssub_xdiffidvals/b.pls
RMSSUB_XDIFFID_VALIDATE.CHECK_MESSAGE
(O_error_message IN OUT VARCHAR2,
O_diffid_rec OUT DIFF_ID_REC,
I_message IN RIB_XDiffIDDesc,
I_message_type IN VARCHAR2)
This function performs all business validation associated with messages and builds the diff ID record for persistence.
DIFF ID CREATE
Checks required fields.
Verifies diff ID not used in diff group head table.
Populates record with message data.
DIFF ID MODIFY
Checks required fields.
Verifies the Diff Id exists.
Populates record with message data.
DIFF ID DELETE
Checks required fields.
Verifies the Diff Id exists.
Deletes the record with diff ID contained in the message data.
Bulk or single DML module
All insert, update and delete SQL statements are located in the family package. This package is DIFF_ID_SQL. The private functions will call this package.
Filename: rmssub_xdiffidsqls/b.pls
This function determines what type of database transaction it will call based on the message type.
DIFF ID CREATE
Create messages get added to the Diff ID table.
DIFF ID MODIFY
Modify messages directly update the Diff ID table with changes.
DIFF ID DELETE
Delete messages directly remove Diff ID records.
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 Type | Message Type Description | XML Schema Definition (XSD) |
|---|---|---|
| xdiffidcre | External Differentiator Create | XDiffIDDesc.xsd |
| xdiffiddel | External Differentiator Delete | XDiffIDRef.xsd |
| xdiffidmod | External Differentiator Modify | XDiffIDDesc.xsd |
This section describes the Direct ship receipt subscription API.
In the direct ship receipt process, a retailer does not own inventory, but still records a sale on their books.
An external integration subsystem takes the order and sends it to a supplier.
When an integration subsystem is notified that a direct ship order is sent from the supplier, it publishes a new direct ship (DS) receipt message to the RIB for RMS' subscription purposes. RMS can then account for the data in the stock ledger.
Processing in conjunction with the subscription ensures that the weighted average cost for the item is recalculated.
RMS subscribes to integration subsystem direct ship receipt (DSR) messages. This records the inventory and financial transactions associated with the direct shipment of merchandise.
Filename: rmssub_dsrcpts/b.pls
RMSSUB_DSRCPT.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
CONSUME simply calls different functions within the corresponding VALIDATE and SQL packages.
Before calling any functions, CONSUME narrows I_message down to the specific object being used, depending on the message_type. For example, a 'Cre' or 'Mod' message type usually means a 'Desc' object is being used. A 'Del' message usually means a 'Ref' object is being used. Object narrowing is done using the TREAT function. If the narrowing fails, then the CONSUME function should return an error message to the RIB stating that the object is not valid for this message family.
CONSUME first calls the family's VALIDATE package to validate the contents of the message. The family's SQL package is then called to perform DML.
Filename: rmssub_dsrcpt_vals/b.pls
CHECK_MESSAGE
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
O_dsrcpt_rec OUT NOCOPY RMSSUB_DSRCPT.DSRCPT_REC_TYPE,
I_message IN "RIB_XOrderDesc_REC",
I_message_type IN VARCHAR2)
This function first calls the CHECK_FIELDS function to make sure all required fields are not NULL. Then, the function will call other functions as needed to validate all of the information that has been passed to it from the RIB.
Filename: rmssub_dsrcpt_sqls/b.pls
RMSSUB_DSRCPT_SQL.PERSIST
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_dsrcpt_rec IN RMSSUB_DSRCPT.DSRCPT_REC_TYPE,
I_message_type IN VARCHAR2)
This function will perform the inventory and financial transactions associated with the direct ship receipt. This includes updating the stock on hand and average cost for the item at the virtual store against which the direct shipment is being received, and, booking the associated purchase to the stock ledger for the item / virtual store.
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) |
|---|---|---|
| Dsrcptcre | Dsrcpt Create Message | DsrcptDesc.xsd |
The subscriber makes some assumptions with the publisher's ability to maintain data integrity. The subscriber will not check for duplicate create messages. It will not check for missing messages because it has no way of knowing what would be missing. It also assumes that messages are sent in the correct sequence.
This section describes the DSD deals subscription API.
Direct Store Delivery (DSD) is a delivery of merchandise and/or services to a store without the benefit of a pre-approved purchase order, such as when the supplier drops off merchandise directly in the retail er's store. This process is common in convenience and grocery stores, where suppliers routinely come to restock merchandise.
In these cases, the invoice may or may not be given to the store (as opposed to sent to corporate), and the invoice may or may not be paid for out of the register.
RMS subscribes to DSD messages from the RIB. These messages notify RMS of a direct store delivery transaction at a location so that it may record the purchase order and account for it in the store's inventory.
The receipt message that enters RMS includes information such as unit quantity, location, and others. Based on the data, RMS performs the following functionality, as necessary.
Creates a purchase order.
Applies any deals
Creates a shipment
Receives a shipment.
Creates an invoice
|
Note: If ReIM is not running, invoices are not created. |
Filename: rmssub_dsddealss/b.pls
This procedure initially ensure that the passed in message type is a valid type for DSD deals. The valid message type for DSD deals messages are listed in a section below.
If the message type is invalid, a status of "E" will be returned to the external system along with an appropriate error message informing the external system that the status is invalid.
For each header level data in the DSD deals table, call the function COMPLETE_TRANSACTION to persist data to the RMS database.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success status, "S", is returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
If an error occurs in this procedure, a call will be placed to HANDLE_ERRORS in order to parse a complete error message and pass back a status to the RIB.
This function checks for a shipment record on the shipment table for the DSD being processed. If no shipment record exists, it applies any applicable deals to the DSD order being processed and inserts shipment records into the shipment and shipsku tables for the newly created purchase order. After creating the new shipment, it receives the shipment and approves the order. If the DSD message contains invoice information, it creates the invoice.
The function consists of a call to API_LIBRARY.HANDLE_ERRORS. API_LIBRARY.HANDLE_ERRORS accepts a program name, the cause of the error and potentially an unparsed error message if one has been created through a call to SQL_LIB.CREATE_MESSAGE.
The function uses these input variables to parse a complete error message and pass back a status, depending upon the message and error type, back up through the consume function and up to the RIB.
Here are the filenames that correspond with each message type. Please see 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) |
|---|---|---|
| dsddealscre | DSD Deals Create Message | DSDDealsDesc.xsd |
This section describes the DSD receipt subscription API.
Direct store delivery (DSD) is the delivery of merchandise and/or services to a store without the benefit of a pre-approved purchase order. When the delivery occurs, the integration subsystem informs RMS of the receipt so a purchase order is created and it is counted in the store's inventory.
Filename: rmssub_dsds/b.pls
RMSSUB_DSD.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_rib_dsddesc_rec IN "RIB_DSDReceiptDesc_REC",
I_message_type IN VARCHAR2,
O_rib_dsddeals_rec OUT "RIB_DSDDealsDesc_REC")
RMSSUB_DSD.CONSUME
The passed in message type is validated to ensure it is a valid type for DSD receipts. The valid message type for DSD Receipts messages are listed in a section below.
If the message type is invalid, a status of "E" will be returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is DSD_CRE, it performs validation on the values in the message. If the data is valid, it processes the non-merchandise data for delivery costs and detail level data before persisting the data to RMS databases.
If the message type is DSD_MOD, call the GET_ORDER_NO function to find the order number for the DSD.
If the message type is a create message, the O_rib_dsddeals_rec record is populated and passed back to the RIB so that it may be sent to the RMSSUB_DSDDEALS consume function. If the message type is not create, then the O_rib_dsddeals_rec should be set to null.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success status, "S", is returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
If an error occurs in this procedure, a call will be placed to HANDLE_ERRORS in order to parse a complete error message and pass back a status to the RIB.
RMSSUB_DSD.GET_ORDER_NO
GET_ORDER_NO (O_error_message IN OUT VARCHAR2,
O_order_no IN OUT ordhead.order_no%TYPE,
I_ext_receipt_no IN shipment.ext_ref_no_in%TYPE,
I_store IN store.store%TYPE,
I_supplier IN sups.supplier%TYPE)
This function is called for message type DSD_MOD. This function retrieves the current order number by searching the shipment tables using the external receipt number, store number and supplier.
RMSSUB_DSD.HANDLE_ERRORS
RMSSUB_DSD.HANDLE_ERRORS
(O_status IN OUT VARCHAR2,
IO_error_message IN OUT VARCHAR2,
I_cause IN VARCHAR2,
I_program IN VARCHAR2)
The function consists of a call to API_LIBRARY.HANDLE_ERRORS. API_LIBRARY.HANDLE_ERRORS accepts a program name, the cause of the error and potentially an unparsed error message if one has been created through a call to SQL_LIB.CREATE_MESSAGE.
The function uses these input variables to parse a complete error message and pass back a status, depending upon the message and error type, back up through the consume function and up to the RIB.
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) |
|---|---|---|
| dsdreceiptcre | DSD Receipt Create Message | DSDReceiptDesc.xsd |
| dsdreceiptmod | DSD Receipt Modify Message | DSDReceiptDesc.xsd |
This section describes the freight terms subscription API.
Freight terms are financial arrangement information that is published to the Oracle Retail Integration Bus (RIB) from a financial system. Freight terms are the terms for shipping (for example, the freight terms could be a certain percentage of the total cost; a flat fee per order, etc).. RMS subscribes to freight terms messages held on the RIB. After confirming the validity of the records enclosed within the message, the RMS database is updated with the information.
Required fields in the message include a unique freight terms ID and a description.
Filename: rmssub_frttermcres/b.pls
rmssub_fterms/b.pls
Subscribing to a freight term message entails the uses of one public consume procedure. This procedure corresponds to the type of activity that can be done to a freight term record (in this case create/update).
This procedure accepts a XML file in the form of an Oracle CLOB data type from the RIB (I_message).
This message will contain a freight term message consisting of the aforementioned record. The procedure will then place a call to the main RMSSUB_FTERM.CONSUME function in order to validate the XML file format and, if successful, parse the values within the file through a series of calls to RIB_XML. The values extracted from the file will then be passed on to private internal functions, which will validate the values and place them on the freight term table depending upon the success of the validation.
Error Handling
If an error occurs in this procedure, a call will be placed to HANDLE_ERRORS in order to parse a complete error message and pass back a status to the RIB.
HANDLE_ERRORS
(O_status IN OUT VARCHAR2,
IO_error_message IN OUT VARCHAR2,
I_cause IN VARCHAR2,
I_program IN VARCHAR2);
All error handling in the internal RMSSUB_FTERM package and all errors that occur during subscription in the RMSSUB_FRTTERMCRE package (and whatever packages it calls) will flow through this function.
The function consists of a call to API_LIBRARY.HANDLE_ERRORS. API_LIBRARY.HANDLE_ERRORS accepts a program name, the cause of the error and potentially an unparsed error message if one has been created through a call to SQL_LIB.CREATE_MESSAGE. The function uses these input variables to parse a complete error message and pass back a status, depending upon the message and error type, back up through the consume function and up to the RIB.
All of the following functions exist within RMSSUB_FTERM.
RMSSUB_FTERM.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message_clob IN CLOB)
This procedure accepts a XML file in the form of an Oracle CLOB data type from the RIB (I_message) from the aforementioned public rmssub_frttermcre procedure whenever a message is made available by the RIB. This message will consist of the aforementioned record.
The procedure then validates the XML file format and, if successful, parses the values within the file through a series of calls to RIB_XML. The values extracted from the file will then be passed on to private internal functions, which will validate the values and place them on the appropriate freight term database table depending upon the success of the validation.
PARSE_FTERM
This function will used to extract the freight term level information from the Freight Term XML file and place that information onto an internal freight term record.
PROCESS_FTERM
After the values are parsed for a particular freight term record, RMSSUB_FTERM.CONSUME will call this function, which will in turn call various functions inside RMSSUB_FTERM in order to validate the values and place them on the appropriate FREIGHT_TERMS table depending upon the success of the validation.
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) |
|---|---|---|
| FrtTermCre | Freight Term Create Message | FrtTermDesc.xsd |
One of the primary assumptions in the current API 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 GL Chart of Accounts Subscription API.
Before RMS publishes stock ledger data to an external financial application, it must receive that application's General Ledger Chart Of Accounts (GLCOA) structure. RMS accomplishes this through a subscription process.
A chart of account is essentially the financial application's debit and credit account segments (for example, company, cost center, account, and others) that applies to RMS product hierarchy. In some financial applications, this is known as Code Combination IDs (CCID). On receiving the GLCOA message data, RMS populates the data to the FIF_GL_ACCT table. The GL cross-reference form is used to associate the appropriate department, class, subclass, and location data to a CCID that allows the population of that data to the GL_FIF_CROSS_REF table.
An external system publishes GL Chart of Accounts, thereby placing the GL chart of accounts information to RIB (Retail Integration Bus). RMS subscribes the GL chart of accounts information as published from the RIB and places the information in RMS tables depending upon the validity of the records enclosed within the message.
Subscribing to a GL chart of accounts message entails the use of one public consume procedure. This procedure corresponds to the type of activity that can be done to currency exchange rate record (in this case create/update).
Filename: rmssub_glcoacreb.pls
RMSSUB_ GLCOACRE.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN CLOB)
This procedure accepts a XML file in the form of an Oracle CLOB data type from the RIB (I_message). This message contains a GL chart of accounts message consisting of the aforementioned record. The procedure places a call to the main RMSSUB_GLCACCT.CONSUME function in order to validate the XML file format and, if successful, parse the values within the file through a series of calls to RIB_XML. The values extracted from the file is passed to private internal functions, which validates the values and place them on the GL chart of accounts table depending upon the success of the validation.
Error Handling:
If an error occurs in this procedure, a call is placed to HANDLE_ERRORS in order to parse a complete error message and pass back a status to the RIB.
HANDLE_ERRORS
(O_status IN OUT VARCHAR2,
IO_error_message IN OUT VARCHAR2,
I_cause IN VARCHAR2,
I_program IN VARCHAR2)
All error handling in the internal RMSSUB_GLCACCT package and all errors that occur during subscription in the RMSSUB_GLCOACRE package (and whatever packages it calls) flows through this function.
The function consists of a call to API_LIBRARY.HANDLE_ERRORS. API_LIBRARY.HANDLE_ERRORS accepts a program name, the cause of the error and potentially an unparsed error message if one has been created through a call to SQL_LIB.CREATE_MESSAGE. The function uses these input variables to parse a complete error message and pass back a status, depending upon the message and error type, back up through the consume function and up to the RIB.
Filename: rmssub_glcacctb.pls
Main Consume Function:
RMSSUB_GLCACCT.CONSUME
(O_ERROR_MESSAGE OUT VARCHAR2,
I_MESSAGE IN CLOB)
This procedure accepts a XML file in the form of an Oracle CLOB data type from the RIB (I_message) from the public rmssub_glcoacre.consume procedure whenever a message is available in RIB. This message consists of the aforementioned record.
The procedure validates the XML file format and if successful, parses the values within the file through a series of calls to RIB_XML. The values extracted from the file is passed to a private internal functions, which validates the value and places to a appropriate GL chart of accounts database table depending upon the success of the validation.
PARSE_HEADER
(O_ERROR_MESSAGE OUT VARCHAR2,
O_GLACCT_RECORD OUT GLACCT_RECTYPE,
I_GLACCT_ROOT IN OUT xmldom.DOMElement)
This function extracts the GL chart of accounts level information from the GL Chart of Accounts XML file and places the information to an internal GL Chart of Accounts record.
Record is based upon the record type glacct_rectype.
PROCESS_HEADER
After the values are parsed for a particular GL chart of accounts record, RMSSUB_GLCACCT.CONSUME calls this function, which in turn calls various functions inside RMSSUB_GLCACCT. In order to validate the values and place them on the appropriate GL chart of accounts table depending upon the success of the validation. PROCESS_GLACCT is called to insert or update the GL chart of accounts table.
PROCESS_GLACCT
Function PROCESS_GLACCT takes the input GL record and places the information to a local GL record which is used in the package to manipulate the data. It calls a series of support functions to perform all business logic on the record.
INSERT_GLACCT
Function INSERT_GLACCT inserts any valid account on the GL table. It is called from PROCESS_GLACCT.
UPDATE_GLACCT
Function UPDATE_GLACCT updates any valid account on the GL table. It is called from PROCESS_GLACCT.
VALIDATE_GLACCT
Function VALIDATE_GLACCT is a wrapper function which is used to call CHECK_NULLS, CHECK_ATTRS for any GL record input into the package.
CHECK_NULLS
Function CHECK_NULLS checks an input value if it is null. If so, an error message is created based on the passed in record type.
CHECK_ATTRS
Function CHK_ATTRS is called within the validation function of this package to ensure that RMS will not accept incomplete data from a financial interface when sent through RIB. This function checks to ensure that each description that is input also has an attribute that it describes.
The GL chart of accounts message is a flat message consists of a GL chart of accounts record.
The record contains information about the GL chart of accounts as a whole.
Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type to get detailed information of the composition of each message.
| Message Types | Message Type Description | XML Schema Definition (XSD) |
|---|---|---|
| Glcoacre | Glco Create Message | GLCOADesc.xsd |
This section describes the inventory adjustment subscription.
RMS receives requests for inventory adjustments from an integration subsystem through the inventory adjustment subscription. The requests contain information about the item, the stockholding location, the quantity, the specific disposition change, and the reason for the adjustment. RMS uses data in these requests to:
Adjust overall quantities of stock on hand for an item at a location
Adjust the availability of item-location quantities. For unavailable inventory adjustments, all quantity adjustment goes to the non-sellable bucket.
After initial processing from the integration subsystem RMS performs the following tasks:
Validates the item-location combinations and adjustment reasons
Updates stock on hand data for the item at the location
Inserts stock adjustment transaction codes on the RMS stock ledger
Adjusts quantities by inventory status for item/location combination
Create an audit trail for the inventory adjustment by item, location, inventory status and reason
RMS evaluates inventory adjustments to decide if overall item-location quantities have changed, or if the statuses of quantities have changed.
The FROM_DISPOSITION and TO_DISPOSITION tags in the message are evaluated to determine if there is a change in overall quantities of an item at a location. For the given item and quantity reported in the message, if either tag contains a null value, RMS evaluates that as a change in overall quantity in inventory.
In addition, if the message shows a change to the status of existing inventory, RMS evaluates this to determine if that change makes a quantity of an item unavailable.
Whenever the status or quantity of stock changes, RMS writes transaction codes to adjust inventory values in the stock ledger. The two types of inventory adjustment transaction codes are:
Adjustments to total stock on hand, where positive and negative adjustments are made to total stock on hand. In this case, a 'Stock Adjustment' transaction (TRAN_CODE = '22' or '23' if the cost of goods indicator associated with the inventory adjustment reason code is 'Y') is inserted on the Stock Ledger (TRAN_DATA table ) for both the retail and cost value of the adjustment
Adjustments to unavailable (non-sellable) inventory. In this case, an 'Unavailable Inventory Transfer' transaction (TRAN_CODE = '25') is inserted on the Stock Ledger (TRAN_DATA table).
This is a layer of code which enables decoupling of localization logic that is only required for certain country-specific configuration. This layer affects the RIB API flows including Inventory Adjustment subscription. This allows RMS to be installed without requiring customers to install or use this localization functionality, where not required.
Filename: rmssub_invadjusts/b.pls
This procedure will initially ensure that the passed in message type is a valid type for inventory adjustment messages. The valid message type for an inventory adjustment message is listed in a section below.
If the message type is invalid, a status of "E" should be returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT needs to be downcast to the actual object using Oracle's treat function.
There will be an object type that corresponds with each message type. If the downcast fails, a status of "E" is returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
RMSSUB_INVADJUST.CONSUME_INVADJ
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2,
I_check_l10n_ind IN VARCHAR2)
Perform localization check. If localized, invoke localization logic through L10N_SQL decoupling layer for procedure key 'CONSUME_INVADJ'. If not localized, call CONSUME_INVADJ for normal processing.
RMSSUB_INVADJUST.CONSUME_INVADJ
(O_error_message IN OUT VARCHAR2,
IO_L10N_RIB_REC IN OUT L10N_OBJ)
Public function to call RMSSUB_INVADJUST.CONSUME_INVADJUST_CORE.
RMSSUB_INVADJUST.CONSUME_INVADJ_CORE
(O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This function contains the main processing logic.
If the downcast is successful, then consume needs to verify that the message passes all of RMS's business validation. It calls the INVADJ_SQL function to perform validation and to insert or update records in the database when the message is valid. If the message passed RMS business validation and is successfully persisted in the database then a successful status is returned to the CONSUME. If the message fails RMS business validation or encounters any other errors, a status of "E" is returned to the external system along with the error message.
RMSSUB_INVADJUST.PROCESS_INVADJ
(O_error_message IN OUT VARCHAR2,
I_message IN "RIB_InvAdjustDesc_REC")
This function calls CHECK_ITEMS, an internal function that checks for any sellable only "break to sell" items and separates these items into an object table for further processing. A table of the corresponding orderable items and quantities for the sellable items is built to submit to the inventory adjustment process. INVADJ_SQL.PROCESS_INVADJ is called for the table of regular items and the table of "break to sell" items to perform all business validation and desired functionality associated with an inventory adjustment message.
Filename: invadjs/b.pls
INVADJ_SQL.BUILD_PROCESS_INVADJ
This function performs business validation and desired functionality for an inventory adjustment message. It includes the following:
Check required fields: item, location, adj_qty, user_id, adj_date.
Verify that the to_disposition or from_disposition or both fields are populated. Both cannot be NULL.
Verify that an orderable but non-sellable and non-inventory item cannot be an inventory adjustment item.
If the item is a simple pack catch weight item, verify that weight and weight UOM are either both defined or both NULL, and, if populated, that the weight UOM is in the MASS UOM class.
Verify that the item is a tran-level or a reference item. When a reference item is passed in, its parent item's inventory is adjusted.
Verify that the item/loc relation exists and create it if it does not exist.
If adjusting a pack at a warehouse, receive_as_type must be 'P' (pack) on ITEM_LOC.
Verify that from disposition and to disposition are valid inventory status codes (on INV_STATUS_CODES).
If the location is a warehouse, then physical location is on the message. The adjusted quantity is distributed among the virtual locations of the physical location.
For available stock on hand, the items are added to the update records for updating the ITEM_LOC_SOH table and a tran code 22 or 23 is prepared for writing the TRAN_DATA records. For external finisher location type and for transformable orderable items, the unit_retail is a calculated value, based on package calls for these two exception cases.
If cost of goods indicator of the inventory adjustment reason code is 'Y', use tran_code 23 instead of 22.
For unavailable stock on hand, the unavailable quantities are computed before the items or the pack components are added to the update records for updating the ITEM_LOC_SOH table and a tran code 25 data is prepared for writing the TRAN_DATA records. For external finisher location type and for transformable orderable items, the unit_retail is calculated with the appropriate package call for these two exception cases.
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) |
|---|---|---|
| invadjustcre | Inventory Adjustment Create Message | InvAdjustDesc.xsd |
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| ITEM_LOC_SOH | Yes | Yes | Yes | No |
| TRAN_DATA(VIEW) | No | Yes | No | No |
| INV_ADJ | No | Yes | No | No |
| INV_STATUS_QTY | No | Yes | Yes | Yes |
| INV_ADJ_REASON | Yes | No | No | No |
| V_PACKSKU_QTY | Yes | No | No | No |
| ITEM_LOC | Yes | No | No | No |
| ITEM_MASTER | Yes | No | No | No |
| INV_STATUS_CODES | Yes | No | No | No |
| TSFHEAD | Yes | No | No | No |
| SHIPSKU | Yes | No | No | No |
This section describes the inventory request subscription API.
RMS receives requests for inventory from an integration subsystem through the inventory request subscription.
Store ordering allows for all items to be ordered by the store and fulfilled by an RMS process. RMS fulfills a store's request regardless of replenishment review cycles, delivery dates, and any other factors that may restrict a request from being fulfilled. However, delivery cannot always be guaranteed on or before the store requested due date, due to supplier or warehouse lead times and other supply chain factors that may restrict on-time delivery.
Store ordering can be used to request inventory for any items that are on the 'Store Order' type of replenishment. The store order replenishment process requires the store to request a quantity and builds the recommended order quantity (ROQ) based on the store's requests. Requests for store order items that will not be reviewed prior to the date requested by the store are fulfilled through a one-off process (executed real-time through the API) that creates warehouse transfers and/or purchase orders to fulfill the requested quantities.
This API can also be used for items setup on other types of replenishment. In this case the store requested quantities will be added 'above and beyond' the calculated recommended order quantities. This API can also be used for items not setup on auto-replenishment. In this case the one-off process described above will be used to create a PO or transfer utilizing attributes defined for the item/location.
Filename: rmssub_invreqs/b.pls
RMSSUB_INVREQ.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2,
O_rib_error_tbl OUT RIB_ERROR_TBL)
This procedure initially downcasts the generic RIB_OBJECT to the actual object using the Oracle treat function.
If the downcast is successful, it will empty out the cache of inserts and updates to the store_orders table and to the PL/SQL ITEM_TBL table. This is done by calling INV_REQUEST_SQL.INIT function. Global variables to be used are initialized by the function RMSSUB_INVREQ_ERROR.INIT. This is called before processing any item/store order request.
Input from the header level info is then validated. If any of the required header level info is NULL, the entire request is rejected; however, there is no need to write to the error table.
Once the header level info has passed validation, RMSSUB_INVREQ_ERROR.BEGIN_INVREQ is called to hold the header level values into global variables which may be used to build an error record when necessary. Each item is processed by calling INV_REQUEST_SQL.PROCESS.
The cache for the STORE_ORDERS table and the PL/SQL ITEM_TBL table is populated by calling INV_REQUEST_SQL.FLUSH function. At the end of the inventory request process, the RMSSUB_INVREQ_ERROR.FINISH function is called to pass a copy of the global error table (if any error exists) which is sent to the RIB for further processing.
Filename: rmssub_invreq_errors/b.pls
Most of the functions included are called by the RMSSUB_INVREQ.CONSUME procedure to process inventory requests.
RMSSUB_INVREQ_ERROR.INIT
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message_type IN VARCHAR2)
This function initializes all of the global variables which include the RIB_OBJECTS that are used to process the inventory request.
RMSSUB_INVREQ_ERROR.BEGIN_INVREQ
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_request_id IN NUMBER,
I_store IN STORE_ORDERS.STORE%TYPE,
I_request_type IN VARCHAR2)
This function populates the global variables using the header level values to create an error record whenever necessary.
RMSSUB_INVREQ_ERROR.ADD_ERROR
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_error_desc IN VARCHAR2,
I_error_object IN RIB_OBJECT)
This function is called whenever an error is encountered during the processing of the inventory request. It adds the error type/description and error object on the global error table.
RMSSUB_INVREQ_ERROR.FINISH
This function is called after processing the inventory request. It passes out a copy of the global error table (if any error is present) to the RIB for further processing.
RMSSUB_INVREQ_ERROR.GET_MESSAGE_KEY
This function gets the key from a SQL_LIB error message. If the error message is just text without any parameters, the entire message is passed back out as the key.
Filename: invrequests/b.pls
INV_REQUEST_SQL.PROCESS
(O_error_message IN OUT VARCHAR2,
I_store IN STORE_ORDERS.STORE%TYPE,
I_request_type IN VARCHAR2,
I_item IN STORE_ORDERS.ITEM%TYPE,
I_need_qty IN STORE_ORDERS.NEED_QTY%TYPE,
I_uop IN UOM_CLASS.UOM%TYPE,
I_need_date IN STORE_ORDERS.NEED_DATE%TYPE)
This function does all the validation and processing of the inventory request. It creates a record for STORE_ORDERS or LP_ITEM_TBL (PL/SQL table for adhoc requests).
INV_REQUEST_SQL.VERIFY_REPL_INFO (local)
This function retrieves the replenishment information. If the request type is 'IR' and the item is not set up on replenishment, set adhoc to 'Y'. Item requests with request type of 'SO' or NULL must have store order replenishment set up in RMS for that item. The need date must be after the next replenishment delivery date if the store order has been rejected by replenishment. If the need date is before the next replenishment review date for both request types, set adhoc to 'Y'.
INV_REQUEST_SQL.FUNCTION CONVERT_NEED_QTY (local)
This function converts the need quantity to 'E'aches for Packs.
INV_REQUEST_SQL.PREPARE_AD_HOC (local)
This function is called if the Adhoc indicator is set to 'Y'. It writes the request to the PL/SQL table that will be passed to the function call CREATE_ORD_TSF_SQL.CREATE_ORD_TSF to create an order or transfer.
INV_REQUEST_SQL.VERIFY_ON_STORE (local)
This function checks to see if the item request already exists on STORE_ORDER. If it exists, call PREPARE_UPDATE to update the need quantity to include the new need quantity. If it does not, call PREPARE_INSERT to insert into STORE_ORDER table.
INV_REQUEST_SQL. PREPARE_INSERT (local)
This function checks the PL/SQL table that contains the BULK INSERT records. If a record exists on the PL/SQL table, update the qty.
INV_REQUEST_SQL. PREPARE_UPDATE (local)
This function adds a record to the PL/SQL table that contains the BULK UPDATE records.
INV_REQUEST_SQL. FLUSH (local)
This function does the actual insert or update to STORE_ORDERS.
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) |
|---|---|---|
| InvReqCre | Inventory Request Create Message | InvReqDesc.xsd |
RMS will round quantities using the store order multiple when an order is created for a warehouse.
Up charges will always be applied to a transfer when they can be defaulted.
RMS will validate that all items belong to the same department when department level ordering (supplier) or department level transfers (warehouse) are being used.
RMS will validate that an item is not a consignment item if the order is for a warehouse.
RMS will validate that a store is open when the store is being transferred to.
This API supports non-fatal error processing. If an error is encountered in one inventory request detail, it will log and return the error to the RIB via RIB_ERROR_TBL and continue processing the next detail.
This section describes the item subscription API.
When this API accepts messages with create message types, it inserts the data into the staging tables, SVC_ITEM_MASTER, SVC_PACKITEM (in the case of a pack), SVC_ITEM_SUPPLIER, SVC_ITEM_COUNTRY, SVC_ITEM_SUPP_COUNTRY, SVC_ITEM_SUPP_COUNTRY_DIM, SVC_ITEM_SUPP_MANU_COUNTRY, SVC_UDA_ITEM_LOV, SVC_UDA_ITEM_FF, SVC_UDA_ITEM_DATE, SVC_ITEM_IMAGE, SVC_ITEM_MASTER_TL, SVC_ITEM_SUPPLIER_TL SVC_ITEM_IMAGE_TL, SVC_ITEM_HTS, SVC_ITEM_HTS_ASSESS, SVC_ITEM_SEASONS, SVC_ITEM_MASTER_CFA_EXT and SVC_ITEM_SUPPLIER_CFA_EXT.
The SVC_VAT_ITEM table is populated with data defaulted from the item's department. Optionally, the records can be inserted into the SVC_VAT_ITEM table to override these defaults. The messages with modify message types consist of snapshots of records for updating the ITEM_MASTER, ITEM_SUPPLIER, ITEM_SUPP_COUNTRY, ITEM_SUPP_COUNTRY_DIM, ITEM_SUPP_MANU_COUNTRY ,ITEM_IMAGE, ITEM_MASTER_TL, ITEM_SUPPLIER_TL ITEM_IMAGE_TL, ITEM_HTS, ITEM_HTS_ASSESS and ITEM_IMAGE_TLSEASONS tables after being processed from the corresponding staging tables.
Item messages include the required detail nodes for the supplier and supplier/country. If the item is not a non-sellable pack, the item/zone/price node is also required. Optional nodes can be included in the message for supplier/country, pack components, and item/vat relationships.
The RIB_XItemDesc_REC message includes the RIB_CustFlexAttriVo_TBL message to enable the subscription of the custom flex attributes.
Items must be created and maintained following a logical hierarchy as outlined by the referential integrity of the item database tables: Item parents before child items; item components before items that are packs; items before item-suppliers; item/suppliers before item/supplier/countries; items before item/locations (a separate API), and so on. Failing to do so results in message failure.
The create and modify messages are hierarchical with required detail nodes of suppliers and supplier/countries and optional nodes for price zones, supplier/country and vat codes. If the item is a pack item, the pack component node is required.
In the header modify message, the detail nodes are not populated, but the full header node is sent. The detail level create or modify messages contains the item header record and one to many detail records in the node or nodes. For example, the message type of XItemSupMod could have one or more supplier details to update in the ITEM_SUPPLIER table. The modify messages contain a snapshot of the record for update rather than only the fields to be changed.
The auto-creation of item children using differentiator records attached to an item parent, as currently occurs using RMS online processes, is not supported in this API.
The delete messages contain only the primary key field for the item, supplier, supplier/country or vat/item record that is to be deleted. When a delete message is processed, the item is not immediately deleted; rather, it is added to the daily purge table. Deleting the item is a batch process.
A major functionality that was added to RMS is the support of Brazil Localization. This introduced a layer of code to enable decoupling of localization logic that is only required for country-specific configuration. This layer affects the RIB API flows including the Xitem subscription.
Oracle Retail Fiscal Management (ORFM) is designed as an add-on product to RMS to handle Brazil-specific fiscal management.
Even though ORFM and RMS exist in the same database schema and ORFM cannot be installed separately without RMS, Oracle Retail ensures that RMS is decoupled from ORFM. This is so that non-Brazilian clients can install RMS without RFM. To achieve that, an L10N decoupling layer was introduced.
In the context of the XItem subscription API, when RMS consumes an XItem message from an external system, if the message involves a localized country, the message must be routed to a third party application (for example, Mastersaf) to calculate tax and/or to ORFM for the setting up of fiscal item attributes. In that case, RMS's XItem subscription API (rmssub_xitem and related packages) call Mastersaf and/or ORFM through an L10N decoupling layer.
XItem API supports the importing of Brazil fiscal item attributes to RMS through the 'xitemctrycre' (create item country) messsage type. The client must populate the "RIB_BrXItemCtryDesc_TBL" node in the XItemDesc message family. The XItem API writes data to the ITEM_COUNTRY_L10N_EXT table based on the meta-data definition of the 'ITEM_COUNTRY' entity.
The structure of the XItemDesc message family is the following:
"RIB_XItemDesc_REC"
-- XItemCtryDesc_TBL "RIB_XItemCtryDesc_TBL"
-- LocOfXItemCtryDesc_TBL "RIB_LocOfXItemCtryDesc_TBL"
-- BrXItemCtryDesc_TBL "RIB_BrXItemCtryDesc_TBL"
This is where client should populate the Brazilian fiscal item attributes.
Supported fiscal item attributes include:
SERVICE_IND
ORIGIN_CODE
CLASSIFICATION_ID
NCM_CHAR_CODE
EX_IPI
PAUTA_CODE
SERVICE_CODE
FEDERAL_SERVICE
STATE_OF_MANUFACTURE
PHARMA_LIST_TYPE
When the message is persisted to the database, if the message type is 'xitemctrycre' (that is, create Item Country), then the above Brazilian fiscal item attributes are imported to the corresponding extension table of ITEM_COUNTRY_L10N_EXT through an L10N localization layer.
Support of translation within the ITEM_MASTER, ITEM_SUPPLIER and ITEM_IMAGE tables.
The XItem API contains additional nodes to support translation of certain information into one or more languages via the following message types:
Xitemtlcre
Xitemtlmod
Xitemtldel
Xitemsuptlcre
Xitemsuptlmod
Xitemsuptldel
Xitemimagetlcre
Xitemimagetlmod
Xitemimagetldel
The following nodes need to be populated in the XItemDesc/XItemRef message family to populate the item_master_tl, item_supplier_tl and item_image_tl tables:
RIB_LangOfXItemImage_TBL / RIB_LangOfXItemImageRef_TBL
RIB_LangOfXItemSupDesc_TBL / RIB_LangOfXItemSupRef_TBL
RIB_LangOfXItemDesc_TBL / RIB_LangOfXItemRef_TBL
In addition to RIB, RMS also exposes an Item Management web service to allow an external application to create, update, and reclassify items in RMS. The web service takes in a collection of items (except for reclass item) and will return success and failure through the service response object.
This section describes the package impact.
Filename: ItemManagementServiceProviderImplSpec.pls ItemManagementServiceProviderImplBody.pls
For a web service deployment, the Item Management service with supported operations is available for an external system to send Item requests to RMS. Each supported operation will invoke the public interfaces in the ItemManagementServiceProviderImpl package as follows:
createItem
createSupplier
createSupplierCountry
modifyItem
reclassItem
These public interfaces will call the corresponding procedures in svcprov_xitem, which will in turn call rmssub_itemsxitem.consume to do the major processing logic.
Filename: svcprov_xitems/b.pls
Procedures called from Item Management web service public interfaces in the ItemManagementServiceProviderImpl package to perform major processing.
If an error occurs, it calls SVCPROV_UTLITY.BUILD_SERVICE_OP_STATUS to build and return RIB_ServiceOpStatus_REC with a failure message; if there are no errors, it builds and returns RIB_InvocationSuccess_REC with a success message.
Filename: rmssub_items/b.pls
RMSSUB_XITEM.CONSUME (O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure will need to initially ensure that the passed in message type is a valid type for organizational hierarchy messages. The valid message types for organizational hierarchy messages are listed in a section below.
If the message type is invalid, a status of "E" should be returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT needs to be downcast to the actual object using the Oracle's treat function. There will be an object type that corresponds with each message type. If the downcast fails, a status of "E" is returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume calls the RMSSUB_XITEM_POP_RECORD.POPULATE function to populate all the fields in the item collections. It is then persisted to the RMS database via RMSSUB_XITEM_SQL.PERSIST function where contents of the collections are inserted into the staging tables in preparation for the upload into the RMS item tables via the Item Induction package. A record is inserted into svc_process_tracker with template_type = 'XITEM' and process_source = 'EXT' (external). A parameter called attempt_rms_load which determines the final destination of the XItem messages is also populated. It can either be 'RMS' (default), which indicates that the message will be uploaded to the RMS item tables, or 'STG' which means that the message will only be inserted into the RMS staging tables for further enrichment, without data validation. Loading of records from staging to RMS will be performed via the induction process.
Once a record is successfully inserted into svc_process_tracker, and the attempt_rms_load parameter is set to 'RMS', the ITEM_INDUCT_SQL.EXEC_ASYNC function calls the CORESVC_ITEM.PROCESS function to perform the bulk of the validations and persistence from staging into the RMS tables.
The function contains validations that exist in item creation via the UI and via item induction, which the XItem messages will be subject to. After having passed the data level validations, the items will be inserted into the main RMS item tables.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success status, "S", is returned to the external system indicating that the message has been successfully received and persisted to the staging tables in the RMS database
If the database persistence fails, the function returns false. A status of "E" is returned to the external system along with the error message returned from the PERSIST function.
RMSSUB_ITEM.HANDLE_ERROR () - This is the standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
All insert, update and delete SQL statements are located in the family packages. The private functions call these packages.
Filename: rmssub_xitemsqls/b.pls
RMSSUB_XITEM_SQL.PERSIST
(O_error_message IN OUT VARCHAR2,
I_message_type IN VARCHAR2,
I_message IN RIB_XItemDesc,
I_item_rec IN RMSSUB_ITEM.ITEM_API_REC)
This function checks the message type to route the object to the appropriate internal functions that perform DML insert and update processes on staging tables.
ITEM CREATE
Inserts a record in the SVC_ITEM_MASTER table
Calls all the "insert" functions to insert records into the following tables:
SVC_ITEM_COUNTRY
SVC_ITEM_SUPPLIER
SVC_ITEM_SUPP_COUNTRY
SVC_ITEM_SUPP_MANU_COUNTRY
SVC_PACKITEM (optional)
SVC_VAT_ITEM (optional)
SVC_UDA_ITEM_FF(optional)
SVC_UDA_ITEM_LOV(optional)
SVC_UDA_ITEM_DATE(optional)
SVC_ITEM_IMAGE(optional)
SVC_ITEM_MASTER_TL(optional)
SVC_ITEM_SUPPLIER_TL(optional)
SVC_ITEM_IMAGE_TL(optional)
SVC_ITEM_HTS (optional)
SVC_ITEM_HTS_ASSESS (optional)
SVC_ITEM_SEASONS (optional)
ITEM MODIFY
Inserts a record in SVC_ITEM_MASTER. It will be used to update the ITEM_MASTER table.
ITEM DELETE
Inserts a record in the SVC_ITEM_MASTER. The record will be processed and inserted into the DAILY_PURGE table.
ITEM COUNTRY CREATE
Inserts records in SVC_ITEM_COUNTRY. It will be used to insert records into the ITEM_COUNTRY table.
For Brazil, the records in SVC_ITEM_COUNTRY will be used to update the ITEM_COUNTRY_L10N_EXT table through L10N decoupling layer (L10N_FLEX_API_SQL.PERSIST_L10N_ATTRIB)
ITEM_COUNTRY DELETE
Inserts record in the SVC_ITEM_COUNTRY table. This will be used to delete records in the ITEM_COUNTRY table and ITEM_COUNTRY_L10N_EXT table.
ITEM_SUPPLIER CREATE
Inserts records in the SVC_ITEM_SUPPLIER table. This will be used to insert records in ITEM_SUPPLIER.
ITEM_SUPPLIER MODIFY
Inserts records in the SVC_ITEM_SUPPLIER table. This will be used to modify the ITEM_SUPPLIER table.
ITEM_SUPPLIER DELETE
Inserts records in the SVC_ITEM_SUPPLIER table for item. This will be used to delete from the ITEM_SUPPLIER table.
ITEM_SUPP_COUNTRY CREATE
Inserts records in SVC_ITEM_SUPP_COUNTRY. This will be used to insert into the ITEM_SUPP_COUNTRY table
ITEM_SUPP_COUNTRY MODIFY
Inserts records in the SVC_ITEM_SUPP_COUNTRY table. This will be used to update the ITEM_SUPP_COUNTRY table.
ITEM_SUPP_COUNTRY DELETE
Inserts records in the SVC_ITEM_SUPP_COUNTRY table. This will be used to delete records from the ITEM_SUPP_COUNTRY table.
ITEM_SUPP_MANU_COUNTRY CREATE
Inserts records in the SVC_ITEM_SUPP_MANU_COUNTRY table. This will be used to insert into the ITEM_SUPP_MANU_COUNTRY table.
ITEM_SUPP_MANU_COUNTRY MODIFY
Inserts records in the SVC_ITEM_SUPP_MANU_COUNTRY table. This will be used to update the ITEM_SUPP_MANU_COUNTRY table.
ITEM_SUPP_MANU_COUNTRY DELETE
Inserts records in the SVC_ITEM_SUPP_MANU_COUNTRY table. This will be used to delete from the ITEM_SUPP_MANU_COUNTRY table.
ITEM_SUPP_COUNTRY_DIM CREATE
Inserts records in the SVC_ITEM_SUPP_COUNTRY_DIM table. This will be used to insert into the ITEM_SUPP_COUNTRY_DIM table.
ITEM_SUPP_COUNTRY_DIM MODIFY
Inserts records in the SVC_ITEM_SUPP_COUNTRY_DIM table. This will be used to update the ITEM_SUPP_COUNTRY_DIM table.
ITEM_SUPP_COUNTRY_DIM DELETE
Inserts records in the SVC_ITEM_SUPP_COUNTRY_DIM table. This will be used to delete records from the ITEM_SUPP_COUNTRY_DIM table.
PACKITEM CREATE
Inserts records in the SVC_PACKITEM table. Records from the staging table will be used to insert into PACKITEM and SVC_PACKITEM AND update ITEM_SUPP_COUNTRY_LOC and/or ITEM_SUPP_COUNTRY with calculated unit_cost.
VAT_ITEM CREATE
Inserts records in the SVC_VAT_ITEM table. The records will then be inserted into VAT_ITEM or replace any default records that were created from department/VAT.
VAT_ITEM DELETE
Inserts records in the SVC_VAT_ITEM table. The records will be used to delete from VAT_ITEM.
ITEM_UDA CREATE
Inserts records into the SVC_UDA_ITEM_DATE, SVC_UDA_ITEM_LOV and SVC_UDA_ITEM_FF tables. The records will then be inserted into the corresponding RMS base tables.
ITEM_UDA MODIFY
Inserts records into the SVC_UDA_ITEM_DATE, SVC_UDA_ITEM_LOV and SVC_UDA_ITEM_FF tables. The records will then be used to update records in the corresponding RMS base tables.
ITEM_UDA DELETE
Inserts records into the SVC_UDA_ITEM_DATE, SVC_UDA_ITEM_LOV and SVC_UDA_ITEM_FF tables. The records will then be used to update records from the corresponding RMS base tables.
ITEM_IMAGE CREATE
Inserts records into the SVC_ITEM_IMAGE table. The records will then be inserted into the corresponding RMS base table.
ITEM_IMAGE MODIFY
Inserts records into the SVC_ITEM_IMAGE table. The records will then be used to update records in the corresponding RMS base table.
ITEM_IMAGE DELETE
Inserts records into the SVC_ITEM_IMAGE table. The records will then be used to delete records from the corresponding RMS base table.ITEM_MASTER_TL CREATE
Inserts records into the SVC_ITEM_MASTER_TL table. The records will then be used to insert records to the corresponding RMS base table.
ITEM_MASTER_TL MODIFY
Inserts records into the SVC_ITEM_MASTER_TL table. The records will then be used to update records in the corresponding RMS base table.
ITEM_MASTER_TL DELETE
Inserts records into the SVC_ITEM_MASTER_TL table. The records will then be used to delete records from the corresponding RMS base table.
ITEM_SUPPLIER_TL CREATE
Inserts records into the SVC_ITEM_SUPPLIER_TL table. The records will then be used to insert records in the corresponding RMS base table.
ITEM_SUPPLIER_TL MODIFY
Inserts records into the SVC_ITEM_SUPPLIER_TL table. The records will then be used to update records in the corresponding RMS base table.
ITEM_SUPPLIER_TL DELETE
Inserts records into the SVC_ITEM_SUPPLIER_TL table. The records will then be used to delete records from the corresponding RMS base table.
ITEM_IMAGE_TL CREATE
Inserts records into the SVC_ITEM_IMAGE_TL table. The records will then be used to insert records in the corresponding RMS base table.
ITEM_IMAGE_TL MODIFY
Inserts records into the SVC_ITEM_IMAGE_TL table. The records will then be used to update records in the corresponding RMS base table.
ITEM_IMAGE_TL DELETE
Inserts records into the SVC_ITEM_IMAGE_TL table. The records will then be used to delete records from the corresponding RMS base table.
ITEM_HTS CREATE
Inserts records into the SVC_ITEM_HTS table. The records will then be used to create records in the corresponding RMS base table.
ITEM_HTS MODIFY
Inserts records into the SVC_ITEM_HTS table. The records will then be used to update records in the corresponding RMS base table.
ITEM_HTS DELETE
Inserts records into the SVC_ITEM_HTS table. The records will then be used to delete records from the corresponding RMS base table.
ITEM_HTS_ASSESS CREATE
Inserts records into the SVC_ITEM_HTS_ASSESS table. The records will then be used to create records in the corresponding RMS base table.
ITEM_HTS_ASSESS MODIFY
Inserts records into the SVC_ITEM_ASSESS table. The records will then be used to update records in the corresponding RMS base table.
ITEM_HTS_ASSESS DELETE
Inserts records into the SVC_ITEM_ASSESS table. The records will then be used to delete records from the corresponding RMS base table.
ITEM_SEASONS CREATE
Inserts records into the SVC_ITEM_SEASONS table. The records will then be used to create records in the corresponding RMS base table.
ITEM_SEASONS DELETE
Inserts records into the SVC_ITEM_SEASONS table. The records will then be used to delete records from the corresponding RMS base table.
Below are the filenames that correspond with each message type. 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) |
|---|---|---|
| createItem | Create Item Service Operation | XItemDesc.xsd |
| createSupplier | Create Item Supplier Service Operation | XItemDesc.xsd |
| createSupplierCountry | Create Item Supplier Country Service Operation | XItemDesc.xsd |
| modifyItem | Modify Item Service Operation | XItemDesc.xsd |
| reclassItem | Reclassify Item Service Operation | XItemRclsDesc.xsd |
| XItemCre | Item Create Message | XItemDesc.xsd |
| XItemMod | Item Modify Message | XItemDesc.xsd |
| XItemDel | Item Delete Message | XItemRef.xsd |
| XItemSupCre | Item/Supplier Create Message | XItemDesc.xsd |
| XItemSupMod | Item/Supplier Modify Message | XItemDesc.xsd |
| XItemSupDel | Item/Supplier Delete Message | XItemRef.xsd |
| XItemSupCtyCre | Item/Supplier/Country Create Message | XItemDesc.xsd |
| XItemSupCtyMod | Item/Supplier/Country Modify Message | XItemDesc.xsd |
| XItemSupCtyDel | Item/Supplier/Country Delete Message | XItemRef.xsd |
| XISCMfrCre | Item/Supplier/Country of Manufacture Create Message | XItemDesc.xsd |
| XISCMfrMod | Item/Supplier/ Country of Manufacture Modify Message | XItemDesc.xsd |
| XISCMfrDel | Item/Supplier/ Country of Manufacture Delete Message | XItemRef.xsd |
| XISCDimCre | Item/Supplier/Country/Dimension Create Message | XItemDesc.xsd |
| XISCDimMod | Item/Supplier/Country/Dimension Modify Message | XItemDesc.xsd |
| XISCDimDel | Item/Supplier/Country/Dimension Delete Message | XItemRef.xsd |
| XItemVatCre | Item/Vat Create Message | XItemDesc.xsd |
| XItemVatDel | Item/Vat Delete Message | XItemRef.xsd |
| XitemCtryCre | Item/Country Create Message | XItemCtryDesc.xsd |
| XitemCtryDel | Item/Country Delete Message | XItemCtryRef.xsd |
| XitemUdaCre | Item/UDA Create Message | XItemDesc.xsd |
| XitemUdaDel | Item/UDA Delete Message | XItemRef.xsd |
| XitemImageCre | Item/Image Create Message | XItemDesc.xsd |
| XitemImageMod | Item/Image Modify Message | XItemDesc.xsd |
| XitemImageDel | Item/Image Delete Message | XItemRef.xsd |
| XitemTLCre | Item Master translated language Create Message | XItemDesc.xsd |
| XitemTLMod | Item Master translated language Modify Message | XItemDesc.xsd |
| XitemTLDel | Item Master translated language Delete Message | XItemRef.xsd |
| XitemSupTLCre | Item/Supplier translated language Create Message | XItemSupDesc.xsd |
| XitemSupTLMod | Item/Supplier translated language Modify Message | XItemSupDesc.xsd |
| XitemSupTLDel | Item/Supplier translated language Delete Message | XItemSupRef.xsd |
| XitemImageTLCre | Item/Image translated language Create Message | XItemImageDesc.xsd |
| XitemImageTLMod | Item/Image translated language Modify Message | XItemImageDesc.xsd |
| XitemImageTLDel | Item/Image translated language Delete Message | XItemImageRef.xsd |
| XItemHTSCre | Item/HTS create message | XItemDesc.xsd |
| XItemHTSMod | Item/HTS modify message | XItemDesc.xsd |
| XItemHTSDel | Item/HTS delete message | XItemRef.xsd |
| XItemHTSAssessCre | Item/HTS assess create message | XItemDesc.xsd |
| XItemHTSAssessMod | Item/HTS assess modify message | XItemDesc.xsd |
| XItemHTSAssessDel | Item/HTS assess delete message | XItemRef.xsd |
| XItemSeasonCre | Item/Seasons create message | XItemDesc.xsd |
| XItemSeasonDel | Item/Seasons delete message | XItemRef.xsd |
Item/Supplier/Country/Location relationships are not addressed by this API.
Item/location relationships are not addressed by this API; they are addressed in a separate Item Location Subscription API.
Oracle Retail Price Management (RPM_ is called to set the initial pricing for the item. This populates tables in the RPM system.
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| SVC_ITEM_MASTER | ||||
| SVC_ITEM_SUPPLIER | Yes | Yes | Yes | No |
| SVC_ITEM_SUPP_COUNTRY | Yes | Yes | Yes | No |
| SVC_ITEM_SUPP_MANU_COUNTRY | Yes | Yes | Yes | No |
| SVC_ITEM_SUPP_COUNTRY_DIM | Yes | Yes | Yes | No |
| SVC_PACKITEM | Yes | Yes | Yes | No |
| SVC_VAT_ITEM | Yes | Yes | Yes | No |
| SYSTEM_OPTIONS | Yes | No | No | No |
| SVC_ITEM_COUNTRY | Yes | Yes | No | No |
| SVC_UDA_ITEM_DATE | Yes | Yes | Yes | Yes |
| SVC_UDA_ITEM_FF | Yes | Yes | Yes | Yes |
| SVC_UDA_ITEM_LOV | Yes | Yes | Yes | Yes |
| SVC_ITEM_IMAGE | Yes | Yes | Yes | Yes |
| SVC_ITEM_MASTER_TL | Yes | Yes | Yes | Yes |
| SVC_ITEM_SUPPLIER_TL | Yes | Yes | Yes | Yes |
| SVC_ITEM_HTS | Yes | Yes | Yes | Yes |
| SVC_ITEM_HTS_ASSESS | Yes | Yes | Yes | Yes |
| SVC_ITEM_SEASONS | Yes | Yes | Yes | Yes |
| SVC_ITEM_IMAGE_TL | Yes | Yes | Yes | Yes |
| SVC_PROCESS_TRACKER | Yes | Yes | No | No |
| ITEM_MASTER | Yes | Yes | Yes | No |
| ITEM_SUPPLIER | Yes | Yes | Yes | Yes |
| ITEM_SUPP_COUNTRY | Yes | Yes | Yes | Yes |
| ITEM_SUPP_MANU_COUNTRY | Yes | Yes | Yes | Yes |
| ITEM_SUPP_COUNTRY_DIM | Yes | Yes | Yes | Yes |
| PACKITEM | Yes | Yes | Yes | Yes |
| PACKITEM_BREAKOUT | Yes | Yes | Yes | Yes |
| VAT_ITEM | Yes | Yes | Yes | Yes |
| ITEM_COUNTRY | Yes | Yes | Yes | Yes |
| UDA_ITEM_DATE | Yes | Yes | Yes | Yes |
| UDA_ITEM_FF | Yes | Yes | Yes | Yes |
| UDA_ITEM_LOV | Yes | Yes | Yes | Yes |
| ITEM_IMAGE | Yes | Yes | Yes | Yes |
| ITEM_MASTER_TL | Yes | Yes | Yes | Yes |
| ITEM_SUPPLIER_TL | Yes | Yes | Yes | Yes |
| ITEM_IMAGE_TL | Yes | Yes | Yes | Yes |
| ITEM_HTS | Yes | Yes | Yes | Yes |
| ITEM_HTS_ASSESS | Yes | Yes | Yes | Yes |
| ITEM_SEASONS | Yes | Yes | Yes | Yes |
| ITEM_SUPPLIER_CFA_EXT | No | Yes | No | No |
| ITEM_MASTER_CFA_EXT | No | Yes | No | No |
| ITEM_SUPP_COUNTRY_CFA_EXT | No | Yes | No | No |
This section describes the item location subscription API.
Item locations can be maintained at the following levels of the organization hierarchy: chain, area, region, district, and store. Records are maintained for all stores within the location group. Because warehouses are not part of the organization hierarchy, they are only impacted by records maintained at the warehouse level. If building item-locations by organizational hierarchy, only locations in the hierarchy that do not already exist on item-location will be built.
Item locations can only be created for a single item. However, levels of the organization hierarchy are available for maintenance in order to facilitate location-level processing into RMS. The detail node is required for both create and modify messages.
Item supplier country locations will be created for the passed-in primary supplier/country if they do not already exist. If primary supplier/country locations are not passed in, then they will default from the item's primary supplier/country and a location will be created, if it does not already exist.
Item locations are required to be interfaced into RMS in active status. There is no delete function in this API. Instead, item locations can be put into inactive, discontinued, or deleted status. However, they will be deleted if the associated item is purged. If building item-locations by store or warehouse, then each passed-in location must not already exist as an item-location.
A major functionality added to RMS is the support of Brazil Localization. This introduced a layer of code to enable decoupling of localization logic that is only required for country-specific configuration. This layer affects the RIB API flows including XItemLoc subscription.
The RIB_XItemLocDtl_REC message is modified to include RIB_CustFlexAttriVo_TBL message to enable the subscription of the custom flex attributes.
Oracle Fiscal Management (ORFM) was designed as an add-on product to RMS to handle Brazil-specific fiscal management. Even though RFM and RMS exist in the same database schema and RFM cannot be installed separately without RMS, Oracle Retail ensures that RMS is decoupled from RFM. This is so that non-Brazilian clients can install RMS without RFM. To achieve that, an L10N decoupling layer was introduced.
In the context of XITEMLOC subscription API, when RMS consumes an XITEMLOC message from an external system, the message must be routed to a third party tax application (for example, Mastersaf) for tax calculation if the message involves ranging an item to a new Brazilian location. In that case, RMS's XItemLoc subscription API (rmssub_xitemloc and related packages) will call Mastersaf through an L10N de-coupling layer.
This section describes the package impact.
Filename: rmssub_xitemlocs/b.pls
RMSSUB_XITEMLOC.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure needs to initially ensure that the passed in message type is a valid type for item location messages. If the message type is invalid, a status of "E" should be returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT needs to be downcast to the actual object using the Oracle's treat function. If the downcast fails, a status of "E" should be returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume needs to verify that the message passes all of RMS's business validation. It calls the RMSSUB_XITEMLOC_VALIDATE.CHECK_MESSAGE function to determine whether the message is valid. If the message passed RMS business validation, then the function will return true; otherwise, it will return false. If the message has failed RMS business validation, a status of "E" should be returned to the external system along with the error message returned from the CHECK_MESSAGE function.
The package RMSSUB_XITEMLOC_CFA enables the subscription of the custom flex attributes. RMSSUB_XITEMLOC_CFA.CONSUME is called to process the custom flex attributes.
Once the message has passed RMS business validation, it can be persisted to the RMS database. It calls the RMSSUB_XITEMLOC_SQL.PERSIST_MESSAGE() function. If the database persistence fails, the function returns false. A status of "E" should be returned to the external system along with the error message returned from the PERSIST_MESSAGE() function.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success status, "S", should be returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
RMSSUB_XITEMLOC.HANDLE_ERROR() - This is the standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
Also detail RIB object RIB_XItemLocDtl_REC is modified to support Store serialization.
It should be noted that some of the business validation is referential or involves uniqueness. This validation is handled automatically by the referential integrity constraints and the unique indexes implemented on the database.
Filename: rmssub_xitemlocvals/b.pls
RMSSUB_XITEMLOC_VALIDATE.CHECK_MESSAGE
(O_error_message IN OUT VARCHAR2,
O_ITEMLOC_rec OUT ITEMLOC_REC,
I_message IN RIB_XItemLocDesc,
I_message_type IN VARCHAR2)
This function performs all business validation associated with message and builds the item locations record for persistence.
ITEMLOC CREATE
Check required fields
Verify primary supplier/country exists on Item-supplier-country
If creating locations by store or warehouse, verify passed in locations do not currently exist.
If item is a buyer pack, verify receive as type is valid based on item's order as type.
Default required fields not provided (store order multiple, taxable indicator, local item description, primary supplier/country, receive as type).
Build item-location records.
Build price history records.
ITEMLOC MODIFY
Check required fields
Populate item-location record.
Filename: rmssub_xitemlocsqls/b.pls
RMSSUB_XITEMLOC_SQL.PERSIST
(O_error_message IN OUT VARCHAR2,
I_dml_rec IN ITEMLOC_RECTYPE ,
I_message IN RIB_XITEMLOCDesc)
ITEMLOC CREATE
Insert a record into the item-location table.
Insert a record into the item-location-stock on hand table
If necessary, insert a record into the item supplier country location table.
Insert a record into the price history table.
ITEMLOC MODIFY
Update item-location table.
Below are the filenames that correspond with each message type. Consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.
| Message Type | Message Type Description | XML Schema Definition (XSD) |
|---|---|---|
| xitemloccre | External item locations create | XItemlocDesc.xsd |
| xitemlocMod | External item locations odification | XItemlocDesc.xsd |
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| ITEM_SUPP_COUNTRY | Yes | No | No | No |
| ITEM_SUPP_COUNTRY_LOC | Yes | Yes | No | No |
| ITEM_LOC_SOH | Yes | Yes | Yes | No |
| STORE | Yes | No | No | No |
| WH | Yes | No | No | No |
| ITEM_LOC | Yes | Yes | Yes | No |
| SYSTEM_OPTIONS | Yes | No | No | No |
| PRICE_HIST | No | Yes | No | No |
| ITEM_MASTER | Yes | No | No | No |
| PACKITEM_BREAKOUT | Yes | No | No | No |
| CHAIN | Yes | No | No | No |
| AREA | Yes | No | No | No |
| REGION | Yes | No | No | No |
| DISTRICT | Yes | No | No | No |
| PACKITEM | Yes | No | No | No |
| RPM_ITEM_ZONE_PRICE | Yes | No | No | No |
| CURRENCIES | Yes | No | No | No |
| ELC_TABLES | Yes | No | No | No |
| VAT_ITEM | Yes | No | No | No |
| PARTNER | Yes | No | No | No |
| ITEM_LOC_CFA_EXT | No | Yes | No | No |
This section describes the item reclassification subscription API.
RMS subscribes to item reclassification messages that are published by an external system. This subscription is necessary in order to keep RMS in sync with the external system. The retailer can view the pending reclassifications online in RMS.
This API allows external systems to create and delete item reclassification events within RMS.
At least one detail must be passed for a valid reclassification message. Reclassification items can be created or deleted within the reclassification message. Reclass item creates will send a snapshot of the reclass event. However, reclass item deletes do not require any header information as items are unique for reclassification and items may be deleted across reclass events.
Only level one items can be interfaced via this API. If the item is a pack, only non-simple packs can be interfaced. Simple pack items will be reclassified when their component is reclassified.
During the reclassification batch process, it will determine if any pack items exist in RMS that contain the items or any of that item's children being reclassified.
If such a pack exists and contains no other items, the batch process adds the pack to the reclassification event being created in RMS.
It is valid for a reclassification event to be created for a department/class/subclass not yet existing but planning to exist. This is valid as long as they department/class/subclass is scheduled to be created on or prior to the reclassification taking effect.
Deleting reclassifications can either occur by:
Items on a reclass event or across events.
A single reclassification event.
All reclassification events on a particular event date (deletion through the use of the reclass_date may result in the deletion of numerous reclass events).
All reclassification events.
Deleting a reclassification header will require either a reclass no, reclass date, or purge all ind.
This section describes the bulk or single DML module.
Filename: rmssub_xitemrclss/b.pls
RMSSUB_XITEMRCLS.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure needs to initially ensure that the passed in message type is a valid type for item reclassification messages. If the message type is invalid, a status of "E" should be returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT needs to be downcast to the actual object using the Oracle's treat function. If the downcast fails, a status of "E" should be returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume needs to verify that the message passes all of RMS's business validation. It calls the RMSSUB_XITEMRCLS_VALIDATE.CHECK_MESSAGE function to determine whether the message is valid. If the message passed RMS business validation, then the function will return true; otherwise, it will return false. If the message has failed RMS business validation, a status of "E" should be returned to the external system along with the error message returned from the CHECK_MESSAGE function.
Once the message has passed RMS business validation, it can be persisted to the RMS database. It calls the RMSSUB_XITEMRCLS_SQL.PERSIST_MESSAGE() function. If the database persistence fails, the function returns false. A status of "E" should be returned to the external system along with the error message returned from the PERSIST_MESSAGE() function.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success status, "S", should be returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
RMSSUB_XITEMRCLS.HANDLE_ERROR() - This is the standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
Filename: rmssub_xitemrclsvals/b.pls
RMSSUB_XITEMRCLS_VALIDATE.CHECK_MESSAGE
(O_error_message IN OUT VARCHAR2,
O_ITEMRCLS_rec OUT ITEMRCLS_REC,
I_message IN RIB_XITEMRCLSDesc,
I_message_type IN VARCHAR2)
This function performs all business validation associated with message and builds the item reclassification record for persistence.
ITEMRCLS CREATE
Check required fields
Verify items not on existing reclassification
Validate the reclassification date (must be today or greater).
Verify hierarchy of item being reclassified to (either an existing hierarchy or a pending hierarchy that will be created prior to the item reclassification)
Verify non-consignment related reclassification and no unit and dollar stocks performed on items
Build reclassification records
ITEMRCLS DELETE
Check required fields
For reclassification header deletes, verify deleting by either reclassification number, reclassification (event) date, or purging all reclassifications
Populate record
ITEMRCLS DETAIL CREATE
Check required fields
Verify items not on existing reclassification
Validate the reclassification date (must be today or greater).
Verify hierarchy of item being reclassified to (either an existing hierarchy or a pending hierarchy that will be created prior to the item reclassification)
Verify non-consignment related reclassification and no unit and dollar stocks performed on items
Build reclassification records
ITEMRCLS DETAIL DELETE
Check required fields
Populate record.
Filename: rmssub_xitemrclssqls/b.pls
RMSSUB_XITEMRCLS_SQL.PERSIST
(O_error_message IN OUT VARCHAR2,
I_dml_rec IN ITEMRCLS_RECTYPE ,
I_message IN RIB_XITEMRCLSDesc)
ITEMRCLS CREATE
Insert a record into the reclass header table
Insert a record into the reclass item table
ITEMRCLS DETAIL DELETE
Delete from the reclass item table.
ITEMRCLS DELETE
If purging all records, delete all from reclass item table.
If purging all records, delete all from reclass header table.
If not purging, delete from reclass item for reclass number or all reclass for an event date.
If not purging, delete from reclass header for reclass number or all reclass for an event date.
ITEMRCLS DELETE
Delete from reclass item for all items on record.
If no items exist for an event, delete the reclass event.
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 Type | Message Type Description | XML Schema Definition (XSD) |
|---|---|---|
| xitemrclscre | External item reclassification create | XItemRclsDesc.xsd |
| xitemrclsdtlcre | External item reclassification detail create | XItemRclsDesc.xsd |
| Xitemrclsdel | External item reclassification delete | XitemRclsRef.xsd |
| Xitemrclsdtldel | External item reclassification detail delete | XItemRclsRef.xsd |
This section describes the location trait subscription API.
The Location Trait Subscription API processes incoming data from an external system to create, edit and delete location traits in RMS. This data is processed immediately upon message receipt so success or failure can be communicated to the external application.
This section describes the package impact.
Filename: rmssub_xloctrts/b.pls
RMSSUB_XLOCTRT.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure will need to initially ensure that the passed in message type is a valid type for loc traits messages. If the message type is invalid, a status of "E" should be returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT need to be downcast to the actual object using the Oracle's treat function. If the downcast fails, a status of "E" should be returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume needs to verify that the message passes all of RMS's business validation. It calls the RMSSUB_XLOCTRT_VALIDATE.CHECK_MESSAGE function to determine whether the message is valid. If the message passed RMS business validation, then the function will return true, otherwise it will return false. If the message has failed RMS business validation, a status of "E" should be returned to the external system along with the error message returned from the CHECK_MESSAGE function.
Once the message has passed RMS business validation, it can be persisted to the RMS database. It calls the RMSSUB_XLOCTRT_SQL.PERSIST_MESSAGE() function.
If the database persistence fails, the function will return false. A status of "E" should be returned to the external system along with the error message returned from the PERSIST_MESSAGE() function.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success, "S", status should be returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
RMSSUB_XLOCTRT.HANDLE_ERROR() - This is the standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
It should be noted that some of the business validation is referential or involves uniqueness. This validation is handled automatically by the referential integrity constraints and the unique indexes implemented on the database.
Filename: rmssub_xloctrtvals/b.pls
RMSSUB_XLOCTRT_VALIDATE.CHECK_MESSAGE
(O_error_message IN OUT VARCHAR2,
O_loctrait_rec OUT LOC_TRAITS_REC,
I_message IN RIB_XLocTraitDesc,
I_message_type IN VARCHAR2)
This function performs all business validation associated with messages and builds the location trait record for persistence.
LOCATION TRAIT CREATE
Check required fields.
Populate record with message data.
LOCATION TRAIT MODIFY
Check required fields.
Verify the location trait exists.
Populate record with message data.
LOCATION TRAIT DELETE
Check required fields.
Verify the location trait exists.
Populate record with message data.
All insert, update and delete SQL statements are located in the family package. This package is LOC_TRAITS_SQL. The private functions will call this package.
Filename: rmssub_xloctrtsqls/b.pls
RMSSUB_XLOCTRT_SQL.PERSIST_MESSAGE
(O_error_message IN OUT VARCHAR2,
I_loc_trait_rec IN LOC_TRAIT_REC,
I_message_type IN VARCHAR2,)
This function determines what type of database transaction it will call based on the message type.
LOCATION TRAIT CREATE
Create messages get added to the location trait table.
LOCATION TRAIT MODIFY
Modify messages directly update the location trait table with changes.
LOCATION TRAIT DELETE
Delete messages directly remove location trait records.
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 Type | Message Type Description | XML Schema Definition (XSD) |
|---|---|---|
| xloctrtcre | External Location Trait Create | XLocTrtDesc.xsd |
| xloctrtdel | External Location Trait Delete | XLocTrtRef.xsd |
| xloctrtmod | External Location Trait Modification | XLocTrtDesc.xsd |
Required fields are shown in RIB documentation.
This section describes the merchandise hierarchy subscription API.
The merchandise hierarchy allows the retailer to create the relationships that are necessary to support the product management structure of a company. This hierarchy reflects a classification of merchandise into multi-level descriptive categorizations to facilitate the planning, tracking, reporting, and management of merchandise within the company.
If RMS is not the system of record for merchandise hierarchy information for an implementation, then this API may be used to create, update or delete elements of the merchandise hierarchy, including division, group, department, class, and subclass, based on an external system.
Division and group deletes also occur immediately upon receipt of the message. However, departments, classes, and subclasses will not actually be deleted from the system upon receipt of the message. Instead, they will be added to the DAILY_PURGE table, where validation will occur to ensure the records can be deleted.
For more on this batch process, see the Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs.
Department VAT records can be created and edited within the department message (VAT records are not deleted). VAT creates can be passed in with a department create message, or they can be passed in with their own specific message type. VAT region and VAT codes records must exist prior to creating department VAT records. Also, when passing in a new VAT region to an existing department with attached items, the VAT information will default to all items.
The merchandise hierarchy must be created from the highest level down. Conversely, the hierarchy must be deleted from the lowest level up. Each lower level references a parent level. This means a department is associated with a group; a class is associated with a department; and a subclass is associated with department/class combination because classes are not unique across departments.
This section describes the package impact.
RMSSUB_XMRCHHR.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure will call the appropriate merchandise hierarchy family package based on the message type passed in.
Any company message type will call RMSSUB_XMRCHHRCOMP.CONSUME
Any division message type will call RMSSUB_XMRCHHRDIV.CONSUME
Any group message type will call RMSSUB_XMRCHHRGRP.CONSUME
Any department message type will call RMSSUB_XMRCHHRDEPT.CONSUME
Any class message type will call RMSSUB_XMRCHHRCLS.CONSUME
Any subclass message type will call RMSSUB_XMRCHHRSCLS.CONSUME
RMSSUB_XMRCHHR[family_name]_VALIDATE.CHECK_MESSAGE
(O_error_message IN OUT VARCHAR2,
O_[family_name]_rec OUT NOCOPY MERCH_SQL.[FAMILY_NAME]_TYPE,
I_message IN RIB_XMrchHr[family_name]Desc,
I_message_type IN VARCHAR2)
This function performs all business validation associated with messages and builds the merchandise hierarchy record for persistence. It should be noted that some of the business validation is referential or involves uniqueness. This validation is handled automatically by the referential integrity constraints and the unique indexes implemented on the database.
RMSSUB_XMRCHHR[family_name]__SQL.PERSIST_MESSAGE
(O_error_message IN OUT VARCHAR2,
I_[family_name]_rec IN MERCH_SQL.[FAMILY_NAME]_TYPE,
I_message_type IN VARCHAR2,)
Consume- This function will take RIB_ XMrchHrDeptDesc_REC as the input. Process the CustFlexAttriVo_TBL in the RIB object and write to DEPS_CFA_EXT table.
Consume - This function will take RIB_ XMrchHrClsDesc_REC as the input. Process the CustFlexAttriVo_TBL in the RIB object and write to CLASS_CFA_EXT table.
Consume- This function will take RIB_ XMrchHrSclsDesc_REC as the input. Process the CustFlexAttriVo_TBL in the RIB object and write to SUBCLASS_CFA_EXT table.
All insert, update and delete SQL statements are located in the family package. This package is MERCH_SQL. The private functions will call this package. This function determines what type of database transaction it will call based on the message 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 Type | Message Type Description | XML Schema Definition (XSD) |
|---|---|---|
| xmrchhrclscre | External Create Class | XMrchHrClsDesc.xsd |
| xmrchhrcompcre | External Create Company | XMrchHrCompDesc.xsd |
| xmrchhrdeptcre | External Create Department | XMrchHrDeptDesc.xsd |
| xmrchhrdivcre | External Create Division | XMrchHrDivDesc.xsd |
| xmrchhrgrpcre | External Create Group | XMrchHrGrpDesc.xsd |
| xmrchhrsclscre | External Create Subclass | XMrchHrSclsDesc.xsd |
| xmrchhrclsdel | External Delete Class | XMrchHrClsRef.xsd |
| xmrchhrdeptdel | External Delete Department | XMrchHrDeptRef.xsd |
| xmrchhrdivdel | External Delete Division | XMrchHrDivRef.xsd |
| xmrchhrgrpdel | External Delete Group | XMrchHrGrpRef.xsd |
| xmrchhrsclsdel | External Delete Subclass | XMrchHrSclsRef.xsd |
| xmrchhrvatcre | External Merch Hierarchy VAT create | XMrchHrDeptDesc.xsd |
| xmrchhrvatmod | External Merch Hierarchy VAT modify | XMrchHrDeptDesc.xsd |
| xmrchhrclsmod | External Modify Class | XMrchHrClsDesc.xsd |
| xmrchhrcompmod | External Modify Company | XMrchHrCompDesc.xsd |
| xmrchhrdeptmod | External Modify Department | XMrchHrDeptDesc.xsd |
| xmrchhrdivmod | External Modify Division | XMrchHrDivDesc.xsd |
| xmrchhrgrpmod | External Modify Group | XMrchHrGrpDesc.xsd |
| xmrchhrsclsmod | External Modify Subclass | XMrchHrSclsDesc.xsd |
A department cannot be set up as both direct cost and consignment. Either the budget markup percent or the budget intake percent must be passed in. If RPM is installed, the average tolerance percent and maximum average counter must be greater than zero.
This section does not include the tables checked in the Daily Purge batch process.
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| COMPHEAD | Yes | Yes | Yes | No |
| DIVISION | Yes | Yes | Yes | Yes |
| DAILY_PURGE | No | Yes | No | No |
| GROUPS | Yes | Yes | Yes | Yes |
| DEPS | Yes | Yes | Yes | No |
| VAT_DEPS | Yes | Yes | Yes | No |
| CLASS | Yes | Yes | Yes | No |
| SUBCLASS | Yes | Yes | Yes | No |
| DEPS_CFA_EXT | Yes | Yes | Yes | Yes |
| CLASS_CFA_EXT | Yes | Yes | Yes | Yes |
| SUBCLASS_CFA_EXT | Yes | Yes | Yes | Yes |
This section describes the merchandise hierarchy reclassification subscription API.
RMS can subscribe to merchandise hierarchy reclassification messages that are published by an external system for retailers who manage their hierarchies in a system outside RMS. This API allows for pending merchandise hierarchy reclassification events to be created, modified or deleted. A separate batch process will read the information off the pending merchandise hierarchy table and create or modify the merchandise hierarchy information in RMS once the change effective date arrives. This API does not accept messages to delete an existing merchandise hierarchy. Any deletion should be done through the Merchandise Hierarchy Subscription API instead. Furthermore, this API will not allow moving a class or subclass between departments. In RMS, a new class and/or subclass needs to be created and the items moved as part of an item reclassification and then the old class and/or subclass deleted.
This section describes the package impact.
Rmssub_xmrchhrclss/b.pls
RMSSUB_XMRCHHRRCLS.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure will initially ensure that the passed in message type is a valid type for merchandise hierarchy reclassification messages. If the message type is invalid, a status of 'E' - Error will be returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT will be downcast to the actual object using the Oracle's Treat function. If the downcast fails, a status of 'E' will be returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume will verify that the message passes all of RMS's business validation. If the message has failed RMS business validation, a status of 'E' will be returned to the external system along with the error message returned from the CHECK_MESSAGE function.
Once the message has passed RMS business validation, it can be persisted to the RMS database. If the database persistence fails, the function will return false.
A status of 'E' will be returned to the external system along with the error message returned from the PERSIST_MESSAGE() function.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success status, 'S', will be returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
RMSSUB_ XMRCHHRRCLS.HANDLE_ERROR() - This is the standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
It should be noted that some of the business validation is referential or involves uniqueness. This validation is handled automatically by the referential integrity constraints and the unique indexes implemented on the database.
Filename: rmssub_xmrchhrrclsvals/b.pls
RMSSUB_XMRCHHRRCLS_VALIDATE.CHECK_MESSAGE
(O_error_message IN OUT VARCHAR2,
O_pend_merch_hier_rec OUT PEND_MERCH_HIER%ROWTYPE,
I_message IN "RIB_XMrchHrRclsDesc_REC",
I_message_type IN VARCHAR2)
This function performs all business validation associated with the messages and builds the merchandise hierarchy record for persistence.
CREATE
Check required fields. Required fields vary based on hierarchy level.
Adding New Hierarchy
Verify passed in hierarchy does not already exist.
Verify parent hierarchy already exists on merchandise hierarchy or pending merchandise hierarchy tables.
Modifying Existing Hierarchy
Verify passed in hierarchy already exists.
Verify that class and subclass hierarchies have passed in parent hierarchy in an existing hierarchy (i.e. classes and subclasses are not allowed to be reclassified into another department).
Populate record with message data
MODIFY
Check required fields.
Verify the hierarchy is already pending.
Populate record with message data.
DELETE
Check required fields.
Verify a pending hierarchy event exists.
Verify no pending hierarchy events exist for levels below the passed in hierarchy level.
Populate record with message data.
All insert, update and delete SQL statements are located in the family package. This package is MERCH_RECLASS_SQL. The private functions will call this package.
Filename: rmssub_ xmrchhrrclssqls /b.pls
RMSSUB_XMRCHHRRCLS_SQL.PERSIST_MESSAGE
(O_error_message IN OUT VARCHAR2,
I_pend_merch_hier_rec IN PEND_MERCH_HIER%ROWTYPE,
I_message_type IN VARCHAR2)
This function determines what type of database transaction it will call based on the message type.
CREATE
Create messages get added to the pending merchandise hierarchy table.
MODIFY
Modify messages directly update the pending merchandise hierarchy table with changes.
DELETE
Delete messages get removed from the pending merchandise hierarchy table.
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 Type | Message Type Description | XML Schema Definition (XSD) |
|---|---|---|
| xmrchhrrclscre | Create Merchandise Hierarchy Reclassification | XMrchHrRclsDesc.xsd |
| xmrchhrrclsdel | Delete Merchandise Hierarchy Reclassification | XMrchHrRclsRef.xsd |
| xmrchhrrclsmod | Modify Merchandise Hierarchy Reclassification | XMrchHrRclsDesc.xsd |
This section describes the organizational hierarchy subscription API.
If RMS is not the system of record for organizational hierarchy information for an implementation, then this API may be used to create, update or delete elements of the hierarchy, including chain based on an external system. The organization hierarchy subscription also assigns existing location traits to or deletes them from elements of the organization hierarchy. Although stores are part of the organization hierarchy, they differ sufficiently to require their own subscription API.
RMS exposes an API that allows external systems to create, edit, and delete chain. All creates, updates, and deletes occur immediately upon receipt of the message.
The organizational hierarchy must be created from the highest level down. Conversely, the hierarchy must be deleted from the lowest level up.
Filenamermssub_xorghrs/b.pls
RMSSUB_XORGHR.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure will initially ensure that the passed in message type is a valid type for organizational hierarchy messages. The valid message types for organizational hierarchy messages are listed in a section below.
If the message type is valid, the generic RIB_OBJECT will be downcast to the actual object using the Oracle's treat function. There will be an object type that corresponds with each message type. If the downcast is successful, then consume will verify that the message passes all of RMS's business validation. It calls the RMSSUB_XORGHR_VALIDATE.CHECK_MESSAGE function to determine whether the message is valid. Once the message has passed RMS business validation, it is persisted to the RMS database. Once the message has been successfully persisted, a success status, "S", is returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
RMSSUB_ XORGHIER.HANDLE_ERROR()-This is the standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
Filename rmssub_xorghrvals/b.pls
RMSSUB_XORGHR_VALIDATE.CHECK_MESSAGE
(O_error_message IN OUT VARCHAR2,
O_org_hier_rec OUT NOCOPY ORGANIZATION_SQL.ORG_HIER_REC,
I_message IN RIB_XOrgHrDesc,
I_message_type IN VARCHAR2)
This function performs all business validation associated with messages and builds the organizational hierarchy record for persistence.
Filename: rmssub_xorghr_sqls/b.pls
RMSSUB_XORGHR_SQL.PERSIST_MESSAGE
(O_error_message IN OUT VARCHAR2,
I_hier_level IN VARCHAR2,
I_org_hier_rec IN ORGANIZATIONAL_SQL.ORG-HIER_REC,
I_message_type IN VARCHAR2,)
This function determines what type of database transaction it will call based on the message type. All insert, update and delete SQL statements are located in the family package. This package is ORGANIZATIONAL_SQL. The private functions will call this package.
Below are the filenames that correspond with each message type. Consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.
| Message Type | Message Type Description | XML Schema Definition (XSD) |
|---|---|---|
| XOrgHrCre | External Create Organizational Hiearchy | XOrgHrDesc.xsd |
| XOrgHrLocTrtCre | External Create Location Trait | XOrgHrDesc.xsd |
| XOrgHrDel | External Delete Organizational Hiearchy | XOrgHrRef.xsd |
| XOrgHrLocTrtDel | External Delete Location Trait | XOrgHrRef.xsd |
| XOrgHrMod | External Modify Organizational Hiearchy | XOrgHrDesc.xsd |
Location trait records must exist prior to attaching them to any hierarchy.
Chains do not have location traits associated with them.
Some of the business validation is referential or involves uniqueness. This validation is handled automatically by the referential integrity constraints and the unique indexes implemented on the database.
This section describes the payment terms subscription API.
Payment terms are supplier-related financial arrangement information that is published to the Oracle Retail Integration Bus (RIB), along with the supplier and the supplier address, from the financial system. Payment terms are the terms established for paying a supplier (for example, 2.5% for 30 days, 3.5% for 15 days, 1.5% monthly, and so on). RMS subscribes to a payment terms message that is held on the RIB. After confirming the validity of the records enclosed within the message, RMS updates its tables with the information.
An external system will publish a payment term, thereby placing the payment term information onto the RIB. RMS will subscribe to the payment term information as published from the RIB and place the information onto RMS tables depending upon the validity of the records enclosed within the message.
Filename: rmssub_ptrms/b.pls
Subscribing to a payment term message entails the use of one public consume procedure. This procedure corresponds to the type of activity that can be done to a payment term record (in this case create/update).
All of the following procedures exist within RMSSUB_PAYTERM.
CONSUME
(O_status_code OUT VARCHAR2,
O_error_message OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure initially checks that the passed in message type is a valid type for Terms messages. The valid message types for Terms messages are: paytermCre, paytermMod, paytermdtlCre and paytermdtlMod. If the message type is invalid, a status of "E" should be returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT will need to be downcast to the actual object using the Oracle's treat function. There will be an object type that corresponds with each message type. If the downcast fails, a status of "E" should be returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume needs to verify that the message passes all of RMS's business validation. It does not actually perform any validation itself; instead, it calls the RMSSUB_PAYTERM_VALIDATE.CHECK_MESSAGE function to determine whether the message is valid. This function is overloaded so simply passing the object in should be sufficient. If the message passed RMS business validation, then the function will return true, otherwise it will return false. If the message has failed RMS business validation, a status of "E" should be returned to the external system along with the error message returned from the CHECK_MESSAGE function.
Once the message has passed RMS business validation, it can be persisted to the RMS database. The consume function does not have to have any knowledge of how to persist the message to the database, it calls the RMSSUB_PAYTERM_SQL.PERSIST() function. This function is overloaded so simply passing the object should be sufficient. If the database persistence fails, the function will return false. A status of "E" should be returned to the external system along with the error message returned from the PERSIST() function.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success status, "S", should be returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
Internal Procedure:
HANDLE_ERROR
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cause IN VARCHAR2,
I_program IN VARCHAR2)
This is the standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
The function consists of a call to API_LIBRARY.HANDLE_ERRORS. API_LIBRARY.HANDLE_ERRORS accepts a program name, the cause of the error and potentially an unparsed error message if one has been created through a call to SQL_LIB.CREATE_MESSAGE. The function uses these input variables to parse a complete error message and pass back a status, depending upon the message and error type, back up through the consume function and up to the RIB.
Business Validation Mode
Filename: rmssub_ptrmvals/b.pls
This function performs all business validation associated with Terms create and modify messages. It is important that the signature uses IN for the message and not IN OUT. When IN is used, the parameter is passed by reference. Passing by reference keeps the server from duplicating the memory allocation.
All of the following functions exist within RMSSUB_PAYTERM_VALIDATE.
CHECK_MESSAGE
(O_error_message OUT RTK_ERRORS.RTK_TEXT%TYPE,
O_dml_rec OUT TERMS_SQL.PAYTERM_REC,
I_message IN "RIB_PayTermDesc_REC",
I_message_type IN VARCHAR2)
This function performs all business validation associated with create/modify messages and builds the order API record with default values for persistence in the payment terms related tables. Any invalid records passed at any time results in message failure.
This function calls CHECK_REQUIRED_FIELDS to make sure that all required fields are not NULL. CHECK_ENABLED is called to check for the validity of records with start_date_active and end_date_active with enabled flag. CHECK_TERMS_HEAD and CHECK_TERMS_DETAIL are called to check for header and detail records before inserting and updating TERMS_DEATIL table. Finally, the payment terms record used for DML is populated within the POPULATE_RECORD function and passed back to RMSSUB_PAYTERM.CONSUME.
Internal Functions:
CHECK_REQUIRED_FIELDS
This function ensures that all required fields in the message are NOT NULL.
POPULATE_RECORDS
This function populates the payment terms output record with the values sent in the message.
CHECK_ENABLED
This function in a loop checks for start_date_active and end_date_active with the enabled_flag setting from RIB_MESSAGE. Declare cursor to retrieve vdate from table period and another cursor to retrieve start_date_active and end_date_active for the terms and terms_seq inputted from TERMS_DETAIL table. In a loop assign terms_seq to a local variable. Open cursor to retrieve start_date_active and end_date_Active from TERMS_DETAIL table. If terms_detail.start_date_active is after period.vdate and if enabled_flag from the rib message is 'Y', then raise program error. If end_date_active is < vdate and enabled_flag from the rib message is 'Y' then raise program error. If vdate > = start_date_active and <= end_date_active and enabled_flag is 'N' then raise a program error.
CHECK_TERMS_HEAD
This function will be responsible for checking TERMS_HEAD record before populating TERMS_DETAIL table for new terms record. Calling TERM_SQL.HEADER_EXISTS function will perform this check.
CHECK_TERMS_DETAIL
This function checks existence of terms_detail records before updating detail record. Calling TERM_SQL.DETAIL_EXISTS function will perform this check.
DML Module
Filename: rmssub_ptrm_sqls/b.pls
The following function exists within RMSSUB_PAYTERM_SQL.
PERSIST
(O_error_message OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message IN TERMS_SQL.PAYTERM_REC,
I_message_type IN VARCHAR2)
Perform INSERT/UPDATE statements by calling the appropriate functions according to the message type and passing the data in a record to these functions.
For the message type indicating a header insert, populate the header record defined in the term_sql package and call the term_sql.insert_header function with this header record. For the message type indicating a header or a detail insert, call the term_sql.insert_detail function and pass to it the detail node from the message.
For the message type indicating a header update, populate the header record defined in the term_sql package and call the term_sql.update_header function with this header record. For the message type indicating a detail update, call the term_sql.update_detail function and pass to it the detail node from the 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) |
|---|---|---|
| PayTermCre | Payment Terms Create Message | PayTermDesc.xsd |
| PayTermMod | Payment Terms Modify Message | PayTermDesc.xsd |
| PayTermDtlCre | Payment Terms Detail Create Message | PayTermDesc.xsd |
| PayTermDtlMod | Payment Terms Detail Modify Message | PayTermDesc.xsd |
One of the primary assumptions in the current API 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 PO subscription API.
This subscription API is used to keep RMS in sync with an external system that is responsible for maintaining purchase orders.
It is assumed that externally generated non-EDI purchase orders are being interfaced expressly for the facilitation of inventory movement in RMS.
This API also default expenses and HTS, applies rounding, defaults inventory management parameters, applies bracket costs, updates OTB, and inserts a record into the deals queue.
This API allows external systems to create, edit, and delete purchase orders within RMS. These transactions are performed immediately upon message receipt so success or failure can be communicated to the calling application.
Purchase order messages are sent across the Oracle Retail Integration Bus (RIB). POs can be created, modified or deleted at the header or the detail level, each with its own message type.
If the Purchase order is a Franchise PO (location is a Franchise store), a corresponding Franchise order is created along with the PO.
In addition to RIB, RMS also exposes a Purchase Order Management web service to allow an external application to create, update, and delete purchase orders in RMS. The web service takes in a collection of purchase orders and will return success and failure through the service response object.
Filename: OrderManagementServiceProviderImplSpec.pls OrderManagementServiceProviderImplBody.pls
For a web service deployment, the 'Purchase Order Management' service with supported operations is available for an external system to send Purchase Order requests to RMS. Each supported operation will invoke the public interfaces in the PurchaseOrderManagementService package as follows:
create - createXOrderColDesc
createDetail - createDetail
modifyHeader - modifyHeader
modifyDetail - modifyDetail
deleteDetail - deleteDetail
These public interfaces will call the corresponding procedures in svcprov_xorder, which will in turn call rmssub_xorder.consume to do the major processing logic.
Filename: svcprov_xorders/b.pls
Procedures called from Purchase Order web service public interfaces in the PurchaseOrderManagementService package to perform major processing.
If error happens, it calls SVCPROV_UTLITY.BUILD_SERVICE_OP_STATUS to build and return RIB_ServiceOpStatus_REC with a failure message; if no errors, it builds and returns RIB_InvocationSuccess_REC with a success message.
Filename: rmssub_xorders/b.pls
RMSSUB_XORDER.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure initially ensures that the passed in message type is a valid type for purchase order messages. The valid message types for purchase order messages are listed in a section below.
If the message type is invalid, a status of "E" is returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT is downcast to the actual object using the Oracle treat function. There is an object type that corresponds with each message type. If the downcast fails, a status of "E" is returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume will verify that the message passes all of RMS's business validation. It calls the RMSSUB_XORDER.BUILD_RECORDS function to validate and populate the header and detail records. The VALIDATE_MESSAGE function within the BUILD_RECORDS function performs XOrder message specific validations. If the message passes these validations, then the function returns true, otherwise it returns false. If the message fails RMS business validation, a status of "E" is returned to the external system along with the error message returned from the VALIDATE_MESSAGE function.
Once the message has passed XOrder specific validations, it is persisted to the RMS database. It calls the RMSSUB_XORDER_SQL.PERSIST() function to insert into the svc_ordhead and svc_orddetail staging tables in preparation for the upload in to the RMS purchase order tables via the PO induction package.
The custom flex attributes in the message are subscribed by calling the function RMSSUB_XORDER_CFA.CONSUME(). This will insert the CFAS data into the table svc_cfa_ext.
A record is inserted into svc_process_tracker with template_type = 'XORDER' and process_source = 'EXT' (external). A parameter called attempt_rms_load which determines the final destination of the XOrder messages is also populated. It can either be 'RMS', which indicates that the message will be uploaded to the RMS purchase order tables, or 'STG' which means that the message will only be inserted into the RMS staging tables for further enrichment. Loading of records from staging to RMS will be performed via the induction process.
Once a record is inserted into svc_process_tracker, and the attempt_rms_load parameter is set to 'RMS' (which is the default), the PO Induction package function, PO_INDUCT_SQL.EXEC_ASYNC is responsible for performing the bulk of the validations and persistence into the RMS tables, is called. It contains validations that exist in PO creation via the UI and via PO induction, which the XOrder messages will be subject to. If the inserts into the ordering tables fails, the function returns false. A status of "E" is returned to the external system along with the error message returned from the PO_INDUCT_SQL.EXEC_ASYNC()function.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success status, "S", is returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
Filename: rmssub_xorder_sqls/b.pls
RMSSUB_XORDER_SQL.PERSIST
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_order_rec IN ORDER_SQL.ORDER_REC,
I_message_type IN VARCHAR2)
This function checks the message type to route the object to the appropriate internal functions that perform the inserts into the svc_ordhead and svc_orddetail staging tables.
ORDER CREATE
Inserts records in the SVC_ORDHEAD and SVC_ORDDETAIL tables.
ORDER MODIFY
Inserts a record into the SVC_ORDHEAD table.
ORDER DELETE
Inserts a record into the SVC_ORDHEAD table.
ORDER DETAIL CREATE
Inserts records into the SVC_ORDDETAIL table.
ORDER DETAIL MODIFY
Inserts records into the SVC_ORDDETAIL table.
ORDER DETAIL DELETE
Inserts records into the SVC_ORDDETAIL table.
Filename: rmssub_xorder_cfas/b.pls
RMSSUB_XORDER_CFA.CONSUME(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message IN "RIB_XOrderDesc_REC",
I_message_type IN VARCHAR2,
I_process_id IN SVC_PROCESS_TRACKER.PROCESS_ID%TYPE)
This function inserts the CFAS data in to svc_cfa_ext table by calling the function CFA_API_SQL.INSERT_SVC_CFA_EXT ().
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) |
|---|---|---|
| create | Create Order Service Operation | XOrderDesc.xsd |
| createDetail | Create Order Detail Service Operation | XOrderDesc.xsd |
| modifyHeader | Modify Order Service Operation | XOrderDesc.xsd |
| modifyDetail | Modify Order Detail Service Operation | XOrderDesc.xsd |
| deleteDetail | Delete Order Detail Service Operation | XOrderRef.xsd |
| XorderCre | Order Create Message | XOrderDesc.xsd |
| XorderMod | Order Modify Message | XOrderDesc.xsd |
| XorderDel | Order Delete Message | XOrderRef.xsd |
| XorderDtlCre | Order Detail Create Message | XOrderDesc.xsd |
| XorderDtlMod | Order Detail Modify Message | XOrderDesc.xsd |
| XorderDtlDel | Order Detail Delete Message | XOrderRef.xsd |
Quantities and dates processed by this API are treated as the actual values that are used to insert/update the RMS ordering tables.
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| SVC_ORDHEAD | Yes | Yes | Yes | Yes |
| SVC_ORDDETAIL | Yes | Yes | Yes | Yes |
| SVC_PROCESS_TRACKER | Yes | Yes | Yes | No |
| CORESVC_PO_ERR | No | Yes | No | No |
| SVC_ORDER_PARAMETER_CONFIG | Yes | No | No | No |
| CORESVC_PO_CHUNKS | Yes | Yes | Yes | Yes |
| ORDHEAD | Yes | Yes | Yes | Yes |
| ORDSKU | Yes | Yes | Yes | Yes |
| ORDLOC | Yes | Yes | Yes | Yes |
| ITEM_SUPPLIER | Yes | Yes | No | No |
| ITEM_SUPP_COUNTRY | Yes | Yes | No | No |
| ITEM_SUPP_MANU_COUNTRY | Yes | Yes | No | No |
| ITEM_SUPP_COUNTRY_LOC | Yes | Yes | No | No |
| ITEM_LOC | Yes | Yes | No | No |
| ITEM_LOC_SOH | Yes | Yes | No | No |
| PRICE_HIST | No | Yes | No | No |
| ITEM_ZONE_PRICE | Yes | No | No | No |
| ITEM_MASTER | Yes | No | No | No |
| PACKITEM_BREAKOUT | Yes | No | No | No |
| SHIPMENT | Yes | No | No | No |
| SHIPSKU | Yes | No | No | No |
| APPT_DETAIL | Yes | No | No | No |
| ALLOC_HEADER | Yes | No | No | Yes |
| ALLOC_DETAIL | Yes | No | No | Yes |
| STORE | Yes | No | No | No |
| WAREHOUSE | Yes | No | No | No |
| SUPS | Yes | No | No | No |
| DEPS | Yes | No | No | No |
| CURRENCIES | Yes | No | No | No |
| CURRENCY_RATES | Yes | No | No | No |
| TERMS | Yes | No | No | No |
| SYSTEM_OPTIONS | Yes | No | No | No |
| UNIT_OPTIONS | Yes | No | No | No |
| ADDR | Yes | No | No | No |
| WF_ORDER_HEAD | Yes | Yes | Yes | Yes |
| WF_ORDER_DETAIL | Yes | Yes | Yes | Yes |
| CFA_EXT_ENTITY | Yes | No | No | No |
| CFA_ATTRIB_GROUP_SET | Yes | No | No | No |
| CFA_ATTRIB_GROUP | Yes | No | No | No |
| CFA_ATTRIB | Yes | No | No | No |
This section describes the price event subscription API.
RMS may subscribe to price change events through this subscription API when Oracle Retail Price Management (RPM) is not being used for pricing. The price event subscription keeps RMS in sync with the external system that is responsible for maintaining price changes. The price event subscription updates prices for item/locations that already exist in RMS. It does not create or delete item/locations in RMS tables. This API supports the following types of Pricing Events: Regular Price Changes (REG), Clearance (CLRS), Clearance Reset (CLRE), Promotion Start (PROMS), Promotion End (PROME) and Base Retail (BASERT) (Change in Item master only).
Price changes can be performed at the following levels of the organization hierarchy: chain, area, region, district, and store. Prices are updated for all stores within the location group. Because warehouses are not part of the organization hierarchy, they are only impacted by price changes applied at the warehouse level. Similarly, item can be provided at parent, parent/diff or transactional level.
If effective date is equal to vdate, the price change is performed immediately; if effective date is greater than vdate, the price event is staged to be processed by nightly batch, one day before the effective date. For multiple conflicting price events affecting the same item location for the same effective date (> vdate), the record with the lowest hierarchy will be picked for processing, and other records will be ignored. For example, if a Clearance event exists at Item-Parent/Chain Level and a Regular Price Change also exists for the same date at Item/Store Level, Regular Price Change will be processed and the Clearance record for that Item/store will be ignored.
This subscription API supports three message types for future price events: xprceventcre (Create), xprceventmod (Modification) and xprceventdel (Deletion).For price events on the same day, only Create Messages are allowed.
The Event ID and Event Type combination must be unique across all creation messages. Modification and Deletion messages are based on this combination for processing. Also, the modification and deletion messages will not be processed for current date price events and for already processed price events.
Most of the basic validations such as Valid Location; Valid Approved Item, and so on mentioned below in the validation package are performed at the RIB message itself. However, the following are a few validations performed during processing of Price Change events as well:
Regular Price Changes cannot be performed if an Item is on clearance.
Location currency must match with the currency for a price event.
Promo Retail values should reside in Item loc for Promotion End event.
The Item should be already on Clearance for a Clearance Reset Price event.
This section describes the package impact.
Filename: rmssub_xprcevents/b.pls
RMSSUB_XPRCEVENT.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure needs to initially ensure that the passed in message type is a valid type for Price change messages. If the message type is valid, the generic RIB_OBJECT needs to be downcast to the actual object using the Oracle's treat function. If the downcast fails, a status of "E" should be returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume needs to verify that the message passes all of the RMS business validation. It calls the RMSSUB_XPRCEVENT_VALIDATE.CHECK_MESSAGE function to determine whether the message is valid. If the message passed RMS business validation, then the function will return true, otherwise it will return false. If the message has failed RMS business validation, a status of "E" should be returned to the external system along with the error message returned from the CHECK_MESSAGE function.
Once the message has passed RMS business validation, it can be persisted to the RMS database. It calls the RMSSUB_XPRCEVENT.PERSIST() function. If the database persistence fails, the function returns false. A status of "E" should be returned to the external system along with the error message returned from the PERSIST() function.
If effective date is greater than vdate, data is staged in SVC_PRICING_EVENT_HEAD and SVC_PRICING_EVENT_LOCS for future processing. If effective date is equal to vdate, message is processed immediately. The processing for price events is done by calling CORESVC_XPRICE_SQL.EXPLODE_DETAILS and CORESVC_XPRICE_SQL.PROCESS_DETAILS functions. If effective date is less than vdate, message is rejected.
Once the message has been successfully persisted, there is nothing more for the consume procedure to do. A success status, "S", should be returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
Filename: rmssub_xprceventvals/b.pls
It should be noted that some of the business validation is referential or involves uniqueness. This validation is handled automatically by the referential integrity constraints and the unique indexes implemented on the database.
This function performs all business validation associated with message.
Check required fields
Validate passed in fields (currency, country, UOM, hierarchy level).
Verify item is above or equal to transaction level and approved.
Verify item passed in is not non-sellable.
If diff ids are passed in, verify they are valid for passed in item.
Validate single and/or multi UOMs passed in are of the same UOM class as the standard UOM.
Validate that event type and event id combination is unique
Validate for uniqueness against same hierarchy and item level for conflicting future price events.
Filename: coresvc_xpricesqls/b.pls
This package contains all the processing logic for completing the execution of a price event.
CORESVC_XPRICE_SQL.EXPLODE_DETAILS
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_process_id IN NUMBER)s
Insert into SVC_PRICING_EVENT_TEMP from SVC_PRICING_EVENT_HEAD and SVC_PRICING_LOCS tables by exploding data to transactional level item and store/wh level.
CORESVC_XPRICE_SQL.PROCESS_DETAILS
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_process_id IN NUMBER)
Validates exploded data for item-location level validations and performs necessary updates and inserts into different tables based on Event types.
For 'Base Retail' Price Events , only current selling unit retail and current selling uom fields are updated in ITEM_MASTER
For 'Regular' and 'Clearance Reset' Price Events, selling unit retail is converted to unit retail in standard uom and table ITEM_LOC is updated. Records are inserted into PRICE_HIST and TRAN_DATA.
For 'Clearance' Events, other than updates and inserts similar to 'Regular' price events, records are also inserted into SUP_DATA. Deactivate date is also updated to vdate in REPL_ITEM_LOC.
For 'Promotion Start' and 'Promotion End' price events , promo selling retail is converted to promo retail in standard uom and table ITEM_LOC is updated. Records are also inserted into PRICE_HIST table.
For emergency price changes (effective date = vdate), on insert into PRICE_HIST table, records are inserted into EMER_PRICE_HIST table as well for update of orders by ordupd(Order Update) batch.
For each TRAN_DATA record inserted, a SUP_DATA record will also be inserted for Clearance markdowns.
Below are the filenames that correspond with each message type. Consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.
| Message Type | Message Type Description | XML Schema Definition (XSD) |
|---|---|---|
| xprceventcre | External Price Events create | XPrcEventDesc.xsd |
| xprceventmod | External Price Events modify | XPrcEventDesc.xsd |
| xprceventdel | External Price Events delete | XPrcEventRef.xsd |
Required fields are shown in the RIB documentation.
Data being subscribed is assumed to be correct in terms of pricing information.
Validations similar to that of conflict checking in RPM are not in scope.
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| ITEM_LOC | Yes | No | Yes | No |
| ITEM_LOC_SOH | Yes | No | No | No |
| STORE | Yes | No | No | No |
| WH | Yes | No | No | No |
| ITEM_MASTER | Yes | No | Yes | No |
| DIFF_GROUP_HEAD | Yes | No | No | No |
| DIFF_GROUP_DETAIL | Yes | No | No | No |
| CHAIN | Yes | No | No | No |
| AREA | Yes | No | No | No |
| REGION | Yes | No | No | No |
| DISTRICT | Yes | No | No | No |
| CURRENCIES | Yes | No | No | No |
| STORE_HIERARCHY | Yes | No | No | No |
| ITEM_SUPP_COUNTRY_LOC | Yes | No | No | No |
| ITEM_SUPP_COUNTRY | Yes | No | No | No |
| PRICE_HIST | Yes | Yes | No | No |
| EMER_PRICE_HIST | No | Yes | No | No |
| SUP_DATA | No | Yes | No | No |
| TRAN_DATA | No | Yes | No | No |
| REPL_ITEM_LOC | Yes | No | Yes | No |
| SVC_PRICING_EVENT_HEAD | Yes | Yes | Yes | No |
| SVC_PRICING_EVENT_LOCS | Yes | Yes | No | No |
| SVC_PRICING_EVENT_TEMP | Yes | Yes | Yes | No |
This section describes the receiving subscription API.
Receipt subscription:
Purchase Order Receiving.
Stock Order Receiving (including Transfers and Allocations).
RMS receives against purchase orders, transfers, and allocations. Transfers and allocations are collectively referred to as stock orders. The receipt subscription API processes carton-level receipts and a number of carton-level exceptions for stock orders receipts.
Purchase orders continue to be received only at the item level. If errors are encountered during purchase order receiving, the entire message is rejected and processing of the message stops.
Stock orders may be received at the bill of lading (BOL), carton, or item level. The following exceptions are automatically processed by the new stock order receiving package:
Receiving against the wrong BOL.
Receiving at a location which is a walk-through store for the intended location.
Wrong store receiving.
Unwanted cartons (those that have not been scanned).
Once RMS determines the appropriate receiving process for a carton, the shipment detail records are identified and existing line item level receiving is executed. The items are received into stock and transactions are updated.
Stock orders may be received at the BOL (receiving the entire shipment without checking the details), carton (receiving the entire carton on SHIPSKU without checking the details), or item level. When an error is encountered during stock order receiving, an error record is created for the BOL, carton, or item in error. Processing continues for the remainder of the stock order receipt message. When the entire message has been processed, all of the error records are then handled. Error records are grouped together based on the type of error and a complete receipt message is created for each group. All errors will be collected in an error table, which will then be passed back to the RIB for further processing or hospitalization.
The process for handling carton level receipts is as follows:
RMS determines whether a message type contains a receipt or an appointment.
If a receipt, RMS determines whether the document type is purchase order (P), transfer (T), or allocation (A).
If a stock order (transfer or allocation), RMS determines whether the receipt is an item level receipt (SK) or a carton level receipt (BL).
If a carton level receipt, two scenarios are possible. The message may contain (a) a bill of lading number but no carton numbers or (b) a bill of lading and one or more carton numbers.
Bill of lading/no cartons: RMS receives all cartons associated with the BOL along with their contents (line items).
Bill of lading/with cartons: RMS receives only the specified cartons and their contents (line items).
The status of the cartons determines how the cartons/items are processed. The status may be Actual (A), Overage (O), or Dummy BOL (D).
The carton does not belong to the current BOL. RMS attempts to match the contents with the correct BOL.
If the carton belongs to a BOL at the given location, RMS receives the carton against the correct BOL at the given location.
If the carton belongs to a BOL at a related walk-through store, RMS receives the carton against the intended BOL at the intended location.
If the carton belongs to a BOL at an unrelated location, RMS uses the wrong store receiving process.
Cartons were received under a dummy bill of lading (BOL) number. RMS attempts to match the contents with a valid BOL.
If the carton belongs to a valid BOL at the given location, RMS receives the carton against the intended BOL at the given location.
If the carton belongs to a valid BOL at a related walk-through store, RMS receives the carton against the intended BOL at the intended location.
If the carton belongs to a valid BOL at an unrelated location, RMS uses the wrong store receiving process.
The wrong_st_receipt_ind system option controls whether wrong store receiving is available in RMS. The wrong_st_receipt_ind must be set to Y (Yes) to turn on this functionality. Wrong store receiving is done at the line item level. Inventory, average costs, and transactions for both the intended location and actual location are adjusted to accurately reflect the actual location of the items.
Receipts are processed based upon the document type indicator in the message. The indicator serves as a flag for RMSSUB_RECEIVE.CONSUME to use when calling the appropriate function that validates the data and writes the data to the base tables. The following are the document types and respective package and function names:
A - for allocation. STOCK_ORDER_RCV_SQL.ALLOC_LINE_ITEM
P - for purchase order. ORDER_RCV_SQL .PO_LINE_ITEM
T - for transfer. STOCK_ORDER_RCV_SQL.TSF_LINE_ITEM
A blind receipt is generated by an external application whenever a movement of goods is initiated by that application. RMS has no prior knowledge of blind receipts. RMS handles blind receipts when it runs STOCK_ORDER_RCV_SQL (transfers and allocations) or PO_RCV_SQL (purchase orders). If no appointment record exists on APPT_DETAIL, the respective function writes a record to the DOC_CLOSE_QUEUE table.
When a transfer, PO or allocation is received at a location, the external location (store or warehouse) will publish a receipt message to the RIB indicating that the stock has arrived. RMS will subscribe to the receipt message and update the appropriate tables, including shipment, transfer/allocation/purchase order, inventory and stock ledger.
For stock order receiving the ownership of the goods moves to the receiving location at the time of shipment. As a result, financial transaction records are written for the goods shipped when RMS processes a BOL message. At the receiving time, financial transaction records will only need to be written for the overage receiving. In addition, the stock order receiving process also handles the situations where stock is received with no receipt, or if the stock is received at wrong stores, or if the item received is on a dummy carton.
The receipt message is a hierarchical message that can contain a series of receipts. Each receipt corresponds to a transfer or an allocation or a PO, and can contain carton or item details. Purchase orders are only received at the item level. Any errors encountered during purchase order receiving will cause the entire message to be rejected and processing of the message will stop.
When receiving a customer order at stores, SIM will send a receipt message to both RMS and OMS, using a new message type of 'receiptordadd'. RMS will process 'receiptordadd' message in the same way as 'receiptadd'.
RMS supports of Brazil Localization. This includes a layer of code to enable decoupling of localization logic that is only required for country-specific configuration. This layer affects the RIB API flows including Receiving subscription.
This is a layer of code which enables decoupling of localization logic that is only required for certain country-specific configuration. This layer affects the RIB API flows including Receiving subscription. This allows RMS to be installed without requiring customers to install or use this localization functionality, where not required.
Filename: rmssub_receivings/b.pls
RMSSUB_RECEIVING.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2,
O_rib_otbdesc_rec OUT "RIB_OTBDesc_REC",
O_rib_error_tbl OUT RIB_ERROR_TBL)
This procedure will make calls to receiving or appointment functions based on the value of I_message_type. If I_message type is RECEIPT_ADD or RECEIPT_UPD or RECEIPT_ORDADD, then a call is made to RMSSUB_RECEIPT.CONSUME, casting the message as a "RIB_ReceiptDesc_REC". If I_message_type is APPOINT_HDR_ADD, APPOINT_HDR_UPD, APPOINT_HDR_DEL, APPOINT_DTL_ADD, APPOINT_DTL_UPD, or APPOINT_DTL_DEL, then a call is made to RMSSUB_APPOINT.CONSUME. This is the procedure called by the RIB.
RMSSUB_RECEIVING.HANDLE_ERRORS
(O_status_code IN OUT VARCHAR2,
IO_error_message IN OUT VARCHAR2,
I_cause IN VARCHAR2,
I_program IN VARCHAR2)
Standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
Filename: rmssub_receipts/b.pls
RMSSUB_RECEIPT.CONSUME
O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_rib_receiptdesc_rec IN "RIB_ReceiptDesc_REC",
I_message_type IN VARCHAR2,
O_rib_otbdesc_rec OUT "RIB_OTBDesc_REC",
O_rib_error_tbl OUT RIB_ERROR_TBL)
This function performs PO receiving and stock order receiving for each receipt in the message. Document type 'P' is for purchase order receiving, 'A' for allocation receiving, and 'T', 'V', 'D' for transfer receiving. All other document types are invalid.
The RIB object "RIB_ReceiptDesc_REC" is included in RIB_ReceiptOverage_REC" to accommodate for Overages.
Calls are made to ORDER_RCV_SQL.INIT_PO_ASN_LOC_GROUP, STOCK_ORDER_RCV_SQL.INIT_TSF_ALLOC_GROUP, and RMSSUB_RECEIPT_ERROR.INIT. These functions initialize global variables and clean out cached info.
The process then loops through each receipt in the message and performs localization check. If localized, invoke localization logic through L10N_SQL decoupling layer for procedure key 'CONSUME_RECEIPT'. If not localized, call CONSUME_RECEIPT for normal processing:
If the document type is 'P' (purchase order), it calls ORDER_RCV_SQL.PO_LINE_ITEM to receive the items on the PO.
If the document type is 'T', 'D', 'V' (transfer) or 'A' (allocation), it calls RMSSUB_STKORD_RECEIPT.CONSUME to receive the items on the transfer or allocation.
If the document type is not 'P', 'T', 'D', 'V' or 'A' the message processing is stopped and an error message returned.
After processing all receipts, call ORDER_RCV_SQL.FINISH_PO_ASN_LOC_GROUP, STOCK_ORDER_RCV_SQL.FINISH_TSF_ALLOC_GROUP, and RMSSUB_RECEIPT_ERROR.FINISH. These functions wrap up the processing for receiving and error logic.
If any records exist on the rib_otb_tbl returned by ORDER_RCV_SQL.FINISH_PO_ASN_LOC_GROUP, then create a rib_otbdesc_rec object and add the rib_otb_tbl to the object.
Filename: rmssub_stkord_receipts/b.pls
RMSSUB_STKORD_RECEIPT.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_appt IN APPT_HEAD.APPT%TYPE,
I_rib_receipt_rec IN "RIB_Receipt_REC")
This function will process stock order receiving for all records within the rib_receipt_rec passed in. First, this function calls RMSSUB_RECEIPT_ERROR.BEGIN_RECEIPT. This function holds onto the header level information (appt_nbr and rib_receipt_rec), which may be used to create error objects.
Next, RMSSUB_RECEIPT_VALIDATE.CHECK_RECEIPT is called, which does validation at the receipt level. If the validation fails the receipt is rejected by calling RMSSUB_RECEIPT_ERROR.ADD_ERROR.
The package does carton-level receiving when receipt_type = 'BL', and item-level receiving when receipt_type = 'SK'.
There are two scenarios for carton-level receiving:
The rib_receipt_rec contains a bol_no and no cartons (no detail nodes). In this case the function RMSSUB_STKORD_RECEIPT_VALIDATE.CHECK_BOL is called, which does business level validation for the BOL. If the validation succeeds then RMSSUB_STKORD_RECEIPT_SQL.PERSIST_BOL is called. If the validation fails the BOL receipt is rejected by calling RMSSUB_RECEIPT_ERROR.ADD_ERROR.
The rib_receipt_rec contains a bol_no and 1 or more cartons (detail nodes). In this case, the process loops through each carton in the receipt and calls the function RMSSUB_STKORD_RECEIPT_VALIDATE.CHECK_CARTON. This function does business level validation for a carton. If the validation succeeds RMSSUB_STKORD_RECEIPT_SQL.PERSIST_CARTON is called. If the validation fails because the carton is a duplicate (by checking the returned validation_code), then the call to PERSIST_CARTON is skipped and processing continues. Duplicates are ignored with no error. If the validation fails for any other reason then the carton is rejected by calling RMSSUB_RECEIPT_ERROR.ADD_ERROR.
Item (SKU) Level Receiving:
If the receipt is item-level ('SK') the process loops through the detail records and calls the function RMSSUB_STKORD_RECEIPT_VALIDATE.CHECK_ITEM, which does business level validation for the item details. If the validation succeeds then RMSSUB_STKORD_RECEIPT_SQL.PERSIST_LINE_ITEM is called to execute existing line item receiving package calls. If the validation fails then the item is rejected by calling RMSSUB_RECEIPT_ERROR.ADD_ERROR.
When all details for the receipt have been processed, or if the entire receipt itself is rejected, then RMSSUB_RECEIPT_ERROR.END_RECEIPT is called. This function groups all similar errors and creates the appropriate error objects.
If a break to sell sellable item is on the message, a call to CHECK_ITEM and GET_ORDERABLE_ITEMS is made to convert the sellable to its orderable items. For a break to sell item, the orderable items are on the transfers, allocations, shipment, inventory and stock ledger.
Filename: rmssub_stkord_rct_vals/b.pls
RMSSUB_STKORD_RECEIPT_VALIDATE.CHECK_RECEIPT
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
O_valid OUT BOOLEAN,
O_validation_code OUT VARCHAR2,
I_rib_receipt_rec IN "RIB_Receipt_REC")
This function performs business validation for a receipt. If any of the validations fail then O_validation_error is populated with the specified error code and O_valid is set equal to FALSE. Otherwise, O_validation_error is left as NULL and O_valid is set equal to TRUE.
RMSSUB_STKORD_RECEIPT_VALIDATE.CHECK_BOL
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
O_valid IN OUT BOOLEAN,
O_validation_code IN OUT VARCHAR2,
O_shipment IN OUT SHIPMENT.SHIPMENT%TYPE,
O_item_table IN OUT STOCK_ORDER_RCV_SQL.ITEM_TAB,
O_qty_expected_table IN OUT STOCK_ORDER_RCV_SQL.QTY_TAB,
O_inv_status_table IN OUT STOCK_ORDER_RCV_SQL.INV_STATUS_TAB,
O_carton_table IN OUT STOCK_ORDER_RCV_SQL.CARTON_TAB,
O_distro_no_table IN OUT STOCK_ORDER_RCV_SQL.DISTRO_NO_TAB,
O_tampered_ind_table IN OUT STOCK_ORDER_RCV_SQL.TAMPERED_IND_TAB,
I_bol_no IN SHIPMENT.BOL_NO%TYPE,
I_to_loc IN SHIPMENT.TO_LOC%TYPE)
This function performs business validation for receipts using BOL-level receiving. During validation this function selects data from the SHIPMENT and SHIPSKU tables and passes this information out through the parameters. This is done so that these tables do not have to be hit again during the receiving (persist) process. If any of the validations fail then O_validation_error is populated with the specified error code and O_valid is set equal to FALSE. Otherwise, O_validation_error is left as NULL and O_valid is set equal to TRUE.
RMSSUB_STKORD_RECEIPT_VALIDATE.CHECK_CARTON
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
O_valid IN OUT BOOLEAN,
O_validation_code IN OUT VARCHAR2,
O_ctn_shipment IN OUT SHIPMENT.SHIPMENT%TYPE,
O_ctn_to_loc IN OUT SHIPMENT.TO_LOC%TYPE,
O_ctn_bol_no IN OUT SHIPMENT.BOL_NO%TYPE,
O_item_table IN OUT STOCK_ORDER_RCV_SQL.ITEM_TAB,
O_qty_expected_table IN OUT STOCK_ORDER_RCV_SQL.QTY_TAB,
O_inv_status_table IN OUT STOCK_ORDER_RCV_SQL.INV_STATUS_TAB,
O_carton_table IN OUT STOCK_ORDER_RCV_SQL.CARTON_TAB,
O_distro_no_table IN OUT STOCK_ORDER_RCV_SQL.DISTRO_NO_TAB,
O_tampered_ind_table IN OUT STOCK_ORDER_RCV_SQL.TAMPERED_IND_TAB,
O_wrong_store_ind IN OUT VARCHAR2,
O_wrong_store IN OUT SHIPMENT.TO_LOC%TYPE,
I_bol_no IN SHIPMENT.BOL_NO%TYPE,
I_to_loc IN SHIPMENT.TO_LOC%TYPE,
I_from_loc IN SHIPMENT.FROM_LOC%TYPE,
I_from_loc_type IN SHIPMENT.FROM_LOC_TYPE%TYPE,
I_rib_receiptcartondtl_rec IN "RIB_ReceiptCartonDTL_REC")
This function performs business validation for receipts using carton-level receiving. Based on the carton status, a carton can be received to the intended store only, or as a dummy carton or to the walk-through store of the intended store.
During validation this function selects data from SHIPMENT and SHIPSKU tables and passes this information out through the parameters. This is done so that these tables do not have to be hit again during the receiving (persist) process. If any of the validations fail then O_validation_error is populated with the specified error code and O_valid is set equal to FALSE. Otherwise, O_validation_error is left as NULL and O_valid is set equal to TRUE.
RMSSUB_STKORD_RECEIPT_VALIDATE.CHECK_ITEM
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
O_valid OUT BOOLEAN,
O_validation_code OUT VARCHAR2,
I_distro_no IN SHIPSKU.DISTRO_NO%TYPE,
I_dummy_carton_ind IN VARCHAR2)
This function performs business validation for item details. If any of the validations fail then O_validation_error is populated with the specified error code and O_valid is set equal to FALSE. Otherwise, O_validation_error is left as NULL and O_valid is set equal to TRUE.
RMSSUB_STKORD_RECEIPT_SQL.PERSIST_BOL
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_appt IN APPT_HEAD.APPT%TYPE,
I_doc_type IN APPT_DETAIL.DOC_TYPE%TYPE,
I_shipment IN SHIPMENT.SHIPMENT%TYPE,
I_to_loc IN SHIPMENT.TO_LOC%TYPE,
I_bol_no IN SHIPMENT.BOL_NO%TYPE,
I_item_table IN STOCK_ORDER_RCV_SQL.ITEM_TAB,
I_qty_expected_table IN STOCK_ORDER_RCV_SQL.QTY_TAB,
I_inv_status_table IN STOCK_ORDER_RCV_SQL.INV_STATUS_TAB,
I_carton_table IN STOCK_ORDER_RCV_SQL.CARTON_TAB,
I_distro_no_table IN STOCK_ORDER_RCV_SQL.DISTRO_NO_TAB,
I_tampered_ind_table IN STOCK_ORDER_RCV_SQL.TAMPERED_IND_TAB)
This function calls STOCK_ORDER_RCV_SQL.TSF_BOL_CARTON (for transfers) and STOCK_ORDER_RCV_SQL.ALLOC_BOL_CARTON (for allocations) to perform BOL level receiving.
RMSSUB_STKORD_RECEIPT_SQL.PERSIST_CARTON (O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE, I_appt IN APPT_HEAD.APPT%TYPE, I_doc_type IN APPT_DETAIL.DOC_TYPE%TYPE, I_shipment IN SHIPMENT.SHIPMENT%TYPE, I_to_loc IN SHIPMENT.TO_LOC%TYPE, I_bol_no IN SHIPMENT.BOL_NO%TYPE, I_receipt_no IN APPT_DETAIL.RECEIPT_NO%TYPE, I_disposition IN INV_STATUS_CODES.INV_STATUS_CODE%TYPE, I_receipt_date IN SHIPMENT.RECEIVE_DATE%TYPE, I_item_table IN STOCK_ORDER_RCV_SQL.ITEM_TAB, I_qty_expected_table IN STOCK_ORDER_RCV_SQL.QTY_TAB, I_weight IN ITEM_LOC_SOH.AVERAGE_WEIGHT%TYPE, I_weight_uom IN UOM_CLASS.UOM%TYPE, I_inv_status_table IN STOCK_ORDER_RCV_SQL.INV_STATUS_TAB, I_carton_table IN STOCK_ORDER_RCV_SQL.CARTON_TAB, I_distro_no_table IN STOCK_ORDER_RCV_SQL.DISTRO_NO_TAB, I_tampered_ind_table IN STOCK_ORDER_RCV_SQL.TAMPERED_IND_TAB, I_wrong_store_ind IN VARCHAR2, I_wrong_store IN SHIPMENT.TO_LOC%TYPE)
This function calls STOCK_ORDER_RCV_SQL.TSF_BOL_CARTON (for transfers) and STOCK_ORDER_RCV_SQL.ALLOC_BOL_CARTON (for allocations) to perform carton level receiving.
RMSSUB_STKORD_RECEIPT_SQL.PERSIST_LINE_ITEM
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_location IN SHIPMENT.TO_LOC%TYPE,
I_bol_no IN SHIPMENT.BOL_NO%TYPE,
I_distro_no IN SHIPSKU.DISTRO_NO%TYPE,
I_distro_type IN VARCHAR2,
I_appt IN APPT_HEAD.APPT%TYPE,
I_rib_receiptdtl_rec IN "RIB_ReceiptDTL_REC")
This function calls STOCK_ORDER_RCV_SQL.TSF_LINE_ITEM (for transfers) and STOCK_ORDER_RCV_SQL.ALLOC_LINE_ITEM (for allocations) to perform item level receiving.
Filename: rmssub_receipt_errors/b.pls
For each item or carton found to be in error during the receiving process, an error record is created. When all details for a receipt have been processed, the error records for that receipt are grouped by the error type. Error objects are collected in an error table, which is passed back to the RIB for additional processing. This type of error handling allows all valid records to be processed even when an invalid record is encountered.
RMSSUB_RECEIPT_ERROR.INIT
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE)
This function initializes variables for error processing. It is called in the 'init' section of the RMSSUB_RECEIPT.CONSUME() function.
RMSSUB_RECEIPT_ERROR.BEGIN_RECEIPT
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_appt_nbr IN APPT_HEAD.APPT%TYPE,
I_rib_receipt_rec IN "RIB_Receipt_REC")
This function is called once for each receipt within RMSSUB_STKORD_RECEIPT.CONSUME(). It copies the header information into the package level variables. This information is used when an error record is created.
RMSSUB_RECEIPT_ERROR.ADD_ERROR
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_error_type IN VARCHAR2,
I_error_code IN VARCHAR2,
I_error_desc IN VARCHAR2,
I_error_level IN VARCHAR2,
I_error_object IN RIB_OBJECT)
Used whenever an item or carton error occurs within the stock order receiving process. All calls to this function occur within RMSSUB_STKORD_RECEIPT.CONSUME.
Parameter explanation:
O_error_message: any error message created if this function fails (EXCEPTION).
I_error_type: either 'BL' for business logic process error, or 'SY' for system error. Currently, 'BL' type errors are limited to BOL/carton level business validation errors.
I_error_code: a specific code to identify why/how the error occurred.
I_error_desc: text description of the error.
I_error_level: lets the package know how to cast the I_detail_rec. Valid values are 'RECEIPT', 'BOL', 'CARTON', 'ITEM'.
I_detail_rec: record which is in error. May be a rib_receipt_rec (RECEIPT or BOL level), rib_receiptdtl_rec (ITEM level), or rib_receiptcartondtl_rec (CARTON level). This value will be cast based on I_error_level.
This function creates a new error record based on the error level passed in (casting the I_error_object appropriately). If the error level is RECEIPT or BOL, then a rib_receipt_rec is created. If the error level is CARTON, a rib_receiptcartondtl_rec is created. If error level is ITEM, a rib_receiptdtl_rec is created. After creating this error record, it is added to the table of error records.
RMSSUB_RECEIPT_ERROR.END_RECEIPT
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE)
This function is called from RMSSUB_STKORD_RECEIPT.CONSUME when all details of a receipt have been processed. It takes all of the error records for this receipt and groups them according to the type of error. It then creates an error object for each error type, adding detail nodes for each error record. When this is finished, it adds all of the error records to the error table.
RMSSUB_RECEIPT_ERROR.FINISH
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
O_rib_error_tbl OUT RIB_ERROR_TBL)
If any errors exist on the package level error table then the error table is copied into the output parameter (O_rib_error_tbl), which in turn gets passed out to the RIB for further processing. This function is called in the 'finish' section of the RMSSUB_STKORD_RECEIPT.CONSUME function.
Filename: stkordrcvs/b.pls
STOCK_ORDER_RCV_SQL.TSF_BOL_CARTON (O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE, I_appt IN APPT_HEAD.APPT%TYPE, I_shipment IN SHIPMENT.SHIPMENT%TYPE, I_to_loc IN SHIPMENT.TO_LOC%TYPE, I_bol_no IN SHIPMENT.BOL_NO%TYPE, I_receipt_no IN APPT_DETAIL.RECEIPT_NO%TYPE, I_disposition IN INV_STATUS_CODES.INV_STATUS_CODE%TYPE, I_tran_date IN PERIOD.VDATE%TYPE, I_item_table IN ITEM_TAB, I_qty_expected_table IN QTY_TAB, I_weight IN ITEM_LOC_SOH.AVERAGE_WEIGHT%TYPE, I_weight_uom IN UOM_CLASS.UOM%TYPE, I_inv_status_table IN INV_STATUS_TAB, I_carton_table IN CARTON_TAB, I_distro_no_table IN DISTRO_NO_TAB, I_tampered_ind_table IN TAMPERED_IND_TAB, I_wrong_store_ind IN VARCHAR2, I_wrong_store IN SHIPMENT.TO_LOC%TYPE)
This function performs the BOL or carton level receiving for a transfer. It does the following:
Update shipment to received status along with the received date.
For each item on the SHIPSKU, builds an API record for transferring the item. An orderable but non-sellable and non-inventory item cannot be transferred. The message contains physical locations, but a transfer created in RMS (non-'EG' type) contains virtual locations only. The physical locations are converted to virtual locations if necessary.
Because an externally generated transfer (type 'EG') holds physical locations on TSFHEAD, and physical warehouses do not have transfer entities, this API does not support the receiving of an externally generated warehouse to warehouse transfer when system option INTERCOMPANY_TSF_IND is 'Y'. However, it does allow store to warehouse 'EG' transfer, because it is assumed that store is sending merchandise to the virtual warehouse within the same channel, hence the same transfer entity.
When receiving a transfer to a finisher location, all stock will be received into the available bucket regardless of the inventory disposition on the message.
When system option WRONG_ST_RECEIPT is 'Y', stock can be received at a store not originally intended. Inventory and stock ledger is adjusted for both the intended and the actual receiving store.
The received quantity on TSFDETAIL is updated. If it is a wrong store receiving, the reconciled quantity on TSFDETAIL is updated.
The received quantity and received weight on SHIPSKU are updated. If SHIPSKU is not found, a new receipt is created.
For an 'EG' type of transfer, the received quantity is distributed among the virtual locations of the physical location based on SHIPMENT_INV_FLOW, and the received quantity on SHIPMENT_INV_FLOW is updated.
For an 'MRT' type of transfer, the received quantity on MRT_ITEM_LOC is updated.
The table APPT_DETAIL is updated if an appointment exists for the transfer detail; otherwise, a record is inserted into DOC_CLOSE_QUEUE.
A call to DETAIL_PROCESSING to perform the bulk of the transfer receiving logic, including moving inventory from the in transit to the stock on bucket for the receiving location is made. For overage receiving, the stock on hand is adjusted for both the sending and receiving locations, the av_cost for the receiving location is adjusted and records are written to the stock ledger.
STOCK_ORDER_RCV_SQL.TSF_LINE_ITEM
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_loc IN ITEM_LOC.LOC%TYPE,
I_item IN ITEM_MASTER.ITEM%TYPE,
I_qty IN TRAN_DATA.UNITS%TYPE,
I_weight IN ITEM_LOC_SOH.AVERAGE_WEIGHT%TYPE,
I_weight_uom IN UOM_CLASS.UOM%TYPE,
I_transaction_type IN VARCHAR2,
I_tran_date IN PERIOD.VDATE%TYPE,
I_receipt_number IN APPT_DETAIL.RECEIPT_NO%TYPE,
I_bol_no IN SHIPMENT.BOL_NO%TYPE,
I_appt IN APPT_HEAD.APPT%TYPE,
I_carton IN SHIPSKU.CARTON%TYPE,
I_distro_type IN VARCHAR2,
I_distro_number IN TSFHEAD.TSF_NO%TYPE,
I_disp IN INV_STATUS_CODES.INV_STATUS_CODE%TYPE,
I_tampered_ind IN SHIPSKU.TAMPERED_IND%TYPE,
I_dummy_carton_ind IN SYSTEM_OPTIONS.DUMMY_CARTON_IND%TYPE)
Similar to TSF_BOL_CARTON, this function performs transfer receiving for one line item. In addition, if the item is indicated as a dummy carton on the message, it writes staging records to the DUMMY_CARTON_STAGE table. The actual matching and receiving of dummy carton transfers is performed during the batch cycle via dummyctn.pc.
STOCK_ORDER_RCV_SQL.ALLOC_BOL_CARTON (O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE, I_appt IN APPT_HEAD.APPT%TYPE, I_shipment IN SHIPMENT.SHIPMENT%TYPE, I_to_loc IN SHIPMENT.TO_LOC%TYPE, I_bol_no IN SHIPMENT.BOL_NO%TYPE, I_receipt_no IN APPT_DETAIL.RECEIPT_NO%TYPE, I_disposition IN INV_STATUS_CODES.INV_STATUS_CODE%TYPE, I_tran_date IN PERIOD.VDATE%TYPE, I_item_table IN ITEM_TAB, I_qty_expected_table IN QTY_TAB, I_weight IN ITEM_LOC_SOH.AVERAGE_WEIGHT%TYPE, I_weight_uom IN UOM_CLASS.UOM%TYPE, I_inv_status_table IN INV_STATUS_TAB, I_carton_table IN CARTON_TAB, I_distro_no_table IN DISTRO_NO_TAB, I_tampered_ind_table IN TAMPERED_IND_TAB, I_wrong_store_ind IN VARCHAR2, I_wrong_store IN SHIPMENT.TO_LOC%TYPE)
This function performs the BOL or carton level receiving for an allocation. It does the following:
Updates the shipment to received status along with the received date.
For each item on the SHIPSKU, builds an API record for allocating the item. An orderable but non-sellable and non-inventory item cannot be allocated.
Validates that item is on the allocation.
When system option WRONG_ST_RECEIPT is 'Y', stock can be received at a store not originally intended. Inventory and stock ledger are adjusted for both the intended and the actual receiving store.
Validates that ALLOC_DETAIL exists. Updates received quantity on ALLOC_DETAIL. If it is a wrong store receiving, updates the reconciled quantity on ALLOC_DETAIL.
Updates received quantity and received weight on SHIPSKU. If SHIPSKU is not found, creates a new receipt for that.
Updates APPT_DETAIL if appointment exists for the allocation detail; otherwise, inserts into DOC_CLOSE_QUEUE.
Calls DETAIL_PROCESSING to perform the bulk of the allocation receiving logic, including moving inventory from the in transit to the stock on bucket for the receiving location. For overage receiving, adjusts stock on hand for both the sending and receiving locations, adjusts av_cost for the receiving location and writes stock ledger.
STOCK_ORDER_RCV_SQL.ALLOC_LINE_ITEM (O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE, I_loc IN ITEM_LOC.LOC%TYPE, I_item IN ITEM_MASTER.ITEM%TYPE, I_qty IN TRAN_DATA.UNITS%TYPE, I_weight IN ITEM_LOC_SOH.AVERAGE_WEIGHT%TYPE, I_weight_uom IN UOM_CLASS.UOM%TYPE, I_transaction_type IN VARCHAR2, I_tran_date IN PERIOD.VDATE%TYPE, I_receipt_number IN APPT_DETAIL.RECEIPT_NO%TYPE, I_bol_no IN SHIPMENT.BOL_NO%TYPE, I_appt IN APPT_HEAD.APPT%TYPE, I_carton IN SHIPSKU.CARTON%TYPE, I_distro_type IN VARCHAR2, I_distro_number IN ALLOC_HEADER.ALLOC_NO%TYPE, I_disp IN INV_STATUS_CODES.INV_STATUS_CODE%TYPE, I_tampered_ind IN SHIPSKU.TAMPERED_IND%TYPE, I_dummy_carton_ind IN SYSTEM_OPTIONS.DUMMY_CARTON_IND%TYPE)
Similar to ALLOC_BOL_CARTON, this function performs allocation receiving for one line item. In addition, if the item is indicated as a dummy carton on the message, it writes staging records to the DUMMY_CARTON_STAGE table. The actual matching and receiving of dummy carton allocations is performed during the batch cycle via dummyctn.pc.
STOCK_ORDER_RCV_SQL.INIT_TSF_ALLOC_GROUP
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE)
For performance reasons, bulk processing is used for stock order receiving. This function initializes global variables for bulk processing and populates system options.
STOCK_ORDER_RCV_SQL.FINISH_TSF_ALLOC_GROUP
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE)
For performance reasons, bulk processing is used for stock order receiving. This function bulk updates APPT_DETAIL, bulk updates DOC_CLOSE_QUEUE and TRAN_DATA.
Filename: ordrcvs/b.pls
ORDER_RCV_SQL.PO_LINE_ITEM
(O_error_message IN OUT rtk_errors.rtk_text%TYPE,
I_loc IN item_loc.loc%TYPE,
I_order_no IN ordhead.order_no%TYPE,
I_item IN item_master.item%TYPE,
I_qty IN tran_data.units%TYPE,
I_tran_type IN VARCHAR2,
I_tran_date IN DATE,
I_receipt_number IN appt_detail.receipt_no%TYPE,
I_asn IN shipment.asn%TYPE,
I_appt IN appt_head.appt%TYPE,
I_carton IN shipsku.carton%TYPE,
I_distro_type IN VARCHAR2,
I_distro_number IN alloc_header.alloc_no%TYPE,
I_destination IN alloc_detail.to_loc%TYPE,
I_disp IN inv_status_codes.inv_status_code%TYPE,
I_unit_cost IN ordloc.unit_cost%TYPE,
I_shipped_qty IN shipsku.qty_expected%TYPE,
I_weight IN item_loc_soh.average_weight%TYPE,
I_weight_uom IN UOM_CLASS.UOM%TYPE,
I_online_ind IN VARCHAR2)
This function is called once for each PO line item received. It validates input and calls RCV_LINE_ITEM for each item/location.
If the PO received is a cross-dock PO to a warehouse, an allocation must exist for the PO/allocation/item/warehouse combination. The message will contain a physical warehouse, whereas ALLOC_HEADER will contain a virtual warehouse.
If the item is received to a physical warehouse, then this function calls the distribution logic to determine each item/virtual warehouse/quantity, and calls RCV_LINE_ITEM for each of these combinations.
If a simple pack catch weight item is received, it also updates SHIPSKU weight received and weight received UOM.
ORDER_RCV_SQL.RCV_LINE_ITEM (O_error_message IN OUT rtk_errors.rtk_text%TYPE, I_phy_loc IN item_loc.loc%TYPE, I_loc IN item_loc.loc%TYPE, I_loc_type IN item_loc.loc_type%TYPE, I_order_no IN ordhead.order_no%TYPE, I_item IN item_master.item%TYPE, I_qty IN tran_data.units%TYPE, I_tran_type IN VARCHAR2, I_tran_date IN DATE, I_receipt_number IN appt_detail.receipt_no%TYPE, I_asn IN shipment.asn%TYPE, I_appt IN appt_head.appt%TYPE, I_carton IN shipsku.carton%TYPE, I_distro_type IN VARCHAR2, I_distro_number IN tsfhead.tsf_no%TYPE, I_destination IN alloc_detail.to_loc%TYPE, I_disp IN inv_status_codes.inv_status_code%TYPE, I_unit_cost IN ordloc.unit_cost%TYPE, I_shipped_qty IN shipsku.qty_expected%TYPE, I_weight IN item_loc_soh.average_weight%TYPE, I_weight_uom IN UOM_CLASS.UOM%TYPE, I_online_ind IN VARCHAR2)
This function is called for each item/location combination. It validates input and performs PO receiving logic for each item.
Receiving (tran_type = 'R') must be against a valid approved order; adjustment (tran_type = 'A') must be against a valid approved or closed order.
Item on the message may be a referential item. Get its transaction level item.
An orderable, but non-sellable and non-inventory item cannot be received.
For a deposit content item, its container item is also received and added to the order if not already on the order.
Inserts or updates ORDLOC for quantity received.
Updates APPT_DETAIL if appointment exists; otherwise, insert into DOC_CLOSE_QUEUE.
Inserts or updates SHIPMENT to received status.
Inserts or updates SHIPSKU for received quantity. If SHIPSKU.QTY_RECEIVED is updated, also updates INVC_MATCH_WKSHT.MATCH_TO_QTY.
If no deals exist for this order/item/loc, then INVC_SQL.UPDATE_INVOICE is called to perform invoice matching logic.
Updates average cost and stock on hand for the stock received. If a pack is on the order, the updates are performed for the component items.
Writes TRAN_DATA records (tran code 20) for the stock received. If a pack is on the order, TRAN_DATA records are written for the component items.
Writes SUP_DATA.
Request tickets to be printed if location is a store.
If this is an adjustment to a closed order, sets the status back to 'A'pproved.
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) |
|---|---|---|
| receiptcre | Receipt Create Message | ReceiptDesc.xsd |
| receiptordcre | Receipt Create Message | ReceiptDesc.xsd |
| receiptmod | Receipt Modify (Adjustment) Message | ReceiptDesc.xsd |
The stock order subscription process supports the break-to-sell functionality. Transfers, allocations and shipments in RMS will only contain break to sell orderable items. Inventory adjustment and stock ledger will be performed on the orderable only, not the sellable.
The stock order and order subscription process supports the catch weight functionality. It is assumed that a break-to-sell sellable item cannot be a simple pack catch weight item.
An externally generated transfer will contain physical locations. When system options INTERCOMPANY_TSF_IND = 'Y', the stock order receiving process currently does not support the receiving of an externally generated transfer that involves a warehouse to warehouse transfer. This is because a physical location does not have transfer entities.
Wrong store receiving is not supported for franchise transactions.
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| TSFHEAD | Yes | No | Yes | No |
| TSFDETAIL | Yes | Yes | Yes | No |
| ALLOC_HEADER | Yes | No | Yes | No |
| ALLOC_DETAIL | Yes | No | Yes | No |
| ORDHEAD | Yes | No | Yes | No |
| ORDSKU | Yes | Yes | Yes | No |
| ORDLOC | Yes | Yes | Yes | No |
| SHIPMENT | Yes | Yes | Yes | No |
| SHIPSKU | Yes | Yes | Yes | No |
| TRAN_DATA | No | Yes | No | No |
| SUP_DATA | No | Yes | No | No |
| ITEM_LOC_SOH | Yes | Yes | Yes | No |
| ITEM_LOC | Yes | Yes | No | No |
| ITEM_ZONE_PRICE | Yes | Yes | No | No |
| PRICE_HIST | No | Yes | No | No |
| SHIPITEM_INV_FLOW | Yes | Yes | Yes | No |
| MRT_ITEM_LOC | Yes | No | Yes | No |
| APPT_DETAIL | Yes | No | Yes | No |
| DOC_CLOSE_QUEUE | No | Yes | No | No |
| DUMMY_CARTON_STAGE | No | Yes | No | No |
| ALC_HEAD | Yes | Yes | Yes | No |
| CONTRACT_HEADER | Yes | No | Yes | No |
| CONTRACT_DETAIL | Yes | No | Yes | No |
| INVC_MATCH_WKSHT | Yes | No | Yes | No |
| INVC_HEAD | Yes | Yes | Yes | No |
| INVC_DETAIL | Yes | Yes | Yes | No |
| INVC_TOLERANCE | Yes | Yes | Yes | Yes |
| INVC_XREF | Yes | Yes | No | No |
| INVC_MATCH_VAT | Yes | Yes | Yes | No |
| TERMS | Yes | No | No | No |
| SUPS | Yes | No | No | No |
| VAT_REGION | Yes | No | No | No |
| DEPS | Yes | No | No | No |
| WEEK_DATA | Yes | No | No | No |
| MONTH_DATA | Yes | No | No | No |
| ITEM_SUPP_COUNTRY | Yes | No | No | No |
| ITEM_SUPP_COUNTRY_LOC | Yes | Yes | No | No |
| ITEM_SUPP_COUNTRY_DIM | Yes | No | No | No |
| UOM_CLASS | Yes | No | No | No |
| NWP | Yes | Yes | Yes | No |
| STORE | Yes | No | No | No |
| WH | Yes | No | No | No |
| ITEM_MASTER | Yes | No | No | No |
| V_PACKSKU_QTY | Yes | No | No | No |
| ITEM_XFORM_HEAD | Yes | No | No | No |
| ITEM_XFORM_DETAIL | Yes | No | No | No |
| CURRENCIES | Yes | No | No | No |
| CURRENCY_RATES | Yes | No | No | No |
| PERIOD | Yes | No | No | No |
| SYSTEM_OPTIONS | Yes | No | No | No |
This section describes the RTV subscription API.
RMS subscribes to return-to-vendor (RTV) messages from the RIB. When an RTV is shipped out from a warehouse or store, the RTV information is sent from the external system (such as RWMS and SIM) to the RIB. RMS subscribes to the RTV information as published from the RIB and places the information onto RMS tables, depending on the validity of the records enclosed within the message.
The RTV message can be processed as a flat message when the header description contains information for one RTV item. The message can also be processed as a hierarchical message when the detail node is populated with one or more RTV items. RMS primarily uses these messages to update inventory quantities and stock ledger values.
This is a layer of code which enables decoupling of localization logic that is only required for certain country-specific configuration. This layer affects the RIB API flows including RTV subscription. This allows RMS to be installed without requiring customers to install or use this localization functionality, where not required.
Filename: rmssub_rtvs/b.pls
RMSSUB_RTV.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure initially ensures that the passed in message type is a valid type for RTV messages. The valid message types for RTV messages are listed in the Message XSD section below.
If the message type is invalid, a status of "E" is returned to the external system along with an appropriate error message informing the external system that the message type is invalid.
If the message type is valid, the generic RIB_OBJECT is downcast to the actual object using the Oracle treat function. If the downcast fails, a status of "E" is returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume parses the message, verifies that the message passes all of RMS's business validation and persists the information to the RMS database. It does this by calling CONSUME_RTV.
RMSSUB_RTV.CONSUME_RTV
O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2,
I_check_l10n_ind IN VARCHAR2)
Performs localization check. If localized, invoke RFM's logic through L10N_SQL decoupling layer for procedure key 'CONSUME_RTV'. If not localized, call CONSUME_RTV for normal processing.
RMSSUB_RTV.CONSUME_RTV
(O_error_message IN OUT VARCHAR2,
IO_L10N_RIB_REC IN OUT L10N_OBJ)
Public function to call RMSSUB_RTV.CONSUME_RTV_CORE.
RMSSUB_RTV.CONSUME_RTV_CORE
(O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This function contains the main processing logic:
If the downcast is successful, then consume calls PARSE_RTV to parse the RTV message and PROCESS_RTV to perform business validation and desired functionality. Any time the message fails business validation, a status of "E" is returned to the external system along with an appropriate error message.
Once the message has been successfully processed, a success status, "S", is returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
PARSE_RTV
This function parses the RIB_OBJECT and builds an API rtv_record for processing.
Gross cost can be included in the detail RIB_RTVDtl_REC. If the gross cost is present, then it is stored as unit_cost and unit_cost is stored as extended_base_cost.
Jurisdiction code also is determined based on supplier.
PROCESS_RTV
This function calls RTV_SQL.APPLY_PROCESS to perform all business validation and desired functionality associated with a RTV message.
For break to sell items, if a sellable only item is on the message, CHECK_ITEMS and GET_ORDERABLE_ITEMS are called to convert the sellable item(s) to the corresponding orderable item(s). The orderable items are inserted or updated on the tables affected by an RTV.
The RTV_SQL.APPLY_PROCESS is called for each of the orderable items and each of the regular items.
CHECK_ITEMS
This function separates the item details on the message into two groups: one contains sellable only items and one contains regular items.
GET_ORDERABLE_ITEMS
This function builds a collection of orderable items based on the sellable items. It calls ITEM_XFORM_SQL.RTV_ORDERABLE_ITEM_INFO to distribute the sellable quantities among the orderable items.
Filename: rtvs/b.pls
RTV_SQL.APPLY_PROCESS
This function performs business validation and desired functionality for a RTV message. It includes the following:
Verifies that an orderable but non-sellable and non-inventory item cannot be an RTV item.
Verifies that an RTV item must be a tran-level or above tran-level item.
If the RTV item is a simple pack catch weight item, verifies that weight and weight unit of measure (UOM) are either both defined or both NULL, and weight UOM is in the MASS UOM class.
Verifies that the item supplier relation exists.
Verifies that the location is a valid store or warehouse.
Verifies that the item/loc relation exists.
If returning a pack to a warehouse, the pack must be received as pack at the warehouse.
Verifies that from disposition is a valid inventory status code (on INV_STATUS_CODES).
Verifies that the reason code is a valid RTV reason code (code type 'RTVR' on CODE_DETAIL).
For an externally generated RTV, if the location is a warehouse, then physical location is on the message. RTV quantity will be distributed among the virtual locations of the physical location.
Checks for the existence of RTV in RTV_HEAD based on: a) rtv_order_no; b) ext_ref_no and location. An RTV is updated if it already exists and inserted if not. The RTV is marked as shipped.
Checks for the existence of RTV item in RTV_DETAIL based on: rtv_order_no, item, reason and inventory status. An RTV_DETAIL is updated if it already exists and inserted if not.
If the RTV item is a content item of a deposit item, RTV_DETAIL is inserted or updated for the associated container item.
Determines RTV unit cost as the following:
Uses the unit cost on the RTV message if defined. It is in location currency. Otherwise.
Uses RTV_DETAIL.unit_cost if exists. It is in supplier currency. Otherwise.
Uses the last receipt cost if exists. It is in location currency. Otherwise.
Uses item's WAC at the location. It is in location currency.
The unit cost is used to evaluate the cost of the RTV goods. The cost values on RTV tables are written in supplier currency, but all TRAN_DATA records are written in location currency.
If the RTV item is a simple pack catch weight item, the total RTV cost is based on weight.
Updates the following stock buckets on ITEM_LOC_SOH: RTV_QTY, STOCK_ON_HAND, PACK_COMP_SOH. For a simple pack catch weight item at the warehouse, also updates average weight.
Writes the following TRAN_DATA records:
24 - for RTV. It writes units, total_cost and total_retail.
71/72 - for cost variance between item's WAC at the location and RTV unit cost. It writes units and total_cost.
65 - for restocking fees. For a non-MRT type of RTV, the restocking fee is written for the RTV location. For an MRT type of RTV, the restocking fee is distributed among the MRT locations. It writes units and total_cost.
22 - for stock adjustment, if stock counting has already happened at the store for the item.
If the RTV item is a pack, TRAN_DATA is written for component items. If the RTV location is a physical warehouse, TRAN_DATA is written for virtual locations. TRAN_DATA total cost and total retail are always written in location currency.
Creates or updates INVC_HEAD and INVC_DETAIL for the RTV.
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) |
|---|---|---|
| rtvcre | RTV Create Message | RTVDesc.xsd |
Catch weight functionality is not applied to the following areas:
Any of the retail calculations (including total_retail on TRAN_DATA and retail markup/markdown).
The total amount on SUP_DATA.
Open to buy buckets.
When a catch weight component item's standard UOM is a MASS UOM, TRAN_DATA.units is based on V_PACKSKU_QTY.qty instead of the actual weight.
MRT RTV can only be created in RMS. Therefore it will only contain virtual locations. Physical location distribution logic does not apply to MRT RTVs.
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| RTV_HEAD | Yes | Yes | Yes | No |
| RTV_DETAIL | Yes | Yes | Yes | No |
| ITEM_LOC_SOH | Yes | No | Yes | No |
| TRAN_DATA | No | Yes | No | No |
| INV_STATUS_CODES | Yes | No | No | No |
| CODE_DETAIL | Yes | No | No | No |
| ITEM_MASTER | Yes | No | No | No |
| ITEM_SUPPLIER | Yes | No | No | No |
| ITEM_SUPP_COUNTRY | Yes | No | No | No |
| ITEM_LOC | Yes | No | No | No |
| STORE | Yes | No | No | No |
| WH | Yes | No | No | No |
| SHIPMENT | Yes | No | No | No |
| SHIPSKU | Yes | No | No | No |
| DEPS | Yes | No | No | No |
| SUPS | Yes | No | No | No |
| ADDR | Yes | No | No | No |
| UOM_CLASS | Yes | No | No | No |
| V_PACKSKU_QTY | Yes | No | No | No |
| MRT_ITEM_LOC | Yes | No | No | No |
| ITEM_XFORM_HEAD | Yes | No | No | No |
| ITEM_XFORM_DETAIL | Yes | No | No | No |
| INVC_HEAD | Yes | Yes | Yes | Yes |
| INVC_DETAIL | Yes | Yes | No | Yes |
| INVC_NON_MERCH | No | Yes | No | Yes |
| INVC_MERCH_VAT | Yes | Yes | Yes | Yes |
| INVC_DETAIL_VAT | Yes | No | No | Yes |
| INVC_MATCH_QUEUE | Yes | No | No | Yes |
| INVC_DISCOUNT | Yes | No | No | Yes |
| INVC_TOLERANCE | Yes | No | No | Yes |
| ORDLOC_INVC_COST | Yes | No | Yes | No |
| NON_MERCH_CODE_HEAD | Yes | No | No | No |
This section describes the stock order status subscription API.
A stock order is an outbound merchandise request from a warehouse or store. In RMS, a stock order takes the form of either a transfer or allocation. RMS subscribes to stock order status messages from the RIB, published by an external application, such as a store system (SIM, for example) or a warehouse management system (RWMS, for example) to communicate the status of a specific stock order. This communication provides for the synchronization of data between RWMS/SIM and RMS. The information from RWMS and SIM has only one level, in other words no detail records. RMS uses the data contained in the messages to:
Update the following tables when the status of the 'distro' changes at the store or warehouse:
ALLOC_DETAIL
ITEM_LOC_SOH
TSFDETAIL
To determine when the store or warehouse is processing a transfer or allocation. In-process transfers or allocations cannot be edited and are determined by the initial and final quantities to be filled by the external system.
When RMS is integrated with an external Order Management System (OMS), OMS will subscribe to SOStatus messages published from SIM and WMS when a store or warehouse cannot fulfill a customer order. OMS, in turn, sends a customer order cancellation request to RMS. In order to prevent duplicate processing for the same cancellation message, this subscription API will ignore 'no inventory' statuses received from RWMS and SIM for a customer order transfer.
The following tables describe the stock order statuses for both transfers and allocation document types and what occurs in RMS after receiving the respective status. Document_types of 'T', 'D' and 'S' indicate if the transfer is initiated in RMS, a warehouse system, or a store system respectively. Statuses other than listed below are ignored by RMS.
| Stock order status received in message on a transfer where 'distro_document_type' = 'T', 'D', 'S') | What RMS does |
|---|---|
| SI (Stock Increased)
When SIM or RWMS publishes a message on a transfer with a status of SI (Stock Increased), RMS will insert or update TSFDETAIL for the transfer/item combination. |
Insert or increase tsfdetail.tsf_qty
Increase item_loc_soh.tsf_reserved_qty for the from location and item_loc_soh.tsf_expected_qty for the to location |
| SD (Stock Decreased)
When SIM or RWMS publishes a message on a transfer with a status of SD (Stock Decreased), RMS will delete or update TSFDETAIL for the transfer/item combination. |
Delete or decrease tsfdetail.tsf_qty.Decrease item_loc_soh.tsf_reserved_qty for the from location and item_loc_soh.tsf_expected_qty for the to location |
| DS (Details Selected)
When RWMS publishes a message on a transfer with a status of DS (Details Selected), RMS will increase the selected quantity on TSFDETAIL for the transfer/item combination. |
Increase tsfdetail.selected_qty |
| DU (Details Un-selected)
When RWMS publishes a message on a transfer with a status of DU (Details Un-Selected), RMS decreases the selected quantity on TSFDETAIL for the transfer/item combination. |
Decrease tsfdetail.selected_qty |
| NI (WMS Line Cancellation)
When RWMS publishes a message on a transfer with a status of NI (No Inventory - WMS Line Cancellation), RMS will decrease the selected quantity by the quantity on the message. RMS will also increase the cancelled quantity, decrease the transfer quantity, decrease the reserved quantity* for the from location, and decrease the expected quantity* for the to location by the lesser of 1). the quantity on the message; 2). the transfer quantity - shipped quantity. *If the transfer status is not Closed. |
Decrease tsfdetail.selected_qty and tsfdetail.tsf_qty, increase tsfdetail.cancelled_qty, decrease item_loc_soh.tsf_reserved_qty for the from location and item_loc_soh.tsf_expected_qty for the from location
Put transfer on doc_close_queue |
| PP (Distributed)
When RWMS publishes a message on a transfer with a status of PP (Pending Pick - Distributed), RMS will decrease the selected quantity and increase the distro quantity. |
Decrease tsfdetail.selected_qty, increase tsfdetail.distro_qty |
| PU (Un-Distribute)
When RWMS publishes a message on a transfer with a status of PU (Un-Distribute), RMS will decrease the distributed qty. |
Decrease tsfdetail.distro_qty |
| RS (Return To Stock)
When RWMS published a message on a transfer with a status of RS (Return To Stock), RMS will decrease the distributed qty. RMS will also increase the cancelled quantity, decrease the transfer quantity, decrease the reserved quantity* for the from location, and decrease the expected quantity* for the to location by the lesser of 1). the quantity on the message; 2). the transfer quantity - shipped quantity. *If the transfer status is not Closed. |
Decrease tsfdetail.distro_qty and tsfdetail.tsf_qty, increase tsfdetail.cancelled_qty, decrease item_loc_soh.tsf_reserved_qty for the from location and item_loc_soh.tsf_expected_qty for the from location |
| EX (Expired)
When RWMS publishes a message on a transfer with a status of EX (Expired), RMS will increase the cancelled quantity, decrease the transfer quantity, decrease the reserved quantity* for the from location, and decrease the expected quantity* for the to location by the lesser of 1). the quantity on the message; 2). the transfer quantity - shipped quantity. *If the transfer status is not Closed. |
Increase tsfdetail.cancelled_qty, decrease tsfdetail.tsf_qty, item_loc_soh.tsf_reserved_qty for the from location and item_loc_soh.tsf_expected_qty for the To location
Put transfer on doc_close_queue |
| SR (Store Reassign)
When RWMS publishes a message on a transfer with a status of SR (Store Reassign) the quantity can be either positive or negative. In either case it will be added to the distro_qty (adding a negative will have the same effect as subtracting it). |
Add to tsfdetail.distro_qty |
| SI (Stock Increased)
When SIM or RWMS publishes a message on an allocation with a status of SI (Stock Increased), RMS will increase ALLOC_DETAIL for the allocation/item combination. |
Increase alloc_detail.qty_allocated
Increase item_loc_soh.tsf_reserved_qty for the from location and item_loc_soh.tsf_expected_qty for the To location |
| SD (Stock Decreased)
When SIM or RWMS publishes a message on an allocation with a status of SD (Stock Decreased), RMS will decrease ALLOC_DETAIL for the allocation/item combination. |
Decrease alloc_detail.qty_allocated.
Decrease item_loc_soh.tsf_reserved_qty for the from location and item_loc_soh.tsf_expected_qty for the To location |
| DS (Details Selected)
When RWMS publishes a message on an allocation with a status of DS (Details Selected), RMS will increase the selected quantity on alloc_detail for the allocation/item/location combination. |
Increase alloc_detail.selected_qty |
| DU (Details Un-Selected)
When RWMS publishes a message on an allocation with a status of DU (Details Un-Selected), RMS will decrease the selected quantity on alloc_detail for the allocation/item combination. |
Decrease alloc_detail.selected_qty |
| NI (WMS Line Cancellation)
When RWMS publishes a message on an allocation with a status of NI (No Inventory - WMS Line Cancellation), RMS will decrease the selected quantity by the quantity on the message. RMS will also increase the cancelled quantity, decrease the allocated quantity, decrease the reserved quantity* for the from location, and decrease the expected quantity* for the to location by the lesser of 1). the quantity on the message; 2). the allocation quantity - shipped quantity. *If the allocation status is not Closed and the allocation is a stand alone allocation. |
Decrease alloc_detail.qty_ selected and alloc_detail.qty_allocated, increase alloc_detail.cancelled_qty, decrease item_loc_soh.tsf_reserved_qty for the from location and item_loc_soh.tsf_expected_qty for the to location
Put allocation on doc_close_queue |
| PP (Distributed)
When RWMS publishes a message on an allocation with a status of PP (Pending Pick - Distributed), RMS will decrement the selected quantity and increment the distro quantity |
Decrease alloc_detail.qty_selecteded, increase alloc_detail.qty_distro |
| PU (Un-Distribute)
When RWMS publishes a message on an allocation with a status of PU (Un-Distribute), RMS will decrease the distributed qty. |
Decrease alloc_detail.qty_distro |
| RS (Return to Stock)
When RWMS published a message on an allocation with a status of RS (Return to Stock), RMS will decrease the distributed qty. RMS will also increase the cancelled quantity, decrease the allocated quantity, decrease the reserved quantity* for the from location, and decrease the expected quantity* for the to location by the lesser of 1). the quantity on the message; 2). the allocation quantity - shipped quantity. *If the allocation status is not Closed and the allocation is a stand alone allocation. |
Decrease alloc_detail.qty_distro and alloc_detail.qty_allocated, increase alloc_detail.cancelled_qty, decrease item_loc_soh.tsf_reserved_qty for the from location and item_loc_soh.tsf_expected_qty for the to location |
| EX (Expired)
When RWMS publishes a message on an allocation with a status of EX (Expired), RMS will increase the cancelled quantity, decrease the allocated quantity, decrease the reserved quantity* for the from location, and decrease the expected quantity* for the to location by the lesser of 1). the quantity on the message; 2). the transfer quantity - shipped quantity. *If the allocation status is not Closed and the allocation is a stand alone allocation. |
Decrease alloc_detail.qty_allocated, increase alloc_detail.qty_cancelled, decrease item_loc_soh.tsf_reserved_qty for the from location and item_loc_soh.tsf_expected_qty for the to location
Put allocation on doc_close_queue |
| SR (Store Reassign)
When RWMS publishes a message on an allocation with a status of SR (Store Reassign) the quantity can be either positive or negative. In either case, it will be added to the qty_distro (adding a negative will have the same affect as subtracting it). |
Add to alloc_detail.qty_distro |
Whenever the from location is a warehouse, a check if the item is a pack or an each is performed. If the item is not a pack item, no special considerations are necessary. For each warehouse-pack item combination, the receive_as_type on ITEM_LOC is checked to determine if it is received into the warehouse as a pack or a component item. If it is received as an each, ITEM_LOC_SOH for the component item is updated. If it is received as a pack, ITEM_LOC_SOH for the pack item and the component item are updated.
Filename: rmssub_sostatuss/b.pls
CONSUME
RMSSUB_SOSTATUS.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN "RIB_SOStatusDesc_REC",
I_message_type IN VARCHAR2);
This procedure accepts Stock Order Status information in the form of an Oracle Object data type from the RIB (I_message) and a message type of 'sostatuscre'.
The procedure first calls the RESET function to initialize internal variables. The procedure then extracts the values from the oracle object. These are then passed on to private internal functions which validate the values and place them on the database depending upon the success of the validation.
BUILD_XTSFDESC
This function builds a RIB_XTsfDesc_REC object to be passed in the RMSSUB_XTSF.CONSUME function.
HANDLE_ERRORS
HANDLE_ERRORS(O_status IN OUT VARCHAR2,
IO_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cause IN VARCHAR2,
I_program IN VARCHAR2);
If an error occurs in this procedure or any of the internal functions, this procedure places a call to HANDLE_ERRORS in order to parse a complete error message and pass back a status to the RIB.
This function is used to put error handling in one place in order to make future error handling enhancements easier to implement. The function consists of a call to API_LIBRARY.HANDLE_ERRORS. API_LIBRARY.HANDLE_ERRORS accepts a program name, the cause of the error and potentially an unparsed error message if one has been created through a call to SQL_LIB.CREATE_MESSAGE. The function uses these input variables to parse a complete error message and pass back a status, depending upon the message and error type, back up through the consume function and up to the RIB.
PARSE_SOS
This function first calls VALIDATE to check that the transfer or allocation from the oracle object exists in RMS. If the transfer or allocation exists, the function breaks down the message into its component parts and sends these parts into PROCESS_SOS. For customer order transfers, the customer order number and fulfill order number is also validated against the corresponding record in ORDCUST.
When RMS is integrated to OMS, this function skips processing for 'NI', 'EX', 'SI', 'SD', 'PP', 'PU' statuses received from RWMS and SIM for customer order transfers.
PROCESS_SOS
Based on the status sent from RWMS and SIM, quantity fields on either TSFDETAIL or ALLOC_DETAIL and ITEM_LOC_SOH are updated.
VALIDATE
Validates the distro is valid. A distro refers to either a transfer or an allocation.
UPDATE_TSF
Updates the record on TSFDETAIL, if the message is for a transfer.
UPDATE_ALLOC
Updates the record on ALLOC_DETAIL, if the message is for an allocation.
UPD_FROM_ITEM_LOC
Updates item_loc_soh.tsf_reserved_qty for the From Location. If the comp_level_upd indicator is 'Y' then it will also update the item_loc_soh.pack_comp_resv field for the item passed in.
UPD_TO_ITEM_LOC
Updates item_loc_soh.tsf_expected_qty for the To Location. If the comp_level_upd indicator is 'Y' then it will also update the item_loc_soh.pack_comp_exp field for the item passed in.
GET_RECEIVE_AS_TYPE
This function gets the Receive as type value from ITEM_LOC for the passed-in item and location combination.
POPULATE_DOC_CLOSE_QUEUE
This function is called to populate an array which holds stock order information that will be placed on the DOC_CLOSE_QUEUE table.
RESET
This function deletes any values that are currently held in the package's global variables.
DO_BULK
This function is used to do bulk inserts or updates of the ALLOC_DETAIL, TSFDETAIL, TSFHEAD and DOC_CLOSE_QUEUE tables. The tables are updated/inserted using the arrays that were built in the rest of the package.
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) |
|---|---|---|
| sostatuscre | Stock Order Status Create Message | SOStatusDesc.xsd |
One of the primary assumptions in the current API 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.
SOStatus supports transfers and allocations linked to a franchise order or return. For an existing transfer and allocation modified by a stock order status message, the quantity change is NOT reflected on the franchise order or return since the franchise order or return would have been approved already.
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| ITEM_LOC_SOH | Yes | Yes | Yes | No |
| ITEM_LOC | Yes | No | No | No |
| ALLOC_DETAIL | Yes | No | Yes | No |
| ALLOC_HEADER | Yes | No | No | No |
| TSFDETAIL | Yes | No | Yes | No |
| TSFHEAD | Yes | No | Yes | No |
| DOC_CLOSE_QUEUE | No | Yes | No | No |
| ORDCUST | Yes | No | No | No |
| SYSTEM_OPTIONS | Yes | No | No | No |
| V_PACKSKU_QTY | Yes | No | No | No |
| WF_ORDER_HEAD | Yes | Yes | No | No |
| WF_ORDER_DETAIL | No | Yes | No | No |
| WF_ORDER_EXP | No | Yes | No | No |
This section describes the stock count schedule subscription API.
Stock count schedule messages are published to the RIB by an integration subsystem, such as a store inventory management system, to communicate unit and value stock count schedules to RMS. RMS uses stock count schedule data to help synchronize the inventories of another application and RMS. The other application performs a physical inventory count and uploads the results, and RMS compares the discrepancies.
This API allows external systems to create, update, and delete stock counts within RMS. Only Unit and Value stock counts (stocktake_type = 'B') are subscribed by RMS at this time. Department, class and subclass can be null; if not provided a full count is presumed.
If the other application requires at year-end to consolidate annual and booking numbers, the annual count can be initiated by the other application and uploaded into RMS. RMS accepts the unit variances and processes these automatically. The financial values will need user input from the central office.
Filename: rmssub_stakeschedules/b.pls
CONSUME (O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2);
This package is used to subscribe to stock count schedule message, parse the details, and pass them into the stock schedule package.
If the message type is StkCountSchDel, validates before deleting the cycle count.
For other message types, business validations are performed before creating or updating the cycle count.
Once the message has been successfully processed, there is nothing more for the consume procedure to do. A success status, "S", should be returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
Filename: stake_schedules/b.pls
This package is used to validate stock schedule data and insert/update to the stock count tables.
VALIDATE_VALUES
Cannot delete a cycle count if it has been processed.
Cannot update a cycle count that has started or has been set to be deleted.
Cannot process anything if stock count is currently locked.
VALIDATE_HIERARCHY
Unit and Value stock counts at a warehouse must be at the department level only.
Validates department, class and subclass.
VALIDATE_LOCATION
Only stockholding (virtual) warehouses can be on a stock count.
PROCESS_PROD
Validates and creates a STAKE_PRODUCT record. No validation is done if the record is passed in for initial processing.
PROCESS_LOC
Validates and creates a STAKE_LOCATION record. No validation is done if the record is passed in for initial processing.
PROCESS_DEL
CREATE_SH_REC
Creates a record for STAKE_HEAD.
CREATE_SP_REC
Creates a STAKE_PRODUCT record.
DELETE_RECS
Deletes from STAKE_PRODUCT and STAKE_LOCATION tables.
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) |
|---|---|---|
| StkCountSchCre | Stock Count SCH Create Message | StkCountSchDesc.xsd |
| StkCountSchMod | Stock Count SCH Modify Message | StkCountSchDesc.xsd |
| StkCountSchDel | Stock Count SCH Delete Message | StkCountSchRef.xsd |
This section describes the store subscription API.
The Store Subscription API provides the ability to keep store data in RMS in sync with an external system, if RMS is not being used as the system of record for organizational hierarchy information. The store data handled by the API includes basic store data in addition to relationship data between stores and their location traits and walk-through stores.
When creating a new store in RMS, the API uses RMS store creation batch logic. When a store creation message is received, it is validated and placed onto a staging table STORE_ADD. The store creation in RMS reads from this table and creates the store in RMS in an asynchronous mode.
When updating an existing store in RMS, the API performs the update immediately upon message receipt.
The API also handles store delete messages. But, like the store creation message subscription process, stores will not actually be deleted from the system upon receipt of the message. After the data has been validated, the store is added to the DAILY_PURGE table for processing via a batch process.
By default, stores inherit the location traits of the district to which they belong. However, specific location traits can also be assigned at the store level. Using the incoming external data, the API will create or delete relationships between stores and existing location traits.
Walkthrough stores are used in RMS as part of the transfer reconciliation process and are used to indicate two or more stores that have a 'walk through' connection between them - on the sales floor and/or the backroom. Using the incoming external data, the API will create or delete these relationships with stores as well.
Location trait and walkthrough store data cannot be sent in on a store create message. The store create program must first process the store before it can have details attached to it.
Location trait and walkthrough store data must be processed separately as they each have their own distinct message types. These detail create messages will contain a snapshot of the store record.
|
Note: Location traits must already exist prior to being added to the store. |
The deletion of location trait and walkthrough store relationships will also be handled within this API. The detail delete messages must be processed separately because they each have their own distinct message types.
The RIB_XStoreDesc_REC message is modified to include RIB_CustFlexAttriVo_TBL message to enable the subscription of the custom flex attributes.
This section describes the package impact.
Filename: rmssub_xstores/b.pls
RMSSUB_XSTORE.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure will initially ensure that the passed in message type is a valid type for store messages. If the message type is invalid, a status of 'E' will be returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT will be downcast to the actual object using the Oracle's treat function. If the downcast fails, a status of 'E' will be returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume will verify that the message passes all of RMS's business validation. It does not actually perform any validation itself, instead, it will call the RMSSUB_XSTORE_VALIDATE.CHECK_MESSAGE function to determine whether the message is valid. If the message has failed RMS business validation, a status of 'E' will be returned to the external system along with the error message returned from the CHECK_MESSAGE function.
The package RMSSUB_XSTORE_CFA enables the subscription of the custom flex attributes. RMSSUB_XSTORE_CFA.CONSUME is called to process the custom flex attributes.
Once the message has passed RMS business validation, it can be persisted to the RMS database by calling RMSSUB_XSTORE_SQL.PERSIST_MESSAGE() function. If the database persistence fails, the function will return false. A status of 'E' should be returned to the external system along with the error message returned from the PERSIST_MESSAGE() function.
Once the message has been successfully persisted, a success status, 'S', should be returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
RMSSUB_XSTORE.HANDLE_ERROR() - This is the standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
Filename: rmssub_xstorevals/b.pls
RMSSUB_XSTORE_VALIDATE.CHECK_MESSAGE
(O_error_message IN OUT VARCHAR2,
O_store_rec OUT NOCOPY STORE_SQL.STORE_ROW_TYPE,
I_message IN RIB_XStoreDesc,
I_message_type IN VARCHAR2)
This function performs all business validation associated with messages and builds the store record for persistence. Some of the key validations performed are:
Check if a like store was passed in. If it is, then the price store and cost location must match the like store. If a like store was not passed in, the copy replenishment, activity, and delivery indicators must be No or null.
For new stores, check that the store number passed in is not currently being used for a store or warehouse.
|
Note: Stores and warehouses in RMS cannot have the same unique identifier. |
Verify the start order days are greater than or equal to zero.
For updates or deletes, verify the store exists on the base table
All insert, update and delete SQL statements are located in the family package. This package is STORE_SQL. The private functions in RMSSUB_STORE_SQL will call this package.
Filename: rmssub_xstoresqls/b.pls
RMSSUB_XSTORE_SQL.PERSIST_MESSAGE
(O_error_message IN OUT VARCHAR2,
I_store_rec IN STORE_SQL. STORE_ROW_TYPE,
I_message_type IN VARCHAR2,)
This function determines what type of database transaction it will call based on the message type.
STORE CREATE
Create messages get added to the staging table to be processed in a batch cycle. The address on the message is inserted as the primary address for the primary address type in the ADDR table. No other detail (child) processing occurs for creates.
STORE MODIFY
Modify messages directly update the store table with changes. The address on the message is updated in the ADDR table. LOCATION TRAIT CREATE
Adds location trait(s) to the store
WALKTHROUGH CREATE
Adds walkthrough store(s) to the store.
LOCATION TRAIT DELETE
Removes location trait(s) to the store
WALKTHROUGH DELETE
Removes walkthrough store(s) to the store.
STORE DELETE
Store gets added to a purging table to be processed in a batch cycle.
Below 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 Type | Message Type Description | XML Schema Definition (XSD) |
|---|---|---|
| XStoreCre | External Store Create | XStoreDesc.xsd |
| XStoreDel | External Store Delete | XStoreRef.xsd |
| XStoreLocTrtCre | External Store Location Trait Create | XStoreDesc.xsd |
| XStoreLocTrtDel | External Store Location Trait Delete | XStoreRef.xsd |
| XStoreMod | External Store Modification | XStoreDesc.xsd |
| XStoreWTCre | External Walk Through Store Create | XStoreDesc.xsd |
| XStoreWTDel | External Walk Through Store Delete | XStoreRef.xsd |
Location traits already exist in RMS.
Location trait and walkthrough store data cannot be sent in on a store create message.
Some of the business validation is referential or involves uniqueness. This validation is handled automatically by the referential integrity constraints and the unique indexes implemented on the database.
This section describes the transfer subscription API.
RMS subscribes to transfers from external systems to create, update or delete transfers in RMS. Oracle Retail's Advanced Inventory Planning system (AIP) also utilizes this API to create standalone warehouse to warehouse and warehouse to store transfers. In addition, RMS utilizes the XTsf API in a number of integration processes that create/modify a transfer in RMS. This includes SOStatus RIB API, Store Order web service, mobile service, and RMS dashboard report actions.
The transfer RIB API has defaulting logic which the API uses to populated defaulted fields. This is designed so that multiple sources can use the transfer API without having to conform to the same default values. Retailers can set-up their own set of default values or logic without having to modify the API code. For fields that are exposed on the message, if a value is provided, it will be used. Default values will only be used if a value is not provided on the message.
This is a layer of code which enables decoupling of localization logic that is only required for certain country-specific configuration. This layer affects the RIB API flows including Transfer subscription. This allows RMS to be installed without requiring customers to install or use this localization functionality, where not required.
Filename: rmssub_xtsfs/b.pls
RMSSUB_XTSF.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure initially ensures that the passed in message type is a valid type for transfer messages.
If the message type is invalid, a status of "E" is returned to the external system along with an appropriate error message informing the external system that the status is invalid.
If the message type is valid, the generic RIB_OBJECT is downcast to the actual object using the Oracle treat function. There is an object type that corresponds with each message type. If the downcast fails, a status of "E" is returned to the external system along with an appropriate error message informing the external system that the object passed in is invalid.
If the downcast is successful, then consume verifies that the message passes all of RMS's business validation. It calls the RMSSUB_XTSF_VALIDATE.CHECK_MESSAGE function to determine whether the message is valid. If the message passed RMS business validation, then the function returns true, otherwise it returns false. If the message fails RMS business validation, a status of "E" is returned to the external system along with the error message returned from the CHECK_MESSAGE function.
Once the message has passed RMS business validation, it is persisted to the RMS database. It calls the RMSSUB_XTSF_SQL.PERSIST() function. If the database persistence fails, the function returns false. A status of "E" is returned to the external system along with the error message returned from the PERSIST() function. Once the message has been successfully persisted, a consume function of RMSSUB_XTSF_CFA.CONSUME is called to capture the customer's attributes data from the external system which in turn loads TSFHEAD_CFA_EXT table of RMS.
Once the message has been successfully persisted, the customer's attributes data is added to the CFAS table, there is nothing more for the consume procedure to do. A success status, "S", is returned to the external system indicating that the message has been successfully received and persisted to the RMS database.
Filename: rmssub_xtsfvals/b.pls
It should be noted that some of the business validation is referential or involves uniqueness. This validation is handled automatically by the referential integrity constraints and the unique indexes implemented on the database.
RMSSUB_XTSF_VALIDATE.CHECK_MESSAGE
This overloaded function performs all business validation associated with create/modify messages and builds the transfer API record with default values for persistence in the transfer related tables. Any invalid records passed at any time result in message failure.
Like other APIs, the transfer API expects a snapshot of the record on both a header modify and a detail modify message, instead of only the fields that are changed. For a detail create or a detail modify message, only the TSF number is validated at the header level; all other header fields are ignored.
Validation related to transfer type and status includes the following:
SIM, EG, AIP cannot be created in Input status
Cannot change a transfer from Approved to Input status
RMS dashboard utilizes the XTsf API to create transfers in RMS in Input status. Valid transfer types that can be created in this process are AD, CF, RV, RAC, BT, MR, and IC. Among these, IC transfers must be intercompany; all other transfers must be intra-company.
Since BT, RV, RAC transfers require special handling when being approved and the relevant approval logic is NOT covered in XTsf, BT, RV, RAC transfers cannot be created in or updated to Approved status through this API.
There is check for CFAS attributes, if there are any entries in TSFHEAD_CFA_EXT, then there is not a 2nd leg transfer for that TSF_NO. This process is achieved by checking TSF_NO with TSF__PARENT_NO, since in the RMS transfer screen, CFAS attributes can only be defined for the 1st leg. Otherwise, CFAS attributes defined for the 2nd leg through an external system cannot be viewed in RMS.
TRANSFER CREATE
Checks required fields.
Validates fields.
Defaults fields (status at header, freight type and tsf type).
Builds transfer records.
TRANSFER MODIFY
Checks required fields on the header nodes.
Verifies TSF number already exists.
Validates fields.
Populates record.
TRANSFER DETAIL CREATE
Checks required fields on the detail node.
Verifies TSF number already exists.
Verifies tsf/item/loc does not already exist.
Creates item/loc relation if not already exists, including creating ITEM_LOC_SOH, ITEM_SUPP_COUNTRY_LOC, and PRICE_HIST records. If a pack item is involved, these records are created for all component items.
Populates record.
TRANSFER DETAIL MODIFY
Checks required fields on the detail node.
Verifies transfer/item/loc already exists.
If TSF quantity is reduced, verifies the new quantity is not below what has already been received plus what is being shipped or expected.
Populates record.
RMSSUB_XTSF_VALIDATE.CHECK_MESSAGE
This overloaded function performs all business validation associated with delete messages and builds the transfer API record with default values for persistence in the transfer related tables. Any invalid records passed at any time results in message failure.
TRANSFER DELETE
Checks required fields.
Verifies TSF number already exists.
Verifies that TSF is not already shipped or received.
Populates record for delete.
TRANSFER DETAIL DELETE
Checks required fields.
Verifies TSF/item/loc already exists.
Verifies that TSF line is not already shipped or received.
Populates record with the TSF no/item/location for delete.
Filename: rmssub_xtsfs/b.pls
RMSSUB_XTSF_SQL.PERSIST
(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_tsf_rec IN RMSSUB_XTSF.TSF_REC,
I_message_type IN VARCHAR2)
This function checks the message type to route the object to the appropriate internal functions that perform DML insert, update and delete processes.
TRANSFER CREATE
Inserts records in the TSFHEAD, TSFDETAIL, TSFDETAIL_CHRG tables.
Updates records in the ITEM_LOC_SOH table.
TRANSFER MODIFY
Updates a record in the TSFHEAD table.
TRANSFER DELETE
Deletes a transfer from TSFHEAD, TSFDETAIL, TSFDETAIL_CHRG tables.
TRANSFER DETAIL CREATE
Inserts records in the TSFDETAIL, TSFDETAIL_CHRG tables.
Updates records in the ITEM_LOC_SOH table.
TRANSFER DETAIL MODIFY
Updates records in the TSFDETAIL, ITEM_LOC_SOH tables.
TRANSFER DETAIL DELETE
Delete records from TSFDETAIL, TSFDETAIL_CHRG tables.
Filename:rmssub_ctsf_cfas/b.pls
This package is used to consume the customer's attributes data from the external systems and load into in TSFHEAD_CFA_EXT table. As part of RIB_XTsfDesc_REC, a CustFlexAttriVo_TBL type has been iterated to extract the data from customer's attributes.
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) |
|---|---|---|
| Xtsfcre | Transfer Create Message | XTsfDesc.xsd |
| Xtsfmod | Transfer Modify Message | XTsfDesc.xsd |
| Xtsfdel | Transfer Delete Message | XTsfRef.xsd |
| Xtsfdtlcre | Transfer Detail Create Message | XTsfDesc.xsd |
| Xtsfdtlmod | Transfer Detail Modify Message | XTsfDesc.xsd |
| Xtsfdtldel | Transfer Detail Delete Message | XTsfRef.xsd |
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| TSFHEAD | Yes | Yes | Yes | Yes |
| TSFDETAIL | Yes | Yes | Yes | Yes |
| TSFDETAIL_CHRG | Yes | Yes | Yes | Yes |
| ITEM_LOC | Yes | Yes | No | No |
| ITEM_LOC_SOH | Yes | Yes | No | No |
| PRICE_HIST | No | Yes | No | No |
| ITEM_MASTER | Yes | No | No | No |
| PACKITEM_BREAKOUT | Yes | No | No | No |
| STORE | Yes | No | No | No |
| WH | Yes | No | No | No |
| SYSTEM_OPTIONS | Yes | No | No | No |
| WF_ORDER_HEAD | Yes | Yes | Yes | No |
| WF_ORDER_DETAIL | Yes | Yes | Yes | Yes |
| WF_ORDER_EXP | Yes | Yes | Yes | Yes |
| TSFHEAD_CFA_EXT | Yes | Yes | Yes | Yes |
This section describes the vendor subscription API.
RMS subscribes to supplier information that is published from an external financial application. 'Vendor' refers to either a partner or a supplier, but only supplier information is subscribed to by RMS. Supplier information also includes supplier addresses, CFAS data and the org unit.
Processing includes a check for the appropriate financial application in RMS on the SYSTEM_OPTIONS table's FINANCIAL_AP column, which will result in different processing. The financial application (such as Oracle EBS) sends the information to RMS through RIB.
The financial application publishes a supplier type vendor, placing the supplier information onto the RIB (Oracle Retail Information Bus). RMS subscribes to the supplier information as published from the RIB and places the information onto RMS tables depending upon the validity of the records enclosed within the message.
Filename: rmssub_vendorcres/b.pls
RMSSUB_VENDORCRE.CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN CLOB);
This procedure accepts a XML file in the form of an Oracle CLOB data type from the RIB (I_message). This message contains a supplier message consisting of the aforementioned header and detail records. The procedure then places a call to the main RMSSUB_SUPPLIER.CONSUME function in order to validate the XML file format and, if successful, parses the values within the file through a series of calls to RIB_XML. The values extracted from the file are then passed on to private internal functions, which validate the values and place them on the supplier and address tables depending upon the success of the validation.
Error Handling
If an error occurs in this procedure, a call is placed to HANDLE_ERRORS in order to parse a complete error message and pass back a status to the RIB.
HANDLE_ERRORS
(O_status IN OUT VARCHAR2,
IO_error_message IN OUT VARCHAR2,
I_cause IN VARCHAR2,
I_program IN VARCHAR2);
This function is used to put error handling in one place in order to make future error handling enhancements easier to implement. All error handling in the internal RMSSUB_SUPPLIER package and all errors that occur during subscription in the RMSSUB_VENDORCRE package (and whatever packages it calls) flow through this function.
The function consists of a call to API_LIBRARY.HANDLE_ERRORS. API_LIBRARY.HANDLE_ERRORS accepts a program name, the cause of the error and potentially an unparsed error message if one has been created through a call to SQL_LIB.CREATE_MESSAGE. The function uses these input variables to parse a complete error message and pass back a status, depending upon the message and error type, back up through the consume function and up to the RIB.
All of the following functions exist within RMSSUB_SUPPLIER.
Main Consume Function
RMSSUB_SUPPLIER.CONSUME
(O_status OUT VARCHAR2,
O_error_message OUT VARCHAR2,
I_document IN CLOB);
This procedure accepts a XML file in the form of an Oracle CLOB data type from the RIB (I_message) from the aforementioned public vendor procedure whenever a message is made available by the RIB. This message consists of the aforementioned header and detail records.
The record is processed and then validates the XML file format and, if successful, calls internal functions to parse the values within the file through a series of calls to RIB_XML. The values extracted from the file are then passed on to private internal functions, which validate the values and place them on the appropriate supplier and address database tables depending upon the success of the validation. The procedure then calls the PROCESS_ADDRESS function to check that the proper addresses have been associated with the supplier and store the address details in ADDR table. After processing the address records, the procedure calls PROCESS_ORGUNIT function to process the org units.
The custom flex attributes in the message are subscribed by calling the function RMSSUB_SUPPLIER_CFA.CONSUME().
PARSE_SUPPLIER
This function is used to extract the header level information from the supplier XML file and place that information onto an internal supplier header record.
The record is based upon the supplier table.
PARSE_ADDRESS
This function extracts the address level information from the supplier XML file and places that information onto an internal address record.
The record is based upon the address table.
PROCESS_SUPPLIER
After the values are parsed for a particular supplier record, RMSSUB_SUPPLIER.CONSUME calls this function, which in turn calls various functions inside RMSSUB_SUPPLIER in order to validate the values and place them on the appropriate supplier table depending upon the success of the validation. Either INSERT_SUPPLIER or UPDATE_SUPPLIER is called to actually insert or update the supplier table.
PROCESS_ADDRESS
After the values are parsed for a particular address record, RMSSUB_SUPPLIER.CONSUME calls this function. If the FINANCIAL_AP system option is set to 'O', this function calls various functions inside RMSSUB_SUPPLIER in order to validate the values and place them on the appropriate address table depending upon the success of the validation. Either INSERT_ADDRESS or UPDATE_ADDRESS is called to actually insert or update the address table.
INSERT_SUPPLIER
This function first checks the PROCUREMENT_UNIT_OPTIONS table to determine what the value of dept_level_orders is. If the dept_level_orders value is 'Y', the inv_mgmt_lvl is defaulted to 'D'. If the dept_level_orders value is anything other than 'Y', the inv_mgmt_lvl is set to 'S.'
The function then takes the information from the passed-in supplier record and inserts it into the SUPS table.
FUNCTION UPDATE_SUPPLIER
This function updates the SUPS table using the values contained in the I_supplier_record.
If the primary address of the supplier is localized then supplier status will be 'I' - Inactive.
FUNCTION UPDATE_ADDRESS
This function updates the supplier information to the address table.
CHECK_CODES
The RMSSUB_SUPPLIER package, specifically the functions check_codes() and check_fkeys(), sends back descriptive error messages when codes are not valid or if a foreign key constraint is violated.
INSERT_ADDRESS
Insert supplier information to address table. If the address in the passed-in address record is the primary address for a particular supplier/address type, this function updates the current primary address so that it is no longer the primary.
VALIDATE_SUPPLIER_RECORD
Validate that all the necessary records are populated. In the supplier site enabled environment (system_options.supplier_site_ind = 'Y') supplier_parent must be present.
VALIDATE_ADDRESS_RECORD
Validate that all the necessary records are populated.
CHECK_NULLS
This function checks that the passed-in record variable is not null. If it is, it will return an error message.
VALIDATE_ORG_UNIT_RECORD
This function checks that the passed-in record variable is not null. If it is, it will return an error message. When not null, it checks for a valid org unit in ORG_UNIT table.
PROCESS_ORGUNIT
After validating the org unit, this function either inserts or updates the record in PARTNER_ORG_UNIT table. If the vendor/orgunit in the passed-in Org Unit record is the primary pay site for a particular vendor/orgunit type, this function updates the current primary paysite so that it is no longer the primary. When supplier_site_ind = 'Y', partner_org_unit only exists for supplier sites, not for parent supplier hence this function will be called for supplier sites and not for supplier.
Filename: rmssub_supplier_cfas/b.pls
RMSSUB_SUPPLIER_CFA.CONSUME(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_supplier_node IN XMLDOM.DOMELEMENT,
I_address IN XMLDOM.DOMNODELIST);
The main CONSUME function processes the CFAS attributes of supplier and address by calling the functions RMSSUB_SUPPLIER_CFA.CONSUME_SUPS_CFAS () and RMSSUB_SUPPLIER_CFA. CONSUME_ADDR_CFAS().
Here are the filenames that correspond with each message type. Please consult Oracle Retail Integration Bus information 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 Message | VendorDesc.xsd |
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| SUPS | Yes | Yes | Yes | No |
| ADDR | Yes | Yes | Yes | No |
| SYSTEM_OPTIONS | Yes | No | No | No |
| UNIT_OPTIONS | Yes | No | No | No |
| CODE_DETAIL | Yes | No | No | No |
| PARTNER_ORG_UNIT | Yes | Yes | Yes | No |
| ORG_UNIT | Yes | No | No | No |
| CFA_EXT_ENTITY | Yes | No | No | No |
| CFA_ATTRIB_GROUP_SET | Yes | No | No | No |
| SUPS_CFA_EXT | No | Yes | No | No |
| ADDR_CFA_EXT | No | Yes | No | No |
This section describes the work order status subscription API.
RMS subscribes to a work order status message sent from internal finishers. Work order status messages contain the items for which the activities have been completed along with the quantity that was completed. All items on transfers that pass through an internal finisher must have at least one work order activity performed upon them. When work order status messages are received for a particular item/quantity, it is assumed that all work order activities associated with the item/quantity have been completed. If work order activities involve item transformation or repacking, the work order status messages are always created in terms of the resultant item.
The work order status message is only necessary when the internal finisher and the final receiving location are in the same physical warehouse. If the internal finisher belongs to the receiving location, a book transfer is made between the internal finisher (which is held as a virtual warehouse) and the final receiving location (also a virtual warehouse). If the internal finisher belongs to the sending location's transfer entity, intercompany out and intercompany in transactions are recorded. Quantities on hand, reserved quantities, and weighted average costs are adjusted to accurately reflect the status of the stock.
Assume that a quantity of 20 of item 100 (White XL T-shirt) are sent to an internal finisher at the receiving physical warehouse where they will be dyed black, thereby transforming them into item 101 (Black XL T-shirt). If all finishing activities were successfully completed in this example, RMS could expect to receive a Work Order Status message containing item 101 with a quantity of 20.
It is possible to receive multiple Work Order Status messages for a particular item/transfer. Work order completion of partial quantities addresses the following scenarios:
1. Work order activities could not be performed for the entire quantity of a particular item at one time.
2. A given quantity of the particular item was damaged while work order activities were performed.
In terms of the previous example, RMS could receive a message containing item 101(Black XL T-shirt) with a quantity of 10. A message stating that work order activities were completed for the remaining 10 items could then be received at a later time. The only scenario in which a Work Order Status message is necessary is when work order activities are taking place at an internal finisher that resides in the same physical warehouse as the transfer's final receiving location. In this scenario, the final 'leg' of the transfer will 'move' merchandise between two virtual warehouses in the same physical warehouse. As this movement cannot be done until all work order activities are completed for a specific item/quantity, the finisher must inform RMS of this completion.
Other finishing scenarios exist in which the finisher is not a virtual warehouse that shares a physical warehouse with the transfer's final receiving location. In these instances, Work Order Status messages are not necessary. This is because these scenarios dictate that merchandise must be physically shipped from the finisher to the transfer's final receiving location. RMS assumes that a finisher will not ship merchandise until all finishing activities have been completed for said merchandise. RMS will disregard Work Order Status messages sent in these scenarios.
Filename: rmssub_wostatuss/b.pls
PROCEDURE CONSUME
(O_status_code IN OUT VARCHAR2,
O_error_message IN OUT VARCHAR2,
I_message IN RIB_OBJECT,
I_message_type IN VARCHAR2)
This procedure is passed an Oracle Object, which it will validate to ensure all required data is present. It will ensure that the finisher and the transfer's final receiving location are in the same physical warehouse. If not, processing is deemed successful and halted. If the message contains an item, RMS work order complete processing will be called for that item. Otherwise, said processing will be called for all items on the transfer. If the entire transfer is processed, the child transfer (that is, the 'second leg') will be set to 'S'hipped status.
|
Note: Work orders are always associated with the second leg of multi-leg transfers. Whether processing is performed at the item or transfer level, transfer closing queue logic will be called to determine if the entire multi-leg transfer can be closed. |
PROCEDURE HANDLE_ERRORS
(O_status_code IN OUT VARCHAR2,
IO_error_message IN OUT VARCHAR2,
I_cause IN VARCHAR2,
I_program IN VARCHAR2)
This is the standard error handling procedure that wraps the API_LIBRARY.HANDLE_ERROR function.
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) |
|---|---|---|
| wostatuscre | Work Order Status Create Message | WOStatusDesc.xsd |
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| TSFHEAD | Yes | No | Yes | No |
| TSFDETAIL | Yes | No | Yes | No |
| TSF_ITEM_COST | Yes | No | Yes | No |
| DOC_CLOSE_QUEUE | No | Yes | No | No |
| ITEM_LOC_SOH | Yes | Yes | Yes | No |
| TRAN_DATA(VIEW) | No | Yes | No | No |
| INV_ADJ | No | Yes | No | No |
| INV_STATUS_QTY | No | Yes | Yes | Yes |
| INV_ADJ_REASON | Yes | No | No | No |
| V_PACKSKU_QTY | Yes | No | No | No |
| ITEM_LOC | Yes | No | No | No |
| ITEM_MASTER | Yes | No | No | No |
| INV_STATUS_CODES | Yes | No | No | No |
| SHIPSKU | Yes | No | No | No |
