zaterdag 15 oktober 2016

eBS R12 Upload BLOB/URL as Attachment

I recently had a request on how to upload attachments in eBS from a file. So I dug into my old code and found this logic I once built for storing attachments (URL and files) you upload through an Apex interface as an eBS attachment. We linked it to a party id (stored in field :p300_party_id in Apex) and used a specific category.


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.


Note that we did not have to show the attachments in a form in eBS again (just in Apex), but we wanted it to be available later in Contact Center for example. In that case we should have chosen the entity Parties instead (which also uses the PARTY_ID as unique id).




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 (
declare
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;