MDGP 2018 RTM Feature of the Day: Email Single Customer Statement

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 own commentary.

The series index for this series of posts is here.

The seventeenth Feature of the Day is Email Single Customer Statement. This feature allows a statement to be emailed from both the Customer Maintenance and Customer Inquiry windows:

This feature is meant to use the statement format defined in Receivables Setup.

From the screenshot I am left with some questions as to how this will actually work, as there is no Email button, but only a print one and I was expecting a button like on the recreate cheque stub function, but instead the button is a Print button.

I’ll have to wait to get hands on with this one to see how it actually works.

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

MDGP 2018 RTM Feature of the Day: Display Hold Status

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 own commentary.

The series index for this series of posts is here.

The fourteenth Feature of the Day is Display Hold Status. The Sales Transaction Entry and Sales Transaction Enquiry/Inquiry windows have been updated to have a hold status icon displayed next to the Type/Type ID field:

Sales Transaction Entry

Continue reading “MDGP 2018 RTM Feature of the Day: Display Hold Status”

MDGP 2018 RTM Feature of the Day: Sorting

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 own commentary.

The series index for this series of posts is here.

The tenth Feature of the Day is Sorting. This feature adds sorting options to five windows in Purchasing, Sales and Bank Reconciliation:

I like this feature a lot. Clients have been asking about being able to reorder the enquiry windows for a long time; it’s nice that I’ll soon be able to tell them that they can now sort the data.

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

SQL View to Return Sales By Customer By Fiscal Year

Microsoft Dynamics GPThe last post was a SQL view to return purchases by vendor by fiscal year in an update of a prior script which only returned the information by the calendar year; I did a similar view at the time for the Sales series which also returned information for the calendar year. This post is an update of that one to return the information linking into the Financial Calendar setup to return the sales by customer by financial year.

IF OBJECT_ID (N'uv_AZRCRV_SalesByCustomerByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesByCustomerByYear
GO
CREATE VIEW uv_AZRCRV_SalesByCustomerByYear 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
	['Receivables Transactions'].CUSTNMBR AS 'Customer ID'
	,['RM Debtor MSTR'].CUSTNAME AS 'Customer Name'
	,['RM Debtor MSTR'].CUSTCLAS AS 'Class ID'
	,['Receivables Transactions'].DOCDATE 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
		,YEAR1 AS DOCDATE
		,GLPOSTDT
		,DOCNUMBR
		,RMDTYPAL
		,SLSAMNT
		,TRDISAMT
		,FRTAMNT
		,MISCAMNT
		,TAXAMNT
		,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
	FROM
		RM20101
	INNER JOIN
		SY40101
			ON
				DOCDATE BETWEEN FSTFSCDY AND LSTFSCDY
	WHERE
		VOIDSTTS >= 0
	AND
		RMDTYPAL IN (1,3,4,7,8)
	UNION ALL
		SELECT
			CUSTNMBR
			,YEAR1 AS DOCDATE
			,GLPOSTDT
			,DOCNUMBR
			,RMDTYPAL
			,SLSAMNT
			,TRDISAMT
			,FRTAMNT
			,MISCAMNT
			,TAXAMNT
			,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
		FROM
			RM30101
		INNER JOIN
			SY40101
				ON
					DOCDATE BETWEEN FSTFSCDY AND LSTFSCDY
		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
	,['Receivables Transactions'].DOCDATE
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 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.

MDGP 2018 RTM Feature of the Day: Doc Attach Notes on Inquiry Windows

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 own commentary.

The series index for this series of posts is here.

The second Feature of the Day is Notes on Inquiry windows. The maintenance windows have long allowed notes to be recorded against the window key field (e.g. Vendor ID on the Vendor Maintenance window):

Vendor Maintenance

Continue reading “MDGP 2018 RTM Feature of the Day: Doc Attach Notes on Inquiry Windows”

MDGP 2018 RTM Feature of the Day: DocAttach Available on More Windows

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.

The series index for this series of posts is here.

The first Feature of the Day is Document Attachment available on more windows.

Five additional windows now have Doc Attach available via a button on the action pane:

It is nice to see Fixed Assets getting some Doc Attach functionality; quite a few clients have documentation or images relating to their assets, so this offers a good way to keep these with the asset.
Continue reading “MDGP 2018 RTM Feature of the Day: DocAttach Available on More Windows”

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.

Dynamics GP Tech Conference 2017: Comprehensive Doc Attach

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

The Document Attachment module has been extended into the action pane in quite a few new windows:

  • Document Attachment on Receivables Transaction Entry
  • Document Attachment on General Ledger Entry
  • Document Attachment on Employee Inquiry
  • Document Attachment in I-9 Form
  • Display Vendor notes on Vendor Inquiry
  • Display Customer notes on Customer Inquiry
  • Display Project Notes in Project Inquiry and PA Project Inquiry
  • Document Attachment in Asset General Information
  • Option to attach new documents in Inquiry windows – there is a setting in the Document Attachment Setup winodw to enable this (and to require a password to be entered).

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