PROMPT Creating view pax_bag_manifest_v1

CREATE OR REPLACE FORCE VIEW pax_bag_manifest_v1
(bag_type_ind, bag_type, pax_id, flight_id, flight_no, destination, flight_date, pax_surname, pax_class, pax_seat_no, pax_sequence_no, pax_security_no, bag_source_indicator, pax_destination, bag_tag, bag_tag1, bag_tag2a, bag_tag2b, bag_tag3, uld_no, loaded_time, loaded_by, bag_exception, label,
	 accompanied_bags)
AS
SELECT 'A', 'Bags loaded from baggage hall',
     p.pax_id,
     f.flight_id,
     f.flight_no,
     bag_report_utils.get_flight_route (f.flight_id) destination,
     f.flight_date,
     p.pax_surname,
     Decode(rtrim(p.pax_class),rtrim(p.pax_bags_class),
                  p.pax_class,p.pax_class||'/'||p.pax_class) pax_class,
     p.pax_seat_no,
     p.pax_sequence_no,
     p.pax_security_no,
     b.bag_source_indicator,
     p.pax_destination,
     b.bag_tag bagtag,
     b.bag_tag1,
     b.bag_tag2a,
     b.bag_tag2b,
     LTRIM(TO_CHAR(b.bag_tag3, '000000')),
     u.uld_no,
     TO_CHAR (b.time_loaded, 'HH24:MI') loaded_time,
     tu.loginname                            loaded_by,
     Decode(b.bag_exception,'GATE','GATE',NULL) exception,
     To_Char (NULL) label,
	   NVL(e.accompanied_bags,'Y')
   FROM  ulds                  u,
         pax                   p,
         bag                   b,
         uld_recorded_id_codes uc,
         bag_trace             btl,
         tagtrak_users         tu,
         flight                f,
         bag_exception_types   e
   WHERE    u.flight_id              = f.flight_id
   AND      p.pax_id                 = b.pax_id
   AND      u.uld_no                 = b.uld_no
   AND      uc.uld_id_code(+)        = u.uld_id_code
   AND      b.bag_tag2b              = btl.bag_tag2b
   AND      b.bag_tag3               = btl.bag_tag3
   AND      b.pax_id                 = btl.pax_id
   AND      btl.user_id              = tu.user_id
   AND      p.flight_id              = f.flight_id
   AND      b.loaded_status          = 'Y'
   AND    ( RTrim(b.bag_exception)   NOT IN( 'CREW' ,'GATE', 'MAIL', 'RUSH')
   OR      b.bag_exception IS NULL)
   AND      b.bag_exception          = e.BAG_EXCEP_CODE(+)
   AND      btl.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
                                       )
UNION
SELECT 'B', 'Gate Bags',
     p.pax_id,
     f.flight_id,
     f.flight_no,
     bag_report_utils.get_flight_route (f.flight_id) destination,
     f.flight_date,
     p.pax_surname,
     DECODE (rtrim(p.pax_class),rtrim(p.pax_bags_class),
                   p.pax_class,p.pax_class||'/'||p.pax_class) pax_class,
     p.pax_seat_no,
     p.pax_sequence_no,
     p.pax_security_no,
     b.bag_source_indicator,
     p.pax_destination,
     b.bag_tag bagtag,
     b.bag_tag1,
     b.bag_tag2a,
     b.bag_tag2b,
     LTRIM(TO_CHAR(b.bag_tag3, '000000')),
     u.uld_no,
     TO_CHAR (b.time_loaded, 'HH24:MI') loaded_time,
     tu.loginname                            loaded_by,
     DECODE (b.bag_exception,'GATE','GATE',NULL) exception,
     Nvl( (SELECT DISTINCT 'Y'
          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.error_id     = 0
          AND    bts.trace_action = 'PRINT TAG'
          ),'N') label,
					'Y' accompanied_bags
   FROM  ulds                  u,
         pax                   p,
         bag                   b,
         uld_recorded_id_codes uc,
         bag_trace             btl,
         tagtrak_users         tu,
         flight                f
   WHERE    u.flight_id              = f.flight_id
   AND      p.pax_id                 = b.pax_id
   AND      u.uld_no                 = b.uld_no
   AND      uc.uld_id_code(+)        = u.uld_id_code
   AND      b.bag_tag2b              = btl.bag_tag2b
   AND      b.bag_tag3               = btl.bag_tag3
   AND      b.pax_id                 = btl.pax_id
   AND      btl.user_id              = tu.user_id
   AND      p.flight_id              = f.flight_id
   AND      b.loaded_status          = 'Y'
   AND    ( RTrim(b.bag_exception)   = 'GATE')
   AND      btl.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
                                       );

