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!
- 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.
- 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.
- 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:
