
rem *********************************************************************

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Filename:             uld_data_v.sql                              *

rem * Module Name:          uld_data_v                                  *

rem * Subsystem Name:       views                                       *

rem * Originating Author:   James Creegan                               *

rem * Design Reference:     FAS/BBTRK/SD/225                            *

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Description:                                                      *

rem *  View required by the inbound flight screens                      *

rem *********************************************************************

rem *                                                                   *

rem * Program Identification Array:                                     *

rem *                                                                   *

rem * "%Z%%M% VERSION %I% %E% %U%"

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Modification History:                                             *

rem *                                                                   *

rem * Rel Date/Time      Proj/Id   CA Ref/Details Of Change             *

rem * --- -------------- --------  ------------------------------------ *

rem * 1.0 28 May 03                File initially checked in            *

rem *********************************************************************/

PROMPT Creating view uld_data_v

CREATE OR REPLACE FORCE VIEW uld_data_v
( flight_id,
  departure_date ,
  uld_no    ,
  uld_pos   ,
  uld_type  ,
  no_bags   ,
  no_bag_exceptions ,
  uld_category )
AS
  SELECT flight.flight_id,
         TO_DATE(flight.flight_date||flight.flight_sch_time, 'DDMONHH24MI'),
         ulds.uld_no,
         ulds.uld_hold_no,
         ulds.uld_type,
         count(bag.bag_tag),
         count(ltrim(bag_exception)),
         ulds.uld_category
  FROM flight, ulds, bag
  WHERE ulds.flight_id     = flight.flight_id AND
        ulds.uld_no        = nvl(bag.uld_no, '???') AND
        flight.flight_id   = nvl(bag.uld_flight_id, 999) AND
        bag.delete_marker != 1
  GROUP BY
        flight.flight_id,
        TO_DATE(flight.flight_date||flight.flight_sch_time, 'DDMONHH24MI'),
        ulds.uld_no,
        ulds.uld_hold_no,
        ulds.uld_type,
        ulds.uld_category;

