Windchill Relationtionship Report SQL SELECT

[gchampoux]

I would like to produce a Windchill relationship text-file report for all Pro/E CAD Documents (epmdocument), using an SQL SELECT statement (Oracle).

The output would include the following info:

DOCTYPE, NUMBER, Version, relationship-type, Related-DOCTYPE, Related-NUMBER, Related Version

Example output would be something like this:

ProE Versioned Part,12345.PRT,A.1,Associated Drawing,ProE Versioned Drawing,12345.DRW,B.5

I am slightly familiar with the tables epmdocumentmaster and epmdocument.
But I don't know which table (and fields) contains the relationship (where-used) data.

Any suggestions?

Windchill 9.1

Gerry Champoux

Williams International

Walled Lake, MI

[BineshKumar1]

For ASM to Part , use EPMMemberLink

select DISTINCT epm.cadname as "parent",epml.asstoredchildname as "child" from EPMMemberLink epml, epmdocumentmaster epm, epmdocument epmd

where epml.ida3a5=epmd.ida2a2 and epmd.ida3masterreference=epm.ida2a2 order by epm.cadname;

For Part,ASM to Drawing, use EPMReferenceLink

select DISTINCT epm.cadname as "parent",epml.asstoredchildname as "child" from EPMMemberLink epml, epmdocumentmaster epm, epmdocument epmd

where epml.ida3a5=epmd.ida2a2 and epmd.ida3masterreference=epm.ida2a2 order by epm.cadname;

Regards

Binesh Kumar

Medtronic MITG

[gchampoux]

Binesh,

Thank you!

Before I try these, I need a clarification:

Both select statements appear to be identical.
In the second select statement, you said that I should use EPMReferenceLink.
Is that all I need to change? Is that the only difference needed?

Gerry

[BineshKumar1]

My bad, Gerry. I am sorry. I thought I changed it before I pasted it. In the second query just change the table to EPMReferenceLink/.

The relationship remains the same.

Regards

Binesh

[gchampoux]

Binesh,

Thanks again.

How do I include the version (revision.iteration) info in the output for both epmdocuments?

Gerry

[BineshKumar1]

Hello Gerry,

epmd.VERSIONIDA2VERSIONINFO||'.'||epmd.ITERATIONIDA2ITERATIONINFO is where you can get the revision.iteration. They are stored in epmdocument table

Thank you

Binesh Kumar

[bclark-2]

Hello,

I am trying to the exact same thing as OP, but it seems that something is missing in the solution here. EPMReferenceLink gives you the link between the EPMDocument (Drawing) and the EPMDocumentMaster (Part), which means that you can find which exact version and iteration of the Drawing relates to a part, but not the exact version and iteration of that part.

Am I missing something here?