/*****************************************************************************
*
* Filename         - $Workfile:   unload_entire_flight.sql  $
*
*                     Name: Yomi Kogbe      Date: 03/05/00   Version:1 CR Number:15066
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/


PROMPT create or replace procedure unload_entire_flight

create or replace procedure unload_entire_flight

(   v_retval          out number
  , v_flight_id       in  flight.flight_id%type
  , v_user_id         in  bag_trace.user_id%type
  , v_loc1            in  bag_trace.location_code%type
  , new_flight_id     in  flight.flight_id%type 
   
) is

			abort EXCEPTION;

        v_fid                   flight.flight_id%type;
        v_uld_no                ulds.uld_no%type;
        v_loc                   bag_trace.location_code%type;
        v_new_flight_dest       flight_dests.flight_destination%type;
        retval                  number(10);
        l_user_id               bag_trace.user_id%type;
        bulk_transfer_error     exception;


       cursor  c1 is
       select  uld_no
         from  ulds
        where  flight_id = v_flight_id
          for  update;  



BEGIN
                v_retval  := 0;
                v_fid     := v_flight_id;
                l_user_id := v_user_id;
                v_loc     := v_loc1;
                
                
                for c1_rec in c1
                loop
                
                 
                                              
                 unload_entire_uld(retval,
                                   v_fid, 
                                   c1_rec.uld_no, 
                                   l_user_id, 
                                   v_loc, 
                                   new_flight_id 
                                   );
                        
                                  IF retval != 0 then
                                     rollback;
                                     v_retval := retval;
                                     RAISE abort;
                                  END IF;


                end loop;
               

                


commit;
EXCEPTION
		WHEN abort THEN
			NULL;
        
     when others then
           
           v_retval := -1063; 
         
           

    
end unload_entire_flight;


/
                                          
                                          
                                       
   
