My Oracle Support Banner

QTY_RESERVED AND QTY_AVAILABLE In BU_ITEMS_INV Table Was Not Updated (Doc ID 2520216.1)

Last updated on JANUARY 08, 2020

Applies to:

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

Symptoms

IN_FUL_BCT creating inventory discrepancy. When the order is short shipped using the INVENTORY_SHIPPING message the IN_FUL_BCT is creating a discrepancy in the QTY between QTY_BASE in PHYSICAL_INV and BU_ITEMS_INV.

The short quantity is not unreserved in the BU_ITEMS_INV.

Requested qty - 10
Qty Picked - 10
Qty Shipped - 8

The BU configuration is set to cancel the backorder.

A new demand line is created for IN_FULFIL_STATE - 20 and cancelled. The PHYSICAL_INV table is updated with the short quantity of 2 but the BU_ITEMS_INV table is not updated the reserved quantity is still 10 and QTY_AVAILABLE is not updated.

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

1.Inventory> FulFil Stock Orders > Fulfilment rules > Setup Fulfilment > Search for BU Enable Cancel Backorder

Inventory> FulFil Stock Orders > Fulfilment rules > Setup Fulfilment >Reservation (tab) > make sure both soft reserve and reserve online in unchecked

2.Created an Item ID- THV1 for testing purpose

3.Add the item to BU - US013

4.Created express putway for Item ID - THV1

Qty -225035

Total Unit cost -10

4.Run the query - 

SELECT A.INV_ITEM_ID,

A.STORAGE_AREA,

A.QTY_BASE,

A.QTY_RESERVED_BASE,

B.QTY_ONHAND,

B.QTY_RESERVED,

B.QTY_AVAILABLE,

( SELECT NVL(

SUM(DECODE(

QTY_SHIP_BASE,

0,

QTY_PICK_BASE,

QTY_SHIP_BASE

) ),

0

)

FROM PS_IN_DEMAND

WHERE INV_ITEM_ID = B.INV_ITEM_ID

AND BUSINESS_UNIT = B.BUSINESS_UNIT

AND IN_FULFILL_STATE < 70

AND IN_FULFILL_STATE > 40

) "QTY Picked/Shipped (Reserved)"

FROM PS_PHYSICAL_INV A,

PS_BU_ITEMS_INV B

WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT

AND A.INV_ITEM_ID = B.INV_ITEM_ID

AND A.BUSINESS_UNIT = 'US013'

AND A.INV_ITEM_ID ='THV1'

ORDER BY 1;

Results-

QTY_BASE =225035

QTY_ONHAND = 225035

QTY_RESERVED = 0

QTY_AVAILABLE = 225035

QTY_PICKEDSHIPPED = 0

5.Create the Order for the Item: THV1 with Quantity 10.

7.Run the Order Release request Process (IN_FUL_RLS) to release the Order:

MSR0000001

After releasing the Order, The quantities are as below:

QTY_BASE =225035

QTY_ONHAND = 225035

QTY_RESERVED = 0

QTY_AVAILABLE = 225035

QTY_PICKEDSHIPPED = 0

8.Create Material Picking Feedback

9. Run the Material Pick Confirmation process (IN_FUL_CONF) for the Order

with Quantity: 10

10.After process gets success, the quantities are as below:

QTY_BASE =225025

QTY_ONHAND = 225035

QTY_RESERVED = 10

QTY_AVAILABLE = 225025

QTY_PICKEDSHIPPED = 10

11. Shipping the Order using Handle Tester with quantity: 5

Execute event

12.Run IN_FUL_BCT process

13.SCM intergrations > Transaction Error Handling > Maintain Transactions >

request to ship > Check the Status to be complete

Now the Quantities are as below:

QTY_BASE =225030

QTY_ONHAND = 225035

QTY_RESERVED = 10

QTY_AVAILABLE = 225025

QTY_PICKEDSHIPPED = 5

Here the shipped Qty 5 is updated at Physical Inventory but not at BU Items.

12.Run Deplete on Hand Quantity process

Run the query -

QTY_BASE =225030

QTY_ONHAND = 225030

QTY_RESERVED = 5

QTY_AVAILABLE = 225025

QTY_PICKEDSHIPPED = 0

The quantity reserved and the QTY_AVAILABLE in BU_ITEMS_INV has not been updated.

 

 

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


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