Debugging Integration Manager Crashes

Microsoft Dynamics GPWe’ve had two separate clients recently log calls where Integration Manager was crashing while performing an integration.

The first client was doing an Inventory Items integration on Microsoft Dynamics GP 2015 R2 and the other was doing a Sales Transaction integration on Microsoft Dynamics GP 2016 R2.

Both of the integrations had been running fine for quite a long period of time before starting to crash; no changes had been made to either system and I’ve not been able to determine why Integration Manager (IM) has started crashing.

However, by the use of two ini file switches, we were able to determine what error was being encountered by IM resulted in a crash.

The two switches are already present in the ini file (C:\Program Files (x86)\Microsoft Dynamics\Integration Manager nn/Microsoft.Dynamics.GP.IntegrationManager.ini by default) whre the False value needs to be changed to True:

[IMGPPrv]
ShowDynamics=False
DoUIRedraw=False

When these switches are set to True, the Microsoft Dynamics GP windows are not hidden when the integration runs.

We found that for the Inventory Items integration, accounts either didn’t exist, didn’t allow account entry or were inactive; for the Sales Transaction integration, Analytical Accounting (AA) had been enabled for one of the accounts and the integration didn’t handle AA.

I created a VB script for the Inventory Items integration to check the accounts in the Before Document script and cancel the document if the account was going to be a problem.

Hands On With MDGP 2018 RTM New Features: Display Sales Hold Status

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

The sixteenth feature of the day, is Display Sales Hold Status, which saw the introduction of a hold flag to the Sales Transaction Entry window (Sales area page >> Transactions >> Sales Transaction Entry):

Sales Transaction Entry

Continue reading “Hands On With MDGP 2018 RTM New Features: Display Sales Hold Status”

SQL Script To Verify Sales Invoice Extended Cost Against Subtotal

Microsoft Dynamics GPThis script is the result of a support call logged by a client where the incorrect value on sales invoices was being invoiced to customers. There was a small number of invoices being created which were showing the incorrect value; there was concern that the issue might be wider than thought, so I wrote this script to verify the sum of the Extended Cost of the lines on an invoice against the Subtotal.

This script is configured to check invoices, but could be used against other transaction types if the highlighted section is changed.

IF OBJECT_ID (N'uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal
GO
CREATE VIEW uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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
	['Sales Transaction Work'].SOPNUMBE
	,FORMAT(['Sales Transaction Work'].DOCDATE, 'yyyy-MM-dd') AS DOCDATE
	,['Sales Transaction Amounts Work'].XTNDPRCE
	,['Sales Transaction Work'].SUBTOTAL
	,'OPEN' AS TRXSTATUS
FROM
	SOP10100 AS ['Sales Transaction Work']
INNER JOIN 
	(SELECT
		SOPNUMBE
		,SOPTYPE
		,SUM(XTNDPRCE) AS XTNDPRCE
	FROM
		SOP10200
	WHERE
		SOPTYPE = 3 --invoice
	GROUP BY
		SOPNUMBE,SOPTYPE) AS ['Sales Transaction Amounts Work']
			ON
				['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE
			AND
				['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE
WHERE
	['Sales Transaction Amounts Work'].XTNDPRCE <> ['Sales Transaction Work'].SUBTOTAL
UNION ALL
	SELECT
		['Sales Transaction History'].SOPNUMBE
		,FORMAT(['Sales Transaction History'].DOCDATE, 'yyyy-MM-dd') AS DOCDATE
		,['Sales Transaction Amounts History'].XTNDPRCE
		,['Sales Transaction History'].SUBTOTAL
		,'HIST' AS TRXSTATUS
	FROM
		SOP30200 AS ['Sales Transaction History']
	INNER JOIN 
		(SELECT
			SOPNUMBE
			,SOPTYPE
			,SUM(XTNDPRCE) AS XTNDPRCE
		FROM
			SOP30300
		WHERE
			SOPTYPE = 3 --invoice
		GROUP BY
			SOPNUMBE,SOPTYPE) AS ['Sales Transaction Amounts History']
				ON
					['Sales Transaction Amounts History'].SOPNUMBE = ['Sales Transaction History'].SOPNUMBE
				AND
					['Sales Transaction Amounts History'].SOPTYPE = ['Sales Transaction History'].SOPTYPE
	WHERE
		['Sales Transaction Amounts History'].XTNDPRCE <> ['Sales Transaction History'].SUBTOTAL
GO
GRANT SELECT ON uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal TO DYNGRP
GO

The view can easily be plugged into SmartList Designer, SmartList Builder, a refreshable Excel Report, a SQL Server Reporting Services report or any other type of reporting tool.

Dynamics GP Tech Conference 2017: Optimised Financials

Microsoft Dynamics GPThis post is part of the series I am doing on the Dynamics GP Tech Conference 2017.

The Financials modules have some enhancements coming which should be of benefit:

  • Copy user access across AA transaction dimension codes – this should make maintaining AA dimension code access easier.
  • Email single statement from customer card and enquiry – handy when you only want to send a single statement to the customer on the phone, rather than to a range of them.
  • PO Generator opens PO List with the new PO’s filter.
  • PO Number display on Requisition List for originator – getting easy access to the PO number for PR orinators will be a good new addition; it will save me writing SmartLists for everyone based off this view.
  • Print Purchase Requisition – I have mixed opinions. Workflow can handle the sending of requisition details to an approver anyway, and I can’t really think of another reason to output one?
  • Display Hold status on Sales Transaction Entry – more information is never a bad thing.
  • One check per invoice settings – allows for more granular control than the setting in the Select Payments window.
  • Save Select Payments settings – are you tired of having to configure your payment run manually every time? Well, Microsoft’s got you covered with this feature.

Click to show/hide the Dynamics GP Tech Conference 2017 Series Index

Hands On With Microsoft Dynamics GP 2016 R2: Display Tax Percent for Historical Sales Transactions

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components and also look at the new functionality introduced; the index for this series can be found here.

In this post, I’m going “hands on” with the sixth of the Microsoft Dynamics GP 2016 R2 Feature of the Day posts, Display Tax Percent for Historical Sales Transactions.

To test this feature, I created a new Tax Detail in Tax Detail Maintenance (Administration >> Setup >> Company >> Tax Details) for a Sales series Standard Rated UK VAT at 20% and created a related Tax Schedule:

Tax Detail Maintenance

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: Display Tax Percent for Historical Sales Transactions”

Integration Manager Sales Order Integration – “The Sales Intrastat Entry window is open”

Microsoft Dynamics GPJust before Christmas I was creating an integration for a client in Integration Manager which would integrate Sales Orders, but unfortunately I was receiving an error message:

Integration Log
Integration: Eriks SO Integration (ID: 8)
Action: None
Start Time: 23/12/2016 11:05:38

  23/12/2016 11:05:38 Source: IIntegrationEngine_Run, Status Code: 0    Opening source query...
  23/12/2016 11:05:38 Source: IIntegrationEngine_Run, Status Code: 0    Initializing Source...
  23/12/2016 11:05:38 Source: IIntegrationEngine_Run, Status Code: 0    Establishing source record count...
  23/12/2016 11:05:38 Source: IIntegrationEngine_Run, Status Code: 0    Beginning integration...
1:  Order  Insert Failed 5. Seconds 
    DOC 1 ERROR: The Sales Intrastat Entry window is open. - The Sales Intrastat Entry window is open.
  23/12/2016 11:05:43 Source: FinishIntegration, Status Code: 3    Integration Failed
  23/12/2016 11:05:43 Source: FinishIntegration, Status Code: 3    Integration Results
    1 documents were read from the source query.
    1 documents were attempted:
        0 integrated without warnings.
        0 integrated with warnings.
        1 failed to integrate.

Finish Time: 23/12/2016 11:05:43

Source Total: 1
Successfully Integrated: 0
Integrated With Warning: 0
Failed: 1

Completion Status: Completed
Integration Status: No Documents Succeeded

Continue reading “Integration Manager Sales Order Integration – “The Sales Intrastat Entry window is open””

MDGP 2016 R2 Feature of the Day: Display Tax Percent for Historical Sales Transactions

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2016 R2; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily.

The series index for this series of posts is here

The sixth Feature of the Day is Display Tax Percent for Historical Sales Transactions.

Sales Tax Summary Enquiry

When drilling into the Sales Tax Summary Enquiry, the tax percent used at time of transaction displays instead of the percent that is setup on the tax detail maintenance window.

Click to show/hide the MDGP 2016 R2 Feature of the Day Series Index

MDGP 2016 R1 Feature of the Day: Inventory All-In-One Document View

Microsoft Dynamics GP 2015 R2 saw the introduction of the Purchasing All-In-One Document View, and Dynamics GP 2016 R1 sees the introduction of an Inventory equivalent:

Inventory All-In-One Document View

The main features of Inventory All-In-One Document View are:

  • View quantity in and quantity out transactions for one item in one window
  • Accessible from Item Maintenance, many item inquiry windows, navigation lists and the home page

As with the Sales All-In-One Document View I am prepared to be underwhelmed by this feature of the day based on the Purchasing one introduced in Dynamics GP 2015 R2.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

MDGP 2016 R1 Feature of the Day: Sales All-In-One Document View

Microsoft Dynamics GP 2015 R2 saw the introduction of the Purchasing All-In-One Document View, and Dynamics GP 2016 R1 sees the introduction of a Sales equivalent:

Sales All-In-One Document View

The main features of Sales All-In-One Document View are:

  • View all related sales documents for a single transaction in one window
  • Accessible from Customer Maintenance, many sales inquiry windows, navigation lists and the home page

When I heard of the Purchasing version of this in 2015 R2 I was looking forward to it, but found the reality to be a little underwhelming as the window isn’ the friendliest around and is a little clunky. The screenshot above shows that the Sales All-In-One Document View is very similar to the purchasing one so fear I will find this equally clunky.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

SQL View To Allow Customer/Item Link In SmartList Builder

Microsoft Dynamics GPOn this one I am open to someone telling me that there is actually a way to do this out of the box, but I haven’t been able to find it. On a SmartList I was creating, I needed to pull the customer item number and description through to a SOP transaction SmartList, but couldn’t find any table to link with.

As a workaround, I created this view which contains the required link using the CUSTNMBR from SOP10100 (Sales Transaction Work) and the ITEMNMBR from SOP10200 (Sales Transaction Amounts Work):


CREATE VIEW uv_AZRCRV_CustomerItemLink AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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
		SOP102.SOPNUMBE
		,SOP102.SOPTYPE
		,SOP102.LNITMSEQ
		,SOP101.CUSTNMBR
		,SOP102.ITEMNMBR
	FROM
		SOP10200 AS SOP102 WITH (NOLOCK)
	INNER JOIN
		SOP10100 AS SOP101 WITH (NOLOCK)
			ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE
	INNER JOIN
		SOP60300 AS SOP603 WITH (NOLOCK)
			ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GO

GRANT SELECT ON uv_AZRCRV_CustomerItemLink TO DYNGRP
GO