PROMPT Creating view onward_uld_breakdown_v1 

CREATE OR REPLACE FORCE
VIEW onward_uld_breakdown_v1 
(flight_id 	  
,flight_no     
,flight_date	
,uld_no        
,uld_class_list
,row_sequence
,group_col		
,ofn1          
,dest1         
,economy1      
,first1        
,business1     
,other1        
,total1			
,ofn2          
,dest2         
,economy2      
,first2        
,business2     
,other2        
,total2
,uld_no_of_bags
) AS
SELECT t.flight_id                                
,flight_no                           
,flight_date                          
,t.uld_no                                
,SUBSTR(uld_class_list(t.uld_no, t.flight_id),1,10) ULD_CLASS_LIST
,1 								ROW_SEQUENCE
,NULL 						GROUP_COL                      
,flight_no 				OFN1                          
,flight_dests			DEST1
,county           ECONOMY1
,countf           FIRST1                                                          
,countc           BUSINESS1                                                      
,counto           OTHER1                                                          
,total            TOTAL1                                                        
,NULL 						OFN2                              
,NULL 						DEST2                              
,TO_NUMBER(NULL) 	ECONOMY2                                 
,TO_NUMBER(NULL) 	FIRST2                                   
,TO_NUMBER(NULL) 	BUSINESS2                               
,TO_NUMBER(NULL) 	OTHER2                                   
,TO_NUMBER(NULL) 	TOTAL2                                  
,uld_no_of_bags
FROM terminating_counts_v1 t
,ulds
WHERE (t.flight_id, t.uld_no) IN 
	(SELECT DISTINCT flight_id, uld_no FROM onward_flight_v2)
AND ulds.flight_id  = t.flight_id
AND ulds.uld_no = t.uld_no
UNION
SELECT o.flight_id                                
,o.flight_no
,o.flight_date
,o.uld_no
,SUBSTR(uld_class_list(o.uld_no, o.flight_id),1,10) ULD_CLASS_LIST
,1 								ROW_SEQUENCE
,NULL 						GROUP_COL                      
,o.flight_no 			OFN1                              
,d.flight_destination DEST1                              
,0 								ECONOMY1                                 
,0 								FIRST1                                   
,0 								BUSINESS1                               
,0 								OTHER1                                   
,0 								TOTAL1                                  
,NULL 						OFN2                              
,NULL 						DEST2                              
,TO_NUMBER(NULL) 	ECONOMY2                                 
,TO_NUMBER(NULL) 	FIRST2                                   
,TO_NUMBER(NULL) 	BUSINESS2                               
,TO_NUMBER(NULL) 	OTHER2                                   
,TO_NUMBER(NULL) 	TOTAL2                                  
,uld_no_of_bags
FROM onward_flight_v2 o
,flight_dests d 
,ulds
WHERE (o.flight_id, o.uld_no) NOT IN 
	(SELECT flight_id, uld_no FROM terminating_counts_v1)
AND o.flight_id = d.flight_id 
AND d.dest_order = (SELECT MAX(dest_order) FROM flight_dests WHERE flight_id = d.flight_id)
AND ulds.flight_id  = o.flight_id
AND ulds.uld_no = o.uld_no
UNION
SELECT o.flight_id 
,flight_no     
,flight_date	
,o.uld_no        
,uld_class_list
,2	 							ROW_SEQUENCE
,DECODE	(ofn1
				,NULL,onward_flight_no_1
				,ofn1) 		GROUP_COL
,ofn1				
,dest1         
,economy1      
,first1        
,business1     
,other1        
,total1			
,ofn2          
,dest2         
,economy2      
,first2        
,business2     
,other2        
,total2
,uld_no_of_bags
FROM onward_flight_v2 o
,onward_flight_v1
,ulds
WHERE o.flight_id = onward_flight_v1.flight_id (+)
AND ofn2 = onward_flight_no_2 (+)
AND ulds.flight_id  = o.flight_id
AND ulds.uld_no = o.uld_no
/
