Go to primary content
Oracle® Retail Merchandising Foundation Cloud Service Operations Guide, Volume 1 - Batch Overviews and Designs
Release 16.0.028
E97818-04
  Go To Table Of Contents
Contents
Previous
Previous 		 Next
Next		  

28 Integration with Oracle Retail Planning and Forecasting

Oracle Retail Merchandising provides critical foundation and transactional information to the Oracle Retail planning and forecasting solutions. Because the planning and forecasting solutions are built on the Retail Predictive Application Server (RPAS), several of the integrations from Merchandising are used by more than one of the planning and forecasting solutions. Additionally, Merchandising receives data back from some of these solutions. The table below summarizes the key integration points by solution.

Integration to Oracle Retail Merchandise Financial Planning Cloud Service (MFPCS)

Description Program
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
Merchandise Hierarchy and Item Extract to Planning and Forecasting BDI_RPAS_MerchHier_Fnd_PF_From_RMS_JOB
Organization Hierarchy Extract to Planning and Forecasting BDI_RPAS_OrgHier_Fnd_PF_From_RMS_JOB
Store Extract to Planning and Forecasting BDI_RPAS_Store_Fnd_PF_From_RMS_JOB
Inventory Extract to Planning BDI_MFP_Inventory_Tx_PF_From_RMS_JOB
On Order Extract to Planning BDI_MFP_OnOrder_Tx_PF_From_RMS_JOB
Transaction Data Extract to Planning BDI_MFP_TranData_Tx_PF_From_RMS_JOB

Integration to Oracle Retail Assortment and Item Planning for Fashion/ Softlines Cloud Service (APCS)

Description Program
Brand Extract to Planning BDI_RPAS_Brand_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
Differentiator Extract to Planning BDI_RPAS_Diff_Fnd_PF_From_RMS_JOB
Merchandise Hierarchy and Item Extract to Planning and Forecasting BDI_RPAS_MerchHier_Fnd_PF_From_RMS_JOB
Organization Hierarchy Extract to Planning and Forecasting BDI_RPAS_OrgHier_Fnd_PF_From_RMS_JOB
Store Extract to Planning and Forecasting BDI_RPAS_Store_Fnd_PF_From_RMS_JOB
Supplier Extract to Planning BDI_RPAS_Supplier_Fnd_PF_From_RMS_JOB
UDA Extract to Planning BDI_RPAS_UdaAndUdaValues_Fnd_PF_From_RMS_JOB
UDA Item Extract to Planning and Forecasting BDI_RDF_UdaItemLov_Fnd_From_RMS_JOB
Inventory Extract to Planning BDI_MFP_Inventory_Tx_PF_From_RMS_JOB
On Order Extract to Planning BDI_MFP_OnOrder_Tx_PF_From_RMS_JOB
Transaction Data Extract to Planning BDI_MFP_TranData_Tx_PF_From_RMS_JOB

Integration from Oracle Retail Assortment and Item Planning for Fashion/ Softlines Cloud Service (APCS)

In APCS, placeholder items can be defined as part of the planning process and, once finalized, sent to Merchandising through the Item Induction process, which allows an item to be uploaded for completion of setup and approval so that it can be used in ordering and other processes. This batch process works based on pre-defined templates that indicate what Merchandising should expect from the sending solution. For the APCS integration, the template that is used is Style and Style/Color Template (STYLE_COLOR_ITEM_DATA), intended to support fashion items only. It includes the data elements that APCS will send if a new style or style/color is created. This template contains three data key data elements for new items:

  • Item Master - At the item level, APCS will send the department, class, subclass, item ID, description, item level, brand, and diff 1 information (for the style/color level only). The template will also default the following item attributes:

    • Catch weight, diff aggregate flags 2-4, forecast, pack, and primary ref item flags will be defaulted to N

    • Inventory, merchandise, orderable, and sellable flags will be defaulted to Y

    • Item number type will be defaulted to Manual - APCS will generate an item number using a pre-defined range of numbers 9000000000-9999999999.


      Note:

      As part of the implementation, a modification in the database should be made to the item number sequence generator to ensure that Oracle Retail Item Number type items numbers won't use this range. For cloud service implementations, this will require an SR be logged with cloud engineering.

    • Standard UOM will be defaulted to EA

    • Status will be defaulted to Worksheet

    • Store Order Multiple will default to eaches

    • Transaction level will default to 2.

  • Item Supplier - At the item/supplier level, it will send just the supplier ID, but the following will also be defaulted in the template for this intersection:

    • Primary supplier flag = Y

    • Case name = CS (Case), Inner name = INR (Inner), Pallet name = PAL (Pallet)

  • Item UDA - For this intersection, any list of value-type UDAs that have been associated with the placeholder item will be sent with their value.

Because the item data is still considered incomplete from a Merchandising perspective when it is received from APCS, it will be loaded into a staging area in Merchandising, where further details on the item can be defined for the style and to add sizes to allow SKUs to be created and approved. Once this occurs, and the full item details will be included in the Merchandise Hierarchy updates sent back to APCS and can be matched to the original placeholder styles.


Note:

For more information on Item Induction, see the white paper in the Merchandising Functional Library at My Oracle Support #1585843.1.

Description Program
Item Induction iindbatch.ksh (see also the Item Induction chapter above)

Integration to Oracle Retail Demand Forecasting Cloud Service (RDFCS)

Description Program
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
Merchandise Hierarchy and Item Extract to Planning and Forecasting BDI_RPAS_MerchHier_Fnd_PF_From_RMS_JOB
Organization Hierarchy Extract to Planning and Forecasting BDI_RPAS_OrgHier_Fnd_PF_From_RMS_JOB
Store Extract to Planning and Forecasting BDI_RPAS_Store_Fnd_PF_From_RMS_JOB
UDA Item Extract to Planning and Forecasting BDI_RDF_UdaItemLov_Fnd_From_RMS_JOB
Out of Stock Extract to Forecasting BDI_RDF_StockOut_Tx_PF_From_RMS_JOB
Weekly Sales Extract to Forecasting BDI_RDF_WeeklySales_Tx_PF_From_RMS_JOB

Integration from Oracle Retail Demand Forecasting Cloud Service (RDFCS)

Description Program
Weekly/Daily Item Forecast Upload load_item_forecast.ksh

Data Maintenance

Description Program
Retail Item Forecast History rms_oi_forecast_history.ksh
Purge Forecast Data fcstprg

Scheduling and dependency information for each program can be found in the program details below. Additional information about the flow of information between Merchandising and the Oracle Retail planning and forecasting applications can be found in the Retail Reference Architecture (available on My Oracle Support).The following processes support this integration.

Integration Program Summary

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

This program extracts the merchandise hierarchy from company to transaction level item to planning and forecasting on a weekly basis. Additional key attributes about the items are also included, such as the primary supplier, brand, and any differentiators (for example, colors, sizes, and so on) that exist for the item.

