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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             arrival_flight_load_popup_v.sql             *

rem * Module Name:          arrival_flight_load_popup                   *

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 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 arrival_flight_load_popup_v

CREATE OR REPLACE FORCE VIEW arrival_flight_load_popup_v
(out_flight_id,
 out_flight_no,
 out_stand,
 out_est_date,
 out_est_time,
 out_transfer,
 out_dest,
 out_no_bags,
 out_no_exceptions,
 in_flight_id,
 in_uld_no,
 flight_agent
 )
AS
/* The IN_FLIGHT_ID should always be specified when selecting from this view */
  SELECT
    flight_out.flight_id,
    flight_out.flight_no,
    get_dep_stand(flight_out.flight_no, flight_out.flight_date, flight_out.site_id),
    flight_out.flight_date,
    NVL(flight_out.flight_est_date, flight_out.flight_sch_time),
    get_transfer_time(flight_in.flight_no, flight_in.flight_date, flight_out.flight_no, flight_out.flight_date, flight_out.site_id) ,
    get_dest(flight_out.flight_id) ,
    COUNT(ibag.bag_tag),
    COUNT(LTRIM(ibag.bag_exception)),
    flight_in.flight_id,
    iuld.uld_no,
    fl.agent_code
  FROM flight_outbound_v1 flight_out
      ,flight_inbound_v1 flight_in
      ,inbound_uld_v1 iuld
      ,flights_linked_v2 fl
      ,inbound_bag_v1 ibag
      ,inbound_pax_v1 ipax
      ,inbound_pax_onward_dests ipod
  WHERE
    /* Get details about the ONWARD_FLIGHT rows for the */
    /* passengers on the IN flight represent the OUT flight */
    flight_in.flight_id = fl.in_flight_id AND
    flight_out.flight_id = fl.out_flight_id AND
    ipax.inbound_flight_id = flight_in.flight_id AND
    iuld.inbound_flight_id = flight_in.flight_id AND
    ibag.inbound_uld_id = iuld.inbound_uld_id AND
    ibag.inbound_pax_id = ipax.inbound_pax_id AND
    ipax.inbound_pax_id = ipod.inbound_pax_id AND
    ipod.onward_flight_no = flight_out.flight_no AND
    ipod.onward_flight_date = flight_out.flight_date AND
    ipod.destination_sequence = 1 AND
    ipax.result_pax_status IN ('CREATED','AMENDED','UNPROCESSED') AND
    ibag.result_bag_status IN ('CREATED','AMENDED','UNPROCESSED')
  GROUP BY
    flight_out.flight_id,
    flight_out.flight_no,
    get_dep_stand(flight_out.flight_no, flight_out.flight_date, flight_out.site_id),
    flight_out.flight_date,
    NVL(flight_out.flight_est_date, flight_out.flight_sch_time),
    get_transfer_time(flight_in.flight_no, flight_in.flight_date, flight_out.flight_no, flight_out.flight_date, flight_out.site_id) ,
    get_dest(flight_out.flight_id) ,
    flight_in.flight_id,
    iuld.uld_no,
    fl.agent_code
UNION
SELECT
    flight_out.flight_id,
    flight_out.flight_no,
    get_dep_stand(flight_out.flight_no, flight_out.flight_date, flight_out.site_id),
    flight_out.flight_date,
    NVL(flight_out.flight_est_date, flight_out.flight_sch_time),
    get_transfer_time(flight_in.flight_no, flight_in.flight_date, flight_out.flight_no, flight_out.flight_date, flight_out.site_id) ,
    get_dest(flight_out.flight_id) ,
    COUNT(ibag.bag_tag),
    COUNT(LTRIM(ibag.bag_exception)),
    flight_in.flight_id,
    iuld.uld_no,
    fl.agent_code
  FROM flight_outbound_v1 flight_out
      ,flight_inbound_v1 flight_in
      ,inbound_uld_v1 iuld
      ,flights_linked_v2 fl
      ,inbound_bag_v1 ibag
      ,inbound_pax_v1 ipax
      ,site_info isi
  WHERE
    /* Get details about the ONWARD_FLIGHT rows for the */
    /* passengers on the IN flight represent the OUT flight */
    flight_in.flight_id = fl.in_flight_id AND
    flight_out.flight_id = fl.out_flight_id AND
    ipax.inbound_flight_id = flight_in.flight_id AND
    iuld.inbound_flight_id = flight_in.flight_id AND
    ibag.inbound_uld_id = iuld.inbound_uld_id AND
    ibag.inbound_pax_id = ipax.inbound_pax_id AND
    isi.site_id = flight_in.site_id AND
    ipax.pax_destination <> isi.airport_code AND
    NOT EXISTS (SELECT 1 FROM inbound_pax_onward_dests WHERE onward_flight_no = flight_out.flight_no AND onward_flight_date = flight_out.flight_date AND destination_sequence = 1 AND inbound_pax_id = ipax.inbound_pax_id) and
    ipax.inbound_pax_status = 'THROUGH' AND
    flight_out.flight_no = flight_in.flight_no and
    ipax.result_pax_status IN ('CREATED','AMENDED','UNPROCESSED') AND
    ibag.result_bag_status IN ('CREATED','AMENDED','UNPROCESSED')
  GROUP BY
    flight_out.flight_id,
    flight_out.flight_no,
    get_dep_stand(flight_out.flight_no, flight_out.flight_date, flight_out.site_id),
    flight_out.flight_date,
    NVL(flight_out.flight_est_date, flight_out.flight_sch_time),
    get_transfer_time(flight_in.flight_no, flight_in.flight_date, flight_out.flight_no, flight_out.flight_date, flight_out.site_id) ,
    get_dest(flight_out.flight_id) ,
    flight_in.flight_id,
    iuld.uld_no,
    fl.agent_code;
