Only Four Left…

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.

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.

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.

/* 
   ebs_postclone_prof.sql
   Contact/blame: John Piwowar
   Purpose: Set Site-level profile options after an EBS clone
   Requires: ebs_def_setprof.sql
   Notes: * Uses internal name for profile option, rather than user-readable 
            value (e.g. "SITENAME", not "Site Name"). Consult 
            fnd_profile_options_tl for appropriate values.
          * Site name and color scheme are the obvious ones; add others as 
            needed
*/

ACCEPT new_SID PROMPT "New SID (e.g. MYCLONE,NEWVIS, etc): "
ACCEPT new_color PROMPT "New Color Scheme (TEAL, RED, KHAKI, OLIVE, PURPLE, TITANIUM): "
ACCEPT env_type PROMPT "Instance type (e.g. Test, Dev, Sandbox): "
ACCEPT data_src PROMPT "Data source note (e.g. 'Prod clone, data as of DD-MON-YYYY'): "

set serveroutput on size 1000000 format word_wrapped;
set verify off feedback off 
 
spool ebs_postclone_prof_&&new_SID..log

DECLARE
@ebs_def_setprof
BEGIN
set_ebs_profile('FND_COLOR_SCHEME','&&new_color', TRUE);
set_ebs_profile('SITENAME','&&env_type (&&new_SID.) - &&data_src', TRUE);
END;
/

APPS@R12VIS(11.1.0.7)>@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:

/*
   om_debug.sql
   Contact/blame: John Piwowar
   Purpose: Set Order Management debug values for a given user
   Usage: @om_debug <username> <debug level>
   Requires: ebs_def_setprof.sql
   Notes: I'm sure I'll think of something...
*/

SET serveroutput on size 1000000 format word_wrapped
SET verify off feedback off 

DECLARE
   outdir APPLSYS.FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE;
   uname APPLSYS.FND_USER.USER_NAME%TYPE := '&1';
   dlvl APPLSYS.FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE := '&2';
   @ebs_def_setprof
BEGIN
   --find a writable directory the lazy way, same way FND_FILE does it. ;-)
   SELECT substr(value,1,instr(value,',')-1)
     INTO outdir
     FROM v$parameter
    WHERE name = 'utl_file_dir';

   dbms_output.put_line('Setting OM debug for ' || uname);
   set_ebs_profile('OE_DEBUG_LOG_DIRECTORY', outdir, TRUE);
   set_ebs_profile('ONT_DEBUG_LEVEL', dlvl, TRUE, uname);
END;
/

Example run:

APPS@R12VIS(11.1.0.7)>@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.

/* 
   ebs_def_setprof.sql
   Contact/blame: John Piwowar
   Purpose: Braindead wrapper for setting site and user-level profile options 
            with fnd_profile.save. 
   Notes: * Call as part of the declare section of an anon PL/SQL block.
            That's my sad attempt at reusability without creating stored procs
          * Assumes a great many things, including that you're running as 
            a user with exec privileges on FND_PROFILE, and that you don't 
            need fancy error reporting if things go wrong. ;-)
          * Only handles site/user-level settings, per protocol El-Ay-Zed-Why
          * Still somewhat primitive, could probably use more of API for 
            success/failure messages, etc.
*/

PROCEDURE set_ebs_profile (
          prof_option APPLSYS.FND_PROFILE_OPTIONS.PROFILE_OPTION_NAME%TYPE
        , prof_value  APPLSYS.FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE 
        , override BOOLEAN DEFAULT FALSE -- controls behavior when already set
        , uname FND_USER.USER_NAME%TYPE DEFAULT NULL -- if no username passed, set at site level
        )
IS
   uid FND_USER.USER_ID%TYPE := NULL;
   prof_level VARCHAR2(8) := 'SITE'; --Current longest val is 'SERVRESP'
   set_result BOOLEAN := FALSE;
   prev_val_defined BOOLEAN := TRUE; --assume yes until we learn otherwise
   prev_val FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE;
BEGIN
   IF uname IS NOT NULL THEN
      dbms_output.put_line('Target user: ' || uname);
      prof_level := 'USER';
      BEGIN --validate username
         SELECT user_id
           INTO uid
           FROM applsys.fnd_user
          WHERE user_name = upper(uname); --do as I think, not as I type
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
           dbms_output.put_line('ERROR: No userid found for ' || uname || '.');
           RETURN; --Whoops. Here's your bucket, bail away.
      END; --validate username
   END IF;

   --check for previously-set value
 
   fnd_profile.get_specific( name_z => prof_option
                           , user_id_z => uid
                           , val_z => prev_val
                           , defined_z => prev_val_defined
                           ); 
  IF NOT prev_val_defined  THEN 
      dbms_output.put_line(prof_option || ' not previously defined');
   END IF; --NOT prev_val

   IF prev_val_defined THEN
      dbms_output.put_line('Previous value of ' || prof_option || ': ' || 
                            prev_val
                          );
   END IF; --prev_val_defined

   IF (NOT override) AND prev_val_defined THEN --do nothing
      dbms_output.put_line('Skipping ' || prof_option || ', OVERRIDE=FALSE');
   ELSIF override AND (prev_val = prof_value) THEN --do nothing
      dbms_output.put_line ('Skipping ' || prof_option || ', no change');
   ELSE --try to set new profile
      set_result := fnd_profile.save( x_name => prof_option
                                    , x_value => prof_value
                                    , x_level_name => prof_level
                                    , x_level_value => uid
                                    );
      IF set_result THEN
         dbms_output.put_line( 'SUCCESS: Set ' || prof_option || ' to ' || 
                               prof_value 
                             );
         COMMIT; --may want to move outside function for large batch updates
      ELSE
         dbms_output.put_line( 'FAILED to set ' || prof_option || ' to ' || 
                               prof_value 
                             );
         dbms_output.put_line('Check option name and value and try again');
         ROLLBACK; --For safety only; current implementation of
                   --fnd_profile.save does not attempt a write before
                   --returning false
      END IF; --set_result
   END IF; --NOT override AND prev_val_defined 
END set_ebs_profile;

Note that 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(11.1.0.7)>@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