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...

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

2 comments:

  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
  2. 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