/*****************************************************************************
*
* Filename         - $Workfile : reflight_pax_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_pax_v1
*
* Description:     Re-flighting module addition.
*                  This view replaces The query in ReFltBagHistory.asp 
*                  It returns returns passenger 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_pax_v1

CREATE OR REPLACE FORCE VIEW reflight_pax_v1
( 
 pax_id
 ,pax_name
 ,site_id
 ,bag_tag
 ,flight_id
)
AS
SELECT rb.pax_id AS pax_id
     ,(trim(p.pax_initials)||' '||trim(p.pax_surname)) AS pax_name
     ,rb.site_id
     ,rb.bag_tag
     ,rb.flight_id
 FROM reflight_bag rb, pax p
WHERE rb.pax_id = p.pax_id 
UNION 
SELECT rb.pax_id AS pax_id
      ,(trim(p.pax_initials)||' '||trim(p.pax_surname)) AS pax_name
      ,rb.site_id
      ,rb.bag_tag
      ,rb.flight_id
 FROM live.reflight_bag rb, arch.pax p
WHERE rb.pax_id = p.pax_id
;
