add

About Me

My photo
Oracle Apps - Techno Functional consultant

Monday, September 19

Set Profile Option Value from Back End

Set the profile option MO: Operating Unit null and saved it. Now you would know that if MO: Operating Unit is set to Null you would not be able to login to the applications.

Technical consultants would know that profile options cannot be just set by updating the id in a table. Actually profile options can be set from the back-end by the fnd_profile package. So anyways here is what I did to set the MO: Operating Unit. You can use the same process to set a value for any other profile option from SQL Plus or TOAD.

The table fnd_profile_options_tl, profile options names are kept. Now MO: Operating Unit or any other profile option name that you know is in the column USER_PROFILE_OPTION_NAME. But we are interested in the corresponding PROFILE_OPTION_NAME. So I found the PROFILE_OPTION_NAME by the simple select

SELECT
profile_option_name FROM fnd_profile_options_tl WHERE user_profile_option_name LIKE 'MO%'


It returns more than one row but i can make out that "ORG_ID" is the PROFILE_OPTION_NAME for MO: Operating Unit. Now I need to know the Org_ID of the Org whose value is to be set in MO: Operating Unit. SO I use the simple select as below

SELECT organization_id, NAME   FROM hr_all_organization_units;

From the organization name I find the one which will be the default Operating Unit, and I note the ID. In my case the ID for my default Operating Unit is 286. Now with the code below I set the profile option value using fnd_profile.save.

DECLARE
stat BOOLEAN;
BEGIN
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE (100000);
stat := fnd_profile.SAVE ('ORG_ID', 286, 'SITE');
IF stat THEN
DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');
ELSE
DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');
END IF;
COMMIT;
END;

No comments: