APPS-ORACLE.RU

Запрос для просмотра полномочий пользователя OEBS:

SELECT distinct
       u.user_id,
       u.user_name,
       fr.responsibility_key,
       r.responsibility_name as responsiblity,
       fa.application_short_name,
       a.application_name    as application,
       sg.security_group_key,
       'dbms_output.put_line('''|| r.responsibility_name ||''');'||chr(10)||'fnd_user_pkg.addresp(l_user_name, '''||fa.application_short_name||''', '''|| fr.responsibility_key ||''', '''||sg.security_group_key||''', ''Add by script'', sysdate, null);' as str
FROM fnd_user              u,
     fnd_user_resp_groups  g,
     fnd_application_tl    a,
     fnd_application       fa,
     fnd_responsibility_tl r,
     fnd_responsibility    fr,
     fnd_security_groups   sg
WHERE g.user_id(+) = u.user_id
      AND g.responsibility_application_id = a.application_id
      AND a.application_id = r.application_id
      and fa.application_id = r.application_id
      AND g.responsibility_id  = r.responsibility_id
      and fr.responsibility_id = r.responsibility_id
      and fr.application_id    = r.application_id
      and sg.security_group_id = g.security_group_id
      AND a.LANGUAGE = 'RU'
      AND r.LANGUAGE = 'RU'
      AND u.user_name = '!!!USER_NAME'
ORDER BY user_name, responsiblity, application

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

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'
                       )

declare
  l_user_id number;

  l_responsibility_key fnd_responsibility.responsibility_key%TYPE
         := 'SYSTEM_ADMINISTRATOR';
  l_application_short_name fnd_application.application_short_name%TYPE
         := 'SYSADMIN';

  l_responsibility_id   number;
  l_application_id      number;
  l_responsibility_name varchar2(200) := 'System Administrator';
  l_security_group_id number := 0;
begin
  l_user_id := fnd_user_pkg.CreateUserId(
      x_user_name            => 'TEST'
    , x_owner                => 'CUST'
    , x_unencrypted_password => '123456'
  );
select application_id into l_application_id
from fnd_application
where application_short_name = l_application_short_name;

select responsibility_id into l_responsibility_id
from fnd_responsibility
where application_id     = l_application_id
and responsibility_key = l_responsibility_key;

  fnd_user_resp_groups_api.insert_assignment(
    user_id                         => l_user_id
    , responsibility_id             => l_responsibility_id
    , responsibility_application_id => l_application_id
    , security_group_id             => l_security_group_id
    , start_date                    => trunc(sysdate)
    , end_date                      => NULL
    , description                   => l_responsibility_name
    );

  fnd_wf_engine.default_event_raise(
     'oracle.apps.fnd.security.user.assignment.change'
     , l_user_id || ':' || l_responsibility_id
     );

  fnd_wf_engine.propagate_user_role(
'FND_USR'
    , l_user_id
    , 'FND_RESP' || l_application_id
    , l_responsibility_id
    , trunc(sysdate)
    , NULL
  );

  -- Added for Function Security Cache Invalidation Project
  fnd_function_security_cache.insert_user_resp(
      l_user_id
    , l_responsibility_id
    , l_application_id
    );
commit;
end;