MDGP 2016 R1 Feature of the Day: Prepayments on Purchase Orders

● Ian Grieve ●  ● 1 Comment   ● 

MDGP 2016 R1 Feature of the Day: Prepayments on Purchase Orders

Again this feature of the day is building on one introduced in a recent version. Prepayments were introduced in Microsoft Dynamics GP 2013 R2, but all the clients I spoke to about it said that it wasn’t useful as the prepayment could only cover the subtotal.

This feature of the day allows the prepayment amount to cover taxes, freight and miscellaneous:

Prepayments on Purchase Orders

The main features of are:

  • Prepayment amount on purchase orders can now include Taxes, Freight and Miscellaneous
  • Entire PO amount can be included in the prepayment

This update to prepayments finally makes them usable. I’ll need to remember the clients I have previously discussed it with and let them know of the new functionality.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

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

MDGP 2016 R1 Feature of the Day: Project Accounting on Requisitions

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GP 2013 R2 saw the introduction of the purchase requisitions and they are being enhanced further with integration with Project Accounting:

Project Accounting on Requisitions

The main features of are:

  • Project Number and Cost Category ID fields available in Purchase Requisition Entry window
  • Requisitions with project and cost category can still go through workflow process
  • Project and cost category will flow to Purchase Order

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

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

MS Connect Suggestion: Add Workflow To Receivings Transaction Entry and Enter/Match Invoice

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPThis has come up from a couple of different clients in the last few weeks. It would be good if approval workflows could be added to Receivings Transaction Entry and also the Enter/Match Invoices windows.

While there is workflow on the PO, a PO might not be fully received/invoiced so it would be good to have approval of the receivings document or the invoice.

The MS Connect suggestion can be found here.

Please take a look and cast your vote.

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

SQL Trigger To Automatically Enable Email Documents In Purchasing – All

● Ian Grieve ●  ● 2 Comments   ● 

Microsoft Dynamics GPThis post includes a script which amalgamates the previous two posted triggers to enable email documents (purchase orders and check remittances) into one.

The below script creates a trigger on the SY04905 table to automatically flag the remittance and purchase orders to be emailed out in PDF format and using Message IDs of REMITTANCE and PURCHASEORDER respectively.

