| Oracle® Retail Advanced Science Engine Implementation Guide Release 14.1 E59126-02 |
|
 Previous |
 Next |
| Oracle® Retail Advanced Science Engine Implementation Guide Release 14.1 E59126-02 |
|
Previous |
Next |
This chapter describes data interfaces, ETL scripts, imports and exports, tablespaces, and CM files. It contains the following sections:
ORASE depends on RADM for much of the data input, and RADM, in turn, acquires data from other sources, such as RMS. Some ASO data are received from CM and other sources through flat file ODI interfaces as defined below. RADM and ORASE are co-deployed on the same Oracle database instance and exchange data in database.
The ORASE interface (see Appendix B, "Database Detail Definitions") defines detailed data requirements for all data to be consumed by ORASE modules and to be sent by ORASE modules to RADM and all subscribing applications.
Subscribing applications include CM, RDF, MSM (or other alternative), and RMS/AIP (or other alternative).
If RMS is installed, the packaged RADM ETL will be used to populate RADM with data from RMS.
Figure 6-1 illustrates the data flow between the related applications. A summary of the main inputs and outputs for ORASE follows.
ORASE and ASO receive input via ETL batch processes:
From RADM
Hierarchies, attributes, sales history (aggregated, daily promotional) and customer-linked transactions history, configuration, price and cost information, segments, item-location ranging, promotion information
ASO uses some of this data from RADM and also receives additional data via file ETL input:
From CM
Assortment and space optimization requests (assortment information), assortment overrides, demand forecasts, placeholder product information, like-items for placeholder products
From MSM or an alternate source
Planograms and product display geometry information
From RMS, AIP, or an alternate source
Product replenishment information
|
Note: Inputs and outputs between ORASE and CM are internal and not detailed in this guide. |
ORASE sends data to CM in the form of ETL files, to RDF in the form of DT API calls, and RA as table views, as follows.
To CM from DT via files
DT parameters, SKU/store, SKU/segment/TA
To RDF from DT via an API call
DT parameters and model apply via an API call
To CM from CDT via files
CDT XML files
To CM from AC via files
Cluster sets
To RADM from AC via a view
Cluster sets
ORASE shares any data residing in the ORASE schema directly with ASO.
ASO makes data available in the form of database views. The consumer application can write custom extracts against these views to retrieve the data.
To CM via database views and custom extracts
Optimized assortments, assortment overrides
To RMS, AIP, or an alternate source via database views and custom extracts
Product replenishment information
To MSM via custom views
Planograms and product display geometry information
Detailed file interface definitions can be found in Appendix B, "Database Detail Definitions".
As part of the implementation, the installation and configuration should already be done as specified. See the Oracle Retail Modeling Engine Installation Guide, the Oracle Retail Assortment and Space Optimization Installation Guide, and Chapter 5, "Configuration."
The following classes of batch scripts are provided:
Data import (including RA to ORASE ETL)
Data export
Batch run
Computation node startup
ORASE and ASO depend on and interact with RA, CM, and RDF. ASO requires additional data from CM, MSM (or other space planning application), and RMS/AIP (or other replenishment application).
To facilitate loading and moving this data, a set of batch scripts is available to orchestrate data import, export, and control batch runs and computation node processing.
These scripts reside on the file system and are created during ORASE installation. The batch scripts rely on the standard Linux technology stack (Linux, Java Virtual Machine, Oracle database client) and environment settings that should be configured as appropriate to allow these scripts to execute properly.
The scripts encompass:
Data import, which includes running ETL procedures for importing the relevant data from RADM and other systems to ORASE.
Data export to the subscribing applications.
Execution of the off-line calculations and a start of the computation scalability nodes, if necessary.
These scripts can be found in the following locations:
ORASE: <RSE_HOME> /cdm/<cdt, dt, cis>/scripts
ASO: <RSE_HOME> /so/scripts
ORASE applications require external data from a number of additional sources beyond those that retrieve data from RA. This data resides in flat files as defined in the file interface definition details found in Appendix B, "Database Detail Definitions."
External data is loaded from these flat files as a set of ETL scripts to a set of staging tables and then transformed and loaded to the ORASE or application databases as appropriate. Every load script follows this naming convention:
<script name>_stg.ctl
<script name>_stg.ksh
<script name>_load.ksh
Most ETL is executed as needed by the user. Alternatively, the scripts can be scheduled periodically, based on customer requirements. Some are required to be run periodically.
For all data loaded via a _STG interface table, there is a way to control how errors limits are handled by the load process. The RSE_LOAD_SRVC_CONFIG and RSE_LOAD_VALDT_RULES_CFG tables contain the information related to the load process and their validation rules and are joinable by the RSE_LOAD_SRVC_CONFIG.ID and RSE_LOAD_VALDT_RULES_CFG.LOAD_SRVC_ID columns. The RSE_LOAD_SRVC_CONFIG can be filtered by the NAME column, which is specified in the script that runs the loader.
The validation rules can be set up to fail either if a certain number of failed records occur (via MAX_NUM_ERRORS), or if a percentage of rows in the interface table has been exceeded. If the typical approach to resolving records that fail validation is to remove the row from being processed, then it may be suitable to adjust one or more validation rules so that they have a tolerance for some invalid rows. If rows fail validation but do not exceed the tolerances defined in the validation rules table, then those rows will be deleted from the staging table, the remaining data will be loaded, and the load routine will terminate with a success condition. The rows in the _BAD table remain, so that exceptions can be handled or reported on via a custom post process.
This section provides details about ETL scripts and batch processes.
Data moves from RADM to ORASE via a set of ETL scripts, as follows:
Executed manually at initial setup
Executed manually ad hoc as needed
Scheduled to run daily or weekly by batch scripts
At Initial Setup
As described in Chapter 3, "ORASE Installation and Implementation Overview" the RSE common and application-specific configuration and seed data were loaded by executing the *_config.ksh and *_master.ksh scripts. Those steps are mandatory and must be completed before any further data loading.
A summary description of these scripts and control files is provided in this guide. See Oracle Retail Advanced Science Engine Implementation Guide, Volume 2 - Data Processes and Configuration Variables for details on each script, the interfaces, and batch frequency.
Loading Additional Sales Transaction Data
The CDT and DT applications require sales transaction data. The seed data load process described in Chapter 3, "ORASE Installation and Implementation Overview" loads the most recent weeks of transaction data by default, as a starting point. This is due to the potential quantity of transaction data. If more transaction data is required, this is the procedure.
The following scripts load sales transaction data from RADM to ORASE:
rse_sls_trx_setup.ksh
rse_sls_trx_process.ksh
In addition, the following post-process raw transactions are used:
rse_wkly_sls_setup.ksh, which aggregates to the weekly level
rse_wkly_sls_process.ksh
rse_wkly_sls_seg_setup.ksh, which aggregates to the segment level
rse_wkly_sls_seg_process.ksh
rse_fake_cust_setup.ksh, which filters false customer transactions
rse_fake_cust_process.ksh
All scripts are located in the directory: <RSE_HOME>/common/scripts/bin and are the same scripts that are executed by the cdt_master.ksh during the seed data load process.
These scripts accept a parameter for the number of weeks to be processed. Execute these scripts, choosing an value according the number of weeks desired in ORASE and available in RADM.
For batch loading and post-processing of transactions, these scripts should be scheduled to execute in the same way in a weekly batch with one week to be processed.
Missing Transaction Data
Sales transaction data is only required by CDT, so if no sales transaction data is available, the other applications can still be used. However, they do require weekly aggregate sales. If RADM does not contain sales transaction data, then weekly aggregate sales data can be gathered using these scripts:
rse_wkly_sls_stg.ksh for product/location/week aggregates
rse_wkly_sls_load.ksh scripts
rse_wkly_sls_seg_stg.ksh for product/location/customer segment/week aggregates
rse_wkly_sls_seg_load.ksh scripts
The latter of these is required for DT only.
As these aggregations can take time, the suggestion is to stage one week of data at a time into the staging table, and then process that data, and repeat this in a loop for each of the historic weeks to be loaded. This will result in faster processing then if all the weeks are staged at the same time and then processed via a single execution. The processes are capable of either approach, or any mix of the two approaches. For example, if files are available that contain four weeks per file, then it is acceptable to load the file and process the data in one execution of the load step. You should not attempt this with a large number of weeks in a single file.
Once the weekly aggregates are loaded, some other initial post-processing steps are required. They all should be executed for the same number of weeks to be processed as, and the number should equal the number used when the sales transaction data was loaded (or should equal the number of weeks that were loaded via the aggregate interfaces). The list of initial routines that need to be run are listed in Table 6-1.
Table 6-1 ORASE Initial Data Setup Routines
| Application | Setup Script | Process Script | Setup Parameters |
|---|---|---|---|
|
CDT |
rse_fake_cust_setup.ksh |
rse_fake_cust_process.ksh |
UPDT_NUM_WEEKS |
|
CDT |
rse_wkly_sls_ph_aggr_setup.ksh |
rse_wkly_sls_process.ksh |
UPDT_NUM_WEEKS, FORCE_UPDT_EXISTING |
|
AC |
rse_wkly_sls_ph_attr_aggr_setup.ksh |
rse_wkly_sls_process.ksh |
UPDT_NUM_WEEKS, FORCE_UPDT_EXISTING |
|
AC |
cis_prod_attr_loc_share_setup.ksh |
cis_prod_attr_loc_share_process.ksh |
UPDT_NUM_WEEKS FORCE_UPDT_EXISTING |
|
DT |
dt_prod_loc_range_setup.ksh |
dt_prod_loc_range_process.ksh |
UPDT_NUM_WEEKS FORCE_UPDT_EXISTING |
|
DT |
dt_loc_range_setup.ksh |
dt_loc_range_process.ksh |
UPDT_NUM_WEEKS |
After all the historic weeks of data have been processed, it is possible to run the remaining weekly batch routines.
The processes listed in Table 6-2 must be configured to execute at the frequency listed and apply to all ORASE applications except MBA. They are listed in process order.
Note that many of these processes have a corresponding initial setup script. Only the processing script is listed here. Refer to Oracle Retail Advanced Science Engine Implementation Guide, Volume 2 - Data Processes and Configuration Variables and find the details on each processing script listed here.
For more details, refer to Oracle Retail Advanced Science Engine Implementation Guide, Volume 2 - Data Processes and Configuration Variables.
Table 6-2 Recurring Batch Processes
| Description | Notes | Frequency | Processing Shell Script |
|---|---|---|---|
|
Load the RADM product hierarchy integration ID values. |
Product hierarchy interface. This is the first part of the product hierarchy interface. It retrieves the integration IDs and creates new internal IDs as needed for new products. |
Daily |
rse_prod_src_xref_load.ksh |
|
Load the RADM product hierarchy. |
Product hierarchy interface. This is the second part of the product hierarchy interface. It retrieves and maintains the product hierarchy descriptions and updates a normalized product hierarchy table. |
Daily |
rse_prod_hier_load.ksh |
|
Create a transitive closure representation of the product hierarchy for the main hierarchy. |
Product hierarchy interface. This is a transformation routine that restructures the normalized product hierarchy table data into a transitive closure representation so that it is possible to quickly navigate between any two levels of the product hierarchy. |
Daily |
rse_prod_tc_load.ksh |
|
Transform the product hierarchy to a de-normalized hierarchy representation. |
Product hierarchy interface. This is a transformation routine that restructures the normalized product hierarchy table data into a de-normalized representation where there are a fixed number of columns representing each level of the hierarchy. This design is more friendly for reporting displays. |
Daily |
rse_prod_dh_load.ksh |
|
Load the RADM location hierarchy integration ID values. |
Location hierarchy interface. This is the first part of the location hierarchy interface. It retrieves the integration IDs and creates new internal IDs as needed for new locations. |
Daily |
rse_loc_src_xref_load.ksh |
|
Load the RADM location hierarchy. |
Location hierarchy interface. This is the second part of the location hierarchy interface. It retrieves and maintains the location hierarchy descriptions and updates a normalized location hierarchy table. |
Daily |
rse_loc_hier_load.ksh |
|
Create a transitive closure representation of the location hierarchy. |
Location hierarchy interface. This is a transformation routine that restructures the normalized location hierarchy table data into a transitive closure representation so that it is possible to quickly navigate between any two levels of the location hierarchy. |
Daily |
rse_loc_hier_tc_load.ksh |
|
Transform the location hierarchy to a de-normalized hierarchy representation. |
Location hierarchy interface. This is a transformation routine that restructures the normalized location hierarchy table data into a de-normalized representation where there are a fixed number of columns representing each level of the hierarchy. This design is more friendly for reporting displays. |
Daily |
rse_loc_hier_dh_load.ksh |
|
Load product attribute data from RADM to ORASE's implementation. |
This process requires a parameter to control which set of attribute data is to be retrieved. For this process, the parameter should be PRODUCT. |
Daily |
rse_cda_etl_load.ksh |
|
Load the RADM calendar dimension. |
The calendar ETL is not a frequently changed dimension, so this routine can be scheduled weekly or less frequently as deemed appropriate. This routine is less likely to be required to run post-application setup. |
Weekly |
rse_regular_main_load.ksh |
|
Load the RADM fiscal calendar hierarchy. |
This calendar ETL is not a frequently changed dimension, so this routine can be scheduled weekly or less frequently as deemed appropriate. It ideally only needs to run as frequently as fiscal calendars are uploaded to RADM. |
Weekly |
rse_fiscal_main_load.ksh |
These processes are either required or optional for ORASE applications (CDT, DT and AC) as listed. They must be configured to execute at the frequency listed and apply to all ORASE applications except MBA. They are listed in Table 6-3 in process order.
Note that many of these processes have a corresponding initial setup script. Only the processing script is listed here. Refer to Oracle Retail Advanced Science Engine Implementation Guide, Volume 2 - Data Processes and Configuration Variables and find the details on each processing script listed here.
For more details, refer to Oracle Retail Advanced Science Engine Implementation Guide, Volume 2 - Data Processes and Configuration Variables.
Table 6-3 ORASE Recurring Batch Processes
| Description | Notes | Required by Application | Frequency | Processing Shell Script |
|---|---|---|---|---|
|
Load the RADM consumer segment. |
Consumer segment hierarchy interface. This is the routine that copies RA's consumer segment data into a suitable table for ORASE to use. This interface is a simple interface without any form of hierarchies. |
AC |
Daily |
rse_conseg_load.ksh |
|
Load the RADM consumer segment location/product allocation data. |
This interface copies the RA allocation data that signifies what percentage of available purchasing base exists for each consumer segment for a configured product hierarchy level and store locations. |
AC |
Daily |
rse_conseg_alloc_load.ksh |
|
Load the customer segment hierarchy levels. |
This script can be skipped if the data is manually added to the rse_hier_level.ctl file. |
CDT, DT |
Setup |
rse_custseg_level_load.ksh |
|
Load the RADM customer segment integration ID values. |
Customer segment interface. This is the first part of the customer segment interface that retrieves data from RA. This process retrieves the integration IDs and the creates new internal IDs as needed for new customer segments. |
CDT, DT |
Daily |
rse_custseg_src_xref_load.ksh |
|
Load the RADM customer segment hierarchy. |
Customer segment hierarchy interface. This is the second part of the customer segment hierarchy interface. It retrieves and maintains the customer segment descriptions and updates a normalized customer segment hierarchy table. |
CDT, DT |
Daily |
rse_custseg_hier_load.ksh |
|
Create a transitive closure representation of the customer segment hierarchy. |
Customer segment hierarchy interface. This is a transformation routine that restructures the normalized customer segment hierarchy table data into a transitive closure representation so that it is possible to quickly navigate between any two levels of the customer segment hierarchy. |
CDT, DT |
Daily |
rse_custseg_hier_tc_load.ksh |
|
Load the RADM customer segment to customer cross reference data. |
Customer segment hierarchy interface. This is the last part of the customer segment hierarchy interface. It retrieves a list of customer IDs from RA that are associated with a customer segment and stores the relationships in a cross reference table so that a customer ID can be classified to its appropriate customer segments. |
CDT, DT |
Daily |
rse_custseg_cust_xref_load.ksh |
|
Copy sales transaction data from RADM to ORASE. |
If sales transaction data is available in RADM, then this is the starting point of all application's access to sales metrics. All subsequent steps are derived from this data or from aggregations that were derived from this data. |
CDT (required) DT (Optional) AC (optional) |
Weekly |
rse_sls_txn_process.ksh |
|
This process performs some ETL of RADM assortment range data into a table specifically designed for DT to use. |
This is a required ETL for DT and can later be manipulated via the DT_PROD_LOC_EXCL_STG interface. |
DT |
Weekly |
dt_prod_loc_range_process.ksh |
Optional daily processes are available if alternate hierarchies are used. If used, they must be configured to execute daily and apply to all ORASE applications except MBA. They are listed in Table 6-4 in process order.
Note that many of these processes have a corresponding initial setup script. Only the processing script is listed here. Refer to Oracle Retail Advanced Science Engine Implementation Guide, Volume 2 - Data Processes and Configuration Variables and find the details on each processing script listed here.
For more details, refer to Oracle Retail Advanced Science Engine Implementation Guide, Volume 2 - Data Processes and Configuration Variables.
Table 6-4 Batch Processes for Alternate Hierarchies
| Description | Notes | Processing Shell Script |
|---|---|---|
|
Load the RADM CM Group alternate product hierarchy integration ID values. |
The CM Group alternate hierarchy allows a customer to have a custom set of product categories be used by all ORASE processes. If the traditional product hierarchy is suitable for grouping related products together, then the routines related to this alternate hierarchy should not be used. Otherwise, this interface will copy the alternate hierarchy integration IDs as defined in RA. |
rse_cm_grp_xref_load.ksh |
|
Load the RADM CM Group alternate product hierarchy. |
Alternate product hierarchy interface. This is the second part of the alternate product hierarchy interface. It retrieves and maintains the alternate product hierarchy descriptions and updates a normalized product hierarchy table. |
rse_cm_grp_hier_load.ksh |
|
Create a transitive closure representation of the product hierarchy for an alternate hierarchy. |
Alternate product hierarchy interface. This is a transformation routine that restructures the normalized product hierarchy table data into a transitive closure representation so that it is possible to quickly navigate between any two levels of the alternate product hierarchy. |
rse_prod_tc_load.ksh |
|
Transform an alternate product hierarchy to a de-normalized hierarchy representation. |
Alternate product hierarchy interface. This is a transformation routine that restructures the normalized alternate product hierarchy table data into a de-normalized representation where there are a fixed number of columns representing each level of the hierarchy. This design is more friendly for reporting displays. |
rse_prod_dh_load.ksh |
|
Load the RADM trade area hierarchy integration ID values. |
The trade area alternate hierarchy allows a customer to have a custom set of location groupings be used for all ORASE analytic processes. If the traditional location hierarchy is suitable for grouping related locations together, then the routines related to this alternate hierarchy should not be used. |
rse_trade_area_src_xref_load.ksh |
|
Load the RADM trade area hierarchy to the location hierarchy table. |
Trade area alternate location hierarchy interface. This is the second part of the trade area alternate location hierarchy interface. It retrieves and maintains the descriptions and updates a normalized location hierarchy table. |
rse_trade_area_hier_load.ksh |
|
Create a transitive closure representation of the location hierarchy for an alternate hierarchy. |
Trade area alternate location hierarchy interface. This is a transformation routine that restructures the normalized location hierarchy table data into a transitive closure representation so that it is possible to quickly navigate between any two levels of the location hierarchy. |
rse_loc_hier_tc_load.ksh |
|
Transform an alternate location hierarchy to a de-normalized hierarchy representation. |
Trade area alternate location hierarchy interface. This is a transformation routine that restructures the normalized location hierarchy table data into a de-normalized representation where there are a fixed number of columns representing each level of the hierarchy. This design is more friendly for reporting displays. |
rse_loc_hier_dh_load.ksh |
ORASE applications requires a number of additional batch process, listed in Table 6-5, beyond those that retrieve data from RA.
These processes must be configured to execute at the frequency listed and apply to ORASE applications as listed. They are shown in process order.
Note that many of these processes have a corresponding initial setup script. Only the processing script is listed here. Refer to Oracle Retail Advanced Science Engine Implementation Guide, Volume 2 - Data Processes and Configuration Variables and find the details on each processing script listed here.
For more details, refer to Oracle Retail Advanced Science Engine Implementation Guide, Volume 2 - Data Processes and Configuration Variables.
See "Market Basket Analysis Overview" for a description of the MBA batch processes.
Table 6-5 ORASE Batch Processes
| Description | Notes | Required By Application | Frequency | Processing Shell Script |
|---|---|---|---|---|
|
This script executes a process that automates the update of store attribute metadata in the appropriate AC metadata tables. |
This script maintains the AC metadata used to define the store attributes in the CIS_TCRITERIA_ATTR and CIS_BUS_OBJ_TCRITERIA_ATT_XREF metadata tables. |
AC |
As Needed |
cis_store_attr_maint.ksh |
|
This script executes a process that automates the update of consumer segment attribute metadata in the appropriate AC metadata tables. |
This script maintains the AC metadata used to define the consumer segment attributes in the CIS_TCRITERIA_ATTR and CIS_BUS_OBJ_TCRITERIA_ATT_XREF metadata tables. |
AC |
As Needed |
cis_conseg_attr_maint.ksh |
|
This script executes a process that automates the update of sales performance attribute metadata in the appropriate AC metadata tables. |
This script maintains the AC metadata used to define the sales performance attributes in the CIS_TCRITERIA_ATTR and CIS_BUS_OBJ_TCRITERIA_ATT_XREF metadata tables. |
AC |
As Needed |
cis_perf_attr_maint.ksh |
|
This script executes a process that automates the update of product attribute metadata in the appropriate AC metadata tables. |
This script maintains the AC metadata used to define the product attribute groups that were loaded via the RSE_PROD_ATTR_GRP_VALUE_STG interface. This routine synchronizes the AC metadata so it contains the relevant data that AC needs. |
AC |
As Needed |
cis_prod_attr_maint.ksh |
|
Aggregate sales transaction data to product, location and week. |
If sales transaction data has been copied via the CORE_DB_SLS_TXN database service, then this routine will create weekly aggregations of that data. This routine is mutually exclusive with the RSE_SLS_PR_LC_WK load service routine. |
DT, AC |
Weekly |
rse_wkly_sls_process.ksh |
|
Aggregate sales transaction data to product, location, customer segment and week. |
If sales transaction data has been copied via the CORE_DB_SLS_TXN database service, then this routine will create weekly aggregations of that data. This routine is mutually exclusive with the RSE_SLS_PR_LC_CS_WK load service routine. |
DT |
Weekly |
rse_wkly_sls_seg_process.ksh |
|
Calculate customer ids that are considered fake customers |
This is used to search for customer cards such as generic store cards. This routine is only usable if RSE_SLS_TXN data has been copied from RADM. |
CDT |
Weekly |
rse_fake_cust_process.ksh |
|
Aggregate weekly sales data to a configured set of product hierarchy levels, location, and week. |
This routine is dependent on configurations made in the RSE_AGGR_SRVC_CONFIG_LEVELS table. The cis_perf_attr_maint.ksh script is a prerequisite for this process. |
AC |
Weekly |
rse_wkly_sls_process.ksh |
|
Aggregate weekly sales data to a configured set of product hierarchy levels, attribute values, location, and week. |
This routine is dependent on configurations made in the RSE_AGGR_SRVC_CONFIG_LEVELS table. The cis_perf_attr_maint.ksh script is a prerequisite for this process. |
AC |
Weekly |
rse_wkly_sls_process.ksh |
|
This script calculates product attribute location share metrics for performance-based clustering metrics. |
The cis_perf_attr_maint.ksh script is a prerequisite for this process. |
AC |
Weekly |
cis_prod_attr_loc_share_process.ksh |
|
This script performs some aggregation of product location assortment range data. |
This routine should run after the loading and manipulation of the ranging data from the prior prerequisite steps. |
DT |
Weekly |
dt_loc_range_process.ksh |
|
This process executes the baseline sales calculation batch process. |
The main input to this process is the RSE_SLS_PR_LC_CS_WK_ and RSE_SLS_PR_LC_WK tables. |
DT |
Weekly |
dt_baseline_process.ksh |
|
This script maintains a table of calendar intervals that Demand Transference performs its calculations within. |
This script only needs to be executed when new fiscal calendars are loaded, although there is no harm in running routinely. The configuration that drives this process cannot be changed once the process has been executed, as the intervals need to remain uniform. The process does not completely replace all previously defined intervals; therefore, it is important to set the configurations correctly before running this step. If the configurations do need to change, then the tables that relate to this data will need to be reset. |
DT |
Weekly |
dt_updt_mdl_interval.ksh |
|
Demand Transference Model Update. |
This script looks for Demand Transference Models that are missing data for new intervals of time and executes the appropriate processes to calculate those intervals and update the Demand Transference Models. This script is recommended to be run weekly, although it can be controlled so that it only processes a portion of the models at each execution. This allows a more even spread of processing resources from one week to another. The use of this script is optional. If the implementation does not desire automatically updated models, then this routine should not be used. However, it is recommended that the models be continuously updated as new data arrives, and this routine accomplishes that. The models retain previously defined decisions, and only adjust the models via relatively small adjustments. |
DT |
Weekly |
dt_updt_model_process.ksh |
|
Auto Update escalation paths for CDT. |
This routine is needed to ensure that new customer segments or locations are assigned CDTs according to the previously defined escalation rules. Once a location and customer segment has a CDT assigned, it will not change via this process. |
CDT |
Weekly |
cdt_updt_esc_results.ksh |
|
Auto Update escalation paths for DT Models. |
This script performs routine maintenance on escalation results, so that as new locations and customer segments are added to the system, they can have Demand Transference results applied to them. |
DT |
Weekly |
dt_updt_esc_results.ksh |
|
Load product attribute metadata for ASO. This process copies some required values from ORASE tables to be used by ASO's visual guideline feature. |
Insert PAG and PAGV to ASO table. |
ASO |
As needed |
so_update_prod_attr_proc.ksh |
|
This script prepares a table of clusters to be exported to external applications. |
Upon the completion of this, routines that use data from CIS_CLUSTER_SET_EXP_VW can be executed. This is also a prerequisite for RA to obtain the cluster outputs, which RA retrieves via batch processing. |
AC |
Weekly |
cis_prepare_cluster_exp.ksh |
|
This process performs a series of calculations needed to support the DT_RDF or DT_AIP export processes. |
This process is a prerequisite for either the DT_RDF or DT_AIP job processor task. |
DT |
Weekly |
dt_export_prep.ksh |
|
This process performs a series of calculations to prepare data for export to the RDF application. |
This script does not create any export files, but does prepare data in tables so that subsequent export scripts can export appropriate data. |
DT |
Weekly |
dt_rdf_export.ksh |
|
This process performs a series of calculations to prepare data for export to the AIP application. |
This script creates a file to be sent to the AIP application, and outputs all data as processed by the DT_AIP job processor task. |
DT |
As Needed |
dt_aip_export.ksh |
ASO requires some of the same RA content that ORASE retrieves. It also requires additional inputs from CM and from other sources such as MSM and AIP, as defined in the ORASE Inputs Summary.
For these additional inputs, ASO uses ETL data load scripts that are executed either as needed or in a scheduled batch. These ETL files must adhere to the file standards defined below.
Note that product IDs and merchandise hierarchies in these ETL files must align with those received from RADM.
Interfaces between CM and ORASE are internal and not documented here. To review data received or passed to from CM, reference the Database Staging and Export view respectively. See Appendix B, "Database Detail Definitions" for details.
Historical planogram information must be loaded into the ORASE database. This information is used to define POG shelf/pegboard dimensions, categories, seasonal information, and product display geometry.
The input file interface follows the Oracle MSM file format so that MSM customers can export historical POG information to files that ASO can import. Customers who use another planogram tool must create the import files to match this interface definition.
It is recommended at implementation to import historical POGs in bulk, rather than on an ad hoc basis. Additional POGs can then be added incrementally as necessary.
|
Note: All units that are used in this interface are predefined at implementation time. |
This file defines the major characteristics of a POG, including name, category, status, seasonality, and dimensions.
Table 6-6 POG Definition File
| Example | Field | Type | Description |
|---|---|---|---|
|
POG_WINTER_104-2 |
POG_Key |
VARCHAR2(80) |
External planogram ID. |
|
GROCERY_Beverages-Coffee_02 |
POG_Name |
VARCHAR2(80) |
Planogram name. |
|
Fall GROCERY Beverages Coffee 02 |
POG_Desc |
VARCHAR2(80) |
Planogram description. |
|
FY2014 |
Season_Code |
VARCHAR2(30) |
Code that identifies the season for which the planogram should be used. |
|
Winter |
seasonal_attribute |
VARCHAR2(30) |
Attribute that describes the season of the year for which the planogram should be used. For example, spring, holiday, year-round. |
|
2013-12-21 |
effective_start_dt |
Date |
Earliest date in the year for which the planogram is effective. The format is YYYY-MM-DD. |
|
2014-03-21 |
effective_end_dt |
Date |
Planogram's end date. Last day of the year that the planogram is effective. The format is YYYY-MM-DD. |
|
Approved |
Status |
VARCHAR2(30) |
Current planogram status. Approved, Rejected, Pending, or Received. It comes in the feed as Pending. ASO provides a list of valid values for POG status within the configuration data files (so_pog_status.ctl).This description can be customized or translated but the meaning and ID of each status must remain the same since the application uses the IDs for specific purposes. The value provided here must match one of the description values within that file or the row will be rejected. |
|
Xmas~2014 |
Category_Key |
VARCHAR2(80) |
POG category key. The second lowest level of POG hierarchy. This value is mandatory. |
|
Holiday Items 2014 |
Category_Name |
VARCHAR2(80) |
POG category name. |
|
Celebration~220117 |
Sub_Category_Key |
VARCHAR2(80) |
POG sub-category key. The lowest level of POG hierarchy. This value is mandatory. |
|
Celebration |
Sub_Category_Name |
VARCHAR2(80) |
POG sub-category name. |
|
Decoration~22 |
Dept_Key |
VARCHAR2(80) |
POG department key. This value is mandatory. |
|
Decoration |
Dept_Name |
VARCHAR2(80) |
POG department name. |
|
144 |
Length |
NUMBER(18,4) |
The total length of a planogram. All units of measure must be provided using the same units, across the whole application.All dimensions for all POG components, product display styles, fixture size, and product positions must use a common unit of measure (for example, inches or centimeters). |
|
36 |
Depth |
NUMBER(18,4) |
The maximum depth of a planogram. All units of measure must be provided using the same units, across the whole application. All dimensions for all POG components, product display styles, fixture size, and product positions must use a common unit of measure (for example, inches or centimeters). |
|
96 |
Height |
NUMBER(18,4) |
The maximum height of a planogram. All units of measure must be provided using the same units, across the whole application. All dimensions for all POG components, product display styles, fixture size, and product positions must use a common unit of measure (for example, inches or centimeters). |
This file maps a POG to a particular store key. This can be a cluster of actual stores.
Table 6-7 POG Store File
| Example | Field | Type | Description |
|---|---|---|---|
|
POG_WINTER_104-2 |
POG_Key |
VARCHAR2(80) |
External planogram ID. |
|
56 |
Store_Key |
VARCHAR2(80) |
External store ID. |
|
2013-12-21 |
Effective_Start |
Date |
Start day of the year for which the historical planogram is effective for the store. Format YYYY-MM-DD. |
|
2014-03-21 |
Effective_End |
Date |
End day of the year for which the historical planogram is effective for the store. format YYYY-MM-DD. |
This file lists the display styles used in certain planograms. It lists which display styles are used in a finished POG.
This file is a cross reference between display style and orientation. This lists the valid orientations for each display style. Each display style must be mapped against at least one orientation.
Table 6-9 Display Style Orientation File
| Example | Field | Type | Description |
|---|---|---|---|
|
SCI_DS_1236808 |
DisplayStyle_Key |
VARCHAR2(80) |
The external display style ID, matching the value from the POG display style file. |
|
Front 0 |
Orientation_Key |
VARCHAR2(80) |
External orientation ID. ASO provides a list of valid orientations within the configuration data files (so_orientation.ctl). The value for orientation_ext_key within that file can be modified or translated to assign different descriptions for each value provided. The description can be changed but the meaning must remain the same since the IDs are already widely used across the application to adjust product dimensions based on the selected orientation. The value provided here must match one of the orientation_ext_key values within that file or the row will be rejected. |
|
Y |
Default_Flag |
VARCHAR2(1) |
Y indicates the orientation should be consider as the default for the display style. N indicates the orientation is valid for the display style but is not a default. |
This file provides a list of bays used by the planograms.
Table 6-10 POG Bay Configuration File
| Example | Field | Type | Description |
|---|---|---|---|
|
POG_FALL_02_BAY_02 |
Bay_Key |
VARCHAR2(80) |
The bay key is associated with certain POG only. The bay's external ID. |
|
POG_FALL_02 |
POG_Key |
VARCHAR2(80) |
The planogram's external ID. It must match the POG file ID. |
|
3 |
Bay_Sequence |
NUMBER(3) |
The position of the bay (left to right) within the planogram. |
This file provides a list of the fixtures that define the planogram.
Table 6-11 Fixture Definition File
| Example | Field | Type | Description |
|---|---|---|---|
|
SCI_POG_04_2_BAY_01_FXT_01 |
Fixture_Key |
VARCHAR2(80) |
The external fixture ID. |
|
Shelf |
Fixture_Type |
VARCHAR2(80) |
Fixture type can be Shelf, Pegboard, or Freezer Chest. ASO provides a list of supported fixture types within the configuration data files (so_fixture_type.ctl). The value for fixture type description on that file can be modified or translated to assign different descriptions for each value provided. The description can be changed but the meaning must remain the same since the IDs are already widely used across the application. The value provided here must match exactly one of the descriptions within that file or the row will be rejected. |
|
24 |
Depth |
NUMBER(18,4) |
The fixture's maximum depth. |
|
74 |
Height |
NUMBER(18,4) |
The fixture's maximum height. |
|
48 |
Width |
NUMBER(18,4) |
The fixture's maximum width. |
|
0.5 |
Vertical_Spacing |
NUMBER(18,4) |
This field is used for pegboard fixture. |
|
0.2 |
Horizontal_Spacing |
NUMBER(18,4) |
This field is used for pegboard fixture. |
|
48 |
Max_Length |
NUMBER(18,4) |
This field is used for pegboard fixture. |
|
42.5 |
Capacity_X |
NUMBER(18,4) |
Freezer length. This field is used for freezer fixture. |
|
19.5 |
Capacity_y |
NUMBER(18,4) |
Freezer depth. This field is used for freezer fixture. |
|
68.5 |
Capacity_Z |
NUMBER(18,4) |
Freezer height. This field is used for freezer fixture. |
This file describes the fixture layout in a bay. A fixture can be Shelf, Pegboard, or Freezer Chest.
Table 6-12 Fixture Configuration File
| Example | Field | Type | Description |
|---|---|---|---|
|
POG_FALL_02_BAY_02 |
Bay_Key |
VARCHAR2(80) |
The external bay ID. It must match the value in the POG Bay file. |
|
SCI_POG_04_2_BAY_01_FXT_01 |
Fixture_Key |
VARCHAR2(80) |
The external fixture ID. It must match the ID from the fixture file. |
|
0 |
Position_x |
NUMBER(18,4) |
Fixture position on the X axis relative to the bay. Origin point: bottom, left, back. |
|
0 |
Position_y |
NUMBER(18,4) |
Fixture position on the Y axis relative to the bay. Origin point: bottom, left, back. |
|
0 |
Position_z |
NUMBER(18,4) |
Fixture position on the Z axis relative to the bay. Origin point: bottom, left, back. |
Cross reference file between fixture types and display styles. This lists the fixtures for which the display style is valid.
Table 6-13 Display Style Compatibility File
| Example | Field | Type | Description |
|---|---|---|---|
|
SCI_DS_1236808 |
DisplayStyle_Key |
VARCHAR2(80) |
The external display style ID, matching the value from the POG display style file. |
|
Freezer Chest |
Fixture_Type |
VARCHAR2(80) |
Fixture type can be Shelf, Pegboard, or Freezer Chest. ASO provides a list of supported fixture types within the configuration data files (so_fixture_type.ctl). The value for fixture type description on that file can be modified or translated to assign different descriptions for each value provided. The description can be changed but the meaning must remain the same since the IDs are already widely used across the application. The value provided here must match exactly one of the descriptions within that file or the row will be rejected. |
This file is provided and required for planograms that include shelf fixtures. It provides the details for each individual shelf in the fixture.
Table 6-14 Shelf Definition File
| Example | Field | Type | Description |
|---|---|---|---|
|
POG_WINTER_01_BAY_03_FXT_01_SF_3 |
Shelf_Key |
VARCHAR2(80) |
The external shelf ID. |
|
24 |
Depth |
NUMBER(18,4) |
The shelf's physical depth. |
|
0.8 |
Height |
NUMBER(18,4) |
The shelf's physical height. This is the thickness of the shelf, not to be mistaken with the space for the product on top of the shelf. |
|
48 |
Width |
NUMBER(18,4) |
The shelf's width. |
This file describes the shelf layout in a fixture (used for shelf fixture only).
Table 6-15 Shelf Configuration File
| Example | Field | Type | Description |
|---|---|---|---|
|
SCI_POG_04_2_BAY_01 |
Bay_Key |
VARCHAR2(80) |
The external bay ID. |
|
SCI_POG_04_2_BAY_01_FXT_01 |
Fixture_Key |
VARCHAR2(80) |
The fixture external ID. |
|
SCI_POG_04_2_BAY_01_FXT_01_SF_07 |
Shelf Key |
VARCHAR2(80) |
The shelf external ID. |
|
0 |
Pos_x |
NUMBER(18,4) |
The origin point on the X axis. |
|
0 |
Pos_y |
NUMBER(18,4) |
The origin point of the Y axis. |
|
61 |
Pos_z |
NUMBER(18,4) |
The origin point on the Z axis. |
Product to display style mapping. It provides a list of display styles available for a specific product.
Table 6-16 Product Display Style File
| Example | Field | Type | Description |
|---|---|---|---|
|
1239856 |
Product_Key |
VARCHAR2(80) |
(RSE Core) It must match the merchandise key definition in RSE Core. |
|
SCI_DS_1234816 |
Display_Style_Key |
VARCHAR2(80) |
External display style ID, matching the ID from the POG display style file. It links an historical planogram with a specific product. |
|
Y |
Default Flg |
VARCHAR2(1) |
Y - Indicates the default display style for a given product.N - Indicates the combination should not be considered as a default.Each product should have one default display style. |
This file provides the display style product settings and dimensions.
Table 6-17 Display Style Definition File
| Example | Field | Type | Description |
|---|---|---|---|
|
SCI_DS_1234816 |
Display_Style_Key |
VARCHAR2(80) |
External display style ID, matching the ID from the POG display style file. |
|
1236214 - Folgers 100% Columbian Non-Flavored De-C |
Display_Style_Name |
VARCHAR2(80) |
The name associated with the display style. |
|
1236214 - Folgers 100% Columbian Non-Flavored De-C |
Display_Style_Desc |
VARCHAR2(80) |
The display style description. |
|
3.73 |
Depth |
NUMBER(18,4) |
The dimension is relevant to "Front", "0" orientation. |
|
5.25 |
Height |
NUMBER(18,4) |
The dimension is relevant to "Front", "0" orientation. |
|
7.2 |
Width |
NUMBER(18,4) |
The dimension is relevant to "Front", "0" orientation. |
|
0.2 |
Finger_Space_Above |
NUMBER(18,4) |
The gap between same product above. |
|
0.2 |
Finger_Space_Beside |
NUMBER(18,4) |
The gap between the same product side by side. |
|
0.1 |
Finger_Space_Behind |
NUMBER(18,4) |
The gap between the same product one in front of the other. |
|
0.5 |
Inter_Product_Gap |
NUMBER(18,4) |
The gap between products. This field captures the gap between different products. |
|
2 |
Max_stack |
NUMBER(10) |
The number of items that can be stacked together. This is equal to 1 if not stackable. |
|
0 |
Nesting_Height |
NUMBER(18,4) |
The product nesting height. The product does not allow nesting if all nesting dimensions are 0. |
|
0 |
Nesting_Width |
NUMBER(18,4) |
The product nesting width. The product does not allow nesting if all nesting dimensions are 0. |
|
0 |
Nesting_Depth |
NUMBER(18,4) |
The product nesting depth. The product does not allow nesting if all nesting dimensions are 0. |
|
Maroon |
Color |
VARCHAR2(30) |
The product color, which can be null. |
|
1 |
Display_Units |
NUMBER(3) |
For unit display style it is 1; otherwise, it is greater than 1. Values of null or 0 are converted to 1. |
|
Single or Unit |
Display_Style_Type |
VARCHAR2(80) |
A valid display style type, such as Case, Pallet, Single or Unit, and Tray. |
Describes the product layout of the shelf fixture. Products are always put at the lowest level of equipment; the anchor point locates at the lower left part.
Table 6-18 Shelf Product Configuration File
| Example | Field | Type | Description |
|---|---|---|---|
|
SCI_DS_1234747 |
DisplayStyle_Key |
VARCHAR2(80) |
The display style external ID. It must match the value in the POG display style file. |
|
POG_FALL_02_BAY_02 |
Bay_Key |
VARCHAR2(80) |
The bay external ID. It must match the value in the POG bay file. |
|
SCI_POG_04_2_BAY_01_FXT_01 |
Fixture_Key |
VARCHAR2(80) |
The fixture external ID. It must match the value in the POG fixture file. |
|
SCI_POG_04_2_BAY_01_FXT_01_SF_06 |
Shelf_Key |
VARCHAR2(80) |
The shelf external ID. It must match the value in the POG shelf file. |
|
Front 0 |
Orientation_Key |
VARCHAR2(80) |
The current orientation of this product within the fixture. ASO provides a list of valid orientations within the configuration data files (so_orientation.ctl). The value for orientation_ext_key within that file can be modified or translated to assign different descriptions for each value provided. The description can be changed but the meaning must remain the same since the IDs are already widely used across the application to adjust product dimensions based on the selected orientation. The value provided here must match one of the orientation_ext_key values within that file or the row will be rejected. |
|
24.5 |
Pos_x |
NUMBER(18,4) |
The position of the product within the shelf X axis. |
|
0 |
Pos_y |
NUMBER(18,4) |
The position of the product within the shelf Y axis. |
|
0 |
Pos_z |
NUMBER(18,4) |
The position of the product within the shelf Z axis. |
|
8 |
Facing_Quantity |
NUMBER(5) |
The number of the product's facings displayed on the shelf. |
Describes the product layout on a freezer/pegboard fixture. Products sare always positioned at the lowest level of equipment; the anchor point at lower left part.
Table 6-19 Pegboard Product Configuration File
| Example | Field | Type | Description |
|---|---|---|---|
|
SCI_DS_1234711 |
DisplayStyle_Key |
VARCHAR2(80) |
The display style external ID. It must match the value in the POG display style file. |
|
SCI_POG_04_2_BAY_01 |
Bay_Key |
VARCHAR2(80) |
The bay external ID. It must match the value in the POG bay file. |
|
SCI_POG_04_2_BAY_01_FXT_01 |
Fixture_Key |
VARCHAR2(80) |
The fixture external ID. It must match a value within the POG fixture file. |
|
Back 90 |
Orientation_Key |
VARCHAR2(80) |
Current orientation of this product. ASO provides a list of valid orientations within the configuration data files (so_orientation.ctl). The value for orientation_ext_key within that file can be modified or translated to assign different descriptions for each value provided. The description can be changed but the meaning must remain the same since the IDs are already widely used across the application to adjust product dimensions based on the selected orientation. The value provided here must match one of the orientation_ext_key values within that file or the row will be rejected. |
|
24.5 |
Pos_x |
NUMBER(18,4) |
The origin position of the product within the pegboard/freezer: bottom, left, back. |
|
0 |
Pos_y |
NUMBER(18,4) |
The position of the product within the pegboard/freezer Y axis. |
|
0 |
Pos_z |
NUMBER(18,4) |
The position of the product within the pegboard/freezer Z axis. |
|
8 |
Facing_Quantity |
NUMBER(5) |
The number of the product's facings displayed in the pegboard/freezer. |
This file provides for every loaded store/historical POG the ability to add a combination of generic user-defined POG attributes: 10 numeric; 5 dates; 5 text, 5 percent %. These attributes are presented within the review results UI. Aggregation and handling of these attributes is adjusted based on the data type and level of aggregation for which the data is presented within the UI.
Table 6-20 Pegboard Product Configuration File
| Example | Field | Type | Description |
|---|---|---|---|
|
POG_WINTER_104-2 |
Pog_Key |
VARCHAR2 (80 CHAR) |
Planogram unique identifier |
|
56 |
Store_Key |
VARCHAR2 (80 CHAR) |
This is the external store ID, known and shared across applications. |
|
22.4353 |
Attr_Num_Value_1 |
NUMBER (18,4) |
Generic numeric attribute |
|
1.5004 |
Attr_Num_Value_2 |
NUMBER (18,4) |
Generic numeric attribute |
|
18.211 |
Attr_Num_Value_3 |
NUMBER (18,4) |
Generic numeric attribute |
|
7.4314 |
Attr_Num_Value_4 |
NUMBER (18,4) |
Generic numeric attribute |
|
Attr_Num_Value_5 |
NUMBER (18,4) |
Generic numeric attribute |
|
|
47.7185 |
Attr_Num_Value_6 |
NUMBER (18,4) |
Generic numeric attribute |
|
19.8553 |
Attr_Num_Value_7 |
NUMBER (18,4) |
Generic numeric attribute |
|
26.1975 |
Attr_Num_Value_8 |
NUMBER (18,4) |
Generic numeric attribute |
|
Attr_Num_Value_9 |
NUMBER (18,4) |
Generic numeric attribute |
|
|
Attr_Num_Value_10 |
NUMBER (18,4) |
Generic numeric attribute |
|
|
11-DEC-2013 |
Attr_Date_Value_1 |
DATE |
Generic date attribute |
|
Attr_Date_Value_2 |
DATE |
Generic date attribute |
|
|
5-DEC-2013 |
Attr_Date_Value_3 |
DATE |
Generic date attribute |
|
18-DEC-2013 |
Attr_Date_Value_4 |
DATE |
Generic date attribute |
|
Attr_Date_Value_5 |
DATE |
Generic date attribute |
|
|
Special Store Opening |
Attr_String_Value_1 |
VARCHAR2 (80 CHAR) |
Generic text attribute |
|
Attr_String_Value_2 |
VARCHAR2 (80 CHAR) |
Generic text attribute |
|
|
Attr_String_Value_3 |
VARCHAR2 (80 CHAR) |
Generic text attribute |
|
|
Attr_String_Value_4 |
VARCHAR2 (80 CHAR) |
Generic text attribute |
|
|
Attr_String_Value_5 |
VARCHAR2 (80 CHAR) |
Generic text attribute |
|
|
0.7386 |
Attr_Pct_Value_1 |
NUMBER (5,4) |
Generic percentage attribute |
|
0.3725 |
Attr_Pct_Value_2 |
NUMBER (5,4) |
Generic percentage attribute |
|
0.9714 |
Attr_Pct_Value_3 |
NUMBER (5,4) |
Generic percentage attribute |
|
Attr_Pct_Value_4 |
NUMBER (5,4) |
Generic percentage attribute |
|
|
0.7109 |
Attr_Pct_Value_5 |
NUMBER (5,4) |
Generic percentage attribute |
This file provides the replenishment parameters at the product/location level. Each product/location must have a corresponding replenishment record.
Table 6-21 Assortment and Space Optimization Replenishment Parameters File
| Example | Field | Type | Description |
|---|---|---|---|
|
1234582 |
Product_Key |
VARCHAR2(80) |
This is the external ID that is known and shared across applications. For placeholder products, this field contain the CM placeholder product key. |
|
56 |
Location_Key |
VARCHAR2(80) |
This is the external store ID, known and shared across applications. |
|
6 |
casepack |
NUMBER(18,4) |
Product casepack for a given store. |
|
2 |
replenishment_freq |
NUMBER(18,4) |
Replenishment frequency (RF) = number of replenishments to the shelf per week. |
|
2 |
replenishment_type |
NUMBER(10) |
Replenishment source/type - two options: 1 = from DC/vendor, 2 = from back room. |
|
0 |
transit_time |
NUMBER(10,2) |
Transit time (TT) = number of days it takes an orde.r to go from source (DC or backroom) to shelf. |
|
2 |
shelf_replenishment_tt |
NUMBER(10) |
Shelf replenishment trigger type - 3 options: 1 = cover demand over replenishment period + transit time 2 = replenishment when inventory gets to a target percentage of capacity 3 = replenishment when a casepack can fit |
|
95 |
shelf_replenishment_param |
NUMBER(18,4) |
Shelf replenishment parameter (currently only applies for option 2). |
|
0.05 |
stdev_booster |
NUMBER(10,6) |
Standard deviation booster (number greater than or equal to 0, makes sense to limit to 1). |
|
7 |
days_of_sales_per_wk |
NUMBER(3,2) |
Days of sales per week (number between 1 and 7). |
|
0 |
facings_lift |
NUMBER(5,4) |
Facing lift parameter. |
This file contains the POG hierarchy to assortment product mapping information. This data is used to identify which POG should be used for each product.
Table 6-22 Assortment and Space Optimization POG to Assortment Mapping File
| Example | Field | Type | Description |
|---|---|---|---|
|
Decoration~22 |
pog_dept_key |
VARCHAR2(80) |
This is the POG dept key. This is a POG hierarchy external key known to the external source. This is a mandatory value. |
|
Xmas~2201 |
pog_category_key |
VARCHAR2(80) |
This is the POG category key. This is a POG hierarchy external key known to the external source. This is a mandatory value. |
|
Celebration~220117 |
pog_sub_category_key |
VARCHAR2(80) |
This is the POG sub-category key. This is a POG hierarchy external key known to the external source. This value is mandatory. |
|
CLS |
assort_product_level |
VARCHAR2(80) |
This is an identifier to the product level within the product hierarchy. This value must match the ASO product hierarchy. |
|
CLS~19~877 |
assort_product_key |
VARCHAR2(80) |
This is an identifier to a node within the merchandise hierarchy. It can be a specific product or any other node not higher than the assortment product category level within the merchandise hierarchy. |
|
100 |
demand_spread_factor |
NUMBER(6,3) |
This is the demand spread factor. This value is normally null, meaning that a 100% demand is assigned to the POG node. In specific cases where the product is placed on multiple POG nodes, a demand spread factor can be used to split the demand across those multiple POGs. Values can be from 0 to 100. |
This file contains the POG season-to-assortment date mapping. Once the mapping from product to POG has been performed, a second pass examines this table to identify the specific correct season for the POG to use, based on the assortment start date.
Table 6-23 Assortment and Space Optimization POG Season-to-Assortment Mapping File
| Example | Field | Type | Description |
|---|---|---|---|
|
Decoration~22 |
pog_dept_key |
VARCHAR2(80) |
This is the POG dept key. This is a POG hierarchy external key known to the external source. This is a mandatory value. |
|
Xmas~2201 |
pog_category_key |
VARCHAR2(80) |
This is the POG category key. This is a POG hierarchy external key known to the external source. This is a mandatory value. If the sub- category key is missing, then this value will become the lowest level within the POG hierarchy. |
|
Celebration~220117 |
pog_sub_category_key |
VARCHAR2(80) |
This is the POG sub-category key. This is a POG hierarchy external key known to the external source. This value can be missing. If it is present this is the lowest level within the POG hierarchy |
|
Winter |
seasonal_attribute |
VARCHAR2(30) |
This refers to a specific year-independent time period (season) for a CM assortment and a POG set. Examples include Spring, holiday, back to school, year-round, Fall, Winter. |
|
0000-12-21 |
min_assort_start_dt |
Date |
The year component is irrelevant. The year component should be delivered as 0000. This is a year-independent time period. The assortment start date is matched within the date range specified by this minimum assortment start date and the maximum assortment start date. The format is YYYY-MM-DD. |
|
0000-03-20 |
max_assort_start_dt |
Date |
The year component is irrelevant. The year component should be delivered as 0000.This is a year-independent time period. The assortment start date is matched within the date range specified by the minimum assortment start date and this maximum assortment start date. The format is YYYY-MM-DD. |
This file is used to accept an optional client feed that provides product-specific stacking height limits.
Table 6-24 Assortment and Space Optimization POG Season-to-Assortment Mapping File
| Example | Field | Type | Description |
|---|---|---|---|
|
1234582 |
product_key |
VARCHAR2(80) |
This is the external product ID that is known and shared across applications. |
|
22 |
stack_height_limit |
NUMBER(18,4) |
This is the stacking height limit for the specific product. The value must be provided using the same measurement units that are used for all other product dimensions. |
|
Y |
enabled_flg |
VARCHAR2(1) |
This flag indicates if the product stacking height limit should be used or not. Y means that the value specified will be used. N means that the value will be ignored and the application global value will be used for the product. |
ASO generates results for optimized assortments, optimized POGs, and replenishment updates.
ASO does not generate export files. Instead, a set of database views is created representing the output of the ASO application. It is up to the implementation team to extract what is needed from these views and import any required results into CM and optionally a replenishment system such as RMS/AIP. ASO does not support database views for planogram results. These views define ASO's output for CM and AIP (or other replenishment system).
This is a database view for ASO output to CM.
Table 6-25 SO_ASSORT_INT_VW
| Column Name | Data Type | Comments | Nullable |
|---|---|---|---|
|
SO_ASSORTMENT_ID |
NUMBER(10) |
Application internal unique assortment ID. |
N |
|
LABEL |
VARCHAR2(50) |
Assortment label as received from the external interface. |
Y |
|
TRADE_AREA_LABEL |
VARCHAR2(80) |
Trade area label as received from the external interface. |
Y |
|
ASSORTMENT_EXT_ID |
VARCHAR2(80) |
Assortment external ID/key as received from the external interface. |
Y |
|
ASSORTMENT_SET_ID |
VARCHAR2(80) |
External value used by APO to bring together multiple user requests that belong to the same assortment group. |
Y |
|
ASSORT_PRODUCT_CATEGORY_KEY |
VARCHAR2(80) |
Assortment product category external key. |
N |
|
ASSORT_PRODUCT_CATEGORY_NAME |
VARCHAR2(80) |
Assortment product category name |
N |
|
ASSORT_ROLE |
VARCHAR2(50) |
Assortment role as received from the external interface. |
Y |
|
ASSORT_TACTIC |
VARCHAR2(100) |
Assortment tactic as received from the external interface. |
Y |
|
ASSORT_GOAL |
VARCHAR2(50) |
Assortment goal as received from the external interface. |
Y |
|
SO_ASSORT_CLUSTER_ID |
NUMBER(10) |
Application internal unique assortment cluster ID. |
N |
|
ASSORT_CLUSTER_NAME |
VARCHAR2(80) |
Assortment cluster name as received from the external interface. |
N |
|
EXT_CLUSTER_KEY |
VARCHAR2(80) |
Assortment cluster external ID or key, as received from the external interface. |
N |
|
START_DT |
DATE |
Assortment cluster or store start date as received from the external interface. |
Y |
|
END_DT |
DATE |
Assortment cluster or store end date as received from the external interface. |
Y |
|
SO_LOC_HIER_ID |
NUMBER(10) |
Application internal unique store ID. |
N |
|
LOC_EXT_KEY |
VARCHAR2(80) |
Store external key as received from the external interface. |
N |
|
LOC_EXT_CODE |
VARCHAR2(80) |
Store external ID or code as received from the external interface. |
Y |
|
LOC_NAME |
VARCHAR2(255) |
Application store internal name. |
Y |
|
SO_PROD_HIER_ID |
NUMBER(10) |
Application internal unique product ID. |
Y |
|
PROD_EXT_KEY |
VARCHAR2(80) |
Product external key as received from the external interface. |
N |
|
PROD_EXT_CODE |
VARCHAR2(80) |
Product external ID or code as received from the external interface. |
Y |
|
PROD_NAME |
VARCHAR2(255) |
Product name. |
Y |
|
FACING_QTY |
NUMBER |
Application calculated number of facings. |
Y |
|
SALES_QTY |
NUMBER |
Application calculated sales units. |
Y |
|
SALES_AMT |
NUMBER |
Application calculated sales amount. |
Y |
|
MARGIN_AMT |
NUMBER |
Application calculated margin value. |
Y |
|
DAYS_OF_SUPPLY |
NUMBER |
Application-calculated days of supply. |
Y |
|
LOST_SALES_QTY |
NUMBER |
Application-calculated lost sales units. |
Y |
|
DEMAND_QTY |
NUMBER |
Application-calculated demand units. |
Y |
|
SERVICE_LEVEL |
NUMBER |
Application-calculated service level. |
Y |
|
POGSET_LOCATION_COUNT |
NUMBER |
POG set location count with facing quantity greater than zero. |
Y |
|
POG_LENGTH |
NUMBER |
Main POG length for the SKU/Store |
Y |
|
CATEGORY_LENGTH |
NUMBER |
Total POG length across POGs that only include products for the single category |
Y |
|
EXPORTED_DT |
DATE |
This date/time is internal to space optimization application and it indicates the time when the user accepts the assortment results and they become available to external applications. |
N |
This is a database view defining ASO output to a replenishment system. (AIP is the template.)
Table 6-26 SO_ASSORT_CM_INT_VW
| Column Name | Data Type | Comments | Nullable |
|---|---|---|---|
|
SO_ASSORTMENT_ID |
NUMBER(10) |
Application internal unique assortment ID. |
N |
|
LABEL |
VARCHAR2(50) |
Assortment label as received from the external interface. |
Y |
|
TRADE_AREA_LABEL |
VARCHAR2(80) |
Trade area label as received from the external interface. |
Y |
|
ASSORTMENT_SET_ID |
VARCHAR2(80) |
External value used by APO to bring together multiple user requests that belong to the same assortment Group. |
N |
|
ASSORTMENT_EXT_ID |
VARCHAR2(80) |
Assortment external ID or key as received from the external interface. |
Y This value is NULL for the Assortment Finalization records, since multiple assortment_ext_ids are included in those results. |
|
ASSORT_PRODUCT_CATEGORY_KEY |
VARCHAR2(80) |
Assortment product category external key. |
N |
|
ASSORT_PRODUCT_CATEGORY_NAME |
VARCHAR2(80) |
Assortment product category name. |
N |
|
ASSORT_ROLE |
VARCHAR2(50) |
Assortment role as received from the external interface. |
Y |
|
ASSORT_TACTIC |
VARCHAR2(100) |
Assortment tactic as received from the external interface. |
Y |
|
ASSORT_GOAL |
VARCHAR2(50) |
Assortment goal as received from the external interface. |
Y |
|
SO_ASSORT_CLUSTER_ID |
NUMBER(10) |
Application internal unique assortment cluster ID. |
N |
|
ASSORT_CLUSTER_NAME |
VARCHAR2(80) |
Assortment cluster name as received from the external interface. |
N |
|
EXT_CLUSTER_KEY |
VARCHAR2(80) |
Assortment cluster external ID or key, as received from the external interface. |
N |
|
START_DT |
DATE |
Assortment cluster or store start date as received from the external interface. |
Y |
|
END_DT |
DATE |
Assortment cluster or store end date as received from the external interface. |
Y |
|
SO_LOC_HIER_ID |
NUMBER(10) |
Application internal unique store ID. |
N |
|
LOC_EXT_KEY |
VARCHAR2(80) |
Store external key as received from the external interface. |
N |
|
LOC_EXT_CODE |
VARCHAR2(80) |
Store external ID or code as received from the external interface. |
Y |
|
LOC_NAME |
VARCHAR2(255) |
Application store internal name. |
Y |
|
SO_PROD_HIER_ID |
NUMBER(10) |
Application internal unique product ID. |
Y |
|
PROD_EXT_KEY |
VARCHAR2(80) |
Product external key as received from the external interface. |
N |
|
PROD_EXT_CODE |
VARCHAR2(80) |
Product external ID or code as received from the external interface. |
Y |
|
PROD_NAME |
VARCHAR2(255) |
Product name. |
Y |
|
FACING_QTY |
NUMBER |
Application calculated number of facings. |
Y |
|
SALES_QTY |
NUMBER |
Application calculated sales units. |
Y |
|
SALES_AMT |
NUMBER |
Application calculated sales amount. |
Y |
|
MARGIN_AMT |
NUMBER |
Application calculated margin value. |
Y |
|
POG_LENGTH |
NUMBER |
Main POG length for the SKU/store. |
Y |
|
CATEGORY_LENGTH |
NUMBER |
Total POG Length across POGs that only include products for the single category. |
Y |
|
EXPORTED_DT |
DATE |
This date/time is internal to space optimization application and it indicates the time the user accepts the assortment results and they become available to external applications. |
N |
This view is a composite view containing contents of both SO_ASSORT_CM_INT_VW and SO_ASSORT_AIPREPL_INT_VW.
Table 6-27 SO_ASSORT_AIPREPL_INT_VW
| Column Name | Data Type | Comments | Nullable |
|---|---|---|---|
|
SO_ASSORTMENT_ID |
NUMBER(10) |
Application internal unique assortment ID. |
N |
|
ASSORTMENT_SET_ID |
VARCHAR2(80) |
External value used by APO to bring together multiple user requests that belong to the same assortment group. |
N |
|
ASSORTMENT_EXT_ID |
VARCHAR2(80) |
Assortment external ID or key as received from the external interface. |
Y This value is NULL for the Assortment Finalization records, since multiple assortment_ext_ids are included in those results. |
|
SO_LOC_HIER_ID |
NUMBER(10) |
Application internal unique store ID. |
N |
|
LOC_EXT_KEY |
VARCHAR2(80) |
Store external key as received from the external interface. |
N |
|
LOC_EXT_CODE |
VARCHAR2(80) |
Store external ID or code as received from the external interface. |
Y |
|
LOC_NAME |
VARCHAR2(255) |
Application store internal name. |
Y |
|
SO_PROD_HIER_ID |
NUMBER(10) |
Application internal unique product ID. |
Y |
|
PROD_EXT_KEY |
VARCHAR2(80) |
Product external key as received from the external interface. |
N |
|
PROD_EXT_CODE |
VARCHAR2(80) |
Product external ID or code as received from the external interface. |
Y |
|
PROD_NAME |
VARCHAR2(255) |
Product name. |
Y |
|
SERVICE_LEVEL |
NUMBER |
Application calculated service level. |
Y |
|
POGSET_LOCATION_COUNT |
NUMBER |
POG Set location count with facing quantity greater than zero. |
Y |
|
START_DT |
DATE |
Assortment cluster or store start date as received from the external interface. |
Y |
|
END_DT |
DATE |
Assortment cluster or store end date as received from the external interface. |
Y |
|
DAYS_OF_SUPPLY |
NUMBER |
Application calculated days of supply. |
Y |
|
EXPORTED_DT |
DATE |
This date/time is internal to space optimization application and it indicates the time when the user accepts the assortment results and they become available to external applications. |
N |
This view delivers the cross reference between planograms and finalized assortments that have products placed on them.
Table 6-28 SO_POG_ASSORT_INT_VW
| Column Name | Data Type | Comments | Nullable? |
|---|---|---|---|
|
POG_KEY |
NUMBER(10) |
ASO's internal optimization planogram identifier (Run POG ID). |
N |
|
ASSORTMENT_CODE |
VARCHAR2(80) |
Finalized Assortment set identifier as received from the external interface.with APO. |
N |
|
FINALIZED_DT |
TIMESTAMP(6) |
Date/time the assortment set finalized by APO. This is the time the data from the assortment becomes final and available through this interface. |
N |
This view delivers POG header information corresponding to planograms that have been used by finalized assortments.
Table 6-29 SO_POG_DTL_INT_VW
| Column Name | Data Type | Comments | Nullable? |
|---|---|---|---|
|
POG_KEY |
NUMBER(10) |
ASO's internal optimization planogram identifier (Run POG ID). |
N |
|
POG_NAME |
VARCHAR2 (80) |
Optimization planogram's name |
Y |
|
POG_DESCRIPTION |
VARCHAR2 (200) |
Optimization planogram's description. |
Y |
|
POG_REVISION |
NUMBER |
Not available. Hard coded value = 1 (as requested by MSM). |
N |
|
POG_STATUS |
CHAR (7) |
Not available. Hard coded value = Current as requested by MSM). |
N |
|
POG_DEPT_CODE |
VARCHAR2 (80) |
Planogram set department key. |
N |
|
POG_DEPT |
VARCHAR2 (80) |
Planogram set department name. |
N |
|
POG_CAT_CODE |
VARCHAR2 (80) |
Planogram set category key. |
N |
|
POG_CAT |
VARCHAR2 (80) |
Planogram set category name. |
N |
|
POG_SUBCAT_CODE |
VARCHAR2 (80) |
Planogram set sub-category key. |
N |
|
POG_SUBCAT |
VARCHAR2 (80) |
Planogram set sub-category name. |
N |
|
SEASON |
VARCHAR2 (30) |
Planogram set seasonal attribute. |
N |
|
SEASON_CODE |
VARCHAR2 (30) |
Planogram set season code. |
N |
|
LENGTH |
NUMBER (18,4) |
Planogram length. |
N |
|
DEPTH |
NUMBER (18,4) |
Planogram maximum depth. |
N |
|
HEIGHT |
NUMBER (18,4) |
Planogram maximum height. |
N |
|
MODIFIED_DATE |
TIMESTAMP(6) |
Finalized date. This field hosts the last time an assortment that uses the planogram was finalized. This value should be used to identify changes on the planogram data set. |
N |
|
EFFECTIVE_DATE |
DATE |
Planogram start date. |
N |
|
EXPIRY_DATE |
DATE |
Planogram end date. |
N |
|
CAN_SPLIT |
NUMBER |
Not available. Hard coded value = 0 (as requested by MSM). |
N |
|
TRAFIC_FLOW_L_TO_R |
NUMBER |
Traffic flow left to Right. Not available. Hard coded value = 1 (as requested by MSM). |
N |
|
ASSORTMENT_CODE |
VARCHAR2 (20) |
Assortment code is not provided here; instead there is a separate cross reference table that provided that data. Many assortments can be linked to the same planogram. |
Y |
|
CATEGORY_ROLE |
VARCHAR2 (20) |
Not available. |
Y |
|
CATEGORY_STRATEGY |
VARCHAR2 (20) |
Not available. |
Y |
|
TEXT1 … TEXT20 |
VARCHAR2 (20) |
Not available. |
Y |
|
NUMBER1…NUMBER20 |
NUMBER |
Not available. |
Y |
|
FLAG1…FLAG10 |
VARCHAR2 (1) |
Not available. |
Y |
|
DATE1…DATE5 |
DATE |
Not available. |
Y |
|
SOURCE |
VARCHAR2 (3) |
Hard coded value = ASO (as requested by MSM). |
N |
This view delivers planogram equipment components for planograms that have been used by finalized assortments.
Note that WT indicates wall thickness. Chest walls are calculated as follows: The ASO view assumes that the capacity is central to the chest dimensions in X and Y and at the top in Z. So wall thickness is (chest size - capacity)/2 and the base size is (chest height - capacity).
Table 6-30 SO_POG_EQUIPMENT_INT_VW
| Column Name | Data Type | Comments | Nullable? |
|---|---|---|---|
|
POG_KEY |
VARCHAR2(80) |
External planogram identifier. |
N |
|
EQUIPMENT_KEY |
VARCHAR2 (46) |
ASO's internal optimization planogram equipment identifier. Shelves IDs are prefixed with "Shelf-" to make them unique and prevent collision with fixture IDs. |
N |
|
PARENT_EQUIPMENT_KEY |
VARCHAR2 (80) |
This value is populated only for shelves. The value is a reference to the parent ASO's internal optimization shelf fixture. Shelf fixtures in this interface are denoted as "Base". |
N |
|
EQUIPMENT_NAME |
VARCHAR2 (80) |
Equipment (fixture/shelf) name. |
N |
|
EQUIPMENT_TYPE |
VARCHAR2 (320) |
Four different equipment types are delivered. Shelf fixtures = "Base" Freezer fixtures = "Freezer Chest" Pegboard fixtures = "Pegboard" Shelves = "Shelf" ASO "Bays" are not included in the interface. |
N |
|
EQUIPMENT_COLOR |
NUMBER |
Not available Hard coded value = 13882323 (as requested by MSM). |
N |
|
LENGTH_SIZE |
NUMBER (18,4) |
Fixture of shelf length. |
N |
|
DEPTH_SIZE |
NUMBER (18,4) |
Fixture of shelf depth. |
N |
|
HEIGHT_SIZE |
NUMBER (18,4) |
ASO receives and uses the freezer dimensions rotated as shown below. The view transposes the dimensions to turn the freezer back to the original position. The rotation is performed as follows (freezers only) ASO length remains as length in the view. ASO depth is delivered as height in the view ASO height is delivered as depth in the view. |
N |
|
HORIZONTAL_POSITION |
NUMBER |
Horizontal position within the planogram. Each base, pegboard or freezer chest horizontal position is adjusted to account for the prior bays/fixtures within the planogram (The EQUIPMENT view aggregates the positions of the fixtures to allow for the bay sequences.) |
N |
|
DEPTH_POSITION |
NUMBER |
Depth shelf/fixture position. |
N |
|
VERTICAL_POSITION |
NUMBER |
Vertical shelf/fixture position. |
N |
|
ROTATION_SLOPE_X |
NUMBER |
Not available. Hard coded value = 0 (as requested by MSM). |
N |
|
ROTATION_ROLL_Y |
NUMBER |
Not available. Hard coded value = 0 (as requested by MSM). |
N |
|
ROTATION_ANGLE_Z |
NUMBER |
Not available Hard coded value = 0 (as requested by MSM) |
N |
|
WT_LEFT |
NUMBER |
(Chest length - capacity_x) /2 |
N |
|
WT_RIGHT |
NUMBER |
(Chest length - capacity_x) /2 |
N |
|
WT_FRONT |
NUMBER |
This uses ASO dimensions before rotating the freezer. (Chest height - capacity_z)/2 |
N |
|
WT_BACK |
NUMBER |
This uses ASO dimensions before rotating the freezer. (Chest height - capacity_z)/2 |
N |
|
WT_BASE |
NUMBER |
This uses ASO dimensions before rotating the freezer. (Chest depth - capacity_y)/ |
N |
|
WT_TOP |
NUMBER |
This is set to zero. |
N |
|
DIVIDERS_WIDTH |
NUMBER |
Not available. |
Y |
|
DIVIDERS_DEPTH |
NUMBER |
Not available. |
Y |
|
DIVIDERS_HEIGHT |
NUMBER |
Not available. |
Y |
|
DIVIDERS_AT_START |
NUMBER |
Not available. Hard coded value = 0 (as requested by MSM). |
N |
|
DIVIDERS_AT_END |
NUMBER |
Not available. Hard coded value = 0 (as requested by MSM). |
N |
|
DIVIDERS_BETWEEN |
NUMBER |
Not available. Hard coded value = 0 (as requested by MSM). |
N |
|
GRILLE_HEIGHT |
NUMBER |
Not available. |
Y |
|
PEG_START_HORIZONTAL |
NUMBER |
Not available. |
Y |
|
PEG_START_VERTICAL |
NUMBER |
Not available. |
Y |
|
PEG_WIDTH |
NUMBER |
Horizontal spacing. |
Y |
|
PEG_HEIGHT |
NUMBER |
Vertical spacing. |
Y |
|
PEG_DROP |
NUMBER |
Not available. |
Y |
|
FINALIZED_DT |
TIMESTAMP(6) |
Date/time the assortment set was finalized by APO. This is the time the data from the assortment becomes final and available through this interface. |
N |
This view delivers planogram/stores cross reference (for planograms that have been used by finalized assortments)
Table 6-31 SO_POG_LOC_INT_VW
| Column Name | Data Type | Comments | Nullable? |
|---|---|---|---|
|
POG_KEY |
NUMBER (10) |
ASO's internal optimization planogram identifier (Run POG ID) |
N |
|
STORE_KEY |
VARCHAR2 (80 Char) |
This is the external store ID, known and shared across applications (RMS ID) |
N |
|
EFFECTIVE_DATE |
DATE |
Planogram start date |
N |
|
EXPIRY_DATE |
DATE |
Planogram end date |
N |
|
SALES |
NUMBER |
Not available |
Y |
|
PROFIT |
NUMBER |
Not available |
Y |
|
MOVEMENT |
NUMBER |
Not available |
Y |
|
TEXT1 |
VARCHAR2 (80 Char) |
Optimization level string attribute 1 |
Y |
|
TEXT2 |
VARCHAR2 (80 Char) |
Optimization level string attribute 2 |
Y |
|
TEXT3 |
VARCHAR2 (80 Char) |
Optimization level string attribute 3 |
Y |
|
TEXT4 |
VARCHAR2 (80 Char) |
Optimization level string attribute 4 |
Y |
|
TEXT5 |
VARCHAR2 (80 Char) |
Optimization level string attribute 5 |
Y |
|
TEXT6 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT7 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT8 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT9 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT10 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT11 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT12 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT13 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT14 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT15 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT16 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT17 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT18 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT19 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
TEXT20 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
NUMBER1 |
NUMBER (18,4) |
Optimization level numeric attribute 1 |
Y |
|
NUMBER2 |
NUMBER (18,4) |
Optimization level numeric attribute 2 |
Y |
|
NUMBER3 |
NUMBER (18,4) |
Optimization level numeric attribute 3 |
Y |
|
NUMBER4 |
NUMBER (18,4) |
Optimization level numeric attribute 4 |
Y |
|
NUMBER5 |
NUMBER (18,4) |
Optimization level numeric attribute 5 |
Y |
|
NUMBER6 |
NUMBER (18,4) |
Optimization level numeric attribute 6 |
Y |
|
NUMBER7 |
NUMBER (18,4) |
Optimization level numeric attribute 7 |
Y |
|
NUMBER8 |
NUMBER (18,4) |
Optimization level numeric attribute 8 |
Y |
|
NUMBER9 |
NUMBER (18,4) |
Optimization level numeric attribute 9 |
Y |
|
NUMBER10 |
NUMBER (18,4) |
Optimization level numeric attribute 10 |
Y |
|
NUMBER11 |
NUMBER (5,4) |
Optimization level percentage attribute 1 |
Y |
|
NUMBER12 |
NUMBER (5,4) |
Optimization level percentage attribute 2 |
Y |
|
NUMBER13 |
NUMBER (5,4) |
Optimization level percentage attribute 3 |
Y |
|
NUMBER14 |
NUMBER (5,4) |
Optimization level percentage attribute 4 |
Y |
|
NUMBER15 |
NUMBER (5,4) |
Optimization level percentage attribute 5 |
Y |
|
NUMBER16 |
NUMBER |
Not available |
Y |
|
NUMBER17 |
NUMBER |
Not available |
Y |
|
NUMBER18 |
NUMBER |
Not available |
Y |
|
NUMBER19 |
NUMBER |
Not available |
Y |
|
NUMBER20 |
NUMBER |
Not available |
Y |
|
FLAG1 |
VARCHAR2 (1 Byte) |
Not available |
Y |
|
FLAG2 |
VARCHAR2 (1 Byte) |
Not available |
Y |
|
FLAG3 |
VARCHAR2 (1 Byte) |
Not available |
Y |
|
FLAG4 |
VARCHAR2 (1 Byte) |
Not available |
Y |
|
FLAG5 |
VARCHAR2 (1 Byte) |
Not available |
Y |
|
FLAG6 |
VARCHAR2 (1 Byte) |
Not available |
Y |
|
FLAG7 |
VARCHAR2 (1 Byte) |
Not available |
Y |
|
FLAG8 |
VARCHAR2 (1 Byte) |
Not available |
Y |
|
FLAG9 |
VARCHAR2 (1 Byte) |
Not available |
Y |
|
FLAG10 |
VARCHAR2 (1 Byte) |
Not available |
Y |
|
DATE1 |
DATE |
Optimization level date attribute 1 |
Y |
|
DATE2 |
DATE |
Optimization level date attribute 2 |
Y |
|
DATE3 |
DATE |
Optimization level date attribute 3 |
Y |
|
DATE4 |
DATE |
Optimization level date attribute 4 |
Y |
|
DATE5 |
DATE |
Optimization level date attribute 5 |
Y |
|
FINALIZED_DT |
TIMESTAMP(6) |
Date/time the assortment set was finalized by APO. This is the time the data from the assortment becomes final and available through this interface. |
N |
This view delivers finalized assortment products hierarchies. ASO uses generic product hierarchy levels. (Each installation could have different names and levels.) MSM requested the following names for the given hierarchy levels: Level 1: Company, Level 2: Division, Level 3: Group, Level 4: Department, Level 5: Class, Level 6: Subclass, Level 7: Style.
Table 6-32 SO_POG_PROD_HIER_INT_VW
| Column Name | Data Type | Comments | Nullable? |
|---|---|---|---|
|
COMPANY |
VARCHAR2 (255) |
ASO hierarchy level 1 (node) name |
N |
|
COMPANY_CODE |
VARCHAR2 (80) |
ASO hierarchy level 1. This is the external hierarchy level ID that is known and shared across applications. (From RMS) |
N |
|
DIVISION |
VARCHAR2 (255) |
ASO hierarchy level 2 (node) name |
N |
|
DIVISION_CODE |
VARCHAR2 (80) |
ASO hierarchy level 2. This is the external hierarchy level ID that is known and shared across applications. (From RMS) |
N |
|
GRP |
VARCHAR2 (255) |
ASO hierarchy level 13 (node) name |
N |
|
GRP_CODE |
VARCHAR2 (80) |
ASO hierarchy level 3. This is the external hierarchy level ID that is known and shared across applications. (From RMS) |
N |
|
DEPARTMENT |
VARCHAR2 (255) |
SO's hierarchy level 4 (node's) name |
N |
|
DEPARTMENT_CODE |
VARCHAR2 (80) |
ASO hierarchy level 4. This is the external hierarchy level ID that is known and shared across applications. (From RMS) |
N |
|
CLASS |
VARCHAR2 (255) |
ASO hierarchy level 5 (node) name |
N |
|
CLASS_CODE |
VARCHAR2 (80) |
ASO hierarchy level 5. This is the external hierarchy level ID that is known and shared across applications. (From RMS) |
N |
|
SUBCLASS |
VARCHAR2 (255) |
ASO hierarchy level 6 (node) name |
N |
|
SUBCLASS_CODE |
VARCHAR2 (80) |
ASO hierarchy level 6. This is the external hierarchy level ID that is known and shared across applications.(From RMS) |
N |
|
STYLE |
VARCHAR2 (255) |
ASO hierarchy level 7 (node) name |
Y |
|
STYLE_CODE |
VARCHAR2 (80) |
ASO hierarchy level 7. This is the external hierarchy level ID that is known and shared across applications. (From RMS) |
Y |
|
STYLE_COLOR |
VARCHAR2 (1) |
Not available |
Y |
|
STYLE_COLOR_CODE |
VARCHAR2 (1) |
Not available |
Y |
|
FINALIZED_DT |
TIMESTAMP(6) |
Date/time the assortment set was finalized by APO. This is the time the data from the assortment becomes final and available through this interface. |
N |
This view delivers finalized assortment products placed within optimized planograms.
Table 6-33 SO_POG_PROD_POS_INT_VW
| Column Name | Data Type | Comments | Nullable? |
|---|---|---|---|
|
POG_KEY |
NUMBER |
ASO internal optimization planogram identifier (Run POG ID) |
N |
|
EQUIPMENT_KEY |
VARCHAR2 (46) |
ASO internal optimization planogram equipment identifier. Shelves IDs are prefixed with "Shelf-" to make them unique and prevent collision with fixture IDs. Products can be placed on Pegboards, Freezer chest and shelves (never in a "base"). |
N |
|
SKU_KEY |
VARCHAR2 (80) |
This is the external product ID that is known and shared across applications. (From RMS) |
N |
|
DISPLAY_STYLE_TYPE |
VARCHAR2 (80) |
Display style type used to place the product in the planogram. For example, Single or Unit, Tray, Case, or Pallet. |
N |
|
FACING_HORIZONTAL |
NUMBER |
Number of horizontal facings of the product |
N |
|
FACING_DEPTH |
NUMBER |
Number of facings deep |
N |
|
FACING_STACK |
NUMBER |
Number of facings above |
N |
|
SIZE_LENGTH |
NUMBER (18,4) |
Facing length dimension |
N |
|
SIZE_DEPTH |
NUMBER (18,4) |
Facing depth dimension |
N |
|
SIZE_HEIGHT |
NUMBER (18,4) |
Facing height dimension |
N |
|
HORIZONTAL_POSITION |
NUMBER |
Product horizontal position relative to the fixture or shelf |
N |
|
DEPTH_POSITION |
NUMBER |
Z and Y position of products on shelves are calculated by the view based on the product size and shelf thickness to give the bottom-back-left corner's position of the product relative to the bottom-back-left corner of the shelf, pegboard or chest. The depth product position is calculated like this ((SO_RUN_POG_SHELF.DEPTH - SO_DISPLAY_STYLE.DEPTH)*-1). For a product within freezer a different approach is used to account for the rotation of the axis: (((ASO freezer.height-) (ASO product height in the freezer)-(product_height))*(-1) |
N |
|
VERTICAL_POSITION |
NUMBER |
Shelf and freezer are set to zero. Pegboards The Z position on pegboards corresponds to the left bottom corner of the product as positioned in the pegboard. |
N |
|
ROTATION_SLOPE_X |
NUMBER |
Hard coded value = 0 (as requested by MSM) |
Y |
|
ROTATION_ROLL_Y |
NUMBER |
Hard coded value = 0 (as requested by MSM) |
Y |
|
ROTATION_ANGLE_Z |
NUMBER |
Hard coded value = 0 (as requested by MSM) |
Y |
|
ORIENTATION |
VARCHAR2 (80 Char) |
String value corresponding to the orientation used to place the product. For example, Front 0, Top 90, Bottom 180. |
Y |
|
VC_ORIENTATION |
VARCHAR2 (80 Char) |
VC = Vertical Caps Not available |
Y |
|
VC_FACING_HORIZONTAL |
NUMBER |
Not available |
Y |
|
VC_FACING_DEPTH |
NUMBER |
Not available |
Y |
|
VC_FACING_STACKING |
NUMBER |
Not available |
Y |
|
VC_SIZE_LENGTH |
NUMBER |
Not available |
Y |
|
VC_SIZE_DEPTH |
NUMBER |
Not available |
Y |
|
VC_SIZE_HEIGHT |
NUMBER |
Not available |
Y |
|
SC_ORIENTATION |
NUMBER |
SC=Side Caps Not available |
Y |
|
SC_FACING_HORIZONTAL |
NUMBER |
Not available |
Y |
|
SC_FACING_DEPTH |
NUMBER |
Not available |
Y |
|
SC_FACING_STACKING |
NUMBER |
Not available |
Y |
|
SC_SIZE_LENGTH |
NUMBER |
Not available |
Y |
|
SC_SIZE_DEPTH |
NUMBER |
Not available |
Y |
|
SC_SIZE_HEIGHT |
NUMBER |
Not available |
Y |
|
FINALIZED_DT |
TIMESTAMP(6) |
Date/time the assortment set was finalized by APO. This is the time the data from the assortment becomes final and available through this interface. |
Y |
This view contains product's display style information. It calculates some of the metrics under the assumption that each product has a unit display style defined.
Table 6-34 SO_POG_SKU_DTL_INT_VW
| Column Name | Data Type | Comments | Nullable? |
|---|---|---|---|
|
SKU_KEY |
VARCHAR2 (80) |
This is the external product ID that is known and shared across applications. (From RMS) |
N |
|
SKU_NAME |
VARCHAR2 (255) |
ASO product description. If NULL then use product's name. |
N |
|
SKU_ABBREV |
VARCHAR2 (255) |
ASO product name |
N |
|
BRAND |
VARCHAR2 (1) |
Not available |
Y |
|
MANUFACTURER |
VARCHAR2 (1) |
Not available |
Y |
|
SUPPLIER |
VARCHAR2 (1) |
Not available |
Y |
|
PARENT_PRODUCT_CODE |
VARCHAR2 (80) |
This is the external product hierarchy level ID that is known and shared across applications. (From RMS) This value links to a parent within the product hierarchy view. |
N |
|
EFFECTIVE_DATE |
DATE |
Not available Set to sysdate as requested by MSM. |
N |
|
EXPIRY_DATE |
DATE |
Not available |
Y |
|
QUANTITY |
NUMBER (10) |
Units to display from the Units display style |
Y |
|
COLOR |
NUMBER |
Not available Set to 16777215 as requested by MSM. |
Y |
|
SHAPE |
CHAR (3) |
Not available Set to Box as requested by MSM. |
Y |
|
DFLT_ORIENTATION |
VARCHAR2 (80) |
Default orientation from the product's UNIT display style |
N |
|
VALID_ORIENTATIONS |
NUMBER |
This value is calculated by adding the bitwise values corresponding to all the orientations that are valid for the product. |
N |
|
WEIGHT |
NUMBER |
Not Available |
Y |
|
LENGTH |
NUMBER (18,4) |
Product's default unit display style length |
N |
|
DEPTH |
NUMBER (18,4) |
Product's default unit display style depth |
N |
|
HEIGHT |
NUMBER (18,4) |
Product's default unit display style height |
N |
|
NESTING_HORIZONTAL |
NUMBER |
Product's default unit display style nesting length |
N |
|
NESTING_DEPTH |
NUMBER |
Product's default unit display style nesting depth |
N |
|
NESTING_VERTICAL |
NUMBER |
Product's default unit display style nesting height |
N |
|
HORIZONTAL_CF |
NUMBER |
CF=Crush Factor Not Available Set to 0 as requested by MSM. |
N |
|
DEPTH_CF |
NUMBER |
Not Available Set to 0 as requested by MSM |
N |
|
VERTICAL_CF |
NUMBER |
Not Available Set to 0 as requested by MSM |
N |
|
FINGER_SPACE_HORIZONTAL |
NUMBER |
Product's default unit display style finger space horizontal |
N |
|
FINGER_SPACE_DEPTH |
NUMBER |
Product's default unit display style finger space depth |
N |
|
FINGER_SPACE_VERTICAL |
NUMBER |
Product's default unit display style finger space vertical |
N |
|
MAX_STACKING |
NUMBER |
Product's default unit display style max stacking |
Y |
|
JUMBLE_FACTOR |
NUMBER |
Not Available Set to 0 as requested by MSM. |
Y |
|
PH1_OFFSET_HORIZONTAL |
NUMBER |
PH1 = Peg Hole 1 Not Available |
Y |
|
PH1_OFFSET_DEPTH |
NUMBER |
Not Available |
Y |
|
PH1_OFFSET_VERTICAL |
NUMBER |
Not Available |
Y |
|
PH2_OFFSET_HORIZONTAL |
NUMBER |
PH2 = Peg Hole 2 Not Available |
Y |
|
PH2_OFFSET_DEPTH |
NUMBER |
Not Available |
Y |
|
PH2_OFFSET_VERTICAL |
NUMBER |
Not Available |
Y |
|
TRAY_LENGTH |
NUMBER |
Tray display style length |
Y |
|
TRAY_DEPTH |
NUMBER |
Tray display style depth |
Y |
|
TRAY_HEIGHT |
NUMBER |
Tray display style height |
Y |
|
TRAY_QTY_WIDE |
NUMBER |
Tray display style length/unit display style length |
Y |
|
TRAY_QTY_DEEP |
NUMBER |
Tray display style depth/unit display style depth |
Y |
|
TRAY_QTY_HIGH |
NUMBER |
Tray display style height/unit display style height |
Y |
|
CASE_LENGTH |
NUMBER |
Case display style length |
Y |
|
CASE_DEPTH |
NUMBER |
Case display style depth |
Y |
|
CASE_HEIGHT |
NUMBER |
Case display style height |
Y |
|
CASE_QTY_WIDE |
NUMBER |
Case display style length/unit display style length |
Y |
|
CASE_QTY_DEEP |
NUMBER |
Case display style depth/unit display style depth |
Y |
|
CASE_QTY_HIGH |
NUMBER |
Case display style height/unit display style height |
Y |
|
DISPLAY_DIM_LENGTH |
NUMBER (18,4) |
Product's default unit display style length |
Y |
|
DISPLAY_DIM_DEPTH |
NUMBER (18,4) |
Product's default unit display style depth |
Y |
|
DISPLAY_DIM_HEIGHT |
NUMBER (18,4) |
Product's default unit display style height |
Y |
|
TEXT1 … TEXT20 |
VARCHAR2 (20 Char) |
Not available |
Y |
|
NUMBER1…NUMBER20 |
NUMBER |
Not available |
Y |
|
FLAG1…FLAG10 |
VARCHAR2 (1 Char) |
Not available |
Y |
|
DATE1…DATE5 |
DATE |
Not available |
Y |
|
FINALIZED_DT |
TIMESTAMP(6) |
Date/time the assortment set was finalized by APO. This is the time the data from the assortment becomes final and available through this interface. |
N |
