/*****************************************************************************
*
* Filename		- mws_flow_rate.sql
*
* Project Name		- UltraTrak Database
*
* Source Code Identifier - FAS/URBRS/TN/10154
*
* Description		- Prodedures for use by MWS to display the number of connecting
*                         bags expected during each hourly period
*
* Language		- Oracle PL/SQL
*
* File History		- For the development history run the Show History report in PVCS
*
* Copyright Ultra Electronics Airport Systems, 2003
*
*****************************************************************************/


PROMPT CREATE OR REPLACE PACKAGE mws_flow_rate

CREATE OR REPLACE PACKAGE mws_flow_rate
AS

/******************************************************************************
 *
 * Public Type Definitions
 *
 ******************************************************************************/

   -- Define the types of record sets (for the workstation)
   TYPE flight_no_type        IS TABLE OF flight.flight_no%TYPE                 INDEX BY BINARY_INTEGER;
   TYPE flight_date_type      IS TABLE OF flight.flight_date%TYPE               INDEX BY BINARY_INTEGER;
   TYPE flight_time_type      IS TABLE OF VARCHAR2(5)                           INDEX BY BINARY_INTEGER;
   TYPE flight_term_type      IS TABLE OF flight_info_inbound.terminal%TYPE     INDEX BY BINARY_INTEGER;
   TYPE flight_origin_time    IS TABLE OF flight_info_inbound.prev_airport%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_count_type        IS TABLE OF INTEGER                               INDEX BY BINARY_INTEGER;
   TYPE outstanding_bags_type IS TABLE OF INTEGER                               INDEX BY BINARY_INTEGER;
   TYPE mct_type              IS TABLE OF mct.min_connection_time%TYPE          INDEX BY BINARY_INTEGER;
   TYPE diff_time_type_oldmws IS TABLE OF VARCHAR2(10)                          INDEX BY BINARY_INTEGER;
   TYPE diff_time_type        IS TABLE OF NUMBER                                INDEX BY BINARY_INTEGER;
   TYPE flight_id_type        IS TABLE OF flight.flight_id%TYPE                 INDEX BY BINARY_INTEGER;
   TYPE vc_flight_id_type     IS TABLE OF VARCHAR2(16)                          INDEX BY BINARY_INTEGER;


/******************************************************************************
 *
 * PROCEDURE GET_EXPECTED_FLOW_OUTGOING
 *
 * Description:
 *
 * This procedure returns the number of bags on an outbound flight that must
 * be loaded from inbound flights (along with a count of how many have already
 * been loaded)
 *
 * Parameters:
 *
 * p_agent_code       - looks for flights that this agent handles only
 * p_start_date       - start looking for flights on this day
 * p_start_hour       - look for flights after this hour in the day
 * p_end_date         - stop looking for flights on this day
 * p_end_hour         - stop looking for flights at this hour of the day
 * p_flight_no        - returned flight number
 * p_flight_date      - returned flight date
 * p_inbound_bags     - count of the number of bags on this flight that are
 *                      arriving on an inbound flight
 * p_bags_outstanding - bags from inbound flights that are yet to be loaded
 *
 ******************************************************************************/

PROCEDURE get_expected_flow_outgoing
(
   p_agent_code       IN  agents.agent_code%type,
   p_start_hour       IN  integer,
   p_end_hour         IN  integer,
   p_date             IN  number,
   p_flight_no        OUT flight_no_type,
   p_flight_date      OUT flight_date_type,
   p_inbound_bags     OUT bag_count_type,
   p_bags_outstanding OUT outstanding_bags_type,
   p_flight_id        OUT flight_id_type
);

/******************************************************************************
 *
 * FUNCTION GET_EXPECTED_FLOW_OUTGOING_2
 *
 * Description:
 *
 * This procedure returns the number of bags on an outbound flight that must
 * be loaded from inbound flights (along with a count of how many have already
 * been loaded). Data are returned in a ref cursor, for MWS 3.
 *
 * Parameters:
 *
 * p_agent_code       - looks for flights that this agent handles only
 * p_start_hour       - look for flights after this hour in the day
 * p_end_hour         - stop looking for flights at this hour of the day 
 * p_date             - the date of the outbound flights
 *
 ******************************************************************************/

FUNCTION get_expected_flow_outgoing_2
(p_agent_code  IN  agents.agent_code%type,
 p_start_hour  IN  integer,
 p_end_hour    IN  integer,
 p_date        IN  NUMBER) RETURN SYS_REFCURSOR;

