/*****************************************************************************
*
* Filename         - $Workfile:   company_mail_v1.sql  $
*
*                    Name: Ian Byass  Date:03/03/05  Version 1.0     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 
*
* Design Spec
* Section          - 
*
* Contents         - Live Schema View
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/

PROMPT Creating view company_mail_v1

CREATE OR REPLACE FORCE VIEW company_mail_v1
(  flight_id
 , booked_flight_id
 , bag_tag
 , destination
 , class
 , pax_id
 , load_time
 , location
 , uld_number
 , uld_seq
)
as
SELECT a.flight_id ,
       a.booked_flight_id ,
       b.bag_tag ,
       b.bag_destination ,
       DECODE(NVL(rtrim(a.pax_bags_class),a.pax_class), a.pax_class ,NVL(rtrim(a.pax_bags_class),a.pax_class), a.pax_class || '/' || a.pax_bags_class),
       a.pax_id, 
       to_char(b.time_loaded, 'hh24:mi'),
       c.location_description,
       b.uld_no ,
       b.bag_sequence_no
 FROM  pax A, bag B, location c
where  b.bag_exception = 'MAIL'
  and  b.delete_marker = 0
  AND  b.authority_to_load_dcs = 'Y'
  AND  A.pax_id = B.pax_id
  and  b.location_code = c.location_code;
  

