declare

v_proj_name project.proj_name%TYPE;

type mfr_mod_CurTyp IS REF CURSOR;
type type_rec is record
  ( global_id NUMBER(22),
    cmpnt_mfr_name NVARCHAR2(20),
    cmpnt_mod_name NVARCHAR2(100),
    proj_name NVARCHAR2(50),
    proj_id NUMBER(22)
  );
type JNL_mfr_mod_overview is table of type_rec;
mfr_mod mfr_mod_CurTyp;
mfr_mod_ids JNL_mfr_mod_overview;

cursor domains is
    select
      proj_name
    from
      project
    where
      proj_del = 'N'
      --and proj_name <> 'SPARE_PARTS'
       and proj_name = 'asset_gr_spare'     
      and proj_id > 0;

begin
open domains;

execute immediate 'truncate table  amr.JNL_mfr_mod_overview';

loop
  fetch domains into v_proj_name;
  exit when domains%NOTFOUND;
  
OPEN mfr_mod FOR 'select cm.cmpnt_mfr_name, cmd.cmpnt_mod_name from ' || to_char(v_proj_name)  || '.component_mfr cm join component_mod cmd on cm.cmpnt_mfr_id = cmd.cmpnt_mfr_id where cmpnt_mfr_id <> 0';
FETCH mfr_mod BULK COLLECT INTO mfr_mod_ids;
CLOSE mfr_mod;
  execute immediate 
  'select cm.cmpnt_mfr_name, cmd.cmpnt_mod_name from ' || to_char(v_proj_name)  || '.component_mfr cm join component_mod cmd on cm.cmpnt_mfr_id = cmd.cmpnt_mfr_id
  where cmpnt_mfr_id <> 0'
BULK COLLECT INTO JNL_mfr_mod_overview;
end loop;
close domains;
end;