/*****************************************************************************
*
* Filename         - $Workfile:   offload_bags_v.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 used by the Bags To Be Offloaded
*                    (by passenger and by ULD) Crystal Reports
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2007
*
*****************************************************************************/

PROMPT Creating view offload_bags_report_v 

CREATE OR REPLACE FORCE VIEW offload_bags_report_v AS
SELECT
     p.pax_id,
     f.flight_id,
     f.flight_no,
     f.flight_date,
     p.pax_surname,
     p.pax_seat_no,
     p.pax_sequence_no,
     p.pax_security_no,
     to_char(p.time_pax_stored, 'HH24:MI:SS') timestamp,
     b.bag_tag bagtag,
     b.bag_tag1,
     b.bag_tag2a,
     b.bag_tag2b,
     LTRIM(TO_CHAR(b.bag_tag3, '000000')) bag_tag3,
     b.uld_no,
     b.bag_sequence_no||'/'||u.uld_no_of_bags uld_seq,
     u.uld_hold_no,
     b.bag_sequence_no,
     (select count(*) from holds h where h.hold_no = b.uld_no) hold_count
FROM  flight                f,
      ulds                  u,
      pax                   p,
      bag                   b
WHERE p.flight_id     = f.flight_id
AND   u.flight_id     = p.flight_id
AND   b.pax_id        = p.pax_id
AND   b.uld_no        = u.uld_no
AND   b.loaded_status = 'Y'
AND   EXISTS (SELECT 1
                      FROM bag_alert ba
                     WHERE ba.bag_tag2b = b.bag_tag2b
                       AND ba.bag_tag3 = b.bag_tag3
                       AND ba.pax_id = b.pax_id
                       AND ba.alert_on_action = 'UNLOAD');
