ID S_OWNER  SOURCE_TABLE     A_OWNER    ARC_TABLE             CONDITION_COLUMN     PERIOD_VALUE   PERIOD_UNIT

1    wedb     Auction_table    wedb     Arc_Auction_table	Auction_date	       15               Days
1    wedb     Sales_table      wedb     ArcSales_table	        Sales_date	       180              Days
1    hr       Accounts_table   hr       Arc_Accounts_table	Account_date	        2	        Years
2    concor   Concur_table     con      Arc_Concur_table        Last_update_date        4               Months

condition_column<=(sysdate-15 days)
condition_column<=(sysdate-180 days)
condition_column<=(sysdate-2 years)
condition_column<=(sysdate-4 months)

CREATE OR REPLACE PROCEDURE WEDB.procedure_control(
   P_ID IN NUMBER)
IS
   CURSOR C
   IS
        SELECT ID,S_OWNER,SOURCE_TABLE,A_OWNER,ARC_TABLE,CONDITION_COLUMN,PERIOD_VALUE,PERIOD_UNIT
          FROM wedb.CONTROL
         WHERE ID = p_id
      ORDER BY ID, SOURCE_TABLE;
      rec C%ROWTYPE;
BEGIN
   FOR I IN C
   LOOP
    EXECUTE IMMEDIATE
               'INSERT INTO '
            || rec.A_OWNER
            || '.'
            || rec.ARC_TABLE
            || '(SELECT * FROM '
            || rec.S_OWNER
            || '.'
            || rec.SOURCE_TABLE
            || ' WHERE '
            || rec.CONDITION_COLUMN
            || '<=I want to pass dynamic condition'
            || ')';

         EXECUTE IMMEDIATE
               'DELETE FROM '
            || rec.S_OWNER
            || '.'
            || rec.SOURCE_TABLE
            || ' WHERE '
            || rec.CONDITION_COLUMN
            || '<= I want to pass dynamic condition'
            || '';

      COMMIT;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (
         'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
END procedure_control;
/

case when period_unit = 'Days'   then sysdate - period_value
     when period_unit = 'Months' then add_months(sysdate, -period_value)
     when period_unit = 'Years'  then add_months(sysdate, -period_value * 12)
end

select distinct retention_unit from APPS_GLOBAL.control_table1
where group_id  = 1;

ORA-00903: invalid table name
ORA-06512: at line 21

DECLARE
   CURSOR C
   IS
        SELECT GROUP_ID,
               SOURCE_TABLE_OWNER_NAME,
               SOURCE_TABLE_NAME,
               ARCHIVE_TABLE_OWNER_NAME,
               ARCHIVE_TABLE_NAME,
               CONDITION_COLUMN_NAME,
               RETENTION_VALUE,
               RETENTION_UNIT,
               FLAG
          FROM APPS_GLOBAL.control_table1
         WHERE GROUP_ID = 1
      ORDER BY GROUP_ID, source_table_name;

   rec   c%ROWTYPE;
BEGIN
   FOR rec IN C
   LOOP
      EXECUTE IMMEDIATE
            'INSERT INTO '
         || rec.ARCHIVE_TABLE_OWNER_NAME
         || '.'
         || rec.ARCHIVE_TABLE_NAME
         || '(SELECT * FROM '
         || rec.SOURCE_TABLE_OWNER_NAME
         || '.'
         || rec.SOURCE_TABLE_NAME
         || ' WHERE '
         || rec.CONDITION_COLUMN_NAME
         || '<='
         || CASE WHEN LOWER (rec.RETENTION_UNIT) = 'd' THEN 'sysdate - ' || rec.RETENTION_VALUE
               WHEN LOWER (rec.RETENTION_UNIT) = 'y'  THEN 'add_months(sysdate,' || rec.RETENTION_VALUE || '*-12)'
            END
         ||')';
   END LOOP;

   COMMIT;
END;
/

CREATE TABLE APPS_GLOBAL.CONTROL_TABLE1
(
  GROUP_ID                  NUMBER,
  SOURCE_TABLE_OWNER_NAME   VARCHAR2(50 CHAR),
  SOURCE_TABLE_NAME         VARCHAR2(50 CHAR),
  ARCHIVE_TABLE_OWNER_NAME  VARCHAR2(50 CHAR),
  ARCHIVE_TABLE_NAME        VARCHAR2(50 CHAR),
  CONDITION_COLUMN_NAME     VARCHAR2(50 CHAR),
  RETENTION_VALUE           NUMBER,
  RETENTION_UNIT            CHAR(1 CHAR),
  FLAG                      CHAR(1 CHAR)
);

Insert into APPS_GLOBAL.CONTROL_TABLE1
   (GROUP_ID, SOURCE_TABLE_OWNER_NAME, SOURCE_TABLE_NAME, ARCHIVE_TABLE_OWNER_NAME, ARCHIVE_TABLE_NAME, 
    CONDITION_COLUMN_NAME, RETENTION_VALUE, RETENTION_UNIT, FLAG)
 Values
   (1, 'APPS_GLOBAL', 'EMP', 'APPS_GLOBAL', 'A_RAM', 
    'HIREDATE', 1, 'Y', 'A');

Insert into APPS_GLOBAL.CONTROL_TABLE1
   (GROUP_ID, SOURCE_TABLE_OWNER_NAME, SOURCE_TABLE_NAME, ARCHIVE_TABLE_OWNER_NAME, ARCHIVE_TABLE_NAME, 
    CONDITION_COLUMN_NAME, RETENTION_VALUE, RETENTION_UNIT, FLAG)
 Values
   (1, 'APPS_GLOBAL', 'DOJ_RAM', 'APPS_GLOBAL', 'B_RAM', 
    'DOJ', 180, 'D', 'A');

Insert into APPS_GLOBAL.CONTROL_TABLE1
   (GROUP_ID, SOURCE_TABLE_OWNER_NAME, SOURCE_TABLE_NAME, ARCHIVE_TABLE_OWNER_NAME, CONDITION_COLUMN_NAME, 
    RETENTION_VALUE, RETENTION_UNIT, FLAG)
 Values
   (1, 'APPS_GLOBAL', 'STUUDENT_RAM', 'APPS_GLOBAL', 'STUD_DOJ', 
    2, 'Y', 'P');

INSERT INTO APPS_GLOBAL.(SELECT * FROM APPS_GLOBAL.STUUDENT_RAM WHERE STUD_DOJ<=add_months(sysdate,2*-12))

INSERT INTO APPS_GLOBAL.B_RAM(SELECT * FROM APPS_GLOBAL.DOJ_RAM WHERE DOJ<=sysdate - 180)

INSERT INTO APPS_GLOBAL.A_RAM(SELECT * FROM APPS_GLOBAL.EMP WHERE HIREDATE<=add_months(sysdate,1*-12)) 

DECLARE
   stmt   LONG;

   CURSOR C
   IS
        SELECT GROUP_ID,
               SOURCE_TABLE_OWNER_NAME,
               SOURCE_TABLE_NAME,
               ARCHIVE_TABLE_OWNER_NAME,
               ARCHIVE_TABLE_NAME,
               CONDITION_COLUMN_NAME,
               RETENTION_VALUE,
               RETENTION_UNIT,
               FLAG
          FROM APPS_GLOBAL.control_table1
         WHERE GROUP_ID = 1
      ORDER BY GROUP_ID, source_table_name;

   rec    c%ROWTYPE;
BEGIN
   FOR rec IN C
   LOOP
stmt := 'INSERT INTO '
         || rec.ARCHIVE_TABLE_OWNER_NAME
         || '.'
         || rec.ARCHIVE_TABLE_NAME
         || '(SELECT * FROM '
         || rec.SOURCE_TABLE_OWNER_NAME
         || '.'
         || rec.SOURCE_TABLE_NAME
         || ' WHERE '
         || rec.CONDITION_COLUMN_NAME
         || '<=';
 
CASE LOWER (rec.RETENTION_UNIT)
    WHEN 'd'
    THEN
     stmt:= stmt||'sysdate -'||rec.RETENTION_VALUE||')';
     ELSE
     stmt := stmt||'add_months(sysdate,'|| rec.RETENTION_VALUE||'*-12)'||')';
END CASE;
execute immediate stmt;
   END LOOP;

   COMMIT;
END;
/

EXECUTE IMMEDIATE
               'INSERT INTO '
            || I.ARCHIVE_TABLE_OWNER_NAME
            || '.'
            || I.ARCHIVE_TABLE_NAME
            || '(SELECT * FROM '
            || I.SOURCE_TABLE_OWNER_NAME
            || '.'
            || I.SOURCE_TABLE_NAME
            || ' WHERE '
            || I.CONDITION_COLUMN_NAME
            || '<=ADD_MONTHS(SYSDATE,-24)'
            || ')';

INSERT INTO APPS_GLOBAL.(SELECT * FROM APPS_GLOBAL.STUUDENT_RAM WHERE STUD_DOJ<=add_months(sysdate,2*-12))