Database table in which WTPart or Change activity Maturity History is stored.

[nmahajan-2]

I need to pull the date on which a WTPart was in inwork state or a CN was in published state. I did my analysis and found that there should be a Maturity history table in database, but i ended up with a table called MaturityBaseline table which does not hold this information. I need guidance on which table this information is stored. Even in API com.ptc.windchill.enterprise.history.HistoryTablesCommands.maturityHistory(wtObject); so it is expect there should be a Maturity history table in database.

[BenPerry]

Perhaps in fact you're looking for the LIFECYCLEHISTORY table? Here are some screenshots I have of a report for a soft type WTPart. I can't "View SQL". I think is is because my FROM clause contains a soft type, not an OOTB type. This is from 9.1.

[MikeLockwood]

Reporting WHEN various events happen is a key need for almost every Windchill system - but is a bit challenging with the reporting provided.

Need to be very precise in defining what is desired:

- When the product data (e.g. Drawing) changes state

- When a related change object (Request, Notice, Task) changes state

- When a related change object workflow task is completed

All state changes are in the LIFECYCLEHISTORY table and can be accessed via query builder by joining Lifecycle History with the Object History association. Need to add to the Criteria Event = Enter_Phase (constant). Workflow task results are separate - they are in WFVOTINGEVENTAUDIT generally.

A bit more challenging to build relationships from the product data >> related change object >> state change of the change object.

Almost everyone has the need to report for example for the last month / quarter / year metrics like "how long did it take on average to get from a submitted change request to an approved change request" or "how long did it take from having an checked Change Task until the product data was actually brought to Released."

[nmahajan-2 · Accepted solution]

Hello,

Here is the database query i created. This is for Change Notice Lifecycle State (Desing Approval).

And Yes the Maturity History is stored in Lifecycle History table for all WT Objects. OBJECTHISTORY table is the link between Lifecycle History and WT Obj tables, in my case it was Change Order.

select lf.*

from wcadmin.WTCHANGEORDER2 ch left outer join wcadmin.WTCHANGEORDER2MASTER chm

on ch.IDA3MASTERREFERENCE = chm.IDA2A2

left outer join wcadmin.OBJECTHISTORY obj on obj.IDA3A5 = ch.IDA2A2

left outer join wcadmin.LIFECYCLEHISTORY lf on lf.IDA2A2=obj.IDA3B5

where chm.WTCHGORDERNUMBER = 'XXXXXXXX123'

and lf.state='DESIGNAPPD'

and lf.action='Set_State';

Thanks for replying, really appreciate your feedback.

[JeffZemsky]

Is there a reason you did this in SQL versus Query Builder? It is much more straight forward to do in Query Builder then to try to reverser engineer the SQL.

[BenPerry]

Jeff,

I won't speak for everyone, and certainly not for Nikhil. But I just wanted to contribute my thoughts about this.

I often like to "View SQL" if it is not greyed out when the search results will be large. Perhaps I'm doing some type of large information dump for administrative purposes. Data migrations or synchronizations/comparisons with ERP system.

The reason for this is to bypass the querylimits that are set through the UI. This is the problem I've encountered with 9.1. I'm not sure if it is any different in 10x, as far as being able to bypass querylimits for admins.

[nmahajan-2]

My requirement was to pull this info through a database link to Production database. Later on this information will be used by a third party application.