28 Integration with Oracle Retail Planning

Retail Predictive Application Server (RPAS) is the platform for Oracle Retail's planning applications. RMS provides foundation and inventory information to RPAS for use in planning processes. All RPAS based planning processes require a minimum amount of information. These platform level integration processes are discussed in this chapter.

Some of additional planning products based on the RPAS platform require additional information from RMS and produce additional results for RMS. RMS also provides specific integrations for Retail Demand Forecasting (RDF) and Merchandise Financial Planning (MFP). These product level integration processes are also discussed in this chapter.

Deeper information about the flow of information between RMS and Planning applications can be found in the Retail Reference Architecture (available on MyOracleSupport).

Many of the integrations described in this chapter use RETL (Retail Extract, Transform, Load).

Foundation Data vs Transaction/Inventory Data

RPAS 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. Weekly information in RMS is rolled up, which pushes some weekly RPAS extracts to quite late in the RMS schedule.

Scheduling and dependency information for each program can be found in the program details section of this chapter.

RPAS Integration Program Summary

Table 28-1 RPAS Integration Program Summary

Program	Description
pre_rmse_rpas.ksh	Extract of RMS System level settings for RPAS
rmse_rpas_suppliers.ksh	Extract of Suppliers for RPAS
rmse_rpas_merchhier.ksh	Extract of Merchandise Hierarchy for RPAS
rmse_rpas_orghier.ksh	Extract of Organizational Hierarchy for RPAS
rmse_rpas_wh.ksh	Extract of Warehouses for RPAS
rmse_rpas_store.ksh	Extract of Stores for RPAS
rmse_rpas_item_master.ksh	Extract of Items for RPAS
rmse_rpas_domain.ksh	Extract of Domains for RPAS
rmse_rpas_attributes.ksh	Extract of User Defined Attributes for RPAS
rmse_rpas_weekly_sales.ksh	Extract of Weekly Sales of Forecasted Items for RPAS
rmse_rpas_daily_sales.ksh	Extract of Daily Sales of Forecasted Items for RPAS
rmse_rpas_stock_on_hand.ksh	Extract of Stock On Hand of Forecasted Items for RPAS
rmse_rpas	RMS-Planning Extract Wrapper Script
rmsl_rpas_update_retl_date.ksh	Update Last RPAS Extract Date
onictext	On Inter-Company Transfer Exhibit
onordext	On Order Extract
gradupld	Upload of Store Grade Classifications from RPAS
onorddnld	On Order Download to Financial Planning

RDF Integration Program Summary

Table 28-2 RDF Integration Program Summary

Program	Description
soutdnld.pc	Download of Out Of Stock Items
ftmednld.pc	Download of Time Hierarchy for Planning Systems
rms_oi_forecast_history.ksh	Retain Item Forecast History
rmsl_rpas_forecast.ksh	Load Daily/Weekly Forecast from RPAS
fcstprg.pc	Purge Forecast Data
rmse_rdf_daily_sales	Extract of Daily Sales of Forecasted Items for RPAS
rmse_rdf_weekly_sales	Extract of Weekly Sales of Forecasted Items for RPAS

MFP Integration Program Summary

Table 28-3 MFP Integration Program Summary

Program	Description
rmse_mfp_inventory.ksh	Extract of Inventory Aggregation for MFP
rmse_mfp_onorder.ksh	Extract of On Order for MFP

For additional details on the RMS/MFP integration from the perspective of MFP, see the Oracle Retail Merchandise Financial Planning Operations Guide.

pre_rmse_rpas (Extract of RMS System level settings for RPAS)

Module Name	pre_rmse_rpas.ksh
Description	Extract of RMS System level settings for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS160
Runtime Parameters	NA

Design Overview

The purpose of this batch module is to fetch the RMS system parameters that must be referenced in RPAS. This program produces a number of output files.

Some of the output files contain relatively static data that generally only changes at implementation. However, two files concern the current and next date in RMS. As dates change in RMS, this program should be run daily.

Scheduling Constraints

Table 28-4 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	If the system is configured to use the Inventory Variance to Forecast report in the Inventory Analyst dashboard, run this program after rms_oi_forecast_history.ksh to preserve 4 weeks of item forecast data before truncating it in this program _
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-5 Key Tables Affected

Table	Select	Insert	Update	Delete
SYSTEM_OPTIONS	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No
PERIOD	Yes	No	No	No
RETL_EXTRACT_DATES	Yes	No	No	No
CURRENCY_RATES	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	consolidation_code.txt
Integration Contract	IntCon000140

Output File Layout

Table 28-6 Output File Layout

Field Name	Field Type	Required	Description
CONSOLIDATION_CODE	Varchar2(1)	Yes	Indicates whether Oracle Retail will support the addition, maintenance, and viewing for the consolidation exchange rate in the Pending Exchange Rate Maintenance process.

Integration Contract

Integration Type	Download from RMS
File Name	vat_ind.txt
Integration Contract	IntCon000141

Output File Layout

Table 28-7 Output File Layout

Field Name	Field Type	Required	Description
VAT_IND	Varchar2(1)	Yes	Indicates whether taxation is used in the system. Valid values: N for SALES tax type. Y for other values.

Integration Contract

Integration Type	Download from RMS
File Name	prime_currency_code.txt
Integration Contract	IntCon000142

Output File Layout

Table 28-8 Output File Layout

Field Name	Field Type	Required	Description
CURRENCY_CODE	Varchar2(3)	Yes	Indicates whether taxation is used in the system. Valid values: N for SALES tax type. Y for other values

Integration Contract

Integration Type	Download from RMS
File Name	prime_currency_code.txt
Integration Contract	IntCon000142

Output File Layout

Table 28-9 Output File Layout

Field Name	Field Type	Required	Description
CURRENCY_CODE	Varchar2(3)	Yes	Indicates the currency code

Integration Contract

Integration Type	Download from RMS
File Name	stkldgr_vat_incl_retl_ind.txt
Integration Contract	IntCon000143

Output File Layout

Table 28-10 Output File Layout

Field Name	Field Type	Required	Description
STKLDGR_VAT_INCL_RETL_IND	Varchar2(1)	Yes	Indicates if the retail value in stock ledger is VAT inclusive or no

Integration Contract

Integration Type	Download from RMS
File Name	multi_currency_ind.txt
Integration Contract	IntCon000144

Output File Layout

Table 28-11 Output File Layout

Field Name	Field Type	Required	Description
MULTI_CURRENCY_IND	Varchar2(1)	Yes	Indicates if there are more than one currency in the system

Integration Contract

Integration Type	Download from RMS
File Name	class_level_vat_ind.txt
Integration Contract	IntCon000145

Output File Layout

Table 28-12 Output File Layout

Field Name	Field Type	Required	Description
CLASS_LEVEL_VAT_IND	Varchar2(1)	Yes	Indicates if VAT is used at the class level

Integration Contract

Integration Type	Download from RMS
File Name	domain_level.txt
Integration Contract	IntCon000146

Output File Layout

Table 28-13 Output File Layout

Field Name	Field Type	Required	Description
DOMAIN_LEVEL	Varchar2(1)	Yes	Indicates the domain grouping level.

Integration Contract

Integration Type	Download from RMS
File Name	vdate.txt
Integration Contract	IntCon000147

Output File Layout

Table 28-14 Output File Layout

Field Name	Field Type	Required	Description
VDATE	Date	Yes	Indicates the system date

