Month: October 2015

While onsite with a client recently, I was asked if it was possible to view the payments in a computer checks batch and whether the creditors (vendors) were enabled for emailed remittances.

Out of the box no, but SmartList Designer can be used to do this. I created a new SQL view to get payments at a status of work and, in SmartList Designer, combined it with the remittance enabled view and vendor email addresses view I have previously posted.

I could have used the table in GP, but I find using views in SmartList Designer easier so created the view.

CREATE VIEW uv_AZRCRV_PaymentRunWORK AS

SELECT
	PM103.BACHNUMB AS 'Batch Number'
	,PM103.VENDORID AS 'Creditor ID'
	,PM2.VENDNAME AS 'Creditor Name'
	,PM2.VADDCDPR AS 'Primary Address'
	,PM2.VADCDTRO AS 'Remit To Address'
	,PM103.DOCDATE AS 'Document Date'
FROM
	PM00200 AS PM2
INNER JOIN
	PM10300 AS PM103 ON PM103.VENDORID = PM2.VENDORID
GO

GRANT SELECT ON uv_AZRCRV_PaymentRunWORK TO DYNGRP
GO

All of these views could have been created natively in SmartList Builder, but by creating them as views I can reuse them in SmartList Designer or any other reporting tool.

I’ve created a number of small views to report on vendor related information (such as email remittance and email addresses) and have recently followed up with another script which returns bank details for vendors:

CREATE VIEW uv_AZRCRV_VendorAddressElectronicTransferMaster AS

SELECT
	['Address Electronic Transfer Master'].VENDORID AS 'Vendor ID'
	,['Address Electronic Transfer Master'].ADRSCODE AS 'Address Code'
	,['Address Electronic Transfer Master'].BANKNAME AS 'Bank Name'
	,['Address Electronic Transfer Master'].EFTBankCode AS 'Bank Sort Code'
	,['Address Electronic Transfer Master'].EFTBankAcct AS 'Bank Account'
FROM
	SY06000 AS ['Address Electronic Transfer Master']
WHERE
	['Address Electronic Transfer Master'].SERIES = 4
GO

GRANT SELECT ON uv_AZRCRV_VendorAddressElectronicTransferMaster TO DYNGRP
GO

This script returns the fields I was interested in for a client, but could easily be extended to include additional fields from the EFT table.

We recently upgraded a client with well over a hundred companies from Microsoft Dynamics GP 10 to GP 2015; one of the new pieces of functionality they wanted to start using was the Budget Transactions.

One problem is that the keep history checkbox for Budget Transactions in General Ledger Setup (Financials » Setup » General Ledger) is unmarked.

Enabling it for this many companies was going to take a substantial amount of time (even if we generated a macro to do this in automatically) as the client has over 120 companies in Dynamics GP. It is a simple setting in one table which needs to be updated, so instead of using a macro I created a SQL script containing a cursor to loop through all of the databases attached to the system database and enable this setting.

As with all scripts which change data, please make sure you have a good backup of your database (or in this case all of your company databases) before running the script.

DECLARE @IntercompanyID VARCHAR(5)
DECLARE @SQLStatement AS VARCHAR(2000)

DECLARE
	cursor_InterID CURSOR 
FOR 
	SELECT
		INTERID
	FROM
		DYNAMICS..SY01500
	INNER JOIN
		master..sysdatabases
	ON
		name = INTERID
	
	OPEN cursor_InterID

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@IntercompanyID
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
			SET @SQLStatement = 'UPDATE ' + @IntercompanyID + '..GL40000
								SET KPBUDTRXHIST = 1
								WHERE KPBUDTRXHIST = 0'
			EXEC (@SQLStatement)
			FETCH NEXT FROM
				cursor_InterID
			INTO
				@IntercompanyID
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

You will need to amend the script if your System Database is not called DYNAMICS.

