[Home] [Help]
View: WSM_SOURCE_LOTS_V
View Text
SELECT MLN.LOT_NUMBER
, ML.INVENTORY_ITEM_ID
, ML.ORGANIZATION_ID
, ML.SUBINVENTORY_CODE
, ML.LOCATOR_ID
, ML.REVISION
, SUM ( ML.TRANSACTION_QUANTITY ) QUANTITY
, MSI.CONCATENATED_SEGMENTS ITEM
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MTL_SECONDARY_INVENTORIES MSUBINV
, MTL_ONHAND_QUANTITIES_DETAIL ML
, MTL_LOT_NUMBERS MLN
WHERE ML.SUBINVENTORY_CODE = MSUBINV.SECONDARY_INVENTORY_NAME
AND MLN.LOT_NUMBER = ML.LOT_NUMBER
AND MLN.ORGANIZATION_ID = ML.ORGANIZATION_ID
AND MLN.INVENTORY_ITEM_ID = ML.INVENTORY_ITEM_ID
AND NVL ( MLN.DISABLE_FLAG
, 2 ) != 1
AND ML.ORGANIZATION_ID = MSUBINV.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = ML.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = ML.ORGANIZATION_ID
AND MSUBINV.SECONDARY_INVENTORY_NAME IN ( SELECT SECONDARY_INVENTORY_NAME
FROM WSM_SUBINVENTORY_EXTENSIONS
WHERE ORGANIZATION_ID= ML.ORGANIZATION_ID )
AND NVL ( MSUBINV.DISABLE_DATE
, SYSDATE + 1 ) > TRUNC ( SYSDATE ) GROUP BY MLN.LOT_NUMBER
, ML.INVENTORY_ITEM_ID
, ML.ORGANIZATION_ID
, ML.SUBINVENTORY_CODE
, ML.LOCATOR_ID
, ML.REVISION
, MSI.CONCATENATED_SEGMENTS HAVING SUM( ML.TRANSACTION_QUANTITY ) > 0
Columns
Name |
LOT_NUMBER |
INVENTORY_ITEM_ID |
ORGANIZATION_ID |
SUBINVENTORY_CODE |
LOCATOR_ID |
REVISION |
QUANTITY |
ITEM |