APPS-ORACLE.RU

Скрипт обновления параметров профиля. В данном примере изменяется поле SQL_VALIDATION.

declare
  l_sql VARCHAR2(4000);
begin
           
  l_sql := 'SQL="SELECT LOOKUP_CODE, MEANING \"Включить проверку\"
            INTO :PROFILE_OPTION_VALUE, :VISIBLE_OPTION_VALUE
            FROM FND_LOOKUP_VALUES_VL
            WHERE LOOKUP_TYPE = ''!!!LOOKUP_NAME''
                  AND ENABLED_FLAG = ''Y''
                  AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE)
                      AND NVL(END_DATE_ACTIVE,SYSDATE)
            ORDER BY MEANING"
            COLUMN="\"Включить проверку\"(20)"';

  for i in (select profile_option_name
                 , user_profile_option_name
                 , start_date_active
                 , end_date_active
                 , user_changeable_flag
                 , user_visible_flag
                 , read_allowed_flag
                 , write_allowed_flag
                 , site_enabled_flag
                 , site_update_allowed_flag
                 , app_enabled_flag
                 , app_update_allowed_flag
                 , resp_enabled_flag
                 , resp_update_allowed_flag
                 , user_enabled_flag
                 , user_update_allowed_flag
                 , (select a.application_short_name 
                    from fnd_application a 
                    where a.application_id = t.application_id
                    ) as appl_short_name
            from fnd_profile_options_vl t
            where profile_option_name like '!!!%'
          )
  loop
   dbms_output.put_line('update '||i.profile_option_name);
   fnd_profile_options_pkg.load_row (
                   x_profile_name             => i.profile_option_name,
                   x_owner                    => '!!!XX',
                   x_application_short_name   => i.appl_short_name,
                   x_user_profile_option_name => i.user_profile_option_name,
                   x_description              => i.user_profile_option_name,
                   x_user_changeable_flag     => i.user_changeable_flag,
                   x_user_visible_flag        => i.user_visible_flag,
                   x_read_allowed_flag        => i.read_allowed_flag,
                   x_write_allowed_flag       => i.write_allowed_flag,
                   x_site_enabled_flag        => i.site_update_allowed_flag,
                   x_site_update_allowed_flag => i.site_update_allowed_flag,
                   x_app_enabled_flag         => i.app_enabled_flag,
                   x_app_update_allowed_flag  => i.app_update_allowed_flag,
                   x_resp_enabled_flag        => i.resp_enabled_flag,
                   x_resp_update_allowed_flag => i.resp_update_allowed_flag,
                   x_user_enabled_flag        => i.user_enabled_flag,
                   x_user_update_allowed_flag => i.user_update_allowed_flag,
                   x_start_date_active        => to_char(i.start_date_active,'YYYY/MM/DD'),
                   x_end_date_active          => to_char(i.end_date_active,'YYYY/MM/DD'),
                   x_sql_validation           => l_sql
                                    );
    dbms_output.put_line('done');
  end loop;
  
end;

Скрипт для создания профиля в OEBS:

DECLARE
  vr_Profile_name VARCHAR2(100) := 'PROFILE_CODE!!';
BEGIN
  fnd_profile_options_pkg.load_row
  (
    x_profile_name              => vr_Profile_name
  , x_owner                     => 'INITIAL SETUP'
  , x_application_short_name    => 'APPL_CODE!!'
  , x_user_profile_option_name  => 'PROFILE_NAME!!'
  , x_description               => 'PROFILE_NAME!!'
  , x_user_changeable_flag      => 'Y'
  , x_user_visible_flag         => 'Y'
  , x_read_allowed_flag         => 'Y'
  , x_write_allowed_flag        => 'Y'
  , x_site_enabled_flag         => 'Y'
  , x_site_update_allowed_flag  => 'Y'
  , x_app_enabled_flag          => 'Y'
  , x_app_update_allowed_flag   => 'Y'
  , x_resp_enabled_flag         => 'Y'
  , x_resp_update_allowed_flag  => 'Y'
  , x_user_enabled_flag         => 'Y'
  , x_user_update_allowed_flag  => 'Y'
  , x_start_date_active         => to_char(sysdate,'yyyy/mm/dd')
  , x_end_date_active           => NULL
  , x_sql_validation            => 'SQL="SELECT LOOKUP_CODE, MEANING \"Включить проверку\"
INTO :PROFILE_OPTION_VALUE, :VISIBLE_OPTION_VALUE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = ''YES_NO''"
COLUMN="\"Включить проверку\"(20)"'
  );
  --
  IF NOT 
    fnd_profile.save
    (
      x_name           => vr_Profile_name
    , x_value          => 'Y'
    , x_level_name     => 'SITE' 
    )
  THEN
    DBMS_OUTPUT.Put_Line('Error in setting value');
  END IF;
  COMMIT;
END;

Довольно удобно выставлять профили через скрипт, особенно если их много.
Данный скрипт выставляет профили на уровне пользователя.

DECLARE
 l_user_name varchar2(100) := 'USER_NAME';

 PROCEDURE set_profile_at_user_level(p_user_name                fnd_user.user_name%TYPE,
                                     p_user_profile_option_name fnd_profile_options_vl.user_profile_option_name%TYPE,
                                     p_profile_option_value     fnd_profile_option_values.profile_option_value%TYPE) IS
   l_user_id             fnd_user.user_id%TYPE;
   l_profile_option_name fnd_profile_options_vl.profile_option_name%TYPE;
 BEGIN
 
   SELECT fu.user_id
     INTO l_user_id
     FROM fnd_user fu
    WHERE fu.user_name = p_user_name;
 
   SELECT fpo.profile_option_name
     INTO l_profile_option_name
     FROM fnd_profile_options_vl fpo
    WHERE fpo.user_profile_option_name = p_user_profile_option_name;
 
   IF Fnd_Profile.save(x_name        => l_profile_option_name,
                       x_value       => p_profile_option_value,
                       x_level_name  => 'USER',
                       x_level_value => l_user_id) THEN
     NULL;
   END IF;
 
 END set_profile_at_user_level;

BEGIN

  EXECUTE IMMEDIATE 'ALTER session SET NLS_LANGUAGE=''RUSSIAN''';

  set_profile_at_user_level (l_user_name, 'ВЕБ: время ожидания для сеанса','3600000');
  set_profile_at_user_level (l_user_name, 'ВЕБ: форматная маска даты','DD-MM-RRRR');
  set_profile_at_user_level (l_user_name, 'Служебные программы: диагностика','Y');
  set_profile_at_user_level (l_user_name, 'Цветовая схема Java','SWAN');
  
  set_profile_at_user_level (l_user_name, 'Настройка веб-определений','Y');
  set_profile_at_user_level (l_user_name, 'БОП: диагностика','Y');
  set_profile_at_user_level (l_user_name, 'БОП: активизация ссылки области индивидуальной настройки', 'Y');
  set_profile_at_user_level (l_user_name, 'БОП: режим встраивания индивидуальной настройки','Y');
  COMMIT;

END;

SELECT fpo.profile_option_name
      ,fpo.user_profile_option_name
      ,fpov.profile_option_value
FROM fnd_profile_option_values fpov,
       fnd_profile_options_vl    fpo
WHERE fpov.application_id    = fpo.application_id
      AND fpov.profile_option_id = fpo.profile_option_id
      AND fpov.level_value = (
                        SELECT fu.user_id
                        FROM fnd_user fu
                        WHERE fu.user_name = '!!!USER_NAME'
                       )

1) Отключение запроса пароля APPS при вызове диагностики или проверки.

Установите следующие системные профили в полномочиях системного администратора

Читать дальше про “OEBS Полезные системные профили” »