WSH Bill of Lading

Description
Categories: BI Publisher, Logistics
Application: Shipping Execution
Source: Bill of Lading (XML)
Short Name: WSHRDBOL_XML
DB package: WSH_WSHRDBOL_XMLP_PKG
select
              decode(:delivery_type,'STANDARD',wda.delivery_id, wda.parent_delivery_id) delivery_id2,
              --decode(:delivery_type,'STANDARD',wda.delivery_id, wda.parent_delivery_id) delivery_id,
              wdd.item_description unpacked_item_description,
              wdd.shipping_instructions unpacked_shipping_instr,
              wdd.hazard_class_id unpacked_item_hazard_class_id,
              nvl(sum(NVL (wdd.shipped_quantity, 0)),0) unpacked_item_shipped_quantity,
              wdd.requested_quantity_uom unpacked_item_quantity_uom,
              nvl(sum(NVL (wdd.shipped_quantity2, 0)),0) unpacked_item_shipped_qty2,
              wdd.requested_quantity_uom2 unpacked_item_quantity_uom2,
              nvl(sum(wdd.net_weight),0) unpacked_item_net_weight,
              wdd.weight_uom_code unpacked_item_weight_uom,
              nvl(sum(wdd.volume),0) unpacked_item_volume,
              wdd.volume_uom_code unpacked_item_volume_uom,
              wdd.classification unpacked_item_classification,
              wdd.cust_po_number   customer_po_number,
              wdd.seal_code seal_code,
              wdd.organization_id    unpacked_item_organization_id,
              wdd.inventory_item_id  unpacked_item_inv_item_id,
              wdd.inventory_item_id ||wdd.shipping_instructions || wdd.hazard_class_id||wdd.requested_quantity_uom||wdd.requested_quantity_uom2||wdd.weight_uom_code||wdd.volume_uom_code||wdd.classification group_exp,
                WSH_WSHRDBOL_XMLP_PKG.cf_commodity_classformula(wdd.inventory_item_id, wdd.organization_id) CF_COMMODITY_CLASS,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_descformula(wdd.item_description, nvl ( sum ( NVL ( wdd.shipped_quantity , 0 ) ) , 0 ), wdd.requested_quantity_uom, wdd.organization_id, wdd.inventory_item_id, wdd.requested_quantity_uom2, nvl ( sum ( NVL ( wdd.shipped_quantity2 , 0 ) ) , 0 )) CF_UNPACKED_ITEM_DESC,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_wtformula(nvl ( sum ( wdd.net_weight ) , 0 ), wdd.weight_uom_code) CF_UNPACKED_ITEM_WT,
                WSH_WSHRDBOL_XMLP_PKG.cf_src_hdr_idformula() CF_SRC_HDR_ID,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_volformula(nvl ( sum ( wdd.volume ) , 0 ), wdd.volume_uom_code) CF_UNPACKED_ITEM_VOL,
                WSH_WSHRDBOL_XMLP_PKG.CP_TOP_MODEL_LINE_ID_p CP_TOP_MODEL_LINE_ID,
                WSH_WSHRDBOL_XMLP_PKG.CP_SRC_LINE_ID_p CP_SRC_LINE_ID
            from
                wsh_delivery_assignments wda,
                wsh_delivery_details wdd
            where  wda.parent_delivery_detail_id is null
            and    wda.delivery_detail_id is not null
            and    wda.delivery_id is not null
            and    wda.delivery_detail_id = wdd.delivery_detail_id
            and    wdd.container_flag = 'N'
            and    wdd.released_status !='B'
            and  ( (:delivery_type ='STANDARD' and wda.parent_delivery_id is null) or
                   (:delivery_type ='CONSOLIDATION' and wda.parent_delivery_id is not null))
            AND EXISTS
                (select 'EXISTS'
                   from  bom_inventory_components bic
                  where bic.component_item_id = wdd.inventory_item_id
                    and   bic.include_on_ship_docs = 1
					 --added for bug 6029900 
        and bic.component_sequence_id = ( select component_sequence_id
                                                              from   oe_order_lines_all
                                                              where line_id = wdd.source_line_id)
                    and   wdd.top_model_line_id is not null
                 UNION
            select 'EXISTS'/*,
                WSH_WSHRDBOL_XMLP_PKG.cf_commodity_classformula(wdd.inventory_item_id, wdd.organization_id) CF_COMMODITY_CLASS,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_descformula(wdd.item_description, nvl ( sum ( NVL ( wdd.shipped_quantity , 0 ) ) , 0 ), wdd.requested_quantity_uom, wdd.organization_id, wdd.inventory_item_id, wdd.requested_quantity_uom2, nvl ( sum ( NVL ( wdd.shipped_quantity2 , 0 ) ) , 0 )) CF_UNPACKED_ITEM_DESC,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_wtformula(nvl ( sum ( wdd.net_weight ) , 0 ), wdd.weight_uom_code) CF_UNPACKED_ITEM_WT,
                WSH_WSHRDBOL_XMLP_PKG.cf_src_hdr_idformula() CF_SRC_HDR_ID,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_volformula(nvl ( sum ( wdd.volume ) , 0 ), wdd.volume_uom_code) CF_UNPACKED_ITEM_VOL,
                WSH_WSHRDBOL_XMLP_PKG.CP_TOP_MODEL_LINE_ID_p CP_TOP_MODEL_LINE_ID,
                WSH_WSHRDBOL_XMLP_PKG.CP_SRC_LINE_ID_p CP_SRC_LINE_ID*/
            from dual
                  where wdd.top_model_line_id is null
                 UNION
            select 'EXISTS'/*,
                WSH_WSHRDBOL_XMLP_PKG.cf_commodity_classformula(wdd.inventory_item_id, wdd.organization_id) CF_COMMODITY_CLASS,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_descformula(wdd.item_description, nvl ( sum ( NVL ( wdd.shipped_quantity , 0 ) ) , 0 ), wdd.requested_quantity_uom, wdd.organization_id, wdd.inventory_item_id, wdd.requested_quantity_uom2, nvl ( sum ( NVL ( wdd.shipped_quantity2 , 0 ) ) , 0 )) CF_UNPACKED_ITEM_DESC,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_wtformula(nvl ( sum ( wdd.net_weight ) , 0 ), wdd.weight_uom_code) CF_UNPACKED_ITEM_WT,
                WSH_WSHRDBOL_XMLP_PKG.cf_src_hdr_idformula() CF_SRC_HDR_ID,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_volformula(nvl ( sum ( wdd.volume ) , 0 ), wdd.volume_uom_code) CF_UNPACKED_ITEM_VOL,
                WSH_WSHRDBOL_XMLP_PKG.CP_TOP_MODEL_LINE_ID_p CP_TOP_MODEL_LINE_ID,
                WSH_WSHRDBOL_XMLP_PKG.CP_SRC_LINE_ID_p CP_SRC_LINE_ID*/
            from mtl_system_items msi
                  where wdd.top_model_line_id is not null
                    and msi.inventory_item_id = wdd.inventory_item_id
                    and msi.auto_created_config_flag = 'Y'
                  UNION
            select 'EXISTS'/*,
                WSH_WSHRDBOL_XMLP_PKG.cf_commodity_classformula(wdd.inventory_item_id, wdd.organization_id) CF_COMMODITY_CLASS,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_descformula(wdd.item_description, nvl ( sum ( NVL ( wdd.shipped_quantity , 0 ) ) , 0 ), wdd.requested_quantity_uom, wdd.organization_id, wdd.inventory_item_id, wdd.requested_quantity_uom2, nvl ( sum ( NVL ( wdd.shipped_quantity2 , 0 ) ) , 0 )) CF_UNPACKED_ITEM_DESC,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_wtformula(nvl ( sum ( wdd.net_weight ) , 0 ), wdd.weight_uom_code) CF_UNPACKED_ITEM_WT,
                WSH_WSHRDBOL_XMLP_PKG.cf_src_hdr_idformula() CF_SRC_HDR_ID,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_volformula(nvl ( sum ( wdd.volume ) , 0 ), wdd.volume_uom_code) CF_UNPACKED_ITEM_VOL,
                WSH_WSHRDBOL_XMLP_PKG.CP_TOP_MODEL_LINE_ID_p CP_TOP_MODEL_LINE_ID,
                WSH_WSHRDBOL_XMLP_PKG.CP_SRC_LINE_ID_p CP_SRC_LINE_ID*/
            from   dual
                where  wdd.top_model_line_id is not null
                and wdd.ato_line_id is not null
                 UNION
            select 'EXISTS'/*,
                WSH_WSHRDBOL_XMLP_PKG.cf_commodity_classformula(wdd.inventory_item_id, wdd.organization_id) CF_COMMODITY_CLASS,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_descformula(wdd.item_description, nvl ( sum ( NVL ( wdd.shipped_quantity , 0 ) ) , 0 ), wdd.requested_quantity_uom, wdd.organization_id, wdd.inventory_item_id, wdd.requested_quantity_uom2, nvl ( sum ( NVL ( wdd.shipped_quantity2 , 0 ) ) , 0 )) CF_UNPACKED_ITEM_DESC,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_wtformula(nvl ( sum ( wdd.net_weight ) , 0 ), wdd.weight_uom_code) CF_UNPACKED_ITEM_WT,
                WSH_WSHRDBOL_XMLP_PKG.cf_src_hdr_idformula() CF_SRC_HDR_ID,
                WSH_WSHRDBOL_XMLP_PKG.cf_unpacked_item_volformula(nvl ( sum ( wdd.volume ) , 0 ), wdd.volume_uom_code) CF_UNPACKED_ITEM_VOL,
                WSH_WSHRDBOL_XMLP_PKG.CP_TOP_MODEL_LINE_ID_p CP_TOP_MODEL_LINE_ID,
                WSH_WSHRDBOL_XMLP_PKG.CP_SRC_LINE_ID_p CP_SRC_LINE_ID*/
            from dual
                   where wdd.top_model_line_id = wdd.source_line_id)
             and decode ( :delivery_type , 'STANDARD' , wda.delivery_id , wda.parent_delivery_id )=:DELIVERY_ID3
            group by
            decode(:delivery_type,'STANDARD',wda.delivery_id, wda.parent_delivery_id),
              wdd.item_description,
              wdd.shipping_instructions,
              wdd.hazard_class_id,
              wdd.requested_quantity_uom,
              wdd.requested_quantity_uom2,
              wdd.weight_uom_code,
              wdd.volume_uom_code,
              wdd.classification,
              wdd.cust_po_number,
              wdd.seal_code,
              wdd.organization_id,
              wdd.inventory_item_id
              --;
Parameter Name SQL text Validation
Item Flex Code
 
Item Display
 
LOV Oracle
Bill of Lading Number
 
LOV Oracle
Trip Name
 
LOV Oracle
Delivery Name
 
LOV Oracle
Freight Carrier
 
LOV Oracle
Delivery Date (High)
 
Date
Delivery Date (Low)
 
Date
Warehouse
 
LOV Oracle