Microsoft Dynamics GP Workflow Approval SQL Views: Purchase Requisitions

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 purchase requisitions.

-- drop view if it exists
	DROP VIEW uv_AZRCRV_POReqApprovalStatus
-- create view
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional ( 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