Search for:
Sep 3 2023
BIP – Query to find size of documents attached in DOR

Oracle HCM provides a functionality to store different kinds of worker documents in Document Records. Over a period of time, a large number of documents get accumulated for different workers. In such cases, there are requirements to know size of attachments in DORs. For, this I have developed a simple query which can be modified as needed:

select fdv.file_name
      ,fdv.title
      ,dm_document_id
      ,TRUNC(SUM(wcc_documents.dFileSize)/1024,2) 			as "Size in KB"
      ,TRUNC(SUM(wcc_documents.dFileSize)/1024/1024,2) 		as "Size in MB"
      ,TRUNC(SUM(wcc_documents.dFileSize)/1024/1024/1024,2) as "Size in GB"
 from hr_documents_of_record hdr, 
      fnd_attached_documents fad, 
      fnd_documents_vl fdv,
      fusion_ocserver11g.revisions wcc_revisions,  
      fusion_ocserver11g.documents wcc_documents
where to_char(hdr.documents_of_record_id) = fad.pk1_value(+)
  and fad.entity_name(+) = 'HR_DOCUMENTS_OF_RECORD'
  and fad.document_id = fdv.document_id(+)
  and fdv.dm_version_number = wcc_revisions.did
  and wcc_revisions.did = wcc_documents.did
  and wcc_documents.disprimary = 1
group by fdv.file_name
        ,fdv.title
	,dm_document_id	
order by 1
Mandeep Gupta 0 Comments
Jul 9 2023
BIP – Extract Document Record File Link from Content Server

Below query can be used to extract the document record from content server.

SELECT papf.person_number		"Person Number",
       ppnf.first_name			"First Name",
       ppnf.last_name			"Last Name",
       fdt.file_name 			"Attached File Name",
       fdt.dm_version_number 	"Document Id",
       fdt.dm_document_id 		"UCM Content Id",
       (SELECT 'https://'||external_virtual_host
          FROM fusion.ask_deployed_domains
         WHERE deployed_domain_name = 'FADomain')
	   ||'/cs/idcplg?IdcService=GET_FILE' 
	   || chr(38) 
	   || '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 hdor,
       fnd_attached_documents fad,
       fnd_documents_tl fdt
 WHERE 1=1
   AND hdor.person_id = papf.person_id
   AND papf.person_id = ppnf.person_id
   AND hdor.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
 ORDER BY 1
Mandeep Gupta 0 Comments
Oct 6 2022
BIP – Query to extract DOR attachment name
select papf.person_number 
      ,hdor.document_code
      ,hdor.document_name
      ,hdor.document_number
      ,fdt.file_name
      ,fdt.dm_version_number document_id
      ,fdt.dm_document_id UCM_file
 from fnd_attached_documents fad, hr_documents_of_record hdor, fnd_documents_tl fdt
   ,per_all_people_f papf
   ,hr_document_types_tl hdtt
 where ENTITY_NAME = 'HR_DOCUMENTS_OF_RECORD'
  and hdor.documents_of_record_id = fad.PK1_VALUE
  and fad.document_id = fdt.document_id
  and fdt.language = 'US'
  and hdor.person_id = papf.person_id
  and TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
  and hdor.document_type_id = hdtt.document_type_id
  and hdtt.language = 'US'
  and hdtt.DOCUMENT_TYPE = 'Test Doc'
Mandeep Gupta 0 Comments
Oct 6 2022
HDL – Sample HDL file to upload DoR for a specific country
METADATA|DocumentsOfRecord|PersonNumber|Country|DocumentType|DocumentCode|DateFrom|DateTo

MERGE|DocumentsOfRecord|78652|United Kingdom|UK Vehicle Information|OVERTIME_AVERAGING_AGREEMENT_2020-10-01|2020/10/01|2022/10/01



METADATA|DocumentAttachment|PersonNumber|Country|DocumentType|DocumentCode|DataTypeCode|Title|URLorTextorFileName|File

MERGE|DocumentAttachment|78652|United Kingdom|UK Vehicle Information|OVERTIME_AVERAGING_AGREEMENT_2020-10-01|FILE|DD test document of record for HDL.pdf|DD test document of record for HDL.pdf|DD test document of record for HDL.pdf

Please note that under attribute country, you will need to pass the full country name. Passing country code like GB, will throw below error:

The values GB aren’t valid for the attribute LegislationCode.

Mandeep Gupta 0 Comments
Mar 31 2021
HDL – Loading Multiple attachments for same document type

There are various cases where more than one attachment should be maintained against employee document record. First of all, we will create a new document type where multiple attachments will be allowed.

  1. Navigate to Setup and Maintenance -> Document Types -> Click on Create

2. Choose multiple occurrences as Yes:

3. Enter the relevant information and submit the record.

4. Prepare the HDL in below format and load the data:

METADATA|DocumentsOfRecord|PersonNumber|DateFrom|DateTo|DocumentType|DocumentName|DocumentNumber|DocumentCode|Country|AttachmentEntityName|Comments|IssuedDate|IssuingAuthority|IssuingCountry|IssuingLocation|Publish|PublishDate|SourceSystemId|SourceSystemOwner
MERGE|DocumentsOfRecord|101|2021/01/01||Test Multi Attachments Doc|Multiple Attachments||TMAD_001||||||||||HRC_SQLLOADER_101_TMAD_001|HRC_SQLLOADER

METADATA|DocumentAttachment|SourceSystemOwner|SourceSystemId|PersonNumber|DocumentType|DocumentCode|Title|Description|DataTypeCode|Country|EntityName|File|FileName|MimeType|URL|URLorTextorFileName|DmVersonNumber
MERGE|DocumentAttachment|HRC_SQLLOADER|HRC_SQLLOADER_101_TMAD_001_1|101|Test Multi Attachments Doc|TMAD_001|Test Multi Attachments Doc|Test Multi Attachments Doc 1|FILE|||Historical_rating.docx|Historical_rating.docx|||Historical_rating.docx|
MERGE|DocumentAttachment|HRC_SQLLOADER|HRC_SQLLOADER_101_TMAD_001_2|101|Test Multi Attachments Doc|TMAD_001|Test Multi Attachments Doc|Test Multi Attachments Doc 2|FILE|||Historical_rating1.docx|Historical_rating1.docx|||Historical_rating1.docx|

5. Place the docx files in BlobFiles folder and zip with the .dat file:

6. Import and Load the file.

7. Once the data is loaded, verify from the UI:

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