PROMPT CREATE OR REPLACE PACKAGE flightinfo 
CREATE OR REPLACE PACKAGE flightinfo IS

/*****************************************************************************
*
* Filename         - $Workfile:   flightinfo.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         - schema flightinfo package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/

        mach_interface constant number := 1;
        fasl_interface constant number := 2;
        hel_interface  constant number := 3;
        prg_interface  constant number := 4;
        yvr_interface  constant number := 5; -- Mik 06/11/02
        lba_interface  constant number := 6;
        lhr_interface  constant number := 7;
        bara_interface constant number := 8;
        dxb_interface  constant number := 10;


        procedure maintain_flight (
                p_flight_no     in    flight.flight_no%type,
                p_dep_time      in    date,
                p_std           in    flight.flight_sch_time%type,
                p_airline       in    flight.carrier_alpha_code%type,
                p_dest          in    flight_dests.flight_destination%type,
                p_flight_status IN    flight.flight_status%type,
                force_mapping   in    boolean,
                departing_airport_code in site_info.airport_code%type DEFAULT NULL,
                p_interface_id      in      flight_info.interface_id%type
        );

        procedure create_flight (
                p_flight_no     in    flight.flight_no%type,
                p_dep_time      in    date,
                p_std           in    flight.flight_sch_time%type,
                p_airline       in    flight.carrier_alpha_code%type,
                p_fltid         out   flight.flight_id%type,
                p_flight_status IN    flight.flight_status%type,
                departing_airport_code in site_info.airport_code%type DEFAULT NULL,
                p_interface_id      in      flight_info.interface_id%type
        );

        procedure update_std (
                p_flight_id     in    flight.flight_id%type,
                p_dep_time      in    date,
                p_std           in    flight.flight_sch_time%type
        );

        procedure identify_flight (
                fltno_in      in    flight.flight_no%type,
                fltdt_in      in    flight.flight_date%type,
                force_mapping in    boolean,
                fltno_out     out   flight.flight_no%type,
                fltdt_out     out   flight.flight_date%type,
                fltid_out     out   flight.flight_id%type,
                retcode       out   number,
                departing_airport_code in site_info.airport_code%type DEFAULT NULL,
                p_interface_id      in      flight_info.interface_id%type
        );

        procedure add_dest(
                p_flight_id   in   flight.flight_id%type,
                p_dest        in   flight_dests.flight_destination%type
        );

        procedure maintain_dest(
                p_flight_id   in   flight.flight_id%type,
                p_dest        in   flight_dests.flight_destination%type
        );

        procedure maintain_hel_dest(
                p_flight_id   in   flight.flight_id%type,
                p_dest        in   flight_dests.flight_destination%type
        );

        procedure incoming_record(
                p_flight_no         in      flight.flight_no%type,
                p_flight_date       in      date,
                p_gate              in      flight_info.gate%type,
                p_position          in      flight_info.apron%type,
                p_effective_std     in      flight_info.std%type,
                p_block_off         in      flight_info.block_off%type,
                p_direction         in      flight_info.direction%type,
                p_interface_id      in      flight_info.interface_id%type,
                p_source_record_id  in      flight_info.raw_data_id%type,
                force_mapping       in      boolean,
                departing_airport_code in site_info.airport_code%type DEFAULT NULL
        );
      -- Added for VIE Fids
        PROCEDURE incoming_record(
                p_flight_no            IN flight.flight_no%TYPE,
                p_position             IN flight_info.apron%TYPE,
                p_direction            IN flight_info.direction%TYPE,
                p_schedule             IN VARCHAR2,
                p_plan_date            IN DATE,
                p_airline              IN flight.carrier_alpha_code%TYPE
        );

      -- Added for bara Fids

        procedure maintain_flight (
                p_flight_no     in    flight.flight_no%type,
                p_dep_time      in    date,
                p_std           in    flight.flight_sch_time%type,
                p_airline       in    flight.carrier_alpha_code%type,
                p_destinations  in    destination_list,
                p_flight_status IN    flight.flight_status%type,
                force_mapping   in    boolean,
                p_airport_code IN site_info.airport_code%type DEFAULT NULL,
                p_interface_id      in      flight_info.interface_id%type
        );

        procedure add_dest (p_flight_id in flight.flight_id%type, p_destinations in destination_list);

        procedure maintain_dest (p_flight_id in flight.flight_id%type, p_destinations in destination_list);

        procedure incoming_record (
                p_flight_no         in      flight.flight_no%type,
                p_flight_date       in      date,
                p_trip_date         in      flight_info.trip_date%type,
                p_trip_number       in      flight_info.trip_number%type,
                p_gate              in      flight_info.gate%type,
                p_position          in      flight_info.apron%type,
                p_effective_std     in      flight_info.std%type,
                p_block_off         in      flight_info.block_off%type,
                p_direction         in      flight_info.direction%type,
                p_interface_id      in      flight_info.interface_id%type,
                p_source_record_id  in      flight_info.raw_data_id%type,
                p_schd_flight_time  in      flight_info.SCHD_FLIGHT_TIME%type,
                p_est_flight_time   in      flight_info.EST_FLIGHT_TIME%type,
                p_act_flight_time   in      flight_info.ACT_FLIGHT_TIME%type,
                p_airport_code      in      site_info.airport_code%type,
                p_aircraft_reg      in      flight_info.AIRCRAFT_REGISTRATION%type,
                p_aircaft_type      in      flight_info.AIRCRAFT_TYPE%type,
                p_prev_airport      in      flight_info_inbound.PREV_AIRPORT%type,
                p_prev_departure_time in    flight_info_inbound.PREV_DEPARTURE_TIME%type,
                force_mapping       in      boolean,
                p_terminal          in      flight_info.TERMINAL%type
        );


        PROCEDURE FIDS ( p_carrier_code          IN flight.carrier_alpha_code%TYPE
                        ,p_flight_no             IN VARCHAR2
                        ,p_flight_date           IN DATE
                        ,p_trip_date		         IN flight_info.trip_date%TYPE
                        ,p_trip_number           IN flight_info.trip_number%TYPE
                        ,p_schd_flight_time      IN flight_info.schd_flight_time%TYPE
                        ,p_est_flight_time       IN flight_info.est_flight_time%TYPE
                        ,p_act_flight_time       IN flight_info.act_flight_time%TYPE
                        ,p_airport_code          IN flight_info.airport_code%TYPE
                        ,p_direction             IN VARCHAR2 DEFAULT 'O'
                        ,p_destinations          IN VARCHAR2
                        ,p_gate                  IN VARCHAR2
                        ,p_apron                 IN VARCHAR2
                        ,p_aircraft_type         IN flight_info.aircraft_type%TYPE
                        ,p_aircraft_registration IN flight_info.aircraft_registration%TYPE
                        ,p_codeshare_flight      IN CHAR
                        ,p_primary_carrier       IN CHAR
                        ,p_primary_flight_no     IN CHAR
                        ,p_primary_flight_date   IN DATE
                        ,p_prev_airport          IN flight_info_inbound.prev_airport%TYPE
                        ,p_prev_departure_time   IN flight_info_inbound.prev_departure_time%TYPE
                        ,p_flight_status         IN flight.flight_status%TYPE DEFAULT '-'
                        ,p_message_number        IN NUMBER
                        ,p_interface_id          IN NUMBER
        );

        /* Exception declarations */
        error_null_airport_code        EXCEPTION;
        error_null_carrier_code        EXCEPTION;
        error_null_flight_no           EXCEPTION;
        error_null_flight_date         EXCEPTION;
        error_null_schd_flight_time    EXCEPTION;
        error_null_interface_id        EXCEPTION;
        error_null_direction           EXCEPTION;

        /* Pragma Exception error numbers */
        PRAGMA EXCEPTION_INIT(error_null_airport_code,    -20774);  -- references constants above
        PRAGMA EXCEPTION_INIT(error_null_carrier_code,    -20775);
        PRAGMA EXCEPTION_INIT(error_null_flight_no,       -20776);
        PRAGMA EXCEPTION_INIT(error_null_flight_date,     -20777);
        PRAGMA EXCEPTION_INIT(error_null_schd_flight_time,-20778);
        PRAGMA EXCEPTION_INIT(error_null_interface_id,    -20779);
        PRAGMA EXCEPTION_INIT(error_null_direction,       -20780);

