Query to find attachments of a contract

Query to find attachments of a contract 

SELECT adoc.creation_date Creation_date
,DECODE (docs.datatype_id,1,docs_tl.media_id,NULL) text_Id
,adoc.pk1_value Contract_Number
,docs_tl.doc_attribute2 file_size
,DECODE (docs.datatype_id,5,docs_tl.file_name,NULL) link_Url
,DECODE (docs.datatype_id,6,docs_tl.file_name,NULL) File_Name
,DECODE (docs.datatype_id,6,docs_tl.media_id,NULL) fileId
FROM fnd_attached_documents adoc
,fnd_documents docs
,fnd_documents_tl docs_tl
,fnd_documents_short_text doc_s_text
WHERE 1 = 1
AND adoc.document_id = docs.document_id
AND docs.document_id = docs_tl.document_id
AND docs_tl.media_id = doc_s_text.media_id(+)
AND adoc.entity_name = 'OKC_K_HEADERS_V'
AND pk1_value = '1081665' -- Enter your contract number here.

Very large files are stored in the fnd_lobs table

To check the attached file in fnd_lobs table

1. Note Document ID from above query.
2. Take media ID for this document ID from fnd_documents_tl table.
3. run following query:
select * from fnd_lobs where file_id = (media id from 2nd query)

Post a Comment