Recently, I was asked to come up with a solution to start sending remittance emails when payments were electronically wired to vendors. The information is currently held in a Great Plains (GP) accounting system and anyone who has ever worked at the table / view level in GP knows it isn’t the easiest system to decipher. But thanks to several blog posts which give some real-world names to the cryptic 7/8 character table and column names, I was able to muddle through and create a workable solution.
Thanks to:
GP Tables from Victoria Yudin: http://victoriayudin.com/gp-tables/
GP 10 Table Reference from http://msdynamicsgp.blogspot.com/: http://cid-bc679914609aa946.office.live.com/view.aspx/Downloads/Dynamics%20GP%20Downloads.xlsx
Basically the script flows as follows:
1. Declare and initialize variables to be used in procedure
2. Select Vendor ID, Vendor Name, Email Address, Bank Information, Transfer Number, Invoice Number, Invoice Date, and Amount Paid Toward Invoice into temp table. That information can be found in the following tables:
a. PM00200 – Vendor Master
b. SY01200 – Email Address Table
c. SY06000 – EFT Master
d. PM30200 – Transactions
e. PM30300 – Apply To History
f. PM00400 – Master File
3. Create and open header cursor that spins through all the distinct vendors in the temp table
4. Since the email will be formatted in HTML and I want the output of the email to look structured, I start a HTML table element for the vendor. This contains the beginning paragraph and the table column headers for the detail lines that will follow
5. Declare and open detail cursor that will spin through and output invoice payment details into the HTML table that was created.
6. Finish the email and close the detail cursor.
7. Send the email to the vendor.
8. Move to the next value in the header cursor, create email and send. This is done for all the distinct vendors in the temp table.
This is then scheduled to run once daily and will pick up any electronic payments scheduled to be made to vendors that day.
If you only want to send emails to vendors who have an email address setup in the system, change the left outer join to an inner join. I specifically put in left outer join in there so that when a vendor had been setup without an email address, the process would send emails for that vendor to a mailbox that someone would monitor and then input an email for that vendor into GP.
If you only want to send emails to vendors who have an email address setup in the system, change the left outer join to an inner join. I specifically put in left outer join in there so that when a vendor had been setup without an email address, the process would send emails for that vendor to a mailbox that someone would monitor and then input an email for that vendor into GP.
NOTE: This script comes as is with no guarantees. Please test on a non production system before using this to send any emails inside or outside of your company.
This code is as follows:
/****************
Declare Variables
****************/
Declare @VendorID varchar(256)
Declare @VendorName varchar(256)
Declare @VendorEmail varchar(256)
Declare @VendorBankName varchar(256)
Declare @VendorBankAcct varchar(256)
Declare @VendorInvoice varchar(256)
Declare @VendorPayment Money --Using Money type to be able to add commas
Declare @VendorTotal Money --Using Money type to be able to add commas
Declare @TableHTML nvarchar(MAX)
Declare @VendorTransferNo varchar(256)
Declare @VendorInvoiceDate varchar(256)
