/*****************************************************************************
*
* Filename         - $Workfile:   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
*
*****************************************************************************/

PROMPT Creating view pax_v1;

CREATE OR REPLACE FORCE VIEW pax_v1 (
  pax_id,
  flight_id,
  pax_initials,
  pax_surname,
  pax_status,
  time_pax_stored,
  no_of_bags,
  no_same_surname,
  pax_destination,
  pax_seat_no,
  pax_class,
  pax_sequence_no,
  pax_security_no,
  pax_name_record,
  pax_security_status,
  departure_airport_code,
  inbound_flight_no,
  inbound_date,
  inbound_boarding_point,
  delete_marker,
  authority_to_load_dcs,
  connection_type,
  BAG_EXCEP_CODE,
  embassy,
	INCLUDE_IN_MWS_PAX_LIST,
  vfdb
) as
  select 
    pax.pax_id,
    pax.flight_id,
    pax.pax_initials,
    NVL(pax.pax_surname,'NOTSUPPLIED'),
    pax.pax_status,
    to_char(pax.time_pax_stored, 'HH24:MI'),
    pax.no_of_bags,
    pax.no_same_surname,
    pax.pax_destination,
    pax.pax_seat_no,
    DECODE(NVL(rtrim(pax.pax_bags_class),pax.pax_class), pax.pax_class ,
				NVL(rtrim(pax.pax_bags_class),pax.pax_class), pax.pax_class || '/' || pax.pax_bags_class),
    derive_seq_no(pax.pax_sequence_no, pax.pax_dcs_sequence_no),
    pax.pax_security_no,
    pax.pax_name_record,
    pax.pax_security_status,
    pax.departure_airport_code,
    pax.inbound_flight_no,
    pax.inbound_date,
    pax.inbound_boarding_point,
    pax.delete_marker,
    pax.authority_to_load_dcs,
    pax.connection_type,
    e.bag_excep_code,
    pax.embassy,
		nvl(INCLUDE_IN_MWS_PAX_LIST,'Y'),
    pax.vfdb
  from
    pax , bag_exception_types e
		where pax.exception_type_id = e.BAG_EXCEP_ID(+) ;
