QTY_RESERVED AND QTY_AVAILABLE In BU_ITEMS_INV Table Was Not Updated
(Doc ID 2520216.1)
Last updated on MARCH 01, 2023
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 |