EBS FND_PROFILE snippets, referenced in blog post http://only4left.jpiwowar.com/2011/08/fun-with-ebs-api-i/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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; | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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 | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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