Для удобного доступа к логу и xml канкарента, можно использовать следующий запрос
SELECT get_log_bfile('l' || x.request_id || '.req') as log_date,
get_xml_bfile('o' || x.request_id || '.out') as xml_date
FROM fnd_concurrent_requests x
WHERE x.request_id = :p_request_id
Но перед его использованием следует создать директории для доступа к файлам:
create or replace directory CUNCURRENT_XML
as '/oracle/NAME_INSTANCE/inst/apps/NAME/logs/appl/conc/out';
create or replace directory CUNCURRENT_LOG
as '/oracle/NAME_INSTANCE/inst/apps/NAME/logs/appl/conc/log';
и сами функции
create or replace function get_log_bfile(p_filename in varchar2)
return bfile as
l_bfile bfile := bfilename('CUNCURRENT_LOG',p_filename);
begin
return l_bfile;
end;
/
create or replace function get_xml_bfile(p_filename in varchar2 )
return bfile as
l_bfile bfile := bfilename('CUNCURRENT_XML',p_filename);
begin
return l_bfile;
end;
/
-- Вывод сообщения в лог
procedure output_log_data(p_log_str in varchar2) is
g_output_dbms number := 1; -- Вывод сообщений в dbms_output.put_line
g_output_oebs number := 2; -- Вывод сообщений в fnd_file.put_line
v_output_current number := g_output_oebs;
begin
if fnd_global.conc_request_id = -1 then
v_output_current := g_output_dbms;
end if;
if v_output_current = g_output_dbms then
dbms_output.put_line(p_log_str);
elsif v_output_current = g_output_oebs then
--fnd_file.put_line(fnd_file.output, p_log_str); --Строка выводится в "Просмотр Результатов"
fnd_file.put_line(fnd_file.log, p_log_str); --Строка выводится в "Просмотр Журнала"
end if;
end output_log_data;
declare
vr_Form_name VARCHAR2(100) := 'XX_NAME';
vr_Description VARCHAR2(200) := 'XX_DESCRIPTION';
begin
-- Регистрация функции формы
fnd_form_functions_pkg.load_row
(
x_function_name => vr_Form_name
, x_application_short_name => 'FND'
, x_form_name => 'FNDRSRUN'
, x_parameters => 'CONCURRENT_PROGRAM_NAME="'||vr_Form_name
||'" PROGRAM_APPL_SHORT_NAME="'||'XX'
||'" TITLE="'||vr_Description||'"'
, x_type => 'FORM'
, x_web_host_name => NULL
, x_web_agent_name => NULL
, x_web_html_call => NULL
, x_web_encrypt_parameters => NULL
, x_web_secured => NULL
, x_web_icon => NULL
, x_object_name => NULL
, x_region_application_name => NULL
, x_region_code => NULL
, x_user_function_name => vr_Description
, x_description => vr_Description
, x_owner => 'INITIAL SETUP'
, x_custom_mode => 'FORCE'
);
-- Регистрация пункта меню
fnd_menu_entries_pkg.load_row
(
x_mode => 'REPLACE'
, x_ent_sequence => 910
, x_menu_name => 'MENU_NAME'
, x_sub_menu_name => NULL
, x_function_name => vr_Form_name
, x_grant_flag => 'Y'
, x_prompt => vr_Description
, x_description => vr_Description
, x_owner => USER
);
--
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(substr('Error: ' || SQLERRM, 1, 255));
ROLLBACK;
end;
/
SHOW ERRORS
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;
Пакет inv_convert
FUNCTION inv_um_convert(p_item_id IN NUMBER,
p_from_uom_code IN VARCHAR2,
p_to_uom_code IN VARCHAR2) RETURN NUMBER;
Пример:
select msi.segment1,
msi.primary_uom_code,
'кг' as second_uom,
inv_convert.inv_um_convert(msi.inventory_item_id,'кг',
msi.primary_uom_code) as coefficient
from mtl_system_items_b msi
where msi.primary_uom_code = 'т'
and rownum <= 10
Последние комментарии