Tuesday, June 15, 2010

FND Attachments In Oracle Apps R12

1. Script to attach a document using API
http://shareoracleapps.blogspot.com/2010/03/how-to-attach-documents-from-backend.html

2. Script to Delete a Attachment using API
http://shareoracleapps.blogspot.com/2010/03/how-to-delete-attachment-from-backend.html

3. Queries

As we know, we can attach documents in different formats like WEBPAGE, FILE, LONG_TEXT and SHORT_TEXT.Hence, i had given queries to find the attached documents FOR EACH format separately.

-- WEBPAGE ATTACHMENT

 SELECT DISTINCT
  AD.SEQ_NUM                  ,
  DCT.USER_NAME               , 
  DAT.USER_NAME               ,
  D.STORAGE_TYPE              ,
  D.FILE_NAME                 ,
  D.IMAGE_TYPE                ,
  D.USAGE_TYPE                ,
  AD.AUTOMATICALLY_ADDED_FLAG ,
  AD.ATTACHED_DOCUMENT_ID     ,
  DET.USER_ENTITY_NAME        ,
  D.DATATYPE_ID               ,
  DAT.NAME                    ,
  D.DOCUMENT_ID               , 
  D.START_DATE_ACTIVE         ,
  D.END_DATE_ACTIVE           ,
  D.SECURITY_TYPE             ,
  D.SECURITY_ID               ,
  D.PUBLISH_FLAG              ,
  AD.CREATED_BY               ,
  DET.USER_ENTITY_PROMPT      ,
  AD.ENTITY_NAME              ,
  AD.COLUMN1                  ,
  AD.PK1_VALUE                , 
  D.MEDIA_ID                  ,
  D.CATEGORY_ID               ,
  D.URL                       ,
  DT.TITLE
FROM FND_DOCUMENT_DATATYPES DAT,
  FND_DOCUMENT_ENTITIES_TL DET    ,
  FND_DOCUMENTS_TL DT             ,
  FND_DOCUMENTS D                 ,
  FND_DOCUMENT_CATEGORIES_TL DCT  , 
  FND_ATTACHED_DOCUMENTS AD
WHERE D.DOCUMENT_ID        = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID         = D.DOCUMENT_ID
AND DCT.CATEGORY_ID        = D.CATEGORY_ID
AND D.DATATYPE_ID          = DAT.DATATYPE_ID
AND AD.ENTITY_NAME         = DET.DATA_OBJECT_CODE
AND DAT.name               = 'WEB_PAGE';


-- LONG_TEXT ATTACHMENT

 SELECT
  AD.SEQ_NUM                  ,
  DCT.USER_NAME               , 
  DAT.USER_NAME               , 
  AD.ATTACHED_DOCUMENT_ID     ,
  DET.USER_ENTITY_NAME        , 
  DAT.NAME                    ,
  D.DOCUMENT_ID               , 
  AD.ENTITY_NAME              , 
  AD.PK1_VALUE                , 
  D.MEDIA_ID                  , 
  D.URL                       ,
  DT.TITLE                    ,
  DLT.LONG_TEXT
FROM FND_DOCUMENT_DATATYPES DAT,
  FND_DOCUMENT_ENTITIES_TL DET    ,
  FND_DOCUMENTS_TL DT             ,
  FND_DOCUMENTS D                 ,
  FND_DOCUMENT_CATEGORIES_TL DCT  , 
  FND_ATTACHED_DOCUMENTS AD       ,
  FND_DOCUMENTS_LONG_TEXT DLT    
WHERE D.DOCUMENT_ID       = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID        = D.DOCUMENT_ID
AND DCT.CATEGORY_ID       = D.CATEGORY_ID
AND D.DATATYPE_ID         = DAT.DATATYPE_ID
AND AD.ENTITY_NAME        = DET.DATA_OBJECT_CODE
AND DLT.MEDIA_ID          = D.MEDIA_ID
AND DAT.NAME              = 'LONG_TEXT';

-- SHORT_TEXT ATTACHMENT

SELECT
  AD.SEQ_NUM                  ,
  DCT.USER_NAME               , 
  DAT.USER_NAME               , 
  AD.ATTACHED_DOCUMENT_ID     ,
  DET.USER_ENTITY_NAME        , 
  DAT.NAME                    ,
  D.DOCUMENT_ID               , 
  AD.ENTITY_NAME              , 
  AD.PK1_VALUE                , 
  D.MEDIA_ID                  , 
  D.URL                       ,
  DT.TITLE                    ,
  DST.SHORT_TEXT
FROM FND_DOCUMENT_DATATYPES DAT,
  FND_DOCUMENT_ENTITIES_TL DET    ,
  FND_DOCUMENTS_TL DT             ,
  FND_DOCUMENTS D                 ,
  FND_DOCUMENT_CATEGORIES_TL DCT  , 
  FND_ATTACHED_DOCUMENTS AD       ,
  FND_DOCUMENTS_SHORT_TEXT DST    
WHERE D.DOCUMENT_ID          = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID           = D.DOCUMENT_ID
AND DCT.CATEGORY_ID          = D.CATEGORY_ID
AND D.DATATYPE_ID            = DAT.DATATYPE_ID
AND AD.ENTITY_NAME           = DET.DATA_OBJECT_CODE
AND DsT.MEDIA_ID             = D.MEDIA_ID
AND DAT.NAME                 = 'SHORT_TEXT';

-- FILE ATTACHMENT

SELECT
  AD.SEQ_NUM                  ,
  DCT.USER_NAME               , 
  DAT.USER_NAME               , 
  AD.ATTACHED_DOCUMENT_ID     ,
  DET.USER_ENTITY_NAME        , 
  DAT.NAME                    ,
  D.DOCUMENT_ID               , 
  AD.ENTITY_NAME              , 
  AD.PK1_VALUE                , 
  D.MEDIA_ID                  , 
  D.URL                       ,
  DT.TITLE                    ,
  DBMS_LOB.SUBSTR(L.file_data,1,10) file_data
FROM FND_DOCUMENT_DATATYPES DAT,
  FND_DOCUMENT_ENTITIES_TL DET    ,
  FND_DOCUMENTS_TL DT             ,
  FND_DOCUMENTS D                 ,
  FND_DOCUMENT_CATEGORIES_TL DCT  , 
  FND_ATTACHED_DOCUMENTS AD       ,
  FND_LOBS  L                         
WHERE D.DOCUMENT_ID          = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID           = D.DOCUMENT_ID
AND DCT.CATEGORY_ID          = D.CATEGORY_ID
AND D.DATATYPE_ID            = DAT.DATATYPE_ID
AND AD.ENTITY_NAME           = DET.DATA_OBJECT_CODE
AND L.FILE_ID                = D.MEDIA_ID
AND DAT.name                 = 'FILE';

-- This query will help you to find the functions using attachments

SELECT
  AD.SEQ_NUM                  ,
  DCT.USER_NAME               , 
  DAT.USER_NAME               , 
  AD.ATTACHED_DOCUMENT_ID     ,
  DET.USER_ENTITY_NAME        , 
  DAT.NAME                    ,
  D.DOCUMENT_ID               , 
  AD.ENTITY_NAME              , 
  AD.PK1_VALUE                , 
  D.MEDIA_ID                  , 
  D.URL                       ,
  DT.TITLE                    ,
  AF.FUNCTION_NAME            ,
  AF.FUNCTION_TYPE             
FROM FND_DOCUMENT_DATATYPES DAT,
  FND_DOCUMENT_ENTITIES_TL DET    ,
  FND_DOCUMENTS_TL DT             ,
  FND_DOCUMENTS D                 ,
  FND_DOCUMENT_CATEGORIES_TL DCT  ,
  FND_DOC_CATEGORY_USAGES DCU     ,
  FND_ATTACHMENT_FUNCTIONS AF     ,
  FND_ATTACHED_DOCUMENTS AD
WHERE 1 = 1
AND D.DOCUMENT_ID              = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID             = D.DOCUMENT_ID
AND DCT.CATEGORY_ID            = D.CATEGORY_ID
AND DCU.CATEGORY_ID            = D.CATEGORY_ID
AND DCU.ATTACHMENT_FUNCTION_ID = AF.ATTACHMENT_FUNCTION_ID
AND D.DATATYPE_ID              = DAT.DATATYPE_ID
AND AD.ENTITY_NAME             = DET.DATA_OBJECT_CODE
AND DCU.ENABLED_FLAG           = 'Y';

2 Responses to “FND Attachments In Oracle Apps R12”

Anonymous said...
July 13, 2017 at 2:42 PM

Tried File Attachment query.. doesn't work.


Anonymous said...
October 11, 2017 at 11:42 AM

Hi,
Can you please help me with query that pulls the below information for FND_ATTACH form
1 – Determine which applications have records where attachments exist
2 – Determine what types of files are being attached


Post a Comment

Subscribe to: Post Comments (Atom)

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search