Day: August 16, 2022

This post is part of the Microsoft Dynamics GP Workflow Approval SQL Views series and contains a SQL view to return the workflow approval status of payables transactions.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_PayablesTransactionApprovalStatus', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_PayablesTransactionApprovalStatus
GO
-- create view
CREATE VIEW uv_AZRCRV_PayablesTransactionApprovalStatus AS
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
WITH PMTrxApprovals AS
	(
		SELECT
			['Workflow Instance Master'].WfBusObjKey
			,['Workflow History'].Workflow_History_User
			,['Workflow History'].Workflow_Completion_Date
			,['Workflow History'].Workflow_Completion_Time
			,['Workflow History'].Workflow_Name
			,['Workflow History'].Workflow_Step_Name
			,CASE WHEN ['Workflow History'].Workflow_Action = 1 THEN
				'Submitted'
			WHEN ['Workflow History'].Workflow_Action = 2 THEN
				'Resubmitted'
			WHEN ['Workflow History'].Workflow_Action = 3  THEN
				'Approved'
			WHEN ['Workflow History'].Workflow_Action = 4 THEN
				'Task Complete'
			WHEN ['Workflow History'].Workflow_Action = 5 THEN
				'Rejected'
			WHEN ['Workflow History'].Workflow_Action = 6 THEN
				'Delegated'
			WHEN ['Workflow History'].Workflow_Action = 7 THEN
				'Recalled'
			WHEN ['Workflow History'].Workflow_Action = 8 THEN
				'Escalated'
			WHEN ['Workflow History'].Workflow_Action = 9 THEN
				'Edit'
			WHEN ['Workflow History'].Workflow_Action = 10 THEN
				'Final Approved'
			END as Workflow_Approval_Status
			,['Workflow History'].Workflow_Comments
			,ROW_NUMBER() OVER(PARTITION BY ['Workflow Instance Master'].WfBusObjKey ORDER BY ['Workflow History'].Workflow_Completion_Date DESC, ['Workflow History'].Workflow_Completion_Time DESC) AS ROW_ID
		FROM 
			WF30100 AS ['Workflow History'] WITH (NOLOCK)
		INNER JOIN
			WFI10002 AS ['Workflow Instance Master'] WITH (NOLOCK)
				ON
					['Workflow Instance Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
		WHERE
			['Workflow Instance Master'].Workflow_Type_Name = 'Payables Transaction Approval'
		AND
			['Workflow History'].Workflow_Action <> 11
	)
	
SELECT
	-- transaction information
	['PM Key Master File'].CNTRLNUM AS 'PO Number'
	,['Payables Document Types'].DOCTYNAM AS 'Document Type'
	,['PM Key Master File'].VENDORID AS 'Vendor ID'
	,['PM Vendor Master'].VENDNAME AS 'Vendor Name'
	,['PM Key Master File'].DOCDATE AS 'Document Date'
	-- approval information
	,PMTrxApprovals.Workflow_Name AS 'Workflow Name'
	,PMTrxApprovals.Workflow_Step_Name AS 'Workflow Step Name'
	,PMTrxApprovals.Workflow_History_User AS 'Workflow User'
	,PMTrxApprovals.Workflow_Approval_Status AS 'Workflow Approval Status'
	,PMTrxApprovals.Workflow_Completion_Date AS 'Workflow Completion Date'
	,PMTrxApprovals.Workflow_Completion_Time AS 'Workflow Completion Time'
	,PMTrxApprovals.Workflow_Comments AS 'Workflow_Comments'
FROM
	PM00400 AS ['PM Key Master File'] WITH (NOLOCK)
INNER JOIN
	PM00200 AS ['PM Vendor Master'] WITH (NOLOCK)
		ON
			['PM Vendor Master'].VENDORID = ['PM Key Master File'].VENDORID
INNER JOIN
	PM40102 AS ['Payables Document Types'] WITH (NOLOCK)
		ON
			['Payables Document Types'].DOCTYPE = ['PM Key Master File'].DOCTYPE
LEFT JOIN
	PMTrxApprovals
		ON
			PMTrxApprovals.WfBusObjKey = CAST(RTRIM(['PM Key Master File'].CNTRLNUM) AS VARCHAR(20)) + '~PM_Trxent'
		AND
			PMTrxApprovals.ROW_ID = 1
GO

-- grant permissions
GRANT SELECT ON uv_AZRCRV_PayablesTransactionApprovalStatus TO DYNGRP
GO