1. Implementation Guide
  2. Data File Generation
  3. Dimension Files
  4. Product File
  5. Re-Using Product Identifiers

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:

  1. 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.

  2. 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.

  3. 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:

  1. Item number 1001 is created as a new item in a source system, such as RMFCS.

  2. Item exists for a period of time while accumulating fact data such as sales and inventory.

  3. 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.

  4. 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.

  5. 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.