Microsoft Dynamics GP Workflow Approval SQL Views: Purchase Orders

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

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

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

Microsoft Dynamics GP Workflow Approval SQL Views: Series Index

Microsoft Dynamics GPA couple of years ago, I posted a SQL view which returned the status of general ledger batches.

I’ve recently been working with a few clients with workflow in Purchasing and a common request was being able to report on the status of documents.

I have three views which were created and which I will post over the next few days. If I create additional ones in future, I’ll tag them into this post.

Microsoft Dynamics GP Workflow Approval SQL Views
Show Workflow Approval Status of posted Microsoft Dynamics GP General Ledger batches
Payables Transactions
Purchase Requisitions
Purchase Orders

Get Serial Number Count and Length in Microsoft Dynamics GP

Microsoft Dynamics GPI needed to check how many serial numbers were assigned to sales transactions and the cumulative length of them. I’m not aware of any function in Microsoft Dynamics GP which would give me this information, so I wrote the below SQL statement to return both values along with the transaction type and document number.

It selects from the Sales Serial/Lot Work and History (SOP10201) table which, allowing for a simple statement, includes both the work and history data (SOP transactions don’t have a status of open).

/*
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). */
SELECT SOP.SOPNUMBE AS 'SOP Number' ,SOP.SOPTYPE AS 'SOP Type' ,SOP.LNITMSEQ AS 'Line Item Sequence' ,COUNT(1) AS 'Serial Number Count' ,SUM(LEN(SOP.SERLTNUM)) AS 'Serial Number Length' FROM SOP10201 AS SOP -- Sales Serial/Lot Work and History (SOP10201) GROUP BY SOP.SOPNUMBE ,SOP.SOPTYPE ,SOP.LNITMSEQ ORDER BY SUM(LEN(SOP.SERLTNUM)) DESC

Fixed Asset Depreciation Not Calculating in Microsoft Dynamics GP

Microsoft Dynamics GPI was on a call with a client the other day looking at a few issues with them in Microsoft Dynamics GP. One of the issues was that they had been setting up the Fixed Asset Management module, but when they ran depreciation, nothing happened.

We did some exploring and the only difference I could find, from who I would have normally configured the module, was that the Fixed Asset Calendar ((Financial » Setup » Fixed Assets » Calendar)) had been configured only for the required years starting in 2020; I usually set it up for a 200 year period.

The first asset being setup was dated in period 12 of 2020 so there is no logical reason why prior years should be needed. However, after we added 2019 into the calendar and re-ran the depreciation, all of the assets depreciated correctly.

Bug With End Of Month Payment Terms in Microsoft Dynamics GP

Microsoft Dynamics GPI was onsite with a client recently and was asked about a problem they were seeing with payment terms. Specifically, the issue was with end of month payment terms where the due date was sometimes being calculated incorrectly. They’d done some testing and identified that it was when the invoice date was the last day of the month that the due date ws incorrect.

I did some testing in Fabrikam and have been able to reproduce this error on all versions of Dynamics GP which I tried.

To reproduce, set a payment term configured with Due set to EOM and Add Days to 30:

Payment Terms Setup of an EOM payment term

Continue reading “Bug With End Of Month Payment Terms in Microsoft Dynamics GP”

Integration Manager Error – “Source Recordset ‘1’ not found’

Microsoft Dynamics GPI was talking to a client on a Teams meeting a while ago and they took the opportunity to ask about a problem they were having with Integration Manager.

When they tried to run a n integration to import Payables Transactions they were receiving an error:

Error message

Integration Manager

The integration cannot be run because of the following problem(s):

- Source Recordset '1' not found - used for Record Source option on collection 'Tax Details'.

The resolution to this is as straightforward as the cause is annoying. Sometimes when you make a change in one part of an integration, a configuration in another part disappears. In this case the Record Source on the options tab of the Tax Details node had been erased. Simply setting the option back to the correct source and saving the integration will fix the issue:

Tax Details node Options

SmartConnect Crashes When Trying to Run Integration into Microsoft Dynamics GP

eOne SolutionsI dealt with a support issue for a client recently where they were reporting that one user could integrate into all but two companies in Microsoft Dynamics GP, but other users couldn’t integrate into any.

The problem is that when they tried an integration which failed, SmartConnect simply crashed with nothing in any error logs in either SmartConnect or Windows.

As it was working in some databases and not others for one user and not at all for other users, it had to be permissions. The user who could use SmartConnect was the one involved in the implementation and the companies he could use it in were the oldest; the two companies which failed were the newest.

We did some checking and found that the user who could run some integrations had his domain account created as a SQL login and had been granted the DYNGRP security role on the company databases; he had not been added to the companies which failed. When we added the security for these databases the integration ran successfully.

We recommended to the client that instead of adding individual users to SQL Server and giving them access to databases they consider creating some AD security groups which had access assigned and they could then grant those roles to users as required.

Recent ISC Software Webinar: A Macro Overview for Microsoft Dynamics GP

ISC Software SolutionsIn our most recent webinar, we took a look at A Macro Overview for Microsoft Dynamics GP. In this webinar, we took a look at what Microsoft Dynamics GP macros are, how they can be used and ran through some examples. If you want to catch up on this, or any other, webinar, you can do so here.

A Macro Overview for Microsoft Dynamics GP

  1. Introduction
  2. What are macros and how to use them
  3. Pitfalls
  4. Recording and Playing Macros
  5. Macro by Mail Merge
  6. Macro by Excel
  7. Macro by SQL
  8. Conclusion

Introduction ^

in this webinar we took a look at macros in Microsoft Dynamics GP, giving an overview of what they are how they can be used and ran through some examples of how they could be used.

I have previously blogged about macros in Dynamics GP and the webinar was along similar lines, albeit using different examples.

Continue reading “Recent ISC Software Webinar: A Macro Overview for Microsoft Dynamics GP”