Hands On With MDGP 2018 RTM New Features: Print Purchase Requisition

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 twenty-second feature of the day is Print Purchase Requisition. I said in the Feature of the Day series, that my feelings were largely negative towards this feature and my opinion hasn’t changed now that I’ve been hands on with it.

A new Print button has been added to the Purchase Requisition Entry window (Purchasing area page >> Transactions >> Purchase Requisitions):

Purchase Requisition Entry

Continue reading “Hands On With MDGP 2018 RTM New Features: Print Purchase Requisition”

Hands On With MDGP 2018 RTM New Features: Print Single Customer Statement

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 nineteenth feature of the day, is Email Single Customer Statement from the Customer Maintenance window (Sales >> Cards >> Customer), or at least that is how it was originally billed by Microsoft.

However, now that I’ve had the chance to get hands on with it, it is not the ability to email a statement, but only the ability to print one using the old Standard report. This is very disappointing as this new functionality doesn’t even allow the printing to the Word Template version of the template.

To print the single statement, open the Customer Maintenance window, load a customer, click the Print button to display a small menu, click Statement:

Customer Maintenance

Continue reading “Hands On With MDGP 2018 RTM New Features: Print Single Customer Statement”

Hands On With MDGP 2018 RTM New Features: New Workflow Functionality – Reporting for Workflow

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 tenth feature of the day was Reporting for Workflow which saw the addition of some Workflow Reports accessible from the Reports section of the Administration area page:

Administration area page Reports section

Continue reading “Hands On With MDGP 2018 RTM New Features: New Workflow Functionality – Reporting for Workflow”

MDGP 2018 RTM Feature of the Day: Print Purchase Requisition

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 RTM; 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 as well as adding my won commentary.

The series index for this series of posts is here.

The eighteenth Feature of the Day is actually a set of distinct feature enhancements to the Purchase Order Processing module around the handling of purchase requisitions and orders. I am breaking this down into three separate posts, as the changes are not related beyond being about purchase requisitions and orders.

The final enhancement is the addition of a print button to the Purchase Requisition Entry window which allows the requisition to be printed:

Purchase Requisition report

I’d say I have very mixed feelings on this one, but, to be entirely honest, my feelings are actually very much on the negative side. One of the reasons for using requisitions is to have approval workflows. With a requisition needing to be approved before being purchased, there is no way for a PO to be emailed or printed; I realise this print option doesn’t print a PO, but it does print the requisition. This opens the system to potential abuse.

This can be limited through the creation or modification of security tasks which do not give access to this window; or the report could be customised to be in a format which could not be presented as if it was a PO.

Click to show/hide the MDGP 2018 RTM Feature of the Day Series Index

Copy Posting Report Configuration Between Companies

Microsoft Dynamics GPI wrote the first version of this script a long time ago, but recently had cause to return to it. A client I am working with has configured the posting reports in one company and wanted to copy the same configuration to the other companies.

This script allows you to do this, on a one by one basis. Set the first highlighted parameter to the source company, and the second to the destination company.

/*
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).
*/
DECLARE @SQLStatement VARCHAR(8000)
DECLARE @SourceCompany VARCHAR(5) = 'T16R2'
DECLARE @DestinationCompany VARCHAR(5) = 'P16R2'

SET @SQLStatement = 'UPDATE
				['Destination Posting Journal Destinations']
			SET
				PRNTJRNL = ['Source Posting Journal Destinations'].PRNTJRNL
				,ASECTMNT = ['Source Posting Journal Destinations'].ASECTMNT
				,PRTOPRNT = ['Source Posting Journal Destinations'].PRTOPRNT
				,PRTOFLNT = ['Source Posting Journal Destinations'].PRTOFLNT
			FROM
				' + @DestinationCompany + '.dbo.SY02200 AS ['Destination Posting Journal Destinations']
			INNER JOIN
				' + @SourceCompany + '.dbo.SY02200 AS ['Source Posting Journal Destinations'] ON ['Source Posting Journal Destinations'].SERIES = ['Destination Posting Journal Destinations'].SERIES
					AND ['Source Posting Journal Destinations'].TRXSOURC = ['Destination Posting Journal Destinations'].TRXSOURC
					AND ['Source Posting Journal Destinations'].PTGRPTNM = ['Destination Posting Journal Destinations'].PTGRPTNM'
