Запрос для вывода списка полномочий и групп запросов по коду или имени отчета/параллельной программы:
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;
Categories: Oracle e-Business Suite Tags: fnd_application, fnd_concurrent_programs, fnd_request_groups, fnd_responsibility, OEBS, report, request groups, responsibility, select, отчет, параллельная программа, полномочия, список
Скрипт для вывода списка полномочий, в которых используется форма.
Так же добавлен путь внутри полномочий для вызова формы.
Желаемый язык для вывода данных вынесен в параметры.
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
)
Categories: Oracle e-Business Suite, Oracle Forms Tags: find, fnd_form, fnd_form_functions_tl, fnd_menu_entries, fnd_responsibility, forms, OEBS, responsibility, select, запрос, поиск, полномочия, путь, форма
Пример использования 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;
/
ОЕБС Расширенный скрипт для инициализации сессии:
- Выводит в output короткую запись для инициализации.
- Если не заданы полномочия или приложение, то выводит в output список назначенных пользователю полномочий.
declare
l_lang varchar2(2) := 'RU';
l_user_name varchar2(100) := 'AOSTAPISHEN';
l_resp_name varchar2(400);
l_resp_appl_name varchar2(400);
-- ------------------------------------------------------------------
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
l_appl_code VARCHAR2(10);
l_org_id NUMBER;
l_mo_sec_profile NUMBER;
l_mo_product number;
begin
-- l_resp_name := 'Управление активами предприятия';
-- l_resp_name := 'Диспетчер Дебиторов';
-- l_resp_name := 'Разработчик приложений';
-- l_resp_name := 'Движение денежных средств: суперпользователь';
-- l_resp_appl_name := 'Управление активами предприятия';
-- l_resp_appl_name := 'Главная книга';
-- l_resp_appl_name := 'Дебиторы'
-- l_resp_appl_name := 'Кредиторы';
-- l_resp_appl_name := 'Движение денежных средств';
-- l_resp_appl_name := 'Библиотека объектов приложений';
select user_id into l_user_id from fnd_user t where user_name like l_user_name;
if (l_resp_name is null or l_resp_appl_name is null) then
for i in (
SELECT ap.application_short_name as appl_code,
a.application_name as appl_name,
r.responsibility_name as resp_name,
r0.responsibility_key,
--
max(length(a.application_name )) over() as length_appl_name
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r,
fnd_responsibility r0,
fnd_application ap
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 = l_lang
and ap.application_id = a.application_id
and r.language = l_lang
and u.user_name = l_user_name
and r.responsibility_name <> 'Диагностика приложения'
and r.responsibility_name = nvl(l_resp_name,r.responsibility_name)
and a.application_name = nvl(l_resp_appl_name,a.application_name)
and r0.responsibility_id = r.responsibility_id
ORDER BY appl_name, resp_name
)
loop
dbms_output.put_line(rpad('l_resp_appl_name := '''||i.appl_name||'''',i.length_appl_name+22,' ') || ' ; l_resp_name := '''||i.resp_name||''';');
dbms_output.put_line(rpad('appl_code := '''||i.appl_code||'''',i.length_appl_name+22,' ') || ' ; responsibility_key := '''||i.responsibility_key||''';');
end loop;
else
select b.application_id, b.application_short_name into l_resp_appl_id, l_appl_code
from fnd_application_tl t
,fnd_application b
where 1=1
and t.application_name like l_resp_appl_name
and t.language = l_lang
and b.application_id = t.application_id;
select t.responsibility_id into l_resp_id from fnd_responsibility_tl t
where t.responsibility_name like l_resp_name and t.language = l_lang and t.application_id = l_resp_appl_id;
l_org_id := fnd_profile.VALUE_SPECIFIC(NAME => 'ORG_ID', RESPONSIBILITY_ID => l_resp_id, APPLICATION_ID=> l_resp_appl_id);
fnd_global.apps_initialize( user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_resp_appl_id
);
SELECT count(1) into l_mo_product FROM fnd_mo_product_init mpi where mpi.application_short_name = l_appl_code and nvl(mpi.status, 'N') = 'Y';
if (l_mo_product = 1) then
begin
MO_GLOBAL.init(l_appl_code);
if (l_org_id is not null) then
mo_global.set_policy_context('M',l_org_id);
end if;
l_mo_sec_profile := fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL');
mo_global.set_org_context(l_resp_id,l_mo_sec_profile,l_appl_code);
exception
when others then
dbms_output.put_line('SQLERRM:'||SQLERRM||chr(10));
dbms_output.put_line('FORMAT_ERROR_BACKTRACE:'||chr(10)||dbms_utility.format_error_backtrace);
end;
dbms_output.put_line('begin'||chr(10)||' fnd_global.apps_initialize('||l_user_id||', '||l_resp_id||', '||l_resp_appl_id||'); --'||l_resp_name||' - '||l_resp_appl_name
||chr(10)||' mo_global.init('''|| l_appl_code||''');'
||case when l_org_id is not null then chr(10)||' mo_global.set_policy_context(''M'','||l_org_id||');' end
||chr(10)||' mo_global.set_org_context('||l_resp_id||', '||l_mo_sec_profile||', '''||l_appl_code||''');'
||chr(10)||'end;');
else
dbms_output.put_line('begin'||chr(10)||' fnd_global.apps_initialize('||l_user_id||', '||l_resp_id||', '||l_resp_appl_id||'); --'||l_resp_name||' - '||l_resp_appl_name
||chr(10)||'end;');
end if;
end if;
end;
С помощью данного 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 добавления полномочия для пользователя” »
Запрос для просмотра полномочий пользователя 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
Последние комментарии