/*****************************************************************************
*
* Filename         - $Workfile:   inbound_flight_v.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         -  Live Schema: view on inbound flights
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*****************************************************************************/
CREATE OR REPLACE FORCE VIEW INBOUND_FLIGHT_V ( INBOUND_FLIGHT_ID, 
INBOUND_FLIGHT_NO, INBOUND_FLIGHT_DATE ) 
AS SELECT fin.flight_id
     , fin.flight_no
     , fin.flight_date
FROM
       flight              fin,
       pax                 p
WHERE
       fin.flight_type      = 'I'
AND    fin.flight_status    = 'IB'
AND	   fin.flight_id		> 0
AND    p.flight_id          = fin.flight_id
UNION
SELECT nvl (fin.flight_id, hash_key(CONCAT (p.inbound_flight_no, p.inbound_date))) FLIGHT_ID
     , p.inbound_flight_no
     , p.inbound_date
FROM
       pax                 p,
       flight              fin,
       flight_access       fa
WHERE
	 p.inbound_flight_no   IS NOT NULL
AND    p.inbound_flight_no   = fin.flight_no (+)
AND    p.inbound_date        = fin.flight_date (+)
AND    p.inbound_boarding_point        = multi_site.get_airport_code(fin.site_id(+))
AND    fa.flight_id = p.flight_id
AND    p.delete_marker = 0;