end flightinfo;
/
PROMPT CREATE OR REPLACE PACKAGE BODY flightinfo 
CREATE OR REPLACE PACKAGE BODY flightinfo IS

procedure maintain_flight (
        p_flight_no     in    flight.flight_no%type,
        p_dep_time      in    date,
        p_std           in    flight.flight_sch_time%type,
        p_airline       in    flight.carrier_alpha_code%type,
        p_dest          in    flight_dests.flight_destination%type,
        p_flight_status IN    flight.flight_status%type,
        force_mapping   in    boolean,
        departing_airport_code IN site_info.airport_code%TYPE DEFAULT NULL,
        p_interface_id      in      flight_info.interface_id%type
)
is
        found boolean;
        departure_time date;

        actual_fltno flight.flight_no%type;
        actual_fltdt flight.flight_date%type;
        actual_fltid flight.flight_id%type;
        rc number;
        psiteid site_info.site_id%type;
        ret     number(10);
        dbvalue db_config.db_value%type;
        depcode site_info.airport_code%type;
begin

    IF  departing_airport_code IS NULL THEN
      IF trim(config.get_value('MULTI_STATION')) = 'N' THEN
        dbvalue := trim(config.get_value ('DEFAULT_AIRPORT'));
        depcode := trim(multi_site.get_airport_code(to_number(dbvalue)));
      ELSE
        depcode := trim (config.get_value ('FIDS_IF_' || p_interface_id));
      END IF;
    ELSE
      depcode := departing_airport_code;
    END IF;


        identify_flight (
                p_flight_no,
                to_char(p_dep_time,'DDMON'),
                force_mapping,
                actual_fltno,
                actual_fltdt,
                actual_fltid,
                rc,
                depcode,
                p_interface_id
        );


        if ( rc = 0 ) then
                found := TRUE;
        else
                found := FALSE;
        end if;

        if ( found = FALSE ) then

                /*
                 * Create the flight.
                 */
                flightinfo.create_flight( actual_fltno, p_dep_time, nvl(p_std,'-'),
                        p_airline, actual_fltid, p_flight_status, depcode, p_interface_id );
                flightinfo.add_dest( actual_fltid, p_dest );

        else

                /*
                 * The flight already exists, we will update it with
                 * the relevant information.
                 */
                flightinfo.update_std( actual_fltid, p_dep_time, nvl(p_std,'-') );
                flightinfo.maintain_dest( actual_fltid, p_dest );

        end if;

end maintain_flight;


PROCEDURE create_flight (
    p_flight_no     IN    flight.flight_no%TYPE,
    p_dep_time      IN    DATE,
    p_std           IN    flight.flight_sch_time%TYPE,
    p_airline       IN    flight.carrier_alpha_code%TYPE,
    p_fltid         OUt   flight.flight_id%TYPE,
    p_flight_status IN    flight.flight_status%type,
    departing_airport_code IN site_info.airport_code%TYPE DEFAULT NULL,
    p_interface_id      in      flight_info.interface_id%type
)
IS
    found number;
    fltid            flight.flight_id%type;
    p_process        flight.process_name%type  := 'FIDS';
    p_unit           flight.unit_name%type     := '1';
    p_subunit        flight.subunit_name%type  := '1';
    p_dcsid          flight.dcsid%type         := '1';
    dbvalue          db_config.db_value%type;
    retcode          number(10)                := 0;
    depcode          site_info.airport_code%type;
    sched_time       flight.flight_sch_time%TYPE := p_std;

