API to Attach documents to a Service Request

Came to know from Oracle Support that there is no public API that would take care of this requirment (refer to the Note : 394811.1 on http://metalink.oracle.com) that corroborates the fact.

Here is the custom API that we developed

CREATE OR REPLACE PROCEDURE LOADFILEINTOBLOB (p_filename IN VARCHAR2, p_content_type in varchar2, p_file_format in varchar2, p_incident_id in number, p_user_id in number) IS

out_blob BLOB;
in_file BFILE := BFILENAME(‘IN_FILE_LOC’, p_filename);
blob_length INTEGER;
v_fnd_lobs_s NUMBER;
v_fnd_attached_docs_s NUMBER;
v_fnd_docs_s NUMBER;

BEGIN

— Obtain the size of the blob file
DBMS_LOB.FILEOPEN(in_file, DBMS_LOB.FILE_READONLY);
blob_length:=DBMS_LOB.GETLENGTH(in_file);
DBMS_LOB.FILECLOSE(in_file);

SELECT fnd_lobs_s.nextval
INTO v_fnd_lobs_s from dual;
— FROM fnd_lobs where file_name = p_filename;

SELECT fnd_attached_documents_s.NEXTVAL
INTO v_fnd_attached_docs_s
FROM SYS.DUAL;

SELECT fnd_documents_s.NEXTVAL
INTO v_fnd_docs_s
FROM DUAL;

— Insert a new record into the table containing the
— filename you have specified and a LOB LOCATOR.
— Return the LOB LOCATOR and assign it to out_blob.
INSERT INTO fnd_lobs (file_id, file_name, file_content_type, file_data, file_format)
VALUES (v_fnd_lobs_s, p_filename, p_content_type, EMPTY_BLOB(), p_file_format)
RETURNING file_data INTO out_blob;

INSERT INTO fnd_documents_tl
(document_id, creation_date, created_by, last_update_date,
last_updated_by, last_update_login, LANGUAGE, description,
file_name, media_id, doc_attribute15, source_lang
)
VALUES (v_fnd_docs_s, SYSDATE, p_user_id, SYSDATE,
p_user_id, ”, ‘US’, ”,
p_filename, v_fnd_lobs_s, v_fnd_docs_s, ‘US’
);

DBMS_OUTPUT.PUT_LINE(‘Media Id : ‘ || v_fnd_lobs_s );

INSERT INTO fnd_attached_documents
(attached_document_id, document_id, creation_date, created_by,
last_update_date, last_updated_by, seq_num, entity_name,
pk1_value, automatically_added_flag
)
VALUES (v_fnd_attached_docs_s, v_fnd_docs_s, SYSDATE, p_user_id,
SYSDATE, p_user_id, 10, ‘CS_INCIDENTS’,
p_incident_id, ‘N’
);

DBMS_OUTPUT.PUT_LINE(‘Doc Id : ‘ || v_fnd_attached_docs_s );

— Load the image into the database as a BLOB
DBMS_LOB.OPEN(in_file, DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(out_blob, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(out_blob, in_file, blob_length);

— Close handles to blob and file
DBMS_LOB.CLOSE(out_blob);
DBMS_LOB.CLOSE(in_file);

COMMIT;

DBMS_OUTPUT.PUT_LINE(‘Successfully inserted the file’);

END;