/*****************************************************************************
*
* Filename         - $Workfile:   tss_create_uld.sql  $
*
*                     Name         Date       Version     CR Number
*                    Peter Charalambous  01/12/2003
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       -
*
* Design Spec
* Section          -  FAS/DUBRS/SD/245
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
PROMPT CREATE OR REPLACE PROCEDURE tss_create_uld

CREATE OR REPLACE PROCEDURE tss_create_uld
   (
    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_site_id             IN  uld_recorded_id_codes.site_id%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_uld_id_code         IN  uld_recorded_id_codes.uld_id_code%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_flight_id           OUT ulds.flight_id%TYPE,
    p_retcode             OUT VARCHAR2
    ) AS

 /******************************************************************************
 *
 * Procedure            - tss_create_uld
 *
 * Description          - This procedure creates a uld if it doesn't exists
 *                        or updates the class and destination values of the
 *                        uld
 * CA History:
 * Date         Name        CA Number           CA Description
 ******************************************************************************/


   -- Constant declarations
   l_process_name    oracle_error.process_name%TYPE     := 'TSS_CREATE_ULD';
   l_error_msg       oracle_error.errror_msg%TYPE       := NULL;
   l_error_time      oracle_error.error_time%TYPE       := NULL;
   l_error_code      oracle_error.error_code%TYPE       := NULL;

   DEBUG             CONSTANT VARCHAR2(1) DEFAULT 'Y';
   VALID             CONSTANT VARCHAR2(1) DEFAULT 'Y';

   l_uld_id_code     uld_recorded_id_codes.uld_id_code%type;

   -- Ulds declarations
   l_uld_type      uld_recorded_id_codes.uld_type%TYPE  := p_uld_type;

   l_siteid        site_info.site_id%TYPE               := NULL;
   l_fid           flight.flight_id%TYPE                := 0;
   l_flts          flight.flight_status%TYPE            := NULL;
   l_conn_type     ulds.connection_type%TYPE            := ' ';
   l_category      ulds.uld_category%TYPE               := NULL;
   l_uldno         ulds.uld_no%TYPE                     := NULL;
   l_loc           location.location_code%type          := NULL;
   l_userid        tagtrak_users.user_id%type           := NULL;
   l_valid_dest    BOOLEAN;

   -- Counter and miscellaneous variables
   l_ret             NUMBER                             := 0;
   insert_flag       NUMBER                             := 0;

   -- Onward Declarations
   l_dest                  uld_cards.dest_list_type;
   l_class                 uld_cards.class_list_type;
   l_onwd_flight_no        uld_cards.onward_flight_no_type;
   l_onwd_flight_date      uld_cards.onward_flight_date_type;

   -- Return variables
   l_result  VARCHAR2(100) := NULL;

   -- Exceptions
   abort     EXCEPTION;

BEGIN
   -- Set retcode
   p_retcode   := 0;

   -- Set variables
   l_conn_type := p_conn_type;
   l_category  := p_uld_category;
   l_uldno     := p_uld_no;
   l_loc       := p_loc;
   l_userid    := p_userid;

   -- populate destination and class list table
   uld_cards.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);

   -- Get site id for p_departing_airport
   l_siteid  := p_site_id;

   --Validate flight_details
   IF ( DEBUG = VALID ) THEN
      dbms_output.put_line('-----------------------------------');
      dbms_output.put_line('Getting flight details');
      dbms_output.put_line('-----------------------------------');

   END IF;

   flight_id( p_flight_no
             ,p_flight_date
             ,l_siteid
             ,l_fid
             );
   -- Trap unknown flight errors
   IF( l_fid = 0 ) THEN
       l_result := pkg_constants.ERROR_UNKNOWN_FLIGHT; --(-139)
       RAISE abort;
   END IF;

   -- Got a flight
   p_flight_id := l_fid;

   --Validate flight status for flight
   IF ( DEBUG = VALID ) THEN
      dbms_output.put_line('-----------------------------------');
      dbms_output.put_line('Getting flight_status.');
      dbms_output.put_line('-----------------------------------');
   END IF;

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

       IF (l_flts IN ('PD')) THEN
          l_result := pkg_constants.ERROR_FLIGHT_STATUS_PD; --(-208);
          RAISE abort;
       ELSIF ( l_flts = pkg_constants.FLIGHT_CLOSED) THEN
          l_result := pkg_constants.ERROR_FLIGHT_CLOSED;
          RAISE abort;
       END IF;
   END IF;

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

   --Validate user access to flight
   IF ( DEBUG = VALID ) THEN
      dbms_output.put_line('-----------------------------------');
      dbms_output.put_line('Validating user access to flight..');
      dbms_output.put_line('-----------------------------------');
   END IF;

   user_authentication.validate_access(l_userid,l_fid,p_flight_no,p_flight_date,l_ret);
   IF( l_ret < 0 ) THEN
       l_result:= pkg_constants.ERROR_FLIGHT_ACCESS_DENIED; --(-1031);
       RAISE abort;
   END IF;

   /****************************************************************************
    * Validate ULD Number to ensure it is of correct format
    ****************************************************************************
    */
    IF (NOT uld_cards.validate_uldno(l_uldno)) THEN
       l_result := pkg_constants.ERROR_UNKNOWN_ULD;
       RAISE abort;
    END IF;


   /***************************************************************
    * Check the uld to see whether it already exists
    * and create it if it doesn't. If it already does exist
    * then update class and destination details.
    ***************************************************************
    */

   -- 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_uldno;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         insert_flag  := 1;
      WHEN OTHERS THEN
          p_retcode := pkg_constants.error_uld_id;
          RAISE abort;
   END;

   uld_cards.register_uld(l_uldno,    l_fid,              l_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_result);


   -- Save the transaction
   COMMIT;

EXCEPTION
     WHEN abort THEN
          IF ( DEBUG = VALID ) THEN
             dbms_output.put_line('-----------------------------------');
             dbms_output.put_line('Error results ....:'||l_result);
             dbms_output.put_line('-----------------------------------');
          END IF;

          -- Error result returned
          p_retcode := l_result;

     WHEN OTHERS THEN
              /* Assign General error */
          l_result := pkg_constants.ERROR_GENERAL_EXCEPTION;
          p_retcode := l_result;

                  /* Create a trace record of error */
          l_error_msg  := SQLERRM;
          l_error_code := SQLCODE;
          l_error_time := SYSDATE;

          INSERT INTO oracle_error ( process_name
                                    ,errror_msg
                                    ,error_code
                                    ,error_time
                                    )
                             VALUES( l_process_name
                                    ,l_error_msg
                                    ,l_error_code
                                    ,l_error_time);
          -- Error general exception
          IF ( DEBUG = VALID ) THEN
             dbms_output.put_line('------------------------------------');
             dbms_output.put_line('When others error: '||l_error_code||'-'||l_error_msg);
          END IF;


END tss_create_uld;
/
