Архив

Публикации с меткой ‘responsibility’

Список полномочий и групп запросов для отчета

15 Апрель 2016 Нет комментариев

Запрос для вывода списка полномочий и групп запросов по коду или имени отчета/параллельной программы:

with params as (
select 'FNDATREP' as short_name
      ,''    as long_name
      ,'RU'  as lang /* RU;US */
from dual
)
select  p1.concurrent_program_name
       ,pt.user_concurrent_program_name
       ,fa.application_short_name as appl_resp
       ,r.responsibility_key
       ,rt.responsibility_name      
       ,fa1.application_short_name as appl_request_group
       ,g.request_group_name
from fnd_request_groups      g,
     fnd_request_group_units u,
     fnd_application    fa,
     fnd_application    fa1,         
     fnd_concurrent_programs_tl pt,
     fnd_concurrent_programs    p1,
     fnd_responsibility    r,
     fnd_responsibility_tl rt,
     params
where 1=1
      and u.application_id = g.application_id
      and u.request_group_id = g.request_group_id
      and (u.request_unit_id = pt.concurrent_program_id
          or u.request_unit_type = 'A')
      and u.unit_application_id = pt.application_id
      and pt.application_id = fa.application_id
      and pt.concurrent_program_id = p1.concurrent_program_id
      and g.application_id = fa1.application_id
      and r.request_group_id = g.request_group_id
      and r.responsibility_id = rt.responsibility_id
      and rt.language = params.lang
      and pt.language  = params.lang
      and (
            (p1.concurrent_program_name like '%'||params.short_name||'%' 
             and params.short_name is not null 
            ) 
            or 
            (pt.user_concurrent_program_name like '%'||params.long_name||'%' 
             and params.long_name is not null and params.short_name is null
            )
          )
order by 1,2;

Список полномочий где используется форма + путь для вызова

15 Апрель 2016 Нет комментариев

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

with params as (
select 'FNDSCRSP' as form_name
      ,''         as user_form_name
      ,''         as description_form
      ,'RU'       as lang /* RU;US */
from dual
)
select (select rt.responsibility_name from fnd_responsibility_tl rt , params
        where rt.responsibility_id  = r.responsibility_id
              and rt.application_id = r.application_id
              and rt.language = params.lang
       ) as responsibility_name
      ,(select ltrim(sys_connect_by_path(me.prompt, ' --> '),' --> ') 
        from (select b.menu_id,b.entry_sequence,b.sub_menu_id,b.function_id, t.prompt
             from fnd_menu_entries_tl t, fnd_menu_entries b, params
             where b.menu_id = t.menu_id
               and b.entry_sequence = t.entry_sequence
               and t.language = params.lang) me
        where me.prompt is not null 
              and me.function_id = ff1.function_id 
              and rownum=1
        start with me.menu_id = r.menu_id  
        connect by prior me.sub_menu_id = me.menu_id and prior me.prompt is not null
       ) path_to_form
      ,r.application_id
      ,r.responsibility_id
      ,r.responsibility_key 

      ,r.menu_id    
      ,(select mt.user_menu_name from fnd_menus_tl mt, params where mt.menu_id = r.menu_id and mt.language = params.lang) as user_menu_name
      ,ff1.function_id
      ,(select fft.user_function_name from fnd_form_functions_tl fft, params where fft.function_id = ff1.function_id and fft.language = params.lang) as user_function_name
      ,ff1.form_id 
      ,(select ff.form_name from fnd_form ff where ff.form_id = ff1.form_id ) as form_name      
      ,(select ftl.user_form_name from fnd_form_tl ftl, params where ftl.form_id = ff1.form_id  and ftl.language = params.lang) as user_form_name
from fnd_responsibility r
    ,(select ff.function_id, ff.form_id from fnd_form_functions ff 
      where ff.form_id in (select f.form_id 
                           from fnd_form f, fnd_form_tl ft, params
                           where f.form_id = ft.form_id
                                 and (
                                        f.form_name = params.form_name 
                                     or (ft.user_form_name like '%'||params.user_form_name||'%' and params.user_form_name is not null)
                                     or (ft.description like '%'||params.description_form||'%' and params.description_form is not null)
                                     )
                          )
     ) ff1 
where 1=1
      and sysdate between r.start_date and nvl(r.end_date, sysdate+1)
      and r.menu_id in (select me.menu_id from fnd_menu_entries me 
                        start with me.function_id = ff1.function_id
                        connect by prior me.menu_id = me.sub_menu_id 
                       )

API для обновления полномочий (RESPONSIBILITY)

25 Февраль 2013 Нет комментариев

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

BEGIN
  FOR cr IN (SELECT * FROM fnd_responsibility_vl
             WHERE responsibility_key LIKE 'XX%'
            ) 
  LOOP
    fnd_responsibility_pkg.update_row(
            x_responsibility_id         => cr.responsibility_id,
            x_application_id            => cr.application_id,
            x_web_host_name             => cr.web_host_name,
            x_web_agent_name            => cr.web_agent_name,
            x_data_group_application_id => cr.data_group_application_id,
            x_data_group_id             => cr.data_group_id,
            x_menu_id                   => cr.menu_id,
            x_start_date                => to_date('01-01-1951','dd-mm-yyyy'),
            x_end_date                  => cr.end_date,
            x_group_application_id      => cr.group_application_id,
            x_request_group_id          => cr.request_group_id,
            x_version                   => cr.VERSION,
            x_responsibility_key        => cr.responsibility_key,
            x_responsibility_name       => cr.responsibility_name,
            x_description               => cr.description,
            x_last_update_date          => SYSDATE,
            x_last_updated_by           => fnd_global.user_id,
            x_last_update_login         => fnd_global.login_id);
  END LOOP;

  COMMIT;
END;
/

API добавления полномочия для пользователя

3 Ноябрь 2011 2 comments

С помощью данного API можно добавить полномочие пользователю

begin
  -- Call the procedure
  fnd_user_pkg.addresp(username       => :username,
                       resp_app       => :resp_app,
                       resp_key       => :resp_key,
                       security_group => :security_group, -- в основном 'STANDARD'
                       description    => :description,    -- любой коммент
                       start_date     => :start_date,
                       end_date       => :end_date);
  commit;                    
  dbms_output.put_line('Responsibility Added Successfully');
exception
 when others then
   dbms_output.put_line(' Responsibility is not added due to ' 
                        || substr(SQLERRM, 1, 100));
   rollback;
end;

И весьма полезный скрипт, c помощью которого можно быстро перетянуть полномочия с одного инстанса на другой.
Читать дальше про “API добавления полномочия для пользователя” »