PROMPT CREATE OR REPLACE PACKAGE fasl_fids

CREATE OR REPLACE PACKAGE fasl_fids IS

/*****************************************************************************
*
* Filename    - $Workfile:   fasl_fids.sql  $
*
* Project Name    - UltraTrak Database
*
* Source Code Identifier  - 0/3/300/8010/112
*
* Description    - Handles FIDS from a number of interfaces. All information
*                         is validated and transformed in order to be passed to the
*                         flightinfo package.
*
* Language    - Oracle PL/SQL
*
* File History    - For the development history run the Show History report in PVCS
*
* (c) Ultra Electronics Limited, 2001
*
*****************************************************************************/

        /*
         * Accept FASL FIDS data, convert it to the generic format
         * and store it in the flight_info table, and then call the
         * generic maintain_flight procedure.
         */
        procedure handle_flight_info (
                interface_id   in   sys_interface.id%type,
                incoming       in   fasl_flight_info%rowtype
        );

        /*
         * Accept YVR FIDS data, convert it to the generic format
         * and store it in the flight_info table, and then call the
         * generic maintain_flight procedure.
         */
        procedure handle_flight_info (
                interface_id   in   sys_interface.id%type,
                incoming       in   yvr_fids_info%rowtype
        );

        /*
         * Accept PRG FIDS data, convert it to the generic format
         * and store it in the flight_info table, and then call the
         * generic maintain_flight procedure.
         */
        procedure handle_prg_flight_info (
                interface_id   in   sys_interface.id%type,
                incoming       in   prg_fids_info%rowtype
        );

        /*
         * Perform any required actions to 'normalize' the flight
         * number so it is compatible with those used in the
         * flight table.
         */
        function normalise_flight (
                p_fasl_flight_carrier in fasl_flight_info.flight_carrier%type,
                p_fasl_flight_number  in fasl_flight_info.flight_number%type,
                p_fasl_flight_suffix  in fasl_flight_info.flight_suffix%type
        ) return
                flight.flight_no%type;

END fasl_fids;




/

PROMPT CREATE OR REPLACE PACKAGE BODY fasl_fids

CREATE OR REPLACE PACKAGE BODY fasl_fids AS

function normalise_flight (
        p_fasl_flight_carrier in fasl_flight_info.flight_carrier%type,
        p_fasl_flight_number  in fasl_flight_info.flight_number%type,
        p_fasl_flight_suffix  in fasl_flight_info.flight_suffix%type
) return
        flight.flight_no%type
is
        result flight.flight_no%type;
begin

        /*
         * FASL flight number formats:
         * Carrier code: right justified, e.g. ' CI'.
         * Flight no.: add leading zeroes to the number for characters 2, 3 and 4.
         *             Character 1 is always space, e.g. ' 023'.
         * Suffix: Ditch the suffix if it is a blank.
         */
        result := rtrim( rpad( ltrim( p_fasl_flight_carrier ), 3 ) ||
                                                lpad( ltrim( p_fasl_flight_number ), 4, '0' ) ||
                                                p_fasl_flight_suffix );

        return result;

end normalise_flight;

procedure handle_flight_info (
        interface_id   in   sys_interface.id%type,
        incoming       in   fasl_flight_info%rowtype
)
is

        norm_flight_no flight.flight_no%type;
        std_conv       char(25);
        departure_time date;
        digits         char(10) := '0123456789';
        generic_dir    flight_info.direction%type;
        effective_std  flight.flight_sch_time%type;
        force_mapping  boolean;
        apron          flight_info.apron%type;
        depcode        site_info.airport_code%type default null;


