Deltek Vision Workflow Dependencies

Workflow dependent stored procedures in Deltek Vision:

The other day a client pointed out to me that one of their custom fields was not being updated with the correct accounting information.  This custom field displays the amount of cash receipts for a particular project.  

I knew that this field is updated using stored procedures written either by myself or their recently departed full time programmer.  I also knew the name of the field.  I didn’t know which stored procedure was updating this field, nor did I know which workflows were calling the procedure.
The process of going about this led me to develop a new code snippet that many of us Deltek Vision programmers would probably find useful.
Here is what happened:

1. The first thing I had to do was figure which stored procedure(s) were updating this field.
2. The second thing I had to do was figure out where it was used.
3. The third thing I had to do was fix it.
My plan was to re-write the stored procedure with a different name, then go in and update the existing workflows with the new stored procedure.
Finding the stored procedure wasn’t that hard… I knew what fields were being updated (a custom field on the general tab) so I used my trusty script to find stored procedures that that update a field…  it looks something like this:


/* in this case the field “feebillings” is being searched for */

set nocount on
  
declare @ProcName nvarchar(100) 
declare @ProcSortOrder int
declare @MyCursor CURSOR   
declare @ProcUser varchar(100)

 select @ProcUser = ‘sa’

   declare @StoredProcs TABLE
   (
     SortOrder int,
     ProcedureName varchar(100),
     ProcedureCode varchar(7500)
   )

 Insert Into @StoredProcs
  select 0,upper(SysObjects.Name),SysComments.Text
   from SysObjects,SysComments   
  where SysObjects.type=’P’
    and (SysObjects.Category = 0)
    and (SysObjects.ID = SysComments.ID) 
 and sysComments.text like ‘%feebillings%’
    order by SysObjects.Name ASC
 
set nocount off

SET @MyCursor = CURSOR FAST_FORWARD 
FOR 
select ProcedureName,
        SortOrder =  (select count(*)
                        from @StoredProcs B
                        WHERE (A.ProcedureName B.ProcedureName)
                          and (REPLACE(UPPER(B.ProcedureCode),B.ProcedureName,”)
                               LIKE ‘%’ + upper(A.ProcedureName) + ‘%’)
                      )
    from @StoredProcs A 
 group by procedurename
    order by SortOrder Desc
   
OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO @ProcName,@ProcSortOrder 

   WHILE @@FETCH_STATUS = 0 
   BEGIN 

      PRINT ‘if exists (select * from dbo.sysobjects ‘
      PRINT ‘ where id = object_id(N’ + char(39) + ‘[dbo].[‘ + @ProcName + ‘]’ + char(39) + ‘)’
      PRINT ‘ and OBJECTPROPERTY(id, N’ + char(39) + ‘IsProcedure’ + char(39) + ‘) = 1) ‘
      PRINT ‘ drop procedure ‘ + @ProcName  
      PRINT ‘ GO ‘
      PRINT ‘ SET QUOTED_IDENTIFIER OFF ‘
      PRINT ‘ GO ‘
      PRINT ‘ SET ANSI_NULLS OFF ‘ 
      PRINT ‘ GO’
      exec sp_helptext @ProcName
      PRINT ‘ GO ‘
      PRINT ‘ SET QUOTED_IDENTIFIER OFF ‘
      PRINT ‘ GO ‘
      PRINT ‘ SET ANSI_NULLS ON ‘ 
      PRINT ‘ GO ‘
      PRINT ‘ GRANT  EXECUTE  ON [dbo].[‘ + @ProcName + ‘]  TO [‘ + @ProcUser + ‘]’
      PRINT ‘ GO ‘

   /*   PRINT @ProcName + ‘  ‘ + cast(@ProcSortOrder as varchar(20)) */
      FETCH NEXT FROM @MyCursor 
      INTO @ProcName,@ProcSortOrder   
   END 

CLOSE @MyCursor 
DEALLOCATE @MyCursor         
————————————- end ———————–
The second part, find out where this stored procedure was called…
I could have searched the workflowactionsproc table for the stored procedure name, but that wouldn’t have helped me much because I needed more information than that.  I needed to be able to locate the workflows through the workflow interface in order to properly change them and update their params.
So… I wrote a script that actually does this:
1. lists out workflow events, actions
2. writes out the stored procedure script
It lists each stored procedure only once, but shows ALL active workflows that call the stored procedure along with identifying information that will allow you to locate the workflow using the standard Deltek Vision workflow interface.
The output of this batch looks like this: 
(this is just the first part…)


 
———– header(s) ————-
/*

Workflow type: ProjectICBO 
Description:  01.01 update billing/receipts amount
Action Description: fmv_pr_update_acutal_receipts
Procedure Name: fmv_pr_update_actual_receipts
*/
 
———– header(s) ————-
/*

Workflow type: ProjectICBO 
Description:  01.01 update billing/receipts amount
Action Description: fmv_pr_update_acutal_receipts
Procedure Name: fmv_pr_update_actual_receipts
*/
 
———– text —————
Text

CREATE  proc [dbo].[pr_update_actual_receipts]
(@wbs1 varchar(32))
as
begin

( continued…)
Notice how in this case the particular stored proc is called in two places…
So I successfully located the stored procedures that I wanted, then set about to finding the root cause of the issue and re-writing the stored proc entirely.
When I am ready to replace the call to the old stored proc with the new one I wrote, I know exactly which workflows to go look at.
If you want the script I used to generate this output shoot me an email and I’ll send the scrip to you for free.
Have a great day!
Loren Saunders

3 thoughts on “Deltek Vision Workflow Dependencies

  1. While working with Deltek on another issue, I think that they have stated that there isn't a guaranteed order in which the workflows fire. This post is great for helping users understand the spider web of workflows in Vision.

  2. You are correct in saying that there is not rhyme or reason to what order the workflows or workflow actions fire (procedures etc.). If you need to control work flow order it's best to do it in one master stored procedure that invokes other stored procedures.

Comments or questions for Loren?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s