VIEW: APPS.WMS_ONHAND_AND_LOADED_QTY_V

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)