/*****************************************************************************
*
* Filename         - $Workfile:   security_screening_v1.sql  $
*
*                     Name              Date      Version     CR Number
*                     Martin Helliwell  5/8/2       1.0        15644
* 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, 2002
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   security_screening_v1.sql  $  $Revision:   1.0  $
*
* 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:   /shared/pvcs/tagtarch/db_ora/db/source/live/views/security_screening_v1.svl  $
*
*****************************************************************************/
PROMPT Creating view security_screening_v1

CREATE OR REPLACE FORCE
VIEW security_screening_v1
(flight_id
,cleared
,not_cleared
,unnecessary
,total
) AS
SELECT flight.flight_id
,SUM(DECODE(security_clearance_status,'C',1,0)) CLEARED
,SUM(DECODE(security_clearance_status,'N',1,0)) NOT_CLEARED
,SUM(DECODE(security_clearance_status,'U',1,0)) UNNECESSARY
,COUNT(*) TOTAL
FROM flight
,pax
,bag
WHERE flight.flight_id = pax.flight_id
AND pax.pax_id = bag.pax_id
AND bag.delete_marker = 0
GROUP by flight.flight_id
/
