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 () 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.
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.