Fun with EBS APIs, part II: Users and responsbilities (FND_USER_PKG)

In my previous post, I demo'ed some simple scripts to make changes to E-Business Suite profile options, using the API provided by the FND_PROFILE package. This post will look at the FND_USER_PKG package (no relation to the "ABS brake system"). While my examples only reference user creation and responsibility assignment, it's really worth reviewing the contents of the rest of the package; there's lots of useful stuff in there.

First things first

Disclaimer time! Are you surprised? I hope you're not surprised. Am I really copying this section from the previous post? Yup, I am!

  1. The example code presented in this series of blog posts uses APIs written by Oracle to manipulate data in the E-Business Suite database. This does NOT imply that the code in these posts is endorsed or supported by Oracle.
  2. The examples in this series of blog posts are presented as demonstration code only, and are neither guaranteed to work in your environment, nor in all cases. Since my environments are not the same as yours, I cannot offer support in the event that this code does not work for you. You are encouraged to make whatever modifications are necessary to make these examples work for you.
  3. The scripts used in these examples were developed and implemented in test and development environments, where change management rules are often more lax. Be sure to test thoroughly, and to consult your organization's change management procedures, before attempting to use this code in production.

Short form: There's data-changing code in these posts. Oracle won't support you if you use the code; I can't support you either. Before running this code in production without testing and approval, you should probably prepare a resumé and, optionally, schedule an appointment with a psychiatrist.

Example 1: Duplicating a user's responsibilities

This is probably most relevant in test/dev/CRP environments, where it's not uncommon to be asked, "Can you please set up Fred to have the same responsibilities as Jane in the UAT environment?" or "Can you please set up 12 users in the Training environment, all with the same responsibilities?" I've written a script called ebs_dupresp that will duplicate all of a user's directly-assigned responsibilities to a target user, optionally creating the user record:

Here's a two-part demo. For the first part, I've assumed that I just got the following email from my boss:

John, Please create an EBS account in the test environment for the new DBA, Devnul Larson. Just give him the same responsibilities that you have, for now.

APPS@R12VIS(11.1.0.7)>@ebs_dupresp johnp devnull
Source user: johnp
Target user: devnull
CTRL-C might be a good idea if these are incorrect/reversed.
Create devnull if non-existent? (y/N) y
User DEVNULL created with id 1013423
Adding responsibility System Administrator
Adding responsibility Oracle Diagnostics Tool
APPS@R12VIS(11.1.0.7)>@lresp johnp
Currently active responsibilities for user johnp

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Diagnostics		      NONE
FND		     Application Diagnostics		      NONE
JTF		     Oracle Diagnostics Tool		      NONE
SYSADMIN	     System Administrator		      NONE
APPS@R12VIS(11.1.0.7)>@lresp devnull
Currently active responsibilities for user devnull

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Diagnostics		      NONE
FND		     Application Diagnostics		      NONE
JTF		     Oracle Diagnostics Tool		      NONE
SYSADMIN	     System Administrator		      NONE

Later that day, another email arrives:

On second thought, just assign all of SYSADMIN's responsibilities to the new guy. BTW, why are you assigning all the new tickets to DEVNULL?

APPS@R12VIS(11.1.0.7)>@ebs_dupresp sysadmin devnull
Source user: sysadmin
Target user: devnull
CTRL-C might be a good idea if these are incorrect/reversed.
Create devnull if non-existent? (y/N) y
User DEVNULL already exists.
Adding responsibility Application Developer
Adding responsibility System Administrator
Adding responsibility Application Developer Common Modules
Adding responsibility System Administration
Adding responsibility Preferences SSWA
Adding responsibility Workflow
(I'll spare you the full list)

It's worth noting that this is a fairly primitive approach, particularly when it comes to the user creation piece (no LDAP integration, no association of user record with an HR person/party record, etc). The ebs_dupresp script also doesn't touch things like profile options, so it shouldn't be considered a "user clone" script. You could probably accomplish this work with FNDLOAD or a third-party tool like DataLoader, but I'd rather write SQL. ;-)

Example 2: Adding a list of responsibilities to a user

Another common need is adding responsibilities to multiple users. Maybe you want to add the Application Developer responsibility to a handful of new consultants, or grant the Oracle Diagnostics responsibility to some freshly trained business analysts and power users. With a little bit of setup, it's pretty easy to do bulk responsibility assignments in a way that's easily repeated across multiple instances. Here's an example script, ebs_bulkresp, that does the job:

As a demo, here's another theoretical work request:

Please assign Application Developer and Oracle Diagnostics responsibilities to users SBERG, YWATSON, and WPARKER. SBERG will also need Functional Administrator. WPARKER will be rolling off the project at the end of the year, so please end-date his responsibilities accordingly. These changes are approved for both development environments and all three CRP instances.

After loading the necessary information into a "staging table" (actually a simply-formatted external table), away we go:

APPS@R12VIS(11.1.0.7)>@ebs_bulkresp validate
Working with following records in table xxrespload:

USER_NAME	     APP	RESP_NAME			    END_DATE
-------------------- ---------- ----------------------------------- ---------------
SBERG		     FND	Application Developer		    NONE
SBERG		     FND	Functional Administrator	    NONE
SBERG		     JTF	Oracle Diagnostics Tool 	    NONE
WPARKER 	     FND	Application Developer		    31-DEC-2011
WPARKER 	     JTF	Oracle Diagnostics Tool 	    31-DEC-2011
YWATSON 	     FND	Application Developer		    NONE
YWATSON 	     JTF	Oracle Diagnostics Tool 	    NONE
Starting validation...
All users valid.
All app-responsibility pairs valid
Invoke script with APPLY to add responsibilities
APPS@R12VIS(11.1.0.7)>@ebs_bulkresp apply
Working with following records in table xxrespload:

USER_NAME	     APP	RESP_NAME			    END_DATE
-------------------- ---------- ----------------------------------- ---------------
SBERG		     FND	Application Developer		    NONE
SBERG		     FND	Functional Administrator	    NONE
SBERG		     JTF	Oracle Diagnostics Tool 	    NONE
WPARKER 	     FND	Application Developer		    31-DEC-2011
WPARKER 	     JTF	Oracle Diagnostics Tool 	    31-DEC-2011
YWATSON 	     FND	Application Developer		    NONE
YWATSON 	     JTF	Oracle Diagnostics Tool 	    NONE
Starting validation...
All users valid.
All app-responsibility pairs valid
Adding responsibilities...
User: YWATSON App: FND Resp: Application Developer
User: WPARKER App: FND Resp: Application Developer
User: SBERG App: FND Resp: Application Developer
User: SBERG App: FND Resp: Functional Administrator
User: YWATSON App: JTF Resp: Oracle Diagnostics Tool
User: WPARKER App: JTF Resp: Oracle Diagnostics Tool
User: SBERG App: JTF Resp: Oracle Diagnostics Tool
APPS@R12VIS(11.1.0.7)>@lresp ywatson
Currently active responsibilities for user ywatson

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Developer		      NONE
FND		     Application Diagnostics		      NONE
PSB		     Budget User - Accounting Dept, Progress  NONE
SQLGL		     General Ledger Super User, Progress S&L  NONE
ICX		     Internet Procurement, Progress S&L       NONE
JTF		     Oracle Diagnostics Tool		      NONE
SQLAP		     Payables Manager, Progress S&L	      NONE
ICX		     Preferences SSWA			      NONE
FND		     Workflow User, Progress S&L	      NONE
APPS@R12VIS(11.1.0.7)>@lresp sberg
Currently active responsibilities for user sberg

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Developer		      NONE
FND		     Application Diagnostics		      NONE
FND		     Functional Administrator		      NONE
IBE		     IBE_CUSTOMER			      NONE
JTF		     Oracle Diagnostics Tool		      NONE
ICX		     Preferences SSWA			      NONE
APPS@R12VIS(11.1.0.7)>@lresp wparker
Currently active responsibilities for user wparker

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Developer		      31-DEC-2011
FND		     Application Diagnostics		      31-DEC-2011
JTF		     Oracle Diagnostics Tool		      31-DEC-2011
ICX		     Preferences SSWA			      NONE
WMS		     Whse Mgmt Mobile User, Vision Operations NONE
		      (USA)

It's rare that you'll get a request that also includes the name of the application to which a responsibility belongs. If you don't keep a mental inventory of valid pairs of application and responsibility names (or maybe that's just me?), here's a quick way to look up that sort of info:

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*