Nolem的部落格

declare
cursor cur is

select c.USER_ID,c.r11_username , b.RESPONSIBILITY_NAME,c.r12_username
  from  (select a.USER_ID,b.user_name r11_username,b.description r11_description ,a.user_name r12_username,a.description  r12_description
               from fnd_user a,  R11_ACT_FND_USE b
            where a.user_name=ltrim(b.CHK_USERNAME(+),'A'))  c ,MV_GB_RESPONSIBILITY@wsdb b
where c.r11_username =b.USER_NAME
   and b.USER_RESP_END is null and RESP_END is null;

cursor resp(pRESPONSIBILITY_NAME in varchar2 ) is

           select b.APPLICATION_SHORT_NAME,a.*

             from GB_RESPONSIBILITY_MAPPING a,FND_APPLICATION_VL b
          where pRESPONSIBILITY_NAME like '%' || R11_RESPONSIBILITY_NAME || '%'
              and R11_RESPONSIBILITY_NAME is not null
              and a.APPLICATION_ID=b.APPLICATION_ID ;
begin
  for rec in cur loop
     for rec_resp in resp(rec.RESPONSIBILITY_NAME) loop
       declare
         get varchar2(1);
       begin
         select 'Y' into get
           from FND_USER_RESP_GROUPS_ALL
          where USER_ID=rec.USER_ID
            and RESPONSIBILITY_ID=rec_resp.r12_RESPONSIBILITY_ID ;
            sys.dbms_output.put_line(to_char(rec.USER_ID) || ' get resp =' || to_char(rec_resp.r12_RESPONSIBILITY_ID));
       exception
         when no_data_found then
         fnd_user_pkg.addresp(username       => rec.r12_username,
                         resp_app       => rec_resp.application_short_name,
                         resp_key       => rec_resp.R12_RESPONSIBILITY_KEY,
                         security_group => 'STANDARD',
                         description    => null,
                         start_date     => sysdate,
                         end_date       => null);
       end;                  
     end loop;
     commit;
  end loop;
end;