PROCEDURE search
IS
flag number:=1;
ship_code number;
src  varchar2(240);
trip date;
   CURSOR c1
   IS
      SELECT   d_dot.whse_code src_whse, d_dot.whse_code dst_whse,
               start_shift_code, tm_item_code, trip_start_date, tm_item_id,
               SUM (unload_quantity) qty, dot.orgn_code
          FROM tm_trip_headers tth,
               tm_routes tr,
               tm_trip_lines ttl,
               tm_locations tl,
               dempo_opm_tm dot,
               tm_locations d_tl,
               dempo_opm_tm d_dot
         WHERE                                  --tth.tm_transaction_id=220381
--AND
               tth.route_id = tr.route_id
           AND tth.tm_transaction_id = ttl.tm_transaction_id
           AND tr.source_sub_inventory = tl.tm_location_id
           AND dot.loc_code = tl.tm_location_id
           AND tr.dest_sub_inventory = d_tl.tm_location_id
           AND d_dot.loc_code =
                           d_tl.tm_location_id
                                              --- Destination  should be Plant
           AND start_shift_code = :tms_rec.shift_code                      --1
           AND TRUNC (trip_start_date) = :tms_rec.trip_start
           AND d_dot.whse_code = :tms_rec.whse_code
           AND d_dot.orgn_type = 'P'
           AND activity_id LIKE 'BUN.FEED%'
           AND ttl.attribute9 IS NULL                -- ie reason code is null
      GROUP BY trip_start_date,                     --, route_code,route_name,
               d_dot.whse_code,
               start_shift_code,
               tm_item_code,
               tm_item_id,
               dot.orgn_code,
               dot.whse_code;
                        
CURSOR c2 IS
	SELECT		
		 tm_item_code,
		 RECIPE_NO ,
		 qty,
		 src_whse,
	   dst_whse,
	   start_shift_code,
	   trip_start_date,
	   orgn_code
 FROM tms_rec         
WHERE  TRIP_START_DATE=:tms_rec.TRIP_START 
AND  SRC_WHSE= :tms_rec.WHSE_CODE
AND START_SHIFT_CODE=:tms_rec.SHIFT_CODE;
         
BEGIN

for j in c2 
loop 

        IF :tms_rec.TRIP_START=j.TRIP_START_DATE THEN
          	IF  :tms_rec.WHSE_CODE=j.SRC_WHSE  THEN
              	IF  :tms_rec.SHIFT_CODE=j.START_SHIFT_CODE THEN
                      flag:=0;
              	END IF;
           END IF;
       END IF; 

end loop;   

 IF flag=1 THEN
 	         FIRST_RECORD;

     FOR i IN c1
        LOOP
            :tms_rec.tm_item_code := i.tm_item_code;
            :tms_rec.qty := i.qty;
            :tms_rec.src_whse := i.src_whse;
            :tms_rec.dst_whse := i.dst_whse;
            :tms_rec.start_shift_code := i.start_shift_code;
            :tms_rec.trip_start_date := i.trip_start_date;
            :tms_rec.orgn_code := i.orgn_code;
          NEXT_RECORD;
        END LOOP;
     previous_RECORD;
 
 
ELSE
    
  Set_Item_Property ('tms_rec.RECIPE_NO', ENABLED, PROPERTY_false);
  FIRST_RECORD;
  FOR j IN c2
        LOOP
             :tms_rec.tm_item_code:=j.tm_item_code;
             :tms_rec.RECIPE_NO:=j.RECIPE_NO; 
             :tms_rec.qty:= j.qty;
             :tms_rec.src_whse:=j.src_whse;
             :tms_rec.dst_whse:=j.dst_whse;
             :tms_rec.start_shift_code:=j.start_shift_code;
             :tms_rec.trip_start_date:=j.trip_start_date;
             :tms_rec.orgn_code:=j.orgn_code;
         NEXT_RECORD;           
        END LOOP;
   previous_RECORD;
  
END IF;
 
END;