PROMPT CREATE OR REPLACE TYPE FIDS_INTERFACE

CREATE OR REPLACE TYPE fids_interface AS OBJECT (
--*****************************************************************************
--
-- Filename- $Workfile$
--
-- Project Name - UltraTrak Database
--
-- Source Code Identifier- 0/3/300/8010/112
--
-- Description -
--
-- Language - Oracle PL/SQL
--
-- File History - For the development history run the Show History report in PVCS
--
-- (c) Ultra Electronics Limited, 2001
--
--*****************************************************************************
      flight_id             NUMBER
    , airport_code          CHAR (3)
    , carrier_code          CHAR (3)
    , flight_no CHAR (5)
    , flight_date DATE
    , trip_date             DATE
    , trip_number           NUMBER
    , schd_flight_time DATE
    , est_flight_time DATE
    , act_flight_time DATE
    , direction CHAR(1)
    , destinations DESTINATION_LIST
    , flight_status         CHAR (2)
    , terminal VARCHAR2(10)
    , gate VARCHAR2(5)
    , apron                 VARCHAR2 (5)
    , aircraft_type         VARCHAR2 (10)
    , aircraft_registration VARCHAR2(20)
    , codeshare_flight CHAR (1)
    , primary_carrier CHAR (3)
    , primary_flight_no CHAR (5)
    , primary_flight_date DATE
    , prev_airport CHAR(3)
    , prev_departure_time DATE
    , message_number NUMBER

    , MEMBER FUNCTION  get_flight_id             RETURN NUMBER
    , MEMBER FUNCTION  get_airport_code          RETURN CHAR
    , MEMBER FUNCTION  get_carrier_code          RETURN CHAR
    , MEMBER FUNCTION  get_flight_no             RETURN CHAR
    , MEMBER FUNCTION  get_flight_date           RETURN DATE
    , MEMBER FUNCTION  get_trip_date             RETURN DATE
    , MEMBER FUNCTION  get_trip_number           RETURN NUMBER
    , MEMBER FUNCTION  get_schd_flight_time      RETURN DATE
    , MEMBER FUNCTION  get_est_flight_time       RETURN DATE
    , MEMBER FUNCTION  get_act_flight_time       RETURN DATE
    , MEMBER FUNCTION  get_direction             RETURN CHAR
    , MEMBER FUNCTION  get_destinations          RETURN DESTINATION_LIST
    , MEMBER FUNCTION  get_flight_status         RETURN CHAR
    , MEMBER FUNCTION  get_terminal              RETURN VARCHAR2
    , MEMBER FUNCTION  get_gate                  RETURN VARCHAR2
    , MEMBER FUNCTION  get_apron                 RETURN VARCHAR2
    , MEMBER FUNCTION  get_aircraft_type         RETURN VARCHAR2
    , MEMBER FUNCTION  get_aircraft_registration RETURN VARCHAR2
    , MEMBER FUNCTION  get_codeshare_flight      RETURN CHAR
    , MEMBER FUNCTION  get_primary_carrier       RETURN CHAR
    , MEMBER FUNCTION  get_primary_flight_no     RETURN CHAR
    , MEMBER FUNCTION  get_primary_flight_date   RETURN DATE
    , MEMBER FUNCTION  get_prev_airport          RETURN CHAR
    , MEMBER FUNCTION  get_prev_departure_time   RETURN DATE
    , MEMBER FUNCTION  get_message_number        RETURN NUMBER

    , MEMBER PROCEDURE set_flight_id             (p_flight_id             IN NUMBER)
    , MEMBER PROCEDURE set_carrier_code          (p_carrier_code          IN CHAR)
    , MEMBER PROCEDURE set_flight_no             (p_flight_no             IN CHAR)
    , MEMBER PROCEDURE set_flight_date           (p_flight_date           IN DATE)
    , MEMBER PROCEDURE set_trip_date             (p_trip_date             IN DATE)
    , MEMBER PROCEDURE set_trip_number           (p_trip_number           IN NUMBER)
    , MEMBER PROCEDURE set_schd_flight_time      (p_schd_flight_time      IN DATE)
    , MEMBER PROCEDURE set_est_flight_time       (p_est_flight_time       IN DATE)
    , MEMBER PROCEDURE set_act_flight_time       (p_act_flight_time       IN DATE)
    , MEMBER PROCEDURE set_airport_code          (p_airport_code          IN CHAR)
    , MEMBER PROCEDURE set_direction             (p_direction             IN CHAR)
    , MEMBER PROCEDURE set_destinations          (p_destinations          IN DESTINATION_LIST)
    , MEMBER PROCEDURE set_destinations          (p_destinations          IN VARCHAR2)
    , MEMBER PROCEDURE set_flight_status         (p_flight_status         IN CHAR)
    , MEMBER PROCEDURE set_terminal              (p_terminal              IN VARCHAR2)
    , MEMBER PROCEDURE set_gate                  (p_gate                  IN VARCHAR2)
    , MEMBER PROCEDURE set_apron                 (p_apron                 IN VARCHAR2)
    , MEMBER PROCEDURE set_aircraft_type         (p_aircraft_type         IN VARCHAR2)
    , MEMBER PROCEDURE set_aircraft_registration (p_aircraft_registration IN VARCHAR2)
    , MEMBER PROCEDURE set_codeshare_flight      (p_codeshare_flight      IN CHAR)
    , MEMBER PROCEDURE set_primary_carrier       (p_primary_carrier       IN CHAR)
    , MEMBER PROCEDURE set_primary_flight_no     (p_primary_flight_no     IN CHAR)
    , MEMBER PROCEDURE set_primary_flight_date   (p_primary_flight_date   IN DATE)
    , MEMBER PROCEDURE set_prev_airport          (p_prev_airport          IN CHAR)
    , MEMBER PROCEDURE set_prev_departure_time   (p_prev_departure_time   IN DATE)
    , MEMBER PROCEDURE set_message_number        (p_message_number        IN NUMBER)

    , MEMBER PROCEDURE handle_flight_info (interface_id IN NUMBER)

    , MEMBER PROCEDURE select_flight_info (
                                           p_airport_code IN CHAR
                                         , p_carrier_code IN CHAR
                                         , p_flight_no    IN CHAR
                                         , p_flight_date  IN DATE
                                         , p_direction    IN CHAR
                                          )

    , MEMBER PROCEDURE select_flight_info (p_airport_code IN CHAR
                                       , p_carrier_code IN CHAR
                                       , p_flight_no    IN CHAR
                                       , p_trip_date    IN DATE
                                       , p_trip_number  IN NUMBER
                                       , p_direction    IN CHAR
                                        )
);
/
PROMPT CREATE OR REPLACE TYPE BODY fids_interface

