[Oracle]Responsibility not display

 參考網址：

http://somebabytina.pixnet.net/blog/post/30684439
https://bbs.aliyun.com/simple/t499645.html

權限名稱：GL_會計負責人
狀況描述：User因為某些關係關閉 GL_會計負責人權限，但是今天因為測試需要重新開啟，於Define_User重新開啟權限後，卻發現沒有在清單中

執行Request：Workflow Directory Services User/Role Validation，參數：10000, Y, Y, Y,
沒有作用後選擇以下方式

SELECT USER_NAME,
       ROLE_NAME,
       USER_END_DATE,
       ROLE_END_DATE,
       ASSIGNING_ROLE_END_DATE,
       EFFECTIVE_END_DATE
  FROM WF_USER_ROLE_ASSIGNMENTS
 WHERE USER_NAME IN ('A0AA','A0BB')
   AND ROLE_NAME LIKE '%GL%CBM%';

SELECT USER_NAME,ROLE_NAME, USER_END_DATE, ROLE_END_DATE, EFFECTIVE_END_DATE,ROWID
  FROM WF_LOCAL_USER_ROLES
 WHERE USER_NAME IN ('A0AA','A0BB')
   AND ROLE_NAME LIKE '%GL%CBM%';

比對以上兩個資料表發現EFFECTIVE_END_DATE不一致，改為9999/1/1，User反應正常顯示

 

20170222補充，因為還是偶爾會遇到，進行程式改良

DECLARE
  P_USER_NAME          VARCHAR2(50) := 'R0028';
  P_USER_ID            NUMBER;
  P_RESPONSIBILITY_KEY VARCHAR2(50);
  P_EFFECTIVE_END_DATE DATE;

  CURSOR CUR_DATA IS
    SELECT FUR.RESPONSIBILITY_ID
      FROM FND_USER_RESP_GROUPS_DIRECT FUR
     WHERE FUR.USER_ID = (SELECT FU.USER_ID
                            FROM FND_USER FU
                           WHERE FU.USER_NAME = P_USER_NAME)
       AND TRUNC(NVL(FUR.END_DATE, SYSDATE + 1)) > TRUNC(SYSDATE);
BEGIN

  FOR C1 IN CUR_DATA LOOP
    SELECT FRV.RESPONSIBILITY_KEY
      INTO P_RESPONSIBILITY_KEY
      FROM FND_RESPONSIBILITY_VL FRV
     WHERE FRV.RESPONSIBILITY_ID = C1.RESPONSIBILITY_ID;
  
    DBMS_OUTPUT.PUT_LINE(P_RESPONSIBILITY_KEY);
  
    UPDATE WF_LOCAL_USER_ROLES WL
       SET WL.EFFECTIVE_END_DATE = TO_DATE('9999/1/1', 'YYYY/MM/DD')
     WHERE WL.USER_NAME = P_USER_NAME
       AND WL.ROLE_NAME LIKE '%' || P_RESPONSIBILITY_KEY || '%';
  END LOOP;
  COMMIT;
END;

20170424補充，程式應該是有作用，只是似乎有背後排程
20171109補充，程式有作用，再去Define_User，同仁帳號表身看不到的權限上，隨便更動一下這筆資料，就會馬上生效
20171201補充

• Sync responsibility role data into the WF table
• Synchronize Workflow Local Tables（可能是R12）
• Synchronize WF LOCAL tables（11i 版本有找到這個）
• Workflow Directory Services User/Role Validation   ( 在跳出的參數選取畫面的第二欄Fix dangling user/roles 選擇yes)