/*****************************************************************************
*
* Filename         - $Workfile:   uld_ldg_prog_v1.sql  $
*
*                     Name              Date      Version     CR Number
* 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, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   uld_ldg_prog_v1.sql  $  $Revision:   1.3  $
*
* 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/uld_ldg_prog_v1.svl  $
* 
*    Rev 1.3   21 Jun 2001 15:38:06   tagtrak
* OR:
* CR:
* Re-baseline version 4.0.0 to include patches.
* 
*    Rev 1.2   15 Jun 1999 18:05:00   tagtrak
*  
* 
*    Rev 1.1   26 Feb 1999 09:01:24   tagtrak
*  
* 
*
****************************************************************************/

PROMPT Creating view   last_bag_loaded   ;
CREATE OR REPLACE FORCE VIEW   last_bag_loaded   ( uld_no, flight_id, last_time_loaded ) as
  select
      uld_no,
      flight_id,
    min(time_loaded)
  from
    bag b,
    pax p
  where
    p.pax_id    = b.pax_id 
      and b.loaded_status in ('Y','F')
   group by uld_no,flight_id ;


PROMPT Creating view   first_bag_loaded   ;
CREATE OR REPLACE FORCE VIEW   first_bag_loaded   ( uld_no, flight_id, first_time_loaded ) as
  select
      uld_no,
      flight_id,
    max(time_loaded)
  from
    bag b,
    pax p
  where
    p.pax_id    = b.pax_id 
   group by b.uld_no,flight_id  ;

PROMPT Creating view   uld_time_loaded   ;
CREATE OR REPLACE FORCE VIEW   uld_time_loaded   ( uld_no, flight_id, time_loaded ) as
select
    uld_no, flight_id, uld_trace_time
  from
    uld_trace ut, location l, location_group lg
  where
    ut.uld_location_code = l.location_code and
    l.group_name = lg.name and
    l.site_id = lg.site_id and
    lg.description in ('LOCAL','TRANSFER') ;


PROMPT Creating view uld_ldg_prog_v1 ;
CREATE OR REPLACE FORCE VIEW uld_ldg_prog_v1 
(flight_id, 
flight_no, 
flight_date, 
uld_no, 
location,
dest,
uld_category,
type,
class, 
bags,air_pos,
first_time_loaded,
last_time_loaded,
lttime,
hold_no, 
weight )
as 
select f.flight_id,
 f.flight_no,
 f.flight_date,
 u.uld_no,
 u.location_code ,
 ud.uld_destination,
 u.uld_category,
 u.uld_type,
 ud.uld_class_list,
 u.uld_no_of_bags,
 to_char(u.uld_load_time,'HH24:MI') , 
 '     ',
 '     ',
 '     ',
 u.uld_hold_no, 
 nvl( (w.weight*u.uld_no_of_bags) ,0) 
from ulds u, uld_dests ud, 
weights w, flight f
where u.uld_no   = ud.uld_no 
and  u.flight_id = ud.flight_id
and  u.flight_id = f.flight_id
and  f.flight_no = w.flight_no(+) ;
