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
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
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
