20090423 plsql for ebs user assignment reduced - plembo/onemoretech GitHub Wiki

title: pl/sql for ebs user assignment, reduced link: https://onemoretech.wordpress.com/2009/04/23/plsql-for-ebs-user-assignment-reduced/ author: lembobro description: post_id: 332 created: 2009/04/23 19:17:39 created_gmt: 2009/04/23 19:17:39 comment_status: open post_name: plsql-for-ebs-user-assignment-reduced status: publish post_type: post

pl/sql for ebs user assignment, reduced

As I’ve said before, projects like this are where the benefits of having a C.S. major in the house show through.

Rob (who I need to get onto cvs or at least blogging more) has reduced what’s needed to do the assignment of a responsibility to an EBS (Oracle E-Business Suite) user in a PL/SQL script, to the following:

begin
fnd_user_pkg.addressp('USER001',
                                'FND',
                                'APPLICATION_DEVELOPER,
                                'STANDARD',
                                null,
                                SYSDATE,
                                null
                            );
commit;
end;

In the actual script ‘AUSER’ would be a real user’s ID. The ‘FND’ value is the short name for the application (see below for how to get that short name). ‘APPLICATION_ENGINEER’ is the “responsibility key”, not the friendly responsibility label that gets displayed in the define user form. ‘STANDARD’ is the security group name. The first “null” is in the description or comment field. “SYSDATE -1” sets the Start Date to yesterday (to make it effective immediately). The last “null is for the End Date.

Finding all Application Short Names

These are found in the fnd_application table on EBS.

SQL> describe 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)

Here’s the SQL to list the short names, in alpha order, along with their app ids:

SQL> select APPLICATION_SHORT_NAME,APPLICATION_ID SQL> from fnd_application SQL> order by APPLICATION_SHORT_NAME;