Key assumptions for this integration:

  • The full merchandise hierarchy and all items are sent each time this process is run.

  • Only approved, inventoried and sellable transaction-level items will be included in the integration. Pack items are not included.

  • All descriptions are sent in the primary language as defined in Merchandising.

  • For transaction items that do not have a parent item, then the transaction item is also displayed as the parent item, as well as the parent/diff level.

  • For a parent item that is not marked as an aggregate item or does not have any of its diffs flagged as aggregates, the parent item is sent as the parent/diff level for all of its transaction items.

  • A single unit of measure is assumed for all items and therefore the standard units of measure for the items are not sent.

  • The intended targets for this integration are

    • Oracle Retail Merchandise Financial Planning Cloud Service (MFPCS)

    • Oracle Retail Demand Forecasting Cloud Service (RDFCS)

    • Assortment & Item Planning for Fashion/Softlines Cloud Service and Assortment & Item Planning Enterprise Edition Cloud Service (referred to jointly as APCS)

This program utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to the target applications. The batch job BDI_RPAS_MerchHier_Fnd_PF_From_RMS_JOB is defined in the Merchandising 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 end-of-week date. If the vdate is an end-of-week 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 the target applications:

  • Extractor jobs (MerchHier_Fnd_Extractor, ItemHdr_Fnd_Extractor) call respective BDI_MERCH_SQL and BDI_ITEM_SQL functions to extract data from Merchandising 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 the target applications. A zero-byte trigger file is also generated to signal that the extract process was successful. Separate copies of the data file and the trigger file are sent to the target applications.

  • The downloaded data files and trigger files are written to designated locations as configured via BDI system options:

    • MFP_outboundLocation

    • RDF_outboundLocation

    • AP_outboundLocation

    • IP_outboundLocation

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

ITEM_SUPPLIER_OUT

Yes

Yes

No

Yes

Integration Contract

The flat file will contain the following information:

Field Name Field Type Required Description

ITEM

Char(25)

Yes

The transaction level item ID.

ITEM_DESC

Char(250)

Yes

The transaction level item description.

ITEM_PARENT_DIFF

Char(30)

Yes

Concatenated value consisting of item parent ID with the composite diff aggregate. If there is no item parent, this will contain the transaction level item.

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 marked as aggregates. If there is no item parent, it will contain the transaction level item description.

ITEM_PARENT

Char(25)

Yes

If there is no item parent, it will contain the transaction level item.

ITEM_PARENT_DESC

Char(250)

Yes

If there is no item parent, it will contain the transaction level item description.

SUBCLASS_ID

Number(10)

Yes

Unique subclass ID

SUBCLASS_NAME

Char(120)

Yes

Concatenated value consisting of the subclass number with name.

CLASS_ID

Number(10)

Yes

Unique class ID

CLASS_NAME

Char(120)

Yes

Concatenated value consisting of the class number with name.

DEPT

Number(4)

Yes

Department ID

DEPT_NAME

Char(120)

Yes

Concatenated value consisting of the department ID and name.

GROUP_NO

Number(4)

Yes

Group ID

GROUP_NAME

Char(120)

Yes

Group name

DIVISION

Number(4)

Yes

Division ID

DIV_NAME

Char(120)

Yes

Division name

COMPANY

Number(4)

Yes

Company ID

COMPANY_NAME

Char(120)

Yes

Company name

FORECAST_IND

Char(1)

Yes

Indicates whether or not the item should be forecasted. Valid values are Y or N.

CLASS

Number(10)

Yes

The class ID that is displayed in the Merchandising screens.

SUBCLASS

Number(10)

Yes

The subclass ID that is displayed in the Merchandising screens.

BRAND_NAME

Char(30)

Yes

If a brand is not assigned, this is defaulted to 'NA'.

BRAND_DESCRIPTION

Char(120)

Yes

The brand description for the transaction item. If a brand is not assigned, this is defaulted to 'Not Assigned'.

SUPPLIER

Number(10)

Yes

The ID of the primary supplier for the transaction item.

SUPPLIER_NAME

Char(240)

Yes

The name of the primary supplier for the transaction item.

DIFF_1

Char(10)

No

The ID of the first diff for the transaction level item. If a diff is not assigned, this is defaulted to 'NA'.

DIFF_1_DESC

Char(120)

No

The name of the first diff for the transaction item. If a diff is not assigned, this is defaulted to 'unassigned'.

DIFF_2

Char(10)

No

The ID of the second diff for the transaction item. If a diff is not assigned, this is defaulted to 'NA'.

DIFF_2_DESC

Char(120)

No

The name of the second diff for the transaction item. If a diff is not assigned, this is defaulted to 'unassigned'.

DIFF_3

Char(10)

No

The ID of the third diff for the transaction item. If a diff is not assigned, this is defaulted to 'NA'.

DIFF_3_DESC

Char(120)

No

The name of the third diff for the transaction item. If a diff is not assigned, this is defaulted to 'unassigned'.

DIFF_4

Char(10)

No

The ID of the fourth diff for the transaction item. If a diff is not assigned, this is defaulted to 'NA'.

DIFF_4_DESC

Char(120)

No

The name of the fourth diff for the transaction item. If a diff is not assigned, this is defaulted to 'unassigned'.