BEGIN
    IF  departing_airport_code IS NULL THEN
      IF trim(config.get_value('MULTI_STATION')) = 'N' THEN
        dbvalue := trim(config.get_value ('DEFAULT_AIRPORT'));
        depcode := trim(multi_site.get_airport_code(to_number(dbvalue)));
      ELSE
        depcode := trim (config.get_value ('FIDS_IF_' || p_interface_id));
      END IF;
    ELSE
      depcode := departing_airport_code;
    END IF;


-- Add the flight.

    add_flight (
            p_flight_no,
            to_char(p_dep_time,'DDMON'),
            depcode,
            p_std,
            p_flight_status,
            p_process,
            p_unit,
            p_dcsid,
            p_subunit,
            mws_web.departure_time(to_char(p_dep_time,'DDMON'),sched_time),
            NULL,
            fltid,
            NULL -- bvis subheader
        );

    p_fltid := fltid;
END create_flight;


procedure update_std (
        p_flight_id     in    flight.flight_id%type,
        p_dep_time      in    date,
        p_std           in    flight.flight_sch_time%type
)
is
begin


        if ( rtrim(p_std) = '-' ) then
                return;
        end if;



        end loop;

end update_std;


procedure identify_flight (
        fltno_in      in    flight.flight_no%type,
        fltdt_in      in    flight.flight_date%type,
        force_mapping in    boolean,
        fltno_out     out   flight.flight_no%type,
        fltdt_out     out   flight.flight_date%type,
        fltid_out     out   flight.flight_id%type,
        retcode       out   number,
        departing_airport_code IN site_info.airport_code%TYPE DEFAULT NULL,
        p_interface_id      in      flight_info.interface_id%type
)
is
        l_fltid flight.flight_id%type := 0;
        l_fltno flight.flight_no%type;
        l_fltdt flight.flight_date%type;

        old_carrier carrier_lookup.alpha_code%type;
        new_carrier carrier_lookup.alpha_code%type;
        description1 carrier_map.airline_des1%type;
        description2 carrier_map.airline_des2%type;
        depcode          site_info.airport_code%type;
        dbvalue          db_config.db_value%type;
        ret              number(10)           := 0;
        psiteid          flight.site_id%type;

begin

    IF  departing_airport_code IS NULL THEN
      IF trim(config.get_value('MULTI_STATION')) = 'N' THEN
        dbvalue := trim(config.get_value ('DEFAULT_AIRPORT'));
        depcode := trim(multi_site.get_airport_code(to_number(dbvalue)));
      ELSE
        depcode := trim (config.get_value ('FIDS_IF_' || p_interface_id));
      END IF;
    ELSE
      depcode := departing_airport_code;
    END IF;

    psiteid := multi_site.get_site_id(depcode);

        /*
         * NOTES
         * if force_mapping is passed in as TRUE, then in the
         * event that the flight does not exist the output
         * parameters will be set to the mapped version when a
         * mapping exists, to be used later for flight creation.
         */

        l_fltno := fltno_in;
        l_fltdt := fltdt_in;

        /*
         * First we try to find the flight by using the original
         * flight number.
         */

        flight_id( l_fltno, l_fltdt, psiteid, l_fltid );

        if ( l_fltid = 0 ) then

                /*
                 * We didn't find the flight by using it's original flight
                 * number. Now we will try using the alternate airline code
                 * (if available).
                 */

                old_carrier := substr( fltno_in, 1, 3 );

                /*
                 * If forced mapping is in operation (mapping incoming FIDS flights)
                 * then we use a special one-way translation as we don't want to map
                 * both ways (i.e. we don't want to map XX to YY and also map YY to XX).
                 */
                if ( force_mapping ) then
                        carrier.translate_carrier(
                                old_carrier,
                                new_carrier
                        );
                else
                        carrier.match_carrier(
                                old_carrier,
                                new_carrier,
                                description1,
                                description2
                        );


                end if;


                if ( old_carrier != new_carrier ) then
                        /*
                         * If we found an alternate airline code, try finding
                         * the flight.
                         */

                        l_fltno := rpad(new_carrier,3) || substr(fltno_in, 4);
                        flight_id( l_fltno, l_fltdt, psiteid, l_fltid );

                end if;

        end if;

        /*
         * Assign output parameters.
         */
        if ( l_fltid > 0 ) then

                /*
                 * We found a flight - copy the flight information
                 * to the output parameters.
                 */

                fltno_out := l_fltno;
                fltdt_out := l_fltdt;
                fltid_out := l_fltid;
                retcode := 0;

        else

                /*
                 * We did not find a flight. return error code -139
                 * (UNKNOWN_FLIGHT).
                 */

                /*
                 * Output parameters will contain the flight number
                 * to be used in creating the flight.
                 */
                if ( force_mapping ) then
                        fltno_out := l_fltno;
                        fltdt_out := l_fltdt;
                else
                        fltno_out := fltno_in;
                        fltdt_out := fltdt_in;
                end if;

                fltid_out := NULL;
                retcode := -139;

        end if;


end identify_flight;


procedure add_dest(
        p_flight_id   in   flight.flight_id%type,
        p_dest        in   flight_dests.flight_destination%type
)

is

begin

        if ( rtrim(p_dest) is null ) then
                return;
        end if;

        /*
         * Add the flight_dest record with source of 'F', this
         * will then be updated when an FOM arrives.
         */

        insert into flight_dests
                ( flight_id, flight_destination, dest_order, dest_source )
        values
                ( p_flight_id, p_dest, 1, 'F' );

end add_dest;



procedure maintain_dest(
        p_flight_id   in   flight.flight_id%type,
        p_dest        in   flight_dests.flight_destination%type
)

is
        l_dest_wrapper DESTINATION_LIST;
        found number := 0;

begin
        l_dest_wrapper := destination_list();  /* CA2535 - prevent dupliacte flight_dests  */
        
        if ( rtrim(p_dest) is null ) then
                return;
        end if;
        
        l_dest_wrapper.extend(1);
        l_dest_wrapper(1) := p_dest;

        flightinfo.maintain_dest(p_flight_id, l_dest_wrapper);