/******************************************************************************
 *
 * PROCEDURE GET_EXPECTED_FLOW_INCOMING
 *
 * Description:
 *
 * This procedure display inbound flight details for a given outbound flight.
 * I.e. details of inbound flights that have passengers which will be transferring
 * to the given outbound flight.
 *
 * Parameters:
 *
 * p_flight_id        - flight id of departure flight for which to find
 *                      associated inbound flight details
 * p_agent_code       - looks for flights that this agent handles only
 * p_flight_no        - returned inbound flight number
 * p_flight_date      - returned flight date
 * p_flight_time      - returned latest arrivals time
 * p_flight_term      - returned arrival terminal
 * p_flight_from      - returned origin airport
 * p_inbound_bags     - returned total number of bags from this inbound flight
 *                      that must be loaded onto the outgoing flight
 * p_bags_outstanding - returned bags from this inbound flight that are yet to be
 *                      loaded onto the outbound flight
 * p_mct              - returned minimum connection time between this inbound and
 *                      the given outbound flight
 * p_diff_time        - returned actual time between inbound arrival time and
 *                      outbound departure time
 * p_flight_id_out    - returned inbound flight id (if available)
 *
 ******************************************************************************/

PROCEDURE get_expected_flow_incoming
(
   p_flight_id        IN  flight.flight_id%type,
   p_agent_code       IN agents.agent_code%type,
   p_flight_no        OUT flight_no_type,
   p_flight_date      OUT flight_date_type,
   p_flight_time      OUT flight_time_type,
   p_flight_term      OUT flight_term_type,
   p_flight_from      OUT flight_origin_time,
   p_inbound_bags     OUT bag_count_type,
   p_bags_outstanding OUT outstanding_bags_type,
   p_mct              OUT mct_type,
   p_diff_time        OUT diff_time_type_oldmws,
   p_flight_id_out    OUT vc_flight_id_type
);

/******************************************************************************
 *
 * FUNCTION GET_EXPECTED_FLOW_INCOMING_2
 *
 * Description:
 *
 * This procedure display inbound flight details for a given outbound flight.
 * I.e. details of inbound flights that have passengers which will be transferring
 * to the given outbound flight. Returns data in a ref cursor
 *
 * Parameters:
 *
 * p_flight_id        - flight id of departure flight for which to find
 *                      associated inbound flight details
 * p_agent_code       - looks for flights that this agent handles only
 *
 ******************************************************************************/

FUNCTION get_expected_flow_incoming_2
(
  p_flight_id        IN flight.flight_id%TYPE,
  p_agent_code       IN agents.agent_code%TYPE
)
RETURN SYS_REFCURSOR;

/******************************************************************************
 *
 * PROCEDURE GET_FLIGHT_DETAILS
 *
 * Description:
 *
 * This procedure returns details of a flight specified by a flight id
 *
 * Parameters:
 *
 * p_flight_id        - flight id of departure flight for which to find
 *                      associated details
 * p_agent_code       - agent code of the user requesting flight details
 * p_flight_no        - returned flight number
 * p_flight_date      - returned flight date
 * p_flight_time      - returned latest known departure time
 * p_flight_term      - returned departure terminal
 *
 ******************************************************************************/

PROCEDURE get_flight_details
(
   p_flight_id        IN  flight.flight_id%type,
   p_agent_code       IN  agents.agent_code%type,
   p_flight_no        OUT flight_no_type,
   p_flight_date      OUT flight_date_type,
   p_flight_time      OUT flight_time_type,
   p_flight_term      OUT flight_term_type
);

/******************************************************************************
 *
 * PROCEDURE GET_FLIGHT_DETAILS_2
 *
 * Description:
 *
 * This procedure returns details of a flight specified by a flight id. It
 * returns scalar parameters.
 *
 * Parameters:
 *
 * p_flight_id        - flight id of departure flight for which to find
 *                      associated details
 * p_agent_code       - agent code of the user requesting flight details
 * p_flight_no        - returned flight number
 * p_flight_date      - returned flight date
 * p_flight_time      - returned latest known departure time
 * p_flight_term      - returned departure terminal
 *
 ******************************************************************************/

PROCEDURE get_flight_details_2
(
   p_flight_id    IN     flight.flight_id%TYPE,
   p_agent_code   IN     agents.agent_code%TYPE,
   p_flight_no       OUT flight.flight_no%TYPE,
   p_flight_date     OUT flight.flight_date%TYPE,
   p_flight_time     OUT VARCHAR2,
   p_flight_term     OUT flight_info_inbound.terminal%TYPE
);


END mws_flow_rate;
/

CREATE OR REPLACE PACKAGE BODY mws_flow_rate
AS

/******************************************************************************
 *
 * PROCEDURE GET_EXPECTED_FLOW_OUTGOING
 *
 ******************************************************************************/

PROCEDURE get_expected_flow_outgoing
(
   p_agent_code       IN  agents.agent_code%type,
   p_start_hour       IN  integer,
   p_end_hour         IN  integer,
   p_date             IN  number,
   p_flight_no        OUT flight_no_type,
   p_flight_date      OUT flight_date_type,
   p_inbound_bags     OUT bag_count_type,
   p_bags_outstanding OUT outstanding_bags_type,
   p_flight_id        OUT flight_id_type
)
IS
   -- Cursor to get the flight ids of outbound flights within
   -- the given time range and that the agent has access to

   CURSOR c_flight_cursor(p_agent_code agents.agent_code%type,
                          p_start_date date,
                          p_end_date date) IS
      SELECT
         f.flight_no,f.flight_date,f.flight_id
      FROM
         flight_info fi, flight_outbound_v1 f, flight_access fa
      WHERE
         NVL(act_flight_time, NVL(est_flight_time, schd_flight_time))
            BETWEEN p_start_date AND p_end_date AND
         fi.direction = 'O'                 AND
         fi.flight_no = f.flight_no         AND
         fi.flight_date = f.flight_date     AND
         fa.flight_id = f.flight_id         AND
         fa.agent_code = p_agent_code;

   l_start_date         DATE;
   l_end_date           DATE;
   l_inbound_bags       INTEGER;
   l_outstanding_bags   INTEGER;
   l_current_flight     BINARY_INTEGER;  -- Record set count variable
   l_add_day            INTEGER := 0;

BEGIN

   -- Find if the start hour is before the end hour
   if (p_start_hour >= p_end_hour) then
      l_add_day := 1;
   end if;

   -- Chop off the hours and minutes from the current time
   l_start_date := TRUNC(SYSDATE);

   -- Change the start date to use the date set on the page
   l_start_date := l_start_date + p_date;

   -- Use the start date as a base for the end date and add whether the end time
   -- is on the following day.
   l_end_date := l_start_date + l_add_day;

   -- Add the hour to the start date we have worked out.
   l_start_date := l_start_date + (p_start_hour/24);
   -- dbms_output.put_line('Start: ' || l_start_date);

   -- Add the hour to the end date we have worked out
   l_end_date := l_end_date + (p_end_hour/24);
   -- dbms_output.put_line('End: ' || l_end_date);

   l_current_flight := 0; -- Initialise the counter

   -- Loop through all flights that this user can
   -- access within the given time range

   FOR this_flight_rec IN c_flight_cursor(p_agent_code, l_start_date, l_end_date) LOOP

     l_current_flight := l_current_flight + 1;

     -- For this outbound flight, find the number of bags
     -- for passengers on inbound flights

     SELECT COUNT(*)
     INTO   l_inbound_bags
     FROM   bag b,
            pax p
     WHERE  b.pax_id = p.pax_id
       AND  p.flight_id = this_flight_rec.flight_id
       AND  p.inbound_flight_no IS NOT NULL
       AND  p.delete_marker = 0
       AND  b.delete_marker = 0;
     -- Now get the number of outstanding bags

     SELECT COUNT(*)
     INTO   l_outstanding_bags
     FROM   bag b,
            pax p
     WHERE  b.pax_id = p.pax_id
       AND  p.flight_id = this_flight_rec.flight_id
       AND  p.inbound_flight_no IS NOT NULL
       AND  p.delete_marker = 0
       AND  b.delete_marker = 0
       AND  b.loaded_status = 'N';

     -- Store the flow rate details for this flight

     p_flight_no(l_current_flight) := this_flight_rec.flight_no;
     p_flight_date(l_current_flight) := this_flight_rec.flight_date;
     p_flight_id(l_current_flight) := this_flight_rec.flight_id;
     p_inbound_bags(l_current_flight) := l_inbound_bags;
     p_bags_outstanding(l_current_flight) := l_outstanding_bags;

   END LOOP;

END get_expected_flow_outgoing;


/******************************************************************************
 *
 * FUNCTION GET_EXPECTED_FLOW_OUTGOING_2
 *
 ******************************************************************************/

FUNCTION get_expected_flow_outgoing_2
(p_agent_code  IN  agents.agent_code%TYPE,
 p_start_hour  IN  INTEGER,
 p_end_hour    IN  INTEGER,
 p_date        IN  NUMBER) RETURN SYS_REFCURSOR IS


  l_start_date  DATE;
  l_end_date    DATE;
  l_add_day     INTEGER := 0;
  l_cursor      SYS_REFCURSOR;

