Purge Price Histories Retains Only One Record When The Price Id's Are Not In Sequential Order

(Doc ID 1915646.1)

Last updated on NOVEMBER 08, 2016

Applies to:

Oracle Retail Store Inventory Management - Version 13.2.4 and later
Information in this document applies to any platform.


In Store Inventory Management (SIM), find that the PurgePriceHistories.sh batch is deleting more records than the allowed limit of the latest four. This batch works fine, if the record id's (LE_HST_ITM_SLS_PRC.AI_PRC_RT) are sequential. However, if the record id's are not sequential, then more records are getting deleted than the specified limit of retaining the latest four price change records.

Steps to recreate:

  1. Query table LE_HST_ITM_SLS_PRC and find a store and item combination that has multiple price change records (greater than four)
  2. Ensure that the price effective date (LE_HST_ITM_SLS_PRC.TS_PR_EF) is less than current date minus the value specified for the system admin parameter "Days to Hold Price History"
  3. Also ensure that the record ids of the above price changes are NOT sequential (for example: LE_HST_ITM_SLS_PRC.AI_PRC_RT has values such as 991, 992, 993, 9097, 8777, 4563 - i.e. non-sequential)
  4. Run the PurgePriceHistories.sh batch
  5. Query table LE_HST_ITM_SLS_PRC and find a store and item combination and note that only one record is retained - This batch should retain the latest four price change records, but if the record ids are not sequential, this is not happening.
  6. Repeat the test with sequential values for LE_HST_ITM_SLS_PRC.AI_PRC_RT for the records
  7. Run the batch and note that four records are retained



Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms