/*****************************************************************************
*
* Filename         - $Workfile : reflight_summary_v.sql $
*
* Project Name     - ULTRATRAK(Oracle Database)
*
* Source Code
* Identifier       - FAS/FARFL
*
* Description      - Re-flighting Module
*
* Language         - SQL Script
*
* File History     - For the development history run the Show History report in PVCS
*
* Coptright Ultra Electronics Airport Systems 2004
*
*****************************************************************************/
/****************************************************************************
* View             reflight_smmary_v
*
* Description:     Re-flighting module addition.
*                  This view is used from the "Re-flighting Carrier Summary" screen
*                  to retrieve the list of carriers the users handles that have 
*                  been configured for re-flighting.
*
* CA History
* Date      Name              CA Number               CA Description
* 07/04/04  Jacqui Bruce      FALCP/CA/461       Initial development of the re-flighting module.
*
*****************************************************************************/
PROMPT Creating view reflight_summary_v

CREATE OR REPLACE FORCE VIEW reflight_summary_v
(user_id,
 carrier,
 enabled,
 configured)
AS
SELECT tu.user_id, 
       cs.alpha_code, 
       decode(cs.config_value, NULL, 'N', cs.config_value), 
       decode(rs.carrier, NULL, 'N', 'Y') 
FROM tagtrak_users       tu, 
     agents		 ag, 
     contracted_agents   ca, 
     reflight_setup      rs, 
     carrier_site_config cs 
WHERE ca.agent_code        = tu.agent_code 
AND   tu.agent_code	   = ag.agent_code 
AND   rs.carrier(+)        = cs.alpha_code 
AND   cs.alpha_code        = ca.airline_alpha_code 
AND   rs.site_id(+)	   = cs.site_id
AND   ag.site_id	   = cs.site_id 
AND   trim(cs.config_item) = 'REFLIGHTING'
/
