------------------------------------------------------------------------------------------
-- Filename         - $Workfile:   late_bags_v.sql  $
--
--                    Name: Nigel Smith    Date: 24/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:   late_bags_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/late_bags_v.sql  $
-- 
------------------------------------------------------------------------------------------

PROMPT Creating view late_bags_v...

CREATE OR REPLACE FORCE VIEW late_bags_v AS
   SELECT   TO_CHAR (NULL)                                  bag_tag,
            TO_CHAR (NULL)                                  bag_destination,
            TO_CHAR (NULL)                                  uld_no,
            TO_CHAR (NULL)                                  pax_initials,
            TO_CHAR (NULL)                                  pax_surname,
            TO_NUMBER (NULL)                                pax_sequence_no,
            TO_DATE (NULL)                                  time_loaded,
            TO_DATE (NULL)                                  flight_closure_time,
            TO_CHAR (NULL)                                  loginname,
            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,
            b.bag_destination,
            b.uld_no,
            p.pax_initials,
            p.pax_surname,
            p.pax_sequence_no,
            b.time_loaded,
            ft.flt_trace_time                               flight_closure_time,
            tu.loginname,
            f.flight_id,
            f.flight_no,
            bag_report_utils.get_flight_route (f.flight_id) flight_route
   FROM     bag           b,
            pax           p,
            flight        f,
            bag_trace     bt,
            flight_trace  ft,
            tagtrak_users tu
   WHERE    b.pax_id                     = p.pax_id
   AND      p.flight_id                  = f.flight_id
   AND      f.flight_id                  = ft.flight_id
   AND      b.bag_tag                    = bt.bag_tag
   AND      b.pax_id                     = bt.pax_id
   AND      bt.user_id                   = tu.user_id
   AND      b.loaded_status         NOT IN ('N', 'U')
   AND      b.delete_marker             != 1
   AND      p.delete_marker             != 1
   AND      b.authority_to_load_dcs      = 'Y'
   AND      f.flight_status              = 'FC'
   AND      ft.new_flight_status         = 'FC'
   AND      ft.flt_trace_time           >= (SELECT MAX (ft2.flt_trace_time)
                                            FROM   flight_trace ft2
                                            WHERE  ft2.flight_id         = f.flight_id
                                            AND    ft2.new_flight_status = 'FC')
   AND      b.time_loaded               >= ft.flt_trace_time
   AND      bt.trace_action              = 'LOAD'
   AND      bt.bag_trace_time           >= (SELECT MAX (bt2.bag_trace_time)
                                            FROM   bag_trace bt2
                                            WHERE  bt2.bag_tag      = b.bag_tag
                                            AND    bt2.pax_id       = b.pax_id
                                            AND    bt2.trace_action = 'LOAD')
/
