SELECT A.pick_slip_number,
       A.from_subinventory,
       A.from_locator_id,
       A.to_subinventory,
       A.to_locator_id,
       A.transaction_id,
       A.rev_txn_id,
       A.move_order_line_id,
       A.detailing_date,
       A.line_status,
       A.primary_qty,
       A.mo_number,
       A.mo_line_id,
       A.mo_line_number,
       A.delivery_detail_id,
       A.ser_dd_id,
       A.source_header_number,                                                                  
       A.order_n_header_char,                                       
       A.order_n_line_char,                                           
       A.source_header_id,                                                                    
       A.source_line_id,                                                                      
       A.shipping_instructions,                                                               
       A.ship_tolerance_above,                                                                
       A.ship_tolerance_below,                                                                
       A.inventory_item_id,                                                                   
       A.requested_quantity_uom,  
       A.delivery_id,                                                           
       A.delivery_name  delivery_name,                                                            
       A.initial_pickup_location_id,
       A.sales_line_number,
       A.line_number,
       A.shipment_number,
       A.option_number,
       A.component_number,
       A.service_number,
       A.item_info,                                                  
       A.item_description,                                                     
       A.set_name ,
       A.customer_flag,
       A.order_number_flag,
       A.subinventory_flag,
       A.customer_flag,
       A.ship_to_flag,
       A.carrier_flag,
       A.shipment_priority_flag,
       A.trip_stop_flag,
       A.delivery_flag,
       A.name,
       A.carrier,
       A.priority,
       A.organization_id,
       A.item_name,
       A.ordered_quantity,
        B.delivery_detail_id delivery_detail_id_b,
        B.lot_txn_id,
        B.serial_transaction_id,
        B.lot_number,
        '|' LB
FROM
      
