SIM Fails To Authorize Unit Amount Stock Count When One Child Auto Authorizes (Doc ID 2805470.1)

Last updated on JUNE 28, 2023

Applies to:

Symptoms

When performing Unit and Amount stock counts in SIM, if one or more children auto authorizes because there are no discrepancies, then APPROVED_QUANTITY and APPROVED_DATE_TIME remain NULL in table STOCK_COUNT_LINE_ITEM, which throws the error in the screen: "Failed to approve stock count child!".

In sim.log the error is:
"Caused by: oracle.jdbc.OracleDatabaseException: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SIM16.UNIT_AMOUNT_EXPORT_PKG", line 107
ORA-01400: cannot insert NULL into ("SIM16"."STOCK_COUNT_EXPORT"."APPROVED_DATE")
ORA-06512: at "SIM16.UNIT_AMOUNT_EXPORT_PKG", line 12
ORA-06512: at line 1"


SIM should properly set APPROVED_QUANTITY and APPROVED_DATE_TIME in table STOCK_COUNT_LINE_ITEM when auto authorizing stock count children.
Result:
Department X stock count child has NULL values in APPROVED_QUANTITY and APPROVED_DATE_TIME in table STOCK_COUNT_LINE_ITEM
Department Y stock count child has values set properly in table STOCK_COUNT_LINE_ITEM
Stock count remains open with status “processing” in department Y

Steps to reproduce:

 1. Create a Unit and Amount product group with departments X and Y, make sure auto authorize is UNCHECKED and breakdown is set to DEPARTMENT.
 2. Create a schedule for the product group for the next day.
 3. Wait one day.
 4. Run batch ExtractUnitAmountStockCount.sh.
 5. Count department X with no discrepancies.
 6. Notice SIM has already authorized department X.
 7. Count department Y with discrepancies.
 8. Authorize department Y manually.
 9. Notice the message 'Failed to approve stock count child!' on the screen.
10. Notice the message 'cannot insert NULL into ("SIM16"."STOCK_COUNT_EXPORT"."APPROVED_DATE")' in sim.log.

Changes

Cause

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