• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 16, 2020
  • Reading time:4 mins read
You are currently viewing SQL Query to fetch the Document of Records
SQL Query to fetch the Document of Records

In this article we will look into how to fetch the Document of Records Information from the backend tables.

When we add a Document for an employee, the information will get stored in multiple tables and we need to know the link between those tables to get the information correctly.

HR_DOCUMENTS_OF_RECORD – This is the main table where the DOR information for an employee is stored.
HR_DOCUMENT_TYPES_B – This is the base table which stores the Document Type Information
FND_ATTACHED_DOCUMENTS – This table stores the Document Information that has been uploaded.
FND_DOCUMENTS_VL – This view gets the properties for the file that has been uploaded to UCM. Title, Content ID, Created By, Creation Time, etc.

All the documents that are uploaded to Document of Records will be uploaded to UCM and a unique Content ID will get generated for each request. However these attachments will be secured and they won’t be available for view and download on UCM as they are uploaded with DOWNLOAD_STATUS of N. Even if we search for the Content ID, we won’t get those in search results. However we can get the Content ID in the backend tables. Not sure if there is a super user for UCM with some privileges using which we can view all these files. I have tried to get that access but couldn’t succeed in getting it.

Table of Contents

SQL Query to get Document of Records Information:

SELECT 
'Document Of Record' Header
,'US' AS COUNTRY_CODE
,A.DOCUMENT_CODE 
,B.LEGISLATION_CODE 
,C.PERSON_NUMBER 
,A.ISSUING_COUNTRY AS Country
,D.DOCUMENT_TYPE 
,A.DOCUMENT_NAME 
,A.DOCUMENT_NUMBER 
,TO_CHAR(A.DATE_FROM,'YYYY-MM-DD') AS DATE_FROM
,TO_CHAR(A.DATE_TO,'YYYY-MM-DD') AS DATE_TO
,A.ISSUING_AUTHORITY 
,TO_CHAR(A.ISSUED_DATE,'YYYY-MM-DD') AS ISSUES_DATE
,A.ISSUING_COUNTRY AS ISSUING_COUNTRY_NAME
,A.ISSUING_LOCATION 
,A.COMMENTS 
,A.PUBLISH 
,TO_CHAR(A.PUBLISH_DATE,'YYYY-MM-DD') AS PUBLISH_DATE
,A.RELATED_OBJECT_NAME 
,A.STATUS 
,A.DEI_ATTRIBUTE_CATEGORY AS ATTRIBUTE_CATEGORY
,A.DEI_ATTRIBUTE1 AS ATTRIBUTE1
,A.DEI_ATTRIBUTE2 AS ATTRIBUTE2
,A.DEI_ATTRIBUTE3 AS ATTRIBUTE3
,A.DEI_ATTRIBUTE4 AS ATTRIBUTE4
,A.DEI_ATTRIBUTE5 AS ATTRIBUTE5
,A.DEI_ATTRIBUTE6 AS ATTRIBUTE6
,A.DEI_ATTRIBUTE7 AS ATTRIBUTE7
,A.DEI_ATTRIBUTE8 AS ATTRIBUTE8
,A.DEI_ATTRIBUTE9 AS ATTRIBUTE9
,A.DEI_ATTRIBUTE10 AS ATTRIBUTE10
FROM  HR_DOCUMENTS_OF_RECORD A
,HR_DOCUMENT_TYPES_B B
,PER_ALL_PEOPLE_F  C
,HR_DOCUMENT_TYPES_TL D
WHERE A.DOCUMENT_TYPE_ID = B.DOCUMENT_TYPE_ID
AND A.PERSON_ID = C.PERSON_ID
AND A.DOCUMENT_TYPE_ID = D.DOCUMENT_TYPE_ID
AND D.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG')
AND SYSDATE BETWEEN C.EFFECTIVE_START_DATE AND C.EFFECTIVE_END_DATE

SQL Query to get the Content ID and details for DOR:

select
fad.DOCUMENT_ID  ,
fdv.DM_DOCUMENT_ID CONTENT_ID,
fdv.datatype_code ,
fdv.FILE_NAME ,
fdv.TITLE ,
fdv.URL ,
fdv.DESCRIPTION ,
HDOR.DOCUMENT_CODE ,
PAPF.PERSON_NUMBER ,
HDTB.DOCUMENT_TYPE ,
fad.ENTITY_NAME ,
fdv.DM_VERSION_NUMBER COLUMNDATA
FROM 
	FND_ATTACHED_DOCUMENTS fad,
	fnd_documents_vl fdv,
	PER_ALL_PEOPLE_F PAPF,
	HR_DOCUMENT_TYPES_vl HDTB,
	HR_DOCUMENTS_OF_RECORD HDOR
WHEREfad.document_id = fdv.document_id
and   PAPF.PERSON_ID = HDOR.PERSON_ID
and papf.person_number = '<PERSON_NUMBER>'
AND HDTB.DOCUMENT_TYPE_ID = HDOR.DOCUMENT_TYPE_ID
and to_char(HDOR.documents_of_record_id) = to_char(FAD.pk1_value)

Hope these queries help when you have requirement to fetch the Document of Records information.