Cancelling a PO Line Created From a Purchase Requisition

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPCancelling a purchase order line which came from a purchase requisition should, one would think, be a simple task. However, prior to Microsoft Dynamics GP 2016 R2, this was not necessarily the case.

This recently came back to mind when a client, who has recently implemented Purchase Order Processing with Workflow approvals on the requisitions (PO Entry window has been customised to lock a lot of the fields) raised an issue with me about not being able to cancel a line on the PO. After writing and demoing the functionality of Dynamics GP 2016 R2 a few times recently, it took a few minutes to identify a workaround for them as the client was running Dynamics GP 2015 R2 (14.00.1016).

The problem, is that you cannot cancel the link to the requisition in the same way you would a Sales commitment. If you try to cancel by entering a Qty Canceled, you get this error:

Microsoft Dynamics GP - The quantity can't be changed because the remaining quantity doesn't satisfy the commitments for this line item

Microsoft Dynamics GP

The purchase order commitment can't be deleted; an attached sales line item is in use or is in history. Line items that weren't in use or in history are no longer committed to a purchase order.

Continue reading → Cancelling a PO Line Created From a Purchase Requisition

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL View to Return Sales By Customer By Year

● Ian Grieve ●  ● 3 Comments   ● 

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.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL Script to Prefix Email Message Subjects with Test

● Ian Grieve ●  ● 2 Comments   ● 

Microsoft Dynamics GPI had an issue reported with purchase requisitions the other day. I logged on and did a little testing and then, a short wile later, got an email from the client asking about some workflow approval emails some managers had received.

The email to the approvers did not contain the word “test” anywhere in either the subject or body. This lead to me having a horrible moment when I thought I might have done my testing on live; I was able to confirm quickly that I had not.

After this I decided I had better knock a quick script together which the client could incorporate into their live to test copy.

The script is more engineered than it might otherwise need to be as I have included code to only do the update when it is running against a test company (flagged with <TEST> at the end of the Company Name) and where the email subject isn’t already prefixed with the word TEST:

/*
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).
*/
UPDATE
	['Email Messages']
SET
	EmailMessageSubject = LEFT('TEST: ' + CAST(['Email Messages'].EmailMessageSubject AS CHAR(150)), 150)
FROM
	SY04901 AS ['Email Messages']
INNER JOIN
	DYNAMICS..SY01500 AS ['Company Master']
		ON
			['Company Master'].INTERID = DB_NAME()
WHERE
	['Company Master'].CMPNYNAM LIKE '%<TEST>'
AND
	LEFT(['Email Messages'].EmailMessageSubject, 4) <> 'TEST'
GO

As always, before running a script against a database, make sure you understand the scirpt and have a good backup of the database.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

SQL View to Return Purchases By Vendor By Year

● Ian Grieve ●  ● 4 Comments   ● 

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.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Copy Workflow Doesn’t Copy Notification Settings

● Ian Grieve ●  ● 2 Comments   ● 

Microsoft Dynamics GPI made a discovery the other day about the Copy Workflow functionality in Workflow Maintenance (Administration area page >> Setup >> Company >> Workflow >> Workflow Maintenance). I have been working with a client to create a new Purchase Order Processing workflow. After the client did UAT on a test system, we needed to transfer the workflow to the live system.

We did this by copying the Workflow from the test company to the live one using the Coopy functionality. After the copy, users did a little testing on live and found that originators were not receiving notifications, but users were receiving the task assignment emails.

This was not the first email issues we had encountered during the project, but turned out to be the simplest to resolve (although it took longer than it should to identify).

To correct the issue, open the Workflow and click the expansion arrow next to the Send notifications for completed actions checkbox and remark the checkboxes for the notifications which should be sent:

Workflow Email Notification Maintenance

● Categories: Dynamics, GP, Microsoft, Workflow ● Tags: , , , , ,  ● Permalink ● Shortlink ●

Customising Dynamics GP Workflow Emails: Adding An Image

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPI thought I had posted about this a littler while ago as part of my Customising Dynamics GP Workflow Emails series, but it seems I missed it. The reason it came back to mind, was when I was onsite with a client doing some training on Workflow, they asked if it was possible to add a company logo to a Workflow task assignment email.

It is. In fact, you can add any image you want to a workflow email. In this example, though, I am going to stick with using a company logo.

In the Message Setup (Administration area page >> Setup >> Company >> Workflow >> Message Setup) window load the message you want to amend and simply add the HTML image tag:

Message Setup

Continue reading → Customising Dynamics GP Workflow Emails: Adding An Image

● Categories: Dynamics, GP, Microsoft, Workflow ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Dynamics GP Tech Conference 2017: Power BI Enhancements

● Ian Grieve ●  ● 0 Comments   ● 

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

Power BI is more the area of Belinda Allen or Mark Polino than mine, but I’ll be getting more of a look at it soon as I have one of my team about to take a more in depth look, so it is nice to hear there will be more new stuff coming in the next version of Dynamics GP:

  • Support for paging in OData Services
  • Support for filtering in OData Services
  • Support for OData v4
  • GP Power BI Content Pack – I need more information on this, but it sounds like a set of pre-packaged reports which will be welcome.
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

Dynamics GP Tech Conference 2017: Comprehensive Doc Attach

● Ian Grieve ●  ● 0 Comments   ● 

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).
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

Dynamics GP Tech Conference 2017: Optimised Human Resources and Payroll

● Ian Grieve ●  ● 0 Comments   ● 

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

Not modules I know anything about, as they are very US and Canada focused, but there are some enhancements coming to Human Resources and Payroll:

  • Turn off garnishment reports in posting setup.
  • Roll down payroll setup options from setup to inactive records.
  • Change department code in all payroll files, including history.
  • Restrict inactive Deduction and Benefit codes in lookups

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

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

Dynamics GP Tech Conference 2017: Optimised Financials

● Ian Grieve ●  ● 0 Comments   ● 

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

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●