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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             reallocate_uld_v.sql                        *

rem * Module Name:          reallocate_uld_v                            *

rem * Subsystem Name:       views                                       *

rem * Originating Author:   James Creegan                               *

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

rem *                                                                   *

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

rem *                                                                   *

rem * Description:                                                      *

rem *  View to display the ULDs which can be reallocated in the MWS     *

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 reallocate_uld_v 

CREATE OR REPLACE FORCE VIEW reallocate_uld_v 
( FLIGHT_ID,
  ULD_NO,
  ULD_POS,
  ULD_CAT,
  ULD_TYPE,
  CLASS,
  NO_BAGS,
  BAG_EXCEPTIONS,
  AGENT_CODE)
AS
  SELECT ulds.flight_id,
         ulds.uld_no,
         ulds.uld_hold_no,
         ulds.uld_category,
         ulds.uld_type,
         rtrim(uld_class_list ( ulds.uld_no, ulds.flight_id)),
         count(bag.bag_tag),
         count(bag.bag_exception),
         flight_access.agent_code
  FROM
        ulds, bag, flight_access
  WHERE
    ulds.uld_no             = NVL(bag.uld_no, -999) AND
    ulds.flight_id          = NVL(bag.uld_flight_id, -999) AND
    flight_access.flight_id = ulds.flight_id 
  GROUP BY
    ulds.flight_id,
    ulds.uld_no,
    ulds.uld_hold_no,
    ulds.uld_category,
    ulds.uld_type,
    rtrim(uld_class_list ( ulds.uld_no, ulds.flight_id)),
    flight_access.agent_code;
