Clarity

Hello,

Can any one tell me what Tables the information for the "Knowledge Store" is kept in...or if it is? I need to provide a list of all of the Documents kept there and not sure where to start. Thanks!

CLB_DMS_FILES
CLB_DMS_FOLDERS contains folders

Oh...so they are kept in the same tables as the Documents in the Project>Collaboration Tab? Do you know how or if the Knowledge Store Documents are differentiated from the Collaboration Files in the Tables?

For example: I'll need to retrieve the following from the Knowledge Store files:
a.
Knowledge Store Folder Path the document resides in (e.g. Top/IT Documents/Planning & Programs (P&P)/IT Portfolio)
b.
Document Name, including file extension
c.
Date Created
d.
Owner
e.
Version #
f.
Version Date , Time
g.
Version Checked-In By
h.
Version Last Accessed Date
i.
Version Last Accessed By
j.
Version Comments


Thanks!

Query for getting details for files in Clarity -

select cdfi.id id,
folder.name fol_name,
cdfi.name Filname,
odf.hosp_department hospital_name_dept,
cdfi.owner_id ownerid,
ii.id Project_int_id,
ii.name projectname,
ii.code Project_id,
ii.is_active Status,
odf.collective_number collnumber,
poa.unit_id unitid,
II.MANAGER_ID proj_manager_id,
(SELECT first_name || ' ' || last_name
FROM cmn_sec_users
WHERE ID = cdfi.owner_id) owner,
(SELECT first_name || ' ' || last_name
FROM cmn_sec_users
WHERE ID = ii.manager_id) ProjectManager
from clb_dms_files cdfi,
clb_dms_folders cdfo,
inv_investments ii,
odf_ca_project odf,
clb_dms_flat_hierarchy cdfh,
prj_obs_associations poa,
clb_dms_folders folder
where cdfo.id = cdfh.ancestor_id
and cdfi.parent_folder_id = cdfh.child_id
and cdfo.assoc_obj_id = ii.id
AND cdfo.assoc_obj_type = 'Projects'
and folder.id=cdfh.child_id
and ii.id = odf.id
and poa.record_id = ii.id
and poa.table_name = 'SRM_PROJECTS'
and poa.unit_id in (select ofm.linked_unit_id from obs_units_flat_by_mode ofm
where ofm.linked_unit_id = poa.unit_id
and ofm.unit_id = (:param_param_obs_unit)
and ofm.unit_mode ='OBS_UNIT_AND_CHILDREN' )

NJ

Ok...Thanks! I'll give this a try!!

Did you succeed with the provided query ? Or did you build something of your own?

NJ

Hi NJ,

Yes, I did starte with this SQL...but ended up customizing it further. ....so thanks much!