WSH Bill of Lading
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Bill of Lading
Application: Shipping Execution
Source: Bill of Lading (XML)
Short Name: WSHRDBOL_XML
DB package: WSH_WSHRDBOL_XMLP_PKG
Description: Bill of Lading
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 | |
---|---|---|---|
Warehouse |
|
LOV Oracle | |
Delivery Date (Low) |
|
Date | |
Delivery Date (High) |
|
Date | |
Freight Carrier |
|
LOV Oracle | |
Delivery Name |
|
LOV Oracle | |
Trip Name |
|
LOV Oracle | |
Bill of Lading Number |
|
LOV Oracle | |
Item Display |
|
LOV Oracle |