EXEC (@SQLStatement)

As always with an SQL script, please make sure you have a good backup before running it.

SQL View to Return Sales By Customer By Year

Microsoft Dynamics GPThe last post was a SQL view to return purchases by vendor by year; in this one I am posting a similar view, but for the Sales series.

/*
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).
*/
IF OBJECT_ID (N'uv_AZRCRV_SalesByCustomerByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesByCustomerByYear
GO
CREATE VIEW uv_AZRCRV_SalesByCustomerByYear AS
SELECT
	['Receivables Transactions'].CUSTNMBR AS 'Customer ID'
	,['RM Debtor MSTR'].CUSTNAME AS 'Customer Name'
	,['RM Debtor MSTR'].CUSTCLAS AS 'Class ID'
	,FORMAT(['Receivables Transactions'].DOCDATE, 'yyyy') AS 'Year'
	,CASE WHEN ['RM Debtor MSTR'].INACTIVE = 1 THEN
		'Inactive'
	ELSE
		'Active'
	END AS 'Status'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].SLSAMNT ELSE ['Receivables Transactions'].SLSAMNT * -1 END) AS 'Purchases Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TRDISAMT * -1 ELSE ['Receivables Transactions'].TRDISAMT END) AS 'Trade Discount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].FRTAMNT ELSE ['Receivables Transactions'].FRTAMNT * -1 END) AS 'Freight'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].MISCAMNT ELSE ['Receivables Transactions'].MISCAMNT * -1 END) AS 'Miscalleneous'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TAXAMNT ELSE ['Receivables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].DOCAMNT ELSE ['Receivables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
	(SELECT
		CUSTNMBR
		,DOCDATE
		,GLPOSTDT
		,DOCNUMBR
		,RMDTYPAL
		,SLSAMNT
		,TRDISAMT
		,FRTAMNT
		,MISCAMNT
		,TAXAMNT
		,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
	FROM
		RM20101
	WHERE
		VOIDSTTS >= 0
	AND
		RMDTYPAL IN (1,3,4,7,8)
	UNION ALL
		SELECT
			CUSTNMBR
			,DOCDATE
			,GLPOSTDT
			,DOCNUMBR
			,RMDTYPAL
			,SLSAMNT
			,TRDISAMT
			,FRTAMNT
			,MISCAMNT
			,TAXAMNT
			,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
		FROM
			RM30101
		WHERE
			VOIDSTTS >= 0
		AND
			RMDTYPAL IN (1,3,4,7,8)
	) AS ['Receivables Transactions']
LEFT JOIN
	RM00101 AS ['RM Debtor MSTR']
		ON
			['Receivables Transactions'].CUSTNMBR >= ['RM Debtor MSTR'].CUSTNMBR
GROUP BY
	['Receivables Transactions'].CUSTNMBR
	,['RM Debtor MSTR'].CUSTNAME
	,['RM Debtor MSTR'].CUSTCLAS
	,['RM Debtor MSTR'].INACTIVE
	,FORMAT(['Receivables Transactions'].DocDate, 'yyyy')
GO
GRANT SELECT ON uv_AZRCRV_SalesByCustomerByYear 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.

SQL View to Return Purchases By Vendor By Year

Microsoft Dynamics GPThere are a number of reports which I keep getting asked for. One of them is a list of the top X number of creditors (or vendors to my American readers).

Rather than keep on reinventing the wheel, I have created a SQL view which sums up the transactions in each year for all vendors:

/*
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).
*/
IF OBJECT_ID (N'uv_AZRCRV_PurchasesByVendorByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_PurchasesByVendorByYear
GO
CREATE VIEW uv_AZRCRV_PurchasesByVendorByYear AS
SELECT
	['Payables Transactions'].VENDORID AS 'Vendor ID'
	,['PM Vendor Master'].VENDNAME AS 'Vendor Name'
	,['PM Vendor Master'].VNDCLSID AS 'Class ID'
	,FORMAT(['Payables Transactions'].DOCDATE, 'yyyy') AS 'Year'
	,CASE WHEN ['PM Vendor Master'].VENDSTTS >= 1 THEN
		'Active'
	WHEN ['PM Vendor Master'].VENDSTTS >= 2 THEN
		'Inctive'
	ELSE
		'Temporary'
	END AS 'Status'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].PRCHAMNT ELSE ['Payables Transactions'].PRCHAMNT * -1 END) AS 'Purchases Amount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TRDISAMT * -1 ELSE ['Payables Transactions'].TRDISAMT END) AS 'Trade Discount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].FRTAMNT ELSE ['Payables Transactions'].FRTAMNT * -1 END) AS 'Freight'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].MSCCHAMT ELSE ['Payables Transactions'].MSCCHAMT * -1 END) AS 'Miscalleneous'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TAXAMNT ELSE ['Payables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].DOCAMNT ELSE ['Payables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
	(SELECT
		VENDORID
		,DOCDATE
		,PSTGDATE
		,DOCNUMBR
		,DOCTYPE
		,PRCHAMNT
		,TRDISAMT
		,FRTAMNT
		,MSCCHAMT
		,TAXAMNT
		,DOCAMNT
	FROM
		PM20000
	WHERE
		VOIDED >= 0
	AND
		DOCTYPE <= 5
	UNION ALL
		SELECT
			VENDORID
			,DOCDATE
			,PSTGDATE
			,DOCNUMBR
			,DOCTYPE
			,PRCHAMNT
			,TRDISAMT
			,FRTAMNT
			,MSCCHAMT
			,TAXAMNT
			,DOCAMNT
		FROM
			PM30200
		WHERE
			VOIDED >= 0
		AND
			DOCTYPE <= 5
	) AS ['Payables Transactions']
LEFT JOIN
	PM00200 AS ['PM Vendor Master']
		ON
			['Payables Transactions'].VENDORID >= ['PM Vendor Master'].VENDORID
GROUP BY
	['Payables Transactions'].VENDORID
	,['PM Vendor Master'].VENDNAME
	,['PM Vendor Master'].VNDCLSID
	,['PM Vendor Master'].VENDSTTS
	,FORMAT(['Payables Transactions'].DocDate, 'yyyy')
GO
GRANT SELECT ON uv_AZRCRV_PurchasesByVendorByYear 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.

Report Used for the Reprint Check Remittance

Microsoft Dynamics GPMicrosoft Dynamics GP 2013 saw the introduction of the ability to reprint the check remittance report. This feature was a very nice one to see, as numerous clients, if not all of them, had been asking for.

At some point after this, and I am not sure in which version, the ability to email the reprinted remittance was introduced:

Payments Payable Zoom

Continue reading “Report Used for the Reprint Check Remittance”

SQL View to Return Budgets By Month

Microsoft Dynamics GPThe budget functionality in Microsoft Dynamics GP isn’t the strongest with reporting being particularly weak. The ability to report on budgets in Management Reporter does somewhat redeem this area of functionality.

However, the absence of a SmartList Object for budgets is quite a big issue, as SmartList is a very nice flexible reporting tool which the majority of my clients know well. For those with SmartList Builder, it was easy enough to create a SmartList Object for them.

With the introduction of SmartList Designer, we were able to roll out the SmartList budget report to all of the clients who wanted it.

The script is below and returns the budget information with the beginning balance, 12 hard-coded periods and total horizontally across the page.

Continue reading “SQL View to Return Budgets By Month”