后台刷新职责关键字

最新推荐文章于 2024-05-25 23:20:29 发布

原创最新推荐文章于 2024-05-25 23:20:29 发布 · 756 阅读

CC 4.0 BY-SA版权

这段Oracle PL/SQL代码用于后台刷新职责关键字。通过游标`cur_old_resp`查询`fnd_responsibility_tl`等表，找到指定语言的责任名称、ID和关键字。然后，更新`fnd_responsibility`、`wf_local_roles`、`wf_local_user_roles`和`wf_user_role_assignments`表，将责任关键字加 '_OLD' 后缀，实现职责关键字的更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

declare
  l_responsibility_name VARCHAR2(1000);
  l_responsibility_id   number;
  l_responsibility_key  VARCHAR2(30);
  l_role_name           VARCHAR2(320);
  CURSOR cur_old_resp IS
    SELECT tt.responsibility_name, t.responsibility_id, b.responsibility_key
      FROM fnd_responsibility_tl t, fnd_responsibility b,fnd_responsibility_tl tt
     WHERE b.responsibility_id = t.responsibility_id
       AND b.application_id = t.application_id
       AND t.language = 'ZHS'
       AND b.responsibility_id = tt.responsibility_id
       AND b.application_id = tt.application_id
       AND tt.language = 'US'
       AND exists
     (SELECT '1'
              FROM cux.cux_fnd_disable_resp cfd
             where cfd.responsibility_name = t.responsibility_name);         
begin
  --更新旧职责关键字
  FOR rec_old_resp IN cur_old_resp LOOP
  --
  BEGIN
    select wlr.name
      into l_role_name
      from wf_local_roles wlr
     where wlr.display_name = rec_old_resp.responsibility_name;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(rec_old_resp.responsibility_name || ' Can Not Find Responsibility Role Name!');
  END;
  --更新FND_RESPONSIBILITY
  update fnd_responsibility fr
     set fr.responsibility_key = SUBSTR(rec_old_resp.responsibility_key,1,26) || '_OLD'
   where fr.responsibility_id = rec_old_resp.responsibility_id;
  --更新WF_LOCAL_ROLES
  update wf_local_roles wlr
     set wlr.name = replace(wlr.name,
                            rec_old_resp.responsibility_key,
                            SUBSTR(rec_old_resp.responsibility_key,1,26) || '_OLD')
   where wlr.display_name = rec_old_resp.responsibility_name;
  --更新WF_LOCAL_USER_ROLES
  UPDATE wf_local_user_roles wlur
     SET wlur.role_name = replace(wlur.role_name,
                                  rec_old_resp.responsibility_key,
                                  SUBSTR(rec_old_resp.responsibility_key,1,26) || '_OLD')
   WHERE wlur.role_name = l_role_name;
  --更新WF_USER_ROLE_ASSIGNMENTS
  UPDATE wf_user_role_assignments wura
     SET wura.role_name = replace(wura.role_name,
                                  rec_old_resp.responsibility_key,
                                  SUBSTR(rec_old_resp.responsibility_key,1,26) || '_OLD')
   WHERE wura.role_name = l_role_name; 
  END LOOP;
end;