/*****************************************************************************
*
* Filename         - $Workfile:   grouped_bags_v1.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - Rob Cooke
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle database)
*
* Contents         - Live schema view
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/

CREATE OR REPLACE VIEW grouped_bags_v1
(bag_tag, location_code, inbound_flight_no, inbound_boarding_point, bag_trace_time, flight_no, 
flight_destination, pax_id, flight_id, group_no, airport_code)
AS
SELECT b.bag_tag bag_tag,
       bt.location_code location_code,
       p.inbound_flight_no inbound_flight_no,
       p.inbound_boarding_point inbound_boarding_point,
       bt.bag_trace_time bag_trace_time,
       f.flight_no flight_no,
       fd.flight_destination flight_destination,
       p.pax_id pax_id,
       f.flight_id flight_id,
       b.group_no group_no,
       bt.airport_code airport_code
FROM bag b,
     flight f,
     pax p,
     flight_dests fd,
     bag_trace bt
WHERE b.pax_id = p.pax_id
AND p.flight_id = f.flight_id
AND fd.flight_id = f.flight_id
AND fd.dest_order = '1'
AND substr(bt.bag_tag, 2) = substr(b.bag_tag, 2)
AND bt.pax_id = b.pax_id
AND bt.bag_trace_time = (SELECT MAX(bt1.bag_trace_time)
                         FROM bag_trace bt1
                         WHERE substr(bt.bag_tag, 2) = substr(bt1.bag_tag, 2)
                         AND bt1.trace_action = 'GROUPED'
                         AND bt1.error_id = '0'
                         AND bt1.pax_id = bt.pax_id)
UNION
SELECT ub.bag_tag bag_tag,
       bt.location_code location_code,
       NULL inbound_flight_no,
       NULL inbound_boarding_point,
       bt.bag_trace_time bag_trace_time,
       f.flight_no flight_no,
       fd.flight_destination flight_destination,
       0 pax_id,
       ub.flight_id flight_id,
       ub.group_no group_no,
       bt.airport_code airport_code
FROM unknown_bag ub,
     flight f,
     flight_dests fd,
     bag_trace bt
WHERE ub.flight_id = f.flight_id (+)
AND fd.flight_id (+) = f.flight_id
AND fd.dest_order (+) = '1'
AND ub.ok_to_fly = '0'
AND substr(bt.bag_tag, 2) = substr(ub.bag_tag, 2)
AND bt.flight_id_scan = ub.flight_id
AND bt.bag_trace_time = (SELECT MAX(bt1.bag_trace_time)
                         FROM bag_trace bt1
                         WHERE substr(bt.bag_tag, 2) = substr(bt1.bag_tag, 2)
                         AND bt1.flight_id_scan = bt.flight_id_scan
                         AND bt1.trace_action = 'GROUPED'
                         AND bt1.error_id = '0');
