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.

Functional Area

Allocation

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.

Functional Area

Appointments

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:

1. 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.

2. Messages that modify the earlier created appointment update the status to 'MS'.

3. Once the merchandise has arrived at the location, the appointment is updated to an 'AR' (arrived) status.

4. Another modification message that contains a receipt identifier prompts Merchandising to insert received quantities into the APPT_DETAIL table.

5. After all items are received, Merchandising attempts to close the appointment by updating it to an 'AC' status.

6. 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.

Functional Area

Advance shipping notice (ASN) from a supplier

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:

1. The public procedure checks if the message type is create (ASNINCRE), modify (ASNINMOD), or delete (ASNINDEL).

2. 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.

3. 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:

   1. Records are passed on to the private function PARSE_ORDER.

   2. Delete container and item records from the previous order.

   3. Check if CARTON_IND is equal to 'C'.

   4. If CARTON_IND equal to 'C', call private functions PARSE_CARTON and PARSE_ITEM to parse cartons and items within a carton.

   5. If CARTON_IND is NOT equal to 'C', call private function PARSE_ITEM to parse items that are not part of a container.

   6. 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

Design Assumptions

None

Table Impact

TABLE	SELECT	INSERT	UPDATE	DELETE
SHIPMENT	Yes	Yes	Yes	Yes
SHIPSKU	Yes	Yes	No	Yes
CARTON	No	Yes	No	Yes
INVC_XREF	No	No	No	Yes
STORE	Yes	No	No	No
WH	Yes	No	No	No
ORDHEAD	Yes	No	No	No

COGS Subscription API

This section describes the COGS subscription API.

Functional Area

COGS Subscription

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.

Table Impact

TABLE	SELECT	INSERT	UPDATE	DELETE
ITEM_LOC	Yes	No	No	No
ITEM_LOC_SOH	No	No	Yes	No
TRAN_DATA	No	Yes	No	No

Cost Change Subscription

This section describes the cost change subscriptions.

Functional Area

Cost Change

Integration Type

Oracle Retail Integration Bus (RIB)

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 for that 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.

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.

Functional Area

Currency Exchange Rates

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.

Message Structure

The currency exchange rate message is a flat message that will consist of a currency exchange rate record.

The record will contain information about the currency exchange rate as a whole.

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.

Table Impact

TABLE	SELECT	INSERT	UPDATE	DELETE
CURRENCY_RATES	Yes	Yes	Yes	No
SYSTEM_OPTIONS	Yes	No	No	No
PERIOD	Yes	No	No	No
FIF_CURRENCY_XREF	Yes	No	No	No

Customer Order Fulfillment Subscription API

Functional Area

Customer Order Fulfillment

Business Overview

Merchandising provides an interface to process Customer Order Fulfillment requests from an external order management system (OMS). If the system option OMS_IND = 'Y', then Merchandising expects to receive customer orders via this API. If the system option PERSIST_CUSTOMER_DATA_IND = ‘N', personal information will not be stored in the customer order table in Merchandising.

Merchandising supports two integration methods for processing Customer Order Fulfillment messages from OMS - either through RIB or Web service. At implementation time, clients should decide on either one or the other integration method, but not both. The same core logic is used to validate and persist customer orders to Merchandising tables.

• In a RIB implementation, Merchandising subscribes to Customer Order Fulfillment messages. When a customer order is created, or partially or fully cancelled, the customer order information is sent from the Order Management System (OMS) to the RIB. Merchandising subscribes to the customer order information as published from the RIB and places the information onto Merchandising tables.

• In a web service implementation, Merchandising exposes a FulfillOrder Web service to create or cancel a customer order in Merchandising. OMS will invoke the service with customer order details to place the information on Merchandising tables. See Customer Order Fulfillment Service in the "SOAP Web Services" chapter of this document for more details on this method.

The Customer Order Fulfillment message staged will go through a process of validation. Records that pass validation will create new customer order records. If any validation error occurs, transaction will be rolled back and no customer orders will be created.

There are two scenarios where a customer order fulfillment request cannot be created in Merchandising:

1. Due to data validation errors (for example, invalid item).

2. Due to 'No Inventory' - There is not enough inventory available at the source location or item is not ranged or inactive at the source location, or item is not supplied by the supplier (in a PO scenario).

The Customer Order Fulfillment messages contain information such as delivery type, source type and destination type. Based on these, the system should proceed to create a Purchase Order, Transfer or Inventory Reservation. The table below shows the customer order scenarios for the combination of delivery type, source type and destination type.

