------------------------------------------------------------------------------------------
-- Filename         - $Workfile:   missing_bags_interline_v.sql  $
--
--                    Name: Nigel Smith    Date: 19/03/04    Version 1.0
--
-- Author           - See revision history below.
-- 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, 2004
--
------------------------------------------------------------------------------------------
-- @(#)$Workfile:   missing_bags_interline_v.sql  $  $Revision:   1.0  $
--
-- Revision History
--
-- Note that user tagtrak is many users. For these users the user name is entered as the
-- last line in the change description.
--
-- $Log:   UltraTrak Merge/Database/live_schema/views/missing_bags_interline_v.sql  $
--
------------------------------------------------------------------------------------------

PROMPT Creating view missing_bags_interline_v...

CREATE OR REPLACE FORCE VIEW missing_bags_interline_v AS
   SELECT   TO_CHAR (NULL)                                  bag_tag,
            TO_CHAR (NULL)                                  bag_tag1,
            TO_CHAR (NULL)                                  bag_tag2a,
            TO_CHAR (NULL)                                  bag_tag2b,
            TO_CHAR (NULL)                                  bag_tag3,
            TO_CHAR (NULL)                                  bag_destination,
            TO_CHAR (NULL)                                  pax_initials,
            TO_CHAR (NULL)                                  pax_surname,
            TO_CHAR (NULL)                                  pax_class,
            TO_CHAR (NULL)                                  loaded_status,
            TO_CHAR (NULL)                                  inbound_flight_no,
            TO_CHAR (NULL)                                  inbound_boarding_point,
            f.flight_id,
            f.flight_no,
            bag_report_utils.get_flight_route (f.flight_id) flight_route
   FROM     flight f
   UNION
   SELECT   b.bag_tag              bag_tag,
            b.bag_tag1,
            b.bag_tag2a,
            b.bag_tag2b,
            LTRIM(TO_CHAR(b.bag_tag3, '000000')),
            b.bag_destination,
            p.pax_initials,
            p.pax_surname,
            p.pax_class,
            b.loaded_status,
            p.inbound_flight_no,
            p.inbound_boarding_point,
            f.flight_id,
            f.flight_no,
            bag_report_utils.get_flight_route (f.flight_id) flight_route
   FROM     bag    b,
            pax    p,
            flight f
   WHERE    b.pax_id                     = p.pax_id
   AND      p.flight_id                  = f.flight_id
   AND      b.loaded_status         NOT IN ('Y', 'F')
   AND      b.delete_marker             != 1
   AND      p.delete_marker             != 1
   AND      b.authority_to_load_dcs      = 'Y'
   AND      f.carrier_alpha_code        != NVL (SUBSTR (p.inbound_flight_no, 1, 3), f.carrier_alpha_code)
/
