Thursday, September 4, 2008

Attachments in Oracle

Attachments are used to link data such as images and word processing documents to your application data. To link non–structured data such as images, word processing documents, spreadsheets, web pages, or video to more structured application data.


fig(a)

The fig(a) shows the toolbar Attachment icon if it is enabled it will allow you attach data to a particular record.

The fig(b) shows the Attachment window. The Attachments window can be used to view or delete existing attachments, and create new attachments.


fig(b)

As we ca see in the figure there are many fields which need to be filled while creating an attachment.

Category

The category controls which forms can access the document. Each form that enables Attachments must list which document categories it can access. A “Miscellaneous” category will be assigned to each form registered for Attachments to facilitate
“cross–form” visibility of Attachments.

Datatype
An attached document can be:
1. Document Reference: A reference to any type of document stored in a database that is accessed through a Document Management system.
2. Short Text: Text stored in the database containing less than 2000 characters.
3. Long Text: Text stored in the database containing 2000 characters or more.
4. File: A file that requires other server applications to view, such as Microsoft Word or Microsoft Excel, image files such as .JPG files, or other types of files. When you attach or create a File type document, the document is loaded into the database. When a File document is selected, the lower half of the Attachments window displays an ”Open Document” button that invokes a web browser and passes the file to the browser.
5. Web Page: A URL reference to a web page that you can view with your web browser. You must define your web browser in the profile option Web Browser to view a web page attachment.

File or URL

If the document is a file, specify the location of the document. The document will then be loaded into the database. If the document is a web page, specify the web page URL, such as http://www.google.co.in/.

May be Changed

The May be Changed checkbox indicates if you are able to edit the attachment.

Document Block

The document block is the section of the window below the multi–line attachment block. The document block displays the current document in the attachments block, or it displays an Open Document button that opens your document for viewing with a browser.

Adding an Attachment

In order to add an attachment you can either click on the toolbar icon shown in fig(a) or you could go to View->Attachments.


fig(c)

To attach a new document to an Oracle Applications record:

1. In an application window, query or enter a data record.
2. Choose the Attachment toolbar icon. The Attachments window opens.
3. Enter a category, a description, and a data type.
4. Type in your document text, or specify a file name or web page URL.
5. Save your work.

To attach an existing document to an Oracle Applications record:

1. In an application window, query or enter a data record.
2. Choose the Attachment toolbar icon. The Attachments window opens.
3. Choose the Document Catalog... button. The Document Catalog window opens.
4. Query an existing document.
5. Check one or more documents.
6. Choose the Attach (number of documents) button.
7. Save your work.

Viewing an Attached Document

1. Query a record.
2. Press the Attachment toolbar icon.
3. Move your cursor to the document you want to view. The attachment appears in the document block or you must press the Open Document button to see the document in a browser.

Deleting an Attachment

When you delete an attachment, you merely remove the association between a record and a document. The document itself is not removed from your file system or database. But the option of removing both the record and the attachment is also given.


fig(d)

To delete a record’s attachment:
1. In an application window, query a data record.
2. Choose the Attachment toolbar icon.
3. Move your cursor to the document you want to delete.
4. Choose Delete from the Edit menu.
5. Save your work.

Copying an Attachment from Another Record
Oracle Applications keeps a catalog of documents that have been attached to applications data records so far; therefore, you can take advantage of this catalog if you want to attach an existing document to another data record.


fig(e)

Steps to attach an existing document:

Document Catalog -> Find -> Attach 1

To copy an attachment from another record:
1. In an application window, query the record that you want to attach a document to.
( Don’t query the record that already has the attachment.)
2. Choose the Attachments toolbar icon.
3. Choose Document Catalog....
4. Enter query criteria in the Attached To: field for the application object you want to copy attachments from.
5. Choose Find.
6. Check one or more documents.
7. Choose the Attach (number of documents) button.
8. Save your work.

Uploading and Downloading Attachment Files
Steps to follow:
Select data-type file -> Browse -> OK/Cancel


fig(f)

To attach an existing file to an Oracle Applications record:
1. In an application window, query or enter a data record.
2. Choose the Attachment toolbar icon. The Attachments window opens.
3. Use the List of Values to choose a Category.
4. Optionally enter a Description.
5. Use the List of Values to choose a Data Type.
6. Use the Tab key to open the Upload window in a separate browser window.
7. You can either enter the path for the file you want to attach or use the Browse button to choose a file.
8. Choose OK and follow the instructions in the browser window.
9. Click on Yes in the Decision window as seen in the figure fig(g)


fig(g)

To open an uploaded attachment:

Choose the Open Document ... button

Attachments as seen from backend ( In Order-Management)

