-- File renamed from prg_fids_triggers.sql - Dave Banks - FALCP/CA/1771 - 8th March 2006

create or replace trigger ti_prg_fids_info
before insert on prg_fids_info
for each row
begin

        if (:new.id is null or :new.id = 0 ) then
                select
                        flight_info_id.nextval
                into
                        :new.id
                from
                        dual;
        end if;

        :new.timestamp := sysdate;

        if ( :new.direction is null  ) then
                :new.direction := 'D';
        end if;

end;
/

create or replace trigger tu_prg_fids_info
before update on prg_fids_info
for each row
begin

        :new.timestamp     := sysdate;





end;
/



create or replace trigger tia_prg_fids_info
after insert on prg_fids_info
for each row
declare
        incoming   prg_fids_info%rowtype;
begin

        incoming.id             := :new.id;
        incoming.timestamp      := :new.timestamp;
        incoming.flight_no      := :new.flight_no;
        incoming.flight_date    := :new.flight_date;
        incoming.flight_dest    := :new.flight_dest;
        incoming.gate           := :new.gate;
        incoming.scheduled      := :new.scheduled;
        incoming.estimate       := :new.estimate;
        incoming.blockoff       := :new.blockoff;
        incoming.runway         := :new.runway;
        incoming.direction      := :new.direction;
        incoming.flight_code3   := :new.flight_code3;
				incoming.flight_code2   := :new.flight_code2;
				incoming.flight_number  := :new.flight_number;
        
        
        
        
        

        fasl_fids.handle_prg_flight_info( flightinfo.prg_interface, incoming);

end;
/

create or replace trigger tua_prg_fids_info
after update on prg_fids_info
for each row
declare
        
        incoming prg_fids_info%rowtype;
        intime   prg_fids_info%rowtype;                     

				
        v_gate flight_info.gate%type;
        v_dir  flight_info.direction%type;


begin
                    
       
				/*  only one column out of scheduled,estimate,blockoff,runway will 
						be updated at a time 
						This will use the one column that has changed in this update 
				*/
        
        
        
        
           v_gate := nvl(:new.gate, :old.gate);

					 update flight_info
							set gate = v_gate,
								  apron = v_gate
         where raw_data_id = :old.id;

        
        
        -- v_dir  := nvl(:new.direction, :old.direction);


        
					if (:old.scheduled <> :new.scheduled  or
					    :old.estimate  <> :new.estimate 	or
					   	:old.blockoff  <> :new.blockoff 	or
					    :old.runway    <> :new.runway     ) then

					update flight_info set 
						std = 
					decode(:old.scheduled ,
								 :new.scheduled, ( decode(:old.estimate,
																						:new.estimate,(decode(:old.blockoff,
																													:new.blockoff,  :new.runway,
																													:new.blockoff )
																													),
																						:new.estimate)
																	),
									:new.scheduled )
					,
					BLOCK_OFF = 
					decode(:old.scheduled ,
									:new.scheduled, ( decode(:old.estimate,
																						:new.estimate,(decode(:old.blockoff,
																													:new.blockoff,  'A',
																													'B' )
																													),
																						'E')
																	),
									' ' )
					where raw_data_id = :old.id;


					update flight 
					set  FLIGHT_SCH_TIME =
					decode(:old.scheduled ,
									:new.scheduled, ( decode(:old.estimate,
																						:new.estimate,(decode(:old.blockoff,
																													:new.blockoff,  :new.runway,
																													:new.blockoff )
																													),
																						:new.estimate)
																	),
									:new.scheduled ) 
					where flight_id = ( select f.flight_id 
																	from flight_outbound_v1 f, flight_info fi
																	where fi.raw_data_id 	= :old.id  and
																	fi.flight_no 					= f.flight_no and
																	fi.flight_date 				= f.flight_date ) ;

				
				end if ;

         
				

end;
/
