/*****************************************************************************
*
* Filename         - $Workfile:   inbound_flight_utils.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/
CREATE OR REPLACE PACKAGE inbound_flight_utils AS
FUNCTION is_through(
             p_object_type       IN  VARCHAR2
            ,p_flight_id         IN  flight.flight_id%TYPE
            ,p_pax_id            IN  NUMBER           DEFAULT NULL
            ,p_bag_id            IN  NUMBER           DEFAULT NULL
            ,p_uld_no            IN  CHAR             DEFAULT NULL
            ) RETURN BOOLEAN;

FUNCTION is_transfer(
             p_object_type       IN  VARCHAR2
            ,p_flight_id         IN  flight.flight_id%TYPE
            ,p_pax_id            IN  NUMBER           DEFAULT NULL
            ,p_bag_id            IN  NUMBER           DEFAULT NULL
            ,p_uld_no            IN  CHAR             DEFAULT NULL
            ) RETURN BOOLEAN;

FUNCTION increment_flt_date1(
                      p_flight_date IN flight.flight_date%TYPE
                     ,num_days     IN NUMBER
                     )RETURN flight.flight_date%TYPE;

FUNCTION increment_flt_date2(
                      p_flight_date IN flight.flight_date%TYPE
                     ,num_days     IN NUMBER
                     )RETURN DATE;

FUNCTION flt_date_to_date(
                      p_flight_date IN flight.flight_date%TYPE
                     )RETURN DATE;

FUNCTION get_flt_date_difference(
                      p_flt_date_subtract_from IN flight.flight_date%TYPE
                     ,p_flt_date_subtracted    IN flight.flight_date%TYPE
                     )RETURN NUMBER;

FUNCTION match_inbound_outbound_flts(
                      p_flight_id IN flight.flight_id%TYPE
                     ) RETURN flight.flight_id%TYPE;

PROCEDURE set_next_leg_id(
                      p_flight_id IN  flight.flight_id%TYPE
                     ,p_updated   OUT BOOLEAN
                     );

FUNCTION get_inbound_orig_site(
                      p_flight_id IN  flight.flight_id%TYPE
                     )RETURN site_info.site_id%TYPE;

PROCEDURE add_inbound_flight (
	fltno       IN    CHAR,  -- inbound flight no
	fltdate     IN    CHAR,  -- inbound flight date
       ibdg        IN    CHAR,  -- inbound_boarding_point,
       outbound_flight_id in number,  -- flight pax is outgoing on
	inbound_fltid       OUT   NUMBER,
       fltstime    IN    CHAR default '0000',
	procname    IN    CHAR default 'DUMMY',
	unitname    IN    CHAR default 1,
	dcsid       IN    CHAR default 'DUMMY',
	subunitname IN    CHAR default 0,
       bvis_subhead IN   flight.bvis_subheader%TYPE default NULL
);

  FUNCTION get_inbound_status(p_object_type       IN  VARCHAR2,
                              p_flight_id         IN  flight.flight_id%TYPE,
                              p_pax_id            IN  NUMBER           DEFAULT NULL,
                              p_bag_id            IN  NUMBER           DEFAULT NULL,
                              p_uld_no            IN  CHAR             DEFAULT NULL
                              ) RETURN VARCHAR2;

END inbound_flight_utils;
/

CREATE OR REPLACE PACKAGE BODY inbound_flight_utils AS

FUNCTION is_through(
             p_object_type       IN  VARCHAR2
            ,p_flight_id         IN  flight.flight_id%TYPE
            ,p_pax_id            IN  NUMBER           DEFAULT NULL
            ,p_bag_id            IN  NUMBER           DEFAULT NULL
            ,p_uld_no            IN  CHAR             DEFAULT NULL
            ) RETURN BOOLEAN
IS

   -- Local Variables
   l_through_flt_window  NUMBER;
   l_flight_type         flight.flight_type%TYPE;
   l_flight_no           flight.flight_no%TYPE;
   l_flight_date         flight.flight_date%TYPE;
   l_src_site_id         site_info.site_id%TYPE;
   l_src_airport_code    site_info.airport_code%TYPE;
   l_object_type         VARCHAR2(10);
   l_next_leg_id         flight.next_leg_id%TYPE;
   l_second_next_leg_id  flight.next_leg_id%TYPE;
   l_dest_airport_code   flight_dests.flight_destination%TYPE;
   l_dest_site_id        site_info.site_id%TYPE;
   l_pax_destination     pax.pax_destination%TYPE;
   l_pax_id              pax.pax_id%TYPE;
   l_temp                NUMBER;
   l_found               BOOLEAN;
   l_next_flight         inbound_pax_onward_dests.onward_flight_no%TYPE;
   l_cnt                 NUMBER := 0;

   -- Cursors
   CURSOR c_match_outbound_flight (p_out_flight flight.flight_id%TYPE) IS
          SELECT next_fo.flight_id
          FROM   flight_outbound_v1 current_fo,
                 flight_outbound_v1 next_fo,
                 flight_dests fd,
                 site_info si
          WHERE  current_fo.flight_id = p_out_flight
            AND  current_fo.flight_no = next_fo.flight_no
            AND  flt_date_to_date(current_fo.flight_date)
                 BETWEEN flt_date_to_date(next_fo.flight_date)
                     AND flt_date_to_date(next_fo.flight_date) + l_through_flt_window
            AND  next_fo.site_id  = si.site_id
            AND  fd.flight_id = current_fo.flight_id
            AND  fd.dest_order = 1
            AND  fd.flight_destination = si.airport_code;

   CURSOR c_outbound_pax (p_out_pax pax.pax_id%TYPE) IS
          SELECT owf.dest_order
          FROM   flight_outbound_v1 fo,
                 pax p,
                 onward_flight owf
          WHERE  p.pax_id = p_out_pax
            AND  fo.flight_id = p.flight_id
            AND  owf.pax_id = p.pax_id
            AND  owf.norm_fno = fo.flight_no
            AND  flt_date_to_date(owf.odate)
                 BETWEEN flt_date_to_date(fo.flight_date)
                     AND flt_date_to_date(fo.flight_date) + l_through_flt_window;

   CURSOR c_inbound_pax (p_in_pax inbound_pax.inbound_pax_id%TYPE) IS
          SELECT ip.inbound_pax_id
          FROM   flight_inbound_v1 f,
                 inbound_pax_v1 ip,
                 inbound_pax_onward_dests ipod
          WHERE  ip.inbound_pax_id = p_in_pax
            AND  f.flight_id = ip.inbound_flight_id
            AND  ipod.inbound_pax_id = ip.inbound_pax_id
            AND  ipod.onward_flight_no = f.flight_no
            AND  flt_date_to_date(ipod.onward_flight_date)
                 BETWEEN flt_date_to_date(f.flight_date)
                     AND flt_date_to_date(f.flight_date) + l_through_flt_window
            AND  ip.result_pax_status IN ('UNPROCESSED', 'CREATED', 'AMENDED');

   CURSOR c_all_outbound_paxes (p_out_flight flight.flight_id%TYPE,
                                p_out_uld ulds.uld_no%TYPE) IS
          SELECT p.pax_id
          FROM   pax p,
                 bag b
          WHERE  p.flight_id = p_out_flight
            AND  p.delete_marker = 0
            AND  b.pax_id = p.pax_id
            AND  b.delete_marker = 0
            AND  b.loaded_status = 'Y'
            AND  TRIM(b.uld_no) = TRIM(p_out_uld)
          GROUP BY p.pax_id;

   CURSOR c_all_inbound_paxes (p_in_flight flight.flight_id%TYPE,
                               p_in_uld inbound_uld.source_uld_no%TYPE) IS
          SELECT ip.inbound_pax_id
          FROM   inbound_pax ip,
                 inbound_bag ib,
                 inbound_uld_v1 iu
          WHERE  ip.inbound_flight_id = p_in_flight
            AND  ip.result_pax_status IN ('CREATED', 'AMENDED', 'UNPROCESSED')
            AND  ib.inbound_pax_id = ip.inbound_pax_id
            AND  ib.result_bag_status IN ('CREATED', 'AMENDED', 'UNPROCESSED')
            AND  iu.inbound_uld_id = ib.inbound_uld_id
            AND  iu.result_uld_status IN ('CREATED', 'AMENDED', 'UNPROCESSED')
            AND  TRIM(iu.uld_no) = TRIM(p_in_uld)
          GROUP BY ip.inbound_pax_id;

BEGIN

   -- Initialise
   l_object_type := TRIM(UPPER(p_object_type));

   -- Validate Parameters
   IF (l_object_type NOT IN ('FLIGHT','PAX','BAG','ULD')) THEN
      raise_application_error(-20000,'FUNCTION: is_through ERROR: invalid object type'
                              ||' REASON: p_object_type must be either FLIGHT,PAX,BAG or ULD');
   END IF;

   IF (NVL(p_flight_id, 0) = 0) THEN
      raise_application_error(-20000,'FUNCTION: is_through ERROR: flight_id not specified'
                              ||' REASON: p_flight_id is a mandatory parameter');
   END IF;

   -- Determine some basic information about the flight
   SELECT f.flight_type,
          f.flight_no,
          f.flight_date,
          f.next_leg_id
   INTO   l_flight_type,
          l_flight_no,
          l_flight_date,
          l_next_leg_id
   FROM   flight f
   WHERE  f.flight_id = p_flight_id;

   -- Validate Flight Direction
   IF (l_flight_type NOT IN ('I', 'O')) THEN

      -- Invalid Flight Type
      raise_application_error(-20000,'FUNCTION: is_through ERROR: flight_type unknown'
                     ||' REASON: Flight Direction should be <I>nbound or <O>utbound');

   END IF;

   -- Get the Source and Destination Airports of the flight
   IF (l_flight_type = 'I') THEN

     -- For an Inbound Flight, the Source Airport is either:
     --   a) The Originating Airport of an Incoming BMM
     --  or
     --   b) The Site of the Outbound Flight with this flight
     --      as its next leg id
     --
     -- The Destination Airport is the site of the Inbound Flight
     SELECT fi.site_id,
            si.airport_code,
            Nvl(msi.site_id, fo.site_id),
            Nvl(m.originating_airport, fsi.airport_code)
     INTO   l_dest_site_id,
            l_dest_airport_code,
            l_src_site_id,
            l_src_airport_code
     FROM   flight fi,
            site_info si,
            flight fo,
            messages m,
            site_info msi,
            site_info fsi
     WHERE  fi.flight_id = p_flight_id
       AND  fi.site_id = si.site_id
       AND  fo.next_leg_id(+) = fi.flight_id
       AND  m.flight_id(+) = fi.flight_id
       AND  m.message_type(+) = 'BMM'
       AND  m.message_direction(+) = 'I'
       AND  m.processed_state(+) = 'P'
       AND  m.originating_airport = msi.airport_code(+)
       AND  fo.site_id = fsi.site_id(+);

   ELSE

      -- For an Outbound Flight, the Source Airport is the site of
      -- the Outbound Flight and the Destination Airport is the site
      -- of the first destination

     SELECT f.site_id,
            current_si.airport_code,
            next_si.site_id,
            fd.flight_destination
     INTO   l_src_site_id,
            l_src_airport_code,
            l_dest_site_id,
            l_dest_airport_code
     FROM   flight f,
            flight_dests fd,
            site_info current_si,
            site_info next_si
     WHERE  f.flight_id = p_flight_id
       AND  f.site_id = current_si.site_id
       AND  f.flight_id = fd.flight_id
       AND  fd.dest_order = 1
       AND  fd.flight_destination = next_si.airport_code;

   END IF;

   -- Fetch relevant configuration parameters
   l_through_flt_window := NVL(config.get_value('THROUGH_FLIGHT_WINDOW'),3);

   ----------------------------------------------------------------------
   ------------------------------- FLIGHT -------------------------------
   ----------------------------------------------------------------------
   IF (l_object_type = 'FLIGHT') THEN

      ------------------------- Inbound Flight -------------------------
      IF (l_flight_type = 'I') THEN

         -- For an Inbound Flight, check the next_leg_id.  If this is set then
         -- the related Outbound Through Flight has already been determined and
         -- hence this flight is a Through Flight.
         -- If the next leg id is not set, then locate an Outbound Flight at the
         -- same site as the Inbound Flight with the following characteristics:
         --   1) Flight Number is the same as the Inbound Flight
         --   2) Flight Date is within the Through Flight Window

         IF (l_next_leg_id IS NOT NULL) THEN

           -- Flight is already determined as a Through Flight
           RETURN TRUE;

         END IF;

         -- Find Matching Outbound Flight
         l_next_leg_id := match_inbound_outbound_flts(p_flight_id);
         IF (l_next_leg_id IS NOT NULL) THEN

            -- Matching Outbound Flight found - flight is Through
            RETURN TRUE;

         END IF;

      ------------------------- Outbound Flight -------------------------
      ELSIF (l_flight_type = 'O') THEN

         -- For an Outbound Flight, check the next leg id.  If it is set, then
         -- determine whether the referenced flight (i.e. the Inbound Flight) has
         -- a next leg id defined.  If it does then it is a Through flight.
         -- If one of the next leg id's is not defined, then locate an Outbound
         -- Flight at the next destination of the current flight with the same
         -- flight number and a date within the through flight window

         IF (l_next_leg_id IS NOT NULL) THEN

            -- Get the next leg id of the next flight
            SELECT f.next_leg_id
            INTO   l_second_next_leg_id
            FROM   flight f
            WHERE  f.flight_id = l_next_leg_id;

            IF (l_second_next_leg_id IS NOT NULL) THEN

               -- Already linked to Through Flight
               RETURN TRUE;

            END IF;

         END IF;

         -- Try to match an Outbound Flight at the next destination with the
         -- current Outbound Flight
         l_temp := NULL;
         OPEN c_match_outbound_flight(p_flight_id);
         FETCH c_match_outbound_flight INTO l_temp;
         CLOSE c_match_outbound_flight;

         IF (l_temp IS NOT NULL) THEN

            -- Managed to link to an Outbound Flight
            RETURN TRUE;

         END IF;

      END IF;

   -------------------------------------------------------------------
   ------------------------------- PAX -------------------------------
   -------------------------------------------------------------------
   ELSIF (l_object_type = 'PAX') THEN

      -- Check that PAX is on associated flight
      IF (l_flight_type = 'O')
      THEN
          SELECT count(*)
          INTO   l_cnt
          FROM   pax p
          WHERE  p.pax_id = p_pax_id
          AND    p.flight_id = p_flight_id;
      
      ELSIF (l_flight_type = 'I')
      THEN
          SELECT count(*)
          INTO   l_cnt
          FROM   inbound_pax_v1 ip
          WHERE  ip.inbound_pax_id = p_pax_id
          AND    ip.inbound_flight_id = p_flight_id;
      END IF;

      IF ( l_cnt = 0 )
      THEN
          raise_application_error(-20000,'FUNCTION: is_through error: REASON pax specified is not on selected flight');
      END IF;

      -- Any Pax can only be a Through Pax if the flight is a Through Flight
      IF (is_through('FLIGHT', p_flight_id, NULL, NULL, NULL) = FALSE) THEN

         -- Flight is not through and hence Pax is not Through
         RETURN FALSE;

      END IF;

      ------------------------- Outbound Pax -------------------------
      IF (l_flight_type = 'O') THEN

         -- Get the Outbound Pax Destination
         SELECT p.pax_destination
         INTO   l_pax_destination
         FROM   pax p
         WHERE  p.pax_id = p_pax_id;

         -- Is the pax destination the same as the destination of the flight
         IF (l_pax_destination <> l_dest_airport_code) THEN

            -- Pax is not bound for next airport and hence must be a Through Pax
            RETURN TRUE;

         END IF;

         -- Check whether the pax has an Onward Flight on the same flight
         OPEN c_outbound_pax(p_pax_id);
         FETCH c_outbound_pax INTO l_temp;
         CLOSE c_outbound_pax;

         IF (l_temp IS NOT NULL) THEN

            -- Found an Onward Flight representing a Through Flight
            RETURN TRUE;

         END IF;

      ------------------------- Inbound Pax -------------------------
      ELSIF (l_flight_type = 'I') THEN

         -- Get the Inbound Pax Destination
         SELECT ip.pax_destination
         INTO   l_pax_destination
         FROM   inbound_pax_v1 ip
         WHERE  ip.inbound_pax_id = p_pax_id;

         -- Get the next flight for this pax
         BEGIN

           SELECT ipod.onward_flight_no
           INTO   l_next_flight
           FROM   inbound_pax_onward_dests ipod
           WHERE  ipod.inbound_pax_id = p_pax_id
             AND  ipod.destination_sequence = 1;

         EXCEPTION
           WHEN No_Data_Found THEN
             l_next_flight := NULL;
         END;

         -- Is the pax destination the same as the next destination of the flight and the next flight is the same flight
         IF (l_pax_destination <> l_dest_airport_code AND Nvl(l_next_flight, l_flight_no) = l_flight_no) THEN

            -- Pax is not bound for next airport and hence must be a Through Pax
            RETURN TRUE;

         END IF;

         OPEN c_inbound_pax(p_pax_id);
         FETCH c_inbound_pax INTO l_temp;
         CLOSE c_inbound_pax;

         IF (l_temp IS NOT NULL) THEN

            -- Found an Onward Flight representing a Through Flight
            RETURN TRUE;

         END IF;

      END IF;

   -------------------------------------------------------------------
   ------------------------------- BAG -------------------------------
   -------------------------------------------------------------------
   ELSIF (l_object_type = 'BAG') THEN

      -- Check that BAG is on associated flight
      IF (l_flight_type = 'O')
      THEN
          SELECT count(*)
          INTO   l_cnt
          FROM   bag b, pax p
          WHERE  b.bag_id = p_bag_id
          AND    b.pax_id = p.pax_id
          AND    p.flight_id = p_flight_id;
      ELSIF (l_flight_type = 'I')
      THEN
          SELECT count(*)
          INTO   l_cnt
          FROM   inbound_bag ib, inbound_pax ip
          WHERE  ib.inbound_bag_id = p_bag_id
          AND    ib.inbound_pax_id = ip.inbound_pax_id
          AND    ip.inbound_flight_id = p_flight_id;
      END IF;

      IF ( l_cnt = 0 )
      THEN
          raise_application_error(-20000,'FUNCTION: is_through error: REASON bag specified is not on selected flight');
      END IF;

      ------------------------- Outbound Bag -------------------------
      IF (l_flight_type = 'O') THEN

         -- Bags always have the same status as their respective passenger.
         -- Locate the passenger for this bag.
         SELECT b.pax_id
         INTO   l_pax_id
         FROM   bag b
         WHERE  b.bag_id = p_bag_id;

      ------------------------- Inbound Bag -------------------------
      ELSIF (l_flight_type = 'I') THEN

         -- Bags always have the same status as their respective passenger.
         -- Locate the passenger for this bag.
         SELECT ib.inbound_pax_id
         INTO   l_pax_id
         FROM   inbound_bag ib
         WHERE  ib.inbound_bag_id = p_bag_id;

      END IF;

      -- Now check the specified passenger to determine whether they are
      -- a Through Passenger (and hence whether this is a Through bag)
      RETURN is_through('PAX', p_flight_id, l_pax_id, NULL, NULL);

   -------------------------------------------------------------------
   ------------------------------- ULD -------------------------------
   -------------------------------------------------------------------
   ELSIF (l_object_type = 'ULD') THEN

      -- Check that ULD_NO is on associated flight
      IF (l_flight_type = 'O')
      THEN
          SELECT count(*)
          INTO   l_cnt
          FROM   ulds u
          WHERE  u.uld_no = p_uld_no
          AND    u.flight_id = p_flight_id;
      ELSIF (l_flight_type = 'I')
      THEN
          SELECT count(*)
          INTO   l_cnt
          FROM   inbound_uld_v1 iu
          WHERE  TRIM(iu.uld_no) = TRIM(p_uld_no)
          AND    iu.inbound_flight_id = p_flight_id;
      END IF;

      IF ( l_cnt = 0 )
      THEN
          raise_application_error(-20000,'FUNCTION: is_through error: REASON uld_no specified is not on selected flight');
      END IF;

      -- Check if this is a Hold - Holds cannot be Through containers
      SELECT COUNT(*)
      INTO   l_temp
      FROM   holds
      WHERE  TRIM(hold_no) = TRIM(p_uld_no);

      IF (l_temp > 0) THEN

         -- This is a Hold
         RETURN FALSE;

      END IF;

      -- Any ULD can only be a Through ULD if the flight is a Through Flight
      IF (is_through('FLIGHT', p_flight_id, NULL, NULL, NULL) = FALSE) THEN

         -- Flight is not through and hence ULD is not Through
         RETURN FALSE;

      END IF;


      ------------------------- Outbound ULD -------------------------
      IF (l_flight_type = 'O') THEN

         -- Check whether the Destination Airport is included in the list
         -- of ULD Destinations
         SELECT COUNT(*)
         INTO   l_temp
         FROM   uld_dests ud
         WHERE  ud.flight_id = p_flight_id
           AND  TRIM(ud.uld_no) = TRIM(p_uld_no)
           AND  uld_destination = l_dest_airport_code;

         IF (l_temp > 0) THEN

            -- ULD Dests defines Destination Airport, therefore not a Through ULD
            RETURN FALSE;

         END IF;

         -- Check whether at least one passenger with bags in the ULD is a Through
         -- passenger
         l_found := FALSE;
         FOR r_pax IN c_all_outbound_paxes(p_flight_id, p_uld_no) LOOP

            -- Check if this passenger is a Through Passenger
            l_found := is_through('PAX', p_flight_id, r_pax.pax_id, NULL, NULL);

            EXIT WHEN l_found;

         END LOOP;

         -- Return the result
         RETURN l_found;

      ------------------------- Inbound ULD -------------------------
      ELSIF (l_flight_type = 'I') THEN

         -- Check whether the Destination Airport is included in the list
         -- of ULD Destinations
         SELECT COUNT(*)
         INTO   l_temp
         FROM   inbound_uld_v1 iu,
                inbound_uld_dests iud
         WHERE  iu.inbound_uld_id = iud.inbound_uld_id
           AND  iu.inbound_flight_id = p_flight_id
           AND  TRIM(iu.uld_no) = TRIM(p_uld_no)
           AND  iud.destination = l_dest_airport_code
           AND  iu.result_uld_status IN ('CREATED', 'AMENDED', 'UNPROCESSED');

         IF (l_temp > 0) THEN

            -- ULD Dests defines Destination Airport, therefore not a Through ULD
            RETURN FALSE;

         END IF;

         -- Check whether at least one passenger with bags in the ULD is a Through
         -- passenger
         l_found := FALSE;
         FOR r_pax IN c_all_inbound_paxes(p_flight_id, p_uld_no) LOOP

            -- Check if this passenger is a Through Passenger
            l_found := is_through('PAX', p_flight_id, r_pax.inbound_pax_id, NULL, NULL);

            EXIT WHEN l_found;

         END LOOP;

         -- Return the result
         RETURN l_found;

      END IF;

   END IF;

   -- If we get here then the object is not Through
   RETURN FALSE;

EXCEPTION
   WHEN OTHERS THEN
      add_app_msg.log_oracle_error('is_through',SQLERRM,SQLCODE);
      RAISE;
END is_through;

FUNCTION is_transfer(
             p_object_type       IN  VARCHAR2
            ,p_flight_id         IN  flight.flight_id%TYPE
            ,p_pax_id            IN  NUMBER           DEFAULT NULL
            ,p_bag_id            IN  NUMBER           DEFAULT NULL
            ,p_uld_no            IN  CHAR             DEFAULT NULL
            ) RETURN BOOLEAN
IS

   -- Local Variables
   l_temp                NUMBER;
   l_flight_type         flight.flight_type%TYPE;
   l_flight_no           flight.flight_no%TYPE;
   l_flight_date         flight.flight_date%TYPE;
   l_object_type         VARCHAR2(10);
   l_next_leg_id         flight.next_leg_id%TYPE;
   l_pax_id              pax.pax_id%TYPE;
   l_category            ulds.uld_category%TYPE;
   l_type                ulds.uld_type%TYPE;
   l_cnt                 NUMBER := 0;

BEGIN
   -- Initialise
   l_object_type := TRIM(UPPER(p_object_type));

   -- Validate Parameters
   IF (l_object_type NOT IN ('FLIGHT','PAX','BAG','ULD')) THEN
      raise_application_error(-20000,'FUNCTION: is_transfer ERROR: invalid object type'
                              ||' REASON: p_object_type must be either FLIGHT,PAX,BAG or ULD');
   END IF;

   IF (NVL(p_flight_id, 0) = 0) THEN
      raise_application_error(-20000,'FUNCTION: is_transfer ERROR: flight_id not specified'
                              ||' REASON: p_flight_id is a mandatory parameter');
   END IF;

   -- Determine some basic information about the flight
   SELECT f.flight_type,
          f.flight_no,
          f.flight_date,
          f.next_leg_id
   INTO   l_flight_type,
          l_flight_no,
          l_flight_date,
          l_next_leg_id
   FROM   flight f
   WHERE  f.flight_id = p_flight_id;

   -- Validate Flight Direction
   IF (l_flight_type NOT IN ('I', 'O')) THEN

      -- Invalid Flight Type
      raise_application_error(-20000,'FUNCTION: is_transfer ERROR: flight_type unknown'
                     ||' REASON: Flight Direction should be <I>nbound or <O>utbound');

   END IF;

   ----------------------------------------------------------------------
   ------------------------------- FLIGHT -------------------------------
   ----------------------------------------------------------------------
   IF (l_object_type = 'FLIGHT') THEN

      -- No such thing as a Transfer Flight
      RETURN FALSE;

   -------------------------------------------------------------------
   ------------------------------- ULD -------------------------------
   -------------------------------------------------------------------
   ELSIF (l_object_type = 'ULD') THEN

      -- Check that ULD_NO is on associated flight
      IF (l_flight_type = 'O')
      THEN
          SELECT count(*)
          INTO   l_cnt
          FROM   ulds u
          WHERE  u.uld_no = p_uld_no
          AND    u.flight_id = p_flight_id;
      ELSIF (l_flight_type = 'I')
      THEN
          SELECT count(*)
          INTO   l_cnt
          FROM   inbound_uld_v1 iu
          WHERE  TRIM(iu.uld_no) = TRIM(p_uld_no)
          AND    iu.inbound_flight_id = p_flight_id;
      END IF;
      IF ( l_cnt = 0 )
      THEN
          raise_application_error(-20000,'FUNCTION: is_transfer error: REASON uld_no specified is not on selected flight');
      END IF;

      -- Check if this is a Hold - Holds cannot be Transfer containers
      SELECT COUNT(*)
      INTO   l_temp
      FROM   holds
      WHERE  TRIM(hold_no) = TRIM(p_uld_no);

      IF (l_temp > 0) THEN

         -- This is a Hold
         RETURN FALSE;

      END IF;

      -- Check that this ULD is not a Through ULD
      IF (is_through('ULD', p_flight_id, NULL, NULL, p_uld_no) = TRUE) THEN

         -- It is a Through ULD and therefore cannot be a Transfer ULD
         RETURN FALSE;

      END IF;

      ------------------------- Outbound ULD -------------------------
      IF (l_flight_type = 'O') THEN

         -- Fetch the ULD Category
         SELECT u.uld_category
         INTO   l_category
         FROM   ulds u
         WHERE  u.flight_id = p_flight_id
           AND  TRIM(u.uld_no) = TRIM(p_uld_no);

      ------------------------- Inbound ULD -------------------------
       ELSIF (l_flight_type = 'I') THEN

         -- Fetch the ULD Category
         SELECT iu.uld_category
         INTO   l_category
         FROM   inbound_uld_v1 iu
         WHERE  iu.inbound_flight_id = p_flight_id
           AND  TRIM(iu.uld_no) = TRIM(p_uld_no)
           AND  iu.result_uld_status IN ('CREATED', 'AMENDED', 'UNPROCESSED');

       END IF;

       BEGIN

         l_type := uld_cards.get_uld_type(l_category);

       EXCEPTION
         WHEN others THEN
           l_type := 'L';
       END;

       IF (l_type = 'T') THEN

          -- This is a Transfer Container
          RETURN TRUE;

       END IF;

   -------------------------------------------------------------------
   ------------------------------- PAX -------------------------------
   -------------------------------------------------------------------
   ELSIF (l_object_type = 'PAX') THEN

      -- Check that PAX is on associated flight
      IF (l_flight_type = 'O')
      THEN
          SELECT count(*)
          INTO   l_cnt
          FROM   pax p
          WHERE  p.pax_id = p_pax_id
          AND    p.flight_id = p_flight_id;
      ELSIF (l_flight_type = 'I')
      THEN
          SELECT count(*)
          INTO   l_cnt
          FROM   inbound_pax_v1 ip
          WHERE  ip.inbound_pax_id = p_pax_id
          AND    ip.inbound_flight_id = p_flight_id;
      END IF;
      IF ( l_cnt = 0 )
      THEN
          raise_application_error(-20000,'FUNCTION: is_transfer error: REASON pax specified is not on selected flight');
      END IF;

      -- Check if this pax is a Through Pax
      IF (is_through('PAX', p_flight_id, p_pax_id, NULL, NULL) = TRUE) THEN

         -- Pax is a Through Pax and therefore cannot be a transfer pax
         RETURN FALSE;

      END IF;

      ------------------------- Outbound Pax -------------------------
      IF (l_flight_type = 'O') THEN

         -- Check if the Outbuond pax has onward flights
         SELECT COUNT(*)
         INTO   l_temp
         FROM   onward_flight owf
         WHERE  owf.pax_id = p_pax_id;

      ------------------------- Inbound Pax -------------------------
      ELSIF (l_flight_type = 'I') THEN

         -- Check if the Inbound Pax has onward flights
         SELECT COUNT(*)
         INTO   l_temp
         FROM   inbound_pax_onward_dests ipod
         WHERE  ipod.inbound_pax_id = p_pax_id;

      END IF;

      -- If Onward Flights were found then this is a transfer pax
      IF (l_temp > 0) THEN

         RETURN TRUE;

      END IF;

   -------------------------------------------------------------------
   ------------------------------- BAG -------------------------------
   -------------------------------------------------------------------
   ELSIF (l_object_type = 'BAG') THEN

      -- Check that BAG is on associated flight
      IF (l_flight_type = 'O')
      THEN
          SELECT count(*)
          INTO   l_cnt
          FROM   bag b, pax p
          WHERE  b.bag_id = p_bag_id
          AND    b.pax_id = p.pax_id
          AND    p.flight_id = p_flight_id;
      ELSIF (l_flight_type = 'I')
      THEN
          SELECT count(*)
          INTO   l_cnt
          FROM   inbound_bag ib, inbound_pax ip
          WHERE  ib.inbound_bag_id = p_bag_id
          AND    ib.inbound_pax_id = ip.inbound_pax_id
          AND    ip.inbound_flight_id = p_flight_id;
      END IF;

      IF ( l_cnt = 0 )
      THEN
          raise_application_error(-20000,'FUNCTION: is_transfer error: REASON bag specified is not on selected flight');
      END IF;

      ------------------------- Outbound Bag -------------------------
      IF (l_flight_type = 'O') THEN

         SELECT b.pax_id
         INTO   l_pax_id
         FROM   bag b
         WHERE  b.bag_id = p_bag_id;

      ------------------------- Inbound Bag -------------------------
      ELSIF (l_flight_type = 'I') THEN

         SELECT ib.inbound_pax_id
         INTO   l_pax_id
         FROM   inbound_bag ib
         WHERE  ib.inbound_bag_id = p_bag_id;

      END IF;

      -- Now determine the status of this passenger
      RETURN is_transfer('PAX', p_flight_id, l_pax_id, NULL, NULL);

   END IF;

   -- If we get to here then it is not a transfer item
   RETURN FALSE;

EXCEPTION
   WHEN OTHERS THEN
      add_app_msg.log_oracle_error('is_transfer',SQLERRM,SQLCODE);
      RAISE;
END is_transfer;

FUNCTION increment_flt_date1(
                      p_flight_date IN flight.flight_date%TYPE
                     ,num_days     IN NUMBER
                     )RETURN flight.flight_date%TYPE
/*Adds <n> days to a given flight date and returns a flight date*/
IS
   new_date DATE;
BEGIN
    new_date := flt_date_to_date(p_flight_date) + num_days;

   /*If the new date is outside the current rolling year then raise an error
   a rolling year is from (Today - 6 Months) thru (Today + 6 Months - 1 day)*/
   IF   new_date < ADD_MONTHS(TRUNC(SYSDATE),  -6)
     OR new_date   > ADD_MONTHS(TRUNC(SYSDATE),  +6) - 1
   THEN
      raise_application_error(-20000,'FUNCTION:increment_flt_date1 ERROR:operation'
                              ||' resulted in a date outside the rolling year');
   END IF;
   /*Now convet the day number to an actual date*/
   RETURN TO_CHAR(new_date,'DDMON');
END increment_flt_date1;

FUNCTION increment_flt_date2(
                      p_flight_date IN flight.flight_date%TYPE
                     ,num_days     IN NUMBER
                     )RETURN DATE
/*Adds <n> days to a given flight date and returns a date datatype*/
IS
   new_date DATE;
BEGIN

    new_date := flt_date_to_date(p_flight_date) + num_days;

   /*If the new date is outside the current rolling year then raise an error
   a rolling year is from (Today - 6 Months) thru (Today + 6 Months - 1 day)*/
   IF   new_date < ADD_MONTHS(TRUNC(SYSDATE),  -6)
     OR new_date   > ADD_MONTHS(TRUNC(SYSDATE),  +6) - 1
   THEN
      raise_application_error(-20000,'FUNCTION:increment_flt_date2 ERROR:operation'
                              ||' resulted in a date outside the rolling year');
   END IF;
   /*Now convet the day number to an actual date*/
   RETURN new_date;
END increment_flt_date2;

FUNCTION flt_date_to_date(
                      p_flight_date IN flight.flight_date%TYPE
                     )RETURN DATE
/*Convert a given flight date to a system date*/
IS
   original_date      DATE;
   final_date       DATE;
   rolling_year_start DATE;
   rolling_year_end   DATE;
BEGIN
   /*Get the date in the rolling year.
   a rolling year is from (Today - 6 Months) thru (Today + 6 Months - 1 day)
   both days inclusive
   */
   rolling_year_start := ADD_MONTHS(TRUNC(SYSDATE),  -6);
   rolling_year_end   := ADD_MONTHS(TRUNC(SYSDATE),  +6) - 1;
   original_date      := TO_DATE(p_flight_date, 'DDMON');

   IF original_date > rolling_year_end THEN
      /*29FEB is not in the rolling year.
      if 29FEB was within the rolling year control wouldn't have come here in first place.
      add_months on 29FEB would change it to 28FEB. raise an error*/
      IF UPPER(p_flight_date) = '29FEB' THEN
          raise_application_error(-20000,'FUNCTION:flt_date_to_date ERROR:29FEB can not be converted'
                                 ||' to a date within the rolling year');
      END IF;
      final_date := ADD_MONTHS(original_date ,-12);/*same day previous year which is within the rolling year*/
   ELSIF original_date < rolling_year_start THEN
      /*29FEB is within the current chronological year but not in the rolling year.
      if 29FEB was within the rolling year control wouldn't have come here in first place.
      add_months on 29FEB would change it to 28FEB. raise an error*/
      IF UPPER(p_flight_date) = '29FEB' THEN
          raise_application_error(-20000,'FUNCTION:flt_date_to_date ERROR:29FEB can not be converted'
                                 ||' to a date within the rolling year');
      END IF;
      final_date := ADD_MONTHS(original_date,12); /*same day next year which is within the rolling year*/
   ELSE
      final_date := original_date;
   END IF;

   RETURN final_date;
EXCEPTION
WHEN OTHERS THEN
   add_app_msg.log_oracle_error('flt_date_to_date',SQLERRM,SQLCODE);
   RAISE;
END flt_date_to_date;

FUNCTION get_flt_date_difference(
                      p_flt_date_subtract_from IN flight.flight_date%TYPE
                     ,p_flt_date_subtracted    IN flight.flight_date%TYPE
                     )RETURN NUMBER
/*Calculate the number of days between 2 given flight dates*/
IS
   date_subtract_from DATE;
   date_subtracted    DATE;
BEGIN
   date_subtract_from := flt_date_to_date(p_flt_date_subtract_from);
   date_subtracted    := flt_date_to_date(p_flt_date_subtracted);
   /*return the difference*/
   RETURN date_subtract_from - date_subtracted;
END get_flt_date_difference;

FUNCTION match_inbound_outbound_flts(
            p_flight_id IN flight.flight_id%TYPE
            ) RETURN flight.flight_id%TYPE
/*accepts a flight id and determines the corresponding flight id
 having opposite direction.(the next leg of a flight)*/
IS
   l_matched_flight_id   flight.flight_id%TYPE;
   l_flight_type         flight.flight_type%TYPE;
   l_flight_no           flight.flight_no%TYPE;
   l_site_id             flight.site_id%TYPE;
   l_flight_date         flight.flight_date%TYPE;
   l_through_flt_window  NUMBER;
BEGIN
   SELECT next_leg_id,
          flight_type,
          flight_no,
          site_id,
          flight_date
     INTO l_matched_flight_id,
          l_flight_type,
          l_flight_no,
          l_site_id,
          l_flight_date
     FROM flight
    WHERE flight_id = p_flight_id;

   IF  l_matched_flight_id IS NULL THEN

      l_through_flt_window := NVL(config.get_value('THROUGH_FLIGHT_WINDOW'),3);

      /*For an Inbound Flight Id, if next_leg_id IS null, then search for
       the earliest outbound flight for the same site and flight number,
       provided that it is within the appropriate window (default - 3 days).
       For an Outbound Flight Id, if next_leg_id IS null, then search for an
       inbound flight for the site relevant to the next destination of the
       outbound flight with the same outbound flight date or one day after
       the outbound flight date.*/
      BEGIN
         SELECT f1.flight_id
           INTO l_matched_flight_id
           FROM (
                  SELECT flight_id AS flight_id
                    FROM flight f
                   WHERE f.flight_type = DECODE(l_flight_type,'I','O','O','I',NULL)
                     AND f.flight_no   = l_flight_no
                     AND (
                            (    f.flight_type = 'O'
                             AND f.site_id     = l_site_id
                             AND flt_date_to_date(f.flight_date)
                                 BETWEEN flt_date_to_date(l_flight_date)
                                 AND flt_date_to_date(l_flight_date) + l_through_flt_window
                            )
                            OR
                            (    f.flight_type = 'I'
                             AND f.site_id     = (SELECT si.site_id
                                                    FROM flight_dests fd , site_info si
                                                   WHERE fd.flight_id  = p_flight_id
                                                     AND fd.dest_order = 1
                                                     AND fd.flight_destination = si.airport_code)
                             AND f.flight_date = l_flight_date
                            )
                         )
                   ORDER BY flt_date_to_date(f.flight_date) ASC
                ) f1
          WHERE ROWNUM = 1;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
         l_matched_flight_id:= NULL;
      WHEN OTHERS THEN
         RAISE;
      END;
   END IF;

   RETURN l_matched_flight_id;

EXCEPTION
WHEN no_data_found THEN
    return NULL;
WHEN OTHERS THEN
   add_app_msg.log_oracle_error('match_inbound_outbound_flts',SQLERRM,SQLCODE);
   RAISE;
END match_inbound_outbound_flts;

PROCEDURE set_next_leg_id(
             p_flight_id IN  flight.flight_id%TYPE
            ,p_updated   OUT BOOLEAN
            )
IS
   l_matched_flight_id     flight.flight_id%TYPE;
BEGIN
   /*Given a flight id, find the flight id which is the next leg of the given
     flight id and update FLIGHT.NEXT_LEG_ID of the given flight to the
     matched flight id*/

   p_updated:= FALSE;

   l_matched_flight_id := match_inbound_outbound_flts(p_flight_id);

   IF l_matched_flight_id IS NOT NULL THEN
      UPDATE flight
         SET next_leg_id = l_matched_flight_id
       WHERE flight_id   = p_flight_id;
      IF SQL%FOUND THEN
         p_updated := TRUE;
      END IF;
   END IF;
EXCEPTION
WHEN OTHERS THEN
   add_app_msg.log_oracle_error('set_next_leg_id',SQLERRM,SQLCODE);
   RAISE;
END set_next_leg_id;

FUNCTION get_inbound_orig_site(
            p_flight_id IN  flight.flight_id%TYPE
            )RETURN site_info.site_id%TYPE
IS
   l_orig_site     site_info.site_id%TYPE;
   l_flight_type   flight.flight_type%TYPE;
BEGIN
   /*Given a flight id, if it is an inbound flight, return the site at which the initiating flight originated.
     return null if flight id is an outbound flight*/
   BEGIN
    SELECT flight_type
      INTO l_flight_type
      FROM flight
     WHERE flight_id = p_flight_id;
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
    l_flight_type := NULL;
   END;

   IF  l_flight_type = 'I' THEN
      BEGIN
         /*only one of the two queries being UNIONed should return a match.
          hence if more than one match found then the error should not be trapped
          as it must be due to rogue data*/
         SELECT site_id
          INTO l_orig_site
          FROM flight
         WHERE flight_type = 'O'
           AND next_leg_id = p_flight_id
         UNION
         SELECT multi_site.get_site_id(m.originating_airport)
          FROM messages m
         WHERE m.flight_id  = p_flight_id
           AND m.processed_state <> 'R';
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      l_orig_site := NULL;
      END;
   ELSE
      l_orig_site := NULL;
   END IF;
RETURN l_orig_site;
EXCEPTION
WHEN OTHERS THEN
   add_app_msg.log_oracle_error('get_inbound_orig_site',SQLERRM,SQLCODE);
   RAISE;
END get_inbound_orig_site;


PROCEDURE add_inbound_flight (
  fltno              IN     CHAR,                                        -- inbound flight no
  fltdate            IN     CHAR,                                        -- inbound flight date
  ibdg               IN     CHAR,                                        -- inbound_boarding_point,
  outbound_flight_id IN     NUMBER,                                      -- flight pax is outgoing on
  inbound_fltid         OUT NUMBER,
  fltstime           IN     CHAR                       DEFAULT '0000',
  procname           IN     CHAR                       DEFAULT 'DUMMY',
  unitname           IN     CHAR                       DEFAULT 1,
  dcsid              IN     CHAR                       DEFAULT 'DUMMY',
  subunitname        IN     CHAR                       DEFAULT 0,
  bvis_subhead       IN     flight.bvis_subheader%TYPE DEFAULT NULL
)
IS
  --  <LocalVariable> <Datatype>;
  psiteid     CHAR;
  l_depsecs   CHAR(17);
  depcode     CHAR(3);
  l_fltstime  CHAR(4);

BEGIN

  inbound_fltid := 0;

  -- are there enough parameters to create the inbound flight
  IF (NVL(fltno,'-')   = '-' OR
      NVL(ibdg,'-')    = '-' OR
      NVL(fltdate,'-') = '-') THEN
    RETURN;
  END IF;

  IF (config.get_value('CREATE_INBOUND_BSM_FLIGHT') <> 'Y') THEN
    RETURN;
  END IF;

  -- Get the Airport Code of the Outbound Flight
  SELECT airport_code
  INTO   depcode
  FROM   flight f,
         site_info s
  WHERE  f.flight_id = outbound_flight_id
    AND  f.site_id = s.site_id;

  --  does the inbound flight already exist
  BEGIN

    SELECT f.flight_id
    INTO   inbound_fltid
    FROM   flight_inbound_v1 f,
           site_info si
    WHERE  f.flight_date   = fltdate
      AND  f.flight_no     = fltno
      AND  f.site_id       = si.site_id
      AND  si.airport_code = depcode;

  EXCEPTION
    WHEN no_data_found THEN
      inbound_fltid := 0;
  END;

  IF (inbound_fltid > 0) then
    RETURN;
  END IF;

  l_fltstime := LPAD(fltstime, 4, '0');
  l_depsecs := TO_CHAR(flt_date_to_date(fltdate)
                       + (TO_NUMBER(SUBSTR(l_fltstime, 1, 2)) * 60 + TO_NUMBER(SUBSTR(l_fltstime, 3, 2))) / 1440,
                      'DD-MON-YYYY HH24:MI');

  /* add flight */
  add_flight (
      fltno,
      fltdate,
      depcode,
      fltstime,
      'IB',          -- flight_status of IB sets flight type to I
      procname,      -- process_name
      unitname,      -- unit_name
      dcsid,         -- dcs_id
      subunitname,   -- subunit_name
      l_depsecs,
      NULL,          -- dcsfno
      inbound_fltid,
      bvis_subhead
  );

EXCEPTION
  WHEN OTHERS THEN
    add_app_msg.log_oracle_error('add_inbound_flight', SQLERRM, SQLCODE);

END add_inbound_flight;


  FUNCTION get_inbound_status(p_object_type       IN  VARCHAR2,
                              p_flight_id         IN  flight.flight_id%TYPE,
                              p_pax_id            IN  NUMBER           DEFAULT NULL,
                              p_bag_id            IN  NUMBER           DEFAULT NULL,
                              p_uld_no            IN  CHAR             DEFAULT NULL
                              ) RETURN VARCHAR2
  IS

    l_status VARCHAR2(11);

  BEGIN

    l_status := 'TERMINATING';

    IF (is_through(p_object_type, p_flight_id, p_pax_id, p_bag_id, p_uld_no) = TRUE) THEN

      l_status := 'THROUGH';

    ELSIF (is_transfer(p_object_type, p_flight_id, p_pax_id, p_bag_id, p_uld_no) = TRUE) THEN

      l_status := 'TRANSFER';

    END IF;

    RETURN l_status;

  END get_inbound_status;

END inbound_flight_utils;
/
