摘自:https://www.cnblogs.com/toowang/p/6019881.html
Oracle EBS FND User Info API (转) EBS用户账号密码职责相关
1. 与用户信息相关API PKG. --和用户处理有关的API FND_USER_PKG; --和用户密码处理有关的API FND_WEB_SEC; --和用户职责处理有关的API FND_USER_RESP_GROUPS_API; 2. 激活失效用户帐号. ----------------------------- ---处理实例 ----------------------------- SELECT * FROM FND_USER WHERE USER_NAME = 'QWR01'; ---更新用户,将用户失效: DECLARE BEGIN FND_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); begin L_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;