Скрипт для вывода списка полномочий, в которых используется форма.
Так же добавлен путь внутри полномочий для вызова формы.
Желаемый язык для вывода данных вынесен в параметры.
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 find, fnd_form, fnd_form_functions_tl, fnd_menu_entries, fnd_responsibility, forms, OEBS, responsibility, select, запрос, поиск, полномочия, путь, форма
Последние комментарии