BIP – Query to find attachment details

There are multiple objects in Oracle HCM Cloud which support attachments. Often, there is a need to extract the attachment details stored at the object level. All the uploaded attachments are stored in fnd_attached_documents table. Below query is used to extract the attachment details stored at Service Request level in HR Helpdesk. You can replace the table name and primary key join to extract the data as per your requirement:

select ssr.sr_id,
       ssr.title,  
       fad.CATEGORY_NAME ,
       fdt.file_name,
       fdt.dm_version_number document_id,
       fdt.dm_document_id UCM_file
  from fnd_attached_documents fad, svc_service_requests ssr, fnd_documents_tl fdt
 where ENTITY_NAME = 'SVC_SERVICE_REQUESTS'
   and ssr.sr_id = fad.PK1_VALUE
   and fad.document_id = fdt.document_id
   and fdt.language = 'US'

Query to get list of Jobs having an attachment:

 select pjft.name
   from per_jobs_f_tl pjft
  where pjft.language = 'US'
    and exists (select 1  
                  from fnd_attached_documents fad, PER_JOBS_F pjf, fnd_documents_tl fdt
                 where ENTITY_NAME = 'PER_JOBS_F'
                   and pjf.job_id = fad.PK1_VALUE
                   and fad.document_id = fdt.document_id
                   and fdt.language = 'US'
				   and pjf.job_id = pjft.job_id)

Refer below link for attachments on Position profile:

https://fusionhcmconsulting.com/2021/03/bip-reports-query-to-get-position-profile-attachment-details/

In order to download the attchments from UCM, the user should have AttachmentsRead role attached. Please check the below post on how to create AttachmentsRead role:

https://fusionhcmconsulting.com/2021/03/security-role-to-view-document-of-record-attachments-on-ucm/