/* Onhand Quantity Exits Solution From Backend */

--CHECK PRE-APPROVED MOVE ORDERS/OPNE MOVE ORDER----

SELECT MTRL.LINE_ID,

      MTRH.HEADER_ID,

      MTRH.REQUEST_NUMBER,

      MTRL.ORGANIZATION_ID,

      FU.USER_NAME

 FROM MTL_TXN_REQUEST_LINES MTRL, MTL_TXN_REQUEST_HEADERS MTRH, FND_USER FU

 WHERE    MTRL.INVENTORY_ITEM_ID = 101258

      AND MTRL.ORGANIZATION_ID IN (88,91,92,93,94,96)

      AND MTRL.LINE_STATUS = 7                        --- Preapproved status

      AND MTRL.HEADER_ID = MTRH.HEADER_ID

      AND MTRL.CREATED_BY = FU.USER_ID;

-------------------------------------------------------------------------------      

 --THERE ARE DATA ONHAND AND/OR IN MTL_SUPPLY FOR THE PROBLEM ITEM

 --if count > 0, this item is in on hand           

SELECT COUNT(1) FROM MTL_ONHAND_QUANTITIES_DETAIL WHERE INVENTORY_ITEM_ID = 101258

AND ORGANIZATION_ID IN (88,91,92,93,94,96)

SELECT COUNT(1) FROM MTL_SUPPLY WHERE ITEM_ID = 101258

AND TO_ORGANIZATION_ID IN (88,91,92,93,94,96)

--OnHand Check

SELECT *

 FROM MTL_ONHAND_QUANTITIES_DETAIL

 WHERE INVENTORY_ITEM_ID = 101258;

--Interface Check

 SELECT *

 FROM MTL_MATERIAL_TRANSACTIONS_TEMP

WHERE INVENTORY_ITEM_ID = 101258;

--Cehcking Sourcing

SELECT *

 FROM MTL_SUPPLY MS

WHERE ITEM_ID = 101258 ---Closed or cancel the requisition line

--Checking Demand

SELECT *

 FROM MTL_DEMAND

WHERE INVENTORY_ITEM_ID = 101258;  

--Checking open Move Order

SELECT *

 FROM MTL_TXN_REQUEST_LINES

WHERE INVENTORY_ITEM_ID = 101258

  AND LINE_STATUS = 7;

-- Solution

-- For on hand, please do misc issue.

---For mtl_supply, please go to supply/demand screen..find out all the POs or Reqs or Interorg shipments linked, then receive and deliver them.