Organization Hierarchy Bulk Data Extract to RPAS (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 organizational hierarchy information to RPAS
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

This program extracts the organization hierarchy data from company to location, which can be stores or warehouses to planning and forecasting on a weekly basis. Additional key attributes about the organizational hierarchy will also be sent to assist in building alternate hierarchies for planning, such as channel.

Key assumptions for this integration:

  • MFPCS will use the third level of the Merchandising hierarchy (area) to represent channel.

  • The full organizational hierarchy is sent each time this process is run.

  • All names and descriptions are sent in the primary language only.

  • The location in the file can represent either a store or a virtual warehouse location.

  • Because warehouses live outside the organization hierarchy, for the levels of the organizational hierarchy above location (chain through district) when the location is a warehouse, the warehouse ID and description will be repeated.

  • The intended targets for this integration are

    • Oracle Retail Merchandise Financial Planning Cloud Service (MFPCS)

    • Oracle Retail Demand Forecasting Cloud Service (RDFCS)

    • Assortment & Item Planning for Fashion/Softlines Cloud Service and Assortment & Item Planning Enterprise Edition Cloud Service (referred to jointly as APCS)

This program utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to the target applications. The batch job BDI_RPAS_OrgHier_Fnd_PF_From_RMS_JOB is defined in the Merchandising 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 end-of-week date. If the vdate is an end-of-week 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:

  • Extractor jobs (Store_Fnd_Extractor, Wh_Fnd_Extractor, OrgHier_Fnd_Extractor) call respective BDI_ORG_SQL functions to extract data from Merchandising 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 the target applications. A zero-byte trigger file is also generated to signal that the extract process was successful. Separate copies of the data file and the trigger file are sent to the target applications.

  • The downloaded data files and trigger files are written to designated locations as configured via BDI system options:

    • MFP_outboundLocation

    • RDF_outboundLocation

    • AP_outboundLocation

    • IP_outboundLocation

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

Field Name Field Type Required Description

LOCATION

Number(10)

Yes

Store or virtual warehouse ID

LOC_NAME

Char(150)

Yes

Store or warehouse name

DISTRICT

Number(10)

Yes

District ID; for warehouses, repeat the warehouse ID with the prefix "WH"

DISTRICT_NAME

Char(120)

Yes

District name; for warehouses, repeat the warehouse name

REGION

Number(10)

Yes

Region ID; for warehouses, repeat the warehouse ID with the prefix "WH"

REGION_NAME

Char(120)

Yes

Region name; for warehouses, repeat the warehouse name

AREA

Number(10)

Yes

Area ID; for warehouses, repeat the warehouse ID with the prefix "WH"

AREA_NAME

Char(120)

Yes

Area name; for warehouses, repeat the warehouse name

CHAIN

Number(10)

Yes

Chain ID; for warehouses, repeat the warehouse ID with the prefix "WH"

CHAIN_NAME

Char(120)

Yes

Chain name; for warehouses, repeat the warehouse name

COMPANY

Number(4)

Yes

Company ID

COMPANY_NAME

Char(120)

Yes

Company name

COMPANY_CURRENCY

Char(3)

Yes

The currency code for the base currency defined in system options

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 ID for warehouses, 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

Channel ID for the store or virtual warehouse; if no channel is defined, then NA

CHANNEL_NAME

Char(120)

Yes

Channel name; if no channel is defined, then 'unassigned'

STORE_CLASS

Char(1)

Yes

For stores, the store class ID; for warehouses or if no store class is defined; then NA.

STORE_CLASS_DESCRIPTION

Char(250)

Yes

For stores, the description of the store class, if defined; for warehouses or if not defined for a store, then 'unassigned'.

STORE_FORMAT

Number(4)

Yes

For stores, the store format ID; for warehouses or if no store class is defined; then NA.

STORE_FORMAT_NAME

Char(60)

Yes

For stores, the description of the store format, if defined; for warehouses or if not defined for a store, then 'unassigned'.

Store Bulk Data Extract to RPAS (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
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

This program extracts store data to planning and forecasting on a weekly basis. This data supplements the store information included in the organizational hierarchy feed.

Key assumptions for this integration:

  • Both stockholding and non-stockholding stores are included.

  • Both company and franchise types of stores are included.

  • All stores are sent each time this process is run.

  • Planning will derive the status of the store (e.g. open or closed) based on the dates sent in this integration. For example, if the open date is in the past and there is no close date defined or it is a future date, then the store is considered open.

  • All descriptions are sent in the primary language as defined in Merchandising.

  • The intended targets for this integration are

    • Oracle Retail Merchandise Financial Planning Cloud Service (MFPCS)

    • Oracle Retail Demand Forecasting Cloud Service (RDFCS)

    • Assortment & Item Planning for Fashion/Softlines Cloud Service and Assortment & Item Planning Enterprise Edition Cloud Service (referred to jointly as APCS)

This program utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from Merchandising to the target applications.

The batch job BDI_RPAS_Store_Fnd_PF_From_RMS_JOB is defined in the Merchandising 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 end-of-week date. If the vdate is an end-of-week 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:

  • Extractor job (Store_Fnd_Extractor) calls BDI_ORG_SQL.STORE_UP function to extract data from Merchandising 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 the target application. 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 the target application.

  • The downloaded data files and trigger files are written to designated locations as configured via BDI system options:

    • MFP_outboundLocation

    • RDF_outboundLocation

    • AP_outboundLocation

    • IP_outboundLocation

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

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. If NULL, set to NA.

STORE_OPEN_DATE

DATE

Yes

Date on which the store opened

REMODEL_DATE

DATE

Yes

Date on which the store was last remodeled. If NULL, set to NA.

STORE_CLASS

Char(1)

Yes

ID for the store class of which the store is a member.

STORE_CLASS_DESCRIPTION

Char(250)

Yes

Store class description

STORE_FORMAT

Number(4)

Yes

Store format. If NULL, set to NA.

STORE_FORMAT_NAME

Char(60)

Yes

Store format name. If NULL, set to 'unassigned'.

CURRENCY

Char(3)

Yes

Currency under which the store operates.

STORE_TYPE

Char(6)

Yes

Indicates whether the store is a franchise (F) or company store (C).

STOCKHOLDING_IND

Char(1)

Yes

Indicates whether the store can hold stock. Valid values are Y or N.

Brand Extract to Planning (BDI_RPAS_Brand_Fnd_PF_From_RMS_JOB)

Module Name BDI_RPAS_Brand_Fnd_PF_From_RMS_JOBbdi_merch_extract_to_file_wrapper.shbdi_rpas_brand_extract.ksh
Description Extracts Brand information to Planning
Functional Area Foundation
Module Type Integration
Module Technology BDI job, shell scripts
Catalog ID NA
Runtime Parameters Brand_Fnd_ProcessFlow_From_RMSBrand_Fnd_ExtractorDatabase connection, download file location, filename, trigger filename

Design Overview

This process extracts its brand data to Planning on a weekly basis.

Key assumptions for this integration:

  • The full set of brands is included in this integration each time it runs.

  • Retailers will not create a Diff with an ID of 'BRAND'.

  • In order to meet the format required by Planning, the UDA description in this extract is hard coded to "Brand" and does not take into account the primary language configuration in Merchandising.

  • The intended targets for this integration are

    • Assortment & Item Planning for Fashion/Softlines Cloud Service and Assortment & Item Planning Enterprise Edition Cloud Service (referred to jointly as APCS)

This process utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to Planning. The batch job BDI_RPAS_Brand_Fnd_PF_From_RMS_JOB is defined in the Merchandising JOS batch job admin as follows:

<job id="BDI_RPAS_Brand_Fnd_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
  <properties>
    <property name="description" value="Extracts Brand information and writes it out to a flat file for processing by AP and IP."/>
  </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_Brand_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 end-of-week date. If the vdate is an end-of-week date, it invokes a BDI process flow (Brand_Fnd_ProcessFlow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications:

  • Extractor job (Brand_Fnd_Extractor) calls BDI_FOUNDATION_SQL.BRAND_UP function to extract data from RMS table BRAND to BDI outbound staging table BRAND_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_brand_extract.ksh to write brand information from the BRAND_OUT table into a comma-delimited flat file, which will be consumed by the target applications. 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 the target applications.

  • The downloaded data files and trigger files are written to designated locations as configured via BDI system options:

    • AP_outboundLocation

    • IP_outboundLocation

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 Select Insert Update Delete

BRAND

Yes

No

No

No

BRAND_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:

Field Name Field Type Required Description

UDA_ID

Char(6)

Yes

Hardcoded to 'BRAND'

UDA_DESC

Char(120)

Yes

Hardcoded to 'Brand'

BRAND_NAME

Char(30)

Yes

The brand ID from the Merchandising Brand table.

BRAND_DESCRIPTION

Char(120)

Yes

The brand description in the primary language from the Merchandising Brand table.

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

This program extracts calendar data to planning and forecasting on a weekly basis.

Key assumptions for this integration:

  • The last two years, current year, and two years into the future are extracted each time this process is run.

  • A data set is sent each time the extract runs.

  • This extract supports a 4-5-4 calendar only.

  • The intended targets for this integration are

    • Oracle Retail Merchandise Financial Planning Cloud Service (MFPCS)

    • Oracle Retail Demand Forecasting Cloud Service (RDFCS)

    • Assortment & Item Planning for Fashion/Softlines Cloud Service and Assortment & Item Planning Enterprise Edition Cloud Service (referred to jointly as APCS)

This program utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from Merchandising to the target applications.

The batch job BDI_RPAS_Calendar_Fnd_PF_From_RMS_JOB is defined in the Merchandising 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 end-of-week date. If the vdate is an end-of-week 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:

  • 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 the target applications. A zero-byte trigger file is also generated to signal that the extract process was successful. Separate copies of the data file and the trigger file are sent to the target applications.

  • The downloaded data files and trigger files are written to designated locations as configured via BDI system options:

    • MFP_outboundLocation

    • RDF_outboundLocation

    • AP_outboundLocation

    • IP_outboundLocation

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

Field Name Field Type Required Description

DAY

Date

Yes

The date for which the 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 month number of the day in the year; valid values 1-12

QUARTER

Number(1)

Yes

The quarter of the year for the day; valid values 1-4

HALF

Number(1)

Yes

The half of the year for the day; valid values are 1 or 2

YEAR

Number(4)

Yes

The year for the day (YYYY format).

WEEK_OF_YEAR

Number(2)

Yes

The week of the year for the day; valid values 1-53

DAY_OF_WEEK

Number(1)

Yes

The day number within the week; valid values 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
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

This program extracts its currency rates data to planning and forecasting on a weekly basis.

Key assumptions for this integration:

  • Only currency rates for which stores and warehouse exist will be included in the extract.

  • Either the consolidated or operational rate will be sent based on the setting of the Consolidation system option. If Y, then the consolidation rates will be sent. If N, then the operational rates are used.

  • All applicable currency rates are sent each time this process is run.

  • The rates sent in this integration are based on a materialized view. The process that refreshes this view (batch_rfmvcurrconv.ksh) must be scheduled to ensure that the latest currency information is sent each week.

  • The intended targets for this integration are

    • Oracle Retail Merchandise Financial Planning Cloud Service (MFPCS)

    • Oracle Retail Demand Forecasting Cloud Service (RDFCS)

    • Assortment & Item Planning for Fashion/Softlines Cloud Service and Assortment & Item Planning Enterprise Edition Cloud Service (referred to jointly as APCS)

This program utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from Merchandising to the target applications.

The batch job BDI_RPAS_CurrConvRates_Fnd_PF_From_RMS_JOB is defined in the Merchandising 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 end-of-week date. If the vdate is an end-of-week 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:

  • Extractor job (CurrConvRates_Fnd_Extractor) calls BDI_FOUNDATION_SQL.CURR_CONV_RATES_UP function to extract data from Merchandising 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 the target applications. A zero-byte trigger file is also generated to signal that the extract process was successful. Separate copies of the data file and the trigger file are sent to the target applications.

  • The downloaded data files and trigger files are written to designated locations as configured via BDI system options:

    • MFP_outboundLocation

    • RDF_outboundLocation

    • AP_outboundLocation

    • IP_outboundLocation

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

batch_rfmvcurrconv.ksh

Post-Processing

N/A

Threading Scheme

N/A

Restart/Recovery

N/A

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:

Field Name Field Type Required Description

EFFECTIVE_DATE

Date

Yes

Holds the 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. This will be either C (consolidation) or O (operational).

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 terms of the to-currency.

Differentiator Extract to Planning (BDI_RPAS_Diff_Fnd_PF_From_RMS_JOB)

Module Name BDI_RPAS_Diff_Fnd_PF_From_RMS_JOBbdi_merch_extract_to_file_wrapper.shbdi_rpas_diff_extract.ksh
Description Extracts Diff Types and Diff ID information to Planning
Functional Area Foundation
Module Type Integration
Module Technology BDI job, shell scripts
Catalog ID NA
Runtime Parameters Diff_Fnd_ProcessFlow_From_RMS

Diff_Fnd_Extractor

Database connection, download file location, filename, trigger filename

Design Overview

This process extracts its differentiator data to Planning on a weekly basis.

Key assumptions for this integration:

  • The full set of differentiators and diff types are included in this integration each time it runs.

  • The intended targets for this integration are

    • Assortment & Item Planning for Fashion/Softlines Cloud Service and Assortment & Item Planning Enterprise Edition Cloud Service (referred to jointly as APCS)

This process utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to Planning. The batch job BDI_RPAS_Diff_Fnd_PF_From_RMS_JOB is defined in the Merchandising JOS batch job admin as follows:

<job id="BDI_RPAS_Diff_Fnd_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
  <properties>
    <property name="description" value="Extracts Diff Types and Diff ID information and writes it out to a flat file for processing by AP and IP."/>
  </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_Diff_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 end-of-week date. If the vdate is an end-of-week date, it invokes a BDI process flow (Diff_Fnd_ProcessFlow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications:

  • Extractor job (Diff_Fnd_Extractor) calls BDI_CROSS_PILLAR_SQL.DIFF_UP function to extract data from DIFF_IDS and DIFF_TYPE to BDI outbound staging table DIFF_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_diff_extract.ksh to write differentiator information from the DIFF_OUT table into a comma-delimited flat file, which will be consumed by the target applications. A zero-byte trigger file is also generated to signal that the extract process was successful. Separate copies of the data file and the trigger file are sent to the target applications.

  • The downloaded data files and trigger files are written to designated locations as configured via BDI system options:

    • AP_outboundLocation

    • IP_outboundLocation

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 Select Insert Update Delete

DIFF_IDS

Yes

No

No

No

DIFF_TYPE

Yes

No

No

No

DIFF_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:

Field Name Field Type Required Description

DIFF_TYPE_ID

Char(6)

Yes

The ID of the diff type (for example, C for color).

DIFF_TYPE_DESC

Char(120)

Yes

The description of the diff type (for example, Color) in the primary language.

DIFF_ID

Char(10)

Yes

The ID of the diff (for example, S for Small).

DIFF_DESC

Char(120)

Yes

The description of the diff (for example, Small) in the primary language.

Supplier Bulk Data Extract to RPAS (BDI_RPAS_Supplier_Fnd_PF_From_RMS_JOB)

Module Name BDI_RPAS_Supplier_Fnd_PF_From_RMS_JOB

bdi_merch_extract_to_file_wrapper.sh

bdi_rpas_supplier_extract.ksh
Description Extracts Supplier information to Planning
Functional Area Foundation
Module Type Integration
Module Technology BDI job, shell scripts
Catalog ID NA
Runtime Parameters Supplier_Fnd_ProcessFlow_From_RMS

Supplier_Fnd_Extractor

Database connection, download file location, filename, trigger filename

Design Overview

This process extracts supplier data to Planning on a weekly basis.

Key assumptions for this integration:

  • All active, orderable supplier sites will be included in this integration each time it runs.

  • Retailers will not create a Diff with an ID of 'SUP'.

  • In order to meet the format required by Planning, the UDA description in this extract is hard coded to "Supplier" and does not take into account the primary language configuration in Merchandising.

  • The intended targets for this integration are

    • Assortment & Item Planning for Fashion/Softlines Cloud Service and Assortment & Item Planning Enterprise Edition Cloud Service (referred to jointly as APCS)

This process utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to Planning.

The batch job BDI_RPAS_Supplier_Fnd_PF_From_RMS_JOB is defined in the Merchandising JOS batch job admin as follows:

<job id="BDI_RPAS_Supplier_Fnd_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
  <properties>
    <property name="description" value="Extracts Supplier information and writes it out to a flat file for processing by AP and IP."/>
  </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_Supplier_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 end-of-week date. If the vdate is an end-of-week date, it invokes a BDI process flow (Supplier_Fnd_ProcessFlow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications:

  • Extractor job (Supplier_Fnd_Extractor) calls BDI_FOUNDATION_SQL.SUPS_UP function to extract data from the Merchandising table SUPS to BDI outbound staging table SUPS_OUT. Only supplier sites will be extracted.

  • 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_supplier_extract.ksh to write supplier information from the SUPS_OUT table into a comma-delimited flat file, which will be consumed by the target applications. 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 the target applications.

  • The downloaded data files and trigger files are written to designated locations as configured via BDI system options:

    • AP_outboundLocation

    • IP_outboundLocation

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 Select Insert Update Delete

SUPS

Yes

No

No

No

SUPS_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:

Field Name Field Type Required Description

UDA_ID

Char(6)

Yes

Hardcoded 'SUP'

UDA_DESC

Char(120)

Yes

Hardcoded 'Supplier'

SUPPLIER

Char(30)

Yes

The supplier site ID.

SUP_NAME

Char(120)

Yes

The supplier site name in the primary language.

UDA Extract to Planning (BDI_RPAS_UdaAndUdaValues_Fnd_PF_From_RMS_JOB)

Module Name BDI_RPAS_UdaAndUdaValues_Fnd_PF_From_RMS_JOB

bdi_merch_extract_to_file_wrapper.sh

bdi_rpas_uda_extract.ksh
Description Extracts LOV Type UDA information to Planning
Functional Area Foundation
Module Type Integration
Module Technology BDI job, shell scripts
Catalog ID NA
Runtime Parameters UdaAndUdaValues_Fnd_ProcessFlow_From_RMS

Uda_Fnd_Extractor

UdaValues_Fnd_Extractor

Database connection, download file location, filename, trigger filename

Design Overview

This process extracts its UDA data to Planning on a weekly basis.

Key assumptions for this integration:

  • The full set of user defined attributes (UDAs) is included in this integration each time it runs.

  • Only list of value type UDAs will be included in the integration.

  • The intended targets for this integration are

    • Assortment & Item Planning for Fashion/Softlines Cloud Service and Assortment & Item Planning Enterprise Edition Cloud Service (referred to jointly as APCS)

This process utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to Planning. The batch job BDI_RPAS_UdaAndUdaValues_Fnd_PF_From_RMS_JOB is defined in the Merchandising JOS batch job admin as follows:

<job id="BDI_RPAS_UdaAndUdaValues_Fnd_PF_From_RMS_JOB" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
  <properties>
    <property name="description" value="Extracts LOV Type UDA information and writes it out to a flat file for processing by AP and IP."/>
  </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_UdaAndUdaValues_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 end-of-week date. If the vdate is an and-of-week date, it invokes a BDI process flow (UdaAndUdaValues_Fnd_ProcessFlow_From_RMS) to perform a series of steps to extract, download, and transport the downloaded files to target applications:

  • Extractor jobs (Uda_Fnd_Extractor, UdaValues_Fnd_Extractor) call respective BDI_FOUNDATION_SQL functions to extract data from Merchandising tables UDA and UDA_VALUES to BDI outbound staging tables UDA_OUT and UDA_VALUES_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_uda_extract.ksh to write UDA information from the UDA_OUT and UDA_VALUES_OUT tables into a comma-delimited flat file, which will be consumed by the target applications. Only LOV type UDAs will be extracted. A zero-byte trigger file is also generated to signal that the extract process was successful. Separate copies of the data file and the trigger file are sent to the target applications.

  • The downloaded data files and trigger files are written to designated locations as configured via BDI system options:

    • AP_outboundLocation

    • IP_outboundLocation

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 Select Insert Update Delete

UDA

Yes

No

No

No

UDA_VALUES

Yes

No

No

No

UDA_OUT

Yes

Yes

No

Yes

UDA_VALUES_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:

Field Name Field Type Required Description

UDA_ID

Number(5)

Yes

The ID of the UDA assigned to the item.

UDA_DESC

Char(120)

Yes

The description of the UDA (for example, Fabric Content).

UDA_VALUE

Number(5)

Yes

The ID of the UDA value for the UDA assigned to the item.

UDA_VALUE_DESC

Char(250)

Yes

The description of the UDA value (for example, Cotton).

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

This process extracts owned inventory information for inventoried, non-pack approved transaction items to planning on a weekly basis, at the end of the week. The integration captures the current on-hand and in-transit for all the included item/locations at the point in time that the integration is run.

Key assumptions for this integration:

  • Only inventoried, approved transaction items are included in the integration.

  • Any inventory for pack items is aggregated with inventory for the component items.

  • Only stockholding stores are included in the integration.

  • Cost values are based on system configuration for cost:

    • For a cost department with the system configured for average cost, the cost basis is the item/location's weighted average cost, converted to primary currency.

    • For a cost department with the system configured for standard cost, the cost basis is the item/locations unit cost, converted to primary currency.

    • For a retail department, the cumulative mark-on percentage is used to calculate cost based on the retail price, converted to primary currency.

  • Retail values sent are based on the current item/location retail price, converted to primary currency. The retail will include VAT if the system option to include VAT in the stock ledger is set to include VAT so that the retail values in this integration are consistent with other data sent to planning.

  • All unit values are sent in terms of the standard unit of measure for the item.

  • Planning will interpret inventory as being clearance if the clearance flag sent in this integration shows the item/location to be on clearance at the end of the week.

  • The intended targets for this integration are

    • Oracle Retail Merchandise Financial Planning Cloud Service (MFPCS)

    • Assortment & Item Planning for Fashion/Softlines Cloud Service and Assortment & Item Planning Enterprise Edition Cloud Service (referred to jointly as APCS)

This process utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from RMS to the target applications. The batch job BDI_MFP_Inventory_Tx_PF_From_RMS_JOB is defined in the Merchandising 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 end-of-week date. If the vdate is an end-of-week 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:

  • Extractor job (Inventory_Tx_ExtractorJob) calls BDI_MFP_SQL. INVENTORY_UP function to extract data from Merchandising view V_BDI_MFP_INVENTORY to BDI outbound staging table INVENTORY_OUT.

  • A generic BDI Downloader file creator job writes inventory quantities information from the INVENTORY_OUT table into a comma-delimited flat file, which will be consumed by the target applications. 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 the target applications.

  • The downloaded data files and trigger files are written to designated locations as configured via BDI system options:

    • MFP_outboundLocation

    • AP_outboundLocation

    • IP_outboundLocation

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

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 virtual 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 currently on clearance.

REGULAR_INVENTORY_UNITS

Number(12,4)

Yes

Current owned inventory for the item/location in units based on the standard unit of measure; calculated as stock on hand + pack component stock on hand + in transit + pack component in transit.

REGULAR_INVENTORY_COST

Number(20,4)

Yes

The cost value of current owned inventory for the item/location; calculated based on unit inventory and the cost basis of the item's department, as described above.

REGULAR_INVENTORY_RETAIL

Number(20,4)

Yes

The retail value of current owned inventory for the item/location; calculated based on the unit inventory value shown above and the current item/location unit retail.

UNIT_COST

Number(20,4)

Yes

The current supplier purchase cost for the item/location.

AV_COST

Number(20,4)

Yes

The current weighted average cost for the item/location.

UNIT_RETAIL

Number(20,4)

Yes

The current unit retail for the item/location. If the item is on clearance, this would be the clearance price.

On Order Bulk 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 Planning
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

This process extracts its quantities on order to planning and forecasting on a weekly basis, at the end of the week. The integration sends any open on order quantities aggregated by week, grouped by the open to buy end of week date. Any on order quantity that is still open and has an OTB EOW date in the past will be combined with the current week's on order.

Key assumptions for this integration:

  • Only orderable, inventoried, approved transaction items are included in the integration.

  • Any on order for pack items is sent based on the component items.

  • Purchase orders flagged to not be included in "on order" are not included in the integration.

  • Cost and retail values sent are based on the purchase order's cost and retail value, converted to primary currency.

  • Retail values will include VAT if the system option to include VAT in the stock ledger is set to include VAT so that the retail values in this integration are consistent with other data sent to planning.

  • All unit values are sent in terms of the standard unit of measure for the item.

  • Planning will interpret the on order as being clearance if the clearance flag sent in this integration shows the item/location to be on clearance at the end of the week.

  • The intended targets for this integration are

    • Oracle Retail Merchandise Financial Planning Cloud Service (MFPCS)

    • Assortment & Item Planning for Fashion/Softlines Cloud Service and Assortment & Item Planning Enterprise Edition Cloud Service (referred to jointly as APCS)

This process utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to the target applications.

The batch job BDI_MFP_OnOrder_Tx_PF_From_RMS_JOB is defined in the Merchandising 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 end of week date. If the vdate is an end of week 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:

  • Extractor job (OnOrder_Tx_Extractor) calls BDI_MFP_SQL. ON_ORDER_UP function to extract data from Merchandising view V_BDI_MFP_ON_ORDER to BDI 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 the target applications. A zero-byte trigger file is also generated to signal that the extract process was successful. Separate copies of the data file and the trigger file are sent to the target applications.

  • The downloaded data files and trigger files are written to designated locations as configured via BDI system options:

    • MFP_outboundLocation

    • AP_outboundLocation

    • IP_outboundLocation

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

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 virtual 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 currently 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 Bulk 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 Planning 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

This process extracts transactional data to planning on a weekly basis, aggregating all transactions that posted in the last week, which could include transactions for previous weeks that posted late.

Key assumptions in this integration:

  • Only orderable, inventoried, approved transaction items are included in the integration.

  • Pack items are not included in this integration; any transactions involving pack items will be sent in terms of the pack's component items.

  • Cost and retail values sent in primary currency.

  • Sales sent will always be net sales. If gross sales are needed in Planning, then net sales can be combined with returns.

  • Retail values will include VAT if the system option to include VAT in the stock ledger is set to include VAT so that the retail values in this integration are consistent with other data sent to planning.

  • All unit values are sent in terms of the standard unit of measure for the item.

  • Late posted transactions included in this integration may be for any week in the open stock ledger month, as well as any week in the previous month that posted during the week but before the previous month closed, if the month close ran during the current week.

  • The intended targets for this integration are

    • Oracle Retail Merchandise Financial Planning Cloud Service (MFPCS)

    • Assortment & Item Planning for Fashion/Softlines Cloud Service and Assortment & Item Planning Enterprise Edition Cloud Service (referred to jointly as APCS)

This process utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to the target applications. The batch job BDI_MFP_TranData_Tx_PF_From_RMS_JOB is defined in the Merchandising 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 end-of-week date. If the vdate is an end-of-week 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:

  • Extractor job (TranData_Tx_Extractor) calls BDI_MFP_SQL. TRAN_DATA_UP function to extract data from the Merchandising view V_BDI_MFP_TRAN_DATA to BDI outbound staging table TRAN_DATA_OUT.

  • A generic BDI Downloader file creator job writes transactional information from the TRAN_DATA_OUT table into a comma-delimited flat file, which will be consumed by the target applications. A zero-byte trigger file is also generated to signal that the extract process was successful. Separate copies of the data file and the trigger file are sent to the target applications.

  • The downloaded data files and trigger files are written to designated MFP location as configured via BDI system options:

    • MFP_outboundLocation

    • AP_outboundLocation

    • IP_outboundLocation

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

Field Name Field Type Required Description

EOW

Date

Yes

Indicates the end of week date that the information pertains to.

ITEM

Varchar2(25)

Yes

Transaction level item only.

LOCATION

Number(10)

Yes

Could be a store or virtual 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)

No

tran_data_history.units: tran_code = 1 and sales type = R

NET_SALES_REG_COST

Number(20,4)

No

tran_data_history.total_cost: tran_code = 1 and sales type = R

NET_SALES_REG_RETAIL

Number(20,4)

No

tran_data_history.total_retail: tran_code = 1 and sales type = R

NET_SALES_PROMO_UNITS

Number(12,4)

No

tran_data_history.units: tran_code = 1 and sales type = P

NET_SALES_PROMO_COST

Number(20,4)

No

tran_data_history.total_cost: tran_code = 1 and sales type = P

NET_SALES_PROMO_RETAIL

Number(20,4)

No

tran_data_history.total_retail: tran_code = 1 and sales type = P

NET_SALES_CLEAR_UNITS

Number(12,4)

 No

tran_data_history.units: tran_code = 1 and sales type = C

NET_SALES_CLEAR_COST

Number(20,4)

 No

tran_data_history.total_cost: tran_code = 1 and sales type = C

NET_SALES_CLEAR_RETAIL

Number(20,4)

 No

tran_data_history.total_retail: tran_code = 1 and sales type = C

NET_SALES_REG_RETAIL_VAT_EXCL

Number(20,4)

No

tran_data_history.total_retail: tran_code = 2 and sales type = R

NET_SALES_PROMO_RTL_VAT_EXCL

Number(20,4)

No

tran_data_history.total_retail: tran_code = 2 and sales type = P

NET_SALES_CLR_RETAIL_VAT_EXCL

Number(20,4)

 No

tran_data_history.total_retail: tran_code = 2 and sales type = C

RETURNS_REG_UNITS

Number(12,4)

No

tran_data_history.units: tran_code = 4 and sales type = R

RETURNS_REG_COST

Number(20,4)

 No

tran_data_history.total_cost: tran_code = 4 and sales type = R

RETURNS_REG_RETAIL

Number(20,4

 No

tran_data_history.total_retail: tran_code = 4 and sales type = R

RETURNS_PROMO_UNITS

Number(12,4)

No

tran_data_history.units: tran_code = 4 and sales type = P

RETURNS_PROMO_COST

Number(20,4)

No

tran_data_history.total_cost: tran_code = 4 and sales type = P

RETURNS_PROMO_RETAIL

Number(20,4)

No

tran_data_history.total_retail: tran_code = 4 and sales type = P

RETURNS_CLEAR_UNITS

Number(20,4)

 No

tran_data_history.units: tran_code = 4 and sales type = C

RETURNS_CLEAR_COST

Number(20,4)

 No

tran_data_history.total_cost: tran_code = 4 and sales type = C

RETURNS_CLEAR_RETAIL

Number(20,4)

 No

tran_data_history.total_cost: tran_code = 4 and sales type = C

REG_MARKDOWN_RETAIL

Number(20,4)

 No

tran_data_history.total_retail: tran_code 13 - tran_code 14 (Markdown Cancel) - tran_code 11 (Markup)

PROMO_MARKDOWN_RETAIL_REG

Number(20,4)

 No

tran_data_history.total_retail: tran_code = 15 - if the item is not on clearance EOW

PROMO_MARKDOWN_RETAIL_CLEAR

Number(20,4)

 No

tran_data_history.total_retail: tran_code = 15 - if the item is on clearance EOW

CLEAR_MARKDOWN_RETAIL

Number(20,4)

 No

tran_data_history.total_retail: tran_code = 16

WF_MARKDOWN_RETAIL

Number(20,4)

 No

tran_data_history.total_retail: tran_code = 85

WF_MARKUP_RETAIL

Number(20,4)

 No

tran_data_history.total_retail: tran_code = 84

SHRINK_UNITS

Number(12,4)

 No

tran_data_history.units: tran_code 22

SHRINK_COST

Number(20,4)

 No

tran_data_history.total_cost: tran_code 22

SHRINK_RETAIL

Number(20,4)

 No

tran_data_history.total_retail: tran_code 22

DEAL_INCOME_COST

Number(20,4)

 No

tran_data_history.total_cost: tran_code 6 & 7

RECEIPT_UNITS

Number(12,4)

 No

tran_data_history.units: tran_code = 20 + 44

RECEIPT_COST

Number(20,4)

 No

tran_data_history.total_cost: tran_code = 20 + 44

RECEIPT_RETAIL

Number(20,4)

 No

tran_data_history.total_retail: tran_code = 20 + 44

NON_SHRINK_ADJ_UNITS

Number(12,4)

 No

tran_data_history.units: tran_code = 23

NON_SHRINK_ADJ_COST

Number(20,4)

 No

tran_data_history.total_cost: tran_code = 23

NON_SHRINK_ADJ_RETAIL

Number(20,4)

 No

tran_data_history.total_retail: tran_code = 23

DEAL_INCOME_PURCHASES

Number(20,4)

No

tran_data_history.total_cost: tran_code 7

MARKUP

Number(20,4)

No

tran_data_history.total_retail: tran_code 11

MARKDOWN_CANCEL

Number(20,4)

No

tran_data_history.total_retail: tran_code 14

INTERCOMPANY_MARKUP

Number(20,4)

No

tran_data_history.total_retail: tran_code 17

INTERCOMPANY_MARKDOWN

Number(20,4)

No

tran_data_history.total_retail: tran_code 18

RTV_UNITS

Number(12,4)

No

tran_data_history.units: tran_code = 24

RTV_COST

Number(20,4)

No

tran_data_history.total_cost: tran_code = 24

RTV_RETAIL

Number(20,4)

No

tran_data_history.total_retail: tran_code = 24

TSF_IN_UNITS

Number(12,4)

No

tran_data_history.units: tran_code = 30

TSF_IN_COST

Number(20,4)

No

tran_data_history.total_cost: tran_code = 30

TSF_IN_RETAIL

Number(20,4)

No

tran_data_history.total_retail: tran_code = 30

TSF_IN_UNITS_BOOK

Number(12,4)

No

tran_data_history.units: tran_code = 31

TSF_IN_COST_BOOK

Number(20,4)

No

tran_data_history.total_cost: tran_code = 31

TSF_IN_RETAIL_BOOK

Number(20,4)

No

tran_data_history.total_retail: tran_code = 31

TSF_OUT_UNITS

Number(12,4)

No

tran_data_history.units: tran_code = 32

TSF_OUT_COST

Number(20,4)

No

tran_data_history.total_cost: tran_code = 32

TSF_OUT_RETAIL

Number(20,4)

No

tran_data_history.total_retail: tran_code = 32

TSF_OUT_UNITS_BOOK

Number(12,4)

No

tran_data_history.units: tran_code = 33

TSF_OUT_COST_BOOK

Number(20,4)

No

tran_data_history.total_cost: tran_code = 33

TSF_OUT_RETAIL_BOOK

Number(20,4)

No

tran_data_history.total_retail: tran_code = 33

RECLASS_IN_UNITS

Number(12,4)

No

tran_data_history.units: tran_code = 34

RECLASS_IN_COST

Number(20,4)

No

tran_data_history.total_cost: tran_code = 34

RECLASS_IN_RETAIL

Number(20,4)

No

tran_data_history.total_retail: tran_code = 34

RECLASS_OUT_UNITS

Number(12,4)

No

tran_data_history.units: tran_code = 36

RECLASS_OUT_COST

Number(20,4)

No

tran_data_history.total_cost: tran_code = 36

RECLASS_OUT_RETAIL

Number(20,4)

No

tran_data_history.total_retail: tran_code = 36

TSF_IN_UNITS_ICT

Number(12,4)

No

tran_data_history.units: tran_code = 37

TSF_IN_COST_ICT

Number(20,4)

No

tran_data_history.total_cost: tran_code = 37

TSF_IN_RETAIL_ICT

Number(20,4)

No

tran_data_history.total_retail: tran_code = 37

TSF_OUT_UNITS_ICT

Number(12,4)

No

tran_data_history.units: tran_code = 38

TSF_OUT_COST_ICT

Number(20,4)

No

tran_data_history.total_cost: tran_code = 38

TSF_OUT_RETAIL_ICT

Number(20,4)

No

tran_data_history.total_retail: tran_code = 38

INTERCOMPANY_MARGIN

Number(20,4)

No

tran_data_history.total_retail: tran_code = 39

TSF_RECEIPT_UNITS

Number(12,4)

No

tran_data_history.units: tran_code = 44

TSF_RECEIPT_COST

Number(20,4)

No

tran_data_history.total_cost: tran_code = 44

TSF_RECEIPT_RETAIL

Number(20,4)

No

tran_data_history.total_retail: tran_code = 44

RTV_RESTOCK_FEE

Number(20,4)

No

tran_data_history.total_cost: tran_code = 65

FRANCHISE_SALES_UNITS

Number(12,4)

No

tran_data_history.units: tran_code = 82

FRANCHISE_SALES_COST

Number(20,4)

No

tran_data_history.total_cost: tran_code = 82

FRANCHISE_SALES_RETAIL

Number(20,4)

No

tran_data_history.total_retail: tran_code = 82

FRANCHISE_RETURNS_UNITS

Number(12,4)

No

tran_data_history.units: tran_code = 83

FRANCHISE_RETURNS_COST

Number(20,4)

No

tran_data_history.total_cost: tran_code = 83

FRANCHISE_RETURNS_RETAIL

Number(20,4)

No

tran_data_history.total_retail: tran_code = 83

FRANCHISE_RESTOCK_FEE

Number(20,4)

No

tran_data_history.total_cost: tran_code = 86

UDA Item Extract to Planning and 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 Planning and Forecasting
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

This process extracts user-defined attributes (UDAs) assigned to item to Planning and Forecasting on a weekly basis.

Key assumptions for this integration:

  • Only list of value (LOV) type UDAs will be included.

  • Both forecasted and non-forecasted items are included in this extract, with the forecast flag included.

  • Planning and Forecasting can only support a specific UDA being associated with an item once. Merchandising has a configuration that allows the same UDA to be associated with an item more than one time. However, when implementing with Planning or Forecasting, this should be avoided for LOV-type UDAs to prevent issues with interpreting the data. If more than one is associated with the item, then only the last UDA with a particular ID will be visible in Planning and Forecasting.

  • The intended targets for this integration are

    • Oracle Retail Demand Forecasting Cloud Service (RDFCS)

    • Assortment & Item Planning for Fashion/Softlines Cloud Service and Assortment & Item Planning Enterprise Edition Cloud Service (referred to jointly as APCS)

This process utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement from RMS to the target applications. The batch job BDI_RDF_UdaItemLov_Fnd_From_RMS_JOB is defined in the Merchandising 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 end-of-week date. If the vdate is an end-of-week 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 the target applications:

  • Extractor jobs (ItemHdr_Fnd_Extractor, UdaItemLov_Fnd_Extractor) call respective BDI_ITEM_SQL functions to extract data from Merchandising 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 the target applications. 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 the target applications.

  • The downloaded data file and trigger file are written to designated locations as configured through BDI system options:

    • RDF_outboundLocation

    • AP_outboundLocation

    • IP_outboundLocation

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

Field Name Field Type Required Description

ITEM

Char(25)

Yes

The ID of the item.

UDA_ID

Number(5)

Yes

The ID of the UDA assigned to the item.

UDA_DESC

Char(120)

Yes

The description of the UDA (for example, Fabric Content)

UDA_VALUE

Number(5)

Yes

The ID of the UDA value for the UDA assigned to the item.

UDA_VALUE_DESC

Char(250)

Yes

The description of the UDA value (for example, Cotton).

FORECAST_IND

Char(1)

Yes

Indicates whether or not the item is to be forecasted. Valid values are Y or N.

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

This process extracts items which are out of stock for use by Forecasting on a weekly basis. This integration sends all item/store combinations that meet the criteria for review and have a stock-on-hand position of less than or equal to zero at the end of the week.

Key assumptions for this integration:

  • Only stockholding stores are included in this integration.

  • Only forecasted items are included in this integration.

  • Only item/store combinations that have a status of Active and a ranged flag of Yes are reviewed for stock out conditions.

  • Only item/store combinations that have a last sold date that is between the end of week date and x number of days back are reviewed for stock out conditions, where x is the value reports system option value Days Since Last Transaction.

  • The intended targets for this integration are

    • Oracle Retail Demand Forecasting Cloud Service (RDFCS)

This process utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to the target applications. The batch job BDI_RDF_StockOut_Tx_PF_From_RMS_JOB is defined in the Merchandising 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 end-of-week date. If the vdate is an end-of-week 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:

  • Extractor job (StockOut_Tx_ExtractorJob) calls BDI_RDF_SQL. STOCKOUT_UP function to extract data from the Merchandising view V_BDI_RDF_STOCKOUT to 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 the target applications. A zero-byte trigger file is also generated to signal that the extract process was successful.

  • The downloaded data files and trigger files are written to designated location as configured through BDI system options:

    • RDF_outboundLocation

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

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

Flag to indicate if the item/store is out of stock at end of week. This will always be 1, as only out-of-stock items are sent.

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

This process extracts weekly sales for use by Forecasting on a weekly basis. It sends only the sales from the last week.

Key assumptions for this integration:

  • This integration sends gross sales. Returns are not netted out of the sales values.

  • Warehouse issues are not included in this integration. Only sales for stores.

  • Only forecasted items are included in this integration.

  • The intended targets for this integration are

    • Oracle Retail Demand Forecasting Cloud Service (RDFCS)

This process utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to the target applications.

The batch job BDI_RDF_WeeklySales_Tx_PF_From_RMS_JOB is defined in the Merchandising 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 end-of-week date. If the vdate is an end-of-week 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:

  • Extractor job (WeeklySales_Tx_ExtractorJob) calls BDI_RDF_SQL. WEEKLY_SALES_UP function to extract data from a Merchandising view V_BDI_RDF_WEEKLY_SALES to 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 the target applications. A zero-byte trigger file is also generated to signal that the extract process was successful. Separate copies of the data file and the trigger file are sent to the target applications.

  • The downloaded data files and trigger files are written to designated location as configured via BDI system options:

    • RDF_outboundLocation

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

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 comma-delimited input file (that is, a csv file) in the format specified in the sqlldr control scripts load_item_forecast.ctl (for Weekly) and load_daily_item_forecast.ctl (for Daily). Please refer to "Integration Contract" for more details. 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.

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 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 a single comma-delimited file (that is, a CSV file):

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 a single comma-delimited file (that is, a CSV file):

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 Assumption

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

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

NA

Key Tables Affected

Table Select Insert Update Delete

ITEM_FORECAST

Yes

No

No

No

ITEM_FORECAST_HIST

No

Yes

No

Yes

Design Assumptions

NA

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

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

  Previous
Previous 		 Next
Next
  Go To Table Of Contents
Contents