Oracle HCM Cloud — View and Report attachments/files stored in UCM

As we know that files/attachments uploaded against candidates and Employees are stored as actual documents in UCM (Universal Content Management Server), which sort of a file server. We can view these attachments directly against the record from UI such as absence document, Document or Record documents or Profile documents stored in talent management (CV/certificates etc). So can we actually view them directly in UCM server and/or can we actually build reports to show these attachments and even show the link to these documents ? . The answer is yes, we can search them in UCM as well as build reports to show the links/document name etc, however we cannot build any report directly showing the content of those attachments yet.

So first thing, How to search them in UCM and view the files.

You can go to the UCM server directly from your POD, just take the POD url and add cs at the end (content server)

<POD URL>/cs

Example :

https://mypod-saasfaprod1.fa.ocs.oraclecloud.com/cs

This will directly take you to the CS server shown below

You can search for any document here but before you do that make sure you have appropriate access. Fusion comes with seeded role which you can assign to a custom role before giving it to your user. User Attachment Administrator for full access. Here are the roles which give you access to cs content

Once you have access you would be able to see any file uploaded in the system, in my example I want to view cv uploaded against the candidate.

Once you have the access, you can search and view the files directly

Now coming to the second question, can we actually build BIP reports/Query to see the document link with other data such as Employee number etc to build more context around who this document belongs to and what type of document it is.

This requires knowledge of the tables which store the link to the actual document stored in UCM.

Good thing is that system stores all information about documents in these 2 tables fnd_attached_documents and fnd_documents_tl

All you need to do is link these tables with transaction tables.

To view profile related documents such as CV/Certificates you can build a query like this.

SELECT
papf.person_number,
ppnf.first_name,
ppnf.last_name,
fdt.file_name,
fdt.dm_version_number document_id,
fdt.dm_document_id UCM_file,
(SELECT
EXTERNAL_VIRTUAL_HOST INSTANCE_NAME
FROM
FUSION.ASK_DEPLOYED_DOMAINS
WHERE
DEPLOYED_DOMAIN_NAME = ‘FADomain’)||’/cs/idcplg?IdcService=GET_FILE&dID=’
|| fdt.dm_version_number
||’&dDocName=’
|| fdt.dm_document_id
|| ‘&allowInterrupt=1’ UCM_file_link
FROM
per_all_people_f papf,
per_person_names_f ppnf,
hrt_profiles_b hpb,
hrt_profiles_tl hpt,
fnd_attached_documents fad,
fnd_documents_tl fdt
WHERE
1=1
AND hpb.person_id = papf.person_id
AND papf.person_id = ppnf.person_id
AND hpb.profile_id = hpt.profile_id
AND hpb.profile_id = fad.pk1_value
AND fad.document_id = fdt.document_id
AND hpt.language = ‘US’
AND fdt.language = ‘US’
AND fad.entity_name = ‘HRT_PROFILES_B’
AND ppnf.name_type = ‘GLOBAL’
AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
AND trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date

— — — — — — — — — — — — — — — — — — — — — — — — —

SELECT
EXTERNAL_VIRTUAL_HOST INSTANCE_NAME
FROM
FUSION.ASK_DEPLOYED_DOMAINS
WHERE
DEPLOYED_DOMAIN_NAME = ‘FADomain’ gives you the POD URL dynamically and GET_FILE function directly fetches the document. The URL for document can directly open the document if user has privilege to the doc.

Similarly below query can show the documents from Document of Records (DOR)

SELECT
papf.person_number,
ppnf.first_name,
ppnf.last_name,
fdt.file_name,
fdt.dm_version_number document_id,
fdt.dm_document_id UCM_file,
‘https://<YOUR POD NAME/use above query>/cs/idcplg?IdcService=GET_FILE&dID=’
|| fdt.dm_version_number
||’&dDocName=’
|| fdt.dm_document_id
|| ‘&allowInterrupt=1’ UCM_file_link
FROM
per_all_people_f papf,
per_person_names_f ppnf,
HR_DOCUMENTS_OF_RECORD hpb,
fnd_attached_documents fad,
fnd_documents_tl fdt
WHERE
1=1
AND hpb.person_id = papf.person_id
AND papf.person_id = ppnf.person_id
AND hpb.DOCUMENTS_OF_RECORD_ID = fad.pk1_value
AND fad.document_id = fdt.document_id
AND fdt.language = ‘US’
AND fad.entity_name = ‘HR_DOCUMENTS_OF_RECORD’
AND ppnf.name_type = ‘GLOBAL’
AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
AND trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date

These queries can be helpful to fetch/query documents/contents from external system.