Mirror of Oracle documentation

Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.

AI Foundation Data Standalone Processes

The primary function of standalone processes in the AI Foundation Data (AIF DATA) schedule is to load history data in a new environment for use in one or more applications on the platform. These process flows group together multiple, related programs that load data files, stage them in the database, and transform them into multiple target tables in the data warehouse. Processes are also available for integrations with Planning applications (MFP, AP, and so on) and Xstore.

Adjustments History Load

Module Name HIST_CSV_ADJUSTMENTS_LOAD_ADHOC Description Loads the ADJUSTMENT.csv file into the data warehouse and populates key data tables used to integrate with other systems for history data. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Historical Data Load

Design Overview

The history load process for Inventory Adjustment transactions accepts an input file at the transaction level using the file specification for ADJUSTMENT.csv . It assumes the file has already been moved into place using the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a preprocessing table in the database, transforms it to RI’s internal staging tables, then loads it into the base fact (item/location/day), as well as the week aggregate used for integrations (item/location/week). The Reason dimension is also seeded with records if the reason code and reason description are provided on the transactions.

Note

This process does not currently populate BI aggregate tables. Those jobs need to be run separately after each execution of this process if it is necessary to use this data for reporting in RI.

Key Tables Affected

Table Usage W_ADJUSTMENT_FTS File Input W_REASON_DS Staging W_DOMAIN_MEMBER_DS_TL Staging W_RTL_INVADJ_IT_LC_DY_FS Staging W_RTL_INVADJ_IT_LC_DY_F Output (Base Fact) W_RTL_INVADJ_IT_LC_WK_A Output (Aggregate)

Table	Usage
W_DOMAIN_MEMBER_LKP_TL	Output (Reason Descriptions)

Aggregate Fact History Load

Module Name HIST_AGGR_FACT_LOAD_ADHOC Description Loads pre-aggregated fact data from flat files into the data warehouse for data that is above the item/location intersection. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Historical Data Load

Design Overview

The history load process for aggregate fact data is intended for new customers or migrating customers that cannot provide item/location level history for sales, inventory, and other areas. Pre-aggregated data can be loaded into one of four identical file interfaces that support a wide variety of measures across all functional areas. Each interface must have data for a single intersection (such as subclass/area/week) and the intersection must be configured in C_ODI_PARAM_VW before running the loads. This process will take care of importing the files and loading the data into data warehouse tables for storage.

There are jobs included in this process to validate the partition structure of these tables, because they support flexible partitioning by day or week levels. The validator jobs (such as W_FACT1_PROD1_LC1_T1_F_VALIDATOR_JOB ) verify whether the partition structure is correct relative to the configuration in C_ODI_PARAM_VW . If the configuration and table structures do not match, it will automatically drop and re-create all partitions in the expected format. This will result in dropped data as well, so it is important to verify the configuration before loading any data.

When using these tables, the overall flow of data is as follows:

1. Update the configuration table ( C_ODI_PARAM_VW ) from the Control & Tactical Center to specify your base intersections for each table.

a. The parameter names for these tables are prefixed with RI_FACT , such as RI_FACT1_PROD_LEVEL for the product level of the FACT1 table.
b. The complete list of values you may put into these parameters is provided below.

Product (PROD)	Organization (ORG)	Calendar (CAL)
CMP	COMPANY	YEAR
DIV	CHAIN	HALFYEAR
GRP	AREA	QUARTER
DEPT	REGION	PERIOD
CLS	DISTRICT	WEEK
SBC	LOCATION	DAY
ITEM	CHANNEL	GREGORIANYEAR
ALL	PLANNING_CHANNEL	GREGORIANQUARTER
FLEX1 -FLEX20	PRICE_ZONE	GREGORIANMONTH

Product (PROD)	Organization (ORG)	Calendar (CAL)
	ALL	GREGORIANDAY

2. Update the table partition configuration in C_MODULE_EXACT_TABLE by setting the PARTITION_COLUMN_TYPE and PARTITION_INTERVAL as WK for week data or DY for day data. Calendar levels above week level do not require partitioning as it’s assumed the data volume will be low.

3. Enable the FACT1 through FACT4 modules (based on your intended usage) in C_MODULE_ARTIFACT by setting the PARTITION_FLG and ACTIVE_FLG to Y .

4. Use the process CREATE_PARTITION_ADHOC to re-execute the partitioning programs for any configuration changes made above, unless you have not yet loaded any calendar data, in which case the CALENDAR_LOAD_ADHOC process will also perform partitioning for these tables.

5. Use this process ( HIST_AGGR_FACT_LOAD_ADHOC ) to populate staging tables and move data from staging ( FS ) to target ( F ) tables. Enable all jobs relating to the table(s) you will be loading (Each table load has a COPY , STG , VALIDATOR , TMP , and F job, and all of them should be enabled).

a. Records may be rejected due to bad/missing data in the 3 base dimensions supported on the facts (product, location, calendar). The job does not fail if rejects occur; it will load any valid records.
b. Rejected records will be copied to E$ tables such as E$_W_RTL_FACT1_PROD1_LC1_T1_TMP . E$ tables are created dynamically when records get rejected so the table may not exist initially.
c. E$ tables will contain the full rejected record, which you may insert back into the staging ( FS ) tables later to attempt to load them again.
d. E$ tables will not purge or drop data unless you perform a full schema cleanup or database clone from another environment.

6. If sending the data to a Planning application, use the processes LOAD_PDS_FACT1_AGGR_PROCESS_ADHOC through LOAD_PDS_FACT4_AGGR_PROCESS_ADHOC to export the contents of each table to PDS. The integration tables for PDS will have similar names as the source tables, only adding PDS in the name. For example, W_RTL_FACT1_PROD1_LC1_T1_F is loaded to W_PDS_FACT1_PROD1_LC1_T1_F by the process LOAD_PDS_FACT1_AGGR_PROCESS_ADHOC .

Key Tables Affected

Table	Usage
W_RTL_FACT1_PROD1_LC1_T1_FS	Staging
W_RTL_FACT2_PROD2_LC2_T2_FS	Staging
W_RTL_FACT3_PROD3_LC3_T3_FS	Staging
W_RTL_FACT4_PROD4_LC4_T4_FS	Staging
W_RTL_FACT1_PROD1_LC1_T1_F	Output
W_RTL_FACT2_PROD2_LC2_T2_F	Output
W_RTL_FACT3_PROD3_LC3_T3_F	Output
W_RTL_FACT4_PROD4_LC4_T4_F	Output

Allocation History Load

Module Name HIST_ALCDETAIL_LOAD_ADHOC Description Loads a full snapshot of allocations data from W_RTL_ALC_DETAILS_DS.dat and W_RTL_ALC_IT_LC_DY_FS.dat to initialize the dimension and fact data before the nightly batch is enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Historical Data Load

Design Overview

Data regarding allocations of merchandise is split between two interfaces, the dimension file W_RTL_ALC_DETAILS_DS.dat and the fact file W_RTL_ALC_IT_LC_DY_FS.dat . This process can be used to load full snapshots of historical or currently active allocations to the data warehouse outside the nightly batch cycle. The two files must be in sync, meaning that every allocation record on the detail file must have a record in the header file. The header file is always a full snapshot of all allocations that should appear as currently active in the data warehouse, meaning that if any allocation records are no longer sent on W_RTL_ALC_DETAILS_DS.dat , they will be marked as inactive/closed in the data warehouse table ( CURRENT_FLG = N ) and should no longer appear in the files.

This data is used primarily for Retail Insights reporting and for Inventory Planning Optimization (IPO).

Key Tables Affected

Table Usage W_RTL_ALC_DETAILS_DS Staging W_RTL_ALC_DETAILS_D Output W_RTL_ALC_IT_LC_DY_FS Staging W_RTL_ALC_IT_LC_DY_F Output

Assortment Group Load

Module Name LOAD_ASSORT_GROUP_DATA_ADHOC Description Loads assortment group information from the Assortment Planning (AP) application export table to Retail Insights. Dependencies None Business Activity Historical Data Load

Design Overview

This process is responsible for extracting data on assortment groups from integration table AP_ASSORT_GROUP_EXP in the RDX01 schema. This table is populated by the Assortment Planning (AP) application when users create and approve assortment groups for their planning processes to use. The assortment groups can be used in Retail Insights for reporting on

specific data points that allow aggregating up to group level, such as sales history and optimized sales. This process is only for ad hoc executions of the extract, as the jobs can also be run as part of nightly batch cycles.

Key Tables Affected

Table	Usage
AP_ASSORT_GROUP_EXP	Input Data
W_RTL_ASSORT_GRP_DS	Staging
W_RTL_ASSORT_GRP_LC_DS	Staging
W_RTL_ASSORT_GRP_D	Output
W_RTL_ASSORT_GRP_LC_D	Output

Base Fact Data Removal

Module Name	DELETE_F_DATA_ADHOC
Description	Delete a set of records from a base fact (`F`) table using the fact staging (`FS`) table as the basis for removal.
Dependencies	None
Business Activity	Nightly Batch Processing

Design Overview

This process will perform a delete action on a specified base fact table in the AIF data warehouse, using the fact’s staging table as the input to determine which rows to delete. This program is necessary to remove base fact records from a transactional fact such as sales or receipts when you are trying to load updated records that already have data in the target table. Transactional facts load data additively, so if the data is not deleted prior to loading new records, the values will be summed instead of being overwritten. The most common reason to use this process is when a problem was identified in the data loaded during a nightly batch run or historical dataload, and you have now prepared corrected data and loaded it as far as the staging table. Run this job to clean up the target table before loading the new data. It is assumed that after you correct the base fact table in this manner, you will also run the aggregation utility to regenerate the fact’s aggregate tables. This program will not delete data from aggregate tables, only the base fact table. The program only supports facts that are at a fixed day level of the calendar (target table has a DT_WID type of column), it will not work with aggregate facts above day level.

You must update the parameters of the job in POM before executing the process. The job requires two input values: the name of the staging table and the name of the fact table. For example:

W_RTL_SLS_TRX_IT_LC_DY_FS W_RTL_SLS_TRX_IT_LC_DY_F

Running the job with these values in the parameter string will delete all data in W_RTL_SLS_TRX_IT_LC_DY_F , which has matching records in W_RTL_SLS_TRX_IT_LC_DY_FS .

Batch Individual File Reprocessing

Module Name REPROCESS_ZIP_FILE_PROCESS_ADHOC Description Looks for the RI_REPROCESS_DATA.zip file and unpacks it, moving any files to the incoming directory for batch processes. Dependencies None Business Activity Nightly Batch Processing

Design Overview

This process moves and unloads a ZIP file (specifically RI_REPROCESS_DATA.zip ) so that the file contents may be added to an in-progress nightly batch run of the AIF DATA schedule. The ZIP file may contain one or multiple files. The ZIP file must not contain any folders, as all ZIP file processing will be done only on files at the root level of a ZIP. If any folders are detected in the file, the process will fail. It only needs to contain the files that you wish to update for the current batch run. Unlike the other ZIP file processes, this process does not archive or delete existing files in the system, so it can safely be used repeatedly to upload new files on top of existing data.

You can use REPROCESS_ZIP_FILE_PROCESS_ADHOC when the nightly ZIP was incomplete or generated for the wrong business date and a corrected ZIP must be loaded for the same schedule run. For example:

1. Obtain the corrected RAP_DATA.zip.

2. Rename the corrected file to RI_REPROCESS_DATA.zip.

3. Upload it to the configured Object Store incoming location, such as ris/incoming.

4. Run REPROCESS_ZIP_FILE_PROCESS_ADHOC.

5. Rerun the failed batch validation or downstream job.

Batch Nightly File Reprocessing

Module Name NIGHTLY_ZIP_FILE_PROCESS_ADHOC Description Looks for the RAP_DATA.zip file and any other nightly ZIP file name and unpacks it, deleting all existing nightly files and copying in the new ones. Dependencies None Business Activity Nightly Batch Processing

Design Overview

This process moves and unloads nightly ZIP files (such as RAP_DATA.zip ) so that the file contents may be used for an in-progress nightly batch run of the AIF DATA schedule. The ZIP file(s) must contain all data files you need for a nightly batch run. This process contains the exact same jobs as the nightly AIF DATA batch and the primary purpose is to let you reload a new ZIP file when your current nightly batch has failed or you’ve accidentally provided the wrong upload and need to replace it.

The program uses the C_LOAD_DATES table to support restartability if it runs multiple times for the same or different ZIP files, based on the following scenarios:

The C_LOAD_DATES entry it creates has PACKAGE_NAME=’ZIPUNLOAD’ .
When you run it as part of the nightly batch or as part of this ad hoc job, it creates the entry in the C_LOAD_DATES table.
When you have a “Success” status in C_LOAD_DATES , then you can rerun the job by placing a new zip file; it will then unzip it in the internal Expand directory (retaining older extracted files in the Expand directory if it is not overwritten).
When you have a status like “Failed”, then rerunning will mark it as “Success” and retain the files in the Expand directory; it will not erase existing files.
When you don’t have any entry in the C_LOAD_DATES table, then running it erases all the directories, then unzips your file into the internal location and processes them as needed.

Cost Adjustments History Load

Module Name	HIST_INVADJC_LOAD_ADHOC
Description	Loads the`W_RTL_INVADJC_IT_LC_DY_FS.dat`fle into the data warehouse and populates key data tables used in BI reporting.
Dependencies	HIST_ZIP_FILE_LOAD_ADHOC
Business Activity	Historical Data Load

Design Overview

The history load process for Inventory Cost Adjustment transactions accepts an input file at the transaction level using the file specification for W_RTL_INVADJC_IT_LC_DY_FS.dat . It assumes the file has already been moved into place using the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a staging table in the database and then loads it into the base fact (item/location/day), as well as the week aggregate used for BI reporting (item/location/week).

Key Tables Affected

Table	Usage
W_RTL_INVADJC_IT_LC_DY_FS	Staging
W_RTL_INVADJC_IT_LC_DY_F	Output (Base Fact)
W_RTL_INVADJC_IT_LC_WK_A	Output (Aggregate)

Cost History Load

Module Name	HIST_COST_LOAD_ADHOC
Description	Loads the`COST.csv`fle into the data warehouse and populates key data tables used to integrate with other systems for history data.
Dependencies	HIST_ZIP_FILE_LOAD_ADHOC
Business Activity	Historical Data Load

Design Overview

The history load process for Cost data accepts an input file at the item/location/date level using the file specification for COST.csv . It assumes the file has already been moved into place using the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a preprocessing table in the database, transforms it to RI’s internal staging tables, then loads it into the base facts for Base Cost and Net Cost. The history load process uses the same source file for both facts, the same way as the nightly batch programs function when loading cost data from files.

Multiple weeks of cost data can be provided in a single file, though it is recommended not to load more than one quarter at a time, unless the volumes are relatively low. Cost data must start with a full snapshot of item/locations requiring cost values at the start of history on the earliest day in history that you will be loading. From then on, you can provide only the cost changes on the dates that they occur. The cost history load will iterate through the provided files one day at a time and load the available cost changes for each date in order.

The C_HIST_LOAD_STATUS configuration table determines the actions taken by the process. Before running the process for the first time, you must set up this table for the history load date to be the very latest date you expect to load history for (this can be changed later if needed to load more weeks). Once that setup is complete, you can begin processing files from the earliest date you plan to load. You must start from the beginning of the history and load it sequentially. You cannot load data out of order, and you cannot load the same date multiple times without first erasing the data from the database. After a date is loaded successfully, the C_HIST_LOAD_STATUS records are updated with the most recent load status and date. When the process reaches the final date to be loaded, it will also seed the current positions into the W_RTL_BCOST_IT_LC_G and W_RTL_NCOST_IT_LC_G tables automatically. Make sure not to run the process for this final date multiple times without cleaning up data, as it will attempt to insert new data into these two tables every time it reaches the last history load date.

Note

This process does not populate BI aggregate tables. The aggregation utility needs to be run separately after execution of this process if it is necessary to use this data for reporting in RI.

Key Tables Affected

Table	Usage
W_COST_FTS	File Input
W_RTL_BCOST_IT_LC_DY_FS	Staging
W_RTL_NCOST_IT_LC_DY_FS	Staging
W_RTL_BCOST_IT_LC_DY_F	Output (Base Fact)
W_RTL_NCOST_IT_LC_DY_F	Output (Base Fact)
W_RTL_BCOST_IT_LC_G	Output (Current Positions)
W_RTL_NCOST_IT_LC_G	Output (Current Positions)

Custom Hierarchy Load

Module Name	CUSTOM_HIER_LOAD_ADHOC
Description	Loads Custom Hierarchy dimension and confguration data for use in Planning.
Dependencies	HIST_ZIP_FILE_LOAD_ADHOC
Business Activity	Historical Data Load

Design Overview

This process loads custom hierarchy definitions and configuration options. Custom hierarchies are additional hierarchy structures that go beyond the core foundation data elements (product, location, and calendar) and require a flexible and generic interface to accept the data. The configuration tables for type and level information may only be loaded using this process or by updating them from the Control & Tactical Center. They will not be populated in the AIF DATA nightly batch as they should not be changing frequently once defined.

The process includes a validation job ( DIM_CUSTOM_HIER_VALIDATOR_JOB ) that will check the custom hierarchy configurations. The validator will fail with various error codes and details written to the job logs depending on the issues encountered. Refer to the Data Validation Framework chapter for the explanation of the validation errors.

Key Tables Affected

Input Table	Target Table
W_RTL_CUSTOM_HIER_TYPE_DS	W_RTL_CUSTOM_HIER_TYPE_D
W_RTL_CUSTOM_HIER_LEVEL_DS	W_RTL_CUSTOM_HIER_LEVEL_D
W_RTL_CUSTOM_HIER_DS	W_RTL_CUSTOM_HIER_D
W_RTL_CUSTOM_HIER_MAP_FS	W_RTL_CUSTOM_HIER_MAP_F

Customer Loyalty Load

Module Name HIST_CUST_LYL_LOAD_ADHOC Description Loads Customer Loyalty dimension and fact data for use in Retail Insights reporting. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Historical Data Load Activity

Design Overview

This process will load the Customer Loyalty dimension and fact files. This data is used for Retail Insights reporting only. All jobs in the process should be enabled before running it.

Key Tables Affected

Input Table Target Table W_RTL_CUST_LYL_PROG_DS W_RTL_CUST_LYL_PROG_D W_RTL_CUST_LYL_ACCT_DS W_RTL_CUST_LYL_ACCT_D W_RTL_CUST_LYL_AWD_ACCT_DS W_RTL_CUST_LYL_AWD_ACCT_D W_RTL_CUST_LYL_TRX_LC_DY_FS W_RTL_CUST_LYL_TRX_LC_DY_F W_RTL_CUST_LYL_AWD_TRX_DY_FS W_RTL_CUST_LYL_AWD_TRX_DY_F W_RTL_CUST_LYL_TRX_LC_DY_F W_RTL_CUST_LYL_PROG_LC_DY_A

Data Security Load

Module Name RAF_SEC_FILTER_LOAD_ADHOC Description Copies the data security staging table data (which is populated from IW) into the target tables, such as RAF_SEC_USER . Dependencies None Business Activity Application Administration

Design Overview

This process loads data for AIF data security functionality. The tables populated by this process limit what data an end user can see in certain AIF applications, such as RI and PMO. This data load flow only accepts data from IW, and is an alternative to sending flat files as part of the nightly batch process. This is only a replacement for the flat file load; if there are any jobs downstream in the applications that must be run, those are still required. The steps to use this process are:

1. Implement data security integration in Innovation Workbench/APEX to retrieve the users, groups, and data filter definitions from an external source. You may use REST APIs or custom file loads for this integration.

2. Develop SQL statements or procedures to insert your data into the staging tables (listed below).

3. Run the RAF_SEC_FILTER_LOAD_ADHOC process, which will truncate the target tables and insert your newly staged data. If a staging table is empty, then it will not truncate the target table.

The entire process could be automated by establishing REST APIs that post into IW tables, a stored procedure that pushes the data from IW into the RAF staging tables, and then adding a DBMS_SCHEDULER job that runs the POM process. Because a truncate-and-load process is used, you must maintain the full set of data security records somewhere to push into the AIF tables.

Key Tables Affected

Staging Table	Target Table
RAF_SEC_USER_STG	RAF_SEC_USER
RAF_SEC_GROUP_STG	RAF_SEC_GROUP

Staging Table Target Table RAF_SEC_USER_GROUP_STG RAF_SEC_USER_GROUP RAF_FILTER_GROUP_MERCH_STG RAF_FILTER_GROUP_MERCH RAF_FILTER_GROUP_ORG_STG RAF_FILTER_GROUP_ORG

Deal Actuals History Load

Module Name HIST_DEAL_LOAD_ADHOC Description Loads the W_RTL_DEALACT_IT_LC_DY_FS.dat file into the data warehouse and populates the target fact table for BI reporting. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Historical Data Load

Design Overview

The history load process for Deal Actuals accepts an input file at the deal/item/location/date level using the file specification for W_RTL_DEALACT_IT_LC_DY_FS.dat . It assumes the file has already been moved into place using the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a staging table in the database and loads it into the base fact (item/ location/day) table for reporting.

Key Tables Affected

Table	Usage
W_RTL_DEALACT_IT_LC_DY_FS	Staging
W_RTL_DEALACT_IT_LC_DY_F	Output (Base Fact)

Deal Income History Load

Module Name HIST_CSV_DEAL_INCOME_LOAD_ADHOC Description Loads the DEAL_INCOME.csv file into the data warehouse and populates key data tables used to integrate with other systems for history data. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Historical Data Load

Design Overview

The history load process for Deal Income transactions accepts an input file at the transaction level using the file specification for DEAL_INCOME.csv . It assumes the file has already been moved into place using the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a preprocessing table in the database, transforms it to RI’s internal staging tables, and then loads it into the base fact (item/location/day) as well as the week aggregate used for integrations (item/location/week).

Key Tables Affected

Table Usage W_RTL_DEALINC_IT_LC_DY_FTS File Input W_RTL_DEALINC_IT_LC_DY_FS Staging W_RTL_DEALINC_IT_LC_DY_F Output (Base Fact) W_RTL_DEALINC_IT_LC_WK_A Output (Aggregate)

Default Calendar Initialization

Module Name AUTO_GEN_CALENDAR_LOAD_ADHOC Description Automatically generates a generic NRF fiscal calendar and sets up the data warehouse database with it. Dependencies None Business Activity Initial System Setup

Design Overview

The auto-generated calendar process does not require any input files. Instead, it uses an internal calendar definition based on the National Retail Federation (NRF) 4-5-4 business calendar to populate the Retail Insights data model with basic calendar information. The NRF calendar typically starts around the first week of February and runs for 52 or 53 weeks, depending on the year. The default calendar starts from January 2017 and extends for approximately 30 years. It automatically includes 53-week years where appropriate and follows the NRF guidelines for fiscal weeks and periods.

This process performs all the necessary transform and load jobs required to set up the RAP data warehouse calendar. This process should only be used if you cannot get a business calendar definition from any other source, and the retailer does not want to provide a file themselves. Once this process runs, you can disable W_MCAL_PERIOD_DS_JOB in your nightly batch if you do not intend to ever provide a calendar file directly.

This process also populates the Gregorian system calendar at the same time the fiscal calendar is loaded. The Gregorian calendar requires additional start and end date parameters from C_ODI_PARAM to define the time range to generate. It must be greater than the range of time in the fiscal calendar. Output tables that start with W_MCAL_ are mainly used for fiscal calendar generation, while the other tables, such as W_DAY_D , are used for the Gregorian calendar. All output tables must be successfully populated with calendar data to use the platform.

Key Tables Affected

Table	Usage
W_MCAL_PERIOD_DS	Staging
W_TIME_OF_DAY_D	Output
W_DAY_D	Output
W_YEAR_D	Output

Table	Usage
W_QTR_D	Output
W_MONTH_D	Output
W_WEEK_D	Output
W_MINUTE_OF_DAY_D	Output
W_MCAL_CONFIG_G	Output
W_MCAL_CAL_D	Output
W_MCAL_PERIOD_D	Output
W_MCAL_DAY_D	Output
W_MCAL_WEEK_D	Output
W_MCAL_YEAR_D	Output
W_MCAL_QTR_D	Output
W_RTL_MCAL_DAY_SHIFT_D	Output
W_RTL_MCAL_DAY_UNSHIFT_D	Output
W_RTL_MCAL_DAY_GUNSHIFT_D	Output
W_RTL_MCAL_DAY_CUSTOM_D	Output
W_RTL_MCAL_WEEK_SHIFT_D	Output
W_RTL_MCAL_WEEK_UNSHIFT_D	Output
W_RTL_MCAL_PERIOD_SHIFT_D	Output
W_RTL_MCAL_PERIOD_UNSHIFT_D	Output

ETL Business Date Update

Module Name RESET_BUSINESS_DATE_ADHOC Description Override the current business date used for loading data into the data warehouse. Dependencies None Business Activity Batch Administration

Design Overview

This process updates the current business date in the foundation data warehouse. The current business date usually reflects the most recent data loaded into the platform. For example, if the last nightly batch imported data is for February 2^nd , 2023, the current business date will be that same date. Specific to Retail Insights, this date is also used whenever calculating a repository variable like CurrentDate or CurrentWeek . There may be times during an implementation of an Analytics and Planning solution where you need to alter this date, such as when you are loading historical data for a specific past date and want the data warehouse to reflect that date as the current date. During nightly batch operations, you may use this process to change the AIF business date, such as when resolving BATCH_VALIDATION_JOB errors. In such cases, you must use this process to set the business date as the first step before running those other processes.

The RESET_BUSINESS_DATE_JOB within this process requires a date as an input parameter. The date must be in the format YYYY-MM-DD . When you run the process from the POM UI, ensure

you edit the parameters for this job first and enter the date at the end of the parameter string. Do not delete the rest of the parameters on the job, only change the date after the RI_ADHOC_BUSINESS_DT parameter. This job will also manage its own updates to the C_LOAD_DATES table, so it does not require clearing C_LOAD_DATES before running the job.

Sample payload when using an API to call the process:

{
"cycleName":"Adhoc",
"flowName":"Adhoc",
"processName":"RESET_BUSINESS_DATE_ADHOC",
"requestParameters":"jobParams.RESET_BUSINESS_DATE_JOB=disJobType=ODI_JOB_RUN|
|name=PLP_RETAILRESETBUSINESSDATEGENERAL||context=DEVELOPMENT||type=SCN||
logLevel=5||RA_BI.RI_ADHOC_BUSINESS_DT:2017-12-31"
}

Key Tables Affected

Ta	ble	Usage
W_	RTL_CURR_MCAL_G	Output

Fix Unusable Indexes

Module Name FIX_UNUSABLE_INDEX_ADHOC Description Repair broken database indexes that may result from erasing and reloading dimension or fact data repeatedly Dependencies None Business Activity Batch Administration

Design Overview

This process executes a single job, FIX_UNUSABLE_INDEX_JOB , which repairs any database indexes in an unusable state. The most common reason to execute this job is that you have encountered the error:

ORA-26026: unique index <INDEX NAME> initially in unusable state

When this error occurs, you must run this process to correct it. If you have a failed batch job that encountered this error, you may restart that job from POM after running this process. This job also runs as part of the AIF DATA weekly maintenance process RI_MAINTENANCE_ADHOC , which should be scheduled to run every week before your nightly batches.

Flexible Fact Load

Module Name FLEXFACT_LOAD_ADHOC Description Loads the flexible fact tables for use in AIF applications and Retail Insights reporting. Dependencies HIST_ZIP_FILE_LOAD_ADHOC

Business Historical Data Load Activity

Design Overview

This process populates the flex fact tables that are used in both AI Foundation to display custom attributes and measures and in Retail Insights reporting on custom fact data. Prior to loading flex fact data, you must configure the proper data levels in C_ODI_PARAM to match the hierarchy levels used in the files. Refer to the Retail Insights Implementation Guide for additional details on configuring flex facts.

There is a job included in this process to validate the partition structure of the FLEXFACT1 table, because it supports flexible partitioning by day or week levels. The validator job ( W_RTL_FLEXFACT1_F_VALIDATOR_JOB ) verifies whether the partition structure is correct relative to the configuration in C_ODI_PARAM_VW . If the configuration and table structures do not match, it automatically drops and re-creates all partitions in the expected format. This will result in dropped data as well, so it is important to verify the configuration before loading any data.

Key Tables Affected

Input Table	Output Table
W_RTL_FLEXFACT1_FS	W_RTL_FLEXFACT1_F
W_RTL_FLEXFACT2_FS	W_RTL_FLEXFACT2_F
W_RTL_FLEXFACT3_FS	W_RTL_FLEXFACT3_F
W_RTL_FLEXFACT4_FS	W_RTL_FLEXFACT4_F

Gift Card Sales Load

Module Name HIST_GCN_TRX_LOAD_ADHOC Description Loads Gift Card Sales fact data for use in Retail Insights reporting. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Historical Data Load Activity

Design Overview

This process loads the Gift Card Sales fact file. This fact data is used for Retail Insights reporting only. All jobs in the process should be enabled before running it.

Key Tables Affected

Table	Usage
W_RTL_GCN_TRX_LC_DY_FS	Staging
W_RTL_GCN_TRX_LC_DY_F	Output

History Data Cleanup

Module Name HIST_DATA_CLEANUP_ADHOC Description Erase all data from Inventory and Price tables in RI, in order to restart your history load for those interfaces. Dependencies None Business Activity Historical Data Load

Design Overview

This process erases all data from select functional areas (currently Inventory Position and Pricing facts). The purpose of the process is to reset the environment if the data currently loaded is invalid or unwanted, and you’d like to start over with empty tables.

Note

It does not erase partition structures, so you need to load data for the same range of dates already available.

It also does not reset the C_HIST_LOAD_STATUS table, so you will need to update that before loading any new data.

History Data File Upload

Module Name HIST_ZIP_FILE_LOAD_ADHOC Description Looks for the RIHIST_RMS_DATA.zip file and unpacks it, moving any files to the incoming directory for batch processes. Dependencies None Business Activity Historical Data Load

Design Overview

This process moves and unloads a ZIP file (specifically RIHIST_RMS_DATA.zip ) so that the file contents may be used for one or more history and seeding load jobs. The ZIP file may contain one or multiple files. This process is a prerequisite to running any history or seeding load programs. The ZIP file must not contain any folders, as all ZIP file processing will be done only on files at the root level of a ZIP. If any folders are detected in the file, the process will fail.

The first job in this process waits a set period of time for the ZIP file to be uploaded, and it fails if it is not received in that time (4 hours by default). The second job moves the ZIP file to the internal server location and unzip it. It deletes any files previously in the destination folder, unzip the new file, and move the ZIP file to an archive when complete. It fails if the ZIP does not contain any data files, as there is nothing for it to move.

History Data Master Flow

Module Name RI_FLOW_ADHOC Description Ad hoc flow for loading all major foundation fact files in a single process that can be scheduled to run repeatedly in the same day for automated history loads. Dependencies None Business Activity Historical Data Load

Design Overview

The RI_FLOW_ADHOC process in the AIF DATA schedule is a replacement for the RI_INTRADAY_CYCLE found in earlier versions. It provides a single master process flow that can load the most common RAP foundation fact files and populate all data warehouse tables using one or multiple input zip files. For example, you may upload the files named RAP_DATA_HIST.zip.1 , RAP_DATA_HIST.zip.2 , and RAP_DATA_HIST.zip.3 at the same time to Object Storage for iterative processing starting with the first file in numerical order. When you provide multiple zip files, you will also schedule the RI_FLOW_ADHOC process to run multiple times and, each time, it will pick up the next ZIP file in the sequence. Make sure you only schedule a number of recurring flow executions equal to the number of ZIPs you will upload. If you run the flow without any ZIPs remaining to be processed, all jobs will still attempt to execute with 0 rows of data in the staging tables. This will cause the flow to fail as some loads are designed to stop the process if there is no data available. The ZIP files must not contain any folders, as all ZIP file processing will be done only on files at the root level of a ZIP. If any folders are detected in the files, the process will fail.

The process flow cannot be executed without first configuring which jobs to run. You may configure the flow by enabling or disabling sets of jobs by process name, and then, within the processes you are enabling, you may selectively disable jobs for tables you are not using. The processes included in the flow are listed below in order of execution (first to last). You should disable the processes you do not need before running or scheduling the flow.

Process Name	Usage
FLOW_LOAD_START_PROCESS	Required, non-functional jobs for logging/ dependency purposes.
CLEANUP_C_LOAD_DATES_FLOW_PROCESS	Required, clears prior run statuses from `C_LOAD_DATES`table.
ZIP_FILE_LOAD_FLOW_PROCESS	Required, selects a ZIP fle for the current run and unpacks it.
STG_SALES_LOAD_ADHOC_PROCESS	Stages the`W_RTL_SLS_TRX_IT_LC_DY_FS.dat` and`W_RTL_SLSPK_IT_LC_DY_FS.dat`fles; disable if not using this fle.
STG_CSV_SALES_LOAD_ADHOC_PROCESS	Stages the`SALES.csv`and`SALES_PACK.csv`fles; disable if not using these fles.
STG_RTV_LOAD_ADHOC_PROCESS	Stages the`W_RTL_INVRTV_IT_LC_DY_FS.dat`fle; disable if not using this fle.
STG_CSV_RTV_LOAD_ADHOC_PROCESS	Stages the`RTV.csv`fle; disable if not using this fle.

Process Name	Usage
STG_INVADJ_LOAD_ADHOC_PROCESS	Stages the`W_RTL_INVADJ_IT_LC_DY_FS.dat`fle; disable if not using this fle.
STG_CSV_INVADJ_LOAD_ADHOC_PROCESS	Stages the`ADJUSTMENT.csv`fle; disable if not using this fle.
STG_INVRC_LOAD_ADHOC_PROCESS	Stages the`W_RTL_INVRC_IT_LC_DY_FS.dat`fle; disable if not using this fle.
STG_CSV_INVRC_LOAD_ADHOC_PROCESS	Stages the`RECEIPT.csv`fle; disable if not using this fle.
STG_INVTSF_LOAD_ADHOC_PROCESS	Stages the`W_RTL_INVTSF_IT_LC_DY_FS.dat`fle; disable if not using this fle.
STG_CSV_INVTSF_LOAD_ADHOC_PROCESS	Stages the`TRANSFER.csv`fle; disable if not using this fle.
STG_MARKDOWN_LOAD_ADHOC_PROCESS	Stages the`W_RTL_MKDN_IT_LC_DY_FS.dat`fle; disable if not using this fle.
STG_CSV_MARKDOWN_LOAD_ADHOC_PROCE SS	Stages the`MARKDOWN.csv`fle; disable if not using this fle.
STG_DEAL_INCOME_ADHOC_PROCESS	Stages the`W_RTL_DEALINC_IT_LC_DY_FS.dat` fle; disable if not using this fle.
STG_CSV_DEAL_INCOME_ADHOC_PROCESS	Stages the`DEAL_INCOME.csv`fle; disable if not using this fle.
STG_INV_LOAD_ADHOC_PROCESS	Stages the`W_RTL_INV_IT_LC_DY_FS.dat`fle; disable if not using this fle.
STG_INVPK_LOAD_ADHOC_PROCESS	Stages the`W_RTL_INVPK_IT_LC_DY_FS.dat`fle; disable if not using this fle.
STG_INVOOS_LOAD_ADHOC_PROCESS	Stages the`INVENTORY_OOS.csv`fle; disable if not using this fle.
STG_CSV_INV_LOAD_ADHOC_PROCESS	Stages the`INVENTORY.csv`fle; disable if not using this fle.
STG_CSV_INVPK_LOAD_ADHOC_PROCESS	Stages the`INVENTORY_PACK.csv`fle; disable if not using this fle.
STG_PRICE_LOAD_ADHOC_PROCESS	Stages the`W_RTL_PRICE_IT_LC_DY_FS.dat`fle; disable if not using this fle.
STG_CSV_PRICE_LOAD_ADHOC_PROCESS	Stages the`PRICE.csv`fle; disable if not using this fle.
STG_ONORD_ADHOC_PROCESS	Stages the`W_RTL_PO_ONORD_IT_LC_DY_FS.dat` fle; disable if not using this fle.
STG_CSV_ONORD_ADHOC_PROCESS	Stages the`ORDER_DETAIL.csv`fle; disable if not using this fle.
STG_INVU_LOAD_ADHOC_PROCESS	Stages the`W_RTL_INVU_IT_LC_DY_FS.dat`fle; disable if not using this fle.
STG_SALES_WF_ADHOC_PROCESS	Stages the`W_RTL_SLSWF_IT_LC_DY_FS.dat`fle; disable if not using this fle.
STG_CSV_SALES_WF_ADHOC_PROCESS	Stages the`SALES_WF.csv`fle; disable if not using this fle.

Process Name	Usage
RESET_ETL_THREAD_VAL_ADHOC_PROCESS	Required; updates your staging data to remove any multi-threading parameter values that are no longer supported.
ADHOC_REFRESH_RADM_PROCESS	Required; collects statistics on your tables before starting the target table loads.
SALES_LOAD_ADHOC_PROCESS	Processes all the data from Sales and Sales Pack fles (CSV or DAT) into the target data warehouse tables and aggregates.
RTV_LOAD_ADHOC_PROCESS	Processes all the data from the RTV fle (CSV or DAT) into the target data warehouse tables and aggregates.
INVADJ_LOAD_ADHOC_PROCESS	Processes all the data from the Adjustment fle (CSV or DAT) into the target data warehouse tables and aggregates.
INVRC_LOAD_ADHOC_PROCESS	Processes all the data from the Receipt fle (CSV or DAT) into the target data warehouse tables and aggregates.
INVTSF_LOAD_ADHOC_PROCESS	Processes all the data from the Transfer fle (CSV or DAT) into the target data warehouse tables and aggregates.
MARKDOWN_LOAD_ADHOC_PROCESS	Processes all the data from the Markdown fle (CSV or DAT) into the target data warehouse tables and aggregates.
DEAL_INCOME_ADHOC_PROCESS	Processes all the data from the Deal Income fle (CSV or DAT) into the target data warehouse tables and aggregates.
INV_LOAD_ADHOC_PROCESS	Processes all the data from the Inventory fle (CSV or DAT) into the target data warehouse tables and aggregates.
INVPK_LOAD_ADHOC_PROCESS	Processes all the data from the Inventory Pack fle (CSV or DAT) into the target data warehouse tables and aggregates.
INVOOS_LOAD_ADHOC_PROCESS	Processes all the data from the Inventory OOS fle into the target data warehouse tables and aggregates.
PRICE_LOAD_ADHOC_PROCESS	Processes all the data from the Price fle (CSV or DAT) into the target data warehouse tables and aggregates.
ONORD_LOAD_ADHOC_PROCESS	Processes all the data from the Purchase Order fle (CSV or DAT) into the target data warehouse tables and aggregates.
INVU_LOAD_ADHOC_PROCESS	Processes all the data from the Unavailable Inventory fle into the target data warehouse tables and aggregates.
SALES_WF_ADHOC_PROCESS	Processes all the data from the Sales Wholesale/ Franchise fle (CSV or DAT) into the target data warehouse tables and aggregates.
FACT_FLOW_END_PROCESS	Required; non-functional jobs for logging/ dependency purposes.
FLOW_LOAD_END_PROCESS	Required; non-functional jobs for logging/ dependency purposes.

Once you’ve disabled all the unused jobs at the process level, you may want to review the remaining jobs and disable table loads that are not needed for your implementation. Unless you are implementing Retail Insights, you do not need any aggregate table above the item/ location/week level intersections. For example, you will need all sales tables containing IT_LC_DY or IT_LC_WK for item/loc/day and week intersections. You may not need the sales aggregates such as W_RTL_SLS_SC_DY_A which is a BI aggregate for reporting, unless you are implementing Retail Insights.

If you are calling the process from Postman or Curl, then you may use a payload like the following to trigger the process flow:

{
  "cycleName" : "Adhoc",
  "flowName" : "RI_FLOW_ADHOC",
  "requestType" : "POM Scheduler"
}

Initial Base Cost Seeding

Module Name SEED_CSV_W_RTL_BCOST_IT_LC_DY_F_PROCESS_ADHOC Description Loads a full snapshot of base cost data from COST.csv to initialize the positional data before a nightly batch can be enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Nightly Batch Preparation

Design Overview

The seeding load process for Base Cost data accepts an input file at the item-location-datesupplier level using the file specification for COST.csv . It assumes the file has already been moved into place by the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a preprocessing table in the database, transforms it to RI’s internal staging tables, then loads it into the base fact (item/location/day). This process is only for the base cost, a separate process loads the net cost, if required.

Note

Seeding processes require a full snapshot of data for a single date, which covers all item/location combinations that should have a starting position for this fact. The seeding process must load data for the day before the nightly batch is going to run. Alternatively, you can include the full snapshots of data in your very first nightly batch and skip the seeding steps. This causes the nightly batch to take a significantly longer time to execute but avoids the manual load processes for all the positional facts.

Key Tables Affected

Table Usage W_COST_FTS File Input W_RTL_BCOST_IT_LC_DY_FS Staging

Table Usage W_RTL_BCOST_IT_LC_G Output W_RTL_BCOST_IT_LC_DY_F Output

Initial Base Cost Seeding (Legacy)

Module Name SEED_W_RTL_BCOST_IT_LC_DY_F_PROCESS_ADHOC Description Loads a full snapshot of base cost data from W_RTL_BCOST_IT_LC_DY_FS.dat to initialize the positional data before a nightly batch can be enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Nightly Batch Preparation

Design Overview

The seeding load process for Base Cost data accepts an input file at the item-location-datesupplier level using the file specification for W_RTL_BCOST_IT_LC_DY_FS.dat . It assumes the file has already been moved into place using the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a preprocessing table in the database, transforms it to RI’s internal staging tables, then loads it into the base fact (item/location/day).

Note

Seeding processes require a full snapshot of data for a single date, which covers all item/location combinations that should have a starting position for this fact. The seeding process must load data for the day before the nightly batch runs. Alternatively, you can include the full snapshots of data in your very first nightly batch and skip the seeding steps. This causes the nightly batch to take a significantly longer time to execute but avoids the manual load processes for all the positional facts.

Key Tables Affected

Table Usage W_RTL_BCOST_IT_LC_DY_FS Staging W_RTL_BCOST_IT_LC_G Output W_RTL_BCOST_IT_LC_DY_F Output

Initial Calendar Load

Module Name CALENDAR_LOAD_ADHOC Description Runs all calendar creation and load processes to set up or update the system and fiscal calendars in the data warehouse. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Initial System Setup

Design Overview

The calendar load ad hoc process performs all the necessary stage, transform, and load jobs to set up the data warehouse calendars. It takes as input:

1. One or more data files from the following list, uploaded and unpacked using the HIST_ZIP_FILE_LOAD_ADHOC process:

a. CALENDAR.csv (required)
b. CALENDAR_ALT.csv (optional)
c. W_RTL_MCAL_DAY_SHIFT_DS.dat (optional)
d. W_RTL_MCAL_DAY_UNSHIFT_DS.dat (optional)
e. W_RTL_MCAL_DAY_GUNSHIFT_DS.dat (optional)
f. W_RTL_MCAL_WEEK_SHIFT_DS.dat (optional)
g. W_RTL_MCAL_WEEK_UNSHIFT_DS.dat (optional)

2. System calendar start and end dates in C_ODI_PARAM

The CALENDAR.csv data must be in the form of a fiscal calendar (for example, a 4-5-4 or 13period calendar). It must be at the period level of detail (not the day level) and should include start and end date ranges for the period, quarter, and year levels on each record. The RAP data warehouse currently supports a single, hard-coded calendar ID (Retail Calendar~41) that should be used in the file’s first column ( MCAL_CAL_ID ). Optional mapping files for this-year-tolast-year mappings may be provided if the business uses a custom definition of LY in reporting and analytics. These mappings control which range of dates are returned when pulling LY metrics in RI, such as when a fiscal week in the current year should be mapped to a different week in LY. Default mappings are created by the process if no data is provided. The CALENDAR_ALT.csv file must contain day-level records with any additional attributes you may require in Planning applications such as Merchandise Financial Planning. The range of dates used in this file must fall within the range of dates in the main CALENDAR.csv file.

This process populates the Gregorian system calendar at the same time the fiscal calendar is loaded. The Gregorian calendar requires additional start and end date parameters from C_ODI_PARAM to define the time range to generate. It must be greater than the range of time in the fiscal calendar. The calendar generation process does not support a 53-week year as the starting year, so it’s recommended to make the start date of the Gregorian calendar at least 1 year earlier than the start of the fiscal calendar, which avoids improperly formed data in the fiscal calendar if the 53-week year is the first year.

Output tables that start with W_MCAL_ are mainly used for fiscal calendar generation, while the other tables such as W_DAY_D are used for the Gregorian calendar. Output tables starting with W_RTL_MCAL_ contain the supplemental calendar data like the LY mappings and flex attributes. All output tables must be successfully populated with calendar data in order to use the platform. Validate the data closely after running this process to ensure nothing is missing or incorrect in the generated calendar data.

Key Tables Affected

Table	Usage
W_MCAL_PERIOD_DTS	Input
W_RTL_MCAL_DAY_SHIFT_DS	Input

Table	Usage
W_RTL_MCAL_DAY_UNSHIFT_DS	Input
W_RTL_MCAL_DAY_GUNSHIFT_DS	Input
W_RTL_MCAL_WEEK_SHIFT_DS	Input
W_RTL_MCAL_WEEK_UNSHIFT_DS	Input
W_RTL_MCAL_DAY_FLEX_DS	Input
W_MCAL_PERIOD_DS	Staging
W_TIME_OF_DAY_D	Output
W_DAY_D	Output
W_YEAR_D	Output
W_QTR_D	Output
W_MONTH_D	Output
W_WEEK_D	Output
W_MINUTE_OF_DAY_D	Output
W_MCAL_CONFIG_G	Output
W_MCAL_CAL_D	Output
W_MCAL_PERIOD_D	Output
W_MCAL_DAY_D	Output
W_MCAL_WEEK_D	Output
W_MCAL_YEAR_D	Output
W_MCAL_QTR_D	Output
W_RTL_MCAL_DAY_SHIFT_D	Output
W_RTL_MCAL_DAY_UNSHIFT_D	Output
W_RTL_MCAL_DAY_GUNSHIFT_D	Output
W_RTL_MCAL_DAY_CUSTOM_D	Output
W_RTL_MCAL_WEEK_SHIFT_D	Output
W_RTL_MCAL_WEEK_UNSHIFT_D	Output
W_RTL_MCAL_PERIOD_SHIFT_D	Output
W_RTL_MCAL_PERIOD_UNSHIFT_D	Output
W_RTL_MCAL_DAY_FLEX_D	Output

Initial Calendar Staging (Legacy)

Module Name CALENDAR_STG_LOAD_ADHOC Description Stages the W_MCAL_PERIOD_DS.dat file for the ad hoc calendar load programs. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Initial System Setup

Design Overview

This process looks for the W_MCAL_PERIOD_DS.dat file placed on the server by a history zip file upload and imports it to a staging table for use in the CALENDAR_LOAD_ADHOC process.

Key Tables Affected

Table	Usage
W_MCAL_PERIODS_DS	File Input

Initial Competitor Price Seeding

Module Name SEED_W_RTL_COMP_PRICE_IT_LC_DY_F_PROCESS_ADHOC Description Loads a full snapshot of competitor price data from W_RTL_COMP_PRICE_IT_LC_DY_FS.dat to initialize the positional data before a nightly batch can be enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Nightly Batch Preparation

Design Overview

The seeding load process for Competitor Price fact data accepts an input file at the itemlocation-competitor store-date level using the file specification for

W_RTL_COMP_PRICE_IT_LC_DY_FS.dat . It assumes the file has already been moved into place by the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a staging table, then loads it into the base fact (item/location/comp store/day).

Note

Seeding processes require a full snapshot of data for a single date, which covers all item/location combinations that should have a starting position for this fact. The seeding process must load data for the day before the nightly batch runs. Alternatively, you can include the full snapshots of data in your very first nightly batch and skip the seeding steps. This causes the nightly batch to take a significantly longer time to execute but avoids the manual load processes for all the positional facts.

Key Tables Affected

Table		Usage
W_RTL_	COMP_PRICE_IT_LC_DY_FS	File Input
W_RTL_	COMP_PRICE_IT_LC_DY_F	Output
W_RTL_	COMP_STORE_DS	File Input
W_RTL_	COMP_STORE_D	Output

Initial Dimension Load

Module Name RI_DIM_INITIAL_ADHOC Description Runs all core dimension load programs in AIF DATA schedule in POM to stage, transform, and load dimension data to the foundation data warehouse tables. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Historical Data Load

Design Overview

This process runs the dimension load programs needed to initialize the data model with the core dataset needed for history and seed loads. Not all dimensions supported by RI or AIF are part of the initial load process, only those that are used in some way for history or downstream application processing. The process will stage and load all the files in a single flow; no other processes are needed to load the dimensions. The jobs used by the process are the same as the ones in the nightly batch so this also validates the file quality and correctness.

The process has three distinct types of jobs:

File import jobs that take a CSV input and load it to the database pre-staging tables (usually tables ending in DTS or FTS )
Staging jobs that transform the raw inputs to the required formats and perform any defaulting of values on data columns
Load jobs that move the staging data to internal target tables

The tables below are broken out by each type, so you can review the inputs and outputs for each block of jobs.

Files to Pre-Staging Tables

Input File	Output Table
PRODUCT.csv	W_PRODUCT_DTS
PRODUCT_ALT.csv	W_PRODUCT_ALT_DTS
ORGANIZATION.csv	W_INT_ORG_DTS
ORGANIZATION_ALT.csv	W_ORGANIZATION_ALT_DTS
EXCH_RATE.csv	W_EXCH_RATE_DTS
CALENDAR.csv	W_MCAL_PERIODS_DTS
SUPPLIER.csv	W_SUPPLIER_DTS
EMPLOYEE.csv	W_EMPLOYEE_DTS
PROD_LOC_ATTR.csv	W_PROD_LOC_ATTR_DTS
PROD_LOC_REPL.csv	W_INVENTORY_PRODUCT_ATTR_DTS
ATTR.csv	W_ATTR_DTS
PROD_ATTR.csv	W_PRODUCT_ATTR_DTS
SEASON.csv	W_RTL_SEASON_PHASE_DTS
PROD_SEASON.csv	W_RTL_SEASON_PHASE_IT_DTS

Input File	Output Table
STORE_COMP.csv	W_RTL_LOC_COMP_MTX_DTS
CODES.csv	W_RTL_CODE_DTS
PROD_PACK.csv	W_RTL_ITEM_GRP2_DTS
DIFF_GROUP.csv	W_DIFF_GROUP_DTS
ADJUSTMENT.csv	W_ADJUSTMENT_FTS
PROMOTION.csv	W_RTL_PROMO_EXT_DTS
PROMO_DETAIL.csv	W_RTL_PROMO_IT_LC_DTS
ORDER_HEAD.csv	W_ORDER_HEAD_FTS
REPL_DISTRO.csv	W_RTL_REPL_DISTRO_IT_LC_DS
REPL_REV_INT.csv	W_RTL_REPL_REV_INT_IT_LC_DS
REPL_LT_INT.csv	W_RTL_REPL_LT_INT_IT_LC_DS

Pre-Staging to Staging Tables

These processes apply all of the transformation scripts needed to take simplified interface (SI) data for dimensions and map it to the internal data model staging tables. The simplified interfaces are a one-to-many mapping to the internal data warehouse structures for dimensions, so this intermediate step is required to transform the incoming data and make it usable downstream.

Input Table	Output Table
W_PRODUCT_DTS	W_PROD_CAT_DHS
W_PRODUCT_DTS	W_PRODUCT_ATTR_DS
W_PRODUCT_DTS	W_PRODUCT_DS
W_PRODUCT_DTS	W_PRODUCT_DS_TL
W_PRODUCT_DTS	W_RTL_PRODUCT_BRAND_DS
W_PRODUCT_DTS	W_RTL_PRODUCT_BRAND_DS_TL
W_PRODUCT_DTS	W_RTL_IT_SUPPLIER_DS
W_PRODUCT_DTS	W_DOMAIN_MEMBER_DS_TL
W_PRODUCT_ALT_DTS	W_PRODUCT_FLEX_DS
W_INT_ORG_DTS	W_INT_ORG_DS
W_INT_ORG_DTS	W_INT_ORG_DS_TL
W_INT_ORG_DTS	W_INT_ORG_DHS
W_INT_ORG_DTS	W_DOMAIN_MEMBER_DS_TL
W_INT_ORG_DTS	W_RTL_CHANNEL_DS
W_INT_ORG_DTS	W_INT_ORG_ATTR_DS
W_INT_ORG_DTS	W_RTL_CHANNEL_CNTRY_DS
W_ORGANIZATION_ALT_DTS	W_ORGANIZATION_FLEX_DS
W_EXCH_RATE_DTS	W_EXCH_RATE_GS
W_MCAL_PERIODS_DTS	W_MCAL_PERIOD_DS
W_SUPPLIER_DTS	W_PARTY_ATTR_DS
W_SUPPLIER_DTS	W_PARTY_ORG_DS

Input Table	Output Table
W_EMPLOYEE_DTS	W_EMPLOYEE_DS
W_PROD_LOC_ATTR_DTS	W_RTL_IT_LC_DS
W_INVENTORY_PRODUCT_ATTR_DTS	W_INVENTORY_PRODUCT_ATTR_DS
W_ATTR_DTS	W_RTL_PRODUCT_ATTR_DS
W_ATTR_DTS	W_RTL_PRODUCT_ATTR_DS_TL
W_ATTR_DTS	W_DOMAIN_MEMBER_DS_TL
W_ATTR_DTS	W_RTL_PRODUCT_COLOR_DS
W_PRODUCT_ATTR_DTS	W_RTL_ITEM_GRP1_DS
W_RTL_SEASON_PHASE_DTS	W_RTL_SEASON_DS
W_RTL_SEASON_PHASE_DTS	W_RTL_PHASE_DS
W_RTL_SEASON_PHASE_DTS	W_DOMAIN_MEMBER_DS_TL
W_RTL_SEASON_PHASE_IT_DTS	W_RTL_SEASON_PHASE_IT_DS
W_RTL_LOC_COMP_MTX_DTS	W_RTL_LOC_COMP_MTX_DS
W_RTL_CODE_DTS	W_RTL_CODE_DS
W_RTL_ITEM_GRP2_DTS	W_RTL_ITEM_GRP2_DS
W_DIFF_GROUP_DTS	W_RTL_DIFF_GRP_DS
W_DIFF_GROUP_DTS	W_RTL_DIFF_GRP_DS_TL
W_ADJUSTMENT_FTS	W_REASON_DS
W_ADJUSTMENT_FTS	W_DOMAIN_MEMBER_DS_TL
W_RTL_PROMO_EXT_DTS	W_RTL_PROMO_EXT_DS
W_RTL_PROMO_IT_LC_DTS	W_RTL_PROMO_IT_LC_DS
W_ORDER_HEAD_FTS	W_RTL_PO_DETAILS_DS

Staging to Target Tables

Input Table	Output Table
W_DOMAIN_MEMBER_DS_TL	W_DOMAIN_MEMBER_LKP_TL
W_EMPLOYEE_DS	W_EMPLOYEE_D
W_EXCH_RATE_GS	W_EXCH_RATE_G
W_INT_ORG_DS	W_INT_ORG_D
W_INT_ORG_DHS	W_INT_ORG_DH
W_ORGANIZATION_FLEX_DS	W_ORGANIZATION_FLEX_D
W_PARTY_ATTR_DS	W_PARTY_ATTR_D
W_PARTY_ORG_DS	W_PARTY_ORG_D
W_PARTY_PER_DS	W_PARTY_PER_D
W_PROD_CAT_DHS	W_PROD_CAT_DH
W_PRODUCT_ATTR_DS	W_PRODUCT_ATTR_D
W_PRODUCT_DS	W_PRODUCT_D
W_PRODUCT_FLEX_DS	W_PRODUCT_FLEX_D
W_REASON_DS	W_REASON_D

Input Table	Output Table
W_RTL_ALC_DETAILS_DS	W_RTL_ALC_DETAILS_D
W_RTL_BUYER_DS	W_RTL_BUYER_D
W_RTL_CHANNEL_DS	W_RTL_CHANNEL_D
W_RTL_CHANNEL_CNTRY_DS	W_RTL_CHANNEL_CNTRY_D
W_RTL_CO_HEAD_DS	W_RTL_CO_HEAD_D
W_RTL_CO_LINE_DS	W_RTL_CO_LINE_D
W_RTL_CO_SHIP_METHOD_DS	W_RTL_CO_SHIP_METHOD_D
W_RTL_CO_SHIP_TYPE_DS	W_RTL_CO_SHIP_TYPE_D
W_RTL_COMP_STORE_DS	W_RTL_COMP_STORE_D
W_RTL_CONS_METADATA_GS	W_RTL_CONS_METADATA_G
W_RTL_COUPON_DS	W_RTL_COUPON_D
W_RTL_DIFF_GRP_DS	W_RTL_DIFF_GRP_D
W_RTL_DIFF_RNG_DS	W_RTL_DIFF_RNG_D
W_RTL_DISCOUNT_TYPE_DS	W_RTL_DISCOUNT_TYPE_D
W_RTL_IT_SUPPLIER_DS	W_RTL_IT_SUPPLIER_D
W_RTL_ITEM_GRP1_DS	W_RTL_ITEM_GRP1_D
W_RTL_LOC_STOCK_CNT_DS	W_RTL_LOC_STOCK_CNT_D
W_RTL_NON_MERCH_CODE_DS	W_RTL_NON_MERCH_CODE_D
W_RTL_ORG_FIN_DS	W_RTL_ORG_FIN_D
W_RTL_PHASE_DS	W_RTL_PHASE_D
W_RTL_PO_DETAILS_DS	W_RTL_PO_DETAILS_D
W_RTL_PO_DETAILS_DS_CFA	W_RTL_PO_DETAILS_D_CFA
W_RTL_PO_DETAILS_IT_LC_DS_CFA	W_RTL_PO_DETAILS_IT_LC_D_CFA
W_RTL_PRICE_CLR_IT_LC_DS	W_RTL_PRICE_CLR_IT_LC_D
W_RTL_PRODUCT_ATTR_DS	W_RTL_PRODUCT_ATTR_D
W_RTL_PRODUCT_BRAND_DS	W_RTL_PRODUCT_BRAND_D
W_RTL_PROMO_DS_TL	W_RTL_PROMO_D_TL
W_RTL_PROMO_IT_LC_DS	W_RTL_PROMO_IT_LC_D
W_RTL_PROMO_CE_IT_LC_DS	W_RTL_PROMO_IT_LC_D
W_RTL_REPL_DISTRO_IT_LC_DS	W_RTL_REPL_DISTRO_IT_LC_D
W_RTL_REPL_REV_INT_IT_LC_DS	W_RTL_REPL_REV_INT_IT_LC_D
W_RTL_REPL_LT_INT_IT_LC_DS	W_RTL_REPL_LT_INT_IT_LC_D
W_RTL_SEASON_DS	W_RTL_SEASON_D
W_RTL_SEASON_PHASE_IT_DS	W_RTL_SEASON_PHASE_IT_D
W_RTL_TNDR_TYPE_DS	W_RTL_TNDR_TYPE_D
W_RTL_TSF_DETAILS_DS_CFA	W_RTL_TSF_DETAILS_D_CFA
W_STATUS_DS	W_STATUS_D

Initial Inventory Pack Seeding

Module Name SEED_CSV_W_RTL_INVPK_IT_LC_DY_F_PROCESS_ADHOC Description Loads a full snapshot of inventory pack data from INVENTORY_PACK.csv to initialize the positional data before a nightly batch can be enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Nightly Batch Preparation

Design Overview

The seeding load process for Inventory Pack data accepts an input file at the item-locationdate level using the file specification for INVENTORY_PACK.csv . It assumes the file has already been moved into place by the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a preprocessing table in the database, transforms it to internal staging tables, then loads it into the base fact (item/location/day).

Note

Seeding processes require a full snapshot of data for a single date. This snapshot needs to cover all item/location combinations that should have a starting position for this fact. The seeding process must load data for the day before the nightly batch runs. Alternatively, you can include the full snapshots of data in your first nightly batch and skip the seeding steps. This causes the nightly batch to take a significantly longer time to execute, but avoids the manual load processes for all the positional facts.

Key Tables Affected

Table Usage W_RTL_INVPK_IT_LC_DY_FTS File Input W_RTL_INVPK_IT_LC_DY_FS Staging W_RTL_INVPK_IT_LC_G Output W_RTL_INVPK_IT_LC_DY_F Output W_RTL_INVPK_IT_LC_WK_A Output

Initial Inventory Seeding

Module Name SEED_CSV_W_RTL_INV_IT_LC_DY_F_PROCESS_ADHOC Description Loads a full snapshot of inventory data from INVENTORY.csv to initialize the positional data before a nightly batch can be enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Nightly Batch Preparation

Design Overview

The seeding load process for Inventory data accepts an input file at the item-location-date level using the file specification for INVENTORY.csv . It assumes the file has already been moved into place by the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a preprocessing table in the database, transforms it to internal staging tables, then loads it into the base fact (item/location/day).

Note

Seeding processes require a full snapshot of data for a single date, which covers all item/location combinations that should have a starting position for this fact. The seeding process must load data for the day before the nightly batch runs. Alternatively, you can include the full snapshots of data in your first nightly batch and skip the seeding steps. This causes the nightly batch to take a significantly longer time to execute, but avoids the manual load processes for all the positional facts.

Key Tables Affected

Table Usage W_RTL_INV_IT_LC_DY_FTS File Input W_RTL_INV_IT_LC_DY_FS Staging W_RTL_INV_IT_LC_G Output W_RTL_INV_IT_LC_DY_F Output

Initial Inventory Seeding (Legacy)

Module Name SEED_W_RTL_INV_IT_LC_DY_F_PROCESS_ADHOC Description Loads a full snapshot of inventory data from W_RTL_INV_IT_LC_DY_FS.dat to initialize the positional data before a nightly batch can be enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Nightly Batch Preparation

Design Overview

The seeding load process for Inventory data accepts an input file at the item-location-date level using the file specification for W_RTL_INV_IT_LC_DY_FS.dat . It assumes the file has already been moved into place by the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file to internal staging tables, then load it into the base fact (item/location/day).

Note

Seeding processes require a full snapshot of data for a single date, which covers all item/location combinations that should have a starting position for this fact. The seeding process must load data for the day before the nightly batch runs. Alternatively, you can include the full snapshots of data in your very first nightly batch and skip the seeding steps. This causes the nightly batch to take a significantly longer time to execute but avoids the manual load processes for all the positional facts.

Key Tables Affected

Table Usage W_RTL_INV_IT_LC_DY_FS File Input W_RTL_INV_IT_LC_G Output W_RTL_INV_IT_LC_DY_F Output

Initial Net Cost Seeding

Module Name SEED_CSV_W_RTL_NCOST_IT_LC_DY_F_PROCESS_ADHOC Description Loads a full snapshot of net cost data from COST.csv to initialize the positional data before a nightly batch can be enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Nightly Batch Preparation

Design Overview

The seeding load process for Net Cost data accepts an input file at the item-location-datesupplier level using the file specification for COST.csv . It assumes the file has already been moved into place by the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a preprocessing table in the database, transforms it to internal staging tables, then loads it into the base fact (item/location/day). This process is only for the net cost; a separate process loads the base cost, if required.

Note

Seeding processes require a full snapshot of data for a single date, which covers all item/location combinations that should have a starting position for this fact. The seeding process must load data for the day before the nightly batch runs. Alternatively, you can include the full snapshots of data in your very first nightly batch and skip the seeding steps. This causes the nightly batch to take a significantly longer time to execute, but avoids the manual load processes for all the positional facts.

Key Tables Affected

Table Usage W_COST_FTS File Input W_RTL_NCOST_IT_LC_DY_FS Staging W_RTL_NCOST_IT_LC_G Output W_RTL_NCOST_IT_LC_DY_F Output

Initial Net Cost Seeding (Legacy)

Module Name SEED_W_RTL_NCOST_IT_LC_DY_F_PROCESS_ADHOC Description Loads a full snapshot of net cost data from W_RTL_NCOST_IT_LC_DY_FS.dat to initialize the positional data before a nightly batch can be enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Nightly Batch Preparation

Design Overview

The seeding load process for Net Cost data accepts an input file at the item-location-datesupplier level using the file specification for W_RTL_NCOST_IT_LC_DY_FS.dat . It assumes the file has already been moved into place using the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a preprocessing table in the database, transforms it to internal staging tables, then loads it into the base fact (item/location/day).

Note

Seeding processes require a full snapshot of data for a single date, which covers all item/location combinations that should have a starting position for this fact. The seeding process must load data for the day before the nightly batch runs. Alternatively, you can include the full snapshots of data in your very first nightly batch and skip the seeding steps. This causes the nightly batch to take a significantly longer time to execute, but avoids the manual load processes for all the positional facts.

Key Tables Affected

Table	Usage
W_RTL_NCOST_IT_LC_DY_FS	File Input
W_RTL_NCOST_IT_LC_G	Output
W_RTL_NCOST_IT_LC_DY_F	Output

Initial Price Seeding

Module Name SEED_CSV_W_RTL_PRICE_IT_LC_DY_F_PROCESS_ADHOC Description Loads a full snapshot of price data from PRICE.csv to initialize the positional data before a nightly batch can be enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Nightly Batch Preparation

Design Overview

The seeding load process for Price data accepts an input file at the item-location-date level using the file specification for PRICE.csv . It assumes the file has already been moved into place by the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a preprocessing table in the database, transforms it to internal staging tables, then loads it into the base fact (item/location/day).

Note

Seeding processes require a full snapshot of data for a single date, which covers all item/location combinations that should have a starting position for this fact. The seeding process must load data for the day before the nightly batch runs. Alternatively, you can include the full snapshots of data in your very first nightly batch and skip the seeding steps. This causes the nightly batch to take a significantly longer time to execute, but avoids the manual load processes for all the positional facts.

Key Tables Affected

Table Usage W_RTL_PRICE_IT_LC_DY_FTS File Input W_RTL_PRICE_IT_LC_DY_FS Staging W_RTL_PRICE_IT_LC_G Output W_RTL_PRICE_IT_LC_DY_F Output

Initial Price Seeding (Legacy)

Module Name SEED_W_RTL_PRICE_IT_LC_DY_F_PROCESS_ADHOC Description Loads a full snapshot of price data from W_RTL_PRICE_IT_LC_DY_FS.dat to initialize the positional data before a nightly batch can be enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Nightly Batch Preparation

Design Overview

The seeding load process for Price data accepts an input file at the item-location-date level using the file specification for W_RTL_PRICE_IT_LC_DY_FS.dat . It assumes the file has already been moved into place by the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a staging table, then loads it into the base fact (item/location/day).

Note

Seeding processes require a full snapshot of data for a single date, which covers all item/location combinations that should have a starting position for this fact. The seeding process must load data for the day before the nightly batch runs. Alternatively, you can include the full snapshots of data in your very first nightly batch and skip the seeding steps. This causes the nightly batch to take a significantly longer time to execute but avoids the manual load processes for all the positional facts.

Key Tables Affected

Table Usage W_RTL_PRICE_IT_LC_DY_FS File Input W_RTL_PRICE_IT_LC_G Output W_RTL_PRICE_IT_LC_DY_F Output Module Name SEED_CSV_W_RTL_PO_ONORD_IT_LC_DY_F_PROCESS_ADHOC Description Loads a full snapshot of purchase order data from ORDER_HEAD.csv and ORDER_DETAIL.csv to initialize the positional data before a nightly batch can be enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Nightly Batch Preparation Activity

Initial Purchase Order Seeding

Design Overview

The seeding load process for Purchase Order data accepts two input files at the order header and order detail levels using the file specifications for ORDER_HEAD.csv and ORDER_DETAIL.csv . It assumes the files have already been moved into place by the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the files into preprocessing tables in the database, transforms them to internal staging tables, then loads them into the base dimension and facts. The dimension is loaded first to support loading the fact table against those foreign keys.

Note

Seeding processes require a full snapshot of data for a single date, which covers all purchase orders and item/location combinations that should have a starting position for this fact. The seeding process must load data for the day before the nightly batch runs. Alternatively, you can include the full snapshots of data in your very first nightly batch and skip the seeding steps. This causes the nightly batch to take a significantly longer time to execute but avoids the manual load processes for all the positional facts.

Key Tables Affected

Table Usage W_ORDER_HEAD_FTS File Input W_RTL_PO_DETAILS_DS Staging W_RTL_PO_DETAILS_D Output W_ORDER_DETAIL_FTS File Input W_RTL_PO_ONORD_IT_LC_DY_FS Staging W_RTL_PO_ONORD_IT_LC_DY_F Output

Initial Purchase Order Seeding (Legacy)

Module Name SEED_W_RTL_PO_ONORD_IT_LC_DY_F_PROCESS_ADHOC Description Loads a full snapshot of purchase order data from W_RTL_PO_ONORD_IT_LC_DY_FS.dat to initialize the positional data before a nightly batch can be enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Nightly Batch Preparation

Design Overview

The seeding load process for Purchase Order fact data accepts an input file at the itemlocation-date level using the file specification for W_RTL_PO_ONORD_IT_LC_DY_FS.dat . It assumes the file has already been moved into place by the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a staging table, then loads it into the base fact (item/ location/day). It assumes the dimension has already been loaded separately using the initial dimension loads.

Note

Seeding processes require a full snapshot of data for a single date, which covers all item/location combinations that should have a starting position for this fact. The seeding process must load data for the day before the nightly batch runs. Alternatively, you can include the full snapshots of data in your very first nightly batch and skip the seeding steps. This causes the nightly batch to take a significantly longer time to execute but avoids the manual load processes for all the positional facts

Key Tables Affected

Table Usage W_RTL_PO_ONORD_IT_LC_DY_FS File Input W_RTL_PO_ONORD_IT_LC_DY_F Output

Initial Purchase Order Allocation Seeding

Module Name SEED_W_RTL_PO_ONALC_IT_LC_DY_F_PROCESS_ADHOC Description Loads a full snapshot of purchase order allocation data from W_RTL_PO_ONALC_IT_LC_DY_FS.dat to initialize the positional data before a nightly batch can be enabled. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Nightly Batch Preparation

Design Overview

The seeding load process for Purchase Order Allocation fact data accepts an input file at the item-location-date level using the file specification for W_RTL_PO_ONALC_IT_LC_DY_FS.dat . It assumes the file has already been moved into place by the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a staging table, then loads it into the base fact (item/ location/day). It assumes the dimension has already been loaded separately using the initial dimension loads.

Note

Seeding processes require a full snapshot of data for a single date, which covers all item/location combinations that should have a starting position for this fact. The seeding process must load data for the day before the nightly batch runs. Alternatively, you can include the full snapshots of data in your very first nightly batch and skip the seeding steps. This causes the nightly batch to take a significantly longer time to execute but avoids the manual load processes for all the positional facts.

Key Tables Affected

Table Usage W_RTL_PO_ONALC_IT_LC_DY_FS File Input W_RTL_PO_ONALC_IT_LC_DY_F Output

Intercompany Margin History Load

Module Name HIST_CSV_ICMARGIN_LOAD_ADHOC Description Loads the IC_MARGIN.csv file into the data warehouse and populates key data tables used to integrate with other systems for history data.

Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Nightly Batch Preparation

Design Overview

The history load process for Intercompany Margin transactions accepts an input file at the item/ location/day level using the file specification for IC_MARGIN.csv . It assumes the file has already been moved into place by the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a preprocessing table in the database, transforms it to RI’s internal staging tables, then loads it into the base fact (item/location/day) as well as the week aggregate used for integrations (item/location/week).

Key Tables Affected

Table Usage W_RTL_ICM_IT_LC_DY_FTS File Input W_RTL_ICM_IT_LC_DY_FS Staging W_RTL_ICM_IT_LC_DY_F Output (Base Fact) W_RTL_ICM_IT_LC_WK_A Output (Aggregate)

Intraday Fact Load

Module Name RI_INTRADAY_FLOW_ADHOC Description Loads certain fact areas incrementally into the data warehouse outside of the nightly batch cycle, with the option to schedule the flow to run periodically throughout the day. Dependencies None Business Activity Intraday Batch Execution

Design Overview

This process flow enables the intraday loading of a subset of data warehouse subject areas in addition to nightly batch cycles. The intraday flow performs the following high-level steps:

1. Process a RAP_INTRADAY.zip file containing only the data to be updated intraday

2. Stage and load the supported intraday dimension and base fact tables (but not aggregates)

3. Capture the intraday records in temporary tables to be used again during nightly batch processing

4. Capture rejected records from intraday processing for review and correction before the next nightly cycle

The ZIP file used by the intraday flow must start with the name RAP_INTRADAY.zip and should be followed by a numerical sequence based on which iteration of the flow it will be used for. For example, if the intraday cycle will run 5 times per day, then the ZIP files provided can be in the format RAP_INTRADAY.zip.1 , RAP_INTRADAY.zip.2 , up to RAP_INTRADAY.zip.5 . This ensures that each execution of the intraday cycle processes the next ZIP file in sequence,

even if multiple ZIPs were uploaded in between two cycles. Other numerical sequences can be used, such as a timestamp, as long as they can be processed in ascending numerical order. ZIP files uploaded to object storage ( ris/incoming prefix) will be detected by the INTRADAY_ZIP_FILE_WAIT_JOB and registered in the table C_INTRADAY_FILES_LOAD_STATUS . The next ZIP file in sequence is then unpacked using INTRADAY_ZIP_FILE_UNLOAD_JOB . If multiple ZIPs are found then it will record all of them but only process one ZIP file per cycle. Ensure that you run the same number of cycles per day as the number of uploaded ZIP files so that all data is processed for the correct business date. If no ZIP files are found and none are remaining in the queue to be processed, then the WAIT job will run up to 4 hours before failing the process. Failed intraday processing will prevent the POM schedule from closing for the day, so it must be addressed in a timely manner by either providing a new ZIP file or skipping the process for the day. The ZIP file must not contain any folders, as all ZIP file processing will be done only on files at the root level of a ZIP. If any folders are detected in the file, the process will fail.

The data warehouse batch framework has features specific to intraday processing. The C_BATCH_RUN table maintains unique batch identifiers and attributes covering both intraday and nightly cycles for tracking and auditing purposes. At the start of a batch, the C_LOAD_DATES table (which tracks the status of batch jobs in the current run) will be archived into the C_LOAD_DATES_HIST table along with the BATCH_RUN_ID for either the nightly or intraday cycle just completed. In the nightly batch cycle, BATCH_POM_DATE_JOB captures the current POM execution date for maintaining C_BATCH_RUN . BATCH_RUN_NIGHTLY_JOB is responsible for initializing the C_BATCH_RUN table. BATCH_RUN_NIGHTLY_UPD_JOB updates the run parameters at the end of the nightly batch. BATCH_RUN_INTRADAY_JOB and BATCH_RUN_INTRADAY_UPD_JOB perform the same functions for the intraday cycles.

After unpacking the ZIP file, the dimension and fact loads that support intraday processing will be run. The following subject areas support intraday processing:

Inventory Position (current positions only)
Pricing (current positions only)
Purchase Orders (dimension and current positions only)
Allocations (dimension and base fact)
Shipments (dimension and base fact)

Specific to intraday data, two sets of temporary tables are additionally populated. Intraday rejected records are written to tables starting with EID$ , such as

EID$_W_RTL_INV_IT_LC_DY_TMP . Rejections happen for the same reasons as the nightly batch, such as one or more dimension identifiers missing from the associated dimension tables. The input data (including rejects) is then copied to another temporary table starting with ID , such as ID_W_RTL_INV_IT_LC_DY_FS . Intraday data is saved so it can be leveraged automatically by nightly batch processing, meaning that any record provided intraday does not need to be provided again in the nightly batch files (unless you need to make further changes to that record). When performing nightly batch runs, the incoming nightly batch files will get priority for the load and will then be supplemented with intraday records by merging the ID table data back into the staging table for the fact. If the ID table has multiple instances of the same positional record (for example, the same item/location has several inventory updates) then the most recent intraday record will be used for the nightly load. Only intraday data loaded since the last nightly batch will be considered for use in the next nightly batch. The temporary tables will retain data for a period of 7 days for debugging purposes, and then PURGE_INTRADAY_JOB will drop the oldest records to pass that threshold. RA_ERROR_COLLECTION_INTRADAY_JOB is responsible for collecting the rejected record details and writing the summary report to W_ID_REJECTED_RECORDS .

For all positional facts, intraday data is loaded only as far as the “general” table, such as W_RTL_INV_IT_LC_G . This allows for intraday updates to the current positions, which could then be used for downstream processing and extensions, without going through the time-intensive process of updating BI aggregates. Because aggregate tables are not updated intraday, it means this data is not available to the daily or weekly PDS exports but could be leveraged in custom processes. Subject areas that do not have a general table will directly update the base item/location/date fact table directly. If a fact table has an associated dimension, then the dimension will be updated first followed by the fact.

Intraday updates to the data warehouse tables will behave differently from standard nightly batch processing. All intraday updates are performed incrementally, even where an interface would normally expect a full snapshot of data to be provided. For the dimensions, incoming data is either inserted or merged onto the existing records. If a record is not provided as input but it already exists in the target table, no changes will be made to that record. If no data is provided, then no changes are made to the dimension. Similar logic is used for the fact updates, where a new record will be inserted and existing records updated, but records not provided on the input table will remain unchanged in the target table. Only the nightly batch cycle will perform the complete load of data, which includes closing/deleting records and applying reclasses or other major changes. Some fact loads have additional changes to their behavior as noted below.

Intraday Load Behavior

Subject Area	Load Behavior
Inventory Position	Data is loaded only to`W_RTL_INV_IT_LC_G`. Base fact measures such as`INV_SOH_QTY`are updated for existing records, but calculated felds such as`LAST_INVRC_DT`will not be updated. Calculated felds are only updated in the nightly batch.
Pricing	Data is loaded only to`W_RTL_PRICE_IT_LC_G`. Base fact measures such as `SELLING_UNIT_RTL_AMT_LCL`as well as calculated measures such as`LST_MKDN_COUNT` will all be updated by the load, with the exception of Prior Cost Amt/Date felds which cannot change intraday.
Purchase Orders	Data is loaded only to`W_RTL_PO_DETAILS_D` and`W_RTL_PO_ONORD_IT_LC_G`. Dimension attributes will be updated on existing records based on`ORDER_NO`. Multiple revisions cannot be inserted intraday due to usage of effective from/to date felds, so new revisions will overwrite the existing one. Base fact measures such as`PO_ONORD_QTY`are updated for existing records. Secondary key values such as buyer and allocation IDs cannot be changed outside of nightly batch, changes to these felds will be ignored intraday.

Subject Area Load Behavior

Allocations Data is loaded toW_RTL_ALC_DETAILS_Dand
W_RTL_ALC_IT_LC_DY_F. Dimension attributes
will be updated on existing records based on
ALLOC_NO. Multiple revisions cannot be
inserted intraday due to usage of effective
from/to date felds, so new revisions will
overwrite the existing one. The fact table is
updated for all felds the same as the nightly
batch job, since this is a non-positional base
fact that allows for inserts and updates.

Shipments Data is loaded toW_RTL_SHIP_DETAILS_Dand
W_RTL_SHIP_IT_LC_DY_F. Dimension attributes
will be updated on existing records based on
SHIPMENT_NO. Multiple revisions cannot be
inserted intraday due to usage of effective
from/to date felds, so new revisions will
overwrite the existing one. The fact table is
updated for all felds the same as the nightly
batch job, since this is a non-positional base
fact that allows for inserts and updates.

Subject Area	Load Behavior
Allocations	Data is loaded to`W_RTL_ALC_DETAILS_D`and `W_RTL_ALC_IT_LC_DY_F`. Dimension attributes will be updated on existing records based on `ALLOC_NO`. Multiple revisions cannot be inserted intraday due to usage of effective from/to date felds, so new revisions will overwrite the existing one. The fact table is updated for all felds the same as the nightly batch job, since this is a non-positional base fact that allows for inserts and updates.
Shipments	Data is loaded to`W_RTL_SHIP_DETAILS_D`and `W_RTL_SHIP_IT_LC_DY_F`. Dimension attributes will be updated on existing records based on `SHIPMENT_NO`. Multiple revisions cannot be inserted intraday due to usage of effective from/to date felds, so new revisions will overwrite the existing one. The fact table is updated for all felds the same as the nightly batch job, since this is a non-positional base fact that allows for inserts and updates.

Inventory History Current Position Load

Module Name	HIST_INV_GENERAL_LOAD_ADHOC
Description	Copies the ending positions of inventory history for the last week into the General (G) table for the purpose of testing the data and integrations within RAP.
Dependencies	HIST_INV_LOAD_ADHOC
Business Activity	Nightly Batch Preparation

Design Overview

This process takes the final week of inventory data loaded using the HIST_INV_LOAD_ADHOC process and copies it into the table for current inventory positions ( W_RTL_INV_IT_LC_G ). This program uses an INSERT statement, so it cannot be re-run multiple times without first truncating the table. The purpose of this program is to test any integrations or reports that use this table prior to actually running nightly batches, when it would normally be populated. The most common use case is for Inventory Planning Optimization testing, which uses this table to get the current inventory position during ad hoc and weekly batch runs.

If you are loading inventory over multiple phases, then this job cannot be used after the first phase. For updating the W_RTL_INV_IT_LC_G table with the item/locations in the second phase of history and beyond, you must include a full snapshot of inventory data in the following nightly batch run after history is loaded. This will be done either by providing the INVENTORY.csv file having all item/locations in it, or if using RDE, by updating the RDE run type from incremental ( I ) to full ( F ) in the system options for the AIF DATA schedule.

Key Tables Affected

Table Usage W_RTL_INV_IT_LC_DY_F Input W_RTL_INV_IT_LC_G Output

Inventory History Load

Module Name HIST_INV_LOAD_ADHOC Description Processes any staged inventory history data for end-of-week snapshots, starting from the last processed week. Dependencies HIST_STG_CSV_INV_LOAD_ADHOC Business Activity Historical Data Load

Design Overview

The inventory history load process supports loading of end-of-week inventory snapshots over a long period of time to populate the data warehouse with historical data. It requires the inventory data to already be staged into the database by one of the available staging processes. Multiple weeks of inventory can be provided in a single file, though it is recommended to not load more than one month at a time unless the volumes are low. Every record in the data must be for a week-ending date; other dates in the file will not work using this process.

The inventory history processes in POM support loading data for two datasets, inventory positions and inventory pack positions. When using CSV file formats, this is the INVENTORY.csv and INVENTORY_PACK.csv files. When using DAT file formats, this is the W_RTL_INV_IT_LC_DY_FS.dat and W_RTL_INVPK_IT_LC_DY_FS.dat files.

The C_HIST_LOAD_STATUS configuration table controls the actions taken by the process. Before running the process for the first time, you must set up this table for the following:

Set the history load date ( HIST_LOAD_LAST_DATE ) to be the very latest date you expect to load history for (this can be changed later if needed to load more weeks). The date must be a week-ending date and should have 00:00:00 as the timestamp after saving the date to the database table.
Disable any aggregate ( _A ) tables you do not wish to populate by setting ENABLED_IND to N . When loading data only for AI Foundation or Planning, you only need the history temporary table ( W_RTL_INV_IT_LC_DY_HIST_TMP ), base fact ( W_RTL_INV_IT_LC_DY_F ) and week aggregate ( W_RTL_INV_IT_LC_WK_A ). If using inventory pack data, then additionally enable the TMP , F , and A tables for that data flow as well. For RI, all tables should be enabled and loaded.

Once setup is complete, begin processing files from the earliest week-ending date you plan to load. You must start from the beginning of the history and load data sequentially. You cannot load data out of order and you cannot load the same week multiple times without first erasing the data from your database. After a week is loaded successfully, the C_HIST_LOAD_STATUS records are updated with the most recent load status and date. You may choose to load both inventory and pack data at the same time or you can run the process only for inventory positions first, and then switch the configurations to run for inventory pack data second.

If you will be loading inventory history after you have already started nightly batches, then you must also change two parameters in C_ODI_PARAM_VW from the Control Center:

INV_NIGHTLY_BATCH_IND – Change this to Y to indicate that nightly batches have been run but you are planning to load history for prior dates.
INV_LAST_HIST_LOAD_DT – Set this to the final week of history data you plan to load, which must be a week-ending date and must be before the nightly batches were started.

If you are loading inventory data over multiple phases (where each phase contains a distinct and separate set of item/locations for some overlapping period of time) then you must be sure to reset C_HIST_LOAD_STATUS before each additional phase to accurately reflect the timeframe in the files and the tables that should be populated with the data. The load status and max completed date columns should be set back to null, and the HIST_LOAD_LAST_DATE should reflect the final date of history in the next phase of data. You would then run HIST_INV_LOAD_ADHOC procedures for your new files. The W_RTL_INV_IT_LC_G table will not be updated by additional phases of history loads, meaning that you must include a full snapshot of inventory data (inclusive of all item/locations currently having inventory across all phases of data) in your next nightly batch run. This full snapshot needs to occur at least on the day after the end of history data in the last phase of loads. For example, if you load a second phase of history data up through Sunday (end of week) then your nightly batch data for Monday must include a full snapshot of inventory to update the current positions in the W_RTL_INV_IT_LC_G table. This full snapshot is provided either using INVENTORY.csv (if loading data from files only) or by configuring the RDE jobs sourcing data from Merchandising to pull a full extract for all positional facts for that night.

Key Tables Affected

Table	Usage
C_HIST_LOAD_STATUS	Confguration
W_RTL_INV_IT_LC_DY_FS	Input
W_RTL_INVPK_IT_LC_DY_FS	Input
W_RTL_INV_IT_LC_DY_F	Output
W_RTL_INVPK_IT_LC_DY_F	Output
W_RTL_INV_IT_LC_G	Output
W_RTL_INVPK_IT_LC_G	Output
W_RTL_INV_IT_LC_WK_A	Output
W_RTL_INVPK_IT_LC_WK_A	Output
W_RTL_INV_IT_RG_DY_A	Output
W_RTL_INV_IT_DY_A	Output
W_RTL_INV_IT_WK_A	Output
W_RTL_INV_SC_LC_DY_A	Output
W_RTL_INV_CL_LC_DY_A	Output
W_RTL_INV_DP_LC_DY_A	Output
W_RTL_INV_SC_LC_DY_CUR_A	Output
W_RTL_INV_SC_DY_A	Output
W_RTL_INV_SC_DY_CUR_A	Output
W_RTL_INV_SC_LC_WK_A	Output
W_RTL_INV_CL_LC_WK_A	Output

Table Usage W_RTL_INV_DP_LC_WK_A Output W_RTL_INV_SC_LC_WK_CUR_A Output W_RTL_INV_SC_WK_A Output W_RTL_INV_SC_WK_CUR_A Output

Inventory History Staging

Module Name HIST_STG_CSV_INV_LOAD_ADHOC Description Stages the INVENTORY.csv and INVENTORY_PACK.csv files for the ad hoc inventory load programs. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Historical Data Load

Design Overview

This process looks for the INVENTORY.csv and INVENTORY_PACK.csv files placed on the server by a history zip file upload, moves them into a preprocessing table, and transforms them for use by the HIST_INV_LOAD_ADHOC process.

Note

The inventory files used for history data must contain only week-ending dates and must be full, weekly snapshots of data.

Key Tables Affected

Table Usage W_RTL_INV_IT_LC_DY_FTS File Input W_RTL_INVPK_IT_LC_DY_FTS File Input W_RTL_INV_IT_LC_DY_FS Output W_RTL_INVPK_IT_LC_DY_FS Output

Inventory History Staging (Legacy)

Module Name HIST_STG_INV_LOAD_ADHOC Description Stages the W_RTL_INV_IT_LC_DY_FS.dat and W_RTL_INVPK_IT_LC_DY_FS.dat files for the ad hoc inventory load programs. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Historical Data Load

Design Overview

This process looks for the W_RTL_INV_IT_LC_DY_FS.dat and W_RTL_INVPK_IT_LC_DY_FS.dat files placed on the server by a history ZIP file upload and loads them for use by the HIST_INV_LOAD_ADHOC process.

Note

The inventory files used for history data must contain only week-ending dates and must be full, weekly snapshots of data.

Key Tables Affected

Table	Usage
W_RTL_INV_IT_LC_DY_FS	File Input
W_RTL_INVPK_IT_LC_DY_FS	File Input

Inventory Out of Stock Load

Module Name HIST_INV_OOS_LOAD_ADHOC Description Stages and loads the INVENTORY_OOS.csv file for out of stock and outlier indicators. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Historical Data Load

Design Overview

This process looks for the INVENTORY_OOS.csv file placed on the server by a history zip file upload, moves it into a preprocessing table, and transforms it into the target table for use in AI Foundation loads.

Note

The inventory OOS file must contain only week-ending dates; other day dates will not be accepted into the interface

Key Tables Affected

Table	Usage
W_RTL_INVOOS_IT_LC_WK_FS	Input
W_RTL_INVOOS_IT_LC_WK_F	Output

Inventory Reclass History Load

Module Name HIST_CSV_INVRECLASS_LOAD_ADHOC Description Loads the INV_RECLASS.csv file into the data warehouse and populates key data tables used to integrate with other systems for history data. Dependencies HIST_ZIP_FILE_LOAD_ADHOC Business Activity Historical Data Load

Design Overview

The history load process for Inventory Reclass transactions accepts an input file at the item/ location/day level using the file specification for INV_RECLASS.csv . It assumes the file has already been moved into place by the HIST_ZIP_FILE_LOAD_ADHOC process. This process imports the file into a preprocessing table in the database, transforms it to internal staging tables, then loads it into the base fact (item/location/day) as well as the week aggregate used for integrations (item/location/week).

Key Tables Affected

Table		Usage
W_RTL	_INVRECLASS_IT_LC_DY_FTS	File Input
W_RTL	_INVRECLASS_IT_LC_DY_FS	Staging
W_RTL	_INVRECLASS_IT_LC_DY_F	Output (Base Fact)
W_RTL	_INVRECLASS_IT_LC_WK_A	Output (Aggregate)

Inventory Refresh from Merchandising

Module Name	RDE_INSERT_FULL_INV_POS_ADHOC
Description	Prepares the RDE nightly extract for inventory positions to pull all possible item/locations for the next run as a means of refreshing RAP application inventory from the source without using separate ad hoc jobs.
Dependencies	None
Business Activity	Nightly Batch Maintenance

Design Overview

This process contains only one job, which is named RDE_INSERT_FULL_INV_POS_JOB . The purpose of this job is to query the Merchandising Foundation Cloud Service (MFCS) table ITEM_LOC and extract all valid item/locations into a temporary table. The list of item/locations is filtered to the same set of items that the nightly batch would allow from MFCS to RAP (only active and approved items). The next time that the inventory position extract RDE_EXTRACT_FACT_P7_INVILDSDE_JOB runs, it will include all of these item/locations even if their inventory values did not change since yesterday. This process is only applicable when MFCS is on version 23 or greater, as the job needs to directly query MFCS data that is being replicated to the Analytics & Planning database.

Key Tables Affected

Table Usage ITEM_LOC MFCS Source Table ITEM_MASTER MFCS Source Table RA_INV_IT_LC_EXT RDE Temp Table

Inventory Reload

Module Name INV_RELOAD_PROCESS_ADHOC Description Provides an automated way to reload a single week of historical inventory as a way of correcting bad data on the inventory position fact table Dependencies None Business Activity Data Correction

Design Overview

This process contains only one job, which is named INV_RELOAD_JOB . This job deletes a week of data from your inventory position fact tables and inserts the data found on W_RTL_INV_IT_LC_DY_FS in its place. The process expects a single week of inventory data (with DAY_DT equal to a week-ending date) to be loaded into the W_RTL_INV_IT_LC_DY_FS staging table. This job deletes that specific week of data from the W_RTL_INV_IT_LC_DY_F and W_RTL_INV_IT_LC_WK_A tables and then inserts the staging table data. If you then need to move this data to downstream applications, you must also run the associated ad hoc processes to load inventory data to those solutions. The process makes use of the C_HIST_LOAD_STATUS table to manage the data reload so that it overwrites the HIST_LOAD_LAST_DATE for tables being processed and may update other fields based on the reload activity. The W_RTL_INV_IT_LC_DY_F and W_RTL_INV_IT_LC_WK_A tables are always loaded by this process, because all customers are expected to be using these two core tables regardless of the RAP applications they subscribe to.

This process is designed for customers that provide full weekly snapshots of their inventory positions, such that deleting a week of data from the internal tables can be based solely on the DAY_DT in the staging table and no other date. This job does not update the W_RTL_INV_IT_LC_G current positions, as it assumes you are only trying to reload prior weeks of inventory, not the current week. The data is assumed to be correct in W_RTL_INV_IT_LC_G ; if it is not, you must push in new inventory data using nightly batch processing to correct any records in the current week. For example, if the current week has some non-zero positions that need to be zeroed out after your data correction, you need to include those zero-balance rows in a nightly batch file so they can be both applied to W_RTL_INV_IT_LC_G and updated for the current week in the other data warehouse tables and downstream applications.

Key Tables Affected

Table	Usage
W_RTL_INV_IT_LC_DY_FS	Input Table

Table	Usage
W_RTL_INV_IT_LC_DY_F	Target Table
W_RTL_INV_IT_LC_WK_A	Target Table

Inventory Selling Date Seeding

Module Name LOAD_W_RTL_INV_IT_LC_G_FIRST_SOLD_DT_ADHOC Description Calculates the initial value of First Sold Date for all item/locations in inventory, based on sales history data. Dependencies SEED_CSV_W_RTL_INV_IT_LC_DY_F_PROCESS_ADHOC Business Activity Historical Data Load

Design Overview

This process populates the fields W_RTL_INV_IT_LC_G.FIRST_SOLD_DT and LAST_SOLD_DT with values, using your historical sales data to calculate the first time each item/location with stock on hand was sold. This process should only run after all inventory and sales history is completely loaded and you are ready to begin nightly batches. If this process does not run, then all item/locations will start with a first/last selling date of the first transaction to occur on it in nightly batch runs. These date values are used by the AI Foundation Cloud Services (Lifecycle Pricing Optimization) as an input to determine item lifecycles from the history data in RI.

Key Tables Affected

Table	Usage
W_RTL_SLS_TRX_IT_LC_DY_F	Input
W_RTL_INV_IT_LC_G	Output

List Server Objects

Module Name	RI_LIST_ENV_OBJECTS_ADHOC_PROCESS
Description	Perform pod listings, get list of fles in Object Store, as well as get directory listings of fles in the key batch fle directories on the application pods.
Dependencies	None
Business Activity	Batch Administration

Design Overview

This process provides a utility job for listing the files present on the application server in certain directories relating to batch processing and data movement. It can list files in object storage and internal server folders, depending on the parameters passed into the job from POM. This process is specifically for the RI application server and associated object storage, which is used for the AIF DATA batches. This process can aid in debugging batch issues where you

need to know which files are currently being used on the server or which files have just been placed on the server by recently executed jobs.

As parameters, one of the following must be passed in from the POM UI:

—ls:

—ftspath:

Input Table	Target Table
W_RTL_MARKET_PRODUCT_DS	W_RTL_MARKET_PRODUCT_D
W_RTL_MARKET_PROD_DHS	W_RTL_MARKET_PROD_DH
W_RTL_MARKET_PRODUCT_DS_TL	W_RTL_MARKET_PRODUCT_D_TL
W_RTL_MARKET_PROD_BRAND_DS_TL	W_RTL_MARKET_PROD_BRAND_D_TL
W_RTL_MARKET_PRODUCT_MTX_DS	W_RTL_MARKET_PRODUCT_MTX_D
W_RTL_MARKET_PROD_ATTR_DS	W_RTL_MARKET_PROD_ATTR_D
W_RTL_MARKET_PROD_ATTR_MTX_DS	W_RTL_MARKET_PROD_ATTR_MTX_D
W_RTL_MARKET_PROD_BRAND_DS	W_RTL_MARKET_PROD_BRAND_D
W_RTL_MARKET_PROD_DH_MTX_DS	W_RTL_MARKET_PROD_DH_MTX_D
W_RTL_MKTSLS_TA_CH_CNG_WK_FS	W_RTL_MKTSLS_TA_CH_CNG_WK_F
W_RTL_MKTSLS_TA_CH_HG_WK_FS	W_RTL_MKTSLS_TA_CH_HG_WK_F
W_RTL_MKTSLS_TA_CH_CNG_WK_F	W_RTL_MKTSLS_TA_CMG_CS_QR_A
W_RTL_MKTSLS_TA_CH_CNG_WK_F	W_RTL_MKTSLS_TA_CMG_QR_A
W_RTL_MKTSLS_TA_CH_CNG_WK_F	W_RTL_MKTSLS_TA_CL_CS_QR_CUR_A
W_RTL_MKTSLS_TA_CH_CNG_WK_F	W_RTL_MKTSLS_TA_CL_QR_CUR_A

Input Table	Output Table
W_INT_ORG_D	W_PDS_ORGANIZATION_D
W_INT_ORG_D_TL	W_PDS_ORGANIZATION_D
W_INT_ORG_DH	W_PDS_ORGANIZATION_D
W_DOMAIN_MEMBER_LKP_TL	W_PDS_ORGANIZATION_D
W_INT_ORG_ATTR_D	W_PDS_ORGANIZATION_D
W_MCAL_DAY_D	W_PDS_CALENDAR_D
W_RTL_MCAL_DAY_FLEX_D	W_PDS_CALENDAR_D
W_EXCH_RATE_G	W_PDS_EXCH_RATE_G
W_RTL_ITEM_GRP1_D	W_PDS_PRODUCT_ATTR_D
W_DOMAIN_MEMBER_LKP_TL	W_PDS_PRODUCT_ATTR_D
W_RTL_PRODUCT_ATTR_D	W_PDS_UDA_D
W_DOMAIN_MEMBER_LKP_TL	W_PDS_UDA_D
W_RTL_PRODUCT_ATTR_D	W_PDS_DIFF_D
W_RTL_PRODUCT_ATTR_D_TL	W_PDS_DIFF_D
W_RTL_ITEM_GRP2_D	W_PDS_PRODUCT_PACK_D
W_RTL_CUSTSEG_D	W_PDS_CUSTSEG_D
W_INVENTORY_PRODUCT_ATTR_D	W_PDS_REPL_ATTR_IT_LC_D
W_INT_ORG_D_CFA	W_PDS_ORG_ATTR_STR_D
W_INT_ORG_ATTR_D	W_PDS_ORG_ATTR_STR_D
W_ORGANIZATION_FLEX_D	W_PDS_ORG_ATTR_STR_D
W_INT_ORG_D_CFA	W_PDS_ORG_ATTR_NBR_D
W_INT_ORG_D_CFA	W_PDS_ORG_ATTR_DT_D
W_PRODUCT_D_CFA	W_PDS_PRODUCT_ATTR_STR_D
W_PRODUCT_D_CFA	W_PDS_PRODUCT_ATTR_NBR_D
W_PRODUCT_D_CFA	W_PDS_PRODUCT_ATTR_DT_D
W_RTL_IT_LC_D_CFA	W_PDS_PROD_ORG_ATTR_STR_D
W_RTL_IT_LC_D_CFA	W_PDS_PROD_ORG_ATTR_NBR_D
W_RTL_IT_LC_D_CFA	W_PDS_PROD_ORG_ATTR_DT_D

Input Table	Output Table
W_RTL_SLS_IT_LC_WK_A	W_PDS_SLS_IT_LC_WK_A
W_RTL_SLS_IT_LC_WK_A	W_PDS_GRS_SLS_IT_LC_WK_A
W_RTL_SLSWF_IT_LC_WK_A	W_PDS_SLSWF_IT_LC_WK_A
W_RTL_INV_IT_LC_WK_A	W_PDS_INV_IT_LC_WK_A
W_RTL_INVU_IT_LC_WK_A	W_PDS_INVU_IT_LC_WK_A
W_RTL_PO_ONORD_IT_LC_DY_F	W_PDS_PO_ONORD_IT_LC_WK_A
W_RTL_PO_ONORD_IT_LC_DY_F	W_PDS_PO_ONORD_IT_LC_DY_F
W_RTL_MKDN_IT_LC_WK_A	W_PDS_MKDN_IT_LC_WK_A
W_RTL_INVADJ_IT_LC_WK_A	W_PDS_INVADJ_IT_LC_WK_A
W_RTL_INVRC_IT_LC_WK_A	W_PDS_INVRC_IT_LC_WK_A
W_RTL_INVTSF_IT_LC_WK_A	W_PDS_INVTSF_IT_LC_WK_A
W_RTL_INVRTV_IT_LC_WK_A	W_PDS_INVRTV_IT_LC_WK_A

Input Table	Output Table
W_RTL_INVRECLASS_IT_LC_WK_A	W_PDS_INVRECLASS_IT_LC_WK_A
W_RTL_DEALINC_IT_LC_WK_A	W_PDS_DEALINC_IT_LC_WK_A
W_RTL_ICM_IT_LC_WK_A	W_PDS_ICM_IT_LC_WK_A
W_RTL_TSF_IT_LC_DY_F	W_PDS_TSF_IT_LC_DY_F
W_RTL_ALC_IT_LC_DY_F	W_PDS_ALC_IT_LC_DY_F

Module Name	HIST_CSV_MARKDOWN_LOAD_ADHOC
Description	Loads the`MARKDOWN.csv`fle into the data warehouse and populates key data tables used to integrate with other systems for history data.
Dependencies	HIST_ZIP_FILE_LOAD_ADHOC
Business Activity	Historical Data Load

Table	Usage
MFP_PLAN1_EXP	Input
W_RTL_PLAN1_PROD1_LC1_T1_FS	Staging
W_RTL_PLAN1_PROD1_LC1_T1_F	Output

Input Table	Output Table
W_PRODUCT_D	W_PDS_PRODUCT_D
W_PRODUCT_D_TL	W_PDS_PRODUCT_D
W_PROD_CAT_DH	W_PDS_PRODUCT_D
W_PRODUCT_ATTR_D	W_PDS_PRODUCT_D
W_DOMAIN_MEMBER_LKP_TL	W_PDS_PRODUCT_D

Module Name	LOAD_PDS_FACT_INITIAL_PROCESS_ADHOC
Description	Exports a full snapshot of historical inventory to the data exchange schema for Planning.
Dependencies	HIST_INV_LOAD_ADHOC
Business Activity	RI Integrations

Table	Usage
C_SOURCE_CDC	Confguration
W_RTL_INV_IT_LC_WK_A	Input
W_PDS_INV_IT_LC_WK_A	Output

Module Name	CLEANUP_C_LOAD_DATES_PLANNING_ADHOC
Description	Erases the execution status of planning batch programs. This is required to run a program multiple times for the same business date.
Dependencies	None
Business Activity	RI Integrations

Table	Usage
W_RTL_PRICE_IT_LC_DY_FTS	File Input
W_RTL_PRICE_IT_LC_DY_FS	Staging
W_RTL_PRICE_IT_LC_DY_F	Output
W_RTL_PRICE_IT_LC_G	Output

Table	Usage
W_RTL_PRACT_IT_LC_DY_FS	Staging
W_RTL_PRACT_IT_LC_DY_F	Output
W_RTL_PRBDGT_IT_LC_FS	Staging
W_RTL_PRBDGT_IT_LC_F	Output

Module Name	RDE_BUSINESS_DATE_UPD_ADHOC
Description	Ad hoc process that updates the current business date in `RA_SRC_CURR_PARAM_G`to be used during the current batch or ad hoc cycle.
Dependencies	None
Business Activity	Nightly Batch Maintenance

Module Name	RECLASS_LOAD_PROCESS_ADHOC
Description	Performs reclassifcation of products or locations across all tables in the data warehouse, including base facts and aggregates, outside of the nightly batch cycle.
Dependencies	None
Business Activity	Nightly Batch Maintenance

Table	Usage
W_RTL_REJECT_DIMLKUP_TMP	Confguration
W_RTL_REJECT_DIMENSION_TMP	Output

Module Name	REJECT_DATA_CLEANUP_ADHOC
Description	Purges rejected records from certain`E$`tables and populates a list of invalid dimension keys present on the purged data. The invalid keys will be ignored if a related fact history load process is re-run after failing due to these rejections.
Dependencies	None
Business Activity	Historical Data Load

Table		Usage
W_RTL	_INVRTV_IT_LC_DY_FS	Staging
W_RTL	_INVRTV_IT_LC_DY_F	Output (Base Fact)
W_RTL	_INVRTV_IT_LC_WK_A	Output (Aggregate)

Module Name	HIST_SALES_LOAD_ADHOC
Description	Processes any staged sales history data and runs all aggregation programs for a specifed history range.
Dependencies	HIST_STG_CSV_SALES_LOAD_ADHOC
Business Activity	Historical Data Load

Table	Usage
W_RTL_SLS_SC_LC_DY_A	Aggregate (for BI reporting)
W_RTL_SLS_SC_LC_WK_A	Aggregate (for BI reporting)
W_RTL_SLS_CL_LC_DY_A	Aggregate (for BI reporting)
W_RTL_SLS_CL_LC_WK_A	Aggregate (for BI reporting)
W_RTL_SLS_DP_LC_DY_A	Aggregate (for BI reporting)
W_RTL_SLS_DP_LC_WK_A	Aggregate (for BI reporting)
W_RTL_SLS_IT_DY_A	Aggregate (for BI reporting)
W_RTL_SLS_IT_WK_A	Aggregate (for BI reporting)
W_RTL_SLS_SC_DY_A	Aggregate (for BI reporting)
W_RTL_SLS_SC_WK_A	Aggregate (for BI reporting)
W_RTL_SLS_LC_DY_A	Aggregate (for BI reporting)
W_RTL_SLS_LC_WK_A	Aggregate (for BI reporting)
W_RTL_SLS_IT_LC_DY_SN_A	Aggregate (for BI reporting)
W_RTL_SLS_IT_LC_WK_SN_A	Aggregate (for BI reporting)
W_RTL_SLS_IT_DY_SN_A	Aggregate (for BI reporting)
W_RTL_SLS_IT_WK_SN_A	Aggregate (for BI reporting)
W_RTL_SLS_SC_LC_DY_CUR_A	Aggregate (for BI reporting)
W_RTL_SLS_SC_LC_WK_CUR_A	Aggregate (for BI reporting)
W_RTL_SLS_CL_LC_DY_CUR_A	Aggregate (for BI reporting)
W_RTL_SLS_DP_LC_DY_CUR_A	Aggregate (for BI reporting)
W_RTL_SLS_CL_LC_WK_CUR_A	Aggregate (for BI reporting)
W_RTL_SLS_DP_LC_WK_CUR_A	Aggregate (for BI reporting)
W_RTL_SLS_SC_DY_CUR_A	Aggregate (for BI reporting)
W_RTL_SLS_CL_DY_CUR_A	Aggregate (for BI reporting)
W_RTL_SLS_DP_DY_CUR_A	Aggregate (for BI reporting)
W_RTL_SLS_SC_WK_CUR_A	Aggregate (for BI reporting)
W_RTL_SLS_CL_WK_CUR_A	Aggregate (for BI reporting)
W_RTL_SLS_DP_WK_CUR_A	Aggregate (for BI reporting)
W_RTL_SLSPK_IT_LC_WK_A	Aggregate (for BI reporting)
W_RTL_SLSPK_IT_DY_A	Aggregate (for BI reporting)
W_EMPLOYEE_D	Supporting Dimension (for BI reporting)
W_PARTY_PER_D	Supporting Dimension (for BI reporting)
W_RTL_CO_HEAD_D	Supporting Dimension (for BI reporting)
W_RTL_CO_LINE_D	Supporting Dimension (for BI reporting)

Table	Usage
W_RTL_SLS_TRX_IT_LC_DY_FTS	File Input
W_RTL_SLSPK_IT_LC_DY_FTS	File Input
W_RTL_SLS_TRX_IT_LC_DY_FS	Output
W_RTL_SLSPK_IT_LC_DY_FS	Output

Module Name	HIST_SALES_TEND_LOAD_ADHOC
Description	Processes any staged sales tender data into the sales tender fact table, including processing of related dimensions used by the fact.
Dependencies	HIST_STG_SALES_TEND_LOAD_ADHOC
Business Activity	Historical Data Load

Table	Usage
W_RTL_TRX_TNDR_LC_DY_FS.dat	Input File
W_RTL_TRX_TNDR_LC_DY_FS	Output

Table		Usage
W_RTL_	SLSWF_IT_LC_DY_FTS	File Input
W_RTL_	SLSWF_IT_LC_DY_FS	Staging

Table	Usage
W_RTL_SLSWF_IT_LC_DY_FS	Staging
W_RTL_SLSWF_IT_LC_DY_F	Base Fact
W_RTL_SLSWF_IT_LC_WK_A	Aggregate (PDS Integration)
W_RTL_SLSWF_IT_DY_A	Aggregate (BI Reporting)
W_RTL_SLSWF_IT_WK_A	Aggregate (BI Reporting)
W_RTL_SLSWF_SC_LC_DY_A	Aggregate (BI Reporting)
W_RTL_SLSWF_SC_LC_WK_A	Aggregate (BI Reporting)
W_RTL_SLSWF_SC_LC_DY_CUR_A	Aggregate (BI Reporting)
W_RTL_SLSWF_SC_LC_WK_CUR_A	Aggregate (BI Reporting)

Oracle Retail Knowledge Base

Explorer

2 AI Foundation Data Standalone Processes

AI Foundation Data Standalone Processes

Adjustments History Load

Design Overview

Note

Key Tables Affected

Aggregate Fact History Load

Design Overview

Key Tables Affected

Allocation History Load

Design Overview

Key Tables Affected

Assortment Group Load

Design Overview

Key Tables Affected

Base Fact Data Removal

Design Overview

Batch Individual File Reprocessing

Design Overview

Batch Nightly File Reprocessing

Design Overview

Cost Adjustments History Load

Design Overview

Key Tables Affected

Cost History Load

Design Overview

Note

Key Tables Affected

Custom Hierarchy Load

Design Overview

Key Tables Affected

Customer Loyalty Load

Design Overview

Key Tables Affected

Data Security Load

Design Overview

Key Tables Affected

Deal Actuals History Load

Design Overview

Key Tables Affected

Deal Income History Load

Design Overview

Key Tables Affected

Default Calendar Initialization

Design Overview

Key Tables Affected

ETL Business Date Update

Design Overview

Key Tables Affected

Fix Unusable Indexes

Design Overview

Flexible Fact Load

Design Overview

Key Tables Affected

Gift Card Sales Load

Design Overview

Key Tables Affected

History Data Cleanup

Design Overview

Note

History Data File Upload

Design Overview

History Data Master Flow

Design Overview

Initial Base Cost Seeding

Design Overview

Note

Key Tables Affected

Initial Base Cost Seeding (Legacy)

Design Overview

Note

Key Tables Affected

Initial Calendar Load

Design Overview

Key Tables Affected

Initial Calendar Staging (Legacy)

Design Overview

Key Tables Affected

Table	Usage
W_RTL_SHIP_DETAILS_DTS	File Input (`SHIPMENT_HEAD.csv`)
W_RTL_SHIP_DETAILS_DS	Staging
W_RTL_SHIP_DETAILS_D	Output
W_RTL_SHIP_IT_LC_DY_FTS	File Input (`SHIPMENT_DETAIL.csv`)
W_RTL_SHIP_IT_LC_DY_FS	Staging
W_RTL_SHIP_IT_LC_DY_F nt Load	Output
Module Name HIST_STOCK_COUNT_LOAD_A	DHOC
Description Loads the stock count dimensi	on and facts for Retail Insights reporting.
Dependencies HIST_ZIP_FILE_LOAD_ADHOC
Business Activity Historical Data Load

Table	Usage
W_RTL_STCKLDGR_SC_LC_WK_FS	Staging
W_RTL_STCKLDGR_SC_LC_WK_F	Output
W_RTL_STCKLDGR_SC_LC_MH_FS	Staging
W_RTL_STCKLDGR_SC_LC_MH_FS	Output

Table	Usage
W_RTL_STTRFC_LC_DY_MI_FS	Staging
W_RTL_STTRFC_LC_DY_MI_F	Output

Module Name	CREATE_PARTITION_ADHOC
Description	Uses the provided range of dates and the loaded calendar information to generate table partitions across the data warehouse data model.
Dependencies	CALENDAR_LOAD_ADHOC
Business Activity	Initial System Setup