Scenario #	Source Location	Fulfillment Location	Delivery Type	Transaction created
1	Warehouse	Store	Pickup in Store	Virtual WH to Physical Store Transfer + Reservation. FulfilOrdDesc will contain: 1st leg: source_loc_type = 'WH', fulfill_loc_type = 'S' 2nd leg: source_loc_type = NULL, fulfill_loc_type = 'S'.
2	Warehouse	Store	Ship to Customer	Virtual WH to Physical Store Transfer + Reservation. FulfilOrdDesc will contain: 1st leg: source_loc_type = 'WH', fulfill_loc_type = 'S' 2nd leg: source_loc_type = NULL, fulfill_loc_type = 'S'.
3	Store A	Store B	Pickup in Store	Physical Store to Physical Store Transfer + Reservation. FulfilOrdDesc will contain: 1st leg: source_loc_type = 'ST', fulfill_loc_type = 'S' 2nd leg: source_loc_type = NULL, fulfill_loc_type = 'S'.
4	Store A	Store B	Ship to Customer	Physical Store to Physical Store Transfer + Reservation. FulfilOrdDesc will contain: 1st leg: source_loc_type = 'ST', fulfill_loc_type = 'S' 2nd leg: source_loc_type = NULL, fulfill_loc_type = 'S'.
5	NULL	Store	Pickup in Store	Reservation. FulfilOrdDesc will contain: Single-leg: source_loc_type = NULL, fulfill_loc_type = 'S'.
6	NULL	Store	Ship to Customer	Reservation. FulfilOrdDesc will contain: Single-leg: source_loc_type = NULL, fulfill_loc_type = 'S'.
7	NULL	Warehouse	Ship to Customer	Virtual WH to Virtual Store Transfer. FulfilOrdDesc will contain: Single-leg: source_loc_type = 'WH', fulfill_loc_type = 'V'.
8	Vendor	Store	Pickup in Store	Purchase Order to Physical Store + Reservation. FulfilOrdDesc will contain: 1st leg: source_loc_type = 'SU', fulfill_loc_type = 'S' 2nd leg: source_loc_type = NULL, fulfill_loc_type = 'S'.
9	Vendor	Store	Ship to Customer	Purchase Order to Physical Store+ Reservation. FulfilOrdDesc will contain: 1st leg: source_loc_type = 'SU', fulfill_loc_type = 'S' 2nd leg: source_loc_type = NULL, fulfill_loc_type = 'S'.
10	NULL	Vendor	Ship to Customer	Purchase Order to Virtual Store FulfilOrdDesc will contain: Single-leg: source_loc_type = 'SU', fulfill_loc_type = 'V'.

The customer order subscription API supports create and cancel operations using the following message types belonging to the 'fulfilord' message family:

• fulfilordapprdel - used by Merchandising to cancel customer orders.

• fulfilordreqdel - used by SIM to request a customer order cancellation. This message type is used only by SIM and is ignored by Merchandising.

• fulfilordpocre - used to create purchase orders as a result of customer order fulfillment requests.

• fulfilordtsfcre - used to create transfers as a result of customer order fulfillment requests.

• fulfilordstdlvcre - used to perform inventory reservation as a result of customer order fulfillment requests.

In a RIB implementation, once fulfillment create messages are processed in Merchandising, Merchandising will publish to the RIB a customer order fulfillment confirmation message with a message type of 'fulfilordcfmcre' via the customer order fulfillment confirmation publishing API. Confirmation messages will only be sent for customer order fulfillment creates requests that result in creating purchase orders and transfers in Merchandising. It will not be sent for cancel requests, or for customer order fulfillment requests that result in inventory reservation.

• If a customer order is partially fulfilled, a confirmation message with status 'P' will be sent with details of fulfilled order quantity.

• If a customer order is not fulfilled at all due to unavailable inventory, a confirmation message with status 'X' will be sent without any details.

• If a customer order is fulfilled completely due to available inventory, a confirmation message with status 'C' will be sent with details for the fulfilled order quantity.

See Customer Order Fulfillment Confirmation in the "RIB Publication Designs" chapter for more details on the confirmation message sent.

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

1. 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).

2. Non-stockholding franchise stores cannot part of a customer order, either as a sourcing location or as a fulfillment location.

3. 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.

4. 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.

5. 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.

Diff Group Subscription API

This section describes the Diff group subscription API.

Functional Area

Diff Group

Integration Type

Oracle Retail Integration Bus (RIB)

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-1 Header Level Validation

Message Element	Required?	Notes
Differentiator Group Identifier	Always	A unique differentiator group identifier.
Differentiator Type	Always	A differentiator type, such as C for color. Must exist as a valid diff type in Merchandising.
Differentiator Group Description	Always	Description of the differentiator group.
Create Date Time	Optional	The date/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.

