WSH Auto Ship Confirm

Description
Categories: BI Publisher, Logistics
Application: Shipping Execution
Source: Auto Ship Confirm Report (XML)
Short Name: WSHRDASC_XML
DB package: WSH_WSHRDASP_XMLP_PKG
select  wnd.name delivery,
            wnd.delivery_id delivery_id,
            substrb(cust.party_name,1,50) customer,
            wpb.name name,
            wsh_util_core.get_location_description(wnd.INITIAL_PICKUP_LOCATION_ID, 'NEW UI CODE') ship_from,
            wsh_util_core.get_location_description(wnd.ULTIMATE_DROPOFF_LOCATION_ID, 'NEW UI CODE') ship_to,
            wnd.INITIAL_PICKUP_DATE pickup_date1,
            wnd.ULTIMATE_DROPOFF_DATE dropoff_date1,
            lv.meaning ship_method,
            wdi.sequence_number bol,
            wnd.WAYBILL waybill,
            wnd.GROSS_WEIGHT gross_weight,
            wnd.WEIGHT_UOM_CODE uom,
            we.exception_id,
            decode(:p_batch_type, 'SC', decode(wnd.status_code, 'CO', 'Y',
                                    'IT','Y',
                                    'CL','Y',
                                     'N'), NULL) shipped,
            decode(we.exception_id, NULL, 'Y'
                                                    ,decode(we.error_message,'E', 'N', 'Y'))  packed,
                WSH_WSHRDASP_XMLP_PKG.cf_1formula(wnd.delivery_id) CF_trip_name,
                WSH_WSHRDASP_XMLP_PKG.cf_message(we.exception_id) CF_message,
            wsh_util_core.get_location_description(wnd.ULTIMATE_DROPOFF_LOCATION_ID, 'NEW UI CODE') ship_to,
            to_char(wnd.INITIAL_PICKUP_DATE,'DD-MON-YY') pickup_date,
            to_char(wnd.ULTIMATE_DROPOFF_DATE ,'DD-MON-YY') dropoff_date,
            lv.meaning ship_method,
            wdi.sequence_number bol,
            wnd.WAYBILL waybill,
  --          wnd.GROSS_WEIGHT gross_weight,
            wnd.WEIGHT_UOM_CODE uom,
            we.exception_id,
            decode(:p_batch_type, 'SC', decode(wnd.status_code, 'CO', 'Y',
                                    'IT','Y',
                                    'CL','Y',
                                     'N'), NULL) shipped,
            decode(we.exception_id, NULL, 'Y'
                                                    ,decode(we.error_message,'E', 'N', 'Y'))  packed
            from wsh_new_deliveries wnd, wsh_picking_batches wpb, wsh_exceptions we,
            fnd_lookup_values_vl lv, wsh_delivery_legs wdl,wsh_document_instances wdi,
            ( select
                cust_acct.cust_account_id,
                party.party_name
              from
                hz_cust_accounts cust_acct,
                hz_parties party
              where
                party.party_id = cust_acct.party_id
             ) cust
            where
            nvl(wnd.shipment_direction, 'O') IN ('O', 'IO') and
            wnd.delivery_type = 'STANDARD' and
            wpb.batch_id = decode(:p_batch_type, 'SC', wnd.batch_id, 'AP', wnd.ap_batch_id, wpb.batch_id) and
            &p_pr_batch
            &lp_bol_number
            &p_exceptions
            we.delivery_id(+) = wnd.delivery_id and
            we.exception_name(+) = 'WSH_BATCH_MESSAGE' and
            lv.lookup_code(+) = wpb.ship_method_code and
            lv.lookup_type(+) = 'SHIP_METHOD' and
            lv.view_application_id(+) = 3 and
            cust.cust_account_id (+)=wnd.customer_id
            and wdi.entity_id (+) = wdl.delivery_leg_id
            AND wdi.entity_name (+) = 'WSH_DELIVERY_LEGS' AND wdi.document_type (+) = 'BOL'
            AND wdi.status (+) <> 'CANCELLED'
            AND wnd.delivery_id = wdl.delivery_id (+)
			ORDER BY 16 DESC,15 DESC,3 ASC,5 ASC,4 ASC,13 ASC,12 ASC,7 ASC,11 ASC,10 ASC,9 ASC,8 ASC,6 ASC,1 ASC,2 ASC
Parameter Name SQL text Validation
Batch Type
 
LOV Oracle
Exceptions Only
 
LOV Oracle
Pick Release Batch
 
LOV Oracle
Ship Method
 
LOV Oracle
Firm Status
 
LOV Oracle
Drop-off Date To
 
Date
Drop-off Date From
 
Date
Pick-up Date To
 
Date
Pick-up Date from
 
Date
Ship To Address
 
LOV Oracle
Ship From Address
 
LOV Oracle
Customer
 
LOV Oracle
Bill of Lading To
 
LOV Oracle
Bill of Lading From
 
LOV Oracle
Delivery To
 
LOV Oracle
Delivery From
 
LOV Oracle
Ship Confirm Rule
 
LOV Oracle
Ship Confirm Batch
 
LOV Oracle