Purchase Requisition Workflow History - SQL View

Purchase requisitions in Dynamics GP with workflow history can be reported through inquiry windows, which provides a thorough details of all the changes/ modifications, approvals of rejections that were applied on the requisition. In this post, I am providing an SQL script that can be used to retrieve these details.

SQL View - Result Set

>>    The script can be downloaded from this link >>>> Download Link

 

/*--------------------------------------------------------------------------

Creation Date: The 9th of July, 2016

Created by: Mahmoud M. AlSaadi

The main purpose of the script is to provide the details of the workflow history 

of purchase requisitions.

Revision No.            RevisionDate    Description

1                       09/07/2016      Original Version

----------------------------------------------------------------------------- */

CREATE VIEW DI_POPReq_WorkflowHistory

AS

    SELECT  ISNULL(C.WfBusObjKey, '') WFBusObjKey ,

            ROW_NUMBER() OVER ( ORDER BY A.DEX_ROW_ID ) RecordSequence ,

           -- A.WorkflowInstanceID ,

        --A.WorkflowStepInstanceID ,

            Workflow_History_User ,

            A.Workflow_Name ,

            A.Workflow_Step_Name ,

            ISNULL(B.WorkflowTaskAssignedTo, '') WorkflowTaskAssignedTo ,

            CASE C.Workflow_Status

              WHEN 1 THEN 'Not Submitted'

              WHEN 2 THEN 'Submitted (Deprecated)'

              WHEN 3 THEN 'No Action Needed'

              WHEN 4 THEN 'Pending User Action'

              WHEN 5 THEN 'Recalled'

              WHEN 6 THEN 'Completed'

              WHEN 7 THEN 'Rejected'

              WHEN 8 THEN 'Workflow Ended (Depricated)'

              WHEN 9 THEN 'Not Activated'

              WHEN 10 THEN 'Deactivated (Depricated)'

              ELSE ''

            END AS WORKFLOW_STATUS ,

            CASE Workflow_Action

              WHEN 1 THEN 'Submit'

              WHEN 2 THEN 'Resubmit'

              WHEN 3 THEN 'Approve'

              WHEN 4 THEN 'Task Complete'

              WHEN 5 THEN 'Reject'

              WHEN 6 THEN 'Delegate'

              WHEN 7 THEN 'Recall'

              WHEN 8 THEN 'Escalate'

              WHEN 9 THEN 'Edit'

              WHEN 10 THEN 'Final Approve'

              ELSE ''

            END AS Workflow_Action ,

            A.Workflow_Due_Date ,

        --A.Workflow_Due_Time ,

            Workflow_Completion_Date ,

        --Workflow_Completion_Time ,

        --Milliseconds ,

            A.DEX_ROW_ID ,

            Workflow_Comments

    FROM    WF30100 AS A

            LEFT OUTER JOIN WFI10004 AS B ON A.WorkflowInstanceID = B.WorkflowInstanceID

            AND A.WorkflowStepInstanceID = B.WorkflowStepInstanceID

            LEFT OUTER JOIN WFI10002 AS C ON C.WorkflowInstanceID = A.WorkflowInstanceID

GO

GRANT ALL ON DI_POPReq_WorkflowHistory TO DYNGRP;

Purchase Requisition Window

Purchase Requisition - Workflow History

SQL View - Data Set

Best Regards, 

Mahmoud M. AlSaadi