Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
EBS FND_PROFILE snippets, referenced in blog post http://only4left.jpiwowar.com/2011/08/fun-with-ebs-api-i/
/*
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
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
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
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;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment