Responsibility in EBS

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;