/*
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 TRIGGER utr_AZRCRV_Update_SY04905_Activate_All ON dbo.SY04905 AFTER Insert AS
	-- Enable Purchase Order
	UPDATE
		Email
	SET
		EmailDocumentEnabled = 1
		,EmailMessageID = 'PURCHASEORDER'
		,EmailDocumentFormat = 3
	FROM
		SY04905 AS Email
	INNER JOIN
		inserted
			ON Email.EmailDictionaryID = 0
				AND Email.EmailSeriesID = 4
				AND Email.MODULE1 = 12
				AND Email.EmailCardID = inserted.EmailCardID
				AND Email.EmailDocumentID = 1
	-- Enable Remittance
	UPDATE
		Email
	SET
		EmailDocumentEnabled = 1
		,EmailMessageID = 'REMITTANCE'
		,EmailDocumentFormat = 3
	FROM
		SY04905 AS Email
	INNER JOIN
		inserted
			ON Email.EmailDictionaryID = 0
				AND Email.EmailSeriesID = 4
				AND Email.MODULE1 = 19
				AND Email.EmailCardID = inserted.EmailCardID
				AND Email.EmailDocumentID = 6
GO
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

SQL Trigger To Automatically Enable Email Documents In Purchasing – Purchase Orders

● Ian Grieve ●  ● 2 Comments   ● 

Microsoft Dynamics GPWhen creating a new vendor there is a fair bit of information which needs to be entered and it is sometimes nice to reduce the amount which needs to be done manually; in the last post I had a script to enable the email document for Check Remittances.

The below script creates a trigger on the SY04905 table to automatically flag the purchase orders to be emailed out in PDF format and using a Message ID of PURCHASEORDER respectively.

/*
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 TRIGGER utr_AZRCRV_Update_SY04905_Activate_PO ON dbo.SY04905 AFTER Insert AS
	-- Enable Purchase Order
	UPDATE
		Email
	SET
		EmailDocumentEnabled = 1
		,EmailMessageID = 'PURCHASEORDER'
		,EmailDocumentFormat = 3
	FROM
		SY04905 AS Email
	INNER JOIN
		inserted
			ON Email.EmailDictionaryID = 0
				AND Email.EmailSeriesID = 4
				AND Email.MODULE1 = 12
				AND Email.EmailCardID = inserted.EmailCardID
				AND Email.EmailDocumentID = 1
GO
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

SQL View To Return Purchasing Transactions With Multicurrency

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPA client I was working with recently was using two SmartLists, one for Payables Transactions and the other from Purchase Order Transactions, to generate a listing of purchasing transactions. However, they had to do manual fiddling around in Excel to get some of the formatting correct and didn;t have all of the information they wanted. Always up for a challenge, I had a go at producing a view we could plug in using SmartList Designer which would give them the information they wanted in the format the needed.

The below is the SQL view which was produced. It includes the PO and Receipt Numbers as well as the Originating Currency (with symbol) and exchange rate; the Functional Currency was left without a symbol so it can be easily totalled in Excel. Where the transaction does not have an Originating Currency I am outputting the Functional Currency.

/*
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 VIEW uv_AZRCRV_PurchasingTrxList AS
	SELECT
		PM.VCHRNMBR AS 'Voucher Number'
		,CASE WHEN PM.DOCTYPE = 1 THEN
			PM401.PMTRXDSC_1
		WHEN PM.DOCTYPE = 2 THEN
			PM401.PMTRXDSC_2
		WHEN PM.DOCTYPE = 3 THEN
			PM401.PMTRXDSC_3
		WHEN PM.DOCTYPE = 4 THEN
			PM401.PMTRXDSC_4
		WHEN PM.DOCTYPE = 5 THEN
			PM401.PMTRXDSC_5
		WHEN PM.DOCTYPE = 6 THEN
			PM401.PMTRXDSC_6
		WHEN PM.DOCTYPE = 7 THEN
			PM401.PMTRXDSC_7
		WHEN PM.DOCTYPE = 8 THEN
			PM401.PMTRXDSC_8
		ELSE
			'Unknown'
		END AS 'Document Type'
		,PM004.DOCTYPE
		,CASE WHEN PM004.DCSTATUS = 1 THEN
			'Work'
		WHEN PM004.DCSTATUS = 2 THEN
			'Open'
		WHEN PM004.DCSTATUS = 3 THEN
			'History'
		ELSE
			'Unknown'
		END AS 'Document Status'
		,PM004.DCSTATUS
		,PM.PORDNMBR AS 'PO Number'
		,ISNULL(POP303.POPRCTNM,'') AS 'Receipt Number'
		,PM.VENDORID AS 'Creditor Name'
		,PM002.VENDNAME AS 'Creditor ID'
		,CONVERT(VARCHAR,PM.DOCDATE,103) AS 'Document Date'
		,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END
			+ ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) 
			+ CAST(CAST(ISNULL(MC.OPURAMT,PM.PRCHAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Purchasing Amount'
		,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END 
			+ ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) 
			+ CAST(CAST(ISNULL(MC.ORTAXAMT,PM.TAXAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Tax Amount'
		,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END 
			+ ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) 
			+ CAST(CAST(ISNULL(MC.ORDOCAMT,PM.DOCAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Document Amount'
		,CASE WHEN MC.XCHGRATE IS NULL THEN 0 ELSE MC.XCHGRATE END AS 'Exchange Rate'
		,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.PRCHAMNT ELSE PM.PRCHAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Purchasing Amount'
		,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.TAXAMNT ELSE PM.TAXAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Tax Amount'
		,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.DOCAMNT ELSE PM.DOCAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Document Amount'
	FROM
		(
		-- Transaction History
		SELECT
			VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT
		FROM
			PM30200 WITH (NOLOCK)
		UNION ALL
			-- Transaction Open
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT
			FROM
				PM20000 WITH (NOLOCK)
		UNION ALL
			-- Transaction Work
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT
			FROM
				PM10000 WITH (NOLOCK)
		UNION ALL
			-- Payment Work
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,'',VENDORID,0,0,CHEKTOTL
			FROM
				PM10300 WITH (NOLOCK)
		UNION ALL
			-- Manual Payment Work
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,'',VENDORID,0,0,DOCAMNT
			FROM
				PM10400 WITH (NOLOCK)
		) AS PM
	 INNER JOIN
		PM00400 PM004 WITH (NOLOCK)
			 ON PM004.CNTRLNUM = PM.VCHRNMBR AND PM004.DOCTYPE = PM.DOCTYPE
	 INNER JOIN
		PM00200 AS PM002 WITH (NOLOCK)
			 ON PM002.VENDORID = PM.VENDORID
	 LEFT JOIN
		MC020103 AS MC WITH (NOLOCK)
			 ON MC.VCHRNMBR = PM.VCHRNMBR AND MC.DOCTYPE = PM.DOCTYPE
	 LEFT JOIN
		POP30300 AS POP303 WITH (NOLOCK)
			 ON POP303.VCHRNMBR = PM.VCHRNMBR
	 LEFT JOIN
		DYNAMICS..MC40200 AS MC402 WITH (NOLOCK)
			 ON MC402.CURRNIDX = MC.CURRNIDX
	 INNER JOIN
		MC40000 AS MC400 WITH (NOLOCK)
			 ON MC400.FUNLCURR = MC400.FUNLCURR
	 INNER JOIN
		DYNAMICS..MC40200 AS MC402F WITH (NOLOCK)
			 ON MC402F.CURRNIDX = MC400.FUNCRIDX
	 INNER JOIN
		PM40100 AS PM401 WITH (NOLOCK)
			 ON PM401.UNIQKEY = PM401.UNIQKEY
	ORDER BY PM.DOCDATE
 GO 

GRANT SELECT ON uv_AZRCRV_PurchasingTrxList TO DYNGRP
GO
● Categories: Dynamics, GP, Microsoft, Multicurrency, SmartList, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2015 R2 Feature of the Day: Enable Email on All Purchase Order Document Formats

● Ian Grieve ●  ● 2 Comments   ● 

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The twentieth Feature of the Day is Enable Email on All Purchase Order Document Formats. The Print Options for Purchase Orders currently only support email for ‘Blank’ document formats. This feature will support email for any document format for all Purchase Order documents.

This feature is supported when printing from the Transaction Entry Window, Navigation Lists, and Print Purchasing Documents window.

Purchase Order Print Options

I like this addition as it has always seemed a very artificial restriction to only be able the blank paper format of the purchase order.

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

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

MDGP 2015 R2 Feature of the Day: Historical Received Not Invoiced Report

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The fifteenth Feature of the Day is Historical Received Not Invoiced Report.

A new SQL Reporting Services Report based off the current Received Not Invoiced report but add new functionality by adding a cutoff date based on transaction date or GL Post Date to allow customers to use the report for Historical purposes. This report will allow users to see what was received into inventory but not invoiced yet as of a specific date.

Historical Received Not Invoiced Report SSRS Report

These reports are becoming increasingly popular with clients as they look far better than the “very white” ones GP has traditionally shipped with. The only downside for someone who isn’t in the US is that they ship with a default language of en_US so all values come out as USD. I have some PowerShell scripts I have been working on to download, update the language and upload the reports from SQL Server Reporting Services. I need to revisit and see if I can knock them into shape to post online; I’m not experienced with PowerShell so it is taking me awhile. If someone wants to volunteer to help, I would appreciate it.

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

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

MDGP 2015 R2 Feature of the Day: Self Service User Type

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The eleventh Feature of the Day is Self Service User Type.

Microsoft Dynamics GP 2015 R2 sees a Self Service user type added.

This user type will enable users that only require very limited access to the system a less expensive option to perform tasks such as entering payroll time, entering project time and expenses, or creating a requisition.

User Setup

This new user type is going to be a really useful addition. I have one client who has been looking for a POP add-on to allow online creation of orders for more than 400 users and all the old options were looking quite pricey.

This Self Service user type used in conjunction with the web client should give them the functionality they want, at a price point they can accept.

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft, Project Accounting, Web Client ● Tags: , , , , , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2015 R2 Feature of the Day: Document Attachment Workflow

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The tenth Feature of the Day is Workflow – Document Attachment.

When a document has been added to a transaction or record using the Microsoft Dynamics GP Document Attachment feature, these attachments can now be sent with the workflow task notification email.

This feature is available for PM Batch Approval, Purchase Order Approval, Purchase Requisition Approval, Vendor Maintenance, and the new workflow type, Payables Transaction Approval.

For example, you can now enter a payables invoice, scan in and attach the record from the vendor, then submit the invoice for approval. When the approver receives the email, they will get the invoice information and the attachment will show the scanned invoice from the vendor.

Workflow Maintenance

For a few of our clients this is the missing piece of the Workflow puzzle. An email action from Workflow can contain the detail lines of an order, but a few clients have been asking to be able to include the attachments, especially for invoices and quotes attached to POs, for the manager or budget holder to review before approving.

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

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