PROMPT CREATE OR REPLACE TYPE bara_flight_info;

CREATE OR REPLACE TYPE bara_flight_info 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
--
--*****************************************************************************

      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
    , airport_code          CHAR (3)
    , direction             CHAR (1)
    , destinations          DESTINATION_LIST
    , terminal              VARCHAR2 (10)
    , gate                  VARCHAR2 (5)
    , apron                 VARCHAR2 (5)
    , aircraft_type         VARCHAR2 (3)
    , aircraft_registration VARCHAR2 (7)
    , 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
    , flight_status         CHAR (1)
    , message_number        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_trip_date    IN DATE
                                       , p_trip_number  IN NUMBER
                                       , p_direction    IN CHAR
                                        )
);
/


PROMPT CREATE OR REPLACE TYPE BODY bara_flight_info

CREATE OR REPLACE TYPE BODY bara_flight_info IS

--****************************************************************************
--
-- 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

        l_flight_type     flight_info.direction%TYPE;
        l_flight_status   flight.flight_status%TYPE := '-';
        force_mapping     boolean;
        l_apron           flight_info.apron%TYPE;
        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_flight_id       flight.flight_id%TYPE;
        l_airport_code    site_info.airport_code%TYPE;
        rc NUMBER;
        l_terminal        flight_info.terminal%TYPE;	

    BEGIN
    -- Normalise flight number
        l_carrier   := carrier.preferred_alpha_code (self.carrier_code);
        l_flight_no := normalise_flight_no (l_carrier || ' ' || self.flight_no);

    -- Ensure trip_date has a value
        self.trip_date := nvl (trip_date, flight_date); -- Only bara supplies this value
        
    -- Ensure trip_number has a value
        self.trip_number := nvl (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;

    -- Determine where flight already exists on system
        flightinfo.identify_flight (
              l_flight_no
            , to_char (self.flight_date ,'DDMON')
            , force_mapping
            , l_flight_no     -- Return value
            , l_flight_date   -- Return value
            , l_flight_id     -- Return value
            , rc              -- Return value (0 if flight found)
            , self.airport_code
            , flightinfo.bara_interface
            );

    -- Intepretate flight status
        IF self.flight_status IN ('D', 'A') THEN
          l_flight_status := 'PD';
        ELSE
          BEGIN
            SELECT
                flight_status INTO l_flight_status
            FROM
                flight
            WHERE
                flight_id = l_flight_id;
            EXCEPTION
            WHEN NO_DATA_FOUND THEN
                l_flight_status := '-';
            END;
        END IF;

        IF self.direction = 'I' THEN
            l_flight_status := 'IB';
        END IF;

    -- Update only outbound flight information in the flight table
        IF self.direction = 'O' THEN
            IF self.codeshare_flight = 'S' THEN

                l_primary_carrier := carrier.preferred_alpha_code (self.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.direction
                    , l_primary_carrier
                    , lpad (trim (self.primary_flight_no), 4, '0')
                    , self.primary_flight_date
                    , l_carrier
                    , self.flight_no
                    , self.flight_date
                    , sysdate
                    , self.airport_code);
            EXCEPTION
            WHEN OTHERS THEN
                NULL;
            END;
        END IF;

    -- update flight_info table
        flightinfo.maintain_flight (
                  l_flight_no
                , self.flight_date
                , to_char (self.act_flight_time ,'HH24MI')
                , l_carrier
                , self.destinations
                , l_flight_status
                , force_mapping
                , self.airport_code
                , flightinfo.bara_interface);
        END IF;

        flightinfo.incoming_record (
                  l_flight_no
                , self.flight_date
                , self.trip_date
                , self.trip_number
                , self.gate
                , l_apron
                , to_char (self.act_flight_time ,'HH24MI')
                , NULL
                , self.direction
                , flightinfo.bara_interface
                , self.message_number
                , self.schd_flight_time
                , self.est_flight_time
                , self.act_flight_time
                , self.airport_code
                , self.aircraft_registration
                , self.aircraft_type
                , self.prev_airport
                , self.prev_departure_time
                , force_mapping
                , l_terminal
        );
    END;



    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 );

        self.airport_code  :=  p_airport_code;
        self.carrier_code  :=  p_carrier_code;
        self.flight_no     :=  p_flight_no;
        self.trip_date     := p_trip_date;
        self.trip_number   := p_trip_number;
        self.direction     :=  p_direction;
        self.flight_status :=  '-';

    -- 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.airport_code -- Please note ordering for secondary unique index
            AND   flight_no    = l_flight_no
            AND   trip_date    = self.trip_date
            AND   trip_number  = self.trip_number
            ;
        ELSE -- IF p_direction = 'I'
            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.airport_code -- Please note ordering for secondary unique index
            AND   flight_no    = l_flight_no
            AND   trip_date    = self.trip_date
            AND   trip_number  = self.trip_number
            ;
        END IF;
    END;
END;
/
