/*****************************************************************************
*
* Filename         - $Workfile:   bag_alert_v3.sql  $
*
*                     Name Yomi Kogbe     Date 03/05/00  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 Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/

PROMPT Creating view bag_alert_v3;
CREATE OR REPLACE FORCE VIEW bag_alert_v3
            (bag_tag,
            destination,
            class,
            pax_id,
            load_time,
            location,
            uld_number,
            uld_seq,
            uld_hold_no,
            flight_id,
            booked_flight_id)
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_loaded, 'hh24:mi'),
         l.location_description,
         b.uld_no,
         b.bag_sequence_no,
         u.uld_hold_no,
         p.flight_id,
         p.booked_flight_id
from     bag_alert ba, bag b, pax p, location l, ulds u
where    b.pax_id  =  ba.pax_id
  and    b.bag_tag = ba.bag_tag
  and    b.pax_id  = p.pax_id
  and    b.uld_no         = u.uld_no(+)
  and    b.uld_flight_id  = u.flight_id(+)
  and    b.location_code = l.location_code
  and    ba.alert_on_action IN ('UNLOAD', 'UNKNOWN');
