Description:
Query that gets the file name, folder name, path name, object name and file version from documents stored in projects and the knowledge store. This query is good to find out what attachments exist in projects or KS. This current solution is for Oracle only
Solution:
select CDV.id as name_in_fs,
SUBSTR(CDV.ID,2,3)||’/00’||SUBSTR(CDV.ID,1,1)||’/’||CDV.ID as folder_name,
cdf.name name_of_file,
cdf.LAST_UPDATED_DATE,
cdfd.PATH_NAME,
FOLDER_TYPE name_of_object,
CDF.PARENT_FOLDER_ID,
cdh.VERSION_ID
from CLB_DMS_FILES CDF
inner join CLB_DMS_VERSIONS CDV on (CDF.id = CDV.file_id)
inner join CLB_DMS_HISTORIES CDH on (CDV.id = CDH.version_id)
inner join CLB_DMS_FOLDERS CDFD on (CDF.PARENT_FOLDER_ID = CDFD.id)
WHERE (FOLDER_TYPE = ‘ProjectFolder’)
(FOLDER_TYPE = ‘ProjectFolder’ or FOLDER_TYPE = ‘StandardFolder’)
“Solutions provided by CA Technolgies”