View: WMS_ONHAND_AND_LOADED_QTY_V

Product:	WMS - Warehouse Management
Description:	This view is an union between the onhand quantities table and all material that has been loaded on to an equipment (everything that is in transit). All the transit records will have a negative quantity, so performing a sum on this view will
Implementation/DBA Data:	APPS.WMS_ONHAND_AND_LOADED_QTY_V

View Text

SELECT M.ORGANIZATION_ID
, M.INVENTORY_ITEM_ID
, M.REVISION
, M.LOT_NUMBER
, M.COST_GROUP_ID
, CONTAINERIZED_FLAG
, M.SUBINVENTORY_CODE
, LOCATOR_ID
, M.PRIMARY_TRANSACTION_QUANTITY QUANTITY
, M.SECONDARY_TRANSACTION_QUANTITY SECONDARY_TRANSACTION_QUANTITY
, 'ONHAND' QTYTYPE
FROM MTL_ONHAND_QUANTITIES_DETAIL M UNION ALL SELECT T.ORGANIZATION_ID
, T.INVENTORY_ITEM_ID
, T.REVISION
, L.LOT_NUMBER
, T.COST_GROUP_ID
, DECODE(NVL(T.LPN_ID
, NVL(T.CONTENT_LPN_ID
, -1))
, -1
, 2
, 1) CONTAINERIZED_FLAG
, T.SUBINVENTORY_CODE
, T.LOCATOR_ID
, -(NVL(L.PRIMARY_QUANTITY
, T.PRIMARY_QUANTITY)) QUANTITY
, -(NVL(L.SECONDARY_QUANTITY
, T.SECONDARY_TRANSACTION_QUANTITY)) SECONDARY_TRANSACTION_QUANTITY
, 'LOADED' QTYTYPE
FROM WMS_DISPATCHED_TASKS W
, MTL_MATERIAL_TRANSACTIONS_TEMP T
, MTL_TRANSACTION_LOTS_TEMP L
WHERE W.STATUS=4
AND W.TASK_TYPE<>2
AND W.TRANSACTION_TEMP_ID =T.TRANSACTION_TEMP_ID
AND T.TRANSACTION_TEMP_ID=L.TRANSACTION_TEMP_ID (+)

Columns