BEGIN

  -- Find if the start hour is before the end hour
  IF (p_start_hour >= p_end_hour) THEN
    l_add_day := 1;
  END IF;

  -- Chop off the hours and minutes from the current time
  l_start_date := TRUNC(SYSDATE);

  -- Change the start date to use the date set on the page
  l_start_date := l_start_date + p_date;

  -- Use the start date as a base for the end date and add whether the end time
  -- is on the following day.
  l_end_date := l_start_date + l_add_day;

  -- Add the hour to the start date we have worked out.
  l_start_date := l_start_date + (p_start_hour/24);
  -- dbms_output.put_line('Start: ' || l_start_date);

  -- Add the hour to the end date we have worked out
  l_end_date := l_end_date + (p_end_hour/24);
  -- dbms_output.put_line('End: ' || l_end_date);

  -- Cursor to get the flight ids of outbound flights within
  -- the given time range and that the agent has access to
  OPEN l_cursor FOR
  SELECT f.flight_no,
         f.flight_date,
         f.flight_id,
         COUNT(b.bag_id) inbound_bags,
         SUM(CASE WHEN b.loaded_status != 'Y' THEN 1
             ELSE 0
             END) outstanding_bags
  FROM flight_info fi, flight_outbound_v1 f, flight_access fa, pax p, bag b
  WHERE NVL(act_flight_time, NVL(est_flight_time, schd_flight_time))
  BETWEEN l_start_date AND l_end_date
  AND fi.direction = 'O'
  AND fi.flight_no = f.flight_no
  AND fi.flight_date = f.flight_date
  AND fa.flight_id = f.flight_id
  AND fa.agent_code = p_agent_code
  AND p.flight_id(+) = f.flight_id
  AND p.inbound_flight_no(+) IS NOT NULL
  AND p.delete_marker(+) = 0
  AND b.pax_id(+) = p.pax_id
  AND b.delete_marker(+) = 0
  GROUP BY f.flight_no, f.flight_date, f.flight_id;

  RETURN l_cursor;

END get_expected_flow_outgoing_2;


/******************************************************************************
 *
 * PROCEDURE GET_EXPECTED_FLOW_INCOMING
 *
 ******************************************************************************/

PROCEDURE get_expected_flow_incoming
(
   p_flight_id        IN flight.flight_id%type,
   p_agent_code       IN agents.agent_code%type,
   p_flight_no        OUT flight_no_type,
   p_flight_date      OUT flight_date_type,
   p_flight_time      OUT flight_time_type,
   p_flight_term      OUT flight_term_type,
   p_flight_from      OUT flight_origin_time,
   p_inbound_bags     OUT bag_count_type,
   p_bags_outstanding OUT outstanding_bags_type,
   p_mct              OUT mct_type,
   p_diff_time        OUT diff_time_type_oldmws,
   p_flight_id_out    OUT vc_flight_id_type
)
IS
   -- Cursor to get the incoming flight numbers and dates for the given flight

   CURSOR c_flight_cursor(p_flight_id flight.flight_id%type) IS
      SELECT DISTINCT
         inbound_flight_no, inbound_date, inbound_boarding_point
      FROM pax WHERE
         flight_id = p_flight_id AND inbound_flight_no IS NOT NULL;

   l_flight_no           flight.flight_no%TYPE;
   l_flight_date         flight.flight_date%TYPE;
   l_flight_id           LONG := 0;  -- This does need to be a LONG type, since the made up "inbound flight id's" from bag_v9 will need this
   l_arrival_time        date;
   l_departure_time      date;
   l_arrival_terminal    flight_info_inbound.terminal%TYPE;
   l_departure_terminal  flight_info.terminal%TYPE;
   l_airport_code        flight_info_inbound.prev_airport%TYPE;
   l_total_bags          INTEGER;
   l_bags_outstanding    INTEGER;
   l_min_ct              mct.min_connection_time%TYPE;
   l_max_ct              mct.max_connection_time%TYPE;
   l_this_airport        flight_info.airport_code%TYPE;
   l_current_flight      BINARY_INTEGER;  -- Record set count variable
   l_no_incoming_info    BOOLEAN := FALSE;
   l_space_padding       VARCHAR2(3) := '';

   l_diff_time           NUMBER(10,0);

