Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Wednesday, August 19, 2015

Workflow 2.0 Approval Hierarchy - Graphical Representation

Early in the morning, I received a request from a friend of mine; Steve Chapman, inquiring about the capability to extract the approval hierarchy configured within Dynamics GP, workflow 2.0 so that it can be presented "Graphically" as it was available with the old Business Portal - Requisition Management module. The idea is quite tempting and I was tempted enough to to roll my sleeves up as the SQL fun time was about to begin.

The idea was to simplify the data stored within the approval hierarchy in some sort of a child-parent relationship, just like the old fashioned employee-manager quiz we used to have in the university. Theoretically speaking, It must be straight forward to do so, as illustrated int he graph below:

Employee-Manager Hierarchy Relationship

The steps configured within the approval hierarchy do have a parent-child relationship, and this relationship should be prepared in some sort of a staging area, so that further SQL functions can run seamlessly and visualize in a diagram-like result. The table that is directly targeted is WF100003; Workflow Step Table. This table includes all the steps and their associated details such as "Assign to User", "Predecessors"... etc. For that, I created the following view:

CREATE VIEW Vw_WorkflowHierarchy
AS
SELECT  A.ID AS ID ,
        ISNULL(B.ID, 0) AS ParentID ,
        A.WORKFLOW_STEP_NAME AS WorkflowStepName ,
        CASE ISNULL(B.ID, 0)
            WHEN 0 THEN ''
            ELSE 'Assign To: ' + RTRIM(LTRIM(C.ADDomain)) + '\'
                + RTRIM(LTRIM(C.ADDisplayName))
        END AS AssignToDetails ,
        '' EmptyColumn
FROM   
(
SELECT  CONVERT(INT, ROW_NUMBER() OVER ( ORDER BY DEX_ROW_ID )) AS ID ,
        Workflow_Name ,
        WORKFLOW_STEP_NAME ,
        WF_Step_Predecessor ,
        Workflow_Step_Assign_To
FROM    WF100003 AS B
WHERE   WORKFLOW_NAME
              = 'Requstion Management Workflow'
) AS A
LEFT OUTER JOIN
(
SELECT  CONVERT(INT, ROW_NUMBER() OVER ( ORDER BY DEX_ROW_ID )) AS ID ,
        Workflow_Name ,
        WORKFLOW_STEP_NAME ,
        WF_Step_Predecessor ,
        Workflow_Step_Assign_To
FROM    WF100003 AS B
WHERE   WORKFLOW_NAME =
              'Requstion Management Workflow'
) AS B ON A.WF_Step_Predecessor = B.Workflow_Step_Name
LEFT OUTER JOIN WF40200 AS C
ON A.Workflow_Step_Assign_To = C.UsersListGuid
 
Vw_WorkflowHierarchy Result

Data Visualization   Here comes the interesting part !

I remembered an old article which I came through long time ago written by MVP Brad Schulz, he developed a legendary stored procedure (usp_DrawTable) that technically analyzes such relations and populate it graphically through SQL. It is one of the thing that I have read and would never forget as it is definitely a piece of art. So I decided to use his master piece in presenting the approval hierarchy graphically through SQL.The article can be found on this link, and the stored procedure can be found on ups_DrawTable.

After creating the stored procedure, I passed the data stored within the staging into the stored procedure by considering the following script:


IF OBJECT_ID('tempdb..#TreeData', 'U') IS NOT NULL
    DROP TABLE #TreeData
SELECT  ID ,
        ISNULL(ParentID, 0) AS ParentID ,
        WorkflowStepName ,
        AssignToDetails ,
        EmptyColumn
INTO    #TreeData
FROM    Vw_WorkflowHierarchy

           EXEC usp_DrawTree 1


Here is the astonishing result

Graphical Approval Hierarchy - SQL Result

Thanks to Brad for he creative work; good deeds remain and shine. Thanks to Steve as well for the unique inquiry which was absolutely mind-stretching.

Best Regards,
Mahmoud M. AlSaadi

1 comment:

  1. BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you can be to get the new PROGRAMMED blank ATM card that is capable of
    hacking into any ATM machine,anywhere in the world. I got to know about 
    this BLANK ATM CARD when I was searching for job online about a month 
    ago..It has really changed my life for good and now I can say I'm rich and 
    I can never be poor again. The least money I get in a day with it is about 
    $50,000.(fifty thousand USD) Every now and then I keeping pumping money 
    into my account. Though is illegal,there is no risk of being caught 
    ,because it has been programmed in such a way that it is not traceable,it 
    also has a technique that makes it impossible for the CCTVs to detect 
    you..For details on how to get yours today, email the hackers on : (
    atmmachinehackers1@gmail.com ). Tell your 
    loved once too, and start to live large. That's the simple testimony of how 
    my life changed for good...Love you all ...the email address again is ;
    atmmachinehackers1@gmail.com

    ReplyDelete