当前位置：Gxlcms >
数据库问题 >
Oracle EBS FND User Info API (转) EBS用户账号密码职责相关
Oracle EBS FND User Info API (转) EBS用户账号密码职责相关
时间：2021-07-01 10:21:17
帮助过：21人阅读
. 与用户信息相关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;
Oracle EBS FND User Info API (转) EBS用户账号密码职责相关
标签：ica table pps arch let bms har 修改密码 ida