BEGIN

   l_current_flight := 0; -- Initialise the counter

   -- Work out what airport we are at so that we can uniquely identify
   -- the departure flight in the FLIGHT_INFO table

   SELECT airport_code INTO l_this_airport FROM SITE_INFO WHERE
       site_id = (SELECT site_id FROM agents WHERE agent_code = p_agent_code);
       
   SELECT flight_no, flight_date INTO l_flight_no,l_flight_date FROM FLIGHT
      WHERE flight_id = p_flight_id;

   FOR this_flight_rec IN c_flight_cursor(p_flight_id) LOOP

      l_current_flight := l_current_flight + 1;
      l_airport_code := NULL;
      l_arrival_terminal := NULL;
      l_arrival_time := NULL;
      -- Get details of this inbound flight from FLIGHT_INFO_INBOUND
      -- Also check ARCH schema in case the arrival flight info has just been archived

      BEGIN

         SELECT
            prev_airport,
            terminal,
            NVL(act_arrival_time, NVL(est_arrival_time, schd_arrival_time))
         INTO
            l_airport_code,
            l_arrival_terminal,
            l_arrival_time
         FROM live.flight_info_inbound
         WHERE
            flight_no = this_flight_rec.inbound_flight_no AND
            flight_date = this_flight_rec.inbound_date AND
            airport_code = l_this_airport;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            -- No matching flight in LIVE so check ARCH in case this is a
            -- flight from yesterday which has been archived
            BEGIN
               SELECT
                  prev_airport,
                  terminal,
                  NVL(act_arrival_time, NVL(est_arrival_time, schd_arrival_time))
               INTO
                  l_airport_code,
                  l_arrival_terminal,
                  l_arrival_time
               FROM arch.flight_info_inbound
               WHERE
                  flight_no = this_flight_rec.inbound_flight_no AND
                  flight_date = this_flight_rec.inbound_date AND
                  airport_code = l_this_airport;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  -- We must have received details of this flight via the gateway, but
                  -- not anything from Saria, so ignore the flight
                  l_no_incoming_info := TRUE;
            END;

      END;

      -- Try and get the flight id of the incoming flight.
      -- Note: we may not be able to get this information
      --       depending on what information we have
      --       received via FIDS and Gateway

      BEGIN
	     SELECT DISTINCT TO_CHAR(inbound_flight_id)
         INTO   l_flight_id
         FROM   bag_v9
         WHERE  inbound_flight_no = this_flight_rec.inbound_flight_no
           AND  inbound_flight_date = this_flight_rec.inbound_date;

      EXCEPTION
         WHEN NO_DATA_FOUND THEN
           l_flight_id := 0;
      END;

      IF l_airport_code IS NULL THEN
         -- Could not get previous airport from FLIGHT_INFO, so try to
         -- use incoming information in the PAX table
         l_airport_code := this_flight_rec.inbound_boarding_point;

      END IF;

      -- Now find the total number of bags for this flight
      SELECT nvl(sum(no_of_bags), 0)
      INTO   l_total_bags
      FROM   pax
      WHERE  inbound_flight_no = this_flight_rec.inbound_flight_no
        AND  inbound_date = this_flight_rec.inbound_date
        AND  flight_id = p_flight_id
        AND  delete_marker = 0;

      -- Get the number of bags still to be loaded
      SELECT count(*)
      INTO   l_bags_outstanding
      FROM   bag b, pax p
      WHERE  b.loaded_status = 'N'
        AND  b.pax_id = p.pax_id
        AND  p.inbound_flight_no = this_flight_rec.inbound_flight_no
        AND  p.inbound_date = this_flight_rec.inbound_date
        AND  p.flight_id = p_flight_id
        AND  p.delete_marker = 0
        AND  b.delete_marker = 0;

      IF l_no_incoming_info = FALSE THEN

         -- Need to get flt number + date as flight_info does not have flight_id field

         -- Now get the departure time
         BEGIN
            SELECT
               NVL(act_flight_time, NVL(est_flight_time, schd_flight_time)),
               terminal
            INTO
               l_departure_time,
               l_departure_terminal
            FROM
               flight_info
            WHERE
               flight_no = l_flight_no AND
               flight_date = l_flight_date AND
               airport_code = l_this_airport;
         EXCEPTION
         WHEN NO_DATA_FOUND THEN
               l_departure_time     := NULL;
               l_departure_terminal := NULL;
         END;

         -- Now work out the difference between the arrival time of the inbound
         -- flight and the departure time of the outgoing flight in minutes

         l_diff_time := ROUND(24*60*(l_departure_time - l_arrival_time));

         -- Now get the MCT time. Note: we take this as the smallest
         -- MCT for agents handing the flight, as we don't actually
         -- know which agent is handling it.

         BEGIN
            SELECT min_connection_time,max_connection_time INTO l_min_ct, l_max_ct FROM mct WHERE
               terminal1 = l_arrival_terminal AND
               terminal2 = l_departure_terminal AND
               agent = p_agent_code;

            IF (l_diff_time <= l_min_ct) THEN
               l_space_padding := '  ';
            ELSIF (l_diff_time <= l_max_ct) THEN
               l_space_padding := ' ';
            ELSE
               l_space_padding := '';
            END IF;

         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               -- Set connection time to 0 to indicate no connection time found
               l_min_ct := 0;
               l_max_ct := 0;
               l_space_padding := '';
         END;

         -- Can we return a sensible time difference?
         -- Can not handle a difference of more than 1000 hours
         IF (l_diff_time >= 60000 OR l_diff_time <= -60000) THEN
            -- The time between arrival and departure flight is too great to display
            p_diff_time(l_current_flight) := '###:##' || l_space_padding;
         ELSE
            --determine sign separately to handle FLOOR(l_diff_time/60) = 0 
            --use ABS in claculations to avoid duplicating the sign
            p_diff_time(l_current_flight) := SUBSTR(SIGN(l_diff_time),1,LENGTH(SIGN(l_diff_time))-1) -- evaluates to either '-' or ''
                                            || FLOOR(ABS(l_diff_time/60))
                                            || ':' 
                                            || TO_CHAR(MOD(ABS(l_diff_time),60), 'FM09') 
                                            || l_space_padding;
         END IF;

         -- Store the MCT details for this flight
         p_flight_time(l_current_flight) := to_char(l_arrival_time,'HH24:MI');
         p_flight_term(l_current_flight) := l_arrival_terminal;
         p_mct(l_current_flight) := l_min_ct;

      END IF;

      -- Now store the details we will always know
      p_flight_no(l_current_flight) := this_flight_rec.inbound_flight_no;
      p_flight_date(l_current_flight) := this_flight_rec.inbound_date;
      p_flight_from(l_current_flight) := l_airport_code;
      p_inbound_bags(l_current_flight) := l_total_bags;
      p_bags_outstanding(l_current_flight) := l_bags_outstanding;
      p_flight_id_out(l_current_flight) := l_flight_id;

      l_no_incoming_info := FALSE;

   END LOOP;