Integration Contract

Integration Type	Download from RMS
File Name	next_vdate.txt
Integration Contract	IntCon000148

Output File Layout

Table 28-15 Output File Layout

Field Name	Field Type	Required	Description
NEXT_VDATE	Date	Yes	Indicates the next system date in the system. VDATE+1

Integration Contract

Integration Type	Download from RMS
File Name	last_eom_date.txt
Integration Contract	IntCon000149

Output File Layout

Table 28-16 Output File Layout

Field Name	Field Type	Required	Description
LAST_EOM_DATE	Date	Yes	Indicates the date of the end of month cycle

Integration Contract

Integration Type	Download from RMS
File Name	curr_bom_date.txt
Integration Contract	IntCon000150

Output File Layout

Table 28-17 Output File Layout

Field Name	Field Type	Required	Description
CURR_BOM_DATE	Date	Yes	Indicates the next succeeding date after the end of the month cycle

Integration Contract

Integration Type	Download from RMS
File Name	max_backpost_days.txt
Integration Contract	IntCon000151

Output File Layout

Table 28-18 Output File Layout

Field Name	Field Type	Required	Description
MAX_BACKPOST_DAYS	Date	Yes	Indicates the number of days from the system date and the last end of month date cycle

Integration Contract

Integration Type	Download from RMS
File Name	last_extr_closed_pot_date.txt
Integration Contract	IntCon000152

Output File Layout

Table 28-19 Output File Layout

Field Name	Field Type	Required	Description
LAST_EXTR_CLOSED_POT_DATE	Date	Yes	Indicates the date of the most recent extraction of closure dates for transactions

Integration Contract

Integration Type	Download from RMS
File Name	last_extr_received_pot_date.txt
Integration Contract	IntCon000153

Output File Layout

Table 28-20 Output File Layout

Field Name	Field Type	Required	Description
LAST_EXTR_RECEIVED_POT_DATE	Date	Yes	Indicates the date of the most recent quantity extraction

Integration Contract

Integration Type	Download from RMS
File Name	prime_exchng_rate.txt
Integration Contract	IntCon000154

Output File Layout

Table 28-21 Output File Layout

Field Name	Field Type	Required	Description
PRIME_EXCHNG_RATE	Number(20,10)	Yes	Indicates the primary exchange rate for the given currency in the system

Design Assumptions

NA

rmse_rpas_suppliers (Extract of Suppliers for RPAS)

Module Name	rmse_rpas_suppliers.ksh
Description	Extract of Suppliers for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS11
Runtime Parameters	NA

Design Overview

This script extracts supplier information for interfacing to an external planning system, such as RPAS. All suppliers are extracted so no delta processing exists.

Scheduling Constraints

Table 28-22 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After pre_rmse_rpas.ksh
Pre-Processing	pre_rmse_rpas.ksh
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-23 Key Tables Affected

Table	Select	Insert	Update	Delete
SUPS	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000095 rmse_rpas_suppliers.schema

Output File Layout

Table 28-24 Output File Layout

Field Name	Field Type	Required	Description
SUPPLIER	Integer(11)	Yes	Sups.supplier
SUP_NAME	Char(240)	Yes	Sups.sup_name

Design Assumptions

NA

rmse_rpas_merchhier (Extract of Merchandise Hierarchy for RPAS)

Module Name	rmse_rpas_merchhier.ksh
Description	Extract of Merchandise Hierarchy for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS10
Runtime Parameters	NA

Design Overview

This script extracts the RMS merchandise hierarchy information for interfacing to an external planning system, such as RPAS. The full hierarchy is extracted so no delta processing exists.

Scheduling Constraints

Table 28-25 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After dlyprg.pc, pre_rmse_rpas.ksh
Pre-Processing	pre_rmse_rpas.ksh, dlyprg.pc
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-26 Key Tables Affected

Table	Select	Insert	Update	Delete
COMPHEAD	Yes	No	No	No
DIVISION	Yes	No	No	No
GROUPS	Yes	No	No	No
DEPS	Yes	No	No	No
CLASS	Yes	No	No	No
SUBCLASS	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	Integration Contract IntCon000090 rmse_rpas_merchhier.schema

Output File Layout

Table 28-27 Output File Layout

Field Name	Field Type	Required	Description
SUBCLASS	Integer(5)	Yes	Subclass.subclass
SUB_NAME	Char(120)	Yes	Subclass.sub_name
CLASS	Integer(5)	Yes	Class.class
CLASS_NAME	Char(120)	Yes	Class.class
DEPT	Integer(5)	Yes	Deps.dept
DEPT_NAME	Char(120)	Yes	Deps.dept_name
GROUP_NO	Integer(5)	Yes	Groups.group_no
GROUP_NAME	Char(120)	Yes	Groups.group_name
DIVISION	Integer(5)	Yes	Division.division
DIV_NAME	Char(120)	Yes	Division.div_name
COMPANY	Integer(5)	Yes	Comphead.company
CO_NAME	Char(120)	Yes	Comphead.co_name

Design Assumptions

NA

rmse_rpas_orghier (Extract of Organizational Hierarchy for RPAS)

Module Name	rmse_rpas_orghier.ksh
Description	Extract of Organizational Hierarchy for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS04
Runtime Parameters	NA

Design Overview

This script extracts the RMS organizational hierarchy information for interfacing to an external planning system, such as RPAS. The full hierarchy is extracted so no delta processing exists.

Scheduling Constraints

Table 28-28 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After dlyprg.pc After pre_rmse_rpas.ksh
Pre-Processing	Dlyprg.pc, pre_rmse_rpas.ksh
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-29 Key Tables Affected

Table	Select	Insert	Update	Delete
COMPHEAD	Yes	No	No	No
CHAIN	Yes	No	No	No
AREA	Yes	No	No	No
REGION	Yes	No	No	No
DISTRICT	Yes	No	No	No

Output File Layout

The output file is in fixed-length format matching to the schema definition in rmse_rpas_orghier.schema.

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000091 rmse_rpas_orghier.schema

Output File

Table 28-30 Output File

Field Name	Field Type	Required	Description
DISTRICT	Integer(11)	No	District.district
DISTRICT_NAME	Char(120)	No	District.district_name
REGION	Integer(11)	No	Region.region
REGION_NAME	Char(120)	No	Region.region_name
AREA	Integer(11)	No	Area.area
AREA_NAME	Char(120)	No	Area.area_name
CHAIN	Integer(11)	Yes	Chain.chain
CHAIN_NAME	Char(120)	Yes	Chain.chain_name
COMPANY	Integer(5)	Yes	Comphead.company
CO_NAME	Char(120)	Yes	Comphead.co_name

Design Assumptions

NA

rmse_rpas_wh (Extract of Warehouses for RPAS)

Module Name	rmse_rpas_wh.ksh
Description	Extract of Warehouses for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS03
Runtime Parameters	NA

Design Overview

This script extracts warehouse information for interfacing to an external planning system, such as RPAS. All stockholding warehouses are extracted so no delta processing exists.

Scheduling Constraints

Table 28-31 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After dlyprg.pc After pre_rmse_rpas.ksh
Pre-Processing	pre_rmse_rpas.ksh, dlyprg.pc
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-32 Key Tables Affected

Table	Select	Insert	Update	Delete
WH	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000097 rmse_rpas_wh.schema

Output File Layout

Table 28-33 Output File Layout

