Script to assign a responsibility to an application user in Oracle E-Business Suite

Use the Script below to assign a certain responsibility to an application user in Oracle E-Business Suite, applicable to both 11i and R12:


DECLARE 

    cursor add_resp is
 SELECT  DISTINCT 
         APPL.APPLICATION_SHORT_NAME
       , RESP.RESPONSIBILITY_KEY       
       , SEC.SECURITY_GROUP_KEY
 FROM    FND_RESPONSIBILITY_VL RESP
       , FND_APPLICATION_VL APPL
       , FND_SECURITY_GROUPS_VL SEC
       , FND_USER_RESP_GROUPS RESPG
 WHERE   RESP.RESPONSIBILITY_NAME = 'System Administrator'
 AND     RESP.APPLICATION_ID = APPL.APPLICATION_ID
 AND     SEC.SECURITY_GROUP_ID = RESPG.SECURITY_GROUP_ID
 AND     RESPG.RESPONSIBILITY_APPLICATION_ID = RESP.APPLICATION_ID
 AND     RESP.RESPONSIBILITY_ID = RESPG.RESPONSIBILITY_ID;

 lc_user_name varchar2(100) := 'ISIPJ85';
 
BEGIN 

 FOR i in add_resp loop

      fnd_user_pkg.addresp 
      (  
       username             => lc_user_name, 
         resp_app             => i.APPLICATION_SHORT_NAME
     ,resp_key             => i.RESPONSIBILITY_KEY
     ,security_group    => i.SECURITY_GROUP_KEY
     ,description          => NULL
     ,start_date           => SYSDATE
     ,end_date             => NULL 
     );
    
    end loop;

 COMMIT; 

EXCEPTION 
 WHEN OTHERS THEN 
    ROLLBACK; 
    DBMS_OUTPUT.PUT_LINE('Error Encountered: '||SQLERRM); 
END; 
/

SHOW ERR;

Take note that you would need enough database privilege to the APPS schema to run this script.

No comments:

Post a Comment

Recent Posts

SQL Fundamentals

Introduction to SQL and Syntax What is SQL? SQL stands for Structured Query Language. is a standard programming language for accessing datab...

Top Posts