SELECT
distinct wnd.delivery_id drop_off_delivery_id ,
null drop_off_item_number ,
msi.description drop_off_item_description ,
wdd.load_seq_number drop_off_item_load_seq ,
wnd.gross_weight drop_off_del_gr_wt ,
wnd.net_weight drop_off_del_net_wt ,
wnd.weight_uom_code drop_off_del_weight_uom ,
wnd.name drop_off_delivery_name ,
wnd.waybill drop_off_delivery_waybill ,
wnd.loading_sequence drop_off_del_loading_seq ,
substrb ( cust.party_name, 1, 50 ) drop_off_customer_name ,
wl.meaning drop_off_del_status ,
wdd.customer_prod_seq drop_off_cust_prod_seq ,
wdd.source_code drop_source_code ,
wdd.source_line_id drop_source_line_id ,
wdd.item_description det_drop_item_description,
wdd.inventory_item_id drop_inventory_item_id,
WSH_WSHRDVLD_XMLP_PKG.cf_drop_item_descformula(wdd.inventory_item_id, msi.description, wdd.item_description) CF_Drop_Item_Desc,
WSH_WSHRDVLD_XMLP_PKG.cf_drop_off_cust_jobformula(wdd.source_code, wdd.source_line_id) CF_drop_off_cust_job,
fnd_flex_xml_publisher_apis.process_kff_combination_1('drop_off_item_num_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') DROP_OFF_ITEM_NUM_DISP
FROM
wsh_delivery_details wdd ,
wsh_delivery_assignments_v wda ,
mtl_system_items_vl msi ,
wsh_new_deliveries wnd ,
(SELECT party.party_name,
cust_acct.cust_account_id,
'C' consignee_flag
FROM hz_parties party,
hz_cust_accounts cust_acct
WHERE cust_acct.party_id = party.party_id
UNION
SELECT party.party_name,
pv.vendor_id cust_account_id,
'V' consignee_flag
FROM hz_parties party,
po_vendors pv
WHERE pv.party_id = party.party_id) cust,
wsh_lookups wl
WHERE
wdd.delivery_detail_id = wda.delivery_detail_id AND
msi.inventory_item_id (+) = wdd.inventory_item_id AND
wdd.organization_id = msi.organization_id AND
wda.delivery_id = wnd.delivery_id AND
wda.delivery_id is not null AND
cust.cust_account_id (+) = wnd.customer_id AND
cust.consignee_flag (+) = nvl(wnd.consignee_flag,'C') AND
wl.lookup_type = 'DELIVERY_STATUS' AND
wnd.status_code = wl.lookup_code AND
wnd.delivery_type = 'STANDARD' AND
wdd.container_flag IN ('N', 'Y')
and wnd.delivery_id = :DROPOFFS_DELIVERY_ID
ORDER BY DROP_OFF_DEL_GR_WT, DROP_OFF_DEL_LOADING_SEQ, drop_off_delivery_id, drop_off_delivery_waybill,
drop_off_delivery_name, DROP_OFF_DEL_NET_WT, drop_off_del_weight_uom, DROP_OFF_DEL_STATUS,
DROP_OFF_CUSTOMER_NAME, DET_DROP_ITEM_DESCRIPTION, drop_inventory_item_id,
drop_off_del_loading_seq, drop_off_item_load_seq |