/*****************************************************************************
*
* Filename         - $Workfile:   pax_and_bag_count_v1.sql  $
*
*                     Name              Date      Version     CR Number
*                     Martin Helliwell  06/06/01              CR15443
* 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:   pax_and_bag_count_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:   /pvcsarch/db_ora/db/source/live/views/pax_and_bag_count_v1.svl  $
* 
*    Rev 1.0   18 Jun 2001 14:15:20   tagtrak
* OR:
* CR: 15443
* REV: 1.0
* DESC: User Group Changes 2001
*
*****************************************************************************/
PROMPT Creating view pax_and_bag_count_v1 

CREATE OR REPLACE FORCE
VIEW pax_and_bag_count_v1 
(flight_id
,pax_checked
,pax_boarded
,pax_no_show
,bags_checked
,bags_loaded
,bags_short
,bags_reject
,bags_normal
,bags_rush
,bags_gate
,bags_crew
,bags_alerts
,bags_standby
,bags_aircraft_side
,bags_local
,bags_transfer
,bags_company_mail
,bags_diplomatic
) AS
SELECT flight_id
,NVL(pbc.pax_checked(flight_id),0)
,NVL(pbc.pax_boarded(flight_id),0)
,NVL(pbc.pax_no_show(flight_id),0)
,NVL(pbc.bags_checked(flight_id),0)
,NVL(pbc.bags_loaded(flight_id),0)
,NVL(pbc.bags_short(flight_id),0)
,NVL(pbc.bags_reject(flight_id),0)
,NVL(pbc.bags_normal(flight_id),0)
,NVL(pbc.bags_rush(flight_id),0)
,NVL(pbc.bags_gate(flight_id),0)
,NVL(pbc.bags_crew(flight_id),0) + NVL(pbc.bags_crew_untagged(flight_id),0)
,NVL(pbc.bags_alerts(flight_id),0)
,NVL(pbc.bags_standby(flight_id),0)
,NVL(pbc.bags_aircraft_side(flight_id),0)
,NVL(pbc.bags_local(flight_id),0)
,NVL(pbc.bags_transfer(flight_id),0)
,NVL(pbc.bags_company_mail(flight_id),0)
,NVL(pbc.bags_diplomatic(flight_id),0)
FROM flight_outbound_v1 
/
