Script to compile all collection and classification packages

SET verify off
whenever sqlerror EXIT failure ROLLBACK;
REM Added for ARU db drv auto generation
REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
REM dbdrv: checkfile:~PROD:~PATH:~FILE
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
REM $Header: cncolgen.sql 115.1.1157.1 2002/03/06 22:54:22 pkm ship     $
declare
    l_errbuf      VARCHAR2(1800);
    l_retcode     NUMBER;
    l_debug_pipe  VARCHAR2(2000);
    l_debug_level NUMBER;
    l_orginal_org VARCHAR2(100);
    l_status      VARCHAR2(100);
    cursor maps is
    select tm.table_map_id,
           tm.org_id,
           tm.module_id,
           mo.module_status
    from cn_table_maps_all tm,
         cn_modules_all_b mo
    where tm.module_id = mo.module_id and
          tm.org_id = mo.org_id and
          mo.module_status = 'GENERATED'
    order by tm.table_map_id, tm.org_id, tm.module_id;

    cursor rules is
    select ruleset_id, org_id
    from cn_rulesets_all_b
        where org_id <> -3113 ;
begin
  dbms_application_info.read_client_info(l_orginal_org);
  -- ==============================
  -- Regenerate Collection packages
  -- ============================== 
  for map in maps loop
    -- dbms_output.put_line('map.table_map_id = ' || map.table_map_id || ' map.org_id = '|| map.org_id);

          dbms_application_info.set_client_info(map.org_id); 
    -- Generate Collection code. Code is stored in CN_SOURCE.
    cn_collection_gen.collection_pkg(l_debug_pipe,
                         l_debug_level,
                         map.table_map_id);
        -- Install the collection test package.
        cn_collection_gen.collection_install(l_errbuf,
                         l_retcode,
                          map.table_map_id,
                         'Y');
        -- Install the collection package.
        cn_collection_gen.collection_install(l_errbuf,
                         l_retcode,
                          map.table_map_id);
  end loop;
  -- ========================================
  -- Regenerate Classification Rules packages
  -- ========================================
  for rule in rules loop
    -- dbms_output.put_line('rule.ruleset_id = ' || rule.ruleset_id || ' rule.org_id = '|| rule.org_id);
           dbms_application_info.set_client_info(rule.org_id);
    cn_rulesets_pkg.sync_ruleset(rule.ruleset_id,
                             l_status);
      IF l_status <> 'UNSYNC' THEN
          cn_classification_gen.Classification_Install(
                 x_errbuf  => l_errbuf,
                 x_retcode => l_retcode,
                 x_ruleset_id => rule.ruleset_id);
      END  IF;
  end loop;
   
  dbms_application_info.set_client_info(l_orginal_org);
exception
  when others then
    dbms_application_info.set_client_info(l_orginal_org);
end;
/
commit;