Using seeded Generic File Manager Upload and Download features

Dear Readers

I am sure many of you have seen on several seeded Oracle forms there is a way to upload/download files from the system using OAF pages

In this post we will talk about how we can leverage the seeded page in a custom form to upload/download a file from Oracle

The key to note is that whenever a file is uploaded via this page, the contents of the same get stored in the FND_LOBS table as a BLOB data

Once the file is uploaded and stored in the table using this page, we can process it anyway we wish by writing to file system, using it as attachment in a mail etc. so this is quite useful feature we can use

Let us first take a look at the upload functionality and how that works. Suppose we have a form with layout as shown below and the highlighted button is the one user will click to upload the file

Now, the underlying code of this button is as shown below

DECLARE
access_id NUMBER;
button_choice NUMBER;
l_gfm_id NUMBER := NULL;
l_file_id VARCHAR2(256);
l_parameters           varchar2(255);
l_server_url VARCHAR2(255);
BEGIN
access_id := fnd_gfm.authorize(null);
fnd_profile.get('APPS_WEB_AGENT', l_server_url);

l_parameters :='access_id='||access_id||'&l_server_url='||l_server_url;

fnd_function.execute
(
FUNCTION_NAME=>'FND_FNDFLUPL',
OPEN_FLAG => 'Y',
SESSION_FLAG => 'Y',
OTHER_PARAMS =>l_parameters
);

FND_MESSAGE.set_name('FND','ATCHMT-FILE-UPLOAD-COMPLETE');

button_choice := FND_MESSAGE.question(
button1=> 'YES',
button2=> null,
button3=> 'NO',
default_btn => 1,
cancel_btn =>3,
icon=> 'question');

IF ( button_choice = 3 ) THEN
fnd_message.debug('File upload has failed! Please re-try again');
ELSIF ( button_choice = 1 ) THEN
l_gfm_id := fnd_gfm.get_file_id(access_id);
IF l_gfm_id IS NOT NULL THEN

select decode(instr(file_name,'/'),0,file_name,
             substr(file_name,instr(file_name,'/')+1))
into   l_file_id
from   fnd_lobs
where  file_id = l_gfm_id;

l_file_id := REPLACE(l_file_id, ' ', '_');
END IF;
END IF;
END;

The code can be divided into 3 parts. The first is setting up parameters and then using fnd_function.execute to open the seeded page. The second is an alert/message on the form asking user to confirm if the file uploaded successfully via the page or not. The third part if the user selects YES as his response on the form is to process the file BLOB data in any way which we want

Now, coming to the download part, this is much easier. All we need to know is the FILE_ID value in the FND_LOBS which we want to download from the system and create a button which will have the underlying code as below

DECLARE
l_gfm_id  NUMBER;
gfm_agent VARCHAR2(255);
l_url  VARCHAR2(2000);
BEGIN
l_gfm_id := <PUT YOUR FILE ID VALUE HERE>;
gfm_agent := fnd_web_config.gfm_agent;
l_url := fnd_gfm.construct_download_URL(gfm_agent, l_gfm_id, FALSE);
fnd_utilities.open_url(l_url);
END;

These features can be used on any form which you may need similar functionality. Hope this helps

Cheers
A