How to get the WorkItem and Workflow related information from the Business Object

[adev]

Hi,

I am working on the requirement where I need to generate the database report using database query to get the Workitem information like name, subject, state, status and the  WfProcess information like status of workflow

can any one help me how I can write database query for that

[BineshKumar1 · Accepted solution]

Below is the SQL which I use for change objects, might need a bit of tuning as per your requirement. You can get more details about the workflow data model from the PTC CS https://support.ptc.com/appserver/cs/view/solution.jsp?n=CS153902 and I find it really useful

SELECT A2.classnameA2A2,A2.idA2A2,A2.role C1C1,A2.eventList C1C2,TO_CHAR(A3.deadline,'dd mm yyyy hh24:mi:ss') C1C3,TO_CHAR(A2.modifyStampA2,
'dd mm yyyy hh24:mi:ss') C1C4,TO_CHAR(A7.modifyStampA2,'dd mm yyyy hh24:mi:ss') C1C5 FROM WfProcess A0,ControlBranch A1,WfVotingEventAudit A2,WfAssignedActivity A3,
WTChangeActivity2 A4,WTChangeActivity2Master A5,WTChangeOrder2 A6,WorkItem A7,WfAssignment A8,IncludedIn2 A9,ActivityAssignmentLink A10,WorkItemLink A11 WHERE (((A0.wtkey = A2.processKey)
AND (A3.wtkey = A2.activityKey) AND (A0.businessObjReference = ('VR:wt.change2.WTChangeActivity2')||(A1.idA2A2)))) AND (((A9.branchIdA3A5 = A6.branchIditerationInfo) AND (A6.latestiterationInfo = 1))
AND ((A9.branchIdA3B5 = A4.branchIditerationInfo) AND (A4.latestiterationInfo = 1))
AND (A10.idA3A5 = A3.idA2A2) AND (A10.idA3B5 = A8.idA2A2) AND (A11.idA3A5 = A8.idA2A2) AND (A11.idA3B5 = A7.idA2A2) AND (A4.idA3masterReference = A5.idA2A2) AND (A1.idA3B5 = A5.idA2A2))

Thank you,

Binesh Kumar

[adev]

Thank you Binesh,

this script works