Records Stuck In Table Mtl_Transactions_Interface , Negative Balances Not Allowed

Records Stuck In Table Mtl_Transactions_Interface , Negative Balances Not Allowed

Run the following script, which will find items that do not have sufficient quantity in the STAGING subinventory.

Note: if you have given some name other than 'STAGE' to your staging subinventory, you will need to modify the lines in bold type

SELECT wdd.organization_id, wdd.inventory_item_id, msi.segment1,
SUM (wdd.requested_quantity) requested_qty,
SUM (NVL (wdd.cancelled_quantity, 0)) cancelled_qty,
SUM (mtrl.quantity) move_order_qty, MAX (oq.onhand_qty) onhand_qty,
SUM (wdd.requested_quantity) - MAX (oq.onhand_qty) change_qty
FROM wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_system_items msi,
(SELECT NVL (SUM (moq.transaction_quantity), 0) onhand_qty,
moq.organization_id, moq.inventory_item_id,
moq.subinventory_code
FROM apps.mtl_onhand_quantities moq
WHERE moq.containerized_flag = 2
AND moq.subinventory_code = 'STAGE'
GROUP BY moq.organization_id,
moq.inventory_item_id,
moq.subinventory_code) oq
WHERE wdd.released_status IN ('C', 'Y')
AND NVL (wdd.inv_interfaced_flag, 'N') IN ('N', 'P')
AND wdd.move_order_line_id = mtrl.line_id
AND wdd.inventory_item_id = oq.inventory_item_id
AND wdd.organization_id = oq.organization_id
AND msi.organization_id = wdd.organization_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND oq.subinventory_code = 'STAGE'
GROUP BY wdd.organization_id, wdd.inventory_item_id, msi.segment1
HAVING SUM (wdd.requested_quantity) > MAX (oq.onhand_qty)
UNION ALL
SELECT wdd.organization_id, wdd.inventory_item_id, msi.segment1,
SUM (wdd.requested_quantity) requested_qty,
SUM (NVL (wdd.cancelled_quantity, 0)) cancelled_qty,
SUM (mtrl.quantity) move_order_qty, 0,
SUM (wdd.requested_quantity) change_qty
FROM wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_system_items msi
WHERE wdd.released_status IN ('Y', 'C')
AND NVL (wdd.inv_interfaced_flag, 'N') IN ('N', 'P')
AND wdd.move_order_line_id = mtrl.line_id
AND msi.organization_id = wdd.organization_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND NOT EXISTS (
SELECT 1
FROM mtl_onhand_quantities oq
WHERE oq.inventory_item_id = wdd.inventory_item_id
AND oq.organization_id = wdd.organization_id
AND oq.subinventory_code = 'STAGE')
GROUP BY wdd.organization_id, wdd.inventory_item_id, msi.segment1
ORDER BY 1, 2


2. Perform subinventory transfers as needed to transfer the quantity identified by the column "change_qty" into the staging subinventory