So poor guy he was unable to login and so was anybody else to the application tier. Now 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.
Ok now in 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;
Hope this helps someone