PROMPT CREATE OR REPLACE PACKAGE BODY uld_cards

CREATE OR REPLACE PACKAGE BODY uld_cards AS

/******************************************************************************
 *
 * Private Procedures and Functions
 *
 ******************************************************************************/

/******************************************************************************
 *
 * PROCEDURE VALIDATE_DATA
 *
 * Description:
 *   This procedure checks that the template name and the agent are set.
 *
 * Parameters:
 *   p_operation     - Operation name that is being attempted (e.g. save).
 *   p_template_name - Name given to the ULD card template.
 *   p_agent         - Name of the agent that owns the ULD card template.
 *
 * Errors raised:
 *   -20000 : Template name and agent must be set when attempting to
 *            <operation> a template.
 *
 ******************************************************************************/

PROCEDURE validate_data
   (p_operation     IN VARCHAR2,
    p_template_name IN uld_card_templates.template_name%TYPE,
    p_agent         IN uld_card_templates.agent%TYPE) IS

BEGIN

   -- Check that a template name and an agent are provided
   IF p_template_name IS NULL OR p_agent IS NULL THEN
      -- Template and agent must be set - raise an error
      RAISE_APPLICATION_ERROR(-20000,
         'Template name and agent must be set when attempting to ' || p_operation ||
         ' (template, agent) | ' ||
         NVL(p_template_name, '<none>') || ', ' || NVL(p_agent, '<none>'));
   END IF;

END validate_data;

/******************************************************************************
 *
 * PROCEDURE UPDATE_ULD_TEMPLATE
 *
 * Description:
 *   This procedure updates the TIMESTAMP in the ULD_CARD_TEMPLATES table to
 *   prevent the row from being archived.
 *
 * Parameters:
 *   p_template_name - Name given to the ULD card template.
 *   p_agent         - Name of the agent that owns the ULD card template.
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

PROCEDURE update_uld_template
   (p_template_name IN uld_card_templates.template_name%TYPE,
    p_agent         IN uld_card_templates.agent%TYPE) IS

BEGIN

   -- Perform the TIMESTAMP update
   UPDATE uld_card_templates
      SET timestamp = SYSDATE
         WHERE template_name = p_template_name AND agent = p_agent;

   -- Save the transaction
   -- COMMIT;

END update_uld_template;

/******************************************************************************
 *
 * FUNCTION GET_ULD_ID_CODE_SEQ
 *
 * Description:
 *   This function gets and returns the ULD ID code.
 *
 * Parameters:
 *   p_departing_airport - Departing airport of the card.
 *
 * Return Value:
 *   The returned get_uld_id_code_seq value.
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

FUNCTION get_uld_id_code_seq
   (p_departing_airport IN uld_recorded_id_codes.dest1%TYPE)
      RETURN VARCHAR2 IS

   l_uld_id_seq VARCHAR2(7); -- ULD ID code sequence number

BEGIN

   -- Get the next ULD ID CODE sequence number
   select TO_CHAR(uld_id_code_seq.nextval) INTO l_uld_id_seq from dual;

   -- Return the code
   RETURN SUBSTR(l_uld_id_seq, 1, 4) ||
          NVL(p_departing_airport, 'ULT') ||
          SUBSTR(l_uld_id_seq, 5, 3);

END get_uld_id_code_seq;

/******************************************************************************
 *
 * FUNCTION GET_ULD_TYPE
 *
 * Description:
 *   This function returns the ULD type given a ULD category. The ULD type is
 *   determined by the following rules:
 *
 *     - If p_uld_category contains a 'H', 'T' or 'Z', the ULD type is
 *       (T)ransfer.
 *     - If p_uld_category contains an 'S' the ULD type is (M)ixed.
 *     - Otherwise the ULD type is (L)ocal.
 *
 *  Note
 *    - Parameter p_uld_category must be set.
 *    - The derived ULD type cannot be both a transfer and a mixed type.
 *
 * Parameters:
 *   p_uld_category - IATA ULD load code.
 *
 * Return Value:
 *   The derived ULD type ('L', 'T' or 'M').
 *
 * Errors raised:
 *   -20000 : ULD category must be set.
 *   -20000 : ULD category cannot include a mixed and a transfer type.
 *
 ******************************************************************************/

FUNCTION get_uld_type
   (p_uld_category IN uld_card_details.uld_category%TYPE)
      RETURN uld_recorded_id_codes.uld_type%TYPE IS

   l_uld_type uld_card_details.uld_type%TYPE; -- Derived ULD type to return

   l_uld_code VARCHAR2(1);           -- An individual ULD category code
   l_transfer BOOLEAN      := FALSE; -- Transfer container indicator
   l_mixed    BOOLEAN      := FALSE; -- Mixed container indicator

BEGIN

   IF p_uld_category IS NULL THEN
      -- ULD category is not set - raise an error
      RAISE_APPLICATION_ERROR(-20000, 'ULD category must be set');
   END IF;

   FOR i IN 1..LENGTH(p_uld_category) LOOP
      -- Check each individual ULD category (ignoring local types or unknowns)
      l_uld_code := SUBSTR(p_uld_category, i, 1);

      IF l_uld_code IN ('H', 'T', 'Z') THEN
         -- This individual category equates to a transfer type
         l_transfer := TRUE;
      ELSIF l_uld_code = 'S' THEN
         -- This individual category equates to a mxied type
         l_mixed := TRUE;
      ELSIF l_uld_code NOT IN ('B', 'C', 'D', 'E', 'F') THEN
         -- ULD load code is not defined
         RAISE_APPLICATION_ERROR(-20000,
            'Unknown ULD category (category) | ' ||
            l_uld_code);
      END IF; -- l_uld_code IN ('H', 'T', 'Z')

   END LOOP; -- FOR i IN 1..LENGTH(p_uld_category)

   -- Validate and set up the ULD Type
   IF l_transfer AND l_mixed THEN
      -- Raise an error if the code contains a transfer and a mixed type
      RAISE_APPLICATION_ERROR(-20000,
         'ULD category cannot include a mixed and a transfer type (category) | ' ||
         p_uld_category);
   ELSIF l_transfer THEN
         -- The ULD type is a transfer container
         l_uld_type := 'T';
   ELSIF l_mixed THEN
         -- The ULD type is a mixed container
         l_uld_type := 'M';
   ELSE
         -- The ULD type is a local container
         l_uld_type := 'L';
   END IF;

   -- Return the derived ULD type
   RETURN l_uld_type;

END get_uld_type;


/******************************************************************************
 *
 * Public Procedures and Functions
 *
 ******************************************************************************/

/******************************************************************************
 *
 * FUNCTION VALIDATE_ULDNO
 *
 * Description:
 *   This function validate the uld no format.
 *
 * Parameters:
 *   uld no -
 *
 * Return Value:
 *    BOOLEAN
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/
FUNCTION validate_uldno
   (
     p_uldno IN ulds.uld_no%type
    )RETURN BOOLEAN IS

   len1      CONSTANT  NUMBER(10)  := 10;
   len2      CONSTANT  NUMBER(9)   := 9;
   mark      CONSTANT  NUMBER      := 1;
   uld_len             NUMBER(10)  := 0;

BEGIN
     --Store uld length
     uld_len := length(rtrim(p_uldno));


     --Check if lenght is within permissible length
     IF(uld_len NOT IN( len1,len2)) THEN
       RETURN  FALSE;
     END IF;
     dbms_output.put_line('PASS VALIDATION 1');


     --Validate that first character in uld no is alpha
     IF( ascii(substr(p_uldno,1,1)) NOT between 65 and 90 ) THEN
       RETURN FALSE;
     END IF;
     dbms_output.put_line('PASS VALIDATION 2');


     --Validate second and third character to be alpha or numeric
     IF(ascii(substr(p_uldno,2,1))  NOT between 48 and 57) THEN
       IF (ascii(substr(p_uldno,2,1))  NOT between 65 and 90 ) THEN
         RETURN FALSE;
       END IF;
     END IF;
     IF(ascii(substr(p_uldno,3,1))  NOT between 48 and 57) THEN
       IF (ascii(substr(p_uldno,3,1))  NOT between 65 and 90 ) THEN
         RETURN FALSE;
       END IF;
     END IF;
     dbms_output.put_line('PASS VALIDATION 3');


     --Validate that fourth to seventh character are numeric
     FOR i IN 4..7 LOOP
         IF ascii(substr(p_uldno,i,mark)) NOT between 48 and 57 THEN
            RETURN FALSE;
         END IF;
     END LOOP;
     dbms_output.put_line('PASS VALIDATION 4');


     --Validate that the eighth and nineth character are alpha or numeric when uld length is 9
     IF (uld_len = len2) THEN
        dbms_output.put_line('IN VALIDATION 5.1');
        IF(ascii(substr(p_uldno,8,1))  NOT between 48 and 57) THEN
          IF (ascii(substr(p_uldno,8,1))  NOT between 65 and 90 ) THEN
             RETURN FALSE;
          END IF;
        END IF;
        IF(ascii(substr(p_uldno,9,1))  NOT between 48 and 57) THEN
          IF (ascii(substr(p_uldno,9,1))  NOT between 65 and 90 ) THEN
              RETURN FALSE;
          END IF;
        END IF;
        dbms_output.put_line('PASS VALIDATION 5.1');

     ELSIF (uld_len = len1)  THEN
       dbms_output.put_line('IN VALIDATION 5.2');
       IF ( ascii(substr(p_uldno,8,1))  between 65 and 90  AND ascii(substr(p_uldno,10,1)) between 48 and 57 )THEN
           RETURN FALSE;
       END IF;
       IF ( ascii(substr(p_uldno,9,1)) NOT between 48 and 57 ) THEN
           IF( ascii(substr(p_uldno,9,1))  NOT between 65 and 90 ) THEN
              RETURN FALSE;
           END IF;
       END IF;
       IF ( ascii(substr(p_uldno,10,1)) NOT between 48 and 57 ) THEN
           IF( ascii(substr(p_uldno,10,1))  NOT between 65 and 90 ) THEN
              RETURN FALSE;
           END IF;
       END IF;
       dbms_output.put_line('PASS VALIDATION 5.2');
     END IF;

     RETURN TRUE;
END validate_uldno;

/******************************************************************************
 *
 * PROCEDURE RECALL_ULD_CARD_TEMPLATE
 *
 ******************************************************************************/

PROCEDURE recall_uld_card_template
   (p_template_name      IN  uld_card_templates.template_name%TYPE,
    p_agent              IN  uld_card_templates.agent%TYPE,
    p_flight_no          OUT uld_card_templates.flight_no%TYPE,
    p_flight_date        OUT uld_card_templates.flight_date%TYPE,
    p_bar_codes          OUT uld_card_templates.bar_codes%TYPE) IS

