I am trying to report out on the open signature documents by user and functional area. I am finding this to be a challenging extraction from the database because many of the signature documents shown as "review" status in the gsmSignatureDocs table are old or no longer relevent to the current spec level workflow step. For example, if someone had a signature document in a "spec review" stage but the owner workflowed it back to draft before the signature doc was actioned then workflowed back to a "spec review" stage, the orginal unactioned signature document remains and will remain unactioned. I am only interested in open signature documents in the current review stage of the spec. Any way to pull this data to compile the output I am looking for?
For NPD signature docs, it appears I may be able to leverage the "is voided" field but would need your confirmation if all unactioned items get voided if the activity is workflowed back prior to completion of a signature document. If not, would need a way to do this as well.
Using agile 5.2 DB schema...
About the first question, we can use the column of gsmSignatureDocs.GroupSequence, while current sequence values are stored in specSummary.ActiveSigDocGroupSequence. So a query sql like select * from specSummary s inner join gsmSignatureDocs sig on s.pkid = sig.fkParent and s.ActiveSigDocGroupSequence = sig.GroupSequence
could be used to get all active signature documents.
And the second, yes, number 0 stands for current active signature documents. Please backup your database before running any sql scripts.
Thanks - this was very helpful. For NPD items, I also had to bring in the Activities and Projects tables to filter out deleted activities and projects that were on hold, completed, or terminated. But it seems to be working well for now! Thx