During business events such as month-end close, only a small group of users are allowed to access EBS website. This can not be accomplished by placing it in maintenance mode. But we can disable most responsibilities to restrict and manage users' access without changing users' accounts.
-- disable responsibilities. Assume GL_SuperUser, AR_SuperUser are two special responsibilities
SQL> update FND_RESPONSIBILITY
set end_date = sysdate
where application_id = 30003
and RESPONSIBILITY_ID in (select responsibility_id
from FND_RESPONSIBILITY_TL
where application_id = 30003
and Responsibility_name not in ('GL_SuperUser', 'AR_SuperUser'))
;
Commit;
-- After maintenance event, run sql to enable the responsibilities (without bouncing Apps services)
SQL> update FND_RESPONSIBILITY
set end_date = null
where application_id = 30003
and RESPONSIBILITY_ID in (select responsibility_id
from FND_RESPONSIBILITY_TL
where application_id = 30003
and Responsibility_name not in ('GL_SuperUser', 'AR_SuperUser'))
;
Commit;
When a responsibility was created, it would be associated with an Application. Below query will find how many responsibilities were created for each application.
SQ> select a.application_id, b.application_short_name, count(*)
from FND_RESPONSIBILITY a , fnd_application b
where a.application_id = b.application_id
group by a.application_id, application_short_name
order by a.application_id;
application_id application_short_name count(*)
--------------- ---------------------------- ---------
0 FND 24
1 SYSADMIN 3
99 OAM 1
101 SQLGL 27
140 OFA 7
... ... ...
9001 MTH 2
9004 INL 1
30003 PUGL 20 <== custom stuff
30004 PUAR 3
30005 PUFND 4
Additionally, below queries can answer questions:
1) the list of responsibilities for a user
2) the list of responsibilities for an application
SQL> SELECT user_name, frt.RESPONSIBILITY_NAME, furg.end_date, fr.application_id
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
AND fu.user_name = 'EBSuserName'
-- AND fr.application_id = 30003
ORDER BY 1,2;
How many users use an application with each responsibility?
SQL> SELECT fr.application_id, frt.RESPONSIBILITY_NAME, count(*)
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
group by fr.application_id, frt.RESPONSIBILITY_NAME
ORDER BY 1,2;
No comments:
Post a Comment