Skip to main content
A
AshleyHolmes1-Visitor
1-Visitor
March 14, 2019
Solved

SQL queries in Windchill for Task History

  • March 14, 2019
  • 2 replies
  • 3676 views

Can anyone help me with my SQL query?

 

I have a query built that integrates several tables in order to report on workflow items and document lifecycle: WTUser, WorkItem, PDMLinkProduct,EPMDocument, etc

I have a bunch of reportable information gathered, but I am missing one key piece - the date a workflow task was accepted. I have workflow created date, I have task completion date, I have the who and the what and the where, but I do not have workflow task accepted date.

 

We discovered that this information is stored in WfAssignmentEventAudit under .timestamp when .actionPerformed=Accepted, but I am unable to get this data table into my query successfully with a JOIN to WfAssignedActivity or or creating a subset, etc.

 

Any SQL experts out there willing to take a look at how to join up these related tables?

Best answer by AshleyHolmes

We found a connection for WfAssignmentEventAudit wfaea and WfAssignedActivity wfaa on

wfaea.activityKey = wfaa.wtkey

 

and used

wfaa.startTime  -- when the task became available in the grp inbox
wfaea.timestamp -- when the task was ACCEPTED where userID is !=null and .actionPerformed = accepted

and
wfaa.endTime  -- when the task was completed

2 replies

Marco Tosin
21-Topaz I
Marco Tosin21-Topaz I
21-Topaz I
March 14, 2019

Take a look at this article:

Windchill Data Model for Workflow related objects

https://www.ptc.com/en/support/article?n=CS153902

 

 

Marco
A
AshleyHolmes1-VisitorAuthorAnswer
1-Visitor
March 21, 2019

We found a connection for WfAssignmentEventAudit wfaea and WfAssignedActivity wfaa on

wfaea.activityKey = wfaa.wtkey

 

and used

wfaa.startTime  -- when the task became available in the grp inbox
wfaea.timestamp -- when the task was ACCEPTED where userID is !=null and .actionPerformed = accepted

and
wfaa.endTime  -- when the task was completed

Terms & ConditionsCookie settingsAccessibility statement