Definition of entities
Login as Application Developer and navigate to Attachments. In our case we want to link the attachment directly to the party and we were also using Customers Online. So we used the IMC_ATTACHMENTS entity and the party_id as primary key. We used hard coded category Miscellaneous, but of course you can also choose the category.
Create attachment
Now this is the code to create the attachment. For an URL you don't need the actual blob, but for a file you do. We use API's to create the attachment except for the actual BLOB in FND_LOBS. Apparently there is no public API for this (
l_rowid rowid;
l_attached_document_id number;
l_document_id number;
l_media_id number;
l_category_id number := 1; -- MISC
l_pk1_value fnd_attached_documents.pk1_value%TYPE := :p300_party_id; -- Unique id of entity it is attached to
l_description fnd_documents_tl.description%TYPE := :p300_description;
l_filename fnd_documents_tl.file_name%TYPE;
l_seq_num number;
l_mime_type varchar2(240);
l_datatype_id number;
-- This was specific for blobs we uploaded in APEX. Based on the name of the file
-- we created an attachment in eBS. Of course you can also fetch your blobs
-- from somewhere else like a file system or the database.
cursor c_blob
is
select blob_content
, filename
, mime_type
from wwv_flow_file_objects$
where name = :p300_file_name
;
l_blob c_blob%rowtype;
BEGIN
-- In Apex we allowed for adding URLs and actual file upload. So depending
-- on the entry (URL or not) we created different data types.
IF :p300_url IS NOT NULL
THEN
l_datatype_id := 5; -- Web page
l_media_id := null;
ELSE
l_datatype_id := 6;
OPEN c_blob;
FETCH c_blob INTO l_blob;
CLOSE c_blob;
l_filename := l_blob.filename;
select fnd_lobs_s.nextval into l_media_id from dual;
-- Determine mime type
l_mime_type := l_blob.mime_type;
END IF;
If l_datatype_id IN ( 5,6)
THEN
select FND_DOCUMENTS_S.nextval
into l_document_id
from dual;
select FND_ATTACHED_DOCUMENTS_S.nextval
into l_attached_document_id
from dual;
select nvl(max(seq_num),0) + 10
into l_seq_num
from fnd_attached_documents
where pk1_value = l_pk1_value -- Your unique ID, we used PARTY_ID
and entity_name = 'IMC_ATTACHMENTS'; -- This depends on your setup of course
fnd_documents_pkg.insert_row
( X_ROWID => l_rowid
, X_DOCUMENT_ID => l_document_id
, X_CREATION_DATE => sysdate
, X_CREATED_BY => fnd_global.user_id
, X_LAST_UPDATE_DATE => sysdate
, X_LAST_UPDATED_BY => fnd_global.user_id
, X_LAST_UPDATE_LOGIN => 0
, X_DATATYPE_ID => l_datatype_id --5 -- Web Page
, X_CATEGORY_ID => l_category_id
, X_SECURITY_TYPE => 2
, X_PUBLISH_FLAG => 'Y'
, X_USAGE_TYPE => 'O'
, X_LANGUAGE => 'US'
, X_DESCRIPTION => l_description
, X_FILE_NAME => l_filename
, X_MEDIA_ID => l_media_id
-- R12
, X_URL => :p300_URL
);
fnd_documents_pkg.insert_tl_row
( X_DOCUMENT_ID => l_document_id
, X_CREATION_DATE => sysdate
, X_CREATED_BY => fnd_global.user_id
, X_LAST_UPDATE_DATE => sysdate
, X_LAST_UPDATED_BY => fnd_global.user_id
, X_LAST_UPDATE_LOGIN => fnd_global.login_id
, X_LANGUAGE => 'US'
, X_DESCRIPTION => l_description
-- Removed in R12
--, X_FILE_NAME => l_filename
--, X_MEDIA_ID => l_media_id
, X_TITLE => l_filename
);
--dbms_output.put_line (''+Document id : ''|| l_attached_document_id);
fnd_attached_documents_pkg.insert_row
( X_ROWID => l_rowid
, X_ATTACHED_DOCUMENT_ID => l_attached_document_id
, X_DOCUMENT_ID => l_document_id
, X_CREATION_DATE => sysdate
, X_CREATED_BY => fnd_global.user_id
, X_LAST_UPDATE_DATE => sysdate
, X_LAST_UPDATED_BY => fnd_global.user_id
, X_LAST_UPDATE_LOGIN => fnd_global.login_id
, X_SEQ_NUM => l_seq_num
, X_ENTITY_NAME => 'IMC_ATTACHMENTS'
, X_COLUMN1 => null
, X_PK1_VALUE => l_pk1_value
, X_PK2_VALUE => null
, X_PK3_VALUE => null
, X_PK4_VALUE => null
, X_PK5_VALUE => null
, X_AUTOMATICALLY_ADDED_FLAG => 'N'
, X_DATATYPE_ID => l_datatype_id
, X_CATEGORY_ID => l_category_id
, X_SECURITY_TYPE => 2
, X_PUBLISH_FLAG => 'Y'
, X_LANGUAGE => 'US'
, X_DESCRIPTION => l_description
, X_FILE_NAME => l_filename
, X_MEDIA_ID => l_media_id
-- R12
, X_URL => :p300_URL
, X_TITLE => l_description
);
IF l_media_id IS NOT NULL
THEN
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
(
l_media_id
, l_filename
, l_mime_type
, l_blob.blob_content
, sysdate
, null
, null
, null
, 'US'
, 'UTF8'
, 'binary'
);
END IF; -- URL has no file'
commit;
END IF;
END;
Logic to fetch a file as BLOB from the file system
In our case the files were loaded into Apex wwv_flow_file_objects, but in some cases if you want to apply the logic from a database, you may need to fetch the files from the file system.
Note that in that case usually your code runs on the database server and your files are most likely on the application server.
What we usually do is either use a shared directory or use a host script to copy the file from the application server to $APPLPTMP (which is usually shared) and then call your SQL Package / Script from the host script.
To load the file from an accessible file location use code like this. Note that YOUR_DIRECTORY is a directory in DBA_DIRECTORIES which refers to an actual file location. You create directories using the create directory statement.
DECLARE
l_bfile BFILE;
l_blob BLOB;
dir_name VARCHAR2(240) := 'YOUR_DIRECTORY';
dir_path VARCHAR2(240) := '&1';
file_name VARCHAR2(240) := '&2';
BEGIN
l_bfile := BFILENAME(dir_name, file_name);
IF (dbms_lob.fileexists(l_bfile) = 1) THEN
dbms_output.put_line('File Exists');
dbms_output.put_line ('Size: ' || dbms_lob.getlength(l_bfile));
dbms_lob.createtemporary (l_blob,true);
dbms_lob.fileopen( l_bfile, dbms_lob.FILE_READONLY );
dbms_output.put_line ('File is open for reading ...');
dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength(l_bfile),1,1 );
dbms_output.put_line ('Load from file ...');
dbms_lob.fileclose( l_bfile );
dbms_output.put_line ('Close file ...');
END IF;
-- So now you have your file in l_blob and you can insert it into a database for example or create your attachment with the logic above.
END;
