/*****************************************************************************
*
* Filename         - $Workfile:   bmm_contents_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
*
*****************************************************************************/
CREATE OR REPLACE FORCE VIEW bmm_contents_v1 (
  inbound_flight_id,
  site_id,
  inbound_flight_no,
  inbound_flight_date,
  originating_airport,
  uld_no,
  uld_position,
  uld_type,
  uld_category,
  inbound_class,
  outbound_flight_no,
  outbound_flight_date,
  outbound_destination,
  outbound_class,
  inbound_pax_id,
  inbound_pax_surname,
  inbound_pax_initials,
  inbound_pax_frequent_flyer,
  bag_tag,
  bag_load_sequence,
  bag_exception_type,
  bag_security_status
) AS
SELECT f.flight_id,
       f.site_id,
       m.flight_number,
       m.flight_date,
       m.originating_airport,
       NVL(u.uld_number, 'UNCONTAINED'),
       u.uld_position,
       u.uld_type,
       u.uld_category,
       ud.class_list,
       od.onward_flight_no,
       od.onward_flight_date,
       od.onward_destination,
       od.onward_class,
       p.inbound_pax_id,
       p.pax_surname,
       p.pax_initials,
       p.frequent_flyer,
       b.bag_tag,
       b.bag_load_sequence,
       b.bag_exception_type,
       b.bag_security_status
FROM messages m,
     flight f,
     inbound_uld u,
     inbound_pax p,
     inbound_bag b,
     inbound_uld_dests ud,
     inbound_pax_onward_dests od
WHERE m.flight_id = f.flight_id
  AND p.source_message_id = m.message_id
  AND p.inbound_flight_id = f.flight_id
  AND od.inbound_pax_id(+) = p.inbound_pax_id
  AND Nvl(od.destination_sequence(+), 2) = 2
  AND b.inbound_pax_id = p.inbound_pax_id
  AND b.inbound_uld_id = u.inbound_uld_id(+)
  AND b.inbound_uld_id = ud.inbound_uld_id(+)
  AND ud.destination_sequence(+) = 1
  AND m.processed_state = 'P'
  AND m.message_direction = 'I'
  AND f.flight_type = 'I'
ORDER BY f.flight_id ASC,
         f.site_id ASC,
         m.flight_number ASC,
         m.flight_date ASC,
         u.uld_number ASC,
         od.onward_flight_no ASC,
         od.onward_flight_date ASC,
         p.inbound_pax_id ASC,
         b.bag_tag ASC;

