Если, при кастомизации стандартной формы OEBS, возникает необходимость сохранить функциональность аттачментов, то реализовать это поможет следующий скрипт:
--copy attachment funtion procedure
DECLARE
l_cnt NUMBER;
l_new_ff_id NUMBER;
l_new_app_id NUMBER;
l_new_att_function_id NUMBER;
l_new_att_block_id NUMBER;
e_exists EXCEPTION;
e_ff_problem EXCEPTION;
e_procc_err EXCEPTION;
--
l_old_func_name VARCHAR2(100) := 'LAST_FUNCTION_NAME';
l_new_func_name VARCHAR2(100) := 'NEW_FUNCTION_NAME';
BEGIN
--(-1) проверка на уже сущ-юю
SELECT COUNT(1)
INTO l_cnt
FROM fnd_attachment_functions
WHERE function_name = l_new_func_name;
IF l_cnt > 0 THEN
RAISE e_exists;
END IF;
--0) определяем id форм-функции
BEGIN
SELECT t.function_id, t.application_id
INTO l_new_ff_id, l_new_app_id
FROM fnd_form_functions t
WHERE t.function_name = l_new_func_name;
EXCEPTION
WHEN OTHERS THEN
RAISE e_ff_problem;
END;
--1) находим сущ-щую ф-ю:
l_cnt := 0;
FOR i IN (SELECT * FROM fnd_attachment_functions
WHERE function_name = l_old_func_name
AND function_type = 'O' --форма
)
LOOP
l_cnt := l_cnt + 1;
IF l_cnt > 1 THEN
RAISE e_procc_err;
END IF;
--вставка новой строки в FND_ATTACHMENT_FUNCTIONS
INSERT INTO fnd_attachment_functions
(attachment_function_id,
function_type,
function_id,
function_name,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
application_id,
session_context_field,
enabled_flag)
VALUES
(fnd_attachment_functions_s.NEXTVAL,
i.function_type,
l_new_ff_id,
l_new_func_name,
SYSDATE,
1,
SYSDATE,
1,
1,
l_new_app_id,
i.session_context_field,
i.enabled_flag
) RETURNING attachment_function_id INTO l_new_att_function_id;
--2) заполняем FND_DOC_CATEGORY_USAGES
FOR j IN (SELECT * FROM fnd_doc_category_usages
WHERE attachment_function_id = i.attachment_function_id)
LOOP
INSERT INTO fnd_doc_category_usages
(doc_category_usage_id,
category_id,
attachment_function_id,
enabled_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(fnd_doc_category_usages_s.nextval,
j.category_id,
l_new_att_function_id,
j.enabled_flag,
SYSDATE,
1,
SYSDATE,
1,
1);
END LOOP;
--3) заполняем FND_ATTACHMENT_BLOCKS
FOR k IN (SELECT * FROM fnd_attachment_blocks
WHERE attachment_function_id = i.attachment_function_id)
LOOP
INSERT INTO fnd_attachment_blocks
(attachment_blk_id,
attachment_function_id,
block_name,
query_flag,
security_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
org_context_field,
set_of_books_context_field,
business_unit_context_field,
context1_field,
context2_field,
context3_field)
VALUES
(fnd_attachment_blocks_s.nextval,
l_new_att_function_id,
k.block_name,
k.query_flag,
k.security_type,
sysdate,
1,
sysdate,
1,
1,
k.org_context_field,
k.set_of_books_context_field,
k.business_unit_context_field,
k.context1_field,
k.context2_field,
k.context3_field
) RETURNING attachment_blk_id INTO l_new_att_block_id;
FOR l IN (SELECT * FROM fnd_attachment_blk_entities
WHERE attachment_blk_id = k.attachment_blk_id)
LOOP
INSERT INTO fnd_attachment_blk_entities
(attachment_blk_entity_id,
attachment_blk_id,
data_object_code,
display_method,
include_in_indicator_flag,
indicator_in_view_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
pk1_field,
pk2_field,
pk3_field,
pk4_field,
pk5_field,
sql_statement,
query_permission_type,
insert_permission_type,
update_permission_type,
delete_permission_type,
condition_field,
condition_operator,
condition_value1,
condition_value2)
VALUES
(fnd_attachment_blk_entities_s.nextval,
l_new_att_block_id,
l.data_object_code,
l.display_method,
l.include_in_indicator_flag,
l.indicator_in_view_flag,
sysdate,
1,
sysdate,
1,
1,
l.pk1_field,
l.pk2_field,
l.pk3_field,
l.pk4_field,
l.pk5_field,
l.sql_statement,
l.query_permission_type,
l.insert_permission_type,
l.update_permission_type,
l.delete_permission_type,
l.condition_field,
l.condition_operator,
l.condition_value1,
l.condition_value2);
END LOOP;
END LOOP;
END LOOP;
--commit;
EXCEPTION
WHEN e_exists THEN
ROLLBACK;
dbms_output.put_line('Ошибка. Для данной ф-ии уже создана attachment_function');
WHEN e_ff_problem THEN
ROLLBACK;
dbms_output.put_line('Ошибка. Не найдена указанная форм-функция');
WHEN e_procc_err THEN
ROLLBACK;
dbms_output.put_line('Ошибка. Найдено более 1 функции для копирования.');
END;
Последние комментарии