When you attach a document to an Order it can be done so at the header level or the line level. The tables/views which are related are

FND_ATTACHED_DOCS_FORM_VL
This is a view which has all the details pertaining to an attached document. It gets populated by the following tables :

FND_DOCUMENT_DATATYPES,
FND_DOCUMENT_ENTITIES_TL,
FND_DOCUMENTS_TL,
FND_DOCUMENTS,
FND_DOCUMENT_CATEGORIES_TL,
FND_DOC_CATEGORY_USAGES,
FND_ATTACHMENT_FUNCTIONS,

The important columns of the table are:
1. SEQ_NUM: Gives the sequence of the attachment it follows like 10,20,30..
2. CATEGORY_DESCRIPTION: Gives the description of the category.
3. DOCUMENT_DESCRIPTION: Gives the unique description of the attachment.
4. FILE_NAME: Name of the attached file.
5. ATTACHED_DOCUMENT_ID: Identifier.
6. DOCUMENT_ID: Identifier.
7. ENTITY_NAME: 'OE_ORDER_HEADERS' or 'OE_ORDER_LINES'
6. PK1_VALUE: Header_id or Line_id depending on if it is a header level or line level attachment.
7. CATEGORY_ID: Unique identifier sourced from the FND_DOCUMENT_CATEGORIES_S sequence
8. FUNCTION_NAME: Name of the function used while attaching
9. FUNCTION_TYPE: Foreign key to either FND_FORM_FUNCTIONS of FND_FORM, depending on the value of FUNCTION_TYPE.

FND_ATTACHED_DOCUMENTS
This table stores information relating a document to an application entity. Its important columns include:
ATTACHED_DOCUMENT_ID: Identifier.
DOCUMENT_ID: Document identifier
PK1_VALUE: Header_id or Line_id depending on if it is a header level or line level
attachment.
ENTITY_NAME: 'OE_ORDER_HEADERS' or 'OE_ORDER_LINES'

FND_ATTACHMENT_FUNCTIONS
This table stores information about those forms and form functions for which the attach-ment feature is enabled.
ATTACHMENT_FUNCTION_ID: Attachment function identifier
FUNCTION_NAME: Name of the function used while attaching
FUNCTION_TYPE: Foreign key to either FND_FORM_FUNCTIONS of FND_FORM,
depending on the value of FUNCTION_TYPE.

FND_DOCUMENTS
This table stores language-independent information about a document
DOCUMENT_ID : Document identifier
CATEGORY_ID: Unique identifier sourced from the FND_DOCUMENT_CATEGORIES_S
sequence

FND_LOBS:
FND_LOBS stores information about all LOBs managed by the Generic File Manager(GFM).Each row includes the file identifier, name, content-type, and actual data.
FILE_ID: Identifier that uniquely identifies the file
FILE_NAME: User's name for the file as provided during the uploading process
FILE_CONTENT_TYPE:
FILE_DATA: The uploaded data itself, which is a binary LOB

OE_ATCHMT_UTIL
This is an API which can be used to add, copy and delete attachments it has procedures

Add_Attachment: Can be used to attach a new attachment to an order

It’s parameters are:
P_API_VERSION IN (Mandatory)
P_ENTITY_CODE IN (Mandatory)
P_ENTITY_ID IN (Mandatory)
P_DOCUMENT_DESC IN default null,
P_DOCUMENT_TEXT IN default null,
P_CATEGORY_ID IN default null,
P_DOCUMENT_ID IN default null,
X_ATTACHMENT_ID OUT
X_RETURN_STATUS OUT
X_MSG_COUNT OUT
X_MSG_DATA OUT

Copy_Attachments : Can be used to attach an existing attachment to an
Order.

It’s parameters are:
P_ENTITY_CODE IN (Mandatory)
P_FROM_ENTITY_ID IN (Mandatory)
P_TO_ENTITY_ID IN (Mandatory)
P_MANUAL_ATTACHMENTS_ONLY IN default 'N'
X_RETURN_STATUS OUT


Delete_Attachments: Can be used to delete an attachment of an order

It’s parameters are:
P_ENTITY_CODE IN (Mandatory)
P_ENTITY_ID IN (Mandatory)
X_RETURN_STATUS OUT

Apply_Automatic_Attachments :

It’s parameters are:
P_INIT_MSG_LIST IN default fnd_api.g_false
P_ENTITY_CODE IN (Mandatory)
P_ENTITY_ID IN (Mandatory)
P_IS_USER_ACTION IN default 'Y'
X_ATTACHMENT_COUNT OUT
X_RETURN_STATUS OUT
X_MSG_COUNT OUT
X_MSG_DATA OUT

No comments: