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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             departure_bag_information_v.sql             *

rem * Module Name:          departure_bag_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 flights 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 departure_bag_information_v

CREATE OR REPLACE FORCE VIEW departure_bag_information_v
(in_flight_no,
 in_stand,
 in_flight_id,
 in_airport,
 arr_date,
 arr_time,
 dep_date,
 dep_time,
 transfer,
 min_con_time,
 max_con_time,
 in_no_bags,
 in_no_exceptions,
 out_flight_id,
 flight_agent
 )
AS
/* The OUT_FLIGHT_ID should always be specified when selecting from this view */
/* outward flight will be picked from the flight loop so extra security is not needed */
SELECT  -- distinct
        flight_in.flight_no,
        get_arr_stand(flight_in.flight_no, flight_in.flight_date, site_in.site_id),
        flight_in.flight_id,
        site_out.airport_code,
        TO_CHAR(get_best_time(flight_in.flight_no, flight_in.flight_date, flight_out.site_id), 'DDMON'),
        TO_CHAR(get_best_time(flight_in.flight_no, flight_in.flight_date, flight_out.site_id), 'HH24MI'),
        flight_in.flight_date,
        NVL(flight_in.flight_est_date, flight_in.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),
        /************************************************************************************/
        /* 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, site_in.site_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, site_in.site_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(LTRIM(ibag.bag_exception)),
        flight_out.flight_id,
        fl.agent_code
  FROM
        flight_outbound_v1 flight_out,
        flight_inbound_v1 flight_in,
        flights_linked_v2 fl,
        site_info site_in,
        site_info site_out,
        inbound_pax_v1 ipax,
        inbound_bag_v1 ibag,
        inbound_pax_onward_dests ipod
  WHERE
        /* Get list of inbound flights with passengers who have an onward flight */
        /* matching the details of our selected onward flight                    */
        flight_in.flight_id = fl.in_flight_id
    AND ipax.inbound_flight_id = flight_in.flight_id
    AND flight_out.flight_id = fl.out_flight_id
     /*site_id of inbound flight is the site it is arriving to.
       site_id of an outbound flight is site it is leaving from.*/
    AND site_in.site_id    = inbound_flight_utils.get_inbound_orig_site(flight_in.flight_id)
    AND ipax.inbound_pax_id = ibag.inbound_pax_id
    AND ipax.result_pax_status IN ('CREATED','AMENDED','UNPROCESSED')
    AND ibag.result_bag_status IN ('CREATED','AMENDED','UNPROCESSED')
    AND ibag.loaded_status = 'Y'
    AND site_out.site_id = flight_out.site_id
    AND ipod.inbound_pax_id = ipax.inbound_pax_id
    AND ipod.destination_sequence = 1
    AND flight_out.flight_no = ipod.onward_flight_no
    AND flight_out.flight_date = ipod.onward_flight_date
  GROUP BY
        flight_in.flight_no,
        get_arr_stand(flight_in.flight_no, flight_in.flight_date, site_in.site_id),
        flight_in.flight_id,
        site_out.airport_code,
        TO_CHAR(get_best_time(flight_in.flight_no, flight_in.flight_date, flight_out.site_id), 'DDMON'),
        TO_CHAR(get_best_time(flight_in.flight_no, flight_in.flight_date, flight_out.site_id), 'HH24MI'),
        flight_in.flight_date,
        NVL(flight_in.flight_est_date, flight_in.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_min_con_time(get_arr_stand(flight_in.flight_no, flight_in.flight_date, site_in.site_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, site_in.site_id),
                         get_dep_stand(flight_out.flight_no, flight_out.flight_date, flight_out.site_id),
                         flight_out.site_id,
                         fl.agent_code),
        flight_out.flight_id,
        fl.agent_code;
