ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D472B651A9E05362FB09050512 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 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 --; Y WSH WSHRDBOLX_XML US WSH Bill of Lading PDF Output Imported from BI Publisher Description: Bill of Lading PDF Output Application: Shipping Execution Source: Bill of Lading PDF Output (XML) Short Name: WSHRDBOLX_XML DB package: WSH_WSHRDBOL_XMLP_PKG BI Publisher :cp_certificate_number :cp_classification :cp_container_classification :cp_hm :cp_key_version :cp_num_packages :cp_src_line_id :cp_top_model_line_id :cp_total_collect_currency :cp_total_prepaid_amount :cp_total_prepaid_currency :delivery_id3 :delivery_type :lp_date_range :lp_delivery_id :lp_delivery_leg_id :lp_freight_code :lp_organization_id :lp_trip_id :lp_where_clause :p_conc_request_id :p_delivery_date_high :p_delivery_date_high_v :p_delivery_date_low :p_delivery_id :p_delivery_leg_id :p_freight_code :p_item_display :p_item_flex_code :p_organization_id :p_trip_id 1 -20 :p_item_flex_code Char MSTK Y US Item Flex Code Item Flex Code 2 10 :p_organization_id LOV Oracle WSH_SRS_ORGANIZATIONS Y select organization_id id, organization_code value, organization_name description from org_organization_definitions order by organization_code select organization_code from org_organization_definitions where organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') US Warehouse Warehouse 3 20 :p_delivery_date_low Date US Delivery Date (Low) Delivery Date (Low) 4 30 :p_delivery_date_high Date select fnd_date.date_to_chardate(fnd_date.canonical_to_date(:$flex$.delivery_date_low)) from dual US Delivery Date (High) Delivery Date (High) 5 40 :p_freight_code LOV Oracle WSH_SRS_FREIGHT_CARRIER Y Y select lookup_code id, meaning value, description description from fnd_lookup_values_vl where view_application_id=3 and lookup_type='SHIP_METHOD' and exists (select 1 from wsh_carrier_services wcs,wsh_carriers wc where wcs.ship_method_code=lookup_code and wcs.carrier_id=wc.carrier_id and nvl(wc.generic_flag,'N')='N') order by lookup_code US Freight Carrier Freight Carrier 6 50 :p_delivery_id LOV Oracle WSH_BOL_DELIVERIES_W_TYPE Y select delivery_id id, name value, delivery_type_meaning description from wsh_deliveries_bol_rdf_v where organization_id=nvl(:$flex$.wsh_srs_organizations:null,organization_id) and nvl(shipment_direction,'O') in('O','IO') order by name US Delivery Name Delivery Name 7 60 :p_trip_id LOV Oracle WSH_SRS_TRIPS Y select trip_id id, name value, null description from wsh_trips where nvl(shipments_type_flag,'O') in ('O','M') order by name US Trip Name Trip Name 8 70 :p_delivery_leg_id LOV Oracle WSH_SRS_BOLS Y select delivery_leg_id id, bill_of_lading_number value, null description from wsh_bols_rd_v order by bill_of_lading_number US Bill of Lading Number Bill of Lading Number 9 80 :p_item_display LOV Oracle WSH_MGD_ITEM_DISPLAY Y select lookup_code id, meaning value, description description from wsh_lookups where lookup_type='ITEM_DISPLAY' and enabled_flag='Y' order by meaning select meaning from wsh_lookups where lookup_type = 'ITEM_DISPLAY' and lookup_code = 'D' Y US Item Display Print Inventory Item Description or Flex