| Oracle® Retail Advanced Inventory Planning Implementation Guide Release 14.1 E64951-02 |
|
 Previous |
 Next |
| Oracle® Retail Advanced Inventory Planning Implementation Guide Release 14.1 E64951-02 |
|
Previous |
Next |
This chapter describes and details how to configure AIP Online to publish Purchase Order and Transfer Data to the Oracle Retail Merchandising System (RMS) through the Oracle Retail Integration Bus (RIB).
The Oracle Retail Integration Bus (RIB) is a near real-time data synchronization solution used by AIP for publishing orders to RMS. Order publication begins with the order release batch adding the affected order to the appropriate message family queue staging table and marking each message with a sequence number. AIP publishes two sets of order messages to the RIB: Purchase Orders, and Transfers. RMS subscribes to the RIB messages and inserts the orders into the appropriate RMS Purchase Order and Transfer tables.
A polling operation on the database triggers the message creation. The polling is performed by two threads:
One for the PO_MFQUEUE staging table
One for the TSF_MFQUEUE staging table
The polling is controlled by the configuration settings in the main.properties file.
The order period count defines the number of time intervals that are to be used. An order period count of 0 indicates that no orders will be released. If the order period count is 0, no threads are started.
The time interval defines the amount of time the threads sleep. A thread will not go to sleep until fewer than the maximum number of allowable messages is processed in a given call to the publisher (OrderSenderBean). Publishing fewer than the maximum allowable messages indicates that all orders on the staging table (at the time it was queried) have been processed. Any orders added to the staging table afterward will be processed the next time the thread wakes up and the publisher is invoked.
For each order period count greater than zero, an order period start and order period end must be added to the properties file. When the thread wakes up and the current time falls between the start and end of any of the intervals (up to X intervals, where X is the order period count), the thread will call the publication procedure. If desired, various time intervals can be created to manage the publication of orders by forcing the threads to only poll the staging tables between certain time periods.
The publisher is an Enterprise Java Session Bean named OrderSenderBean. The CheckAndPublish method will query the staging table and the base order table to get the message detail. The publisher will also ensure that messages are published to the RIB in the correct order.
Once the message payload is built by the OrderSenderBean, the RIB message publisher takes the payload and wraps it with an envelope used by the RIB infrastructure.
The purchase order publication messages are in the XOrder message family. In AIP, this message family processes the staged orders on the PO_MFQUEUE table. There are four purchase-order message types used by AIP:
XORDERCRE
XORDERDTLCRE
XORDERMOD
XORDERDTLMOD.
All four message types use the XOrderDesc.xsd.
This message type indicates that a brand new purchase order is being sent to RMS. The orders are sent to RMS in an A, indicating Approved status. This message type is inserted into PO_MFQUEUE in three different circumstances:
The purchase order was released by the batch, or you have chosen to release the purchase order in the OM Order Maintenance window.
You have created a new purchase order in the OM Order Create window.
In the OM Order Maintenance window, you have chosen to move a purchase order delivery date and/or destination and generated a new order number.
This message type indicates a new line item is being added to the purchase order after the order was externally communicated. This message type is inserted into PO_MFQUEUE when you have moved the purchase order destination and chosen to retain the existing order number, and the destination does not already exist on the order for that item.
This message type indicates that a modification was made to the overall purchase order details (header level information). This message type is inserted into PO_MFQUEUE in the following circumstances:
You have moved the purchase order delivery date and chosen to retain the existing order number.
You have canceled all ordered quantity of all items on the purchase order. The total order quantity for the entire purchase order is zero. The purchase order is sent to RMS with a C, indicating Canceled status.
This message type indicates that a modification was made to the purchase order line items after the order was externally communicated. This message type is inserted into PO_MFQUEUE when you perform various actions in the OM Order Maintenance window.
You have modified the order quantity of a purchase order that is not Closed.
You have chosen to move a purchase order line item to a new destination and retain the order number. If the move to destination already exists on the order, a message will be written to the staging table to increase the quantity at the move to location.
|
Note: Only one message can be inserted for the move to destination. This will either be an XORDERDTLCRE if the destination is new or XORDERDTLMOD if the SKU is already being delivered to the move to destination. |
The order quantity of the move from destination must be decremented to equal the received quantity. A message will be staged for the move from destination.
The Order Management application within AIP releases the necessary data to be sent to RMS into staging tables.
Data sent into RMS is listed in these tables:
Table 15-1 Purchase Order Header Message Layout
| Column Name | Data Type | RIB XML Message Tag | Description/Comments |
|---|---|---|---|
|
Order Number |
String |
order_no |
Pre-defined unique number |
|
Supplier ID |
String |
supplier |
Supplier unique identifier |
|
Currency Code |
String |
currency_code |
|
|
Terms |
String |
Terms |
|
|
Delivery Date |
Date |
not_before_date not_after_date |
Earliest expected delivery date. Latest expected delivery date. |
|
Open-to-buy End-of-Week Date |
Date |
otb_eow_date |
|
|
Department |
Number |
dept |
|
|
Status |
String |
Status |
A status value of W for Worksheet or A for Approved is required for purchase order creation. A purchase order cannot be created in approved status without detail line items attached to it. Attempting to do so will result in message rejection. |
|
Exchange Rate |
Number |
exchange_rate |
|
|
Include on Order indicator |
String |
include_on_ord_ind |
|
|
Written Date |
Date |
written_date |
|
|
Order Line Item Detail |
Pointer |
XOrderDtl |
This is a pointer to the line item details. Depending on the message type, this tag is repeated for each line item. |
|
Origin Indicator |
String |
orig_ind |
Indicates the System of Origination. |
|
EDI |
String |
edi_po_ind |
|
|
Pre-Mark Indicator |
String |
pre_mark_ind |
|
|
User ID |
String |
user_id |
|
|
User ID |
String |
Comment_desc |
Table 15-2 Purchase Order - Detail Message Layout
| Column Name | Data Type | RIB XML Message Tag | Description/Comments |
|---|---|---|---|
|
RMS SKU |
String |
XOrderDtl.item |
Uses the RMS SKU mapping table to convert AIP commodity pack size into RMS SKU. |
|
Location |
Integer |
XOrderDtl.location |
Globally unique scheduling location identifier. |
|
Unit Cost |
Decimal |
xOrderDtl.unit_cost |
Not Available. |
|
Reference item |
String |
xOrderDtl.ref_item |
|
|
Origin Country Indicator |
String |
xOrderDtl.origin_country_id |
|
|
Supplier Pack Size |
Decimal |
XOrderDtl.supp_pack_size |
|
|
Order Quantity |
Decimal |
XOrderDtl.qty_ordered |
|
|
Location Type |
String |
XorderDtl.location_type |
Order Destination Type: Store or Warehouse. |
|
Cancel Indicator |
String |
xOrderDtl.cancel_ind |
|
|
Reinstate Indicator |
String |
xOrderDtl.reinstate_ind |
Table 15-3 Transfers - Header Message Layout
| Column Name | Data Type | RIB XML Message Tag | Description/Comments |
|---|---|---|---|
|
Transfer Number |
Integer |
tsf_no |
Pre-defined unique number. |
|
From Location Type |
String |
from_loc_type |
|
|
From Location |
String |
from_loc |
|
|
To Location Type |
String |
to_loc_type |
|
|
To Location |
String |
to_loc |
|
|
Delivery Date |
Date |
delivery_date |
|
|
Department |
Integer |
dept |
Not available in AIP. |
|
Routing Code |
String |
routing_code |
Not available in AIP. |
|
Freight Code |
String |
freight_code |
Not available in AIP. |
|
Transfer Type |
String |
tsf_type |
|
|
Transfer Detail |
Pointer |
XTsfDtl* |
|
|
Transfer Status |
String |
status |
|
|
User ID |
String |
user_id |
|
|
Comments |
String |
comment_desc |
Data Element Details
| Data Type | Data Element Name | Data Description |
|---|---|---|
| RIB Publication Message | Create Order | Contains Purchase Order header and details. |
Extracting Program Details
| Program Type | Program Name | Schema File | Program Frequency |
|---|---|---|---|
| EJB | OrderSenderBean.java | not applicable | Near Real Time |
Data Source and Target Details
| Data Source Details | Target Data Details | ||
|---|---|---|---|
| Data Origin System | AIP Online | Target Object Type | RIB Message - Xorder Family |
| Source Tables/Files | STORE_ORDER, STORE, SUPPLIER, PO_MFQUEUE, COMMODITY_MAPPING, NON_CONTENTS_ORDER, STOCKING_POINT | Target Object Name | XORDERCRE Message |
| Target Load Type | not applicable | ||
Field Level Mapping - Source
| # |
Source Table | Source Table Column | Source Field Description | Data Type | Field Length |
|---|---|---|---|---|---|
| 1 | PO_MFQUEUE | ORDER_NUMBER | Order Number | Number | (10,0) |
| 2 | SUPPLIER | SUPPLIER_CODE | |||
| 3 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 4 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 5 | STORE_ORDER NON_CONTENTS_ORDER | DELIVERY_DATE | Delivery Date | Date | not applicable |
| 6 | STORE_ORDER NON_CONTENTS_ORDER | DELIVERY_DATE | Delivery Date | Date | not applicable |
| 7 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 8 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 9 | PO_MFQUEUE | STATUS | Status | Varchar2 | 1 |
| 10 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 11 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 12 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 13 | XORDER Detail Records | ||||
| COMMODITY_MAPPING | RMS_SKU_NUMBER | RMS SKU | Varchar2 | 25 | |
| STORE STOCKING_POINT | STORE_CODE STOCKING_POINT_NUMBER | Store Code Stocking Point Number | Varchar2 | 20 | |
| not applicable | not applicable | not applicable | not applicable | not applicable | |
| not applicable | not applicable | not applicable | not applicable | not applicable | |
| not applicable | not applicable | not applicable | not applicable | not applicable | |
| COMMODITY_MAPPING | RMS_ORDER_MULTIPLE | RMS Order Multiple | Number | 8 | |
| STORE_ORDER NON_CONTENTS_ORDER COMMODITY_MAPPING | CASE_VOLUME QUANTITY PACK_SIZE | Case Volume Quantity Pack Size | Number | 8 | |
| not applicable | not applicable | not applicable | not applicable | not applicable | |
| not applicable | not applicable | not applicable | not applicable | not applicable | |
| not applicable | not applicable | not applicable | not applicable | not applicable | |
| 14 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 15 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 16 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 17 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 18 | not applicable | not applicable | not applicable | not applicable | not applicable |
Field Level Mapping - Target
| # |
Target Data Field Name | Target Field Description | Field Data Type | Field Length | Condition/Format |
|---|---|---|---|---|---|
| 1 | order_no | The unique identifier for the order. | Varchar2 | 10 | not applicable |
| 2 | supplier | The identifier of the supplier from which the order will be sourced. This cannot be modified if details exist for the PO. | Varchar2 | 10 | A substring is used to drop the V prefix that is appended to all RMS supplier numbers. |
| 3 | currency_code | The code of the order's currency. | Varchar2 | 3 | Hardcoded as NULL. |
| 4 | terms | The sales terms of the order. | Varchar2 | 15 | Hardcoded as NULL. |
| 5 | not_before_date | The first date that delivery will be accepted. | Date | Select the minimum delivery date from the order line items that are not closed. All order line items that are not closed will have the same delivery date. | |
| 6 | not_after_date | The last date that delivery will be accepted. | Date | Select the maximum delivery date from the order line items that are not closed. All order line items that are not closed will have the same delivery date. | |
| 7 | otb_eow_date | The end of week date of the OTB bucket used. | Date | Hardcoded as NULL. | |
| 8 | dept | The department in which all the items are on the order. | Number | 4 | Hardcoded as NULL. |
| 9 | status | The code for the status of the order. Valid values are W for Worksheet and A for Approved for PO creation. It is also possible to modify the status to C for Closed. | Varchar2 | 1 | The table column has a default of A. |
| 10 | exchange_rate | The rate of exchange for the PO used between the order and primary currencies. | Number | 20 | Hardcoded as NULL. |
| 11 | include_on_ord_ind | Indicates if the order should be included in on-order calculations. | Varchar2 | 1 | Hardcoded as NULL. |
| 12 | written_date | The date the order was created. | Date | Hardcoded as NULL. | |
| 13 | XORDER Detail Records | ||||
| item | An approved, transaction level item. | Varchar2 | 25 | not applicable | |
| location | An active store or warehouse. | Number | (10,0) | A substring is used to drop the S prefix that is appended to all RMS store numbers and to drop the W prefix that is appended to all RMS warehouse numbers. | |
| unit_cost | The cost of the item from the supplier in the order's currency. | Number | (20,4) | Hardcoded as NULL. | |
| ref_item | The ID of a reference item that can be used instead of using the item field. | Varchar2 | 25 | Hardcoded as NULL. | |
| origin_country_id | The identifier of the country from which the item is being sourced. | Varchar2 | 3 | Hardcoded as NULL. | |
| supp_pack_size | The supplier pack size for the item on the order. | Number | (12,3) | The AIP SKU-pack size is mapped to the RMS Item and Order Multiple. | |
| qty_ordered | The quantity ordered of item. | Number | (12,4) | Non-pack SKUs·:
Formal Pack SKUs:
|
|
| location_type | The location type of the location. | Varchar2 | 1 | S indicates the destination location is a store.
W indicates the destination location is a warehouse. |
|
| cancel_ind | Indicates if the detail record's quantity should be cancelled. | Varchar2 | 1 | Hardcoded as NULL. | |
| reinstate_ind | Indicates if a detail record that was previously cancelled should be reinstated. | Varchar2 | 1 | Hardcoded as NULL. | |
| 14 | origin_ind | Indicates where the order originated. Valid values include: 2 - Manual, 6 - AIP generated order, 7, 8. | Varchar2 | 1 | Six (6) is a unique RMS identifier that indicates the PO was created in AIP and is hardcoded. |
| 15 | edi_po_ind | Indicates whether or not the order is transmitted to the supplier through an Electronic Data Exchange transaction. Valid values are: Y = Submit byEDI, N = Do not use EDI. | Varchar2 | 1 | Hardcoded as NULL. |
| 16 | pre_mark_ind | This field indicated whether or not a supplier has agreed to break an order into separate boxes so that the boxes can be sent directly to stores. | Varchar2 | 1 | Hardcoded as NULL. |
| 17 | user_id | Indicates where the order was approved. It will be the user ID of the person approving the order. | Varchar2 | 30 | Hardcoded as NULL. |
| 18 | comment_desc | Any comments pertaining to the order. | Varchar2 | 2000 | Hardcoded as NULL. |
Store Orders
poQ.file_interface_ind = 'N' AND so.order_number = poQ.order_number AND so.future_release_ind = 'N' AND so.supplier_id = supp.supplier_id AND so.commodity_id=cm.commodity_id AND so.pack_size=cm.pack_size AND s.store_id=so.store_id AND (poQ.store_order_id=so.store_order_id OR poQ.store_order_id IS NULL)
Warehouse Orders
poQ.file_interface_ind = 'N' AND nco.order_number = poQ.order_number AND nco.source_type='V' AND nco.source_id=s.supplier_id AND nco.commodity_id=cm.commodity_id AND nco.pack_size=cm.pack_size AND nco.stocking_point_id = chamber.stocking_point_id AND (poQ.non_contents_order_id=nco.non_contents_order_id OR poQ.non_contents_order_id IS NULL) AND wh.stocking_point_id(+) = chamber.parent_stocking_point_id
The Order Detail create message is in the same format and basic content as the Order Create message; however, the message will only contain any new order line items. Any line items that have already been communicated to RMS will not be included in an Order Detail Create message.
Data Element Details
| Data Type | Data Element Name | Data Description |
|---|---|---|
| RIB Publication Message | Create Order Detail | Contains Purchase Order Header and new detail information. |
Extracting Program Details
| Program Type | Program Name | Schema File | Program Frequency |
|---|---|---|---|
| EJB | OrderSenderBean.java | not applicable | Near Real Time |
Data Source and Target Details
| Data Source Details | Target Data Details | ||
|---|---|---|---|
| Data Origin System | AIP Online | Target Object Type | RIB Message - Xorder Family |
| Source Tables/Files | STORE_ORDER, STORE, SUPPLIER, PO_MFQUEUE, COMMODITY_MAPPING, NON_CONTENTS_ORDER, STOCKING_POINT | Target Object Name | XORDERDTL Detail Message |
| Target Load Type | not applicable | ||
Field Level Mapping - Source
| # |
Source Table | Source Table Column | Source Field Description | Data Type | Field Length |
|---|---|---|---|---|---|
| 1 | COMMODITY_MAPPING | RMS_SKU_NUMBER | RMS SKU | Varchar2 | 25 |
| 2 | STORE STOCKING_POINT | STORE_CODE STOCKING_POINT_NUMBER | Store Code Stocking Point Number | Varchar2 | 20 |
| 3 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 4 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 5 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 6 | COMMODITY_MAPPING | RMS_ORDER_MULTIPLE | RMS Order Multiple | Number | 8 |
| 7 | STORE_ORDER NON_CONTENTS_ORDER COMMODITY_MAPPING | CASE_VOLUME QUANTITY PACK_SIZE | Case Volume Quantity Pack Size | Number | 8 |
| 8 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 9 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 10 | not applicable | not applicable | not applicable | not applicable | not applicable |
Field Level Mapping - Target
| # |
Target Data Field Name | Target Field Description | Field Data Type | Field Length | Condition/Format |
|---|---|---|---|---|---|
| 1 | item | An approved, transaction level item. | Varchar2 | 25 | not applicable |
| 2 | location | An active store or warehouse. | Number | (10,0) | A substring is used to drop the S prefix that is appended to all RMS store numbers and to drop the W prefix that is appended to all RMS warehouse numbers. |
| 3 | unit_cost | The cost of the item from the supplier in the order's currency. | Number | (20,4) | Hard coded as NULL. |
| 4 | ref_item | The ID of a reference item that can be used instead of using the item field. | Varchar2 | 25 | Hard coded as NULL. |
| 5 | origin_country_id | The identifier of the country from which the item is being sourced. | Varchar2 | 3 | Hard coded as NULL. |
| 6 | supp_pack_size | The supplier pack size for the item on the order. | Number | (12,3) | The AIP SKU-pack size is mapped to the RMS Item and Order Multiple. |
| 7 | qty_ordered | The quantity ordered of item. | Number | (12,4) | Non-pack SKUs:
Formal Pack SKUs:
|
| 8 | location_type | The location type of the location. | Varchar2 | 1 | S indicates the destination location is a store. W indicates the destination location is a warehouse. |
| 9 | cancel_ind | Indicates if the detail record's quantity should be cancelled. | Varchar2 | 1 | Hard coded as NULL. |
| 10 | reinstate_ind | Indicates if a detail record that was previously cancelled should be reinstated. | Varchar2 | 1 | Hard coded as NULL. |
Store Orders
so.order_number=pm.order_number AND so.supplier_id = supp.supplier_id AND so.commodity_id=cm.commodity_id AND so.pack_size=cm.pack_size AND s.store_id=so.store_id AND (pm.store_order_id=so.store_order_id OR pm.store_order_id IS NULL)
Warehouse Orders
nco.source_type="V" AND nco.order_number=pm.order_number AND nco.source_id=s.supplier_id AND nco.commodity_id=cm.commodity_id AND nco.pack_size=cm.pack_size AND nco.stocking_point_id = sp1.stocking_point_id AND (pm.non_contents_order_id=nco.non_contents_order_id OR pm.non_contents_order_id IS NULL) AND sp2.stocking_point_id(+) = sp1.parent_stocking_point_id
Data Element Details
| Data Type | Data Element Name | Data Description |
|---|---|---|
| RIB Publication Message | Modify Order Header | Contains Purchase Order header details. |
Extracting Program Details
| Program Type | Program Name | Schema File | Program Frequency |
|---|---|---|---|
| EJB | OrderSenderBean.java | not applicable | Near Real Time |
Data Source and Target Details
| Data Source Details | Target Data Details | ||
|---|---|---|---|
| Data Origin System | AIP Online | Target Object Type | RIB Message - Xorder Family |
| Source Tables/Files | STORE_ORDER, STORE, SUPPLIER, PO_MFQUEUE, COMMODITY_MAPPING, NON_CONTENTS_ORDER, STOCKING_POINT | Target Object Name | XORDERMOD Header Message |
| Target Load Type | not applicable | ||
Field Level Mapping - Source
| # |
Source Table | Source Table Column | Source Field Description | Data Type | Field Length |
|---|---|---|---|---|---|
| 1 | PO_MFQUEUE | ||||
| 2 | SUPPLIER | ||||
| 3 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 4 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 5 | STORE_ORDER NON_CONTENTS_ORDER | DELIVERY_DATE | Delivery Date | Date | not applicable |
| 6 | STORE_ORDER NON_CONTENTS_ORDER | DELIVERY_DATE | Delivery Date | Date | not applicable |
| 7 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 8 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 9 | PO_MFQUEUE | STATUS | Status | Varchar2 | 1 |
| 10 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 11 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 12 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 13 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 14 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 15 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 16 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 17 | not applicable | not applicable | not applicable | not applicable | not applicable |
Field Level Mapping - Target
| # |
Target Data Field Name | Target Field Description | Field Data Type | Field Length | Condition/Format |
|---|---|---|---|---|---|
| 1 | order_no | The unique identifier for the order. | Varchar2 | 10 | not applicable |
| 2 | supplier | The identifier of the supplier from which the order will be sourced. This cannot be modified if details exist for the PO. | Varchar2 | 10 | A substring is used to drop the V prefix that is appended to all RMS supplier numbers. |
| 3 | currency_code | The code of the order's currency. | Varchar2 | 3 | Hardcoded as NULL. |
| 4 | terms | The sales terms of the order. | Varchar2 | 15 | Hardcoded as NULL. |
| 5 | not_before_date | The first date that delivery will be accepted. | Date | Select the minimum delivery date from the order line items that are not closed. All order line items that are not closed will have the same delivery date. | |
| 6 | not_after_date | The last date that delivery will be accepted. | Date | Select the maximum delivery date from the order line items that are not closed. All order line items that are not closed will have the same delivery date. | |
| 7 | otb_eow_date | The end of week date of the OTB bucket used. | Date | Hardcoded as NULL. | |
| 8 | dept | The department in which are all the items on the order. | Number | 4 | Hardcoded as NULL. |
| 9 | status | The code for the status of the order. Valid values are W for Worksheet and A for Approved for PO creation. It is also possible to modify the status to C for Closed. | Varchar2 | 1 | The table column has a default of A. If all order quantities are 0 the status of C for Cancel must be sent to RMS. |
| 10 | exchange_rate | The rate of exchange for the PO used between the order and primary currencies. | Number | 20 | Hardcoded as NULL. |
| 11 | include_on_ord_ind | Indicates if the order should be included in on-order calculations. | Varchar2 | 1 | Hardcoded as NULL. |
| 12 | written_date | The date the order was created. | Date | Hardcoded as NULL. | |
| 13 | origin_ind | Indicates where the order originated. Valid values include: 2 - Manual, 6 - AIP generated order, 7, 8. | Varchar2 | 1 | Six (6) is a unique RMS identifier that indicates the PO was created in AIP and is hardcoded. |
| 14 | edi_po_ind | Indicates whether or not the order will be transmitted to the supplier through an Electronic Data Exchange transaction. Valid values are: Y = Submit by EDI, N = Do not use EDI. | Varchar2 | 1 | Hardcoded as NULL. |
| 15 | pre_mark_ind | This field indicated whether or not a supplier has agreed to break an order into separate boxes so that the boxes can be sent directly to stores. | Varchar2 | 1 | Hardcoded as NULL. |
| 16 | user_id | Indicates where the order was approved. It will be the user ID of the person approving the order. | Varchar2 | 30 | Hardcoded as NULL. |
| 17 | comment_desc | Any comments pertaining to the order. | Varchar2 | 2000 | Hardcoded as NULL. |
Store Orders
so.order_number=pm.order_number AND so.supplier_id = supp.supplier_id AND so.commodity_id=cm.commodity_id AND so.pack_size=cm.pack_size AND s.store_id=so.store_id AND (pm.store_order_id=so.store_order_id OR pm.store_order_id IS NULL)
Warehouse Orders
nco.source_type="V" AND nco.order_number=pm.order_number AND nco.source_id=s.supplier_id AND nco.commodity_id=cm.commodity_id AND nco.pack_size=cm.pack_size AND nco.stocking_point_id = sp1.stocking_point_id AND (pm.non_contents_order_id=nco.non_contents_order_id OR pm.non_contents_order_id IS NULL) AND sp2.stocking_point_id(+) = sp1.parent_stocking_point_id
The Order Detail Modification message is in the same format and has similar content as the Order Create message; however, the message will only contain any modified order line items. Any line items that have already been communicated to RMS but have not been modified will not be included in an Order Detail Modification message.
Data Element Details
| Data Type | Data Element Name | Data Description |
|---|---|---|
| RIB Publication Message | Modify Order Detail | Contains Purchase Order header and detail information. |
Extracting Program Details
| Program Type | Program Name | Schema File | Program Frequency |
|---|---|---|---|
| EJB | OrderSenderBean.java | not applicable | Near Real Time |
Data Source and Target Details
| Data Source Details | Target Data Details | ||
|---|---|---|---|
| Data Origin System | AIP Online | Target Object Type | RIB Message - Xorder Family |
| Source Tables/Files | STORE_ORDER, STORE, SUPPLIER, PO_MFQUEUE, COMMODITY_MAPPING, NON_CONTENTS_ORDER, STOCKING_POINT | Target Object Name | XORDERDTLMOD Message |
| Target Load Type | not applicable | ||
Field Level Mapping - Source
| # |
Source Table | Source Table Column | Source Field Description | Data Type | Field Length |
|---|---|---|---|---|---|
| 1 | COMMODITY_MAPPING | RMS_SKU_NUMBER | RMS SKU | Varchar2 | 25 |
| 2 | STORE STOCKING_POINT | STORE_CODE STOCKING_POINT_NUMBER | Store Code Stocking Point Number | Varchar2 | 20 |
| 3 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 4 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 5 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 6 | COMMODITY_MAPPING | RMS_ORDER_MULTIPLE | RMS Order Multiple | Number | 8 |
| 7 | STORE_ORDER NON_CONTENTS_ORDER COMMODITY_MAPPING | CASE_VOLUME_DELTA QUANTITY_DELTA PACK_SIZE | Case Volume Quantity Pack Size | Number | 8 |
| 8 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 9 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 10 | not applicable | not applicable | not applicable | not applicable | not applicable |
Field Level Mapping - Target
| # |
Target Data Field Name | Target Field Description | Field Data Type | Field Length | Condition/Format |
|---|---|---|---|---|---|
| 1 | item | An approved, transaction-level item | Varchar2 | 25 | not applicable |
| 2 | location | An active store or warehouse. | Number | (10,0) | A substring is used to drop the S prefix that is appended to all RMS store numbers and to drop the W prefix that is appended to all RMS warehouse numbers. |
| 3 | unit_cost | An active store or warehouse. | Number | (20,4) | Hardcoded as NULL. |
| 4 | ref_item | The cost of the item from the supplier in the order's currency. | Varchar2 | 25 | Hardcoded as NULL. |
| 5 | origin_country_id | The ID of a reference item that can be used instead of using the item field. | Varchar2 | 3 | Hardcoded as NULL. |
| 6 | supp_pack_size | The identifier of the country from which the item is being sourced. | Number | (12,3) | The AIP SKU-pack size is mapped to the RMS Item and Order Multiple. |
| 7 | qty_ordered | The supplier pack size for the item on the order. | Number | (12,4) | Non-pack SKUs:
Formal Pack SKUs:
|
| 8 | location_type | Changed quantity in eaches. | Varchar2 | 1 | S indicates the destination location is a store.
W indicates the destination location is a warehouse. |
| 9 | cancel_ind | The location type of the location. | Varchar2 | 1 | Hardcoded as NULL. |
| 10 | reinstate_ind | Indicates if a detail record that was previously cancelled should be reinstated. | Varchar2 | 1 | Hardcoded as NULL. |
Store Orders
so.order_number=pm.order_number AND so.supplier_id = supp.supplier_id AND so.commodity_id=cm.commodity_id AND so.pack_size=cm.pack_size AND s.store_id=so.store_id AND (pm.store_order_id=so.store_order_id OR pm.store_order_id IS NULL)
Warehouse Orders
nco.source_type="V" AND nco.order_number=pm.order_number AND nco.source_id=s.supplier_id AND nco.commodity_id=cm.commodity_id AND nco.pack_size=cm.pack_size AND nco.stocking_point_id = sp1.stocking_point_id AND (pm.non_contents_order_id=nco.non_contents_order_id OR pm.non_contents_order_id IS NULL) AND sp2.stocking_point_id(+) = sp1.parent_stocking_point_id
Data Element Details
| Data Type | Data Element Name | Data Description |
|---|---|---|
| RIB Publication Message | New Transfer | Contains Transfer header and details. |
Extracting Program Details
| Program Type | Program Name | Schema File | Program Frequency |
|---|---|---|---|
| EJB | OrderSenderBean.java | not applicable | Near Real Time |
Data Source and Target Details
| Data Source Details | Target Data Details | ||
|---|---|---|---|
| Data Origin System | AIP Online | Target Object Type | RIB Message XTsf Family |
| Source Tables/Files | STORE_ORDER, STORE, TSF_MFQUEUE, COMMODITY_MAPPING, NON_CONTENTS_ORDER, STOCKING_POINT | Target Object Name | XTSFCRE Message |
| Target Load Type | not applicable | ||
Field Level Mapping - Source
| # |
Source Table | Source Table Column | Source Field Description | Data Type | Field Length |
|---|---|---|---|---|---|
| 1 | TSF_MFQUEUE | TSF_NUMBER | Transfer Number | Number | (10,0) |
| 2 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 3 | STOCKING_POINT | STOCKING_POINT_NUMBER | not applicable | not applicable | not applicable |
| 4 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 5 | STORE STOCKING_POINT | STORE_CODE STOCKING_POINT_NUMBER | Store Code Stocking Point Number | Varchar2 | 20 |
| 6 | STORE_ORDER NON_CONTENTS_ORDER | DELIVERY_DATE | Delivery Date | Date | not applicable |
| 7 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 8 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 9 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 10 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 11 | XTSF Detail Record Layout | ||||
| COMMODITY_MAPPING | RMS_SKU_NUMBER | RMS SKU | Varchar2 | 25 | |
| STORE_ORDER NON_CONTENTS_ORDER COMMODITY_MAPPING | CASE_VOLUME QUANTITY PACK_SIZE | Case Volume Quantity Pack Size | Number | 8 | |
| COMMODITY_MAPPING | RMS_ORDER_MULTIPLE | RMS Order Multiple | Number | 8 | |
| not applicable | not applicable | not applicable | not applicable | not applicable | |
| not applicable | not applicable | not applicable | not applicable | not applicable | |
| 12 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 13 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 14 | not applicable | not applicable | not applicable | not applicable | not applicable |
Field Level Mapping - Target
| # |
Target Data Field Name | Target Field Description | Field Data Type | Field Length | Condition/Format |
|---|---|---|---|---|---|
| 1 | tsf_no | Number that uniquely identifies the transfer. | Number | 10 | not applicable |
| 2 | from_loc_type | The location type of the from location. | Varchar2 | 1 | Hardcoded as W |
| 3 | from_loc | The location number of the from location. | Varchar2 | 10 | A substring is used to drop the W prefix that is appended to all RMS warehouse numbers. |
| 4 | to_loc_type | The location type of the to location. | Varchar2 | 1 | S indicates the destination location is a store.
W indicates the destination location is a warehouse. |
| 5 | to_loc | The location number of the to location. | Varchar2 | 10 | A substring is used to drop the S prefix that is appended to all RMS store numbers and to drop the W prefix that is appended to all RMS warehouse numbers. |
| 6 | delivery_date | The earliest date the transfer can be delivered. | Date | not applicable | |
| 7 | dept | The department number associated with the transfer. | Number | 4 | Hardcoded as NULL |
| 8 | routing_code | If the freight status is expedite, this is a code indicating more detailed freight info. | Varchar2 | 1 | Hardcoded as NULL. |
| 9 | freight_code | A code indicating the freight status of the transfer (for example, normal, expedite). | Varchar2 | 1 | Hardcoded as NULL. |
| 10 | tsf_type | A code indicating the type of transfer (for example, store requisition, book transfer). | Varchar2 | 6 | Hardcoded as NULL. |
| 11 | XTSF Detail Record Layout | ||||
| item | The unique identifier of the item being transferred. | Varchar2 | 25 | not applicable | |
| tsf_qty | The total quantity of the item reserved for this transfer at the from location. | Number | (12,4) | Non-pack SKUs·:
Formal Pack SKUs:
|
|
| supp_pack_size | The supplier pack size for this item/transfer. | Number | (12,4) | The AIP SKU-pack size is mapped to the RMS Item and Order Multiple. | |
| inv_status | A code indicating the inventory status for this transfer detail; valid values are found on the inv_status_types table. | Number | 2 | Hardcoded as NULL. | |
| unit_cost | Not mapped to a database field. Sometimes used to calculate retail price. | Number | (20,4) | Hardcoded as NULL. | |
| 12 | status | A code indicating the status of the transfer (for example, approved, closed,). | Varchar2 | 1 | The transfer is created in Approved status so hardcoded as A. |
| 13 | user_id | The user ID of the user who created the transfer. | Varchar2 | 30 | Hardcoded as NULL. |
| 14 | comment_desc | Comments associated with the transfer. | Varchar2 | 2000 | Hardcoded as NULL. |
Data Element Details
| Data Type | Data Element Name | Data Description |
|---|---|---|
| RMS Subscriber Mapping | Create/Modify Order Header | Contains Purchase Order header details. |
Extracting Program Details
| Program Type | Program Name | Schema File | Program Frequency |
|---|---|---|---|
| RIB Subscriber Adapter | RMS Subscriber Adapter | not applicable | Near Real Time |
Data Source and Target Details
| Data Source Details | Target Data Details | ||
|---|---|---|---|
| Data Origin System | AIP Online | Target Object Type | RMS Database |
| Source Tables/Files | STORE_ORDER, STORE, SUPPLIER, PO_MFQUEUE, COMMODITY_MAPPING, NON_CONTENTS_ORDER, STOCKING_POINT | Target Object Name | ORDHEAD Table |
| Target Load Type | not applicable | ||
Field Level Mapping - Source
| # |
Source Table | Source Table Column | Source Field Description | Data Type | Field Length |
|---|---|---|---|---|---|
| 1 | PO_MFQUEUE | ORDER_NUMBER | Order Number | Number | (10,0) |
| 2 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 3 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 4 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 5 | SUPPLIER | SUPPLIER_CODE | Supplier Code | Varchar2 | 20 |
| 6 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 7 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 8 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 9 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 10 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 11 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 12 | STORE_ORDER NON_CONTENTS_ORDER | min of DELIVERY_DATE | Delivery Date | Date | not applicable |
| 13 | STORE_ORDER NON_CONTENTS_ORDER | max of DELIVERY_DATE | Delivery Date | Date | not applicable |
| 14 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 15 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 16 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 17 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 18 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 19 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 20 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 21 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 22 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 23 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 24 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 25 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 26 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 27 | PO_MFQUEUE | STATUS | Status | Varchar2 | 1 |
| 28 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 29 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 30 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 31 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 32 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 33 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 34 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 35 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 36 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 37 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 38 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 39 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 40 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 41 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 42 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 43 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 44 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 45 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 46 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 47 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 48 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 49 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 50 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 51 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 52 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 53 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 54 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 55 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 56 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 57 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 58 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 59 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 60 | not applicable | not applicable | not applicable | not applicable | not applicable |
Field Level Mapping - Target
| # |
Target Data Field Name | Target Field Description | Field Data Type | Field Length | Condition/Format |
|---|---|---|---|---|---|
| 1 | ORDER_NO | The unique identifier for the order. | Number | (8,0) | not applicable |
| 2 | ORDER_TYPE | Order Type | Varchar2 | 3 | Hardcode as N/B at destination. |
| 3 | DEPT | Number | (4,0) | Hardcoded as NULL at Source. | |
| 4 | BUYER | Number | (4,0) | NULL | |
| 5 | SUPPLIER | The identifier of the supplier from which the order will be sourced. This cannot be modified if details exist for the PO. | Number | (10,0) | A substring is used to drop the V prefix that is appended to all RMS supplier numbers. |
| 6 | SUPP_ADD_SEQ_NO | Supplier Address Sequence Number | Number | (4,0) | Populated with primary address sequence number for the primary supplier. |
| 7 | LOC_TYPE | Location Type | Varchar2 | 1 | NULL |
| 8 | LOCATION | Location Type | Number | (10,0) | NULL |
| 9 | PROMOTION | Promotion Number | Number | (10,0) | NULL |
| 10 | QC_IND | QC Indicator | Varchar2 | 1 | Hardcoded as N at destination. |
| 11 | WRITTEN_DATE | The date order was created. | Date | Hardcoded as today's Vdate. | |
| 12 | NOT_BEFORE_DATE | The first date that delivery will be accepted. | Date | If Source value is NULL, then Vdate Else Source Value. | |
| 13 | NOT_AFTER_DATE | The last date that delivery will be accepted. | Date | If Source value is NULL, then Vdate Else Source Value. | |
| 14 | OTB_EOW_DATE | The end of week date of the OTB bucket used. | Date | Populated with EOW date for the date NOT_AFTER_DATE at destination. | |
| 15 | EARLIEST_SHIP_DATE | Earliest Shipment Date | Date | Populated as NOT_BEFORE_DATE at destination. | |
| 16 | LATEST_SHIP_DATE | Latest Shipment Date | Date | Calculated at destination as NOT_BEFORE_DATE + LATEST_SHIP_DAYS from SYSTEM_OPTIONS table. | |
| 17 | CLOSE_DATE | Order Close Date | Date | Hardcoded as NULL. | |
| 18 | TERMS | The sales terms of the order. | Varchar2 | 15 | Populated as TERMS of primary supplier from SUPS table. |
| 19 | FREIGHT_TERMS | The freight terms of the order. | Varchar2 | 30 | Populated as FREIGHT_TERMS of primary supplier from SUPS table. |
| 20 | ORIG_IND | Indicates where the order originated. Valid values include: 2 - Manual, 6 - AIP generated order, 7, 8. | Number | (1,0) | Six (6) is a unique RMS identifier that indicates the PO was created in AIP and is hardcoded at source. |
| 21 | CUST_ORDER | Customer Order Indicator | Varchar2 | 1 | Hardcoded as N at destination. |
| 22 | PAYMENT_METHOD | Payment Method for the Order | Varchar2 | 6 | Populated as PAYMENT_METHOD of primary supplier from SUPS table. |
| 23 | BACKHAUL_TYPE | Backhaul Type | Varchar2 | 6 | NULL |
| 24 | BACKHAUL_ALLOWANCE | Backhaul Allowance | Number | (20,4) | NULL |
| 25 | SHIP_METHOD | Shipping Method | Varchar2 | 6 | Populated as SHIP_METHOD of primary supplier from SUPS table. |
| 26 | PURCHASE_TYPE | Purchase Type | Varchar2 | 6 | NULL Label column |
| 27 | STATUS | The code for the status of the order. | Varchar2 | 1 | Source has the status as A. |
| 28 | ORIG_APPROVAL_DATE | Original Approval Date of the Order | Date | If Status is Approved, hardcoded as VDATE at destination, otherwise, NULL. | |
| 29 | ORIG_APPROVAL_ID | Original Approval User ID | Varchar2 | 30 | User ID used to run the batch/adapter. |
| 30 | SHIP_PAY_METHOD | Shipment Pay Method | Varchar2 | 2 | NULL |
| 31 | FOB_TRANS_RES | Trans Reserve | Varchar2 | 2 | NULL |
| 32 | FOB_TRANS_RES_DESC | Trans Reserve Description | Varchar2 | 45 | NULL |
| 33 | FOB_TITLE_PASS | Title Pass | Varchar2 | 2 | Populated as FOB_TITLE_PASS from SYSTEM_OPTIONS table. |
| 34 | FOB_TITLE_PASS_DESC | Title Pass Description | Varchar2 | 45 | NULL |
| 35 | EDI_SENT_IND | EDI Sent Indicator | Varchar2 | 1 | Hardcoded as N at destination. |
| 36 | EDI_PO_IND | EDI PO Indicator | Varchar2 | 1 | Hardcoded as N at destination. |
| 37 | IMPORT_ORDER_IND | Import Order Indicator | Varchar2 | 1 | Hardcoded as N at destination. |
| 38 | IMPORT_COUNTRY_ID | Imported Country ID | Varchar2 | 3 | Populated as BASE_COUNTRY_ID from SYSTEM_OPTIONS table. |
| 39 | PO_ACK_RECVD_IND | PO Acknowledgement Received Indicator | Varchar2 | 1 | Hardcoded as N at destination. |
| 40 | INCLUDE_ON_ORDER_IND | Indicates if the order should be included in on-order calculations. | Varchar2 | 1 | Hardcoded as Y at destination. |
| 41 | VENDOR_ORDER_NO | Vendor Order Indicator | Varchar2 | 15 | NULL |
| 42 | EXCHANGE_RATE | The rate of exchange for the PO used between the order and primary currencies. | Number | (20,10) | Populated as Exchange rate for the primary currency and exchange type P. |
| 43 | FACTORY | Factory | Varchar2 | 10 | NULL |
| 44 | AGENT | Agent | Varchar2 | 10 | NULL |
| 45 | DISCHARGE_PORT | Discharge Port | Varchar2 | 5 | NULL |
| 46 | LADING_PORT | Landing Port | Varchar2 | 5 | NULL |
| 47 | BILL_TO_ID | Location to be billed | Varchar2 | 5 | Populated as BILL_TO_LOC from SYSTEM_OPTIONS table. |
| 48 | FREIGHT_CONTRACT_NO | Freight Contract Number | Varchar2 | 10 | NULL |
| 49 | PO_TYPE | PO Type | Varchar2 | 4 | NULL |
| 50 | PRE_MARK_IND | Pre Mark Indicator | Varchar2 | 1 | Hardcoded as N at destination. |
| 51 | CURRENCY_CODE | Currency Code of the order | Varchar2 | 3 | Populated as CURRENCY_CODE of the primary supplier from SUPS table. |
| 52 | REJECT_CODE | Rejection Code | Varchar2 | 6 | NULL |
| 53 | CONTRACT_NO | Contract Number | Number | (6,0) | NULL |
| 54 | LAST_SENT_REV_NO | Last Sent Review Number | Number | (6,0) | NULL. |
| 55 | SPLIT_REF_ORDNO | Split Order Reference Number | Number | (8,0) | NULL. |
| 56 | PICKUP_LOC | Pickup Location | Varchar2 | 45 | NULL. |
| 57 | PICKUP_NO | Pickup Number | Varchar2 | 25 | NULL |
| 58 | PICKUP_DATE | Pickup Date | Date | If NOT_BEFORE_DATE is not null then NOT_BEFORE_DATE else VDATE. | |
| 59 | APP_DATETIME | Approved Date and Time | Date | NULL | |
| 60 | COMMENT_DESC | Comments | Varchar2 | 250 | NULL |
This section addresses the RMS Subscriber mappings from the XORDER detail message, which contains Purchase Order line item detail. The detail information contained in the message is mapped to two RMS database tables: the Order SKU (ORDSKU) and Order Location (ORDLOC) tables.
Data Element Details
| Data Type | Data Element Name | Data Description |
|---|---|---|
| RMS Subscriber Mapping | Create/Modify Order Detail | Contains Purchase Order Line Item details |
Extracting Program Details
| Program Type | Program Name | Schema File | Program Frequency |
|---|---|---|---|
| RIB Subscriber Adapter | RMS Subscriber Adapter | not applicable | Near Real Time |
Data Source and Target Details
| Data Source Details | Target Data Details | ||
|---|---|---|---|
| Data Origin System | AIP Online | Target Object Type | RMS Database |
| Source Tables/Files | STORE_ORDER, STORE, SUPPLIER, PO_MFQUEUE, COMMODITY_MAPPING, NON_CONTENTS_ORDER, STOCKING_POINT | Target Object Name | ORDSKU and ORDLOC Tables |
| Target Load Type | not applicable | ||
Field Level Mapping - Source for Order SKU (ORDSKU) Table
The following table shows source data mapped to the Order SKU (ORDSKU) table.
| # |
Source Table | Source Table Column | Source Field Description | Data Type | Field Length |
|---|---|---|---|---|---|
| 1 | PO_MFQUEUE | ORDER_NUMBER | Order Number | Number | (10,0) |
| 2 | COMMODITY_MAPPING | RMS_SKU_NUMBER | RMS SKU | Varchar2 | 25 |
| 3 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 4 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 5 | SUPPLIER | SUPPLIER_CODE | Supplier Code | Varchar2 | 20 |
| 6 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 7 | COMMODITY_MAPPING | RMS_ORDER_MULTIPLE | RMS Order Multiple | Number | 8 |
| 8 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 9 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 10 | not applicable | not applicable | not applicable | not applicable | not applicable |
Field Level Mapping - Target Order SKU (ORDSKU) Table
The following table lists target attributes for the source data being mapped to the Order SKU table (ORDSKU).
| # |
Target Data Field Name | Target Field Description | Field Data Type | Field Length | Condition/Format |
|---|---|---|---|---|---|
| 1 | ORDER_NO | The unique identifier for the order. | Number | (8,0) | not applicable |
| 2 | ITEM | An approved, transaction level item. | Varchar2 | 25 | |
| 3 | REF_ITEM | The ID of a reference item that can be used instead of using the item field. | Varchar2 | 25 | Hardcoded as NULL at Source. |
| 4 | ORIGIN_COUNTRY_ID | The identifier of the country from which the item is being sourced. | Varchar2 | 3 | Populated as ORIGIN_COUNTRY_ID of the primary supplier and item combination from ITEM_SUPP_COUNTRY table. |
| 5 | EARLISET_SHIP_DATE | Earliest Shipment Date | Date | Populated as EALISET_SHIP_DATE of the header row from ORDHEAD table. | |
| 6 | LATEST_SHIP_DATE | Latest Shipment Date | Date | Populated as LATEST_SHIP_DATE of the header row from ORDHEAD table. | |
| 7 | SUPP_PACK_SIZE | The supplier pack size for the item on the order. | Number | (12,4) | NULL |
| 8 | NON_SCALE_IND | Non-Scale Indicator | Varchar2 | 1 | Hardcoded as Y at destination. |
| 9 | PICKUP_LOC | Pickup Location | Varchar2 | 45 | NULL |
| 10 | PICKUP_NO | Pickup Number | Varchar2 | 25 | NULL |
Field Level Mapping - Source for Order Location (ORDLOC) Table
The following table lists source data mapped to the Order Location (ORDLOC) table:
| # |
Source Table | Source Table Column | Source Field Description | Data Type | Field Length |
|---|---|---|---|---|---|
| 1 | PO_MFQUEUE | ORDER_NUMBER | Order Number | Number | (10,0) |
| 2 | COMMODITY_MAPPING | RMS_SKU_NUMBER | RMS SKU | Varchar2 | 25 |
| 3 | STORE STOCKING_POINT | STORE_CODE STOCKING_POINT_NUMBER | Store Code Stocking Point Number | Varchar2 | 20 |
| 4 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 5 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 6 | STORE_ORDER NON_CONTENTS_ORDER COMMODITY_MAPPING | CASE_VOLUME QUANTITY PACK_SIZE | Case Volume Quantity Pack Size | Number | 8 |
| 7 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 8 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 9 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 10 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 11 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 12 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 13 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 14 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 15 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 16 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 17 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 18 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 19 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 20 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 21 | not applicable | not applicable | not applicable | not applicable | not applicable |
Field Level Mapping - Target Order Location (ORDLOC) Table
The following table lists source data that is mapped to the Order Location table (ORDLOC):
| # |
Target Data Field Name | Target Field Description | Field Data Type | Field Length | Condition/Format |
|---|---|---|---|---|---|
| 1 | ORDER_NO | The unique identifier for the order. | Number | (8,0) | not applicable |
| 2 | ITEM | An approved, transaction level item. | Varchar2 | 25 | |
| 3 | LOCATION | An active store or warehouse. If multichannel is on, and a warehouse is receiving orders, a virtual warehouse is expected. | Number | (10,0) | A substring is used to drop the S prefix that is appended to all RMS store numbers and to drop the W prefix that is appended to all RMS warehouse numbers at Source. |
| 4 | LOC_TYPE | The location type of the location. | Varchar2 | 1 | S indicates the destination location is a store at Source.
W indicates the destination location is a warehouse at Source. |
| 5 | UNIT_RETAIL | Unit Retail price for item and location combination. | Number | (20,4) | Calculated at destination as for non-sellable pack item, build the unit_retail based on component items unit_retail and for non-pack item or sellable pack item, get the unit_retail from item_zone_price. |
| 6 | QTY_ORDERED | The quantity ordered of item. | Number | (12,4) | Non-pack SKUs:
Formal Pack SKUs:
|
| 7 | QTY_PRESCALED | Quantity Prescaled. | Number | (12,4) | Populated same as QTY_ORDERED at destination. |
| 8 | QTY_RECEIVED | Received Quantity. | Number | (12,4) | NULL |
| 9 | LAST_RECEIVED | Last Received Quantity. | Number | (12,4) | NULL |
| 10 | LAST_ROUNDED_QTY | Last Rounded Quantity. | Number | (12,4) | NULL |
| 11 | LAST_GRP _ROUNDED_QTY | Last GRP Rounded Quantity. | Number | (12,4) | NULL |
| 12 | QTY_CANCELLED | Quantity Cancelled. | Number | (12,4) | NULL |
| 13 | CANCEL_CODE | Cancellation Code. | Varchar2 | 1 | NULL |
| 14 | CANCEL_DATE | Cancellation Date. | Date | NULL | |
| 15 | CANCEL_ID | User ID Cancelled. | Varchar2 | 30 | NULL |
| 16 | ORIGINAL_REPL_QTY | Original Replenishment Quantity. | Number | (12,4) | NULL |
| 17 | UNIT_COST | The cost of the item from the supplier in the order's currency. | Number | (20,4) | Populated from ITEM_SUPP_COUNTRY_LOC or ITEM_SUPP_COUNTRY for the combination item/supplier/country/ loc. |
| 18 | UNIT_COST_INIT | Initial Unit Cost. | Number | (20,4) | NULL |
| 19 | COST_SOURCE | Varchar2 | 4 | Hardcoded as NORM at destination. | |
| 20 | NON_SCALE_IND | Varchar2 | 1 | Hardcoded as Y at destination. | |
| 21 | TSF_PO_LINK_NO | Number | (10,0) | NULL |
Data Element Details
| Data Type | Data Element Name | Data Description |
|---|---|---|
| RMS Subscriber Mapping | Create Transfer Header | Contains Transfer header details. |
Extracting Program Details
| Program Type | Program Name | Schema File | Program Frequency |
|---|---|---|---|
| RIB Subscriber Adapter | RMS Subscriber Adapter | not applicable | Near Real Time |
Data Source and Target Details
| Data Source Details | Target Data Details | ||
|---|---|---|---|
| Data Origin System | AIP Online | Target Object Type | RMS Database |
| Source Tables/Files | STORE_ORDER, STORE, TSF_MFQUEUE, COMMODITY_MAPPING, NON_CONTENTS_ORDER, STOCKING_POINT | Target Object Name | TSFHEAD Table |
| Target Load Type | not applicable | ||
Field Level Mapping - Source
| # |
Source Table | Source Table Column | Source Field Description | Data Type | Field Length |
|---|---|---|---|---|---|
| 1 | TSF_MFQUEUE | TSF_NUMBER | Order Number | Number | (10,0) |
| 2 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 3 | STOCKING_POINT | STOCKING_POINT_NUMBER | not applicable | not applicable | not applicable |
| 4 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 5 | STORE STOCKING_POINT | STORE_CODE STOCKING_POINT_NUMBER | Store Code Stocking Point Number | Varchar2 | 20 |
| 6 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 7 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 8 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 9 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 10 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 11 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 12 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 13 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 14 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 15 | STORE_ORDER NON_CONTENTS_ORDER | DELIVERY_DATE | Delivery Date | Date | not applicable |
| 16 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 17 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 18 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 19 | not applicable | not applicable | not applicable | not applicable | not applicable |
Field Level Mapping - Target
| # |
Target Data Field Name | Target Field Description | Field Data Type | Field Length | Condition/Format |
|---|---|---|---|---|---|
| 1 | TSF_NO | Number that uniquely identifies the transfer. | Number | (10,0) | not applicable |
| 2 | FROM_LOC_TYPE | The location type of the from location. | Varchar2 | 1 | Hardcoded as W at Source. |
| 3 | FROM_LOC | The location number of the from location. | Number | (10,0) | A substring is used to drop the W prefix that is appended to all RMS warehouse numbers. |
| 4 | TO_LOC_TYPE | The location type of the to location. | Varchar2 | 1 | Hardcoded as S that indicates the destination location is a store.
W indicates the destination location is a warehouse. |
| 5 | TO_LOC | The location number of the to location. | Number | (10,0) | A substring is used to drop the S prefix that is appended to all RMS store numbers and to drop the W prefix that is appended to all RMS warehouse numbers. |
| 6 | DEPT | The department number associated with the transfer. | Number | (4,0) | Hardcoded as NULL at Source. |
| 7 | TSF_TYPE | A code indicating the type of transfer (for example: store requisition, book transfer). | Varchar2 | 6 | Hardcoded as AIP at Source. |
| 8 | STATUS | A code indicating the status of the transfer (for example: approved, closed). | Varchar2 | 1 | The transfer will be created in Approved status so hardcoded as A at Source. |
| 9 | FREIGHT_CODE | A code indicating the freight status of the transfer (for example, normal, expedite). | Varchar2 | 1 | Hardcoded as N at destination. |
| 10 | ROUTING_CODE | If the freight status is expedite, this is a code indicating more detailed freight info. | Varchar2 | 1 | Hardcoded as NULL. |
| 11 | CREATE_DATE | Transfer Creation Date | Date | Hardcoded as today's Vdate. | |
| 12 | CREATE_ID | User who created the transfer. | Varchar2 | 30 | Hardcoded as current logged in User. |
| 13 | APPROVAL_DATE | Transfer Approval Date | Date | Hardcoded as today's Vdate. | |
| 14 | APPROVAL_ID | User who approved the transfer. | Varchar2 | 30 | Hardcoded as current logged in User. |
| 15 | DELIVERY_DATE | The earliest date the transfer can be delivered. | Date | not applicable | |
| 16 | CLOSE_DATE | Date | NULL | ||
| 17 | EXT_REF_NO | Varchar2 | 14 | NULL | |
| 18 | REPL_TSF_APPROVE_IND | Varchar22 | 1 | Hardcoded as N at destination. | |
| 19 | COMMENT_DESC | Comments associated with the transfer. | Varchar2 | 300 | NULL |
Data Element Details
| Data Type | Data Element Name | Data Description |
|---|---|---|
| RMS Subscriber Mapping | Create Transfer Detail | Contains Transfer detail line of items. |
Extracting Program Details
| Program Type | Program Name | Schema File | Program Frequency |
|---|---|---|---|
| RIB Subscriber Adapter | RIB Subscriber Adapter | not applicable | Near Real Time |
Data Source and Target Details
| Data Source Details | Target Data Details | ||
|---|---|---|---|
| Data Origin System | AIP Online | Target Object Type | RMS Database |
| Source Tables/Files | STORE_ORDER, STORE, TSF_MFQUEUE, COMMODITY_MAPPING, NON_CONTENTS_ORDER, STOCKING_POINT | Target Object Name | TSFDETAIL Table |
| Target Load Type | not applicable | ||
Field Level Mapping - Source
| # |
Source Table | Source Table Column | Source Field Description | Data Type | Field Length |
|---|---|---|---|---|---|
| 1 | TSF_MFQUEUE | TSF_NUMBER | Order Number | Number | (10,0) |
| 2 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 3 | COMMODITY_MAPPING | RMS_SKU_NUMBER | RMS SKU | Varchar2 | 25 |
| 4 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 5 | STORE_ORDER NON_CONTENTS_ORDER
COMMODITY_MAPPING |
CASE_VOLUME QUANTITY PACK_SIZE | Case Volume Quantity Pack Size | Number | 8 |
| 6 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 7 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 8 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 9 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 10 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 11 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 12 | COMMODITY_MAPPING | RMS_ORDER_MULTIPLE | RMS Order Multiple | Number | 8 |
| 13 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 14 | not applicable | not applicable | not applicable | not applicable | not applicable |
| 15 | not applicable | not applicable | not applicable | not applicable | not applicable |
Field Level Mapping - Target
| # |
Target Data Field Name | Target Field Description | Field Data Type | Field Length | Condition/Format |
|---|---|---|---|---|---|
| 1 | TSF_NO | Number that uniquely identifies the transfer. | Varchar2 | (10,0) | The transfer number from header row. |
| 2 | TSF_SEQ_NO | Transfer Line Item Number | Number | (8,0) | Transfer line item number under the current header row. |
| 3 | ITEM | The unique identifier of the item being transferred. | Number | 25 | not applicable |
| 4 | INV_STATUS | A code indicating the inventory status for this transfer detail; valid values are found on the inv_status_types table. | Number | (2,0) | Hardcoded as NULL. |
| 5 | TSF_QTY | The total quantity of the item reserved for this transfer at the from location. | Number | (12,4) | Non-pack SKUs·:
Formal Pack SKUs:
|
| 6 | FILL_QTY | Fill Quantity | Varchar2 | (12,4) | NULL |
| 7 | SHIP_QTY | Shipped Quantity | Number | (12,4) | NULL |
| 8 | RECEIVED_QTY | Received Quantity | Number | (12,4) | NULL |
| 9 | DISTRO_QTY | Distributed Quantity | Number | (12,4) | NULL |
| 10 | SELECTED_QTY | Selected Quantity | Number | (12,4) | NULL |
| 11 | CANCELLED_QTY | Cancelled Quantity | Varchar2 | (12,4) | NULL |
| 12 | SUPP_PACK_SIZE | Supplier Pack Size | Number | (12,4) | The AIP SKU-pack size is mapped to the RMS Item and Order Multiple. |
| 13 | TSF_PO_LINK_NO | Transfer to PO Link number | Number | (10,0) | NULL |
| 14 | MBR_PROCESSED_IND | Member Processed Indicator | Number | 1 | NULL |
| 15 | PUBLISH_IND | Publishing Indicator | Number | 1 | Hardcoded as N. |
