Determine owner of a document

We are looking at upgrading our 9.7.1 environment to 10.x we are looking to do data clean up of our current system before upgrade so we can trim our DB.

I am new to LiveReports and was looking for some help in getting some reports to determine where the document sits(a path) and/or who owns it.

I was looking at some of the Live reports in our 9.7.1 environment and there are a couple that look like they would suit mainly Folder Assigned Access and/or Folder Hierarchy with Subitems but they use GET_FOLDER_TREE, is this a function? and how do I find if we have it installed? I know we have updated our DB to the 10 version.

Or is there someone who has gone through an updgrade that may have some suggestions on data cleaning

thanks

Craig

Find more posts tagged with

Comments

Lindsay_Davies

Hi Craig,

Are you using Oracle or SQL Server as the relational database?

Get_Folder_Tree might be a stored procedure that has been created in your SQL Server database.

If you use Oracle, the normal method is to use a connect by prior construct.

I imagine you want to report only the items where the user who owns an item is no longer a valid Livelink user.

If you let me know whether you are using for you database and which version then I can give more useful guidance.

Regards

Lindsay

European Escalation Team

OpenText

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, September 10, 2014 6:39 PM
To: eLink Recipient
Subject: Determine owner of a document

Determine owner of a document

Posted bycraig.goldsmith@gov.bc.ca (Goldsmith, Craig) On 09/10/2014 01:35 PM

We are looking at upgrading our 9.7.1 environment to 10.x we are looking to do data clean up of our current system before upgrade so we can trim our DB.

I am new to LiveReports and was looking for some help in getting some reports to determine where the document sits(a path) and/or who owns it.

Or is there someone who has gone through an updgrade that may have some suggestions on data cleaning

thanks

Craig

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Colin_Schmidt

Oracle has a function built-in: SYS_CONNECT_BY_PATH. You can find examples out there on the Internet and probably even in this Discussion forum. In the same query, you can retrieve the Owner/Creator ID and pull the Name from KUAF.

If you aren't using Oracle, then someone else will have to answer.

select dt.modifydate, Level,

'Enterprise' || SYS_CONNECT_BY_PATH (dt.name, ' | ') "Path", dt.dataid, dt.name

from dtree dt

/* join dtree dt1 on dt.parentid = dt1.dataid and lower(dt1.name) = 'pdf' */

join dtree dt2 on abs(dt.ownerid) = dt2.dataid

where (dt.subtype = 144 and dt.ownerid = -2000)

and to_char(dt.modifydate, 'yyyy-mm-dd') >= '2013-05-29'

CONNECT BY PRIOR dt.dataid = abs(dt.parentid) START WITH dt.parentid = 2000

order by dt.modifydate

Here's one example though not specific to your need.

Colin J.

On Wed, Sep 10, 2014 at 1:39 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Determine owner of a document Posted bycraig.goldsmith@gov.bc.ca (Goldsmith, Craig)On 09/10/2014 01:35 PM

We are looking at upgrading our 9.7.1 environment to 10.x we are looking to do data clean up of our current system before upgrade so we can trim our DB. I am new to LiveReports and was looking for some help in getting some reports to determine where the document sits(a path) and/or who owns it. I was looking at some of the Live reports in our 9.7.1 environment and there are a couple that look like they would suit mainly Folder Assigned Access and/or Folder Hierarchy with Subitems but they use GET_FOLDER_TREE, is this a function? and how do I find if we have it installed? I know we have updated our DB to the 10 version. Or is there someone who has gone through an updgrade that may have some suggestions on data cleaning thanks Craig
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

Craig_Goldsmith

Hi Lindsey,

We are using Oracle as our database. Yes we would want to have only valid users.

I will look at the connect by prior construct, not something I am greatly familiar with.

Thanks for your imput

Craig Goldsmith

Systems Analyst, Application Management

Information Management Branch

Corporate Services Division

Phone: 250.415.6273 Fax: 250.356.2643

Please consider the environment before printing this email.

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, September 10, 2014 11:00 AM
To: eLink Recipient
Subject: RE Determine owner of a document

RE Determine owner of a document

Posted by ldavies@opentext.com (Davies, Lindsay) On 09/10/2014 01:59 PM

Hi Craig,

Are you using Oracle or SQL Server as the relational database?

Get_Folder_Tree might be a stored procedure that has been created in your SQL Server database.

If you use Oracle, the normal method is to use a connect by prior construct.

I imagine you want to report only the items where the user who owns an item is no longer a valid Livelink user.

If you let me know whether you are using for you database and which version then I can give more useful guidance.

Regards

Lindsay

European Escalation Team

OpenText

Determine owner of a document

Posted by craig.goldsmith@gov.bc.ca (Goldsmith, Craig) On 09/10/2014 01:35 PM

We are looking at upgrading our 9.7.1 environment to 10.x we are looking to do data clean up of our current system before upgrade so we can trim our DB.

I am new to LiveReports and was looking for some help in getting some reports to determine where the document sits(a path) and/or who owns it.

Or is there someone who has gone through an updgrade that may have some suggestions on data cleaning

thanks

Craig

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Determine owner of a document
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Craig_Goldsmith

Thanks Colin,

We are using Oracle and this is a great start.

Thanks,

Craig Goldsmith

Systems Analyst, Application Management

Information Management Branch

Corporate Services Division

Phone: 250.415.6273 Fax: 250.356.2643

Please consider the environment before printing this email.

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, September 10, 2014 12:18 PM
To: eLink Recipient
Subject: Re Determine owner of a document

Re Determine owner of a document

Posted by colin.schmidt@mwv.com (Schmidt, Colin) On 09/10/2014 03:17 PM

If you aren't using Oracle, then someone else will have to answer.

select dt.modifydate, Level,

'Enterprise' || SYS_CONNECT_BY_PATH (dt.name, ' | ') "Path", dt.dataid, dt.name

from dtree dt

/* join dtree dt1 on dt.parentid = dt1.dataid and lower(dt1.name) = 'pdf' */

join dtree dt2 on abs(dt.ownerid) = dt2.dataid

where (dt.subtype = 144 and dt.ownerid = -2000)

and to_char(dt.modifydate, 'yyyy-mm-dd') >= '2013-05-29'

CONNECT BY PRIOR dt.dataid = abs(dt.parentid) START WITH dt.parentid = 2000

order by dt.modifydate

Here's one example though not specific to your need.

Colin J.

On Wed, Sep 10, 2014 at 1:39 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Determine owner of a document

Posted by craig.goldsmith@gov.bc.ca (Goldsmith, Craig) On 09/10/2014 01:35 PM

We are looking at upgrading our 9.7.1 environment to 10.x we are looking to do data clean up of our current system before upgrade so we can trim our DB.

I am new to LiveReports and was looking for some help in getting some reports to determine where the document sits(a path) and/or who owns it.

Or is there someone who has gone through an updgrade that may have some suggestions on data cleaning

thanks

Craig

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

This electronic message contains information from MeadWestvaco Corporation or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

[To post a comment, use the normal reply function]
Topic:	Determine owner of a document
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center