BEGIN

   BEGIN
      -- Get the required details from the database
      SELECT flight_no,
             flight_date,
             bar_codes
      INTO
             p_flight_no,
             p_flight_date,
             p_bar_codes
      FROM
             uld_card_templates
      WHERE
             template_name = p_template_name AND agent = p_agent;
   EXCEPTION
      WHEN no_data_found THEN
         -- Template does not exist - raise an error
         RAISE_APPLICATION_ERROR(-20000,
            'Recalled template does not exist (template, agent) | ' ||
            NVL(p_template_name, '<none>') || ', ' || NVL(p_agent, '<none>'));
   END;

END recall_uld_card_template;

/******************************************************************************
 *
 * PROCEDURE RECALL_ULD_CARD_DETAILS
 *
 ******************************************************************************/

PROCEDURE recall_uld_card_details
   (p_template_name      IN  uld_card_templates.template_name%TYPE,
    p_agent              IN  uld_card_templates.agent%TYPE,
    p_uld_category       OUT uld_category_type,
    p_uld_type           OUT uld_type_type,
    p_quantity           OUT quantity_type,
    p_dest1              OUT dest1_type,
    p_class1             OUT class1_type,
    p_dest2              OUT dest2_type,
    p_class2             OUT class2_type,
    p_dest3              OUT dest3_type,
    p_class3             OUT class3_type,
    p_dest4              OUT dest4_type,
    p_class4             OUT class4_type,
    p_dest5              OUT dest5_type,
    p_class5             OUT class5_type,
    p_dest6              OUT dest6_type,
    p_class6             OUT class6_type,
    p_loaded_at          OUT loaded_at_type,
    p_transfer_at        OUT transfer_at_type,
    p_card_id            OUT card_id_type,
    p_contents_remarks   OUT contents_remarks_type,
    p_remarks            OUT remarks_type,
    p_onward_flight_no1   OUT onward_flight_no1_type,
    p_onward_flight_date1 OUT onward_flight_date1_type,
    p_onward_flight_no2   OUT onward_flight_no2_type,
    p_onward_flight_date2 OUT onward_flight_date2_type,
    p_onward_flight_no3   OUT onward_flight_no3_type,
    p_onward_flight_date3 OUT onward_flight_date3_type,
    p_onward_flight_no4   OUT onward_flight_no4_type,
    p_onward_flight_date4 OUT onward_flight_date4_type,
    p_onward_flight_no5   OUT onward_flight_no5_type,
    p_onward_flight_date5 OUT onward_flight_date5_type,
    p_onward_flight_no6   OUT onward_flight_no6_type,
    p_onward_flight_date6 OUT onward_flight_date6_type) IS

   -- Cursor to select rows from ULD_CARD_DETAILS table
   CURSOR c_uld_card_details IS
      SELECT *
         FROM uld_card_details
            WHERE template_name = p_template_name AND agent = p_agent
               ORDER BY card_id;

   l_counter     BINARY_INTEGER;  -- Record set count variable

BEGIN

   l_counter := 0; -- Initialise the counter

   FOR uld_card_details_rec IN c_uld_card_details LOOP
      -- Scroll through all uld_card_details for the template and agent

      -- Increment the counter for this pass
      l_counter := l_counter + 1;

      -- Store the recurring ULD details
      p_card_id(l_counter) := uld_card_details_rec.card_id;
      p_dest1(l_counter) := uld_card_details_rec.dest1;
      p_dest2(l_counter) := uld_card_details_rec.dest2;
      p_dest3(l_counter) := uld_card_details_rec.dest3;
      p_dest4(l_counter) := uld_card_details_rec.dest4;
      p_dest5(l_counter) := uld_card_details_rec.dest5;
      p_dest6(l_counter) := uld_card_details_rec.dest6;
      p_class1(l_counter) := uld_card_details_rec.class1;
      p_class2(l_counter) := uld_card_details_rec.class2;
      p_class3(l_counter) := uld_card_details_rec.class3;
      p_class4(l_counter) := uld_card_details_rec.class4;
      p_class5(l_counter) := uld_card_details_rec.class5;
      p_class6(l_counter) := uld_card_details_rec.class6;
      p_loaded_at(l_counter) := uld_card_details_rec.loaded_at;
      p_transfer_at(l_counter) := uld_card_details_rec.transfer_at;
      p_uld_category(l_counter) := uld_card_details_rec.uld_category;
      p_uld_type(l_counter) := uld_card_details_rec.uld_type;
      p_onward_flight_no1(l_counter)   := uld_card_details_rec.onward_flight_no1;
      p_onward_flight_date1(l_counter) := uld_card_details_rec.onward_flight_date1;
      p_onward_flight_no2(l_counter)   := uld_card_details_rec.onward_flight_no2;
      p_onward_flight_date2(l_counter) := uld_card_details_rec.onward_flight_date2;
      p_onward_flight_no3(l_counter)   := uld_card_details_rec.onward_flight_no3;
      p_onward_flight_date3(l_counter) := uld_card_details_rec.onward_flight_date3;
      p_onward_flight_no4(l_counter)   := uld_card_details_rec.onward_flight_no4;
      p_onward_flight_date4(l_counter) := uld_card_details_rec.onward_flight_date4;
      p_onward_flight_no5(l_counter)   := uld_card_details_rec.onward_flight_no5;
      p_onward_flight_date5(l_counter) := uld_card_details_rec.onward_flight_date5;
      p_onward_flight_no6(l_counter)   := uld_card_details_rec.onward_flight_no6;
      p_onward_flight_date6(l_counter) := uld_card_details_rec.onward_flight_date6;
      p_contents_remarks(l_counter) := uld_card_details_rec.contents_remarks;
      p_remarks(l_counter) := uld_card_details_rec.remarks;
      p_quantity(l_counter) := uld_card_details_rec.quantity;

   END LOOP;

END recall_uld_card_details;

/******************************************************************************
 *
 * PROCEDURE SAVE_ULD_CARD_TEMPLATE
 *
 ******************************************************************************/

PROCEDURE save_uld_card_template
   (p_template_name      IN uld_card_templates.template_name%TYPE,
    p_agent              IN uld_card_templates.agent%TYPE,
    p_flight_no          IN uld_card_templates.flight_no%TYPE,
    p_flight_date        IN uld_card_templates.flight_date%TYPE,
    p_bar_codes          IN uld_card_templates.bar_codes%TYPE) IS

BEGIN

   -- Check template and agent are set
   validate_data('save a template', p_template_name, p_agent);

   -- Attempt to update the uld_card_templates table
   UPDATE
          uld_card_templates
   SET
          timestamp = SYSDATE,
          flight_no = p_flight_no,
          flight_date = p_flight_date,
          bar_codes = p_bar_codes
   WHERE
          template_name = trim(p_template_name)
   AND
          agent = p_agent;

   IF SQL%ROWCOUNT = 0 THEN
      -- The update did not work (rows does not exist), perform an insert

      INSERT INTO uld_card_templates
         (template_name,
          agent,
          timestamp,
          flight_no,
          flight_date,
          bar_codes)
      VALUES
         (trim(p_template_name),
          p_agent,
          SYSDATE,
          p_flight_no,
          p_flight_date,
          p_bar_codes);

   END IF; -- SQL%ROWCOUNT = 0

END save_uld_card_template;

/******************************************************************************
 *
 * PROCEDURE SAVE_ULD_CARD_DETAILS
 *
 ******************************************************************************/

PROCEDURE save_uld_card_details
   (p_template_name      IN uld_card_details.template_name%TYPE,
    p_agent              IN uld_card_details.agent%TYPE,
    p_uld_category       IN uld_card_details.uld_category%TYPE,
    p_uld_type           IN uld_card_details.uld_type%TYPE,
    p_dest1              IN uld_card_details.dest1%TYPE,
    p_class1             IN uld_card_details.class1%TYPE,
    p_dest2              IN uld_card_details.dest2%TYPE,
    p_class2             IN uld_card_details.class2%TYPE,
    p_dest3              IN uld_card_details.dest3%TYPE,
    p_class3             IN uld_card_details.class3%TYPE,
    p_dest4              IN uld_card_details.dest4%TYPE,
    p_class4             IN uld_card_details.class4%TYPE,
    p_dest5              IN uld_card_details.dest5%TYPE,
    p_class5             IN uld_card_details.class5%TYPE,
    p_dest6              IN uld_card_details.dest6%TYPE,
    p_class6             IN uld_card_details.class6%TYPE,
    p_quantity           IN uld_card_details.quantity%TYPE,
    p_loaded_at          IN uld_card_details.loaded_at%TYPE,
    p_transfer_at        IN uld_card_details.transfer_at%TYPE,
    p_card_id            IN uld_card_details.card_id%TYPE,
    p_contents_remarks   IN uld_card_details.contents_remarks%TYPE,
    p_remarks            IN uld_card_details.remarks%TYPE,
    p_onward_flight_no1   IN uld_card_details.onward_flight_no1%TYPE,
    p_onward_flight_date1 IN uld_card_details.onward_flight_date1%TYPE,
    p_onward_flight_no2   IN uld_card_details.onward_flight_no2%TYPE,
    p_onward_flight_date2 IN uld_card_details.onward_flight_date2%TYPE,
    p_onward_flight_no3   IN uld_card_details.onward_flight_no3%TYPE,
    p_onward_flight_date3 IN uld_card_details.onward_flight_date3%TYPE,
    p_onward_flight_no4   IN uld_card_details.onward_flight_no4%TYPE,
    p_onward_flight_date4 IN uld_card_details.onward_flight_date4%TYPE,
    p_onward_flight_no5   IN uld_card_details.onward_flight_no5%TYPE,
    p_onward_flight_date5 IN uld_card_details.onward_flight_date5%TYPE,
    p_onward_flight_no6   IN uld_card_details.onward_flight_no6%TYPE,
    p_onward_flight_date6 IN uld_card_details.onward_flight_date6%TYPE
) IS

   l_uld_type uld_recorded_id_codes.uld_type%TYPE; -- Derived ULD type

