Oracle EBS FND User Info API (轉) EBS使用者帳號密碼職責相關

標籤：ica   table   pps   arch   let   bms   har   修改密碼   ida   

1. 與使用者資訊相關API PKG.--和使用者處理有關的APIFND_USER_PKG;  --和使用者密碼處理有關的APIFND_WEB_SEC;  --和使用者職責處理有關的APIFND_USER_RESP_GROUPS_API;  2. 啟用失效使用者帳號.--------------------------------處理執行個體----------------------------- SELECT * FROM FND_USER WHERE USER_NAME = ‘QWR01‘; ---更新使用者，將使用者失效：DECLAREBEGINFND_USER_PKG.UpdateUser(x_user_name => ‘QWR01‘,x_owner => NULL,x_end_date => SYSDATE);--用下面的也可以，其實還是調用一樣的處理過程。--FND_USER_PKG.DisableUser(‘QWR01‘);END;  --批量失效使用者:DECLARE   CURSOR CUR_DISABLE_USER   IS      SELECT USER_NAME        FROM FND_USER       WHERE USER_NAME IN (‘‘);   L_DEAL_COUNT NUMBER;BEGIN   L_DEAL_COUNT := 0;   FOR REC_DISABLE_USER IN CUR_DISABLE_USER LOOP      FND_USER_PKG.DisableUser(REC_DISABLE_USER.USER_NAME);      L_DEAL_COUNT := L_DEAL_COUNT+L_DEAL_COUNT;   END LOOP;   DBMS_OUTPUT.PUT_LINE(‘成功失效使用者數:‘||L_DEAL_COUNT);END; ------------------批量失效使用者-------------------DECLARE   CURSOR CUR_DISABLE_USER   IS      SELECT USER_NAME        FROM FND_USER       WHERE SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE+1)         AND USER_NAME IN (‘QWR01‘);   L_DEAL_COUNT NUMBER;BEGIN   fnd_global.APPS_Initialize( user_id=>1183, resp_id=>20420, resp_appl_id =>1);   L_DEAL_COUNT := 0;   FOR REC_DISABLE_USER IN CUR_DISABLE_USER LOOP      FND_USER_PKG.DisableUser(REC_DISABLE_USER.USER_NAME);      L_DEAL_COUNT := L_DEAL_COUNT+1;   END LOOP;   COMMIT;   DBMS_OUTPUT.PUT_LINE(‘成功失效使用者數:‘||L_DEAL_COUNT);END; 3. 啟用失效使用者職責.--增加使用者的職責：FND_USER_PKG.AddResp;  --失效使用者的職責：FND_USER_PKG.DelResp;------------------------失效使用者的職責------------------------- ---失效超過一定日期沒使用過的職責。DECLARE   CURSOR CUR_DISABLE   IS      SELECT FU.USER_ID            ,FU.USER_NAME            ,FRESP.APPLICATION_SHORT_NAME            ,FRESP.RESPONSIBILITY_KEY            ,FRESP.SECURITY_GROUP_KEY            ,FRESP.RESPONSIBILITY_NAME            ,FRESP.START_DATE            ,FRESP.END_DATE            ,RESP_LOGIN.MAX_START_TIME        FROM FND_USER_RESP_GROUPS_DIRECT_V FRESP            ,FND_USER FU            ,(SELECT FL.USER_ID,FLR.RESPONSIBILITY_ID,MAX(FLR.START_TIME) MAX_START_TIME            FROM FND_LOGIN_RESPONSIBILITIES FLR,FND_LOGINS FL             WHERE FLR.LOGIN_ID = FL.LOGIN_ID             GROUP BY FL.USER_ID,FLR.RESPONSIBILITY_ID) RESP_LOGIN       WHERE 1=1         AND FU.USER_ID = FRESP.USER_ID         AND RESP_LOGIN.USER_ID(+) = FRESP.USER_ID         AND RESP_LOGIN.RESPONSIBILITY_ID(+) = FRESP.RESPONSIBILITY_ID         AND ((SYSDATE - RESP_LOGIN.MAX_START_TIME) >= 60 OR RESP_LOGIN.MAX_START_TIME IS NULL)         AND SYSDATE BETWEEN FU.START_DATE AND NVL(FU.END_DATE,SYSDATE+1)         AND SYSDATE BETWEEN FRESP.START_DATE AND NVL(FRESP.END_DATE,SYSDATE+1)         AND FU.USER_NAME = ‘QWR01‘         AND FRESP.RESPONSIBILITY_KEY = ‘XYG-WH-WCB-OM-QUERY‘       ORDER BY FU.USER_NAME,FRESP.RESPONSIBILITY_NAME;         L_DEAL_COUNT NUMBER;BEGIN   fnd_global.APPS_Initialize( user_id=>1183, resp_id=>20420, resp_appl_id =>1);   L_DEAL_COUNT := 0;   DELETE XYG_PUB_COMMON_TABLE_TEMP;   FOR REC_DISABLE IN CUR_DISABLE LOOP FND_USER_PKG.DelResp(REC_DISABLE.USER_NAME,REC_DISABLE.APPLICATION_SHORT_NAME,REC_DISABLE.RESPONSIBILITY_KEY,REC_DISABLE.SECURITY_GROUP_KEY);      L_DEAL_COUNT := L_DEAL_COUNT+1;   END LOOP;   COMMIT;   DBMS_OUTPUT.PUT_LINE(‘成功失效職責數:‘||L_DEAL_COUNT);END;  4. 使用者密碼相關.---直接修改密碼。這是直接修改的密碼，User重新登入之後，直接登入。下面有修改密碼之後，第一次使用者登入必須要更改口令的過程。SELECT fnd_web_sec.change_password(‘QWR01‘,‘PASSWORD‘) FROM DUAL;  ---驗證密碼的有效性SELECT fnd_web_sec.validate_login(‘QWR01‘,‘PASSWORD‘) FROM DUAL;  ---擷取錯誤的資訊。select fnd_message.get() from dual;  ---擷取加密的密碼declare  l_enc_fnd_pwd VARCHAR2(4000);  l_enc_user_pwd VARCHAR2(4000);  L_RETURN VARCHAR2(4000);beginL_RETURN :=  fnd_web_sec.get_encrypted_passwords(‘QWR01‘,5954,‘PASSWORD1‘,l_enc_fnd_pwd,l_enc_user_pwd);DBMS_OUTPUT.PUT_LINE(‘L_RETURN:‘||L_RETURN);DBMS_OUTPUT.PUT_LINE(‘p_enc_fnd_pwd:‘||l_enc_fnd_pwd||CHR(10)||‘p_enc_user_pwd:‘||l_enc_user_pwd);end;  ---修改：ENCRYPTED_USER_PASSWORD，暫時沒測試。declare   newpass varchar2(100);begin   newpass := fnd_user_pkg.getreencryptedpassword(‘QWR01‘, ‘NEWKEY‘);   DBMS_OUTPUT.PUT_LINE(‘newpass:‘||newpass);   --fnd_user_pkg.setreencryptedpassword(‘QWR01‘, newpass, ‘NEWKEY‘);end; 

 

Oracle EBS FND User Info API (轉) EBS使用者帳號密碼職責相關