Workflow Tab data using sql

All, I am trying to pull data for the workflow tab for all change objects, have anyone here developed a SQL to extract data.   Thanks in advance Ridhi

Agile User Asked on November 26, 2019 in Agile PLM (v9).
Add Comment
1 Answer(s)

What is shown in the Workflow tab comes from the SIGNOFF and WORKFLOW_PROCESS tables. WORKFLOW_PROCESS shows the stages of the workflow that is being used by the change, and how things can progress (although not completely). SIGNOFF shows all of the folks that have commented on/approved/rejected things during the workflow processing.

CHANGE.PROCESS_ID has the ID from WORKFLOW_PROCESS that is tied to the current status of the change, where CHANGE.ID = WORKFLOW_PROCESS.CHANGE_ID and CHANGE.WORKFLOW_ID = WORKFLOW_PROCESS.WORKFLOW_ID.

SIGNOFF lists who/when someone signed off on the workflow, as well as what their response was and if it was required, where CHANGE.ID – SIGNOFF.CHANGE_ID and SIGNOFF.PROCESS_ID = WORKFLOW_PROCESS.ID (showing which status this happened at). In addition, SIGNOFF.HISTORY_ID links to the CHANGE_HISTORY record showing what happened.

There can be MANY duplicate records between the 4 tables involved here, and it is not an easy task to figure out what should be there. But if you get the ordering correct, and only use the set of WORKFLOW_PROCESS records that include the ID in CHANGE.PROCESS_ID for each change, you should be good. A lot of things you will get from NODETABLE, like status values, the workflow name and the signoff_status. However, signoff_status also uses some hard-coded values 1-10 that are NOT in a list but merely defined (list “Action List” shows most of them, but not all).

I start with the following query :

SELECT ID, WORKFLOW_ID, STATE, CHANGED_BY, CHANGED_ON, LOCAL_DATE, STATE_CODE, CHANGED_BY_USERNAME, STATUSTYPE
FROM WORKFLOW_PROCESS WHERE CHANGE_ID = chngID
 ORDER BY CHANGED_ON DESC, ORDER_BY ASC;

And then for each workflow record returned that is not a duplicate (see below), check to see what SIGNOFF and CHANGE_HISTORY have for that record :

SELECT A.ID, A.USER_NAME_ASSIGNED, A.REQUIRED, A.Signoff_STATUS, A.USER_NAME_SIGNED,
       TO_Char(null), TO_Char(null), TO_Char(null), TO_Char(null), TO_Char(null)
  FROM Signoff A, CHANGE_HISTORY B
 WHERE A.PROCESS_ID = wfpID AND (A.Signoff_HISTORYID IS NULL OR A.Signoff_HISTORYID = 0) AND A.HISTORY_ID = B.ID
   AND A.Signoff_STATUS NOT IN (6, 9)
   AND (A.REQUIRED = 1 OR A.REQUIRED = 2 OR A.REQUIRED = 5 OR A.REQUIRED = 6 OR A.REQUIRED = 13 OR A.REQUIRED=14 OR A.REQUIRED=15)
UNION ALL

SELECT A.ID, A.USER_NAME_ASSIGNED, A.REQUIRED, A.Signoff_STATUS, A.USER_NAME_SIGNED,
       C.COMMENTS, TO_Char(B.TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS'), TO_Char(B.LOCAL_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_Char(C.TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS'), TO_Char(C.LOCAL_DATE, 'YYYY-MM-DD HH24:MI:SS')
  FROM Signoff A, CHANGE_HISTORY B, CHANGE_HISTORY C
 WHERE A.PROCESS_ID = wfpID AND (A.Signoff_HISTORYID IS not NULL AND A.Signoff_HISTORYID != 0)
   AND A.HISTORY_ID = B.ID AND A.Signoff_HISTORYID = C.ID AND A.Signoff_STATUS NOT IN (6, 9)
   AND (A.REQUIRED = 1 OR A.REQUIRED = 2 OR A.REQUIRED = 5 OR A.REQUIRED = 6 OR A.REQUIRED = 13 OR A.REQUIRED=14 OR A.REQUIRED=15)
ORDER BY 4, 3, 2;

You also need to make sure you have no duplicates using the first query over workflow_id, changed_by and changed_on, as well as making sure that state_code is an even number (0=Current process, 2=Process moved Forward, 4=Skipped – look at “Status Code List”). The list for status type is “Status Type Selection”, and the list for Required seems to be odd = “Yes” and even = “No” (I suspect that these are again constants somewhere).

Bottom line, this can be a hot mess with lots of things that are not easily found. and getting both the ordering and inclusion correct isn’t easy. What I have shown above is where I am in extracting workflow history (basic history tab data is easy in comparison). I am still working on getting it to 100%, but keep finding weird stuff.

Agile Angel Answered on November 26, 2019.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.