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:
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: