1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Set Profile Option Value from Back End

Discussion in 'Oracle Apps Technical' started by Kirti, Apr 27, 2009.

  1. Kirti

    Kirti Forum Advisor

    Messages:
    46
    Likes Received:
    12
    Trophy Points:
    130
    We had a new joinee at ou office recently. He was experimenting with Oracle Apps Vision Instance on our test server. Now in his enthusiasm to learn things by "doing it", he did a very interesting thing. He was playing with different Profile Values and he "accidently" (that's what he says! :) ) 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.

    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

    Code (SQL):
    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

    Code (SQL):
    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.

    Code (SQL):
    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 :)

    Cheers!
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,890
    Likes Received:
    248
    Trophy Points:
    1,155
    hey thanks Kirti... that's a brilliant article!
     
  3. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    hey that's a nice way... thanks
     
  4. mahajan_ruchi

    mahajan_ruchi Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thanks Kirti,this is really useful.