3 RIB Subscription Designs
This chapter provides an overview of the RIB subscription APIs used in by Merchandising.
Allocation Subscription API
This section describes the allocation subscription API.
Business Overview
The allocation subscription API allows an external application to create, update, and delete allocations within Merchandising. The main reason for doing so is to successfully interface and track all dependent bills of lading (BOL) and receipt messages into Merchandising, 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, cross-dock and inventory source based on Transfer, Allocation, ASN,BOL. The Oracle Retail Allocation product does NOT use this API to interface allocations to Merchandising. From an Oracle Retail perspective, this API is used by AIP to support the creation of cross dock POs, based on POs sent to Merchandising using the Order Subscription API.
Allocations created and edited based on the source of inventory in Transfer, Allocation, ASN, or BOL are required to send Document ID as well as Document type in corresponding message fields. The Document ID and Document Type fields are optional in the message. If these are not present, the API treats the allocation as a warehouse-inventory-based one, or a cross-dock allocation.
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, Merchandising will also create a corresponding franchise order. This API supports multiple types of destination locations (warehouses as well as stores) as part of the detail section within the same message.
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 Merchandising that do not previously exist within Merchandising.
New locations can be added to existing allocations, or current locations can be modified on existing allocations. If modifying an existing location, Merchandising 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, Merchandising also exposes an Allocation web service to allow an external application to create, update, and delete allocations in Merchandising. The web service takes in a collection of allocations and will return success and failure through the service response object.
Package Impact
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 Merchandising. 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 Merchandising's business validation. It calls the RMSSUB_XALLOC_VALIDATE.CHECK_MESSAGE function to determine whether the message is valid. If the message passed Merchandising business validation, the function returns true, otherwise it returns false. If the message has failed Merchandising 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 Merchandising business validation, it will be persisted to the Merchandising 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 Merchandising 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 document type field exists check for valid document.
-
Validate document type field (valid values are TSF, ASN, BOL, and ALLOC).
-
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)
-
If allocation is created on valid document id check for allocation quantity based on the document, location and item combination.
-
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
-
If allocation detail is created on valid document id check for allocation quantity against document available quantity with document, location and item combination
-
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 allocation is modified on valid document id check for allocation quantity against document available quantity with document, location and item combination.
-
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.
-
Persist doc and doc_type fields in allocation header if allocation is created from valid document as source of inventory.
-
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
Message XSD
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 |
Design Assumptions
-
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 Merchandising inventory positions, Merchandising provides no stock on hand or inventory validation.
Table Impact
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 |
Appointments Subscription API
This section describes the appointments subscription API.
Business Overview
An appointment is information about the arrival of merchandise at a location. From the RIB, Merchandising subscribes to appointment messages that are published by an external application, such as a warehouse management system (for example, RWMS). Merchandising processes these messages and attempts to receive against and close out the appointment. In addition, Merchandising attempts to close the document that is related to the appointment. A document can be a purchase order, a transfer, or an allocation.
Appointment status
Appointment messages cause the creation, update, and closure of an appointment in Merchandising. 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.
Appointment processing
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 Merchandising to insert received quantities into the APPT_DETAIL table.
-
After all items are received, Merchandising attempts to close the appointment by updating it to an 'AC' status.
-
Merchandising 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 Merchandising.
Package Impact
Filename: rmssub_receivings/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 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 IN VARCHAR2)
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.
Message XSD
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 |
Design Assumptions
-
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 Impact
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 |
ASNIN Subscription API
This section describes the ASNIN subscription API.
Business Overview
A supplier or consolidator will send an advanced shipping notice (ASN) to Merchandising through the Oracle Retail Information Bus (RIB). Merchandising subscribes to the ASN information and places the information onto Merchandising 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 Merchandising, 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.
Package Impact
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.
-
Error Handling
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.
Private Internal Functions and Procedures
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.
Validation
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.
Message XSD
Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
asnincre |
ASN Inbound Create Message |
ASNInDesc.xsd |
asnindel |
ASN Inbound Delete Message |
ASNInRef.xsd |
asninmod |
ASN Inbound Modify Message |
ASNInDesc.xsd |
ASN Outbound Subscription API
This section describes the ASNOUT subscription API.
Business Overview
Merchandising receives advanced shipping notifications (ASNs), also known as a bill of lading (BOL) messages, from a warehouse management system (WMS), like Oracle Warehouse Management Cloud, or a store inventory system like Oracle Retail Store Inventory and Operations Cloud Service (SIOCS).
These ASNs are notifications to Merchandising that inventory is moving from one location to another. These notification messages contain data that is used by Merchandising to create or modify a shipment record. ASNs are received for:
-
Pre-existing allocations.
-
Pre-existing transfers.
-
Externally generated transfers, created in the store or warehouse (created as transfer type of EG within Merchandising).
An ASN message may contain multiple transfers or allocations, and as a result, the shipment record in Merchandising will reflect these multiple movements of merchandise. A BOL number on the shipment record is a means of tracking one or more transfers and allocations back through the respective stock order records. Shipments for customer orders, franchise orders, and franchise returns are also managed through this API. If the receiving location is a non-stockholding location, like in the case of a warehouse shipment to a non-stockholding franchise store, or a warehouse shipment direct to a customer (that is processed through a non-stockholding store) then the shipment will be auto-received when processed by Merchandising.
Note:
ASNs related to a purchase order from a supplier are classified as an Inbound ASNs. Details for those types of expected shipments are found in the ASN In Subscription API section.Other Notes
-
For customer order fulfillment, SIOCS will send an ASN Out message that does not include a ship-to location. Merchandising ignores these messages.
-
Store to customer fulfillment request will not have associated transfer in Merchandising. When Oracle Retail Store Inventory and Operations Cloud Service (SIOCS) ships the customer order, SIOCS will generate an Outbound ASN message with an empty To Location or with Location Type as Customer (C). Since there are no associated transfers in Merchandising, Merchandising will not process these Outbound ASN messages. The reserved inventory will be backed out in Merchandising when Merchandising processes a SALES transaction backend.
-
Messages consumed through this API can create new shipments or update existing shipments. A new shipment record will be created in Merchandising with Input status if the BOL number is not yet associated to any shipment record. If the BOL number is already associated to a shipment record, the shipment record will be updated accordingly.
-
The Universal Identification Number (UIN) child node may be included in the message, but this information is not used by Merchandising. This is used by SIOCS.
-
A Shipment and Carton Custom Flex Attribute child nodes can be included in the message, but this information is not used by Merchandising. This is used by SIOCS.
Shipment Message Details
When inventory is shipped from one location to another, Merchandising will be notified and will then create a shipment record based on the content of the message received. If the shipment already exists, the details of the existing shipment will be updated. The message includes the following:
ASNOUT Details
Message Element | Required? | Notes |
---|---|---|
Schedule Number |
Optional |
Not used by Merchandising. |
Auto Receive Indicator |
Optional |
Not used by Merchandising. |
To Location |
Optional |
This field contains the location where the shipment will be delivered. |
To Location Type |
Optional |
This field contains the location type of the location where the shipment will be delivered to. Valid values are store (S), warehouse (W), or finisher (E). |
To Store Type |
Optional |
Not used by Merchandising. |
To Stockholding Indicator |
Optional |
Not used by Merchandising. |
From Location |
Always |
This field contains the location from which the shipment was sourced. This applies to transfer and allocation shipments. |
From Location Type |
Optional |
This field contains the location type of the location from which the shipment was sourced. Valid values are store (S) or warehouse (W). |
From Store Type |
Optional |
Not used by Merchandising. |
From Stockholding Indicator |
Optional |
Not used by Merchandising. |
ASN Number |
Optional |
This field contains the bill of lading number associated with the shipment. |
ASN Type |
Optional |
Not used by Merchandising. |
Container Quantity |
Optional |
This field contains the number of boxes associated with the shipment. |
BOL Number |
Always |
This field contains the transaction sequence number from the transfer shipment confirmation process. This is the same as the ASN Number. |
Shipment Date |
Optional |
This field contains the date the transfer and/or allocation was shipped. |
Estimated Arrival Date |
Optional |
This field contains the estimated arrival date when the shipment is expected to arrive at the destination. |
Shipment Address 1 |
Optional |
Not used by Merchandising. |
Shipment Address 2 |
Optional |
Not used by Merchandising. |
Shipment Address 3 |
Optional |
Not used by Merchandising. |
Shipment Address 4 |
Optional |
Not used by Merchandising. |
Shipment Address 5 |
Optional |
Not used by Merchandising. |
Shipment City |
Optional |
Not used by Merchandising. |
Shipment State |
Optional |
Not used by Merchandising. |
Shipment Zip Code |
Optional |
Not used by Merchandising. |
Shipment Country ID |
Optional |
Not used by Merchandising. |
Trailer Number |
Optional |
Not used by Merchandising. |
Seal Number |
Optional |
Not used by Merchandising. |
Transshipment Number |
Optional |
Not used by Merchandising. |
Comments |
Optional |
This field contains any miscellaneous comments about the shipment. |
Carrier Code |
Optional |
This field contains the courier that will deliver the shipment. |
Carrier Service Code |
Optional |
This field contains the service level code for the courier that will deliver the shipment. Valid values are found in code type CSVC. Not used by Merchandising. |
System Code |
Optional |
Not used by Merchandising. |
From Location Virtual Warehouse |
Optional |
Not used by Merchandising. |
ASNOUT Distro Details
This level of the message contains the details about the individual stock orders contained in the shipment.
Message Element | Required? | Notes |
---|---|---|
Distro Number |
Always |
This field contains the transfer number or allocation number associated with the shipment. |
Distro Doc Type |
Always |
This field determines if the Distro Number specified is a Transfer (T) or an Allocation (A). |
Customer Order Number |
Optional |
This contains the customer order number associated with the transfer on the shipment. |
Fulfill Order Number |
Optional |
This contains the fulfillment order number associated with the customer order number for the transfer on the shipment. |
Consumer Direct |
Optional |
Not used by Merchandising. |
Comments |
Optional |
This field contains any comments about the stock orders contained in the shipment. |
ASNOUT Carton Details
This section of the message contains details about the cartons for a distro on a shipment.
Message Element | Required? | Notes |
---|---|---|
Final Location |
Optional |
Not used by Merchandising. |
Container ID |
Always |
This field contains the carton number for shipments originating from the ASN process as carton shipments. This field will be zero for all shipments that are not at a carton level. |
Container Weight |
Optional |
Not used by Merchandising. |
Container Length |
Optional |
Not used by Merchandising. |
Container Width |
Optional |
Not used by Merchandising. |
Container Height |
Optional |
Not used by Merchandising. |
Container Cube |
Optional |
Not used by Merchandising. |
Expedite Flag |
Optional |
Not used by Merchandising. |
In Store Date |
Optional |
Not used by Merchandising. |
Tracking Number |
Optional |
This field contains a unique tracking number that is used to track containers through a carrier’s system. Not used by Merchandising. |
Freight Charge |
Optional |
Not used by Merchandising. |
Master Container ID |
Optional |
Not used by Merchandising. |
Comments |
Optional |
This field contains any comments about the shipment container. |
Weight |
Optional |
This field contains the actual weight shipped for the container. |
Weight UOM |
Optional |
This field contains the unit of measurement for weight (e.g., pounds, kilograms) that was shipped. |
Carrier Shipment Number |
Optional |
Not used by Merchandising. |
Original Item ID |
Optional |
Not used by Merchandising. |
ASNOUT Item Details
This section outlines details about the items in the carton.
Message Element | Required? | Notes |
---|---|---|
Item ID |
Always |
This field contains the unique identifier for the item. |
Unit Quantity |
Always |
This field contains the quantity of the item shipped in the carton for this shipment in the standard unit of measure. |
Gross Cost |
Optional |
Not used by Merchandising. |
Priority Level |
Optional |
Not used by Merchandising. |
Order Line Number |
Optional |
This field is used to carry the customer order line number value for customer orders. This field is not used by Merchandising for non-customer orders. |
Lot Number |
Optional |
Not used by Merchandising. |
Final Location |
Optional |
Not used by Merchandising. |
From Disposition |
Optional |
This value is used to determine if the quantity shipped is available or unavailable. Valid values for this field are inventory status codes (INV_STATUS_CODE) in the INV_STATUS_CODES table in Merchandising. |
To Disposition |
Optional |
Not used by Merchandising. |
Voucher Number |
Optional |
Not used by Merchandising. |
Voucher Expiration Date |
Optional |
Not used by Merchandising. |
Container Quantity |
Optional |
Not used by Merchandising. |
Comments |
Optional |
Not used by Merchandising. |
Unit Cost |
Optional |
This field contains the unit cost of the item in the shipment. This is used only for the Brazil Localization setup to calculate transaction code 74 (Recoverable Tax for Destination Location). In all other cases this should be NULL. |
Base Cost |
Optional |
This value will be used for the Brazil Localization setup only to get the base cost (BC) from Fiscal Management for a transfer, which will flow into Merchandising. In all other cases this should be NULL. |
Weight |
Optional |
This field contains the actual weight shipped. This may be included for catch weight items. If not included Merchandising will use the average weight or nominal weight for the item at the shipping location. |
Weight UOM |
Optional |
This field contains the unit of measurement for weight (e.g., pounds, kilograms) shipped. Required if weight is included in the message. |
Error Handling
When the publication encounters a non-fatal error, messages continue to be processed. For the particular message where the error was encountered, a status of Hospital (H) is sent to the RIB and the status of the message in the queue is set to H. These types of errors occur when no changes in the database have been made and a process to try to re-publish the messages is available. In case the error is a fatal error, a status of Error (E) is sent to the RIB and the next message in the queue is not retrieved until the error is resolved.
Message XSD
Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
asnoutcre |
ASN Outbound Create Message |
ASNOutDesc.xsd |
COGS Subscription API
This section describes the COGS subscription API.
Business Overview
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 Merchandising, 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.
Merchandising subscribes to integration subsystem COGS messages. This process records the inventory and financial transactions associated with a cost of goods sold message.
Package Impact
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.
Business Validation Mode
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.
DML Module
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.
Message XSD
Here are the filenames that correspond with each message type. Please consult the mapping documents for each message type in order to get a detailed picture of the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
CogsCre |
COGS Create Message |
CogsDesc.xsd |
Design Assumptions
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.
Cost Change Subscription API
This section describes the cost change subscriptions.
Business Overview
Merchandising exposes an API that will allow external systems to update unit cost within Merchandising. Cost changes can be performed at the item level, or at the following levels of the organization hierarchy: chain, area, region, district, and store. Unit costs are 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.
All cost changes that are sent through this API are executed immediately. The cost change subscription creates both the cost change events with an effective date of the current date, as well as updates unit costs for item/locations that already exist in Merchandising. It does not create or delete item/locations in Merchandising.
In addition to RIB, Merchandising also exposes this API as a web service. The web service takes in a collection of cost changes and will return success and failure through the service response object. See the "Cost Change Service" section of this document in the "Provider Services" section of "SOAP Web Services" more information.
This API checks that the required fields are provided and checks the supplier's currency and the item status. If differentiator IDs are passed in, it verifies that they are valid for the passed in item. The API also retrieves the following:
-
Transaction level items, if the passed in item is an item parent
-
All locations based on the passed in hierarchy type and value, if provided.
-
All item/location combinations where the passed in supplier/country is the primary supplier/country at an item location.
-
All orderable buyer packs that the passed-in item or its children, if above transaction level
-
All item/locations on approved (and worksheet) order, if the recalculate order indicator is set to Yes.
This API will perform the following actions:
-
Create a cost change event in Executed status, with an effective date of the current date.
-
Update the unit cost in Merchandising for all item/supplier/country and item/supplier/country/locations based on the information provided.
-
Create price history for all item/locations that got updated as part of the cost change.
-
If the recalculate order indicator is Yes, update all relevant order/item/locations unit cost in merchandising.
It is important to note that cost changes sent through this API do not include estimated landed costs. The cost updated here is the default purchase cost, before any deals, that will be used for purchase orders created in Merchandising, similar to cost changes initiated in Merchandising.
The format for creating and updating cost changes is shown below.
Table 3-1 Cost Change
Message Element | Required? | Notes |
---|---|---|
Item |
Yes |
This field contains the item to which the cost change applies. It can be a parent item, but its item level cannot be greater than its transaction level. This cannot be a buyer pack. |
Supplier |
Yes |
This field contains the number of the supplier. The supplier must be a valid supplier in merchandising. This can be a primary or non-primary supplier. |
Origin Country ID |
Yes |
This field contains the identifier of the origin country of the item/supplier to which the cost change will apply. This value must be a valid country in merchandising. This can be the primary or a non-primary country. |
Diff ID |
No |
This field contains the identifier for a differentiator. This can be used with a parent or grandparent item. This value must be a valid differentiator ID in merchandising. |
Unit Cost |
Yes |
This field contains the new unit cost of the item in the currency specified on the message. |
Recalculate Order Indicator |
Yes |
This field indicates if orders in approved status for items on the cost change will be recalculated with the new cost. Valid values are Yes (Y) and No (N). |
Currency Code |
Yes |
This field contains the currency code of the unit cost. This must be a valid currency code in merchandising. |
Hierarchy Level |
No |
This field indicates the level of the organizational hierarchy to which the cost change applies. Valid values are:
|
Cost Change Hierarchy |
No |
Child node |
Custom Flex Attributes |
No |
Child node |
Purchase Rate |
No |
This field contains the percentage of the retail price which will determine the cost paid to the supplier for a consignment or concession item. |
Table 3-2 Cost Change Hierarchy
Message Element | Required? | Notes |
---|---|---|
Hierarchy Value |
Yes |
This field contains the hierarchy value at the specified level of the hierarchy which encompasses the locations affected by the cost change. This value must exist as a valid chain, area, region, district, store or warehouse in Merchandising. |
Custom Flex Attributes
If any custom flex attributes (CFAS) for the cost change have been added or modified, it will trigger an update message. The node of the integration that supports this will contain the name of the attribute as it is defined in the group set level view, the value of the custom attribute. If it is a date attribute, the date value is in a separate field. Flex attributes can only be added to or updated; they cannot be deleted.
Table 3-3 Flex Attributes
Message Element | Required? | Notes |
---|---|---|
Name |
Yes |
Hold the attribute name. |
Value |
Conditional |
Holds the value of the attribute for number and character type attributes. Either the value or the value date field should be provided. |
Value Date |
Conditional |
Holds the date for date type attributes. Either the value or the value date field should be provided. |
Error Handling
This API ensures that the correct message type is passed in for cost change messages. If the message type is invalid, an error status is returned to the external system, along with the appropriate error message. This is to inform the external system that the message type is invalid.
The standard error handling functions of Merchandising are in place in this API and return messages as appropriate to the outcome.
Message XSD
Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.
Message Type | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
xcostchgmod |
External Cost Change Modify |
XCostChgDesc.xsd |
Currency Exchange Rates Subscription API
This section describes the currency exchange rates subscription API.
Business Overview
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 Merchandising 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 Merchandising before a rate change is possible. No functionality currently exists to bridge this data.
Data Flow
An external system will publish a currency exchange rate, thereby placing the currency exchange rate information onto the RIB. Merchandising will subscribe to the currency exchange rate information as published from the RIB and place the information onto Merchandising tables depending upon the validity of the records enclosed within the message.
Package Impact
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:
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 accepts an XML file in the form of an RIB Object 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 RIB Object. The values extracted from the RIB Object 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 "RIB_CurrRateDesc_REC"))
This procedure accepts a XML file in the form of a RIB Object 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 RIB Object. The values extracted from the RIB Object 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 Merchandising 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.
Message XSD
Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
CurrRateCre |
Currency Rate Create Message |
CurrRateDesc.xsd |
CurrRateCre |
Currency Rate Modify Message |
CurrRateDesc.xsd |
Design Assumptions
-
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.
Customer Order Fulfillment Subscription API
Business Overview
When Merchandising is integrated with an order management system (OMS) or Oracle Retail Order Broker (OROB), one of the supported deployment methods is interfacing customer order fulfillment requests into Merchandising through the Oracle Retail Integration Bus (RIB). When Merchandising processes the customer order fulfillment request from OROB, it will also publish a confirmation message back indicating whether the order was created fully, partially, or not created. The confirmation message details are shown below.
Merchandising will only publish confirmation messages associated to customer orders that create a Purchase Order or Transfer, which is used for fulfillment from a supplier or a warehouse, or if the fulfillment requires movement between two locations, such as from warehouse to store for customer pickup.
Table 3-4 Fulfill Order Confirmation Message
Message Element | Included? | Notes |
---|---|---|
Customer Order Number |
Always |
This is the customer order number from OROB or OMS that is being confirmed. |
Fulfillment Order Number |
Always |
This is the fulfillment order number from OROB or OMS. |
Confirmation Type |
Always |
Contains the confirmation type. Valid values are:
|
Confirmation Number |
Optional |
This field contains the PO or Transfer number in Merchandising related to the fulfillment order line. This is populated if fulfillment status is P or C. |
Confirmation Details |
Optional |
Child Node |
Fulfillment Location ID |
Optional |
This field contains the fulfillment location for the order. |
System Code |
Always |
Used by OROB to identify the system associated with this message. This is defaulted from the Merchandising system option. |
Table 3-5 Fulfill Order Confirmation Detail Message
Message Element | Required? | Notes |
---|---|---|
Item |
Always |
This field indicates the item ordered by the customer. |
Reference Item |
Optional |
This field indicates the reference item ordered by the customer. It is used only if a specific UPC is ordered. |
Confirmed Quantity |
Always |
This field indicates the quantity of the item that can be sourced or fulfilled on the order. |
Confirmed Quantity UOM |
Always |
This field indicates the unit of measure of the confirmation quantity. |
Item Line Number |
Optional |
This field indicates the detail item line number on the order. |
Message XSD
Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
Fulfilordapprdel |
Fulfilment Cancel Message |
FulfilOrdRef.xsd |
Fulfilordreqdel |
Fulfilment Cancel Request Message |
FulfilOrdRef.xsd |
Fulfilordpocre |
Fulfilment PO Create Message |
FulfilOrdDesc.xsd |
Fulfilordtsfcre |
Fulfilment Transfer Create Message |
FulfilOrdDesc.xsd |
Fulfilordstdlvcre |
Fulfilment Store Delivery Create Message |
FulfilOrdDesc.xsd |
Design Assumptions
-
Customer order fulfillment request cannot be created in RMS for the following scenarios:
-
Customer orders are not created due to any validation error;
-
Customer orders are created in 'X' status due to 'no inventory' (e.g. not enough available at the source location, or item not ranged to or active at the source location, or in a PO scenario, item not supplied by the supplier).
-
-
Non-stockholding franchise stores cannot part of a customer order, either as a sourcing location or as a fulfillment location.
-
Only approved, inventoried and sellable items will be published to OMS. Therefore, item types like catch weight and transformable sellable items will NOT be published to OMS, and will NOT be supported by this interface. To sell items that can vary by weight, like bananas, through online channels, setup should be done as a regular (non-catch weight) item with a unit cost and standard UOM defined in items of eaches.
-
It is assumed that customer orders will be captured in the selling UOM in OMS, but that all transactions will be communicated to Merchandising in standard UOM.
-
If the same customer order fulfillment request is sent for a different item or for an existing item but with a different item line number, the existing PO or transfer will be updated.
Differentiator Group Subscription API
This section describes the Diff group subscription API.
Business Overview
This API allows external systems to create, edit, and delete differentiator groups within Merchandising. Diff ID details can be added, edited, or deleted within the diff group message. When creating a new diff group, diff ID must be included, but they can also be passed in with their own specific message type. Diff ID detail create and modify messages must also include the diff group record.
Creating Diff Groups
When a new differentiator group is created, this API will first validate that all required fields are present in the message. When creating a new differentiator group at least one detail line must also be included in the message. After that, business level validation on the input information will be performed. The tables below summarize the validation.
Table 3-6 Diff Group Header
Message Element | Required? | Notes |
---|---|---|
Differentiator Group Identifier |
Yes |
This field contains the unique differentiator group identifier. |
Differentiator Type |
Yes |
This field contains the differentiator type, such as C for color. Must exist as a valid diff type in Merchandising. |
Differentiator Group Description |
Yes |
This field contains the description of the differentiator group. |
Create Date Time |
No |
This field contains the date and time the differentiator group was created. If it is not populated on the subscription message it will be defaulted to the time of creation in Merchandising. |
Differentiator Group Detail |
Conditional |
Child node This is required in a diff group detail create and modify message. |
Table 3-7 Diff Group Detail
Message Element | Required? | Notes |
---|---|---|
Differentiator ID |
Yes |
This field contains the identifier of the differentiator contained within the differentiator group. This id must be unique within the diff group and must already exist in Merchandising. |
Display Seq |
No |
This field contains the order in which the diff ID should appear within the differentiator group, when displayed on-line. |
Create Date Time |
No |
This field contains the date/time the differentiator ID was added to the differentiator group. If it is not populated on the subscription message it will be defaulted to the time of creation in Merchandising. |
Updating Diff Groups
When updating a differentiator group, the group ID must already be present in the Merchandising. Changes can be sent for header level updates or detail level updates. If the changes are at the header level, then all of the required header level information needs to be included in the update, similar to that described above for creating a new differentiator. However, the diff details should not be included in a header only update. Fields that can be updated at the header level using this API include:
-
Differentiator type
-
Differentiator group description
For updating the record, the diff group ID is required in the header level and diff ID is required at the detail level. Fields that can be updated at the detail level using this API include:
-
Display seq
Deleting Differentiator Groups
If you are deleting a differentiator detail in the differentiator group or deleting the whole differentiator group, then the API will validate that the differentiator group is valid and that it is not associated with any items or diff ranges. If you are deleting the whole differentiator group, then no details should be included in the message. If you are deleting a detail record on the differentiator, then validation will be done to ensure that the diff id exists on the differentiator group.
Table 3-8 Diff Group Header
Message Element | Required? | Notes |
---|---|---|
Differentiator Group Identifier |
Yes |
This field contains the diff group to be deleted. |
Differentiator Group Detail |
Conditional |
Child node This is required in a diff group detail delete message. |
Table 3-9 Diff Group Detail
Message Element | Required? | Notes |
---|---|---|
Differentiator ID |
Yes |
This field contains the identifier of the differentiator that will be deleted, that is contained within the differentiator group. This id must be unique within the diff group and must already exist in Merchandising. |
Error Handling
If any errors are encountered in the validations described above or any of the message structure validations, a status of E is returned to the external system along with the appropriate error message. If 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 Merchandising database.
Message XSD
Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
XDiffGrpDes |
Diff Group Create and Modify |
XDiffGrpDesc.xsd |
XDiffGrpDtl |
Diff Group Detail Create and Modify |
XDiffGrpDesc.xsd |
XDiffGrpRef |
Diff Group Delete |
XDiffGrpRef.xsd |
XDiffGrpDtlRef |
Diff Group Detail Delete |
XDiffGrpRef.xsd |
Differentiator Subscription API
This section describes the Diff ID subscription API.
Business Overview
This API subscribes to differentiators from external systems to create, update or delete differentiators in Merchandising. This subscription API provides a means to keep Merchandising in sync with an external system. These transactions are performed immediately upon message receipt so success or failure can be communicated to the calling application.
Creating Differentiators
When a new differentiator is created, this API will first validate that all required fields are present in the message. After that, business level validation on the input information will be performed. The business validation:
-
verifies the diff id does not contain white space or underscores
-
verifies if diff id is not already present as a diff id or diff group id
-
verifies the diff type is a valid value on the code detail table under code type DIFF
If all the validations are met, the differentiator in the message data is created in Merchandising.
The format for creating and updating differentiators is shown below.
Differentiator
Message Element | Required? | Notes |
---|---|---|
Differentiator ID |
Yes |
This field contains the unique identifier of the differentiator. This must be a valid value under the code type DIFF. |
Differentiator Type |
Yes |
This field contains the identifier of the differentiator type. This value must be a valid diff_type in merchandising. |
Differentiator Description |
Yes |
This field contains the description of the differentiator. |
Industry Code |
No |
This field contains the unique reference number which represents all possible combinations of sizes according to the National Retail Federation. |
Industry Subgroup |
No |
This field contains a sub-grouping code used by industry standards to further identify the differentiator. |
Create Date Time |
No |
This contains the date and time the differentiator was created. If this field is not populated on the message it will default to the time of creation in merchandising. |
Updating Differentiators
When a differentiator is updated, this API will first validate that all required fields are present in the message. After that, business-level validation on the input information will be performed. If all the validations are met, the differentiator in the message data are updated in Merchandising. The message and validation aresimilar to that of the creating differentiators.
Deleting Differentiators
When a differentiator is deleted, this API will first validate that all required fields are present in the message. After that, business level validation on the input information will be performed. The business validation:
-
verifies if diff id to be deleted exists in Merchandising
If all the validations are met, the differentiator in the message data is deleted from Merchandising.
Error Handling
If any errors are encountered in the validations described above or any of the message structure validations, a status of E is returned to the external system along with the appropriate error message. If 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 Merchandising database.
Message XSD
Here are the filenames that correspond with each message type. Please consult RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message Type | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
xdiffidcre |
Differentiator Create |
XDiffIDDesc.xsd |
xdiffidmod |
Differentiator Modify |
XDiffIDDesc.xsd |
xdiffiddel |
Differentiator Delete |
XDiffIDRef.xsd |
Direct Ship Receipt Subscription API
This section describes the Direct ship receipt subscription API.
Business Overview
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 Merchandising' subscription purposes. Merchandising 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.
Merchandising subscribes to integration subsystem direct ship receipt (DSR) messages. This records the inventory and financial transactions associated with the direct shipment of merchandise.
Package Impact
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.
Message XSD
Here are the filenames that correspond with each message type. Please consult RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
Dsrcptcre |
Dsrcpt Create Message |
DsrcptDesc.xsd |
Design Assumptions
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.
DSD Deals Subscription API
This section describes the DSD deals subscription API.
Integration Type
Oracle Retail Integration Bus (RIB)
Business Overview
Direct store delivery (DSD) is the 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 retailer's store. This process is common in convenience and grocery stores, where suppliers routinely come to restock merchandise. In these cases, the invoice may 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.
Merchandising subscribes to DSD messages from the RIB. These messages notify Merchandising 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. Merchandising also subscribes to DSD deals messages for deals applicable to any DSD order and performs the following functionalities as necessary:
-
Applies any deals to a DSD purchase order if the deals indicator in the message is set to Y
-
Creates a shipment
-
Receives a shipment.
-
Creates an invoice
Note:
Invoices are not created if Invoice Matching is not running, if the invoice indicator or paid indicator from the message is N, or if paid indicator on the message is Y and Sales Audit is not running.
Error Handling
If any errors are encountered in the validations described above or any of the message structure validations, a status of E is returned to the external system along with the appropriate error message. If 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 Merchandising database.
Message XSD
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 |
DSD Receipt Subscription API
This section describes the DSD receipt subscription API.
Business Overview
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 Merchandising of the receipt so a purchase order is created and it is counted in the store's inventory.
Package Impact
Filename: rmssub_dsds/b.pls
RMSSUB_DSD.CONSUME
PROCEDURE CONSUME(O_status_code IN OUT VARCHAR2, O_error_message IN OUT VARCHAR2, I_message IN RIB_OBJECT, I_message_type IN VARCHAR2)
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 Merchandising databases.
If the message type is DSD_MOD, call the GET_ORDER_NO function to find the order number for the DSD.
RMSSUB_DSD consumes "RIB_DSDReceiptDesc_REC" (message_types 'dsdreceiptcre' and 'dsdreceiptmod') and returns "RIB_DSDDealsDesc_REC" (message_type 'dsddealscre'), which is consumed by RMSSUB_DSDDEALS.
RMSSUB_DSDDEALS - calls APPLY_DEALS_TO_ORDER (dealordcall.pls). As part of the Merchandising16 SaaS C Library change requirement, APPLY_DEALS_TO_ORDER no longer invokes a ProC library function; instead it calls a PLSQL function DEAL_ORD_LIB_SQL.EXTERNAL_SHARE_APPLY_DEALS (dealordlibb.pls).
As such, RMSSUB_DSD does NOT need to publish back "RIB_DSDDealsDesc_REC". Instead, RMSSUB_DSD can call RMSSUB_DSDDEALS.CONSUME to complete the process of applying deals to order in a single transaction.
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 Merchandising 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.
Message XSD
Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
dsdreceiptcre |
DSD Receipt Create Message |
DSDReceiptDesc.xsd |
dsdreceiptmod |
DSD Receipt Modify Message |
DSDReceiptDesc.xsd |
Freight Term Subscription API
This section describes the freight terms subscription API.
Business Overview
Freight terms are financial arrangement information related to shipping that can be subscribed to by Merchandising 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, and so on. After confirming the validity of the records enclosed within the message, Merchandising is updated with the information.
Creating/Updating Freight Terms
When a new freight term is created, this API will first validate that all required fields are present in the message. Required fields are terms, description, enabled flag, and start and end dates. After required field validation, the freight term record in the message will be inserted if the term does not exist. If the freight term exists, then the dates and enabled flag will be updated.
Error Handling
If an error occurs in this procedure, a call will be placed to a function to build a complete error message. This message together with a status of E is returned to the external system. If 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 Merchandising database.
Message XSD
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 |
GL Chart of Accounts Subscription API
This section describes the GL chart of accounts subscription API.
Business Overview
Before Merchandising publishes stock ledger data to an external financial application, it must receive that application's General Ledger Chart Of Accounts (GLCOA) structure. Merchandising 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 the Merchandising product hierarchy. In some financial applications, this is known as Code Combination IDs (CCID). On receiving the GLCOA message data, Merchandising 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). Merchandising subscribes the GL chart of accounts information as published from the RIB and places the information in Merchandising tables depending upon the validity of the records enclosed within the message.
Package Impact
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).
Public API Procedures:
Filename: rmssub_glcoacreb.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 accepts an XML file in the form of a RIB Object 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 RIB Object. The values extracted from the RIB Object are then passed to private internal functions, which validate the values and place them on the GL chart of accounts table depending upon the success of the validation.
Private Internal Functions and Procedures (rmssub_glcoacreb.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)
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.
Private Internal Functions and Procedures (other):
Filename: rmssub_glcacctb.pls
Main Consume Function:
RMSSUB_GLCACCT.CONSUME (O_ERROR_MESSAGE OUT VARCHAR2, I_MESSAGE IN "RIB_GLCOADesc_REC")
This procedure accepts an XML file in the form of an RIB Object 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 then validates the XML file format and, if successful, parses the values within the RIB Object. The values extracted from the RIB Object are passed on to private internal functions, which will validate the values and place them on the appropriate GL chart of accounts database table, depending upon the success of the validation.
XML Parsing:
PARSE_HEADER (O_ERROR_MESSAGE OUT VARCHAR2, O_GLACCT_RECORD OUT GLACCT_RECTYPE, I_GLACCT_ROOT IN OUT "RIB_GLCOADesc_REC")
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.
Validation
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 Merchandising 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.
Message XSD
The GL chart of accounts message is a flat message that 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 |
GL Chart of Account Validation Service
This section describes the GL Chart of Accounts Subscription API.
Overview
When using Oracle Retail Financials Integration (RFI) to manage General Ledger integration an Oracle financial solution, a validation service is used to ensure that the segment combinations mapped to by Merchandising and Sales Audit users are valid combinations in the General Ledger. This validation is called from Merchandising and Sales Audit when creating General Ledger cross-reference mappings.
Note:
This validation is also used by Oracle Retail Invoice Matching.
As part of your implementation, you will need to configure the URL for the service call in the RETAIL_SERVICE_REPORT_URL table for code RAV. For cloud service implementations, configuration of this service call should be done in coordination with the Oracle Cloud Operations team by logging an SR. For more information, see the RFI Implementation Guide.
Inventory Adjustment Subscription API
This section describes the Inventory Adjustment Subscription API.
Business Overview
Merchandising receives requests for inventory adjustments from an integration subsystem through the Inventory Adjustment Subscription API. The requests contain information about the item and location whose inventory is being adjusted, the quantity to adjust, a from and to disposition code, and the reason for the adjustment. Merchandising uses information in these requests to:
-
Adjust overall quantities of stock on hand for an item at a location.
-
Adjust the availability of item-location quantities based on status.
After initial processing and validation, Merchandising performs the following tasks:
-
The item/location is ranged if it does not already exist.
-
For total stock on hand adjustments:
-
Stock on hand is updated for the item at the location, for total stock on hand adjustments.
-
Stock adjustment is recorded to the Merchandising transaction level stock ledger
-
-
For status-based adjustments:
-
Quantities by inventory status are adjusted for the item/location combination.
-
Non-sellable quantity is updated for the item/location.
-
-
For both types, an audit trail is created for the inventory adjustment by item, location, inventory status and reason.
Note:
An adjustment can impact both total stock on hand and inventory status at the same time.
Inventory Adjustment Transaction Codes
Whenever the status or quantity of inventory changes, Merchandising writes transaction codes to adjust inventory values in the stock ledger. The types of inventory adjustment transaction codes are:
-
Tran code 22 - Adjustments where positive and negative adjustments are made to total stock on hand using a reason code with the COGS indicator = N. In this case, a transaction is inserted to the transaction level stock ledger for both the retail and cost value of the adjustment.
-
Tran code 23 - Adjustments where positive and negative adjustments are made to total stock on hand using a reason code with the COGS indicator = Y. In this case, a transaction is inserted to the transaction level stock ledger for both the retail and cost value of the adjustment.
-
Tran code 25 - Adjustments to inventory status, where inventory is moved to or from an unavailable or non-sellable status.
Other Notes:
-
One or both of the to disposition and from disposition fields must have values. Both cannot be empty.
-
The item must be inventoried and approved.
-
If the item is a simple pack catch weight item, then the weight and weight UOM are either both defined or both NULL. Weight UOM must be of the type Mass.
-
The item is a transaction-level or a reference item. When a reference item is passed in, its parent item (the transaction level item) has its inventory adjusted.
-
If adjusting a pack at a warehouse, the pack item must have its inventory tracked at the pack level (receive as type = Pack for the item/warehouse).
-
If the location is a warehouse, then either a virtual or physical warehouse can be supported. If it is a virtual warehouse, it must be a stockholding warehouse. If it is a physical warehouse, then the adjusted quantity is distributed among the virtual locations of the physical location.
The table below contains the details of the message as well as the validations.
Table 3-10 Inventory Adjustment Header
Message Element | Required? | Notes |
---|---|---|
Destination ID |
Yes |
This field contains the location where the inventory adjustment is being made. |
Inventory Adjustment Detail |
Yes |
Child node |
Table 3-11 Inventory Adjustment Detail
Message Element | Required? | Notes |
---|---|---|
Item |
Yes |
This contains the item for which stock is being adjusted. The item should be an approved inventory item. |
Adjustment Reason Code |
No |
This field contains the reason for inventory adjustment. This field will only have a value for sellable inventory types. |
Unit Quantity |
Yes |
This field contains the number of units to be added or reduced. The value should not be 0 and should be a whole number if the standard UOM of the item is EA. |
Transshipment Number |
No |
Not used |
From Disposition |
Conditional |
From Disposition and To Disposition should both have values or both should be NULL. |
To Disposition |
Conditional |
From Disposition and To Disposition should both have values or both should be NULL. |
From Trouble Code |
No |
Not used |
To Trouble Code |
No |
Not used |
From WIP Code |
No |
Not used |
To WIP Code |
No |
Not used |
Transaction Code |
No |
Not used |
User ID |
Yes |
This field contains the name of the user who created the inventory adjustment. |
Create Date Time |
Yes |
This field contains the date and time the inventory adjustment was made. |
PO Number |
No |
This field contains either a PO, Allocation, BOL or Transfer number associated to the inventory adjustment based on the doc_type. The value should exist in Merchandising. |
Document Type |
No |
This field indicates the type of document where the inventory adjustment originated from. Valid values are:
|
Auxiliary Reason Code |
No |
Not used |
Weight |
Conditional |
This contains the weight of the item. Weight and Weight UOM should both have values or both should be NULL. |
Weight UOM |
Conditional |
This is the unit of measurement for weight. Weight and Weight UOM should both have values or both should be NULL. This value should belong to the uom_class, MASS. |
Unit Cost |
No |
This field contains the unit cost of the item. |
Inventory Adjustment UIN |
No |
Child node |
Table 3-12 Inventory Adjustment UIN
Message Element | Required? | Notes |
---|---|---|
Unique Identification Number |
Yes |
This field contains the Universal Identification Number of the item at the location. |
Status |
Yes |
This field contains the status code of the UIN. |
Error Handling
If any errors are encountered while publishing the message, a fatal error with status E (Error) is sent to RIB. The subscribing adapter notes this internally and rolls back all database work associated with the message. When the message is re-processed (because it has yet to be processed successfully), the adapter now recognizes this message is problematic and checks it into the RIB hospital.
Message XSD
Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
invadjustcre |
Inventory Adjustment Create Message |
InvAdjustDesc.xsd |
Inventory Request Subscription API
This section describes the inventory request subscription API.
Business Overview
Merchandising receives requests for inventory using the Inventory Request API, which allows for items to be ordered by the store and fulfilled by the Merchandising. Unlike store order replenishment, Merchandising fulfills inventory requests from the store regardless of replenishment review cycles, delivery dates, and any other factors that may restrict a request from being fulfilled.
For item/store combinations that are on the Store Order type of replenishment in Merchandising, orders will be placed using this API and then the replenishment process builds the recommended order quantity (ROQ) based on the store's requests. Requests that will not be reviewed prior to the date requested by the store are fulfilled through a one-off process (executed real-time through this API) that creates warehouse transfers and/or purchase orders to fulfill the requested quantities.
For item/location combinations that are currently using other methods of replenishment in Merchandising, the store requested quantities will be added on top of the calculated recommended order quantities to increase the overall replenishment. It can also be used for item/store combinations not on replenishment in Merchandising. In these cases, the one-off process described above will be used to create a POs or transfers utilizing attributes defined for the item/location.
Other validation notes:
-
Order quantities will be rounded using the store order multiple when an order is created for a warehouse or to the case size if ordering from the supplier.
-
Upcharges will always be applied to a transfer, when they can be defaulted.
-
Merchandising will validate that all items belong to the same department when department level ordering (supplier) or department level transfers (warehouse) are being used.
-
The store must be open for ordering.
Creating Inventory Requests
The table below summarizes these validations applicable for this API.
Message Element | Required? | Notes |
---|---|---|
Item |
Always |
The item must be approved, orderable, and inventoried item; it must also be ranged to the location in the inventory request and must be active at that location. |
Unit of purchase |
Always |
Unit of purchase must either be eaches (EA), case (CA), or pallet (PA). |
Need date |
Always |
This is the date that the store needs the item by. |
Need quantity |
Always |
This is the quantity being requested in standard UOM. |
Delivery slot |
Optional |
Valid delivery slots are in the delivery_slot table. |
Store |
Always |
The store must exist as a valid stockholding store in Merchandising. |
Request type |
Optional |
Store order (SO) or Inventory Request (IR) If the request type is SO or blank, then replenishment method should be store order. If the request type is IR, delivery slot should be provided. |
Error Handling
If an error occurs in this procedure, a call will be placed to a function to build a complete error message. This message together with a status of E is returned to the external system. If 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 Merchandising database. 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.
Message XSD
Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
InvReqCre |
Inventory Request Create Message |
InvReqDesc.xsd |
Item Subscription API
This section describes the item subscription API.
Design Overview
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_EXPENSES, SVC_ITEM_TICKET, 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, ITEM_EXP_HEAD, ITEM_EXP_DETAIL, ITEM_TICKET and ITEM_SEASONS 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 Merchandising 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 Merchandising 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.
L10N Localization Decoupling Layer:
Oracle Retail Fiscal Management (ORFM) is designed as an add-on product to Merchandising to handle Brazil-specific fiscal management.
Even though ORFM and Merchandising exist in the same database schema and ORFM cannot be installed separately without Merchandising, Oracle Retail ensures that Merchandising is decoupled from ORFM. This is so that non-Brazilian clients can install Merchandising without RFM. To achieve that, an L10N decoupling layer was introduced.
In the context of the XItem subscription API, when Merchandising 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, Merchandising's XItem subscription API (rmssub_xitem and related packages) call Mastersaf and/or ORFM through an L10N decoupling layer.
Import Brazil-specific Fiscal Item Attributes to the Flex Attributes Extension Table (ITEM_COUNTRY_L10N_EXT):
XItem API supports the importing of Brazil fiscal item attributes to Merchandising 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, Merchandising also exposes an Item Management web service to allow an external application to create, update, and reclassify items in Merchandising. The web service takes in a collection of items (except for reclass item) and will return success and failure through the service response object.
Package Impact
This section describes the package impact.
Consume Module
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 Merchandising. Each supported operation will invoke the public interfaces in the ItemManagementServiceProviderImpl package as follows:
-
createItem
-
modifyItem
-
createSupplier
-
modifySupplier
-
deleteSupplier
-
createSupplierCountry
-
modifySupplierCountry
-
deleteSupplierCountry
-
createSupplierCountryDim
-
modifySupplierCountryDim
-
deleteSupplierCountryDim
-
createItemReclass
-
deleteItemReclass
-
createItemReclassDetail
-
deleteItemReclassDetail
-
createUDA
-
modifyUDA
-
deleteUDA
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.
The item reclassification interfaces are an exception. These call the corresponding procedures in svcprov_xitem, which will in turn call rmssub_xitemrcls.consume to do the reclassification 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 Merchandising 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 Merchandising 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 'Merchandising' (default), which indicates that the message will be uploaded to the Merchandising item tables, or 'STG' which means that the message will only be inserted into the Merchandising staging tables for further enrichment, without data validation. Loading of records from staging to Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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.
Bulk or Single DML Module
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)
-
SVC_ITEM_EXPENSES(optional)
-
SVC_ITEM_TICKET(optional)
-
SVC_ITEM_EXPENSES(optional)
-
SVC_ITEM_TICKET(optional)
-
SVC_ITEM_CHRG(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 Merchandising 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 Merchandising 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 Merchandising base tables.
ITEM_IMAGE CREATE
-
Inserts records into the SVC_ITEM_IMAGE table. The records will then be inserted into the corresponding Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising 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 Merchandising base table.
ITEM_EXPENSES CREATE
-
Inserts records into the SVC_ITEM_EXPENSES table. The records will then be used to create records in the corresponding Merchandising base table.
ITEM_EXPENSES MODIFY
-
Inserts records into the SVC_ITEM_EXPENSES table. The records will then be used to update records in the corresponding Merchandising base table.
ITEM_EXPENSES DELETE
-
Inserts records into the SVC_ITEM_EXPENSES table. The records will then be used to delete records from the corresponding Merchandising base table.
ITEM_TICKET CREATE
-
Inserts records into the SVC_ITEM_TICKET table. The records will then be used to create records in the corresponding Merchandising base table.
ITEM_TICKET MODIFY
-
Inserts records into the SVC_ITEM_TICKET table. The records will then be used to update records in the corresponding Merchandising base table.
ITEM_TICKET DELETE
-
Inserts records into the SVC_ITEM_TICKET table. The records will then be used to delete records from the corresponding Merchandising 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 Merchandising 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 Merchandising base table.
ITEM UP CHARGE CREATE
-
Inserts records into the SVC_ITEM_CHRG table. The records will then be used to create records in the corresponding Merchandising base table.
ITEM UP CHARGE MODIFY
-
Inserts records into the SVC_ITEM_CHRG table. The records will then be used to update existing records in the corresponding Merchandising base table.
ITEM UP CHARGE DELETE
-
Inserts records into the SVC_ITEM_CHRG table. The records will then be used to delete existing records from the corresponding Merchandising base table.
Message XSD
Below are the filenames that correspond with each message type. These are the message types available through RIB. 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) |
---|---|---|
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 |
XItemExpensesCre |
Item/Expenses create message |
XItemDesc.xsd |
XItemExpensesMod |
Item/Expenses modify message |
XItemDesc.xsd |
XItemExpensesDel |
Item/Expenses delete message |
XItemRef.xsd |
XItemTicketCre |
Item/Ticket create message |
XItemDesc.xsd |
XItemTicketMod |
Item/Ticket modify message |
XItemDesc.xsd |
XItemTicketDel |
Item/Ticket delete message |
XItemRef.xsd |
XItemSeasonCre |
Item/Seasons create message |
XItemDesc.xsd |
XItemSeasonDel |
Item/Seasons delete message |
XItemRef.xsd |
xitemchgcre |
Item up charge create message |
XItemDesc.xsd |
xitemchgdtlmod |
Item up charge modify message |
XItemDesc.xsd |
xitemchgdel |
Item up charge delete message |
XItemRef.xsd |
These are the message types supported by the Item Management Web Service. Refer to "Package Impact" "Consume Module" section for the list of procedures that correspond to these message types.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
createItem |
Create Item Service Operation |
XItemDesc.xsd |
createSupplier |
Create Item Supplier Service Operation |
XItemDesc.xsd |
modifySupplier |
Modify Item Supplier Service Operation |
XItemDesc.xsd |
deleteSupplier |
Delete Item Supplier Service Operation |
XItemRef.xsd |
createSupplierCountry |
Create Item Supplier Country Service Operation |
XItemDesc.xsd |
modifySupplierCountry |
Modify Item Supplier Country Service Operation |
XItemDesc.xsd |
deleteSupplierCountry |
Delete Item Supplier Country Service Operation |
XItemDesc.xsd |
modifyItem |
Modify Item Service Operation |
XItemDesc.xsd |
createSupplierCountryDim |
Create Item Supplier Country Dimension Service |
XItemDesc.xsd |
modifySupplierCountryDim |
Modify Item Supplier Country Dimension Service |
XItemDesc.xsd |
deleteSupplierCountryDim |
Delete Item Supplier Country Dimension Service |
XItemRef.xsd |
createItemReclass |
Create Item Reclass Service Operation |
XItemRclsDesc.xsd |
deleteItemReclass |
Delete Item Reclass Service Operation |
XItemRclsRef.xsd |
createItemReclassDetail |
Create Item Reclass Detail Service Operation |
XItemRclsDesc.xsd |
deleteItemReclassDetail |
Delete Item Reclass Detail Service Operation |
XItemRclsRef.xsd |
createUDA |
Create Item UDA Service Operation |
XItemDesc.xsd |
modifyUDA |
Modify Item UDA Service Operation |
XItemDesc.xsd |
deleteUDA |
Delete Item UDA Service Operation |
XItemRef.xsd |
Design Assumptions
-
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.
-
Item reclassification is not addressed by this API; they are addressed in a separate Item Reclassification Subscription API.
Tables
TABLE | SELECT | INSERT | UPDATE | DELETE |
---|---|---|---|---|
SVC_ITEM_MASTER |
Yes |
Yes |
Yes |
No |
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_EXPENSES |
Yes |
Yes |
Yes |
Yes |
SVC_ITEM_TICKET |
Yes |
Yes |
Yes |
Yes |
SVC_ITEM_SEASONS |
Yes |
Yes |
Yes |
Yes |
SVC_ITEM_IMAGE_TL |
Yes |
Yes |
Yes |
Yes |
SVC_ITEM_CHRG |
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_EXP_HEAD |
Yes |
Yes |
Yes |
Yes |
ITEM_EXP_DETAIL |
Yes |
Yes |
Yes |
Yes |
ITEM_TICKET |
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 |
ITEM_CHRG_HEAD |
Yes |
Yes |
Yes |
Yes |
ITEM_CHRG_DETAIL |
Yes |
Yes |
Yes |
Yes |
Item Location Subscription API
This section describes the item location subscription API.
Business Overview
This API subscribes to item location from external systems to create or modify item location combinations in Merchandising. Item/location relationships can be created for an item and a single location or using one of the levels of the organizational hierarchy.
Creating Locations
When a new item location is created, this API will first validate that all required fields are present in the message. Additionally, when creating a new item location at least one detail line must also be included in the message. After that, business level validation on the input information will be performed. The tables below summarize these two types of validation.
Table 3-13 Header Level Validation
Message Element | Required? | Notes |
---|---|---|
Item |
Yes |
Must be an existing item in Merchandising. |
Hier Level |
Yes |
Must be a valid organization hierarchy level. Valid values are chain (CH), area (AR), region (RE), district (DI), store (S) or warehouse (W). |
Table 3-14 Detail Level Validation
Message Element | Required? | Notes |
---|---|---|
Hier Value |
Yes |
Must be valid ID for a chain, area, region, district, store or warehouse given the Hier Level. |
Status |
Yes |
Must be either active (A), inactive (I), discontinued (C). |
Store Ord Mult |
Yes |
Must either be inner (I), case (C), or each (E). |
Source Method |
Conditional |
Must either be supplier (S) or warehouse (W). Additionally, valid value is dependent on the Hier Level: For Hier Level of store (S):
For Hier Level of warehouse (W)
Source Method must be W if the Source Warehouse is populated. |
Source Warehouse |
Conditional |
Must be a valid virtual warehouse. Source Warehouse is required if Source Method is warehouse. Source Warehouse should be blank when Hier Level is warehouse or Source Method is supplier. Additionally, item should already be ranged to the Source Warehouse. |
Receive as Type |
Conditional |
Valid values are pack (P) or eaches (E). Must be blank if Item is not a pack or Hier Level is not warehouse. Must be eaches (E), when Hier Level is warehouse and Hier Value is an internal finisher. Must be pack (P), when Item is a vendor pack. Must be pack (P), when Item order as type is pack and warehouse break pack indicator is N. |
Taxable Indicator |
No |
Must be yes (Y) or no (N), if populated. |
UIN Type |
No |
Must be a valid code from code_type = UINT if populated:
|
UIN Label |
Conditional |
UIN Label is required if UIN Type is populated. Must be a valid code from code_type = ULBL if populated:
|
Ext UIN Indicator |
No |
Valid values are Y or N. Must not be no (N) if Capture Time is (S). |
Primary Supplier |
No |
Primary Supplier and Primary Country relationship should exist for an orderable item and is required for consignment or concession items. |
Primary Country |
No |
Primary Supplier and Primary Country relationship should exist for an orderable item and is required for consignment or concession items. |
Local Item Description |
No |
|
Ti |
No |
Must be a valid numeric value |
Hi |
No |
Must be a valid numeric value |
Daily Waste Percent |
No |
Must be a valid numeric value |
Local Short Description |
No |
|
UIN Type |
No |
|
UIN Label |
No |
|
Capture Time |
No |
|
Unit Cost |
No |
Must be a valid numeric value |
Cost UOM |
No |
|
Purchase Type |
No |
Valid values are Owned (0), Consignment (1) , Concession (2). Must be 0 if the Consignment/Concession system option = N. |
Calculation Basis |
No |
Valid values are Cost Per Unit (C) or Purchase Rate (P). |
Purchase Rate |
No |
Must only be populated if Calculation Basis is Purchase Rate (P). |
Promotable Indicator |
No |
Valid values are Y or N. |
Table 3-15 Item Location Trait Validation
Message Element | Required? | Notes |
---|---|---|
Launch Date |
No |
|
Quantity Key Options |
No |
Valid values are in code type RPO. |
Manual Price Entry |
No |
Valid values are in code type RPO. |
Deposit Code |
No |
Valid values are in code type DEPO. |
Food Stamp |
No |
Valid values are Y or N. |
WIC |
No |
Valid values are Y or N. |
Proportional Tare Percent |
No |
|
Fixed Tare Value |
No |
|
Fixed Tare UOM |
No |
Must be a valid UOM. |
Reward Eligible |
No |
Valid values are Y or N. |
National Brand Competitor Item |
No |
Must be a valid Item. |
Return Policy |
No |
Valid values are in code type RETP. |
Stop Sale |
No |
Valid values are Y or N. |
Electronic Market Clubs |
No |
Valid values are in code type MKTC. |
Report Code |
No |
Valid values are in code type REPC. |
Shelf Life on Selection |
No |
|
Shelf Life on Receipt |
No |
|
Investment Buy (IB) Shelf Life |
No |
|
Store Reorderable |
No |
Valid values are Y or N. |
Rack Size |
No |
Valid values are in code type RACK. |
Full Pallet Item |
No |
Valid values are Y or N. |
In Store Market Basket |
No |
Valid values are in code type STMB. |
Storage Location |
No |
Alternate Storage Location |
Returnable |
No |
Valid values are Y or N. |
Refundable |
No |
Valid values are Y or N. |
Back Order |
No |
Valid values are Y or N. |
Updating Item Location
When updating an item location, this API will first validate that all required fields are present in the message. Additionally, when updating an item location at least one detail line must also be included in the message. After that, business level validation on the input information will be performed. The tables below summarize these two types of validation.
Table 3-16 Header Level Validation
Message Element | Required? | Notes |
---|---|---|
Item |
Yes |
Same as create. |
Hier Level |
Yes |
Same as create. |
Table 3-17 Detail Level Validation
Message Element | Required? | Notes |
---|---|---|
Hier Value |
Yes |
Same as create |
Status |
Yes |
Must be either active (A), inactive (I), discontinued (C), deleted (D). |
Store Ord Mult |
Yes |
Same as create |
Source Method |
Conditional |
Same as create |
Source Warehouse |
Conditional |
Same as create |
Receive as Type |
Conditional |
Same as create |
Taxable Indicator |
No |
Same as create |
UIN Type |
No |
Same as create |
UIN Label |
Conditional |
Same as create |
Ext UIN Indicator |
No |
Same as create |
Primary Supplier |
No |
Same as create |
Primary Country |
No |
Same as create |
Local Item Desc |
No |
Same as create |
Ti |
No |
Same as create |
Hi |
No |
Same as create |
Daily Waste Pct |
No |
Same as create |
Local Short Desc |
No |
Same as create |
UIN Type |
No |
Same as create |
UIN Label |
No |
Same as create |
Capture Time |
No |
Same as create |
Unit Cost |
No |
Same as create |
Cost UOM |
No |
Same as create |
Purchase Type |
No |
Same as create |
Calculation Basis |
No |
Same as create |
Purchase Rate |
No |
Same as create |
Promotable Indicator |
No |
Same as create |
Table 3-18 Item Location Trait Validation
Message Element | Required? | Notes |
---|---|---|
Launch Date |
No |
Same as create |
Qty Key Options |
No |
Same as create |
Manual Price Entry |
No |
Same as create |
Deposit Code |
No |
Same as create |
Food Stamp Ind |
No |
Same as create |
Wic Ind |
No |
Same as create |
Proportional Tare Pct |
No |
Same as create |
Fixed Tare Value |
No |
Same as create |
Fixed Tare UOM |
No |
Same as create |
Reward Eligible Ind |
No |
Same as create |
Natl Brand Comp Item |
No |
Same as create |
Return Policy |
No |
Same as create |
Stop Sale Ind |
No |
Same as create |
Elect Mkt Clubs |
No |
Same as create |
Report Code |
No |
Same as create |
Req Shelf Life on Selection |
No |
Same as create |
Req Shelf Life on Receipt |
No |
Same as create |
Ib Shelf Life |
No |
Same as create |
Store Reorderable Ind |
No |
Same as create |
Rack Size |
No |
Same as create |
Full Pallet Item |
No |
Same as create |
In Store Market Basket |
No |
Same as create |
Storage Location |
No |
Same as create |
Alt Storage Location |
No |
Same as create |
Returnable Ind |
No |
Same as create |
Refundable Ind |
No |
Same as create |
Back Order Ind |
No |
Same as create |
Flex Attributes
If you have defined any custom flex attributes (CFAS) for item locations, then they can be integrated as part of this API. The node of the integration that supports this will accept the name of the attribute as it is defined in the group set level view and the value for the attribute. For date type CFAS attributes, there is a separate value column to pass the value.
Error Handling
If any errors are encountered in the validations described above or any of the message structure validations, a status of E is returned to the external system along with the appropriate error message. If 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 Merchandising database.
Message XSD
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 modification |
XItemlocDesc.xsd |
Item Reclassification Subscription API
This section describes the item reclassification subscription API.
Design Overview
Merchandising subscribes to item reclassification messages, which update the department, class, and/or subclass for the item, that are published by an external system. This subscription is necessary in order to keep Merchandising in sync with the external system. This API allows external systems to create and delete item reclassification events within Merchandising.
Only the following item types can be interfaced using this API:
-
Transaction level items without a parent
-
Parent items, whose child items are the transaction level, such as with a fashion style (parent) and its SKUs (children)
-
Complex pack items - but the reclassification cannot include the component items in the pack
The following item types cannot be reclassified:
-
Child items with a parent - these are reclassified when the parent is updated
-
Reference items - these below transaction level items are automatically reclassified with the transaction level item or its parent, whichever applies
-
Simple Packs - these are reclassified when the component item is reclassified
This API allows a reclassification event to be created for a department/class/subclass combination that does not yet exist. This is valid as long as the merchandise hierarchy is scheduled to be created on or prior to the reclassification taking effect.
New/Updated Reclassifications
When a reclassification is created, both a reclassification header and detail are required. For an update, either to update the header or add a detail, all fields in both header and detail nodes are required.
Reclassification Header
Message Element | Required? | Notes |
---|---|---|
Reclass Number |
Always |
This is the unique number which identifies the reclassification event. For a detail create message, the reclassification number should already exist in Merchandising. |
Reclass Description |
Always |
This is the description of the reclassification event. |
Reclass Date |
Always |
The date on which the reclassification event is scheduled to take place. The date must be at least a day after the current business day. |
To Department |
Always |
The department to which the item will belong after the reclassification event. The dept/class/subclass combination must already exist or will be created before the reclassification date. |
To Class |
Always |
The class to which the item will belong after the reclassification event. The dept/class/subclass combination must already exist or will be created before the reclassification date. |
To Subclass |
Always |
The subclass to which the item will belong after the reclassification event. The dept/class/subclass combination must already exist or will be created before the reclassification date. |
Item Reclassification Detail |
Always |
Child node |
Reclassification Detail
Message Element | Required? | Notes |
---|---|---|
Item |
Always |
This is the item that will be reclassified. Following are the validations that will be done on upload: Must be a level 1 item If the item is a pack, it should not be a simple pack. Must not be on any approved order Must not be an orderable buyer pack that can be received as component items. Must not be on an existing reclassification |
Deleting Reclassifications
Reclassifications can be deleted by:
-
Deleting a single reclassification event
-
Deleting specific items on a reclassification event
-
Deleting all reclassification events on a particular event date
-
Deleting all reclassification events
Reclassification Header
Message Element | Required? | Notes |
---|---|---|
Reclass Number |
Conditional |
The reclassification event that will be deleted. If this is populated in a header delete message, then the specific reclassification event will be deleted. This is required in a header delete message if Reclass Date is NULL and the Purge All indicator is N or NULL. If Reclass Number is provided, Reclass Date should be NULL. |
Reclass Date |
Conditional |
The date of the reclassification events to be deleted. If this is populated in a header delete message, then all reclassification events occurring on the given date will be deleted. This is required in a header delete message if the Reclass Number is NULL and the Purge All indicator is N or NULL. If Reclass Date is provided, Reclass Number should be NULL. |
Purge All |
Conditional |
If this field is Y in a header delete message, then all item reclassification events will be deleted. When the purge indicator is Y, both Reclass Number and Reclass date should be NULL. If the Purge All indicator is NULL or N, only the Reclass Number or the Reclass date should contain a value. |
Reclassification Detail |
Optional |
Child Node |
Reclassification Detail
Message Element | Required? | Notes |
---|---|---|
Item |
Conditional |
This is the item within a reclassification event that will be deleted. This is the only field required for detail delete messages as an item can only exist in one reclassification event. The entire reclassification event will be deleted when no item detail exists after detail deletion. |
Message XSD
Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message 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 |
Location Trait Subscription API
This section describes the location trait subscription API.
Design Overview
The Location Trait Subscription API processes incoming data from an external system to create, edit and delete location traits in Merchandising. This data is processed immediately upon message receipt so success or failure can be communicated to the external application.
The table below contains the details of the message as well as the validations.
Table 3-19 Location Traits
Message Element | Required? | Notes |
---|---|---|
Trait ID |
Yes |
This field contains the unique id number of the location trait. |
Trait Description |
Yes |
This field contains the description of the location trait. |
Message XSD
Here are the filenames that correspond with each message type. Please consult RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message 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.
Merchandise Hierarchy Subscription API
This section describes the merchandise hierarchy subscription API.
Business Overview
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 Merchandising is not the system of record for merchandise hierarchy information, 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 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 a table, where a background process (Daily Purge of Foundation Data) will occur to ensure the records can be deleted as part of the delete process. For more on this batch process, see the Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs.
If you are implementing Merchandising with Simple VAT as the default tax type, then department-level 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.
Creating a Company
When a new company is created, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed, which checks that the company doesn't already exist. If the validation is met, the company in the message data is created. Only one company can exist in Merchandising.
Updating a Company
When a company is updated, this API will first validate that all required fields are present in the message. Business level validation on the input information will be performed, which verifies if the company ID to be updated already exists. If the company already exists, the company's details in the message data are updated.
Creating Divisions
When a new division is created, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed. The business validation:
-
Verifies division is not already present.
-
Verifies that, if total market amount is received, then it should be at least 1000.
If all the validations are met, the division in the message data is created.
Updating Divisions
When a division is updated, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed. The business validation:
-
Verifies division is present.
-
Verifies that, if total market amount is received, then it should be at least 1000.
If all the validations are met, the division's details are updated.
Deleting Divisions
When a division is deleted, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed, which verifies if division to be deleted already exists. If it exists, the message deletes the division from the merchandise hierarchy.
Creating Groups
When a new group is created, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed, which checks whether the group already exists. If it does not exist, the group in the message data is created.
Updating Groups
When a group is updated, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed, which verifies whether the group to be updated already exists. If group already exists, the group details are updated.
Deleting Groups
When a group is deleted, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed, which verifies if the group to be deleted already exists. If it exists, the message deletes the group from the merchandise hierarchy.
Creating Departments
When a new department is created, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed. The business validation:
-
Verifies the department is not already present.
-
Verifies if total market amount is received then it should be at least 1000.
-
Verifies the child messages required fields are present. The child messages contains the VAT and upcharge details for a department.
If all the validations are met, the department in the message data is created. Custom flex attributes can also be created for the department through this API, if they are active for the department.
Updating Departments
When a department is updated, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed. The business validation:
-
Verifies if the department is present.
-
Verifies if total market amount is received then it should be at least 1000.
-
Verifies the child messages required field is already present. The child messages contains VAT and upcharge details for a department.
If all the validations are met, the department in the message data is created. Like with create, custom flex attributes can also be updated for the department, if active.
Deleting Departments
When a department is deleted, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed to verify if the department to be deleted already exists. If it exists, the department will be added to a purging staging table for processing in the Daily Purge of Foundation Data process. Flex attributes are deleted when the department is deleted.
Creating VAT Information for Departments
If you are configured to run Merchandising using Simple VAT (SVAT) for your default tax type, then you can create set the VAT rates by region for the department using this API. If included, this API will check for all required fields in the message and create the VAT record for a department. When adding new VAT region to an existing department with attached items, the VAT information will default to all items.
Updating VAT Information for Departments
If you are configured to run Merchandising using Simple VAT (SVAT) for your default tax type, then you can update the VAT rates by region for the department using this API. This API will check for all required fields in the message and updates the VAT information for a department. When updating VAT details for a department with attached items, the VAT information will default to all items.
Creating Up-charges for Departments
When a message contains up-charge details, first it validates for the required fields, including "from" locations and "to" locations in the message. If no up-charge record is found, this message creates the up-charge for a department and from/to location combination. As part of the addition, you can indicate in the message if you want to have the up-charges added to existing items or only added for new items. Similarly there is an flag in the message to indicate whether the new upcharges will be cascaded to transfers and allocations which are unshipped and not in closed or deleted status. The department upcharges will be created as soon as the message is consumed, however the new upcharges will be cascaded to items, transfers, and allocations via batches which runs at the end of every day.
Updating Up-charges for Departments
When a message contains up-charge details, first it validates for the required field in the message. If up-charge record exist for a department and the from/to location combination in the message, then the up-charge details are updated for the department. As part of the update there is also an option to have the up-charges updated for items in the department, or unshipped transfers and allocations for items in the department. The department upcharges will be updated as soon as the message is consumed, however the updates will be cascaded to items, transfers, and allocations via batches which runs at the end of every day.
Deleting Up-charges for Departments
When a message contains an up-charge, first it validates for the required field in the message. If up-charge record exists for a department, this message deletes the upcharge details for a department. Deleting up-charges from a department does not automatically remove them from the items or transfers and allocations for items in the department.
Creating Classes
When a new class is created, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed, which checks whether the class already exists. If the class does not exist, the class in the message data is created. Custom flex attributes can also be created for the class through this API, if they are active for the class.
Updating Classes
When a class is updated, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed, which verifies if the class to be updated already exists. If class already exists, the class details are updated. Like with create, custom flex attributes can also be updated for the class, if active.
Deleting Classes
When a class is deleted, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed to verify:
-
The class exists.
-
It is not associated to a diff group.
-
It is not associated to a season.
-
It is not associated to a ticket type.
-
It is not associated to a UDA.
If these validations pass, the class will be added to a purging staging table for processing in the Daily Purge of Foundation Data process. Flex attributes are deleted when the class is deleted.
Creating Subclasses
When a new subclass is created, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed, which checks whether the subclass already exists. If the subclass does not exist, then it is created. Custom flex attributes can also be created for the subclass through this API, if they are active for the subclass.
Updating Subclasses
When a subclass is updated, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed which verifies if the subclass to be updated already exists. If subclass already exists, it is updated. Like with create, custom flex attributes can also be updated for the subclass, if active.
Deleting Subclasses
When a subclass is deleted, this API will first validate that all required fields are present in the message. Business-level validation on the input information will be performed to:
-
Verify the subclass already exists.
-
Verify the subclass is not associated to a diff group.
-
Verify the subclass is not associated to a season id.
-
Verify the subclass is not associated to a ticket type.
-
Verify the subclass is not associated to a UDA.
If these validations pass, the subclass will be added to a purging staging table for processing in the Daily Purge of Foundation Data process. Flex attributes are deleted when the subclass is deleted.
Error Handling
If any errors are encountered in the validations described above or any of the message structure validations, a status of E is returned to the external system along with the appropriate error message. If 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 Merchandising database.
Message XSD
Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
xmrchhrcompcre |
External Create Company |
XMrchHrCompDesc.xsd |
xmrchhrcompmod |
External Modify Company |
XMrchHrCompDesc.xsd |
xmrchhrdivcre |
External Create Division |
XMrchHrDivDesc.xsd |
xmrchhrdivmod |
External Modify Division |
XMrchHrDivDesc.xsd |
xmrchhrdivdel |
External Delete Division |
XMrchHrDivRef.xsd |
xmrchhrgrpcre |
External Create Group |
XMrchHrGrpDesc.xsd |
xmrchhrgrpmod |
External Modify Group |
XMrchHrGrpDesc.xsd |
xmrchhrgrpdel |
External Delete Group |
XMrchHrGrpRef.xsd |
xmrchhrdeptcre |
External Create Department |
XMrchHrDeptDesc.xsd |
xmrchhrdeptmod |
External Modify Department |
XMrchHrDeptDesc.xsd |
xmrchhrdeptdel |
External Delete Department |
XMrchHrDeptRef.xsd |
Xmrchhrvatcre |
External Merch Hierarchy VAT create |
XMrchHrDeptDesc.xsd |
xmrchhrvatmod |
External Merch Hierarchy VAT modify |
XMrchHrDeptDesc.xsd |
xmrchhrdeptchrgcre |
External Merch Hier Dept Up-Charge create |
XMrchHrDeptDesc.xsd |
xmrchhrdeptchrgmod |
External Merch Hier Dept Up-Charge modify |
XMrchHrDeptDesc.xsd |
xmrchhrdeptchrgdel |
External Merch Hier Dept Up-Charge delete |
XMrchHrDeptRef.xsd |
xmrchhrclscre |
External Create Class |
XMrchHrClsDesc.xsd |
xmrchhrclsmod |
External Modify Class |
XMrchHrClsDesc.xsd |
xmrchhrclsdel |
External Delete Class |
XMrchHrClsRef.xsd |
xmrchhrsclsdel |
External Delete Subclass |
XMrchHrSclsRef.xsd |
xmrchhrsclsmod |
External Modify Subclass |
XMrchHrSclsDesc.xsd |
xmrchhrsclsdel |
External Delete Subclass |
XMrchHrSclsRef.xsd |
Merchandise Hierarchy Reclassification Subscription API
This section describes the merchandise hierarchy reclassification subscription API.
Business Overview
This API allows Merchandising to subscribe merchandise hierarchy reclassification messages, that are published by an external system. It is intended to be used by retailers who manage their hierarchies in a system outside Merchandising. 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 creates or modifies the merchandise hierarchy information in Merchandising once the effective date arrives.
Creating Merchandise Hierarchy Reclassifications
When a new merchandise hierarchy reclassification is created, the API will first validate that all required fields are present in the message. Certain of the fields are required regardless of hierarchy level, while others are dependent on other hierarchy configurations. After that, business level validation on the input information will be performed. The tables below summarizes the validation.
Table 3-20 Header Level Validation
Message Element | Required? | Notes |
---|---|---|
Merchandise Hierarchy Level |
Always |
Indicates the level of merchandise hierarchy. Valid values are V (division), G (group), D (department), C (class), and S (subclass). |
Merchandise Hierarchy ID |
Always |
Holds the merchandise hierarchy ID, of the merchandise hierarchy component being created or updated. |
Merchandise Hierarchy parent ID |
Always |
This field will hold the parent of the hierarchy identified in the Merchandise Hierarchy ID field. This column will only be populated if the Merchandise Hierarchy level is class or subclass. |
Merchandise Hierarchy Grandparent ID |
Conditional |
This field will hold the grandparent ID of the hierarchy identified in the Merchandise Hierarchy ID field. This column will only be populated if the Merchandise Hierarchy Level is subclass. |
Merchandise Hierarchy Name |
Always |
The name of the hierarchy value. |
Effective Date |
Always |
The date the hierarchy change will become effective. The effective date must be greater than or equal to the current date. |
Action Type |
Conditional |
Indicates if this field is an addition (A) or modification (M). It is required on a create message and should not be populated on a modify message. |
Buyer |
Conditional, Optional |
The number of the buyer associated with the entity. This value must be predefined in Merchandising. This field should only hold a value if the hierarchy level indicates division, group, or department. |
Purchase Type |
Conditional |
The code indicates whether items in the department will be created by default as normal merchandise (0), consignment (1), or concession (2). This field is required if the hierarchy level indicates department, otherwise it should be null. Additionally, if the Consignment/Concession system option is set to N, this should always be 0. |
Total Market Amount |
Optional |
This field stores total market amount that is expected for the entity. This field will only be used if the hierarchy value indicates division or department. |
Merchandiser |
Conditional, Optional |
This field indicates the number of the merchandiser associated with the entity. This value must be predefined in Merchandising. This field should hold a value only if the hierarchy level indicates division, group, or department. |
Budgeted Markup Percentage |
Conditional |
This field stores the markup percent of cost. Budgeted Markup Percentage or Budgeted Intake Percentage cannot be both null or both have values. This field is required if the hierarchy level indicates department, otherwise it should be null. |
Profit Calculation Type |
Conditional |
Indicates whether profit will be calculated by direct cost (1) or retail inventory (2). This field is required for a new department, otherwise it should be null. A Department cannot be set up as profit calculation type of Direct Cost and purchase type of Consignment Stock. |
Markup Calculation Type |
Conditional |
Indicates how markup is calculated in the department. Valid values are for a new department, otherwise it should be null. |
OTB Calculation Type |
Conditional |
Indicates how open to buy is calculated in the department. Valid values are cost (C) and retail (R). This field is required for a new department, otherwise it should be null. |
Maximum Average Counter |
Conditional |
The maximum count of days with acceptable data to include in an average for items with the department. This field is required for a new department, otherwise it should be null. The value cannot be a negative. |
Average Tolerance Percentage |
Conditional |
The tolerance percentage value used in averaging for items within a department. This field will only be used for a new department. The value cannot be a negative. |
Budgeted Intake Percentage |
Conditional |
Indicates the markup percent of retail to use as a default for the department. Budgeted Markup Percentage or Budgeted Intake Percentage cannot be both null or both have values. This field is required for a new department, otherwise it should be null. |
Department VAT Inclusive Indicator |
Conditional |
Indicates the default value for the class VAT indicator. When classes are initially set up, they will inherit this value. This field will only be populated when the hierarchy level indicates department. |
Class VAT Indicator |
Conditional |
Indicates whether retail is displayed and held with or without VAT for items within a class. Valid values are Y (yes) and N (no). This field is required if the hierarchy level indicates department and you are configured for Simple VAT or Global Tax and must be set to Y in those cases. If you are configured for US Sales Tax, then it must be N. |
Updating Merchandise Hierarchy Reclassifications
For updating a previously created reclassification, the hierarchy type must be already present in Merchandising. For updates, the validation is similar to that described above for creating a new reclassification. If updating the effective date of a reclassification that has an Add action type, there should not be any child hierarchy with earlier effective date. For example, if you are adding a department, there cannot be a reclassification for adding a class in the department with an earlier effective date.
Deleting Merchandise Hierarchy Reclassifications
To delete a previously created reclassification event, the below validations will be executed to ensure there are no conflicts, along with checks for the existence of child reclassification records, before deleting the record.
Message Element | Required? | Notes |
---|---|---|
Merchandise Hierarchy Level |
Always |
Indicates the level of merchandise hierarchy. Valid values are V (division), G (group), D (department), C (class), and S (subclass). |
Merchandise Hierarchy ID |
Always |
Holds the merchandise hierarchy ID for the selected level. |
Merchandise Hierarchy parent ID |
Optional |
This field will hold the parent of the hierarchy identified in the Merchandise Hierarchy ID field. This column will only be populated if the Merchandise Hierarchy Level is class or subclass. |
Merchandise Hierarchy Grandparent ID |
Optional |
This field will hold the grandparent ID of the hierarchy identified in the Merchandise Hierarchy ID field. This column will only be populated if the Merchandise Hierarchy Level is subclass. |
Error Handling
If any errors are encountered in the validations described above or any of the message structure validations, a status of E is returned to the external system along with the appropriate error message. If 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 Merchandising database.
Message XSD
Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
xmrchhrclscre |
Create Merchandise Hierarchy Reclassification |
XMrchHrRclsDesc.xsd |
xmrchhrclsmod |
Modify Merchandise Hierarchy Reclassification |
XMrchHrRclsDesc.xsd |
xmrchhrclsdel |
Delete Merchandise Hierarchy Reclassification |
XMrchHrRclsRef.xsd |
Organizational Hierarchy Subscription API
This section describes the organizational hierarchy subscription API.
Business Overview
If Merchandising 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 based on an external system. The organization hierarchy subscription also assigns existing location traits to, or deletes them from the stores in the given organization hierarchy level.
The following organizational hierarchy elements can be created, modified, or deleted using this API: chain, area, region, or district. The organizational hierarchy must be created from the highest level down. Conversely, the hierarchy must be deleted from the lowest level up.
The following validation is applicable for the data sent in this API:
Message Element | Required? | Notes |
---|---|---|
Hierarchy Value |
Always |
Indicates the ID of the hierarchy component being added, modified, or deleted. It is validated based on the hierarchy level attribute included. |
Hierarchy Description |
Conditional |
Indicates the description for the hierarchy component. This is required for create only. For modifications, it is optional. For delete, it is ignored. |
Hierarchy Level |
Always |
Indicates the level of the hierarchy being created, updated, or deleted. Valid values are CH (chain), AR (area), RE (region) and DI (district). |
Parent |
Conditional |
Identifies the parent level of the hierarchy for this component. For example, if the hierarchy level is district, this would be the ID of the region for the district. |
Manager Name |
Optional |
Name of the manager for this hierarchy component. |
Currency Code |
Optional |
The code that identifies the currency under which the hierarchy component operations. |
Message XSD
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 Hierarchy |
XOrgHrDesc.xsd |
XOrgHrLocTrtCre |
External Create Location Trait |
XOrgHrDesc.xsd |
XOrgHrDel |
External Delete Organizational Hierarchy |
XOrgHrRef.xsd |
XOrgHrLocTrtDel |
External Delete Location Trait |
XOrgHrRef.xsd |
XOrgHrMod |
External Modify Organizational Hierarchy |
XOrgHrDesc.xsd |
Order Subscription API
This section describes the PO subscription API.
Business Overview
This subscription API is used to keep Merchandising in sync with an external system that is responsible for maintaining purchase orders. It is assumed that the source of orders sent in this API is not the supplier, as vendor managed inventory (VMI) POs can be sent using the Upload Purchase Order and Purchase Order Change Acknowledgements from Suppliers to Merchandising (ediupack) batch upload. It also does not support creating customer order POs or contract POs. Customer order POs are assumed to be sent using the Customer Order Fulfillment Subscription API and contract orders are created using replenishment processes in Merchandising or manually using the UI.
POs can be created, modified or deleted at the header or the detail level. This API also creates, edits, and deletes other data associated with a purchase order, including letter of credit, expenses, harmonized tariff schedules (HTS) and assessments, and custom flex attributes (CFAS). It also will apply rounding rules, default inventory management parameters, apply bracket costs, update open to buy buckets, and insert a record into the deals queue for deals to be applied to the order, if applicable. These transactions are performed immediately upon receipt of the message so that success or failure can be sent back to the calling application.
If the location on a purchase order is a franchise store, a corresponding franchise order is also created along with the PO.
Creating Purchase Orders
New purchase order messages pass through a series of validations such as required field and valid value validations for each field as well as business validations. The tables below summarize these validations.
Table 3-21 Header Level Validation
Message Element | Required? | Notes |
---|---|---|
Order Number |
Always |
Must be a unique order number not used by any existing purchase orders in Merchandising. |
Supplier |
Always |
Must be a valid, active supplier in Merchandising. The supplier and locations on the order must belong to the same org unit. If the EDI PO indicator is set to Y, the supplier of the order must also be an EDI supplier. |
Currency Code |
Optional |
Must be a valid currency code. If not provided, this defaults to the currency code used by the supplier. |
Terms |
Optional |
Must be a valid payment terms in Merchandising. If terms is not provided in the message, the API will default this to supplier terms. |
Not Before Date |
Optional |
Must be equal to or after the current date and before the Not After Date, if provided. If the date is not provided, the API will default the value to the current date + default supplier lead time if there are not items attached to the order. If items are already added to the order, it will be defaulted to current date + minimum lead time + minimum pickup lead time among all items in the order. |
Not After Date |
Optional |
Must be equal to or after the current date and after the Not Before Date, if provided. If the date is not provided, the API will default the value to the current date + default supplier lead time if there are not items attached to the order. If items are already added to the order, it will be defaulted to current date + maximum lead time + maximum pickup lead time among all items in the order. |
OTB End of Week Date |
Optional |
Must be a valid end of week date and equal to or after the current date. If the date is not provided, the API will default the value to the last day of the week that the not after date value falls in. |
Department |
Optional |
Must be a valid department in Merchandising. The department field should not be populated if items belonging to different departments are present in the order. Department is required if the Department Level PO system option is Y. |
Status |
Optional |
Valid statuses are Worksheet (W), Submitted (S), or Approved. If not provided, the status will be defaulted to W. Status of closed (C) also allowed, but for order updates only. |
Exchange Rate |
Optional |
The exchange rate should be greater than zero. If not provided, and the currency code is provided, the exchange rate will be based on the given currency code and primary currency. If the currency code is not provided, then the exchange rate will default based on the supplier’s currency code and primary currency. |
Include On Order Indicator |
Optional |
Valid values are Y and N. If not provided in the message, it will be defaulted to Y. |
Written Date |
Optional |
If not provided in the message, it will be defaulted to the current date. |
Origin Indicator |
Optional |
Valid values for origin indicator are: 0 – Merchandising generated PO (from replenishment) 1 – Other system generated PO 2 – Manual purchase order 3 – Buyer worksheet PO 4 – Consignment sales generated PO 5 – Vendor generated PO 6 – AIP generated PO 7 – SIM generated PO 8 – Allocation generated PO 9 – Consignment transfer generated PO 10 – Consignment ownership change generated PO The expected values for this field in purchase order subscription are 1, 2, 6, 7 and 8. If it is not provided in the message, it will be defaulted to 2. |
EDI PO Indicator |
Optional |
Valid values are Y and N. If not provided in the message, it will be defaulted to the supplier’s EDI PO indicator. |
Pre Mark Indicator |
Optional |
Valid values are Y and N. If not provided in the message, it will be defaulted to N. If Y, then the order must be pre-allocated before it is approved. |
User ID |
Optional |
If not passed into the message, then a value will be defaulted for auditing purposes. |
Comment |
Optional |
|
Attempt RMS Load |
Optional |
If not passed into the message, then it will be defaulted to RMS, which means that the information will persist to the Merchandising tables as opposed to the staging tables. Valid values are RMS and STG. |
Master PO number |
Optional |
This is can be used for linking multiple orders together for multiple delivery date orders. |
Lading Port |
Optional |
If not provided, this will be defaulted based on supplier import attributes. If provided, it should be a valid lading port in Merchandising. |
Discharge Port |
Optional |
If not provided, this will be defaulted based on supplier import attributes. If provided, it should be a valid discharge port in Merchandising. |
Factory |
Optional |
If not provided, this will be defaulted based on supplier import attributes. If provided, it should be a valid, active factory in Merchandising. |
Agent |
Optional |
If not provided, this will be defaulted based on supplier import attributes. If provided, it should be a valid, active agent in Merchandising. |
Ship Method |
Optional |
If not provided, this will be defaulted based on supplier attributes. This must be a valid ship method in Merchandising, which are stored in the codes table under the code type SHPM. |
Partner Type 1 |
Optional |
This should be provided if partner 1 is given. Valid values are S1, S2 and S3. These are stored under the codes table under the code type SUHL. |
Partner 1 |
Optional |
If not provided, this will be defaulted based on supplier import attributes. If provided, it should be a valid, active partner in Merchandising for the given partner type. Partner and partner type should be provided, or both should be null. |
Partner Type 2 |
Optional |
This should be provided if partner 2 is given. Valid values are S1, S2 and S3. These are stored under the codes table under the code type SUHL. |
Partner 2 |
Optional |
If not provided, this will be defaulted based on supplier import attributes. If provided, it should be a valid, active partner in Merchandising for the given partner type. Partner and partner type should be provided, or both should be null. |
Partner Type 3 |
Optional |
This should be provided if partner 3 is given. Valid values are S1, S2 and S3. These are stored under the codes table under the code type SUHL. |
Partner 3 |
Optional |
If not provided, this will be defaulted based on supplier import attributes. If provided, it should be a valid, active partner in Merchandising for the given partner type. Partner and partner type should be provided, or both should be null. |
Payment Method |
Optional |
Valid values are stored under the code type PYMT in the codes table. If not provided, this will default to the supplier payment method. |
Purchase Type |
Optional |
Valid values are stored under the code type PURT in the codes table. If not provided, this will default to the purchase type defined at the supplier inventory management level. |
FOB Title Pass |
Optional |
This is required for import orders with payment method of Letter of Credit. If this is not provided in the message, it will default to the FOB Title Pass defined at the system level. Valid values are stored under the code type FOBT in the codes table. |
FOB Title Pass Desc |
Optional |
This is required for import orders with payment method of Letter of Credit. If this is not provided in the message, this will default to the FOB Title Pass Description defined at the system level. |
PO Type |
Optional |
This should be a valid PO Type in Merchandising. Valid PO Types are found in the PO_TYPE table. |
Import Country ID |
Optional |
This is required for import orders. The value should be a valid country in Merchandising. If the value is not provided in the message, it will default to the country ID of the primary address of the location in the order. |
Table 3-22 Detail Level Validation
Message Element | Required? | Notes |
---|---|---|
Item Number |
Required |
Must be an approved, orderable transaction level item in Merchandising supplied by the supplier indicated in the message. The Item Number should not be in the process of being deleted and must be active at the location specified in the message. |
Location |
Required |
Must be a valid stockholding store or virtual warehouse in Merchandising. |
Location Type |
Required |
Valid values are store (S) and warehouse (W). |
Unit Cost |
Optional |
Must be greater than or equal to zero. If more than one virtual warehouse for the same physical warehouse are included in the details for the order, then the unit cost must be the same for those item/warehouses. |
Reference Item |
Optional |
Must be a valid, approved reference item of the item being ordered and should not be in the process of being deleted. The Reference Item should be supplied by the supplier in the message. |
Origin Country ID |
Optional |
Must exist as a valid country for the supplier/item provided in the message. If the country is not provided in the message, the value is defaulted to the item’s primary country of sourcing. |
Supplier Pack Size |
Optional |
Must be greater than zero. If there are several order lines with the same item in the message, the supplier pack size and origin country of these records should all be the same. If not provided in the message, the API will default the value based on the supplier/country for the item. |
Quantity Ordered |
Required |
Must be greater than zero. |
Cancel Indicator |
Optional |
This is used for purchase order detail modification only. |
Reinstate Indicator |
Optional |
This is used for purchase order detail modification only. |
Delivery Date |
Optional |
If the above validation passes, then the purchase order and details will be created with the status set in the message. If the status in the message is approved, then the order will also be subjected to a series of approval checks. If the order cannot be approved, it will not be created.
You can also include information on the order - letter of credit (if the payment method is letter of credit), landed cost expenses, and for import orders you can include HTS and assessments. If included, these will be created simultaneously with the creation of the order.
Updating Purchase Orders
Updates can be made either at header level or at detail level for orders that are in Worksheet, Approved or Closed status. For both kinds of update messages, the API will validate that the order number included in the message already exists in Merchandising while item number and location will be also validated for existence in detail level updates.
Header Level Updates
Only header level fields need to be provided for header-level updates. Any order details included in the message will be ignored for a header-level update message. There are certain fields that are not allowed to be updated at header level depending on the status, and if these are still provided in the message, no error message will be returned. The values will simply be ignored. However, modifying the following header level fields is allowed made while the order is submitted or approved, without having to set the order in worksheet status:
-
status
-
not before date
-
not after date
-
terms
-
include on-order indicator
-
comments
Detail Level Updates
Order details can be updated for orders in Approved, Worksheet, Submitted or Closed status. The only information needed at the header level is the order number, which if not provided, will cause the message to be rejected. All other details provided at the header level will be ignored. Modifying order quantity, as well as supplier pack size or unit cost on an approved or submitted order will in effect set the order status to worksheet and subject it for automatic re-approval. When modifying order quantities, the full amount should be provided, not just the difference in the old and new values. Validations are also done on quantity changes, such as the ordered quantity should not go below the allocated quantity or replenishment quantity, quantity ordered cannot not be less than quantity received.
Fields that can be modified in worksheet, submitted and approved status:
-
Supplier Pack Size
-
Unit Cost - for items with no received quantities
-
Quantity Ordered
Fields that cannot be modified in statuses other than worksheet:
-
Origin Country ID
-
Location
Fields that can be modified only in approved status:
-
Quantity Cancelled
-
Cancel Code
Reinstating Order Lines
To reinstate orders, the reinstate indicator should be set to Y. In effect, this will set the cancelled quantities of the line items to 0 and reinstate the ordered quantities. This will set the status of the reinstated order to Worksheet.
Cancelling a Line Item in an Approved Order
In order to cancel a line item on the order, you can set the cancel indicator at the detail level to Y and at the same time, the quantity ordered for that line item must be set to 0. For partial cancellations, either reduce the quantity of an approved order or populate the quantity cancelled field making sure the cancel indicator is blank or set to N. This will allow for the automatic re-approval of the entire order, if there are line items still on the order once processed by the API. The cancel indicator and reinstate indicator cannot be set to Y at the same time.
Deleting Purchase Orders
If you are deleting a line item on the purchase order or deleting the whole purchase order, the API will first validate that the order number is valid. The order number is the only required field for a header delete message. All other fields will be ignored. For detail delete messages, you must provide the item as well and optionally, the location. These should exist in Merchandising, or else a reject message will be returned.
Deleting the Entire Order
In order to delete an entire order, you must send a header delete message. This will in effect set the status of the order at the header level to D. Only worksheet orders can be deleted. Deleting the purchase order cannot be done if the order is submitted, approved or has been approved, or if allocations exist for the order. Delete messages will still be processed, however it will be treated as an update of cancelled quantity and the quantity ordered will be reduced to the quantity available to be cancelled. If this results in all line items being cancelled or if the delete is made at header level, the status of the order will become Closed.
If an order is still in worksheet status, the entire order will be deleted. If the order involves any franchise stores, then any franchise order or return created with the order will also be cancelled or deleted.
Deleting a Line Item
If an order is still in worksheet status, line items will be deleted from the order. If all line items are deleted, the order header will also be deleted. For orders that are not in worksheet status, when a detail delete is requested, it will update the quantities to cancelled quantities and will be subject for re-approval.
Creating a Purchase Order Letter of Credit
A letter of credit may be created together with the creation of a new order or added to an existing order with a payment method of Letter of Credit. In order to create/edit/delete a letter of credit, the order should be in worksheet status. Below are the validations:
Message Element | Required? | Notes |
---|---|---|
Letter of Credit Reference ID |
Optional |
The reference ID must be exist in Merchandising for the given beneficiary and applicant. The Free on Board title pass description and purchase type in the letter of credit table must match that of the values in the order. |
Letter of Credit Group ID |
Optional |
If included, must be a valid value in Merchandising. |
Applicant |
Always |
The applicant must be an active partner in Merchandising. |
Beneficiary |
Always |
Must be a valid active supplier who can be a beneficiary. |
Merchandise description |
Always |
|
Transshipment Indicator |
Always |
Valid values are Y or N. |
Letter of credit indicator |
Always |
Valid values are Y or N. |
Updating a Purchase Order Letter of Credit
In order to update an order letter of credit, the letter of credit must exist for the order in Merchandising, otherwise, an error will be returned. All fields identified in the create section above are updateable and will go through the same validation as in the creation of a letter of credit.
Deleting a Purchase Order Letter of Credit
In order to delete an order letter of credit, the letter of credit must exist for the order in Merchandising, otherwise, an error will be returned.
Creating Expenses
Expenses may be created together with the creation of a new order or added to an existing order that has location records defined. In order to create/edit/delete expenses, the order should be in worksheet status. Below are the validations:
Message Element | Required? | Notes |
---|---|---|
Item |
Always |
The item/location/location type combination must be present in the order. For buyer packs with an order as type of Pack, this should be a component item in the pack and the item on the order should be present in the pack item field. |
Pack item |
Optional |
If provided, the item/pack item/location/location type combination must be present in the order. This is required if the item on the order is a buyer pack with an order as type of Pack. |
Location |
Always |
The item/location/location type combination must be present in the order. |
Location type |
Always |
The item/location/location type combination must be present in the order. Valid values are S or W. |
Component ID |
Always |
Must be a valid expense component in Merchandising. This should be present in ELC_COMP. |
CVB code |
Conditional |
Required if the component rate calculation basis is value (V), otherwise this will be defaulted to NULL. Must be a valid CVB code in Merchandising. |
Cost basis |
Optional |
Valid values are supplier (S) or order (O). If CVB code is provided, then this should be null. |
Component rate |
Optional |
This will be defaulted based on the component if not provided. |
Component currency |
Optional |
This will be defaulted based on the component if not provided. If it is present in the message, this must be a valid currency code. |
Exchange rate |
Optional |
This should be the exchange rate used in relation to the location on the order. If this is not provided in the message, the API defaults it, depending on the order exchange indicator set at system level. If the indicator is Y, it defaults based on the component currency. If the component currency of the component is the same as that of the location, then exchange rate should be 1. If the system level indicator is set to N, the exchange rate will be based on the location currency . |
Per count |
Optional |
If the component rate calculation basis is specific (S), it is defaulted based on the expensed component if not provided in the message. |
Per count UOM |
Optional |
If the component rate calculation basis is specific (S), it is defaulted based on the expense component if not provided in the message. This must be a valid unit of measure. |
Nominal flag 1 |
Optional |
This will be defaulted based on the expense component if not provided. If it is present in the message, this must be N,+, or -. |
Nominal flag 2 |
Optional |
This will be defaulted based on the expense component if not provided. If it is present in the message, this must be N,+, or -. |
Nominal flag 3 |
Optional |
This will be defaulted based on the expense component if not provided. If it is present in the message, this must be N, +, or -. |
Nominal flag 4 |
Optional |
This will be defaulted based on the expense component if not provided. If it is present in the message, this must be N, +, or -. |
Nominal flag 5 |
Optional |
This will be defaulted based on the expense component if not provided. If it is present in the message, this must be N,+, or -. |
Updating Expenses
In order to update expenses, the order/item/location/component ID must exist for the order in Merchandising, otherwise, an error will be returned. All fields identified in the create section above except for order/item/pack item/location/component id are updateable and will go through the same validation as in the creation of expenses.
Deleting Expenses
In order to delete expenses, the order/item/location/component ID must exist for the order in Merchandising, otherwise, an error will be returned.
Creating HTS and Assessments
HTS and Assessment may be created together with the creation of a new import order or added to an existing order that has location records defined. In order to create/edit/delete HTS and assessments, the order should be in worksheet status. Below are the validations:
Table 3-23 HTS Validation Message Elements
Message Element | Required? | Notes |
---|---|---|
Item |
Always |
The item must exist on the order. For buyer packs with an order as type of Pack, this should be a component item in the pack and the item on the order should be present in the pack item field. |
Pack item |
Conditional |
The item must exist on the order. This is required if the item on the order is a buyer pack with an order as type of Pack. |
HTS |
Always |
Must be a valid HTS code for the supplier’s import country in Merchandising |
Status |
Always |
Valid values are Worksheet (W) or Approved (A). |
Origin Country ID |
Optional |
Must be a valid country in Merchandising. If the HTS tracking level based on country of manufacture, this should be a valid country of manufacture for the item/supplier. If it is not provided, it will default to the item's primary manufacturing country. If the HTS tracking level is country of sourcing, it will default to the primary sourcing country for the item/supplier. |
Import Country ID |
Optional |
Must be a valid country in Merchandising. If not provided, this will default to the import country ID at the order level. |
Table 3-24 HTS Assessment Validation
Message Element | Required? | Notes |
---|---|---|
Component ID |
Always |
Must be a valid assessment component ID in Merchandising. |
Component rate |
Optional |
This will be defaulted based on the component if not provided. |
Per count |
Optional |
This is defaulted to the component when the calculation basis is specific (S), otherwise this will be defaulted to NULL. |
Per count UOM |
Optional |
This is defaulted to the component when the calculation basis is specific (S), otherwise this will be defaulted to NULL. |
CVB code |
Optional |
This is defaulted to the component when the calculation basis is value (V), otherwise this will be defaulted to NULL. |
Nominal flag 1 |
Optional |
This will be defaulted based on the assessment component if not provided. If it is present in the message, this must be N, +, or -. |
Nominal flag 2 |
Optional |
This will be defaulted based on the assessment component if not provided. If it is present in the message, this must be N, +, or -. |
Nominal flag 3 |
Optional |
This will be defaulted based on the assessment component if not provided. If it is present in the message, this must be N,+, or -. |
Nominal flag 4 |
Optional |
This will be defaulted based on the assessment component if not provided. If it is present in the message, this must be N, +, or -. |
Nominal flag 5 |
Optional |
This will be defaulted based on the assessment component if not provided. If it is present in the message, this must be N, +, or -. |
Updating HTS and Assessments
In order to update HTS and assessments, the record to be updated must exist in Merchandising, otherwise, an error will be returned. Status and origin country ID can be updated at the HTS level. For assessments, all fields identified in the create section above except for component ID are updateable and will go through the same validation as in the creation of assessments.
Deleting HTS and Assessments
In order to delete HTS and assessments, expenses, the record to be deleted must exist in Merchandising, otherwise, an error will be returned.
Publishing Updates
Purchase orders will be published back to the RIB if approved or previously approved, such that system responsible for managing the purchase orders are notified.
Flex Attributes
If custom flex attributes (CFAS) have been defined for purchase orders, or at the order/item or order/item/location level, then they can be integrated as part of this API. The node of the integration that supports this will accept the name of the attribute as it is defined in the group set level view and the value for the attribute. Flex attributes can only be added to or updated on a purchase order at header and detail levels but cannot be deleted.
Error Handling
If any errors are encountered in the validations described above or any of the message structure validations, a status of E is returned to the external system along with the appropriate error message. If 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 Merchandising database.
Message XSD
Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
XorderCre |
Order Create Message |
XOrderDesc.xsd |
XorderCre (CustFlexAttriVo) |
Order Flex Attribute Create Message for the Order Header |
XOrderDesc.xsd |
XorderDtlCre |
Order Detail Create Message |
XOrderDesc.xsd |
XorderDtlCre (CustFlexAttriVo) |
Order Detail Flex Attribute Create message |
|
XorderLCCre |
Order LC Create Message |
XOrderDesc.xsd |
XorderLocExpCre |
Order Location Expense Create Message |
XOrderDesc.xsd |
XorderSkuHtsCre |
Order SKU HTS Create Message |
XOrderDesc.xsd |
XorderSkuHtsAssessCre |
Order SKU HTS Assess Create Message |
XOrderDesc.xsd |
XorderMod |
Order Modify Message |
XOrderDesc.xsd |
XorderMod (CustFlexAttriVo) |
Order Flex Attribute Modify Message for the order header |
XOrderDesc.xsd |
XorderDtlMod |
Order Detail Modify Message |
XOrderDesc.xsd |
XorderDtlMod (CustFlexAttriVo) |
Order Detail Flex Attribute Modify Message |
XOrderDesc.xsd |
XorderLCMod |
Order LC Modify Message |
XOrderDesc.xsd |
XorderLocExpMod |
Order Location Expense Modify Message |
XOrderDesc.xsd |
XorderSkuHtsMod |
Order SKU HTS Modify Message |
XOrderDesc.xsd |
XorderSkuHtsAssessMod |
Order SKU HTS Assess Modify Message |
XOrderDesc.xsd |
XorderDel |
Order Delete Message |
XOrderRef.xsd |
XorderDtlDel |
Order Detail Delete Message |
XOrderRef.xsd |
XorderLCDel |
Order LC Delete Message |
XOrderRef.xsd |
XorderLocExpDel |
Order Location Expense Delete Message |
XOrderRef.xsd |
XorderSkuHtsDel |
Order SKU HTS Delete Message |
XOrderRef.xsd |
XorderSkuHtsAssessDel |
Order SKU HTS Assess Delete Message |
XOrderRef.xsd |
Payment Terms Subscription API
This section describes the payment terms subscription API.
Integration Type
Oracle Retail Integration Bus (RIB)
Business Overview
Payment terms are supplier-related financial arrangement information that can be subscribed to by Merchandising from a financial system. Payment terms are the terms established for paying a supplier (for example, 2.5% for 30 days) for purchase orders. After confirming the validity of the records enclosed within the message, Merchandising updates its tables with the information.
Creating Payment Terms
When a new payment term is subscribed to by Merchandising, it will first validate that all required fields are present in the message. Payment terms details should also be present when creating a new payment term, and when creating and updating a new payment term detail. After that, business level validation on the input information will be performed. The tables below summarize these two types of validations.
Table 3-25 Header Level Validation
Message Elements | Required? | Notes |
---|---|---|
terms |
Always |
This represents the unique ID to track this payment term in Merchandising. |
terms code |
Always |
This is value is intended to hold the code in the financial system. |
terms desc |
Always |
Description of the supplier terms |
rank |
Always |
Unique number to rate invoice payment terms against purchase order terms |
Table 3-26 Message Elements
Message Elements | Required? | Notes |
---|---|---|
due max amount |
Always |
This is the maximum amount due by a certain date |
percent |
Always |
Percentage discount if payment is made within the time frame |
terms sequence |
Optional |
The order in which to apply the discount percent |
due days |
Conditional |
This is the number of days until payment is due. The following due days data combinations are valid:
|
due day of month |
Conditional |
Day of month used to calculate due date of invoice payment line. The following due days data combinations are valid:
|
due months forward |
Conditional |
Number of months ahead used to calculate due date of invoice payment line. The following due days data combinations are valid:
|
discount days |
Conditional |
This is the number of days in which payment must be made in order to receive the discount. The following discount days data combinations are valid:
|
discount day of month |
Conditional |
Day of month used to calculate discount date for invoice payment line The following discount days data combinations are valid:
|
discount months forward |
Conditional |
Number of months ahead to calculate discount date for invoice payment line. The following discount days data combinations are valid:
|
fixed date |
Optional |
This is the fixed due date |
enabled flag |
Always |
This flag should be 'Y' if the start active date is less than or equal to the current date and the end date is greater than or equal to the current date. Otherwise, the enabled flag should be 'N'. |
start active date |
Optional |
Start active date must be less than end active date. |
end active date |
Optional |
End date should be greater than start active date. |
cutoff day |
Optional |
This is the last day before payment is scheduled |
Payment term details sent via the detail message can also be added when a payment term already exists in Merchandising. If the terms detail being added already exists, an error is raised.
After the message passes all validations, the payment terms are inserted into the Merchandising tables.
Updating Payment Terms
Payment terms can be updated at header or detail level. When updating at the header level, the payment term details for the term being updated should already exist in Merchandising. When updating payment term details, the term header and detail must already exist.
After the message passes all validations, the payment terms in Merchandising are updated. Rank, terms, code, and terms desc are the values that can be updated at header level. At the detail level, due days, due max amount, due months forward, discount days, percent, discount day of month, discount months forward, fixed date, enabled flag, start active date, end active date and cutoff day may be updated.
Error Handling
If an error occurs in this procedure, a call will be placed to a function to build a complete error message. This message together with a status of E is returned to the external system. If 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 Merchandising database.
Message XSD
Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
PayTermCre |
Payment Terms Create Message |
PayTermDesc.xsd |
PayTermDtlCre |
Payment Terms Detail Create Message |
PayTermDesc.xsd |
PayTermMod |
Payment Terms Modify Message |
PayTermDesc.xsd |
PayTermDtlMod |
Payment Terms Detail Modify Message |
PayTermDesc.xsd |
Receiving Subscription API
This section describes the receiving subscription API.
Functional Area
Receipt subscription:
-
Purchase Order Receiving.
-
Stock Order Receiving (including Transfers and Allocations).
Business Overview
Merchandising 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 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
-
Unwanded cartons (those that have not been scanned)
-
Misdirected container (those that are shipped to one store and received at another store)
-
Zero receipt
Once Merchandising 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.
Carton-Level Receiving
The process for handling carton level receipts is as follows:
-
Merchandising determines whether a message type contains a receipt or an appointment.
-
If a receipt, Merchandising determines whether the document type is purchase order (P), transfer (T), or allocation (A).
-
If a stock order (transfer or allocation), Merchandising 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: Merchandising receives all cartons associated with the BOL along with their contents (line items).
-
Bill of lading/with cartons: Merchandising 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), Dummy BOL (D), or Closed (C).
Overage (O)
The carton does not belong to the current BOL. Merchandising attempts to match the contents with the correct BOL.
-
If the carton belongs to a BOL at the given location, Merchandising receives the carton against the correct BOL at the given location.
-
If the carton belongs to a BOL at a related walk-through store, Merchandising receives the carton against the intended BOL at the intended location.
-
If the carton belongs to a BOL at an unrelated location, Merchandising uses the wrong store receiving process.
Dummy BOL (D)
Cartons were received under a dummy bill of lading (BOL) number. Merchandising attempts to match the contents with a valid BOL.
-
If the carton belongs to a valid BOL at the given location, Merchandising 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, Merchandising receives the carton against the intended BOL at the intended location.
-
If the carton belongs to a valid BOL at an unrelated location, Merchandising uses the wrong store receiving process.
Closed (C)
The BOL or the carton is closed. It indicates that no more receipts are expected against the BOL or the carton. Merchandising will adjust any outstanding shipped-but-not-received quantity to accurately reflect the stock position.
The wrong_st_receipt_ind system option controls whether wrong store receiving is available in Merchandising. 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.
Misdirected Container
When a carton is shipped to one store but received at another store, the Store system (for example, SIM) can send the original carton ID in the ref_container_id field of RIB_ReceiptDtl_REC for Merchandising to identify and reconcile the original shipment and receive the items into the actual location. This is only supported for item-level receiving of stock orders at stores when the wrong_st_receipt_ind system option is set to Y. If the intended store sends a BOL-level or carton-level zero receipt to report the missing item, the zero receipt may arrive before or after the misdirected container receipt:
-
Zero receipt comes before the misdirected container receipt: when Merchandising processes the zero receipt, it will adjust any outstanding shipped-but-not-received quantity at the intended store; when Merchandising processes the misdirected container receipt, it will receive the items as overage at the actual store, because the original SHIPSKU has already been adjusted.
-
Zero receipt comes after the misdirected container receipt: the zero receipt will have no effect, because the original SHIPSKU has already been received and there is nothing to adjust.
Blind Receipt Processing
A blind receipt is generated by an external application whenever a movement of goods is initiated by that application. Merchandising has no prior knowledge of blind receipts. Merchandising 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.
Doc Types
Receipts are processed based upon the document type indicator in the message. The indicator serves as a flag for RMSSUB_RECEIPT.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
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. Merchandising 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 Merchandising processes a BOL message. At the receiving time, financial transaction records will only need to be written for the overage receiving.
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.
When receiving a customer order at stores, SIM will send a receipt message to both Merchandising and OMS, using a new message type of 'receiptordadd'. Merchandising will process 'receiptordadd' message in the same way as 'receiptadd'.
L10N Localization Decoupling Layer
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 Merchandising to be installed without requiring customers to install or use this localization functionality, where not required.
Package Impact
Filename: rmssub_receivings/b.pls
CRMSSUB_RECEIVING.ONSUME(O_status_code IN OUT VARCHAR2, O_error_message IN OUT VARCHAR2, I_message IN "RIB_ReceiptDesc_REC", I_message_type IN VARCHAR2)
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: 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 Merchandising (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.
Message XSD
Here are the filenames that correspond with each message type. Please consult RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
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 Merchandising 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 Impact
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 |
RTV Subscription API
This section describes the RTV subscription API.
Business Overview
Merchandising subscribes to return-to-vendor (RTV) messages when an RTV is shipped out from a warehouse or store. This shipment could be for an RTV that was initially created in Merchandising or one initiated in the store or warehouse. The RTV information is sent from a warehouse management system (WMS), such as Oracle WMS Cloud, or the store inventory solutions, such as Oracle Retail Store Inventory and Operations Cloud Service (SIOCS) when the RTV is shipped out of the location.
Note:
Unlike other RIB messages, both new and updates sent through this message use the RTVCre message type.New RTVs
If the message contains a new RTV generated in the store or warehouse, then it must contain both header and detail information. RTV create messages can only be sent in Approved or Shipped status.
Updated RTVs
If this is an update to an RTV, it can be performed through this API. To update an RTV, you can send either the header information only or both header and detail information. The most common update is to ship a previously created RTV. Approved RTVs can be Shipped if the RTV is created in Merchandising or SIOCS.
It is assumed that RTVs from the warehouse are always created in Shipped status.
Note:
Once RTVs are shipped, they cannot be changed back to Approved. Alternatively, approved RTVs can also be set to Cancelled status, if for some reason they cannot be shipped.RTV Header
Message Element | Required | Notes |
---|---|---|
Destination ID |
Always |
Contains the location shipping the RTV - either the store ID or the physical warehouse ID. |
RTV ID |
Always |
This contains the external reference number for RTVs created outside of Merchandising. |
Return Authorization Number |
Optional |
Contains the number that the supplier provides when the decision is made that the merchandise may be returned. This will be required depending on the configuration of the supplier site in Merchandising. |
Vendor Number |
Always |
Contains the supplier site ID to which the merchandise is being returned. The site must be configured in Merchandising to allow returns. |
Address 1 |
Optional |
Contains the first line of the supplier's address for returns. If not provided, the address for an externally created RTV will be pulled from the supplier address information in Merchandising. This applies for all of the below address fields as well. |
Address 2 |
Optional |
Contains the second line of the supplier's address for returns. |
Address 3 |
Optional |
Contains the third line of the supplier's address for returns. |
State |
Optional |
Contains the state of the supplier's address for returns. The state and country combination must be valid. |
City |
Optional |
Contains the city of the supplier's address for returns. |
Postal Code |
Optional |
Contains the postal code of the supplier's address for returns. |
Country |
Optional |
Contains the country ID of the supplier's address for returns. The state and country combination must be valid. |
Creation Time Stamp |
Optional |
Contains the date the vendor return was created. This defaults to current date if not specified for an externally generated RTV. |
Comments |
Optional |
Contains any comments associated with the return. |
RTV Order Number |
Optional |
This contains the RTV ID generated by Merchandising when the event was created. For externally generated RTVs, this should be NULL. |
Status |
Optional |
This value is used to determine the current status of an externally generated RTV. Valid values are Approved (A) or Shipped (S). If this is Approved, Merchandising will create the RTV and set it to an In Progress status. If this is Shipped or null, it will be set to Shipped status when created or updated. |
RTV Details
Message Element | Required | Notes |
---|---|---|
Item ID |
Always |
Unique identifier for the item on the RTV. The item must be a transaction level inventory item or reference item. |
Unit Quantity |
Always |
Contains the quantity of the item being returned to the supplier under this RTV number. A quantity less than zero indicates a quantity cancellation. If the RTV is already in progress, the quantity in the message must not be zero. |
Container Quantity |
Optional |
Not used in Merchandising |
From Disposition |
Optional |
This value is used to determine if the inventory is available or unavailable. Valid values are based on the INV_STATUS values in the INV_STATUS_CODES table. |
To Disposition |
Optional |
Not used in Merchandising |
Unit Cost |
Optional |
Contains the cost per unit for the SKU being returned. This field is stored in the supplier's currency. If not provided, then value will be defaulted based on the system option RTV Unit Cost Source setting - either the from location's weighted average cost, last received cost, or standard cost. |
Reason |
Optional |
Identifies the reason for the return. Valid values are for this field are held in the Merchandising codes tables under code type RTVR. By default, the values are: Q - QC Failed U - Unavailable Inventory. W - Externally Initiated RTV If the reason is not provided for an externally generated RTV, it will be defaulted to W. |
Weight |
Optional |
Actual weight shipped for the item on the RTV, which is used for some catch weight items. It is expected that either both weight and weight UOM have a value or neither have a value. |
Weight UOM |
Optional |
Indicates the unit of measure represented for the provided weight (for example, pounds, kilograms) where UOM class is of type MASS. It is expected that either both weight and weight UOM have a value or neither have a value. |
Gross Cost |
Optional |
Contains the unit cost and expenses incurred on an item in a particular transaction. This is used for Brazil processing only. For all implementations not using the Brazil Localization configuration, this should be NULL. |
RTV Detail UIN |
Optional |
Not used in Merchandising |
Custom Flex Attributes
If flex attributes have been defined for an RTV they can be included in this node of the message.
Message Element | Required | Notes |
---|---|---|
Name |
Always |
The flex attribute name defined by the business |
Value |
Optional |
The value of the flex attribute defined by the business for alphanumeric or number attributes. |
Value Date |
Optional |
The date value of the flex attribute, if the flex attribute is defined as a date. |
Message XSD
Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
rtvcre |
RTV Create Message |
RTVDesc.xsd |
Stock Order Status Subscription API
This section describes the stock order status subscription API.
Business Overview
A stock order is an outbound merchandise request from a warehouse or store. In Merchandising, a stock order takes the form of either a transfer or allocation. Merchandising 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 Merchandising. The information from RWMS and SIM has only one level, in other words no detail records. Merchandising 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 Merchandising 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 Merchandising. 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.
Stock Order Status Explanations
The following tables describe the stock order statuses for both transfers and allocation document types and what occurs in Merchandising after receiving the respective status. Document_types of 'T', 'D' and 'S' indicate if the transfer is initiated in Merchandising, a warehouse system, or a store system respectively. Statuses other than listed below are ignored by Merchandising.
Stock order status received in message on a transfer where 'distro_document_type' = 'T', 'D', 'S') | What Merchandising does |
---|---|
SI (Stock Increased) When SIM or RWMS publishes a message on a transfer with a status of SI (Stock Increased), Merchandising 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), Merchandising 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), Merchandising 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), Merchandising 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), Merchandising will decrease the selected quantity by the quantity on the message. Merchandising 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), Merchandising 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), Merchandising 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), Merchandising will decrease the distributed qty. Merchandising 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), Merchandising 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), Merchandising 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), Merchandising 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), Merchandising 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), Merchandising 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), Merchandising will decrease the selected quantity by the quantity on the message. Merchandising 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), Merchandising 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), Merchandising 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), Merchandising will decrease the distributed qty. Merchandising 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), Merchandising 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 |
Pack Considerations
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.
Package Impact
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 Merchandising. 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 Merchandising 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.
Message XSD
Here are the filenames that correspond with each message type. Please consult the RIB documentation for each message type in order to get a detailed picture of the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
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 Impact
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 |
Stock Count Schedule Subscription API
This section describes the stock count schedule subscription API.
Business Overview
Stock count schedule messages are published to the RIB by an inventory sub-system, such as Oracle Retail Store Inventory and Operations Cloud Service (SIOCS), to communicate unit and value stock count schedules to Merchandising. Merchandising uses stock count schedule data to help synchronize the inventories of the integrated system and Merchandising. The integrated system then performs a physical inventory count and uploads the results, and Merchandising compares the discrepancies.
This API allows the external systems to create, update, and delete Unit and Value stock count requests within Merchandising. The count is assumed to be for the full location, unless department, class and subclass data are included.
Creating/Updating Stock Count Requests
When a new stock count request is created or an existing stock count request is modified, this API will validate all the required fields are present in the message. Required information for the stock count includes a description, date, type (always B), location type, and locations. Optionally the merchandise hierarchy information can also be included, but, if not included, it will be assumed the entire location will be counted. After the required field and business validations, the stock counts will be created or updated in Merchandising.
The format used when creating or modifying a stock count is shown below.
Table 3-27 Stock Count Header
Message Element | Required? | Notes |
---|---|---|
Cycle Count |
Yes |
This field contains the number which uniquely identifies the stock or cycle count. |
Cycle Count Description |
Yes |
This field contains a description of the cycle or stock count which, along with the cycle count number, identifies the cycle or stock count. |
Location Type |
Yes |
This field contains an indicator which identifies whether the cycle count will be for Stores or Warehouses. Valid values are Store (S) and Warehouse (W). |
Stocktake Date |
Yes |
This field contains the date on which the stock or cycle count will take place. This date should be greater or equal to the current date plus the stake lockout days defined at system level. |
Stocktake Type |
Yes |
This field contains a value which indicates the stock take type. Valid values are Both Unit and Dollar (B) and Unit only (U). However, the RIB interface only allows modification of Unit and Dollar stock take types. |
Stock Count Schedule Products |
No |
Child node |
Stock Count Schedule Locations |
No |
Child node |
Table 3-28 Stock Count Schedule Products
Message Element | Required? | Notes |
---|---|---|
Department |
Yes |
This field contains the department number where the cycle count will occur. If the value = -1, the stock count will apply to all departments. The dept/class/subclass hierarchy must be a valid hierarchy in Merchandising. |
Class |
No |
This field contains the class number where the cycle count will occur. The dept/class/subclass hierarchy must be a valid hierarchy in Merchandising. |
Subclass |
No |
This field contains the subclass number where the cycle count will occur. The dept/class/subclass hierarchy must be a valid hierarchy in Merchandising. |
Table 3-29 Stock Count Schedule Locations
Message Element | Required? | Notes |
---|---|---|
Location |
Yes |
This field contains the store or warehouse number on the cycle count. This must be a valid store or a stockholding warehouse in Merchandising. |
Deleting Stock Count Requests
When an existing stock count request is deleted, this API will validate all the required fields are present in the message. After required field and business validation, the stock counts will be removed in Merchandising. This API also supports deleting a location from the count. The count and locations can only be deleted through this API if no results have been processed for the location on the count. If the last location is deleted from the count, then the count itself will be deleted.
The format used when deleting a stock count is shown below.
Table 3-30 Stock Count Header
Message Element | Required? | Notes |
---|---|---|
Cycle Count |
Yes |
This field contains the number which uniquely identifies the stock or cycle count being deleted. |
Stock Count Schedule Locations |
No |
Child node |
Table 3-31 Stock Count Schedule Locations
Message Element | Required? | Notes |
---|---|---|
Location |
Yes |
This field contains the store or warehouse being deleted on the cycle count. |
Error Handling
If an error occurs in this procedure, a call will be placed to a function to build a complete error message. This message together with a status of E is returned to the external system. If 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 Merchandising database.
Message XSD
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) |
---|---|---|
stkcountschcre |
Stock Count Schedule Create Message |
StkCountSchDesc.xsd |
stkcountschmod |
Stock Count Schedule Modify Message |
StkCountSchDesc.xsd |
stkcountschdel |
Stock Count Schedule Delete Message |
StkCountSchRef.xsd |
stkcountschtldel |
Stock Count Schedule Delete Message for a Location |
StkCountSchRef.xsd |
Store Subscription API
This section describes the store subscription API.
Business Overview
The Store Subscription API provides the ability to keep store data in Merchandising in sync with an external system, if Merchandising 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 Merchandising, the API uses Merchandising 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 Merchandising reads from this table and creates the store in Merchandising in an asynchronous mode.
When updating an existing store in Merchandising, 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 Merchandising 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.
Package Impact
This section describes the package impact.
Consume Module
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 Merchandising'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 Merchandising 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 Merchandising business validation, it can be persisted to the Merchandising 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 Merchandising database.
RMSSUB_XSTORE.HANDLE_ERROR() - This is the standard error handling function that wraps the API_LIBRARY.HANDLE_ERROR function.
Business Validation Module
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 Merchandising 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
Bulk or Single DML Module
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. If store hours for a particular day or days are mentioned on the message, then the store hour's values will be added to the staging table and will be processed in a batch cycle.
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.
DEPARTMENT UP-CHARGE CREATE
-
Adds up-charge information of a department to a certain location.
DEPARTMENT UP-CHARGE MODIFY
-
Updates up-charge information of a department to a certain location.
DEPARTMENT UP-CHARGE DELETE
-
Removes up-charge information of a department to a certain location
STORE HOURS CREATE
-
Store hours for the particular day or days will be added for the existing store, to the staging table and processed in a batch cycle.
STORE HOURS MODIFY
-
Modify store hours for the particular day or days for the existing store.
STORE HOURS DELETE
-
Delete store hours for the particular day or days for the existing store.
Message XSD
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 |
XStoreDeptChrgCre |
External Department Up-Charge Create |
XStoreDesc.xsd |
XStoreDeptChrgMod |
External Department Up-Charge Modify |
XStoreDesc.xsd |
XStoreDeptChrgDel |
External Department Up-Charge Delete |
XStoreRef.xsd |
XStoreHrCre |
External Store Hours Create |
XStoreDesc.xsd |
XStoreHrMod |
External Store Hours Modify |
XStoreDesc.xsd |
XStoreHrDel |
External Store Hours Delete |
XStoreRef.xsd |
Design Assumptions
-
Location traits already exist in Merchandising.
-
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.
Tables
TABLE | SELECT | INSERT | UPDATE | DELETE |
---|---|---|---|---|
STORE_ADD |
No |
Yes |
No |
No |
STORE |
Yes |
No |
Yes |
No |
ADDR |
Yes |
Yes |
Yes |
No |
DAILY_PURGE |
No |
Yes |
No |
No |
LOC_TRAITS_MATRIX |
Yes |
Yes |
No |
Yes |
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
TSF_ENTITY |
Yes |
No |
No |
No |
WH |
Yes |
No |
No |
No |
WALK_THROUGH_STORE |
No |
Yes |
No |
Yes |
UP_CHARGE_TEMP |
Yes |
Yes |
No |
Yes |
COST_COMP_UPD_STG |
Yes |
Yes |
Yes |
Yes |
DEPT_CHRG_HEAD |
Yes |
Yes |
No |
Yes |
DEPT_CHRG_DETAIL |
Yes |
Yes |
Yes |
Yes |
FROM_LOC_TEMP |
Yes |
Yes |
No |
Yes |
TO_LOC_TEMP |
Yes |
Yes |
No |
Yes |
ELC_COMP |
Yes |
No |
No |
No |
UOM_CLASS |
Yes |
No |
No |
No |
CURRENCIES |
Yes |
No |
No |
No |
CODE_DETAIL |
Yes |
No |
No |
No |
V_DEPS |
Yes |
No |
No |
No |
V_DIVISIONS |
Yes |
No |
No |
No |
V_GROUPS |
Yes |
No |
No |
No |
STORE_HOURS_ADD |
Yes |
Yes |
Yes |
Yes |
STORE_HOURS |
Yes |
Yes |
Yes |
Yes |
Transfer Subscription API
This section describes the transfer subscription API.
Business Overview
This API subscribes to transfers from external systems to create, update or delete transfers in Merchandising. Within Oracle Retail solutions, this API is also leveraged by Advanced Inventory Planning (AIP) to create standalone transfers generated out of its replenishment processing. AIP does not use this API to update or delete previously created transfers.
Creating Transfers
When a new transfer is created, this API will first validate that all required fields are present in the message. Certain of the fields are required regardless of transfer type and system configuration, while others are dependent on other Merchandising configurations. Additionally, when creating a new transfer at least one detail line must also be included in the message. After that, business level validation on the input information will be performed. The tables below summarize these two types of validation.
Table 3-32 Header Level Validation
Message Element | Required? | Notes |
---|---|---|
Transfer Number |
Always |
Must be a unique transfer number not used by any existing transfers in Merchandising. |
From Location Type |
Always |
Must be either a store (S) or a warehouse (W). |
From Location |
Always |
See below. |
To Location Type |
Always |
Must be either a store (S), warehouse (W), or external finisher (E). For more on transfers with finishing, see below. |
To Location |
Always |
See below. |
Delivery Date |
Conditional |
When AIP is part of your implementation, this is required for all transfer types, except RAC, EG, and SIM transfers. If included in the message, this must be today or a future date. |
Department |
Conditional |
A system option determines whether or not the department is required for transfers. If the system option is set to require a department, then this must be included in the message. If the system option is set to not require the department, then the department must be null in this message unless the transfer type is SIM, AIP, or EG. |
Routing Code |
Conditional |
If the freight code is Expedite (E), then this must have a value. Otherwise, it must be null. Valid values are 1, 2, or 3. The descriptions for these three options are held in the Codes table under code TRRC and can be configured as needed for your business. |
Freight Code |
Optional |
If this is included in the message, it must have a value of normal (N), hold (H), or expedite (E). If no value is provided, it will default to normal. |
Transfer Type |
Optional |
The following types of transfers can be created in this API:
If the transfer type is not specified for the new transfer, then it will be defaulted to either Manual Requisition or Intercompany, depending on the legal entities of the locations on the transfer. See below for more details on transfer types. |
Status |
Optional |
Transfers can be created in Input (I) or Approved (A) status in this API. See below for more on transfer status validation. |
Create ID |
Optional |
If not passed into the message, then a value will be defaulted for auditing purposes. |
Comments |
Optional |
Can support up to 2000 characters of text. |
Context Type |
Optional |
Valid values for this field are found in the Codes table under code type CNTX. |
Context Value |
Optional |
This may be used to provide additional information about the context of the transfer. For example, if the context type is promotion, this may indicate the promotion number or a description. |
Table 3-33 Detail Level Validation
Message Element | Required? | Notes |
---|---|---|
Item |
Always |
An item must be a transaction-level, inventoried, and approved in order to be included on a transfer. If the transfer is from a store, then it cannot be a pack item, unless the transfer is of type AIP, SIM, or EG. Also, packs can also only be transferred from a warehouse if they have a receive as type of Pack, so that inventory exists at that level. If a department has been included in the transfer message at the header level, then all items must belong to that department. |
Transfer Quantity |
Always |
If the item has a standard unit of measure in the quantity class, then the quantity for the transfer must be an integer. |
Supplier Pack Size |
Optional |
If included, this must be greater than zero. If not included, this will default to the primary supplier's pack size for the item at the from location, if an orderable item. If not orderable, this will default to 1. |
Inventory Status |
Optional |
All items on a transfer must either be from available status or from unavailable status. A single transfer cannot mix available and unavailable statuses. Available inventory transfers should use a -1 or a null value in this field in the message. Unavailable inventory transfers should include a valid status from the Inventory Status Types configured in your environment. |
Unit Cost |
Not used |
This is not used by Merchandising. |
Adjustment Type |
Conditional |
This field, along with the adjustment value, is used to calculate the transfer price for intercompany transfers. It will be ignored for all other transfers. If the adjustment value is provided, then the type must also be specified. The valid values for this field are:
IA and IP can only be used if you have your system options set to allow the transfer price to exceed weighted average cost. |
Adjustment Value |
Conditional |
If the adjustment type is provided, then the value must also be specified. This must always be a positive amount. |
Location Validation
The from and to locations passed into the message must be valid stores or warehouses in Merchandising; but they cannot be the same. If both locations are stores, then they must both exist in the same transfer zone. Additionally, if the to location is a store, then it must be open. This is determined based on whether there is a close date defined for the store and the stop order days.
If either location is a warehouse, then it can be either a physical warehouse or a virtual warehouse, depending on transfer type. A physical warehouse is only allowed as the from location type for an EG type of transfer. Additionally, only Book type transfers are allowed between two warehouses in the same physical warehouse.
If either the from or to location is a franchise store, then the other location cannot be a finisher. If the franchise store is a non-stockholding location, then the other location on the transfer must be a warehouse.
Validation is also done at the item level based on the locations on the transfer. Each item on the transfer must be in active, inactive, or discontinued status at the from location. It also must have been ranged to the from location in Merchandising, when that location is a warehouse. However, if the from location is a store, there is an exception where the transfer can still be created even though it is not yet ranged, which also bypasses inventory validation. This is to support a specific function in Oracle Retail Store Inventory Management (SIM). See the section on SIM Generated transfers below for more details.
If the item is not already ranged to the to location, then ranging will occur when the transfer is created, regardless of status. The ranging that occurs will flag the item/location as unintentionally ranged for all transfer types except AIP.
If the to location is an external finisher, see the section below on transfers with finishing.
Inventory Validation
Another part of the validation that is applicable for all transfers created is that inventory is available for transfer if the status passed through the integration is approve (A), with a few exceptions. First, EG type transfers do not have inventory validated as it is assumed that this type of transfer is generated in the store or warehouse and the inventory availability check has been done in that solution as part of the shipping of the inventory. Additionally, if the system option titled Validate External Warehouse Availability is set to No (unchecked), then warehouse inventory will not be validated for any transfers initiated in this API regardless of type. Store inventory availability is never validated by this API because of support for the process where the item does not need to be ranged to the shipping store.
Status Validation
Transfers can be created in a status of Input (I) or Approved (A) using this API. Transfers in input status are not subject to inventory validation, but all other validations are applicable. Book type transfers can only be created in Input status using this API, as there isn't really a concept of an "approved" book transfer - as soon as it is approved it is executed. Additionally, transfers of type Reallocation (RAC) and Return to Vendor (RV) can also only be created in Input status. Conversely, transfers of type AIP, SIM, and EG must always be created in Approved status. If any validation fails when processing the new transfer that results in it not being able to be approved, the transfer will be created but will remain in input status. The exception to this is for transfers of type AIP, SIM, and EG, as they must always be created in approved status. If they are not able to be approved, the transfer is not created or updated.
Transfer Type Specific Validation
Most of the validation defined above is relevant regardless of transfer type, except where noted. However, there are also some other validations done as part of this API's processing that are specific to a type of transfer.
AIP Generated (AIP)
This type of transfer is expected only to be sent from AIP as an output of the replenishment process. As such, Merchandising assumes certain validations have been done by AIP in advance of receiving the transfer and slightly different validation is enforced. The following special validations apply for this transfer type using this API:
-
Must be created in Approved status
-
Can only be to stockholding locations
-
Supports transferring packs from stores
-
Allows the department number to be passed even when the system option is N
-
Item/location ranging to the to location will result in the Ranged flag being set to Yes as it is assumed this an intentional ranging.
-
Can be an intercompany transfer
Book (BT)
Book transfers processed through this API can be created for two virtual warehouses in the same physical warehouse only. This is usually used for inventory rebalancing between virtual locations. The following special validations apply for this transfer type using this API:
-
Can only be created in Input status
-
Can only be created for virtual warehouses in the same physical warehouse
-
Warehouses must be in the same legal entity
Externally Generated (EG)
Externally Generated transfers are assumed to be created in the store or warehouse. Further, it is assumed that once they get to Merchandising, the transfer is already in process at that location. As such, there are certain validations that are managed differently for this transfer type in this API:
-
Must be created in Approved status
-
Supports transferring packs from stores
-
Allows the department number to be passed even when the system option is N
-
Can be an intercompany transfer
-
Uses the physical warehouse number, not a virtual warehouse number, if warehouses are involved
Intercompany (IC)
An intercompany transfer is a type of business to business transaction that sells product from one legal entity and purchases it into another. Legal entities in Merchandising are determined based on the setting of the Intercompany Basis system option, which indicates whether the transfer entity or the set of books of a location should be used. This transfer type is used when either it is explicitly passed into the API or if the transfer type is NULL in the inbound message and the locations are in different legal entities. Other transfer types may also be intercompany, as well, but the below rules apply for those flagged as intercompany type explicitly:
-
The legal entity of the from and to locations must be different.
-
If an adjustment type or value is passed into the message, that will be used to calculate the "selling" price between entities. Otherwise, the from location's weighted average cost is used.
Manual Requisition (MR)
This is the most basic type of transfer in Merchandising, so it is used as a default transfer type when either it is explicitly passed into the API or if the transfer type is NULL in the inbound message and the locations are in the same legal entity. The behavior for this transfer type is the same as that for AD and CF types of transfers - those could be used as different reasons for a transfer. For this transfer type the following validation rules are enforced:
-
Locations must be in the same legal entity
Reallocation (RAC)
A reallocation transfer is assumed to be used to pull back inventory from stores or warehouses to a single warehouse for re-allocation to other stores or other warehouses. This is the type of transfer that is created when a mass-return transfer is created, for example. Because it has unique rules tied to it related to MRTs, some additional validations are followed:
-
Can only be created in Input status in this API
-
Locations must be in the same legal entity
Return to Vendor (RV)
A return to vendor type of transfer is similar to a reallocation type, in that it is assumed to be pulling inventory back to a warehouse from stores or other warehouses, but in this case, for the purpose of returning the merchandise to the supplier. This is the type of transfer that is created when a mass-return transfer is created, for example. Because it has some unique rules tied to it related to MRTs, some additional validations are followed:
-
Can only be created in Input status in this API
-
Locations must be in the same legal entity
SIM Generated (SIM)
SIM generated transfers are created only by the store orders process in SIM. This functionality is not available in SIOCS. Because of this, they have special rules applied, including the ability to create the transfer even though no item/store relationship exists for the originating location in Merchandising. The rules that apply for this type of transfer include:
-
Must be created in Approved status
-
Supports transferring packs from stores
-
Allows the department number to be passed even when the Merchandising system option is No
-
Can be an intercompany transfer
All Transfer Types
For all of the above transfer types, if all validation described above passes, then the transfer will be created. If the transfer is created in Approved status, then in addition to the transfer itself, other details may also be created based on the items and locations involved.
-
Inventory will be updated to reflect the reserved quantity at the from location and expected quantity at the to location.
-
Upcharges will be applied, if configured, for transfers that do not include a physical warehouse location. For transfers with a physical warehouse, the records for upcharges are added when the transfer is shipped.
-
An associated franchise order or return will be created if the transfer involves a franchise location.
Transfers with Finishing
Transfers with finishing are sometimes referred to as a two-legged transfer, as they generate two transfers in Merchandising. One from the originating store or warehouse to the finisher and one from the finisher back to a store or warehouse. This API supports the creation of a transfer with finishing only through an external finisher, a type of partner, and back to the originating location. Transfers to an internal finisher are not supported via this integration. To do this, when sending the transfer details in the message, you will indicate the external finisher as the "to" location. Then when the transfer is created, it will automatically generate the second leg.
When creating transfers in this way, it does not generate any work order activities to send to the finisher with the transfer - these will either need to be added manually in the Merchandising screens, or sent separately to the finisher.
Updating Transfers
For updates, the transfer number included in the message must already exist in Merchandising. Changes can be sent for header level updates or detail level updates. If the changes are at the header level, then the all the required header level information need to be included in the update, similar to that described above for creating a new transfer. However, the transfer details should not be included in a header level update. Fields that can be updated at the header level using this API include:
-
Delivery Date - must always be a date today or later.
-
Routing Code - if the freight code is updated to expedite (E), then this must also have a value. If freight code is updated to something other than expedite, then this should be null.
-
Freight Code
-
Status - to move from Input or Submitted to Approved only. Transfers cannot be moved back to Input status using this API.
-
Comments
-
Context Type
-
Context Value
If the update is at the detail level - to add or update a line item - only the transfer number is required in the header record, the other details are ignored. If not included, then the message will be rejected. Adding a new item to the transfer will use similar validation to that described above when creating the transfer.
If modifying an existing transfer line item, the full transfer quantity should be sent with the update, not the difference from the original quantity. This will be compared to the previous transfer quantity to determine how to update the transfer. For example, if the transfer is in approved or submitted status, a reduction in quantity would update the cancelled quantity on the transfer. It will also be validated to ensure that the quantity change doesn't result in the total transfer quantity being lower than what has already been shipped or what is expected to be picked based on updates to the selected or distro quantities on the transfer. For increases in transfer quantity, if the transfer is in submitted or approved status, then inventory will be validated based on the changed quantity (depending on system option settings) to validate that the additional units are available. The inventory status for the item cannot be modified.
Deleting Transfers
If you are deleting a line item on the transfer or deleting the whole transfer, then the API will validate that the transfer number is valid and that the transfer or transfer line was not already shipped or received, at least partially, or is not in process at the shipping warehouse or store. If you are deleting the whole transfer, then no details should be included in the message. If you are deleting a line on the transfer, then validation will be done to ensure that the item exists on the transfer.
Transfers are not actually deleted via this API, rather they are updated to a deleted status and a secondary process does the actual removal. Transfers can be deleted in any status, other than those already in a closed or deleted status, using this API. If the transfer involved an external finisher, then both legs on the transfer will be marked for delete. Deleting the last line on the transfer will also result in the transfer being flagged for delete.
If the transfer is in a status other than input, moving it to a deleted status or deleting a line will also update inventory to release the reserved inventory at the from location and decrease expected quantity at the to location. As well, if the transfer involves any franchise stores, then any franchise order or return created with the transfer will also be cancelled.
Publishing Updates
Because these transfers that can be created, updated, or deleted using this API are managed in an external system, there are some cases where it is not published back out by Merchandising after it is processed to avoid the source system from receiving unneeded updates. This applies for transfers of type EG only. All other transfers will be published back to the RIB if approved or previously approved, such that the store and warehouse solutions responsible for executing the transfers are notified.
Flex Attributes
If you have defined any custom flex attributes (CFAS) for transfers, then they can be integrated as part of this API. The node of the integration that supports this will accept the name of the attribute as it is defined in the group set level view and the value for the attribute. Flex attributes can only be added or updated to a transfer, they cannot be deleted. Additionally, for transfers with finishing, flex attributes can only be added to the first leg of the transfer.
Error Handling
If any errors are encountered in the validations described above or any of the message structure validations, a status of E is returned to the external system along with the appropriate error message. If 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 Merchandising database.
Message XSD
Below are the filenames that correspond with each message type. Please consult the Oracle Retail Integration Guide for each message type for the details on the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
Xtsfcre |
Transfer Create |
XTsfDesc.xsd |
Xtsfdtlcre |
Transfer Detail Create |
XTsfDesc.xsd |
CustFlexAttriVO |
Transfer Flex Attribute Create |
XTsfDesc.xsd |
Xtsfmod |
Transfer Modify |
XTsfDesc.xsd |
Xtsfdtlmod |
Transfer Detail Modify |
XTsfDesc.xsd |
Xtsfdtlmod |
Transfer Flex Attribute Modify |
XTsfDesc.xsd |
Xtsfdel |
Transfer Delete |
XTsfDesc.xsd |
Xtsfdtldel |
Transfer Detail Delete |
XTsfRef.xsd |
Vendor Subscription API
This section describes the vendor subscription API.
Business Overview
Merchandising 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 Merchandising. Supplier information also includes supplier addresses, CFAS data and the org unit.
Processing includes a check for the appropriate financial application in Merchandising 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 Merchandising through RIB.
The financial application publishes a supplier type vendor, placing the supplier information onto the RIB (Oracle Retail Information Bus). Merchandising subscribes to the supplier information as published from the RIB and places the information onto Merchandising tables depending upon the validity of the records enclosed within the message.
Package Impact
Filename: rmssub_vendorcres/b.pls
Public API Procedures
RMSSUB_VENDORCRE.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 accepts an RIB object as input (I_message). This contains a supplier message consisting of the aforementioned header and detail records. This procedure will initially ensure that the passed-in message type is a valid type for vendor subscription. 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.
The procedure then places a call to the main RMSSUB_SUPPLIER.CONSUME function to validate the RIB Object. The values extracted from the RIB Object 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.
Private Internal Functions and Procedures (rmssub_vendorcre.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_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.
Private Internal Functions and Procedures (other):
All of the following functions exist within RMSSUB_SUPPLIER.
Main Consume Function
RMSSUB_SUPPLIER.CONSUME(O_status OUT VARCHAR2, O_error_message OUT RTK_ERRORS.RTK_TEXT%TYPE, I_message IN "RIB_VendorDesc_REC")
This function accepts the RIB Object (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 values extracted from the RIB Object 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 input RIB Object 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_rib_vendorhdrdesc_REC IN "RIB_VendorHdrDesc_REC", I_rib_vendoraddrdesc_TBL IN "RIB_VendorAddrDesc_TBL");
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().
Message XSD
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 |
Design Assumptions
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 |
Work Order Status Subscription API
This section describes the work order status subscription API.
Business Overview
For transfers with finishing, Merchandising subscribes to work order status messages sent from internal finishers indicating that the work order activities are complete. This message is used for internal finishers located in the same physical warehouse as the final destination for the transfer, as there is no physical shipment of goods. 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, and Merchandising will disregard Work Order Status messages sent in these scenarios.
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 associated with them. When work order status messages are received for a particular item/quantity, it is assumed that all activities on the work order 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 or pack.
On processing the work order status update, a book transfer is executed 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.
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.
Work Order Example
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, Merchandising could expect to receive a Work Order Status message containing item 101 with a quantity of 20.
Work Order Status Creation
While consuming the Work Order Status message, Merchandising validates that the finisher and the transfer's final receiving location are in the same physical warehouse. If not, processing is halted. If the message contains an item, 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 Shipped status. Note that 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 logic will be used to determine if the entire multi-leg transfer can be closed.
Table 3-34 Work Order Status
Message Element | Required? | Notes |
---|---|---|
Work Order ID |
Yes |
This field contains the work order number under which the finishing activities were performed. |
Distro Number |
Yes |
This field contains the transfer number containing the work order. This is the 2nd leg transfer number. |
Distro Doc Type |
Yes |
This should always be transfer (T). |
Distro Parent Number |
No |
This is the first leg transfer number. If this field must be the same as the tsf_parent_no of the distro number in the tsfhead table in Merchandising. |
Distro Parent Type |
No |
Not used |
Item |
Conditional |
This contains the item on which the work order has been completed. If an item transformation occurred, this should be the resultant item. If the completed quantity field has a value, then this field is required. |
Warehouse |
Yes |
Not used, but required as input |
Location Type |
Yes |
This field contains the destination location type where the finished goods are sent. Not used, but required as input |
Location |
Yes |
This field contains the destination location where the finished goods are sent. This is the final location of the 2-legged transfer. |
Sequence Number |
Yes |
Not used, but required as input |
Work In Process Code |
Yes |
Not used, but required as input |
Instructions |
Yes |
Not used, but required as input |
Complete Date |
Yes |
This field contains the date when the work order was completed. |
Completed Quantity |
Conditional |
This contains the number of items that resulted from the work order activity. If the item field has a value, then this field is required. |
Completed Indicator |
Yes |
Not used |
Work Order Status Inventory Adjustment |
No |
Child node Not used |
Table 3-35 Work Order Status Inventory Adjustment
Message Element | Required? | Notes |
---|---|---|
From Disposition |
No |
Not used |
To Disposition |
No |
Not used |
Unit Quantity |
No |
Not used |
Message XSD
Here are the filenames that correspond with each message type. Please consult RIB documentation for each message type to get a detailed picture of the composition of each message.
Message Types | Message Type Description | XML Schema Definition (XSD) |
---|---|---|
wostatuscre |
Work Order Status Create Message |
WOStatusDesc.xsd |