CREATE OR REPLACE PACKAGE BODY ulds_pkg AS

-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
PROCEDURE get_uld_info
   (p_uld_no       IN OUT uld_recorded_id_codes.uld_no%TYPE,
    p_uld_id_code  IN OUT uld_recorded_id_codes.uld_id_code%TYPE,
    p_user_id      IN     ulds.user_id%TYPE,
    p_flight_id       OUT ulds.flight_id%TYPE,
    p_flight_no       OUT flight.flight_no%TYPE,
    p_flight_date     OUT uld_recorded_id_codes.flight_date%TYPE,
    p_uld_category    OUT uld_recorded_id_codes.uld_category%TYPE,
    p_uld_type        OUT uld_recorded_id_codes.uld_type%TYPE,
    p_dest1           OUT uld_recorded_id_codes.dest1%TYPE,
    p_class1          OUT uld_recorded_id_codes.class1%TYPE,
    p_dest2           OUT uld_recorded_id_codes.dest2%TYPE,
    p_class2          OUT uld_recorded_id_codes.class2%TYPE,
    p_dest3           OUT uld_recorded_id_codes.dest3%TYPE,
    p_class3          OUT uld_recorded_id_codes.class3%TYPE,
    p_dest4           OUT uld_recorded_id_codes.dest4%TYPE,
    p_class4          OUT uld_recorded_id_codes.class4%TYPE,
    p_dest5           OUT uld_recorded_id_codes.dest5%TYPE,
    p_class5          OUT uld_recorded_id_codes.class5%TYPE,
    p_dest6           OUT uld_recorded_id_codes.dest6%TYPE,
    p_class6          OUT uld_recorded_id_codes.class6%TYPE,
    p_uld_status      OUT INTEGER,
    p_retcode         OUT INTEGER) IS

   -- Cursor to derive ULD destinations and classes
   CURSOR c_uld_dests(p_uld_dest_uld_no IN VARCHAR2, p_uld_dest_flight_id IN INTEGER) IS
      SELECT uld_destination, uld_class_list
         FROM uld_dests
            WHERE uld_no = p_uld_dest_uld_no AND flight_id = p_uld_dest_flight_id;

   l_access            INTEGER;                -- User has flight access

BEGIN

   -- Initialise the return code and ULD status
   p_retcode := 0;
   p_uld_status := 0;

   BEGIN
      -- Get details from the ULDS table
      SELECT
             uld_no,
             uld_id_code,
             flight_id,
             uld_category,
             uld_type
      INTO
             p_uld_no,
             p_uld_id_code,
             p_flight_id,
             p_uld_category,
             p_uld_type
      FROM
             ulds
      WHERE
             uld_no = p_uld_no OR
             uld_id_code = p_uld_id_code;

      -- The ULD has been registered (the exception was not reached),
      -- the ULD exists so update the ULD status
      p_uld_status := 2;

      IF p_uld_id_code IS NOT NULL THEN
         -- The ULD is also associated (it has a value in its uld_id_code
         -- column), update the status
         p_uld_status := p_uld_status + 1;
      END IF;

      -- Get the ULDS destinations
      FOR uld_dests_rec IN c_uld_dests(p_uld_no, p_flight_id) LOOP
         -- Set up ULD dests and classes
         IF p_dest1 IS NULL THEN
            p_dest1 := uld_dests_rec.uld_destination;
            p_class1 := uld_dests_rec.uld_class_list;
         ELSIF p_dest2 IS NULL THEN
            p_dest2 := uld_dests_rec.uld_destination;
            p_class2 := uld_dests_rec.uld_class_list;
         ELSIF p_dest3 IS NULL THEN
            p_dest3 := uld_dests_rec.uld_destination;
            p_class3 := uld_dests_rec.uld_class_list;
         ELSIF p_dest4 IS NULL THEN
            p_dest4 := uld_dests_rec.uld_destination;
            p_class4 := uld_dests_rec.uld_class_list;
         ELSIF p_dest5 IS NULL THEN
            p_dest5 := uld_dests_rec.uld_destination;
            p_class5 := uld_dests_rec.uld_class_list;
         ELSIF p_dest6 IS NULL THEN
            p_dest6 := uld_dests_rec.uld_destination;
            p_class6 := uld_dests_rec.uld_class_list;
         END IF; -- p_dest1 IS NULL
      END LOOP;


      BEGIN
         -- Get the flight details
         SELECT
                flight_no,
                flight_date
         INTO
                p_flight_no,
                p_flight_date
         FROM
                flight
         WHERE
                flight_id = p_flight_id;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            -- The flight row does not exist, so just ignore
            NULL;
      END;

   EXCEPTION
      -- Exception from the ULDS table select
      WHEN NO_DATA_FOUND THEN
         -- The ULD is not registered, so just ignore
         NULL;
   END;

   IF p_uld_status = 0 THEN
      -- The ULD is not registered, so get the ULD recorded details
      BEGIN
         -- Get the ULD info from the ULD_RECORDED_ID_CODES table
         SELECT uld_no,
                uld_id_code,
                flight_no,
                flight_date,
                uld_category,
                uld_type,
                dest1,
                dest2,
                dest3,
                dest4,
                dest5,
                dest6,
                class1,
                class2,
                class3,
                class4,
                class5,
                class6
         INTO p_uld_no,
              p_uld_id_code,
              p_flight_no,
              p_flight_date,
              p_uld_category,
              p_uld_type,
              p_dest1,
              p_dest2,
              p_dest3,
              p_dest4,
              p_dest5,
              p_dest6,
              p_class1,
              p_class2,
              p_class3,
              p_class4,
              p_class5,
              p_class6
         FROM
              uld_recorded_id_codes
         WHERE
              uld_id_code = p_uld_id_code;

         IF p_uld_no IS NOT NULL THEN
            -- The ULD card is also associated (it has a value in its uld_no
            -- column), set the status
            p_uld_status := 1;
         END IF;

      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            -- The record does not exist for the provided ULD_ID_CODE,
            -- return an error code (this code is required by the scanner)
            p_retcode := -1300;
      END;

   END IF; -- p_uld_status = 0

   -- Check that the user has the required access to the flight
   IF p_retcode = 0 AND p_uld_status > 1 AND
      ( p_flight_id IS NOT NULL OR
      ( p_flight_no IS NOT NULL AND p_flight_date IS NOT NULL ) )
   THEN
      -- There are sufficient details to check for flight access
      -- (there are no errors and we have flight details)

      -- Check whether the user has access to the flight
      user_authentication.validate_access (p_user_id, NVL(p_flight_id, '0'), p_flight_no, p_flight_date, l_access);

      IF l_access != 0 THEN
         -- The user does not have access to this flight, return the erronesous access code
         p_retcode := l_access;
      END IF;

   END IF; -- p_retcode != 0

