The E-Business Suite APIs are not just the realm of developers and data migration specialists. There are a number of APIs, particularly in the FND module, that can make life a lot easier for Apps DBAs. In my next two (maybe three, if I'm feeling ambitious) posts, I'll provide some examples of how to use PL/SQL to perform tasks that normally require slogging around in the EBS Forms interface.
First things first
Disclaimer time! Are you surprised? I hope you're not surprised.
- 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.
Okay, moving on…
Now that I have all that out of the way, here are two basic examples demonstrating the use of FND_PROFILE. As with most PL/SQL code in the E-Business Suite database, FND_PROFILE is owned by the APPS schema. Browse through it, there's interesting stuff in there. Please note that the
set_ebs_profile function referenced in these examples is my hackwork (defined in ebs_def_setprof.sql), not part of the Oracle-supplied API.
Example 1: Setting profile options after cloning
Autoconfig takes care of setting a lot of site-level profile options after cloning, but there may be a few instances when you need to change settings manually before releasing the instance to users. The following script is a template that I use, adding calls to
set_ebs_profile as needed.
APPS@R12VIS(22.214.171.124)>@ebs_postclone_prof New SID (e.g. MYCLONE,NEWVIS, etc): JPR12VIS New Color Scheme (TEAL, RED, KHAKI, OLIVE, PURPLE, TITANIUM): PURPLE Instance type (e.g. Test, Dev, Sandbox): Visionary Data source note (e.g. 'Prod clone, data as of DD-MON-YYYY'): Timeless data Previous value of FND_COLOR_SCHEME: KHAKI SUCCESS: Set FND_COLOR_SCHEME to PURPLE Previous value of SITENAME: Vision Applications SUCCESS: Set SITENAME to Visionary (JPR12VIS) - Timeless data
Example 2: Setting multiple profile options for a user
In a development environment, you may be asked to set debug profile options for particular modules. It can be handy to have a script that does this for a particular user, ensure that no one "accidentally" sets debug options at the site level (oh, I know, it's rare, but still…). It's ridiculously easy to put together a quick script to take care of this:
APPS@R12VIS(126.96.36.199)>@om_debug jpiwowar 5 Setting OM debug for jpiwowar Previous value of OE_DEBUG_LOG_DIRECTORY: /usr/tmp Skipping OE_DEBUG_LOG_DIRECTORY, no change Target user: jpiwowar Previous value of ONT_DEBUG_LEVEL: 0 SUCCESS: Set ONT_DEBUG_LEVEL to 5
The interesting bits
The code that defines the procedure
set_ebs_profile can be found below. I've purposely written the script to be plugged into anonymous PL/SQL blocks instead of creating a procedure, because deployment of custom code is not always permitted.
set_ebs_profile expects the internal name for a profile option, instead of the "user-readable" name you'd normally see in the Forms interface. It's useful to become familiar with a query similar to the following. Pay attention to the user_enabled_flag, or you might spend time wondering why your changes aren't sticking.
APPS@R12VIS(188.8.131.52)>@iprof "OM: Debug" PROF_CODE PROF_NAME USER_EN ------------------------- ---------------------------------------- ------- ONT_DEBUG_LEVEL OM: Debug Level Y OE_DEBUG_LOG_DIRECTORY OM: Debug Log Directory N