END get_expected_flow_incoming;

/******************************************************************************
 *
 * FUNCTION GET_EXPECTED_FLOW_INCOMING_2
 *
 ******************************************************************************/

FUNCTION get_expected_flow_incoming_2
(
  p_flight_id        IN flight.flight_id%TYPE,
  p_agent_code       IN agents.agent_code%TYPE
)
RETURN SYS_REFCURSOR IS

  l_flight_no            flight.flight_no%TYPE;
  l_flight_date          flight.flight_date%TYPE;
  l_arrival_time         DATE;
  l_departure_time       DATE;
  l_arrival_terminal     flight_info_inbound.terminal%TYPE;
  l_departure_terminal   flight_info.terminal%TYPE;
  l_airport_code         flight_info_inbound.prev_airport%TYPE;
  l_total_bags           INTEGER;
  l_bags_outstanding     INTEGER;
  l_mct                  mct.min_connection_time%TYPE;
  l_diff_time            NUMBER;
  l_this_airport         flight_info.airport_code%TYPE;
  l_inbound_flight_id    flight.flight_id%TYPE;
  l_current_flight       BINARY_INTEGER;  -- Record set count variable
  l_incoming_info        BOOLEAN;
  l_outgoing_info        BOOLEAN;

  l_results_tab          expected_flow_incoming_tab := expected_flow_incoming_tab();
  l_cursor               SYS_REFCURSOR;

--These were the out the parameters, they're now local variables used to populate
--the results tab.
  lt_flight_no           flight_no_type;
  lt_flight_date         flight_date_type;
  lt_flight_time         flight_time_type;
  lt_flight_term         flight_term_type;
  lt_flight_from         flight_origin_time;
  lt_inbound_bags        bag_count_type;
  lt_bags_outstanding    outstanding_bags_type;
  lt_mct                 mct_type;
  lt_diff_time           diff_time_type;
  lt_inbound_flight_id   flight_id_type;
   
BEGIN

   l_current_flight := 0; -- Initialise the counter

   -- Work out what airport we are at so that we can uniquely identify
   -- the departure flight in the FLIGHT_INFO table

   SELECT airport_code
   INTO l_this_airport
   FROM site_info
   WHERE site_id = (SELECT site_id
                    FROM agents
                    WHERE agent_code = p_agent_code);

  -- Need to get flt number + date as flight_info does not have flight_id field

  SELECT flight_no, flight_date
  INTO l_flight_no, l_flight_date
  FROM flight
  WHERE flight_id = p_flight_id;

  /* Now get the departure time. If no flight_info records exist, set l_outgoing_info to false*/
  l_outgoing_info := TRUE;
  
  BEGIN
  
    SELECT NVL(act_flight_time, NVL(est_flight_time, schd_flight_time)),
           terminal
    INTO l_departure_time,
         l_departure_terminal
    FROM flight_info
    WHERE flight_no = l_flight_no
    AND flight_date = l_flight_date
    AND airport_code = l_this_airport;
   
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_outgoing_info := FALSE;
  
  END;
  
  FOR this_flight_rec IN (SELECT DISTINCT inbound_flight_no, inbound_date, inbound_boarding_point
                          FROM pax
                          WHERE flight_id = p_flight_id
                          AND inbound_flight_no IS NOT NULL) LOOP

    l_current_flight := l_current_flight + 1;

    --Set these values to ensure we don't pick up stuff from the previous time
    --round the loop.
    l_incoming_info := TRUE;
    l_diff_time := NULL;
    l_mct := NULL;

    -- Get details of this inbound flight from FLIGHT_INFO_INBOUND
    BEGIN

      SELECT prev_airport,
             terminal,
             NVL(act_arrival_time, NVL(est_arrival_time, schd_arrival_time))
      INTO l_airport_code,
           l_arrival_terminal,
           l_arrival_time
      FROM flight_info_inbound
      WHERE flight_no = this_flight_rec.inbound_flight_no
      AND to_char(trip_date, 'DDMON') = this_flight_rec.inbound_date
      AND airport_code = l_this_airport;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      
        --see if the inbound flight has already been archived.
        BEGIN
        
          SELECT prev_airport,
	         terminal,
	         NVL(act_arrival_time, NVL(est_arrival_time, schd_arrival_time))
	  INTO l_airport_code,
	       l_arrival_terminal,
	       l_arrival_time
	  FROM arch.flight_info_inbound
	  WHERE flight_no = this_flight_rec.inbound_flight_no
	  AND to_char(trip_date, 'DDMON') = this_flight_rec.inbound_date
          AND airport_code = l_this_airport;
          
        EXCEPTION WHEN NO_DATA_FOUND THEN
      
          -- We must have received details of this flight via the gateway, but
          -- not anything from FIDS, so ignore the flight
          l_incoming_info := FALSE;
          l_airport_code := NULL;
          l_arrival_terminal := NULL;
          l_arrival_time := NULL;
          
        END;

    END;

    -- Try and get the flight id of the incoming flight.
    -- Note: we may not be able to get this information
    --       depending on what information we have
    --       received via FIDS and Gateway

    BEGIN

      SELECT flight_id
      INTO l_inbound_flight_id
      FROM flight_inbound_v1
      WHERE flight_no = this_flight_rec.inbound_flight_no
      AND flight_date = this_flight_rec.inbound_date
      AND site_id = (SELECT site_id
                     FROM agents
                     WHERE agent_code = p_agent_code);

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_inbound_flight_id := 0;

    END;

    IF l_airport_code IS NULL THEN
      -- Could not get previous airport from FLIGHT_INFO, so try to
      -- use incoming information in the PAX table
      l_airport_code := this_flight_rec.inbound_boarding_point;

    END IF;

    -- Now find the total number of bags for this flight
    SELECT nvl(SUM(no_of_bags), 0)
    INTO l_total_bags
    FROM pax
    WHERE inbound_flight_no = this_flight_rec.inbound_flight_no
    AND inbound_date = this_flight_rec.inbound_date
    AND flight_id = p_flight_id
    AND delete_marker = 0;

    -- Get the number of bags still to be loaded
    SELECT COUNT(*)
    INTO l_bags_outstanding
    FROM bag b, pax p
    WHERE b.loaded_status != 'Y'
    AND b.pax_id = p.pax_id
    AND p.inbound_flight_no = this_flight_rec.inbound_flight_no
    AND p.inbound_date = this_flight_rec.inbound_date
    AND p.flight_id = p_flight_id
    AND p.delete_marker = 0
    AND b.delete_marker = 0;
    
    IF l_incoming_info AND l_outgoing_info THEN

      -- Now work out the difference between the arrival time of the inbound
      -- flight and the departure time of the outgoing flight
      l_diff_time := ROUND((l_departure_time - l_arrival_time) * 24 * 60);

      -- Now get the MCT time. Note: we take this as the smallest
      -- MCT for agents handing the flight, as we don't actually
      -- know which agent is handling it.

      BEGIN

        SELECT min_connection_time
        INTO l_mct
        FROM mct
        WHERE terminal1 = l_arrival_terminal
        AND terminal2 = l_departure_terminal
        AND RTRIM(agent) = RTRIM(p_agent_code);

      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          -- Set connection time to 0 to indicate no connection time found
          l_mct := 0;
      END;

    END IF;

    -- Store the MCT details for this flight
    lt_flight_time(l_current_flight) := to_char(l_arrival_time,'HH24:MI');
    lt_flight_term(l_current_flight) := l_arrival_terminal;
    lt_mct(l_current_flight) := l_mct;
    lt_diff_time(l_current_flight) := l_diff_time;
    
    -- Now store the details we will always know
    lt_flight_no(l_current_flight) := this_flight_rec.inbound_flight_no;
    lt_flight_date(l_current_flight) := this_flight_rec.inbound_date;
    lt_flight_from(l_current_flight) := l_airport_code;
    lt_inbound_bags(l_current_flight) := l_total_bags;
    lt_bags_outstanding(l_current_flight) := l_bags_outstanding;
    lt_inbound_flight_id(l_current_flight) := l_inbound_flight_id;

  END LOOP;

  FOR n IN nvl(lt_flight_no.FIRST, 1)..nvl(lt_flight_no.LAST, 0) LOOP
   
    BEGIN
     
      --make room in sql table.
      l_results_tab.extend(1);

      --use constructor to populate.
      l_results_tab(n) := expected_flow_incoming_rec(lt_flight_no(n),
                                                     lt_flight_date(n),
                                                     lt_flight_time(n),
                                                     lt_flight_term(n),
                                                     lt_flight_from(n),
                                                     lt_inbound_bags(n),
                                                     lt_bags_outstanding(n),
                                                     lt_mct(n),
                                                     lt_diff_time(n),
                                                     lt_inbound_flight_id(n));
                                     
    EXCEPTION WHEN no_data_found THEN
      NULL; --don't worry about gaps in the sql tables.
      
    END;
    
  END LOOP;
  
  --Build ref cursor from sql table.
  OPEN l_cursor FOR
  SELECT *
  FROM TABLE(CAST(l_results_tab AS expected_flow_incoming_tab));

  RETURN l_cursor;

END get_expected_flow_incoming_2;


/******************************************************************************
 *
 * PROCEDURE GET_FLIGHT_DETAILS
 *
 ******************************************************************************/

PROCEDURE get_flight_details
(
   p_flight_id        IN  flight.flight_id%type,
   p_agent_code       IN  agents.agent_code%type,
   p_flight_no        OUT  flight_no_type,
   p_flight_date      OUT flight_date_type,
   p_flight_time      OUT flight_time_type,
   p_flight_term      OUT flight_term_type
)
IS
   l_flight_no flight.flight_no%type;
   l_flight_date flight.flight_date%type;
   l_time date;
   l_terminal flight_info.terminal%TYPE;
   l_current_flight BINARY_INTEGER;  -- Record set count variable
   l_this_airport site_info.airport_code%type;
BEGIN

   l_current_flight := 1; -- Initialise the counter

   -- First, get flight number and date from the FLIGHT table

   SELECT flight_no, flight_date INTO l_flight_no, l_flight_date FROM
      flight WHERE flight_id = p_flight_id;

   -- Now get departure time and terminal from FLIGHT_INFO table

   -- First we need the airport code, so that we can uniquely identify
   SELECT si.airport_code
   INTO   l_this_airport
   FROM   site_info si,
          agents a
   WHERE  si.site_id = a.site_id
     AND  a.agent_code = p_agent_code;

   -- We can get departure time and terminal

   BEGIN
      SELECT
         NVL(act_flight_time, NVL(est_flight_time, schd_flight_time)),
         terminal
      INTO
         l_time,
         l_terminal
      FROM
         flight_info
      WHERE
         flight_no = l_flight_no AND
         flight_date = l_flight_date AND
         airport_code = l_this_airport;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         -- Ignore this as we may not have the data from FIDS
         null;
   END;

   p_flight_no(l_current_flight) := l_flight_no;
   p_flight_date(l_current_flight) := l_flight_date;
   p_flight_time(l_current_flight) := to_char(l_time,'HH24:MI');
   p_flight_term(l_current_flight) := l_terminal;

END get_flight_details;

/******************************************************************************
 *
 * PROCEDURE GET_FLIGHT_DETAILS_2
 *
 ******************************************************************************/

PROCEDURE get_flight_details_2
(
   p_flight_id    IN     flight.flight_id%TYPE,
   p_agent_code   IN     agents.agent_code%TYPE,
   p_flight_no       OUT flight.flight_no%TYPE,
   p_flight_date     OUT flight.flight_date%TYPE,
   p_flight_time     OUT VARCHAR2,
   p_flight_term     OUT flight_info_inbound.terminal%TYPE
)
IS

  l_this_airport   site_info.airport_code%TYPE;

BEGIN


  -- First, get flight number and date from the FLIGHT table
  SELECT flight_no, flight_date
  INTO p_flight_no, p_flight_date
  FROM flight
  WHERE flight_id = p_flight_id;

  -- Now get departure time and terminal from FLIGHT_INFO table
  -- First we need the airport code, so that we can uniquely identify
  SELECT si.airport_code
  INTO l_this_airport
  FROM site_info si,
       agents a
  WHERE si.site_id = a.site_id
  AND a.agent_code = p_agent_code;

  -- We can get departure time and terminal
  BEGIN

    SELECT to_char(NVL(act_flight_time, NVL(est_flight_time, schd_flight_time)), 'HH24:MI'),
           terminal
    INTO p_flight_time,
         p_flight_term
    FROM flight_info
    WHERE flight_no = p_flight_no
    AND flight_date = p_flight_date
    AND airport_code = l_this_airport;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- Ignore this as we may not have the data from FIDS
      NULL;

  END;

END get_flight_details_2;


END mws_flow_rate;
/



