Image FIle Attachment in Oracle APps 11i

Reference: http://www.khwaja.info

Technical Details of Image File Attachment in Oracle Applications 11i.

Attaching an image file in Oracle Apps say, you attach an image of an Item.
For this you will click on the attachments icon(Paperclip Icon) of the toolbar in Apps Window and this will take you to Attachment Window.
Here You can specify Category, Description,Datatype.
If you select "File" as datatype, you will be taken to a webpage asking for uploading the image.
Once you upload the image successfully, You can view the image using the "Open Document" button on the "Attachment Window".

This is how you will be uploading the image.

Q. Now where did the image go and how can you access it??
A. When you attach a file say, "A380.jpg", you can find the document_id of that image using
query:

SELECT document_id
FROM fnd_documents_tl
WHERE file_name = 'A380.jpg'
output: document_id = 11807

Use this document_id to get the media_id of the file. Media_id is the actual file_id.
SELECT media_id
FROM fnd_documents_tl
WHERE document_id = 11807
output: media_id = 318949

Use the media_id in below query to get the file details.
SELECT file_id, file_name,file_content_type,file_data
FROM fnd_lobs
WHERE file_id = 318949

In this table, "FND_LOBS" you can find the BLOB column, "FILE_DATA" which stores the actual file.

If you want to show this file on a webpage, ie. if the user wants to see the image of the item
from a custom form for items,he can click a button on the form which will take him to a webpage
where he will be able to see the image.

For this, you can use the below query to get the url for the image:

SELECT fnd_gfm.construct_download_URL(fnd_web_config.gfm_agent,318929)
FROM dual

You can use this url say, "l_url" in fnd_utilities.open_url to open the webpage containing the image.
fnd_utilities.open_url (l_url)

If you want to show the image on the form itself then we need to add the file_data column of fnd_lobs table to the datablock in the form. and use it for displaying in the Form.