13 Pre-Pack Optimization

This chapter describes the Pre-Pack configuration module in AI Foundation Cloud Service.

Overview

Pre-Pack Optimization recommends optimal Pre-Pack configurations that meet a store-specific requirement while maximizing supply chain efficiencies. It considers vendor constraints, size min/max requirements, and other common pre-pack related constraints with scenario modeling support. Users can learn about the business impact on changes to constraints. The approval process enables users to review and finalize pre-pack configurations with information on benefits from recommended pre-pack configurations.

Data Requirements

This section provides information about setting up the data that the Pre-Pack Optimization uses, including guidelines regarding the expectations for the data element requested and where it is used. Information about these files can be found in Oracle Retail Insights Cloud Service Suite.

For more details on file specifications, see the AI Foundation Interfaces Guide on My Oracle Support (MOS): Oracle Retail Insights Cloud Service Suite, AI Foundation Cloud Services, Retail Data Extractor for Cloud, and Retail Home Documentation Library at Doc ID 2539848.1.

Hierarchy Data

The three types of hierarchies required are Location Hierarchy, Merchandise Hierarchy, and Calendar Hierarchy. Data is first loaded into AIF Data and then loaded into AIF applications. For additional details, refer to the Data Loads and Initial Batch Processing section of the Oracle Retail Analytics and Planning Implementation Guide. If Pre-Pack Optimization is the first module of the AIF applications that is being implemented, then set the following configuration settings in RSE_CONFIG file.
APPL CODE PARAM NAME PARAM VALUE DESCR
RSE EXTENDED_HIERARCHY_SRC RMS

Data source providing extended hierarchy data RMS/NON-RMS.
RSE LOAD_EXTENDED_PROD_HIER Y Y/N value. This parameter is used by the product hierarchy ETL to determine if the extended product hierarchy is required.

Sales Data

Historical sales data is required at the sku-store-week level and must be provided. Refer to the Data Loads and Initial Batch Processing section of the Oracle Retail Analytics and Planning Implementation Guide.

Size and Size Range Data

The mapping between SKUs and sizes, as well as the mapping between sizes and size ranges, are both required. This data can be provided through DIFF_GROUP.csv (staging files W_RTL_DIFF_GRP_DS, and W_RTL_DIFF_GRP_D_TL). It is also necessary to set the value of SPO_DIFF_TYPE in RSE_CONFIG to be the same as the diff_type that is in W_RTL_DIFF_GRP_D for size attribute.

In addition to these two interfaces, it is expected that the column prod_attr_grp_id will be populated in W_RTL_ITEM_GRP1_D for the PROD_GRP_ TYPE that is associated with Size. For the jobs that must be run to load data to above tables, see the Batch and Ad-Hoc Jobs section of this chapter.

Note:

It is necessary that all attributes, including the Size attribute, are provided at the lowest level of product hierarchy (that is, the level in RSE_ HIER_LEVEL that has LEAF_NODE_FLG = Y).

Supplier Data (Optional)

If supplier data is provided, it is possible to generate Pre-Pack configurations that are supplier-specific. Supplier data can be provided via PRODUCT.csv file. This in turn loads tables W_PARTY_ORG_D and W_RTL_IT_SUPPLIER_D. These two RI tables are used to load RSE_SUPPLIER and RSE_SUPPLIER_ITEM.

External Assortment Plan Data (Optional)

It is possible to load external Assortment Plans and use this as a basis for optimizing pre-pack configurations. Assortment Plan data is expected at the Style Color/Store/Week level. The format of the external plan data is consistent with the Assortment Plan (PLAN5) interface in Retail Insights Cloud Service. The required interface is W_RTL_PLAN5_PROD5_LC5_T5_FS. See the following section to make sure the data level is set correctly.

Interface Name: W_RTL_PLAN5_PROD5_LC5_T5_FS.dat

Setting Data Levels

C_ODI_PARAM needs to be updated before loading any style-color data via the Control and Tactical Center. In the C_OCI_Param table:

  • find param_name = 'RI_PLAN5_PROD_LEVEL' and set param_value = 'ITEM_LEVEL1'.
  • find param_name = 'RI_PLAN5_ATTR_LEVEL' and set param_value = 'C'

Refer to the section on Planning and Flex Fact Configuration in the Oracle Retail Insights Cloud Service Implementation Guide for more details on making updates to the C_ODI_PARAM_VW table.

PLAN data files are picked up by nightly POM jobs. In addition, it is also possible to load them as ad hoc processes via DAT_REPROCESS_ADHOC. Refer to the AI Foundation Data Standalone Process Flows section in the Oracle Retail AI Foundation Cloud Services Operations Guide.

Sample Data

W_RTL_PLAN5_PROD5_LC5_T5_FS.dat:

PROD_DH_NUM,PROD_DH_ATTR,ORG_DH_NUM,SUPPLIER_NUM,CAL_DATE,PLANNING_TYPE_CODE,VERSION_NUM,SLS_QTY,SLS_RTL_AMT,SLS_COST_AMT,EOH_COST_AMT,EOH_RTL_AMT,EOH_QTY,INVRC_COST_AMT,INVRC_RTL_AMT,INVRC_QTY,DOC_CURR_CODE,DATASOURCE_NUM_ID,ETL_THREAD_VAL,INTEGRATION_ID,LOC_CURR_CODE
990874190,multi,2041,-1,20230923,NA,0,17.474183200394,2098.0299530574,1070.6304425932, 43.538242705932,85.318456926443,.71060489128625,0,0,0,USD,1,1,990874190~2041~23-SEP-23~-1~multi~NA~0,USD
1102018,yellow,2154,-1,20230826,NA,0,3.5946690094215,508.60971814305,273.05105795566, 46.936181947753,87.427598522743,.61790655539433,0,0,0,USD,1,1,1102018~2154~26-AUG-23~-1~yellow~NA~0,USD
1102019,green,2116,-1,20230826,NA,0,4.9103104966421,844.52430231748,511.50704443521, 53.458223625021, 88.262262467769,.51318252495941,0,0,0,USD,1,1,1102019~2116~26-AUG-23~-1~green~NA~0,USD

W_RTL_PLAN5_PROD5_LC5_T5_FS.dat.ctx:

#TABLE#W_RTL_PLAN5_PROD5_LC5_T5_FS#
#DELIMITER#,#
#DATEFORMAT#YYYYMMDD#
#REJECTLIMIT#1#
#RECORDDELIMITER#\n#
#IGNOREBLANKLINES#false#
#SKIPHEADERS#1#
#TRIMSPACES#rtrim#
#TRUNCATECOL#false#
#COLUMNLIST#
PROD_DH_NUM
PROD_DH_ATTR
ORG_DH_NUM
SUPPLIER_NUM
CAL_DATE
PLANNING_TYPE_CODE
VERSION_NUM
SLS_QTY
SLS_RTL_AMT
SLS_COST_AMT
EOH_COST_AMT
EOH_RTL_AMT
EOH_QTY
INVRC_COST_AMT
INVRC_RTL_AMT
INVRC_QTY
DOC_CURR_CODE
DATASOURCE_NUM_ID
ETL_THREAD_VAL
INTEGRATION_ID
LOC_CURR_CODE

Configurations

The following table shows the configurations that must be provided at the time of implementation. These configurations can be viewed and modified using Strategy & Policy Management under Control & Tactical Center. In this dashboard, you navigate to Manage System Configurations, select ALL for application, RSE_CONFIG for table, and filter the table by APPL_CODE=PPO.
PARAM_NAME PARAM_VALUE DESC
PPO_CAL_HIER_PROCESSING_LVL 4 Level Identifier for the level of the Calendar Hierarchy at which we process pre-pack/size data. Default 4 is to process at Week Level.
PPO_CAL_HIER_TYPE 11 Hierarchy Type Identifier for the Calendar Hierarchy Type.
PPO_LOC_HIER_PROCESSING_LVL 6 Level Identifier for the level of the Location Hierarchy at which we process pre-pack/size data. Default 6 is to process at STORE Level.
PPO_LOC_HIER_TYPE 2 Hierarchy Type Identifier for the Location Hierarchy Type.
PPO_MIN_STORE_SIZE_COUNT 2 Minimum number of sizes at a store within a style-color.
PPO_PROD_HIER_PARENT_LVL 8 Level Identifier for the level of the Product Hierarchy of the SKU-Parent. Default 8 is the STYLE-COLOR Level.
PPO_PROD_HIER_PROCESSING_LVL 9 Level Identifier for the level of the Product Hierarchy at which we process pre-pack/size data. Default 9 is to process at SKU Level.
PPO_PROD_HIER_TYPE 3 Hierarchy Type Identifier for the Product/Merchandise Hierarchy Type.
PPO_SIZE_PROFILE_LOC_LEVEL 6 Level Identifier for the level of the Location Hierarchy at which we process Size Profile. Default 6 is to process at STORE Level.
PPO_SIZE_PROFILE_MERCH_LEVEL 6 Level Identifier for the level of the Product Hierarchy at which we process Size Profile. Default 6 is to process at SUBCLASS Level.
PPO_SIZE_PROFILE_MERCH_LOWEST_LEVEL 9 Level Identifier for the lowest level of the Product Hierarchy for Size Profile. Default 6 is to process at SKU Level.
PPO_SIZE_PROFILE_MERCH_LOWEST_PARENT_LEVEL 8 Level Identifier for the parent level of the lowest level of the Product Hierarchy for Size Profile. Default 8 is to process at STYLE-COLOR Level.

Batch and Ad-Hoc Jobs

In addition to the standard jobs to load data into AIF applications for standard hierarchies and sales data, the following jobs must be run to populate the size, size range, and sub size range information that is required. Note that these jobs must be run even if SPO is not being implemented.

  • SPO_SIZE_ETL_START_JOB

  • SPO_SIZE_ETL_SETUP_JOB

  • SPO_SIZE_ETL_PROCESS_JOB

  • SPO_SIZE_ETL_END_JOB

  • SPO_PROD_LOC_SIZE_ETL_START_JOB

  • SPO_SIZE_RANGE_ETL_SETUP_JOB

  • SPO_SIZE_RANGE_ETL_PROCESS_JOB

  • SPO_SUB_SIZE_RANGE_ETL_SETUP_JOB

  • SPO_SUB_SIZE_RANGE_ETL_PROCESS_JOB

  • SPO_SIZE_MAP_ETL_SETUP_JOB

  • SPO_SIZE_MAP_ETL_PROCESS_JOB

  • SPO_SUB_SIZE_MAP_ETL_SETUP_JOB

  • SPO_SUB_SIZE_MAP_ETL_PROCESS_JOB

Refer to Data-Loading Batch and Ad-Hoc Jobs for specific details.