
rem *********************************************************************

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Filename:             inbound_flight_loop_v1.sql                  *

rem * Module Name:          inbound_flight_loop_v1                      *

rem * Subsystem Name:       views                                       *

rem * Originating Author:   Billy Hayer                                 *

rem * Design Reference:     FAS/BBTRK/SD/225                            *

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Description:                                                      *

rem *  View required for inbound flight screens- the list of inbound    *

rem * flights along with the agents privileged to see them              *

rem *********************************************************************

rem *                                                                   *

rem * Program Identification Array:                                     *

rem *                                                                   *

rem * "%Z%%M% VERSION %I% %E% %U%"

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Modification History:                                             *

rem *                                                                   *

rem * Rel Date/Time      Proj/Id   CA Ref/Details Of Change             *

rem * --- -------------- --------  ------------------------------------ *

rem * 1.0 28 May 03                File initially checked in            *

rem *********************************************************************/
PROMPT Creating view inbound_flight_loop_v1
/*

 Select flight_id,flight_no,flight_date,dep_time,agent_code,flight_status
 from the flight table with access restricted only to Inbound Flights for
 the Local Site and for the current Agent.
*/ 
CREATE OR REPLACE FORCE VIEW inbound_flight_loop_v1
AS
SELECT 
       fi.flight_id, fi.flight_no,
       TO_CHAR (NVL(get_best_time(fi.flight_no, fi.flight_date, fi.site_id), 
                    fi.departure_time ), 'DDMON') FLIGHT_DATE,
       TO_CHAR (NVL(get_best_time(fi.flight_no, fi.flight_date, fi.site_id), 
                    fi.departure_time ), 'HH24MI') DEP_TIME,     
       fa.agent_code,
       fi.flight_status
FROM flight_access fa, flight_inbound_v1 fi, messages m
WHERE
     fi.flight_id = fa.flight_id
AND  fa.flight_id = m.flight_id
AND  m.message_direction = 'I'
AND  m.processed_state <> 'R'
AND  m.message_type = 'BMM';

