/*****************************************************************************
*
* Filename         - $Workfile:   standby_bags_v1.sql  $
*
*                     Name Yomi Kogbe     Date 03/05/00  Version     CR Number 15006
*                     Martin Helliwell    23/07/01                   15467
* 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 Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/



PROMPT Creating view standby_bags_v1;
CREATE OR REPLACE FORCE VIEW standby_bags_v1
            (bag_tag,
             destination,
             class,
             pax_id, 
             bsm_time,
             pax_surname,
             pax_initials,
             pax_sequence_no,
             flight_id,
             inbound_flight_no)
as          
select   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_bag_stored, 'hh24:mi'),
         p.pax_surname,
         p.pax_initials,
         p.pax_sequence_no,
         f.flight_id,
         p.inbound_flight_no
 from    flight f,  bag b, pax p, location l
where    b.pax_id  = p.pax_id
	and		 b.delete_marker = 0
  and    b.location_code = l.location_code
  and    p.flight_id = f.flight_id
  and    p.pax_status = 'S';







