API INV_MATERIAL_STATUS_PUB.UPDATE_STATUS Does Not Insert History Records Correctly (Doc ID 2301570.1)

Last updated on JANUARY 03, 2023

Applies to:

Symptoms

When calling INV_MATERIAL_STATUS_PUB.update_status to update the material status of on-hand, the history records are not being inserted correctly.
In this case, only the inventory item id and lot number are being passed (subinventory and locator_id are not passed).
Incorrect history records are inserted when there multiple MTL_ONHAND_QUANTITY_DETAILS records for the same item/lot.

Steps

1. Besides organization id and inventory item id, the following values are passed to INV_MATERIAL_STATUS_PUB.update_status:
   
   l_status_rec.lot_number := 'C00001';
   l_status_rec.zone_code := NULL; -- (subinventory)
   l_status_rec.locator_id := NULL;
   l_status_rec.status_id := 62;
   
   
2. Results:
   
   MOQD records before API Call:
   
     SUBINVENTORY LOCATOR_ID LOT_NUMBER  STATUS_ID
     STOCK        3          C00001      65
     RCV_AREA     49         C00001      61
   

  MOQD records after API call (MOQD is updated correctly):

    SUBINVENTORY LOCATOR_ID LOT_NUMBER STATUS_ID
    STOCK        3          C00001     62
    RCV_AREA     49         C00001     62

  Status history records after API call (ZONE_CODE is incorrect for second record)

         ZONE_CODE LOCATOR_ID LOT_NUMBER STATUS_ID
         STOCK     3          C00001     62
         STOCK     3          C00001     62    <-- STOCK should be RCV_AREA

Cause

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