28 Integration with Oracle Retail Planning

Oracle Retail's Merchandise Financial Planning (MFP) and Retail Predictive Forecasting (RDF) cloud services rely on the Merchandising Foundation Cloud Service to provide critical foundation and transactional information. Because both MFP and RDF are built on the Retail Predictive Application Server (RPAS), there are some common programs from RMS that provide this information. These integration processes are 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 My Oracle Support).

Foundation Data vs Transaction/Inventory Data

MFP and RDF require both foundation and transaction data from RMS.

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.

Integration Program Summary

Table 28-1 Integration Program Summary

Program	Description
rms_oi_forecast_history.ksh	Retain Item Forecast History
fcstprg.pc	Purge Forecast Data
load_item_forecast.ksh	RMS load of weekly or daily item forecast from RDF or a 3rd party forecasting application
BDI_RPAS_Calendar_Fnd_PF_From_RMS_JOB	Calendar Bulk Extract to RPAS
BDI_RPAS_CurrConvRates_Fnd_PF_From_RMS_JOB	Currency Rates Bulk Extract to RPAS
BDI_RPAS_MerchHier_Fnd_PF_From_RMS_JOB	Merchandise Hierarchy and Item Bulk Data Extract to RPAS
BDI_RPAS_OrgHier_Fnd_PF_From_RMS_JOB	Organization Hierarchy Bulk Data Extract to RPAS
BDI_RPAS_Store_Fnd_PF_From_RMS_JOB	Store Bulk Data Extract to RPAS
BDI_RDF_UdaItemLov_Fnd_From_RMS_JOB	UDA Item LOV Bulk Data Extract to RDF
BDI_RDF_StockOut_Tx_PF_From_RMS_JOB	Extracts information for items which are out of stock for use by the RDF application.
BDI_RDF_WeeklySales_Tx_PF_From_RMS_JOB	Extracts weekly sales information for use by the RDF application.
BDI_MFP_Inventory_Tx_PF_From_RMS_JOB	Inventory Bulk Extract to MFP
BDI_MFP_OnOrder_Tx_PF_From_RMS_JOB	OnOrder Bulk Extract to MFP
BDI_MFP_TranData_Tx_PF_From_RMS_JOB	Transaction Data Bulk Extract to MFP

For additional details on integrating RMS with MFP and RDF, the Oracle Retail Merchandise Financial Planning Operations Guide provides information from the point of view of MFP and RDF.

The following processes support this integration.

Merchandise Hierarchy and Item Extract to Planning and Forecasting
(BDI_RPAS_MerchHier_Fnd_PF_From_RMS_JOB)

Module Name	BDI_RPAS_MerchHier_Fnd_PF_From_RMS_JOB bdi_merch_extract_to_file_wrapper.sh bdi_rpas_merchhier_extract.ksh
Description	Extracts merchandise hierarchy and item information to RPAS from RMS
Functional Area	Foundation
Module Type	Integration
Module Technology	BDI job, shell scripts
Catalog ID	NA
Runtime Parameters	ItemHdrAndMerchHier_Fnd_ProcessFlow_From_RMS ItemHdr_Fnd_Extractor Database connection, download file location, filename, trigger filename

Design Overview

RMS extracts its merchandise hierarchy and item data to MFP and RDF on a weekly basis. It utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from RMS to MFP and RDF.

The batch job BDI_RPAS_MerchHier_Fnd_PF_From_RMS_JOB is defined in the RMS JOS batch job admin as follows:

<job id="BDI_RPAS_MerchHier_Fnd_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
    <properties>
        <property name="description" value="Extracts Merch Hierarchy information and writes it out to a flat file for processing by both MFP and RDF."/>
    </properties>
    <step id="batchlet-step">
        <batchlet ref="BDIInvokerBatchlet">
            <properties>
                <property name="bdiProcessFlowUrl" value="#SysOpt.bdiProcessFlowUrl"/>
                <property name="bdiProcessFlowCredential" value="#SysOpt.bdiProcessFlowUrlUserAlias"/>
                <property name="predicateDS" value="RmsDBDS"/>
                <property name="predicateFunction" value="RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL"/>
            </properties>
        </batchlet>
        <end on="COMPLETED"/>
    </step>
</job>

