VIEW: APPS.WSM_SOURCE_LOTS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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