CREATE OR REPLACE PROCEDURE APSP.JOB_TEST_RAM
AS
   V_SQL   VARCHAR2 (1000);
BEGIN
   FOR i
   IN (SELECT   gbm.OWNER_NAME owner_name,
                vdb.NAME || '_BU_' || gbm.BU_CODE,
                gbm.bu_id bu_id
         FROM   apps_global.GLOBAL_BU_MAPPING gbm, v$database vdb
        WHERE   OWNER_NAME NOT IN ('APPS_GLOBAL'))
   LOOP
      V_SQL :=
         'BEGIN 
              DBMS_SCHEDULER.CREATE_JOB (
                  job_name => '''
         || i.owner_name
         || '_archive_job'',
      job_class => '''
         || i.owner_name
         || '_job_class'',
                  job_type => ''PLSQL_BLOCK'',
                  job_action => ''begin APSP.POP_TARGET(''APPS_XX'',i.owner,i.buid); end;'',
                  auto_drop => TRUE,
                  comments => '''|| i.owner_name||''',
              enabled => TRUE);
            END;';

      EXECUTE IMMEDIATE V_SQL;
   END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE APSP.POP_TARGET (
 p_schema_name           VARCHAR2,
   p_actual_schema_name    VARCHAR2,
   p_buid                  NUMBER
)

''begin APPS_GLOBAL.ARCHIVE_TARGET_TABLES(''''APPS_XX'''','''''||i.owner_name||''''','''''||i.bu_id||'''''); end;''