WSH Auto-pack

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 party.party_name,
                      cust_acct.cust_account_id,
                      'C' consignee_flag
               FROM   hz_parties party,
                      hz_cust_accounts cust_acct
               WHERE  cust_acct.party_id = party.party_id
               UNION
               SELECT party.party_name,
                      pv.vendor_id cust_account_id,
                      'V' consignee_flag
               FROM   hz_parties party,
                      po_vendors pv
               WHERE  pv.party_id = party.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 cust.consignee_flag (+)= NVL(wnd.consignee_flag,'C')            
            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