END get_uld_info;

-----------------------------------------------------------------------------
-----------------------------------------------------------------------------

PROCEDURE get_ulds_details ( p_uld_no IN OUT  CHAR,
	                     fltid    IN OUT  NUMBER,
                             cur_0    IN OUT  cur_get_ulds_details_type
)
IS
BEGIN

   /*
    * - open cursor and select the uld_destination, uld_class_list and
    *   the uld_no_of_bags from the ulds and uld_dests tables where the
    *   two uld_no's are the same, the flight_id = fltid and the flight
    *   id's are the same.
    */

   /* select the uld_details */

   OPEN cur_0 FOR 
     SELECT
         d.uld_destination,
         NVL(d.uld_class_list, ' '),
         u.uld_no_of_bags,
         NVL(d.onward_flight_no, ' '),
         NVL(d.onward_flight_date, ' ')
     FROM 
         ulds u, uld_dests d
     WHERE
         u.uld_no    =  p_uld_no     AND
         u.flight_id =  fltid        AND
         u.uld_no    =  d.uld_no     AND
         u.flight_id =  d.flight_id
     ORDER BY 
         d.dest_order;

END get_ulds_details;

FUNCTION first_dest(p_flight_id  ulds.flight_id%TYPE,
                    p_uld_no     ulds.uld_no%TYPE)
RETURN uld_dests.uld_destination%TYPE IS

  l_retval  uld_dests.uld_destination%TYPE;

BEGIN

  BEGIN
    --Get 1st dest from uld_recorded_id_codes
    SELECT dest1 INTO l_retval
    FROM uld_recorded_id_codes uri, ulds u
    WHERE uri.uld_id_code = u.uld_id_code
    AND u.uld_no = p_uld_no
    AND u.flight_id = p_flight_id;

  EXCEPTION WHEN no_data_found THEN

    BEGIN

      --No ULD Card was created so try and find the uld_dest that corresponds to the
      --earliest matching flight_dest using dest_order.
      SELECT uld_destination INTO l_retval
      FROM (SELECT ud.uld_destination
            FROM flight_dests fd, uld_dests ud
            WHERE fd.flight_id = ud.flight_id
            AND fd.flight_destination = ud.uld_destination
            AND ud.uld_no = p_uld_no
            AND ud.flight_id = p_flight_id
            ORDER BY fd.dest_order)
      WHERE rownum = 1;

    EXCEPTION WHEN no_data_found THEN

      --No matching flight_dests.
      SELECT CAST(LTRIM(MIN(uld_destination)) AS CHAR(3)) INTO l_retval
      FROM uld_dests
      WHERE uld_no = p_uld_no
      AND flight_id = p_flight_id
      ORDER BY dest_order;

    END;

  END;

  RETURN l_retval;

