-- File renamed from tia_yvr_fids_info.sql and file tib_yvr_fids_info.sql inserted into it
-- Dave Banks - FALCP/CA/1771 - 8th March 2006

PROMPT CREATE OR REPLACE TRIGGER tia_yvr_fids_info

CREATE OR REPLACE TRIGGER tia_yvr_fids_info
AFTER INSERT ON yvr_fids_info
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
/*****************************************************************************
*
* Filename		- $Workfile$
*
* Project Name		- UltraTrak Database
*
* Source Code Identifier	- 0/3/300/8010/112
*
* Description	        - Passes incomming FIDs to fasl fids to handle.
*
* Language		- Oracle PL/SQL
*
* File History		- For the development history run the Show History report in PVCS
*
* (c) Ultra Electronics Limited, 2002
*
*****************************************************************************/

/*****************************************************************************
*
* Trigger       - tia_yvr_fids_info
*
* Description	- Passes incomming FIDs to fasl fids to handle.
*
* CR History:
* Date	Name		CR Number	CR Description
*
*****************************************************************************/
DECLARE
    incoming                YVR_FIDS_INFO%ROWTYPE;
    l_dests                 VARCHAR2 (255);
    l_direction             CHAR (1);
    l_prev_airport          flight_info_inbound.prev_airport%TYPE := NULL;
    l_interface_id          NUMBER := flightinfo.yvr_interface;

BEGIN

    incoming.id          := :NEW.id;
    incoming.carrier     := :NEW.carrier;
    incoming.flight_no   := :NEW.flight_no;
    incoming.flight_date := :NEW.flight_date;
    incoming.leg         := :NEW.leg;
    incoming.sch_dt      := :NEW.sch_dt;
    incoming.sch_tm      := :NEW.sch_tm;
    incoming.est_dt      := :NEW.est_dt;
    incoming.est_tm      := :NEW.est_tm;
    incoming.act_dt      := :NEW.act_dt;
    incoming.act_tm      := :NEW.act_tm;
    incoming.std         := :NEW.std;
    incoming.via_1       := :NEW.via_1;
    incoming.via_2       := :NEW.via_2;
    incoming.via_3       := :NEW.via_3;
    incoming.via_4       := :NEW.via_4;
    incoming.via_5       := :NEW.via_5;
    incoming.via_6       := :NEW.via_6;
    incoming.range       := :NEW.range;
    incoming.carousel    := :NEW.carousel;
    incoming.gate        := :NEW.gate;
    incoming.actype      := :NEW.actype;
    incoming.acreg       := :NEW.acreg;
    incoming.remark      := :NEW.remark;
    incoming.timestamp   := :NEW.timestamp;

-- Convert direction. Historically we have used I for inbound and O for outbound.
    IF (incoming.leg = 'ARR') THEN -- YVR Passes ARR for inbound flights
        l_direction := 'I';
      l_prev_airport := nvl (incoming.via_6, nvl (incoming.via_5, nvl (incoming.via_4, nvl (incoming.via_3, nvl (incoming.via_2, incoming.via_1)))));
    ELSIF (incoming.leg = 'DEP') THEN
        l_direction:= 'O';
    ELSE -- YVR passes DEP for outbound
        add_app_msg.log_oracle_error ('TIA_YVR_FIDS_INFO', 'Direction not translated for flight ' || incoming.flight_no, -20000);
        RAISE_APPLICATION_ERROR ('-20000', 'Direction not translated for flight '    || incoming.flight_no);
    END IF;

    l_dests := incoming.via_1;

    IF incoming.via_2 IS NOT NULL THEN
        l_dests := l_dests || ',' || incoming.via_2;
    ELSIF incoming.via_3 IS NOT NULL THEN
        l_dests := l_dests || ',' || incoming.via_3;
    ELSIF incoming.via_4 IS NOT NULL THEN
        l_dests := l_dests || ',' || incoming.via_4;
    ELSIF incoming.via_5 IS NOT NULL THEN
      l_dests := l_dests || ',' || incoming.via_5;
    ELSIF incoming.via_6 IS NOT NULL THEN
      l_dests := l_dests || ',' || incoming.via_6;
    END IF;

    l_interface_id := flightinfo.yvr_interface;


-- Call the FIDS Interface
    flightinfo.fids( p_carrier_code         => substr (incoming.carrier, 1, 3)
                   ,p_flight_no             => substr (incoming.flight_no, 4, 7)
                   ,p_flight_date           => to_date (incoming.flight_date, 'DDMONRR')
                   ,p_trip_date             => null
                   ,p_trip_number           => null
                   ,p_schd_flight_time      => to_date (incoming.sch_dt||incoming.sch_tm, 'DDMONRRHH24MI')
                   ,p_est_flight_time       => to_date (incoming.est_dt||incoming.est_tm, 'DDMONRRHH24MI')
                   ,p_act_flight_time       => to_date (incoming.act_dt||incoming.act_tm, 'DDMONRRHH24MI')
                   ,p_airport_code          => trim (config.get_value ('FIDS_IF_' || l_interface_id))
                   ,p_direction             => l_direction
                   ,p_destinations          => l_dests
                   ,p_gate                  => incoming.gate
                   ,p_apron                 => NULL
                   ,p_aircraft_type         => incoming.actype
                   ,p_aircraft_registration => incoming.acreg
                   ,p_codeshare_flight      => NULL
                   ,p_primary_carrier       => NULL
                   ,p_primary_flight_no     => NULL
                   ,p_primary_flight_date   => NULL
                   ,p_prev_airport          => l_prev_airport
                   ,p_prev_departure_time   => NULL
                   ,p_flight_status         => NULL
                   ,p_message_number        => incoming.id
                   ,p_interface_id          => l_interface_id);
END;
/

PROMPT CREATE OR REPLACE TRIGGER tib_yvr_fids_info

CREATE OR REPLACE TRIGGER tib_yvr_fids_info
BEFORE INSERT ON yvr_fids_info
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
/*****************************************************************************
*
* Filename		- $Workfile$
*
* Project Name		- UltraTrak Database
*
* Source Code Identifier	- 0/3/300/8010/112
*
* Description	        - Generates a new id for the incomming fids record
*
* Language		- Oracle PL/SQL
*
* File History		- For the development history run the Show History report in PVCS
*
* (c) Ultra Electronics Limited, 2002
*
*****************************************************************************/

/*****************************************************************************
*
* Trigger       - tib_yvr_fids_info 
*
* Description	- Generates a new id for the incomming fids record
*
* CR History:
* Date	Name		CR Number	CR Description
*
*****************************************************************************/
BEGIN
    IF (:NEW.id IS NULL OR :NEW.id = 0 ) THEN
        SELECT
            flight_info_id.NEXTVAL
        INTO
            :NEW.id
        FROM
            dual;
    END IF;
END;
/

