In EBS it uses responsibilities to control user
access to menus, forms, applications, data, functions and queries in Oracle
E-Business Suite. One EBS user is
assigned certain responsibilities so he can access the application or modules
to perform the works. Meanwhile the responsibility
also restricts him from other applications which are not open to the user.
Responsibilities are created and assigned through another responsibility called “System
Administrator”.
Responsibility is associated with
application. One application can have multiple
responsibilities. One responsibility defines
the access level for associated application.
Oracle comes with many pre-defined
responsibilities. Custom
responsibility can be created.
A responsibility can be assigned to many users and a user can be
assigned many responsibilities. A user
with multiple responsibilities can switch responsibilities after login into
EBS.
Behind the scene all these are stored in several
database tables:
FND_USER,
FND_APPLICATION,
FND_RESPONSIBILITY,
FND_RESPONSIBILITY_TL,
FND_SECURITY_GROUPS,
FND_USER_RESP_GROUPS
Here the prefix FND stands for foundation table,
suffix TL means that this table is basically the same as the table without TL
but with language support.
FND_USER table stores details about the application
user information such as name, email, encrypted password and etc.
FND_USER
Name Null Type
----------------------------- ------------------------------------------------
-------------
USER_ID NOT NULL
NUMBER(15)
USER_NAME NOT NULL
VARCHAR2(100)
EMAIL_ADDRESS VARCHAR2(240)
ENCRYPTED_FOUNDATION_PASSWORD NOT NULL
VARCHAR2(100)
ENCRYPTED_USER_PASSWORD NOT NULL VARCHAR2(100)
………………..
FND_APPLICATION table defines the information
about all applications in EBS.
FND_APPLICATION
Name Null Type
---------------------- -------- ------------
APPLICATION_ID NOT NULL NUMBER
APPLICATION_SHORT_NAME NOT NULL
VARCHAR2(50)
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER(15)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER(15)
LAST_UPDATE_LOGIN NUMBER(15)
BASEPATH VARCHAR2(20)
PRODUCT_CODE VARCHAR2(50)
Responsibility is stored in the following tables:
FND_RESPONSIBILITY
FND_RESPONSIBILITY_TL
FND_RESPONSIBILTY table defines the information
about all responsibilities in EBS. It
has responsibility Key which is the short name for responsibility. It also has its
associated application and data group which is related to table FND_SECURITY_GROUPS.
FND_RESPONSIBILITY
Name Null Type
---------------------- -------- ------------
APPLICATION_ID NOT NULL NUMBER(15)
RESPONSIBILITY_ID NOT
NULL NUMBER(15)
RESPONSIBILITY_KEY NOT NULL VARCHAR2(30)
DATA_GROUP_ID NOT NULL NUMBER(15)
………………
FND_RESPONSIBILITY_TL is almost same as FND_RESPONSIBILITY. But it has the name of responsibility, which
can is any user understandable name in specified language.
FND_RESPONSIBILITY_TL
Name Null Type
------------------- -------- -----------------------------------------------
APPLICATION_ID NOT NULL NUMBER
RESPONSIBILITY_ID NOT NULL NUMBER
LANGUAGE NOT NULL VARCHAR2(4)
RESPONSIBILITY_NAME NOT NULL VARCHAR2(100)
DESCRIPTION VARCHAR2(240)
…………
FND_SECURITY_GROUP Stores information about security groups used
to partition data.
FND_SECURITY_GROUPS
Name Null Type
------------------ -------- ------------
SECURITY_GROUP_ID NOT
NULL NUMBER(15)
SECURITY_GROUP_KEY NOT NULL VARCHAR2(30)
…………….
SECURITY_GROUP_ID SECURITY_GROUP_KEY CREATED_BY CREATION_DATE LAST_UPDATED_BY
LAST_UPDATE_DATE LAST_UPDATE_LOGIN
-----------------
------------------------------ ---------- ------------- ---------------
---------------- -----------------
0 STANDARD 1 09/OCT/98 2 22/OCT/01 0
FND_USER_RESP_GROUPS will define the assigned responsibilities for
the users. It is here that user –
responsibility association is defined.
FND_USER_RESP_GROUPS
Name Null Type
----------------------------- --------
----------
USER_ID NOT NULL NUMBER(15)
RESPONSIBILITY_ID NUMBER
RESPONSIBILITY_APPLICATION_ID NOT NULL NUMBER
SECURITY_GROUP_ID NOT NULL NUMBER(15)
START_DATE DATE
END_DATE DATE
DESCRIPTION VARCHAR2()
CREATED_BY NUMBER
CREATION_DATE DATE
LAST_UPDATED_BY NUMBER
LAST_UPDATE_DATE DATE
LAST_UPDATE_LOGIN NUMBER
After understanding the schema of the above tables we can have
some PL/SQL codes which can be used to query and manipulate the responsibility in
the background.
Find the application short name by Responsibility name
SELECT FA.APPLICATION_SHORT_NAME,
FR.RESPONSIBILITY_KEY,
FRG.SECURITY_GROUP_KEY,
FRT.DESCRIPTION
FROM FND_RESPONSIBILITY FR,
FND_APPLICATION FA,
FND_SECURITY_GROUPS FRG,
FND_RESPONSIBILITY_TL FRT
WHERE FR.APPLICATION_ID = FA.APPLICATION_ID
AND FR.DATA_GROUP_ID = FRG.SECURITY_GROUP_ID
AND FR.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID
AND FRT.LANGUAGE = 'US'
AND FRT.RESPONSIBILITY_NAME = 'Payables Manager';
Check the user’s responsibilities
SELECT fu.USER_NAME,
frt.RESPONSIBILITY_NAME
FROM FND_USER_RESP_GROUPS furg,
FND_USER fu,
FND_RESPONSIBILITY_TL frt
WHERE furg.USER_ID = fu.USER_ID
AND furg.RESPONSIBILITY_ID = frt.RESPONSIBILITY_ID
AND fu.USER_NAME = 'CHEM307'
Check the user’s responsibilities
set serveroutput on format wrapped;
DECLARE
v_user_name VARCHAR2 (100) := 'CHEM307';
v_responsibility_name VARCHAR2 (100) := 'Payables Manager';
v_application_name VARCHAR2 (50);
v_responsibility_key VARCHAR2 (30);
v_security_group VARCHAR2 (30);
v_description VARCHAR2 (240);
BEGIN
---- Get the responsibility key, application name, security group and etc. for given responsibility name
SELECT fa.APPLICATION_SHORT_NAME,
fr.RESPONSIBILITY_KEY,
frg.SECURITY_GROUP_KEY,
frt.DESCRIPTION
INTO v_application_name,
v_responsibility_key,
v_security_group,
v_description
FROM FND_RESPONSIBILITY fr,
FND_APPLICATION fa,
FND_SCURITY_GROUPS frg,
FND_RESPONSIBILITY_TL frt
WHERE fr.APPLICATION_ID = fa.APPLICATION_ID
AND fr.DATA_GROUP_ID = frg.SECURITY_GROUP_ID
AND fr.RESPONSIBILITY_ID = frt.RESPONSIBILITY_ID
AND frt.LANGUAGE = 'US'
AND frt.RESPONSIBILITY_NAME = v_responsibility_name;
---- Invoke addresp in fnd_user_pkg to assign the responsibility to the user
fnd_user_pkg.addresp (username=> v_user_name,
resp_app=> v_application_name,
resp_key=> v_responsibility_key,
security_group => v_security_group,
description=> v_description,
start_date=> SYSDATE,
end_date=> NULL);
DBMS_OUTPUT.put_line ( 'Responsiblity ' || v_responsibility_name || ' is assigned to the user ' || v_user_name);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line( 'Error occurred while assigning responsibility to the user. The error is '|| SQLERRM );
END;
