Microsoft Dynamics GP-Workflow-Genehmigungs-SQL-Ansichten: Bestellanforderungen

Dieser Beitrag ist Teil der Microsoft Dynamics GP-Workflow-Genehmigungs-SQL-Ansichten-Serie und enthält eine SQL-Ansicht, um den Workflow-Genehmigungsstatus von Bestellanforderungen zurückzugeben.

-- 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

Lesen Sie den ursprünglichen Beitrag Microsoft Dynamics GP Workflow Approval SQL Views: Purchase Requisitions at azurecurve|Ramblings of an IT Professional

.

Author: admin

Leave a Reply

Your email address will not be published.