Field Name	Field Type	Required	Description
WH	Integer(11)	Yes	Wh.wh
WH_NAME	Char(150)	Yes	Wh.wh_name
FORECAST_WH_IND	Char(1)	Yes	Wh.forecast_wh_ind
STOCKHOLDING_IND	Char(1)	Yes	Wh.stockholding_ind
CHANNEL_ID	Number(4)	Yes	Wh.channel_id
CHANNEL_NAME	Varchar2(120)	Yes	Channels.channel_name

Design Assumptions

NA

rmse_rpas_store (Extract of Stores for RPAS)

Module Name	rmse_rpas_store.ksh
Description	Extract of Stores for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS02
Runtime Parameters	NA

Design Overview

This script extracts store information for interfacing to an external planning system, such as RPAS. All open stores are extracted so no delta processing exists.

Scheduling Constraints

Table 28-34 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After dlyprg.pc After pre_rmse_rpas.ksh
Pre-Processing	dlyprg.pc, pre_rmse_rpas.ksh
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-35 Key Tables Affected

Table	Select	Insert	Update	Delete
STORE	Yes	No	No	No
STORE_FORMAT	Yes	No	No	No
CODE_DETAIL	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000094 rmse_rpas_store.schema

Output File Layout

Table 28-36 Output File Layout

