Loading

Tuesday, June 15, 2010

FND Attachments In Oracle Apps R12



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';

1 Responses to “FND Attachments In Oracle Apps R12”

Paul Sprunken said...
January 4, 2016 at 8:18 AM

You might have the answer for me: I'mnot an SQLliterate but here is my question/needs:
we have many Master Item entries with PDF format file attachments.
I would want to determine the URL that allows to access any attachments quickly on line via the browser.
When I query the item, to the Attachments, select the File attachment and then click Open Document... then a temporary URL is created and the file is displayed in the browser. After 24 hrs the URL seems to expire. But I am sure the object (pdf file) resides in the same place in the DB as before; Wouldn't it be possible to construct a URL that I can put in eg. an intranet page (or included in a BI query result) which I can follow as a hyperlink(and which doesn't expire)?
Any clues or suggestions would be greatly appreciated


Post a Comment

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.