ORACLE APP : IMP of Operation Unit System profile

On typical Friday I got a Multiple request that one of our critical environment is down. During investigation I found someone remove Site level profile for operating unit. No user was able to login to application. Anyone who has access to the System Profile Values form will be able to remove the value and thus break the environment for everyone else.

The statement mentioned above applies to any 11.5.10 or higher environment. Previous releases of Oracle Applications did allow the System Administrator to login to the application using the Forms Login (known as dev60cgi) and correct the problem.

1. Login into Oracle Applications
2. Select the System Administrator Responsibility
3. Navigate to Profile --> System
4. Query up "MO: Operating Unit" profile
5. In the site level column there should be a default organization.
6. If this field is blank, select a default organization from the LOV

As of Oracle Applications 11.5.10 and due to some code changes it is no longer possible for the System Administrator to correct the problem via the application itself.

• Identify if a site level row exists for the profile option:
  

SQL> select application_id, profile_option_id,
site_enabled_flag, site_update_allowed_flag
from fnd_profile_options where PROFILE_OPTION_NAME = 'ORG_ID'

• Identify if a site level row exists for the profile option:

SQL> select * from fnd_profile_option_values
where profile_option_id = &option_id
and level_id = 10001;

• If site_enabled_flag is not equal 'Y'

SQL> update fnd_profile_options set site_enabled_flag = 'Y' 
where profile_option_name = 'ORG_ID';
commit;

• Find the valid organization id

  SQL> SELECT ORGANIZATION_ID, NAME FROM HR_OPERATING_UNITS;

Pick one of the organization_id values and use it with this insert statement:

• If a row does exist for the site level in table fnd_profile_option_values;

        SQL> Update FND_PROFILE_OPTION_VALUES
      set PROFILE_OPTION_VALUES = ''
      where PROFILE_OPTION_ID = 1991
      and APPLICATION_ID =0
      and level_id = 10001;

• If a row does not exist for the site level in table fnd_profile_option_values;

     SQL> insert into fnd_profile_option_values
  (values 0, profile_option_id_from_query1, 10001, 0, sysdate, 0,
   sysdate, -1, 0, "valid_organization_id", null, null);
   commit;

 ** Where "valid_organization_id" is replaced by an actual organization_id value in quotes. 

  OR you can use API 

 SQL> DECLARE 
stat boolean; 
BEGIN 
dbms_output.disable; 
dbms_output.enable(100000); 
stat := FND_PROFILE.SAVE('ORG_ID', valid_organization_id, '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; 

• validate if a site level row exists for the profile option:

        SQL> select * from fnd_profile_option_values
          where profile_option_id = &option_id and level_id = 10001;

• Compile pkg FND_GLOBA (optinal):

   SQL> alter package FND_GLOBAL compile;

  alter package FND_GLOBAL compile body;

• Restart all nodes.