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

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