Attachment in Oracle Application


Attachment in Oracle Application

What is 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.

 

8 Responses to Attachment in Oracle Application

  1. Ed Hayes says:

    How can I search for attachments with specific content in the Long Text memo?

    • Venu Sreeramoju says:

      /*——————————————————————————————–*
      * Script Description :- Single Insert Script to Attachments *
      * Author :- Venu Sreeramoju *
      * Date :- 22 October 2011 *
      * —————————————————————————————— *
      * *
      * Pre-Requirements *
      * *
      * 1.Create a Custom Directory and Grant Permissions *
      * 2.Place the Files in this Directory as Below *
      * *
      * CREATE OR REPLACE DIRECTORY Apps AS ‘/usr/tmp/venu’; *
      * GRANT READ ON DIRECTORY Apps TO PUBLIC; *
      * *
      *———————————————————————————————*/

      declare
      l_rowid rowid;
      l_attached_document_id number;
      l_document_id number;
      l_media_id number;
      l_file_id number;
      l_category_id number := 1; — Misc
      l_pk1_value fnd_attached_documents.pk1_value%TYPE := ‘204’;
      l_description fnd_documents_tl.description%TYPE := ‘Testing Attachment File’;
      l_filename fnd_documents_tl.file_name%TYPE := ‘Testing pdf.pdf’;
      l_seq_num number:=26;
      l_blob BLOB;
      l_bfile bfile;
      l_byte number;
      x_return_status varchar2(2000);
      x_msg_count number(10);
      x_msg_data varchar2(1000);
      blob_length number;
      x_blob BLOB;
      –1008030 Venu Sreeramoju
      begin
      fnd_global.apps_initialize(1008030,50583,401);
      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 = ‘MTL_SYSTEM_ITEMS’;

      SELECT fnd_lobs_s.NEXTVAL
      INTO l_media_id
      FROM DUAL;

      l_bfile := bfilename(‘SCMR’,’Testing pdf.pdf’);—Bfile Checks the File existance at directory

      DBMS_LOB.fileopen (l_bfile, DBMS_LOB.file_readonly);
      blob_length := DBMS_LOB.getlength (l_bfile);
      DBMS_LOB.fileclose (l_bfile);

      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’, SYSDATE,
      NULL, ‘FNDATTCH’, NULL, empty_blob(),
      ‘US’,’UTF8′, ‘BINARY’
      )returning file_data into x_blob;


      DBMS_LOB.OPEN (l_bfile, DBMS_LOB.lob_readonly);
      DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);
      DBMS_LOB.loadfromfile (x_blob, l_bfile, blob_length);
      DBMS_LOB.CLOSE (x_blob);
      DBMS_LOB.CLOSE (l_bfile);
      COMMIT;

      DBMS_OUTPUT.put_line (‘File Successfully Inserted into Table as BLOB’);

      DBMS_LOB.CREATETEMPORARY(x_blob,true);
      commit;
      if DBMS_LOB.FILEEXISTS(l_bfile) = 1 then
      dbms_output.put_line( ‘ File Exists!’);

      else
      dbms_output.put_line( ‘Not Exists!’);
      end if;

      fnd_documents_pkg.insert_row
      ( X_ROWID => l_rowid
      , X_DOCUMENT_ID => l_document_id
      , X_CREATION_DATE => sysdate
      , X_CREATED_BY => 1008030
      , X_LAST_UPDATE_DATE => sysdate
      , X_LAST_UPDATED_BY => 1008030
      , X_LAST_UPDATE_LOGIN => 1008030
      , X_DATATYPE_ID => 6 — FILE
      , 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
      );
      commit;
      —dbms_output.put_line (‘document id is l_document_id ‘||l_document_id);
      fnd_documents_pkg.insert_tl_row
      ( X_DOCUMENT_ID => l_document_id
      , X_CREATION_DATE => sysdate
      , X_CREATED_BY => 1008030
      , X_LAST_UPDATE_DATE => sysdate
      , X_LAST_UPDATED_BY => 1008030
      , X_LAST_UPDATE_LOGIN => 1008030
      , X_LANGUAGE => ‘US’
      , X_DESCRIPTION => l_description
      , X_FILE_NAME => l_filename
      , X_MEDIA_ID => l_media_id
      );
      commit;
      —dbms_output.put_line (‘attachment_document id is l_attached_document_id ‘||l_attached_document_id);
      —l_document_id :=l_document_id-1;
      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 => 1008030
      , X_LAST_UPDATE_DATE => sysdate
      , X_LAST_UPDATED_BY => 1008030
      , X_LAST_UPDATE_LOGIN => 1008030
      , X_SEQ_NUM => l_seq_num
      , X_ENTITY_NAME => ‘MTL_SYSTEM_ITEMS’
      , X_COLUMN1 => null
      , X_PK1_VALUE => l_pk1_value
      , X_PK2_VALUE => 12251—–12164–carbody_d—11991 —car_433
      , 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 => 2
      , 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 (‘attachment_document id is l_attached_document_id ‘||l_attached_document_id);
      dbms_output.put_line (‘document id is l_document_id ‘||l_document_id);
      dbms_output.put_line (‘ l_media_id ‘||l_media_id);
      EXCEPTION
      WHEN OTHERS
      THEN
      dbms_output.put_line(‘Failed ‘||sqlerrm);
      commit;
      end;

      ——————
      /
      select * from all_directories;

  2. varaprasad says:

    Hi,
    Thanks for your information………….
    CAN I HAVE THE DESCRIPTION FOR
    HOW TO LOAD THE XML FILE WITHOUT RDF FILE

    • Venu Sreeramoju says:

      U want to load the XML file/XML data into for the XMLP Report with out using the RDF?
      we had some of the XML function to make the output as XML File

      Let me Give the Example

      SELECT XMLELEMENT(“XML_Examples”,
      XMLAGG(
      XMLELEMENT(“User_Details”,
      XMLFOREST(
      User_name as “Name”,
      user_id as “Id”
      , Creation_date as “Creation_date”,
      Start_date as “Start_date”
      )
      )
      )
      ) AS XMP_Output_Examples
      FROM fnd_user
      WHERE rownum<1000;

      Save the output to Filename.xml

  3. Vijay says:

    Hi, This info is great. I have small issue. we have 3 images for an Inventory Item, so I have records in fnd_attached_documents and fnd_lobs tables. Now user have deleted couple of items from application, but still I see 3 records in these tables. Not sure how to eliminate from the query. Start_date_active and end_date_active fields are empty for all 3. And also Publish_flag is ‘Y’ for all. Appreciate if you can advice,
    —————-
    SELECT fad.*
    FROM fnd_attached_documents fad, fnd_documents fd, fnd_lobs fl
    WHERE fad.entity_name = ‘MTL_SYSTEM_ITEMS’
    AND fad.document_id = fd.document_id
    AND fd.media_id=fl.file_id
    AND fd.file_name = fl.file_name
    AND fad.pk2_value = 135879
    ———————–

    Thanks, Vijay

    • Hi Vijay,

      I think there are some issues with Orphaned records in FND_LOBS table with instance 12.0.6 or prior to that. Now although the user has deleted the Item, the attachments related to the Item may still there in those tables. Oracle has provided some concurrent programs and scripts to purge obsolete data from those tables. You can check out those details.

      Here are some related Metalink Notes:

      1] Orphaned records in FND_LOBS table when uploading attachments using FNDATTACH form [ID 963222.1]
      2] How To Manage, Reduce, and/or Purged The FND_LOBS Table? [ID 1288149.1]
      3] Useful SQL Statements to Check What Makes FND_LOBS Grow? [ID 1245974.1]

      Hope it helps.

      Thanks
      Dibyajyoti

  4. rohit says:

    Hi,
    Can you tell me how to find order number who had attachments and type of attachment?

    • rohit says:

      SELECT FDST.MEDIA_ID,
      OOH.ORDER_NUMBER “Order Number”,
      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_ATTACHED_DOCS_FORM_VL FD,
      FND_DOCUMENT_CATEGORIES_TL FDCT,
      FND_ATTACHED_DOCUMENTS FAD,
      FND_DOCUMENTS_SHORT_TEXT FDST,
      OE_ORDER_LINES_ALL OOL,
      OE_ORDER_HEADERS_ALL OOH
      WHERE FDT.MEDIA_ID = FD.MEDIA_ID
      AND FDT.MEDIA_ID = FDST.MEDIA_ID
      AND FD.DATATYPE_ID = FDAT.DATATYPE_ID
      AND FAD.ENTITY_NAME = FDET.DATA_OBJECT_CODE
      AND FDET.DATA_OBJECT_CODE = FD.ENTITY_NAME
      AND FDT.DOCUMENT_ID = FD.DOCUMENT_ID
      AND FDT.DOCUMENT_ID = FAD.DOCUMENT_ID
      AND FD.ATTACHED_DOCUMENT_ID = FAD.ATTACHED_DOCUMENT_ID
      AND FD.PK1_VALUE = OOH.HEADER_ID
      and FD.PK1_VALUE = FAD.PK1_VALUE
      –AND OOL.HEADER_ID = OOH.HEADER_ID
      AND FD.CATEGORY_ID = FAD.CATEGORY_ID
      AND FDCT.CATEGORY_ID = FAD.CATEGORY_ID
      and FDCT.CATEGORY_ID = FD.CATEGORY_ID
      and FD.CATEGORY_ID = FAD.CATEGORY_ID
      –and FDST.SHORT_TEXT like ‘test01’–just for checking
      GROUP BY OOH.ORDER_NUMBER ,
      FAD.SEQ_NUM ,
      FDAT.USER_NAME ,
      FDCT.USER_NAME ,
      FAD.ATTACHED_DOCUMENT_ID ,
      FDET.USER_ENTITY_NAME ,
      FD.DOCUMENT_ID ,
      FAD.ENTITY_NAME ,
      FD.MEDIA_ID,
      FDST.SHORT_TEXT,
      FDST.MEDIA_ID;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: