WSH Commercial Invoice PDF Output
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Commercial Invoice PDF Output
Application: Shipping Execution
Source: Commercial Invoice PDF Output (XML)
Short Name: WSHRDINVX_XML
DB package: WSH_WSHRDINV_XMLP_PKG
Description: Commercial Invoice PDF Output
Application: Shipping Execution
Source: Commercial Invoice PDF Output (XML)
Short Name: WSHRDINVX_XML
DB package: WSH_WSHRDINV_XMLP_PKG
Run
WSH Commercial Invoice PDF Output and other Oracle EBS reports with Blitz Report™ on our demo environment
select wnd.delivery_id delivery_id3, to_char(wnd.delivery_id) delivery_id_chr, wnd.initial_pickup_date ship_date, nvl(wnd.currency_code, wdd.currency_code) currency_code, wnd.ship_method_code ship_via, wnd.gross_weight, wnd.weight_uom_code, wnd.number_of_lpn num_lpn, loc.country country_of_origin, loc.address1 from_addr1, loc.address2 from_addr2, loc.address3 from_addr3, loc.address4 from_addr4, loc.city||', '||loc.state||' '||loc.postal_code from_city_state, loc.country from_country, loc1.address1 to_addr1, loc1.address2 to_addr2, loc1.address3 to_addr3, loc1.address4 to_addr4, loc1.city||', '||loc1.state||' '||loc1.postal_code to_city_state, loc1.country to_country, mc.description category, round( nvl(wdd.unit_price,0) * WSH_WV_UTILS.CONVERT_UOM(wdd.requested_quantity_uom, wdd.src_requested_quantity_uom, 1, wdd.inventory_item_id), 2) item_cost, round( sum(nvl(wdd.unit_price,0) * WSH_WV_UTILS.CONVERT_UOM(wdd.requested_quantity_uom, wdd.src_requested_quantity_uom, NVL(wdd.shipped_quantity,wdd.requested_quantity), wdd.inventory_item_id)), 2) extended_cost, wdd.organization_id, wdd.inventory_item_id, wdd.customer_item_id, sum(nvl(wdd.shipped_quantity,wdd.requested_quantity)) shipped_quantity, wdd.requested_quantity_uom unit_of_measure, wdd.cust_po_number po, wdd.source_header_number order_num, wdd.source_header_id header_id10, to_char(wdd.source_header_id) source_header_id_chr, wdd.source_line_id line_id, to_char(wdd.source_line_id) source_line_id_chr, wdd.ship_to_site_use_id ship_to_site_use_id, wdd.ship_to_contact_id, wdd.src_requested_quantity_uom source_uom, wdd.source_code, wdd.item_description item_description, wdd.top_model_line_id top_model_line_id10, wdd.ato_line_id ato_line_id , wdd.cust_po_number, 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, wdd.requested_quantity2 secondary_req_quantity, wdd.requested_quantity_uom2 secondary_req_qty_uom, wnd.port_of_loading port_of_loading, wnd.port_of_discharge port_of_discharge, wnd.waybill waybill, wdd.delivery_detail_id delivery_detail_id10, to_char(wdd.delivery_detail_id) delivery_detail_id_chr, WSH_WSHRDINV_XMLP_PKG.c_ship_viaformula(wnd.delivery_id, wnd.ship_method_code, wdd.organization_id) C_ship_via, WSH_WSHRDINV_XMLP_PKG.c_num_boxesformula(wnd.delivery_id, wnd.number_of_lpn) C_num_boxes, WSH_WSHRDINV_XMLP_PKG.c_data_foundformula(wnd.delivery_id) C_DATA_FOUND, WSH_WSHRDINV_XMLP_PKG.cf_customer_nameformula() CF_customer_name, WSH_WSHRDINV_XMLP_PKG.f_ship_to_cust_nameformula(wdd.ship_to_site_use_id) F_SHIP_TO_CUST_NAME, WSH_WSHRDINV_XMLP_PKG.CP_ship_to_addr1_p CP_ship_to_addr1, WSH_WSHRDINV_XMLP_PKG.CP_ship_to_addr2_p CP_ship_to_addr2, WSH_WSHRDINV_XMLP_PKG.CP_ship_to_addr3_p CP_ship_to_addr3, WSH_WSHRDINV_XMLP_PKG.CP_ship_to_addr4_p CP_ship_to_addr4, WSH_WSHRDINV_XMLP_PKG.CP_ship_to_city_state_p CP_ship_to_city_state, WSH_WSHRDINV_XMLP_PKG.CP_ship_to_country_p CP_ship_to_country, WSH_WSHRDINV_XMLP_PKG.cf_contact_nameformula(wdd.ship_to_contact_id) CF_contact_name, WSH_WSHRDINV_XMLP_PKG.cf_commodity_classformula(wdd.inventory_item_id, wdd.organization_id) CF_COMMODITY_CLASS, WSH_WSHRDINV_XMLP_PKG.c_item_dispformula(wdd.customer_item_id, wdd.inventory_item_id, wdd.organization_id, wdd.item_description) C_item_disp, WSH_WSHRDINV_XMLP_PKG.CP_item_cost_p CP_item_cost, WSH_WSHRDINV_XMLP_PKG.CP_extended_cost_p CP_extended_cost from wsh_new_deliveries wnd, wsh_delivery_assignments_v wda, wsh_delivery_details wdd, wsh_locations loc, wsh_locations loc1, mtl_categories mc, mtl_item_categories mic, mtl_default_category_sets mdc where wnd.delivery_id = wda.delivery_id AND nvl(wnd.shipment_direction, 'O') IN ('O', 'IO') AND wnd.delivery_type = 'STANDARD' AND (wdd.requested_quantity > 0 OR wdd.released_status != 'D') AND wdd.container_flag IN ('N', 'Y') AND wdd.delivery_detail_id = wda.delivery_detail_id AND wda.delivery_id is not null AND wdd.ship_from_location_id = loc.wsh_location_id AND wdd.ship_to_location_id = loc1.wsh_location_id AND wdd.organization_id = mic.organization_id AND wdd.inventory_item_id = mic.inventory_item_id AND mic.category_id = mc.category_id AND mic.category_set_id = mdc.category_set_id AND mdc.functional_area_id = 7 AND EXISTS (select 'EXISTS' from bom_inventory_components bic where bic.include_on_ship_docs = 1 and bic.component_sequence_id = ( select component_sequence_id from oe_order_lines_all oel where oel.line_id = wdd.source_line_id ) UNION select 'EXISTS' from dual where wdd.top_model_line_id is null UNION select 'EXISTS' from dual where wdd.top_model_line_id is not null and wdd.ato_line_id is not null UNION select 'EXISTS' from dual where wdd.top_model_line_id = wdd.source_line_id) &LP_DELIVERY_ID &LP_DEPARTURE_DATE &LP_FREIGHT_CODE &LP_ORGANIZATION_ID &LP_TRIP_STOP_ID group by wnd.delivery_id, to_char(wnd.delivery_id), wnd.initial_pickup_date, nvl(wnd.currency_code, wdd.currency_code), wnd.ship_method_code, wnd.gross_weight, wnd.weight_uom_code, wnd.number_of_lpn, loc.country , loc.address1 , loc.address2 , loc.address3 , loc.address4, loc.city||', '||loc.state||' '||loc.postal_code, loc.country, loc1.address1 , loc1.address2 , loc1.address3 , loc1.address4, loc1.city||', '||loc1.state||' '||loc1.postal_code, loc1.country, mc.description , nvl(wdd.unit_price,0) * WSH_WV_UTILS.CONVERT_UOM(wdd.requested_quantity_uom, wdd.src_requested_quantity_uom, 1, wdd.inventory_item_id), nvl(wdd.unit_price,0) * WSH_WV_UTILS.CONVERT_UOM(wdd.requested_quantity_uom, wdd.src_requested_quantity_uom, NVL(wdd.shipped_quantity,wdd.requested_quantity), wdd.inventory_item_id), wdd.organization_id, wdd.inventory_item_id, wdd.customer_item_id, wdd.requested_quantity, wdd.src_requested_quantity, wdd.requested_quantity_uom , wdd.cust_po_number , wdd.source_header_number , wdd.source_header_id , to_char(wdd.source_header_id), wdd.source_line_id , to_char(wdd.source_line_id), wdd.ship_to_site_use_id , wdd.ship_to_contact_id, wdd.src_requested_quantity_uom, wdd.source_code, wdd.item_description, wdd.top_model_line_id, wdd.ato_line_id, wdd.cust_po_number, wnd.attribute1, wnd.attribute2 , wnd.attribute3 , wnd.attribute4 , wnd.attribute5 , wnd.attribute6 , wnd.attribute7 , wnd.attribute8 , wnd.attribute9 , wnd.attribute10, wnd.attribute11, wnd.attribute12, wnd.attribute13, wnd.attribute14, wnd.attribute15, wdd.requested_quantity2, wdd.requested_quantity_uom2, wnd.port_of_loading, wnd.port_of_discharge, wnd.waybill, wdd.delivery_detail_id, to_char(wdd.delivery_detail_id) |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Trip Stop |
|
LOV Oracle | |
Stop Planned Depart Date (Low) |
|
Date | |
Stop Planned Depart Date (High) |
|
Date | |
Freight Carrier |
|
LOV Oracle | |
Warehouse |
|
LOV Oracle | |
Delivery Name |
|
LOV Oracle | |
Item Display |
|
LOV Oracle | |
Currency Code |
|
LOV Oracle | |
Print Customer Item |
|
LOV Oracle |