The Oracle E-Business Suite attachments feature 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.
Attachment information can flow through your entire application. For example, if you enable attachments for a part number, where users would attach images of the part, you can then enable attachments for all your other forms that refer to your part number. Users would then be able to see the image of the part wherever that part number occurs.
You can provide security to limit which attachments users can see from particular forms by assigning document categories to your form functions. Users then assign individual attachments to particular categories.
You can add the attachments feature to your application forms and functions without modifying form code, so long as your forms are built using Oracle E-Business Suite standards (starting with the Oracle E-Business Suite TEMPLATE form).
It is useful to specifically define certain terms that have special meaning within the context of the attachments feature.
A document is any object that provides information to support another object or action. Examples include images, word processing documents, spreadsheets, or text.
An entity is an object within Oracle E-Business Suite data, such as an item, an order, or an order line. The attachments feature must be enabled for an entity before users can link attachments to the entity.
In the context of attachments, an entity can be considered either a base entity or a related entity. A base entity is the main entity of the block. A related entity is an entity that is usually related to the block by a foreign-key relationship.
For example, suppose you have an Order Lines window that shows the contents of an Order_Lines block. The Order Lines entity would be considered the base entity of the Order_Lines block. If that block included a field called Product, the Product entity would be considered a related entity of the Order_Lines block. If you also had a Products window that shows the contents of the Products block, the Product entity would be considered the base entity of the Products block.
Important: The Orders/Order Lines/Products example used throughout this chapter is a generic example meant to illustrate attachments concepts. It is not to be confused with actual attachments setups used in Oracle E-Business Suite such as the attachments to purchase orders used in Oracle Purchasing. Those actual setups may differ considerably from our example.
A document associated with an entity is called an attachment.
A form or form function in your application cannot use attachments until the attachments feature is set up for that form or function; that is, it must be defined as an "attachment function" in the Attachment Functions window.
A document category is a label that users apply to individual attachments and documents. Document categories provide security by restricting the documents that can be viewed or added via a specific form or form function.
When you set up the attachments feature, you assign document categories to particular forms or form functions. When a user defines a document, the user assigns a category to the document. The attachments form can query only those documents that are assigned to a category to which the calling form or form function is associated. A "Miscellaneous" category is seeded to provide easy visibility of a document across forms.
Related Topics
Attachments for Forms or Form Functions
Planning to Add the Attachments Feature to Your Application
Setting Up the Attachments Feature for Your Form
When a user is using a block in a form where the attachments feature has been enabled, the attachments icon is enabled in the toolbar (empty paper clip). If the user clicks on the icon, the Attachments window opens. In the Attachments window, the user can either create a new attachment document or attach an existing document to the base entity of the block.
Depending on how attachments have been set up, if a document has already been attached to the entity, the icon in the toolbar indicates that an attachment is present (paper in paper clip). If the user clicks on the icon, the Attachments window opens and automatically queries the attachment. For a given form function, the user only sees attachments whose assigned categories are available for that form function.
For some setups of the attachment feature, the Attachments window automatically queries attachments for the base entity of the block. To see attachments that are attached to related entities, the user checks the Include Related Documents check box. The Attachments window then queries those attachments as well as the attachments for the base entity.
However, the attachments feature can be set up so that all attachments for both the base entity and related entities of the block can be seen initially (without the user checking the Related Documents check box). The user cannot modify or insert attachments for the related entities in either case.
When a user attaches a document to a record in a form, Oracle E-Business Suite stores information about the document, and the document itself (or its URL). Oracle E-Business Suite separately stores the attachment information that links the document to the record. Storing the linkage information separately allows users to attach the same document to multiple records.
The information that links the entity and the document is stored in an Oracle Application Object Library table, FND_ATTACHED_DOCUMENTS. That information includes the document ID and the entity name, combined with information from the record that uniquely identifies the instance of the entity (the entity record). For example, if you were to attach an image to a product in the products form, the attachment linkage information that would be stored would be the image document ID, the name of the products entity, and the primary key information that uniquely identifies the product (such as the product_ID). As you then move to other forms that show the product, those attachment functions for those forms would define, for the products entity, the primary key field as being the product_ID field (not also the order_ID field, for example). In this way, wherever the product entity appears in the application, the attached image would be available so long as the corresponding attachment functions were defined correctly).
The association between the form and the document is constructed as follows: the form is connected to a block, and the block is connected to an entity. Because the attachment linkage is stored with the entity name, combined with information from the record that uniquely identifies the instance of the entity (the entity record), the link between the form block and the document are derived at runtime through the entity.
Each document is associated with a category. Each attachment function is also associated with one or more categories, and at runtime, only documents whose categories are associated with the current attachment function (form) can be viewed or attached.
Note: For information on security features used with attachments, see "Security Configuration Mechanism in the Attachments Feature in Oracle E-Business Suite," My Oracle Support Document 1357849.1.
Related Topics
Attachments for Forms or Form Functions
Planning to Add the Attachments Feature to Your Application
Setting Up the Attachments Feature for Your Form
To enable the attachments feature in a form or form function, you need to define information in the Attachment Functions window.
Registration at the form or form function level allows users to define attachment data at the appropriate level. If you want all form functions for a given form to have the same attachment capabilities, you can define attachment data at the form level and eliminate the need to enter redundant data for each form function. On the other hand, if you need different attachment functionality in a form function, you can define the data at the form-function level.
For example, the Bill of Materials form may have two form functions: one is used by design engineers, and the other is used by production engineers. If you want to allow a design engineer access to a broader range of document categories than production engineers, you will need to set up attachments separately for each of the two form functions. If you want attachments to act the same in both form functions you would define attachments data at the form level.
The Attachments logic first checks to see if the form function has attachment data defined. If it does, it uses that definition to run the attachments system. If no data exists for the form function, the form-level is checked for defined attachment data. If attachments data exists at the form-level, it is used to run the attachments system. If attachment data exists, the form caches information in three record groups that control how the attachment icon is displayed, and what functionality is available when the attachments form is invoked. If no attachment data is declared, the attachment icon is disabled and nothing will happen if the user clicks on the toolbar icon.
Registration of form or form function requires information at the following levels: Form, Block, Entity, and Category.
Related Topics
Planning to Add the Attachments Feature to Your Application
Setting Up the Attachments Feature for Your Form
Oracle Application Object Library provides database views of attachment document information you need when you want to report on attachment information for an entity. To provide security for reporting on attachments, we recommend that you register your concurrent program as an attachment function and associate one or more document categories with your concurrent program. You do not need to provide any block or entity information when you register your concurrent program as an attachment function, although you must define your concurrent program using the Concurrent Program window before registering it as an attachment function.
You must plan your attachments feature carefully before attempting to set it up using the definition forms. The order in which you use the definition forms is not the same order in which you plan your attachments feature.
Warning: You must plan and set up your attachments feature carefully. Once you have attachments for your entities, you should not modify the attachment function setup except to add categories or entities. Other modifications could cause existing attachments to become invalid.
This planning task is meant to give you a high-level, skeletal structure that will help you define your attachments feature correctly. This task is not meant to give you a complete document containing every field value that you will need to define in the attachments setup forms.
Determine which entities in your application require attachments (such as items, purchase orders, purchase order lines, and so on).
For each entity, determine the main table that holds the entity. Note that a table can contain more than one entity.
Determine the columns in that table that make up the primary key for that entity. When you set up the attachments feature for your form, you will need to specify the form fields that correspond to these primary key columns for your entity.
For example, for an Order Lines entity, the primary key columns could be ORDER_ID and ORDER_LINE_NUMBER. For a Product entity, the primary key column could be PRODUCT_ID. Then, when a user of the Order Lines window queries the attachments for an order line, the user would see the correct attachments for that order line and for the product that the order line references.
Determine which forms or form functions should show attachments for those entities.
For each form that requires attachments, determine whether you want to enable attachments for a specific form function or for all occurrences of the form. See: Attachments for Forms or Form Functions
For the entire form or function, identify what attachment categories you want to use.
For each form (function), determine the block/entity correspondence. That is, determine which entities should have attachments and in which block(s) those entities should have attachments.
For example, for the Orders function shown in , you may want to use attachments for the Order (entity), the Order Lines (entity) on the Orders block. You may also want to use the Order Lines entity and the Product entity on the Order_Lines block. For each entity, you can attach pictures, notes, and so on.
For each block/entity combination, determine whether the entity is a base entity or a related entity. Only one entity per block can be a base entity.
In our example, the Order Lines entity is the base entity and the Product entity is the related entity for the Order_Lines block. Users would be able to view or add new attachments for the Order Lines entity, but they would only be able to view attachments for the Products entity on the Order_Lines block (users would add Product attachments using the attachments feature on the Products form, assuming that form is set up for attachments).
Users can query and see attachments for more than one entity in a given form block; however, users may only insert or update attachments for the base entity of the block. A block can have only one base entity.
For example, for the Lines block shown in , the Order Lines entity is the base entity of the block, and the Product entity is not. In this case, users would be able to create or update attachments for the Order Lines entity, but they would only be able to view attachments for the Products entity.
You can set up the attachments feature for any form in your application where you want the user to be able to attach documents, images, notes, document URLs, files, or other information not otherwise captured in the form.
To set up the attachments feature for your form, perform the following;
Plan your attachments feature for your application. See: Planning to Add the Attachments Feature to Your Application
Define your document entities using the Document Entities window.
Define your document categories using the Document Categories window.
Define your attachment functions using the Attachment Functions window.
We recommend that you go through the Attachment Functions window and its related windows to familiarize yourself with their fields and requirements before attempting to define your attachment functions.
Related Topics
Attachments for Forms or Form Functions
Planning to Add the Attachments Feature to Your Application
Use this window to register attachment entities. A single table may contain multiple entities. An entity needs to be registered only once, even though attachments to it may be viewed in multiple places.
You must plan your attachments feature thoroughly before using this form. See: Planning to Add the Attachments Feature to Your Application.
Enter the name of the main table that contains the entity. For example, if you have a Products entity that is contained in the DEM_PRODUCTS table, and that appears as a foreign key column in several other tables, you would enter DEM_PRODUCTS.
Enter a name that uniquely identifies the entity internally. Typically this name is the same as the table name, such as DEM_PRODUCTS. If there is more than one entity in the table, append distinguishing information to the table name, as in DEM_PRODUCTS_COMPUTER. Use all uppercase letters and underscores. Do not use spaces or special characters other than underscores.
The entity name users see in the Attachments form when the form displays the list of attachments to an entity. Enter an entity name meaningful to an end user attaching or viewing a document attached to this entity.
The user entity prompt. The prompt is required, but is not currently used by the attachments feature. If you are building reports based on your attachments, you may use this column to store a prompt used in your reports.
The application that owns the entity (or that owns the entity table).
Note that if you are defining custom attachments functionality that is based on Oracle E-Business Suite tables and forms, you should define your custom entity using a custom application name instead of the Oracle E-Business Suite product name. This will help to preserve your custom entity upon upgrade.
Related Topics
Attachments for Forms or Form Functions
Planning to Add the Attachments Feature to Your Application
Setting Up the Attachments Feature for Your Form
Document categories provide security by restricting the documents that can be viewed or added via a specific form or form function. When a user defines a document, the user assigns a category to the document. The attachments form can only query documents that are assigned to a category with which the form or form function is associated.
Oracle E-Business Suite provides a "Miscellaneous" category that you can assign to your attachment function, so if you intend to use that category, you do not need to define any new categories in this form.
You must plan your attachments feature thoroughly before using this form. See: Planning to Add the Attachments Feature to Your Application
Enter a user-friendly name for the category. Users see this name in the Attachments window.
The default datatype is the initial value for a document that is created using the category. The user can override the default datatype.
The possible datatypes are:
File - File documents are external files such as Microsoft Word files, Microsoft Excel files, image files such as .JPG files, or other types of files. When File type documents are attached, they are 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. The web browser handles displaying the file as appropriate based on its filename extension.
Long Text - Text documents that are longer than 4000 bytes.
Short Text - Text documents that are no more than 4000 bytes in length.
Web Page - Web Page documents are attached as URLs in the format http://www.example.com (or the format www.example.com if your browsers can use that format). When a user selects a Web Page document to view, the lower half of the Attachments window displays an "Open Document" button that invokes a web browser and passes the URL to the browser.
The effective dates for the category.
This button brings up the Category Assignments window that you can use to view and/or enter the forms or form functions for which your category is available.
Related Topics
Attachments for Forms or Form Functions
Planning to Add the Attachments Feature to Your Application
Setting Up the Attachments Feature for Your Form
Use the Category Assignments window to view attachment functions that are currently using your category and/or to assign one or more previously-existing attachment functions to your category. You cannot assign to your category any form function that has not already been enabled as an attachment function.
Choose Form or Function.
Enter the name of a form or function (that has already been enabled for attachments) that you want to be able to use your category.
Check the Enabled check box if the category should be enabled for the form or function. If you uncheck the Enabled check box for a form or function, any existing attachments to that form or function that use this category will no longer be visible to the user using the Attachments window.
Related Topics
Attachments for Forms or Form Functions
Planning to Add the Attachments Feature to Your Application
Setting Up the Attachments Feature for Your Form
Use the Attachment Functions windows to set up the attachments feature for your form or form function. Before you use this form, you must:
Carefully plan your attachments feature. See: Planning to Add the Attachments Feature to Your Application.
Define your document entities using the Document Entities window.
Define your document categories using the Document Categories window.
We recommend that you go through the Attachment Functions window and its related windows to familiarize yourself with their fields and requirements before attempting to define your attachment functions.
Choose Form, Function, or Report. The function type determines the list of values for the Name field.
Use the list of values to choose the form, form function, or report for which you want to set up the attachment feature. For a function, this name is the internal name of the function, such as DEM_DEMXXEOR.
The user-friendly name of the form, function, or report will default in based on the function name chosen.
Optionally enter the name of the field or parameter in the form that should be used to obtain the session context for the title of the Attachments window. In general, the session context field holds the organization name for manufacturing applications or the set of books name for financial applications.
You must enter the Oracle Forms internal field name, not the displayed prompt. Use the syntax block.field. You must include the block name.
Check the enabled box if the attachment feature should be enabled for the form, function, or report.
This button brings up the Categories window that you can use to view and/or enter the document categories available to your form (function). You must assign at least one category to your attachment function.
This button brings up the Block Declaration window that you can use to enter the block declarations for your attachment function.
Related Topics
Attachments for Forms or Form Functions
Planning to Add the Attachments Feature to Your Application
Setting Up the Attachments Feature for Your Form
Use this window to view or assign document categories to your attachment function. Categories you assign to your function are available across all blocks in your form where attachments are enabled (that is, you cannot have a category available in one block of a form and not another block in the same form where both blocks have attachments enabled).
Enter the category you want to assign to this function. You must assign at least one category to your attachment function. Oracle E-Business Suite provides a "Miscellaneous" category that you can assign to your attachment function.
Check the enabled box if this category should be enabled for this function.
Related Topics
Attachments for Forms or Form Functions
Planning to Add the Attachments Feature to Your Application
Setting Up the Attachments Feature for Your Form
Information about blocks is required to determine whether the Attachments toolbar icon should be enabled. In addition, the various attributes associated with a block affect how the attachments form appears and behaves when it is invoked from a block.
If you are using this form to set up attachment categories for reports, you need not use the Blocks or Entities windows.
The Oracle Forms block name as entered in Form Builder. Enter only blocks for which you want to enable the attachment feature.
Choose either "Allow Change" or "Query Only". "Allow Change" means that the user can insert, update, or delete attachments when the attachment form is invoked from the block. "Query Only" means that the user can view, but not change, delete or create, attachments when the attachment form is invoked. If you select "Query Only" in this field, it applies to all attachments for all entities for your block. If you select "Allow Change", you can selectively restrict privileges for specific entities using the Entities window.
Choose Organization, Set of Books, Business Unit, or None, depending on how the form and its data is secured. Financial applications are typically secured by sets of books. Manufacturing applications are typically secured by organization, and Human Resources applications are typically secured by business unit ID.
When a document is defined, its security mechanism is defined as well. For example, you can specify that a document is secured by organization, and that it is owned by organization ABC. The attachment system will now only display this document when the attachments form is invoked by a form running in the context of the ABC organization.
To facilitate sharing of documents across security contexts (organization, set of books, business unit), a document can be defined as having "None" as its security type, or it can be defined as being "Shared." Defining a document with either of these attributes will allow the attachments form to display the document regardless of the security context of the form that invokes the attachments form.
If the attachment is secured by organization, enter the name of the context field in your form that holds the organization ID. Use the syntax block.field (for example, ITEMS.ORGANIZATION_ID). You must include the block name.
If the attachment is secured by the set of books, enter the name of the context field in your form that holds the set of books ID. Use the syntax block.field (for example, JOURNAL_ENTITIES.SET_OF_BOOKS_ID). You must include the block name.
If the attachment is secured by business unit, enter the name of the context field in your form that holds the business unit ID. Use the syntax block.field (for example, EMPLOYEE.ORGANIZATION_ID). You must include the block name.
You can set up your attachment function so that when a user opens the Attachments window, the title of the Attachments window displays up to three values from your form. These values can help the user identify the record to which the user is attaching the document. You can specify the names of up to three fields from which the attachments feature can derive these values. For example, for attachments to an order, you may want the title of the Attachments window to display the order number and the customer name, so you would specify the name of the field that holds the order number and the name of the field that holds the customer name.
Enter the name of the field that holds the context information to be used in the Attachments form title. Use the syntax block.field (for example, ORDERS.ORDER_ID). You must include the block name.
This button brings up the Entity Declaration window that you can use to enter the entity declarations for your attachment function.
Related Topics
Attachments for Forms or Form Functions
Planning to Add the Attachments Feature to Your Application
Setting Up the Attachments Feature for Your Form
Use the Entity Declaration window to list the entities for your block and to provide information about each entity. You must complete the Entity Declaration window again for each block you listed in the Block Declaration window. If you have an entity that you are using for more than one block, you must complete the Entity Declaration window separately for each block and provide all the entity information each time.
You must already have used the Document Entities window to define any entities you need before using them in this window.
Important: In setting an entity to have insert, update, or delete privileges, no other entity can have these privileges already. Also, simply changing the entity to not have the privilege will not resolve the issue - you must commit the changes so that no entity has the privilege(s), and then enable the privilege on the desired entity.
Enter an entity name from the list of entities that allow attachments.
The Attachments window has two modes in which it displays attachments. This mode is toggled with the "Include Related Documents" check box. The only difference is which attachments will be queried when the user enters the window. If the "Include Related Documents" check box is unchecked, the window should display only those attachments that are directly linked to the current record. When "Include Related Documents" is checked, attachments loosely related to the current record can be included as well.
| Variable | Description |
|---|---|
| Main Window | Specify "Main Window" for entities whose attachments you want to see immediately in the Attachments window whether or not the "Include Related Documents" check box is checked. Typically you would specify "Main Window" for the base entity of the block (or the one entity for the block that allows insert of new attachments). Entities that are included in turning on the attachment toolbar icon indicator should all use the "Main Window" display method. The user should never be shown an icon that indicates that attachments exist, press the toolbar icon, and find nothing queried up in the attachments form. |
| Related Window | Entity attachments with a display method of "Related Window" will be displayed along with those that use the "Main Window" display method only when the "Include Related Documents" checkbox is checked. Attachments to entities related to the base entity by a foreign key would typically use the "Related Window" display method. For example, in the Order_Lines block attachments to either the order or the product should be shown in the "related" attachment window, not the "main" attachment window. Attachments not included in setting the toolbar iconic button would typically use the "Related Window" display method. |
Check the "Include in Indicator" checkbox for each entity that should be included in setting the toolbar iconic button to indicate whether or not attachments exist for a record.
Any entity with a display method of "Main Window" should have "Include in Indicator" checked so the user is not surprised by finding attachments queried up by the Attachments window when the toolbar icon indicated that no attachments existed.
Depending on how you have implemented the attachments feature, checking "Include in Indicator" will cause a stored procedure to be executed for each entity at the WHEN-NEW-RECORD-INSTANCE event point (that is, the stored procedure will be executed for each record as the user scrolls through a group of queried records). You should avoid including loosely-related entities in the indicator. Segregating attachments in this way helps performance by limiting the entities that must be checked for attachments in order to show the appropriate attachment toolbar icon (with or without a paper in the paper clip).
Check the "Indicator in View" check box if you have made some modification to the form or view to determine whether or not attachments exist (that is, you are using a special implementation of the attachments feature). For a "standard" implementation of the attachments feature, you would not check this check box, and checking "Include in Indicator" or "Indicator in View" would be mutually exclusive.
The developer may either have opted to include a function in their view definitions to resolve attachment existence or may have implemented some other work around in the form code itself.
For options on how attachments can implemented to reduce the performance impact, see the Attachment Indicator: Performance Ramifications" section
You can define privileges to allow or prevent query, insert, update, or delete of attachments to a specific entity for the block. You can also define a conditional statement for privileges. For example, in the Oracle General Ledger journal entries form, you might allow query of attachments at any time, but not allow insert, update, or delete of attachments if the journal has been posted.
Privileges can be defined with the values of:
Always
Never
When condition True
When condition False
Note that these settings depend on the settings of the Method field in the Block Declaration window. If the method is set to "Allow Change", then you can further restrict the privileges for particular entities in this region. If the method is set to "Query Only", then you cannot use this region to expand upon those query-only privileges (for example, to allow inserts for a particular entity).
Determines whether you can query attachments to the entity.
Determines whether you can insert attachments to the entity. Only one attachment entity per block can allow inserts (the base entity), and the primary key fields must be populated (the attachment feature will create the record in the FND_ATTACHED_DOCUMENTS table with whatever values exist in those fields).
Determines whether you can update attachments to the entity.
Determines whether you can delete attachments to the entity. Generally you should only allow deletes from the form which defines the entity.
If you base your privileges on a condition, enter the name of the field in the calling form that contains the value to be used in evaluating the condition. Use the syntax block.field. You must include the block name.
If you base your privileges on a condition, select the operator to be used for evaluating the condition.
If you base your privileges on a condition, enter the value the condition field should be compared to. For most operators you will only enter a value for the Value 1 field.
Enter a second value only when using the BETWEEN operator.
The primary key fields are described here.
Enter the names of the fields in the calling form from which the primary keys for the entity can be derived. Use the syntax block.field. You must include the block name (for example, ORDER_LINES.PRODUCT_ID).
You must specify at least one primary key field for each entity (and for each block using the entity). Queries, inserts, updates, and deletes of attachments all depend on the primary key fields in the calling form containing values by the time the user presses the toolbar attachments icon. If the primary key values are not available when the button is pressed, the SQL statement built into the Attachments form may not include the attachments the user expects, or may be broader than the user expects.
These fields correspond to the primary key columns that uniquely identify an entity in the entity table. For example, for an Order Lines entity, the primary key columns could be ORDER_ID and ORDER_LINE_NUMBER, with the corresponding form fields ORDER_LINES.ORDER_ID and ORDER_LINES.ORDER_LINE_NUMBER. For a Product entity, the primary key column could be PRODUCT_ID, with the corresponding form field on the Orders form of ORDER_LINES.PRODUCT_ID.
Enter the primary keys in the order in which you want the data to be stored in FND_ATTACHED_DOCUMENTS. Data from the Key 1 field will be stored in the PK1_VALUE column, and so on. The PK1_VALUE through PK5_VALUE columns are defined as VARCHAR2(100) columns to enable an index to be defined across all columns.
In order to display attachments to an entity when the toolbar button is pressed, the necessary identifying data will have to be available in the form fields. For more information read the description of the "SQL Statement" attribute.
Use the SQL statement field to create "advanced" query criteria (restrictions) that you cannot get using the standard attachment entity attributes.
For example, if you have an Orders block and you want to include attachments to all Purchase Order Lines for that order as "related" attachments, you can achieve this goal without specifying a SQL fragment in this field. You would achieve this behavior by simply using the Lines entity with the Orders block but only specifying the first part of the Lines entity primary key (that is, Key 1 = LINES.ORDER_ID). For this to work, attachments to Order Lines must be created with ORDER_ID stored in the column FND_ATTACHED_DOCUMENTS.PK1_VALUE (that is, in any block where attachments for order lines can be created, the ORDER_ID field must be defined as the first primary key field).
If, however, you only want to see attachments to "enabled" Order Lines, you could use the SQL statement to limit the records returned in the attachments form using a SQL statement like: "AND EXISTS (SELECT 1 FROM order_lines WHERE order_id = FND_ATTACHED_DOCS_FORM_VL.pk1_value AND enabled_flag = 'Y')".
Enter a valid SQL fragment. Because this fragment will be added to the attachment form's WHERE clause, it cannot reference any fields using ":block.field" notation. The SQL statement cannot exceed 2000 characters.
In order to understand how to use the SQL statement, you need to understand the basic structure of the query in the Attachments form. The WHERE clause of the Attachments form will look something like this:
SELECT <columns> FROM fnd_attached_docs_form_vl
WHERE function_type = :parameter.function_type
AND function_name = :parameter.function_name
AND ( (entity_name = '<entity 1>'
AND pk1_value = '<key 1 value>'
...
AND pk5_value = '<key 5 value>'
AND <your SQL Statement for entity 1>)
OR (entity_name = '<entity 2>'
AND pk1_value = '<key 1 value>'
...
AND pk5_value = '<key 5 value>'
AND <your SQL Statement for entity 2>)
)
Warning: Using a SQL statement requires the use of dynamic SQL to perform the checks for attachments. While this is available in the FND_ATTACHMENT_UTIL_PKG.get_atchmt_exists_sql function, this function cannot be used in the definition of a view. Therefore any use of a SQL statement should be restricted to attachments to entities that will be displayed in the "related" attachments window and not included in setting the attachment indicator.
Related Topics
Attachments for Forms or Form Functions
Planning to Add the Attachments Feature to Your Application