You can get attached_document_id of approximate all applications transaction as shown below.
=============================================================
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'R_DOCUMENT_EXTRA_INFO'
AND FAD.PK1_VALUE IN(SELECT HDEI.DOCUMENT_EXTRA_INFO_ID DOCUMENT_EXTRA_INFO_ID
FROM hr.hr_document_extra_info HDEI
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'PQH_SS_ATTACHMENT'
AND fad.pk1_value IN(SELECT hat.transaction_id transaction_id
FROM hr.hr_api_transactions hat
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'AP_INVOICES'
AND fad.pk1_value IN(SELECT aia.invoice_id invoice_id
FROM ap.ap_invoices_all aia
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name IN ('PO_HEAD','PO_HEADERS')
AND fad.pk1_value IN (SELECT pha.po_header_id header_id
FROM po.po_headers_all pha
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'OIE_HEADER_ATTACHMENTS'
AND fad.pk1_value IN(SELECT arha.report_header_id report_header_id
FROM ap.ap_expense_report_headers_all arha
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'OKC_CONTRACT_DOCS'
AND fad.pk2_value IN(SELECT ocd.business_document_id
FROM okc.okc_contract_docs ocd
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'PO_LINES'
AND fad.pk1_value IN(SELECT pla.po_line_id po_line_id
FROM po.po_lines_all pla
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'OIE_LINE_ATTACHMENTS'
AND fad.pk1_value IN(SELECT arla.report_header_id report_header_id
FROM ap.ap_expense_report_lines_all arla
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'REQ_HEADERS'
AND fad.pk1_value IN(SELECT prha.requisition_header_id requisition_header_id
FROM po.po_requisition_headers_all prha
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name ='REQ_LINES'
AND fad.pk1_value IN(SELECT prla.requisition_line_id requisition_line_id
FROM po.po_requisition_lines_all prla
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name ='PO_SHIPMENTS'
AND fad.pk1_value IN(SELECT plla.line_location_id line_location_id
FROM po.po_line_locations_all plla
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'IBY_PAY_INSTRUCTIONS_ALL'
AND fad.pk1_value IN(SELECT pia.payment_instruction_id payment_insruction_id
FROM iby.iby_pay_instructions_all pia
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'PER_ABSENCE_ATTENDANCES'
AND fad.pk1_value IN(SELECT to_char(paa.absence_attendance_id) absence_attendance_id
FROM hr.per_absence_attendances paa
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'XX_LOOKUP_DOCUMENT_ENTITY'
AND fad.pk2_value LIKE '%BBW%%'
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'PER_PEOPLE_F'
AND fad.pk1_value IN(SELECT asg.person_id person_id
FROM hr.per_all_assignments_f asg
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'PAY_ELEMENT_ENTRIES_F'
AND fad.pk1_value IN(SELECT peef.element_entry_id element_entry_id
FROM hr.pay_element_entries_f peef
)
UNION
SELECT fad.attached_document_id attached_document_id
FROM applsys.fnd_attached_documents fad
WHERE fad.entity_name = 'OTA_DELEGATE_BOOKINGS'
AND fad.pk1_value IN(SELECT odb.booking_id booking_id
FROM OTA.OTA_DELEGATE_BOOKINGS ODB
)
==================================================================
Once you have the attached_document_id
you can use below query
SELECT fd.* FROM FND_ATTACHED_DOCUMENTS FAD, FND_DOCUMENTS FD
WHERE FAD.DOCUMENT_ID=FD.DOCUMENT_ID;
based on datatype_id in fnd_documents
you can join with any of
,APPLSYS.FND_LOBS
,APPLSYS.FND_DOCUMENTS_SHORT_TEXT
,APPLSYS.FND_DOCUMENTS_LONG_TEXT
tables.
