- Display an Attachments Table on the Page of a Single Row Region
- Display
In-line Links of Attachments in a Single Row Region
- Display
an Attachments Column in a Multi-Row Table Region
Add
Attachment Page:
On
Clicking Add Attachment, users are navigated to this page.
It is
clearly displaying the three types of attachments supported:
Attention:
All Text attachments are added as
"Short Text" type, with a maximum length of 4000 bytes. If
you need to add an attachment that is longer than 4000 bytes, put the
text into a text file and add the attachment as a File.
Steps to
add Attachments:
Add
the UI widget for attachments. Following are the widgets:
attachmentTable
- for Attachments table
attachmentLink
- for View List link and Add button
MessageInlineAttachment
- for inline attachment links
attachmentImage
- For Attachment column in Table and Advance Table
Specify
View Instance property
Select
the default entityMap that is created under the attachment region
item, set the Entity value to some unique value which will be used
to identify all attachments.
Right
Click on Entity Map and Add primary keys and specify the primary
keys used to identify the attachment uniquely.
Some key
SQL Queries:
Query
1: List of All Attachments
--------------------------------------------
select
* from fnd_attached_documents where creation_date > sysdate - 1/24
;/
//
List of all Attachments attached in last 1 hr.
Query
2: List of All Attachments
--------------------------------------------
select
* from fnd_attached_documents where entity_name = 'XXSL_GROUP' ;/
//
List of all Attachments with entity_name = 'XXSL_GROUP'
Query
3: List of All Attachments
--------------------------------------------
select
attached_document_id, -- Primary Key
document_id,
-- foreign key for FND_DOCUMENTS
entity_name,
-- Name of unique entity entered in OAF page's Attachment component
PK1_value,
-- primary key attribute specified in OAF page's Attachment
component.
--
There are 5 Primary Key columns here to store composite primary keys.
seq_num,
-- sequence number of the attachment added.
category_id
-- Category of the attachment, foreign key to FND_DOCUMENT_CATEGORIES
--
In case you are not able to see an attachment in the UI,
--
check the "Show All" property of the Entity Map
from
fnd_attached_documents
WHERE
ENTITY_NAME = '<your entity name>' and pk1_value = '<primary
key of your VO>'; /
Query
4: Details of the document attached
---------------------------------------------------------
SELECT
* FROM FND_DOCUMENTS where document_id in (7922696, 7922698, 7922700,
7922702); /
SELECT
document_id, -- Primary Key
datatype_id,
-- foreign key to FND_DOCUMENT_DATATYPES, tells about the type of
attachment
category_id,
-- Category of the attachment, foreign key to FND_DOCUMENT_CATEGORIES
url,
-- URL field is populated in case of URL Type attachment
File_name,
-- File name field is populated in case of a file type attachment
media_id
-- Foreign key to fnd_lobs for datatype_id = 6
--
FROM
FND_DOCUMENTS -- Attachment Title is stored in FND_DOCUMENTS_TL
where
document_id in (select document_id from fnd_attached_documents where
ENTITY_NAME = '<your entity name>' and pk1_value = '<primary
key of your VO>');
/
Query
5: Types of Attachments
-------------------------------------------
select
* from FND_DOCUMENT_DATATYPES;/
Short
Text
Long
Text
Image
OLE
Object
Web
Page
File
Document
Reference
Oracle
Files Folder/Workspace
Oracle
File
select
* from xxsl_lms_group_headers where group_id = 1722 ;/
Query
6: Types of File Attached
---------------------------------------------
--Record
will be in FND_LOB only for datatype_id = 6
select
* from fnd_lobs
where
file_id in
(select
media_id
from
FND_DOCUMENTS
where
document_id in
(select
document_id from fnd_attached_documents where ENTITY_NAME = '<your
entity name>' and pk1_value = '<primary key of your VO>' and
datatype_id = 6)
);
/
Query
7: for "Short Text" type:
------------------------------------------
--
Record will exist in FND_DOCUMENTS_SHORT_TEXT if datatype_id is 1
select
* from FND_DOCUMENTS_SHORT_TEXT
where
media_id in
(select
media_id
from
FND_DOCUMENTS
where
document_id in
(select
document_id from fnd_attached_documents where ENTITY_NAME = '<your
entity name>' and pk1_value = '<primary key of your VO>' and
datatype_id = 1)
);
/
API
Support:
For Api support refer to following packages -
1. FND_DOCUMENTS_PKG,
2. FND_ATTACHED_DOCUMENTS_PKG
3. fnd_attached_documents2_pkg.delete_attachments
(to delete attachments)
Followings
are not covered in this post:
