Saturday, August 7, 2010

xxdc_p2_conv_util

CREATE OR REPLACE PACKAGE BODY APPS.XXDC_P2_CONV_UTIL
IS
   PROCEDURE gen_pre_validation (p_conv_id IN NUMBER)
   IS
      TYPE cur_type IS REF CURSOR;

      exp_records        cur_type;
      x_rowid            UROWID;
      x_key              VARCHAR2 (255);
      x_src_file_name    VARCHAR2 (255);
      sql_stmt           VARCHAR2 (4000);
      --p_conv_id         NUMBER (15)     := 10;
      upd_sql_stmt       VARCHAR2 (4000);
      err_sql_stmt       VARCHAR2 (4000);
      x_err_msg          VARCHAR2 (4000);
      x_process_status   VARCHAR2 (2)    := '-1';
      l_upd_sql          VARCHAR (4000);

      CURSOR get_process_status (l_conv_id IN NUMBER)
      IS
         SELECT DISTINCT process_status
                    FROM xxdc_p2_conv_rules
                   WHERE conv_id = l_conv_id
                     AND process_status NOT IN ('E', 'UT')
                     AND rule_id <> -100;

      --ORDER BY 1 DESC;
      CURSOR get_data_validation_rules (
         l_conv_id          IN   NUMBER,
         l_process_status   IN   VARCHAR
      )
      IS
         SELECT rule_id, table_name, rule_name, sql_code, process_status
           FROM xxdc_p2_conv_rules
          WHERE conv_id = l_conv_id
            AND process_status = l_process_status
            AND rule_id <> -100;

      CURSOR get_update_sql (l_conv_id IN NUMBER)
      IS
         SELECT rule_id, table_name, rule_name, sql_code
           FROM xxdc_p2_conv_rules
          WHERE conv_id = l_conv_id AND process_status = 'UT';
   BEGIN
      FOR get_update_sql_rec IN get_update_sql (p_conv_id)
      LOOP
         DBMS_OUTPUT.put_line ('rule_name - ' || get_update_sql_rec.rule_name);
         l_upd_sql := get_update_sql_rec.sql_code;
         DBMS_OUTPUT.put_line ('Update sql_stmt  l_upd_sql - ' || l_upd_sql);
      END LOOP;

      FOR get_process_status_rec IN get_process_status (p_conv_id)
      LOOP
         FOR get_data_validation_rules_rec IN
            get_data_validation_rules (p_conv_id,
                                       get_process_status_rec.process_status
                                      )
         LOOP
            DBMS_OUTPUT.put_line (   'rule_name - '
                                  || get_data_validation_rules_rec.rule_name
                                 );
            DBMS_OUTPUT.put_line
                               (   'get_data_validation_rules_rec.sql_code - '
                                || get_data_validation_rules_rec.sql_code
                               );
            DBMS_OUTPUT.put_line
                             (   'get_data_validation_rules_rec.table_name - '
                              || get_data_validation_rules_rec.table_name
                             );
            sql_stmt := get_data_validation_rules_rec.sql_code;
            DBMS_OUTPUT.put_line ('<><><><> sql_stmt <><><> ');
            DBMS_OUTPUT.put_line (sql_stmt);
            DBMS_OUTPUT.put_line ('<><><><><><><><><><><><> ');
            DBMS_OUTPUT.put_line ('Use of cursor in selecting exception rows');

            OPEN exp_records FOR sql_stmt;

            LOOP
               FETCH exp_records
                INTO x_rowid, x_key, x_err_msg;

               EXIT WHEN exp_records%NOTFOUND;
               DBMS_OUTPUT.put_line (   'Conv Key - '
                                     || x_key
                                     || ' x_rowid - '
                                     || x_rowid
                                     || '  x_err_msg - '
                                     || x_err_msg
                                    );

               INSERT INTO xxdc.xxdc_p2_conv_errors
                           (conv_id, rule_id,
                            request_id, conv_key, error_msg,
                            created_by, creation_date, last_updated_by,
                            last_update_date
                           )
                    VALUES (p_conv_id, get_data_validation_rules_rec.rule_id,
                            fnd_global.conc_request_id, x_key, x_err_msg,
                            fnd_global.user_id, SYSDATE, fnd_global.user_id,
                            SYSDATE
                           );

               DBMS_OUTPUT.put_line ('After Insert');
               --'Total Collected and Orginal Amounts are null');
               upd_sql_stmt :=
                     'UPDATE '
                  || get_data_validation_rules_rec.table_name
                  || ' SET process_status = '
                  || 'decode (process_status , ''RS'', ''RS'', ''RD'', ''RD'','''
                  || get_data_validation_rules_rec.process_status
                  || ''') '
                  || ' where rowid = :1';
               DBMS_OUTPUT.put_line (' upd_sql_stmt - ' || upd_sql_stmt);

               EXECUTE IMMEDIATE upd_sql_stmt
                           USING x_rowid;

               DBMS_OUTPUT.put_line (' After first update sql ');
               DBMS_OUTPUT.put_line (' l_upd_sql - ' || l_upd_sql);
               DBMS_OUTPUT.put_line
                        (   ' get_data_validation_rules_rec.process_status - '
                         || get_data_validation_rules_rec.process_status
                        );
               DBMS_OUTPUT.put_line ('x_key - ' || x_key);

               IF (l_upd_sql IS NOT NULL)
               THEN
                  EXECUTE IMMEDIATE l_upd_sql
                              USING
                                    get_data_validation_rules_rec.process_status,
                                    x_key;

                  DBMS_OUTPUT.put_line ('after execute update ');
               END IF;
            END LOOP;
         END LOOP;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLCODE);
         DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLERRM);
   END gen_pre_validation;

   PROCEDURE gen_datatype_validation (p_conv_id IN NUMBER)
   IS
      v_data_type        VARCHAR2 (50);
      v_data_length      NUMBER;
      v_rowid            ROWID;
      v_row_data         VARCHAR2 (255);
      v_date             DATE;
      v_number           NUMBER;

      TYPE cur_type IS REF CURSOR;

      src_records        cur_type;
      v_sql_stmt         VARCHAR2 (4000);
      v_upd_sql_stmt     VARCHAR2 (4000);
      v_insrt_sql_stmt   VARCHAR2 (4000);

      CURSOR get_map_data (v_conv_id NUMBER)
      IS
         SELECT *
           FROM xxdc_p2_conv_table_column_map
          WHERE conv_id = v_conv_id AND validate_flag = 'Y';

      CURSOR get_sql             -- (v_conv_id NUMBER, v_table_name VARCHAR2)
      IS
         SELECT *
           FROM xxdc_p2_conv_rules
          WHERE rule_id = 0;
   BEGIN
      FOR get_map_data_rec IN get_map_data (p_conv_id)
      LOOP
         DBMS_OUTPUT.put_line ('******************************************* ');
         DBMS_OUTPUT.put_line (   'get_map_data_rec.TARGET_TABLE_NAME - '
                               || get_map_data_rec.target_table_name
                              );
         DBMS_OUTPUT.put_line (   'get_map_data_rec.TARGET_COLUMN_NAME -  '
                               || get_map_data_rec.target_column_name
                              );

         SELECT data_type, data_length
           INTO v_data_type, v_data_length
           FROM all_tab_columns
          WHERE table_name = UPPER (get_map_data_rec.target_table_name)
            AND column_name = UPPER (get_map_data_rec.target_column_name);

         DBMS_OUTPUT.put_line (' v_data_type - ' || v_data_type);
         DBMS_OUTPUT.put_line (' v_data_length -' || v_data_length);

         IF (v_data_type = 'VARCHAR2')
         THEN
            v_sql_stmt :=
                  'SELECT ROWID FROM '
               || get_map_data_rec.source_table_name
               || ' WHERE length( '
               || get_map_data_rec.source_column_name
               || ' ) > '
               || v_data_length;
            DBMS_OUTPUT.put_line ('v_sql_stmt -  ' || v_sql_stmt);

            OPEN src_records FOR v_sql_stmt;

            LOOP
               FETCH src_records
                INTO v_rowid;

               EXIT WHEN src_records%NOTFOUND;

               FOR get_sql_rec IN get_sql
               LOOP
                  DBMS_OUTPUT.put_line (' In varchar2 exception ');
                  DBMS_OUTPUT.put_line ('v_rowid -  ' || v_rowid);
                  v_upd_sql_stmt :=
                        'UPDATE '
                     || get_map_data_rec.source_table_name
                     || ' SET process_status = '''
                     || get_sql_rec.process_status
                     || ''' where rowid = :1';
                  DBMS_OUTPUT.put_line (' v_upd_sql_stmt - ' || v_upd_sql_stmt);

                  EXECUTE IMMEDIATE v_upd_sql_stmt
                              USING v_rowid;

                  v_insrt_sql_stmt := get_sql_rec.sql_code;
                  DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                        || v_insrt_sql_stmt
                                       );
                  v_insrt_sql_stmt :=
                     REPLACE (v_insrt_sql_stmt,
                              'tab_name',
                              get_map_data_rec.source_table_name
                             );
                  DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                        || v_insrt_sql_stmt
                                       );

                  EXECUTE IMMEDIATE v_insrt_sql_stmt
                              USING p_conv_id,
                                    get_sql_rec.rule_id,
                                    get_map_data_rec.pk_column_name,
                                    get_map_data_rec.source_column_name,
                                    get_map_data_rec.source_column_name,
                                    v_data_type,
                                    get_map_data_rec.source_table_name,
                                    v_rowid;

                  DBMS_OUTPUT.put_line ('after execute update ');
               END LOOP;
            END LOOP;
         ELSIF (v_data_type = 'DATE')
         THEN
            v_sql_stmt :=
                  'SELECT ROWID, '
               || get_map_data_rec.source_column_name
               || ' FROM '
               || get_map_data_rec.source_table_name;
            DBMS_OUTPUT.put_line ('v_sql_stmt -  ' || v_sql_stmt);

            OPEN src_records FOR v_sql_stmt;

            LOOP
               FETCH src_records
                INTO v_rowid, v_row_data;

               EXIT WHEN src_records%NOTFOUND;

               BEGIN
                  v_date := TO_DATE (v_row_data, 'MM/DD/YYYY HH24:MI:SS');
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     DBMS_OUTPUT.put_line (' In to_date exception ');
                     DBMS_OUTPUT.put_line ('v_rowid -  ' || v_rowid);
                     DBMS_OUTPUT.put_line ('v_row_data - ' || v_row_data);

                     FOR get_sql_rec IN get_sql
                     LOOP
                        v_upd_sql_stmt :=
                              'UPDATE '
                           || get_map_data_rec.source_table_name
                           || ' SET process_status = '''
                           || get_sql_rec.process_status
                           || ''' where rowid = :1';
                        DBMS_OUTPUT.put_line (   ' v_upd_sql_stmt - '
                                              || v_upd_sql_stmt
                                             );

                        EXECUTE IMMEDIATE v_upd_sql_stmt
                                    USING v_rowid;

                        v_insrt_sql_stmt := get_sql_rec.sql_code;
                        DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                              || v_insrt_sql_stmt
                                             );
                        DBMS_OUTPUT.put_line ('p_conv_id - ' || p_conv_id);
                        DBMS_OUTPUT.put_line (   'get_sql_rec.rule_id - '
                                              || get_sql_rec.rule_id
                                             );
                        DBMS_OUTPUT.put_line
                                      (   'get_map_data_rec.pk_column_name - '
                                       || get_map_data_rec.pk_column_name
                                      );
                        DBMS_OUTPUT.put_line
                                  (   'get_map_data_rec.source_column_name - '
                                   || get_map_data_rec.source_column_name
                                  );
                        DBMS_OUTPUT.put_line ('v_data_type - ' || v_data_type);
                        DBMS_OUTPUT.put_line
                                   (   'get_map_data_rec.source_table_name - '
                                    || get_map_data_rec.source_table_name
                                   );
                        DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
                        DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                              || v_insrt_sql_stmt
                                             );
                        v_insrt_sql_stmt :=
                           REPLACE (v_insrt_sql_stmt,
                                    'tab_name',
                                    get_map_data_rec.source_table_name
                                   );
                        DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                              || v_insrt_sql_stmt
                                             );

                        EXECUTE IMMEDIATE v_insrt_sql_stmt
                                    USING
                                          p_conv_id,
                                          get_sql_rec.rule_id,
                                          get_map_data_rec.pk_column_name,
                                          get_map_data_rec.source_column_name,
                                          get_map_data_rec.source_column_name,
                                          v_data_type,
                                          --get_map_data_rec.source_table_name,
                                          v_rowid;

                        DBMS_OUTPUT.put_line ('after execute update ');
                     END LOOP;
               END;
            END LOOP;
         ELSIF (v_data_type = 'NUMBER')
         THEN
            v_sql_stmt :=
                  'SELECT ROWID, '
               || get_map_data_rec.source_column_name
               || ' FROM '
               || get_map_data_rec.source_table_name;
            DBMS_OUTPUT.put_line ('v_sql_stmt -  ' || v_sql_stmt);

            OPEN src_records FOR v_sql_stmt;

            LOOP
               FETCH src_records
                INTO v_rowid, v_row_data;

               EXIT WHEN src_records%NOTFOUND;

               BEGIN
                  v_number := TO_NUMBER (v_row_data);
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     DBMS_OUTPUT.put_line (' In to_number exception ');
                     DBMS_OUTPUT.put_line ('v_rowid -  ' || v_rowid);
                     DBMS_OUTPUT.put_line ('v_row_data - ' || v_row_data);

                     FOR get_sql_rec IN get_sql
                     LOOP
                        v_upd_sql_stmt :=
                              'UPDATE '
                           || get_map_data_rec.source_table_name
                           || ' SET process_status = '''
                           || get_sql_rec.process_status
                           || ''' where rowid = :1';
                        DBMS_OUTPUT.put_line (   ' v_upd_sql_stmt - '
                                              || v_upd_sql_stmt
                                             );

                        EXECUTE IMMEDIATE v_upd_sql_stmt
                                    USING v_rowid;

                        DBMS_OUTPUT.put_line ('p_conv_id - ' || p_conv_id);
                        DBMS_OUTPUT.put_line (   'get_sql_rec.rule_id - '
                                              || get_sql_rec.rule_id
                                             );
                        DBMS_OUTPUT.put_line
                                      (   'get_map_data_rec.pk_column_name - '
                                       || get_map_data_rec.pk_column_name
                                      );
                        DBMS_OUTPUT.put_line
                                  (   'get_map_data_rec.source_column_name - '
                                   || get_map_data_rec.source_column_name
                                  );
                        DBMS_OUTPUT.put_line ('v_data_type - ' || v_data_type);
                        DBMS_OUTPUT.put_line
                                   (   'get_map_data_rec.source_table_name - '
                                    || get_map_data_rec.source_table_name
                                   );
                        DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
                        v_insrt_sql_stmt := get_sql_rec.sql_code;
                        DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                              || v_insrt_sql_stmt
                                             );
                        v_insrt_sql_stmt :=
                           REPLACE (v_insrt_sql_stmt,
                                    'tab_name',
                                    get_map_data_rec.source_table_name
                                   );
                        DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                              || v_insrt_sql_stmt
                                             );

                        EXECUTE IMMEDIATE v_insrt_sql_stmt
                                    USING
                                          p_conv_id,
                                          get_sql_rec.rule_id,
                                          get_map_data_rec.pk_column_name,
                                          get_map_data_rec.source_column_name,
                                          get_map_data_rec.source_column_name,
                                          v_data_type,
                                          --get_map_data_rec.source_table_name,
                                          v_rowid;

                        DBMS_OUTPUT.put_line ('after execute update ');
                     END LOOP;
               END;
            END LOOP;
         END IF;

         DBMS_OUTPUT.put_line ('******************************************* ');
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('In exception ');
         DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLCODE);
         DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLERRM);
   END gen_datatype_validation;

   PROCEDURE gen_ins_upd_error (
      p_conv_id         IN   NUMBER,
      p_rule_id         IN   NUMBER,
      p_sqlldr_req_id   IN   NUMBER,
      p_key             IN   VARCHAR2,
      p_err_msg         IN   VARCHAR2
   )
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      v_err_msg    VARCHAR2 (4000);
      v_sql_code   VARCHAR2 (4000);

      CURSOR get_upd_sql (v_conv_id IN NUMBER, v_rule_id IN NUMBER)
      IS
         SELECT sql_code, process_status, rule_id
           FROM xxdc_p2_conv_rules
          WHERE rule_id = v_rule_id AND conv_id = v_conv_id;
   BEGIN
      IF (p_rule_id IS NOT NULL) AND (p_key IS NOT NULL)
      THEN
         /*
          * executing insert command to insert error records in XXDC_P2_CONV_ERRORS
          */
         v_err_msg := SUBSTR (p_err_msg, 1, 4000);
         fnd_file.put_line (fnd_file.LOG,
                            'XXDC_P2_CONV_ERRORS - v_err_msg - ' || v_err_msg
                           );

         EXECUTE IMMEDIATE 'INSERT INTO xxdc.xxdc_p2_conv_errors VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)'
                     USING p_conv_id,
                           p_rule_id,
                           fnd_global.conc_request_id,
                           p_key,
                           v_err_msg,
                           fnd_global.user_id,
                           SYSDATE,
                           fnd_global.user_id,
                           SYSDATE,
                           p_sqlldr_req_id;

         fnd_file.put_line (fnd_file.LOG,
                            'XXDC_P2_CONV_ERRORS - After insert');

         /*
          * executing update command to update error status in source tables..
          */
         FOR get_upd_sql_rec IN get_upd_sql (p_conv_id, p_rule_id)
         LOOP
            v_sql_code := get_upd_sql_rec.sql_code;

            IF (v_sql_code IS NOT NULL)
            THEN
               fnd_file.put_line (fnd_file.LOG,
                                     'XXDC_P2_CONV_ERRORS - v_sql_code - '
                                  || v_sql_code
                                 );

               EXECUTE IMMEDIATE v_sql_code
                           USING get_upd_sql_rec.process_status,
                                 p_key,
                                 p_sqlldr_req_id;
            END IF;
         END LOOP;
      END IF;

      COMMIT;
   END;

------
------ controller below
------
   PROCEDURE controller (
      p_mode              IN   VARCHAR2,
      p_info              IN   VARCHAR2,
      p_conv_id           IN   NUMBER,
      p_file_cdate        IN   VARCHAR2,
      p_user_id           IN   NUMBER,
      p_user              IN   VARCHAR2,
      p_run_req_id        IN   NUMBER,
      p_sqlload_req_id    IN   NUMBER,
      p_launched_req_id   IN   NUMBER,
      p_launched_prog     IN   VARCHAR2
   )
   IS
      CURSOR src_file_cur
      IS
         SELECT source_file_date, source_file_name, load_file_name
           FROM xxdc_p2_source_files_v
          WHERE conv_id = p_conv_id AND source_file_cdate = p_file_cdate;

      CURSOR load_file_cur
      IS
         SELECT load_file_name
           FROM xxdc_p2_source_files_v
          WHERE conv_id = p_conv_id AND source_file_cdate IS NULL;

      CURSOR stg_tbl_cur
      IS
         SELECT 'XXDC.' || table_name table_name
           FROM all_tables, xxdc.xxdc_p2_convs xpc
          WHERE owner = 'XXDC'
            AND 'XXDC_' || xpc.conv_code =
                             SUBSTR (table_name, 1, LENGTH (xpc.conv_code) + 5)
            AND conv_id = p_conv_id;

      v_sql                 VARCHAR2 (32767);
      v_archive_file_name   VARCHAR2 (255);
      v_prog_unit           VARCHAR (100)    := gc_pkg || '.CONTROLLER';
   BEGIN
      xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                     p_new_text       => xxd_utils.gc_start,
                     p_prog_unit      => v_prog_unit
                    );
      DBMS_OUTPUT.put_line (gv_debug_log_text);
      gv_debug_log_text := NULL;

      IF p_mode = 'LP' AND p_info IN ('SQLLOAD FAILED', 'SQLLOAD SUCCESS')
      THEN
-- run post load stuff
     -- clean up loaded file
         FOR load_file_rec IN load_file_cur
         LOOP
            UTL_FILE.fremove (gc_p2_data_dir, load_file_rec.load_file_name);
            xxd_utils.LOG
                        (p_log_text       => gv_debug_log_text,
                         p_new_text       =>    'Conversion Source file removed: '
                                             || gc_p2_data_dir
                                             || '/'
                                             || load_file_rec.load_file_name,
                         p_prog_unit      => v_prog_unit
                        );
            DBMS_OUTPUT.put_line (gv_debug_log_text);
            gv_debug_log_text := NULL;
         END LOOP;

-- Update launched request status
         UPDATE xxdc.xxdc_p2_conv_runs
            SET launched_request_status = p_info
          WHERE run_request_id = p_run_req_id
            AND sqlload_request_id = p_sqlload_req_id;

         IF p_info = 'SQLLOAD SUCCESS'
         THEN
-- update staging table with request id
            FOR stg_tbl_rec IN stg_tbl_cur
            LOOP
               v_sql :=
                     'UPDATE '
                  || stg_tbl_rec.table_name
                  || ' SET request_id = '
                  || p_sqlload_req_id
                  || ', created_by = '
                  || p_user_id
                  || ', last_updated_by = '
                  || p_user_id
                  || ' where nvl(request_id,-1) = -1';

               EXECUTE IMMEDIATE v_sql;

               xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                              p_new_text       =>    'Set request ID '
                                                  || p_sqlload_req_id
                                                  || ' for '
                                                  || stg_tbl_rec.table_name
                                                  || ' with '
                                                  || v_sql,
                              p_prog_unit      => v_prog_unit
                             );
               DBMS_OUTPUT.put_line (gv_debug_log_text);
               gv_debug_log_text := NULL;
            END LOOP;

            IF p_conv_id = 18
            THEN
               xxdc_load_insp_case.split_data (p_run_req_id);
            END IF;
         END IF;
      ELSE
-- log run
         INSERT INTO xxdc.xxdc_p2_conv_runs
                     (run_mode, run_request_id, run_date, run_by_user_id,
                      run_by_user, conv_id,
                      source_file_date,
                      source_file_cdate, sqlload_request_id,
                      launched_request_id, launched_program,
                      launched_request_status
                     )
              VALUES (p_mode, p_run_req_id, SYSDATE, p_user_id,
                      p_user, p_conv_id,
                      TO_DATE (p_file_cdate, 'YYYY-MM-DD_HH24-MI-SS'),
                      p_file_cdate, p_sqlload_req_id,
                      p_launched_req_id, p_launched_prog,
                      p_info
                     );
      END IF;

-- archive/move file for SQLLOAD
      IF p_mode = 'LP' AND p_info = 'SQLLOAD'
      THEN
         FOR src_file_rec IN src_file_cur
         LOOP
            xxd_utils.LOG
                        (p_log_text       => gv_debug_log_text,
                         p_new_text       =>    'Process conversion source file: '
                                             || xxd_utils.get_path
                                                               (gc_p2_data_dir)
                                             || '/'
                                             || src_file_rec.source_file_name,
                         p_prog_unit      => v_prog_unit
                        );
            DBMS_OUTPUT.put_line (gv_debug_log_text);
            gv_debug_log_text := NULL;
            v_archive_file_name :=
                  'LOADED_'
               || TO_CHAR (SYSDATE, 'YYYY-MM-DD_HH24:MI:SS')
               || '_'
               || src_file_rec.source_file_name;
-- copy to archive
            UTL_FILE.fcopy (gc_p2_data_dir,
                            src_file_rec.source_file_name,
                            gc_p2_archive_dir,
                            v_archive_file_name
                           );
            xxd_utils.LOG
                     (p_log_text       => gv_debug_log_text,
                      p_new_text       =>    'Conversion Source file archived to: '
                                          || xxd_utils.get_path
                                                            (gc_p2_archive_dir)
                                          || '/'
                                          || v_archive_file_name,
                      p_prog_unit      => v_prog_unit
                     );
            DBMS_OUTPUT.put_line (gv_debug_log_text);
            gv_debug_log_text := NULL;
-- rename for load
            UTL_FILE.frename (gc_p2_data_dir,
                              src_file_rec.source_file_name,
                              gc_p2_data_dir,
                              src_file_rec.load_file_name,
                              gc_true
                             );
            xxd_utils.LOG
                      (p_log_text       => gv_debug_log_text,
                       p_new_text       =>    'Conversion Source file renamed to: '
                                           || src_file_rec.load_file_name,
                       p_prog_unit      => v_prog_unit
                      );
            DBMS_OUTPUT.put_line (gv_debug_log_text);
            gv_debug_log_text := NULL;

-- log file
            INSERT INTO xxdc.xxdc_p2_conv_run_files
                        (run_request_id, conv_id,
                         source_file_date,
                         source_file_name, archive_file_name
                        )
                 VALUES (p_run_req_id, p_conv_id,
                         src_file_rec.source_file_date,
                         src_file_rec.source_file_name, v_archive_file_name
                        );
         END LOOP;
      END IF;

-- end p_mode
      xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                     p_new_text       => xxd_utils.gc_complete,
                     p_prog_unit      => v_prog_unit
                    );
      DBMS_OUTPUT.put_line (gv_debug_log_text);
      gv_debug_log_text := NULL;
   EXCEPTION
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || v_prog_unit
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => 'james.jen@calrecycle.ca.gov',
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
         DBMS_OUTPUT.put_line (gv_debug_log_text);
         gv_debug_log_text := NULL;
   END controller;

   FUNCTION get_attr_group_id (
      p_attr_grp_disp_name   IN   ego_attr_groups_v.attr_group_disp_name%TYPE
   )
      RETURN NUMBER
   IS
      v_attr_group_id   NUMBER;
   BEGIN
      SELECT attr_group_id
        INTO v_attr_group_id
        FROM ego_attr_groups_v
       WHERE attr_group_disp_name = p_attr_grp_disp_name;

      RETURN v_attr_group_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END get_attr_group_id;

   PROCEDURE gen_pre_validation (p_conv_id IN NUMBER, p_request_id IN NUMBER)
   IS
      TYPE cur_type IS REF CURSOR;

      exp_records        cur_type;
      x_rowid            UROWID;
      x_key              VARCHAR2 (255);
      x_src_file_name    VARCHAR2 (255);
      sql_stmt           VARCHAR2 (4000);
      --p_conv_id         NUMBER (15)     := 10;
      upd_sql_stmt       VARCHAR2 (4000);
      err_sql_stmt       VARCHAR2 (4000);
      x_err_msg          VARCHAR2 (4000);
      x_process_status   VARCHAR2 (2)    := '-1';
      l_upd_sql          VARCHAR (4000);
      v_prog_unit        VARCHAR (100)   := gc_pkg || '.GEN_PRE_VALIDATION';

      CURSOR get_process_status (l_conv_id IN NUMBER)
      IS
         SELECT DISTINCT process_status
                    FROM xxdc_p2_conv_rules
                   WHERE conv_id = l_conv_id
                     AND process_status NOT IN ('E', 'UT')
                     AND rule_id <> -100;

      --ORDER BY 1 DESC;
      CURSOR get_data_validation_rules (
         l_conv_id          IN   NUMBER,
         l_process_status   IN   VARCHAR
      )
      IS
         SELECT rule_id, table_name, rule_name, sql_code, process_status
           FROM xxdc_p2_conv_rules
          WHERE conv_id = l_conv_id
            AND process_status = l_process_status
            AND rule_id <> -100;

      CURSOR get_update_sql (l_conv_id IN NUMBER)
      IS
         SELECT rule_id, table_name, rule_name, sql_code
           FROM xxdc_p2_conv_rules
          WHERE conv_id = l_conv_id AND process_status = 'UT';
   BEGIN
      xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                     p_new_text       => xxd_utils.gc_start,
                     p_prog_unit      => v_prog_unit
                    );

      FOR get_process_status_rec IN get_process_status (p_conv_id)
      LOOP
         FOR get_data_validation_rules_rec IN
            get_data_validation_rules (p_conv_id,
                                       get_process_status_rec.process_status
                                      )
         LOOP
            xxd_utils.LOG
                 (p_log_text       => gv_debug_log_clob,
                  p_new_text       =>    'Rule Name - '
                                      || get_data_validation_rules_rec.rule_name
                                      || 'Table Name - '
                                      || get_data_validation_rules_rec.table_name
                                      || 'Process Status -'
                                      || get_data_validation_rules_rec.process_status,
                  p_prog_unit      => v_prog_unit
                 );
            /*dbms_output.put_line('Rule Name - '
                                      || get_data_validation_rules_rec.rule_name
                                      || 'Table Name - '
                                      || get_data_validation_rules_rec.table_name
                                      || 'Process Status -'
                                      || get_data_validation_rules_rec.process_status);*/
            xxd_utils.LOG
               (p_log_text       => gv_debug_log_clob,
                p_new_text       =>    'SQL Code - '
                                    || TO_CHAR
                                          (get_data_validation_rules_rec.sql_code
                                          ),
                p_prog_unit      => v_prog_unit
               );
            sql_stmt := get_data_validation_rules_rec.sql_code;
            DBMS_OUTPUT.put_line (' sql_stmt - ' || sql_stmt);

            OPEN exp_records FOR sql_stmt USING p_request_id;

            LOOP
               FETCH exp_records
                INTO x_rowid, x_key, x_err_msg;

               EXIT WHEN exp_records%NOTFOUND;
               xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                              p_new_text       =>    'Conv Key - '
                                                  || x_key
                                                  || ' x_rowid - '
                                                  || x_rowid
                                                  || '  x_err_msg - '
                                                  || x_err_msg,
                              p_prog_unit      => v_prog_unit
                             );

               /*dbms_output.put_line('Conv Key - '
                                                  || x_key
                                                  || ' x_rowid - '
                                                  || x_rowid
                                                  || '  x_err_msg - '
                                                  || x_err_msg);
                                                  */
               INSERT INTO xxdc.xxdc_p2_conv_errors
                           (conv_id, rule_id,
                            request_id, conv_key, error_msg,
                            created_by, creation_date, last_updated_by,
                            last_update_date
                           )
                    VALUES (p_conv_id, get_data_validation_rules_rec.rule_id,
                            fnd_global.conc_request_id, x_key, x_err_msg,
                            fnd_global.user_id, SYSDATE, fnd_global.user_id,
                            SYSDATE
                           );

               upd_sql_stmt :=
                     'UPDATE '
                  || get_data_validation_rules_rec.table_name
                  || ' SET process_status = '
                  || 'decode (process_status , ''RS'', ''RS'', ''RD'', ''RD'','''
                  || get_data_validation_rules_rec.process_status
                  || ''') '
                  || ' where rowid = :1';
               xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                              p_new_text       =>    ' upd_sql_stmt - '
                                                  || upd_sql_stmt,
                              p_prog_unit      => v_prog_unit
                             );

               --dbms_output.put_line('upd_sql_stmt - '||upd_sql_stmt);
               --dbms_output.put_line('x_rowid - '||x_rowid);
               EXECUTE IMMEDIATE upd_sql_stmt
                           USING x_rowid;

               FOR get_update_sql_rec IN get_update_sql (p_conv_id)
               LOOP
                  l_upd_sql := get_update_sql_rec.sql_code;
                  xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                                 p_new_text       =>    ' l_upd_sql - '
                                                     || l_upd_sql
                                                     || 'rule_name - '
                                                     || get_update_sql_rec.rule_name,
                                 p_prog_unit      => v_prog_unit
                                );
               END LOOP;

               --dbms_output.put_line('l_upd_sql - '||l_upd_sql);
               IF (l_upd_sql IS NOT NULL)
               THEN
                  EXECUTE IMMEDIATE l_upd_sql
                              USING
                                    get_data_validation_rules_rec.process_status,
                                    x_key,
                                    p_request_id;
               END IF;
            END LOOP;
         END LOOP;
      END LOOP;

      xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                     p_new_text       => xxd_utils.gc_complete,
                     p_prog_unit      => v_prog_unit
                    );
      xxd_utils.write_stuff (p_type         => fnd_file.LOG,
                             p_retcode      => '0',
                             p_debug        => 'Y',
                             p_stuff        => gv_debug_log_clob,
                             p_result       => gv_result
                            );
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLCODE);
         DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLERRM);
         xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || v_prog_unit
                                            || ', error: '
                                            || SQLCODE
                                            || ' - '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
   END gen_pre_validation;

   PROCEDURE gen_datatype_validation (
      p_conv_id      IN   NUMBER,
      p_request_id   IN   NUMBER
   )
   IS
      v_data_type        VARCHAR2 (50);
      v_data_length      NUMBER;
      v_rowid            ROWID;
      v_row_data         VARCHAR2 (255);
      v_date             DATE;
      v_number           NUMBER;

      TYPE cur_type IS REF CURSOR;

      src_records        cur_type;
      v_sql_stmt         VARCHAR2 (4000);
      v_upd_sql_stmt     VARCHAR2 (4000);
      v_insrt_sql_stmt   VARCHAR2 (4000);

      CURSOR get_map_data (v_conv_id NUMBER)
      IS
         SELECT *
           FROM xxdc_p2_conv_table_column_map
          WHERE conv_id = v_conv_id AND validate_flag = 'Y';

      CURSOR get_sql             -- (v_conv_id NUMBER, v_table_name VARCHAR2)
      IS
         SELECT *
           FROM xxdc_p2_conv_rules
          WHERE rule_id = 0;
   BEGIN
      FOR get_map_data_rec IN get_map_data (p_conv_id)
      LOOP
         DBMS_OUTPUT.put_line ('******************************************* ');
         DBMS_OUTPUT.put_line (   'get_map_data_rec.TARGET_TABLE_NAME - '
                               || get_map_data_rec.target_table_name
                              );
         DBMS_OUTPUT.put_line (   'get_map_data_rec.TARGET_COLUMN_NAME -  '
                               || get_map_data_rec.target_column_name
                              );

         SELECT data_type, data_length
           INTO v_data_type, v_data_length
           FROM all_tab_columns
          WHERE table_name = UPPER (get_map_data_rec.target_table_name)
            AND column_name = UPPER (get_map_data_rec.target_column_name);

         DBMS_OUTPUT.put_line (' v_data_type - ' || v_data_type);
         DBMS_OUTPUT.put_line (' v_data_length -' || v_data_length);

         IF (v_data_type = 'VARCHAR2')
         THEN
            v_sql_stmt :=
                  'SELECT ROWID FROM '
               || get_map_data_rec.source_table_name
               || ' WHERE length( '
               || get_map_data_rec.source_column_name
               || ' ) > '
               || v_data_length
               || ' AND NVL(REQUEST_ID, -1) = nvl('
               || p_request_id
               || ', -1)';
            DBMS_OUTPUT.put_line ('v_sql_stmt -  ' || v_sql_stmt);

            OPEN src_records FOR v_sql_stmt;

            LOOP
               FETCH src_records
                INTO v_rowid;

               EXIT WHEN src_records%NOTFOUND;

               FOR get_sql_rec IN get_sql
               LOOP
                  DBMS_OUTPUT.put_line (' In varchar2 exception ');
                  DBMS_OUTPUT.put_line ('v_rowid -  ' || v_rowid);
                  v_upd_sql_stmt :=
                        'UPDATE '
                     || get_map_data_rec.source_table_name
                     || ' SET process_status = '''
                     || get_sql_rec.process_status
                     || ''' where rowid = :1';
                  DBMS_OUTPUT.put_line (' v_upd_sql_stmt - ' || v_upd_sql_stmt);

                  EXECUTE IMMEDIATE v_upd_sql_stmt
                              USING v_rowid;

                  v_insrt_sql_stmt := get_sql_rec.sql_code;
                  DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                        || v_insrt_sql_stmt
                                       );
                  v_insrt_sql_stmt :=
                     REPLACE (v_insrt_sql_stmt,
                              'tab_name',
                              get_map_data_rec.source_table_name
                             );
                  DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                        || v_insrt_sql_stmt
                                       );

                  EXECUTE IMMEDIATE v_insrt_sql_stmt
                              USING p_conv_id,
                                    get_sql_rec.rule_id,
                                    get_map_data_rec.pk_column_name,
                                    get_map_data_rec.source_column_name,
                                    get_map_data_rec.source_column_name,
                                    v_data_type,
                                    get_map_data_rec.source_table_name,
                                    v_rowid;

                  DBMS_OUTPUT.put_line ('after execute update ');
               END LOOP;
            END LOOP;
         ELSIF (v_data_type = 'DATE')
         THEN
            v_sql_stmt :=
                  'SELECT ROWID, '
               || get_map_data_rec.source_column_name
               || ' FROM '
               || get_map_data_rec.source_table_name
               || ' WHERE NVL(REQUEST_ID, -1) = nvl('
               || p_request_id
               || ', -1)';
            DBMS_OUTPUT.put_line ('v_sql_stmt -  ' || v_sql_stmt);

            OPEN src_records FOR v_sql_stmt;

            LOOP
               FETCH src_records
                INTO v_rowid, v_row_data;

               EXIT WHEN src_records%NOTFOUND;

               BEGIN
                  v_date := TO_DATE (v_row_data, 'MM/DD/YYYY HH24:MI:SS');
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     DBMS_OUTPUT.put_line (' In to_date exception ');
                     DBMS_OUTPUT.put_line ('v_rowid -  ' || v_rowid);
                     DBMS_OUTPUT.put_line ('v_row_data - ' || v_row_data);

                     FOR get_sql_rec IN get_sql
                     LOOP
                        v_upd_sql_stmt :=
                              'UPDATE '
                           || get_map_data_rec.source_table_name
                           || ' SET process_status = '''
                           || get_sql_rec.process_status
                           || ''' where rowid = :1';
                        DBMS_OUTPUT.put_line (   ' v_upd_sql_stmt - '
                                              || v_upd_sql_stmt
                                             );

                        EXECUTE IMMEDIATE v_upd_sql_stmt
                                    USING v_rowid;

                        v_insrt_sql_stmt := get_sql_rec.sql_code;
                        DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                              || v_insrt_sql_stmt
                                             );
                        DBMS_OUTPUT.put_line ('p_conv_id - ' || p_conv_id);
                        DBMS_OUTPUT.put_line (   'get_sql_rec.rule_id - '
                                              || get_sql_rec.rule_id
                                             );
                        DBMS_OUTPUT.put_line
                                      (   'get_map_data_rec.pk_column_name - '
                                       || get_map_data_rec.pk_column_name
                                      );
                        DBMS_OUTPUT.put_line
                                  (   'get_map_data_rec.source_column_name - '
                                   || get_map_data_rec.source_column_name
                                  );
                        DBMS_OUTPUT.put_line ('v_data_type - ' || v_data_type);
                        DBMS_OUTPUT.put_line
                                   (   'get_map_data_rec.source_table_name - '
                                    || get_map_data_rec.source_table_name
                                   );
                        DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
                        DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                              || v_insrt_sql_stmt
                                             );
                        v_insrt_sql_stmt :=
                           REPLACE (v_insrt_sql_stmt,
                                    'tab_name',
                                    get_map_data_rec.source_table_name
                                   );
                        DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                              || v_insrt_sql_stmt
                                             );

                        EXECUTE IMMEDIATE v_insrt_sql_stmt
                                    USING
                                          p_conv_id,
                                          get_sql_rec.rule_id,
                                          get_map_data_rec.pk_column_name,
                                          get_map_data_rec.source_column_name,
                                          get_map_data_rec.source_column_name,
                                          v_data_type,
                                          --get_map_data_rec.source_table_name,
                                          v_rowid;

                        DBMS_OUTPUT.put_line ('after execute update ');
                     END LOOP;
               END;
            END LOOP;
         ELSIF (v_data_type = 'NUMBER')
         THEN
            v_sql_stmt :=
                  'SELECT ROWID, '
               || get_map_data_rec.source_column_name
               || ' FROM '
               || get_map_data_rec.source_table_name
               || ' WHERE NVL(REQUEST_ID, -1) = nvl('
               || p_request_id
               || ', -1)';
            DBMS_OUTPUT.put_line ('v_sql_stmt -  ' || v_sql_stmt);

            OPEN src_records FOR v_sql_stmt;

            LOOP
               FETCH src_records
                INTO v_rowid, v_row_data;

               EXIT WHEN src_records%NOTFOUND;

               BEGIN
                  v_number := TO_NUMBER (v_row_data);
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     DBMS_OUTPUT.put_line (' In to_number exception ');
                     DBMS_OUTPUT.put_line ('v_rowid -  ' || v_rowid);
                     DBMS_OUTPUT.put_line ('v_row_data - ' || v_row_data);

                     FOR get_sql_rec IN get_sql
                     LOOP
                        v_upd_sql_stmt :=
                              'UPDATE '
                           || get_map_data_rec.source_table_name
                           || ' SET process_status = '''
                           || get_sql_rec.process_status
                           || ''' where rowid = :1';
                        DBMS_OUTPUT.put_line (   ' v_upd_sql_stmt - '
                                              || v_upd_sql_stmt
                                             );

                        EXECUTE IMMEDIATE v_upd_sql_stmt
                                    USING v_rowid;

                        DBMS_OUTPUT.put_line ('p_conv_id - ' || p_conv_id);
                        DBMS_OUTPUT.put_line (   'get_sql_rec.rule_id - '
                                              || get_sql_rec.rule_id
                                             );
                        DBMS_OUTPUT.put_line
                                      (   'get_map_data_rec.pk_column_name - '
                                       || get_map_data_rec.pk_column_name
                                      );
                        DBMS_OUTPUT.put_line
                                  (   'get_map_data_rec.source_column_name - '
                                   || get_map_data_rec.source_column_name
                                  );
                        DBMS_OUTPUT.put_line ('v_data_type - ' || v_data_type);
                        DBMS_OUTPUT.put_line
                                   (   'get_map_data_rec.source_table_name - '
                                    || get_map_data_rec.source_table_name
                                   );
                        DBMS_OUTPUT.put_line ('v_rowid - ' || v_rowid);
                        v_insrt_sql_stmt := get_sql_rec.sql_code;
                        DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                              || v_insrt_sql_stmt
                                             );
                        v_insrt_sql_stmt :=
                           REPLACE (v_insrt_sql_stmt,
                                    'tab_name',
                                    get_map_data_rec.source_table_name
                                   );
                        DBMS_OUTPUT.put_line (   'v_insrt_sql_stmt - '
                                              || v_insrt_sql_stmt
                                             );

                        EXECUTE IMMEDIATE v_insrt_sql_stmt
                                    USING
                                          p_conv_id,
                                          get_sql_rec.rule_id,
                                          get_map_data_rec.pk_column_name,
                                          get_map_data_rec.source_column_name,
                                          get_map_data_rec.source_column_name,
                                          v_data_type,
                                          --get_map_data_rec.source_table_name,
                                          v_rowid;

                        DBMS_OUTPUT.put_line ('after execute update ');
                     END LOOP;
               END;
            END LOOP;
         END IF;

         DBMS_OUTPUT.put_line ('******************************************* ');
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('In exception ');
         DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLCODE);
         DBMS_OUTPUT.put_line ('sqlerrcode - ' || SQLERRM);
   END gen_datatype_validation;

   PROCEDURE get_case_party_info (
      p_legacy_acct_num   IN       VARCHAR2,
      p_party_id          OUT      NUMBER,
      p_party_type        OUT      VARCHAR2,
      p_account_id        OUT      NUMBER,
      p_party_site_id     OUT      NUMBER
   )
   IS
      v_prog_unit   VARCHAR (100) := gc_pkg || '.GET_LOAD_INSP_PARTY_INFO';
   BEGIN
      xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                     p_new_text       => xxd_utils.gc_start,
                     p_prog_unit      => v_prog_unit
                    );

      SELECT hp.party_id, hp.party_type, hps.party_site_id,
             hca.cust_account_id
        INTO p_party_id, p_party_type, p_party_site_id,
             p_account_id
        FROM hz_parties hp, hz_cust_accounts hca, hz_party_sites hps
       WHERE hca.party_id = hp.party_id
         AND hps.party_id = hp.party_id
         AND hps.identifying_address_flag = 'Y'
         AND hca.account_number = p_legacy_acct_num;

      xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                     p_new_text       => xxd_utils.gc_complete,
                     p_prog_unit      => v_prog_unit
                    );
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         p_party_id := -1;
         p_party_type := NULL;
         p_account_id := -1;
         p_party_site_id := -1;
      WHEN TOO_MANY_ROWS
      THEN
         p_party_id := -2;
         p_party_type := NULL;
         p_account_id := -2;
         p_party_site_id := -2;
   END get_case_party_info;

   PROCEDURE prevalidation (
      p_conv_id      IN       NUMBER,
      p_request_id   IN       NUMBER,
      p_log          OUT      CLOB,
      p_result       OUT      VARCHAR2
   )
   IS
      TYPE cur_type IS REF CURSOR;

      exp_records        cur_type;
      x_rowid            UROWID;
      x_key              VARCHAR2 (255);
      x_src_file_name    VARCHAR2 (255);
      sql_stmt           VARCHAR2 (4000);
      --p_conv_id         NUMBER (15)     := 10;
      upd_sql_stmt       VARCHAR2 (4000);
      err_sql_stmt       VARCHAR2 (4000);
      x_err_msg          VARCHAR2 (4000);
      x_process_status   VARCHAR2 (2)    := '-1';
      l_upd_sql          VARCHAR (4000);
      v_insert_cnt       INTEGER         := 0;
      v_update_cnt       INTEGER         := 0;
      v_prog_unit        VARCHAR (100)   := gc_pkg || '.PREVALIDATION';

      CURSOR get_process_status (l_conv_id IN NUMBER)
      IS
         SELECT DISTINCT process_status
                    FROM xxdc_p2_conv_rules
                   WHERE conv_id = l_conv_id
                     AND process_status NOT IN ('E', 'UT')
                     AND rule_id <> -100;

      --ORDER BY 1 DESC;
      CURSOR get_data_validation_rules (
         l_conv_id          IN   NUMBER,
         l_process_status   IN   VARCHAR
      )
      IS
         SELECT rule_id, rule_number, table_name, rule_name, sql_code,
                process_status
           FROM xxdc_p2_conv_rules
          WHERE conv_id = l_conv_id
            AND process_status = l_process_status
            AND rule_id <> -100;

      CURSOR get_update_sql (l_conv_id IN NUMBER)
      IS
         SELECT rule_id, table_name, rule_name, sql_code
           FROM xxdc_p2_conv_rules
          WHERE conv_id = l_conv_id AND process_status = 'UT';
   BEGIN
      p_result := 0;
      xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                     p_new_text       => xxd_utils.gc_start,
                     p_prog_unit      => v_prog_unit
                    );
      xxd_utils.LOG (p_log_text         => gv_debug_log_clob,
                     p_new_text         => 'Conv ID: ' || p_conv_id,
                     p_dt_stamp_yn      => 'N',
                     p_prog_unit        => v_prog_unit
                    );
      xxd_utils.LOG (p_log_text         => gv_debug_log_clob,
                     p_new_text         =>    'SQLLoad Request ID: '
                                           || p_request_id,
                     p_dt_stamp_yn      => 'N',
                     p_prog_unit        => v_prog_unit
                    );

      FOR get_process_status_rec IN get_process_status (p_conv_id)
      LOOP
         FOR get_data_validation_rules_rec IN
            get_data_validation_rules (p_conv_id,
                                       get_process_status_rec.process_status
                                      )
         LOOP
            xxd_utils.LOG
                 (p_log_text       => gv_debug_log_clob,
                  p_new_text       =>    'Rule ID/#/Name: '
                                      || get_data_validation_rules_rec.rule_id
                                      || '/'
                                      || get_data_validation_rules_rec.rule_number
                                      || '/'
                                      || get_data_validation_rules_rec.rule_name,
                  p_prog_unit      => v_prog_unit
                 );
            xxd_utils.LOG
                  (p_log_text       => gv_debug_log_clob,
                   p_new_text       =>    'Table: '
                                       || get_data_validation_rules_rec.table_name
                                       || ' Process Status: '
                                       || get_data_validation_rules_rec.process_status,
                   p_prog_unit      => v_prog_unit
                  );
/**
            xxd_utils.LOG (p_log_text =>       gv_debug_log_clob
                          ,p_new_text =>          'SQL: '
                                               || TO_CHAR
                                                     (get_data_validation_rules_rec.sql_code)
                          ,p_prog_unit =>      v_prog_unit);
**/
            sql_stmt := get_data_validation_rules_rec.sql_code;
            v_insert_cnt := 0;
            v_update_cnt := 0;

            OPEN exp_records FOR sql_stmt USING p_request_id;

            LOOP
               FETCH exp_records
                INTO x_rowid, x_key, x_err_msg;

               EXIT WHEN exp_records%NOTFOUND;

               INSERT INTO xxdc.xxdc_p2_conv_errors
                           (conv_id,
                            rule_id,
                            request_id, conv_key, error_msg,
                            created_by, creation_date, last_updated_by,
                            last_update_date, sqlload_request_id
                           )
                    VALUES (p_conv_id,
                            get_data_validation_rules_rec.rule_id,
                            fnd_global.conc_request_id, x_key, x_err_msg,
                            fnd_global.user_id, SYSDATE, fnd_global.user_id,
                            SYSDATE, p_request_id
                           );

               v_insert_cnt := v_insert_cnt + SQL%ROWCOUNT;
               upd_sql_stmt :=
                     'UPDATE xxdc.'
                  || get_data_validation_rules_rec.table_name
                  || ' SET process_status = '
                  || 'decode (process_status , ''RS'', ''RS'', ''RD'', ''RD'','''
                  || get_data_validation_rules_rec.process_status
                  || ''') '
                  || ' where rowid = :1';

/**
              xxd_utils.LOG (p_log_text =>       gv_debug_log_clob
                             ,p_new_text =>       ' upd_sql_stmt - ' || upd_sql_stmt
                             ,p_prog_unit =>      v_prog_unit);
**/
               EXECUTE IMMEDIATE upd_sql_stmt
                           USING x_rowid;

               v_update_cnt := v_update_cnt + SQL%ROWCOUNT;

               FOR get_update_sql_rec IN get_update_sql (p_conv_id)
               LOOP
                  l_upd_sql := get_update_sql_rec.sql_code;
/**
                  xxd_utils.LOG (p_log_text =>       gv_debug_log_clob
                                ,p_new_text =>          ' l_upd_sql - '
                                                     || l_upd_sql
                                                     || 'rule_name - '
                                                     || get_update_sql_rec.rule_name
                                ,p_prog_unit =>      v_prog_unit);
**/
               END LOOP;

               --dbms_output.put_line('l_upd_sql - '||l_upd_sql);
               IF (l_upd_sql IS NOT NULL)
               THEN
                  EXECUTE IMMEDIATE l_upd_sql
                              USING
                                    get_data_validation_rules_rec.process_status,
                                    x_key,
                                    p_request_id;
               END IF;
            END LOOP;

            xxd_utils.LOG
               (p_log_text       => gv_debug_log_clob,
                p_new_text       =>    v_insert_cnt
                                    || ' record(s) inserted into XXDC.XXDC_P2_CONV_ERRORS for rule.',
                p_prog_unit      => v_prog_unit
               );
            xxd_utils.LOG
                   (p_log_text       => gv_debug_log_clob,
                    p_new_text       =>    v_update_cnt
                                        || ' record(s) updated in '
                                        || get_data_validation_rules_rec.table_name
                                        || ' for rule.',
                    p_prog_unit      => v_prog_unit
                   );

            IF (v_insert_cnt > 0) OR (v_update_cnt > 0)
            THEN
               p_result := 1;
            END IF;
         END LOOP;
      END LOOP;

      xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                     p_new_text       => xxd_utils.gc_complete,
                     p_prog_unit      => v_prog_unit
                    );
      p_log := gv_debug_log_clob;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_result := 2;
         xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || v_prog_unit
                                            || ', error: '
                                            || SQLCODE
                                            || ' - '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         p_log := gv_debug_log_clob;
   END prevalidation;

----------------------------------------------------
---- shared lookup procedures/functions below ------
----------------------------------------------------
--
-- to be clean/revised
--
   PROCEDURE get_bill_to_site_use_id (
      p_legacy_acct_num       IN       VARCHAR2,
      p_bill_to_party_id      OUT      NUMBER,
      p_cust_acct_id          OUT      NUMBER,
      p_bill_to_site_id       OUT      NUMBER,
      p_bill_to_site_use_id   OUT      NUMBER
   )
   IS
      v_party_id              hz_parties.party_id%TYPE                := NULL;
      v_cust_acct_id          hz_cust_accounts.cust_account_id%TYPE   := NULL;
      v_bill_to_site_id       hz_party_sites.party_site_id%TYPE       := NULL;
      v_bill_to_site_use_id   hz_party_site_uses.party_site_use_id%TYPE
                                                                      := NULL;
   BEGIN
      SELECT aa.cust_account_id, ua.site_use_id, sa.party_site_id,
             hp.party_id
        INTO v_cust_acct_id, v_bill_to_site_id, v_bill_to_site_use_id,
             v_party_id
        FROM hz_cust_accounts_all aa,
             hz_parties hp,
             hz_cust_acct_sites_all sa,
             hz_cust_site_uses_all ua
       WHERE aa.account_number = p_legacy_acct_num
         AND hp.party_id = aa.party_id
         AND sa.cust_account_id = aa.cust_account_id
         AND sa.cust_acct_site_id = ua.cust_acct_site_id
         AND ua.site_use_code = 'BILL_TO'
         AND ua.status = 'A'
         AND ua.primary_flag = 'Y';

      p_bill_to_party_id := v_party_id;
      p_cust_acct_id := v_cust_acct_id;
      p_bill_to_site_id := v_bill_to_site_id;
      p_bill_to_site_use_id := v_bill_to_site_use_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_bill_to_party_id := NULL;
         p_bill_to_site_id := NULL;
         p_bill_to_site_use_id := NULL;
         p_cust_acct_id := NULL;
   END get_bill_to_site_use_id;

--
-- generic utility
--
   FUNCTION get_inventory_item_id (p_caller IN VARCHAR2, p_segment1 IN VARCHAR2)
      RETURN NUMBER
   IS
      v_prog_unit           VARCHAR (100)
                                        := gc_pkg || '.GET_INVENTORY_ITEM_ID';
      v_inventory_item_id   mtl_system_items_vl.inventory_item_id%TYPE;
   BEGIN
      gv_debug_log_text := NULL;

      SELECT inventory_item_id
        INTO v_inventory_item_id
        FROM mtl_system_items_vl iv
       WHERE iv.segment1 = p_segment1
         AND iv.inventory_item_status_code = 'Active';

      RETURN v_inventory_item_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || ' p_caller - '
                                            || p_caller
                                            || ' p_segment1 - '
                                            || p_segment1
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
         RETURN NULL;
   END get_inventory_item_id;

   FUNCTION get_person_id (p_caller IN VARCHAR2, p_emp_num IN VARCHAR2)
      RETURN NUMBER
   IS
      v_prog_unit   VARCHAR (100)               := gc_pkg || '.GET_PERSON_ID';
      v_person_id   per_all_people_f.person_id%TYPE;
   BEGIN
      gv_debug_log_text := NULL;

      SELECT person_id
        INTO v_person_id
        FROM per_all_people_f papf
       WHERE papf.employee_number = p_emp_num
         AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

      RETURN v_person_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN NULL;
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || ' p_caller - '
                                            || p_caller
                                            || ' p_emp_num - '
                                            || p_emp_num
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
         RETURN NULL;
   END get_person_id;

   FUNCTION get_attr_group_id (
      p_caller               IN   VARCHAR2,
      p_attr_grp_disp_name   IN   ego_attr_groups_v.attr_group_disp_name%TYPE
   )
      RETURN NUMBER
   IS
      v_prog_unit       VARCHAR (100) := gc_pkg || '.GET_ATTR_GROUP_ID';
      v_attr_group_id   NUMBER;
   BEGIN
      gv_debug_log_text := NULL;

      SELECT attr_group_id
        INTO v_attr_group_id
        FROM ego_attr_groups_v
       WHERE attr_group_disp_name = p_attr_grp_disp_name;

      RETURN v_attr_group_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || ' p_caller - '
                                            || p_caller
                                            || ' p_attr_grp_disp_name - '
                                            || p_attr_grp_disp_name
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
         RETURN NULL;
   END get_attr_group_id;

--
-- Case stuff
--
   PROCEDURE get_party_info (
      p_caller            IN       VARCHAR2,
      p_legacy_acct_num   IN       VARCHAR2,
      p_party_id          OUT      NUMBER,
      p_party_type        OUT      VARCHAR2,
      p_account_id        OUT      NUMBER,
      p_party_site_id     OUT      NUMBER
   )
   IS
      v_prog_unit   VARCHAR (100) := gc_pkg || '.GET_PARTY_INFO';
   BEGIN
      xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                     p_new_text       => xxd_utils.gc_start,
                     p_prog_unit      => v_prog_unit
                    );

      SELECT hp.party_id, hp.party_type, hps.party_site_id,
             hca.cust_account_id
        INTO p_party_id, p_party_type, p_party_site_id,
             p_account_id
        FROM hz_parties hp, hz_cust_accounts hca, hz_party_sites hps
       WHERE hca.party_id = hp.party_id
         AND hps.party_id = hp.party_id
         AND hps.identifying_address_flag = 'Y'
         AND hca.account_number = p_legacy_acct_num;

      xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                     p_new_text       => xxd_utils.gc_complete,
                     p_prog_unit      => v_prog_unit
                    );
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         p_party_id := -1;
         p_party_type := NULL;
         p_account_id := -1;
         p_party_site_id := -1;
      WHEN TOO_MANY_ROWS
      THEN
         p_party_id := -2;
         p_party_type := NULL;
         p_account_id := -2;
         p_party_site_id := -2;
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || ' p_caller - '
                                            || p_caller
                                            || 'Party_info '
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
   END get_party_info;

   PROCEDURE get_bill_to_pt_site_use_id (
      p_caller                IN       VARCHAR2,
      p_legacy_acct_num       IN       VARCHAR2,
      p_bill_to_party_id      OUT      NUMBER,
      p_cust_acct_id          OUT      NUMBER,
      p_bill_to_site_id       OUT      NUMBER,
      p_bill_to_site_use_id   OUT      NUMBER
   )
   IS
      v_prog_unit   VARCHAR (100) := gc_pkg || '.GET_BILL_TO_PT_SITE_USE_ID';
   BEGIN
      SELECT ps.party_site_id, pu.party_site_use_id, ca.cust_account_id,
             hp.party_id
        INTO p_bill_to_site_id, p_bill_to_site_use_id, p_cust_acct_id,
             p_bill_to_party_id
        FROM hz_parties hp,
             hz_cust_accounts_all ca,
             hz_party_sites ps,
             hz_party_site_uses pu
       WHERE ca.account_number = p_legacy_acct_num
         AND ca.party_id = hp.party_id
         AND ps.party_id = ca.party_id
         AND pu.party_site_id = ps.party_site_id
         AND ps.identifying_address_flag = 'Y'
         AND pu.site_use_type = 'BILL_TO'
         AND pu.primary_per_type = 'Y';
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         p_bill_to_party_id := -1;
         p_bill_to_site_id := -1;
         p_bill_to_site_use_id := -1;
         p_cust_acct_id := -1;
      WHEN TOO_MANY_ROWS
      THEN
         p_bill_to_party_id := -2;
         p_bill_to_site_id := -2;
         p_bill_to_site_use_id := -2;
         p_cust_acct_id := -2;
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || 'p_caller - '
                                            || p_caller
                                            || v_prog_unit
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
   END get_bill_to_pt_site_use_id;

   PROCEDURE get_bill_to_acct_site_use_id (
      p_caller                IN       VARCHAR2,
      p_legacy_acct_num       IN       VARCHAR2,
      p_bill_to_party_id      OUT      NUMBER,
      p_cust_acct_id          OUT      NUMBER,
      p_bill_to_site_id       OUT      NUMBER,
      p_bill_to_site_use_id   OUT      NUMBER
   )
   IS
      v_prog_unit             VARCHAR (100)
                                 := gc_pkg || '.GET_BILL_TO_ACCT_SITE_USE_ID';
      v_party_id              hz_parties.party_id%TYPE                := NULL;
      v_cust_acct_id          hz_cust_accounts.cust_account_id%TYPE   := NULL;
      v_bill_to_site_id       hz_party_sites.party_site_id%TYPE       := NULL;
      v_bill_to_site_use_id   hz_party_site_uses.party_site_use_id%TYPE
                                                                      := NULL;
   BEGIN
      gv_debug_log_text := NULL;

      SELECT aa.cust_account_id, ua.site_use_id, sa.party_site_id,
             hp.party_id
        INTO v_cust_acct_id, v_bill_to_site_id, v_bill_to_site_use_id,
             v_party_id
        FROM hz_cust_accounts_all aa,
             hz_parties hp,
             hz_cust_acct_sites_all sa,
             hz_cust_site_uses_all ua
       WHERE aa.account_number = p_legacy_acct_num
         AND hp.party_id = aa.party_id
         AND sa.cust_account_id = aa.cust_account_id
         AND sa.cust_acct_site_id = ua.cust_acct_site_id
         AND ua.site_use_code = 'BILL_TO'
         AND ua.status = 'A'
         AND ua.primary_flag = 'Y';

      p_bill_to_party_id := v_party_id;
      p_cust_acct_id := v_cust_acct_id;
      p_bill_to_site_id := v_bill_to_site_id;
      p_bill_to_site_use_id := v_bill_to_site_use_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_bill_to_party_id := NULL;
         p_bill_to_site_id := NULL;
         p_bill_to_site_use_id := NULL;
         p_cust_acct_id := NULL;
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || ' p_caller - '
                                            || p_caller
                                            || ' p_legacy_acct_num - '
                                            || p_legacy_acct_num
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
   END get_bill_to_acct_site_use_id;

   FUNCTION get_inc_problem_code (
      p_caller            IN   VARCHAR2,
      p_problem_meaning   IN   VARCHAR2
   )
      RETURN VARCHAR2
   IS
      v_prog_unit      VARCHAR (100)     := gc_pkg || '.GET_INC_PROBLEM_CODE';
      v_problem_code   cs_lookups.lookup_code%TYPE;
   BEGIN
      gv_debug_log_text := NULL;

      SELECT lookup_code
        INTO v_problem_code
        FROM cs_lookups
       WHERE lookup_type = 'REQUEST_PROBLEM_CODE'
         AND UPPER (meaning) = UPPER (p_problem_meaning)
         AND enabled_flag = 'Y'
         AND SYSDATE BETWEEN start_date_active AND NVL (end_date_active,
                                                        SYSDATE
                                                       );

      RETURN v_problem_code;
   EXCEPTION
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || ' p_caller - '
                                            || p_caller
                                            || ' p_problem_meaning - '
                                            || p_problem_meaning
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
         RETURN NULL;
   END get_inc_problem_code;

   FUNCTION get_inc_type_id (p_caller IN VARCHAR2, p_incident_type IN VARCHAR2)
      RETURN NUMBER
   IS
      v_prog_unit          VARCHAR (100)      := gc_pkg || '.GET_INC_TYPE_ID';
      v_incident_type_id   cs_incidents_all_b.incident_type_id%TYPE;
   BEGIN
      gv_debug_log_text := NULL;

      SELECT incident_type_id
        INTO v_incident_type_id
        FROM cs_incident_types_vl
       WHERE UPPER (NAME) = UPPER (p_incident_type)
         AND incident_subtype = gc_inc_subtype
         AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE)
                         AND NVL (end_date_active, SYSDATE);

      RETURN v_incident_type_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || ' p_caller - '
                                            || p_caller
                                            || ' p_incident_type - '
                                            || p_incident_type
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
         RETURN NULL;
   END get_inc_type_id;

   FUNCTION get_inc_resolution_code (
      p_caller               IN   VARCHAR2,
      p_resolution_meaning   IN   VARCHAR2
   )
      RETURN VARCHAR2
   IS
      v_prog_unit         VARCHAR (100)
                                      := gc_pkg || '.GET_INC_RESOLUTION_CODE';
      v_resolution_code   cs_lookups.lookup_code%TYPE;
   BEGIN
      gv_debug_log_text := NULL;

      SELECT lookup_code
        INTO v_resolution_code
        FROM cs_lookups
       WHERE lookup_type = 'REQUEST_RESOLUTION_CODE'
         AND UPPER (meaning) = UPPER (p_resolution_meaning)
         AND enabled_flag = 'Y'
         AND SYSDATE BETWEEN start_date_active AND NVL (end_date_active,
                                                        SYSDATE
                                                       );

      RETURN v_resolution_code;
   EXCEPTION
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || ' p_caller - '
                                            || p_caller
                                            || ' p_resolution_meaning - '
                                            || p_resolution_meaning
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
         RETURN NULL;
   END get_inc_resolution_code;

   FUNCTION get_inc_urgency_id (p_caller IN VARCHAR2, p_urgency IN VARCHAR2)
      RETURN NUMBER
   IS
      v_prog_unit             VARCHAR (100)
                                           := gc_pkg || '.GET_INC_URGENCY_ID';
      v_incident_urgency_id   cs_incident_urgencies_vl.incident_urgency_id%TYPE;
   BEGIN
      gv_debug_log_text := NULL;

      SELECT incident_urgency_id
        INTO v_incident_urgency_id
        FROM cs_incident_urgencies_vl
       WHERE UPPER (NAME) = UPPER (p_urgency)
         AND SYSDATE BETWEEN start_date_active AND NVL (end_date_active,
                                                        SYSDATE
                                                       );

      RETURN v_incident_urgency_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || ' p_caller - '
                                            || p_caller
                                            || ' p_urgency - '
                                            || p_urgency
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
         RETURN NULL;
   END get_inc_urgency_id;

   FUNCTION get_inc_severity_id (p_caller IN VARCHAR2, p_severity IN VARCHAR2)
      RETURN NUMBER
   IS
      v_prog_unit              VARCHAR (100)
                                          := gc_pkg || '.GET_INC_SEVERITY_ID';
      v_incident_severity_id   cs_incident_severities_vl.incident_severity_id%TYPE;
   BEGIN
      gv_debug_log_text := NULL;

      SELECT incident_severity_id
        INTO v_incident_severity_id
        FROM cs_incident_severities_vl
       WHERE UPPER (NAME) = UPPER (p_severity)
         AND incident_subtype = gc_inc_subtype;

      RETURN v_incident_severity_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || ' p_caller - '
                                            || p_caller
                                            || ' p_severity - '
                                            || p_severity
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
         RETURN NULL;
   END get_inc_severity_id;

   FUNCTION get_inc_group_id (p_caller IN VARCHAR2, p_group_name IN VARCHAR2)
      RETURN NUMBER
   IS
      v_prog_unit      VARCHAR (100)         := gc_pkg || '.GET_INC_GROUP_ID';
      v_inc_group_id   jtf_rs_groups_vl.GROUP_ID%TYPE;
   BEGIN
      gv_debug_log_text := NULL;

      SELECT GROUP_ID
        INTO v_inc_group_id
        FROM jtf_rs_groups_vl
       WHERE UPPER (group_name) = UPPER (p_group_name)
         AND SYSDATE BETWEEN start_date_active AND NVL (end_date_active,
                                                        SYSDATE
                                                       );

      RETURN v_inc_group_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || ' p_caller - '
                                            || p_caller
                                            || ' p_group_name - '
                                            || p_group_name
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
         RETURN NULL;
   END get_inc_group_id;

   FUNCTION get_inc_status_id (p_caller IN VARCHAR2, p_inc_status IN VARCHAR2)
      RETURN NUMBER
   IS
      v_prog_unit       VARCHAR (100)       := gc_pkg || '.GET_INC_STATUS_ID';
      v_inc_status_id   jtf_rs_groups_vl.GROUP_ID%TYPE;
   BEGIN
      gv_debug_log_text := NULL;

      SELECT incident_status_id
        INTO v_inc_status_id
        FROM cs_incident_statuses_vl
       WHERE UPPER (NAME) = UPPER (p_inc_status)
         AND incident_subtype = gc_inc_subtype;

      RETURN v_inc_status_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || ' p_caller - '
                                            || p_caller
                                            || ' p_inc_status - '
                                            || p_inc_status
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
         RETURN NULL;
   END get_inc_status_id;

   PROCEDURE get_inc_location_info (
      p_caller            IN       VARCHAR2,
      p_legacy_acct_num   IN       VARCHAR2,
      p_party_id          OUT      NUMBER,
      p_party_name        OUT      VARCHAR2,
      p_party_type        OUT      VARCHAR2,
      p_account_id        OUT      NUMBER,
      p_party_site_id     OUT      NUMBER
   )
   IS
      v_prog_unit   VARCHAR (100) := gc_pkg || '.GET_INC_LOCATION_INFO';
   BEGIN
      xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                     p_new_text       => xxd_utils.gc_start,
                     p_prog_unit      => v_prog_unit
                    );

      SELECT hp.party_id, hp.party_name, ca.cust_account_id,
             ps.party_site_id, hp.party_type
        INTO p_party_id, p_party_name, p_account_id,
             p_party_site_id, p_party_type
        FROM hz_parties hp,
             hz_cust_accounts_all ca,
             hz_party_sites ps,
             hz_party_site_uses pu
       WHERE ca.account_number = p_legacy_acct_num
         AND ca.party_id = hp.party_id
         AND ps.party_id = ca.party_id
         AND ps.identifying_address_flag = 'Y'
         AND pu.party_site_id = ps.party_site_id
         AND pu.site_use_type = 'BUSINESS';

      xxd_utils.LOG (p_log_text       => gv_debug_log_clob,
                     p_new_text       => xxd_utils.gc_complete,
                     p_prog_unit      => v_prog_unit
                    );
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         p_party_id := NULL;
         p_party_name := NULL;
         p_party_type := NULL;
         p_account_id := NULL;
         p_party_site_id := NULL;
      WHEN OTHERS
      THEN
         xxd_utils.LOG (p_log_text       => gv_debug_log_text,
                        p_new_text       =>    xxd_utils.gc_exception
                                            || ' p_caller - '
                                            || p_caller
                                            || 'Party_info '
                                            || ', error: '
                                            || SQLERRM,
                        p_prog_unit      => v_prog_unit
                       );
         xxd_utils.alert (p_to             => xxd_email_addr.dl_doriis_tech,
                          p_prog_unit      => v_prog_unit,
                          p_mesg           => gv_debug_log_text
                         );
   END get_inc_location_info;
END xxdc_p2_conv_util;
/

No comments:

Post a Comment