WSH Master Bill of Lading

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