| Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs 16.0.024 E89599-02 |
|
 Previous |
 Next |
| Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs 16.0.024 E89599-02 |
|
Previous |
Next |
This chapter contains information about the processes that enables out of the box integration with Oracle Retail Advanced Inventory Planning (AIP).
AIP is a replenishment system. AIP uses foundation and inventory information mastered in RMS to suggest purchase orders. These suggested purchase orders are sent to RMS to be actualized.
Extracts from RMS are performed via batch ReTL (Retail Extract Transform) scripts described in this chapter. Suggested purchase orders are publiched to the RIB by AIP; RMS subscribes to these purchase order RIB messages. For more information about the PO Subscription, see the Oracle Retail Merchandising Foundation Cloud Service Operations Guide, Volume 2 - Message Publication and Subscription Design
According to RRA, there are two RPAS programs that should be run for AIP integration, they are:
ftmednld.pc
rmse_rpas_dailt_sales.ksh
For more information about the rpas programs, see the Integration with Oracle Retail Planning chapter.
RMS and AIP integration stands independent of additional RPAS integration for other RPAS based solutions.
AIP integration jobs only need to be scheduled if a client integrates with AIP.
AIP requires both foundation and transaction data from RMS. In most cases, foundation data extracts can be run ad hoc at any time.
Transaction and inventory extracts should be scheduled after main RMS inventory processing.
Scheduling and dependency information for each program can be found in the program details section of this chapter.
Table 26-1 Program Summary
| Program | Description |
|---|---|
|
rmse_aip_batch.ksh |
Optional Wrapper Script to run all AIP Extracts |
|
pre_rmse_aip.ksh |
Extract of RMS System level settings for AIP |
|
rmse_aip_merchhier.ksh |
Extract of Merchandise Hierarchy for AIP |
|
rmse_aip_orghier.ksh |
Extract of Organization Hierarchy for AIP |
|
rmse_aip_item_master.ksh |
Extract of Items for AIP |
|
rmse_aip_store.ksh |
Extract of Stores for AIP |
|
rmse_aip_wh.ksh |
Extract of Warehouses for AIP |
|
rmse_aip_substitute_items.ksh |
Extract of Substitute Items for AIP |
|
rmse_aip_suppliers.ksh |
Extract of Suppliers for AIP |
|
rmse_aip_alloc_in_well.ksh |
Extract of Allocations in the Well Quantities for AIP |
|
rmse_aip_cl_po.ksh |
Extract of AIP Generated POs, Allocations and Transfers Cancelled or Closed in RMS for AIP |
|
rmse_aip_future_delivery_alloc.ksh |
Extract of Allocation Quantities for Future Delivery for AIP |
|
rmse_aip_future_delivery_order.ksh |
Extract of Purchase Order Quantities for Future Delivery for AIP |
|
rmse_aip_future_delivery_tsf.ksh |
Extract On Order and In Transit Transfer Quantities for Future Delivery for AIP |
|
rmse_aip_future_item_loc_traits.ksh |
Extract of Shelf Life on Receipt Location Trait for AIP |
|
rmse_aip_item_retail.ksh |
Extract of Forecasted Items for AIP |
|
rmse_aip_item_sale.ksh |
Extract of Scheduled Item Maintenance On/Off Sale Information for AIP |
|
rmse_aip_item_supp_country.ksh |
Extract of Order Multiples by Item/Supplier/Origin Country for AIP |
|
rmse_aip_rec_qty.ksh |
Extract of Received PO, Allocation and Transfer Quantities for AIP |
|
rmse_aip_store_cur_inventory.ksh |
Extract of Store Current Inventory data for AIP |
|
rmse_aip_tsf_in_well.ksh |
Extract of Transfer in the Well Quantities to AIP |
|
rmse_aip_wh_cur_inventory.ksh |
Extract of Warehouse Current Inventory for AIP |
| Module Name | rmse_aip_batch.ksh |
| Description | Optional Wrapper Script to run all AIP Extracts |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | NA |
| Runtime Parameters | RMS118 |
The rmse_aip_batch.ksh script is an optional wrapper that runs all extracts from RMS for AIP.
This wrapper script assumes default input parameters for some jobs. Care should be taken to ensure that if a client uses this wrapper script, those default input parameters are either correct or updated to the correct value for the implementation.
This wrapper script also assumes that all extracts from RMS should be run. There are cases (detailed in the extract script specific documentation) where this might not be the case. Care should be taken to ensure that if a client uses this wrapper script, it is updated as needed to reflect the extracts appropriate to the implementation.
This wrapper script also assumes that all extracts should be run sequentially at a single point in the RMS batch schedule. This may or may not be the best assumption for a given implementation.
If a client chooses not to use this wrapper script, he can schedule most AIP integration jobs at ad-hoc at any time in the batch schedule. Only a few jobs have specific dependencies. Most data can be sent to AIP early in the cycle. Only a few jobs will have to wait until later in the batch schedule. Some clients find are able to start the AIP processing earlier in the schedule if they do not use this wrapper script.
If a client uses this wrapper script, no extraction for AIP will be performed until the most restrictive dependencies allow it. This may mean a delay in getting any information to AIP so its processing can begin.
The wrapper script is convenient, but may not be the right choice for all implementations.
The scripts included in this wrapper are:
pre_rmse_aip.ksh
rmse_aip_item_master.ksh
rmse_aip_item_supp_country.ksh
rmse_aip_merchhier.ksh
rmse_aip_orghier.ksh
rmse_aip_store.ksh
rmse_aip_suppliers.ksh
rmse_aip_wh.ksh
rmse_aip_item_retail.ksh
rmse_aip_item_loc_traits.ksh
rmse_aip_substitute_items.ksh
rmse_aip_store_cur_inventory.ksh
rmse_aip_wh_cur_inventory.ksh
rmse_aip_future_delivery_alloc.ksh
rmse_aip_alloc_in_well.ksh
rmse_aip_future_delivery_order.ksh
rmse_aip_future_delivery_tsf.ksh
rmse_aip_tsf_in_well.ksh
rmse_aip_item_sale.ksh
rmse_aip_cl_po.ksh
rmse_aip_rec_qty.ksh
Table 26-2 Scheduling Constraints
| Schedule Information | Description |
|---|---|
|
Frequency |
Daily |
|
Scheduling Considerations |
Optional - If a client uses this wrapper script, no extraction for AIP will be performed until the most restrictive sub script dependencies allow it This may mean a delay in getting any information to AIP so its processing can begin If this script is NOT used, it is possible to get some data to AIP earlier in the total batch schedule. This may have an impact on when AIP is able to begin AIP batch processing |
|
Pre-Processing |
NA |
|
Post-Processing |
NA |
|
Threading Scheme |
NA |
| Module Name | pre_rmse_aip.ksh |
| Description | Extract of RMS System level settings for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS159 |
| Runtime Parameters | NA |
This script extracts assorted RMS system level settings to files. This module produces 14 single value output files. These files can be loaded into AIP.Most RETL programs use schema files to describe the definition of the output files. As the files produced by this module are incredibly simple, no schema files are used.
| Integration Type | Download from RMS |
| File Name | consolidation_code.txt |
| Integration Contract | IntCon000180 |
Field Name: CONSOLIDATION_CODE
Field Type : Varchar2(1)
Required: Yes
| Integration Type | Download from RMS |
| File Name | vat_ind.txt |
| Integration Contract | IntCon000181 |
Field Name: VAT_IND
Field Type : Varchar2(6)
Required: Yes
| Integration Type | Download from RMS |
| File Name | stkldgr_vat_incl_retl_ind.txt |
| Integration Contract | IntCon000182 |
Field Name: STKLDGR_VAT_INCL_RETL_IND
Field Type : Varchar2(1)
Required: Yes
| Integration Type | Download from RMS |
| File Name | multi_currency_ind.txt |
| Integration Contract | IntCon000183 |
Field Name: MULTI_CURRENCY_IND
Field Type : Varchar2(1)
Required: Yes
| Integration Type | Download from RMS |
| File Name | prime_currency_code.txt |
| Integration Contract | IntCon000184 |
Field Name: CURRENCY_CODE
Field Type : Varchar2(3)
Required: Yes
| Integration Type | Download from RMS |
| File Name | class_level_vat_ind.txt |
| Integration Contract | IntCon000185 |
Field Name: CLASS_LEVEL_VAT_IND
Field Type : Varchar2(1)
Required: Yes
| Integration Type | Download from RMS |
| File Name | domain_level.txt |
| Integration Contract | IntCon000186 |
Field Name: DOMAIN_LEVEL
Field Type : Varchar2(1)
Required: Yes
| Integration Type | Download from RMS |
| File Name | vdate.txt |
| Integration Contract | IntCon000187 |
Field Name: VDATE
Field Type : Date
Required: Yes
| Integration Type | Download from RMS |
| File Name | next_vdate.txt |
| Integration Contract | IntCon000188 |
Field Name: NEXT_VDATE
Field Type : Date
Required: Yes
| Integration Type | Download from RMS |
| File Name | last_eom_date.txt |
| Integration Contract | IntCon000189 |
Field Name: LAST_EOM_DATE
Field Type : Date
Required: Yes
| Integration Type | Download from RMS |
| File Name | curr_bom_date.txt |
| Integration Contract | IntCon000190 |
Field Name: CURR_BOM_DATE
Field Type : Date
Required: Yes
| Integration Type | Download from RMS |
| File Name | max_backpost_days.txt |
| Integration Contract | IntCon000191 |
Field Name: MAX_BACKPOST_DAYS
Field Type : Date
Required: Yes
| Integration Type | Download from RMS |
| File Name | last_extr_closed_pot_date.txt |
| Integration Contract | IntCon000192 |
Field Name: LAST_EXTR_CLOSED_POT_DATE
Field Type : Date
Required: Yes
| Integration Type | Download from RMS |
| File Name | last_extr_received_pot_date.txt |
| Integration Contract | IntCon000193 |
Field Name: LAST_EXTR_RECEIVED_POT_DATE
Field Type : Date
Required: Yes
| Integration Type | Download from RMS |
| File Name | last_extr_received_pot_date.txt |
| Integration Contract | IntCon000194 |
Field Name: LAST_EXTR_RECEIVED_POT_DATE
Field Type : Date
Required: Yes
| Integration Type | Download from RMS |
| File Name | prime_exchng_rate.txt |
| Integration Contract | IntCon000195 |
Field Name: PRIME_EXCHNG_RATE
Field Type : Number(20,10)
Required: Yes
| Module Name | Rmse_aip_merchhier.ksh |
| Description | Extract of Merchandise Hierarchy for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS32 |
| Runtime Parameters | NA |
This script extracts RMS merchandise hierarchy information for integration with Oracle Retail Advanced Inventory Planning (AIP).
| Integration Type | Download from RMS |
| File Name | Determined by runtime parameter |
| Integration Contract | IntCon000077
rmse_aip_merchhier.schema |
Table 26-7 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
SUBCLASS |
Integer(5) |
Yes |
Subclass.subclass |
|
SUB_NAME |
Char(20) |
Yes |
Subclass.sub_name |
|
CLASS |
Integer(5) |
Yes |
Subclass.class |
|
CLASS_NAME |
Char(20) |
Yes |
Class.calss_name |
|
DEPT |
Integer(5) |
Yes |
Class.dept |
|
DEPT_NAME |
Char(20) |
Yes |
Deps.dept_name |
|
GROUP_NO |
Integer(5) |
Yes |
Deps.Group_no |
|
GROUP_NAME |
Char(20) |
Yes |
Groups.group_name |
|
DIVISION |
Integer(5) |
Yes |
Groups.division |
|
DIV_NAME |
Char(20) |
Yes |
Division.div_name |
|
COMPANY |
Integer(5) |
Yes |
Comphead.company |
|
CO_NAME |
Char(20) |
Yes |
Comphead.co_name |
|
PURCHASE_TYPE |
Integer(1) |
Yes |
Deps.purchase_type |
| Module Name | rmse_aip_orghier.ksh |
| Description | Extract of Organization Hierarchy for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS26 |
| Runtime Parameters | NA |
This script extracts from RMS organizational hierarchy information for integration with Oracle Retail Advanced Inventory Planning (AIP).
| Integration Type | Download from RMS |
| File Name | rmse_aip_orghier.dat |
| Integration Contract | IntCon000078
rmse_aip_orghier.schema |
Table 26-10 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
DISTRICT |
Integer(11) |
No |
District.district |
|
DISTRICT_NAME |
Char(20) |
No |
District.district_name |
|
REGION |
Integer(11) |
No |
Region.region |
|
REGION_NAME |
Char(20) |
No |
Region.region_name |
|
AREA |
Integer(11) |
No |
Area.area |
|
AREA_NAME |
Char(20) |
No |
Area.area_name |
|
CHAIN |
Integer(11) |
Yes |
Chain.chain |
|
CHAIN_NAME |
Char(20) |
Yes |
Chain.chain_name |
|
COMPANY |
Integer(5) |
Yes |
Comphead.company |
|
CO_NAME |
Char(20) |
Yes |
Comphead.co_name |
| Module Name | rmse_aip_item_master.ksh |
| Description | Extract of Items for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS30 |
| Runtime Parameters | NA |
This script extracts RMS item information for integration with Oracle Retail Advanced Inventory Planning (AIP). Two output files are produced by this extract. One contains approved transaction-level items while the other contains purged items from the daily_purge table.
|
Note: Items are generally not deleted from RMS in a one day process (records will exist on the DAILY_PURGE table for some time). This assumption means that it is reasonable for the dlyprg program (which deleted from DAILY_PURGE) to run before this extract. |
| Integration Type | Download from RMS |
| File Name | rmse_aip_item_master.dat |
| Integration Contract | IntCon000073
rmse_aip_item_master.schema |
Table 26-13 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
ITEM |
Char(25) |
Yes |
Item_master.item |
|
ITEM_DESC |
Char(250) |
Yes |
Item_master.item_desc |
|
ITEM_PARENT |
Char(25) |
No |
Item_master.item_parent |
|
ITEM_GRANDPARENT |
Char(25) |
No |
Item_master.item_grandparent |
|
AIP_SKU |
Char(25) |
Yes |
V_packsku_qty.item or Item_master.item |
|
SUBCLASS |
Integer(5) |
Yes |
Item_master.subclass |
|
CLASS |
Integer(5) |
Yes |
Item_master.class |
|
DEPT |
Integer(5) |
Yes |
Item_master.dept |
|
FORECAST_IND |
Char(1) |
Yes |
Item_master.forecast_ind |
|
SUPPLIER |
Integer(11) |
Yes |
Item_supplier.supplier |
|
PRIMARY_SUPP_IND |
Char(1) |
Yes |
Item_supplier.primary_supp_ind |
|
STANDARD_UOM |
Char(4) |
Yes |
Item_master.standard_uom |
|
STANDARD_UOM_DESCRIPTION |
Char(120) |
Yes |
Uom_class.uom_desc |
|
SKU_TYPE |
Char(6) |
No |
Item_master.handling_temp or 0 |
|
SKU_TYPE_DESCRIPTION |
Char(40) |
No |
Code_detail.code_desc (for code_type ’HTMP') |
|
PACK_QUANTITY |
Char(6) |
No |
V_packsku_qty.qty or 0 |
|
PACK_IND |
Char(1) |
Yes |
Item_master.pack_ind |
|
SIMPLE_PACK_IND |
Char(1) |
Yes |
Item_master.simple_pack_ind |
|
ITEM_LEVEL |
Integer(1) |
Yes |
Item_master.item_level |
|
TRAN_LEVEL |
Integer(1) |
Yes |
Item_master.tran_level |
|
RETAIL_LABEL_TYPE |
Char(6) |
No |
Item_master.retail_label_type |
|
CATCH_WEIGHT_IND |
Char(1) |
Yes |
Item_master.catch_weight_ind |
|
SELLABLE_IND |
Char(1) |
Yes |
Item_master.sellable_ind |
|
ORDERABLE_IND |
Char(1) |
Yes |
Item_master.orderable_ind |
|
DEPOSIT_ITEM_TYPE |
Char(6) |
No |
Item_master.deposit_item_type |
|
ITEM |
Char(25) |
Yes |
Item_master.item |
| Module Name | Rmse_aip_store.ksh |
| Description | Extract of Stores for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS40 |
| Runtime Parameters | NA |
This script extracts store information for integration with Oracle Retail Advanced Inventory Planning (AIP).
| Integration Type | Download from RMS |
| File Name | Determined by runtime parameter |
| Integration Contract | IntCon000080
rmse_aip_store.schema |
Table 26-17 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
STORE |
Integer(11) |
Yes |
Store.store |
|
STORE_NAME |
Char(20) |
Yes |
Store.store_name |
|
DISTRICT |
Integer(11) |
Yes |
Store.district |
|
STORE_CLOSE_DATE |
Date |
No |
Store.store_close_date |
|
STORE_OPEN_DATE |
Date |
Yes |
Store.store_open_date |
|
STORE_CLASS |
Char(1) |
Yes |
Store.store_class |
|
STORE_CLASS_DESCRIPTION |
Char(40) |
Yes |
Code_detail.code_desc |
|
STORE_FORMAT |
Integer(5) |
No |
Store.store_format |
|
FORMAT_NAME |
Char(20) |
No |
Store_format.format_name |
|
STOCKHOLDING_IND |
Char(1) |
Yes |
Store.stockholding_ind |
|
REMERCH_IND |
Char(1) |
Yes |
Store.remerch_ind |
|
CLOSING_STORE_IND |
Char(1) |
Yes |
'N' if Store.store_close_date is empty, else 'Y' |
| Module Name | rmse_aip_wh.ksh |
| Description | Extract of Warehouses for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | RMS35 |
| Runtime Parameters | NA |
This script extracts from RMS warehouse information for integration with Oracle Retail Advanced Inventory Planning (AIP).
The script produces three extract files:
rmse_aip_wh.dat
rmse_aip_wh.txt
rmse_aip_wh_type.txt
Only stock holding warehouses are extracted to the rmse_aip_wh.txt and rmse_aip_wh_type.txt files
| Integration Type | Download from RMS |
| File Name | rmse_aip_wh.dat |
| Integration Contract | IntCon000085
rmse_aip_wh_dat.schema |
| Integration | Download from RMS |
| File Name | rmse_aip_wh.txt |
| Integration Contract | IntCon000137
rmse_aip_wh_dat.schema |
| Module Name | rmse_aip_substitute_item.ksh |
| Description | Extract of Substitute Items for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS38 |
| Runtime Parameters | NA |
This script extracts substitute item information from RMS for integration with Oracle Retail Advanced Inventory Planning (AIP).
| Integration | Download from RMS |
| File Name | rmse_aip_substitute_items.dat |
| Integration Contract | IntCon000082
rmse_aip_substitute_items.schema |
Table 26-25 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
ITEM |
Char(25) |
Yes |
Sub_items_detail.item |
|
LOCATION |
Integer(10) |
Yes |
Sub_items_detail.location |
|
SUB_ITEM |
Char(25) |
Yes |
Sub_items_detail.sub_item |
|
LOC_TYPE |
Char(1) |
Yes |
Sub_items_detail.loc_type |
|
START_DATE |
Date |
No |
Sub_items_detail.start_date |
|
END_DATE |
Date |
No |
Sub_items_detail.end_date |
|
SUBSTITUTE_REASON |
Char(1) |
No |
Sub_items_detail.substitute_reason |
| Module Name | rmse_aip_suppliers.ksh |
| Description | Extract of Suppliers for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS37 |
| Runtime Parameters | NA |
This script extracts supplier/supplier site information for integration with Oracle Retail Advanced Inventory Planning (AIP).
The script produces three extract files:
rmse_aip_suppliers.dat
splr.txt
dmx_dirspl.txt
Splr.txt and dmx_dirspl.txt only contain active suppliers (sups.sup_status = 'A').
| Integration Type | Download from RMS |
| File Name | rmse_aip_suppliers.dat |
| Integration Contract | IntCon000083
rmse_aip_suppliers.schema |
| Integration Type | Download from RMS |
| File Name | splr.txt |
| Integration Contract | IntCon000175
rmse_aip_suppliers.schema |
| Module Name | rmse_aip_alloc_in_well.ksh |
| Description | Extract of Allocations in the Well Quantities for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS20 |
| Runtime Parameters | NA |
This script extracts RMS "in the well" allocation quantities for integration with Oracle Retail Advanced Inventory Planning (AIP). In the well pertains to inventory that has been reserved by allocations in approved or reserved status. The expected release date is also included in the extract.
| Integration Type | Download from RMS |
| File Name | rmse_aip_alloc_in_well.dat |
| Integration Contract | IntCon000066
rmse_aip_alloc_in_well.schema |
Table 26-33 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
DAY |
Char(9) |
Yes |
alloc_header.release_date |
|
LOC |
Integer(20) |
Yes |
Alloc_header.wh |
|
ITEM |
Char(20) |
Yes |
Formal Case Type: If simple pack then and alloc_detail.to_loc_type = 'S' then this would be the component of the pack in v_packsku_qty else item_master.item. Informal Case Type: Item_master.item |
|
ORDER_MULTIPLE |
Char(6) |
Yes |
Formal Case Type: If simple pack and alloc_detail.to_loc_type = 'W' then this would be v_packsku_qty.qty of the pack component else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
ALLOC_RESERVE_QTY |
Char(8) |
Yes |
Formal Case Type: Alloc_detail.qty_allocated - alloc_detail.qty_received. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Alloc_detail.qty_allocated - alloc_detail.qty_received expressed in multiples of the primary case size. The remainder is expressed in Standard UOM. |
|
ORDER_NO |
Integer(12) |
No |
Order number |
The reject file rmse_aip_alloc_in_well_reject_ord_mult.txt is in pipe delimited (|) format
Table 26-34 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
DAY |
Char(9) |
Yes |
alloc_header.release_date |
|
LOC |
Integer(20) |
Yes |
Alloc_header.wh |
|
ITEM |
Char(20) |
Yes |
Formal Case Type: If simple pack then and alloc_detail.to_loc_type = 'S' then this would be the component of the pack in v_packsku_qty else item_master.item. Informal Case Type: Item_master.item |
|
ORDER_MULTIPLE |
Char(6) |
Yes |
Formal Case Type: If simple pack and alloc_detail.to_loc_type = 'W' then this would be v_packsku_qty.qty of the pack component else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
ALLOC_RESERVE_QTY |
Char(8) |
Yes |
Formal Case Type: Alloc_detail.qty_allocated - alloc_detail.qty_received. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Alloc_detail.qty_allocated - alloc_detail.qty_received expressed in multiples of the primary case size. The remainder is expressed in Standard UOM. |
|
ORDER_NO |
Integer(12) |
No |
Order number |
| Module Name | rmse_aip_cl_po.ksh |
| Description | Extract of AIP Generated POs, Allocations and Transfers Cancelled or Closed in RMS for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS21 |
| Runtime Parameters | NA |
This script extracts from RMS cancelled or closed purchase orders, transfers and allocations for integration with Oracle Retail Advanced Inventory Planning (AIP). Only records that meet the following criteria below are extracted:
For Purchase Orders:
Ordhead.close_date is not NULL
Ordhead.orig_ind = 6 (external system generated)
Ordhead.close_date > Retl_extract_dates.last_extr_closed_pot_date
For Transfers:
Tsfhead.close_date is not NULL
Tsfhead.tsf_type = 'AIP' (generated by AIP)
Ordhead.close_date > Retl_extract_dates.last_extr_closed_pot_date
For Allocations:
Alloc_header.close_date is not NULL
Alloc_header.origin_ind = 'AIP'(generated by AIP)
Alloc_header.close_date> Retl_extract_dates.last_extr_closed_pot_date
Table 26-36 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
ORDHEAD |
Yes |
No |
No |
No |
|
TSFHEAD |
Yes |
No |
No |
No |
|
ALLOC_HEADER |
Yes |
No |
No |
No |
rmse_aip_cl_po.ksh calls another script rmsl_aip_update_retl_date.ksh, which updates the AIP RETL extract dates. The tables affected by this script is:
| Module Name | rmse_aip_future_delivery_alloc.ksh |
| Description | Extract of Allocation Quantities for Future Delivery for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS28 |
| Runtime Parameters | NA |
This script extracts RMS in-transit and on-order allocation quantities for future deliveryfor integration with AIP. For warehouse-inbound transactions (for example:. alloc_detail.to_loc_type = 'W'), alloc_no will be included as the transaction number in the output file. For store-inbound transactions (for example:. alloc_detail.to_loc_type = 'S'), NULL will be included as the transaction number in the output file and transaction quantity will be rolled up by item/store/day. Both standalone allocations and cross-docked allocations from a PO will be extracted, but cross-docked allocations from a PO associated with a customer order (for example:. order_type = 'CO') will NOT be extracted.
Table 26-40 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
ITEM_MASTER |
Yes |
No |
No |
No |
|
ITEM_SUPP_COUNTRY |
Yes |
No |
No |
No |
|
ITEM_SUPPLIER |
Yes |
No |
No |
No |
|
ITEM_SUPPLIER |
Yes |
No |
No |
No |
|
ALLOC_HEADER |
Yes |
No |
No |
No |
|
ALLOC_DETAIL |
Yes |
No |
No |
No |
|
V_PACKSKU_QTY |
Yes |
No |
No |
No |
|
PACKITEM |
Yes |
No |
No |
No |
|
TRANSIT_TIMES |
Yes |
No |
No |
No |
|
V_WH |
Yes |
No |
No |
No |
| Integration Type | Download from RMS |
| File Name | rmse_aip_future_delivery_alloc.dat |
| Integration Contract | IntCon000069
rmse_aip_future_delivery_alloc.schema |
Table 26-41 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
TRANSACTION_NUM |
Integer(12) |
No |
If alloc_detail.to_loc_type ='W' then value will be Alloc_header.alloc_no else null |
|
DAY |
Char(9) |
Yes |
'D'|| Alloc_header.release_date + transit_times.transit_time |
|
SUPPLIER |
Integer(20) |
No |
If there is no associated order then primary supplier on item_supplier.supplier else ordhead.supplier |
|
LOC |
Integer(20) |
Yes |
Alloc_detail.to_loc |
|
LOC_TYPE |
Char(1) |
Yes |
Alloc_detail.to_loc_type |
|
ITEM |
Char(20) |
Yes |
Formal Case Type: If simple pack then and alloc_detail.to_loc_type = 'S' then this would be the component of the pack in v_packsku_qty else item_master.item. Informal Case Type: Item_master.item |
|
ORDER_MULTIPLE |
Char (6) |
Yes |
Formal Case Type: V_packsku_qty.qty for simple pack, else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
IN_TRANSIT_ALLOC_QTY |
Char (8) |
Yes |
Formal Case Type: Alloc_detail.Qty_transferred - Alloc_detail.Qty_received. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Alloc_detail.Qty_transferred - Alloc_detail.Qty_received expressed in the primary case size. Remainder is in Standard UOM |
|
ON_ORDER_ALLOC_QTY |
Char (8) |
Yes |
Formal Case Type: Alloc_detail.Qty_allocated - Alloc_detail.Qty_transferred. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Alloc_detail.Qty_allocated - Alloc_detail.Qty_transferred expressed in the primary case size. Remainder is in Standard UOM |
The reject file rmse_aip_future_delivery_alloc_reject_ord_mult.txt is in pipe delimited (|) format.
Table 26-42 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
TRANSACTION_NUM |
Integer(12) |
No |
If alloc_detail.to_loc_type ='W' then value will be Alloc_header.alloc_no else null |
|
DAY |
Char(9) |
Yes |
'D'|| Alloc_header.release_date + transit_times.transit_time |
|
SUPPLIER |
Integer(20) |
No |
If there is no associated order then primary supplier on item_supplier.supplier else ordhead.supplier |
|
LOC |
Integer(20) |
Yes |
Alloc_detail.to_loc |
|
LOC_TYPE |
Char(1) |
Yes |
Alloc_detail.to_loc_type |
|
ITEM |
Char(20) |
Yes |
Formal Case Type: If simple pack then and alloc_detail.to_loc_type = 'S' then this would be the component of the pack in v_packsku_qty else item_master.item. Informal Case Type: Item_master.item |
|
ORDER_MULTIPLE |
Char (6) |
Yes |
Formal Case Type: V_packsku_qty.qty for simple pack, else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
IN_TRANSIT_ALLOC_QTY |
Char (8) |
Yes |
Formal Case Type: Alloc_detail.Qty_transferred - Alloc_detail.Qty_received. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Alloc_detail.Qty_transferred - Alloc_detail.Qty_received expressed in the primary case size. Remainder is in Standard UOM |
|
ON_ORDER_ALLOC_QTY |
Char (8) |
Yes |
Formal Case Type: Alloc_detail.Qty_allocated - Alloc_detail.Qty_transferred. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Alloc_detail.Qty_allocated - Alloc_detail.Qty_transferred expressed in the primary case size. Remainder is in Standard UOM |
| Module Name | rmse_aip_future_delivery_order.ksh |
| Description | Extract of Purchase Order Quantities for Future Delivery to AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS22 |
| Runtime Parameters | NA |
This script extracts RMS purchase order quantities for future delivery for integration with Oracle Retail Advanced Inventory Planning (AIP).For warehouse-inbound transactions (for example:. ordloc.to_loc_type = 'W'), order_no will be included as the transaction number in the output file. For store-inbound transactions (for example:. ordloc.to_loc_type = 'S'), NULL will be included as the transaction number in the output file and transaction quantity will be rolled up by item/store/day. Both standalone POs and cross-docked POs to a transfer or allocation will be extracted, but POs associated with a customer order (for example:. order_type = 'CO') will NOT be extracted.
| Integration Type | Download from RMS |
| File Name | rmse_aip_future_delivery_order.dat |
| Integration Contract | IntCon000070
rmse_aip_future_delivery_order.schema |
Table 26-45 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
TRANSACTION_NUM |
Integer(12) |
No |
If ordloc.loc_type ='W' then value will be ordloc.order_no else null |
|
DAY |
Char(9) |
Yes |
'D' || Ordhead.not_after_date |
|
SUPPLIER |
Integer(20) |
Yes |
Ordhead.supplier |
|
LOC |
Integer(20) |
Yes |
Ordloc.location |
|
ITEM |
Char(20) |
Yes |
Formal Case Type: If simple pack and ordloc.loc_type = 'S' then this would be the component of the pack in v_packsku_qty else item_master.item. Informal Case Type: Item_master.item |
|
ORDER_MULTIPLE |
Char(6) |
Yes |
Formal Case Type: If ordloc.loc_type = 'S' then 1 If ordloc.loc_type = 'W' and (ordloc.qty_ordered - ordloc.qty_received) >= item_supp_country.supp_pack_size and a simple pack then V_packsku_qty.qty else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
PO_QTY |
Char(8) |
Yes |
(Ordloc.qty_ordered - Ordloc.qty_received) or 0 |
|
LOC_TYPE |
Char(1) |
Yes |
Ordloc.loc_type |
The reject file rmse_aip_future_delivery_order_reject_ord_mult.txt is in pipe delimited (|) format.
Table 26-46 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
TRANSACTION_NUM |
Integer(12) |
No |
If ordloc.loc_type ='W' then value will be ordloc.order_no else null |
|
DAY |
Char(9) |
Yes |
'D' || Ordhead.not_after_date |
|
SUPPLIER |
Integer(20) |
Yes |
Ordhead.supplier |
|
LOC |
Integer(20) |
Yes |
Ordloc.location |
|
ITEM |
Char(20) |
Yes |
Formal Case Type: If simple pack and ordloc.loc_type = 'S' then this would be the component of the pack in v_packsku_qty else item_master.item. Informal Case Type: Item_master.item |
|
ORDER_MULTIPLE |
Char(6) |
Yes |
Formal Case Type: If ordloc.loc_type = 'S' then 1 If ordloc.loc_type = 'W' and (ordloc.qty_ordered - ordloc.qty_received) >= item_supp_country.supp_pack_size and a simple pack then V_packsku_qty.qty else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
PO_QTY |
Char(8) |
Yes |
(Ordloc.qty_ordered - Ordloc.qty_received) or 0 |
|
LOC_TYPE |
Char(1) |
Yes |
Ordloc.loc_type |
| Module Name | rmse_aip_future_delivery_tsf.ksh |
| Description | Extract On Order and In Transit Transfer Quantities for Future Delivery for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS29 |
| Runtime Parameters | NA |
This script extracts RMS on-order and in-transit transfer quantities for future delivery for Integration with AIP.For warehouse-inbound transactions (for example:. tsfhead.to_loc_type = 'W'), transfer number will be included as the transaction number in the output file. For store-inbound transactions (for example:. tsfhead.to_loc_type = 'S'), NULL will be included as the transaction number in the output file and transaction quantity will be rolled up by item/store/day. Transfers created by RMS's franchise ordering/returning processes will not be extracted.
Table 26-48 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
ITEM_MASTER |
Yes |
No |
No |
No |
|
ITEM_SUPP_COUNTRY |
Yes |
No |
No |
No |
|
ITEM_SUPPLIER |
Yes |
No |
No |
No |
|
TSFHEAD |
Yes |
No |
No |
No |
|
TSFDETAIL |
Yes |
No |
No |
No |
|
SHIPITEM_INV_FLOW |
Yes |
No |
No |
No |
|
V_PACKSKU_QTY |
Yes |
No |
No |
No |
|
PACKITEM |
Yes |
No |
No |
No |
|
TRANSIT_TIMES |
Yes |
No |
No |
No |
|
V_WH |
Yes |
No |
No |
No |
| Integration Type | Download from RMS |
| File Name | rmse_aip_future_delivery_tsf.dat |
| Integration Contract | IntCon000071
rmse_aip_future_delivery_tsf.schema |
Table 26-49 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
TRANSACTION_NUM |
Integer(12) |
No |
If tsfhead.to_loc_type ='W' then value will be tsfhead.tsf_no else null |
|
DAY |
Char(9) |
Yes |
'D' || tsfhead.delivery_date + transit_times.transit_time |
|
SUPPLIER |
Integer(20) |
No |
Item_supp_country.supplier |
|
LOC |
Integer(20) |
Yes |
Shipitem_inv_flow.to_loc if tsfhead.to_loc_type = 'W' and tsfhead.tsf_type = 'EG else Tsfhead.to_loc |
|
ITEM |
Char(20) |
Yes |
Formal Case Type: If simple pack and tsfhead.to_loc_type = 'S' then this would be the component of the pack in v_packsku_qty else item_master.item. Informal Case Type: Item_master.item |
|
ORDER_MULTIPLE |
Char (6) |
Yes |
Formal Case Type: If simple pack and tsfhead.to_loc_type = 'W' the v_packsku_qty.qty else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
TSF_QTY |
Char (8) |
Yes |
Formal Case Type: Tsfdetail.tsf_qty - tsfdetail.received_qty. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Tsfdetail.tsf_qty - tsfdetail.received_qty expressed in the primary case size. Remainder is in Standard UOM |
|
IN_TRANSIT_TSF_QTY |
Char (8) |
Yes |
Formal Case Type: Tsfdetail.ship_qty - tsfdetail.received_qty. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Tsfdetail.ship_qty - tsfdetail.received_qty expressed in the primary case size. Remainder is in Standard UOM |
|
ON_ORDER_TSF_QTY |
Char (8) |
Yes |
Formal Case Type: Tsfdetail.tsf_qty - tsfdetail.ship_qty. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Tsfdetail.tsf_qty - tsfdetail.ship_qty expressed in the primary case size. Remainder is in Standard UOM |
|
LOC_TYPE |
Char(1) |
Yes |
Tsfhead.to_loc_type |
|
TSF_TYPE |
Char(6) |
Yes |
Tsfhead.tsf_type |
The reject file rmse_aip_future_delivery_tsf_reject_ord_mult.txt is in pipe delimited (|) format.
Table 26-50 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
TRANSACTION_NUM |
Integer(12) |
No |
If tsfhead.to_loc_type ='W' then value will be tsfhead.tsf_no else null |
|
DAY |
Char(9) |
Yes |
'D' || tsfhead.delivery_date + transit_times.transit_time |
|
SUPPLIER |
Integer(20) |
No |
Item_supp_country.supplier |
|
LOC |
Integer(20) |
Yes |
Shipitem_inv_flow.to_loc if tsfhead.to_loc_type = 'W' and tsfhead.tsf_type = 'EG else Tsfhead.to_loc |
|
ITEM |
Char(20) |
Yes |
Formal Case Type: If simple pack and tsfhead.to_loc_type = 'S' then this would be the component of the pack in v_packsku_qty else item_master.item. Informal Case Type: Item_master.item |
|
ORDER_MULTIPLE |
Char (6) |
Yes |
Formal Case Type: If simple pack and tsfhead.to_loc_type = 'W' the v_packsku_qty.qty else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
TSF_QTY |
Char (8) |
Yes |
Formal Case Type: Tsfdetail.tsf_qty - tsfdetail.received_qty. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Tsfdetail.tsf_qty - tsfdetail.received_qty expressed in the primary case size. Remainder is in Standard UOM |
|
IN_TRANSIT_TSF_QTY |
Char (8) |
Yes |
Formal Case Type: Tsfdetail.ship_qty - tsfdetail.received_qty. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Tsfdetail.ship_qty - tsfdetail.received_qty expressed in the primary case size. Remainder is in Standard UOM |
|
ON_ORDER_TSF_QTY |
Char (8) |
Yes |
Formal Case Type: Tsfdetail.tsf_qty - tsfdetail.ship_qty. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Tsfdetail.tsf_qty - tsfdetail.ship_qty expressed in the primary case size. Remainder is in Standard UOM |
|
LOC_TYPE |
Char(1) |
Yes |
Tsfhead.to_loc_type |
|
TSF_TYPE |
Char(6) |
Yes |
Tsfhead.tsf_type |
| Module Name | rmse_aip_item_loc_traits.ksh |
| Description | Extract of Shelf Life on Receipt Location Trait for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS23 |
| Runtime Parameters | NA |
This script extracts from RMS item location traits information for integration with Oracle Retail Advanced Inventory Planning (AIP). Only the following items are extracted:
Approved, non-pack and forecastable
Approved and a simple pack item whose component is forecastable.
Items which are intentionally ranged to the location.(that is, item which has ranged_ind='Y' for the location in the item_loc table)
| Module Name | rmse_aip_item_retail.ksh |
| Description | Extract of Forecasted Items for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS24 |
| Runtime Parameters | NA |
This script extracts from RMS item information required by the item transformation script aipt_item.ksh for integration with Oracle Retail Advanced Inventory Planning (AIP). Records that meet the following criteria are extracted:
| Integration Type | Download from RMS |
| File Name | rmse_aip_item_retail.dat |
| Integration Contract | IntCon000074
rmse_aip_item_retail.schema |
Table 26-56 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
ITEM |
Char(25) |
Yes |
Item_master.item |
|
AIP_SKU |
Char(25) |
Yes |
Item_master.item |
|
SUBCLASS |
Integer(5) |
Yes |
Item_master.subclass |
|
CLASS |
Integer(5) |
Yes |
Item_master.class |
|
DEPT |
Integer(5) |
Yes |
Item_master.dept |
|
STANDARD_UOM |
Char(4) |
Yes |
Item_master.standard_uom |
|
STANDARD_UOM_ DESCRIPTION |
Char(20) |
Yes |
Uom_class.uom_desc_standard |
|
SKU_TYPE |
Char(6) |
No |
Non-pack items Item_master.handling_temp. "0" if NULL. Simple pack components Item_master.handling_temp or NULL. |
|
SKU_TYPE_ DESCRIPTION |
Char(40) |
No |
Non-pack items Code_detail.code_desc . "0" if NULL. Simple pack components Code_detail.code_desc or NULL. |
|
ORDER_MULTIPLE |
Char(6) |
Yes |
1 |
|
PACK_QUANTITY |
Char(6) |
No |
0 |
| Module Name | rmse_aip_item_sale.ksh |
| Description | Extract of Scheduled Item Maintenance On/Off Sale Information for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS31 |
| Runtime Parameters | NA |
This script extracts on/off sale information for integration with Oracle Retail Advanced Inventory Planning (AIP). This integration is designed to be used in conjunction with Scheduled Item Maintenance functionality in RMS.
The script produces two output files, one containing on sale records (sit_detail.status = 'A') and the other off sale records (sit_detail.status = 'C').
If a client does not use Scheduled Item Maintenance functionality to manage the on and off sale attributes of items at locations, the client does not need to run this program. Instead, the customer should create on/off sales information for AIP through a custom process.
This information extracted for AIP includes the status, status update date and order multiple for an item/location.
A status of 'A' indicates that an item/location is valid and can be ordered and sold. A status of 'C' indicates than an item/location is invalid and cannot be ordered or sold. The script only extracts items that meet the following criteria:
In active status
Transaction-level
Either non-pack or a simple pack
Sit_detail.status is either 'A' or 'C'
Sit_detail.status_update_date is greater than the current date
Only the order multiple for the primary supplier and primary supplier country is extracted.
| Integration Type | Download from RMS |
| File Name | dm0_onseffdt_.txt |
| Integration Contract | IntCon000075
rmse_aip_item_on_sale.schema |
Table 26-59 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
STORE |
Integer(20) |
Yes |
Sit_explode.location |
|
RMS_SKU |
Char(20) |
Yes |
Sit_explode.item |
|
ORDER_MULTIPLE |
Char(6) |
Yes |
If item_master.pack_ind = 'Y' then v_packsku_qty.qty (for the component item) else item_supp_country.order_multiple |
|
ON_SALE_EFFECTIVE_DATE |
Date |
Yes |
Sit_detail.status_update_date |
| Integration Type | Download from RMS |
| File Name | dm0_ofseffdt_.txt |
| Integration Contract | IntCon000135
rmse_aip_item_off_sale.schema |
Table 26-60 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
STORE |
Integer(20) |
Yes |
Sit_explode.location |
|
RMS_SKU |
Char(20) |
Yes |
Sit_explode.item |
|
ORDER_MULTIPLE |
Char(6) |
Yes |
If item_master.pack_ind = 'Y' then v_packsku_qty.qty (for the component item) else item_supp_country.order_multiple |
|
OFF_SALE_EFFECTIVE_DATE |
Date |
Yes |
Sit_detail.status_update_date |
The reject file rmse_aip_item_sale_reject_ord_mult.txt is in pipe delimited (|) format.
Table 26-61 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
STORE |
Integer(20) |
Yes |
Sit_explode.location |
|
RMS_SKU |
Char(20) |
Yes |
Sit_explode.item |
|
ORDER_MULTIPLE |
Char(6) |
Yes |
If item_master.pack_ind = 'Y' then v_packsku_qty.qty (for the component item) else item_supp_country.order_multiple |
|
OFF_SALE_EFFECTIVE_DATE ON_SALE_EFFECTIVE_DATE |
Date |
Yes |
Sit_detail.status_update_date |
| Module Name | rmse_aip_item_supp_country.ksh |
| Description | Extract of Order Multiples by Item/Supplier/Origin Country for AIP |
| Functional Area | RMS to AIP Integration |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS25 |
| Runtime Parameters | NA |
This script extracts RMS item-supplier information for integration with Oracle Retail Advanced Inventory Planning (AIP). Three output files are produced by this extract. Two contain item-supplier information. The other is a reject file containing item suppliers with rejected order multiples.
| Integration Type | Download from RMS |
| File Name | rmse_aip_item_supp_country.dat |
| Integration Contract | IntCon000076
rmse_aip_item_supp_country.schema |
Table 26-64 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
ITEM |
Char(25) |
Yes |
Item_supp_country.item |
|
SUPPLIER |
Integer(11) |
Yes |
Item_supp_country.supplier |
|
ORDER_MULTIPLE |
Integer(4) |
Yes |
Formal Case Type: V_packsku_qty.qty for simple pack, else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
PRIMARY_SUPP_IND |
Char(1) |
Yes |
Item_supp_country.primary_supp_ind |
| Integration Type | Download from RMS |
| File Name | aip_dmx_prdsplks.txt |
| Integration Contract | IntCon000133
rmse_aip_dmx_prdspllks.schema |
Table 26-65 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
SUPPLIER |
Integer(20) |
Yes |
Item_supp_country.supplier |
|
RMS_SKU |
Char(20) |
Yes |
Item_supp_country.item |
|
ORDER_MULTIPLE |
Char (6) |
Yes |
Formal Case Type: V_packsku_qty.qty for simple pack, else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
COMMODITY_SUPPLIER_LINKS |
Char(1) |
Yes |
1 |
The reject file rmse_aip_item_supp_country_reject_ord_mult.txt is in pipe delimited (|) format.
Table 26-66 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
ITEM |
Char(25) |
Yes |
Item_supp_country.item |
|
SUPPLIER |
Integer(11) |
Yes |
Item_supp_country.supplier |
|
ORDER_MULTIPLE |
Char(6) |
Yes |
Formal Case Type: V_packsku_qty.qty for simple pack, else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
PRIMARY_SUPP_IND |
Char(1) |
Yes |
Item_supp_country.primary_supp_ind |
| Module Name | rmse_aip_rec_qty.ksh |
| Description | Extract of Received PO, Allocation and Transfer Quantities for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS33 |
| Runtime Parameters | NA |
This script extracts from RMS received PO, transfer and allocation quantities for integration with Oracle Retail Advanced Inventory Planning (AIP). Only records that meet the following criteria below are extracted:
For Purchase Orders:
Ordhead.close_date is NULL or ordhead.close_date >= (current date - 1max_notafter_days);
Ordhead.not_after_date is not NULL
Ordhead.orig_ind = 6 (external system generated)
Ordloc.received_qty is not NULL
For Transfers:
Tsfhead.close_date is NULL or tsfhead.close_date >= (current date - max_notafter_days);
Tsfhead.tsf_type = 'AIP' (generated by AIP)
Tsfhead.delivery_date is not NULL
Tsfdetail.received_qty is not NULL
For Allocations:
Alloc_header.close_date is NULL or alloc_header.close_date >= (current date - 1max_notafter_days);
Alloc_header.origin_ind = 'AIP' (generated by AIP)
Alloc_header.release_date is not NULL
Alloc_detail.qty_received is not NULL
Alloc_header.order_no is not NULL(AIP generated allocations will always have an order associated with them)
| Integration Type | Download from RMS |
| File Name | received_qty.txt |
| Integration Contract | IntCon000079
rmse_aip_rec_qty.schema |
Table 26-69 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
ORDER_NUMBER |
Integer(12) |
Yes |
Ordhead.order_no or tsfhead.tsf_no or alloc_header.alloc_no |
|
ORDER_TYPE |
Char(1) |
Yes |
'P' for purchase orders or 'T' for transfers or 'A' for allocations |
|
RMS_SKU |
Char(25) |
Yes |
Ordsku.item or tsfdetail.item or alloc_header.item |
|
ORDER_MULTIPLE |
Char(6) |
Yes |
Ordsku.supp_pack_size or tsfdetail.supp_pack_size |
|
PACK_QTY |
Char(6) |
Yes |
If pack item then sum of V_packsku_qty.qty else 0 |
|
STORE |
Integer(10) |
No |
If ordloc.loc_type = 'S' then ordloc.location or If tsfhead.to_loc_type = 'S' then tsfhead.to_loc or If alloc_detail.to_loc_type = 'S' then alloc_detail.to_loc |
|
WAREHOUSE |
Integer(10) |
No |
If ordloc.loc_type = 'W' then ordloc.location or If tsfhead.to_loc_type = 'W' then tsfhead.to_loc or If alloc_detail.to_loc_type = 'W' then alloc_detail.to_loc |
|
RECEIVED_DATE |
Date |
Yes |
Ordhead.not_after_date or tsfhead.delivery_date or alloc_header.release_date |
|
QUANTITY |
Char(8) |
No |
Ordloc.qty_received or tsfdetail.received_qty or alloc_detail.qty_received |
| Module Name | rmse_aip_store_cur_inventory.ksh |
| Description | Extract of Store Current Inventory data for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS39 |
| Runtime Parameters | NA |
This script extracts RMS current inventory for store locations for integration with Oracle Retail Advanced Inventory Planning (AIP). This script requires an 'F' or 'D' parameter:
F - full extract of items/locations. Multiple output files. One file per item_loc_soh partition.
D - delta extract of items/locations for the current day's transactions as well as for the locations for which backorder message was received. Single output file.
Table 26-70 Scheduling Constraints
| Schedule Information | Description |
|---|---|
|
Frequency |
Daily |
|
Scheduling Considerations |
All RMS inventory jobs should complete before this extract is performed |
|
Pre-Processing |
NA |
|
Post-Processing |
NA |
|
Threading Scheme |
One thread per partition of item_loc_soh will be invoked if the script is run with a parameter of 'F' |
| Integration Type | Download from RMS |
| File Name | sr0_curinv_{THREAD_NO}.txt |
| Integration Contract | IntCon000081
rmse_aip_store_cur_inventory.schema |
Table 26-72 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
STORE |
Integer(20) |
Yes |
Item_loc_soh.loc |
|
RMS_SKU |
Char(20) |
Yes |
Item_master.item |
|
STORE_CUR_INV |
Char (8) |
No |
Item_loc_soh.stock_on_hand - (item_loc_soh.tsf_reserved_qty + item_loc_soh.rtv_qty + item_loc_soh.non_sellable_qty + item_loc_soh.customer_resv) |
|
BACKORDER_QUANTITY |
Char (8) |
No |
Item_loc_soh.customer_backorder |
| Module Name | rmse_aip_tsf _in_well.ksh |
| Description | Extract of Transfer in the Well Quantities to AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS36 |
| Runtime Parameters | NA |
This script extracts RMS "in the well" transfer quantities for integration with AIP. In the well pertains to inventory that has been reserved by an approved or shipped transfer. The expected delivery date is also included in the extract. Transfers created by the RMS wholesale/franchise ordering and return processes will not be extracted.
Table 26-74 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
ITEM_MASTER |
Yes |
No |
No |
No |
|
ITEM_SUPP_COUNTRY |
Yes |
No |
No |
No |
|
ITEM_SUPPLIER |
Yes |
No |
No |
No |
|
TSFHEAD |
Yes |
No |
No |
No |
|
TSFDETAIL |
Yes |
No |
No |
No |
|
SHIPITEM_INV_FLOW |
Yes |
No |
No |
No |
|
TRANSIT_TIMES |
Yes |
No |
No |
No |
|
V_WH |
Yes |
No |
No |
No |
|
V_PACKSKU_QTY |
Yes |
No |
No |
No |
|
PACKITEM |
Yes |
No |
No |
No |
| Integration Type | Download from RMS |
| File Name | rmse_aip_tsf_in_well.dat |
| Integration Contract | IntCon000084
rmse_aip_tsf_in_well.schema |
Table 26-75 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
DAY |
Char(9) |
Yes |
tsfhead.delivery_date - transit_times.transit_time |
|
LOC |
Integer(20) |
Yes |
f tsfhead.from_loc type = 'W' and (tsfhead.tsf_type = 'EG' or tsfhead.tsf_type = 'CO' and OMS_IND = 'Y') then shipitem_inv_flow.from_loc else tsfhead.from_loc |
|
ITEM |
Char(20) |
Yes |
Formal Case Type: If simple pack then and tsfhead.to_loc_type = 'S' then this would be the component of the pack in v_packsku_qty else item_master.item. Informal Case Type: Item_master.item |
|
ORDER_MULTIPLE |
Char (6) |
Yes |
Formal Case Type: V_packsku_qty.qty for simple pack, else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
TSF_RESERVED_QTY |
Char (8) |
Yes |
Formal Case Type: Tsfdetail.tsf_qty - tsfdetail.ship_qty. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Tsfdetail.tsf_qty - tsfdetail.ship_qty expressed in the primary case size. Remainder is in Standard UOM |
The reject file rmse_aip_tsf_in_well_reject_ord_mult.txt is in pipe delimited (|) format.
Table 26-76 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
DAY |
Char(9) |
Yes |
tsfhead.delivery_date - transit_times.transit_time |
|
LOC |
Integer(20) |
Yes |
If tsfhead.from_loc type = 'W' and (tsfhead.tsf_type = 'EG' or tsfhead.tsf_type = 'CO' and OMS_IND = 'Y') then shipitem_inv_flow.from_loc else tsfhead.from_loc |
|
ITEM |
Char(20) |
Yes |
Formal Case Type: If simple pack then and tsfhead.to_loc_type = 'S' then this would be the component of the pack in v_packsku_qty else item_master.item. Informal Case Type: Item_master.item |
|
ORDER_MULTIPLE |
Char (6) |
Yes |
Formal Case Type: V_packsku_qty.qty for simple pack, else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
TSF_RESERVED_QTY |
Char (8) |
Yes |
Formal Case Type: Tsfdetail.tsf_qty - tsfdetail.ship_qty. Resulting quantity is multiplied by V_packsku_qty.qty if item is a pack. Informal Case Type: Tsfdetail.tsf_qty - tsfdetail.ship_qty expressed in the primary case size. Remainder is in Standard UOM |
| Module Name | rmse_aip_wh_cur_inventory.ksh |
| Description | Extract of Warehouse Current Inventory for AIP |
| Functional Area | Integration - AIP |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | RMS34 |
| Runtime Parameters | NA |
This script extracts RMS current warehouse inventory information for integration with Oracle Retail Advanced Inventory Planning (AIP).
This script requires an 'F' or 'D' parameter:
F - full extract of items/locations. Creates multiple files per warehouse. Files are concatenated into a single file upon successful completion.
D - delta extract of items/locations for the current day's transactions as well as for the locations for which backorder message was received. Creates a single extract file.
The script creates a backup of the previous day's data file labeled with the date on which they were created.
Table 26-77 Scheduling Constraints
| Schedule Information | Description |
|---|---|
|
Frequency |
Daily |
|
Scheduling Considerations |
All RMS inventory jobs should complete before this extract is performed |
|
Pre-Processing |
NA |
|
Post-Processing |
NA |
|
Threading Scheme |
One thread per warehouse will be invoked if the script is run with a parameter of 'F' |
Table 26-78 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
ITEM_MASTER |
Yes |
No |
No |
No |
|
ITEM_SUPP_COUNTRY |
Yes |
No |
No |
No |
|
ITEM_LOC_SOH |
Yes |
No |
No |
No |
|
WH |
Yes |
No |
No |
No |
|
ALLOC_DETAIL |
Yes |
No |
No |
No |
|
ALLOC_HEADER |
Yes |
No |
No |
No |
|
ORDHEAD |
Yes |
No |
No |
No |
|
ITEM_SUPPLIER |
Yes |
No |
No |
No |
|
PACKITEM |
Yes |
No |
No |
No |
|
V_PACKSKU_QTY |
Yes |
No |
No |
No |
|
IF_TRAN_DATA_TEMP |
Yes |
No |
No |
No |
| Integration Type | Download from RMS |
| File Name | wr1_curinv.txt |
| Integration Contract | IntCon000092
rmse_aip_wh_cur_inventory.schema |
Table 26-79 File Layout
| Field Name | Field Type | Required | Description |
|---|---|---|---|
|
WAREHOUSE |
Integer(20) |
Yes |
Item_loc_soh.loc |
|
RMS_SKU |
Char(20) |
Yes |
Item_master.item |
|
ORDER_MULT |
Char (6) |
Yes |
Formal Case Type: V_packsku_qty.qty for simple pack, else 1 Informal Case Type: One unique record for each item/supplier with order multiples of: 1, supp_pack_size, inner_pack_size and (ti * hi * supp_packsize) |
|
WH_CUR_INV |
Char (8) |
Yes |
Formal Case Type: ((Item_loc_soh.stock_on_hand - (item_loc_soh.tsf_reserved_qty + item_loc_soh.rtv_qty + item_loc_soh.non_sellable_qty + item_loc_soh.customer_resv)) - alloc_detail.qty_distro * Informal Case Type: ((Item_loc_soh.stock_on_hand - (item_loc_soh.tsf_reserved_qty + item_loc_soh.rtv_qty + item_loc_soh.non_sellable_qty + item_loc_soh.customer_resv)) - alloc_detail.qty_distro) |
|
WH_BO_INV |
Char (8) |
Yes |
Item_loc_soh.customer_backorder |