begin

        /*
         * Only process outgoing flights.
         */
        if ( incoming.direction != 'DEP' ) then
                return;
        end if;

        if ( rtrim(incoming.std) is not null ) then
                effective_std := rtrim(incoming.std);
        else
                effective_std := '-';
        end if;

        if ( config.get_value( 'FORCE_OPDB_MAPPING' ) = 'Y' ) then
                force_mapping := TRUE;
        else
                force_mapping := FALSE;
        end if;

        if ( config.get_value( 'OPDB_GATE_IS_APRON' ) = 'Y' ) then
                apron := incoming.gate;
        else
                apron := NULL;
        end if;

        /*
         * Produce a date/time value from the text flight date and
         * STD if this is numeric.
         */
        std_conv := incoming.flight_date;

        if ( instr(digits,substr(incoming.std,1,1)) != 0 and
                instr(digits,substr(incoming.std,2,1)) != 0 and
                instr(digits,substr(incoming.std,3,1)) != 0 and
                instr(digits,substr(incoming.std,4,1)) != 0
        ) then
                std_conv := rtrim(std_conv) || ' ' || rtrim(incoming.std);
        else
                std_conv := rtrim(std_conv) || ' 0000';
        end if;

        departure_time := to_date( std_conv, 'YYYYMMDD HH24MI' );

        norm_flight_no := normalise_flight(
                incoming.flight_carrier,
                incoming.flight_number,
                incoming.flight_suffix
        );

        /*
         * Convert direction. Historically we have used
         * I for inbound and O for outbound.
         */
        if ( incoming.direction = 'ARR' ) then
                generic_dir := 'I';
        else
                generic_dir := 'O';
        end if;

        /*
         * Add the generic flight_info record.
         */
        flightinfo.incoming_record (
                norm_flight_no,
                departure_time,
                incoming.gate,
                apron,
                incoming.std,
                NULL,
                generic_dir,
                interface_id,
                incoming.id,
                force_mapping,
                depcode
        );

        /*
         * Call the generic flightinfo routine to apply the
         * information as appropriate.
         */
        flightinfo.maintain_flight (
                norm_flight_no,
                departure_time,
                effective_std,
                incoming.flight_carrier,
                incoming.destination,
                '-',
                force_mapping,
                depcode,
                interface_id
        );

end handle_flight_info;

PROCEDURE handle_flight_info ( -- Mik (6|Nov|2002) Impemented for YVR
    interface_id   IN   sys_interface.id%TYPE,
    incoming       IN   yvr_fids_info%ROWTYPE
)
IS

/*****************************************************************************
*
* Package Procedure - handle_flight_info
*
* Description - Handle incoming FIDS information for YVR. Validate and perform
*               lookups in order to pass information to the flightinfo package
*               to update the flight and flight_info tables.
*
* CR History:
* Date  Name    CR Number  CR Description
*
*****************************************************************************/

    generic_dir            flight_info.direction%TYPE;
    l_flight_status        flight.flight_status%TYPE := '-';
    departing_airport_code site_info.airport_code%TYPE;
    force_mapping          boolean;
    apron                  flight_info.apron%TYPE;

    actual_fltno           flight.flight_no%TYPE;
    actual_fltdt           flight.flight_date%TYPE;
    actual_fltid           flight.flight_id%TYPE;

    rc NUMBER;
BEGIN

-- Convert direction. Historically we have used I for inbound and O for outbound.
    IF (incoming.leg = 'ARR') THEN
        generic_dir := 'I';
        l_flight_status := 'IB';
    ELSE
        generic_dir := 'O';
        l_flight_status := '-';
    END IF;

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

-- Confirm Purpose
    IF (config.get_value('OPDB_GATE_IS_APRON') = 'Y') THEN
        apron := incoming.gate;
    ELSE
        apron := NULL;
    END IF;

-- Get Departing Airport Code from db_config
    departing_airport_code := trim (config.get_value ('FIDS_IF_' || interface_id));