BEGIN

   -- Check template and agent are set
   validate_data('save a template', p_template_name, p_agent);

   -- Derive the ULD type (if not set)
   IF p_template_name != 'BLANK' THEN
      l_uld_type := NVL( p_uld_type, get_uld_type( p_uld_category ) );
   ELSE
      l_uld_type := p_uld_type;
   END IF;


   -- Attempt to update the uld_card_details table
   UPDATE
      uld_card_details
   SET
      dest1 = p_dest1,
      dest2 = p_dest2,
      dest3 = p_dest3,
      dest4 = p_dest4,
      dest5 = p_dest5,
      dest6 = p_dest6,
      class1 = p_class1,
      class2 = p_class2,
      class3 = p_class3,
      class4 = p_class4,
      class5 = p_class5,
      class6 = p_class6,
      onward_flight_no1   = p_onward_flight_no1,
      onward_flight_date1 = p_onward_flight_date1,
      onward_flight_no2   = p_onward_flight_no2,
      onward_flight_date2 = p_onward_flight_date2,
      onward_flight_no3   = p_onward_flight_no3,
      onward_flight_date3 = p_onward_flight_date3,
      onward_flight_no4   = p_onward_flight_no4,
      onward_flight_date4 = p_onward_flight_date4,
      onward_flight_no5   = p_onward_flight_no5,
      onward_flight_date5 = p_onward_flight_date5,
      onward_flight_no6   = p_onward_flight_no6,
      onward_flight_date6 = p_onward_flight_date6,
      loaded_at = p_loaded_at,
      transfer_at = p_transfer_at,
      uld_category = p_uld_category,
      uld_type = l_uld_type,
      contents_remarks = p_contents_remarks,
      remarks = p_remarks,
      quantity = p_quantity
   WHERE
      template_name = p_template_name
   AND
      agent = p_agent
   AND
      card_id = p_card_id;

   IF SQL%ROWCOUNT = 0 THEN
      -- The update did not work (rows does not exist), perform an insert

      INSERT INTO uld_card_details
         (template_name,
          agent,
          card_id,
          dest1,
          dest2,
          dest3,
          dest4,
          dest5,
          dest6,
          class1,
          class2,
          class3,
          class4,
          class5,
          class6,
          loaded_at,
          transfer_at,
          uld_category,
          uld_type,
          onward_flight_no1,
          onward_flight_date1,
          onward_flight_no2,
          onward_flight_date2,
          onward_flight_no3,
          onward_flight_date3,
          onward_flight_no4,
          onward_flight_date4,
          onward_flight_no5,
          onward_flight_date5,
          onward_flight_no6,
          onward_flight_date6,
          contents_remarks,
          remarks,
          quantity)
      VALUES
         (p_template_name,
          p_agent,
          p_card_id,
          p_dest1,
          p_dest2,
          p_dest3,
          p_dest4,
          p_dest5,
          p_dest6,
          p_class1,
          p_class2,
          p_class3,
          p_class4,
          p_class5,
          p_class6,
          p_loaded_at,
          p_transfer_at,
          p_uld_category,
          l_uld_type,
          p_onward_flight_no1,
          p_onward_flight_date1,
          p_onward_flight_no2,
          p_onward_flight_date2,
          p_onward_flight_no3,
          p_onward_flight_date3,
          p_onward_flight_no4,
          p_onward_flight_date4,
          p_onward_flight_no5,
          p_onward_flight_date5,
          p_onward_flight_no6,
          p_onward_flight_date6,
          p_contents_remarks,
          p_remarks,
          p_quantity);

   END IF; -- SQL%ROWCOUNT = 0

   -- Save the transaction
   COMMIT;

END save_uld_card_details;


/******************************************************************************
 *
 * PROCEDURE SAVE_ULD_CARD_DETAILS_2
 *
 ******************************************************************************/

PROCEDURE save_uld_card_details_2
   (p_template_name      IN uld_card_details.template_name%TYPE,
    p_agent              IN uld_card_details.agent%TYPE,
    p_uld_category       IN uld_card_details.uld_category%TYPE,
    p_uld_type           IN uld_card_details.uld_type%TYPE,
    p_dest1              IN uld_card_details.dest1%TYPE,
    p_class1             IN uld_card_details.class1%TYPE,
    p_dest2              IN uld_card_details.dest2%TYPE,
    p_class2             IN uld_card_details.class2%TYPE,
    p_dest3              IN uld_card_details.dest3%TYPE,
    p_class3             IN uld_card_details.class3%TYPE,
    p_dest4              IN uld_card_details.dest4%TYPE,
    p_class4             IN uld_card_details.class4%TYPE,
    p_dest5              IN uld_card_details.dest5%TYPE,
    p_class5             IN uld_card_details.class5%TYPE,
    p_dest6              IN uld_card_details.dest6%TYPE,
    p_class6             IN uld_card_details.class6%TYPE,
    p_quantity           IN uld_card_details.quantity%TYPE,
    p_loaded_at          IN uld_card_details.loaded_at%TYPE,
    p_transfer_at        IN uld_card_details.transfer_at%TYPE,
    p_card_id            IN uld_card_details.card_id%TYPE,
    p_contents_remarks   IN uld_card_details.contents_remarks%TYPE,
    p_remarks            IN uld_card_details.remarks%TYPE,
    p_onward_flight_no1   IN uld_card_details.onward_flight_no1%TYPE,
    p_onward_flight_date1 IN uld_card_details.onward_flight_date1%TYPE,
    p_onward_flight_no2   IN uld_card_details.onward_flight_no2%TYPE,
    p_onward_flight_date2 IN uld_card_details.onward_flight_date2%TYPE,
    p_onward_flight_no3   IN uld_card_details.onward_flight_no3%TYPE,
    p_onward_flight_date3 IN uld_card_details.onward_flight_date3%TYPE,
    p_onward_flight_no4   IN uld_card_details.onward_flight_no4%TYPE,
    p_onward_flight_date4 IN uld_card_details.onward_flight_date4%TYPE,
    p_onward_flight_no5   IN uld_card_details.onward_flight_no5%TYPE,
    p_onward_flight_date5 IN uld_card_details.onward_flight_date5%TYPE,
    p_onward_flight_no6   IN uld_card_details.onward_flight_no6%TYPE,
    p_onward_flight_date6 IN uld_card_details.onward_flight_date6%TYPE,
    p_date_offset_1       IN uld_card_details.date_offset_1%TYPE,
    p_date_offset_2       IN uld_card_details.date_offset_2%TYPE,
    p_date_offset_3       IN uld_card_details.date_offset_3%TYPE,
    p_date_offset_4       IN uld_card_details.date_offset_4%TYPE,
    p_date_offset_5       IN uld_card_details.date_offset_5%TYPE,
    p_date_offset_6       IN uld_card_details.date_offset_6%TYPE
) IS

   l_uld_type uld_recorded_id_codes.uld_type%TYPE; -- Derived ULD type

BEGIN

   -- Check template and agent are set
   validate_data('save a template', p_template_name, p_agent);

   -- Derive the ULD type (if not set)
   IF p_template_name != 'BLANK' THEN
      l_uld_type := NVL( p_uld_type, get_uld_type( p_uld_category ) );
   ELSE
      l_uld_type := p_uld_type;
   END IF;


   -- Attempt to update the uld_card_details table
   UPDATE
      uld_card_details
   SET
      dest1 = p_dest1,
      dest2 = p_dest2,
      dest3 = p_dest3,
      dest4 = p_dest4,
      dest5 = p_dest5,
      dest6 = p_dest6,
      class1 = p_class1,
      class2 = p_class2,
      class3 = p_class3,
      class4 = p_class4,
      class5 = p_class5,
      class6 = p_class6,
      onward_flight_no1   = p_onward_flight_no1,
      onward_flight_date1 = p_onward_flight_date1,
      onward_flight_no2   = p_onward_flight_no2,
      onward_flight_date2 = p_onward_flight_date2,
      onward_flight_no3   = p_onward_flight_no3,
      onward_flight_date3 = p_onward_flight_date3,
      onward_flight_no4   = p_onward_flight_no4,
      onward_flight_date4 = p_onward_flight_date4,
      onward_flight_no5   = p_onward_flight_no5,
      onward_flight_date5 = p_onward_flight_date5,
      onward_flight_no6   = p_onward_flight_no6,
      onward_flight_date6 = p_onward_flight_date6,
      loaded_at = p_loaded_at,
      transfer_at = p_transfer_at,
      uld_category = p_uld_category,
      uld_type = l_uld_type,
      contents_remarks = p_contents_remarks,
      remarks = p_remarks,
      quantity = p_quantity,
      date_offset_1 = p_date_offset_1,
      date_offset_2 = p_date_offset_2,
      date_offset_3 = p_date_offset_3,
      date_offset_4 = p_date_offset_4,
      date_offset_5 = p_date_offset_5,
      date_offset_6 = p_date_offset_6
   WHERE
      template_name = p_template_name
   AND
      agent = p_agent
   AND
      card_id = p_card_id;

   IF SQL%ROWCOUNT = 0 THEN
      -- The update did not work (rows does not exist), perform an insert

      INSERT INTO uld_card_details
         (template_name,
          agent,
          card_id,
          dest1,
          dest2,
          dest3,
          dest4,
          dest5,
          dest6,
          class1,
          class2,
          class3,
          class4,
          class5,
          class6,
          loaded_at,
          transfer_at,
          uld_category,
          uld_type,
          onward_flight_no1,
          onward_flight_date1,
          onward_flight_no2,
          onward_flight_date2,
          onward_flight_no3,
          onward_flight_date3,
          onward_flight_no4,
          onward_flight_date4,
          onward_flight_no5,
          onward_flight_date5,
          onward_flight_no6,
          onward_flight_date6,
          contents_remarks,
          remarks,
          quantity,
          date_offset_1,
          date_offset_2,
          date_offset_3,
          date_offset_4,
          date_offset_5,
          date_offset_6)
      VALUES
         (p_template_name,
          p_agent,
          p_card_id,
          p_dest1,
          p_dest2,
          p_dest3,
          p_dest4,
          p_dest5,
          p_dest6,
          p_class1,
          p_class2,
          p_class3,
          p_class4,
          p_class5,
          p_class6,
          p_loaded_at,
          p_transfer_at,
          p_uld_category,
          l_uld_type,
          p_onward_flight_no1,
          p_onward_flight_date1,
          p_onward_flight_no2,
          p_onward_flight_date2,
          p_onward_flight_no3,
          p_onward_flight_date3,
          p_onward_flight_no4,
          p_onward_flight_date4,
          p_onward_flight_no5,
          p_onward_flight_date5,
          p_onward_flight_no6,
          p_onward_flight_date6,
          p_contents_remarks,
          p_remarks,
          p_quantity,
          p_date_offset_1,
          p_date_offset_2,
          p_date_offset_3,
          p_date_offset_4,
          p_date_offset_5,
          p_date_offset_6);

   END IF; -- SQL%ROWCOUNT = 0

END save_uld_card_details_2;

/******************************************************************************
 *
 * PROCEDURE SAVE_AS_ULD_TEMPLATE
 *
 ******************************************************************************/

