VIEW MATERIAL TRANSACTIONS QUERY LPN

/* Formatted on 7/26/2012 4:28:45 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,
         MMT.TRANSFER_SUBINVENTORY Transfer_Subinventory,
         (SELECT   A.LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers A
           WHERE   A.lpn_id = mmt.TRANSFER_LPN_ID)
            Transfer_Lpn,
         (SELECT   B.CONCATENATED_SEGMENTS
            FROM   mtl_item_locations_kfv B
           WHERE   B.INVENTORY_LOCATION_ID = MMT.TRANSFER_LOCATOR_ID)
            TRANSFER_LOCATOR,
         (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,
         --    ood.ORGANIZATION_CODE,
         MMT.SHIPMENT_NUMBER,
         (SELECT   USER_ID
            FROM   FND_USER
           WHERE   USER_ID = MMT.CREATED_BY)
            USER_ID
  --         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 moqd.SUBINVENTORY_CODE = 'Z'
         --         AND milkfv.INVENTORY_ITEM_ID = mmt.inventory_item_id
         --         AND milkfv.organization_id = mmt.organization_id
         --         AND milkfv.INVENTORY_LOCATION_ID = mmt.LOCATOR_ID
         --         AND mtt.TRANSACTION_TYPE_ID = mmt.TRANSACTION_TYPE_ID
         --         AND mtt.TRANSACTION_SOURCE_TYPE_ID = mmt.TRANSACTION_SOURCE_TYPE_ID
                        AND TRUNC (MMT.creation_date) BETWEEN '01-APR-2012'
                                                      AND  '30-APR-2012'
          --       AND ood.organization_id = msib.organization_id
         --         AND mkfv.INVENTORY_LOCATION_ID = mmt.TRANSFER_LOCATOR_ID
        AND MSIB.SEGMENT1 = '23461KSP910'
--GROUP BY   MSIB.SEGMENT1,
--           MMT.SUBINVENTORY_CODE,
--           MILKFV.CONCATENATED_SEGMENTS,
--           MMT.TRANSFER_SUBINVENTORY,
--           MMT.TRANSACTION_DATE,
--           MMT.TRANSACTION_QUANTITY,
--           MMT.TRANSACTION_UOM,
--           MMT.PRIMARY_QUANTITY,
--           MMT.SHIPMENT_NUMBER,
--           mtt.TRANSACTION_TYPE_NAME
--         AND MMTMM.TRANSACTION_ID = MMT.TRANSACTION_ID
--         AND MMTMM.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
--         AND MMTMM.ORGANIZATION_ID = MMT.ORGANIZATION_ID
---------------------------------------------------------------------------------------------------------------------

/* Formatted on 7/26/2012 4:45:25 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,
         MMT.TRANSFER_SUBINVENTORY Transfer_Subinventory,
         (SELECT   A.LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers A
           WHERE   A.lpn_id = mmt.TRANSFER_LPN_ID)
            Transfer_Lpn,
         (SELECT   B.CONCATENATED_SEGMENTS
            FROM   mtl_item_locations_kfv B
           WHERE   B.INVENTORY_LOCATION_ID = MMT.TRANSFER_LOCATOR_ID)
            TRANSFER_LOCATOR,
         (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,
         --    ood.ORGANIZATION_CODE,
         MMT.SHIPMENT_NUMBER,
         (SELECT   USER_ID
            FROM   FND_USER
           WHERE   USER_ID = MMT.CREATED_BY)
            USER_ID
  --         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'

----------------------------------------------------------------------------------------------------------------------
/* Formatted on 7/26/2012 10:09:01 AM (QP5 v5.114.809.3010) */
SELECT   msib.segment1 item_code,
         MMT.SUBINVENTORY_CODE,
         milkfv.CONCATENATED_SEGMENTS LOCATORS,
         MMT.TRANSFER_SUBINVENTORY,
         MMT.TRANSACTION_DATE,
         MMT.TRANSACTION_QUANTITY,
         MMT.TRANSACTION_UOM,
         MMT.PRIMARY_QUANTITY,
         ood.ORGANIZATION_CODE,
         MMT.SHIPMENT_NUMBER,
         --         WLPN.LICENSE_PLATE_NUMBER LPN,
         mtt.TRANSACTION_TYPE_NAME transaction_type,
         --   mtts.TRANSACTION_TYPE_NAME source_type,
         mkfv.CONCATENATED_SEGMENTS transfer_locator,
         (SELECT   LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers
           WHERE   lpn_id = mmt.lpn_id)
            lpn
  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
 --mtl_transaction_types mtts
 --      WMS_LICENSE_PLATE_NUMBERS WLPN
 --      MTL_MATERIAL_TRANSACTIONS MMTMM
 WHERE       msib.organization_id = 83
         --     AND msib.INVENTORY_ITEM_ID = moqd.INVENTORY_ITEM_ID
         --     AND MSIB.ORGANIZATION_ID = MOQD.ORGANIZATION_ID
         AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
         AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
         -- and moqd.SUBINVENTORY_CODE = 'Z'
         AND milkfv.INVENTORY_ITEM_ID = mmt.inventory_item_id
         AND milkfv.organization_id = mmt.organization_id
         AND milkfv.INVENTORY_LOCATION_ID = mmt.LOCATOR_ID
         AND mtt.TRANSACTION_TYPE_ID = mmt.TRANSACTION_TYPE_ID
         AND mtt.TRANSACTION_SOURCE_TYPE_ID = mmt.TRANSACTION_SOURCE_TYPE_ID
         --         AND mtts.TRANSACTION_TYPE_ID = mmt.TRANSACTION_TYPE_ID
         --         AND mtts.TRANSACTION_SOURCE_TYPE_ID = mmt.TRANSACTION_SOURCE_TYPE_ID
         --         --         AND MSIB.ORGANIZATION_ID = WLPN.ORGANIZATION_ID
         --         AND MSIB.INVENTORY_ITEM_ID = WLPN.INVENTORY_ITEM_ID
         --         AND mmt.LOCATOR_ID(+) = WLPN.LOCATOR_ID
         AND TRUNC (MMT.creation_date) BETWEEN '01-APR-2012'
                                           AND  '30-APR-2012'
         AND ood.organization_id = msib.organization_id
         AND mkfv.INVENTORY_LOCATION_ID = mmt.TRANSFER_LOCATOR_ID
-- AND MSIB.SEGMENT1 = '23461KSP910'
--GROUP BY   MSIB.SEGMENT1,
--           MMT.SUBINVENTORY_CODE,
--           MILKFV.CONCATENATED_SEGMENTS,
--           MMT.TRANSFER_SUBINVENTORY,
--           MMT.TRANSACTION_DATE,
--           MMT.TRANSACTION_QUANTITY,
--           MMT.TRANSACTION_UOM,
--           MMT.PRIMARY_QUANTITY,
--           MMT.SHIPMENT_NUMBER,
--           mtt.TRANSACTION_TYPE_NAME
--         AND MMTMM.TRANSACTION_ID = MMT.TRANSACTION_ID
--         AND MMTMM.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
--         AND MMTMM.ORGANIZATION_ID = MMT.ORGANIZATION_ID