END first_dest;

-----------------------------------------------------------------------------
-----------------------------------------------------------------------------

FUNCTION first_class(p_flight_id  ulds.flight_id%TYPE,
                     p_uld_no     ulds.uld_no%TYPE)
RETURN uld_dests.uld_class_list%TYPE IS

  l_retval  uld_dests.uld_class_list%TYPE;

BEGIN

  BEGIN
    --Get 1st class from uld_recorded_id_codes
    SELECT class1 INTO l_retval
    FROM uld_recorded_id_codes uri, ulds u
    WHERE uri.uld_id_code = u.uld_id_code
    AND u.uld_no = p_uld_no
    AND u.flight_id = p_flight_id;

  EXCEPTION WHEN no_data_found THEN

    BEGIN

      --No ULD Card was created so try and find the uld_dest that corresponds to the
      --earliest matching flight_dest using dest_order and get the class from that.
      SELECT uld_class_list INTO l_retval
      FROM (SELECT ud.uld_class_list
             FROM uld_dests ud
             WHERE ud.uld_no = p_uld_no
             AND ud.flight_id = p_flight_id
             ORDER BY ud.dest_order)
      WHERE rownum = 1;

    EXCEPTION WHEN no_data_found THEN

      --No matching flight_dests.
      SELECT uld_class_list INTO l_retval
      FROM uld_dests
      WHERE uld_no = p_uld_no
      AND flight_id = p_flight_id
      AND uld_destination = (SELECT LTRIM(MIN(uld_destination))
                             FROM uld_dests
                             WHERE uld_no = p_uld_no
                             AND flight_id = p_flight_id)
      ORDER by dest_order;

    END;

  END;

  RETURN l_retval;

END first_class;

-----------------------------------------------------------------------------
-----------------------------------------------------------------------------

FUNCTION first_onward_dest(p_flight_id  ulds.flight_id%TYPE,
                           p_uld_no     ulds.uld_no%TYPE)
RETURN uld_dests.uld_destination%TYPE IS

  l_retval  uld_dests.uld_destination%TYPE;

BEGIN

  --Get first uld_destination that doesn't have a corresponding flight_dests record.
  SELECT CAST(LTRIM(MIN(uld_destination)) AS CHAR(3)) INTO l_retval
  FROM (SELECT ud.uld_destination
       FROM uld_dests ud
       WHERE ud.flight_id = p_flight_id AND
       ud.uld_no = p_uld_no AND
       ud.uld_destination NOT IN  (SELECT fd.flight_destination FROM flight_dests fd
                               WHERE fd.flight_id = p_flight_id)
       ORDER BY ud.dest_order)
  WHERE rownum = 1;

  RETURN l_retval;


END first_onward_dest;

PROCEDURE close_uld
(      p_flight_id   IN  number
     , p_uld_no      IN  char
     , p_loc         IN  char
     , p_volume      IN  number
     , p_user_id     IN  tagtrak_users.user_id%TYPE
     , p_retcode       OUT number
) IS

  l_found_uld               BOOLEAN := FALSE;
  l_nob                     ulds.uld_no_of_bags%TYPE;
  l_trace_time              char(5);
  l_config_value            carrier_site_config.config_value%TYPE;

BEGIN
  p_retcode := 0;
  
   BEGIN
  
    SELECT c.config_value INTO l_config_value
    FROM carrier_site_config c, flight f
    WHERE f.flight_id = p_flight_id 
    AND c.alpha_code = f.carrier_alpha_code 
    AND f.site_id = c.site_id 
    AND c.config_item = 'ULD_VOLUME_ON_CLOSE';
   EXCEPTION
    WHEN no_data_found THEN
      NULL;
   END;

   IF l_config_value = 'Y' AND nvl(p_volume, -1) = -1 THEN
    p_retcode := -20800;
     RETURN;
   END IF;

  BEGIN
    SELECT uld_no_of_bags INTO l_nob
    FROM ulds 
    WHERE uld_no = p_uld_no
    AND flight_id = p_flight_id
    AND pd_marker = 'N';

    l_found_uld := TRUE;
   
  EXCEPTION
     WHEN no_data_found THEN

      l_found_uld := FALSE;
  END;

   -- Can close ULD if it contains > 0 bags, or if the system parameter
   -- CAN_CLOSE_EMPTY_ULDS is set to 'Y'
  IF l_found_uld THEN
    IF l_nob = 0 AND nvl(rtrim(config.get_value('CAN_CLOSE_EMPTY_ULDS')), 'N') != 'Y' THEN
      p_retcode := -220;
      RETURN;
    END IF;
  END IF;

  UPDATE ulds
  SET close_flag = 'Y',
      volume_code = p_volume
  WHERE uld_no = p_uld_no
  AND flight_id = p_flight_id
  AND pd_marker = 'N';

  IF (SQL%FOUND) THEN
    p_retcode := 0;
    
    add_uld_trace_record(p_uld_no, 
                         p_flight_id, 
                         p_user_id, 
                         '0', 
                         p_loc, 
                         l_trace_time, 
                         'CLOSED', 
                         'Y');
  END IF;
