The 'Inventory Adjustments' Process (INPVIADJ) Is Adjusting the Quantity of an Item Linked to a Container while the Item and Container Have Been Transferred to a Different Storage Location (Doc ID 2022250.1)

Last updated on MARCH 16, 2016

Applies to:

PeopleSoft Enterprise SCM Inventory - Version 9.1 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

The 'Inventory Adjustments' process (INPVIADJ) (navigation: SCM Integrations, Process Transactions, Inventory, Quantity Adjustment) is adjusting the quantity of an Item linked to a Container while the Item and Container have been transferred to a different Storage Location.

As a result, the Container exists in 2 different Storage Locations. This can make the 'Inventory Transfers' process (IN_TRANSFER) (navigation: SCM Integrations, Process Transactions, Inventory, Storage Location Transfer) fail with the following error:

SQL error. Stmt #: 1620 Error Position: 105 Return: 1427 - ORA-01427: single-row subquery returns more than one row

Failed SQL stmt:UPDATE PS_IN_TR_BC_TAO4 SET ( STORAGE_AREA, STOR_LEVEL_1, STOR_LEVEL_2, STOR_LEVEL_3, STOR_LEVEL_4 ) = ( SELECT DISTINCT C.STORAGE_AREA , C.STOR_LEVEL_1 , C.STOR_LEVEL_2 , C.STOR_LEVEL_3 , C.STOR_LEVEL_4 FROM PS_IN_CTR_LOC2_VW C , PS_PHYSICAL_INV P WHERE C.BUSINESS_UNIT = '5025Z' AND C.BUSINESS_UNIT = PS_IN_TR_BC_TAO4.BUSINESS_UNIT AND C.CONTAINER_ID = PS_IN_TR_BC_TAO4.CONTAINER_ID AND P.BUSINESS_UNIT = '5025Z' AND P.BUSINESS_UNIT = C.BUSINESS_UNIT AND P.CONTAINER_ID = C.CONTAINER_ID AND P.STORAGE_AREA = C.STORAGE_AREA AND P.STOR_LEVEL_1 = C.STOR_LEVEL_1 AND P.STOR_LEVEL_2 = C.STOR_LEVEL_2 AND P.STOR_LEVEL_3 = C.STOR_LEVEL_3 AND P.STOR_LEVEL_4 = C.STOR_LEVEL_4 AND (P.QTY <> 0 OR P.QTY_RESERVED <> 0) ) WHERE PROCESS_INSTANCE = 6043 AND BUSINESS_UNIT = '5025Z' AND CONTAINER_ID &amp;lt;&amp;gt; ' ' AND EXISTS ( SELECT 'X' FROM PS_IN_CTR_LOC2_VW C2 , PS_PHYSICAL_INV P2 WHERE C2.BUSINESS_UNIT = '5025Z' AND C2.BUSINESS_UNIT = PS_IN_TR_BC_TAO4.BUSINESS_UNIT AND C2.CONTAINER_ID = PS_IN_TR_BC_TAO4.CONTAINER_ID AND P2.BUSINESS_UNIT = '5025Z' AND P2.BUSINESS_UNIT = C2.BUSINESS_UNIT AND P2.CONTAINER_ID = C2.CONTAINER_ID AND P2.STORAGE_AREA = C2.STORAGE_AREA AND P2.STOR_LEVEL_1 = C2.STOR_LEVEL_1 AND P2.STOR_LEVEL_2 = C2.STOR_LEVEL_2 AND P2.STOR_LEVEL_3 = C2.STOR_LEVEL_3 AND P2.STOR_LEVEL_4 = C2.STOR_LEVEL_4 AND (P2.QTY &amp;lt;&amp;gt; 0 OR P2.QTY_RESERVED <> 0))



Steps to replicate the issue:

  1. Create an Express Putaway of an Item and Container in Storage Location A.
  2. Transfer the Item and Container from Storage Location A to Storage Location B.
  3. Create a BCT transaction to adjust the quantity of the Item and Container in Storage Location A.
  4. Run the INPVIADJ process > no error is issued and the quantity of the the Item and Container in Storage Location A is adjusted. There is a now a quantity available for the same Item and Container in 2 different Storage Locations.

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