Day: August 17, 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 purchase requisitions.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_POReqApprovalStatus', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_POReqApprovalStatus
GO
-- create view
CREATE VIEW uv_AZRCRV_POReqApprovalStatus 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 PRApprovals AS
	(
		SELECT
			['Workflow Instance Master'].WfBusObjKey AS PRRequisitionNumber
			,['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 = 'Purchase Requisition Approval'
		AND
			['Workflow History'].Workflow_Action <> 11
	)

SELECT
	-- requisition headers
	['Purchase Requisitions'].POPRequisitionNumber
	,['Purchase Requisitions'].DOCDATE
	,['Purchase Requisitions'].RequisitionDescription
	,['Purchase Requisitions'].REQSTDBY
	-- approval information
	,PRApprovals.Workflow_Name
	,PRApprovals.Workflow_Step_Name
	,PRApprovals.Workflow_History_User
	,PRApprovals.Workflow_Approval_Status
	,PRApprovals.Workflow_Completion_Date
	,PRApprovals.Workflow_Completion_Time
	,PRApprovals.Workflow_Comments
	-- requisition lines
	,['Purchase Requisition Lines'].ITEMNMBR
	,['Purchase Requisition Lines'].ITEMDESC
	,['Purchase Requisition Lines'].QTYORDER
	,['Purchase Requisition Lines'].UNITCOST
	,['Purchase Requisition Lines'].EXTDCOST
	,['Purchase Requisition Lines'].VENDORID
	,['PM Vendor Master'].VENDNAME
FROM
	(
		SELECT
			POPRequisitionNumber
			,DOCDATE
			,RequisitionDescription
			,REQSTDBY
		FROM
			POP10200 AS ['Purchase Requisition Work'] WITH (NOLOCK)
		UNION ALL
			SELECT
				POPRequisitionNumber
				,DOCDATE
				,RequisitionDescription
				,REQSTDBY
			FROM
				POP30200 AS ['Purchase Requisition History'] WITH (NOLOCK)
	)  AS ['Purchase Requisitions']
INNER JOIN
	(
		SELECT
			POPRequisitionNumber
			,ITEMNMBR
			,ITEMDESC
			,QTYORDER
			,UNITCOST
			,EXTDCOST
			,VENDORID
		FROM
			POP10210 AS ['Purchase Requisition Lines Work'] WITH (NOLOCK)
		UNION ALL
			SELECT
				POPRequisitionNumber
				,ITEMNMBR
				,ITEMDESC
				,QTYORDER
				,UNITCOST
				,EXTDCOST
				,VENDORID
			FROM
				POP30210 AS ['Purchase Requisition Lines History'] WITH (NOLOCK)
	) AS ['Purchase Requisition Lines']
		ON
			['Purchase Requisition Lines'].POPRequisitionNumber = ['Purchase Requisitions'].POPRequisitionNumber
LEFT JOIN
	PRApprovals
		ON
			PRApprovals.PRRequisitionNumber = ['Purchase Requisitions'].POPRequisitionNumber
		AND
			PRApprovals.ROW_ID = 1
LEFT JOIN
	PM00200 AS ['PM Vendor Master'] WITH (NOLOCK)
		ON
			['PM Vendor Master'].VENDORID = ['Purchase Requisition Lines'].VENDORID
GO

GRANT SELECT ON uv_AZRCRV_POReqApprovalStatus TO DYNGRP
GO