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

MDGP 2018 RTM Feature of the Day: Make PO Number Visible to Requisition Originator

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 second of the changes, is making the purchase order number easily visible to the requisition originator.

The PO number has been made available in two places. The first of them is on the Dynamics GP home page. The Procurement panel has been updated too have a new PO Number in the Requisition List:

Home page

Continue reading “MDGP 2018 RTM Feature of the Day: Make PO Number Visible to Requisition Originator”

MDGP 2018 RTM Feature of the Day: On PO Generation Show Purchase Order Transactions Navigation List

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 first of the three features is a new option to the Purchase Requisition Entry window on the generation of a purchase order which will pop up the Purchase Order Transactions navigation list and show the generated orders:

Purchase Order Transactions Navigation List

From the screenshots supplied by Microsoft, it isn’t apparent how the option works, but this is potentially very useful, in that it will allow the originator to convert the requisition to an order and then, from the navigation list, be able to email or print the purchase order.

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

SQL Function To Return Last Workflow Comment

Microsoft Dynamics GPBack in April 2017 I posted an SQL function which can be used to return the workflow status of a transaction or card. This function is now complimented by another one which can be used to return the last comment recorded. This was created for use on a customisation of the Purchase Requisition Entry window which added a Rejection Reason field so users could see at a glance why a purchase requisition had been rejected.

IF OBJECT_ID (N'uf_AZRCRV_GetWorkflowApprovalComments', N'FN') IS NOT NULL
    DROP FUNCTION uf_AZRCRV_GetWorkflowApprovalComments
GO
CREATE FUNCTION dbo.uf_AZRCRV_GetWorkflowApprovalComments(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(20))
	RETURNS VARCHAR(14)
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).

Returns Workflow Approval status of a specified workflow item.

Requires input parameters of WorkflowTypeName and WfBusObjKey

Valid Workflow Type Names are (as of Microsoft Dynamics GP 2016 R2):
	General Ledger Batch Approval
	Receivables Batch Approval
	Payables Batch Approval
	Payables Transaction Approval
	Purchase Order Approval
	Purchase Requisition Approval
	Vendor Approval
	Employee Profile Approval
	Employee Skills Approval
	Payroll Direct Deposit Approval
	Payroll Timecard Approval
	Payroll W4 Approval
	Expense Report Approval
	Timesheet Approval
	Smartlist Designer View Approval
*/
BEGIN
	RETURN ISNULL((
		SELECT TOP 1 
			WF30100 AS ['Workflow History']
		FROM 
			WF30100 AS ['Workflow History']
		INNER JOIN
			WFI10002 AS ['Workflow Master']
				ON
					['Workflow Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
		WHERE
			['Workflow Master'].Workflow_Type_Name = @WorkflowTypeName
		AND
			['Workflow Master'].WfBusObjKey = @WfBusObjKey
		ORDER BY
			['Workflow History'].DEX_ROW_ID DESC)
	,'Not Submitted')
END
GO
GRANT EXECUTE ON uf_AZRCRV_GetWorkflowApprovalComments TO DYNGRP
GO

This function can easily be included in a view or other query used in a variety of reporting tools.

MS Connect Suggestion: On Purchase Stop Summarising PR Lines

Microsoft Dynamics GPThis suggestion came about from an issue raised by a client while I was working with them to implement Purchase Order Processing with Workflow for approvals of purchase requisitions (PRs).

The issue encountered, is that when multiple lines for the same product at the same cost are added to the PR, approved and converted to PO, these lines are consolidated. This results in loss of information as only one item description or comments are pulled through to the PO.

To illustrate this, I created the following example.

First, create a PR with at least two lines. I have used the 2-A3284A product and changed the description on both rows:

Purchase Requisition Entry

Continue reading “MS Connect Suggestion: On Purchase Stop Summarising PR Lines”

SQL Script to Remove Purchase Requisition Workflow Status

Microsoft Dynamics GPA client recently had some hardware issues causing problems with the submission of purchases requisitions into a Workflow approval process. The hardware issues were causing some speed issues which resulted in the workflow timing out and the approval status becoming corrupt.

Instead of the PR being approved and going to a status of Final Approved, it was going to Approved, even though there was only one level of approval required by the workflow.

When the hardware issues were resolved, the error stopped occurring, but a number of corrupt PRs were remaining.

The way to correct the data is to reset the workflow status. I produced a small script which does this by resetting the Workflow_Status on the Purchasing Requisition Work (POP10200) table and removing the entries from the following tables:

  1. Workflow Step Instance Table (WFI10003)
  2. Workflow Tasks Table (WFI10004)
  3. Workflow Instance Master (WFI10002)

The list of PRs to be reset are entered in the highlighted part of the script:

/*
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).
*/
CREATE TABLE #POPRequisitions(
	POPRequisitionNumber VARCHAR(21)
)
GO

INSERT INTO
	#POPRequisitions
VALUES
	('REQ00000000000017')
GO

