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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             arrival_flight_uld_contents_v.sql           *

rem * Module Name:          arrival_flight_uld_contents_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 for Inbound flight screeens                        *

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 arrival_flight_uld_contents_v

CREATE OR REPLACE FORCE VIEW arrival_flight_uld_contents_v
(uld_no, 
 bag_tag,
 destination,
 class,
 uld_load_sequence,
 passenger,
 pax_sequence_no,
 exception,
 in_flight_id,      -- Must have this field...
 in_flight_no,      -- Or these 1,
 in_flight_date,    --          2,
 in_airport_code,   --          3.
 out_flight_id,     -- Must have this field
 out_flight_no,     -- Or these 1,
 out_flight_date,   --          2.
 transfer_flight_no, -- non-displayed field needed for future popups
 transfer_flight_date,
 uld_hold_no,
 originating_flight_id
 )
AS
  /* note that extra security is not needed as the */
  /* inbound flight and ULD have already been */
  /* selected by secure methods */
  SELECT 
    iuld.uld_no,
    ibag.bag_tag, 
    ibag.bag_destination, 
    ipax.pax_class,
    ibag.bag_sequence_no,
    ipax.pax_surname,
    ipax.pax_sequence_no,
    ibag.bag_exception,
    flight_in.flight_id,
    flight_in.flight_no,
    flight_in.flight_date,
    site_in.airport_code,
    flight_out.flight_id,
    flight_out.flight_no,
    flight_out.flight_date,
    source_bag.next_flight_no,
    source_bag.next_flight_date,
    iuld.uld_hold_no,
    flight_orig.flight_id
  FROM inbound_bag_v1 ibag, inbound_pax_v1 ipax, inbound_uld_v1 iuld, 
       flight_inbound_v1 flight_in, flight_outbound_v1 flight_out, 
       site_info site_in, bag source_bag, flights_linked_v2 fl,
       inbound_pax_onward_dests ipod, flight flight_orig
  WHERE 
    fl.in_flight_id = flight_in.flight_id AND
    fl.out_flight_id = flight_out.flight_id AND
    /********************************/
    /* BAG IS ON THE INBOUND ULD... */	
    /********************************/
    ipax.inbound_flight_id = flight_in.flight_id AND
    ibag.inbound_pax_id    = ipax.inbound_pax_id AND
    ibag.inbound_uld_id    = iuld.inbound_uld_id AND
    ibag.loaded_status     = 'Y' AND
    ipax.result_pax_status IN ('CREATED','AMENDED','UNPROCESSED') AND
    ibag.result_bag_status IN ('CREATED','AMENDED','UNPROCESSED') AND
    ipax.inbound_pax_status <> 'TERMINATING' AND
    flight_in.site_id      = site_in.site_id AND
    ipod.onward_flight_no  = flight_out.flight_no AND
    ipod.onward_flight_date = flight_out.flight_date AND
    ipod.inbound_pax_id    = ipax.inbound_pax_id AND
    ipod.destination_sequence = 1 AND
    /**********************************************************************
     The following joins are used to populate transfer_* columns which will 
     be populated only for bags which have already been transferred.
    ***********************************************************************/
    ibag.bag_id  = source_bag.bag_id(+) AND
    flight_orig.next_leg_id(+) = flight_in.flight_id
  UNION
  SELECT 
    iuld.uld_no,
    ibag.bag_tag, 
    ibag.bag_destination, 
    ipax.pax_class,
    ibag.bag_sequence_no,
    ipax.pax_surname,
    ipax.pax_sequence_no,
    ibag.bag_exception,
    flight_in.flight_id,
    flight_in.flight_no,
    flight_in.flight_date,
    site_in.airport_code,
    0,
    NULL,
    NULL,
    NULL,
    NULL,
    iuld.uld_hold_no,
    flight_orig.flight_id
  FROM inbound_bag_v1 ibag, inbound_pax_v1 ipax, inbound_uld_v1 iuld, 
       flight_inbound_v1 flight_in, site_info site_in, flight flight_orig
  WHERE 
    /********************************/
    /* BAG IS ON THE INBOUND ULD... */	
    /********************************/
    ipax.inbound_flight_id = flight_in.flight_id AND
    ibag.inbound_pax_id    = ipax.inbound_pax_id AND
    ibag.inbound_uld_id    = iuld.inbound_uld_id AND
    flight_in.site_id      = site_in.site_id AND
    ipax.result_pax_status IN ('CREATED','AMENDED','UNPROCESSED') AND
    ibag.result_bag_status IN ('CREATED','AMENDED','UNPROCESSED') AND
    ipax.inbound_pax_status = 'TERMINATING' AND
    ibag.loaded_status = 'Y' AND
    flight_orig.next_leg_id(+) = flight_in.flight_id;

