WSH Commercial Invoice PDF Output

Description
Categories: BI Publisher
Application: Shipping Execution
Source: Commercial Invoice PDF Output (XML)
Short Name: WSHRDINVX_XML
DB package: WSH_WSHRDINV_XMLP_PKG
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
Item Flex Code