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