Table 3-2 Detail Level Validation

Message Element	Required?	Notes
Diff ID	Always	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	Optional	The order in which the diff ID should appear within the differentiator group, when displayed on-line.
Create Date Time	Optional	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 be already 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 the all 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.

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

Differentiator Subscription API

This section describes the Diff ID subscription API.

Functional Area

Foundation

Integration Type

Oracle Retail Integration Bus (RIB)

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.

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. The business validation:

• verifies if diff id to be updated exists in Merchandising

If all the validations are met, the differentiator in the message data is updated in Merchandising.

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.

Functional Area

Direct Ship Receipt Subscription.

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.

Table Impact

TABLE	SELECT	INSERT	UPDATE	DELETE
ITEM_MASTER	Yes	No	No	No
PACKITEM	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	Yes	No
TRAN_DATA	No	Yes	No	No

DSD Deals Subscription API

This section describes the DSD deals subscription API.

Functional Area

DSD deals subscription

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 or may not be given to the store (as opposed to sent to corporate), and the invoice may or may not be paid for out of the register.

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

Design Assumptions

None

DSD Receipt Subscription API

This section describes the DSD receipt subscription API.

Functional Area

DSD Receipt

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

Design Assumptions

None

Table Impact

TABLE	SELECT	INSERT	UPDATE	DELETE
SHIPMENT	Yes	No	No	No
ORDHEAD	Yes	No	No	No

Freight Terms Subscription API

This section describes the freight terms subscription API.

Functional Area

Foundation

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Freight terms are financial arrangement information that is published to the Oracle Retail Integration Bus (RIB) from a financial system. Freight terms are the terms for shipping (for example, the freight terms could be a certain percentage of the total cost; a flat fee per order, and so on). Merchandising subscribes to freight terms messages held on the RIB. After confirming the validity of the records enclosed within the message, the Merchandising database is updated with the information.

Creating 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, start and end date. 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

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.

GL Chart of Accounts Subscription API

This section describes the GL chart of accounts subscription API.

Functional Area

GL Chart of Accounts

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

Design Assumptions

Required fields are shown in the RIB documentation.

Many ordering functionalities that are available on-line are not supported through this API. Triggers related to these functionalities must be turned off.

Table Impact

TABLE	SELECT	INSERT	UPDATE	DELETE
FIF_GL_ACCT	Yes	Yes	Yes	No

GL Chart of Account Validation Service

This section describes the GL Chart of Accounts Subscription API.

Functional Area

Financial Integration

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.

Functional Area

Inventory Adjustments

Integration Type

Oracle Retail Integration Bus (RIB)

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.

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.

Functional Area

Inventory Request Subscription

Integration Type

Oracle Retail Integration Bus (RIB)

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.

Functional Area

Item

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.

Functional Area

Items

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-3 Header Level Validation

Message Element	Required?	Notes
Item	Always	Must be an existing item in Merchandising.
Hier Level	Always	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-4 Detail Level Validation

Message Element	Required?	Notes
Hier Value	Always	Must be valid ID for a chain, area, region, district, store or warehouse given the Hier Level.
Status	Always	Must be either active (A), inactive (I), discontinued (C).
Store Ord Mult	Always	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): Source Method is required to be warehouse (W) for wholesale or franchise stores. For Hier Level of warehouse (W) Source Method is required to be supplier (S) 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	Optional	Must be yes (Y) or no (N), if populated.
UIN Type	Optional	Must be a valid code from code_type = UINT if populated: AGSN - Auto Generate SN SN - Serial Number
UIN Label	Conditional	UIN Label is required if UIN Type is populated. Must be a valid code from code_type = ULBL if populated: IMEI - International Mobile Equipment Identity LN - License Number PN - Part Number SIN - Serial Identification Number SN - Serial Number
Ext UIN Indicator	Optional	Valid values are Y or N. Must not be no (N) if Capture Time is (S).
Primary Supplier	Optional	Primary Supplier and Primary Country relationship should exist for an orderable item and is required for consignment or concession items.
Primary Country	Optional	Primary Supplier and Primary Country relationship should exist for an orderable item and is required for consignment or concession items.
Local Item Description	Optional
Ti	Optional	Must be a valid numeric value
Hi	Optional	Must be a valid numeric value
Daily Waste Percent	Optional	Must be a valid numeric value
Local Short Description	Optional
UIN Type	Optional
UIN Label	Optional
Capture Time	Optional
Unit Cost	Optional	Must be a valid numeric value
Cost UOM	Optional
Purchase Type	Optional	Valid values are Owned (0), Consignment (1) , Concession (2). Must be 0 if the Consignment/Concession system option = N.
Calculation Basis	Optional	Valid values are Cost Per Unit (C) or Purchase Rate (P).
Purchase Rate	Optional	Must only be populated if Calculation Basis is Purchase Rate (P).
Promotable Indicator	Optional	Valid values are Y or N.

