Oracle EBS: Comparing Profile Options In Two Systems (i.e. Test vs Live)

Probably one of the most useful code snippets. By creating a database link between test and live systems you can directly compare the contents of tables and see which items have changed. This script specifically details the profile options that have changed as well as any new profile options that have appeared.

I find this especially useful for writing documentation, or just for capturing changes.

NOTE: For the purposes of this script the link between live and test is called APPSRO, the live system is called APPLIVE (and runs on ENTERPRISE) and the test system is called APPDEV(and runs on DEFIANT).

declare
  -- These constants have the server names and SIDs, they're necessary so that "normal" changes during the
  -- cloning process aren't reported as profile option changes.
  c_TEST_SERVERNAME constant varchar2(255) := 'APPDEV';
  c_TEST_SERVER     constant varchar2(255) := 'DEFIANT';
  c_LIVE_SERVERNAME constant varchar2(255) := 'APPLIVE';
  c_LIVE_SERVER     constant varchar2(255) := 'ENTERPRISE';

  -- This will produce a MASSIVE report if set to YES
  v_ShowUnchanged varchar2(3) := 'NO';

  cursor c_AllProfileOptions is
    select fpo.profile_option_id, fpo.profile_option_name
      from applsys.fnd_profile_options fpo;

  cursor c_TestOptions(p_OptionId in number) is
    select Application_Id, Profile_Option_Id, Level_Id, Level_Value, Profile_Option_Value
      from applsys.fnd_profile_option_values fpov
     where fpov.profile_option_id = p_OptionId
       and fpov.level_id in (10001, 10002, 10003); -- site/app/resp level

  cursor c_LiveOption(p_Application in number, p_ProfileOption in number, p_Level in number, p_LevelValue in number) is
    select profile_option_value
      from applsys.fnd_profile_option_values@APPSRO fpov
     where fpov.application_id = p_Application
       and fpov.profile_option_id = p_ProfileOption
       and fpov.level_id = p_Level
       and fpov.level_value = p_LevelValue;

  cursor c_NewOptions is
    select fpov1.Profile_Option_Id,
           fpov1.Level_Id,
           fpov1.Profile_Option_Value,
           fpov1.Application_Id,
           fpo.profile_option_name
      from applsys.fnd_profile_option_values fpov1,
           applsys.fnd_profile_options       fpo
     where fpov1.level_id in (10001, 10002, 10003)
       and fpov1.profile_option_id = fpo.profile_option_id
       and not exists
     (select 'x'
              from applsys.fnd_profile_option_values@APPSRO fpov2
             where fpov2.application_id = fpov1.application_id
               and fpov2.profile_option_id = fpov1.profile_option_id
               and fpov2.level_id = fpov1.level_id
               and fpov2.level_value = fpov1.level_value);

  function isDifferent(p_Text1 in varchar2, p_Text2 in varchar2) return boolean is
    v_Text1 varchar2(1024);
    v_Text2 varchar2(1024);
  begin
    v_Text1 := upper(nvl(p_Text1, ''));
    v_Text2 := upper(nvl(p_Text2, ''));
    if instr(v_Text1, c_TEST_SERVERNAME) > 0 then
      v_Text1 := replace(v_Text1, c_TEST_SERVERNAME, c_LIVE_SERVERNAME);
    end if;
    if instr(v_Text2, c_TEST_SERVERNAME) > 0 then
      v_Text2 := replace(v_Text2, c_TEST_SERVERNAME, c_LIVE_SERVERNAME);
    end if;
  
    if instr(v_Text1, c_TEST_SERVER) > 0 then
      v_Text1 := replace(v_Text1, c_TEST_SERVER, c_LIVE_SERVER);
    end if;
    if instr(v_Text2, c_TEST_SERVER) > 0 then
      v_Text2 := replace(v_Text2, c_TEST_SERVER, c_LIVE_SERVER);
    end if;
  
    return not(v_Text1 = v_Text2);
  end isDifferent;
begin
  rollback; -- because of the DB link you can get transaction problems if you run the process twice
  for v_Option in c_AllProfileOptions loop
    for v_Test in c_TestOptions(v_Option.Profile_Option_Id) loop
      for v_LiveValue in c_LiveOption(v_Test.Application_Id,
                                      v_Test.Profile_Option_Id,
                                      v_Test.Level_Id,
                                      v_Test.Level_Value) loop
        if isDifferent(v_LiveValue.profile_option_value,
                   v_Test.Profile_Option_Value) then
          dbms_output.put_line('CHANGED TESTVALUE ' ||
                               v_Test.Application_Id || ',' ||
                               v_Test.Profile_Option_Id || ',' ||
                               v_Test.Level_Id || ' (' ||
                               v_Option.Profile_Option_Name || ')=' ||
                               v_Test.Profile_Option_Value);
          dbms_output.put_line('CHANGED LIVEVALUE ' ||
                               v_Test.Application_Id || ',' ||
                               v_Test.Profile_Option_Id || ',' ||
                               v_Test.Level_Id || ' (' ||
                               v_Option.Profile_Option_Name || ')=' ||
                               v_LiveValue.profile_option_value);
        else
          if v_ShowUnchanged = 'YES' then
            dbms_output.put_line('NO CHANGE ' || v_Test.Application_Id || ',' ||
                                 v_Test.Profile_Option_Id || ',' ||
                                 v_Test.Level_Id || ' (' ||
                                 v_Option.Profile_Option_Name || ')=' ||
                                 v_Test.Profile_Option_Value);
          end if;
        end if;
      end loop;
    end loop;
  end loop;

  for v_NewOption in c_NewOptions loop
    dbms_output.put_line('NEW OPTION ' || v_NewOption.Application_Id || ',' ||
                         v_NewOption.Profile_Option_Id || ',' ||
                         v_NewOption.Level_Id || ' (' ||
                         v_NewOption.Profile_Option_Name || ')=' ||
                         v_NewOption.Profile_Option_Value);
  end loop;
  rollback;
end;