Microsoft Dynamics GP Workflow Approval SQL Views: Payables Transactions

Microsoft Dynamics GPThis 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