Script to Delete Concurrent Programs from Oracle E-Business Suite 11i and R12

There might be instances wherein one needs to remove an invalid Concurrent Program setup in Oracle E-Business Suite, including its Concurrent Executable setup. This cannot be done from the front-end application, as it only allows you to disable the Concurrent Program.

Fortunately, we have the option to do this from the back-end:

declare

    cursor conc_cur
    is
    SELECT  appl.application_short_name application
          , CONC.USER_CONCURRENT_PROGRAM_NAME
          , EXEC.EXECUTABLE_NAME
          , CONC.CONCURRENT_PROGRAM_NAME
          , lookup.meaning execution_method_code
          , EXEC.EXECUTION_FILE_NAME      
    FROM    FND_CONCURRENT_PROGRAMS_VL CONC
          , FND_EXECUTABLES EXEC
          , FND_APPLICATION_VL APPL
          , fnd_lookup_values lookup
    WHERE   EXEC.EXECUTABLE_ID = CONC.EXECUTABLE_ID
    AND     EXEC.EXECUTION_FILE_NAME = :pkg_name.procedure_name -- change this to the Concurrent Program Executable
    AND     APPL.APPLICATION_ID = EXEC.APPLICATION_ID
    AND     LOOKUP.LOOKUP_CODE = EXEC.EXECUTION_METHOD_CODE
    and     LOOKUP.lookup_type='CP_EXECUTION_METHOD_CODE';

Begin

    for c1 in conc_cur loop

        fnd_program.delete_program(c1.CONCURRENT_PROGRAM_NAME, c1.application);
        fnd_program.delete_executable(c1.EXECUTABLE_NAME, c1.application);
    
    end loop;
    
    commit;
    
End;

Please note that you would need to have access to the APPS schema or a user with similar or higher privileges.

Update: June 28, 2019: For EBS 12.2.5, the above query might not return any values because of the Views. Use the following code instead:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

declare cursor conc_cur is SELECT appl.application_short_name application --, CONC.USER_CONCURRENT_PROGRAM_NAME , EXEC.EXECUTABLE_NAME , CONC.CONCURRENT_PROGRAM_NAME , lookup.meaning execution_method_code , EXEC.EXECUTION_FILE_NAME FROM FND_CONCURRENT_PROGRAMS CONC , FND_EXECUTABLES EXEC , FND_APPLICATION APPL , fnd_lookup_values lookup WHERE EXEC.EXECUTABLE_ID = CONC.EXECUTABLE_ID AND EXEC.EXECUTION_FILE_NAME = 'GLMSUMMACCNEW' AND APPL.APPLICATION_ID = EXEC.APPLICATION_ID AND LOOKUP.LOOKUP_CODE = EXEC.EXECUTION_METHOD_CODE and LOOKUP.lookup_type='CP_EXECUTION_METHOD_CODE'; Begin for c1 in conc_cur loop fnd_program.delete_program(c1.CONCURRENT_PROGRAM_NAME, c1.application); fnd_program.delete_executable(c1.EXECUTABLE_NAME, c1.application); end loop; commit; End;

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)

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.

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)

Script to create an Application User in Oracle E-Business Suite

Use the Script below to create an Application User in Oracle E-Business Suite, applicable to both 11i and R12:

DECLARE 

   lc_user_name                          VARCHAR2(100)     := 'ISIPJ85'; 
   lc_user_password                    VARCHAR2(100)     := 'Oracle123'; 
   ld_user_start_date                    DATE                := TO_DATE(SYSDATE); 
   ld_user_end_date                     VARCHAR2(100)    := NULL; 
   ld_password_date                    VARCHAR2(100)    := TO_DATE(SYSDATE); 
   ld_password_lifespan_days     NUMBER              := 90; 
   ln_person_id                          NUMBER              := NULL; 
   lc_email_address                      VARCHAR2(100)    := 'migs.isip.23@gmail.com';

BEGIN

  fnd_user_pkg.createuser 
  (  x_user_name                             => lc_user_name, 
     x_owner                                    => NULL, 
     x_unencrypted_password         => lc_user_password, 
     x_start_date                               => ld_user_start_date, 
     x_end_date                                 => ld_user_end_date, 
     x_password_date                      => ld_password_date, 
     x_password_lifespan_days       => ld_password_lifespan_days, 
     x_employee_id                          => ln_person_id, 
     x_email_address                       => lc_email_address 
 ); 
  
 COMMIT;


EXCEPTION 
 WHEN OTHERS THEN 
    ROLLBACK; 
    DBMS_OUTPUT.PUT_LINE(SQLERRM); 
END; 
/

SHOW ERR;

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

Script to Reset Password in Oracle E-Business Suite 11i and R12

Use the Script below to reset a User's password in Oracle E-Business Suite, applicable to both 11i and R12:

declare

 v_user_name  varchar2(100) := 'SYSADMIN';
 v_unencrypted_password varchar2(100) := 'oracle123';

begin

 apps.nd_user_pkg.updateuser
 (
     x_user_name   => v_user_name
   , x_owner    => null
   , x_unencrypted_password  => v_unencrypted_password
   , x_password_date   => sysdate
 );
  
 commit;
 
end;
/

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