VIEW: APPS.MTL_LOT_ONHAND_SUM_V

Source

View Text - Preformatted

SELECT moq.organization_id organization_id , mp.organization_code organization_code , moq.subinventory_code subinventory_code , moq.locator_id locator_id , mil.concatenated_segments LOCATOR , moq.inventory_item_id inventory_item_id , msiv.description item_description , msiv.concatenated_segments item , moq.revision revision , msiv.primary_uom_code uom , sum(moq.primary_transaction_quantity) on_hand , moq.lpn_id , wlpn.license_plate_number lpn , moq.lot_number lot_number , msi.status_id subinventory_status_id , mil.status_id locator_status_id , mln.status_id lot_status_id , moq.secondary_uom_code secondary_uom_code , sum(moq.secondary_transaction_quantity) secondary_onhand , mln.grade_code FROM mtl_parameters mp , mtl_item_locations_kfv mil , mtl_secondary_inventories msi , mtl_lot_numbers mln , mtl_system_items_vl msiv , mtl_onhand_quantities_detail moq , wms_license_plate_numbers wlpn WHERE moq.organization_id = mp.organization_id AND moq.organization_id = mil.organization_id(+) AND moq.lpn_id = wlpn.lpn_id(+) AND moq.locator_id = mil.inventory_location_id(+) AND moq.organization_id = msi.organization_id AND moq.subinventory_code = msi.secondary_inventory_name AND moq.organization_id = mln.organization_id(+) AND moq.inventory_item_id = mln.inventory_item_id(+) AND moq.lot_number = mln.lot_number(+) AND moq.organization_id = msiv.organization_id AND moq.inventory_item_id = msiv.inventory_item_id GROUP BY moq.organization_id, mp.organization_code, moq.inventory_item_id, moq.revision,moq.lpn_id,msiv.description,msiv.primary_uom_code, moq.secondary_uom_code,mln.grade_code,wlpn.license_plate_number, mil.status_id,mln.status_id,msi.status_id, mil.concatenated_segments, msiv.concatenated_segments, moq.subinventory_code, moq.locator_id, moq.lot_number

View Text - HTML Formatted

SELECT MOQ.ORGANIZATION_ID ORGANIZATION_ID
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, MOQ.SUBINVENTORY_CODE SUBINVENTORY_CODE
, MOQ.LOCATOR_ID LOCATOR_ID
, MIL.CONCATENATED_SEGMENTS LOCATOR
, MOQ.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSIV.DESCRIPTION ITEM_DESCRIPTION
, MSIV.CONCATENATED_SEGMENTS ITEM
, MOQ.REVISION REVISION
, MSIV.PRIMARY_UOM_CODE UOM
, SUM(MOQ.PRIMARY_TRANSACTION_QUANTITY) ON_HAND
, MOQ.LPN_ID
, WLPN.LICENSE_PLATE_NUMBER LPN
, MOQ.LOT_NUMBER LOT_NUMBER
, MSI.STATUS_ID SUBINVENTORY_STATUS_ID
, MIL.STATUS_ID LOCATOR_STATUS_ID
, MLN.STATUS_ID LOT_STATUS_ID
, MOQ.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
, SUM(MOQ.SECONDARY_TRANSACTION_QUANTITY) SECONDARY_ONHAND
, MLN.GRADE_CODE
FROM MTL_PARAMETERS MP
, MTL_ITEM_LOCATIONS_KFV MIL
, MTL_SECONDARY_INVENTORIES MSI
, MTL_LOT_NUMBERS MLN
, MTL_SYSTEM_ITEMS_VL MSIV
, MTL_ONHAND_QUANTITIES_DETAIL MOQ
, WMS_LICENSE_PLATE_NUMBERS WLPN
WHERE MOQ.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MOQ.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MOQ.LPN_ID = WLPN.LPN_ID(+)
AND MOQ.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MOQ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MOQ.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MOQ.ORGANIZATION_ID = MLN.ORGANIZATION_ID(+)
AND MOQ.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID(+)
AND MOQ.LOT_NUMBER = MLN.LOT_NUMBER(+)
AND MOQ.ORGANIZATION_ID = MSIV.ORGANIZATION_ID
AND MOQ.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID GROUP BY MOQ.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, MOQ.INVENTORY_ITEM_ID
, MOQ.REVISION
, MOQ.LPN_ID
, MSIV.DESCRIPTION
, MSIV.PRIMARY_UOM_CODE
, MOQ.SECONDARY_UOM_CODE
, MLN.GRADE_CODE
, WLPN.LICENSE_PLATE_NUMBER
, MIL.STATUS_ID
, MLN.STATUS_ID
, MSI.STATUS_ID
, MIL.CONCATENATED_SEGMENTS
, MSIV.CONCATENATED_SEGMENTS
, MOQ.SUBINVENTORY_CODE
, MOQ.LOCATOR_ID
, MOQ.LOT_NUMBER