UPDATE POP10200 SET Workflow_Status = 1 WHERE POPRequisitionNumber IN (SELECT POPRequisitionNumber FROM #POPRequisitions)

DELETE FROM WFI10003 WHERE WorkflowInstanceID IN 
	(SELECT WorkflowInstanceID FROM WFI10002 WHERE Workflow_Type_Name='Purchase Requisition Approval' AND WfBusObjKey IN (SELECT POPRequisitionNumber FROM #POPRequisitions))

DELETE FROM WFI10004 WHERE WorkflowInstanceID IN 
	(SELECT WorkflowInstanceID FROM WFI10002 WHERE Workflow_Type_Name='Purchase Requisition Approval' AND WfBusObjKey IN (SELECT POPRequisitionNumber FROM #POPRequisitions))

DELETE FROM WFI10002 WHERE Workflow_Type_Name='Purchase Requisition Approval' AND WfBusObjKey IN
	(SELECT POPRequisitionNumber FROM #POPRequisitions)
GO

DROP TABLE #POPRequisitions
GO

As always before running a script on live, make sure you have a good backup and test the script in a test company.

Cancelling a PO Line Created From a Purchase Requisition

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”

Dynamics GP Tech Conference 2017: New Features In Workflow

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

There have been new features in other areas, which I will come back to, but Workflow is sort of “my thing”, so I’m going to mention the new features which are being added to Workflow 4.0 (I apparently missed the boat somewhere on the name change to Workflow 3.0?).

The coming new features are:

  • Reminder emails for Workflow – this is a feature which has been requested of ym by a couple of clients recently, so nice that I’ll be able to go back and tell them it will be in the next version.
  • Copy Workflow Step – nice to see this one added as it was one of my suggestions on MS Connect.
  • Reporting for Workflow – I’m not sure what is coming in this, but it would be nice to have some default reports showing the history of documents as they passed through the Workflow.
  • Payables Transaction Workflow – additional fields
  • PO Workflow Messages – additional fields – there are never enough fields available for the Workflow, so nice to know more will be available by default.
  • Add EFT/Bank Details to Vendor Approval Workflow – if this one is as it sounds, that will be fantastic as so many clients want this functionality. I think it only took so long as EFT isn’t anywhere near as big in the US as it is in the UK.
  • GL Account Workflow Approval – I have a couple of clients who can ditch some customisations with this one. And a few more who will be delighted so see this added.
  • Purchase Receiving Workflow – I have a few customers waiting for this one to be introduced.
  • Purchase Enter/Match Invoice Workflow – as above, a few clients are waiting for this one.

I know we can’t have everything we want, and the above are good additions to Workflow, but I still want more!

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

Microsoft Dynamics GP July Hotfix – 2017 Mid Year Tax Update Now Available

Microsoft Dynamics GPThe Dynamics GP Support and Services Blog blog announced the availability of the Microsoft Dynamics GP July Hotfix – 2017 Mid Year Tax Update towards the end of June.

This hotfix is available for Microsoft Dynamics GP 2013 R2, 2015 r2 and 2016 R2.

While all of them have updates for the US and Canadian Payroll systems, there were three changes in the 2016 version which caught my eye:

  • Financials

    • You may not be able to view any document attachment for any OPEN Payables Document.
    • Invoice Receipt Date field added for UK installs, click here for details.
  • System

    • Scan functionality may not function with document attach

The fixes to Document Attachment are good to see, but the Invoice Receipt Date was especially interesting as it results from new legislation which came into effect on April 1st for businesses of a certain size in the UK with a duty to report on their payment practices, policies and performance. This new requirement was introduced to protect smaller businesses that suffer from late payments, which can adversely affect their cash flow and jeopardize their ability to trade.

The new Invoice Receipt Date field is available on Payables Transaction Entry (Purchasing >> Transactions >> Transaction Entry), Receivings Transaction Entry (Purchasing >> Transactions >> Receivings Transaction Entry) and Purchasing Invoice Entry (Purchasing >> Transactions >> Enter/Match Invoice).

The Doc Attach changes only effect 2016, but the new field is available in 2013, 2015 and 2016.

Enabling the PO Email Document Without an Exchange Account.

Microsoft Dynamics GPI am very much an advocate for using the Microsoft Dynamics GP application for making changes to configuration or data rather than jumping straight to the SQL database. However, sometimes needs really must.

I was onsite with a client a few weeks ago to assist them in implementing Purchase Order Processing and, as part of this implementation, I needed to enable the emailing of the purchase order. Typically, I would do this through the Company E-mail Setup (Administration >> Setup >> Company >>
E-mail Setup
)) window, but the client is configured to use Exchange for emails and I don’t have an Exchange account on their system.

Due to my location compared to the users, it was quicker to create an SQL script to enable the email rather than trying to locate a user with the relevant permissions to screen share with:

UPDATE
	SY04903
SET
	EmailDocumentEnabled = 1
	,EmailMessageID = 'PO'
WHERE
	EmailDictionaryID = 0
AND
	EmailSeriesID = 4
AND
	MODULE1 = 12
AND
	EmailDocumentID = 1

The highlighted section is the name of the Message ID for the PO.

As always before running a script, ensure you test the script and have a good backup of your company database.