Inventory Position History Load
RAP supports the loading of inventory position history using full, end-of-week snapshots. These weekly snapshots may be provided one week at a time or as multiple weeks in a single file. The data must be loaded sequentially from the earliest week to the latest week with no gaps or out-of-order periods. For example, you cannot start with the most recent inventory file and go backward; you must start from the first week of history. Refer to Data File Generation for more details on the file requirements.
A variety of C_ODI_PARAM_VW
settings are available in the Control Center to disable inventory features
that are not required for your implementation. All of the following parameters can be changed to a value of N during the history load and enabled later for daily batches, as it will greatly improve the load times:
-
RI_INVAGE_REQ_IND
– Disables calculation of first/last receipt dates and inventory age measures. Receipt date calculation is used in RI and required for Lifecycle Pricing Optimization (as a method of determining entry/exit dates for items). It is also required for Forecasting for the Short Lifecycle (SLC) methods. Set toY
if using any of these applications. -
RA_CLR_LEVEL
– Disables the mapping of clearance event IDs to clearance inventory updates. Used only in RI reporting. -
RI_PRES_STOCK_IND
– Disables use of replenishment data for presentation stock to calculate inventory availability measures. Used only in RI reporting. -
RI_BOH_SEEDING_IND
– Disables the creation of initial beginning-on-hand records so analytics has a non-null starting value in the first week. Used only in RI reporting. -
RI_MOVE_TO_CLR_IND
– Disables calculation of move-to-clearance inventory measures when an item/location goes into or out of clearance status. Used only in RI reporting. -
RI_MULTI_CURRENCY_IND
– Disables recalculation of primary currency amounts if you are only using a single currency. Should be enabled for multi-currency, or disabled otherwise.
If you will be loading inventory history after you have already started nightly batches, then you must also update two additional parameters:
-
INV_NIGHTLY_BATCH_IND
– Change this toY
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 it must be before the nightly batches were started
Although it is supported, it is not advisable to load history data after nightly batches have started. It would be difficult to erase or correct historical data after it is loaded without affecting your nightly batch data as well. For this reason it is best to validate the history data thoroughly in a non-production environment before loading it to the production system.
The following steps describe the process for loading inventory history:
-
If you need inventory to keep track of First/Last Receipt Dates for use in Lifecycle Pricing Optimization or Forecasting (SLC) then you must first load a
RECEIPT.csv
file for the same historical period as your inventory file (because it is used in forecasting, that may make it required for your Inventory Planning Optimization loads as well, if you plan to use SLC forecasting). You must also setRI_INVAGE_REQ_IND
toY
. Receipts are loaded using the processHIST_CSV_INVRECEIPTS_LOAD_ADHOC
. Receipts may be provided at day or week level depending on your history needs. -
Create the file
INVENTORY.csv
containing one or more weeks of inventory snapshots in chronological order along with your CTX file to define the columns that are populated. TheDAY_DT
value on every record must be an end-of-week date (Saturday by default). -
Upload the history file and its context file to Object Storage using the
RAP_DATA_HIST.zip
file. -
Update column
HIST_LOAD_LAST_DATE
on the tableC_HIST_LOAD_STATUS
to be the date matching the last day of your overall history load (will be later than the dates in the current file). This can be done from the Control & Tactical Center. If you are loading history after your nightly batches were already started, then you must set this date to be the last week-ending date before your first daily/weekly batch. No other date value can be used in this case. -
Execute the
HIST_ZIP_FILE_LOAD_ADHOC
process. -
If you are providing
RECEIPT.csv
for tracking receipt dates in history, runHIST_CSV_INVRECEIPTS_LOAD_ADHOC
at this time. -
Execute the
HIST_STG_CSV_INV_LOAD_ADHOC
process to stage your data into the database. Validate your data before proceeding. Refer to Sample Validation SQLs for sample queries you can use for this. -
Execute the
HIST_INV_LOAD_ADHOC
batch process to load the file data. The process loops over the file one week at a time until all weeks are loaded. It updates theC_HIST_LOAD_STATUS
table with the progress, which you can monitor from APEX or DV. Sample Postman message bodies:{ "cycleName":"Adhoc", "flowName":"Adhoc", "processName":"HIST_STG_CSV_INV_LOAD_ADHOC" } { "cycleName":"Adhoc", "flowName":"Adhoc", "processName":"HIST_INV_LOAD_ADHOC" }
This process can be repeated as many times as needed to load all history files for the inventory position. Remember that inventory cannot be loaded out of order, and you cannot go back in time to reload files after you have processed them (for the same item/loc intersections). If you load a set of inventory files and then find issues during validation, erase the tables in the database and restart the load with corrected files.
If you finish the entire history load and need to test downstream systems (like Inventory Planning Optimization) then you
must populate the table W_RTL_INV_IT_LC_G
first (the history load skips this table). There is a separate
standalone job HIST_LOAD_INVENTORY_GENERAL_JOB
in the process HIST_INV_GENERAL_LOAD_ADHOC
that you may execute to copy the final week of inventory from the fact table to this table.
If your inventory history has invalid data, you may get rejected records and the batch process will fail with a message that rejects exist in the data. If this occurs, you cannot proceed until you resolve your input data, because rejections on positional data MUST be resolved for one date before moving onto the next. If you move onto the next date without reprocessing any rejected data, that data is lost and cannot be loaded at a later time without starting over. When this occurs:
-
The inventory history load will automatically populate the table
W_RTL_REJECT_DIMENSION_TMP
with a list of invalid dimensions it has identified. If you are running any other jobs besides the history load, you can also run the processW_RTL_REJECT_DIMENSION_TMP_ADHOC
to populate that table manually. You have the choice to fix the data and reload new files or proceed with the current file -
After reviewing the rejected records, run
REJECT_DATA_CLEANUP_ADHOC
, which will erase theE$
table and move all rejected dimensions into a skip list. You must pass in the module code you want to clean up data for as a parameter on the POM job (in this case the module code isINV
). The skip list is loaded to the tableC_DISCARD_DIMM
. Skipped identifiers will be ignored for the current file load, and then reset for the start of the next run.Example Postman message body:
{ "cycleName": "Adhoc", "flowName":"Adhoc", "processName":"REJECT_DATA_CLEANUP_ADHOC", "requestParameters":"jobParams.REJECT_DATA_CLEANUP_JOB=INV" }
-
If you want to fix your files instead of continuing the current load, stop here and reload your dimensions and/or fact data following the normal process flows.
-
If you are resuming with the current file with the intent to skip all data in
C_DISCARD_DIMM
, restart the failed POM job now. The skipped records are permanently lost and cannot be reloaded unless you erase your inventory data and start loading files from the beginning.
Log a Service Request with Oracle Support for assistance with any of the above steps if you are having difficulties with loading inventory history or dealing with rejected records.