Archiving - A generic solution - Part 5

-------------------------------------------------------- -- DDL for Procedures for HR_ARCHIVE ----------------------------------------- 12_ddl_cre_archive_procedures.sql

--------------------------------------------------------
--  DDL for Procedure PR_ARCHIVE_ERROR_LOG
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_ARCHIVE_ERROR_LOG (
    v_program_name IN VARCHAR2,
    v_table_name   IN VARCHAR2,
    v_remarks      IN VARCHAR2,
    v_sqlcode      IN VARCHAR2,
    v_sqlerrm      IN VARCHAR2,
    v_log_date     IN DATE,
    v_log_user     IN VARCHAR2,
    v_error_code OUT NUMBER )
AS
  ------------------------------------------------------------------------------------------------
  -- Program Name          :  PR_ARCHIVE_ERROR_LOG
  -- Description           :  Insert into error log
  -------------------------------------------------------------------------------------------------
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_err_desc VARCHAR2(500);
BEGIN
  INSERT
  INTO ARCHIVE_ERROR_LOG
    (
      NUM_ARCHIVE_LOG_ID,
      TXT_PROGRAM_NAME,
      TXT_TABLE_NAME,
      TXT_REMARKS,
      TXT_SQLCODE,
      TXT_SQLERRM,
      DAT_ARCHIVE_DATE
    )
    VALUES
    (
      SEQ_ARCHIVE_LOG_ID.nextval,
      v_program_name,
      v_table_name,
      v_remarks,
      v_sqlcode,
      v_sqlerrm,
      v_log_date
    );
  v_error_code := PKG_ARCHIVE.ERROR;
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  ROLLBACK;
  v_err_desc   := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
  v_error_code := SQLCODE;
  PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
END PR_ARCHIVE_ERROR_LOG;
/
--------------------------------------------------------
--  DDL for Procedure PR_ARCHIVE_LOG_EXCEPTIONS
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_ARCHIVE_LOG_EXCEPTIONS (
    ------------------------------------------------------------------------------------------------
    -- Program Name        :  PR_ARCHIVE_LOG_EXCEPTIONS
    -- Description         :  Insert Exception Data into Exception table For insert failed records
    -------------------------------------------------------------------------------------------------
    p_archive_log_header_id IN VARCHAR2,
    p_tbl_nm                IN VARCHAR2,
    p_remarks               IN VARCHAR2,
    p_sqlcode               IN VARCHAR2,
    p_sqlerrm               IN VARCHAR2 )
AS
  v_program_name VARCHAR2(100) := 'PR_ARCHIVE_LOG_EXCEPTIONS';
  v_table_name   VARCHAR2(100) := NULL;
  v_err_desc     VARCHAR2(500);
  v_error_code   NUMBER;
  v_key_column_arr TYPE_REF_STR;
  v_arr_cntr                  NUMBER := 0;
  i_archive_log_exceptions_id NUMBER;
BEGIN
  SELECT SEQ_ARCHIVE_LOG_EXCEPTIONS_ID.NEXTVAL
  INTO i_archive_log_exceptions_id
  FROM DUAL;
  INSERT
  INTO ARCHIVE_LOG_EXCEPTIONS
    (
      NUM_ARCHIVE_LOG_EXCEPTIONS_ID,
      NUM_ARCHIVE_LOG_HEADER_ID,
      TXT_TABLE_NM,
      NUM_EXCEPTION_ERR_CODE,
      TXT_EXCEPTION_ERR_DESC,
      TXT_REMARKS
    )
    VALUES
    (
      SEQ_ARCHIVE_LOG_EXCEPTIONS_ID.nextval,
      p_archive_log_header_id,
      p_tbl_nm,
      p_sqlcode,
      p_sqlerrm,
      p_remarks
    );
EXCEPTION
WHEN OTHERS THEN
  ROLLBACK;
  v_err_desc   := 'ERROR IN ' || v_program_name || ' at : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
  v_error_code := SQLCODE;
  PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
END PR_ARCHIVE_LOG_EXCEPTIONS;
/

--------------------------------------------------------
--  DDL for Procedure PR_UPD_ARCHIVE_BATCH_STATUS
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_UPD_ARCHIVE_BATCH_STATUS (
------------------------------------------------------------------------------------------------
-- Program Name        :  PR_UPD_ARCHIVE_BATCH_STATUS
-- Description         :  Update the status of the archive batch
-------------------------------------------------------------------------------------------------
    p_archive_batch_id NUMBER,
    p_archive_is_complete OUT VARCHAR2,
    p_error_code IN OUT NUMBER )
AS
  v_program_name VARCHAR2(100) := 'PR_UPD_ARCHIVE_BATCH_STATUS';
  v_table_name   VARCHAR2(100) := NULL;
  v_err_desc     VARCHAR2(500);

BEGIN
  p_error_code := PKG_ARCHIVE.SUCCESS;

  p_archive_is_complete := PKG_ARCHIVE.YES;
  
  BEGIN
    SELECT PKG_ARCHIVE.NO
    INTO p_archive_is_complete
    FROM DUAL
    WHERE EXISTS
      (SELECT 1
      FROM ARCHIVE_LOG_HEADER
      WHERE NUM_ARCHIVE_BATCH_ID  = p_archive_batch_id
      AND TXT_TBL_ARCHIVAL_STATUS = PKG_ARCHIVE.ARCHIVE_PARTIAL
      );
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_archive_is_complete := PKG_ARCHIVE.YES;
  END;
  IF p_archive_is_complete = PKG_ARCHIVE.YES THEN
    UPDATE ARCHIVE_LOG_MAIN
    SET TXT_ARCHIVAL_STATUS    = PKG_ARCHIVE.ARCHIVE_COMPLETE
    WHERE NUM_ARCHIVE_BATCH_ID = p_archive_batch_id;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
      v_err_desc   := 'Error in ' || v_program_name || '. Error at : '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      p_error_code := SQLCODE;
      PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_UPD_ARCHIVE_BATCH_STATUS;