CREATE OR REPLACE TYPE BODY fids_interface IS
--****************************************************************************
--
-- PL*SQL Member Functions get_xxxx Routines
--
-- Description - These functions set various attributed that may have values
--
--
-- CR History:
-- Date          Name          CR Number  CR Description
-- 13/Jan/2004  PC             CA431      Added set and get methods
--
--****************************************************************************

    MEMBER FUNCTION  get_flight_id RETURN NUMBER IS
    BEGIN
        RETURN self.flight_id;
    END;

    MEMBER FUNCTION  get_airport_code RETURN CHAR IS
    BEGIN
        RETURN self.airport_code;
    END;

    MEMBER FUNCTION  get_carrier_code RETURN CHAR IS
    BEGIN
        RETURN self.carrier_code;
    END;

    MEMBER FUNCTION  get_flight_no RETURN CHAR IS
    BEGIN
        RETURN self.flight_no;
    END;

    MEMBER FUNCTION  get_flight_date RETURN DATE IS
    BEGIN
        RETURN self.flight_date;
    END;

    MEMBER FUNCTION  get_trip_date RETURN DATE IS
    BEGIN
        RETURN self.trip_date;
    END;

    MEMBER FUNCTION  get_trip_number RETURN NUMBER IS
    BEGIN
        RETURN self.trip_number;
    END;

    MEMBER FUNCTION  get_schd_flight_time RETURN DATE IS
    BEGIN
        RETURN self.schd_flight_time;
    END;

    MEMBER FUNCTION  get_est_flight_time RETURN DATE IS
    BEGIN
        RETURN self.est_flight_time;
    END;

    MEMBER FUNCTION  get_act_flight_time RETURN DATE IS
    BEGIN
        RETURN self.act_flight_time;
    END;

    MEMBER FUNCTION  get_direction RETURN CHAR IS
    BEGIN
        RETURN self.direction;
    END;

    MEMBER FUNCTION  get_destinations RETURN DESTINATION_LIST IS
    BEGIN
        RETURN self.destinations;
    END;

    MEMBER FUNCTION  get_flight_status RETURN CHAR IS
    BEGIN
        RETURN self.flight_status;
    END;

    MEMBER FUNCTION  get_terminal RETURN VARCHAR2 IS
    BEGIN
        RETURN self.terminal;
    END;

    MEMBER FUNCTION  get_gate RETURN VARCHAR2 IS
    BEGIN
        RETURN self.gate;
    END;

    MEMBER FUNCTION  get_apron RETURN VARCHAR2 IS
    BEGIN
        RETURN self.apron;
    END;

    MEMBER FUNCTION  get_aircraft_type RETURN VARCHAR2 IS
    BEGIN
        RETURN self.aircraft_type;
    END;

    MEMBER FUNCTION  get_aircraft_registration RETURN VARCHAR2 IS
    BEGIN
        RETURN self.aircraft_registration;
    END;

    MEMBER FUNCTION  get_codeshare_flight RETURN CHAR IS
    BEGIN
        RETURN self.codeshare_flight;
    END;

    MEMBER FUNCTION  get_primary_carrier RETURN CHAR IS
    BEGIN
        RETURN self.primary_carrier;
    END;

    MEMBER FUNCTION  get_primary_flight_no RETURN CHAR IS
    BEGIN
        RETURN self.primary_flight_no;
    END;

    MEMBER FUNCTION  get_primary_flight_date RETURN DATE IS
    BEGIN
        RETURN self.primary_flight_date;
    END;

    MEMBER FUNCTION  get_prev_airport RETURN CHAR IS
    BEGIN
        RETURN self.prev_airport;
    END;

    MEMBER FUNCTION  get_prev_departure_time RETURN DATE IS
    BEGIN
        RETURN self.prev_departure_time;
    END;

    MEMBER FUNCTION  get_message_number RETURN NUMBER IS
    BEGIN
        RETURN self.message_number;
    END;

