


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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             reallocate_bag_v.sql                        *

rem * Module Name:          reallocate_bag                              *

rem * Subsystem Name:       views                                       *

rem * Originating Author:   James Creegan                               *

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

rem *                                                                   *

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

rem *                                                                   *

rem * Description:                                                      *

rem *  View required for merge bag screen on 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 * 1.1 30 Mar 05                CA 01253                             *

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

PROMPT Creating view reallocate_bag_v

CREATE OR REPLACE FORCE VIEW reallocate_bag_v
(bag_tag, dest, cls, uld_load_seq, passenger, exception, uld, uld_pos, flight_id, agent_code, pax_id)
AS
  SELECT 
    bag.bag_tag,
    bag.bag_destination,
    pax.pax_class,
    bag.bag_sequence_no,
    pax.pax_surname, 
    bag.bag_exception,
    bag.uld_no,
    ulds.uld_hold_no,
    flight_access.flight_id,
    flight_access.agent_code,
    pax.pax_id
  FROM
    bag, pax, ulds, flight_access
  WHERE
    bag.pax_id                 = pax.pax_id AND
    NVL(pax.flight_id, -999)   = NVL(ulds.flight_id, 999) AND
    ulds.uld_no                = NVL(bag.uld_no, -999) AND
    LTRIM(bag.next_flight_no) IS NULL AND      
    LTRIM(bag.next_flight_date) IS NULL AND
    flight_access.flight_id    = ulds.flight_id; 
    
