/**********************************************************************************************
* Filename        - $Workfile:   aircraft_side_bag_v1.sql  $
*
*                     Name: Yomi Kogbe    Date: 03/05/00  Version1.0     CR Number:14954
*                     Martin Helliwell          09/04/02         1.1               15336
* 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 aircraft_side_bag;
CREATE OR REPLACE FORCE VIEW aircraft_side_bag
           (bag_tag,
            destination,
            class,
            pax_id, 
            load_time,
            location,
            uld_number,
            uld_seq,          
            flight_id,
            booked_flight_id
          )
as          
select   DISTINCT b.bag_tag ,
         b.bag_destination,
         DECODE(NVL(rtrim(p.pax_bags_class),p.pax_class), p.pax_class ,NVL(rtrim(p.pax_bags_class),p.pax_class), p.pax_class || '/' || p.pax_bags_class),
         p.pax_id,
         to_char(b.time_loaded, 'hh24:mi'),
         l.location_description,
         b.uld_no,
         b.bag_sequence_no,
         f.flight_id,
         p.booked_flight_id
 from    flight f, bag b, pax p, location l, ulds u
where    f.flight_id = p.flight_id
  and    p.pax_id = b.pax_id
  and    b.location_code = l.location_code
  and    b.uld_no = u.uld_no(+)
  and    b.loaded_status = 'Y'
  and    b.authority_to_load_dcs = 'Y'
  and    u.uld_loaded_status = 'Y';

