Search for:
Jun 10 2021
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/

Mandeep Gupta 0 Comments
Mar 4 2021
Security – Role to view document of record attachments on UCM

Document of record attachments uploaded by workers in the Fusion HCM resides on UCM server. If there is a requirement to download or view the uploaded attachment on UCM, none of the standard roles like HR Analyst, HR Specialist have the privilege to view the DOR attachments on UCM. To achieve this, a custom role should be created. Please follow the below mentioned steps:

  1. Create a new abstract role ‘XXX Read Attachments’ and add ‘AttachmentsRead’ standard role to it.

2. Once the role is ready, assign it to the required User and run ‘Import User and Role Application Security Data’ process.

3. On successful completion of the process, the user will be able to search any of the document of record attachment using content ID in UCM server and will be able to download the same.

Mandeep Gupta 0 Comments