/*****************************************************************************
*
* Filename         - $Workfile : reflight_flight_v1.sql $
*
* Project Name     - ULTRATRAK(Oracle Database)
*
* Source Code
* Identifier       - UAS/FARFL
*
* Description      - Re-flighting Module
*
* Language         - SQL Script
*
* File History     - Initial Version
*
* Coptright Ultra Electronics Airport Systems 2005
*
*****************************************************************************/
/****************************************************************************
* View             reflight_flight_v1
*
* Description:     Re-flighting module addition.
*                  This view replaces The query in ReFltBagHistory.asp 
*                  It returns flight information and searches across the live and arch schema.
*                  
*
* CA History
* Date      Name              CA Number               CA Description
* 30/09/05  Himanshu Saini    FALCP/CA/1560           CA01560 - Arthur Reflighting information in live and arch
*
*****************************************************************************/
PROMPT Creating view reflight_flight_v1

CREATE OR REPLACE FORCE VIEW reflight_flight_v1
( 
 flight_id
 ,flight_details
 ,site_id
 ,bag_tag
)
AS
SELECT rb.flight_id AS flight_id
     ,(f.flight_no||' '||f.flight_date||' '||f.flight_sch_time) AS flight_details
     ,rb.site_id
     ,rb.bag_tag
 FROM reflight_bag rb, flight f   
WHERE rb.flight_id = f.flight_id
UNION                       
SELECT rb.flight_id AS flight_id
     ,(f.flight_no||' '||f.flight_date||' '||f.flight_sch_time) AS flight_details
     ,rb.site_id
     ,rb.bag_tag     
 FROM live.reflight_bag rb, arch.flight f 
WHERE rb.flight_id = f.flight_id
;