/
--------------------------------------------------------
--  DDL for Procedure PR_UPD_ARCHIVE_COUNT_N_STATUS
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_UPD_ARCHIVE_COUNT_N_STATUS (
    ------------------------------------------------------------------------------------------------
    -- Program Name        :  PR_ARCHIVE_TABLE_DATA
    -- Description         :  Update count and status of records archived
    -------------------------------------------------------------------------------------------------
    p_archive_batch_id      NUMBER,
    p_archive_log_header_id NUMBER,
    p_table_name            VARCHAR2,
    p_tbl_archive_status OUT VARCHAR2,
    p_error_code IN OUT NUMBER )
AS
  v_program_name VARCHAR2(100) := 'PR_UPD_ARCHIVE_COUNT_N_STATUS';
  v_table_name   VARCHAR2(100) := NULL;
  v_err_desc     VARCHAR2(500);
  v_num_records_to_archive ARCHIVE_LOG_HEADER.NUM_RECORDS_TO_ARCHIVE%TYPE;
  v_num_records_archived ARCHIVE_LOG_HEADER.NUM_RECORDS_ARCHIVED%TYPE;
  v_num_records_failed_archival ARCHIVE_LOG_HEADER.NUM_RECORDS_FAILED_ARCHIVAL%TYPE;
  v_num_records_pending_archival ARCHIVE_LOG_HEADER.NUM_RECORDS_PENDING_ARCHIVAL%TYPE;
BEGIN
  p_error_code := PKG_ARCHIVE.SUCCESS;

  --Logic to be changed to select from ARCHIVE_PK_DTLS
  SELECT COUNT(1)
  INTO v_num_records_to_archive
  FROM ARCHIVE_PK_DTLS
  WHERE num_archive_batch_id    = p_archive_batch_id
  AND num_archive_log_header_id = p_archive_log_header_id
  AND txt_tbl_nm                = p_table_name;
  
  --ARCHIVE_PK_DTLS
  SELECT COUNT(1)
  INTO v_num_records_archived
  FROM ARCHIVE_PK_DTLS
  WHERE num_archive_batch_id    = p_archive_batch_id
  AND num_archive_log_header_id = p_archive_log_header_id
  AND txt_tbl_nm                = p_table_name
  AND txt_arch_stat             = PKG_ARCHIVE.ARCHIVE_COMPLETE;
  
  SELECT COUNT(1)
  INTO v_num_records_failed_archival
  FROM ARCHIVE_LOG_EXCEPTIONS
  WHERE NUM_ARCHIVE_LOG_HEADER_ID = p_archive_log_header_id
  AND TXT_TABLE_NM                = p_table_name;
  
  SELECT COUNT(1)
  INTO v_num_records_pending_archival
  FROM ARCHIVE_PK_DTLS
  WHERE num_archive_batch_id    = p_archive_batch_id
  AND num_archive_log_header_id = p_archive_log_header_id
  AND txt_tbl_nm                = p_table_name
  AND txt_arch_stat             = PKG_ARCHIVE.ARCHIVE_PENDING;
  
  --v_num_records_pending_archival := v_num_records_to_archive - (v_num_records_archived + v_num_records_failed_archival);
  IF v_num_records_pending_archival = 0 THEN
    p_tbl_archive_status           := PKG_ARCHIVE.ARCHIVE_COMPLETE;
  ELSE
    p_tbl_archive_status := PKG_ARCHIVE.ARCHIVE_PENDING;
  END IF;
  
  UPDATE ARCHIVE_LOG_HEADER
  SET NUM_RECORDS_TO_ARCHIVE      = v_num_records_to_archive,
    NUM_RECORDS_ARCHIVED          = v_num_records_archived,
    NUM_RECORDS_FAILED_ARCHIVAL   = v_num_records_failed_archival,
    NUM_RECORDS_PENDING_ARCHIVAL  = v_num_records_pending_archival,
    TXT_TBL_ARCHIVAL_STATUS       = p_tbl_archive_status
  WHERE NUM_ARCHIVE_LOG_HEADER_ID = p_archive_log_header_id
  AND NUM_ARCHIVE_BATCH_ID        = p_archive_batch_id
  AND TXT_TABLE_NAME              = p_table_name;

EXCEPTION
  WHEN OTHERS THEN
      v_err_desc   := 'Error in ' || v_program_name || '. Error at : '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      p_error_code := SQLCODE;
      PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);  
END PR_UPD_ARCHIVE_COUNT_N_STATUS;
/

--------------------------------------------------------
--  DDL for Procedure PR_INS_PK_DTLS
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_INS_PK_DTLS (
p_batch_id   NUMBER,
p_archive_log_hdr_id      NUMBER,
p_tbl_name   VARCHAR2,
p_rowid      VARCHAR2,
p_error_code OUT NUMBER
)
  ------------------------------------------------------------------------------------------------
  -- Program Name        :  PR_INS_PK_DTLS
  -- Description         :  Insert Key data to be archived into Temporary table
  -------------------------------------------------------------------------------------------------
AS
  v_program_name VARCHAR2(100) := 'PR_INSERT_ARCHIVE_PK_DATA';
  v_table_name   VARCHAR2(100) := NULL;
  ARCHIVE_EXCEPTION   EXCEPTION;
  v_err_desc     VARCHAR2(500);
  arr_pk_cons_cols    tab_col_dtls;
  arr_pk_cons_cols_dtyp    tab_col_dtls;
  v_constraint_nm     ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
  v_sql               VARCHAR2(4000);
  v_tbl_pk_val        ARCHIVE_PK_DTLS.TXT_PK_COL_VALS%TYPE;
  v_tbl_pk_nm         ARCHIVE_PK_DTLS.TXT_PK_COL_NMS%TYPE;
  v_tbl_pk_dtyp         ARCHIVE_PK_DTLS.TXT_PK_COL_DTYPS%TYPE;
  v_owner             VARCHAR2(100) := PKG_ARCHIVE.MAIN_SCHEMA;
  PNDNG               CONSTANT VARCHAR2(10) := PKG_ARCHIVE.ARCHIVE_PENDING;
 
