SELECT
wdg.delivery_leg_id delivery_leg_id_dp,
wnd.delivery_id delivery_id_dp,
to_char(wnd.delivery_id) delivery_id_dp_chr,
wnd.name name_dp,
wnd.status_code delivery_status_dp,
wdg.pick_up_stop_id,
wnd.Initial_pickup_location_id initial_pickup_loc_id_dp,
wdi.sequence_number bol_number_dp ,
wnd.waybill waybill_dp,
wnd.freight_terms_code freight_terms_dp,
wnd.loading_sequence loading_sequence_dp,
substrb(cust.party_name,1,50) customer_name_dp,
cust.account_number customer_number_dp,
wnd.ultimate_dropoff_location_id ultimate_dropoff_loc_id_dp,
wl.address1 address1_dp,
wl.address2 address2_dp,
wl.address3 address3_dp,
wl.address4 address4_dp,
wl.city city_dp,
nvl(nvl(wl.province,wl.state),wl.county) region_dp,
wl.postal_code postal_code_dp,
wl.country country_dp,
wnd.gross_weight gross_weight_dp,
wnd.net_weight net_weight_dp,
wnd.weight_uom_code weight_uom_dp,
wnd.volume volume_dp,
wnd.volume_uom_code volume_uom_dp,
wnd.number_of_lpn pieces_dp,
wnd.cod_amount,
wnd.freight_terms_code,
wnd.fob_code,
wdg.bill_freight_to,
wnd.attribute1 wnd_attribute1,
wnd.attribute2 wnd_attribute2,
wnd.attribute3 wnd_attribute3,
wnd.attribute4 wnd_attribute4,
wnd.attribute5 wnd_attribute5,
wnd.attribute6 wnd_attribute6,
wnd.attribute7 wnd_attribute7,
wnd.attribute8 wnd_attribute8,
wnd.attribute9 wnd_attribute9,
wnd.attribute10 wnd_attribute10,
wnd.attribute11 wnd_attribute11,
wnd.attribute12 wnd_attribute12,
wnd.attribute13 wnd_attribute13,
wnd.attribute14 wnd_attribute14,
wnd.attribute15 wnd_attribute15,
WSH_WSHRDMBL_XMLP_PKG.cf_parent_del_name_dpformula(wdg.delivery_leg_id) CF_PARENT_DEL_NAME_DP,
WSH_WSHRDMBL_XMLP_PKG.cf_freight_terms_dpformula(wnd.freight_terms_code) CF_FREIGHT_TERMS_DP,
WSH_WSHRDMBL_XMLP_PKG.cf_delivery_status_dpformula(wnd.status_code) CF_Delivery_status_dp,
WSH_WSHRDMBL_XMLP_PKG.cf_address_deliveries_pickedup(wl.address1, wl.address2, wl.address3, wl.address4) CF_address_deliveries_pickedup,
WSH_WSHRDMBL_XMLP_PKG.cf_weight_uom_dpformula(wnd.weight_uom_code) CF_Weight_uom_dp,
WSH_WSHRDMBL_XMLP_PKG.cf_volume_uom_dpformula(wnd.volume_uom_code) CF_Volume_uom_dp
FROM
wsh_delivery_legs wdg,
wsh_new_deliveries wnd,
wsh_locations wl,
wsh_document_instances wdi,
(SELECT party.party_name,
cust_acct.cust_account_id,
'C' consignee_flag,
cust_acct.account_number account_number
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,
pv.segment1 account_number
FROM hz_parties party,
po_vendors pv
WHERE pv.party_id = party.party_id) cust
WHERE
wnd.delivery_id = wdg.delivery_id
AND wl.wsh_location_id = wnd.ultimate_dropoff_location_id
AND wnd.customer_id = cust.cust_account_id(+)
AND nvl(wnd.consignee_flag,'C') = cust.consignee_flag(+)
AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
AND wdi.entity_id = nvl(wdg.parent_delivery_leg_id,wdg.delivery_leg_id)
AND wnd.delivery_type ='STANDARD'
and wdg.pick_up_stop_id=:stop_id
order by wnd.name |