/*****************************************************************************
*
* Filename              - flight_info_inbound_triggers.sql
*
* Project Name          - UltraTrak Database
*
* Source Code Identifier - FAS/URBRS/TN/10154
*
* Description           - Triggers for FLIGHT_INFO_INBOUND table. These triggers
*                         automatically populate the TERMINAL field from the
*                         gate field
*
* Language              - Oracle PL/SQL
*
* File History          - For the development history run the Show History report in PVCS
*
* Copyright Ultra Electronics Airport Systems, 2003
*
*****************************************************************************/

/*
 * Before each insert into the flight_info table
 */

CREATE OR REPLACE TRIGGER ti_flight_info_inbound
BEFORE INSERT OR UPDATE ON flight_info_inbound
FOR EACH ROW
DECLARE
   -- Declare local variables to trigger.
   populate db_config.db_value%TYPE;
   l_flight_id flight.flight_id%TYPE;
   l_flight_no flight.flight_no%TYPE;
   l_flight_date flight.flight_date%TYPE;
   l_airport_code CHAR(3);
   l_flts_time flight.flight_sch_time%TYPE;
   l_flight_status flight.flight_status%TYPE;
   l_process_name flight.process_name%TYPE;
   l_dcs_id flight.dcsid%TYPE;
   l_subunit_name flight.subunit_name%TYPE;
   l_depsecs CHAR(17);
   l_dcs_flight_no flight.dcs_flight_no%TYPE;
   l_last_pax_sno CHAR(3);
   l_bvis_subhead flight.bvis_subheader%TYPE;
   l_schd_arrival_time flight_info_inbound.schd_arrival_time%TYPE;
   l_unit_name flight.unit_name%TYPE;
BEGIN

   BEGIN

      SELECT db_value INTO populate FROM db_config WHERE
         db_item = 'EXPECTED_FLOW_RATE';

   EXCEPTION

      WHEN OTHERS THEN
         null;
   END;
   
   IF populate = 'Y' THEN

      :new.terminal := rtrim(ltrim(:new.gate));

   END IF;
   
   -- Instantiate local variables.
   l_flight_no := :new.flight_no;
   l_flight_date := :new.flight_date;
   l_airport_code := :new.airport_code;
   l_flight_status := 'IB';
   l_process_name := 'FIDS';
   l_dcs_id := '1';
   l_unit_name := '1';
   l_subunit_name := '1';   
   l_schd_arrival_time := :new.schd_arrival_time;
   l_depsecs := TO_CHAR(l_schd_arrival_time, 'DD-MON-YYYY HH24:MI');
   l_flts_time := TO_CHAR(l_schd_arrival_time, 'HH24MI');
   l_flight_id := NULL;
   l_dcs_flight_no := NULL;
   l_last_pax_sno := '000';
   l_bvis_subhead := NULL;
   
   -- Create the flight if it does not already exist in the flight table.
   IF NVL(:new.source,'?') != 'ARCHIVE' THEN
      modify_flight(l_flight_no, l_flight_date, l_airport_code, l_flts_time, 
         l_flight_status, l_process_name, l_unit_name, l_dcs_id, l_subunit_name, l_depsecs, 
      l_dcs_flight_no, l_flight_id, l_last_pax_sno, l_bvis_subhead);
   END IF;

END;
/