(
SELECT DISTINCT wpsv.pick_slip_number,                                                            
       decode(msi.reservable_type,2,wdd.subinventory,wpsv.from_subinventory) from_subinventory,   
       decode(msi.reservable_type,2,wdd.locator_id,wpsv.from_locator_id) from_locator_id,         
       wpsv.to_subinventory,                                                                      
       wpsv.to_locator_id,                                                                        
       nvl(wpsv.transaction_id ,-99) transaction_id,
       nvl(wpsv.transaction_id, -99) rev_txn_id,      
       wpsv.move_order_line_id,                                                                 
       wpsv.detailing_date,                                                                     
       wpsv.line_status,                                                                        
       wpsv.primary_qty,                                                                        
       mtrh.request_number mo_number,                                                             
       mtrl.line_id mo_line_id,                                                                   
       mtrl.line_number mo_line_number,
       -99  delivery_detail_id,  
       -99 ser_dd_id,                                                          
       wdd.source_header_number,                                                                  
       to_char(wdd.source_header_id) order_n_header_char,                                       
       to_char(wdd.source_line_id) order_n_line_char,                                           
       wdd.source_header_id,                                                                    
       wdd.source_line_id,                                                                      
       wdd.shipping_instructions,                                                               
       wdd.ship_tolerance_above,                                                                
       wdd.ship_tolerance_below,                                                                
       wdd.inventory_item_id,                                                                   
       wdd.requested_quantity_uom,  
       wnd.delivery_id,                                                           
       wnd.name  delivery_name,                                                            
       wnd.initial_pickup_location_id,
      oola.line_number||DECODE (oola.shipment_number, NULL, NULL, '.')|| oola.shipment_number
          || DECODE (oola.option_number, NULL, NULL, '.')
          || oola.option_number
          || DECODE (oola.component_number, NULL, NULL, '.')
          || oola.component_number
          || DECODE (oola.service_number, NULL, NULL, '.')
          || oola.service_number sales_line_number,
      oola.line_number,
      oola.shipment_number,
      oola.option_number,
      oola.component_number,
      oola.service_number,
      &LP_ITEM_DISPLAY_VALUE item_info,                                                  
       msi.description item_description,                                                     
       os.set_name ,
--       wpgr.customer_flag,
       wpgr.order_number_flag,
       wpgr.subinventory_flag,
       wpgr.customer_flag,
       wpgr.ship_to_flag,
       wpgr.carrier_flag,
       wpgr.shipment_priority_flag,
       wpgr.trip_stop_flag,
       wpgr.delivery_flag,
       wpgr.name,
       wdd.ship_method_code carrier,
       wdd.shipment_priority_code priority,
       wdd.organization_id,
       msi.segment1 item_name,
       oola.ordered_quantity ordered_quantity
  FROM wsh_pick_slip_v wpsv,                                                                 
       mtl_system_items msi,                                                                 
       wsh_delivery_details wdd,                                                             
       mtl_txn_request_lines mtrl,                                                         
       mtl_txn_request_headers mtrh,
       wsh_delivery_assignments wda,                                                         
       wsh_new_deliveries wnd,                                                               
       oe_order_lines_all oola,                                                              
       oe_sets os,    
       wsh_pick_grouping_rules wpgr                                                                            
 WHERE wpsv.line_status = 'UNPICKED'
   AND wpsv.move_order_line_id = mtrl.line_id                                      
   AND  mtrl.header_id = mtrh.header_id
   AND  mtrl.line_id = wdd.move_order_line_id 
   AND wdd.inventory_item_id = msi.inventory_item_id(+)                                        
   AND wdd.organization_id = msi.organization_id(+)                                             
   AND wdd.delivery_detail_id = wda.delivery_detail_id               
   AND wda.delivery_id = wnd.delivery_id(+)                          
   AND wdd.source_line_id = oola.line_id                             
   AND wdd.source_header_id = oola.header_id 
   AND wdd.released_status='S'                       
   AND wdd.source_code = 'OE'
   AND wdd.ship_set_id = os.set_id(+)
   AND mtrh.grouping_rule_id = wpgr.pick_grouping_rule_id(+)

        &LP_WAREHOUSE_CLAUSE
            &LP_MO_CLAUSE     
            &lp_pick_slip_num
            &LP_ORDER_NUM
            &LP_ORDER_TYPE
            &LP_CUSTOMER_ID
            &lp_ship_method_code
             &LP_DETAIL_DATE
             &LP_PICK_STATUS 
            &LP_PRINTER_NAME
      
UNION ALL
SELECT DISTINCT wpsv.pick_slip_number,                                                            
       decode(msi.reservable_type,2,wdd.subinventory,wpsv.from_subinventory) from_subinventory,   
       decode(msi.reservable_type,2,wdd.locator_id,wpsv.from_locator_id) from_locator_id,         
       wpsv.to_subinventory,                                                                      
       wpsv.to_locator_id,                                                                        
       nvl(wpsv.transaction_id ,-99) transaction_id,
       nvl(wdd.transaction_id, -99) rev_txn_id,     
       wpsv.move_order_line_id,                                                                 
       wpsv.detailing_date,                                                                     
       wpsv.line_status,                                                                        
       wdd.requested_quantity primary_qty,                                                                        
       mtrh.request_number mo_number,                                                             
       mtrl.line_id mo_line_id,                                                                   
       mtrl.line_number mo_line_number,  
       wdd.delivery_detail_id,  
       wdd.delivery_detail_id  ser_dd_id,                                                        
       wdd.source_header_number,                                                                  
       to_char(wdd.source_header_id) order_n_header_char,                                       
       to_char(wdd.source_line_id) order_n_line_char,                                           
       wdd.source_header_id,                                                                    
       wdd.source_line_id,                                                                      
       wdd.shipping_instructions,                                                               
       wdd.ship_tolerance_above,                                                                
       wdd.ship_tolerance_below,                                                                
       wdd.inventory_item_id,                                                                   
       wdd.requested_quantity_uom,  
       wnd.delivery_id,                                                           
       wnd.name  delivery_name,                                                            
       wnd.initial_pickup_location_id,
      oola.line_number||DECODE (oola.shipment_number, NULL, NULL, '.')|| oola.shipment_number
          || DECODE (oola.option_number, NULL, NULL, '.')
          || oola.option_number
          || DECODE (oola.component_number, NULL, NULL, '.')
          || oola.component_number
          || DECODE (oola.service_number, NULL, NULL, '.')
          || oola.service_number sales_line_number,
      oola.line_number,
      oola.shipment_number,
      oola.option_number,
      oola.component_number,
      oola.service_number,
      &LP_ITEM_DISPLAY_VALUE item_info,                                                  
       msi.description item_description,                                                     
       os.set_name ,
--       wpgr.customer_flag,
       wpgr.order_number_flag,
       wpgr.subinventory_flag,
       wpgr.customer_flag,
       wpgr.ship_to_flag,
       wpgr.carrier_flag,
       wpgr.shipment_priority_flag,
       wpgr.trip_stop_flag,
       wpgr.delivery_flag,
       wpgr.name,
       wdd.ship_method_code carrier,
       wdd.shipment_priority_code priority,
       wdd.organization_id,
       msi.segment1 item_name,
       oola.ordered_quantity ordered_quantity
  FROM wsh_pick_slip_v wpsv,                                                                 
       mtl_system_items msi,                                                                 
       wsh_delivery_details wdd,                                                             
       mtl_txn_request_lines mtrl,                                                         
       mtl_txn_request_headers mtrh,
       wsh_delivery_assignments wda,                                                         
       wsh_new_deliveries wnd,                                                               
       oe_order_lines_all oola,                                                              
       oe_sets os,    
       wsh_pick_grouping_rules wpgr                                                                            
 WHERE wpsv.line_status = 'PICKED'
   AND  wpsv.move_order_line_id = mtrl.line_id                                      
   AND  mtrl.header_id = mtrh.header_id
   AND  mtrl.line_id = wdd.move_order_line_id 
   AND wpsv.transaction_id=decode(nvl(wdd.transaction_id ,-99),-99,wpsv.transaction_id,wdd.transaction_id)
   AND wdd.inventory_item_id = msi.inventory_item_id(+)                                        
   AND wdd.organization_id = msi.organization_id(+)                                             
   AND wdd.delivery_detail_id = wda.delivery_detail_id               
   AND wda.delivery_id = wnd.delivery_id(+)                          
   AND wdd.source_line_id = oola.line_id                             
   AND wdd.source_header_id = oola.header_id                         
   AND wdd.source_code = 'OE'
   AND wdd.released_status != 'S'
   AND wdd.ship_set_id = os.set_id(+)
   AND mtrh.grouping_rule_id = wpgr.pick_grouping_rule_id(+)

        &LP_WAREHOUSE_CLAUSE
            &LP_MO_CLAUSE     
            &lp_pick_slip_num
            &LP_ORDER_NUM
            &LP_ORDER_TYPE
            &LP_CUSTOMER_ID
            &lp_ship_method_code
             &LP_DETAIL_DATE
             &LP_PICK_STATUS 
            &LP_PRINTER_NAME
  
   
   ) A,
   /*PICKED*/
(
SELECT 
wdd.delivery_detail_id delivery_detail_id,
nvl(wdd.transaction_id,-99) lot_txn_id,
NVL(wdd.transaction_temp_id, delivery_detail_id)  serial_transaction_id,
wdd.lot_number  lot_number
FROM 
wsh_delivery_details wdd
WHERE lot_number IS NOT NULL
UNION ALL
/*UNPICKED*/   
SELECT 
-99  delivery_detail_id,
mtlt.transaction_temp_id  lot_txn_id,                          
mtlt.serial_transaction_temp_id serial_transaction_id,     
mtlt.lot_number lot_number                               
FROM mtl_transaction_lots_temp mtlt
) B

WHERE A.REV_TXN_ID=B.LOT_TXN_ID(+)
AND A.DELIVERY_DETAIL_ID=B.DELIVERY_DETAIL_ID(+)
ORDER BY
 A.from_subinventory,
 A.item_name,
 A.transaction_id,
 B.lot_number,
 A.line_number,
 A.shipment_number,
 A.option_number,
 A.component_number,
 A.service_number