Field Name	Field Type	Required	Description
STORE	Integer(11)	Yes	Store.store
STORE_NAME	Char(150)	Yes	Store.store_name
DISTRICT	Integer(11)	Yes	Store.district
STORE_CLOSE_DATE	Date(8)	No	Store.store_close_date
STORE_OPEN_DATE	Date(8)	Yes	Store.store_open_date
STORE_CLASS	Char(1)	Yes	Store.store_class
STORE_CLASS_DESCRIPTION	Char(40)	Yes	Code_detail.code_desc (for code_type ’CSTR')
STORE_FORMAT	Integer(5)	No	Store.store_format
FORMAT_NAME	Char(60)	No	Store_format.format_name
CHANNEL_ID	Number(4)	Yes	Store.channel_id
CHANNEL_NAME	Varchar2(120)	Yes	Channels.channel_name

Design Assumptions

NA

rmse_rpas_item_master (Extract of Items for RPAS)

Module Name	rmse_rpas_item_master.ksh
Description	Extract of Items for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS05
Runtime Parameters	NA

Design Overview

This script extracts item information from RMS for interfacing to an external planning system, such as RPAS. This extract will pull all approved items. All items meeting the criteria will be extracted so no delta processing exists.

Note: In RMS, diff_type is a string of up to 6 characters. However, in RPAS, the diff_type is only 1 character long. IF_RDF_DIFF_MAP table holds the mapping between the RMS diff_type and RPAS diff_type. The RPAS diff_type is extracted to the output file.

Scheduling Constraints

Table 28-37 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After sitmain.pc, reclsdly.pc and dlyprg.pc After pre_rmse_rpas.ksh
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-38 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_MASTER	Yes	No	No	No
ITEM_SUPPLIER	Yes	No	No	No
DIFF_IDS	Yes	No	No	No
IF_RDF_DIFF_MAP	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000089 rmse_rpas_item_master.schema

Output File Layout

Table 28-39 Output 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
ITEM_LEVEL	Integer(1)	Yes	Item_master.item_level
TRAN_LEVEL	Integer(1)	Yes	Item_master.tran_level
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 supplier only
DIFF_1_TYPE	Char(1)	No	If_rdf_diff_map.rdf_diff_type_map
DIFF_1	Char(10)	No	Diff_ids.diff_id
DIFF_DESC_1	Char(120)	No	Diff_ids.diff_desc
DIFF_FILE_POSITION_1	Integer(2)	No	If_rdf_diff_map.file_position
DIFF_1_AGGREGATE_IND	Char(1)	No	Item_master.diff_1_aggregate_ind
DIFF_2_TYPE	Char(1)	No	If_rdf_diff_map.rdf_diff_type_map
DIFF_2	Char(10)	No	Diff_ids.diff_id
DIFF_DESC_2	Char(120)	No	Diff_ids.diff_desc
DIFF_FILE_POSITION_2	Integer(2)	No	If_rdf_diff_map.file_position
DIFF_2_AGGREGATE_IND	Char(1)	No	Item_master.diff_2_aggregate_ind
DIFF_3_TYPE	Char(1)	No	If_rdf_diff_map.rdf_diff_type_map
DIFF_3	Char(10)	No	Diff_ids.diff_id
DIFF_DESC_3	Char(120)	No	Diff_ids.diff_desc
DIFF_FILE_POSITION_3	Integer(2)	No	If_rdf_diff_map.file_position
DIFF_3_AGGREGATE_IND	Char(1)	No	Item_master.diff_3_aggregate_ind
DIFF_4_TYPE	Char(1)	No	If_rdf_diff_map.rdf_diff_type_map
DIFF_4	Char(10)	No	Diff_ids.diff_id
DIFF_DESC_4	Char(120)	No	Diff_ids.diff_desc
DIFF_FILE_POSITION_4	Integer(2)	No	If_rdf_diff_map.file_position
DIFF_4_AGGREGATE_IND	Char(1)	No	Item_master.diff_4_aggregate_ind

Design Assumptions

NA

rmse_rpas_domain (Extract of Domains for RPAS)

Module Name	rmse_rpas_domain.ksh
Description	Extract of Domains for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS06
Runtime Parameters	NA

Design Overview

This script extracts from RMS domain information for RMS integration with an external planning system, for example RPAS.

Scheduling Constraints

Table 28-40 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After pre_rmse_rpas.ksh
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-41 Key Tables Affected

Table	Select	Insert	Update	Delete
SYSTEM_OPTIONS	Yes	No	No	No
DOMAIN	Yes	No	No	No
DOMAIN_DEPT	Yes	No	No	No
DOMAIN_CLASS	Yes	No	No	No
DOMAIN_SUBCLASS	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000088 rmse_rpas_domain.schema

Output File Layout

Table 28-42 Output File Layout

Field Name	Field Type	Required	Description
DOMAIN_ID	Integer(3)	No	Domain.domain_id
DOMAIN_DESC	Char(20)	No	Domain.domain_desc
DEPT	Integer(5)	No	Domain_dept.dept or Domain_class.dept or Domain_subclass.dept
CLASS	Integer(5)	No	Domain_class.class or Domain_subclass.class or NULL
SUBCLASS	Integer(5)	No	Domain_subclass.subclass or NULL
LOAD_SALES_IND	Char(2)	No	Domain_dept.load_sales_ind or Domain_class.load_sales_ind or Domain_subclass.load_sales_ind

Design Assumptions

NA

rmse_rpas_attributes (Extract of User Defined Attributes for RPAS)

Module Name	Rmse_rpas_attributes.ksh
Description	Extract of User Defined Attributes for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS01
Runtime Parameters	NA

Design Overview

This script extracts from RMS user defined attributes information for RMS integration with an external planning system, for example RPAS.

If launched through rmse_rpas.ksh, this program is only going to be executed if either PROD_ATTRIBUTES_ACTIVE or LOC_ATTRIBUTES_ACTIVE parameter is set to TRUE in rmse_rpas_config.ksh.

Note: This script provides a framework of UDA extract. Each client will have to customize it to reflect the UDA ids associated with the desired attributes (such as,. season, brand, ethnic, and so on).

Scheduling Constraints

Table 28-43 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After pre_rmse_rpas.ksh
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-44 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_MASTER	Yes	No	No	No
ITEM_SUPPLIER	Yes	No	No	No
UDA_ITEM_LOV	Yes	No	No	No
UDA	Yes	No	No	No
UDA_VALUES	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000086 rmse_rpas_attributes.schema

Note: Each client needs to customize the field definitions in rmse_rpas_attributes.schema. The field definitions must be kept in sync with the UDAxxx fields of rdft_merchhier.attributes.schema.

Output File Layout

Table 28-45 Output File Layout

Field Name	Field Type	Required	Description
ITEM	Char(25)	Yes	Item_master.item
COMPANY	Integer(20)	Yes	Comphead.company
CO_NAME	Char(120)	Yes	Comphead.co_name
UDA_VALUE_101	Char(20)	No	Uda_values.uda_value
UDA_VALUE_DESC_101	Char(250)	No	Uda_values.uda_value_desc
UDA_VALUE_103	Char(20)	No	Uda_values.uda_value
UDA_VALUE_DESC_103	Char(250)	No	Uda_values.uda_value_desc
UDA_VALUE_104	Char(20)	No	Uda_values.uda_value
UDA_VALUE_DESC_104	Char(250)	No	Uda_values.uda_value_desc
UDA_VALUE_501	Char(20)	No	Uda_values.uda_value
UDA_VALUE_DESC_501	Char(250)	No	Uda_values.uda_value_desc

rmse_rpas_weekly_sales (Extract of Weekly Sales of Forecasted Items for RPAS)

Module Name	rmse_rpas_weekly_sales.ksh
Description	Extract of Weekly Sales of Forecasted Items for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS09
Runtime Parameters	NA

Design Overview

This script extracts item weekly sales information at a location for interfacing to an external planning system, such as RPAS. Only forecastable items are extracted. This extract will contain only weeks that have yet to be extracted. Once the extract is completed this process with execute the rmsl_rpas_update_last_hist_exp_date.ksh script to update the last export date for any extracted item/locations which is used for subsequent extracts.

Scheduling Constraints

Table 28-46 Scheduling Constraints

Schedule Information	Description
Frequency	Weekly
Scheduling Considerations	After hstwkupd.pc After salweek.pc After pre_rmse_rpas.ksh
Pre-Processing	pre_rmse_rpas.ksh, hstwkupd, salweek
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-47 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_MASTER	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	No	No
ITEM_LOC_HIST	Yes	No	No	No
PERIOD	Yes	No	No	No
DOMAIN_DEPT	Yes	No	No	No
DOMAIN_CLASS	Yes	No	No	No
DOMAIN_SUBCLASS	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000096 rmse_rpas_weekly_sales.schema

Output File Layout

Table 28-48 Output File Layout

Field Name	Field Type	Required	Description
ITEM	Char(25)	Yes	Item_master.item
LOC	Integer(11)	Yes	Item_loc_soh.loc
EOW_DATE	Date(8)	No	Item_loc_hist.eow_date in YYYYMMDD forma
SALES_ISSUES	Double(18)	No	Item_loc_hist.sales_issues
SALES_TYPE	Char(1)	Yes	Item_loc_hist.sales_type
ROW_ID	Char(18)	No	Item_loc_soh.row_id
DOMAIN_ID	Integer(3)	Yes	Domain_dept.domain_id or domain_class.domain_id or domain_subclass.domain_id

Design Assumptions

NA

rmse_rpas_daily_sales (Extract of Daily Sales of Forecasted Items for RPAS)

Module Name	Rmse_rpas_daily_sales.ksh
Description	Extract of Daily Sales of Forecasted Items for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS08
Runtime Parameters	NA

Design Overview

This script extracts from RMS item's daily sales information at a location for RMS integration with an external planning system, for example RPAS. Only forecastable items are extracted. For a store, the sales data represents the net sales (gross sales - returns); for a warehouse, the sales data represents the stock transferred out of the warehouse.

Each client can customize the variable USE_IF_TRAN_DATA in this script to choose whether the sales data should come from IF_TRAN_DATA table or TRAN_DATA_HISTORY table.

Scheduling Constraints

Table 28-49 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After saldly.pc After pre_rmse_rpas.ksh
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-50 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_MASTER	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	No	No
IF_TRAN_DATA	Yes	No	No	No
TRAN_DATA_HISTORY	Yes	No	No	No
DOMAIN_DEPT	Yes	No	No	No
DOMAIN_CLASS	Yes	No	No	No
DOMAIN_SUBCLASS	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000087 rmse_rpas_daily_sales.schema

Output File Layout

Table 28-51 Output File Layout

Field Name	Field Type	Required	Description
LOC	Integer(11)	Yes	Item_loc_soh.loc
ITEM	Char(25)	No	If_tran_data.item or tran_data_history.item
TRAN_DATE	Date(8)	Yes	If_tran_data.tran_date or tran_data_history.tran_date
SUM_UNITS	Double(14)	No	If_tran_data.units or tran_data_history.units
SALES_TYPE	Char(1)	No	If_tran_data.sales_type or tran_data_history.sales_type
TRAN_CODE	Integer(3)	Yes	If_tran_data.tran_code or tran_data_history.tran_code
DOMAIN_ID	Integer(3)	Yes	Domain_dept.domain_id or domain_class.domain_id or domain_subclass.domain_id

Design Assumptions

NA

rmse_rpas_stock_on_hand (Extract of Stock On Hand of Forecasted Items for RPAS)

Module Name	rmse_rpas_stock_on_hand.ksh
Description	Extract of Stock On Hand of Forecasted Items for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS07
Runtime Parameters	NA

Design Overview

This script extracts item stock on hand information at a location for interfacing to an external planning system, for example RPAS. Only Approved items marked as forecastable will be extracted.

A run-time parameter is used to indicate whether the stock on hand information for warehouses should be extracted or not. Item/store's stock on hand is always extracted as 'sales'. However, item/warehouse's stock on hand is only extracted as 'issues' when the run-time parameter ISSUES_ACTIVE is 'True'.

Scheduling Constraints

Table 28-52 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After stkdly.pc After pre_rmse_rpas.ksh
Pre-Processing	pre_rmse_rpas.ksh, stkdly.pc
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-53 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_MASTER	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	No	No
DOMAIN_DEPT	Yes	No	No	No
DOMAIN_CLASS	Yes	No	No	No
DOMAIN_SUBCLASS	Yes	No	No	No

Integration Contract

There are two output files associated with this script, one for stores and one for warehouses.

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000093 rmse_rpas_stock_on_hand_sales.schema rmse_rpas_stock_on_hand_issues.schema

Output File Layout

Table 28-54 Output File Layout

Field Name	Field Type	Required	Description
ITEM	Char(25)	Yes	Item_loc_soh.item
LOC	Integer(11)	Yes	Item_loc_soh.loc
STOCK_ON_HAND	Double(14)	Yes	Item_loc_soh.stock_on_hand

Design Assumptions

NA

rmse_rpas (RMS-Planning Extract Wrapper Script)

Module Name	rmse_rpas.ksh
Description	Optional Wrapper Script to run all RPAS RETL Extracts
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	NA
Runtime Parameters	NA

Design Overview

The rmse_rpas.ksh script is an optional wrapper that runs all extracts from RMS for RPAS.

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 individually schedule RPAS extract jobs. Some jobs which send stable foundation data can be scheduled ad hoc at any time.

If a client uses this wrapper script, no extraction for RPAS will be performed until the most restrictive dependencies allow it. This may mean a delay in getting any information to RPAS 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:

• rmse_rpas_attributes

• rmse_rpas_daily_sales

• rmse_rpas_domain

• rmse_rpas_item_master

• rmse_rpas_merchhier

• rmse_rpas_orghier

• rmse_rpas_stock_on_hand

• rmse_rpas_store

• rmse_rpas_suppliers

• rmse_rpas_weekly_sales

• rmse_rpas_wh

Scheduling Constraints

Table 28-55 Scheduling Constraints

Schedule Information	Description
Frequency	Optional - If a client uses this wrapper script, no extraction for RPAS will be performed until the most restrictive sub script dependencies allow it. This may mean a delay in getting any information to RPAS so its processing can begin If this script is NOT used, it is possible to get some data to RPAS earlier in the total batch schedule. This may have an impact on when RPAS is able to begin it's batch processing
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Integration Contract

Integration Type	Download from RMS
File Name	See specific rmse_rpas* batch designs
Integration Contract	NA

Design Assumptions

NA

rmsl_rpas_update_retl_date (Update Last RPAS Extract Date)

Module Name	rmsl_rpas_update_retl_date.ksh
Description	Update Last RPAS Extract Date
Functional Area	Integration - RPAS
Module Type	Admin
Module Technology	Ksh
Catalog ID	RMS161
Runtime Parameters	NA

Design Overview

This script updates the RMS RETL extract date on RETL_EXTRACT_DATES table. The program can be run with a run-time parameter of 'CLOSED_ORDER' or 'RECEIVED_QTY' which indicates whether the purchase order closed date or last received date is to be updated.

Scheduling Constraints

Table 28-56 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After all daily RPAS Integration RETL scripts are run
Pre-Processing	pre_rmse_rpas.ksh rmse_rpas.ksh rmse_rpas_attributes.ksh rmse_rpas_daily_sales.ksh rmse_rpas_domain.ksh rmse_rpas_item_master.ksh rmse_rpas_merchhier.ksh rmse_rpas_orghier.ksh rmse_rpas_stock_on_hand.ksh rmse_rpas_store.ksh rmse_rpas_suppliers.ksh rmse_rpas_wh.ksh rmsl_rpas_forecast.ksh rmse_rpas_merchhier.ksh rmse_rpas_item_master.ksh rmse_rpas_orghier.ksh rmse_rpas_store.ksh rmse_rpas_wh.ksh
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-57 Key Tables Affected

Table	Select	Insert	Update	Delete
RETL_EXTRACT_DATES	No	No	Yes	No

Integration Contract

NA

Design Assumptions

NA

soutdnld (Stockout Download)

Module Name	soutdnld.pc
Description	Download of Out Of Stock Items
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS115
Runtime Parameters	NA

Design Overview

A forecasting interface requires a notification whenever an item stock on hand at a store goes to zero or below that level. This soutdnld program loops through the item/store stock on hand table and outputs any item/store combinations that have a stock out condition to an output file. This output file will then be sent to the forecasting system.

Scheduling Constraints

Table 28-58 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	Processing that updates the stock levels should be completed before running this program
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	The forecasting system requires that the output files generated by this program be grouped by domain number. To accommodate this requirement, soutdnld.pc should be threaded by a domain

Restart/Recovery

The logical unit of work for this program is set at item/location level. Table based restart/recovery is used. The commit_max_ctr field should be set to prevent excessive rollback space usage, and to reduce the overhead of the file I/O.

Since threads are determined by the value of the domain ID, the RESTART_PROGRAM_STATUS table should contain a row for each domain ID. The thread value of the domain ID should be used as the thread value on this table. The total number of domains/number of threads should be equal to the number of rows on the RESTART_PROGRAM_STATUS table. This value must be entered into the restart_control table num_threads field. Note that anytime a new domain is created, an additional row should be added to the RESTART_PROGRAM_STATUS table with the thread value equal to the domain ID and the restart_control table num_threads field must be incremented to equal the total number of domains.

Key Tables Affected

Table 28-59 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_MASTER	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	No	No
DOMAIN_DEPT	Yes	No	No	No
DOMAIN_CLASS	Yes	No	No	No
DOMAIN_SUBCLASS	Yes	No	No	No
SUB_ITEMS_DETAIL	Yes	No	No	No
V_PACKSKU_QTY	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	sout%d.dat, where %d is substitued with the department id
Integration Contract	IntCon000036

Output File Layout

The output filename is hardcoded to sout%d.dat where %d is substitued with the department id. Each run of the program can produce multiple output files, one for each department.

Table 28-60 Output File Layout

Field Name	Field Type	Default Value	Description
Date	Char(8)	Period.vdate	The date of the stockout in YYYYMMDD format
Store	Number(10)	NA	The store at which the sku encountered the stockout - left justified with trailing blanks
Item	Char(25)	NA	The item that encountered the stockout - left justified with trailing blanks

Design Assumptions

NA

ftmednld (Download of Time Hierarchy for Planning Systems)

Module Name	ftmednld.pc
Description	Download of Time Hierarchy for Planning Systems
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS15

Design Overview

The FTMEDNLD.PC module downloads the RMS calendar (year, half, quarter, month, week, day, and date) in the 454-calendar format. The download consists of the entire calendar in the RMS. This program accounts for a fiscal year that could be different from the standard year in the CALENDAR table.

As part of the implementation, the extracted flat file needs to be transferred to a location where the planning system (with its transformation script) can access it.

Scheduling Constraints

Table 28-61 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA (Single Thread)

Restart/Recovery

Due to the relatively small amount of processing this program performs; restart recovery will not be used. The calls to retek_init() and retek_close() are used in the program only for logging purposes (to prevent double-runs).

Locking Strategy

NA

Key Tables Affected

Table 28-62 Key Tables Affected

Table	Select	Insert	Update	Delete
CALENDAR	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	rmse_rpas_clndmstr.dat
Integration Contract	IntCon000035

Output File Layout

The file outputted will be named rmse_rpas_clndmstr.dat.

Table 28-63 Output File Layout

Field Name	Field Type	Description
Year	Number(4)	The 4-5-4 year
Half	Number(1)	The 4-5-4 half of the year, valid values are 1 or 2
Quarter	Number(1)	The 4-5-4 quarter of the year, valid values 1-4
Month	Number(2)	The 4-5-4 month of the year, valid values 1-12
Week	Number(2)	The 4-5-4 week of the year, valid values 1-53
Day	Number(1)	The 4-5-4 day of the current week, valid values 1-7
Date	Date	The date from which the 4-5-4 data was derived, in YYYYMMDD format

Design Assumptions

NA

rms_oi_forecast_history.ksh (Retain Item Forecast History)

Module Name	rms_oi_forecast_history.ksh
Description	Retain 4 weeks of Item Forecast History
Functional Area	Item Forecast, Inventory Analyst Report
Module Type	Admin
Module Technology	Ksh
Catalog ID	RMS491
Runtime Parameters	$UP (database connect string)

Design Overview

This batch program preserves 4 weeks of weekly forecasted sales data in ITEM_FORECAST to the ITEM_FORECAST_HISTORY table before ITEM_FORECAST is truncated and refreshed by the rmsl_rpas_forecast.ksh batch program. The data in ITEM_FORECAST_HISTORY is used to support the Inventory Variance to Forecast report in the Inventory Analyst dashboard. If the system is not configured to use this report (for example:. rms_oi_system_options.ia_variance_to_forecast_ind is N), then running this batch job will NOT copy any data to ITEM_FORECAST_HISTORY.

To support potentially large volume of data on ITEM_FORECAST and ITEM_FORECAST_HISTORY, ITEM_FORECAST_HISTORY is interval partitioned by EOW_DATE with a partition interval of 7 days and an interval high value of EOW_DATE+1. EOW_DATE must be a valid EOW_DATE based on calendar type - (4) 454 or (C) Standard Calendar.

Scheduling Constraints

Table 28-64 Scheduling Constraints

Schedule Information	Description
Frequency	Weekly
Scheduling Considerations	Before rmsl_rpas_forecast.ksh weekly runs that truncates the data in ITEM_FORECAST table.
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

Table 28-65 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_FORECAST	Yes	No	No	No
ITEM_FORECAST_HIST	No	Yes	No	Yes

Design Assumptions

NA

rmsl_rpas_forecast (RMS Load of Forecast from RPAS)

Module Name	rmsl_rpas_forecast.ksh
Description	Load Daily/Weekly Forecast from RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS134
Runtime Parameters	NA

Design Overview

This script loads item forecast data into the RMS forecast tables. The forecast data comes from an external planning system such as RPAS.

A run-time parameter of 'daily' or 'weekly' indicates whether the daily or weekly forecast data is being loaded into RMS. If the forecast is a daily forecast, information is written to the DAILY_ITEM_FORECAST table. If the forecast is a weekly forecast, information is written to the ITEM_FORECAST table.

Scheduling Constraints

Table 28-66 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	If the system is configured to use the Inventory Variance to Forecast report in the Inventory Analyst dashboard, run this program after rms_oi_forecast_history.ksh to preserve 4 weeks of item forecast data before truncating it in this
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-67 Key Tables Affected

Table	Select	Insert	Update	Delete
DAILY_ITEM_FORECAST	No	Yes (if run daily)	No	Yes
ITEM_FORECAST	No	Yes (if run weekly)	No	Yes
FORECAST_REBUILD	No	Yes	No	Yes

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000038 (weekly) rmse_rpas_forecast_weekly.schema IntCon000155 (weekly) rmse_rpas_forecast_daily.schema

File Layout

If a run-time parameter of 'weekly' is used, the input file is in fixed-length format matching to the schema definition in rmse_rpas_forecast_weekly.schema:

Table 28-68 File Layout - Run-Time Parameter Weekly

Field Name	Field Type	Required	Description
EOW_DATE	Date(8)	Yes	Item_forecast.eow_date
ITEM	Char(25)	Yes	Item_forecast.item
LOC	Char(20)	Yes	Item_forecast.loc
FORECAST_SALES	Double(14)	Yes	Item_forecast.forecast_sales
FORECAST_STD_DEV	Double(14)	Yes	Item_forecast.forecast_std_dev

If a run-time parameter of 'daily' is used, the input file is in fixed-length format matching to the schema definition in rmse_rpas_forecast_daily.schema:

Table 28-69 File Layout - Run-Time Parameter Daily

Field Name	Field Type	Required	Description
DATA_DATE	Date(8)	Yes	Daily_item_forecast.data_date
ITEM	Char(25)	Yes	Daily_item _forecast.item
LOC	Char(20)	Yes	Daily_item _forecast.loc
FORECAST_SALES	Double(14)	Yes	Daily_item_forecast.forecast_sales
FORECAST_STD_DEV	Double(14)	Yes	Daily_item_forecast.forecast_std_dev

Design Assumptions

NA

fcstprg (Purge Forecast Data)

Module Name	fcstprg.pc
Description	Purge Forecast Data
Functional Area	Interface - Planning
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS227
Runtime Parameters	NA

Design Overview

This program deletes data from the RMS forecast information tables. This program serves to delete data by domains so that they can re-loaded with new forecast information from a forecasting system such as RDF.

Scheduling Constraints

Table 28-70 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	NA
Pre-Processing	prepost fcstprg pre - disables indexes
Post-Processing	prepost fcstprg post - rebuilds indexes
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

Table 28-71 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_FORECAST	No	No	No	Yes
DEPT_SALES_FORECAST	No	No	No	Yes
CLASS_SALES_FORECAST	No	No	No	Yes
SUBCLASS_SALES_FORECAST	No	No	No	Yes

Design Assumptions

NA

rmse_rdf_daily_sales (Extract of Daily Sales of Forecasted Items for RPAS)

Module Name	Rmse_rdf_daily_sales.ksh
Description	Extract of Daily Sales of Forecasted Items for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS08
Runtime Parameters	NA

Design Overview

This script extracts from RMS item's daily sales information at a location for RMS integration with an external planning system, for example RDF. Only forecastable items are extracted. For a store, the sales data represents the net sales (gross sales - returns); for a warehouse, the sales data represents the stock transferred out of the warehouse.

Each client can customize the variable USE_IF_TRAN_DATA in this script to choose whether the sales data should come from IF_TRAN_DATA table or TRAN_DATA_HISTORY table.

Scheduling Constraints

Table 28-72 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After saldly.pc After pre_rmse_rpas.ksh
Pre-Processing	pre_rmse_rpas.ksh, saldly.pc
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-73 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_MASTER	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	No	No
IF_TRAN_DATA	Yes	No	No	No
TRAN_DATA_HISTORY	Yes	No	No	No
DOMAIN_DEPT	Yes	No	No	No
DOMAIN_CLASS	Yes	No	No	No
DOMAIN_SUBCLASS	Yes	No	No	No
SUB_ITEMS_DETAIL	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000087 rmse_rdf_daily_sales.schema

File Layout

Table 28-74 File Layout

Field Name	Field Type	Required	Description
LOC	Integer(11)	Yes	Item_loc_soh.loc
ITEM	Char(25)	No	If_tran_data.item or tran_data_history.item
TRAN_DATE	Date(8)	Yes	If_tran_data.tran_date or tran_data_history.tran_date
SUM_UNITS	Double(14)	No	If_tran_data.units or tran_data_history.units
SALES_TYPE	Char(1)	No	If_tran_data.sales_type or tran_data_history.sales_type
TRAN_CODE	Integer(3)	Yes	If_tran_data.tran_code or tran_data_history.tran_code
DOMAIN_ID	Integer(3)	Yes	Domain_dept.domain_id or domain_class.domain_id or domain_subclass.domain_id

Design Assumptions

NA

rmse_rdf_weekly_sales (Extract of Weekly Sales of Forecasted Items for RPAS)

Module Name	rmse_rdf_weekly_sales.ksh
Description	Extract of Weekly Sales of Forecasted Items for RPAS
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS09
Runtime Parameters	NA

Design Overview

This script extracts item weekly sales information at a location for interfacing to an external planning system, such as RDF. Only forecastable items are extracted. This extract will contain only weeks that have yet to be extracted. Once the extract is completed this process with execute the rmsl_rpas_update_last_hist_exp_date.ksh script to update the last export date for any extracted item/locations which is used for subsequent extracts.

Scheduling Constraints

Table 28-75 Scheduling Constraints

Schedule Information	Description
Frequency	Weekly
Scheduling Considerations	After hstwkupd.pc After salweek.pc After pre_rmse_rpas.ksh
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-76 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_MASTER	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	No	No
ITEM_LOC_HIST	Yes	No	No	No
PERIOD	Yes	No	No	No
DOMAIN_DEPT	Yes	No	No	No
DOMAIN_CLASS	Yes	No	No	No
DOMAIN_SUBCLASS	Yes	No	No	No
SUB_ITEMS_DETAIL	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000096 rmse_rdf_weekly_sales.schema

Output File Layout

Table 28-77 Output File Layout

Field Name	Field Type	Required	Description
ITEM	Char(25)	Yes	Item_master.item
LOC	Integer(11)	Yes	Item_loc_soh.loc
EOW_DATE	Date(8)	No	Item_loc_hist.eow_date in YYYYMMDD format
SALES_ISSUES	Double(18)	No	Item_loc_hist.sales_issues
SALES_TYPE	Char(1)	Yes	Item_loc_hist.sales_type
ROW_ID	Char(18)	No	Item_loc_soh.row_id
DOMAIN_ID	Integer(3)	Yes	Domain_dept.domain_id or domain_class.domain_id or domain_subclass.domain_id

Design Assumptions

NA

rmse_mfp_inventory (Extract of Inventory Aggregation for MFP)

Module Name	rmse_mfp_inventory.ksh
Description	Extract of Inventory Aggregation for MFP
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS106
Runtime Parameters	NA

Design Overview

The purpose of this batch module is to extract the item inventory aggregates for the integrated Oracle Retail Predictive Application Server (RPAS) application. MFP refers to the Merchandise Financial Planning component.

Scheduling Constraints

Table 28-78 Scheduling Constraints

Schedule Information	Description
Frequency	Weekly
Scheduling Considerations	In normal processing, this program is run weekly (with the input parameter W-'W'eekly load) However, it is also possible to run this program for an intial load of aggregated inventory for MFP (using the input parameter I - 'Initial Load)
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-79 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
TRAN_DATA_HISTORY	Yes	No	No	No
CALENDAR	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No
STORE	Yes	No	No	No
WH	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
DEPS	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	rmse_mfp_inventory.W.dat
Integration Contract	IntCon000100

Output File Layout

Table 28-80 Output File Layout

Field Name	Field Type	Description
EOW_DATE	Date	Indicates the date of the end of week cycle
ITEM	VARCHAR2(25)	Item number in the item
LOCATION	NUMBER(10)	Location number
CLEARANCE_INVENTORY_UNITS	NUMBER(25)	NA
CLEARANCE_INVENTORY_COST	NUMBER(25)	NA
CLEARANCE_INVENTORY_RETAIL	NUMBER(25)	NA
REGULAR_INVENTORY_UNITS	NUMBER(25)	NA
REGULAR_INVENTORY_COST	NUMBER(25)	NA
REGULAR_INVENTORY_RETAIL	NUMBER(25)	NA
RECEIPT_UNITS	NUMBER(25)	NA
RECEIPT_COST	NUMBER(25)	NA
RECEIPT_RETAIL	NUMBER(25)	NA

Design Assumptions

NA

rmse_mfp_onorder (Extract of On Order for MFP)

Module Name	rmse_mfp_onorder.ksh
Description	Extract of On Order for MFP
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS107
Runtime Parameters	NA

Design Overview

The purpose of this batch module is to extract on-order units, cost, and retail values from RMS for the integrated Oracle Retail Predictive Application Server (RPAS) application. MFP refers to the Merchandise Financial Planning component. Data is extracted at the Parent Item / Diff Aggregate level.

Scheduling Constraints

Table 28-81 Scheduling Constraints

Schedule Information	Description
Frequency	Weekly
Scheduling Considerations	This program must be run after core RMS inventory processing
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

This is a standard Oracle Retail RETL script. No restart/recovery is used.

Key Tables Affected

Table 28-82 Key Tables Affected

Table	Select	Insert	Update	Delete
STORE	Yes	No	No	No
WH	Yes	No	No	No
ORDHEAD	Yes	No	No	No
ORDSKU	Yes	No	No	No
ORDLOC	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
ITEM_LOC	Yes	No	No	No
ITEM_SUPP_COUNTRY	Yes	No	No	No
DEPS	Yes	No	No	No
V_PACKSKU_QTY	Yes	No	No	No
VAT_ITEM	Yes	No	No	No
CLASS	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No
GTAX_ITEM_ROLLUP	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	rmse_mfp_onorder.dat
Integration Contract	IntCon000101

Output File Layout

Table 28-83 Output File Layout

Field Name	Field Type	Required	Description
EOW_DATE	Date	Yes	Indicates the date of the end of week cycle
ITEM	VARCHAR2(25)	Yes	Item number - will contain the parent item ID with the aggregated diff ID value(s) concatenated
LOCATION	NUMBER(10)	Yes	Location number of the order
ON_ORDER_UNITS	NUMBER(12)	Yes	Indicates the total quantity of the item in the order
ON_ORDER_COST	NUMBER(20,4)	Yes	Unit cost of the item
ON_ORDER_RETAIL	NUMBER(20,4)	Yes	Retail price of the item

Design Assumptions

NA

onictext (On Inter-Company Transfer Exhibit)

Module Name	onictext.pc
Description	On Inter-Company Transfer Exhibit
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS128

Design Overview

This program calculates the value in cost and retail of items that are on intercompany transfers. It calculates the on order cost and retail for all approved intercompany transfers that have exp_dc_eow_dates less than or equal to the planning horizon date. Once the program has calculated the costs and retails, they are inserted into the ON_ORDER_TEMP table.

This program takes in a small input file. The input file determines if the run should be for weekly or historical data.

Scheduling Constraints

Table 28-84 Scheduling Constraints

Schedule Information	Description
Frequency	Weekly
Scheduling Considerations	Note that program can be run ad hoc for a historical extract, but is generally run weekly
Pre-Processing	onordext
Post-Processing	onorddnld
Threading Scheme	Threaded by Transfer number

Restart/Recovery

The logical unit of work is unique transfer number. Each time the record counter equals the maximum recommended commit number the retek_commit function is called. The program is multithreaded using v_restart_transfer view.

Locking Strategy

NA

Security Considerations

NA

Performance Considerations

NA

Key Tables Affected

Table 28-85 Key Tables Affected

Table	Select	Insert	Update	Delete
STORE	Yes	No	No	No
WH	Yes	No	No	No
TSF_ITEM_COST	Yes	No	No	No
TSFHEAD	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	No	No
ITEM_LOC	Yes	No	No	No
TSF_XFORM	Yes	No	No	No
TSF_XFORM_DETAIL	Yes	No	No	No
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
ON_ORDER_TEMP	Yes	No	No	No

Integration Contract

Integration Type	Input File for RMS from Planning
File Name	Determined by runtime parameter
Integration Contract	IntCon000026

File Layout

Table 28-86 File Layout

Field Name	Field Type	Description
Weekly or historic indicator	Char (1)	Weekly or Historic indicator
Planning horizon start date	Date (8)	Planning start date in YYYYMMDD format
Planning Horizon end date	Date(8)	Planning end date in YYYYMMDD format

Integration Contract

Integration Type	Download from RMS
File Name	NA
Integration Contract	IntCon000028

Staging Table

Table 28-87 Staging Table

Staging Table	Description
ON_ORDER_TEMP	See the RMS data model for more details about the staging table structure.

onordext (On Order Extract)

Module Name	onordext.pc
Description	On Order Extract
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS129

Design Overview

This program calculates the value in cost and retail of items that are on order for the department/class/subclass/location level. This program is the first step in the stock ledger download process to RPAS. It calculates the on order cost and retail for all approved orders that have not before dates less than or equal to the planning horizon date. Once the program has calculated the costs and retails, they are inserted into the ON_ORDER_TEMP table. Customer Order POs are filtered out and will not affect the on order quantity that is sent to RPAS.

Scheduling Constraints

Table 28-88 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	before onictext
Pre-Processing	Run prepost onordext pre program
Post-Processing	onictext
Threading Scheme	Threaded by Order number

Restart/Recovery

The logical unit of work is unique order number. Each time the record counter equals the maximum recommended commit number the retek_commit function is called.

It is also split into two sections item and pack. First all items on orders are processed. When they are done a pack 'flag' is turned on and the restart order is reset. Then all the packs on order are processed. So all orders are considered twice, once for items and once for packs.

Locking Strategy

NA

Security Considerations

NA

Performance Considerations

NA

Key Tables Affected

Table 28-89 Key Tables Affected

Table	Select	Insert	Update	Delete
ORDHEAD	Yes	No	No	No
ORDLOC	Yes	No	No	No
ORDSKU	Yes	No	No	No
ALLOC_HEADER	Yes	No	No	No
ALLOC_DETAIL	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
ITEM_SUPP_COUNTRY_LOC	Yes	No	No	No
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
CLASS	Yes	No	No	No
ON_ORDER_TEMP	No	Yes	No	No
DEFAULT_TAX_TYPE	Yes	No	No	No
VAT_REGION	Yes	No	No	No
WH	Yes	No	No	No
VAT_ITEM	Yes	No	No	No
ITEM_LOC	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	No	No
ITEM_SUPPLIER	Yes	No	No	No
UOM_CLASS	Yes	No	No	No
UOM_CONVERSION	Yes	No	No	No
ITEM_SUPP_UOM	Yes	No	No	No

Integration Contract

Integration Type	Input File for RMS from Planning
File Name	Determined by runtime parameter
Integration Contract	IntCon000026

Table 28-90 File Layout

Staging Table	Description
Weekly or historic indicator	Weekly or historic indicator.
Planning horizon start date	Planning start date in YYYYMMDD format.
Planning horizon end date	Planning end date in YYYYMMDD format.

Integration Contract

Integration Type	Download from RMS
File Name	NA
Integration Contract	IntCon000028

Table 28-91 Staging Table

Staging Table	Description
ON_ORDER_TEMP	See the RMS data model for more details about the staging table structure.

gradupld (Upload of Store Grade Classifications from RPAS)

Module Name	gradupld.pc
Description	Upload of Store Grade Classifications from RPAS
Functional Area	Integration - RPAS
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS133
Runtime Parameter	NA

Design Overview

The store grade upload module is designed to load forecasting-driven store grades into RMS. Data will be loaded into the STORE_GRADE_GROUP, STORE_GRADE and STORE_GRADE_STORE tables.

Scheduling Constraints

Table 28-92 Scheduling Constraints

Schedule Information	Description
Frequency	As Needed
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA - File-based processing

Restart/Recovery

Oracle Retail standard restart/recovery is used. The commit_max_ctr field should be set to prevent excessive rollback space usage, and to reduce the overhead of file I/O. The recommended commit counter setting is 1000 records (subject to change based on implementation).

Key Tables Affected

Table 28-93 Key Tables Affected

Table	Select	Insert	Update	Delete
Buyer	Yes	No	No	No
Store	Yes	No	No	No
Store_grade_group	Yes	Yes	No	No
Store_grade	Yes	Yes	No	No
Store_grade_store	Yes	Yes	Yes	No
ORDLOC_WKSHT	No	No	No	Yes

Integration Contract

Integration Type	Upload to RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000037

The input filename is not fixed; the input filename is determined by a runtime parameter. Records rejected by the import process are written to a reject file. The reject filename is not fixed; the reject filename is determined by a runtime parameter.

Input File Layout

The input file should be sorted by grade group description, grade ID, and grade store. The grade group description should be unique by grade group ID.

Table 28-94 Input File Layout

Record Name	Field Name	Field Type	Default Value	Description
FHEAD	Record type	Char(5)	FHEAD	Record Identifier
	Line ID	Number(10)	0000000001	Line Sequence Identifier
	File name	Char(5)	GRADU	File Identifier
FDETL	Record type	Char(5)	FDETL	Record Identifier
	Line id	Number(10)	NA	Line Sequence Identifier
	Grade Group ID	Number(8)	NA	Valid Grade Group ID
	Grade Group	Char(120)	NA	Valid Grade Group
	Grade store	Number(10)	NA	Valid Grade store
	Grade ID	Number(10)	NA	Valid Grade ID
	Grade name	Char(120)	NA	Valid Grade name
FTAIL	Record Type	Char(5)	FTAIL	Record Identifier
	Line id	Number(10)	NA	Line Sequence Identifier
	Line Total	Number(10)	NA	Total number of FDETL lines in the file.

Design Assumptions

NA

onorddnld (On Order Download to Financial Planning)

Module Name	ONORDDNLD.PC
Description	On Order Download to Financial Planning
Functional Area	Integration - Planning
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS12

Design Overview

This program sends on order cost, retail and quantity at the item/location/week level to a planning system. The values are used by a financial planning system to generate OTB numbers that are interfaced back into the RMS.

This program creates three output files: one for orders, one for intercompany transfer sending locations and one for intercompany transfer receiving locations.

Scheduling Constraints

Table 28-95 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This program is run towards end of the batch schedule after the ONORDEXT.PC (on order extract) and ONICTEXT.PC
Pre-Processing	onordext.pc, onictext.pc
Post-Processing	NA
Threading Scheme	Threaded by location

Restart/Recovery

The logical unit of work for this program is set at item/location/eow_date level. Table based restart/recovery must be used. The commit_max_ctr field should be set to prevent excessive rollback space usage, and to reduce the overhead of file I/O. The recommended commit counter setting is 1000 records (subject to change based on implementation).

Locking Strategy

NA

Security Considerations

NA

Performance Considerations

NA

Key Tables Affected

Table 28-96 Key Tables Affected

Table	Select	Insert	Update	Delete
ON_ORDER_TEMP	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	The filename is hardcoded to onorder.dat%d, onictsend.dat%d, or onictrcv.dat%d where %d is substituted with the domain ID
Integration Contract	IntCon000027

Output File Layout

Each run of the program can produce multiple output files, one for each domain.

Table 28-97 Output File Layout

Field Name	Field Type	Description
ITEM	Char(25)	RMS ITEM Identifier.
Location (Store / WH)	NUMBER(20)	Store or WH identifier.
Location Type ('S' or 'W')	Char(1)	Indicates if the location is a store or a warehouse: S - if the location is a store, W - If the location is a warehouse.
OTB EOW date	DATE (8)	The OTB End of week date.
On Order Retail	NUMBER(25,4)	Total on order retail for the item/location/EOW date.
On order Cost	NUMBER(25,4)	Total on order cost for the item/location/EOW date.
On Order Quantity	NUMBER(17,4)	Total on order Quantity for the item/location/EOW date.