WSH Commercial Invoice

Description
Categories: BI Publisher
Columns: Delivery Id3, Delivery Id Chr, Ship Date, Currency Code, Ship Via, Gross Weight, Weight Uom Code, Num Lpn, Country Of Origin, From Addr1 ...
Application: Shipping Execution
Source: Commercial Invoice (XML)
Short Name: WSHRDINV_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.city||', '||nvl(nvl(loc.state,loc.province),loc.county)||' '||loc.postal_code from_city_state,
                --loc.country  from_country,
                terr_from.territory_short_name 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.city||', '||nvl(nvl(loc1.province,loc1.state),loc1.county)||' '||loc1.postal_code to_city_state,
                --loc1.country  to_country,
                terr_to.territory_short_name 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,
                decode(:P_STANDALONE,'N', wdd.source_header_number,nvl(wdd.reference_number,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,wdd.consignee_flag) 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.C_item_cost_fmtFormula(wdd.source_code, wdd.source_line_id , wdd.requested_quantity_uom ,wdd.src_requested_quantity_uom ,wdd.inventory_item_id ) CP_item_cost,
                WSH_WSHRDINV_XMLP_PKG.C_ext_cost_fmtFormula(wdd.source_code, wdd.source_line_id , wdd.requested_quantity_uom	,wdd.src_requested_quantity_uom , nvl(wdd.shipped_quantity,wdd.requested_quantity) , wdd.inventory_item_id) CP_extended_cost,
                WSH_WSHRDINV_XMLP_PKG.cf_signatureformula(wnd.delivery_id) CF_SIGNATURE,
                WSH_WSHRDINV_XMLP_PKG.CP_CERTIFICATE_NUMBER_p CP_CERTIFICATE_NUMBER,
                WSH_WSHRDINV_XMLP_PKG.CP_KEY_VERSION_p CP_KEY_VERSION
            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,
 				fnd_territories_TL terr_to,
        		fnd_territories_TL terr_from
            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
                	loc.country = terr_from.territory_code(+) AND
					loc1.country = terr_to.territory_code(+) AND
					decode(loc.country, null, userenv('LANG'), terr_from.language) = userenv('LANG') AND --Bugfix 6524448
					decode(loc1.country, null, userenv('LANG'), terr_to.language) = userenv('LANG') AND --Bugfix 6524448
                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
                 wdd.consignee_flag, 
                 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.city||', '||nvl(nvl(loc.state,loc.province),loc.county)||' '||loc.postal_code,
                 --loc.country,
                 terr_from.territory_short_name,
                 loc1.address1 ,
                 loc1.address2 ,
                 loc1.address3 ,
                 loc1.address4,
                 --loc1.city||', '||loc1.state||' '||loc1.postal_code,
                 loc1.city||', '||nvl(nvl(loc1.province,loc1.state),loc1.county)||' '||loc1.postal_code,
                 --loc1.country,
                 terr_to.territory_short_name,
                 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 ,
                 decode(:P_STANDALONE,'N', wdd.source_header_number,nvl(wdd