end maintain_dest;

procedure maintain_hel_dest(
        p_flight_id   in   flight.flight_id%type,
        p_dest        in   flight_dests.flight_destination%type
)
is

        max_dest_order          flight_dests.dest_order%type;

begin

        if ( rtrim(p_dest) is null ) then
                return;
        end if;


        /*
         * Get the maximum dest_order from flight_dests
         */
        select
                nvl(max(dest_order), 0)
        into
                max_dest_order
        from
                flight_dests
        where
                flight_id   = p_flight_id and
                dest_source = 'F';

        /* Increment max_dest_order by 1 */
        max_dest_order := max_dest_order + 1;

        insert into
                flight_dests (
                        flight_id,
                        flight_destination,
                        dest_order,
                        dest_source
                ) values (
                        p_flight_id,
                        p_dest,
                        max_dest_order,
                        'F'
                );
end maintain_hel_dest;

procedure incoming_record (
        p_flight_no         in      flight.flight_no%type,
        p_flight_date       in      date,
        p_gate              in      flight_info.gate%type,
        p_position          in      flight_info.apron%type,
        p_effective_std     in      flight_info.std%type,
        p_block_off         in      flight_info.block_off%type,
        p_direction         in      flight_info.direction%type,
        p_interface_id      in      flight_info.interface_id%type,
        p_source_record_id  in      flight_info.raw_data_id%type,
        force_mapping       in      boolean,
        departing_airport_code IN site_info.airport_code%TYPE DEFAULT NULL
)
is
        actual_fltno flight.flight_no%type;
        actual_fltdt flight.flight_date%type;
        actual_fltid flight.flight_id%type;
        rc number;

begin
        
        identify_flight (
                p_flight_no,
                to_char(p_flight_date,'DDMON'),
                force_mapping,
                actual_fltno,
                actual_fltdt,
                actual_fltid,
                rc,
                departing_airport_code,
                p_interface_id
        );

        delete from
                flight_info
        where
                flight_no = actual_fltno and
                flight_date = to_char(p_flight_date,'DDMON') and
                interface_id = p_interface_id;

        insert into flight_info (
                flight_no,
                flight_date,
                gate,
                apron,
                std,
                block_off,
                direction,
                interface_id,
                raw_data_id,
                departure_time,
                trip_date
        ) values (
                actual_fltno,
                to_char(p_flight_date,'DDMON'),
                p_gate,
                p_position,
                p_effective_std,
                p_block_off,
                p_direction,
                p_interface_id,
                p_source_record_id,
                p_flight_date,
                p_flight_date
        );
end incoming_record;

/* This procedure is used solely for Vienna MACH FIDS and is called by 
   add_flt_info_in.pc. It is used to deal with incoming flights.  The
   processing is similar to that of mach_fids.handle_flight_info which is
   called by add_flt_info_out.pc.
*/

PROCEDURE incoming_record(p_flight_no            IN flight.flight_no%TYPE,
                          p_position             IN flight_info.apron%TYPE,
                          p_direction            IN flight_info.direction%TYPE,
                          p_schedule             IN VARCHAR2,
                          p_plan_date            IN DATE,
                          p_airline              IN flight.carrier_alpha_code%TYPE)
  IS
                          
  actual_fltno   flight.flight_no%TYPE;
  actual_fltdt   flight.flight_date%TYPE;
  actual_fltid   flight.flight_id%TYPE;
  norm_flight_no flight.flight_no%TYPE;
  effective_std  mach_flight_info.schedule%TYPE;
  std_conv       CHAR(25);
  digits         CHAR(10) := '0123456789';
  p_airport_code site_info.airport_code%TYPE;
  l_interface_id flight_info.interface_id%TYPE;
  force_mapping  BOOLEAN DEFAULT FALSE;
  rc             NUMBER;

BEGIN

  l_interface_id := flightinfo.mach_interface;
  p_airport_code := trim(config.get_value ('FIDS_DEFAULT_AIRPORT'));
  
  /* Convert incoming MACH data TO THE format required 
     FOR THE generic flight_info TABLE.
  
      * Work out the value to use for STD.
      * If schedule is given, use it.
      * otherwise use '-' for unknown.
      */

  IF rtrim(p_schedule) IS NOT NULL THEN 
     effective_std := p_schedule;
  ELSE 
    effective_std := '-';
  END IF;

  /*
    * Codeshare package has a convenient routine to make
    * a flight number from mach components.
  */
  codeshare.compose_flight_no(p_airline, 
                              p_flight_no, 
                              norm_flight_no);
                              
  /*
    * Make a date+time value from the text date, and STD if numeric.
  */
  std_conv := rtrim(to_char(p_plan_date, 'YYYYMMDD'));
  
  IF (instr(digits, substr(effective_std, 1, 1)) != 0 AND 
      instr(digits, substr(effective_std, 2, 1)) != 0 AND 
      instr(digits, substr(effective_std, 3, 1)) != 0 AND 
      instr(digits, substr(effective_std, 4, 1)) != 0) THEN 
      std_conv := rtrim(std_conv) || ' ' || rtrim(effective_std); 
  ELSE 
      std_conv := rtrim(std_conv) || ' 0000'; 
  END IF;

  identify_flight(p_flight_no, 
                  to_char(p_plan_date, 'DDMON'),
                  force_mapping, 
                  actual_fltno, 
                  actual_fltdt, 
                  actual_fltid, 
                  rc, 
                  p_airport_code, 
                  l_interface_id);

  DELETE FROM flight_info_inbound 
  WHERE flight_no = actual_fltno 
  AND flight_date = std_conv 
  AND interface_id = l_interface_id;

  INSERT INTO flight_info_inbound(flight_no, 
                                  flight_date, 
                                  trip_date, 
                                  trip_number, 
                                  airport_code, 
                                  schd_arrival_time, 
                                  apron, 
                                  interface_id)
                          VALUES( actual_fltno, 
                                  to_char (p_plan_date,'DDMON'),
                                  to_date(std_conv,'YYYYMMDD HH24MI'),                                   
                                  0, 
                                  p_airport_code, 
                                  to_date(std_conv,'YYYYMMDD HH24MI'), 
                                  p_position, 
                                  l_interface_id);
                                    
END incoming_record;

-- Added for BARA FIDs

  PROCEDURE maintain_flight (
    p_flight_no            in  flight.flight_no%type,
    p_dep_time             in  date,
    p_std                  in  flight.flight_sch_time%type,
    p_airline              in  flight.carrier_alpha_code%type,
    p_destinations         in  destination_list,
    p_flight_status        in  flight.flight_status%type,
    force_mapping          in  boolean,
    p_airport_code         in  site_info.airport_code%type DEFAULT NULL,
    p_interface_id         in  flight_info.interface_id%type
  ) IS
    flight_found boolean;

    departure_time date;

    l_flight_no flight.flight_no%type;
    l_flight_date flight.flight_date%type;
    l_flight_id flight.flight_id%type;
    l_user_id   tagtrak_users.user_id%type;
    rc number;

    psiteid site_info.site_id%type;
    ret     number(10);
    dbvalue db_config.db_value%type;
    l_airport_code site_info.airport_code%type;

    l_flight_status flight.flight_status%type;

    l_flight_status_retcode integer; -- Status code from modify_flight_status (not used)
  BEGIN

    l_flight_status := p_flight_status;

    IF p_airport_code IS NULL THEN
      IF trim(config.get_value('MULTI_STATION')) = 'N' THEN
        dbvalue := trim(config.get_value ('DEFAULT_AIRPORT'));
        l_airport_code := trim(multi_site.get_airport_code(to_number(dbvalue)));
      ELSE
        l_airport_code := trim (config.get_value ('FIDS_IF_' || p_interface_id));
      END IF;
    ELSE
      l_airport_code := p_airport_code;
    END IF;

    identify_flight (
            p_flight_no,
            to_char(p_dep_time,'DDMON'),
            force_mapping,
            l_flight_no,
            l_flight_date,
            l_flight_id,
            rc,
            l_airport_code,
            p_interface_id);

    IF (rc = 0) THEN
      flight_found := TRUE;
    ELSE
      flight_found := FALSE;
    END IF;

    IF (flight_found = FALSE) THEN
    -- Create the flight.

      flightinfo.create_flight (
          l_flight_no,
          p_dep_time,
          nvl(p_std,'-'),
          p_airline,
          l_flight_id,
          l_flight_status,
          l_airport_code,
          p_interface_id
      );

      flightinfo.add_dest (l_flight_id, p_destinations);
    ELSE
    -- The flight already exists, we will update it with the relevant information.

      flightinfo.update_std (l_flight_id, p_dep_time, nvl(p_std,'-'));
      flightinfo.maintain_dest (l_flight_id, p_destinations);

      BEGIN

           -- Get auto user name - this is used when creating a flight status audit entry
           SELECT user_id INTO l_user_id FROM tagtrak_users WHERE TRIM(loginname) = TRIM(multi_site.get_auto_loginname(l_airport_code));

      EXCEPTION

         WHEN OTHERS THEN
            -- Ensure logging errors don't cause failure
            null;

      END;

      modify_flight_status (l_flight_id, p_flight_no, p_dep_time, p_interface_id,
                               l_user_id, multi_site.get_auto_location(l_airport_code), l_flight_status, l_flight_status_retcode);

    END IF;
  END maintain_flight;


  PROCEDURE add_dest (p_flight_id in flight.flight_id%type, p_destinations in destination_list) is
    element binary_integer;
    l_destination flight_dests.flight_destination%type;
  BEGIN

    -- if no destinations passed in make no changes
    IF (p_destinations.count() = 0) then
       RETURN;
    END IF;

    FOR element in p_destinations.first()..p_destinations.last() loop
      l_destination := p_destinations (element);

      BEGIN
      -- Add the flight_dest record with source of 'F', this will then be updated when an FOM arrives.
        INSERT INTO flight_dests
          (flight_id, flight_destination, dest_order, dest_source)
        VALUES
          (p_flight_id, l_destination, element, 'F' );

      EXCEPTION
      WHEN OTHERS THEN
          raise_application_error (-20000, 'Exception raised inserting into flight_dests for flight_id: '
              || p_flight_id || ' flight_destination: ' || l_destination || ' element: ' || element || '. ' || SQLERRM);
      END;
    END LOOP;
  END add_dest;


  PROCEDURE maintain_dest (p_flight_id IN flight.flight_id%type, p_destinations IN destination_list) IS
    l_dest_order number := 0;
    l_destination flight_dests.flight_destination%type;

    CURSOR c_bsm_dests IS
      SELECT flight_id, flight_destination
      FROM flight_dests
      WHERE flight_id = p_flight_id
      AND flight_destination NOT IN (SELECT column_value FROM TABLE (CAST (p_destinations AS destination_list)));
  BEGIN

  -- if no destinations passed in make no changes
  if (p_destinations.count() = 0) then
     return;
  end if;

  -- Delete any destinations on the FLIGHT_DEST table that exist for the flight
    BEGIN
      DELETE FROM
        flight_dests
      WHERE
        flight_id = p_flight_id
      AND
        (flight_destination IN (SELECT column_value FROM TABLE (cast (p_destinations AS destination_list))) OR
         dest_source = 'F');    -- UAS/FALCP/CA/00728

    EXCEPTION
      WHEN others THEN
        raise_application_error (-20000, 'Exception raised inserting into  flight_dests for flight_id: '
            || p_flight_id || ' flight_destination: ' || l_destination || '. ' || SQLERRM);
        raise;
    END;

  -- Insert data from p_destinations (in order to gain the right ordering)
    add_dest (p_flight_id, p_destinations);

  -- Reorder previously unmatched destinations (that were not deleted)
    l_dest_order := p_destinations.count;

    BEGIN
      FOR r_bsm_dests in c_bsm_dests LOOP
        l_dest_order := l_dest_order + 1;

        update flight_dests
        set dest_order = l_dest_order
        where flight_id = r_bsm_dests.flight_id
        and flight_destination = r_bsm_dests.flight_destination;

        IF SQL%NOTFOUND THEN
          RAISE_APPLICATION_ERROR (-20000, 'No data found when updating flight_dests.dest_order for flight_id: '
              || p_flight_id || ' flight_destination: ' || r_bsm_dests.flight_destination);
        END IF;
      END LOOP;

    EXCEPTION
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20000, 'Exception raised updating the flight_dests.dest_order for flight_id: ' || p_flight_id || '. ' || SQLERRM);
        RAISE;
    END;

  END   maintain_dest;

  PROCEDURE incoming_record (
    p_flight_no         in      flight.flight_no%type,
    p_flight_date       in      date,
    p_trip_date         in      flight_info.trip_date%type,
    p_trip_number       in      flight_info.trip_number%type,
    p_gate              in      flight_info.gate%type,
    p_position          in      flight_info.apron%type,
    p_effective_std     in      flight_info.std%type,
    p_block_off         in      flight_info.block_off%type,
    p_direction         in      flight_info.direction%type,
    p_interface_id      in      flight_info.interface_id%type,
    p_source_record_id  in      flight_info.raw_data_id%type,
    p_schd_flight_time  in      flight_info.SCHD_FLIGHT_TIME%type,
    p_est_flight_time   in      flight_info.EST_FLIGHT_TIME%type,
    p_act_flight_time   in      flight_info.ACT_FLIGHT_TIME%type,
    p_airport_code      in      site_info.airport_code%type,
    p_aircraft_reg      in      flight_info.AIRCRAFT_REGISTRATION%type,
    p_aircaft_type      in      flight_info.AIRCRAFT_TYPE%type,
    p_prev_airport      in      flight_info_inbound.PREV_AIRPORT%type,
    p_prev_departure_time in    flight_info_inbound.PREV_DEPARTURE_TIME%type,
    force_mapping       in      boolean,
    p_terminal          in      flight_info.terminal%type
  ) IS
    rc          number;

  BEGIN
    IF p_direction = 'O' THEN
      delete from flight_info
      where airport_code = p_airport_code
      and   flight_no    = p_flight_no
      and   trip_date    = p_trip_date
      and   trip_number  = p_trip_number;

      insert into flight_info (
            flight_no
          , flight_date
          , trip_date
          , trip_number
          , airport_code
          , gate
          , apron
          , std
          , block_off
          , direction
          , interface_id
          , raw_data_id
          , departure_time
          , schd_flight_time
          , est_flight_time
          , act_flight_time
          , aircraft_registration
          , aircraft_type
          , terminal

      ) values (
            p_flight_no
          , to_char (p_flight_date,'DDMON')
          , p_trip_date
          , p_trip_number
          , p_airport_code
          , p_gate
          , p_position
          , p_effective_std
          , p_block_off
          , p_direction
          , p_interface_id
          , p_source_record_id
          , p_flight_date
          , p_schd_flight_time
          , p_est_flight_time
          , p_act_flight_time
          , p_aircraft_reg
          , p_aircaft_type
          , p_terminal
      );

    ELSIF p_direction = 'I' THEN
      delete from flight_info_inbound
      where airport_code = p_airport_code
      and   flight_no    = p_flight_no
      and   flight_date  = to_char(p_flight_date, 'DDMON');

      insert into flight_info_inbound (
            flight_no
          , flight_date
          , trip_date
          , trip_number
          , airport_code
          , schd_arrival_time
          , est_arrival_time
          , act_arrival_time
          , gate
          , apron
          , block_off
          , aircraft_type
          , aircraft_registration
          , prev_airport
          , prev_departure_time
          , interface_id
          , raw_data_id
          , terminal
      ) values (
            p_flight_no
          , to_char (p_flight_date,'DDMON')
          , p_trip_date
          , p_trip_number
          , p_airport_code
          , p_schd_flight_time
          , p_est_flight_time
          , p_act_flight_time
          , p_gate
          , p_position
          , p_block_off
          , p_aircaft_type
          , p_aircraft_reg
          , p_prev_airport
          , p_prev_departure_time
          , p_interface_id
          , p_source_record_id
          , p_terminal
      );
    END IF;
  END incoming_record;


  PROCEDURE FIDS ( p_carrier_code         IN flight.carrier_alpha_code%TYPE
                 ,p_flight_no             IN VARCHAR2
                 ,p_flight_date           IN DATE
                 ,p_trip_date		          IN flight_info.trip_date%TYPE
                 ,p_trip_number           IN flight_info.trip_number%TYPE
                 ,p_schd_flight_time      IN flight_info.schd_flight_time%TYPE
                 ,p_est_flight_time       IN flight_info.est_flight_time%TYPE
                 ,p_act_flight_time       IN flight_info.act_flight_time%TYPE
                 ,p_airport_code          IN flight_info.airport_code%TYPE
                 ,p_direction             IN VARCHAR2 DEFAULT 'O'
                 ,p_destinations          IN VARCHAR2
                 ,p_gate                  IN VARCHAR2
                 ,p_apron                 IN VARCHAR2
                 ,p_aircraft_type         IN flight_info.aircraft_type%TYPE
                 ,p_aircraft_registration IN flight_info.aircraft_registration%TYPE
                 ,p_codeshare_flight      IN CHAR
                 ,p_primary_carrier       IN CHAR
                 ,p_primary_flight_no     IN CHAR
                 ,p_primary_flight_date   IN DATE
                 ,p_prev_airport          IN flight_info_inbound.prev_airport%TYPE
                 ,p_prev_departure_time   IN flight_info_inbound.prev_departure_time%TYPE
                 ,p_flight_status         IN flight.flight_status%TYPE DEFAULT '-'
                 ,p_message_number        IN NUMBER
                 ,p_interface_id          IN NUMBER) IS

    /* Constant Declarations */
    c_DEBUG                 CONSTANT VARCHAR2(1)               := 'Y';
    l_process_name          oracle_error.process_name%TYPE     := 'FLIGHTINFO.FIDS';
    l_error_msg             oracle_error.errror_msg%TYPE       := NULL;
    l_error_code            oracle_error.error_code%TYPE       := NULL;
    l_error_time            oracle_error.error_time%TYPE       := NULL;

    /* Local variables to be set with calling data */
    l_flight_id             flight.flight_id%TYPE              := NULL;
    l_carrier_code          CHAR(3)                            := NULL;
    l_flight_no             CHAR(5)                            := NULL;
    l_flight_date           DATE                               := NULL;
    l_trip_date             flight_info.trip_date%TYPE         := NULL;
    l_trip_number           flight_info.trip_number%TYPE       := NULL;
    l_schd_flight_time      flight_info.schd_flight_time%TYPE  := NULL;
    l_est_flight_time       flight_info.est_flight_time%TYPE   := NULL;
    l_act_flight_time       flight_info.act_flight_time%TYPE   := NULL;
    l_airport_code          flight_info.airport_code%TYPE      := NULL;
    l_direction             CHAR(1)                            := NULL;
    l_destinations          DESTINATION_LIST                   := DESTINATION_LIST();
    l_terminal              flight_info.terminal%TYPE          := NULL;
    l_gate                  VARCHAR2(5)                        := NULL;
    l_apron                 VARCHAR2(5)                        := NULL;
    l_aircraft_type         flight_info.aircraft_type%TYPE          := NULL;
    l_aircraft_registration flight_info.aircraft_registration%TYPE  := NULL;
    l_codeshare_flight      CHAR(1)                            := NULL;
    l_primary_carrier       CHAR(3)                            := NULL;
    l_primary_flight_no     CHAR(5)                            := NULL;
    l_primary_flight_date   DATE                               := NULL;
    l_prev_airport          CHAR(3)                            := NULL;
    l_prev_departure_time   flight_info_inbound.prev_departure_time%TYPE := NULL;
    l_flight_status         flight.flight_status%TYPE          := NULL;
    l_message_number        NUMBER                             := NULL;
    l_interface_id          NUMBER                             := p_interface_id;

    /* Instantiate and initialise fids_inteface with nulls */
     fids 	fids_interface := fids_interface(l_flight_id
                                            ,l_airport_code
                                            ,l_carrier_code
                                            ,l_flight_no
                                            ,l_flight_date
                                            ,l_trip_date
                                            ,l_trip_number
                                            ,l_schd_flight_time
                                            ,l_est_flight_time
                                            ,l_act_flight_time
                                            ,l_direction
                                            ,l_destinations
                                            ,l_flight_status
                                            ,l_terminal
                                            ,l_gate
                                            ,l_apron
                                            ,l_aircraft_type
                                            ,l_aircraft_registration
                                            ,l_codeshare_flight
                                            ,l_primary_carrier
                                            ,l_primary_flight_no
                                            ,l_primary_flight_date
                                            ,l_prev_airport
                                            ,l_prev_departure_time
                                            ,l_message_number);
    BEGIN
     /* Check for null interface_id */
     IF ( p_interface_id IS NULL ) THEN
        RAISE flightinfo.error_null_interface_id;
     END IF;

     /* Initially Set the flight details */
     fids.set_airport_code(p_airport_code);
     fids.set_carrier_code(p_carrier_code);
     fids.set_flight_no   (p_flight_no);
     fids.set_flight_date (p_flight_date);
     fids.set_direction   (p_direction);

     /* Select data from flight_info */
     BEGIN
     fids.select_flight_info ( p_airport_code
                             , p_carrier_code
                             , p_flight_no
                             , p_flight_date
                             , p_direction
                             );
     EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
     END;

     /* Set the appropriate attributes to be modified, after the selection */
     fids.set_destinations         ( p_destinations  );
     fids.set_flight_status        ( p_flight_status);
     fids.set_schd_flight_time     ( p_schd_flight_time );
     fids.set_est_flight_time      ( p_est_flight_time );
     fids.set_act_flight_time      ( p_act_flight_time );
     fids.set_gate                 ( p_gate );
     fids.set_apron                ( p_apron );
     fids.set_trip_date            ( p_trip_date );
     fids.set_trip_number          ( p_trip_number );
     fids.set_aircraft_type        ( p_aircraft_type );
     fids.set_aircraft_registration( p_aircraft_registration );
     fids.set_codeshare_flight     ( p_codeshare_flight );
     fids.set_primary_carrier      ( p_primary_carrier );
     fids.set_primary_flight_no    ( p_primary_flight_no );
     fids.set_primary_flight_date  ( p_primary_flight_date );
     fids.set_prev_airport         ( p_prev_airport );
     fids.set_prev_departure_time  ( p_prev_departure_time );
     fids.set_message_number       ( p_message_number );

     /* Process the flight for the specific FIDS implementation */
     fids.handle_flight_info (l_interface_id) ;

     /* For Debug */
     IF c_DEBUG = 'Y' THEN
      /* Get the destinations for debug */
      l_destinations := fids.get_destinations();

      dbms_output.put_line('************** Data used for FIDS: BEGIN**************');
      dbms_output.put_line('airport_code          ::'||fids.get_airport_code());
      dbms_output.put_line('carrier_code          ::'||fids.get_carrier_code());
      dbms_output.put_line('flight_no             ::'||fids.get_flight_no());
      dbms_output.put_line('flight_date           ::'||fids.get_flight_date());
      dbms_output.put_line('direction             ::'||fids.get_direction());
      IF l_destinations.COUNT > 0 THEN
         FOR i in l_destinations.FIRST..l_destinations.LAST LOOP
             dbms_output.put_line('destinations('||i||')       ::'||l_destinations(i));
         END LOOP;
      END IF;
      dbms_output.put_line('flight_status         ::'||fids.get_flight_status());
      dbms_output.put_line('code_share            ::'||fids.get_codeshare_flight());
      dbms_output.put_line('trip_date             ::'||fids.get_trip_date());
      dbms_output.put_line('trip_number           ::'||fids.get_trip_number());
      dbms_output.put_line('schd_flight_time      ::'||fids.get_schd_flight_time());
      dbms_output.put_line('est_flight_time       ::'||fids.get_est_flight_time());
      dbms_output.put_line('act_flight_time       ::'||fids.get_act_flight_time());
      dbms_output.put_line('terminal              ::'||fids.get_terminal());
      dbms_output.put_line('gate                  ::'||fids.get_gate());
      Dbms_Output.put_line('apron                 ::'||fids.get_apron());
      dbms_output.put_line('aircraft_type         ::'||fids.get_aircraft_type());
      dbms_output.put_line('aircraft_registration ::'||fids.get_aircraft_registration());
      dbms_output.put_line('************** Data used for FIDS: END **************');
    END IF;

    EXCEPTION
       WHEN flightinfo.error_null_airport_code THEN
            IF c_DEBUG = 'Y' THEN
               dbms_output.put_line('Error airport_code is NULL');
            END IF;
            RAISE_APPLICATION_ERROR(pkg_constants.c_null_airport_code,'Error airport_code is NULL');

       WHEN flightinfo.error_null_carrier_code THEN
            IF c_DEBUG = 'Y' THEN
               dbms_output.put_line('Error carrier_code is NULL');
            END IF;
            RAISE_APPLICATION_ERROR(pkg_constants.c_null_carrier_code,'Error carrier_code is NULL');

       WHEN flightinfo.error_null_flight_no THEN
            IF c_DEBUG = 'Y' THEN
               dbms_output.put_line('Error flight_no is NULL');
            END IF;
            RAISE_APPLICATION_ERROR(pkg_constants.c_null_flight_no,'Error flight_no is NULL');

       WHEN flightinfo.error_null_flight_date THEN
            IF c_DEBUG = 'Y' THEN
               dbms_output.put_line('Error flight_date is NULL');
            END IF;
            RAISE_APPLICATION_ERROR(pkg_constants.c_null_flight_date,'Error flight_date is NULL');

       WHEN flightinfo.error_null_schd_flight_time THEN
            IF c_DEBUG = 'Y' THEN
               dbms_output.put_line('Error schd_flight_time is NULL');
            END IF;
            RAISE_APPLICATION_ERROR(pkg_constants.c_null_schd_flight_time,'Error schd_flight_time is NULL');

       WHEN flightinfo.error_null_interface_id THEN
            IF c_DEBUG = 'Y' THEN
               dbms_output.put_line('Error interface_id is NULL');
            END IF;
            RAISE_APPLICATION_ERROR(pkg_constants.c_null_interface_id,'Error interface_id is NULL');

       WHEN flightinfo.error_null_direction THEN
            IF c_DEBUG = 'Y' THEN
               dbms_output.put_line('Error direction is NULL');
            END IF;
            RAISE_APPLICATION_ERROR(pkg_constants.c_null_direction,'Error direction is NULL');

       WHEN OTHERS THEN
          /* Set values */
          l_error_msg  := SQLERRM;
          l_error_code := SQLCODE;
          l_error_time := SYSDATE;

          IF ( c_DEBUG = 'Y' ) THEN
             dbms_output.put_line('------------------------------------');
             dbms_output.put_line('WHEN OTHERS ERROR: '||l_error_code||'-'||l_error_msg);
          END IF;

          /* For Debug */
          IF c_DEBUG = 'Y' THEN
             /* Get the destinations for debug */
             l_destinations := fids.get_destinations();

             dbms_output.put_line('************** Data used for FIDS: BEGIN**************');
             dbms_output.put_line('airport_code          ::'||fids.get_airport_code());
             dbms_output.put_line('carrier_code          ::'||fids.get_carrier_code());
             dbms_output.put_line('flight_no             ::'||fids.get_flight_no());
             dbms_output.put_line('flight_date           ::'||fids.get_flight_date());
             dbms_output.put_line('direction             ::'||fids.get_direction());
             IF l_destinations.COUNT > 0 THEN
                FOR i in l_destinations.FIRST..l_destinations.LAST LOOP
                    dbms_output.put_line('destinations('||i||')       ::'||l_destinations(i));
                END LOOP;
             END IF;
             dbms_output.put_line('flight_status         ::'||fids.get_flight_status());
             dbms_output.put_line('code_share            ::'||fids.get_codeshare_flight());
             dbms_output.put_line('trip_date             ::'||fids.get_trip_date());
             dbms_output.put_line('trip_number           ::'||fids.get_trip_number());
             dbms_output.put_line('schd_flight_time      ::'||fids.get_schd_flight_time());
             dbms_output.put_line('est_flight_time       ::'||fids.get_est_flight_time());
             dbms_output.put_line('act_flight_time       ::'||fids.get_act_flight_time());
             dbms_output.put_line('terminal              ::'||fids.get_terminal());
             dbms_output.put_line('gate                  ::'||fids.get_gate());
             dbms_output.put_line('apron                 ::'||fids.get_apron());
             dbms_output.put_line('aircraft_type         ::'||fids.get_aircraft_type());
             dbms_output.put_line('aircraft_registration ::'||fids.get_aircraft_registration());
             dbms_output.put_line('************** Data used for FIDS: END **************');
           END IF;
           
           add_app_msg.log_oracle_error ('FLIGHTINFO.FIDS', SQLERRM, SQLCODE);

    END fids;
END flightinfo;
/
