Day: November 9, 2017

MDGP 2018 RTM Feature of the Day: DocAttach Security Setup

Ian Grieve3 minute read1 Comment

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 fourth Feature of the Day is the security setup around allowing users to add document attachments via inquiry/enquiry windows.

The third Feature of the Day post, yesterday, covered the enquiry windows which had been updated to allow notes to be added via DocAttach.

The feature today is the security around this new capability; you need to mark the Allow attachments to be added in inquiry windows for users to be able to use the new functionality to add notes.

There is also a password field which can be set to allow users to use the new functionality when enabled; this allows you to retain some control over which users can add an attachment:

Document Attachment Setup

I like the ability to add attachments to enquiry windows, but I’m not sure that it needed separate security in this way; it’s yet one more checkbox to enable when implementing the feature.

The password may be useful, but I think, for most of my clients at least, anyone with access to the enquiry window would be able to add attachments anyway.

That said, each site using Dynamics GP is different and I am sure some users will like the flexibility of both of the security options which have been introduced.

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

MDGP 2018 RTM Feature of the Day
DocAttach Available on More Windows
Doc Attach Notes on Inquiry Windows
DocAttach Security Setup
Document Attachment Unplugged
New Workflows
Workflow Additional Fields
New Workflow Functionality – Reminder Emails
New Workflow Functionality – Copy Workflow Steps
New Workflow Functionality – Reporting for Workflow
Rename Payment Run Windows
Sorting
Save Select Payment Settings
Web Client Changes
System Password Remembered
Display Hold Status
Copy User Access Across AA Dimensions
SmartList Favorite Password Protection
Email Single Customer Statement
On PO Generation Show Purchase Order Transactions Navigation List
Make PO Number Visible to Requisition Originator
Print Purchase Requisition
Human Resources and US Payroll Enhancements
One Payment Per Vendor/Invoice Setting on Vendor Card

SQL Script To Verify Sales Invoice Extended Cost Against Subtotal

Ian Grieve4 minute read3 Comments

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 (https://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.