PROCEDURE save_as_uld_template
   (p_template_name      IN uld_card_templates.template_name%TYPE,
    p_agent              IN uld_card_templates.agent%TYPE,
    p_uld_category       IN uld_category_type,
    p_uld_type           IN uld_type_type,
    p_dest1              IN dest1_type,
    p_class1             IN class1_type,
    p_dest2              IN dest2_type,
    p_class2             IN class2_type,
    p_dest3              IN dest3_type,
    p_class3             IN class3_type,
    p_dest4              IN dest4_type,
    p_class4             IN class4_type,
    p_dest5              IN dest5_type,
    p_class5             IN class5_type,
    p_dest6              IN dest6_type,
    p_class6             IN class6_type,
    p_quantity           IN quantity_type,
    p_loaded_at          IN loaded_at_type,
    p_transfer_at        IN transfer_at_type,
    p_flight_no          IN uld_card_templates.flight_no%TYPE,
    p_flight_date        IN uld_card_templates.flight_date%TYPE,
    p_bar_codes          IN uld_card_templates.bar_codes%TYPE,
    p_card_id            IN card_id_type,
    p_contents_remarks   IN contents_remarks_type,
    p_remarks            IN remarks_type,
    p_onward_flight_no1   IN onward_flight_no1_type,
    p_onward_flight_date1 IN onward_flight_date1_type,
    p_onward_flight_no2   IN onward_flight_no2_type,
    p_onward_flight_date2 IN onward_flight_date2_type,
    p_onward_flight_no3   IN onward_flight_no3_type,
    p_onward_flight_date3 IN onward_flight_date3_type,
    p_onward_flight_no4   IN onward_flight_no4_type,
    p_onward_flight_date4 IN onward_flight_date4_type,
    p_onward_flight_no5   IN onward_flight_no5_type,
    p_onward_flight_date5 IN onward_flight_date5_type,
    p_onward_flight_no6   IN onward_flight_no6_type,
    p_onward_flight_date6 IN onward_flight_date6_type
) IS

   l_uld_type uld_recorded_id_codes.uld_type%TYPE; -- Derived ULD type
   l_counter  BINARY_INTEGER;                      -- Record set count variable

BEGIN

   -- Check template and agent are set
--   validate_data('save a template', p_template_name, p_agent);

   -- Attempt to update the uld_card_templates table
   UPDATE
          uld_card_templates
   SET
          timestamp = SYSDATE,
          flight_no = p_flight_no,
          flight_date = p_flight_date,
          bar_codes = p_bar_codes
   WHERE
          template_name = trim(p_template_name)
   AND
          agent = p_agent;

   IF SQL%ROWCOUNT = 0 THEN
      -- The update did not work (rows does not exist), perform an insert

      INSERT INTO uld_card_templates
         (template_name,
          agent,
          timestamp,
          flight_no,
          flight_date,
          bar_codes)
      VALUES
         (trim(p_template_name),
          p_agent,
          SYSDATE,
          p_flight_no,
          p_flight_date,
          p_bar_codes);

   END IF; -- SQL%ROWCOUNT = 0

   -- Save the individual ULD card details
   FOR l_counter IN p_card_id.FIRST..p_card_id.LAST LOOP
      -- Scroll through the collection saving each record

      IF p_card_id.EXISTS(l_counter) THEN
         -- Only save records that exist

         -- Derive the ULD type (if not set)
         l_uld_type := NVL( p_uld_type(l_counter), get_uld_type( p_uld_category(l_counter) ) );

         -- Attempt to update the uld_card_details table
         UPDATE
                uld_card_details
         SET
                dest1 = p_dest1(l_counter),
                dest2 = p_dest2(l_counter),
                dest3 = p_dest3(l_counter),
                dest4 = p_dest4(l_counter),
                dest5 = p_dest5(l_counter),
                dest6 = p_dest6(l_counter),
                class1 = p_class1(l_counter),
                class2 = p_class2(l_counter),
                class3 = p_class3(l_counter),
                class4 = p_class4(l_counter),
                class5 = p_class5(l_counter),
                class6 = p_class6(l_counter),
                onward_flight_no1 = p_onward_flight_no1(l_counter),
                onward_flight_date1 = p_onward_flight_date1(l_counter),
                onward_flight_no2 = p_onward_flight_no2(l_counter),
                onward_flight_date2 = p_onward_flight_date2(l_counter),
                onward_flight_no3 = p_onward_flight_no3(l_counter),
                onward_flight_date3 = p_onward_flight_date3(l_counter),
                onward_flight_no4 = p_onward_flight_no4(l_counter),
                onward_flight_date4 = p_onward_flight_date4(l_counter),
                onward_flight_no5 = p_onward_flight_no5(l_counter),
                onward_flight_date5 = p_onward_flight_date5(l_counter),
                onward_flight_no6 = p_onward_flight_no6(l_counter),
                onward_flight_date6 = p_onward_flight_date6(l_counter),
                loaded_at = p_loaded_at(l_counter),
                transfer_at = p_transfer_at(l_counter),
                uld_category = p_uld_category(l_counter),
                uld_type = l_uld_type,
                contents_remarks = p_contents_remarks(l_counter),
                remarks = p_remarks(l_counter),
                quantity = p_quantity(l_counter)
         WHERE
                template_name = p_template_name
         AND
                agent = p_agent
         AND
                card_id = p_card_id(l_counter);

         IF SQL%ROWCOUNT = 0 THEN
            -- The update did not work (rows does not exist), perform an insert

            INSERT INTO uld_card_details
               (template_name,
                agent,
                card_id,
                dest1,
                dest2,
                dest3,
                dest4,
                dest5,
                dest6,
                class1,
                class2,
                class3,
                class4,
                class5,
                class6,
                loaded_at,
                transfer_at,
                uld_category,
                uld_type,
                onward_flight_no1,
                onward_flight_date1,
                onward_flight_no2,
                onward_flight_date2,
                onward_flight_no3,
                onward_flight_date3,
                onward_flight_no4,
                onward_flight_date4,
                onward_flight_no5,
                onward_flight_date5,
                onward_flight_no6,
                onward_flight_date6,
                contents_remarks,
                remarks,
                quantity)
            VALUES
               (p_template_name,
                p_agent,
                p_card_id(l_counter),
                p_dest1(l_counter),
                p_dest2(l_counter),
                p_dest3(l_counter),
                p_dest4(l_counter),
                p_dest5(l_counter),
                p_dest6(l_counter),
                p_class1(l_counter),
                p_class2(l_counter),
                p_class3(l_counter),
                p_class4(l_counter),
                p_class5(l_counter),
                p_class6(l_counter),
                p_loaded_at(l_counter),
                p_transfer_at(l_counter),
                p_uld_category(l_counter),
                l_uld_type,
                p_onward_flight_no1(l_counter),
                p_onward_flight_date1(l_counter),
                p_onward_flight_no2(l_counter),
                p_onward_flight_date2(l_counter),
                p_onward_flight_no3(l_counter),
                p_onward_flight_date3(l_counter),
                p_onward_flight_no4(l_counter),
                p_onward_flight_date4(l_counter),
                p_onward_flight_no5(l_counter),
                p_onward_flight_date5(l_counter),
                p_onward_flight_no6(l_counter),
                p_onward_flight_date6(l_counter),
                p_contents_remarks(l_counter),
                p_remarks(l_counter),
                p_quantity(l_counter));

         END IF; -- SQL%ROWCOUNT = 0

      END IF; -- p_card_id.EXISTS(l_counter)

   END LOOP; -- l_counter IN p_card_id.FIRST..

   -- Save the transaction
   COMMIT;

END save_as_uld_template;

/******************************************************************************
 *
 * PROCEDURE DELETE_ULD_TEMPLATE
 *
 ******************************************************************************/

PROCEDURE delete_uld_template
   (p_template_name IN uld_card_templates.template_name%TYPE,
    p_agent         IN uld_card_templates.agent%TYPE) IS

BEGIN

   -- Remove the details from the database
   DELETE FROM uld_card_details
      WHERE template_name = p_template_name AND agent = p_agent;

   -- Remove the template from the database
   DELETE FROM uld_card_templates
      WHERE template_name = p_template_name AND agent = p_agent;

   -- Save the transaction
   COMMIT;

END delete_uld_template;

/******************************************************************************
 *
 * PROCEDURE DELETE_ULD_DETAILS
 *
 ******************************************************************************/

PROCEDURE delete_uld_details
   (p_template_name IN uld_card_details.template_name%TYPE,
    p_agent         IN uld_card_details.agent%TYPE,
    p_card_id       IN uld_card_details.card_id%TYPE) IS

BEGIN

   -- Remove the details from the database
   DELETE FROM uld_card_details
      WHERE template_name = p_template_name AND
            agent = p_agent AND
            card_id = p_card_id;

   -- Save the transaction
   COMMIT;

END delete_uld_details;

/******************************************************************************
 *
 * PROCEDURE RECORD_ULD_TEMPLATE
 *
 ******************************************************************************/

PROCEDURE record_uld_template
   (p_template_name       IN  uld_recorded_id_codes.template_name%TYPE,
    p_agent               IN  uld_recorded_id_codes.agent%TYPE,
    p_card_id             IN  uld_recorded_id_codes.card_id%TYPE,
    p_uld_category        IN  uld_recorded_id_codes.uld_category%TYPE,
    p_uld_type            IN  uld_recorded_id_codes.uld_type%TYPE,
    p_dest1               IN  uld_recorded_id_codes.dest1%TYPE,
    p_class1              IN  uld_recorded_id_codes.class1%TYPE,
    p_dest2               IN  uld_recorded_id_codes.dest2%TYPE,
    p_class2              IN  uld_recorded_id_codes.class2%TYPE,
    p_dest3               IN  uld_recorded_id_codes.dest3%TYPE,
    p_class3              IN  uld_recorded_id_codes.class3%TYPE,
    p_dest4               IN  uld_recorded_id_codes.dest4%TYPE,
    p_class4              IN  uld_recorded_id_codes.class4%TYPE,
    p_dest5               IN  uld_recorded_id_codes.dest5%TYPE,
    p_class5              IN  uld_recorded_id_codes.class5%TYPE,
    p_dest6               IN  uld_recorded_id_codes.dest6%TYPE,
    p_class6              IN  uld_recorded_id_codes.class6%TYPE,
    p_flight_no           IN  uld_recorded_id_codes.flight_no%TYPE,
    p_flight_date         IN  uld_recorded_id_codes.flight_date%TYPE,
    p_departing_airport   IN  uld_recorded_id_codes.dest1%TYPE,
    p_contents_remarks    IN  uld_recorded_id_codes.contents_remarks%TYPE,
    p_remarks             IN  uld_recorded_id_codes.remarks%TYPE,
    p_onward_flight_no1   IN  uld_recorded_id_codes.onward_flight_no1%TYPE,
    p_onward_flight_date1 IN  uld_recorded_id_codes.onward_flight_date1%TYPE,
    p_onward_flight_no2   IN  uld_recorded_id_codes.onward_flight_no2%TYPE,
    p_onward_flight_date2 IN  uld_recorded_id_codes.onward_flight_date2%TYPE,
    p_onward_flight_no3   IN  uld_recorded_id_codes.onward_flight_no3%TYPE,
    p_onward_flight_date3 IN  uld_recorded_id_codes.onward_flight_date3%TYPE,
    p_onward_flight_no4   IN  uld_recorded_id_codes.onward_flight_no4%TYPE,
    p_onward_flight_date4 IN  uld_recorded_id_codes.onward_flight_date4%TYPE,
    p_onward_flight_no5   IN  uld_recorded_id_codes.onward_flight_no5%TYPE,
    p_onward_flight_date5 IN  uld_recorded_id_codes.onward_flight_date5%TYPE,
    p_onward_flight_no6   IN  uld_recorded_id_codes.onward_flight_no6%TYPE,
    p_onward_flight_date6 IN  uld_recorded_id_codes.onward_flight_date6%TYPE,
    p_uld_id_code         OUT uld_recorded_id_codes.uld_id_code%TYPE) IS

BEGIN

   -- Get the next ULD sequence ID code
   p_uld_id_code := get_uld_id_code_seq(p_departing_airport);

   record_uld(p_template_name, p_agent, p_card_id, p_uld_category, p_uld_type, p_dest1,
              p_class1, p_dest2, p_class2, p_dest3, p_class3, p_dest4, p_class4, p_dest5,
              p_class5, p_dest6, p_class6, p_flight_no, p_flight_date, p_departing_airport,
              p_contents_remarks, p_remarks, p_onward_flight_no1,
              p_onward_flight_date1, p_onward_flight_no2, p_onward_flight_date2,
              p_onward_flight_no3, p_onward_flight_date3, p_onward_flight_no4,
              p_onward_flight_date4, p_onward_flight_no5, p_onward_flight_date5,
              p_onward_flight_no6, p_onward_flight_date6, p_uld_id_code, FALSE);

   -- The template is active, update its timestamp to prevent archiving
   update_uld_template(p_template_name, p_agent);

   -- Save the transaction
   COMMIT;

END record_uld_template;

/******************************************************************************
 *
 * PROCEDURE RECORD_REGISTER_ULD
 *
 ******************************************************************************/

PROCEDURE record_register_uld
   (
    p_template_name       IN  uld_card_templates.template_name%TYPE,
    p_agent               IN  uld_card_templates.agent%TYPE,
    p_card_id             IN  uld_recorded_id_codes.card_id%TYPE,
    p_uld_category        IN  uld_recorded_id_codes.uld_category%TYPE,
    p_uld_type            IN  uld_recorded_id_codes.uld_type%TYPE,
    p_dest1               IN  uld_recorded_id_codes.dest1%TYPE,
    p_class1              IN  uld_recorded_id_codes.class1%TYPE,
    p_dest2               IN  uld_recorded_id_codes.dest2%TYPE,
    p_class2              IN  uld_recorded_id_codes.class2%TYPE,
    p_dest3               IN  uld_recorded_id_codes.dest3%TYPE,
    p_class3              IN  uld_recorded_id_codes.class3%TYPE,
    p_dest4               IN  uld_recorded_id_codes.dest4%TYPE,
    p_class4              IN  uld_recorded_id_codes.class4%TYPE,
    p_dest5               IN  uld_recorded_id_codes.dest5%TYPE,
    p_class5              IN  uld_recorded_id_codes.class5%TYPE,
    p_dest6               IN  uld_recorded_id_codes.dest6%TYPE,
    p_class6              IN  uld_recorded_id_codes.class6%TYPE,
    p_flight_no           IN  uld_recorded_id_codes.flight_no%TYPE,
    p_flight_date         IN  uld_recorded_id_codes.flight_date%TYPE,
    p_departing_airport   IN  uld_recorded_id_codes.dest1%TYPE,
    p_uld_no              IN  uld_recorded_id_codes.uld_no%TYPE,
    p_conn_type           IN  ulds.connection_type%TYPE,
    p_userid              IN  tagtrak_users.user_id%TYPE,
    p_loc                 IN  location.location_code%TYPE,
    p_contents_remarks    IN  uld_recorded_id_codes.contents_remarks%TYPE,
    p_remarks             IN  uld_recorded_id_codes.remarks%TYPE,
    p_onward_flight_no1   IN  uld_recorded_id_codes.onward_flight_no1%TYPE,
    p_onward_flight_date1 IN  uld_recorded_id_codes.onward_flight_date1%TYPE,
    p_onward_flight_no2   IN  uld_recorded_id_codes.onward_flight_no2%TYPE,
    p_onward_flight_date2 IN  uld_recorded_id_codes.onward_flight_date2%TYPE,
    p_onward_flight_no3   IN  uld_recorded_id_codes.onward_flight_no3%TYPE,
    p_onward_flight_date3 IN  uld_recorded_id_codes.onward_flight_date3%TYPE,
    p_onward_flight_no4   IN  uld_recorded_id_codes.onward_flight_no4%TYPE,
    p_onward_flight_date4 IN  uld_recorded_id_codes.onward_flight_date4%TYPE,
    p_onward_flight_no5   IN  uld_recorded_id_codes.onward_flight_no5%TYPE,
    p_onward_flight_date5 IN  uld_recorded_id_codes.onward_flight_date5%TYPE,
    p_onward_flight_no6   IN  uld_recorded_id_codes.onward_flight_no6%TYPE,
    p_onward_flight_date6 IN  uld_recorded_id_codes.onward_flight_date6%TYPE,
    p_uld_id_code         OUT uld_recorded_id_codes.uld_id_code%TYPE,
    p_retcode             OUT NUMBER
    ) IS

   l_onwd_flight_no           onward_flight_no_type;
   l_onwd_flight_date         onward_flight_date_type;

   i                 NUMBER(10);
   l_siteid          site_info.site_id%TYPE;
   l_fid             flight.flight_id%TYPE                             := 0;
   l_flts            flight.flight_status%TYPE;
   l_conn_type       ulds.connection_type%TYPE;
   l_uld_type        uld_recorded_id_codes.uld_type%TYPE;
   l_uld_id_code     uld_recorded_id_codes.uld_id_code%TYPE;
   l_uld_category    ulds.uld_category%TYPE;
   l_uld_no          ulds.uld_no%TYPE;
   l_userid          tagtrak_users.user_id%TYPE;
   l_loc             location.location_code%TYPE;
   l_flag            NUMBER                                            := 0;
   l_ret             NUMBER                                            := 0;
   l_ret2            NUMBER                                            := 0;
   insert_flag       NUMBER(1)                                         := 0;
   l_dest            dest_list_type;
   l_class           class_list_type;
   l_valid_dest      BOOLEAN                                           := FALSE;
   l_curr_uld_dests  ulds_pkg.cur_get_ulds_details_type;
   l_curr_dest       uld_recorded_id_codes.dest1%TYPE;
   l_curr_class      uld_recorded_id_codes.class1%TYPE;
   l_curr_bags       NUMBER(10);
   l_uld_has_bags    BOOLEAN                                           := FALSE;
   l_err_msg         VARCHAR2(300);
   l_err_code        NUMBER(10);

   l_uld_operation   CONSTANT VARCHAR2(1)                              := 'A';


