EBS API：附件批量导入

附件相关表
附件主要视图：fnd_attached_docs_form_vl
附件主要表：fnd_attached_documents
附件表：fnd_documents
附件数据类型表：fnd_document_datatypes
附件短文本表：fnd_documents_short_text
附件长文本表：fnd_documents_long_text
附件文件表：fnd_lobs

Related API: fnd_webattch.add_attachment

文件类型附件的批量导入。例： 采购单导入图片附件

DECLARE
    v_entity_name VARCHAR2(30) := 'PO_HEADERS';
    v_media_id NUMBER;
    v_category_id NUMBER;
    v_seq_num NUMBER;
    v_datatype_id NUMBER;

--批量导入的话可将以下信息保存在临时表，游标循环插入
    p_entity_id NUMBER := 33933;           --po_header_id
    p_document_desc VARCHAR2(120) := 'TEST';
    p_file_name VARCHAR2(100) := 'po.bmp'; --文件名称
     
    b_file BFILE;
    b_lob BLOB;
    
BEGIN
    --Initialize
    fnd_global.apps_initialize(user_id      => 0,       --SYSADMIN
                               resp_id      => 50260,   --TC_PO_SUPERUSER
                               resp_appl_id => 201);    --採購管理系統

    dbms_application_info.set_client_info(4);

    --Get category id
    SELECT category_id
    INTO v_category_id
    FROM fnd_document_categories_tl
    WHERE user_name = 'To Supplier';  

    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);

    --Invoke the API for attaching the file to the PO
    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 = 'PO_POXPOEPO'
    AND seq_num = v_seq_num;

--file_content_type由导入的文件类型（bmp图片、Word文档等）决定
--不确定的话可以在界面上添加附件，然后查询fnd_lobs表
    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, 'image/bmp', empty_blob(), SYSDATE,
           NULL, 'FNDATTCH', '', 'US', 'UTF8', 'BINARY') 
    RETURN file_data INTO b_lob;
     
    --create or replace directory USER_DIR as '/tmp';
    --select * from dba_directories 
    b_file := bfilename('USER_DIR',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;

短文本、长文本类型的附件也是同一个API：fnd_webattch.add_attachment
例： 采购单导入短文本附件

DECLARE
    v_entity_name VARCHAR2(30) := 'PO_HEADERS';
    v_media_id NUMBER;
    v_category_id NUMBER;
    v_seq_num NUMBER;
    v_datatype_id NUMBER;

    p_entity_id NUMBER := 33933;     --po_header_id
    p_document_desc VARCHAR2(120) := 'TEST';
    p_text LONG :=  'PO Short Text Attachment Test.';
    
BEGIN
    --Initialize
    fnd_global.apps_initialize(user_id      => 0,       --SYSADMIN
                               resp_id      => 50260,   --TC_PO_SUPERUSER
                               resp_appl_id => 201);    --採購管理系統

    dbms_application_info.set_client_info(4);

    --Get category id
    SELECT category_id
    INTO v_category_id
    FROM fnd_document_categories_tl
    WHERE user_name = 'To Supplier';  

    SELECT datatype_id
    INTO v_datatype_id
    FROM fnd_document_datatypes
    WHERE NAME = 'SHORT_TEXT'
    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);

    --Invoke the API for attaching the file to the PO
    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                 => p_text,
                                file_name            => NULL,
                                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;

    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
END;

补充：附件的删除
Reference： http://www.shareoracleapps.com/2010/03/how-to-delete-attachment-from-backend.html
Related API: fnd_attached_documents2_pkg.delete_attachments

declare 
    l_entity_name varchar2(20):= 'PO_HEADERS';   
    l_pk1_value varchar2 (20) := '33933';     
    l_delete_document_flag varchar2 (1):= 'Y';    

begin
    --Initialize
    fnd_global.apps_initialize(user_id      => 0,       --SYSADMIN
                               resp_id      => 50260,   --TC_PO_SUPERUSER
                               resp_appl_id => 201);    --採購管理系統
                                
    fnd_attached_documents2_pkg.delete_attachments
                               ( X_entity_name          => l_entity_name
                                ,X_pk1_value            => l_pk1_value
                                ,X_delete_document_flag => l_delete_document_flag);

     commit;
end;