Attachment in Oracle Application
The attachments feature in oracle application enables users to link unstructured data, such as images, word-processing documents, spreadsheets, or text to their application data. For example, users can link images to items or video to operations as operation instructions.
Where to find an attachment?
There is an attachment icon in the oracle application toolbar that indicates whether the Attachments feature is enabled in a form block. When the button is dimmed, the Attachment feature is not available. When the Attachment feature is enabled in a form block, the icon becomes a solid paper clip. The icon switches to a paper clip holding a paper when the Attachment feature is enabled in a form lock and the current record has at least one attachment.
Attachment types:
An attached document can be:
1] Short Text
Text stored in the database containing less than 2000 characters.
2] Long Text
Text stored in the database containing 2000 characters or more.
3] Image
An image that Oracle Forms can display, including: bmp, cals, jfif, jpeg, gif, pcd, pcx, pict, ras, and tif.
4] OLE Object
An OLE Object that requires other OLE server applications to view, such as Microsoft Word or Microsoft Excel.
5] Web Page
A URL reference to a web page which you can view with your web browser.
Tables Involved:
For Importing Attachments in oracle application one has to populate following tables.
1. FND_DOCUMENTS
2. FND_ATTACHED_DOCUMENTS
3. FND_DOCUMENTS_TL
4. FND_DOCUMENT_DATATYPES.
5. FND_DOCUMENT_CATEGORIES
6. FND_DOCUMENTS_LONG_TEXT (Long text type attachment).
7. FND_DOCUMENTS_SHORT_TEXT (Short text type attachment).
8. FND_DOCUMENTS_LONG_RAW
9. FND_LOBS (File type attachments).
FND_DOCUMENTS:
FND_DOCUMENTS stores language-independent information about a document. For example, each row contains a document identifier, a category identifier, the method of security used for the document (SECURITY_TYPE, where 1=Organization,2=Set of Books, 3=Business unit,4=None), the period in which the document is active, and a flag to indicate whether or not the document can be shared outside of the security type (PUBLISH_FLAG).
Other specifications in this table include: datatype (DATATYPE_ID, where 1=short text,2=long text, 3=image, 4=OLE object), image type, and storage type (STORAGE_TYPE, where 1=stored in the database, 2=stored in the file system).
The document can be referenced by many application entities and changed only in the define document form (USAGE_TYPE=S); it can be used as a fill-in-the-blanks document, where each time you use a template, you make a copy of it (USAGE_TYPE=T); or it can be used only one time (USAGE_TYPE=O).Images and OLE Objects cannot be used as templates.
FND_ATTACHED_DOCUMENTS:
FND_ATTACHED_DOCUMENTS stores information relating a document to an application entity. For example, a record may link a document to a sales order or an item. Each row contains foreign keys to FND_DOCUMENTS and FND_DOCUMENT_ENTITIES. There is also a flag to indicate whether or not an attachment was created automatically.
FND_DOCUMENTS_TL:
FND_DOCUMENTS_TL stores translated information about the documents in FND_DOCUMENTS. Each row includes the document identifier, the language the row is translated to, the description of the document, the file in which the image is stored, and an identifier (MEDIA_ID) of the sub-table in which the document is saved (FND_DOCUMENTS_SHORT_TEXT, FND_DOCUMENTS_LONG_TEXT, or FND_DOCUMENTS_LONG_RAW).
FND_DOCUMENT_DATATYPES:
FND_DOCUMENT_DATATYPES stores the document datatypes that are supported. Initial values are: short text, long text, image, and OLE Object (DATATYPE_ID=1, 2, 3, or 4). Customers can add datatypes to handle documents stored outside of Oracle and use non-native Forms applications to view/edit their documents. The table uses a “duplicate record” model for handling multi-lingual needs. That is, for each category there will be one record with the same CATEGORY_ID and CATEGORY_NAME for each language.
FND_DOCUMENT_CATEGORIES:
FND_DOCUMENT_CATEGORIES stores information about the categories in which documents are classified. For example, documents may be considered “Bill of Material Comments”, “WIP Job Comments”, etc. Document categories are used to provide a measure of security on documents. Each form that enables the attachment feature lists which categories of documents can be viewed in the form. This table uses a “duplicate record” model for handling multi-lingual needs.
FND_DOCUMENTS_LONG_TEXT:
FND_DOCUMENTS_LONG_TEXT stores information about long text documents.
FND_DOCUMENTS_SHORT_TEXT:
FND_DOCUMENTS_SHORT_TEXT stores information about short text documents.
FND_DOCUMENTS_LONG_RAW:
FND_DOCUMENTS_LONG_RAW stores images and OLE Objects, such as Word Documents and Excel spreadsheets, in the database.
FND_DOCUMENT_ENTITIES:
FND_DOCUMENT_ENTITIES lists each entity to which attachments can be linked. For example, attachments can be linked to Items, Sales Orders, etc. Since the table uses a “duplicate record” model for handling multi-lingual needs, for each document entity there will be one record with the same DOCUMENT_ENTITY_ID and DATA_OBJECT_CODE for each language.
Queries:
1] To find all Long Text attachments:
SELECT
FAD.SEQ_NUM “Seq Number”,
FDAT.USER_NAME “Data Type”,
FDCT.USER_NAME “Category User Name”,
FAD.ATTACHED_DOCUMENT_ID “Attached Document Id”,
FDET.USER_ENTITY_NAME “User Entity”,
FD.DOCUMENT_ID “Document Id”,
FAD.ENTITY_NAME “Entity Name”,
FD.MEDIA_ID “Media Id”,
FD.URL “Url”,
FDT.TITLE “Title”,
FDLT.LONG_TEXT “Attachment Text”
FROM
FND_DOCUMENT_DATATYPES FDAT,
FND_DOCUMENT_ENTITIES_TL FDET,
FND_DOCUMENTS_TL FDT,
FND_DOCUMENTS FD,
FND_DOCUMENT_CATEGORIES_TL FDCT,
FND_ATTACHED_DOCUMENTS FAD,
FND_DOCUMENTS_LONG_TEXT FDLT
WHERE
FD.DOCUMENT_ID = FAD.DOCUMENT_ID
AND FDT.DOCUMENT_ID = FD.DOCUMENT_ID
AND FDCT.CATEGORY_ID = FD.CATEGORY_ID
AND FD.DATATYPE_ID = FDAT.DATATYPE_ID
AND FAD.ENTITY_NAME = FDET.DATA_OBJECT_CODE
AND FDLT.MEDIA_ID = FD.MEDIA_ID
AND FDAT.NAME = ‘LONG_TEXT’;
2] To find all Short Text attachments:
SELECT
FAD.SEQ_NUM “Seq Number”,
FDAT.USER_NAME “Data Type”,
FDCT.USER_NAME “Category User Name”,
FAD.ATTACHED_DOCUMENT_ID “Attached Document Id”,
FDET.USER_ENTITY_NAME “User Entity”,
FD.DOCUMENT_ID “Document Id”,
FAD.ENTITY_NAME “Entity Name”,
FD.MEDIA_ID “Media Id”,
FD.URL “Url”,
FDT.TITLE “Title”,
FDST.SHORT_TEXT “Attachment Text”
FROM
FND_DOCUMENT_DATATYPES FDAT,
FND_DOCUMENT_ENTITIES_TL FDET,
FND_DOCUMENTS_TL FDT,
FND_DOCUMENTS FD,
FND_DOCUMENT_CATEGORIES_TL FDCT,
FND_ATTACHED_DOCUMENTS FAD,
FND_DOCUMENTS_SHORT_TEXT FDST
WHERE
FD.DOCUMENT_ID = FAD.DOCUMENT_ID
AND FDT.DOCUMENT_ID = FD.DOCUMENT_ID
AND FDCT.CATEGORY_ID = FD.CATEGORY_ID
AND FD.DATATYPE_ID = FDAT.DATATYPE_ID
AND FAD.ENTITY_NAME = FDET.DATA_OBJECT_CODE
AND FDST.MEDIA_ID = FD.MEDIA_ID
AND FDAT.NAME = ‘SHORT_TEXT’;
Attachment upload through API:
Attachments can also be uploaded through an oracle provided API called FND_ATTACHED_DOCUMENTS_PKG.
It consist of three procedures
1) Insert Row
2) Update Row
3) Lock Row
Names of these procedures are self explanatory. insert row is used to insert a new row for attachment data, update row is used to update existing row for a particular row and Lock Row is used to lock a existing row for further modification.
FND_ATTACHED_DOCUMENTS_PKG is not an API. It's an internal Oracle database package and not supported for external use.
Hi Bruce,
Its an API, which is internally used in oracle forms. All API are registered as database package only. Refer below link for more info.
https://docs.oracle.com/cd/E26401_01/doc.122/e22961/T302934T462356.htm
For any custom application, if we wants to use standard oracle attachments functionality we can call this API from backend.
Thanks
Velmurugan
Hi There,
This is the closest blog post so far I have found that seems to reference what I’m trying to do. I have a client running Oracle ERP ebusiness suite (they can’t tell me the version, I think version 12 was alluded to) running on 10g. I need to insert an attachment / content item with some metadata (typically a .pdf file and about 10 fields – client name, date, etc), from a c# application.
The best I can see is that the FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW call should be used (maybe ?!), but I can’t make head or tail of how to call this / what parameters are needed / where I pass the actual attachment in, etc.
Please note that I’m not an Oracle developer (I last used oracle 6 years ago via an adodb adapter), I don’t know the ERP system at all, and can’t seem to get any assitance from the so-called ERP resources at the client (they actually seem to know less than I do – if that’s possible).
Please help
Thanks
Robert
Hi Robert,
I’m not familiar with C# Applications, but you can upload the file in Oracle EBS using below script by passing your filename,description,file id (Unique id to pass l_pk1_value).
For Storing any additional columns like client name, date , address and so on in some custom table. You can link the two tables with l_document_id, l_attached_document_id to identify the additional information’s.
Below sample code with help you to insert pdf document to server path.
–Create a Server Directories in Oracle
–This will be done by DBA mostly
–Physical directory must be created inorder to save the files into server
CREATE OR REPLACE DIRECTORY SALE_INVOICE_PATH AS '/files/SALE_INVOICE/';SET SERVEROUTPUT ON;DECLARE
l_rowid ROWID;
l_attached_document_id NUMBER;
l_document_id NUMBER;
l_media_id NUMBER;
l_category_id number;
l_pk1_value fnd_attached_documents.pk1_value%type:= 999999; --Primary Key information that uniquely identifies the information
l_description fnd_documents_tl.description%type:= 'Customer Invoice Attachment';
l_filename VARCHAR2(240) := 'Sales_Invoice_14Nov2016.pdf';
l_file_path varchar2(240) := 'SALE_INVOICE_PATH'; --Server Directory Path for upload files
l_seq_num NUMBER;
l_blob_data BLOB;
l_blob BLOB;
l_bfile BFILE;
l_byte NUMBER;
l_fnd_user_id NUMBER;
l_short_datatype_id NUMBER;
x_blob BLOB;
fils BFILE;
blob_length integer;
l_entity_name varchar2(100) := 'CUSTOM_LOAD'; --Must be defined before or use existing ones. Table: FND_DOCUMENT_ENTITIES
l_category_name VARCHAR2(100) := 'Other'; --Must be defined before or use existing ones.
BEGIN
--Enter USER_ID,RESP_ID,RESP_APPL_ID
fnd_global.apps_initialize (&USER_ID, &RESP_ID, &RESP_APPL_ID);
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 AND entity_name = l_entity_name;
-- Select User_id
SELECT user_id
INTO l_fnd_user_id
from apps.fnd_user
WHERE user_name = 'OPERATIONS'; --Username who will be uploading file.
-- Get Data type id for Short Text types of attachments
SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes
WHERE NAME = 'FILE';
-- Select Category id for Attachments
SELECT category_id
INTO l_category_id
FROM apps.fnd_document_categories_vl
WHERE USER_NAME = l_category_name;
-- Select nexvalues of document id, attached document id and
SELECT apps.fnd_documents_s.NEXTVAL,
apps.fnd_attached_documents_s.NEXTVAL
into l_document_id,l_attached_document_id
FROM DUAL;
SELECT MAX (file_id) + 1
INTO l_media_id
FROM fnd_lobs;
fils := BFILENAME (l_file_path, l_filename);
-- Obtain the size of the blob file
DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);
blob_length := DBMS_LOB.getlength (fils);
DBMS_LOB.fileclose (fils);
-- 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 x_blob.
INSERT INTO fnd_lobs
(file_id, file_name, file_content_type, upload_date,
expiration_date, program_name, program_tag, file_data,
LANGUAGE, oracle_charset, file_format
)
VALUES (l_media_id, l_filename, 'application/pdf',--'text/plain',
SYSDATE, NULL, 'FNDATTCH', NULL, EMPTY_BLOB (), --l_blob_data,
'US', 'UTF8', 'binary'
)
RETURNING file_data
INTO x_blob;
-- Load the file into the database as a BLOB
DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (x_blob, fils, blob_length);
-- Close handles to blob and file
DBMS_LOB.CLOSE (x_blob);
DBMS_LOB.CLOSE (fils);
DBMS_OUTPUT.put_line ('FND_LOBS File Id Created is ' || l_media_id);
COMMIT;
-- This package allows user to share file across multiple orgs or restrict to single org
fnd_documents_pkg.insert_row
(x_rowid => l_rowid,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_datatype_id => l_short_datatype_id,
X_security_id => 21, --Security ID defined in your Attchments, Usaully SOB ID/ORG_ID
x_publish_flag => 'N', --This flag allow the file to share across multiple organization
x_category_id => l_category_id,
x_security_type => 1,
x_usage_type => 'S',
x_language => 'US',
x_description => l_description,
x_file_name => l_filename,
x_media_id => l_media_id
);
commit;
-- Description informations will be stored in below table based on languages.
fnd_documents_pkg.insert_tl_row
(x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_language => 'US',
x_description => l_description
);
commit;
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 => l_fnd_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_seq_num => l_seq_num,
x_entity_name => l_entity_name,
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 => 6,
x_category_id => l_category_id,
x_security_type => 1,
X_security_id => 21, --Security ID defined in your Attchments, Usaully SOB ID/ORG_ID
x_publish_flag => 'Y',
x_language => 'US',
x_description => l_description,
x_file_name => l_filename,
x_media_id => l_media_id
);
COMMIT;
DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);
end;
/
In case anything, reach me.
Good Day.
Thanks,
Velmurugan
Hi ,
I am trying to convert Sales Order Attachments from One R12 oracle system to another R12 oracle system.The document types that I have is short_text,long_text and FILE.How will I locate the exact path of file in the database server?
Hi Swatie,
you can find the file name and data in FND_LOBS table. I’m not sure you can find exact path in DB table. If you want to get the exact path of your file, you can link dba_directories and fnd_lobs to generate a link.
Below script will help you to get the file from DB, then you can send this file to another system by reading the file using UTL_FILE.
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
l_blob_fnme VARCHAR2(200);
BEGIN
SELECT file_name, file_data
INTO l_blob_fnme, l_blob
FROM FND_LOBS
WHERE FILE_ID = 5431524;
l_blob_len := DBMS_LOB.getlength(l_blob);
— Open the target file.
l_file := UTL_FILE.fopen(‘/dev/tmp’,l_blob_fnme, ‘A’, 32767);
— Loop through the BLOB and write to file
WHILE l_pos < l_blob_len LOOP DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer); UTL_FILE.put_raw(l_file, l_buffer, TRUE); l_pos := l_pos + l_amount; END LOOP; -- Close the file. UTL_FILE.fclose(l_file); EXCEPTION WHEN OTHERS THEN -- Close the file if something goes wrong. IF UTL_FILE.is_open(l_file) THEN UTL_FILE.fclose(l_file); END IF; RAISE; END; / Thanks
Hi experts , What is the table name where short text and long text are stored in Oracle apps fusion apps ? thanks
Hi there
I am trying to add an “Add Attachment” option on My Employee Self Service –> Employee Declarations Menu –> Declaration of Medical Aid, on a Flex: Descriptive Flex … for employees to upload documents.
Is this possible?
Thanks
Rene
Hi API completed successfully and data is pushed into the underline tables but not reflecting in asset attachments.