Script to Copy Oracle EBS Responsibilities from One User to Another

Use the Script below to copy an application user's responsibilities to another 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_DIRECT RESPG
    , fnd_user fu
 WHERE   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
 and  FU.USER_ID = RESPG.USER_ID 
 AND  fu.user_name = 'SYSADMIN'; -- Change the username with the one you want to copy from

 lc_user_name varchar2(100) := 'JISIP';
 -- Change the username with the one you want to assign the responsibilies to
 
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.

Update! May 23, 2019:

I tried the same script for E-Business Suite 12.2.5 but it didn't work. I made a few changes and this did the trick:


set SERVEROUTPUT ON;
DECLARE 

    cursor add_resp is
 SELECT  DISTINCT 
   APPL.APPLICATION_SHORT_NAME
    , RESP2.RESPONSIBILITY_KEY       
    , SEC.SECURITY_GROUP_KEY
 FROM    FND_RESPONSIBILITY_tl RESP
    , FND_APPLICATION APPL
    , FND_SECURITY_GROUPS SEC
    , FND_USER_RESP_GROUPS RESPG
    , FND_RESPONSIBILITY RESP2
 WHERE   RESP.RESPONSIBILITY_NAME = 'System Administrator'
 and  RESP2.RESPONSIBILITY_id = RESP.RESPONSIBILITY_id
 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) := 'XXSAMPLE';
 
 v_count number := 0;
 
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 
     );
  
  v_count := v_count + 1;
  
    end loop;
 
 DBMS_OUTPUT.PUT_LINE('Responsibilities Added to '|| lc_user_name ||':'|| v_count ); 

 COMMIT; 

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

SHOW ERR;


For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/
Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

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