BEGIN

  SELECT CONSTRAINT_NAME
    INTO v_constraint_nm
   FROM ALL_CONSTRAINTS 
  WHERE OWNER = v_owner
    AND TABLE_NAME = p_tbl_name
    AND CONSTRAINT_TYPE = 'P';
    
    SELECT acc.COLUMN_NAME, DATA_TYPE 
    BULK COLLECT 
    INTO arr_pk_cons_cols, arr_pk_cons_cols_dtyp
    FROM ALL_CONS_COLUMNS acc, ALL_TAB_COLUMNS atc
    where 1=1 
    AND acc.TABLE_NAME = atc.TABLE_NAME
    AND acc.COLUMN_NAME = atc.COLUMN_NAME
    AND acc.OWNER = atc.OWNER
    AND acc.OWNER = v_owner
    AND acc.TABLE_NAME = p_tbl_name
    AND acc.CONSTRAINT_NAME = v_constraint_nm;
    
  v_sql := 'SELECT ';
  IF arr_pk_cons_cols.COUNT > 0
  THEN
    FOR i IN 1 .. arr_pk_cons_cols.COUNT -- arr_pk_cons_cols.FIRST .. arr_pk_cons_cols.LAST
    LOOP --{
       IF arr_pk_cons_cols_dtyp(i) = 'DATE'
       THEN
         v_sql := v_sql || arr_pk_cons_cols(i);
         v_sql := v_sql || '|| '',''|| ';
      ELSE    
         v_sql := v_sql || arr_pk_cons_cols(i);
         v_sql := v_sql || '|| '',''|| ';
      END IF;   
    END LOOP; --}
    v_sql := RTRIM(v_sql, '|| '',''|| ');
  END IF;
  v_sql := v_sql || ' FROM ' || v_owner || '.' || p_tbl_name || ' WHERE ROWID = :p_rowid';
  
  EXECUTE IMMEDIATE v_sql INTO v_tbl_pk_val USING p_rowid;
  
  SELECT LISTAGG(COLUMN_VALUE, ',')
  INTO v_tbl_pk_nm
  FROM TABLE(arr_pk_cons_cols);
  
  SELECT LISTAGG(COLUMN_VALUE, ',')
  INTO v_tbl_pk_dtyp
  FROM TABLE(arr_pk_cons_cols_dtyp);
  
   INSERT INTO ARCHIVE_PK_DTLS
   (
     NUM_ARCHIVE_BATCH_ID,
     NUM_ARCHIVE_LOG_HEADER_ID,
     TXT_TBL_NM,
     TXT_PK_COL_NMS,
     TXT_PK_COL_VALS,
     TXT_PK_COL_DTYPS,
     TXT_ARCH_STAT
   )
   VALUES
   (
   p_batch_id,
   p_archive_log_hdr_id,
   p_tbl_name,
   v_tbl_pk_nm,
   v_tbl_pk_val,
   v_tbl_pk_dtyp,
   PNDNG
   );
   
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
  ROLLBACK;
  PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
  
WHEN OTHERS THEN
  ROLLBACK;
  v_err_desc   := 'Error in ' || v_program_name || '. Error at : '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
  p_error_code := SQLCODE;
  PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);   
END PR_INS_PK_DTLS;
/

--------------------------------------------------------
--  DDL for Procedure PR_INSERT_ARCHIVE_PK_DATA
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_INSERT_ARCHIVE_PK_DATA (
    p_archive_batch_id   NUMBER,
    p_archive_log_hdr_id NUMBER,
    p_tbl_nm             VARCHAR2,
    p_ref_rowid_arr TYP_REF_STR,
    p_error_code OUT NUMBER)
  ------------------------------------------------------------------------------------------------
  -- Program Name        :  PR_INSERT_ARCHIVE_PK_DATA
  -- Description         :  Insert Key data to be archived into ARCHIVE_PK_DTLS table
  -------------------------------------------------------------------------------------------------
AS
  v_program_name VARCHAR2(100) := 'PR_INSERT_ARCHIVE_PK_DATA';
  v_table_name   VARCHAR2(100) := NULL;
  ARCHIVE_EXCEPTION   EXCEPTION;
  v_err_desc     VARCHAR2(500);

BEGIN
  p_error_code := PKG_ARCHIVE.SUCCESS;
    
  FOR i IN p_ref_rowid_arr.FIRST..p_ref_rowid_arr.LAST
  LOOP
    PR_INS_PK_DTLS(p_archive_batch_id, p_archive_log_hdr_id, p_tbl_nm, p_ref_rowid_arr(i),p_error_code);
    IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
      v_err_desc   := 'ERROR IN PR_INS_PK_DTLS. Error code : '|| p_error_code; 
      RAISE ARCHIVE_EXCEPTION;
    END IF;    
  END LOOP;

EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
  ROLLBACK;
  PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
  
WHEN OTHERS THEN
      ROLLBACK;
      v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' ||  DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      p_error_code := SQLCODE;
      PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);

END PR_INSERT_ARCHIVE_PK_DATA;
/