BEGIN

   -- Initialise
   p_retcode     := pkg_constants.register_uld_success;
   l_userid      := p_userid;                             -- Have to use local variable since used as IN OUT parameter to procedures
   l_loc         := p_loc;                                -- Have to use local variable since used as IN OUT parameter to procedures
   p_uld_id_code := 0;

   -- Get site id for p_departing_airport
   l_siteid := multi_site.get_site_id(p_departing_airport);

   --Validate flight_details
   flight_id(p_flight_no, p_flight_date, l_siteid, l_fid);
   IF (l_fid = 0) THEN
      l_flag := pkg_constants.error_unknown_flight;
      RAISE abort;
   END IF;

   --Validate flight status for flight
   IF (l_fid > 0) THEN
      SELECT flight_status INTO l_flts FROM flight WHERE flight_id = l_fid;

      IF (l_flts IN (pkg_constants.flight_closed, pkg_constants.post_departure)) THEN
         l_flag := pkg_constants.error_flight_status_pd;
         RAISE abort;
      END IF;
   END IF;

   --Validate user access to flight
   user_authentication.validate_access(l_userid, l_fid, p_flight_no, p_flight_date, l_ret);
   IF (l_ret < 0) THEN
      l_flag := pkg_constants.error_flight_access_denied;
      RAISE abort;
   END IF;

   --Validate uld no
   l_uld_no := p_uld_no;
   IF (NOT validate_uldno(l_uld_no)) THEN
      l_flag := -1000;
      RAISE abort;
   END IF;

   -- Derive the ULD type (if not set)
   l_uld_category  := p_uld_category;
   IF (l_uld_category IS NOT NULL) THEN
      l_uld_type := NVL(p_uld_type, get_uld_type(l_uld_category));
   END IF;

   --Validate_connection type - if uld_type in 'M' OR 'L' then set connection type should be set to null
   l_conn_type := p_conn_type;
   IF (l_uld_type IN ('M', 'L')) THEN
      l_conn_type := NULL;
   END IF;

   -- Move destination information into associative arrays to make processing easier
   populate_dest_arrays(p_dest1,               p_dest2,               p_dest3,
                        p_dest4,               p_dest5,               p_dest6,
                        p_class1,              p_class2,              p_class3,
                        p_class4,              p_class5,              p_class6,
                        p_onward_flight_no1,   p_onward_flight_no2,   p_onward_flight_no3,
                        p_onward_flight_no4,   p_onward_flight_no5,   p_onward_flight_no6,
                        p_onward_flight_date1, p_onward_flight_date2, p_onward_flight_date3,
                        p_onward_flight_date4, p_onward_flight_date5, p_onward_flight_date6,
                        l_dest,                l_class,               l_onwd_flight_date,
                        l_onwd_flight_no,      l_valid_dest);

   -- check if there is uld_id_code already associated with the uld if so return the existing uld_id_code
   BEGIN
      SELECT uld_id_code
        INTO l_uld_id_code
        FROM uld_recorded_id_codes
       WHERE flight_no    = p_flight_no
         AND flight_date  = p_flight_date
         AND site_id      = l_siteid
         AND uld_no       = l_uld_no;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         insert_flag  := 1;
      WHEN OTHERS THEN
          l_flag := pkg_constants.error_uld_id;
          RAISE abort;
   END;

   IF (insert_flag = 0 AND l_valid_dest) THEN                  -- association already exist send out ULD_ID_CODE for printing on ULD_CARD

      -- Update the ULD_RECORDED_ID_CODES table to reflect changes in the ULD
      BEGIN
         record_uld(p_template_name,       p_agent,               p_card_id,             l_uld_category,        l_uld_type,
                    l_dest(1),             l_class(1),            l_dest(2),             l_class(2),            l_dest(3),
                    l_class(3),            l_dest(4),             l_class(4),            l_dest(5),             l_class(5),
                    l_dest(6),             l_class(6),            p_flight_no,           p_flight_date,         p_departing_airport,
                    p_contents_remarks,    p_remarks,             l_onwd_flight_no(1),   l_onwd_flight_date(1), l_onwd_flight_no(2),
                    l_onwd_flight_date(2), l_onwd_flight_no(3),   l_onwd_flight_date(3), l_onwd_flight_no(4),   l_onwd_flight_date(4),
                    l_onwd_flight_no(5),   l_onwd_flight_date(5), l_onwd_flight_no(6),   l_onwd_flight_date(6), l_uld_id_code,
                    TRUE);
      EXCEPTION
         WHEN OTHERS THEN
            l_flag := SQLCODE;
            RAISE abort;
      END;

   ELSIF (insert_flag = 1 AND l_valid_dest) THEN                -- associate template to flight and associate uld_id_code to ULD_NO

      -- Get the next ULD sequence ID code
      l_uld_id_code := get_uld_id_code_seq(p_departing_airport);

      -- Record the ULD details in the ULD_RECORDED_ID_CODES table
      record_uld(p_template_name,       p_agent,               p_card_id,             l_uld_category,        l_uld_type,
                 l_dest(1),             l_class(1),            l_dest(2),             l_class(2),            l_dest(3),
                 l_class(3),            l_dest(4),             l_class(4),            l_dest(5),             l_class(5),
                 l_dest(6),             l_class(6),            p_flight_no,           p_flight_date,         p_departing_airport,
                 p_contents_remarks,    p_remarks,             l_onwd_flight_no(1),   l_onwd_flight_date(1), l_onwd_flight_no(2),
                 l_onwd_flight_date(2), l_onwd_flight_no(3),   l_onwd_flight_date(3), l_onwd_flight_no(4),   l_onwd_flight_date(4),
                 l_onwd_flight_no(5),   l_onwd_flight_date(5), l_onwd_flight_no(6),   l_onwd_flight_date(6), l_uld_id_code,
                 FALSE);

      --Associate uld no to uld id code
      associate_recorded_uld(l_uld_no, l_uld_id_code, l_uld_operation, l_ret);
      IF ( l_ret < 0 ) THEN
         l_flag:= -500;
         RAISE abort;
      END IF;

   ELSE

      l_flag := -500;
      RAISE abort;

   END IF;  -- association already exist send out ULD_ID_CODE for printing on ULD_CARD

   -- Set up the returned ULD ID Code
   p_uld_id_code := l_uld_id_code;

   -- The template is active, update its timestamp to prevent archiving
   update_uld_template(p_template_name, p_agent);

   register_uld(l_uld_no,   l_fid,              l_uld_category,       l_conn_type,     l_valid_dest,
                l_userid,   l_loc,              insert_flag,          l_uld_id_code,   l_dest,
                l_class,    l_onwd_flight_no,   l_onwd_flight_date,   l_uld_type,      l_flag);
   IF (l_flag < 0) THEN
     RAISE ABORT;
   END IF;

   p_retcode := l_flag;

   -- Save the transaction
   COMMIT;

EXCEPTION

   WHEN abort THEN

       -- Note the internal error number, rollback any changes and exit
       p_retcode := l_flag;
       ROLLBACK;

   WHEN OTHERS THEN

       -- Get the reason for the error
       l_err_msg := SQLERRM;
       l_err_code := SQLCODE;

       -- Rollback any database changes
       ROLLBACK;

       -- Create an error record and save it.
       INSERT INTO oracle_error
              (process_name, errror_msg, error_code, error_time)
         VALUES
              ('ULD_CARDS.RECORD_REGISTER_ULD', l_err_msg, l_err_code, SYSDATE);
       COMMIT;
       p_retcode := -1;

END record_register_uld;

/******************************************************************************
 *
 * PROCEDURE RECORD_ULD
 *
 ******************************************************************************/

PROCEDURE record_uld
   (p_template_name       IN  uld_recorded_id_codes.template_name%TYPE,
    p_agent               IN  uld_recorded_id_codes.agent%TYPE,
    p_card_id             IN  uld_recorded_id_codes.card_id%TYPE,
    p_uld_category        IN  uld_recorded_id_codes.uld_category%TYPE,
    p_uld_type            IN  uld_recorded_id_codes.uld_type%TYPE,
    p_dest1               IN  uld_recorded_id_codes.dest1%TYPE,
    p_class1              IN  uld_recorded_id_codes.class1%TYPE,
    p_dest2               IN  uld_recorded_id_codes.dest2%TYPE,
    p_class2              IN  uld_recorded_id_codes.class2%TYPE,
    p_dest3               IN  uld_recorded_id_codes.dest3%TYPE,
    p_class3              IN  uld_recorded_id_codes.class3%TYPE,
    p_dest4               IN  uld_recorded_id_codes.dest4%TYPE,
    p_class4              IN  uld_recorded_id_codes.class4%TYPE,
    p_dest5               IN  uld_recorded_id_codes.dest5%TYPE,
    p_class5              IN  uld_recorded_id_codes.class5%TYPE,
    p_dest6               IN  uld_recorded_id_codes.dest6%TYPE,
    p_class6              IN  uld_recorded_id_codes.class6%TYPE,
    p_flight_no           IN  uld_recorded_id_codes.flight_no%TYPE,
    p_flight_date         IN  uld_recorded_id_codes.flight_date%TYPE,
    p_departing_airport   IN  uld_recorded_id_codes.dest1%TYPE,
    p_contents_remarks    IN  uld_recorded_id_codes.contents_remarks%TYPE,
    p_remarks             IN  uld_recorded_id_codes.remarks%TYPE,
    p_onward_flight_no1   IN  uld_recorded_id_codes.onward_flight_no1%TYPE,
    p_onward_flight_date1 IN  uld_recorded_id_codes.onward_flight_date1%TYPE,
    p_onward_flight_no2   IN  uld_recorded_id_codes.onward_flight_no2%TYPE,
    p_onward_flight_date2 IN  uld_recorded_id_codes.onward_flight_date2%TYPE,
    p_onward_flight_no3   IN  uld_recorded_id_codes.onward_flight_no3%TYPE,
    p_onward_flight_date3 IN  uld_recorded_id_codes.onward_flight_date3%TYPE,
    p_onward_flight_no4   IN  uld_recorded_id_codes.onward_flight_no4%TYPE,
    p_onward_flight_date4 IN  uld_recorded_id_codes.onward_flight_date4%TYPE,
    p_onward_flight_no5   IN  uld_recorded_id_codes.onward_flight_no5%TYPE,
    p_onward_flight_date5 IN  uld_recorded_id_codes.onward_flight_date5%TYPE,
    p_onward_flight_no6   IN  uld_recorded_id_codes.onward_flight_no6%TYPE,
    p_onward_flight_date6 IN  uld_recorded_id_codes.onward_flight_date6%TYPE,
    p_uld_id_code         IN  uld_recorded_id_codes.uld_id_code%TYPE,
    p_allow_update        IN  BOOLEAN) IS

   l_uld_type uld_recorded_id_codes.uld_type%TYPE; -- Derived ULD type
   l_siteid   site_info.site_id%TYPE;              -- ID of the departing airport
   update_flag NUMBER;

BEGIN

   IF (p_uld_category IS NOT NULL) THEN
      -- Derive the ULD type (if not set)
      l_uld_type := NVL( p_uld_type, get_uld_type(p_uld_category) );
   END IF;

   -- Get site id for p_departing_airport
   l_siteid  := multi_site.get_site_id(p_departing_airport);

   -- Determine whether this ULD_ID has already been recorded
   SELECT COUNT(*)
     INTO update_flag
     FROM uld_recorded_id_codes
    WHERE uld_id_code  = p_uld_id_code;

   IF (update_flag = 1 AND p_allow_update) THEN

      UPDATE uld_recorded_id_codes
         SET template_name       = p_template_name,
             agent               = p_agent,
             card_id             = p_card_id,
             flight_no           = p_flight_no,
             flight_date         = p_flight_date,
             site_id             = l_siteid,
             uld_category        = p_uld_category,
             uld_type            = l_uld_type,
             dest1               = p_dest1,
             dest2               = p_dest2,
             dest3               = p_dest3,
             dest4               = p_dest4,
             dest5               = p_dest5,
             dest6               = p_dest6,
             class1              = p_class1,
             class2              = p_class2,
             class3              = p_class3,
             class4              = p_class4,
             class5              = p_class5,
             class6              = p_class6,
             contents_remarks    = p_contents_remarks,
             remarks             = p_remarks,
             onward_flight_no1   = p_onward_flight_no1,
             onward_flight_date1 = p_onward_flight_date1,
             onward_flight_no2   = p_onward_flight_no2,
             onward_flight_date2 = p_onward_flight_date2,
             onward_flight_no3   = p_onward_flight_no3,
             onward_flight_date3 = p_onward_flight_date3,
             onward_flight_no4   = p_onward_flight_no4,
             onward_flight_date4 = p_onward_flight_date4,
             onward_flight_no5   = p_onward_flight_no5,
             onward_flight_date5 = p_onward_flight_date5,
             onward_flight_no6   = p_onward_flight_no6,
             onward_flight_date6 = p_onward_flight_date6
       WHERE uld_id_code = p_uld_id_code;

   ELSIF (update_flag = 0) THEN

      -- Record the ULD details in the ULD_RECORDED_ID_CODES table
      INSERT INTO uld_recorded_id_codes
         (uld_id_code,           template_name,         agent,                 card_id,               flight_no,
          flight_date,           site_id,               uld_category,          uld_type,              dest1,
          dest2,                 dest3,                 dest4,                 dest5,                 dest6,
          class1,                class2,                class3,                class4,                class5,
          class6,                contents_remarks,      remarks,               uld_id_code_created,   onward_flight_no1,
          onward_flight_date1,   onward_flight_no2,     onward_flight_date2,   onward_flight_no3,     onward_flight_date3,
          onward_flight_no4,     onward_flight_date4,   onward_flight_no5,     onward_flight_date5,   onward_flight_no6,
          onward_flight_date6
          )
      VALUES
         (p_uld_id_code,         p_template_name,       p_agent,               p_card_id,             p_flight_no,
          p_flight_date,         l_siteid,              p_uld_category,        l_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,              p_contents_remarks,    p_remarks,             SYSDATE,               p_onward_flight_no1,
          p_onward_flight_date1, p_onward_flight_no2,   p_onward_flight_date2, p_onward_flight_no3,   p_onward_flight_date3,
          p_onward_flight_no4,   p_onward_flight_date4, p_onward_flight_no5,   p_onward_flight_date5, p_onward_flight_no6,
          p_onward_flight_date6
          );

   END IF;

END record_uld;

/******************************************************************************
 *
 * PROCEDURE REGISTER_ULD
 *
 ******************************************************************************/

PROCEDURE register_uld
   (p_uld_no              IN OUT uld_recorded_id_codes.uld_no%TYPE,
    p_fid                 IN OUT flight.flight_id%TYPE,
    p_uld_category        IN OUT uld_recorded_id_codes.uld_category%TYPE,
    p_conn_type           IN OUT ulds.connection_type%TYPE,
    p_valid_dest          IN     BOOLEAN,
    p_userid              IN OUT tagtrak_users.user_id%TYPE,
    p_loc                 IN OUT location.location_code%TYPE,
    p_insert_flag         IN     NUMBER,
    p_uld_id_code         IN     uld_recorded_id_codes.uld_id_code%TYPE,
    p_dest                IN OUT dest_list_type,
    p_class               IN OUT class_list_type,
    p_onwd_flight_no      IN     onward_flight_no_type,
    p_onwd_flight_date    IN     onward_flight_date_type,
    p_uld_type            IN OUT uld_recorded_id_codes.uld_type%TYPE,
    p_flag                OUT    NUMBER ) IS


   l_ret             NUMBER                                            := 0;
   l_ret2            NUMBER                                            := 0;
   l_curr_uld_dests  ulds_pkg.cur_get_ulds_details_type;
   l_curr_dest       uld_recorded_id_codes.dest1%TYPE;
   l_curr_class      uld_recorded_id_codes.class1%TYPE;
   l_curr_onw_flt    uld_recorded_id_codes.onward_flight_no1%TYPE;
   l_curr_onw_date   uld_recorded_id_codes.onward_flight_date1%TYPE;
   l_curr_bags       NUMBER(10);
   l_uld_has_bags    BOOLEAN                                           := FALSE;
   i                 NUMBER;
   l_flag            NUMBER                                            := 0;
   l_timestamp       CHAR(5); -- Timestamp returned from trace action being created.


BEGIN

   -- Initialise
   l_flag := 0;

   -- Check uld before registration
   -- CHECK_ULDS returns the following values for L_RET:-
   --   l_ret  ULD      Flight  Category  Conn Type    Hold?   Assigned?
   --         Exists      PD    Correct?  Correct?
   --    7     NO        N/A      N/A       N/A         YES       NO
   --    8     YES       N/A      N/A       N/A         YES       YES
   --    8     YES       NO       YES       YES         NO        YES
   --    9     YES       YES      YES       YES         NO        YES
   --    18    YES       NO      -- one is NO --        NO        YES
   --    19    YES       YES     -- one is NO --        NO        YES
   --
   -- Error Codes:-
   --   -1408   Not assigned but bag group of same name exists
   --   -200    Wrong Flight
   --
   check_ulds(p_uld_no, p_fid, p_uld_category, l_ret, p_conn_type);
   IF (l_ret < 0) THEN

       l_flag := pkg_constants.error_uld_diff_flight;
       RAISE abort;

   END IF;

   -- Remove all current destinations
   IF (l_ret <> 7) THEN

       -- ULD Exists - remove all current destinations
       ulds_pkg.get_ulds_details(p_uld_no, p_fid, l_curr_uld_dests);

       LOOP

          -- Get the current destination, class and number of bags in ULD
          FETCH l_curr_uld_dests INTO l_curr_dest, l_curr_class, l_curr_bags, l_curr_onw_flt, l_curr_onw_date;
          EXIT WHEN l_curr_uld_dests%NOTFOUND;

          -- Set flag if ULD contains bags
          IF (l_curr_bags > 0) THEN
              l_uld_has_bags := TRUE;
          END IF;

          -- Check if user wants to remove ULD, but it has bags
          IF (NOT p_valid_dest AND l_uld_has_bags) THEN
             l_flag := pkg_constants.error_bags_loaded_in_uld;
             CLOSE l_curr_uld_dests;
             RAISE abort;
          END IF;

          -- Remove this destination
          remove_uld_dests_record(p_uld_no, l_curr_dest, p_fid);

       END LOOP;

       -- If this ULD is no longer required, deassign it from the flight
       IF (NOT p_valid_dest) THEN
          deassign_uld(p_uld_no, p_userid, p_loc, l_ret2);
          IF (l_ret2 < 0) THEN
            l_flag := l_ret2;           -- Some error occurred - raise and exit
            RAISE abort;
          END IF;
       END IF;

   END IF;

   -- All further processing only occurs if the ULD contains at least 1 valid destination
   IF (p_valid_dest) THEN

       -- Add an UPDATE ULD_TRACE action if the ULD details had been altered and
       -- registered.
       IF (l_ret <> 7) THEN
           add_uld_trace_record(p_uld_no, p_fid, p_userid, '0', p_loc, l_timestamp, 'UPDATE', 'Y');
       END IF;

       -- Create the ULD record - if required
       IF (l_ret = 7 AND p_valid_dest AND p_insert_flag = 1) THEN

          add_uld_record(p_uld_no, p_fid, p_uld_category, p_uld_type, p_userid, p_loc, p_conn_type, p_uld_id_code);

       END IF;

       -- Check details on ULD are correct
       IF (l_ret IN (9, 19)) THEN

          -- PD Marker Set
          modify_uld_record(p_uld_no, p_fid);

       END IF;

       -- Check Category and Connection Type are correct
       IF (l_ret IN (18, 19)) THEN

          -- Category or Connection Type is wrong
          modify_uld_type(p_uld_no, p_fid, p_uld_category, p_uld_type, p_conn_type);

       END IF;

       -- Add the new destinations
       FOR i IN 1 .. 6 LOOP

          -- Create the destination record
          IF (p_dest(i) IS NOT NULL AND p_class(i) IS NOT NULL) THEN
             add_uld_dests_record(p_uld_no, p_dest(i), p_class(i), p_fid, p_onwd_flight_no(i), p_onwd_flight_date(i));
          END IF;

       END LOOP;

   END IF;

   -- Check whether we have created or updated a ULD
   IF (p_insert_flag = 0 AND l_ret <> 7) THEN

       -- We have updated
       l_flag := 600;

   END IF;

   p_flag := l_flag;

EXCEPTION

  WHEN ABORT THEN

    p_flag := l_flag;

  WHEN OTHERS THEN

    p_flag := SQLCODE;
    RAISE;

END register_uld;

/******************************************************************************
 *
 * PROCEDURE POPULATE_DEST_ARRAYS
 *
 ******************************************************************************/
PROCEDURE populate_dest_arrays
   (p_dest1               IN  uld_recorded_id_codes.dest1%TYPE,
    p_dest2               IN  uld_recorded_id_codes.dest2%TYPE,
    p_dest3               IN  uld_recorded_id_codes.dest3%TYPE,
    p_dest4               IN  uld_recorded_id_codes.dest4%TYPE,
    p_dest5               IN  uld_recorded_id_codes.dest5%TYPE,
    p_dest6               IN  uld_recorded_id_codes.dest6%TYPE,
    p_class1              IN  uld_recorded_id_codes.class1%TYPE,
    p_class2              IN  uld_recorded_id_codes.class2%TYPE,
    p_class3              IN  uld_recorded_id_codes.class3%TYPE,
    p_class4              IN  uld_recorded_id_codes.class4%TYPE,
    p_class5              IN  uld_recorded_id_codes.class5%TYPE,
    p_class6              IN  uld_recorded_id_codes.class6%TYPE,
    p_onward_flight_no1   IN  uld_recorded_id_codes.onward_flight_no1%TYPE,
    p_onward_flight_no2   IN  uld_recorded_id_codes.onward_flight_no2%TYPE,
    p_onward_flight_no3   IN  uld_recorded_id_codes.onward_flight_no3%TYPE,
    p_onward_flight_no4   IN  uld_recorded_id_codes.onward_flight_no4%TYPE,
    p_onward_flight_no5   IN  uld_recorded_id_codes.onward_flight_no5%TYPE,
    p_onward_flight_no6   IN  uld_recorded_id_codes.onward_flight_no6%TYPE,
    p_onward_flight_date1 IN  uld_recorded_id_codes.onward_flight_date1%TYPE,
    p_onward_flight_date2 IN  uld_recorded_id_codes.onward_flight_date2%TYPE,
    p_onward_flight_date3 IN  uld_recorded_id_codes.onward_flight_date3%TYPE,
    p_onward_flight_date4 IN  uld_recorded_id_codes.onward_flight_date4%TYPE,
    p_onward_flight_date5 IN  uld_recorded_id_codes.onward_flight_date5%TYPE,
    p_onward_flight_date6 IN  uld_recorded_id_codes.onward_flight_date6%TYPE,
    p_dest                OUT dest_list_type,
    p_class               OUT class_list_type,
    p_onward_flight_date  OUT onward_flight_date_type,
    p_onward_flight_no    OUT onward_flight_no_type,
    p_valid_dest          OUT BOOLEAN) IS

   i   NUMBER;

BEGIN

   -- Populate destination table
   p_dest(1)  := p_dest1;
   p_dest(2)  := p_dest2;
   p_dest(3)  := p_dest3;
   p_dest(4)  := p_dest4;
   p_dest(5)  := p_dest5;
   p_dest(6)  := p_dest6;

   -- Populate class list table
   p_class(1) := p_class1;
   p_class(2) := p_class2;
   p_class(3) := p_class3;
   p_class(4) := p_class4;
   p_class(5) := p_class5;
   p_class(6) := p_class6;

   -- Populate onward flight number table
   p_onward_flight_no(1)       := p_onward_flight_no1;
   p_onward_flight_no(2)       := p_onward_flight_no2;
   p_onward_flight_no(3)       := p_onward_flight_no3;
   p_onward_flight_no(4)       := p_onward_flight_no4;
   p_onward_flight_no(5)       := p_onward_flight_no5;
   p_onward_flight_no(6)       := p_onward_flight_no6;

   -- Populate onward flight date table
   p_onward_flight_date(1)     := p_onward_flight_date1;
   p_onward_flight_date(2)     := p_onward_flight_date2;
   p_onward_flight_date(3)     := p_onward_flight_date3;
   p_onward_flight_date(4)     := p_onward_flight_date4;
   p_onward_flight_date(5)     := p_onward_flight_date5;
   p_onward_flight_date(6)     := p_onward_flight_date6;

   -- NOTE:  There is 1 too many ONWARD FLIGHT fields in the database.
   --        6 destinations can be served by 5 onward flights, since the
   --        flight to the first destination is the actual flight we
   --        are assigning the ULD to.
   --        This means that the first ONWARD_FLIGHT details are always NULL.
   FOR i IN 1..6 LOOP

      IF (p_onward_flight_no(i) IS NOT NULL AND i > 1) THEN

         -- Populate the onward destination and onward class with '*' if they are null
         p_dest(i)  := NVL(p_dest(i), '*');
         p_class(i) := NVL(p_class(i), '*');

      END IF;

      -- Determine the action we are attempting
      IF (p_dest(i) IS NOT NULL AND p_class(i) IS NOT NULL) THEN
         p_valid_dest := TRUE;
      END IF;

   END LOOP;

