EBS FND_PROFILE snippets, referenced in blog post http://only4left.jpiwowar.com/2011/08/fun-with-ebs-api-i/

ebs_def_setprof.sql

/* 
   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;

ebs_postclone_prof.sql

/* 
   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;
/

iprof.sql

/*
  iprof.sql
  Contact: John Piwowar
  Purpose: List useful identifiers for EBS profile options
  Notes:  Prompts for fragment of responsibility name
*/

col prof_name for a40
col prof_code for a25
col user_enabled for a7
col resp_id for 999999

set pagesize 9999 verify off

select profile_option_name prof_code
     , t.user_profile_option_name prof_name
     , o.user_enabled_flag user_enabled
  from fnd_profile_options o
  join fnd_profile_options_tl t using (profile_option_name)
 where upper(t.user_profile_option_name) like upper('%&1%')
 order by user_profile_option_name
/

om_debug.sql

/*
   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;
/