--****************************************************************************
--
-- PL*SQL Member Procedures set_xxxx Routines
--
-- Description - These functions get various attributes that may have values
--
--
-- CR History:
-- Date          Name          CR Number  CR Description
-- 13/Jan/2004  PC
--
--****************************************************************************

    MEMBER PROCEDURE set_flight_id (p_flight_id IN NUMBER) IS
    BEGIN
        self.flight_id := p_flight_id;
    END;

    MEMBER PROCEDURE set_airport_code (p_airport_code IN CHAR) IS
    BEGIN
        IF p_airport_code IN ('', ' ') OR p_airport_code IS NULL THEN
            RAISE flightinfo.error_null_airport_code;
        END IF;

        self.airport_code := p_airport_code;
    END;

    MEMBER PROCEDURE set_carrier_code (p_carrier_code IN CHAR) IS
    BEGIN
        IF p_carrier_code IN ('', ' ') OR p_carrier_code IS NULL THEN
            RAISE flightinfo.error_null_carrier_code;
        END IF;

        self.carrier_code := p_carrier_code;
    END;

    MEMBER PROCEDURE set_flight_no (p_flight_no IN CHAR) IS
    BEGIN
        IF p_flight_no IN ('', ' ') OR p_flight_no IS NULL  THEN
            RAISE flightinfo.error_null_flight_no;
        END IF;

        self.flight_no := p_flight_no;
    END;

    MEMBER PROCEDURE set_flight_date (p_flight_date IN DATE) IS
    BEGIN
        IF p_flight_date IS NULL THEN
            RAISE flightinfo.error_null_flight_date;
        END IF;

        self.flight_date := p_flight_date;
    END;

    MEMBER PROCEDURE set_trip_date (p_trip_date IN DATE) IS
    BEGIN
        self.trip_date := p_trip_date;
    END;

    MEMBER PROCEDURE set_trip_number (p_trip_number IN NUMBER) IS
    BEGIN
        self.trip_number := p_trip_number;
    END;

    MEMBER PROCEDURE set_schd_flight_time (p_schd_flight_time IN DATE) IS
    BEGIN
        IF p_schd_flight_time IS NULL THEN
            RAISE flightinfo.error_null_schd_flight_time;
        END IF;

        self.schd_flight_time := p_schd_flight_time;
    END;

    MEMBER PROCEDURE set_est_flight_time (p_est_flight_time IN DATE) IS
    BEGIN
        self.est_flight_time := p_est_flight_time;
    END;

    MEMBER PROCEDURE set_act_flight_time (p_act_flight_time IN DATE) IS
    BEGIN
        self.act_flight_time := p_act_flight_time;
    END;

    MEMBER PROCEDURE set_direction (p_direction IN CHAR) IS
    BEGIN
        IF p_direction IN ('', ' ') OR p_direction IS NULL THEN
            RAISE flightinfo.error_null_direction;
        END IF;

        self.direction := p_direction;
    END;

    MEMBER PROCEDURE set_destinations (p_destinations IN DESTINATION_LIST) IS
    BEGIN
        self.destinations := p_destinations;
    END;

    MEMBER PROCEDURE set_destinations (p_destinations IN VARCHAR2) IS
        l_destinations        DESTINATION_LIST := DESTINATION_LIST();
        l_table               dbms_utility.uncl_array;                      -- Destinations
        l_destinations_length BINARY_INTEGER;                               -- Number of destinations

    BEGIN
        /* Exclude NULL values */
        IF p_destinations IS NOT NULL THEN
            /* Process and split the destinations */
            uas_comma_to_table (p_destinations, l_destinations_length, l_table);   -- UAS/FALCP/CA/00767

            /* Populate destinations */
            FOR  i IN l_table.first()..l_destinations_length LOOP
                l_destinations.extend();
                l_destinations(i) := l_table(i);
            END LOOP;
        END IF;
        set_destinations (l_destinations);
    END;

    MEMBER PROCEDURE set_flight_status  (p_flight_status  IN CHAR) IS
    BEGIN
           IF (nvl(trim(get_flight_status()),'-') = '-'  ) AND
              (nvl(trim(p_flight_status),'-')     = '-'  ) THEN
               self.flight_status := '-';
           ELSIF (p_flight_status = 'PD' AND  nvl(trim(get_flight_status()),'-') != 'PD' ) OR
                 (p_flight_status != 'PD' AND nvl(trim(p_flight_status),'-') != '-'  AND
                                               (get_flight_status() != 'PD' OR
                                               nvl(trim(get_flight_status()),'-') = '-')) THEN

                  self.flight_status := p_flight_status;
           ELSIF (p_flight_status != 'PD' AND  get_flight_status() = 'PD' ) OR
                 (nvl(trim(p_flight_status),'-') = '-' AND get_flight_status() IS NOT NULL) THEN
                  NULL;
           END IF;
    END;

    MEMBER PROCEDURE set_terminal (p_terminal IN VARCHAR2) IS
    BEGIN
        self.terminal := p_terminal;
    END;

    MEMBER PROCEDURE set_gate (p_gate IN VARCHAR2) IS
    BEGIN
        self.gate := p_gate;
    END;

    MEMBER PROCEDURE set_apron (p_apron IN VARCHAR2) IS
    BEGIN
        self.apron := p_apron;
    END;

    MEMBER PROCEDURE set_aircraft_type (p_aircraft_type IN VARCHAR2) IS
    BEGIN
        self.aircraft_type := p_aircraft_type;
    END;

    MEMBER PROCEDURE set_aircraft_registration (p_aircraft_registration IN VARCHAR2) IS
    BEGIN
        self.aircraft_registration := p_aircraft_registration;
    END;

    MEMBER PROCEDURE set_codeshare_flight (p_codeshare_flight IN CHAR) IS
    BEGIN
        self.codeshare_flight := p_codeshare_flight;
    END;

    MEMBER PROCEDURE set_primary_carrier (p_primary_carrier IN CHAR) IS
    BEGIN
        self.primary_carrier := p_primary_carrier;
    END;

    MEMBER PROCEDURE set_primary_flight_no (p_primary_flight_no IN CHAR) IS
    BEGIN
        self.primary_flight_no := p_primary_flight_no;
    END;

    MEMBER PROCEDURE set_primary_flight_date (p_primary_flight_date IN DATE) IS
    BEGIN
        self.primary_flight_date := p_primary_flight_date;
    END;

    MEMBER PROCEDURE set_prev_airport (p_prev_airport IN CHAR) IS
    BEGIN
        self.prev_airport := p_prev_airport;
    END;

    MEMBER PROCEDURE set_prev_departure_time (p_prev_departure_time IN DATE) IS
    BEGIN
        self.prev_departure_time := p_prev_departure_time;
    END;

    MEMBER PROCEDURE set_message_number (p_message_number IN NUMBER) IS
    BEGIN
        self.message_number := p_message_number;
    END;

--****************************************************************************
--
-- PL*SQL Member Procedure handle_flight_info
--
-- Description - Populates the object types fields from the timezone table
--               for a given airport code.
--
-- CR History:
-- Date          Name          CR Number  CR Description
-- 17/Jun/2003   Mik Davis     FALCP/CA45 Added call to carrier.preferred_alpha_code()
--
--****************************************************************************

    MEMBER PROCEDURE handle_flight_info (interface_id IN NUMBER) IS

        force_mapping    boolean;
        l_carrier        carrier_lookup.alpha_code%type;
        l_primary_carrier carrier_lookup.alpha_code%type;
        l_flight_no      flight.flight_no%TYPE;
        l_flight_date    flight.flight_date%TYPE;
        l_apron          flight_info.apron%TYPE;

        l_sysdate         DATE DEFAULT SYSDATE;

        rc NUMBER;
    BEGIN
    -- Normalise flight number
        l_carrier   := carrier.preferred_alpha_code (get_carrier_code);
        l_flight_no := normalise_flight_no (l_carrier || ' ' || get_flight_no);

    -- Ensure trip_date has a value
        set_trip_date (nvl (get_trip_date(), get_flight_date())); -- Only bara supplies this value

    -- Ensure trip_number has a value
        set_trip_number (nvl (get_trip_number(), 0)); -- Only bara supplies this value sometimes

    -- Confirm Purpose
        IF (config.get_value ('FORCE_OPDB_MAPPING') = 'Y') THEN
            force_mapping := TRUE;
        ELSE
            force_mapping := FALSE;
        END IF;

        -- Setup apron
        l_apron := self.apron;
	IF ( l_apron IS NULL) THEN
           IF (config.get_value ('OPDB_GATE_IS_APRON') = 'Y') THEN
             l_apron := self.gate;
           END IF;
        END IF;

  -- Update only outbound flight information in the flight table
        IF get_direction() = 'O' THEN
            IF get_codeshare_flight() = 'S' THEN
                l_primary_carrier := carrier.preferred_alpha_code (get_primary_carrier());
                BEGIN
                    INSERT INTO code_share (
                                direction
                                , op_carrier
                                , op_fltno
                                , op_date
                                , cs_carrier
                                , cs_fltno
                                , cs_date
                                , timestamp
                                , airport_code
                                )
                    VALUES (
                                  self.get_direction()
                                , l_primary_carrier
                                , lpad (trim (self.get_primary_flight_no()), 4, '0')
                                , self.get_primary_flight_date()
                                , l_carrier
                                , self.get_flight_no()
                                , self.get_flight_date()
                                , l_sysdate
                                , self.get_airport_code()
                                );
                EXCEPTION
                WHEN OTHERS THEN
                    NULL;
                END;
            END IF;

    -- update flight_info table
            flightinfo.maintain_flight (
                      l_flight_no
                    , get_flight_date()
                    , to_char (nvl (get_act_flight_time(), get_est_flight_time()),'HH24MI')
                    , l_carrier
                    , get_destinations()
                    , get_flight_status()
                    , force_mapping
                    , get_airport_code()
                    , interface_id
                    );
        END IF;

        flightinfo.incoming_record (
                    l_flight_no
                    , get_flight_date()
                    , get_trip_date()
                    , get_trip_number()
                    , get_gate()
                    , l_apron
                    , to_char (nvl (get_act_flight_time(), get_est_flight_time()),'HH24MI')
                    , NULL
                    , get_direction()
                    , interface_id
                    , get_message_number()
                    , get_schd_flight_time
                    , get_est_flight_time()
                    , get_act_flight_time()
                    , get_airport_code()
                    , get_aircraft_registration()
                    , get_aircraft_type()
                    , get_prev_airport
                    , get_prev_departure_time()
                    , force_mapping
                    , self.terminal
                    );
    END;

--****************************************************************************
--
-- PL*SQL Member Procedure select_flight_info
--
-- Description - Initialises the object with an existing flights information
--
-- CR History:
-- Date          Name          CR Number  CR Description
--
--****************************************************************************

    MEMBER PROCEDURE select_flight_info (p_airport_code IN CHAR
                                       , p_carrier_code IN CHAR
                                       , p_flight_no    IN CHAR
                                       , p_flight_date  IN DATE
                                       , p_direction    IN CHAR
                                        )
    IS
        l_flight_no  flight.flight_no%TYPE;
        l_carrier    carrier_lookup.alpha_code%TYPE;

        rc NUMBER;
    BEGIN

        l_carrier   := carrier.preferred_alpha_code (p_carrier_code);
        l_flight_no := normalise_flight_no (l_carrier || ' ' || p_flight_no );

      -- Pre-set attrubutes to the incoming parameters.

        set_trip_date     (p_flight_date); -- Maintain trip date unique key for BARA

        set_carrier_code  (p_carrier_code);
        set_flight_no     (p_flight_no);
        set_airport_code  (p_airport_code);
        set_direction     (p_direction);

        set_flight_status ('-');            -- Initialise flight_status with '-'


    -- Depending on direction read from flight_info_(inbound)
        IF p_direction = 'O' THEN
            SELECT
                  to_date (fi.flight_date, 'DDMON')
                , fi.trip_date
                , fi.schd_flight_time
                , fi.est_flight_time
                , fi.act_flight_time
                , fi.airport_code
                , fi.terminal
                , fi.gate
                , fi.aircraft_type
                , fi.aircraft_registration
            INTO
                  self.flight_date
                , self.trip_date
                , self.schd_flight_time
                , self.est_flight_time
                , self.act_flight_time
                , self.airport_code
                , self.terminal
                , self.gate
                , self.aircraft_type
                , self.aircraft_registration
            FROM flight_info fi
            WHERE airport_code = self.get_airport_code() -- Please note ordering for secondary unique index
            AND   flight_no    = l_flight_no
            AND   trip_date    = self.get_trip_date();

          -- flight_id procedure should really be in a package. We need the flight_id to get the current flight status value id it exists
            live.flight_id (
                  l_flight_no
                , to_char (get_flight_date(), 'DDMON')
                , multi_site.get_site_id (p_airport_code)
                , self.flight_id -- flight_id returned
                );

            SELECT flight_status INTO self.flight_status
            FROM flight
            WHERE flight_id = self.get_flight_id();
        ELSE -- IF p_direction = 'I'
            SELECT
                  to_date (fi.flight_date, 'DDMON')
                , fi.trip_date
                , fi.schd_arrival_time
                , fi.est_arrival_time
                , fi.act_arrival_time
                , fi.gate
                , fi.aircraft_type
                , fi.aircraft_registration
                , fi.prev_airport
                , fi.prev_departure_time
            INTO
                  self.flight_date
                , self.trip_date
                , self.schd_flight_time
                , self.est_flight_time
                , self.act_flight_time
                , self.gate
                , self.aircraft_type
                , self.aircraft_registration
                , self.prev_airport
                , self.prev_departure_time
            FROM  flight_info_inbound fi
            WHERE airport_code = self.get_airport_code() -- Please note ordering for secondary unique index
            AND   flight_no    = l_flight_no
            AND   trip_date    = self.get_trip_date()
            ;
        END IF;
    END;

--****************************************************************************
--
-- PL*SQL Member Procedure select_flight_info
--
-- Description - Initialises the object with an existing flights information
--
-- CR History:
-- Date          Name          CR Number  CR Description
--
--****************************************************************************

    MEMBER PROCEDURE select_flight_info (p_airport_code IN CHAR
                                       , p_carrier_code IN CHAR
                                       , p_flight_no    IN CHAR
                                       , p_trip_date    IN DATE
                                       , p_trip_number  IN NUMBER
                                       , p_direction    IN CHAR
                                       )
    IS
        l_flight_no  flight.flight_no%TYPE;
        l_carrier    carrier_lookup.alpha_code%TYPE;
    BEGIN

        l_carrier   := carrier.preferred_alpha_code (p_carrier_code);
        l_flight_no := normalise_flight_no (l_carrier || ' ' || p_flight_no );

        set_airport_code (p_airport_code);
        set_carrier_code (p_carrier_code);
        set_flight_no    (p_flight_no);
        set_trip_date    (p_trip_date);
        set_trip_date    (p_trip_date); -- Maintain trip date unique key for BARA
        set_trip_number  (p_trip_number);
        set_direction    (p_direction);

        set_flight_status ('-');            -- Initialise flight_status with '-'

    -- Depending on direction read from flight_info_(inbound)
        IF p_direction = 'O' THEN
            SELECT
                  to_date (fi.flight_date, 'DDMON')
                , fi.trip_date
                , fi.trip_number
                , fi.schd_flight_time
                , fi.est_flight_time
                , fi.act_flight_time
                , fi.airport_code
                , fi.terminal
                , fi.gate
                , fi.aircraft_type
                , fi.aircraft_registration
            INTO
                  self.flight_date
                , self.trip_date
                , self.trip_number
                , self.schd_flight_time
                , self.est_flight_time
                , self.act_flight_time
                , self.airport_code
                , self.terminal
                , self.gate
                , self.aircraft_type
                , self.aircraft_registration
            FROM flight_info fi
            WHERE airport_code = self.get_airport_code() -- Please note ordering for secondary unique index
            AND   flight_no    = l_flight_no
            AND   trip_date    = self.get_trip_date()
            AND   trip_number  = self.get_trip_number()
            ;

          -- flight_id procedure should really be in a package. We need the flight_id to get the current flight status value id it exists
            live.flight_id (
                  p_flight_no
                , to_char (get_flight_date(), 'DDMON')
                , multi_site.get_site_id (p_airport_code)
                , self.flight_id                            -- flight_id returned
                );

            SELECT flight_status INTO self.flight_status
            FROM flight
            WHERE flight_id = self.get_flight_id();
        ELSE -- IF p_direction = 'I' (ASSUMPTION)
            SELECT
                  to_date (fi.flight_date, 'DDMON')
                , fi.trip_date
                , fi.trip_number
                , fi.schd_arrival_time
                , fi.est_arrival_time
                , fi.act_arrival_time
                , fi.gate
                , fi.aircraft_type
                , fi.aircraft_registration
                , fi.prev_airport
                , fi.prev_departure_time
            INTO
                  self.flight_date
                , self.trip_date
                , self.trip_number
                , self.schd_flight_time
                , self.est_flight_time
                , self.act_flight_time
                , self.gate
                , self.aircraft_type
                , self.aircraft_registration
                , self.prev_airport
                , self.prev_departure_time
            FROM  flight_info_inbound fi
            WHERE airport_code = self.get_airport_code() -- Please note ordering for secondary unique index
            AND   flight_no    = l_flight_no
            AND   trip_date    = self.get_trip_date()
            AND   trip_number  = self.get_trip_number()
            ;
        END IF;
    END;
END;
/
