Inventory Insufficient Quantity Error

INCOMPLETE
 
Process  was added to the Cost Accounting jobset.  This process finds inventory items with an insufficient quantity error.   An email is sent to the Inventory Team.
 
The error occurs when more inventory depletes from a lot than was received.  The quantities get out of balance between     
 
To find the IBU, Item ID and Lot ID error query CM_DEPLETION where MESSAGE_NBR = 106.
To find the correct quantities beginning and current quantities query TRANSACTION_INV and download into Excel.  I sort by Lot ID, then DTTM and calculate the running balance of each lot. 
 
Sample Scripts

UPDATE B
SET B.INV_LOT_ID = '01098_L00000796'
, B.NON_OWN_FLAG = 'Y'
, B.COSTED_FLAG = 'X'
, B.STORAGE_AREA = 'ROW'
, B.STOR_LEVEL_1 = '11'
, B.STOR_LEVEL_2 = 'J1'
, B.STOR_LEVEL_3 = '0'
, B.STOR_LEVEL_4 = '00C'
FROM PS_TRANSACTION_INV B
WHERE B.BUSINESS_UNIT = 'DNJL'
AND B.INV_ITEM_ID = '000000000000468077'
AND EXISTS (SELECT 'X'
FROM PS_CM_DEPLETION A
WHERE A.MESSAGE_SET_NBR = 15560
AND A.MESSAGE_NBR = 106
AND A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.INV_ITEM_ID = B.INV_ITEM_ID
AND A.DT_TIMESTAMP = B.DT_TIMESTAMP
AND A.SEQ_NBR = B.SEQ_NBR)

DELETE B
FROM PS_CM_DEPLETE B
WHERE B.BUSINESS_UNIT = 'DNJL'
AND B.INV_ITEM_ID = '000000000000468077'
AND EXISTS (SELECT 'X'
FROM PS_CM_DEPLETION A
WHERE A.MESSAGE_SET_NBR = 15560
AND A.MESSAGE_NBR = 106
AND A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.INV_ITEM_ID = B.INV_ITEM_ID
AND A.CM_BOOK = B.CM_BOOK
AND A.DT_TIMESTAMP = B.DT_TIMESTAMP
AND A.SEQ_NBR = B.SEQ_NBR)

DELETE PS_CM_DEPLETION
WHERE BUSINESS_UNIT = 'DNJL'
AND INV_ITEM_ID = '000000000000468077'
AND MESSAGE_SET_NBR = 15560
AND MESSAGE_NBR = 106


-- Each of these 2 SQLs will update 1 row

UPDATE PS_PHYSICAL_INV
SET QTY = 759
, QTY_BASE = 759
WHERE BUSINESS_UNIT = 'DNJL'
AND INV_ITEM_ID = '000000000000468077'
AND INV_LOT_ID = '01098_L00000796'

UPDATE PS_LOT_CONTROL_INV
SET QTY_ONHAND = 759
, QTY_AVAILABLE = 591
WHERE BUSINESS_UNIT = 'DNJL'
AND INV_ITEM_ID = '000000000000468077'
AND INV_LOT_ID = '01098_L00000796'

UPDATE PS_BU_ITEMS_INV
SET QTY_OWNED = 0
, QTY_ONHAND = 4026
WHERE BUSINESS_UNIT = 'DNJL'
AND INV_ITEM_ID = '000000000000468077'