Table 3-5 Item Location Trait Validation

Message Element	Required?	Notes
Launch Date	Optional
Quantity Key Options	Optional	Valid values are in code type RPO.
Manual Price Entry	Optional	Valid values are in code type RPO.
Deposit Code	Optional	Valid values are in code type DEPO.
Food Stamp	Optional	Valid values are Y or N.
WIC	Optional	Valid values are Y or N.
Proportional Tare Percent	Optional
Fixed Tare Value	Optional
Fixed Tare UOM	Optional	Must be a valid UOM.
Reward Eligible	Optional	Valid values are Y or N.
National Brand Competitor Item	Optional	Must be a valid Item.
Return Policy	Optional	Valid values are in code type RETP.
Stop Sale	Optional	Valid values are Y or N.
Electronic Market Clubs	Optional	Valid values are in code type MKTC.
Report Code	Optional	Valid values are in code type REPC.
Shelf Life on Selection	Optional
Shelf Life on Receipt	Optional
Investment Buy (IB) Shelf Life	Optional
Store Reorderable	Optional	Valid values are Y or N.
Rack Size	Optional	Valid values are in code type RACK.
Full Pallet Item	Optional	Valid values are Y or N.
In Store Market Basket	Optional	Valid values are in code type STMB.
Storage Location	Optional	Alternate Storage Location
Returnable	Optional	Valid values are Y or N.
Refundable	Optional	Valid values are Y or N.
Back Order	Optional	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-6 Header Level Validation

Message Element	Required?	Notes
Item	Always	Same as create.
Hier Level	Always	Same as create.

Table 3-7 Detail Level Validation

Message Element	Required?	Notes
Hier Value	Always	Same as create
Status	Always	Must be either active (A), inactive (I), discontinued (C), deleted (D).
Store Ord Mult	Always	Same as create
Source Method	Conditional	Same as create
Source Warehouse	Conditional	Same as create
Receive as Type	Conditional	Same as create
Taxable Indicator	Optional	Same as create
UIN Type	Optional	Same as create
UIN Label	Conditional	Same as create
Ext UIN Indicator	Optional	Same as create
Primary Supplier	Optional	Same as create
Primary Country	Optional	Same as create
Local Item Desc	Optional	Same as create
Ti	Optional	Same as create
Hi	Optional	Same as create
Daily Waste Pct	Optional	Same as create
Local Short Desc	Optional	Same as create
UIN Type	Optional	Same as create
UIN Label	Optional	Same as create
Capture Time	Optional	Same as create
Unit Cost	Optional	Same as create
Cost UOM	Optional	Same as create
Purchase Type	Optional	Same as create
Calculation Basis	Optional	Same as create
Purchase Rate	Optional	Same as create
Promotable Indicator	Optional	Same as create

Table 3-8 Item Location Trait Validation

Message Element	Required?	Notes
Launch Date	Optional	Same as create
Qty Key Options	Optional	Same as create
Manual Price Entry	Optional	Same as create
Deposit Code	Optional	Same as create
Food Stamp Ind	Optional	Same as create
Wic Ind	Optional	Same as create
Proportional Tare Pct	Optional	Same as create
Fixed Tare Value	Optional	Same as create
Fixed Tare UOM	Optional	Same as create
Reward Eligible Ind	Optional	Same as create
Natl Brand Comp Item	Optional	Same as create
Return Policy	Optional	Same as create
Stop Sale Ind	Optional	Same as create
Elect Mkt Clubs	Optional	Same as create
Report Code	Optional	Same as create
Req Shelf Life on Selection	Optional	Same as create
Req Shelf Life on Receipt	Optional	Same as create
Ib Shelf Life	Optional	Same as create
Store Reorderable Ind	Optional	Same as create
Rack Size	Optional	Same as create
Full Pallet Item	Optional	Same as create
In Store Market Basket	Optional	Same as create
Storage Location	Optional	Same as create
Alt Storage Location	Optional	Same as create
Returnable Ind	Optional	Same as create
Refundable Ind	Optional	Same as create
Back Order Ind	Optional	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.

Functional Area

Items - Reclassification

Integration Type

Oracle Retail Integration Bus (RIB)

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.

Functional Area

Foundation Data

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.

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.

Functional Area

Foundation

Integration Type

Oracle Retail Integration Bus (RIB)

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.

Functional Area

Merchandise Hierarchy Reclassification

Integration Type

Oracle Retail Integration Bus (RIB)

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-9 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 if 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.

Functional Area

Foundation Data

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.

Other Notes:

• Location trait records must exist prior to attaching them to any hierarchy.

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

Outbound ASNs

This section describes the ASNOUT subscription API.

Functional Area

ASNOUT

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Merchandising receives advance shipment notifications (ASN), 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.

Child Nodes

• ASNOUT Distro Details

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.

Child Nodes

• ASNOUT Carton Details

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 (for example, pounds, kilograms) that was shipped.
Carrier Shipment Number	Optional	Not used by Merchandising.
Original Item ID	Optional	Not used by Merchandising.

Child Nodes

ASNOUT Item Details

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 (for example, 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

Payment Terms Subscription API

This section describes the payment terms subscription API.

Functional Area

Payment Terms

Integration Type

Oracle Retail Integration Bus (RIB)

Business Overview

Payment terms are supplier-related financial arrangements 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. Merchandising subscribes to a payment terms message that is held on the RIB. 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-10 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-11 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 days, due day of month and due months forward should all be provided Due days, due day of month and due months forward are all NULL Due days should be provided and both due day of month and due months forward are provided.
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 days, due day of month and due months forward should all be provided Due days, due day of month and due months forward are all NULL Due days should be provided and both due day of month and due months forward are provided.
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: Due days, due day of month and due months forward should all be provided Due days, due day of month and due months forward are all NULL Due days should be provided and both due day of month and due months forward are provided.
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 days, discount day of month and discount months forward should all be provided Discount days, discount day of month and discount months forward are all NULL Discount days should be provided and both discount day of month and discount months forward are provided. Discount days is NULL and both discount day of month and discount months forward are provided.
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 days, discount day of month and discount months forward should all be provided Discount days, discount day of month and discount months forward are all NULL Discount days should be provided and both discount day of month and discount months forward are provided. Discount days is NULL and both discount day of month and discount months forward are provided.
discount months forward	Conditional	Number of months ahead to calculate discount date for invoice payment line. The following discount days data combinations are valid: Discount days, discount day of month and discount months forward should all be provided Discount days, discount day of month and discount months forward are all NULL Discount days should be provided and both discount day of month and discount months forward are provided. Discount days is NULL and both discount day of month and discount months forward are provided.
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.

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

PO Subscription API

This section describes the PO subscription API.

Functional Area

Purchase Orders

Integration Type

Oracle Retail Integration Bus (RIB)

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-12 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 (A). 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-13 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 the 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:

Table 3-14 Creating a Purchase Order Letter of Credit

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:

Table 3-15 Create Expenses

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-16 HTS Validation

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 is 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-17 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

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:

1. Merchandising determines whether a message type contains a receipt or an appointment.

2. If a receipt, Merchandising determines whether the document type is purchase order (P), transfer (T), or allocation (A).

3. If a stock order (transfer or allocation), Merchandising determines whether the receipt is an item level receipt (SK) or a carton level receipt (BL).

4. 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).

5. 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).

Actual (A)

The cartons are received at the correct location against the correct bill of lading.

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:

1. 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.

2. 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

1. 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.

2. 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.

3. 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.

4. 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.

Functional Area

Return to Vendor

Integration Type

Oracle Retail Integration Bus (RIB)

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.

Child Node

• RTV Details

• Custom Flex Attribute

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.

Functional Area

Stock Order Status

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.

Functional Area

Inventory

Integration Type

Oracle Retail Integration Bus (RIB)

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 required field and business validations, the stock counts will be created or updated 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.

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.

Functional Area

Foundation Data

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.

Functional Area

Transfers

Integration Type

Oracle Retail Integration Bus (RIB)

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-18 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: Administrative (AD) AIP Generated (AIP) Book (BT) Confirmation (CF) Externally Generated (EG) Intercompany (IC) Manual Requisition (MR) Reallocation (RAC) Return to Vendor (RV) SIM Generated (SIM) 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-19 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 - Increase by Amount IP - Increase by Percent DA - Decrease by Amount DP - Decrease by Percent S - Set Price 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.

Administration (AD)

• See Manual Requisition

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

Confirmation (CF)

• See Manual Requisition

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.

Functional Area

Foundation Data

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.

Functional Area

Transfers

Integration Type

Oracle Retail Integration Bus (RIB)

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. 1.Work order activities could not be performed for the entire quantity of a particular item at one time.

2. 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 deemed successful and 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.

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)
wostatuscre	Work Order Status Create Message	WOStatusDesc.xsd