SQL to get the document from UCM server

To access the documents from UCM we need the Attachments Administrator role.

We can download the documents, which we are uploaded in Document of Records, Profiles etc.,

From the below SQL we can get we can get the URL’s to download the documents:

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://'|| (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 ,

fad.entity_name

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