WMS - LPN In Pre Stagging Report

/* Formatted on 4/9/2012 2:44:15 PM (QP5 v5.114.809.3010) */
  SELECT   ROW_NUMBER () OVER (ORDER BY wdd.ship_method_code) AS sr_no,
           COUNT (wlc.quantity) no_of_lines,
           SUM (wlc.quantity) transaction_quantity,
           mil.concatenated_segments concatenated_segments,
           wlpn.license_plate_number,
           wsc.parent_lpn,
           wlpn.lpn_id,
           wdd.ship_method_code,
           wdd.source_line_id,
           wdd.subinventory
    FROM   wms_license_plate_numbers wlpn,
           wms_lpn_contents wlc,
           mtl_item_locations_kfv mil,
           wsh_deliverables_v wdd,
           wsh_dsno_containers_v wsc
   WHERE       1 = 1
           AND wlpn.lpn_id = wlc.parent_lpn_id
           AND wdd.container_instance_id = wsc.container_instance_id
           AND wdd.lpn_id = wlpn.lpn_id
           AND mil.inventory_location_id = wdd.locator_id
           AND wdd.ship_method_code = NVL (:p_route, wdd.ship_method_code)
           AND mil.concatenated_segments =
                 NVL (:p_loc_segs, mil.concatenated_segments)
           AND wdd.organization_id =
                 NVL (:p_organization_id, wdd.organization_id)           --122
           AND mil.subinventory_code = 'STGN'
           AND UPPER (wdd.released_status_name) = 'PICKED'
           AND NOT EXISTS (SELECT   *
                             FROM   wsh_dsno_containers_v
                            WHERE   wlpn.license_plate_number = parent_lpn)
GROUP BY   mil.concatenated_segments,
           wlpn.license_plate_number,
           wsc.parent_lpn,
           wlpn.lpn_id,
           wdd.ship_method_code,
           wdd.source_line_id,
           wdd.subinventory;