WMS - Pick Pending Summery Report

/* Formatted on 4/9/2012 3:12:18 PM (QP5 v5.114.809.3010) */
  SELECT   ROW_NUMBER ()
              OVER (
                 ORDER BY
                    oel.shipping_method_code, hp.party_name, oeh.order_number
              )
              AS sr_no,
           mmtt.transaction_temp_id task_no,
           wdt.task_id,
           hp.party_name customer,
           msi.DESCRIPTION,
           flv.meaning task_status,
           fu1.user_name USER_name,
           mmtt.transaction_quantity QUANTITY,
           oeh.order_number,
           mmtt.SUBINVENTORY_CODE,
           mmtt.TRANSACTION_SOURCE_NAME,
           wt.user_task_type_code EQUIPMENT,
           mmtt.LOCATOR_ID,
           oel.shipping_method_code route,
           ot.name order_type
    FROM   hz_parties hp,
           hz_cust_accounts hca,
           oe_order_headers_all oeh,
           mtl_material_transactions_temp mmtt,
           wms_dispatched_tasks wdt,
           oe_order_lines_all oel,
           fnd_lookup_values flv,
           fnd_user fu1,
           mtl_system_items_b msi,
           oe_transaction_types_tl ot,
           wms_tasks_v wt
   WHERE       msi.organization_id = mmtt.organization_id
           AND msi.inventory_item_id = mmtt.inventory_item_id
           AND oel.line_id = mmtt.trx_source_line_id
           AND flv.lookup_type = 'WMS_TASK_STATUS'
           AND flv.lookup_code =
                 NVL (wdt.status, NVL (mmtt.wms_task_status, 1))
           AND flv.language = 'US'
           AND wdt.transaction_temp_id(+) = mmtt.transaction_temp_id
           AND oeh.header_id = oel.header_id
           AND fu1.employee_id(+) = wdt.person_id
           AND oeh.sold_to_org_id = hca.cust_account_id
           AND hca.party_id = hp.party_id
           AND flv.MEANING IN ('Pending', 'Unreleased')
           AND oeh.order_type_id = ot.transaction_type_id
           AND wt.transaction_temp_id = mmtt.transaction_temp_id
           AND hp.party_name = NVL (:P_CUST, hp.party_name)
           AND wt.user_task_type_code =
                 NVL (:P_EQUIPMENT, wt.user_task_type_code)
           AND oeh.order_number = NVL (:P_ORD_NO, oeh.order_number)
           AND ot.name = NVL (:P_ORD_TYP, ot.name)
           AND ot.language = 'US'
           AND NVL (oel.shipping_method_code, -99) =
                 NVL (:P_ROUTE, NVL (oel.shipping_method_code, -99))
           AND mmtt.SUBINVENTORY_CODE =
                 NVL (:P_SUB_INV, mmtt.SUBINVENTORY_CODE)
           AND TRUNC (mmtt.creation_date) BETWEEN TRUNC(NVL (
                                                           :P_FRM_DT,
                                                           mmtt.creation_date
                                                        ))
                                              AND  TRUNC(NVL (
                                                            :P_TO_DT,
                                                            mmtt.creation_date
                                                         ))
           AND mmtt.organization_id =
                 NVL (:P_ORGANIZATION_ID, mmtt.organization_id)
ORDER BY   oel.shipping_method_code, hp.party_name, oeh.order_number