Просмотр полномочий пользователя

SELECT distinct
       u.user_id,
       u.user_name,
       r.responsibility_name as responsiblity,
       a.application_name    as application
FROM fnd_user u,
       fnd_user_resp_groups g,
       fnd_application_tl a,
       fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
      AND g.responsibility_application_id = a.application_id
      AND a.application_id = r.application_id
      AND g.responsibility_id = r.responsibility_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);

 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

  l_user_name := '!!!USER_NAME';

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