Re-Using Product Identifiers
It may happen over time that the same product keys (such as SKU numbers) will be re-used to represent brand new items.
There are two parameters in C_ODI_PARAM_VW
that must be updated to enable this functionality (RI_ITEM_REUSE_IND
and RI_ITEM_REUSE_AFTER_DAYS
). Set RI_ITEM_REUSE_IND
to Y
and set RI_ITEM_REUSE_AFTER_DAYS
to some number greater than 0. The combination of these parameters will alter the product
hierarchy load in the following ways:
-
Once enabled, if an item is deleted (when using incremental loads) or stops appearing in the nightly product files (when using full snapshot loads) it will NOT be closed, it will remain as
current_flg=Y
for the number of days specified in the configuration. -
If the item re-appears in the product file the next night, then the existing item record remains open with
current_flg=Y
and it will be as if it was never dropped or deleted. -
If the item re-appears in the product file only after the set number of days has elapsed, then the old version of the product is both closed and archived (example below). The incoming item record is inserted as a new item with no history.
Here is an example of re-using an item:
-
Item number 1001 is created as a new item in a source system, such as RMFCS.
-
Item exists for a period of time while accumulating fact data such as sales and inventory.
-
Item becomes inactive and is eventually deleted from the source system, which marks it inactive in RAP after the set number of days for re-use has passed.
-
After another period of time (such as 1 month) the same item number 1001 is added back in the source system, representing a completely new item with different attributes and hierarchies. The window between the old and new item is configured in
C_ODI_PARAM_VW
as mentioned in the Setup and Configuration chapter. -
When item 1001 comes to RAP again, the old version of this item will be archived using a value appended to the code (for example, 1001_230101) across all product tables in the data warehouse (the item data would already have been dropped from Planning when the re-use number of days had elapsed). Item 1001 is then inserted as a new item not associated in any way with the prior instance of that ID. The history that already exists for item 1001 is now associated with the archived item 1001_230101, and new fact data will be associated only with the new definition of item 1001 going forward.
The same process flow applies both when you are creating the PRODUCT.csv
file from a non-Oracle source
and when you use RMFCS to provide the data.