/*****************************************************************************
*
* Filename         - $Workfile:   inbound_pax_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 inbound_pax_v1 AS
SELECT ip.source_pax_id AS pax_id
         ,ip.inbound_pax_id
         ,ip.result_pax_id
         ,NVL(p.flight_id, 0) AS flight_id
         ,ip.inbound_flight_id
         ,NVL(p.booked_flight_id, 0) AS booked_flight_id
         ,NVL(ip.pax_initials, p.pax_initials)   AS pax_initials
         ,NVL(ip.pax_surname, p.pax_surname)     AS pax_surname
         ,NVL(p.pax_status, 'N')                 AS pax_status
         ,NVL(p.time_pax_stored, TRUNC(SYSDATE)) AS time_pax_stored
         ,NVL(p.no_of_bags, 0)                   AS no_of_bags
         ,NVL(ip.pax_number_same_surname, p.no_same_surname) AS no_same_surname
         ,NVL(ip.pax_destination, p.pax_destination)   AS pax_destination
         ,p.pax_seat_no
         ,NVL(p.pax_class, '*')      AS pax_class
         ,NVL(p.pax_sequence_no, 0)  AS pax_sequence_no
         ,p.pax_security_no
         ,p.pax_name_record
         ,Nvl(p.pax_security_status, 'U') AS pax_security_status
         ,p.exception_type_id
         ,NVL(p.departure_airport_code, m.originating_airport)   AS departure_airport_code
         ,p.inbound_flight_no
         ,p.inbound_date
         ,p.inbound_boarding_point
         ,NVL(p.delete_marker, 0)    AS delete_marker
         ,NVL(p.authority_to_load_dcs, 'N')    AS authority_to_load_dcs
         ,NVL(ip.pax_full_inits, p.pax_full_inits) AS pax_full_inits
         ,p.onward_transfer
         ,p.pax_dcs_sequence_no
         ,p.onward_flight_count
         ,p.connection_type
         ,p.leg_number
         ,NVL(ip.frequent_flyer, p.freq_flyer) AS freq_flyer
         ,p.pax_bags_class
         ,p.embassy
         ,ip.source_message_id
         ,DECODE(ip.source_message_id, NULL, 'COPY', 'BMM') AS pax_source
         ,ip.error_code
         ,ip.result_pax_status
         ,ip.inbound_pax_status
    FROM inbound_pax ip,
         pax p,
         messages m,
         flight f,
         site_info si
   WHERE ip.source_pax_id = p.pax_id(+)
     AND ip.source_message_id = m.message_id(+)
     AND ip.inbound_flight_id = f.flight_id
     AND f.site_id = si.site_id;

