/*****************************************************************************
*
* Filename         - $Workfile:   flights_linked_v1.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
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
CREATE OR REPLACE FORCE VIEW flights_linked_v1
(
         out_flight_id,
         in_flight_id
) as
(SELECT  fo.flight_id,
         fi.flight_id
FROM     flight_inbound_v1 fi,
         flight_outbound_v1 fo,
         inbound_pax ip,
         inbound_pax_onward_dests ipod
WHERE    fi.flight_id = ip.inbound_flight_id
  AND    ipod.inbound_pax_id = ip.inbound_pax_id
  AND    ipod.destination_sequence = 1
  AND    ipod.onward_flight_date = fo.flight_date
  AND    ipod.onward_flight_no = fo.flight_no
  AND    ip.result_pax_status IN ('CREATED', 'AMENDED', 'UNPROCESSED')
  AND    fi.site_id = fo.site_id
GROUP BY fi.flight_id,
         fo.flight_id
UNION
SELECT   fo.flight_id,
         fi.flight_id
FROM     flight_inbound_v1 fi,
         flight_outbound_v1 fo
WHERE    fi.next_leg_id = fo.flight_id);
/

