WSH Master Bill of Lading

Description
Categories: BI Publisher
Columns: Delivery Leg Id Dp, Delivery Id Dp, Delivery Id Dp Chr, Name Dp, Delivery Status Dp, Pick Up Stop Id, Initial Pickup Loc Id Dp, Bol Number Dp, Waybill Dp, Freight Terms Dp ...
Application: Shipping Execution
Source: Master Bill of Lading (XML)
Short Name: WSHRDMBL_XML
DB package: WSH_WSHRDMBL_XMLP_PKG
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
Parameter NameSQL textValidation
Trip
 
LOV Oracle
Print BOLs
 
LOV Oracle