--------------------------------------------------------
--  DDL for Procedure PR_ARCHIVE_RULE_PROCESS
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_ARCHIVE_RULE_PROCESS (
------------------------------------------------------------------------------------------------ 
-- Program Name        :  PR_ARCHIVE_RULE_PROCESS
-- Description         :  Archive data processing for rule passed
-------------------------------------------------------------------------------------------------
p_archive_batch_id        NUMBER,
p_archive_log_hdr_id      NUMBER,
p_rule_cd                 VARCHAR2,
p_error_code     IN OUT      NUMBER)
IS

v_program_name                VARCHAR2(100) := 'PR_ARCHIVE_RULE_PROCESS';
v_table_name                  VARCHAR2(100) := NULL;
ARCHIVE_EXCEPTION             EXCEPTION;
v_err_desc                    VARCHAR2(500);

CURSOR C1
IS
SELECT TXT_RULE_QUERY, TXT_PRIMARY_TABLE
FROM ARCHIVE_RULES
WHERE TXT_RULE_CD = p_rule_cd;

TYPE cur_typ IS REF CURSOR;
CUR cur_typ;
--  TYPE values_t IS TABLE OF NUMBER;

l_ref_number_arr   TYP_REF_NUMBER;
l_ref_date_arr     TYP_REF_DATE;
l_ref_rowid_arr    TYP_REF_STR;

v_sql   VARCHAR2(4000);
BEGIN
  p_error_code := PKG_ARCHIVE.SUCCESS;

--Insert all records into a temp table for restartibility logic   
  FOR rec IN C1
  LOOP
    v_sql := rec.TXT_RULE_QUERY;
  
      OPEN CUR FOR v_sql;  
      LOOP
       NULL;
      /* CALL PROCEDURE TO INSERT INTO TEMP_ARCHIVE PK COLS/VALS and status PENDING */
        FETCH CUR BULK COLLECT INTO l_ref_rowid_arr LIMIT 5000;
        IF l_ref_rowid_arr.COUNT > 0
        THEN
          PR_INSERT_ARCHIVE_PK_DATA (p_archive_batch_id, p_archive_log_hdr_id,rec.TXT_PRIMARY_TABLE, l_ref_rowid_arr, p_error_code);
          IF p_error_code != PKG_ARCHIVE.SUCCESS
          THEN
            v_table_name := rec.TXT_PRIMARY_TABLE;
            v_err_desc   := 'ERROR IN PR_INSERT_ARCHIVE_PK_DATA. Error code : '|| p_error_code; 
            RAISE ARCHIVE_EXCEPTION;
          END IF;
        END IF;  
      EXIT WHEN CUR%NOTFOUND; 
      END LOOP;
  CLOSE CUR;
  END LOOP;
--COMMIT;

EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
    ROLLBACK;
    PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
  
WHEN OTHERS THEN
    ROLLBACK;
    v_err_desc := 'ERROR IN  ' || v_program_name || ' AT: ' ||  DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
    p_error_code := SQLCODE;
    PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END;
/

--------------------------------------------------------
--  DDL for Procedure PR_ARCHIVE_TABLE_INSRT
--------------------------------------------------------
set define off;

create or replace PROCEDURE PR_ARCHIVE_TABLE_INSRT (
    p_archive_log_header_id NUMBER,
    p_table_name            VARCHAR2,
    p_error_code IN OUT NUMBER)
AS
  ------------------------------------------------------------------------------------------------
  -- Program Name        :  PR_ARCHIVE_TABLE_INSRT
  -- Description         :  Insert data into archive table
  -------------------------------------------------------------------------------------------------