END populate_dest_arrays;

/**************************************************************************************
*   PROCEDURE load_tab
*
*   Description
*     This procedure takes in a string of comma delimited ULD IDs, and creates a table
*     of one column with the ULD ID values.
*
*   Parameters
*     The input variable p_string is a string of comma delimited ULD_IDs
*
*   Output Variables
*     p_tab is a table made up of a single column. In this column are the ULD IDs that
*     were passed into the procedure.
*
**************************************************************************************/
PROCEDURE load_tab(p_string VARCHAR2,
                   p_tab    IN OUT uld_id_list_tab) IS

  t_continue BOOLEAN := TRUE;
  t_string   VARCHAR2(1000) := p_string;
  t_delim    VARCHAR2(1) := ',';
  t_chunk    VARCHAR2(1000);
  t_counter  NUMBER := p_tab.COUNT;
  t_pos      NUMBER;

BEGIN

  WHILE t_continue LOOP

    t_pos := instr(t_string, t_delim, 1, 1);

    IF t_pos > 0 THEN
      --found a delimeter, lets remove it.
      t_chunk := substr(t_string, 1 , t_pos - 1);
      t_string := substr(t_string, t_pos + 1);

    ELSE
      --no more delimeters to be found, last code in string
      t_chunk := t_string;
      t_continue := FALSE;

    END IF;

    t_counter := t_counter + 1;
    --remove any quotes
    t_chunk := rtrim(ltrim(t_chunk, ''''), '''');
    --add id_code
    p_tab.EXTEND;
    p_tab(t_counter) := t_chunk;

  END LOOP;

END load_tab;

FUNCTION uld_class_desc(p_uld_class  IN cabin_classes.cabin_class%TYPE)
RETURN cabin_classes.description%TYPE IS

  t_retval cabin_classes.description%TYPE;

BEGIN

  BEGIN

    SELECT description
    INTO t_retval
    FROM cabin_classes
    WHERE cabin_class = p_uld_class;

  EXCEPTION WHEN no_data_found THEN

    t_retval := NULL; --don't want an oracle error here.

  END;

  RETURN t_retval;

END uld_class_desc;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
PROCEDURE uld_cards_report(p_ref_cur in OUT uld_cards_ref_cur,
                           p_str1    IN     VARCHAR2,
                           p_str2    IN     VARCHAR2,
                           p_str3    IN     VARCHAR2,
                           p_str4    IN     VARCHAR2,
                           p_str5    IN     VARCHAR2,
                           p_str6    IN     VARCHAR2,
                           p_str7    IN     VARCHAR2,
                           p_str8    IN     VARCHAR2,
                           p_str9    IN     VARCHAR2,
                           p_str10   IN     VARCHAR2) IS

  id_list_table   uld_id_list_tab := uld_id_list_tab();

BEGIN
  --load each of the parameters into id_list_table
  load_tab(p_str1,
           id_list_table);

  load_tab(p_str2,
           id_list_table);

  load_tab(p_str3,
           id_list_table);

  load_tab(p_str4,
           id_list_table);

  load_tab(p_str5,
           id_list_table);

  load_tab(p_str6,
           id_list_table);

  load_tab(p_str7,
           id_list_table);

  load_tab(p_str8,
           id_list_table);

  load_tab(p_str9,
           id_list_table);

  load_tab(p_str10,
           id_list_table);
--/*+ INDEX (a uld_recorded_id_codes_sk1) INDEX_FFS (b uld_card_details_sk1) */
--Hint was in select. Probably no longer necessary

  --Populate ref cursor
  OPEN p_ref_cur FOR
  SELECT uric.uld_id_code,
         '*'||uric.uld_id_code||'*' uld_id_code_bc,
         uric.uld_no,
         uric.dest1 DEST,
         '*'||uric.dest1||'*' dest_bc,
         ucd.loaded_at,
         uric.flight_no        LOADED_AT_FLIGHT,
         '*'||REPLACE(uric.flight_no, ' ','')||'*' loaded_at_flight_bc,
         ucd.transfer_at,
         decode(length(ucd.transfer_at),
                NULL,
                NULL,
                '*'||ucd.transfer_at||'*') transfer_at_bc,
         uric.onward_flight_no2 TRANSFER_AT_FLIGHT,
         decode(length(uric.onward_flight_no2),
                NULL,
                NULL,
                '*'||REPLACE(uric.onward_flight_no2, ' ','')||'*') transfer_at_flight_bc,
         uric.class1,
         uric.uld_category,
         uric.uld_type,
         uric.flight_date,
         uric.contents_remarks,
         uric.remarks,
         uric.onward_flight_no1,
         uric.dest1,
         uric.onward_flight_no2,
         uric.dest2,
         uric.onward_flight_no3,
         uric.dest3,
         uric.onward_flight_no4,
         uric.dest4,
         uric.onward_flight_no5,
         uric.dest5,
         uric.onward_flight_no6,
         uric.dest6,
         substr(uric.class1,1,1) uld_class1,
         substr(uric.class1,2,1) uld_class2,
         substr(uric.class1,3,1) uld_class3,
         substr(uric.class1,4,1) uld_class4,
         substr(uric.class1,5,1) uld_class5,
         uld_class_desc(substr(uric.class1,1,1)) uld_class_desc1,
         uld_class_desc(substr(uric.class1,2,1)) uld_class_desc2,
         uld_class_desc(substr(uric.class1,3,1)) uld_class_desc3,
         uld_class_desc(substr(uric.class1,4,1)) uld_class_desc4,
         uld_class_desc(substr(uric.class1,5,1)) uld_class_desc5,
         decode(uric.uld_type, 'L', 'Local', 'T', 'Transfer', 'Mixed') type_desc,
         fie.box sorting_destination
  FROM uld_recorded_id_codes uric,
       uld_card_details ucd,
       flight_info_extras fie
  WHERE uric.template_name = ucd.template_name
  AND uric.agent = ucd.agent
  AND uric.card_id = ucd.card_id
  AND rtrim(fie.flight_no(+)) = uric.flight_no
  AND rtrim(fie.flight_date(+)) = uric.flight_date
  AND uric.uld_id_code IN (SELECT *
                           FROM TABLE(CAST(id_list_table AS uld_id_list_tab)) id_list)
  ORDER BY uric.uld_id_code;



END uld_cards_report;


/*******************************************************************************
*
*   PROCEDURE ULD_CARDS_REPORT_CORE
*
*******************************************************************************/

PROCEDURE uld_cards_report_core(p_uld_ref_cur IN OUT sys_refcursor,
                                p_ULD_str1    IN     VARCHAR2,
                                p_ULD_str2    IN     VARCHAR2,
                                p_ULD_str3    IN     VARCHAR2,
                                p_ULD_str4    IN     VARCHAR2,
                                p_ULD_str5    IN     VARCHAR2,
                                p_ULD_str6    IN     VARCHAR2,
                                p_ULD_str7    IN     VARCHAR2,
                                p_ULD_str8    IN     VARCHAR2,
                                p_ULD_str9    IN     VARCHAR2,
                                p_ULD_str10   IN     VARCHAR2)
IS

  --Initialise varray to hold list of ULD IDs passed in
  id_list_table   uld_id_list_tab := uld_id_list_tab();

BEGIN

  --For each of the 10 ULD ID strings passed in (p_str1..p_str10)
  --call load_tab to extract each comma sperated value into
  --a single column arrayload varray with the id codes from the 10 parameters
  load_tab(p_ULD_str1,
           id_list_table);

  load_tab(p_ULD_str2,
           id_list_table);

  load_tab(p_ULD_str3,
           id_list_table);

  load_tab(p_ULD_str4,
           id_list_table);

  load_tab(p_ULD_str5,
           id_list_table);

  load_tab(p_ULD_str6,
           id_list_table);

  load_tab(p_ULD_str7,
           id_list_table);

  load_tab(p_ULD_str8,
           id_list_table);

  load_tab(p_ULD_str9,
           id_list_table);

  load_tab(p_ULD_str10,
           id_list_table);


  --Now select the data required for the selected ULD ID codes
  --This select statement replicates the exact data returned by querying
  --the view RECORDED_ULD_INFO_V1 but only selects data for the ULD IDs chosen
  OPEN p_uld_ref_cur FOR
   SELECT a.uld_id_code
     , a.uld_no
     , decode (a.uld_category, 'S', 'SORT',  Nvl(a.dest6,nvl (a.dest5,
                                                          nvl (a.dest4,
                                                           nvl (a.dest3,
                                                            nvl (a.dest2, a.dest1)))))) DEST
     , b.loaded_at
     , a.flight_no        LOADED_AT_FLIGHT
     , b.transfer_at
     , a.onward_flight_no1 TRANSFER_AT_FLIGHT
     , a.uld_category
     , a.uld_type
     , a.flight_date
     , a.contents_remarks
     , a.remarks
     , a.onward_flight_no1 OFN_1
     , a.dest1 OFD_1
     , a.onward_flight_no2 OFN_2
     , a.dest2 OFD_2
     , a.onward_flight_no3 OFN_3
     , a.dest3 OFD_3
     , a.onward_flight_no4 OFN_4
     , a.dest4 OFD_4
     , a.onward_flight_no5 OFN_5
     , a.dest5 OFD_5
     , a.onward_flight_no6 OFN_6
     , a.dest6 OFD_6
   FROM  uld_recorded_id_codes a,
         uld_card_details b
  WHERE   a.template_name = b.template_name
    AND   a.agent         = b.agent
    AND   a.card_id       = b.card_id
    AND   a.uld_id_code IN (SELECT *
                              FROM TABLE(CAST(id_list_table AS uld_id_list_tab)) id_list)
  ORDER BY a.uld_id_code;

END uld_cards_report_core;

END uld_cards;
/
--CA2159 Call script to create type required by proc uld_cards_report_core
@@uld_id_list_tab.sql
