Salesprocess.ksh Fails With ORA-30926 Errors When Substituting Items exist (Doc ID 3065699.1)

Last updated on JANUARY 02, 2025

Applies to:

Symptoms

On : 16.0.3 version, Items

salesprocess.ksh fails with ORA-30926 errors when substituting items exist regardless of the location for which the substitute item has been setup will following error:

1) There is no join on the SUB_ITEMS_DETAIL.LOCATION field to the SVC_POSUPLD_LINE_ITEM_GTT.LOC_VALUE, meaning that all substitute items regardless of location will be returned which is not correct.
2) When a substitute item exists the SUB_ITEMS_DETAIL.ITEM value will be used, however the IM_DEPT, IM_CLASS and IM_SUBCLASS fields are the dept/class/subclass for the Substitute item and if different than the item then 2 rows are returned for ITEM, SALES_TYPE, LOC, EOW_DATE since the MERGE statement has a group by that includes IM_DEPT, IM_CLASS and IM_SUBCLASS.
 
ERROR
-----------------------
Error ORA-30926: unable to get a stable set of rows in the source tables returned by program unit CORESVC_SALES_UPLOAD_PRST_SQL.PERSIST_ITEM_LOC_HIST.
salesprocess~20240213183428~PROCESS_SALES~CORESVC_SALES_UPLOAD_SQL.PROCESS_SALES Process:28618757 Thread: Failed.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1) Set a ‘Promotional substitute item (i.e. SUBSTITUTE_REASON = ‘P’) for an item that does not have the same dept/class/subclass .
2) Upload a POSU sales file via uploadsales.ksh that contains sales for both the regular item and the substitute item for the same date at the same location.
3) Notice that both the regular item and substitute item now exists in SVC_POSUPLD_STAGING
4) Process salesprocess.ksh and notice the error.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.