-- Determine where flight already exists on system
    flightinfo.identify_flight (incoming.flight_no
            , to_char (incoming.std ,'DDMON')
            , force_mapping
            , actual_fltno     -- Return value
            , actual_fltdt     -- Return value
            , actual_fltid     -- Return value
            , rc               -- Return value (0 if flight found)
            , departing_airport_code
            , interface_id
        );

    IF (rc >= 0) THEN -- Flight found in system
        BEGIN
        -- If leg has changed do not update filght information.
            SELECT
                0 into rc
            FROM
                flight
            WHERE
                flight_id = actual_fltid
            AND
                flight_type = generic_dir;
        EXCEPTION
        WHEN NO_DATA_FOUND then
            RAISE_APPLICATION_ERROR (-20000, 'Flight found on the system with a different direction.');
        WHEN OTHERS THEN
            RAISE;
        END;
    END IF;

    flightinfo.incoming_record (
                  incoming.flight_no            -- SQL*Loader Normalises the value
                , incoming.std                  -- departure_time
                , incoming.gate
                , apron
                , to_char (incoming.std, 'HH24MI')
                , NULL
                , generic_dir
                , interface_id
                , incoming.id
                , force_mapping
                , departing_airport_code
            );

      -- Call the generic flightinfo routine to apply the information as appropriate.
        flightinfo.maintain_flight (
                  incoming.flight_no            -- SQL*Loader Normalises the value
                , incoming.std
                , to_char (incoming.std, 'HH24MI')
                , incoming.carrier
                , incoming.via_1                -- Final flight destination
                , l_flight_status
                , force_mapping
                , departing_airport_code
                , interface_id
            );

END handle_flight_info;

procedure handle_prg_flight_info (
        interface_id   in   sys_interface.id%type,
        incoming       in   prg_fids_info%rowtype
)
is

        make_flight_no flight.flight_no%type;
        norm_flight_no flight.flight_no%type;
        std_conv       char(25);
        std            char(4);
        departure_time date;
        digits         char(10) := '0123456789';
        generic_dir    flight_info.direction%type;
        effective_std  flight.flight_sch_time%type;
        force_mapping  boolean;
        apron          flight_info.apron%type;
        l_suffix       char(1):= ' ';
        v_std          date;
        l_number       number;
        l_fno          fasl_flight_info.flight_number%type;
        l_carrier      fasl_flight_info.flight_carrier%type;
        l_block        flight_info.block_off%type;
        depcode        site_info.site_id%type;

begin

        /*
         * Only process outgoing flights.
         */
        if ( incoming.direction != 'D' ) then
                return;
        end if;


        if ( config.get_value( 'FORCE_OPDB_MAPPING' ) = 'Y' ) then
                force_mapping := TRUE;
        else
                force_mapping := FALSE;
        end if;

        if ( config.get_value( 'OPDB_GATE_IS_APRON' ) = 'Y' ) then
                apron := incoming.gate;
        else
                apron := NULL;
        end if;

        /*
         * Produce a date/time value from the text flight date and
         * STD if this is numeric.
         */

             std_conv :=   incoming.flight_date;
             std      := incoming.scheduled;


                     if std = ' ' then

                        std := incoming.estimate;
                        l_block := 'E';

                     end if;


                     if std = ' ' then

                          std := incoming.blockoff;
                          l_block := 'B';

                     end if;


                    if std = ' ' then

                        std := incoming.runway;
                        l_block := 'A';

                    end if;


                        effective_std := rtrim(std);


       departure_time :=  std_conv || ' ' || to_char(sysdate, 'yyyy') ;



      make_flight_no := nvl(rtrim(incoming.flight_code2), rtrim(incoming.flight_code3))
                                          || incoming.flight_number;



        /*  split flight numberinto its three parts  */

         norm_flight_no := normalise_flight_no(make_flight_no);

         dbms_output.put_line(norm_flight_no);

        /*
         * Convert direction. Historically we have used
         * I for inbound and O for outbound.
         */
        if ( incoming.direction = 'A' ) then
                generic_dir := 'I';
        else
                generic_dir := 'O';
        end if;

        /*
         * Add the generic flight_info record.
         */


      flightinfo.incoming_record (
                norm_flight_no,
                departure_time,
                incoming.gate,
                apron,
                std,
                l_block,
                generic_dir,
                interface_id,
                incoming.id,
                force_mapping,
                depcode
        );

        /*
         * Call the generic flightinfo routine to apply the
         * information as appropriate.
         */
        flightinfo.maintain_flight (
                norm_flight_no,
                departure_time,
                effective_std,
                l_carrier,
                incoming.flight_dest,
                '-',
                force_mapping,
                depcode,
                interface_id
        );

end handle_prg_flight_info;
end fasl_fids;
/