When the batch job BDI_RPAS_MerchHier_Fnd_PF_From_RMS_JOB is executed, a batchlet (BDIInvokerBatchlet) starts the execution flow. It calls a PLSQL function (RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL) to ensure the process flow is only executed on an EOW_DATE. If vdate is an EOW_DATE, it invokes a BDI process flow (ItemHdrAndMerchHier_Fnd_ProcessFlow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications (MFP and RDF):

Extractor jobs (MerchHier_Fnd_Extractor, ItemHdr_Fnd_Extractor) call respective BDI_MERCH_SQL and BDI_ITEM_SQL functions to extract data from RMS tables to BDI outbound staging tables MERCH_HIER_OUT and ITEM_HDR_OUT.
Downloader file creator job calls the wrapper script, bdi_merch_extract_to_file_wrapper.sh, to set the runtime parameters on environment variables. This script will then call bdi_rpas_merchhier_extract.ksh to write merchandise hierarchy and item information from the MERCH_HIER_OUT and ITEM_HDR_OUT tables into a comma-delimited flat file, which will be consumed by RDF and MFP. A zero-byte trigger file is also generated to signal that the extract process was successful. Two separate copies of the data file and the trigger file are sent to MFP, RDF, AP and IP.
- Merchandise Hierarchy information extracted are those related to items.
- Records for inventoried, sellable, non-pack transaction items will be extracted.
The downloaded data files and trigger files are written to designated MFP and RDF locations as configured via BDI system options MFP_outboundLocation and RDF_outboundLocation.

Scheduling Constraints

Table 28-2 Scheduling Constraints

Schedule Information	Description
Processing Cycle	End of Day
Frequency	Scheduled daily but files will only be generated weekly on End of Week date.
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 28-3 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
ITEM_MASTER	Yes	No	No	No
DIFF_GROUP_HEAD	Yes	No	No	No
DIFF_IDS	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
MERCH_HIER_OUT	Yes	Yes	No	Yes
ITEM_HDR_OUT	Yes	Yes	No	Yes
BDI_DWNLDR_IFACE_MOD_DATA_CTL	Yes	No	No	No
BDI_DWNLDR_IFACE_DATA_CTL	Yes	No	No	No

Integration Contract

The flat file will contain the following information:

Table 28-4 Flat File

Field Name	Field Type	Required	Description
ITEM	Char(25)	Yes	N/A
ITEM_DESC	Char(250)	Yes	N/A
ITEM_PARENT_DIFF	Char(30)	Yes	Concatenated value consisting of item parent ID with the letter 'A' at the end.
ITEM_PARENT_DIFF_DESC	Char(250)	Yes	Description of the item parent diff. Concatenated value consisting of the item parent description and the diff IDs for all diffs associated to the parent which is marked as aggregate.
ITEM_PARENT	Char(25)	Yes	N/A
ITEM_PARENT_DESC	Char(250)	Yes	N/A
SUBCLASS_ID	Number(10)	Yes	Unique subclass ID
SUBCLASS_NAME	Char(120)	Yes	Concatenated value consisting of subclass number with name.
CLASS_ID	Number(10)	Yes	Unique class ID
CLASS_NAME	Char(120)	Yes	Concatenated value consisting of class number with name.
DEPT	Number(4)	Yes	N/A
DEPT_NAME	Char(120)	Yes	N/A
GROUP_NO	Number(4)	Yes	N/A
GROUP_NAME	Char(120)	Yes	N/A
DIVISION	Number(4)	Yes	N/A
DIV_NAME	Char(120)	Yes	N/A
COMPANY	Number(4)	Yes	N/A
COMPANY_NAME	Char(120)	Yes	N/A
FORECAST_IND	Char(1)	Yes	N/A

Organizational Hierarchy Extract to Planning and Forecasting
(BDI_RPAS_OrgHier_Fnd_PF_From_RMS_JOB)

Module Name	BDI_RPAS_OrgHier_Fnd_PF_From_RMS_JOB bdi_merch_extract_to_file_wrapper.sh bdi_rpas_orghier_extract.ksh
Description	Extracts org hierarchy information to RPAS from RMS
Functional Area	Foundation
Module Type	Integration
Module Technology	BDI job, shell scripts
Catalog ID	NA
Runtime Parameters	StoreAndWhAndOrgHier_Fnd_ProcessFlow_From_RMS Store_Fnd_Extractor Wh_Fnd_Extractor OrgHier_Fnd_Extractor Database connection, download file location, filename, trigger filename

Design Overview

RMS extracts its organization hierarchy data to MFP and RDF on a weekly basis. It utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from RMS to MFP and RDF.

The batch job BDI_RPAS_OrgHier_Fnd_PF_From_RMS_JOB is defined in the RMS JOS batch job admin as follows:

<job id="BDI_RPAS_OrgHier_Fnd_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
    <properties>
        <property name="description" value="Extracts Org Hierarchy information and writes it out to a flat file for processing by both MFP and RDF."/>
    </properties>
    <step id="batchlet-step">
        <batchlet ref="BDIInvokerBatchlet">
            <properties>
                <property name="bdiProcessFlowUrl" value="#SysOpt.bdiProcessFlowUrl"/>
                <property name="bdiProcessFlowCredential" value="#SysOpt.bdiProcessFlowUrlUserAlias"/>
                <property name="predicateDS" value="RmsDBDS"/>
                <property name="predicateFunction" value="RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL"/>
            </properties>
        </batchlet>
        <end on="COMPLETED"/>
    </step>
</job>

When the batch job BDI_RPAS_OrgHier_Fnd_PF_From_RMS_JOB is executed, a batchlet (BDIInvokerBatchlet) starts the execution flow. It calls a PLSQL function (RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL) to ensure the process flow is only executed on an EOW_DATE. If vdate is an EOW_DATE, it invokes a BDI process flow (StoreAndWhAndOrgHier_Fnd_ProcessFlow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications (MFP and RDF):

Extractor jobs (Store_Fnd_Extractor, Wh_Fnd_Extractor, OrgHier_Fnd_Extractor) call respective BDI_ORG_SQL functions to extract data from RMS tables to BDI outbound staging tables ORG_HIER_OUT, STORE_OUT, and WH_OUT.
Downloader file creator job calls the wrapper script, bdi_merch_extract_to_file_wrapper.sh, to set the runtime parameters on environment variables. This script will then call bdi_rpas_orghier_extract.ksh to write organization hierarchy information from the ORG_HIER_OUT, STORE_OUT, and WH_OUT tables into a comma-delimited flat file, which will be consumed by RDF and MFP. A zero-byte trigger file is also generated to signal that the extract process was successful. Two separate copies of the data file and the trigger file are sent to MFP and RDF.
The downloaded data files and trigger files are written to designated MFP and RDF locations as configured via BDI system options MFP_outboundLocation and RDF_outboundLocation.

Scheduling Constraints

Table 28-5 Scheduling Constraints

Schedule Information	Description
Processing Cycle	End of Day
Frequency	Scheduled daily but files will only be generated weekly on End of Week date.
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 28-6 Key Tables Affected

Table	Select	Insert	Update	Delete
STORE	Yes	No	No	No
WH	Yes	No	No	No
AREA	Yes	No	No	No
CHAIN	Yes	No	No	No
DISTRICT	Yes	No	No	No
REGION	Yes	No	No	No
COMPHEAD	Yes	No	No	No
CHANNELS	Yes	No	No	No
CODE_DETAIL	Yes	No	No	No
STORE_FORMAT	Yes	No	No	No
LANG	Yes	No	No	No
VAT_REGION	Yes	No	No	No
TSFZONE	Yes	No	No	No
ORG_HIER_OUT	Yes	Yes	No	Yes
STORE_OUT	Yes	Yes	No	Yes
WH_OUT	Yes	Yes	No	Yes
BDI_DWNLDR_IFACE_MOD_DATA_CTL	Yes	No	No	No
BDI_DWNLDR_IFACE_DATA_CTL	Yes	No	No	No

Integration Contract

The flat file will contain the following information:

Table 28-7 Flat File

Field Name	Field Type	Required	Description
LOCATION	Number(10)	Yes	Store or warehouse ID
LOC_NAME	Char(150)	Yes	Store or warehouse name
DISTRICT	Number(10)	Yes	For warehouses, repeat the warehouse ID
DISTRICT_NAME	Char(120)	Yes	For warehouses, repeat the warehouse name
REGION	Number(10)	Yes	For warehouses, repeat the warehouse ID
REGION_NAME	Char(120)	Yes	For warehouses, repeat the warehouse name
AREA	Number(10)	Yes	For warehouses, repeat the warehouse ID
AREA_NAME	Char(120)	Yes	For warehouses, repeat the warehouse name
CHAIN	Number(10)	Yes	For warehouses, repeat the warehouse ID
CHAIN_NAME	Char(120)	Yes	For warehouses, repeat the warehouse name
COMPANY	Number(4)	Yes	Company ID
COMPANY_NAME	Char(120)	Yes	Company name
COMPANY_CURRENCY	Char(3)	Yes	Currency code
LOC_TYPE	Char(1)	Yes	'S' for store, 'W' for warehouse
LOC_TYPE_NAME	Char(120)	Yes	Store or Warehouse depending on location type
PHYSICAL_WH	Number(10)	Yes	Physical warehouse for warehouse, repeat store ID for store
PHYSICAL_WH_NAME	Char(120)	Yes	Physical warehouse name for warehouse, repeat store name for stores
CHANNEL_ID	Number(4)	Yes	Will be used as fulfillment type alternate in MFP
CHANNEL_NAME	Char(120)	Yes	Channel name

Store Bulk Data Extract to RPAS Planning and Forecasting (BDI_RPAS_Store_Fnd_PF_From_RMS_JOB)

Module Name	BDI_RPAS_Store_Fnd_PF_From_RMS_JOB bdi_merch_extract_to_file_wrapper.sh bdi_rpas_store_extract.ksh
Description	Extracts store information to RPAS from RMS
Functional Area	Foundation
Module Type	Integration
Module Technology	BDI, shell scripts
Catalog ID	NA
Runtime Parameters	Store_Fnd_ProcessFlow_From_RMS Store_Fnd_Extractor Database connection, download file location, filename, trigger filename

Design Overview

RMS extracts its store data to MFP and RDF on a weekly basis. It utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from RMS to MFP and RDF.

The batch job BDI_RPAS_Store_Fnd_PF_From_RMS_JOB is defined in the RMS JOS batch job admin as follows:

<job id="BDI_RPAS_Store_Fnd_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
    <properties>
        <property name="description" value="Extracts store information and writes it out to a flat file for processing by both MFP and RDF."/>
    </properties>
    <step id="batchlet-step">
        <batchlet ref="BDIInvokerBatchlet">
            <properties>
                <property name="bdiProcessFlowUrl" value="#SysOpt.bdiProcessFlowUrl"/>
                <property name="bdiProcessFlowCredential" value="#SysOpt.bdiProcessFlowUrlUserAlias"/>
                <property name="predicateDS" value="RmsDBDS"/>
                <property name="predicateFunction" value="RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL"/>
            </properties>
        </batchlet>
        <end on="COMPLETED"/>
    </step>
</job>

When the batch job BDI_RPAS_Store_Fnd_PF_From_RMS_JOB is executed, a batchlet (BDIInvokerBatchlet) starts the execution flow. It calls a PLSQL function (RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL) to ensure the process flow is only executed on an EOW_DATE. If vdate is an EOW_DATE, it invokes a BDI process flow (Store_Fnd_ProcessFlow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications (MFP and RDF):

Extractor job (Store_Fnd_Extractor) calls BDI_ORG_SQL.STORE_UP function to extract data from RMS tables to BDI outbound staging table STORE_OUT.
Downloader file creator job calls the wrapper script, bdi_merch_extract_to_file_wrapper.sh, to set the runtime parameters on environment variables. This script will then call bdi_rpas_store_extract.ksh to write store information from the STORE_OUT table into a comma-delimited flat file, which will be consumed by RDF and MFP. A zero-byte trigger file is also generated to signal that the extract process was successful. Two separate copies of the data file and the trigger file are sent to MFP and RDF.
The downloaded data files and trigger files are written to designated MFP and RDF locations as configured via BDI system options MFP_outboundLocation and RDF_outboundLocation.

Scheduling Constraints

Table 28-8 Scheduling Constraints

Schedule Information	Description
Processing Cycle	End of Day
Frequency	Scheduled daily but files will only be generated weekly on End of Week date.
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 28-9 Key Tables Affected

Table	Select	Insert	Update	Delete
STORE	Yes	No	No	No
CHANNELS	Yes	No	No	No
CODE_DETAIL	Yes	No	No	No
STORE_FORMAT	Yes	No	No	No
LANG	Yes	No	No	No
VAT_REGION	Yes	No	No	No
TSFZONE	Yes	No	No	No
STORE_OUT	Yes	Yes	No	Yes
BDI_DWNLDR_IFACE_MOD_DATA_CTL	Yes	No	No	No
BDI_DWNLDR_IFACE_DATA_CTL	Yes	No	No	No

Integration Contract

The flat file will contain the following information:

Table 28-10 Flat File

Field Name	Field Type	Required	Description
STORE	Number(10)	Yes	Store ID
STORE_NAME	Char(150)	Yes	Store name
DISTRICT	Number(10)	Yes	District in which the store is a member
STORE_CLOSE_DATE	DATE	Yes	Date on which the store closed
STORE_OPEN_DATE	DATE	Yes	Date on which the store opened
REMODEL_DATE	DATE	Yes	Date on which the store was last remodeled
STORE_CLASS	Char(1)	Yes	Class of which the store is a member
STORE_CLASS_DESCRIPTION	Char(250)	Yes	Store class description
STORE_FORMAT	Number(4)	Yes	Store format
STORE_FORMAT_NAME	Char(60)	Yes	Store format name
CURRENCY	Char(3)	Yes	Currency under which the store operates
STORE_TYPE	Char(6)	Yes	Indicates whether the store is a franchise or company store
STOCKHOLDING_IND	Char(1)	Yes	Indicates whether the store can hold stock

Calendar Extract to Planning and Forecasting
(BDI_RPAS_Calendar_Fnd_PF_From_RMS_JOB)

Note:

This module replaces the ftmednld.pc module from previous releases.

Module Name	BDI_RPAS_Calendar_Fnd_PF_From_RMS_JOB
Description	Extracts calendar information to RPAS from RMS
Functional Area	Foundation
Module Type	Integration
Module Technology	BDI job
Catalog ID	NA
Runtime Parameters	Calendar_Fnd_ProcessFlow_From_RMS Calendar_Fnd_Extractor

Design Overview

RMS extracts its calendar data to RPAS (including MFP and RDF) on a weekly basis. It utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from RMS to MFP and RDF.

The batch job BDI_RPAS_Calendar_Fnd_PF_From_RMS_JOB is defined in the RMS JOS batch job admin as follows:

<job id="BDI_RPAS_Calendar_Fnd_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
    <properties>
        <property name="description" value="Extracts calendar information and writes it out to a flat file for processing by both MFP and RDF."/>
    </properties>
    <step id="batchlet-step">
        <batchlet ref="BDIInvokerBatchlet">
            <properties>
                <property name="bdiProcessFlowUrl" value="#SysOpt.bdiProcessFlowUrl"/>
                <property name="bdiProcessFlowCredential" value="#SysOpt.bdiProcessFlowUrlUserAlias"/>
                <property name="predicateDS" value="RmsDBDS"/>
                <property name="predicateFunction" value="RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL"/>
            </properties>
        </batchlet>
        <end on="COMPLETED"/>
    </step>
</job>

When the batch job BDI_RPAS_Calendar_Fnd_PF_From_RMS_JOB is executed, a batchlet (BDIInvokerBatchlet) starts the execution flow. It calls a PLSQL function (RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL) to ensure the process flow is only executed on an EOW_DATE. If vdate is an EOW_DATE, it invokes a BDI process flow (Calendar_Fnd_ProcessFlow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications (MFP and RDF):

Extractor job (Calendar_Fnd_Extractor) calls BDI_FOUNDATION_SQL.CALENDAR_UP function to extract data from RMS view V_BDI_DAY_LEVEL_CALENDAR to BDI outbound staging table CALENDAR_OUT.
A generic BDI Downloader file creator job writes calendar information from the CALENDAR_OUT table into a comma-delimited flat file, which will be consumed by RDF and MFP. A zero-byte trigger file is also generated to signal that the extract process was successful. Two separate copies of the data file and the trigger file are sent to MFP and RDF.
The downloaded data files and trigger files are written to designated MFP and RDF locations as configured via BDI system options MFP_outboundLocation and RDF_outboundLocation.

Scheduling Constraints

Table 28-11 Scheduling Constraints

Schedule Information	Description
Processing Cycle	End of Day
Frequency	Scheduled daily but files will only be generated weekly on End of Week date.
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 28-12 Key Tables Affected

Table	Select	Insert	Update	Delete
V_BDI_DAY_LEVEL_CALENDAR	Yes	No	No	No
CALENDAR_OUT	Yes	Yes	No	Yes
BDI_DWNLDR_IFACE_MOD_DATA_CTL	Yes	No	No	No
BDI_DWNLDR_IFACE_DATA_CTL	Yes	No	No	No

Integration Contract

The flat file will contain the following information:

Table 28-13 Flat File

Field Name	Field Type	Required	Description
DAY	Date	Yes	The date from which the 4-5-4 data was derived, in YYYYMMDD format
WEEK	Date	Yes	The end of week date for the day, in YYYYMMDD format
MONTH	Number(2)	Yes	The 4-5-4 month of the year, valid values 1-12
QUARTER	Number(1)	Yes	The 4-5-4 quarter of the year, valid values 1-4
HALF	Number(1)	Yes	The 4-5-4 half of the year, valid values are 1 or 2
YEAR	Number(4)	Yes	The 4-5-4 year
WEEK_OF_YEAR	Number(2)	Yes	The 4-5-4 week of the year, valid values 1-53
DAY_OF_YEAR	Number(1)	Yes	The day number within the week 1-7

Currency Rates Extract to Planning and Forecasting
(BDI_RPAS_CurrConvRates_Fnd_PF_From_RMS_JOB)

Module Name	BDI_RPAS_CurrConvRates_Fnd_PF_From_RMS_JOB bdi_merch_extract_to_file_wrapper.sh bdi_rpas_curr_conv_rates_extract.ksh
Description	Extracts currency rates information to RPAS from RMS
Functional Area	Foundation
Module Type	Integration
Module Technology	BDI job, shell scripts
Catalog ID	NA
Runtime Parameters	CurrConvRates_Fnd_ProcessFlow_From_RMS CurrConvRates_Fnd_Extractor Database connection, download file location, filename, trigger filename

Design Overview

RMS extracts its currency rates data to MFP and RDF on a weekly basis. It utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from RMS to MFP and RDF.

The batch job BDI_RPAS_CurrConvRates_Fnd_PF_From_RMS_JOB is defined in the RMS JOS batch job admin as follows:

<job id="BDI_RPAS_CurrConvRates_Fnd_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
    <properties>
        <property name="description" value="Extracts currency conversion rate information and writes it out to a flat file for processing by both MFP and RDF."/>
    </properties>
    <step id="batchlet-step">
        <batchlet ref="BDIInvokerBatchlet">
            <properties>
                <property name="bdiProcessFlowUrl" value="#SysOpt.bdiProcessFlowUrl"/>
                <property name="bdiProcessFlowCredential" value="#SysOpt.bdiProcessFlowUrlUserAlias"/>
                <property name="predicateDS" value="RmsDBDS"/>
                <property name="predicateFunction" value="RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL"/>
            </properties>
        </batchlet>
        <end on="COMPLETED"/>
    </step>
</job>

When the batch job BDI_RPAS_CurrConvRates_Fnd_PF_From_RMS_JOB is executed, a batchlet (BDIInvokerBatchlet) starts the execution flow. It calls a PLSQL function (RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL) to ensure the process flow is only executed on an EOW_DATE. If vdate is an EOW_DATE, it invokes a BDI process flow (CurrConvRates_Fnd_ProcessFlow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications (MFP and RDF):

Extractor job (CurrConvRates_Fnd_Extractor) calls BDI_FOUNDATION_SQL.CURR_CONV_RATES_UP function to extract data from RMS view MV_CURRENCY_CONVERSION_RATES to BDI outbound staging table CURR_CONV_RATES_OUT.
- Only the currencies for which stores and warehouses exist in RMS will be extracted.
- Either consolidated or operational rates will be included based on RMS system options (consolidation_ind).
Downloader file creator job calls the wrapper script, bdi_merch_extract_to_file_wrapper.sh, to set the runtime parameters on environment variables. This script will then call bdi_rpas_curr_conv_rates_extract.ksh to write currency rates information from the CURR_CONV_RATES_OUT table into a comma-delimited flat file, which will be consumed by RDF and MFP. A zero-byte trigger file is also generated to signal that the extract process was successful. Two separate copies of the data file and the trigger file are sent to MFP and RDF.
The downloaded data files and trigger files are written to designated MFP and RDF locations as configured via BDI system options MFP_outboundLocation and RDF_outboundLocation.

Scheduling Constraints

Table 28-14 Scheduling Constraints

Schedule Information	Description
Processing Cycle	End of Day
Frequency	Scheduled daily but files will only be generated weekly on End of Week date
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 28-15 Key Tables Affected

Table	Select	Insert	Update	Delete
MV_CURRENCY_CONVERSION_RATES	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
CURR_CONV_RATES_OUT	Yes	Yes	No	Yes
BDI_DWNLDR_IFACE_MOD_DATA_CTL	Yes	No	No	No
BDI_DWNLDR_IFACE_DATA_CTL	Yes	No	No	No

Integration Contract

The flat file will contain the following information:

Table 28-16 Flat File

Field Name	Field Type	Required	Description
EFFECTIVE_DATE	Date	Yes	Holds the next effective date of the exchange rate for the currencies and the exchange type
FROM_CURRENCY_CODE	Char(3)	Yes	Holds the convert from currency code.
TO_CURRENCY_CODE	Char(3)	Yes	Holds the convert to currency code.
EXCHANGE_TYPE	Char(1)	Yes	Identifies the type of exchange rate.
EXCHANGE_RATE	Number(20,10)	Yes	Contains the exchange rate between the from and to currencies for the specified exchange type on the next effective date. It is expressed in the to-currency.

Inventory Extract to Planning (BDI_MFP_Inventory_Tx_PF_From_RMS_JOB)

Module Name	BDI_MFP_Inventory_Tx_PF_From_RMS_JOB
Description	Extracts inventory information to MFP from RMS
Functional Area	Inventory
Module Type	Integration
Module Technology	BDI job
Catalog ID	NA
Runtime Parameters	Inventory_Tx_ProcessFlow_From_RMS Inventory_Tx_Extractor

Design Overview

RMS extracts inventoried, non-pack transaction items to Merchandise Financial Planning (MFP) on a weekly basis. It utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from RMS to MFP.

The batch job BDI_MFP_Inventory_Tx_PF_From_RMS_JOB is defined in the RMS JOS batch job admin as follows:

<job id="BDI_MFP_Inventory_Tx_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
    <properties>
        <property name="description" value="Extracts information regarding inventory for use by the MFP application"/>
    </properties>
    <step id="batchlet-step">
        <batchlet ref="BDIInvokerBatchlet">
            <properties>
                <property name="bdiProcessFlowUrl" value="#SysOpt.bdiProcessFlowUrl"/>
                <property name="bdiProcessFlowCredential" value="#SysOpt.bdiProcessFlowUrlUserAlias"/>
                <property name="predicateDS" value="RmsDBDS"/>
                <property name="predicateFunction" value="RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL"/>
            </properties>
        </batchlet>
        <end on="COMPLETED"/>
    </step>
</job>

When the batch job BDI_MFP_Inventory_Tx_PF_From_RMS_JOB is executed, a batchlet (BDIInvokerBatchlet) starts the execution flow. It calls a PLSQL function (RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL) to ensure the process flow is only executed on an EOW_DATE. If vdate is an EOW_DATE, it invokes a BDI process flow (Inventory_Tx_ProcessFLow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications (MFP):

Extractor job (Inventory_Tx_ExtractorJob) calls BDI_MFP_SQL. INVENTORY_UP function to extract data from RMS view V_BDI_MFP_INVENTORY to MFP outbound staging table INVENTORY_OUT.
A generic BDI Downloader file creator job writes quantities on order information from the INVENTORY_OUT table into a comma-delimited flat file, which will be consumed by MFP. A zero-byte trigger file is also generated to signal that the extract process was successful. Two separate copies of the data file and the trigger file are sent to MFP.
The downloaded data files and trigger files are written to designated MFP location as configured via BDI system options MFP_outboundLocation.

Scheduling Constraints

Table 28-17 Scheduling Constraints

Schedule Information	Description
Processing Cycle	End of Day
Frequency	Scheduled daily but files will only be generated weekly on End of Week date
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 28-18 Key Tables Affected

Table	Select	Insert	Update	Delete
V_BDI_MFP_INVENTORY	Yes	No	No	No
INVENTORY_OUT	Yes	Yes	No	Yes
BDI_DWNLDR_IFACE_MOD_DATA_CTL	Yes	No	No	No
BDI_DWNLDR_IFACE_DATA_CTL	Yes	No	No	No

Integration Contract

The flat file will contain the following information:

Table 28-19 Flat File - Inventory Bulk Extract to MFP

Field Name	Field Type	Required	Description
EOW	Date	Yes	Indicates the end of week date that the on order information pertains to.
ITEM	Varchar2(25	Yes	Transaction level item only.
LOCATION	Number(10)	Yes	Could be a store or warehouse.
LOC_TYPE	VARCHAR2(1)	Yes	Indicates if the location is a store or warehouse - S = Store; W = Warehouse.
CLEAR_IND	Number(1)	Yes	If Y, item/location is currently on clearance, then inventory values below will be mapped to the clearance measures in MFP; if N, then they will be mapped to the regular/promotion inventory measures in MFP.
REGULAR_INVENTORY_UNITS	NUMBER(12,4)	Yes	item_loc_soh: inventory units = stock_on_hand + pack_comp_soh + in_transit_qty + pack_comp_intran
REGULAR_INVENTORY_COST	NUMBER(20,4)	Yes	item_loc_soh: inventory units * cost - cost will differ based on accounting method - If item is in a cost department and running average cost, use av_cost - If item is in a cost department and running standard cost, use unit_cost - If item is in a retail department, use cum_markon_pct for subclass/location * unit_retail
REGULAR_INVENTORY_RETAIL	NUMBER(20,4)	Yes	item_loc_soh: inventory units * unit_retail - If system option set to have stock ledger run VAT exclusive, then a VAT exclusive unit_retail should be used here.

On Order Extract to Planning (BDI_MFP_OnOrder_Tx_PF_From_RMS_JOB)

Note:

This module replaces the onordext.pc and onorddnld.pc modules from previous releases.

Module Name	BDI_MFP_OnOrder_Tx_PF_From_RMS_JOB
Description	Extracts inventory information to MFP from RMS
Functional Area	Inventory Tracking
Module Type	Integration
Module Technology	BDI job
Catalog ID	NA
Runtime Parameters	OnOrder_Tx_ProcessFlow_From_RMS OnOrder_Tx_Extractor

Design Overview

RMS extracts its quantities on order to MFP on a weekly basis. It utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from RMS to MFP.

The batch job BDI_MFP_OnOrder_Tx_PF_From_RMS_JOB is defined in the RMS JOS batch job admin as follows:

<job id="BDI_MFP_OnOrder_Tx_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
    <properties>
        <property name="description" value="Extracts information regarding quantities on order for use by the MFP application"/>
    </properties>
    <step id="batchlet-step">
        <batchlet ref="BDIInvokerBatchlet">
            <properties>
                <property name="bdiProcessFlowUrl" value="#SysOpt.bdiProcessFlowUrl"/>
                <property name="bdiProcessFlowCredential" value="#SysOpt.bdiProcessFlowUrlUserAlias"/>
                <property name="predicateDS" value="RmsDBDS"/>
                <property name="predicateFunction" value="RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL"/>
            </properties>
        </batchlet>
        <end on="COMPLETED"/>
    </step>
</job>

When the batch job BDI_MFP_OnOrder_Tx_PF_From_RMS_JOB is executed, a batchlet (BDIInvokerBatchlet) starts the execution flow. It calls a PLSQL function (RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL) to ensure the process flow is only executed on an EOW_DATE. If vdate is an EOW_DATE, it invokes a BDI process flow (OnOrder_Tx_ProcessFlow_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications (MFP):

Extractor job (OnOrder_Tx_Extractor) calls BDI_MFP_SQL. ON_ORDER_UP function to extract data from RMS view V_BDI_MFP_ON_ORDER to MFP outbound staging table ON_ORDER_OUT.
A generic BDI Downloader file creator job writes quantities on order information from the ON_ORDER_OUT table into a comma-delimited flat file, which will be consumed by MFP. A zero-byte trigger file is also generated to signal that the extract process was successful. Two separate copies of the data file and the trigger file are sent to MFP.
The downloaded data files and trigger files are written to designated MFP location as configured via BDI system options MFP_outboundLocation.

Scheduling Constraints

Table 28-20 Scheduling Constraints

Schedule Information	Description
Processing Cycle	End of Day
Frequency	Scheduled daily but files will only be generated weekly on End of Week date.
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 28-21 Key Tables Affected

Table	Select	Insert	Update	Delete
V_BDI_MFP_ON_ORDER	Yes	No	No	No
ON_ORDER_OUT	Yes	Yes	No	Yes
BDI_DWNLDR_IFACE_MOD_DATA_CTL	Yes	No	No	No
BDI_DWNLDR_IFACE_DATA_CTL	Yes	No	No	No

Integration Contract

The flat file will contain the following information:

Table 28-22 Flat File - OnOrder Bulk Extract to MFP

Field Name	Field Type	Required	Description
EOW	Date	Yes	Indicates the end of week date that the on order information pertains to.
ITEM	Varchar2(25)	Yes	Transaction level item only.
LOCATION	Number(10)	Yes	Could be a store or warehouse.
LOC_TYPE	VARCHAR2(1)	Yes	Indicates if the location is a store or warehouse - S = Store; W = Warehouse.
CLEAR_IND	Number(1)	Yes	Indicates if the item/location is on clearance.
ON_ORDER_UNITS	NUMBER(12)	Yes	Indicates the total quantity of the item in the order in standard unit of measure.
ON_ORDER_COST	NUMBER(20,4)	Yes	on order * PO cost in primary currency
ON_ORDER_RETAIL	NUMBER(20,4)	Yes	on order * PO retail in primary currency

Transaction Data Extract to Planning (BDI_MFP_TranData_Tx_PF_From_RMS_JOB)

Module Name	BDI_MFP_TranData_Tx_PF_From_RMS_JOB
Description	Extracts Transaction data to MFP from RMS
Functional Area	Transactional Data
Module Type	Integration
Module Technology	BDI job
Catalog ID	NA
Runtime Parameters	TranData_Tx_ProcessFlow_From_RMS TranData_Tx_Extractor

Design Overview

RMS extracts transactional data to MFP on a weekly basis. It utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from RMS to MFP.

The batch job BDI_MFP_TranData_Tx_PF_From_RMS_JOB is defined in the RMS JOS batch job admin as follows:

<job id="BDI_MFP_TranData_Tx_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
    <properties>
        <property name="description" value="Extracts information regarding transaction data for use by the MFP application"/>
    </properties>
    <step id="batchlet-step">
        <batchlet ref="BDIInvokerBatchlet">
            <properties>
                <property name="bdiProcessFlowUrl" value="#SysOpt.bdiProcessFlowUrl"/>
                <property name="bdiProcessFlowCredential" value="#SysOpt.bdiProcessFlowUrlUserAlias"/>
                <property name="predicateDS" value="RmsDBDS"/>
                <property name="predicateFunction" value="RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL"/>
            </properties>
        </batchlet>
        <end on="COMPLETED"/>
    </step>
</job>

When the batch job BDI_MFP_TranData_Tx_PF_From_RMS_JOB is executed, a batchlet (BDIInvokerBatchlet) starts the execution flow. It calls a PLSQL function (RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL) to ensure the process flow is only executed on an EOW_DATE. If vdate is an EOW_DATE, it invokes a BDI process flow (Trandata_Tx_ProcessFLow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications (MFP):

Extractor job (TranData_Tx_Extractor) calls BDI_MFP_SQL. TRAN_DATA_UP function to extract data from RMS view V_BDI_MFP_TRAN_DATA to MFP outbound staging table TRAN_DATA_OUT.
A generic BDI Downloader file creator job writes quantities on order information from the TRAN_DATA_OUT table into a comma-delimited flat file, which will be consumed by MFP. A zero-byte trigger file is also generated to signal that the extract process was successful. Two separate copies of the data file and the trigger file are sent to MFP.
The downloaded data files and trigger files are written to designated MFP location as configured via BDI system options MFP_outboundLocation.

Scheduling Constraints

Table 28-23 Scheduling Constraints

Schedule Information	Description
Processing Cycle	End of Day
Frequency	Scheduled daily but files will only be generated weekly on End of Week date
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 28-24 Key Tables Affected

Table	Select	Insert	Update	Delete
V_BDI_MFP_TRAN_DATA	Yes	No	No	No
TRAN_DATA_OUT	Yes	Yes	No	Yes
BDI_DWNLDR_IFACE_MOD_DATA_CTL	Yes	No	No	No
BDI_DWNLDR_IFACE_DATA_CTL	Yes	No	No	No

Integration Contract

The flat file will contain the following information:

Table 28-25 Flat File

Field Name	Field Type	Required	Description
EOW	Date	Yes	Indicates the end of week date that the on order information pertains to.
ITEM	Varchar2(25)	Yes	Transaction level item only.
LOCATION	Number(10)	Yes	Could be a store or warehouse.
LOC_TYPE	VARCHAR2(1)	Yes	Indicates if the location is a store or warehouse - S = Store; W = Warehouse.
CLEAR_IND	Number(1)	Yes	If Y, item/location is currently on clearance.
NET_SALES_REG_UNITS	NUMBER(12,4)	Yes	tran_data_history.units: tran_code = 1 and sales type = R or P
NET_SALES_REG_COST	NUMBER(20,4)	Yes	tran_data_history.total_cost: tran_code = 1 and sales type = R or P
NET_SALES_REG_RETAIL	NUMBER(20,4)	Yes	tran_data_history.total_retail: tran_code = 1 and sales type = R or P
NET_SALES_REG_UNITS_VAT_EXCL	NUMBER(12,4)	N/A	tran_data_history.units: tran_code = 2 and sales type = R or P
NET_SALES_REG_COST_VAT_EXCL	NUMBER(20,4)	N/A	tran_data_history.total_cost: tran_code = 2 and sales type = R or P
NET_SALES_REG_RETAIL_VAT_EXCL	NUMBER(20,4)	N/A	tran_data_history.total_retail: tran_code = 2 and sales type = R or P
RETURNS_REG_UNITS	NUMBER(12,4)	N/A	tran_data_history.units: tran_code = 4 (clear_ind for item/loc = N)
RETURNS_REG_COST	NUMBER(20,4)	N/A	tran_data_history.total_cost: tran_code = 4 (clear_ind for item/loc = N)
RETURNS_REG_RETAIL	NUMBER(20,4	N/A	tran_data_history.total_retail: tran_code = 4 (clear_ind for item/loc = N)
NET_SALES_CLEAR_UNITS	NUMBER(12,4)	N/A	tran_data_history.units: tran_code = 1 and sales type = C
NET_SALES_CLEAR_COST	NUMBER(20,4)	N/A	tran_data_history.total_cost: tran_code = 1 and sales type = C
NET_SALES_CLEAR_RETAIL	NUMBER(20,4)	N/A	tran_data_history.total_retail: tran_code = 1 and sales type = C
NET_SALES_CLR_UNITS_VAT_EXCL	NUMBER(12,4)	N/A	tran_data_history.units: tran_code = 2 and sales type = C
NET_SALES_CLR_COST_VAT_EXCL	NUMBER(20,4)	N/A	tran_data_history.total_cost: tran_code = 2 and sales type = C
NET_SALES_CLR_RETAIL_VAT_EXCL	NUMBER(20,4)	N/A	tran_data_history.total_retail: tran_code = 2 and sales type = C
RETURNS_CLEAR_UNITS	NUMBER(20,4)	N/A	tran_data_history.units: tran_code = 4 (clear_ind for item/loc = Y)
RETURNS_CLEAR_COST	NUMBER(20,4)	N/A	tran_data_history.total_cost: tran_code = 4 (clear_ind for item/loc = Y)
RETURNS_CLEAR_RETAIL	NUMBER(20,4)	N/A	tran_data_history.total_cost: tran_code = 4 (clear_ind for item/loc = Y)
REG_MARKDOWN_RETAIL	NUMBER(20,4)	N/A	tran_data_history.total_retail: tran_code 13 - tran_code 14 (Markdown Cancel) - tran_code 11 (Markup)
PROMO_MARKDOWN_RETAIL_REG	NUMBER(20,4)	N/A	tran_data_history.total_retail: tran_code = 15 - if the item is not on clearance EOW
PROMO_MARKDOWN_RETAIL_CLEAR	NUMBER(20,4)	N/A	tran_data_history.total_retail: tran_code = 15 - if the item is on clearance EOW
CLEAR_MARKDOWN_RETAIL	NUMBER(20,4)	N/A	tran_data_history.total_retail: tran_code = 16
WF_MARKDOWN_RETAIL	NUMBER(20,4)	N/A	tran_data_history.total_retail: tran_code = 85
WF_MARKUP_RETAIL	NUMBER(20,4)	N/A	tran_data_history.total_retail: tran_code = 84
SHRINK_UNITS	NUMBER(12,4)	N/A	tran_data_history.units: tran_code 22
SHRINK_COST	NUMBER(20,4)	N/A	tran_data_history.total_cost: tran_code 22
SHRINK_RETAIL	NUMBER(20,4)	N/A	tran_data_history.total_retail: tran_code 22
DEAL_INCOME_COST	NUMBER(20,4)	N/A	tran_data_history.total_cost: tran_code 6 & 7
RECEIPT_UNITS	NUMBER(12,4)	N/A	tran_data_history.units: tran_code = 20
RECEIPT_COST	NUMBER(20,4)	N/A	tran_data_history.total_cost: tran_code = 20
RECEIPT_RETAIL	NUMBER(20,4)	N/A	tran_data_history.total_retail: tran_code = 20
NON_SHRINK_ADJ_UNITS	NUMBER(12,4)	N/A	tran_data_history.units: tran_code = 23
NON_SHRINK_ADJ_COST	NUMBER(20,4)	N/A	tran_data_history.total_cost: tran_code = 23
NON_SHRINK_ADJ_RETAIL	NUMBER(20,4)	N/A	tran_data_history.total_retail: tran_code = 23

UDA Item Extract to Forecasting (BDI_RDF_UdaItemLov_Fnd_From_RMS_JOB)

Module Name	BDI_RDF_UdaItemLov_Fnd_From_RMS_JOB bdi_merch_extract_to_file_wrapper.sh bdi_rdf_itemuda_extract.ksh
Description	Extracts information for LOV type of UDAs to RDF from RMS
Functional Area	Foundation
Module Type	Integration
Module Technology	BDI job, shell scripts
Catalog ID	NA
Runtime Parameters	ItemHdrAndUdaItemLov_Fnd_ProcessFlow_From_RMS ItemHdr_Fnd_Extractor UdaItemLov_Fnd_Extractor Database connection, download file location, filename, trigger filename

Design Overview

RMS extracts LOV type of UDA data to RDF on a weekly basis. It utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from RMS to RDF.

The batch job BDI_RDF_UdaItemLov_Fnd_From_RMS_JOB is defined in the RMS JOS batch job admin as follows:

<job id="BDI_RDF_UdaItemLov_Fnd_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
    <properties>
        <property name="description" value="Extracts UDA item LOV information and writes it out to a flat file for processing by RDF."/>
    </properties>
    <step id="batchlet-step">
        <batchlet ref="BDIInvokerBatchlet">
            <properties>
                <property name="bdiProcessFlowUrl" value="#SysOpt.bdiProcessFlowUrl"/>
                <property name="bdiProcessFlowCredential" value="#SysOpt.bdiProcessFlowUrlUserAlias"/>
                <property name="predicateDS" value="RmsDBDS"/>
                <property name="predicateFunction" value="RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL"/>
            </properties>
        </batchlet>
        <end on="COMPLETED"/>
    </step>
</job>

When the batch job BDI_RDF_UdaItemLov_Fnd_From_RMS_JOB is executed, a batchlet (BDIInvokerBatchlet) starts the execution flow. It calls a PLSQL function (RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL) to ensure the process flow is only executed on an EOW_DATE. If vdate is an EOW_DATE, it invokes a BDI process flow (ItemHdrAndUdaItemLov_Fnd_ProcessFlow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to RDF:

Extractor jobs (ItemHdr_Fnd_Extractor, UdaItemLov_Fnd_Extractor) call respective BDI_ITEM_SQL functions to extract data from RMS tables to BDI outbound staging tables ITEM_HDR_OUT and UDA_ITEM_LOV_OUT.
Downloader file creator job calls the wrapper script, bdi_merch_extract_to_file_wrapper.sh, to set the runtime parameters on environment variables. This script will then call bdi_rdf_itemuda_extract.ksh to write LOV type of UDA information from the ITEM_HDR_OUT and UDA_ITEM_LOV_OUT tables into a comma-delimited flat file, which will be consumed by RDF. A zero-byte trigger file is also generated to signal that the extract process was successful. The data file and the trigger file are then sent to RDF.
- Only UDA IDs and values associated to items are extracted.
- Both forecastable and non-forecastable items will be extracted.
The downloaded data file and trigger file are written to designated RDF locations as configured via BDI system options RDF_outboundLocation.

Scheduling Constraints

Table 28-26 Scheduling Constraints

Schedule Information	Description
Processing Cycle	End of Day
Frequency	Scheduled daily but files will only be generated weekly on End of Week date.
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 28-27 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_MASTER	Yes	No	No	No
CLASS	Yes	No	No	No
SUBCLASS	Yes	No	No	No
DIFF_GROUP_HEAD	Yes	No	No	No
DIFF_IDS	Yes	No	No	No
SYSTEM_OPTION	Yes	No	No	No
UDA_ITEM_LOV	Yes	No	No	No
UDA	Yes	No	No	No
UDA_VALUES	Yes	No	No	No
UDA_ITEM_LOV_OUT	Yes	Yes	No	Yes
ITEM_HDR_OUT	Yes	Yes	No	Yes
BDI_DWNLDR_IFACE_MOD_DATA_CTL	Yes	No	No	No
BDI_DWNLDR_IFACE_DATA_CTL	Yes	No	No	No

Integration Contract

The flat file will contain the following information:

Table 28-28 Flat File

Field Name	Field Type	Required	Description
ITEM	Char(25)	Yes	N/A
UDA_ID	Number(5)	Yes	N/A
UDA_DESC	Char(120)	Yes	N/A
UDA_VALUE	Number(5)	Yes	N/A
UDA_VALUE_DESC	Char(250)	Yes	N/A

Out of Stock Extract to Forecasting (BDI_RDF_StockOut_Tx_PF_From_RMS_JOB)

Note:

This module replaces the soutdnld.pc module from previous releases.

Module Name	BDI_RDF_StockOut_Tx_PF_From_RMS_JOB
Description	Extracts out of stock item location information to RDF from RMS
Functional Area	Foundation
Module Type	Integration
Module Technology	BDI job
Catalog ID	NA
Runtime Parameters	StockOut_Tx_ProcessFlow_From_RMS StockOut_Tx_Extractor

Design Overview

RMS extracts items which are out of stock for use by RDF on a weekly basis. It utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from RMS to RDF.

The batch job BDI_RDF_StockOut_Tx_PF_From_RMS_JOB is defined in the RMS JOS batch job admin as follows:

<job id="BDI_RDF_StockOut_Tx_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
    <properties>
        <property name="description" value="Extracts information for items which are out of stock for use by the RDF application"/>
    </properties>
    <step id="batchlet-step">
        <batchlet ref="BDIInvokerBatchlet">
            <properties>
                <property name="bdiProcessFlowUrl" value="#SysOpt.bdiProcessFlowUrl"/>
                <property name="bdiProcessFlowCredential" value="#SysOpt.bdiProcessFlowUrlUserAlias"/>
                <property name="predicateDS" value="RmsDBDS"/>
                <property name="predicateFunction" value="RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL"/>
            </properties>
        </batchlet>
        <end on="COMPLETED"/>
    </step>
</job>

When the batch job BDI_RDF_StockOut_Tx_PF_From_RMS_JOB is executed, a batchlet (BDIInvokerBatchlet) starts the execution flow. It calls a PLSQL function (RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL) to ensure the process flow is only executed on an EOW_DATE. If vdate is an EOW_DATE, it invokes a BDI process flow (StockOut_Tx_ProcessFlow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications (RDF):

Extractor job (StockOut_Tx_ExtractorJob) calls BDI_RDF_SQL. STOCKOUT_UP function to extract data from RMS view V_BDI_RDF_STOCKOUT to RDF outbound staging table STOCKOUT_OUT.
A generic BDI Downloader file creator job writes out of stock item information from the STOCKOUT_OUT table into a comma-delimited flat file, which will be consumed by RDF. A zero-byte trigger file is also generated to signal that the extract process was successful. Two separate copies of the data file and the trigger file are sent to RDF.
The downloaded data files and trigger files are written to designated RDF location as configured via BDI system options RDF_outboundLocation.

Scheduling Constraints

Table 28-29 Scheduling Constraints

Schedule Information	Description
Processing Cycle	End of Day
Frequency	Scheduled daily but files will only be generated weekly on End of Week date.
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 28-30 Key Tables Affected

Table	Select	Insert	Update	Delete
V_BDI_RDF_STOCKOUT	Yes	No	No	No
STOCKOUT_OUT	Yes	Yes	No	Yes
BDI_DWNLDR_IFACE_MOD_DATA_CTL	Yes	No	No	No
BDI_DWNLDR_IFACE_DATA_CTL	Yes	No	No	No

Integration Contract

The flat file will contain the following information:

Table 28-31 Flat File

Field Name	Field Type	Required	Description
ITEM	Varchar2(25)	Yes	Item that is out of stock at the store.
STORE	Number(10)	Yes	Store that is out of stock for the item.
EOW_DATE	Date	Yes	Indicates the end of week date for which the data applies.
OUT_OF_STOCK	Number(1)	Yes	Default to 1 to indicate out of stock. Only item/locs that are actually out of stock should be included in the file. So the value will always be 1.

Weekly Sales Extract to Forecasting (BDI_RDF_WeeklySales_Tx_PF_From_RMS_JOB)

Module Name	BDI_RDF_WeeklySales_Tx_PF_From_RMS_JOB
Description	Extracts weekly sales information to RDF from RMS
Functional Area	Foundation
Module Type	Integration
Module Technology	BDI job
Catalog ID	NA
Runtime Parameters	WeeklySales_Tx_ProcessFlow_From_RMS WeeklySales_Tx_Extractor

Design Overview

RMS extracts weekly sales for use by RDF on a weekly basis. It utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from RMS to RDF.

The batch job BDI_RDF_WeeklySales_Tx_PF_From_RMS_JOB is defined in the RMS JOS batch job admin as follows:

<job id="BDI_RDF_WeeklySales_Tx_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
    <properties>
        <property name="description" value="Extracts weekly sales information for use by the RDF application"/>
    </properties>
    <step id="batchlet-step">
        <batchlet ref="BDIInvokerBatchlet">
            <properties>
                <property name="bdiProcessFlowUrl" value="#SysOpt.bdiProcessFlowUrl"/>
                <property name="bdiProcessFlowCredential" value="#SysOpt.bdiProcessFlowUrlUserAlias"/>
                <property name="predicateDS" value="RmsDBDS"/>
                <property name="predicateFunction" value="RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL"/>
            </properties>
        </batchlet>
        <end on="COMPLETED"/>
    </step>
</job>

When the batch job BDI_RDF_WeeklySales_Tx_PF_From_RMS_JOB is executed, a batchlet (BDIInvokerBatchlet) starts the execution flow. It calls a PLSQL function (RMS_BATCH_STATUS_SQL.GET_EOW_RUN_SIGNAL) to ensure the process flow is only executed on an EOW_DATE. If vdate is an EOW_DATE, it invokes a BDI process flow (WeeklySales_Tx_ProcessFlow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications (RDF):

Extractor job (WeeklySales_Tx_ExtractorJob) calls BDI_RDF_SQL. WEEKLY_SALES_UP function to extract data from RMS view V_BDI_RDF_WEEKLY_SALES to RDF outbound staging table WEEKLY_SALES_OUT.
A generic BDI Downloader file creator job writes weekly sales information from the WEEKLY_SALES_OUT table into a comma-delimited flat file, which will be consumed by RDF. A zero-byte trigger file is also generated to signal that the extract process was successful. Two separate copies of the data file and the trigger file are sent to RDF.
The downloaded data files and trigger files are written to designated RDF location as configured via BDI system options RDF_outboundLocation.

Scheduling Constraints

Table 28-32 Scheduling Constraints

Schedule Information	Description
Processing Cycle	End of Day
Frequency	Scheduled daily but files will only be generated weekly on End of Week date.
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 28-33 Key Tables Affected

Table	Select	Insert	Update	Delete
V_BDI_RDF_WEEKLY_SALES	Yes	No	No	No
WEEKLY_SALES_OUT	Yes	Yes	No	Yes
BDI_DWNLDR_IFACE_MOD_DATA_CTL	Yes	No	No	No
BDI_DWNLDR_IFACE_DATA_CTL	Yes	No	No	No

Integration Contract

The flat file will contain the following information:

Table 28-34 Flat File

Field Name	Field Type	Required	Description
ITEM	Varchar2(25)	Yes	Indicates the item.
STORE	Number(10)	Yes	Indicates the store.
EOW_DATE	Date	Yes	Indicates the end of week date for which the data applies.
SALES_UNITS	Number(12,4)	No	This value will be the total sales units for the item/location for the week.
SALES_TYPE	Varchar2(1)	Yes	Indicates the sales type. For example, R (Regular Sales), P (Promotional Sales) or C (Clearance Sales).

Upload to RMS

The following module uploads data to RMS.

Weekly/Daily Item Forecast Upload (load_item_forecast)

Module Name	load_item_forecast.ksh
Description	Load daily/weekly item forecast from Oracle Retail Demand Forecasting (RDF) Cloud Service
Functional Area	Integration - Forecast
Module Type	Integration
Module Technology	Ksh
Catalog ID	N/A
Runtime Parameters	Database connection, Upload file name, Type of the run (Valid values: W – Weekly and D - Daily)

Design Overview

This script loads item forecast data into the RMS forecast tables.

The forecast data comes from RDF in a CSV (comma separated) format file. RMS expects a single input file (that is, a csv file) in an archive file format (that is, zip). A run-time parameter (that is, run type) of &rsquor;D' or &rsquor;W' 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. Depending on the run type parameter, the batch truncates the respective forecast table prior to loading.

This batch is executed through the wrapper script rmswrap_shell_in.ksh

Scheduling Constraints

Table 28-35 Scheduling Constraints

Schedule Information	Description
Processing Cycle	N/A
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	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

Evaluate the successful load of the data.

In case of any failures:

SQL load – SQL load dumps invalid records that do not meet certain technical requirements (that is, data type inconsistencies, and so on). The rejected record is written either to a bad file or to a discard file. The discard file contains records that do not satisfy conditions such as, missing or invalid record types. Records with other technical issues are written to the bad file.

Note:

A non-fatal code is returned by the program and a message will be written to the log file if reject files are created.

User Action: When such conditions exist, the user may update either the bad or the discard file and attempt to reload using the same files.

Or fix the data input file and reload, so that the item forecast tables will be truncated and uploads item forecast tables with the corrected the data.

Key Tables Affected

Table 28-36 Key Tables Affected

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

Integration Contract

If a run-time parameter of weekly is used, the input file is in fixed-length format.

Table 28-37 load_item_forecast.ksh - Input File Layout Weekly

Field Name	Field Type	Required	Description
EOW_DATE	Date(8)	Yes	Item_forecast.eow_date (YYYYMMDD)
ITEM	Char(25)	Yes	Item_forecast.item
LOC	Char(10)	Yes	Item_forecast.loc
FORECAST_SALES	Double(14)	Yes	Item_forecast.forecast_sales Note - this field can contain decimal quantities. Unlike quantity fields in RMS ProC Batch files, this qty field is not assumed to be extended to significant digits.
FORECAST_STD_DEV	Double(14)	Yes	Item_forecast.forecast_std_dev Note - this field can contain decimal quantities. Unlike quantity fields in RMS ProC Batch files, this qty field is not assumed to be extended to significant digits.

If a run-time parameter of daily is used, the input file is in fixed-length format.

Table 28-38 load_item_forecast.ksh - Input File Layout Daily

Field Name	Field Type	Required	Description
DATA_DATE	Date(8)	Yes	Daily_item_forecast.data_date (YYYYMMDD)
ITEM	Char(25)	Yes	Daily_item _forecast.item
LOC	Char(10)	Yes	Daily_item _forecast.loc
FORECAST_SALES	Double(14)	Yes	Daily_item_forecast.forecast_sales Note - this field can contain decimal quantities. Unlike quantity fields in RMS ProC Batch files, this qty field is not assumed to be extended to significant digits.
FORECAST_STD_DEV	Double(14)	Yes	Daily_item_forecast.forecast_std_dev Note - this field can contain decimal quantities. Unlike quantity fields in RMS ProC Batch files, this qty field is not assumed to be extended to significant digits

I/O Specification

N/A

Design Assumptions

Domain is not a relevant concept any more. Domain_id on ITEM_FORECAST and DAILY_ITEM_FORECAST will always be 1.

Data Maintenance

The following modules maintain and clean up data.

Retain Item Forecast History (rms_oi_forecast_history.ksh)

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 load_item_forecast.ksh weekly 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-39 Scheduling Constraints

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

Restart/Recovery

Key Tables Affected

Table 28-40 Key Tables Affected

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

Design Assumptions

Purge Forecast Data (fcstprg)

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-41 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

Key Tables Affected

Table 28-42 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

28 Integration with Oracle Retail Planning

Foundation Data vs Transaction/Inventory Data

Integration Program Summary

Merchandise Hierarchy and Item Extract to Planning and Forecasting (BDI_RPAS_MerchHier_Fnd_PF_From_RMS_JOB)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Organizational Hierarchy Extract to Planning and Forecasting (BDI_RPAS_OrgHier_Fnd_PF_From_RMS_JOB)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Store Bulk Data Extract to RPAS Planning and Forecasting (BDI_RPAS_Store_Fnd_PF_From_RMS_JOB)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Calendar Extract to Planning and Forecasting (BDI_RPAS_Calendar_Fnd_PF_From_RMS_JOB)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Currency Rates Extract to Planning and Forecasting (BDI_RPAS_CurrConvRates_Fnd_PF_From_RMS_JOB)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Inventory Extract to Planning (BDI_MFP_Inventory_Tx_PF_From_RMS_JOB)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

On Order Extract to Planning (BDI_MFP_OnOrder_Tx_PF_From_RMS_JOB)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Transaction Data Extract to Planning (BDI_MFP_TranData_Tx_PF_From_RMS_JOB)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

UDA Item Extract to Forecasting (BDI_RDF_UdaItemLov_Fnd_From_RMS_JOB)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Out of Stock Extract to Forecasting (BDI_RDF_StockOut_Tx_PF_From_RMS_JOB)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Weekly Sales Extract to Forecasting (BDI_RDF_WeeklySales_Tx_PF_From_RMS_JOB)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Upload to RMS

Weekly/Daily Item Forecast Upload (load_item_forecast)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

I/O Specification

Design Assumptions

Data Maintenance

Retain Item Forecast History (rms_oi_forecast_history.ksh)

Merchandise Hierarchy and Item Extract to Planning and Forecasting
(BDI_RPAS_MerchHier_Fnd_PF_From_RMS_JOB)

Organizational Hierarchy Extract to Planning and Forecasting
(BDI_RPAS_OrgHier_Fnd_PF_From_RMS_JOB)

Calendar Extract to Planning and Forecasting
(BDI_RPAS_Calendar_Fnd_PF_From_RMS_JOB)

Currency Rates Extract to Planning and Forecasting
(BDI_RPAS_CurrConvRates_Fnd_PF_From_RMS_JOB)