CREATE OR REPLACE VIEW bag_v16 (
  flight_no,
  flight_id,
  flight_date,
  uld_no,
  uld_type,
  status,
  total_bags,
  bag_tag,
  bag_tag1,
  bag_tag2a,
  bag_tag2b,
  bag_tag3,
  pax_name,
  class,
  bag_type,
  sequence_no,
  security,
  bulk_group
) AS
select
  flight.flight_no,
  flight.flight_id,
  flight.flight_date,
  bag.uld_no,
  ulds.uld_type,
	ulds.close_flag,
	ulds.uld_no_of_bags,
 	CONCAT(RTRIM(nvl(bag.bag_tag2a,bag.bag_tag2b)),LPAD(TO_CHAR(bag.bag_tag3),6,0)) BAG_TAG,
  bag.bag_tag1,
  bag.bag_tag2a,
  bag.bag_tag2b,
  LPAD(TO_CHAR(bag.bag_tag3),6,0),
  CONCAT(RTRIM(pax.pax_surname),CONCAT(' ',RTRIM(pax.pax_initials))) PAX_NAME,
  pax.pax_class,
  bag.bag_exception,
  bag.bag_sequence_no,
  bag.security_clearance_status,
	DECODE(SUBSTR(bag.uld_no,2,4),'BULK',1,0) BULK_GROUP
FROM  bag, pax, flight, ulds
WHERE pax.pax_id = bag.pax_id
AND   flight.flight_id  = pax.flight_id
AND 	flight.flight_id = ulds.flight_id
AND	  bag.uld_no = ulds.uld_no
AND   bag.delete_marker = '0'
ORDER BY
	flight.flight_no,
	BULK_GROUP,
	bag.uld_no,
	BAG_TAG,
	PAX_NAME
/

