
/*****************************************************************************
*
* Filename         - $Workfile:   bag_alert_v4.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       - 
*
* Design Spec
* Section          -
*
* Contents         - Live Schema View
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/

PROMPT Creating view   BAG_ALERT_V4   ;

CREATE OR REPLACE FORCE VIEW   BAG_ALERT_V4 (
bag_tag ,  flight_no, flight_date, pax_initials,
pax_surname, pax_class, bag_destination, alert_on_action, 
no_warnings, last_time_stamp, time_bag_stored, uld_no, uld_hold_no , agent_code
) as
SELECT
/*+ ORDERED USE_NL(bag bag_alert) */
/*+ INDEX (bag pk_bag) */
b.bag_tag, f.flight_no, f.flight_date, p.pax_initials, p.pax_surname,
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),
b.bag_destination, ba.alert_on_action, ba.no_warnings,
to_char(ba.last_time_stamp, 'HH24:MI' ), to_char(b.time_bag_stored, 'HH24:MI'),
u.uld_no, u.uld_hold_no  , fa.agent_code
from bag_alert  ba , bag b , pax p , flight f , ulds u, flight_access fa
WHERE f.flight_id = p.flight_id AND f.flight_id = u.flight_id
AND p.pax_id = b.pax_id AND b.pax_id = ba.pax_id AND p.pax_id = ba.pax_id
AND b.uld_no = u.uld_no AND b.bag_tag = ba.bag_tag AND f.flight_id = fa.flight_id ;
