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 |