VIEW MATERIAL QUERY

/* Formatted on 7/27/2012 2:40:04 PM (QP5 v5.114.809.3010) */
SELECT   msib.segment1 item,
         MMT.SUBINVENTORY_CODE Subinventory,
         -- milkfv.CONCATENATED_SEGMENTS LOCATORS,
         (SELECT   D.CONCATENATED_SEGMENTS
            FROM   mtl_item_locations_kfv D
           WHERE   D.INVENTORY_LOCATION_ID = MMT.LOCATOR_ID)
            LOCATOR,
         (SELECT   C.LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers C
           WHERE   C.lpn_id = mmt.lpn_id)
            lpn,
         (SELECT   location_code
            FROM   hr_locations_v
           WHERE   location_id = MMT.LOCATOR_ID)
            location,
         MMT.TRANSFER_SUBINVENTORY Transfer_Subinventory,
         (SELECT   B.CONCATENATED_SEGMENTS
            FROM   mtl_item_locations_kfv B
           WHERE   B.INVENTORY_LOCATION_ID = MMT.TRANSFER_LOCATOR_ID)
            TRANSFER_LOCATOR,
         (SELECT   A.LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers A
           WHERE   A.lpn_id = mmt.TRANSFER_LPN_ID)
            Transfer_Lpn,
         (SELECT   S.LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers S
           WHERE   S.lpn_id = mmt.CONTENT_LPN_ID)
            CONTENT_Lpn,
         (SELECT   ORGANIZATION_CODE
            FROM   ORG_ORGANIZATION_DEFINITIONS
           WHERE   ORGANIZATION_ID = MSIB.ORGANIZATION_ID)
            Transfer_Org,
         MMT.TRANSACTION_DATE,
         MMT.TRANSACTION_QUANTITY,
         MMT.TRANSACTION_UOM,
         MMT.PRIMARY_QUANTITY,
         (SELECT   TRANSACTION_SOURCE_TYPE_NAME
            FROM   MTL_TXN_SOURCE_TYPES
           WHERE   TRANSACTION_SOURCE_TYPE_ID =
                      MMT.TRANSACTION_SOURCE_TYPE_ID)
            SOURCE_TYPE,
         --    ood.ORGANIZATION_CODE,
         MMT.SHIPMENT_NUMBER,
         (SELECT   USER_NAME
            FROM   FND_USER
           WHERE   USER_ID = MMT.CREATED_BY)
            USER_NAME
  --         mtt.TRANSACTION_TYPE_NAME transaction_type,
  --         mkfv.CONCATENATED_SEGMENTS transfer_locator
  FROM   MTL_SYSTEM_ITEMS_B msib, MTL_MATERIAL_TRANSACTIONS MMT
 --         mtl_item_locations_kfv milkfv,
 --         mtl_transaction_types mtt,
 --         mtl_item_locations_kfv mkfv,
 --         org_organization_definitions ood
 WHERE       msib.organization_id = 83
         AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
         AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
         --         AND TRUNC (MMT.creation_date) BETWEEN '01-APR-2012'
         --                                           AND  '30-APR-2012'
         AND MSIB.SEGMENT1 = '23461KSP910'