Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Monday, July 11, 2016

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

1 comment:

  1. Hi Mahmood

    This worked perfect for me. I now just have to also display the PO number link to the REQ.

    Thank you very much.

    Thami

    ReplyDelete