WSH Bill of Lading

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
              --;