/*****************************************************************************
*
* Filename         - $Workfile:   pre_clear_bag.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, 2004
*
*****************************************************************************/
PROMPT CREATE OR REPLACE PROCEDURE pre_clear_bag

CREATE OR REPLACE PROCEDURE pre_clear_bag (  p_user_id       IN  tagtrak_users.user_id%TYPE
                              ,p_scanner_id    IN  VARCHAR2
                              ,p_location_code IN  location.location_code%TYPE
                              ,p_bag_tag       IN  bag.bag_tag%TYPE
                              ,p_retval        OUT NUMBER)
AS
/*******************************************************************************
 *
 * Procedure            - pre_clear_bag
 *
 * Description          - This procedure identifies which bags to separately
 *                        transfer and have been cleared to load from
 *                        those bags which have not, regardless of their
 *                        onwards flight. This procedure also records that a
 *                        bag has been seen and its location.
 * CA History:
 * Date         Name        CA Number           CA Description
 ******************************************************************************/

   -- Decalrations
   l_process_name   oracle_error.process_name%TYPE := 'TSS_PRE_CLEAR_BAG';
   l_error_msg      oracle_error.errror_msg%TYPE   := NULL;
   l_error_code     oracle_error.error_code%TYPE   := NULL;
   l_error_time     oracle_error.error_time%TYPE   := NULL;

   -- Local constants
   YES                  CONSTANT NUMBER(1)   DEFAULT 1;
   DEBUG                CONSTANT VARCHAR2(1) DEFAULT 'Y';
   NO                   CONSTANT NUMBER(1)   DEFAULT 0;
   VALID                CONSTANT VARCHAR2(1) DEFAULT 'Y';
   DENIED               CONSTANT VARCHAR2(1) DEFAULT 'N';

   -- General declarations
   l_location_code      location.location_code%TYPE  := p_location_code;
   l_user_id            tagtrak_users.user_id%TYPE   := p_user_id;
   l_scanner_id         kit.scanner_id%TYPE          := p_scanner_id;
   l_retval             NUMBER                       := 0;
   l_duplicate_retval   NUMBER                       := 0;
   l_error_message      VARCHAR2(512)                := NULL;
   l_error_number       NUMBER                       := NULL;
   l_retcode            NUMBER                       := 0;

   -- Bag varibales
   l_bag_bag_tag        bag.bag_tag%TYPE             := p_bag_tag;
   l_bag_bag_tag1       bag.bag_tag1%TYPE            := 0;
   l_bag_bag_tag2a      bag.bag_tag2a%TYPE           := 0;
   l_bag_bag_tag2b      bag.bag_tag2b%TYPE           := 0;
   l_bag_bag_tag3       bag.bag_tag3%TYPE            := 0;
   l_bag_pax_id         bag.pax_id%TYPE              := 0;

   l_bag_uld_no         bag.uld_no%TYPE              := NULL;    -- added ULD number (Nigel, 01/04/04, CA598)

   l_bag_bag_exception  bag.bag_exception%TYPE       := NULL;
   l_bag_loaded_status  bag.loaded_status%TYPE       := NULL;
   l_bag_location_code  bag.location_code%TYPE       := NULL;
   l_bag_scanner_id     bag.scanner_id%TYPE          := NULL;
   l_bag_dcs_bag_tag    bag.dcs_bag_tag%TYPE         := NULL;
   l_bag_exception_log  bag.bag_exception%TYPE       := NULL;
   l_bag_atl_brs        bag.authority_to_load_brs%TYPE  := NULL;
   l_bag_atl_dcs        bag.authority_to_load_dcs%TYPE  := NULL;
   l_res_unresolve_dups NUMBER                       := 0;
   l_res_bag_exception  NUMBER                       := 0;
   
   -- Pax variables
   l_pax_flight_id      pax.flight_id%TYPE           := 0;
   
   -- Bag tags
   l_tag1                               CHAR(1)                      := NULL;
   l_tag2a                        CHAR(3)                      := NULL;
   l_tag2b                              CHAR(3)                      := NULL;
   l_tag3                                 CHAR(6)                      := NULL;
   l_tagno_num          CHAR(10)                     := NULL;
   l_tag_type     NUMBER                       := NULL;
   l_total_bags   NUMBER                       := 0;

   -- Tagtrak_users variables
   l_loginname          tagtrak_users.loginname%TYPE := NULL;

   -- Flight variables
   l_flight_num         flight.flight_no%TYPE        := NULL;
   l_flight_date        flight.flight_date%TYPE      := NULL;
   l_flight_status      flight.flight_status%TYPE    := NULL;
   l_site_id            flight.site_id%TYPE          := NULL;
   l_flight_access_error NUMBER              := 0;

   -- app_priv variables
   l_preclear_bag_priv  VARCHAR2(10)                 := NULL;
   l_app_priv_name_preclear_bag     app_priv.app_priv_name%TYPE := 'TETHER_PreClear_bag';

   -- Type declarations
   TYPE bag_tag_tt       IS TABLE OF bag.bag_tag%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_tag1_tt      IS TABLE OF bag.bag_tag1%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_tag2a_tt     IS TABLE OF bag.bag_tag2a%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_tag2b_tt     IS TABLE OF bag.bag_tag2b%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_tag3_tt      IS TABLE OF bag.bag_tag3%TYPE INDEX BY BINARY_INTEGER;
   TYPE pax_id_tt        IS TABLE OF bag.pax_id%TYPE INDEX BY BINARY_INTEGER;

   TYPE uld_no_tt        IS TABLE OF bag.uld_no%TYPE INDEX BY BINARY_INTEGER;    -- added type for table of ULD numbers
                                                                                 -- (Nigel, 01/04/04, CA598)

   TYPE bag_exception_tt IS TABLE OF bag.bag_exception%TYPE INDEX BY BINARY_INTEGER;
   TYPE loaded_status_tt IS TABLE OF bag.loaded_status%TYPE INDEX BY BINARY_INTEGER;
   TYPE location_code_tt IS TABLE OF bag.location_code%TYPE INDEX BY BINARY_INTEGER;
   TYPE scanner_id_tt    IS TABLE OF bag.scanner_id%TYPE INDEX BY BINARY_INTEGER;
   TYPE dcs_bag_tag_tt   IS TABLE OF bag.dcs_bag_tag%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_exception_t  IS TABLE OF bag.bag_tag%TYPE INDEX BY BINARY_INTEGER;
   TYPE pax_flight_id_tt IS TABLE OF pax.flight_id%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_atl_brs_tt   IS TABLE OF bag.authority_to_load_brs%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_atl_dcs_tt   IS TABLE OF bag.authority_to_load_dcs%TYPE INDEX BY BINARY_INTEGER;
   TYPE number_tt        IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  
   -- Index counters
   indx                  BINARY_INTEGER              := 0;
   dup_indx              BINARY_INTEGER              := 0;
   
   -- PL/SQL table declarations
   l_bag_tag_tab            bag_tag_tt;
   l_bag_tag1_tab           bag_tag1_tt;
   l_bag_tag2a_tab          bag_tag2a_tt;
   l_bag_tag2b_tab          bag_tag2b_tt;
   l_bag_tag3_tab           bag_tag3_tt;
   l_bag_pax_id_tab         pax_id_tt;

   l_bag_uld_no_tab         uld_no_tt;    -- added table of ULD numbers (Nigel, 01/04/04, CA598)

   l_bag_exception_tab      bag_exception_tt;
   l_bag_loaded_status_tab  loaded_status_tt;
   l_bag_location_code_tab  location_code_tt;
   l_bag_scanner_id_tab     scanner_id_tt;
   l_bag_dcs_bag_tag_tab    dcs_bag_tag_tt;
   l_bag_exception_log_tab  bag_exception_tt;
   l_pax_flight_id_tab      pax_flight_id_tt;
   
   l_res_unresolve_dups_tab number_tt;
   l_res_bag_exception_tab  number_tt;

   -- Exceptions
   error_flight_access        EXCEPTION;

   -- Cursor declarations
   -- Main cursor for checking the bag tags
   CURSOR c_check_bag_exists(  p_bag_tag   IN bag.bag_tag%TYPE)
   IS     SELECT  bag.bag_tag
                 ,bag.pax_id
                 ,bag.bag_exception
                 ,bag.loaded_status
                 ,bag.bag_tag1
                 ,bag.bag_tag2a
                 ,bag.bag_tag2b
                 ,bag.bag_tag3
                 ,decode(bag.location_code,'-',p_location_code,p_location_code)
                 ,bag.scanner_id
                 ,pax.flight_id
                 ,bag.uld_no    -- added ULD number (Nigel, 01/04/04, CA598)
          FROM   bag, pax
          WHERE  bag.bag_tag   = p_bag_tag
          AND    bag.delete_marker = '0'
          AND    pax.pax_id = bag.pax_id;
          
   -- Cursor to deal with duplicates
   CURSOR c_process_dups(  p_bag_tag   IN bag.bag_tag%TYPE)
   IS     SELECT  bag.bag_tag
                 ,bag.pax_id
                 ,bag.bag_exception
                 ,bag.loaded_status
                 ,bag.bag_tag1
                 ,bag.bag_tag2a
                 ,bag.bag_tag2b
                 ,bag.bag_tag3
                 ,decode(bag.location_code,'-',p_location_code,p_location_code)
                 ,bag.scanner_id
                 ,pax.flight_id
                 ,bag.uld_no    -- added ULD number (Nigel, 01/04/04, CA598)
          FROM   bag, pax
          WHERE  bag.bag_tag   = p_bag_tag
          AND    bag.delete_marker = '0'
          AND    pax.pax_id = bag.pax_id; 
 