Want an easy way to see which vendors are configured for remittances to be emailed? Plug this view into SmartList Designer, or SmartList Builder, and you’ll have that easy way.

CREATE VIEW uv_AZRCRV_VendorRemittanceEnabled AS
SELECT 
	EmailCardID AS VENDORID
	,CASE WHEN EmailDocumentEnabled = 1 THEN 'Yes' ELSE 'No' END AS 'Remittance Enabled'
 FROM 
	SY04905
WHERE
	EmailSeriesID = 4
AND
	MODULE1 = 19
AND	
	EmailDocumentID = 6
GO

GRANT SELECT ON uv_AZRCRV_VendorRemittanceEnabled TO DYNGRP
GO

I produced this view to return vendor email address for a client a while ago; I don’t typically use this view by itself, but instead combine it with GP tables to produce a larger SmartList report using either SmartList Designer or SmartList Builder.

CREATE VIEW uv_AZRCRV_VendorInternetAddresses
AS
SELECT
	['Internet Addresses'].Master_ID AS 'Vendor ID'
	,['Internet Addresses'].ADRSCODE AS 'Address Code'
	,['Internet Addresses'].EmailToAddress AS 'Email To Address'
	,['Internet Addresses'].EmailCcAddress AS 'Email Cc Address'
	,['Internet Addresses'].EmailBccAddress AS 'Email Bcc Address'
	,['Internet Addresses'].INET1 AS 'Email'
FROM
	SY01200 AS ['Internet Addresses']
WHERE
	['Internet Addresses'].Master_Type = 'VEN'
GO

GRANT SELECT ON uv_AZRCRV_VendorInternetAddresses TO DYNGRP
GO

This one came up from a query a client had about linking a sales order transaction line to the customer item in SmartList Builder. The problem is that to join two tables together, you need all of the key fields to be on the same table; unfortunately, with the SOP Transaction table, this isn’t the case when you want to link to the customer item.

SOP10100 (Sales Transaction Work) holds the CUSTNMBR (Customer Number), but SOP10200 (Sales Transaction Amounts Work) holds the ITEMNMBR (Item Number) which are both needed to link to SOP60300 (Sales Customer Item Cross Reference) which holds the customer item number and description.

While there may be a way to do this in SmartList Builder I’ve not been able to work it out (other than using two calculated fields), it is easier, quicker and more reusable, to create a simple SQL View which returns the relevant information.

In this case the view I created works only for transactions which are at a status of work:

CREATE VIEW uv_PI_SOPCustomerItemLink
AS
	SELECT
		SOP101.CUSTNMBR
		,SOP102.ITEMNMBR
		,SOP603.CUSTITEMNMBR
		,SOP603.CUSTITEMDESC
	FROM
		SOP10200 AS SOP102
	INNER JOIN
		SOP10100 AS SOP101
			ON SOP101.SOPNUMBE = SOP102.SOPNUMBE
				AND SOP101.SOPOwner = SOP102.SOPOwner
	INNER JOIN
		SOP60300 AS SOP603
			ON SOP603.CUSTNMBR = SOP101.CUSTNMBR
				AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GO

GRANT SELECT ON uv_PI_SOPCustomerItemLink TO DYNGRP
GO

The SQL above includes the Grant statement used to add select permissions for the DYNGRP.

Well, I received the notification email yesterday afternoon that I had been awarded the Microsoft Most Valuable Professional award for the third year running. It’s really niec to receive the award as it shows I am still contributing in a meaningful way to the Dynamics GP community.

There was another new MVP for Dynamics GP added to the roster this month as well; David Musgrave. Previously David was ineligible for the award as he worked directly for Microsoft, but as of October last year he has been independent again and running Winthrop Development Consultants (the company responsible for GP Power Tools, formerly known as the Support Debugging Tool).

David has always been a great contributor to the Dynamics GP community and I’m delighted to see that this has been officially recognised by Microsoft with his MVP award.