Month: June 2020

I recently had an error reported to me in Microsoft Dynamics GP where some final approved purchase orders were not working correctly when opened in the Purchase Order Entry window (Purchasing area page » Transactions » Purchase Order Entry).

The PO is shown as not submitted in the workflow status bar, but the button on the tool is a Recall button rather than a Submit one showing that the document has been submitted:

Continue reading “Incorrect Microsoft Dynamics GP Purchase Orders Workflow Status”

Back when this blog was a few months old, I posted a script on updating emails on vendors to keep emails internal for testing and have recently had the need to provide a customer a similar script for testing the emailing of invoices and statements.

This first script updates all existing records to replace the email address with one supplied by the customer (the highlighted email address should be changed to the required email address):

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
DECLARE @TestEmail VARCHAR(100)
SET @TestEmail = 'email@example.com'

UPDATE
	SY01200
SET
	INET1 = @TestEmail
	,EmailToAddress = @TestEmail
	,EmailCcAddress = ''
	,EmailBccAddress = ''
WHERE
	Master_Type = 'CUS'

this second script will insert an email address against all customer addresses which don’t currently have an email (the highlighted email address should be changed to the required email address).

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
DECLARE @TestEmail VARCHAR(100)
SET @TestEmail = 'email@example.com'

INSERT INTO SY01200
	(
		Master_Type
		,Master_ID
		,ADRSCODE
		,INETINFO
		,EmailToAddress
		,EmailCcAddress
		,EmailBccAddress
	)
--VALUES
	(
		SELECT 
			'CUS'
			,CUSTNMBR
			,ADRSCODE
			,''
			,@TestEmail
			,''
			,''
		FROM
			RM00102 AS ['Customer Master Address File']
		WHERE
			(
				SELECT
					COUNT(Master_ID)
				FROM
					SY01200 AS ['Internet Addresses']
				WHERE
					Internet Addresses.Master_Type = 'CUS'
				AND
					Internet Addresses.Master_ID = ['Customer Master Address File'].CUSTNMBR
				AND
					Internet Addresses.ADRSCODE = ['Customer Master Address File'].ADRSCODE
			) = 0
	)

These scripts are only intended for use on a development or test system so please be careful when running them; as always with update scripts, I’d recommend having a good backup before running them just in case of problems.

I recently wrote a report for a client which included payables distributions; in order to output the distribution type, I joined to the Payables Distribution Type SETP (PM40103) table. Unfortunately, when the report was deployed, it didn’t work: all of the rows had a blank distribution type.

When I investigated I found that the Payables Distribution Type SETP (PM40103) table was empty. My demo environment is running Fabrikam and had fifteen rows of data, so I decided to migrate my table content to the clients system using a simple SQL script to insert the extracted rows.

The script is a simple one which doesn’t do any checking so confirm the table is empty before running.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
INSERT INTO PM40103
	(DISTTYPE, DSNMSHRT, DSNMLONG)
VALUES
	(1, 'CASH', 'CASH')
	,(2, 'PAY', 'Accounts Payable')
	,(3, 'AVAIL', 'Discounts Available')
	,(4, 'TAKEN', 'Discounts Taken')
	,(5, 'FNCHG', 'Finance Charge')
	,(6, 'PURCH', 'Purchases')
	,(7, 'TRADE', 'Trade Discounts')
	,(8, 'MISC', 'Miscellaneous')
	,(9, 'FREIGHT', 'FREIGHT')
	,(10, 'TAXES', 'TAXES')
	,(11, 'WRITE', 'Write Offs')
	,(12, 'OTHER', 'OTHER')
	,(13, 'GST', 'GST')
	,(14, 'PPS', 'PPS')
	,(15, 'UNIT', 'UNIT')