/*****************************************************************************
*
* Filename         - $Workfile:   onward_flight_v2.sql  $
*
*                     Name              Date      Version     CR Number
*                     Martin Helliwell  20/12/01    1.2        15540
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         - Live Schema View
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2001
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   onward_flight_v2.sql  $  $Revision:   1.2  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /pvcsarch/db_ora/db/source/live/views/onward_flight_v2.svl  $
* 
*    Rev 1.2   20 Dec 2001 13:34:44   tagtrak
* OR:
* CR: 15540
* REV: 1.2
* DESC: Use functions to obtain counts
*
*****************************************************************************/
PROMPT Creating view onward_flight_v2 

CREATE OR REPLACE FORCE
VIEW onward_flight_v2 
(flight_id   	  
,flight_no    		 
,flight_date			
,uld_no        	
,uld_class_list	
,ofn1          	
,dest1         	
,economy1      	
,first1        	
,business1     	
,other1        	
,total1					
,ofn2          	
,dest2         	
,economy2      	
,first2        	
,business2     	
,other2        	
,total2
) AS
SELECT b.flight_id															
,b.flight_no															
,b.flight_date														
,b.uld_no																
,u.uld_class_list												
,DECODE(o.dest_order,1,o.no,NULL)					
,DECODE(o.dest_order,1,o.dest,NULL)				
,DECODE(o.dest_order,1
        ,onward_count(b.flight_id,'Y',b.uld_no,o.no,o.dest,o.dest_order)
        ,NULL)			
,DECODE(o.dest_order,1
        ,onward_count(b.flight_id,'F',b.uld_no,o.no,o.dest,o.dest_order)
        ,NULL)			
,DECODE(o.dest_order,1
        ,onward_count(b.flight_id,'C',b.uld_no,o.no,o.dest,o.dest_order)
        ,NULL)			
,DECODE(o.dest_order,1
        ,onward_count(b.flight_id,'O',b.uld_no,o.no,o.dest,o.dest_order)
        ,NULL)			
,DECODE(o.dest_order,1
        ,onward_count(b.flight_id,'T',b.uld_no,o.no,o.dest,o.dest_order)
        ,NULL)			
,DECODE(o.dest_order,2,o.no,NULL)					
,DECODE(o.dest_order,2,o.dest,NULL)				
,DECODE(o.dest_order,2
        ,onward_count(b.flight_id,'Y',b.uld_no,o.no,o.dest,o.dest_order)
        ,NULL)			
,DECODE(o.dest_order,2
        ,onward_count(b.flight_id,'F',b.uld_no,o.no,o.dest,o.dest_order)
        ,NULL)			
,DECODE(o.dest_order,2
        ,onward_count(b.flight_id,'C',b.uld_no,o.no,o.dest,o.dest_order)
        ,NULL)			
,DECODE(o.dest_order,2
        ,onward_count(b.flight_id,'O',b.uld_no,o.no,o.dest,o.dest_order)
        ,NULL)			
,DECODE(o.dest_order,2
        ,onward_count(b.flight_id,'T',b.uld_no,o.no,o.dest,o.dest_order)
        ,NULL)			
FROM bag_v2 b
,onward_flight o
,uld_dests  u
WHERE loaded_status = 'Y'
AND delete_marker = 0
AND o.pax_id = b.pax_id
AND o.dest_order IN (1,2)
AND b.uld_no = u.uld_no
AND b.flight_id = u.flight_id
AND (b.bag_destination = u.uld_destination
 OR (b.uld_no IN (SELECT hold_no FROM holds)
        AND u.uld_destination = '*'))
GROUP BY b.flight_id
,b.flight_no
,b.flight_date
,b.uld_no
,u.uld_class_list
,o.no
,o.dest
,o.dest_order
/
