/*****************************************************************************
*
* Filename         - $Workfile:   add_bag_data2.sql  $
*
*                     Name              Date      Version     CR Number
* 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: triggers for tagtrak_users table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/

create or replace package add_bag_data
is


procedure add_data(incoming    in   bag_trace%rowtype);

function  get_location(loc     in  location.location_code%type) return location.location_description%type;

function  get_name (userid     in  tagtrak_users.user_id%type) return tagtrak_users.loginname%type;

function  get_error(errorid    in  error.error_id%type) return   error.error_description%type;


end add_bag_data;


/


create or replace package body add_bag_data
as

function get_location(loc in  location.location_code%type) return location.location_description%type
is

    v_loc          location.location_description%type;

begin
	
		select location_description
			into v_loc
      from location
     where location_code = loc;

		 return v_loc;

exception

		 when no_data_found then
			 v_loc  := NULL;
			 return v_loc;

end get_location;


function get_name (userid  in  tagtrak_users.user_id%type) return tagtrak_users.loginname%type
is

			 v_logname      tagtrak_users.loginname%type;

begin

   select loginname 
 		 into v_logname
		 from tagtrak_users
		where user_id = userid;

  	 return v_logname;

exception

		when no_data_found then
			v_logname  := NULL; 
 		  return v_logname;

end get_name;



function  get_error(errorid    in  error.error_id%type) return   error.error_description%type
is

	    v_error        error.error_description%type;

begin

   select error_description 
	 	 into v_error
	  	from error
		where error_id = errorid;

	 return v_error;

exception

		 when no_data_found then
				 v_error  := NULL; 
				 return v_error;

end get_error;



procedure add_data(incoming    in   bag_trace%rowtype)
is

   v_fno          flight.flight_no%type;
	 v_date         flight.flight_date%type;
   v_error        error.error_description%type;
   v_loc          location.location_description%type;
   v_logname      tagtrak_users.loginname%type;
	 v_agent        tagtrak_users.agent_code%type;


cursor get_agent is
			 select flight_id, agent_code
		     from live.flight_access
        where flight_id = incoming.flight_id_scan;


begin



           v_error   := get_error(incoming.error_id);
					 v_loc     := get_location(incoming.location_code);
					 v_logname := get_name(incoming.user_id);




      If incoming.flight_id_scan > 0  then


    	    begin


				 select flight_no,
								flight_date
           into v_fno,
								v_date
           from flight
          where flight_id = incoming.flight_id_scan;

         exception

	         when no_data_found	 then			
              
							 v_fno  := NULL; 
							 v_date := NULL;
					 
          end;


      For get_agent_rec IN get_agent Loop

					insert into bag_trace_data
					( flight_date,
						flight_no,
						bag_trace_date,
						bag_trace_time,
                                                action_date,
                                                action_time,
						trace_action,
						bag_tag,
						location_code,
						error_id,
						flight_id_scan,
						pax_id,
						location_description,
						loginname,
						agent_code,
						error_description
           )
					 values
					 ( v_date,
						 v_fno,
					   incoming.bag_trace_time,
						 to_char(incoming.bag_trace_time, 'HH24:MI'),
                                                 incoming.action_time,
                                                 to_char(incoming.action_time, 'HH24:MI'),
						 incoming.trace_action,
						 incoming.bag_tag,
						 incoming.location_code,
						 incoming.error_id,
						 incoming.flight_id_scan,
						 incoming.pax_id,
             v_loc,
						 v_logname,
						 get_agent_rec.agent_code,
						 v_error
           );

		  End Loop;			 
					 

		Else 
          begin

          select agent_code
						into v_agent
						from tagtrak_users
						where user_id = incoming.user_id;
       

					exception
						 when no_data_found then

								 v_agent := null;
          end;


          insert into bag_trace_data
					( flight_date,
						flight_no,
						bag_trace_date,
						bag_trace_time,
                                                action_date,
                                                action_time,
					  trace_action,
						bag_tag,
						location_code,
						error_id,
						flight_id_scan,
						pax_id,
  					location_description,
  					loginname,
						agent_code,
				  	error_description
				 )
				 values
				 (  v_date,
						v_fno,
						incoming.bag_trace_time,
 				  	to_char(incoming.bag_trace_time, 'HH24:MI'),
                                                incoming.action_time,
                                                to_char(incoming.action_time, 'HH24:MI'),
						incoming.trace_action,
					  incoming.bag_tag,
						incoming.location_code,
					  incoming.error_id,
						incoming.flight_id_scan,
						incoming.pax_id,
   					v_loc,
						v_logname,
						v_agent,
						v_error
				);
        
    End if;


End add_data;

End add_bag_data;

/

