Day: August 18, 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 orders.

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

SELECT
	-- order headers
	['Purchase Orders'].PONUMBER
	,['Purchase Orders'].DOCDATE
	,['Purchase Orders'].VENDORID
	-- approval information
	,POApprovals.Workflow_Name
	,POApprovals.Workflow_Step_Name
	,POApprovals.Workflow_History_User
	,POApprovals.Workflow_Approval_Status
	,POApprovals.Workflow_Completion_Date
	,POApprovals.Workflow_Completion_Time
	,POApprovals.Workflow_Comments
	-- order lines
	,['Purchase Order Lines'].ITEMNMBR
	,['Purchase Order Lines'].ITEMDESC
	,['Purchase Order Lines'].QTYORDER
	,['Purchase Order Lines'].UNITCOST
	,['Purchase Order Lines'].EXTDCOST
	,['PM Vendor Master'].VENDNAME
FROM
	(
		SELECT
			PONUMBER
			,DOCDATE
			,BUYERID
			,VENDORID
		FROM
			POP10100 AS ['Purchase Order Work'] WITH (NOLOCK)
		UNION ALL
			SELECT
				PONUMBER
				,DOCDATE
				,BUYERID
				,VENDORID
			FROM
				POP30100 AS ['Purchase Order History'] WITH (NOLOCK)
	)  AS ['Purchase Orders']
INNER JOIN
	(
		SELECT
			PONUMBER
			,ITEMNMBR
			,ITEMDESC
			,QTYORDER
			,UNITCOST
			,EXTDCOST
		FROM
			POP10110 AS ['Purchase Order Lines Work'] WITH (NOLOCK)
		UNION ALL
			SELECT
				PONUMBER
				,ITEMNMBR
				,ITEMDESC
				,QTYORDER
				,UNITCOST
				,EXTDCOST
			FROM
				POP30110 AS ['Purchase Order Lines History'] WITH (NOLOCK)
	) AS ['Purchase Order Lines']
		ON
			['Purchase Order Lines'].PONUMBER = ['Purchase Orders'].PONUMBER
LEFT JOIN
	POApprovals
		ON
			POApprovals.PONUMBER = ['Purchase Orders'].PONUMBER
		AND
			POApprovals.ROW_ID = 1
LEFT JOIN
	PM00200 AS ['PM Vendor Master'] WITH (NOLOCK)
		ON
			['PM Vendor Master'].VENDORID = ['Purchase Orders'].VENDORID
GO

GRANT SELECT ON uv_AZRCRV_POPOrderApprovalStatus TO DYNGRP
GO