Oracle Commands

[gkemner]

Can someone help me with the oracle command line prompts in order to
accomplish the following in Windchill 9.0?

I need to generate a list all objects in the database that meet the
following criteria:
Iteration = LATEST
Revision = LATEST
State = RELEASE

I am trying to write a script to generate this list on a regular basis
so I have to figure out the command prompt commands to use. I know how
to log into Sqlplus from the Windchill shell but it is the commands that
I need after that that has me stumped at the moment.

Thanks!

[MikeLockwood]

All options built into OTB search, and available to regular users.

[gkemner]

Thanks for the suggestion. Yes, I have already been doing that.
Generating the list manually via the search functionality, however
frequently the export to file process crashes for unknown reasons. Now
I am trying to automate that process and develop a more reliable method
for generating the list by writing a script. Any other thoughts or
suggestions would be appreciated.



Thanks!



From: Lockwood,Mike,IRVINE,R&D [

[sdrzewiczewski]

The suggested method would be to build it using Query Builder. From
there you can view the SQL

Sent from my iPhone

On Mar 9, 2010, at 2:45 PM, "Kemner, Greg (AS)" <->
wrote:

> Thanks for the suggestion. Yes, I have already been doing that.
> Generating the list manually via the search functionality, however
> frequently the export to file process crashes for unknown reasons.
> Now I am trying to automate that process and develop a more reliable
> method for generating the list by writing a script. Any other
> thoughts or suggestions would be appreciated.
>
>
>
> Thanks!
>
>
>
> From: Lockwood,Mike,IRVINE,R&D [

[BrianAggen]

Greg --

Here's a sql query for pulling information from WTParts, WTDocs and EPMDocs
with a RELEASED state. Hope this gives you an idea for what you might be
trying to do.

(select a.wtpartnumber as NUM, b.versionida2versioninfo as REV,
b.iterationida2iterationinfo as ITER, b.statestate as STATE, b.classnamea2a2
as TYP
from wtpartmaster a, wtpart b
where b.statestate='RELEASED' and b.ida3masterreference=a.ida2a2)
union
(select a.wtdocumentnumber as NUM, b.versionida2versioninfo as REV,
b.iterationida2iterationinfo as ITER, b.statestate as STATE, b.classnamea2a2
as TYP
from wtdocumentmaster a, wtdocument b
where b.statestate='RELEASED' and b.ida3masterreference=a.ida2a2)
union
(select a.documentnumber as NUM, b.versionida2versioninfo as REV,
b.iterationida2iterationinfo as ITER, b.statestate as STATE, b.classnamea2a2
as TYP
from epmdocumentmaster a, epmdocument b
where b.statestate='RELEASED' and b.ida3masterreference=a.ida2a2);

Regards,
On Tue, Mar 9, 2010 at 12:23 PM, Kemner, Greg (AS) <->wrote:

> Can someone help me with the oracle command line prompts in order to
> accomplish the following in Windchill 9.0?
>
> I need to generate a list all objects in the database that meet the
> following criteria:
>
> Iteration = LATEST
>
> Revision = LATEST
>
> State = RELEASE
>
> I am trying to write a script to generate this list on a regular basis so I
> have to figure out the command prompt commands to use. I know how to log
> into Sqlplus from the Windchill shell but it is the commands that I need after
> that that has me stumped at the moment.
>
> Thanks!
>

[BrianAggen]

Greg --

Getting the modified user information adds a little more complexity to the
query, but it should look something like this.

select a.wtpartnumber as NUM, b.versionida2versioninfo as REV,
b.iterationida2iterationinfo as ITER, b.statestate as STATE, b.classnamea2a2
as TYP*, c.name as USR
*from wtpartmaster a, wtpart b*, wtuser c*
where b.statestate='RELEASED' and b.ida3masterreference=a.ida2a2 *and
b.ida3d2iterationinfo=c.ida2a2*;

I basically just added what's in *bold* to one of the original queries I
sent you.

Glad to help.


On Wed, Mar 17, 2010 at 2:38 PM, Kemner, Greg (AS) <->wrote:

> Thank you so much. This was extremely helpful. One more question. Any
> idea how I would add who that iteration was Modified By?
>
>
>
> Thanks again.
>
>
>
>
>
> *From:* Brian Aggen [