PROMPT Creating view late_bags_report_v...

CREATE OR REPLACE FORCE VIEW late_bags_report_v AS
   SELECT f.flight_id,
          f.flight_no,
          f.flight_date,
          bag_report_utils.get_flight_route (f.flight_id)  flight_route,
          b.bag_tag,
          b.bag_tag1,
          b.bag_tag2a,
          b.bag_tag2b,
          LTRIM(TO_CHAR(b.bag_tag3, '000000')) bag_tag3,
          b.bag_destination,
          b.uld_no,
          p.pax_initials,
          p.pax_surname,
          p.pax_sequence_no,
          b.time_loaded,
          tu.loginname,
          ft.flt_trace_time                                flight_closure_time
   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_tag2b              = bt.bag_tag2b
   AND    b.bag_tag3               = bt.bag_tag3
   AND    b.pax_id                 = bt.pax_id
   AND    bt.user_id               = tu.user_id
   AND    b.loaded_status          = 'Y'
   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 (fts.flt_trace_time)
                                      FROM   flight_trace fts
                                      WHERE  fts.flight_id         = f.flight_id
                                      AND    fts.new_flight_status = 'FC')
   AND    b.time_loaded           >= ft.flt_trace_time
   AND    bt.trace_action         IN ('LOAD', 'LOAD WHERE', 'MWS_LOAD', 'FORCED')
   AND    bt.record_id             = (SELECT MAX (bts.record_id)
                                      FROM   bag_trace bts
                                      WHERE  bts.bag_tag2b     = b.bag_tag2b
                                      AND    bts.bag_tag3      = b.bag_tag3
                                      AND    bts.pax_id        = b.pax_id
                                      AND    bts.trace_action IN ('LOAD', 'LOAD WHERE', 'MWS_LOAD', 'FORCED'));