BEGIN
    /* Firstly split the bag_tag to use bits of it.
     */
    IF (p_bag_tag IS NOT NULL) then
        split_tags (  tagno     => p_bag_tag
                     ,tag1      => l_tag1
                     ,tag2a     => l_tag2a
                     ,tag2b     => l_tag2b
                     ,tag3      => l_tag3
                     ,tagno_num => l_tagno_num
                     ,tag_type  => l_tag_type
                   );
    END IF; 
    
    /* Some debug information */
    IF ( DEBUG = VALID ) THEN
       dbms_output.put_line('----------------------------------');
       dbms_output.put_line('Results from Split Tag....');
       dbms_output.put_line('----------------------------------');
       dbms_output.put_line('tagno      :'||p_bag_Tag);
       dbms_output.put_line('tag1       :'||l_tag1);
       dbms_output.put_line('tag2a      :'||l_tag2a);
       dbms_output.put_line('tag2b      :'||l_tag2b);
       dbms_output.put_line('tag3       :'||l_tag3);
       dbms_output.put_line('tagno_num  :'||l_tagno_num);
       dbms_output.put_line('tag_type   :'||l_tag_type);
       dbms_output.put_line('l_user_id  :'||l_user_id);
       dbms_output.put_line('----------------------------------');
    END IF; 

    
     dbms_output.put_line('Test');
    /* Check bag initially */
     OPEN c_check_bag_exists( p_bag_tag );
     LOOP
     FETCH  c_check_bag_exists 
     INTO    l_bag_bag_tag
            ,l_bag_pax_id
            ,l_bag_bag_exception
            ,l_bag_loaded_status
            ,l_bag_bag_tag1
            ,l_bag_bag_tag2a
            ,l_bag_bag_tag2b
            ,l_bag_bag_tag3
            ,l_bag_location_code
            ,l_bag_scanner_id
            ,l_pax_flight_id
            ,l_bag_uld_no;    -- added ULD number (Nigel, 01/04/04, CA598)
     EXIT WHEN c_check_bag_exists%NOTFOUND;
     
     /* Some debug information */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('----------------------------------');
        dbms_output.put_line('After feching c_check_bag_exists..');
        dbms_output.put_line('----------------------------------');
        dbms_output.put_line('l_bag_bag_tag       :'||l_bag_bag_tag);
        dbms_output.put_line('l_bag_pax_id        :'||l_bag_pax_id);
        dbms_output.put_line('l_pax_flight_id     :'||l_pax_flight_id);
        dbms_output.put_line('l_bag_bag_exception :'||l_bag_bag_exception);
        dbms_output.put_line('rowcount            :'||c_check_bag_exists%ROWCOUNT);
        dbms_output.put_line('----------------------------------');
     END IF;
     
     END LOOP;
     
     /* Trap unknown bag_tags i.e. like scanning a ULD 
      * these are return codes not whats logged in the trace file */
     IF ( c_check_bag_exists%NOTFOUND AND 
          c_check_bag_exists%ROWCOUNT = 0 ) THEN
          dbms_output.put_line('Unknown tag, hence bsm not found');
          l_retval := -1002; -- no_bsm_found
          
     ELSIF c_check_bag_exists%ROWCOUNT > 1 THEN  -- duplicates
           dbms_output.put_line('Duplicate Tag');
           l_retval := -1003;  -- need further refining for bag_exceptions
         
     ELSIF c_check_bag_exists%ROWCOUNT = 1 THEN  -- normal
        IF ( l_bag_bag_exception IS NOT NULL ) THEN
             dbms_output.put_line('Exception Bag');
             l_retval := pkg_constants.ERROR_EXCEPTION_BAG;   -- bag exception
            
        ELSE
             dbms_output.put_line('BSM Found');               -- bsm found
             l_retval := 0;
           
        END IF;
     END IF;
  
     -- Close cursor
     CLOSE c_check_bag_exists;
     
     /* Now process bag_tag if this is a duplicate tag */    
     IF (l_retval = -1003) THEN
        OPEN c_process_dups( p_bag_tag);
        LOOP
        FETCH  c_process_dups 
        INTO    l_bag_tag_tab(indx)
               ,l_bag_pax_id_tab(indx)
               ,l_bag_exception_tab(indx)
               ,l_bag_loaded_status_tab(indx)
               ,l_bag_tag1_tab(indx)
               ,l_bag_tag2a_tab(indx)
               ,l_bag_tag2b_tab(indx)
               ,l_bag_tag3_tab(indx)
               ,l_bag_location_code_tab(indx)
               ,l_bag_scanner_id_tab(indx)
               ,l_pax_flight_id_tab(indx)
               ,l_bag_uld_no_tab(indx);    -- added ULD number (Nigel, 01/04/04, CA598)
     
         EXIT WHEN c_process_dups%NOTFOUND;
         /* Set l_res_unresolve_dups if this is a normal duplicate */
         /* We already know this is a duplicate bag, but need to separate */
         /* Same flights dups with different flight dups. */
         /* l_res_unresolve_dups_tab = 1 if normal duplicate */
         IF l_pax_flight_id_tab(0) != l_pax_flight_id_tab(indx) THEN
            l_res_unresolve_dups_tab(indx) := 1;
         END IF;
         
         /* If any of the duplicates are a exception bag */
         IF l_bag_exception_tab(indx) IS NOT NULL THEN
            l_res_bag_exception_tab(indx)  := 1;
         END IF;
         
         /* Deal with authority to load issues */
        
         indx := indx + 1;
        END LOOP;
        
        /* Now deal with the data, checking for un-resolvable or duplicate tag */
        IF l_bag_pax_id_tab.COUNT > 0 THEN
           
           /* Need to set one value */
           IF l_res_unresolve_dups_tab.COUNT > 0 THEN
           FOR dup_indx IN l_res_unresolve_dups_tab.FIRST..l_res_unresolve_dups_tab.LAST LOOP
               IF l_res_unresolve_dups_tab(dup_indx) = 1 THEN
                  l_res_unresolve_dups := 1;
               END IF;
               
           END LOOP;
           END IF;
           
           /* Need to set at least one */
           IF l_res_bag_exception_tab.COUNT > 0 THEN
           FOR dup_indx IN l_res_bag_exception_tab.FIRST..l_res_bag_exception_tab.LAST LOOP
               IF l_res_bag_exception_tab(dup_indx) = 1 THEN
                  l_res_bag_exception := 1;
               END IF;
           END LOOP; 
           END IF;
           
           
           FOR indx IN l_bag_pax_id_tab.FIRST..l_bag_pax_id_tab.LAST LOOP
           /* Set a flag if this is an unresolvale */
           IF ( DEBUG = VALID ) THEN
             dbms_output.put_line('----------------------------------');
             dbms_output.put_line('Are these dups / Exceptions...');
             dbms_output.put_line('----------------------------------');
             dbms_output.put_line('l_bag_pax_id_tab         :'||l_bag_pax_id_tab(indx));
             dbms_output.put_line('l_pax_flight_id_tab      :'||l_pax_flight_id_tab(indx));
             dbms_output.put_line('l_bag_bag_exception      :'||l_bag_exception_tab(indx));
             dbms_output.put_line('l_res_unresolve_dups     :'||l_res_unresolve_dups);
             dbms_output.put_line('l_res_bag_exception      :'||l_res_bag_exception);
             dbms_output.put_line('----------------------------------');
           END IF;
           
            /* Un-resolvable bags:l_res_unresolve = 0,  duplicate bag = 1 */
           IF (l_res_unresolve_dups = 1 AND l_res_bag_exception = 1) OR 
              (l_res_unresolve_dups = 0 AND l_res_bag_exception = 1) THEN
               l_duplicate_retval := pkg_constants.ERROR_EXCEPTION_BAG; -- duplicate_bag
           ELSIF l_res_unresolve_dups = 1 AND l_res_bag_exception = 0 THEN
               l_duplicate_retval := 0;
           ELSIF l_res_unresolve_dups = 0 AND l_res_bag_exception = 0 THEN
               l_duplicate_retval := 0;
           END IF;
          
           
           
           /* Some debug information for checking */
           IF ( DEBUG = VALID ) THEN
              dbms_output.put_line('----------------------------------');
              dbms_output.put_line('Before add_bag_trace_record...');
              dbms_output.put_line('----------------------------------');
              dbms_output.put_line('l_bag_bag_tag       :'||l_bag_tag_tab(indx));
              dbms_output.put_line('l_bag_pax_id        :'||l_bag_pax_id_tab(indx));
              dbms_output.put_line('l_bag_bag_exception :'||l_bag_exception_tab(indx));
              dbms_output.put_line('l_bag_loaded_status :'||l_bag_loaded_status_tab(indx));
              dbms_output.put_line('l_bag_bag_tag2b     :'||l_bag_tag2b_tab(indx));
              dbms_output.put_line('l_bag_bag_tag3      :'||l_bag_tag3_tab(indx));
              dbms_output.put_line('l_bag_location_code :'||l_bag_location_code_tab(indx));
              dbms_output.put_line('l_bag_scanner_id    :'||l_bag_scanner_id_tab(indx));
              dbms_output.put_line('l_pax_flight_id     :'||l_pax_flight_id_tab(indx));
              dbms_output.put_line('l_scanner_id        :'||l_scanner_id);
              dbms_output.put_line('l_duplicate_retval  :'||l_duplicate_retval);
              dbms_output.put_line('----------------------------------');
           END IF;
        
          /* Create the duplicate bag_trace record */
          add_bag_trace_record( l_user_id
                               ,l_location_code
                               ,l_duplicate_retval  -- error_code
                               ,l_bag_tag_tab(indx)
                               ,l_bag_tag1_tab(indx)
                               ,l_bag_tag2a_tab(indx)
                               ,l_bag_tag2b_tab(indx)
                               ,l_bag_tag3_tab(indx)
                               ,'PRE_CLEAR'       -- trace_action
                               ,l_scanner_id
                               ,l_pax_flight_id_tab(indx)   -- flight_id
                               ,l_bag_pax_id_tab(indx)      -- pax_id
                               ,l_bag_uld_no_tab(indx)      -- pass ULD number (Nigel, 01/04/04, CA598)
                               ); 
    
          END LOOP; -- l_bag_pax_id_tab 
          CLOSE c_process_dups;
        END IF; -- duplicate tags
       END IF; -- l_bag_pax_id_tab.COUNT
    
   
    /* send retval, if all has been successful then BSM_FOUND */
    IF l_retval = -1003 THEN
       p_retval := l_duplicate_retval;
    ELSE
       p_retval := l_retval;
    END IF;
    
    dbms_output.put_line('Final p_retval :'||p_retval);
    
    -- CA 709 Flight Access Changes (RA 22/6/2004)
    IF (l_retval != -1002) THEN -- unknown bag
      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_user_id, l_pax_flight_id, null, null, l_retcode);

      IF ( l_retcode < 0 ) THEN
        IF ( DEBUG = VALID ) THEN
           dbms_output.put_line('No Access to Flight ID '||l_pax_flight_id);
        END IF;

        l_retval := -1031; -- flight access denied;
        RAISE error_flight_access;
      END IF;
    END IF;
    -- End of Flight Access Changes

    /* IF this is an unknown bag, need to log this in the system */
    IF l_retval = -1002 THEN
       l_retval := -1336; -- pre clear not authorised.
       /* Also assign out value */
       p_retval := l_retval;
    END IF;
    
    /* Finally create the trace record, except for duplicate ones, since they have
     * already been created
     */
    IF l_retval != -1003 THEN
     /* Some debug infor */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('----------------------------------');
        dbms_output.put_line('Final Bag Trace Created');
        dbms_output.put_line('----------------------------------');
        dbms_output.put_line('l_user_id        :'|| l_user_id);
        dbms_output.put_line('l_location_code  :'||l_location_code);
        dbms_output.put_line('l_retval         :'||l_retval);     
        dbms_output.put_line('l_bag_bag_tag    :'||l_bag_bag_tag);
        dbms_output.put_line('l_bag_bag_tag1   :'||l_bag_bag_tag1);
        dbms_output.put_line('l_bag_bag_tag2a  :'||l_bag_bag_tag2a);
        dbms_output.put_line('l_bag_bag_tag2b  :'||l_bag_bag_tag2b);
        dbms_output.put_line('l_bag_bag_tag3   :'||l_bag_bag_tag3);
        dbms_output.put_line('l_scanner_id     :'||l_scanner_id);
        dbms_output.put_line('l_pax_flight_id  :'||l_pax_flight_id);
        dbms_output.put_line('l_bag_pax_id     :'||l_bag_pax_id);
        dbms_output.put_line('----------------------------------');
      END IF;
      
      /* Create the trace record */
      add_bag_trace_record( l_user_id
                           ,l_location_code
                           ,l_retval          -- error_code
                           ,l_bag_bag_tag
                           ,l_bag_bag_tag1
                           ,l_bag_bag_tag2a
                           ,l_bag_bag_tag2b
                           ,l_bag_bag_tag3
                           ,'PRE_CLEAR'       -- trace_action
                           ,l_scanner_id
                           ,l_pax_flight_id   -- flight_id
                           ,l_bag_pax_id      -- pax_id
                           ,l_bag_uld_no      -- pass ULD number (Nigel, 01/04/04, CA598)
                           );
     END IF;
 
 EXCEPTION     
   WHEN error_flight_access THEN
     /* Some debug information */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('---------------------------------------------------');
        dbms_output.put_line('User is not authorised to pre_clear_bags for flight');
     END IF;
        
     IF ( c_check_bag_exists%ISOPEN) THEN
       CLOSE c_check_bag_exists;
     END IF;
     
     IF ( c_process_dups%ISOPEN ) THEN
          CLOSE c_process_dups;
     END IF;
     
     /* Assign general error */
     l_retval := -1031; -- flight access denied
     p_retval := l_retval;
     
   WHEN OTHERS THEN
     /* Close cursor */   
     IF ( c_check_bag_exists%ISOPEN) THEN
          CLOSE c_check_bag_exists;
     END IF;
     IF ( c_process_dups%ISOPEN ) THEN
          CLOSE c_process_dups;
     END IF;
     /* Assign general error */
         l_retval := -1089; -- general exception
     p_retval := l_retval;
          
     /* General catch all error, inserts into Oracle_error table */
     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);
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('------------------------------------');
        dbms_output.put_line('When others error: '||l_error_code||'-'||l_error_msg);
     END IF;

    
END pre_clear_bag;
/
