45
Attachments 32-21
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.
SQL Statement Tabbed Region
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
