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
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'
PM00200 AS PM2
PM10300 AS PM103 ON PM103.VENDORID = PM2.VENDORID
GRANT SELECT ON uv_AZRCRV_PaymentRunWORK TO DYNGRP
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
['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'
SY06000 AS ['Address Electronic Transfer Master']
['Address Electronic Transfer Master'].SERIES = 4
GRANT SELECT ON uv_AZRCRV_VendorAddressElectronicTransferMaster TO DYNGRP
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 ( ) 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)
name = INTERID
FETCH NEXT FROM
WHILE (@@FETCH_STATUS <> -1)
IF (@@FETCH_STATUS <> -2)
SET @SQLStatement = 'UPDATE ' + @IntercompanyID + '..GL40000
SET KPBUDTRXHIST = 1
WHERE KPBUDTRXHIST = 0'
FETCH NEXT FROM
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
EmailCardID AS VENDORID
,CASE WHEN EmailDocumentEnabled = 1 THEN 'Yes' ELSE 'No' END AS 'Remittance Enabled'
EmailSeriesID = 4
MODULE1 = 19
EmailDocumentID = 6
GRANT SELECT ON uv_AZRCRV_VendorRemittanceEnabled TO DYNGRP
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
['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'
SY01200 AS ['Internet Addresses']
['Internet Addresses'].Master_Type = 'VEN'
GRANT SELECT ON uv_AZRCRV_VendorInternetAddresses TO DYNGRP
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
SOP10200 AS SOP102
SOP10100 AS SOP101
ON SOP101.SOPNUMBE = SOP102.SOPNUMBE
AND SOP101.SOPOwner = SOP102.SOPOwner
SOP60300 AS SOP603
ON SOP603.CUSTNMBR = SOP101.CUSTNMBR
AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GRANT SELECT ON uv_PI_SOPCustomerItemLink TO DYNGRP
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.