Oracle EBS ATTACHMENT with file

最新推荐文章于 2023-02-10 08:35:59 发布

congqishi0629

最新推荐文章于 2023-02-10 08:35:59 发布

阅读量219

点赞数

文章标签：数据库

DECLARE
v_entity_name VARCHAR2(30) := 'ENG_ENGINEERING_CHANGES';
v_media_id NUMBER;
v_category_id NUMBER;
v_seq_num NUMBER;
v_datatype_id NUMBER;
p_entity_id NUMBER := 9008;
p_document_desc VARCHAR2(120) := 'TEST';
p_file_name VARCHAR2(100) := '11-a.pdf';
b_file BFILE;
b_lob BLOB;
BEGIN
fnd_global.apps_initialize(user_id => 1,
resp_id => 2,
resp_appl_id => 3);
dbms_application_info.set_client_info(4);
SELECT category_id
INTO v_category_id
FROM fnd_document_categories_tl
WHERE user_name = 'ECO Attachments';
SELECT datatype_id
INTO v_datatype_id
FROM fnd_document_datatypes
WHERE NAME = 'FILE'
AND LANGUAGE = 'US';
SELECT (nvl(MAX(seq_num),
0) + 10)
INTO v_seq_num
FROM fnd_attached_documents
WHERE entity_name = v_entity_name
AND pk1_value = to_char(p_entity_id);
fnd_webattch.add_attachment(seq_num => v_seq_num,
category_id => v_category_id,
document_description => p_document_desc,
datatype_id => v_datatype_id,
text => NULL,
file_name => p_file_name,
url => NULL,
function_name => NULL,
entity_name => v_entity_name,
pk1_value => to_char(p_entity_id),
pk2_value => NULL,
pk3_value => NULL,
pk4_value => NULL,
pk5_value => NULL,
media_id => v_media_id,
user_id => fnd_global.user_id);
COMMIT;
SELECT media_id
INTO v_media_id
FROM fnd_attached_docs_form_vl
WHERE entity_name = v_entity_name
AND pk1_value = to_char(p_entity_id)
AND function_name = 'ENGFDECN'
AND seq_num = v_seq_num;
INSERT INTO fnd_lobs
(file_id,
file_name,
file_content_type,
file_data,
upload_date,
expiration_date,
program_name,
program_tag,
LANGUAGE,
oracle_charset,
file_format)
VALUES
(v_media_id,
p_file_name,
'application/pdf',
empty_blob(),
SYSDATE,
NULL,
'FNDATTCH',
'',
'US',
'UTF8',
'BINARY') RETURN file_data INTO b_lob;
b_file := bfilename('JACK',
p_file_name);
dbms_lob.open(b_file,
dbms_lob.file_readonly);
dbms_lob.loadfromfile(b_lob,
b_file,
dbms_lob.getlength(b_file));
dbms_lob.close(b_file);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;