Purge_Data Package Is Using Invalid Validation Scripts (Doc ID 2290424.1)

Last updated on JULY 27, 2017

Applies to:

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

Symptoms

On Oracle Retail Store Inventory Management (SIM) 13.2.4 version, there is an issue to an item purging validations to Unique identifier Number (UIN) intersection tables.

Script below that is under IS_ITEM_DELETE_ALLOWED function.
Purpose of this function is to validate if the item marked for deletion should be deleted. If any pending transactions exist related to the item, then the validation should return false.

CURSOR c_custord_line_item_uin IS
  SELECT 'x'
  FROM custord_line_item_uin
  WHERE custord_line_item_id = i_item_id
  AND ROWNUM = 1;
  CURSOR c_return_line_item_uin IS
  SELECT 'x'
  FROM return_line_item_uin
  WHERE return_line_item_id = i_item_id
  AND ROWNUM = 1;
  WHERE return_line_item_id = i_item_id
  AND ROWNUM = 1;
  CURSOR c_rk_shipment_line_item_uin IS
  SELECT 'x'
  FROM rk_shipment_line_item_uin
  WHERE shipment_line_item_id = i_item_id
  AND ROWNUM = 1;
  CURSOR c_stock_count_line_item_uin IS
  SELECT 'x'
  FROM stock_count_line_item_uin
  WHERE stock_count_line_item_id = i_item_id
  AND ROWNUM = 1;

Above validation script, is that the ITEM IDs are being compared to the IDENTIFIER IDs to the line item the record is associated to.

Expected behavior:
That portion of the validation script required to join to a line item table in order to get and validate the ITEM IDs.


The issue can be reproduced at will with the following steps:

  1. select * from sim01.stock_count_line_item_uin;
  2. select * from SIM01.STOCK_COUNT_LINE_ITEM where id=XXX;
  3. select * from sim01.uin_detail where item_id='XXX0';
  4. select 'x'
     FROM   stock_count_line_item_uin
     WHERE  stock_count_line_item_id = i_item_id
            AND ROWNUM = 1; -- will not correctly validate the item
  5. Expected:
SELECT 'x'
  FROM sim01.stock_count_line_item_uin su inner join sim01.stock_count_line_item si on su.stock_count_line_item_id=si.id
  WHERE si.item_id = 'XXXX'
  AND ROWNUM = 1;



Changes

 

Cause

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