END close_uld;

PROCEDURE reopen_uld
(      p_flight_id   IN  number
     , p_uld_no      IN  char
     , p_loc         IN  char
     , p_user_id     IN  tagtrak_users.user_id%TYPE
     , retcode       OUT number
) IS

 flst           flight.flight_status%type;
 trace_time     char(5);
 l_uld_no       ulds.uld_no%TYPE := p_uld_no;

BEGIN
   select flight_status
   into flst
   from flight
   where flight_id = p_flight_id;

   IF (flst IN ('FC','PD','FF')) THEN
      retcode := -1081;
      return;
   END IF;

   update ulds
   set close_flag = 'N'
   where uld_no   = p_uld_no
   and flight_id  = p_flight_id
   and pd_marker  = 'N';

   IF (SQL%FOUND) THEN
      BEGIN
         retcode := 0;

         add_uld_trace_record(l_uld_no, p_flight_id, p_user_id, '0', p_loc, trace_time, 'OPENED', 'Y');
         
      END;
   END IF;
END reopen_uld;

FUNCTION get_onward_uld_dest
  (p_uld_no IN ulds.uld_no%TYPE
  ,p_flight_id IN flight.flight_id%TYPE
  ,p_dest_order IN uld_dests.dest_order%TYPE) RETURN VARCHAR2 IS

  -- Cursor will return all destinations for ULD, correctly ordered.
  CURSOR c_uld_dests(p_uld_no ulds.uld_no%TYPE, p_flight_id flight.flight_id%TYPE) IS
    SELECT uld_destination, dest_order
    FROM uld_dests
    WHERE uld_no = p_uld_no
    AND flight_id = p_flight_id
    ORDER BY dest_order;

  -- Variable to hold the separator character if returning all destinations concatenated.
  l_separator CHAR(3) DEFAULT NULL;

  -- The destion specified or the list of all destinations for the ULD t be stored here.
  l_uld_destination VARCHAR2(30) DEFAULT NULL;
  BEGIN

    -- Return the ULD destination using the order specified.
    IF p_dest_order > 0 THEN
       FOR dest_rec IN c_uld_dests(p_uld_no, p_flight_id) LOOP
         IF dest_rec.dest_order = p_dest_order THEN
            l_uld_destination := dest_rec.uld_destination;
         END IF;  
       END LOOP;
    -- If the order passed is 0, then concatenate the destinations. This is used to display
    -- all destinations on the ULD card report from the MWS.
    ELSIF p_dest_order = 0 THEN
       FOR dest_rec IN c_uld_dests(p_uld_no, p_flight_id) LOOP
         l_uld_destination := l_uld_destination || l_separator || dest_rec.uld_destination;
         l_separator := ' / ';
       END LOOP;
    END IF;
    
    -- Return the destination(s)
    RETURN l_uld_destination;

  END get_onward_uld_dest;
  
FUNCTION get_onward_uld_flight_no
  (p_uld_no IN ulds.uld_no%TYPE
  ,p_flight_id IN flight.flight_id%TYPE
  ,p_dest_order IN uld_dests.dest_order%TYPE) RETURN VARCHAR2 IS
  
  -- Variable to store the onward flight number specified.
  l_flight_no uld_dests.onward_flight_no%type;
  
  BEGIN
    BEGIN
     
       -- Select the flight number from the list of flight numbers for the specified ULD.
       SELECT onward_flight_no
       INTO l_flight_no 
       FROM uld_dests
       WHERE uld_no = p_uld_no
       AND flight_id = p_flight_id
       AND dest_order = p_dest_order;
     
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
             l_flight_no := NULL;    
     END;
    
    -- Return the flight number.
    RETURN l_flight_no;

  END get_onward_uld_flight_no;

END ulds_pkg;
/