TYPE cur_typ
IS
  REF
  CURSOR;
    CUR cur_typ;
    v_program_name VARCHAR2(100) := 'PR_ARCHIVE_TABLE_INSRT';
    v_table_name   VARCHAR2(100) := p_table_name;
    v_owner        VARCHAR2(100) :=  PKG_ARCHIVE.MAIN_SCHEMA;
    v_arch_owner   VARCHAR2(100) :=  PKG_ARCHIVE.ARCHIVE_SCHEMA;
    v_err_desc     VARCHAR2(500);
    v_rowid_arr TYP_REF_STR := TYP_REF_STR();
    v_sql                VARCHAR2(4000);
    v_tbl_archive_status VARCHAR2(20) := pkg_archive.archive_complete;

    tab_col_nm TYPE_REF_STR;
    tab_col_val TYPE_REF_STR;
    tab_col_dtyp TYPE_REF_STR;
    i                    INTEGER := 0;
    j                    INTEGER := 0;
    v_sql_insrt          VARCHAR2(32000);
    v_sql_delet          VARCHAR2(32000);
    v_sql_clmn           VARCHAR2(32000);
    v_sql_clmn_sel       VARCHAR2(32000);
    v_archive_log_hdr_id CONSTANT VARCHAR2(100) := 'ARCHIVE_LOG_HEADER_ID';
    v_archive_date       CONSTANT VARCHAR2(100) := 'ARCHIVE_DATE';
    
    i_commit_cnt    NUMBER := 0;
    CURSOR C1
    IS
      SELECT TXT_TBL_NM,
        TXT_PK_COL_NMS,
        TXT_PK_COL_VALS,
        TXT_PK_COL_DTYPS
      FROM ARCHIVE_PK_DTLS
      WHERE TXT_TBL_NM  = p_table_name
      AND TXT_ARCH_STAT = PKG_ARCHIVE.ARCHIVE_PENDING;
    CURSOR C2
    IS
      SELECT COLUMN_NAME,
        DATA_TYPE
      FROM ALL_TAB_COLUMNS
      WHERE TABLE_NAME     = p_table_name
      AND OWNER            = v_arch_owner
      AND COLUMN_NAME NOT IN ('ARCHIVE_LOG_HEADER_ID','ARCHIVE_DATE');
  BEGIN
    p_error_code := PKG_ARCHIVE.SUCCESS;
    i            := 0;
    FOR rec IN C1
    LOOP --{
      i            := i+1;
      j            := j+1;
      v_sql        := 'SELECT rowid FROM ';
      v_sql        := v_sql || v_owner || '.' || rec.TXT_TBL_NM || ' WHERE 1=1 ';
      tab_col_nm   := fxn_str_to_columns(rec.TXT_PK_COL_NMS,',');
      tab_col_val  := fxn_str_to_columns(rec.TXT_PK_COL_VALS,',');
      tab_col_dtyp := fxn_str_to_columns(rec.TXT_PK_COL_DTYPS,',');
      FOR i IN 1..tab_col_nm.COUNT
      LOOP --{
        IF tab_col_dtyp(i) = 'NUMBER' THEN
          v_sql           := v_sql || ' AND ' || tab_col_nm(i) || ' = ' || tab_col_val(i);
        ELSIF tab_col_dtyp(i) = 'VARCHAR2' THEN
          v_sql           := v_sql || ' AND ' || tab_col_nm(i) || ' = ''' || tab_col_val(i) || '';
        ELSIF tab_col_dtyp(i) = 'DATE' THEN
          v_sql           := v_sql || ' AND TRUNC(' || tab_col_nm(i) || ') = TO_DATE(''' || tab_col_val(i) || ''',''DD-MON-RRRR HH24:MI:SS'') ';
        ELSE
          v_sql           := v_sql || ' AND ' || tab_col_nm(i) || ' = ''' || tab_col_val(i) || '';
        END IF;
      END LOOP;
      --USE 4TH ORDER DYNAMIC QUERY
      v_rowid_arr.EXTEND;
--      dbms_output.put_line('v_sql ' || v_sql);
      EXECUTE IMMEDIATE v_sql INTO v_rowid_arr(j);-- USING p_table_name, tab_col_nm(1), tab_col_val(1) ;
    END LOOP;                                     --}
    FOR rec IN C2
    LOOP --{
      v_sql_clmn := v_sql_clmn || rec.COLUMN_NAME || ',';
    END LOOP;
    v_sql_insrt := 'INSERT INTO ' || v_arch_owner || '.' || p_table_name || '(';
    v_sql_insrt := v_sql_insrt || v_sql_clmn || v_archive_log_hdr_id || ',' || v_archive_date || ')';
    v_sql_insrt := v_sql_insrt || 'SELECT ' || v_sql_clmn || ' :p_archive_log_header_id,:SYSDT';
    v_sql_insrt := v_sql_insrt || ' FROM ' || v_owner || '.' || p_table_name;
    v_sql_insrt := v_sql_insrt || ' WHERE rowid = :p_rowid';

    v_sql_delet := 'DELETE FROM ' || v_owner || '.' || p_table_name;
    v_sql_delet := v_sql_delet || ' WHERE rowid = :p_rowid';
    
--    dbms_output.put_line('v_sql_insrt ' || v_sql_insrt);
--    dbms_output.put_line('v_sql_delet ' || v_sql_delet);

    FOR i IN 1 .. v_rowid_arr.COUNT
    LOOP --{
      i_commit_cnt := i_commit_cnt + 1;
      BEGIN
        EXECUTE IMMEDIATE v_sql_insrt USING p_archive_log_header_id, SYSDATE, v_rowid_arr(i);
      EXCEPTION
        WHEN OTHERS
        THEN
          v_err_desc := 'Error inserting archive record for ' || p_table_name;
          PR_ARCHIVE_LOG_EXCEPTIONS(p_archive_log_header_id,p_table_name,v_err_desc,SQLCODE, SQLERRM);
          CONTINUE;
      END;
      EXECUTE IMMEDIATE v_sql_delet USING v_rowid_arr(i); --exception to be handled for delete. Here if delete fails, insert into archvive table should also be deleted.
      IF i_commit_cnt = PKG_ARCHIVE.COMMIT_CNT
      THEN
        COMMIT;
        i_commit_cnt := 0;
      END IF;
    END LOOP; --}


EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' ||  DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
        p_error_code := SQLCODE;
        PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END;
/

--------------------------------------------------------
--  DDL for Procedure PR_ARCHIVE_TABLE_DATA
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_ARCHIVE_TABLE_DATA (
    ------------------------------------------------------------------------------------------------
    -- Program Name        :  PR_ARCHIVE_TABLE_DATA
    -- Description         :  Archive data for the required table
    -------------------------------------------------------------------------------------------------
    p_archive_batch_id      NUMBER,
    p_archive_log_header_id NUMBER,
    p_table_name            VARCHAR2,
    p_error_code IN OUT NUMBER )
AS
  v_program_name VARCHAR2(100) := 'PR_ARCHIVE_TABLE_DATA';
  v_table_name   VARCHAR2(100) := NULL;
  ARCHIVE_EXCEPTION   EXCEPTION;
  v_err_desc     VARCHAR2(500);

BEGIN
  p_error_code := PKG_ARCHIVE.SUCCESS;
  PR_ARCHIVE_TABLE_INSRT(p_archive_log_header_id, p_table_name, p_error_code); -- TO CHANGE NAME
  IF p_error_code = PKG_ARCHIVE.SUCCESS THEN
    UPDATE ARCHIVE_PK_DTLS
    SET TXT_ARCH_STAT             = PKG_ARCHIVE.ARCHIVE_COMPLETE
    WHERE NUM_ARCHIVE_BATCH_ID    = p_archive_batch_id
    AND NUM_ARCHIVE_LOG_HEADER_ID = p_archive_log_header_id
    AND TXT_TBL_NM                = p_table_name;
  ELSE
     v_err_desc   := 'ERROR IN PR_ARCHIVE_TABLE_INSRT. Error code : '|| p_error_code; 
      RAISE ARCHIVE_EXCEPTION;
  END IF;
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
  ROLLBACK;
  PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
  
WHEN OTHERS THEN
      ROLLBACK;
      v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' ||  DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      p_error_code := SQLCODE;
      PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_ARCHIVE_TABLE_DATA;
/

--------------------------------------------------------
--  DDL for Procedure PR_ARCHIVE_PROCESS
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_ARCHIVE_PROCESS (
    ------------------------------------------------------------------------------------------------
    -- Program Name        :  PR_ARCHIVE_PROCESS
    -- Description         :  Processing logic for archiving data for each rule
    -------------------------------------------------------------------------------------------------
    p_archive_batch_id   NUMBER,
    p_archive_log_hdr_id NUMBER,
    p_rule_cd            NUMBER,
    p_error_code OUT NUMBER)
AS
  V_PROGRAM_NAME VARCHAR2(100) := 'PR_ARCHIVE_PROCESS';
  V_TABLE_NAME   VARCHAR2(100) := NULL;
  ARCHIVE_EXCEPTION   EXCEPTION;
  v_err_desc     VARCHAR2(500);
  CURSOR C1(p_archive_batch_id NUMBER)
  IS
    SELECT NUM_ARCHIVE_LOG_HEADER_ID,
      TXT_TABLE_NAME
    FROM ARCHIVE_LOG_HEADER
    WHERE NUM_ARCHIVE_BATCH_ID   = p_archive_batch_id
    AND TXT_TBL_ARCHIVAL_STATUS IN (PKG_ARCHIVE.ARCHIVE_PARTIAL,PKG_ARCHIVE.ARCHIVE_PENDING)
    ORDER BY DECODE(TXT_TBL_ARCHIVAL_STATUS,PKG_ARCHIVE.ARCHIVE_PARTIAL,1,PKG_ARCHIVE.ARCHIVE_PENDING,2);
  p_archive_status VARCHAR2(20);
BEGIN
 p_error_code := PKG_ARCHIVE.SUCCESS; 
  /* New rule processing */
  -- this should happen after pending are all completed
  IF p_rule_cd IS NOT NULL THEN
    PR_ARCHIVE_RULE_PROCESS(p_archive_batch_id,p_archive_log_hdr_id, p_rule_cd, p_error_code);
    IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
      v_err_desc   := 'ERROR IN PR_ARCHIVE_RULE_PROCESS. Error code : '|| p_error_code; 
      RAISE ARCHIVE_EXCEPTION;
    END IF;  
  END IF;
  FOR rec IN C1(p_archive_batch_id)
  LOOP --{
    PR_ARCHIVE_TABLE_DATA(p_archive_batch_id, rec.NUM_ARCHIVE_LOG_HEADER_ID, rec.TXT_TABLE_NAME, p_error_code );
    IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
      ROLLBACK;
      v_table_name := rec.TXT_TABLE_NAME;
      v_err_desc   := 'ERROR IN PR_ARCHIVE_TABLE_DATA. Error code : '|| p_error_code;
      PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
      CONTINUE; -- continue to the next record to be archived.
    END IF;
    PR_UPD_ARCHIVE_COUNT_N_STATUS (p_archive_batch_id, rec.NUM_ARCHIVE_LOG_HEADER_ID, rec.TXT_TABLE_NAME,p_archive_status, p_error_code);
    IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
      ROLLBACK;
      v_table_name := rec.TXT_TABLE_NAME;
      v_err_desc   := 'ERROR IN PR_UPD_ARCHIVE_COUNT_N_STATUS. Error code : '|| p_error_code;
      PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
      CONTINUE; -- continue to the next record to be archived.
    END IF;
    COMMIT; --Commit archiving of one table
  END LOOP; --}
  PR_UPD_ARCHIVE_BATCH_STATUS(p_archive_batch_id, p_archive_status, p_error_code );
  IF p_error_code != pkg_archive.SUCCESS -- AND p_archive_status= PKG_ARCHIVE.YES
  THEN
      v_err_desc   := 'ERROR IN PR_UPD_ARCHIVE_BATCH_STATUS. Error code : '|| p_error_code; 
      RAISE ARCHIVE_EXCEPTION;    
  END IF;
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
  ROLLBACK;
  PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
  
WHEN OTHERS THEN
      ROLLBACK;
      v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' ||  DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      p_error_code := SQLCODE;
      PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);

END PR_ARCHIVE_PROCESS;
/

--------------------------------------------------------
--  DDL for Procedure PR_ARCHIVE_PENDING
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_ARCHIVE_PENDING (p_error_code OUT NUMBER )
  ------------------------------------------------------------------------------------------------
  -- Program Name          :  PR_ARCHIVE_PENDING
  -- Description           :  Data Archiving for pending archive records
  -------------------------------------------------------------------------------------------------
AS
  v_program_name VARCHAR2(100) := 'PR_ARCHIVE_PENDING';
  v_table_name   VARCHAR2(100) := NULL;
  ARCHIVE_EXCEPTION   EXCEPTION;
  v_err_desc     VARCHAR2(500);
  v_error_code   NUMBER       := PKG_ARCHIVE.SUCCESS;
  CURSOR C1
  IS
    SELECT alh.NUM_ARCHIVE_BATCH_ID,
      alh.NUM_ARCHIVE_LOG_HEADER_ID,
      alh.TXT_RULE_CD
    FROM ARCHIVE_LOG_MAIN alm,
      ARCHIVE_LOG_HEADER alh
    WHERE alm.NUM_ARCHIVE_BATCH_ID = alh.NUM_ARCHIVE_BATCH_ID
    AND TXT_ARCHIVAL_STATUS        = PKG_ARCHIVE.ARCHIVE_PENDING;
  v_archive_batch_id NUMBER;
BEGIN
  p_error_code := PKG_ARCHIVE.SUCCESS;
  -- For pending Archival Rule. At a time only one rule may be pending due to previous batch getting aborted.
  FOR rec IN C1
  LOOP --{
    PR_ARCHIVE_PROCESS(rec.NUM_ARCHIVE_BATCH_ID,rec.NUM_ARCHIVE_LOG_HEADER_ID, rec.TXT_RULE_CD, p_error_code);
    IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
      v_err_desc   := 'ERROR IN PR_ARCHIVE_PROCESS. Error code : '|| p_error_code; 
      RAISE ARCHIVE_EXCEPTION;
    END IF;
  END LOOP; --}
  RETURN;
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
  ROLLBACK;
  PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
  
WHEN OTHERS THEN
      ROLLBACK;
      v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' ||  DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      p_error_code := SQLCODE;
      PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_ARCHIVE_PENDING;
/



--------------------------------------------------------
--  DDL for Procedure PR_GENERATE_ALH_QUERY_STR
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_GENERATE_ALH_QUERY_STR (
    ------------------------------------------------------------------------------------------------
    -- Program Name        :  PR_GENERATE_ALH_QUERY_STR
    -- Description         :  Generate Insert string for ARCHIVE_LOG_HEADER table
    -------------------------------------------------------------------------------------------------
    p_archive_batch_id NUMBER,
    p_rule_cd          VARCHAR2,
    p_table_name       VARCHAR2,
    p_query_str OUT VARCHAR2,
    p_archive_log_header_id OUT NUMBER,
    p_error_code IN OUT NUMBER )
AS
  v_program_name          VARCHAR2(100) := 'PR_GENERATE_ALH_QUERY_STR';
  v_table_name            VARCHAR2(100) := NULL;
  v_err_desc              VARCHAR2(500);
  i_archive_log_header_id NUMBER;
BEGIN
  p_error_code := PKG_ARCHIVE.SUCCESS;

  SELECT SEQ_ARCHIVE_LOG_HEADER_ID.NEXTVAL
  INTO i_archive_log_header_id
  FROM DUAL;
  
  p_archive_log_header_id       := i_archive_log_header_id;
  p_query_str                   := 'INSERT INTO ' ||PKG_ARCHIVE.ARCHIVE_SCHEMA || '.ARCHIVE_LOG_HEADER( ';
  p_query_str                   := p_query_str || 'NUM_ARCHIVE_LOG_HEADER_ID, ';
  p_query_str                   := p_query_str || 'TXT_RULE_CD, ';
  p_query_str                   := p_query_str || 'NUM_ARCHIVE_BATCH_ID, ';
  p_query_str                   := p_query_str || 'TXT_TABLE_NAME, ';
  p_query_str                   := p_query_str || 'TXT_TBL_ARCHIVAL_STATUS) ';
  
  p_query_str                   := p_query_str || 'VALUES (';
  p_query_str                   := p_query_str || i_archive_log_header_id ||', ';
  p_query_str                   := p_query_str || '''' || p_rule_cd || ''', ';
  p_query_str                   := p_query_str || p_archive_batch_id || ', ';
  p_query_str                   := p_query_str || '''' || p_table_name || ''', ';
  p_query_str                   := p_query_str || '''PENDING'')';

EXCEPTION

WHEN OTHERS THEN
      ROLLBACK;
      v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' ||  DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      p_error_code := SQLCODE;
      PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
END PR_GENERATE_ALH_QUERY_STR;
/
--------------------------------------------------------
--  DDL for Procedure PR_INSERT_ARCHIVE_LOG_HEADER
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_INSERT_ARCHIVE_LOG_HEADER (
    ------------------------------------------------------------------------------------------------
    -- Program Name        :  PR_INSERT_ARCHIVE_LOG_HEADER
    -- Description         :  Insert data into ARCHIVE LOG HEADER table for a given rule
    -------------------------------------------------------------------------------------------------
    p_archive_batch_id   IN VARCHAR2,
    p_rule_cd            IN VARCHAR2,
    p_archive_table_list IN VARCHAR2,
    p_out_archve_log_hdr OUT NUMBER,
    p_error_code IN OUT NUMBER )
AS
  V_PROGRAM_NAME VARCHAR2(100) := 'PR_INSERT_ARCHIVE_LOG_HEADER';
  ARCHIVE_EXCEPTION   EXCEPTION;
  V_TABLE_NAME   VARCHAR2(100) := NULL;
  V_ERR_DESC     VARCHAR2(500);
  v_output_arr TYPE_REF_STR;
  v_output NUMBER;
  v_rec_key_columns TYP_REC_TABLE_KEY_COLS := TYP_REC_TABLE_KEY_COLS(NULL,NULL,NULL,NULL,NULL,NULL);
  v_query_str VARCHAR2(4000);
BEGIN
  p_error_code := PKG_ARCHIVE.SUCCESS;

  v_output_arr := FXN_STR_TO_COLUMNS(p_archive_table_list,',');

  FOR i IN v_output_arr.FIRST .. v_output_arr.LAST
  LOOP --{

    PR_GENERATE_ALH_QUERY_STR(p_archive_batch_id, p_rule_cd, v_output_arr(i),  v_query_str,p_out_archve_log_hdr, p_error_code);
    IF p_error_code != PKG_ARCHIVE.SUCCESS THEN
      v_err_desc   := 'ERROR IN PR_GENERATE_ALH_QUERY_STR. Error code : '|| p_error_code; 
      RAISE ARCHIVE_EXCEPTION;
    END IF;

    BEGIN
      EXECUTE IMMEDIATE v_query_str;
    EXCEPTION
    WHEN OTHERS THEN
      p_error_code := SQLCODE;
      v_err_desc := 'Error in Execution of Query: ' || v_query_str;
      RAISE ARCHIVE_EXCEPTION;
    END;

  END LOOP;--}
EXCEPTION
WHEN ARCHIVE_EXCEPTION
THEN
  ROLLBACK;
  PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
  
WHEN OTHERS THEN
      ROLLBACK;
      v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' ||  DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      p_error_code := SQLCODE;
      PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
  
END PR_INSERT_ARCHIVE_LOG_HEADER;
/
--------------------------------------------------------
--  DDL for Procedure PR_INSERT_ARCHIVE_LOG_MAIN
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_INSERT_ARCHIVE_LOG_MAIN (
    ------------------------------------------------------------------------------------------------
    -- Program Name        :  PR_INSERT_ARCHIVE_LOG_MAIN
    -- Description         :  Data Archiving Main
    -------------------------------------------------------------------------------------------------
    p_archive_batch_id OUT NUMBER,
    p_error_code OUT NUMBER )
AS
  v_program_name VARCHAR2(100) := 'PR_INSERT_ARCHIVE_LOG_MAIN';
  v_table_name   VARCHAR2(100) := NULL;
  v_err_desc     VARCHAR2(500);
BEGIN

  p_error_code := PKG_ARCHIVE.SUCCESS;
  
  --Procedure will insert entry into ARCHIVE_LOG_MAIN table and pass archive_batch_id as the output
  SELECT SEQ_ARCHIVE_BATCH_ID.NEXTVAL
  INTO p_archive_batch_id
  FROM DUAL;
  INSERT
  INTO ARCHIVE_LOG_MAIN
    (
      NUM_ARCHIVE_BATCH_ID,
      DAT_ARCHIVE_DATE,
      TXT_ARCHIVAL_STATUS
    )
    VALUES
    (
      p_archive_batch_id,
      SYSDATE,
      PKG_ARCHIVE.ARCHIVE_PENDING
    );
EXCEPTION
WHEN OTHERS THEN
      ROLLBACK;
      v_err_desc := 'ERROR IN ' || v_program_name || ' at : ' ||  DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      p_error_code := SQLCODE;
      PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, p_error_code);
  
END PR_INSERT_ARCHIVE_LOG_MAIN;
/


--------------------------------------------------------
--  DDL for Procedure PR_ARCHIVE_MACHINE
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE PR_ARCHIVE_MACHINE 
  ------------------------------------------------------------------------------------------------
  -- Program Name          :  PR_ARCHIVE_MACHINE
  -- Description           :  Data Archiving Main
  -------------------------------------------------------------------------------------------------
AS
  v_program_name   VARCHAR2(100) := 'PR_ARCHIVE_MACHINE';
  v_table_name     VARCHAR2(100) := NULL;
  ARCHIVE_EXCEPTION   EXCEPTION;
  v_err_desc       VARCHAR2(500);
  v_error_code     NUMBER       := PKG_ARCHIVE.SUCCESS;
  v_archve_log_hdr NUMBER;
  CURSOR C2
  IS
    SELECT TXT_RULE_CD,
      TXT_ARCHIVE_TABLE_LIST
    FROM ARCHIVE_RULES
    WHERE TXT_ACTIVE_FLG = 'Y';
  v_archive_batch_id NUMBER;
BEGIN
  DBMS_OUTPUT.DISABLE;
  v_error_code := PKG_ARCHIVE.SUCCESS; 
  -- For pending Archival Rule. At a time only one rule may be pending due to previous batch getting aborted.
  PR_ARCHIVE_PENDING(v_error_code);
  IF v_error_code != PKG_ARCHIVE.SUCCESS THEN
      v_err_desc   := 'ERROR IN PR_ARCHIVE_PENDING. Error code : '|| v_error_code; 
      RAISE ARCHIVE_EXCEPTION;
  END IF;
  --For New Archival Rules
  FOR rec2 IN C2
  LOOP --{

    PR_INSERT_ARCHIVE_LOG_MAIN(v_archive_batch_id, v_error_code);
    IF v_error_code != PKG_ARCHIVE.SUCCESS THEN
        ROLLBACK;
        v_err_desc   := 'ERROR IN PR_INSERT_ARCHIVE_LOG_MAIN. Error code : '|| v_error_code || ' .Archive Batch Id: ' || v_archive_batch_id; 
        PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
        CONTINUE;
    END IF;    
    PR_INSERT_ARCHIVE_LOG_HEADER(v_archive_batch_id, rec2.TXT_RULE_CD, rec2.TXT_ARCHIVE_TABLE_LIST,v_archve_log_hdr, v_error_code);
    IF v_error_code != PKG_ARCHIVE.SUCCESS THEN
        ROLLBACK;
        v_err_desc   := 'ERROR IN PR_INSERT_ARCHIVE_LOG_HEADER. Error code : '|| v_error_code 
                         || ' .Archive Batch Id: ' || v_archive_batch_id || ' .Archive Log Header Id: ' || v_archve_log_hdr; 
        PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
        CONTINUE;
    END IF;
    PR_ARCHIVE_PROCESS(v_archive_batch_id, v_archve_log_hdr, rec2.TXT_RULE_CD, v_error_code);
    IF v_error_code != PKG_ARCHIVE.SUCCESS THEN
        ROLLBACK;
        v_err_desc   := 'ERROR IN PR_ARCHIVE_PROCESS. Error code : '|| v_error_code
                        || ' .Archive Batch Id: ' || v_archive_batch_id || ' .Archive Log Header Id: ' || v_archve_log_hdr;
        PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
        CONTINUE;
    END IF;
  END LOOP; --}
  RETURN;
EXCEPTION
    WHEN ARCHIVE_EXCEPTION
    THEN
        ROLLBACK;
        PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
    WHEN OTHERS THEN
        ROLLBACK;
        v_err_desc := 'ERROR IN ' || v_program_name || ' AT: ' ||  DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
        v_error_code := SQLCODE;
        PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
END PR_ARCHIVE_MACHINE;
/