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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             arrival_baggage_information_v.sql           *

rem * Module Name:          arrival_baggage_information_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 functionality                   *

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_baggage_information_v 

CREATE OR REPLACE FORCE VIEW arrival_baggage_information_v 
(FLIGHT_NO,
 STAND,
 DEP_DATE,
 DEP_TIME,
 OUT_TRANSFER,
 MIN_CON_TIME,
 MAX_CON_TIME,
 OUT_NO_BAGS,
 OUT_NO_EXCEPTIONS,
 IN_FLIGHT_ID,
 OUT_FLIGHT_ID,
 FLIGHT_AGENT
 )
as
  /* The IN_FLIGHT_ID  FLIGHT_AGENT should always be specified when selecting from this view */
  SELECT flight_out.flight_no, 
        get_dep_stand(flight_out.flight_no, flight_out.flight_date, flight_out.site_id), 
        to_char(flight_out.departure_time, 'DDMON'),
        flight_out.flight_est_date,
        get_transfer_time(flight_in.flight_no, flight_in.flight_date, flight_out.flight_no, flight_out.flight_date, flight_out.site_id),  
        /************************************************************************************/
        /* Use the arr and departure stands to find the minimum and mximum connection times */
        /************************************************************************************/
        get_min_con_time(get_arr_stand(flight_in.flight_no, flight_in.flight_date, inbound_flight_utils.get_inbound_orig_site(flight_in.flight_id)),
                         get_dep_stand(flight_out.flight_no, flight_out.flight_date, flight_out.site_id),
                         flight_out.site_id,
                         fl.agent_code),
        get_max_con_time(get_arr_stand(flight_in.flight_no, flight_in.flight_date, inbound_flight_utils.get_inbound_orig_site(flight_in.flight_id)),
                         get_dep_stand(flight_out.flight_no, flight_out.flight_date, flight_out.site_id),
                         flight_out.site_id,
                         fl.agent_code),
        count(ibag.bag_tag),
        count(ibag.bag_exception),
        flight_in.flight_id,
        flight_out.flight_id,
        fl.agent_code
  FROM
        flight_outbound_v1 flight_out,
        flight_inbound_v1 flight_in,
        flights_linked_v2 fl,
        inbound_pax_v1 ipax,
        inbound_bag_v1 ibag,
        inbound_pax_onward_dests ipod
  WHERE
        flight_in.flight_id = fl.in_flight_id AND
        flight_out.flight_id = fl.out_flight_id AND
        flight_in.flight_id = ipax.inbound_flight_id AND
        ibag.inbound_pax_id = ipax.inbound_pax_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
        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
  GROUP BY
        flight_out.flight_no,
        get_dep_stand(flight_out.flight_no, flight_out.flight_date, flight_out.site_id),
        to_char(flight_out.departure_time, 'DDMON'),
        flight_out.flight_est_date,
        get_transfer_time(flight_in.flight_no, flight_in.flight_date, flight_out.flight_no, flight_out.flight_date, flight_out.site_id),  
        get_min_con_time(get_arr_stand(flight_in.flight_no, flight_in.flight_date, inbound_flight_utils.get_inbound_orig_site(flight_in.flight_id)),
                       get_dep_stand(flight_out.flight_no, flight_out.flight_date, flight_out.site_id),
                         flight_out.site_id,
                         fl.agent_code),
        get_max_con_time(get_arr_stand(flight_in.flight_no, flight_in.flight_date, inbound_flight_utils.get_inbound_orig_site(flight_in.flight_id)),
                         get_dep_stand(flight_out.flight_no, flight_out.flight_date, flight_out.site_id),
                         flight_out.site_id,
                         fl.agent_code),
        flight_in.flight_id,
        flight_out.flight_id,
        fl.agent_code
  UNION
  /* Find the number of bags terminating at this airport */
  SELECT 'TERM',
          NULL,
          NULL,
          NULL,
          0,
          0,
          0,
          count(ibag.bag_tag),
          count(ltrim(ibag.bag_exception)),
          flight_in.flight_id,
          0,
          fa.agent_code
  FROM
          flight_inbound_v1 flight_in,flight_access fa, inbound_bag_v1 ibag, inbound_pax_v1 ipax
  WHERE   flight_in.flight_id = fa.flight_id AND
          ipax.inbound_flight_id = flight_in.flight_id AND
          ipax.result_pax_status IN ('CREATED', 'AMENDED', 'UNPROCESSED') AND
          ibag.result_bag_status IN ('CREATED', 'AMENDED', 'UNPROCESSED') AND
          ibag.inbound_pax_id = ipax.inbound_pax_id AND
          ipax.inbound_pax_status = 'TERMINATING'
   GROUP BY flight_in.flight_id,
            fa.agent_code;

