WSH Open Deliveries

SELECT  wnd.organization_id,
                    wnd.delivery_id deliv,
                     wnd.name del_name,
                    wnd.creation_date credate,
                    wsh_util_core.get_location_description(wnd.ultimate_dropoff_location_id,
                      'NEW UI CODE') ship_to,
                    wsh_util_core.get_location_description(wnd.intmed_ship_to_location_id,
                      'NEW UI CODE') int_med_ship_to,
                    wsh_util_core.derive_shipment_priority(wnd.delivery_id) as shippri,
                    wdd.delivery_detail_id,
                    wdd.requested_quantity,
                    decode(wdd.released_status,'Y',nvl(wdd.picked_quantity,wdd.requested_quantity),NULL) picked_qty,
                    wdd.shipped_quantity,
                    wdd.source_header_number,
                    wdd.batch_id,
                    wdd.container_flag,
                    decode(wdd.inventory_item_id, NULL,
                    decode(:P_ITEM_DISPLAY,'D',wdd.item_description,'F',msik.concatenated_segments,
                            msik.concatenated_segments||' '||wdd.item_description),
                decode(:P_ITEM_DISPLAY,'D',msi.description,'F',msik.concatenated_segments,
                            msik.concatenated_segments||' '||msi.description) )  item_description,
                    wdd.inventory_item_id,
                    wpb.name batchname,
                    wpb.creation_date reldate,
                    msi.organization_id,
                    msik.organization_id,
                    hca.account_number customer,
                    mp.organization_code,
                    flv.meaning as shipmeth,
                   wl.meaning as srcys,
                  &F_SORT_SELECT,
                WSH_WSHRDOPN_XMLP_PKG.cf_shipment_priorityformula(wsh_util_core.derive_shipment_priority ( wnd.delivery_id )) CF_shipment_priority,
                WSH_WSHRDOPN_XMLP_PKG.cf_lpnformula(wdd.container_flag) CF_CONTAINER_FLAG
            FROM    wsh_new_deliveries wnd,
                    wsh_delivery_assignments_v wda,
                    wsh_delivery_details wdd,
                    wsh_picking_batches wpb ,
                    mtl_system_items_vl msi,
                    mtl_system_items_kfv msik,
                    wsh_lookups wl,
                   ( SELECT cust_acct.cust_account_id,
                           'C' consignee_flag,
                           cust_acct.account_number account_number
                    FROM   hz_cust_accounts cust_acct
                    UNION
                    SELECT pv.vendor_id cust_account_id,
                           'V' consignee_flag,
                           pv.segment1 account_number
                    FROM   po_vendors pv ) hca,
                    mtl_parameters mp,
                    fnd_lookup_values_vl flv
            WHERE   wnd.status_code IN ('OP', 'SR', 'SC', 'SA')
              AND   wda.delivery_id = wnd.delivery_id
              AND   wda.delivery_id IS NOT NULL
              AND   wda.delivery_detail_id = wdd.delivery_detail_id
              AND   wdd.batch_id = wpb.batch_id(+)
              AND   wdd.organization_id = msi.organization_id(+)
              AND   wdd.inventory_item_id = msi.inventory_item_id(+)
              AND   wdd.organization_id = msik.organization_id(+)
              AND   wdd.inventory_item_id = msik.inventory_item_id(+)
              AND   wdd.source_code = wl.lookup_code
              AND   wl.lookup_type = 'SOURCE_SYSTEM'
              AND   wnd.customer_id = hca.cust_account_id(+)
              AND   nvl(wnd.consignee_flag,'C') = hca.consignee_flag(+) 
              AND   wnd.organization_id = mp.organization_id(+)
              AND   nvl(wnd.shipment_direction, 'O') IN ('O', 'IO')
              AND   wnd.delivery_type = 'STANDARD'
              AND  wdd.container_flag IN ('N', 'Y')
              AND   wdd.organization_id = :p_organization_id
              AND   flv.lookup_type(+) = 'SHIP_METHOD'
              AND   flv.lookup_code(+) = wnd.ship_method_code
              AND   flv.view_application_id(+) = 3
              &F_WHERE
            order by 19 ASC,22 ASC,7 ASC,2 ASC,18 ASC,4 
  ASC,21 ASC,5 ASC,6 ASC,3 ASC,23 ASC,24 ASC,12 ASC ,&F_SORT_ORDER