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

PROMPT Creating view load_v1;

CREATE OR REPLACE FORCE VIEW load_v1 ( flight_id, flight_no, flight_date, pax_initials, pax_surname,
      bag_no, bag_tag1, bag_tag2a, bag_tag2b, bag_tag3, dest, uld_no, bag_seq_no, position, bag_type, security_status,
      bag_exception, bag_security_status, misc_exceptions )
as
select b.flight_id, b.flight_no, b.flight_date, b.pax_initials, b.pax_surname,
       rtrim(b.bag_tag2a) || b.bag_tag3, b.bag_tag1, b.bag_tag2a, b.bag_tag2b, b.bag_tag3a, b.bag_destination, b.uld_no,
       b.bag_sequence_no, u.uld_hold_no,
       decode(b.bag_exception, 'RUSH', 'UNACCOMP', 'MAIL', 'UNACCOMP', 'ACCOMP'),
       decode(b.security_clearance_status, 'N', 'UNCLR', 'C', 'CLR', 'U', 'N/A'),
       b.bag_exception,
       b.security_clearance_status,
       Aggregate_Misc_Exceptions(b.bag_id)
from
    bag_v2 b, ulds u
where
    b.uld_no = u.uld_no
and
    b.flight_id = u.flight_id
/
