PROMPT Creating view bag_v7

CREATE OR REPLACE FORCE VIEW bag_v7 	(flight_no
			,flight_id
			,flight_date
			,uld_no
			,bag_1
			,bag_1_1
			,bag_1_2a
			,bag_1_2b
			,bag_1_3
			,bag_2
			,bag_2_1
			,bag_2_2a
			,bag_2_2b
			,bag_2_3
			,bag_3
			,bag_3_1
			,bag_3_2a
			,bag_3_2b
			,bag_3_3
			,bag_4
			,bag_4_1
			,bag_4_2a
			,bag_4_2b
			,bag_4_3
			,bag_5
			,bag_5_1
			,bag_5_2a
			,bag_5_2b
			,bag_5_3
			) AS
SELECT
BD.flight_no
,BD.flight_id
,BD.flight_date
,BD.uld_no
,MAX(DECODE(MOD(ROWNUM,5),1,BD.bag_tag,NULL)) 	BAG_1
,MAX(DECODE(MOD(ROWNUM,5),1,BAG_V2.BAG_TAG1,NULL))
,MAX(DECODE(MOD(ROWNUM,5),1,BAG_V2.BAG_TAG2A,NULL))
,MAX(DECODE(MOD(ROWNUM,5),1,BAG_V2.BAG_TAG2B,NULL))
,MAX(DECODE(MOD(ROWNUM,5),1,BAG_V2.BAG_TAG3A,NULL))
,MAX(DECODE(MOD(ROWNUM,5),2,BD.bag_tag,NULL)) 	BAG_2
,MAX(DECODE(MOD(ROWNUM,5),2,BAG_V2.BAG_TAG1,NULL))
,MAX(DECODE(MOD(ROWNUM,5),2,BAG_V2.BAG_TAG2A,NULL))
,MAX(DECODE(MOD(ROWNUM,5),2,BAG_V2.BAG_TAG2B,NULL))
,MAX(DECODE(MOD(ROWNUM,5),2,BAG_V2.BAG_TAG3A,NULL))
,MAX(DECODE(MOD(ROWNUM,5),3,BD.bag_tag,NULL)) 	BAG_3
,MAX(DECODE(MOD(ROWNUM,5),3,BAG_V2.BAG_TAG1,NULL))
,MAX(DECODE(MOD(ROWNUM,5),3,BAG_V2.BAG_TAG2A,NULL))
,MAX(DECODE(MOD(ROWNUM,5),3,BAG_V2.BAG_TAG2B,NULL))
,MAX(DECODE(MOD(ROWNUM,5),3,BAG_V2.BAG_TAG3A,NULL))
,MAX(DECODE(MOD(ROWNUM,5),4,BD.bag_tag,NULL)) 	BAG_4
,MAX(DECODE(MOD(ROWNUM,5),4,BAG_V2.BAG_TAG1,NULL))
,MAX(DECODE(MOD(ROWNUM,5),4,BAG_V2.BAG_TAG2A,NULL))
,MAX(DECODE(MOD(ROWNUM,5),4,BAG_V2.BAG_TAG2B,NULL))
,MAX(DECODE(MOD(ROWNUM,5),4,BAG_V2.BAG_TAG3A,NULL))
,MAX(DECODE(MOD(ROWNUM,5),0,BD.bag_tag,NULL)) 	BAG_5
,MAX(DECODE(MOD(ROWNUM,5),0,BAG_V2.BAG_TAG1,NULL))
,MAX(DECODE(MOD(ROWNUM,5),0,BAG_V2.BAG_TAG2A,NULL))
,MAX(DECODE(MOD(ROWNUM,5),0,BAG_V2.BAG_TAG2B,NULL))
,MAX(DECODE(MOD(ROWNUM,5),0,BAG_V2.BAG_TAG3A,NULL))
FROM 
	(SELECT DISTINCT
	flight_no
	,bag_v2.flight_id
	,flight_date
	,DECODE(SUBSTR(bag_v2.uld_no,2,4),'BULK',1,0) BULK_GROUP
	,bag_v2.uld_no
	,CONCAT(RTRIM(bag_tag2a),LPAD(TO_CHAR(bag_tag3),6,0))   BAG_TAG
        ,bag_tag2a
        ,bag_tag3
	,ROWNUM
	FROM bag_v2
	,ulds
	WHERE 	bag_v2.flight_id = ulds.flight_id
	AND	bag_v2.uld_no = ulds.uld_no) BD,
        BAG_V2
WHERE 
bag_v2.bag_tag2a = bd.bag_tag2a and bag_v2.bag_tag3 = bd.bag_tag3
GROUP BY
BD.flight_no
,BD.flight_id
,BD.flight_date
,BD.bulk_group
,BD.uld_no
,TRUNC((ROWNUM - 1) / 5) + 1
/
