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)
/*******************
Initialize Variables
*******************/
Set @VendorID = ''
Set @VendorName = ''
Set @VendorEmail = ''
Set @VendorBankName = ''
Set @VendorBankAcct = ''
Set @VendorPayment = 0
Set @VendorTotal = 0
Set @VendorInvoice = ''
Set @VendorInvoiceDate = ''
Set @TableHTML = ''
Set @VendorTransferNo = ''
/******************************************************************************
Initial Select To Get Invoices, Payments, Vendors, and Bank Account Information
******************************************************************************/
SELECT
Rtrim(PM00200.VendorID) as 'Vendor ID'
,Rtrim(PM00200.VendName) as 'Vendor Name'
,Rtrim(PM00200.VNDCLSID) as 'Class ID'
,Coalesce(Rtrim(SY01200.INET1),'If not email is present, send it here.') as 'Email Address'
,Rtrim(SY06000.BankName) as 'Bank Name'
,Rtrim(SY06000.EFTBankAcct) as 'Bank Acct'
,Rtrim(PM30200.DOCDATE) as 'Paid Date'
,Rtrim(PM30200.DOCNUMBR) as 'Transfer Number'
,Rtrim(PM30200.DOCAMNT) as 'Total Transfer Amount For EFT#'
,Rtrim(PM30300.APToDCNM) as 'Invoice Number'
,Rtrim(CONVERT(VARCHAR(10), PM00400.DocDate, 101)) as 'Invoice Date'
,Rtrim(PM30300.APFRMAPLYAMT) as 'Paid Toward Invoice'
Into #Master
FROM PM00200 (nolock) left join SY01200 (nolock) --Vendor Master to Vendor Email Table
on PM00200.VendorID = SY01200.Master_ID
and PM00200.VADCDTRO = SY01200.ADRSCODE
inner join SY06000 (nolock) --Vendor Bank Info
on PM00200.VendorID = SY06000.VendorID
inner join PM30200 (nolock) --Historical / Paid Transactions
on PM00200.VendorID = PM30200.VendorID
inner join PM30300 (nolock) --Apply To History
on PM30200.VendorID = PM30300.VendorID
and PM30200.DOCNUMBR = PM30300.APFRDCNM
inner join PM00400 (nolock) --PM Key Master File
on PM30300.APToDCNM = PM00400.DOCNUMBR
and PM30300.VendorID = PM00400.VendorID
Where
PM30200.DOCType = 6 -- Payment (Check, Cash, or Credit Card)
and PM30200.Electronic = 1 -- (1 = Yes, 0 = No)
and PM30200.PYENTTYP = 3 -- (0 = Check,1 = Cash, 2 = Credit Card, 3 = EFT)
and SY06000.Inactive = 0 -- (0 = No, 1 = Yes)
and DateAdd(dd,0,DateDiff(dd,0,PM30200.DocDate)) = DateAdd(dd,0,DateDiff(dd,0,GetDate()))
and PM00400.CNTRLTYP = 0 -- (0 for Invoice, 1 is for Payment)
and PM00400.CNTRLTYP = 0 -- (0 for Invoice, 1 is for Payment)
order by 1
/******************************************************************************************
Create and Open Cursor That Will Spin Through All The Distinct Vendors In The #Master Table
******************************************************************************************/
Declare Vendor_Cursor Cursor Static For
Select Distinct([Vendor ID]),
[Vendor Name],
[Email Address],
[Bank Name],
[Bank Acct]
From #Master
Open Vendor_Cursor
Fetch Next From Vendor_Cursor
Into @VendorID,
@VendorName,
@VendorEmail,
@VendorBankName,
@VendorBankAcct
/*****************
Start Outer Cursor
*****************/
While @@Fetch_Status = 0
Begin
/**********
Start Email
**********/
Set @TableHTML = ''
Set @TableHTML = '<html><body><p>
A payment has been wired to your ' + @VendorBankName +
+ ' bank account ending with ' + Right(@VendorBankAcct,3) + '.</p>
<Table border = "1" width="400">
<tr>
<th>Invoice</th>
<th>Invoice Date</th>
<th>Amount</th>
<th>Check Number</th>
</tr>'
/*********************************************************************************
Declare and Open Inner Cursor For Individual Invoiced and Payments Made To Vendors
*********************************************************************************/
Declare Vendor_Detail_Cursor Cursor Static For
Select [Invoice Number],
[Paid Toward Invoice],
[Transfer Number],
[Invoice Date]
From #Master
Where [Vendor ID] = @VendorID
Order by [Invoice Date], [Invoice Number]
Open Vendor_Detail_Cursor
Fetch Next From Vendor_Detail_Cursor
Into @VendorInvoice
,@VendorPayment
,@VendorTransferNo
,@VendorInvoiceDate
/*****************
Start Inner Cursor
*****************/
While @@Fetch_Status = 0
Begin
Set @TableHTML = @TableHTML +
'<tr>
<td align="center">' + @VendorInvoice + '</td>
<td align="center">' + @VendorInvoiceDate + '</td>
<td align="right">$' + Convert(varchar,@VendorPayment,1) + '</td>
<td align="center">' + @VendorTransferNo + '</td>
</tr>'
Set @VendorTotal = @VendorTotal + @VendorPayment
Fetch Next From Vendor_Detail_Cursor
Into @VendorInvoice
,@VendorPayment
,@VendorTransferNo
,@VendorInvoiceDate
End
/****************************
End Inner Cursor and Clean Up
****************************/
Close Vendor_Detail_Cursor
Deallocate Vendor_Detail_Cursor
/**************
Finish Up Email
**************/
Set @TableHTML = @TableHTML + '<tr>
<td align="center">Total</td>
<td> </td>
<td align="right">$' + Convert(varchar,@VendorTotal,1) + '</td>
<td> </td>
</tr>
</table>
<br>
<p>Put text here if you want something at the bottom of the email</p>
</body></html>'
Print @TableHTML
Set @VendorTotal = 0
/*********
Send Email
*********/
Exec MSDB..sp_send_dbmail
@Profile_Name = <dbmail_profile>
,@Recipients = @VendorEmail
,@Subject = <subject>
,@Body = @TableHTML
,@Body_Format = 'HTML'
Fetch Next From Vendor_Cursor
Into @VendorID,
@VendorName,
@VendorEmail,
@VendorBankName,
@VendorBankAcct
End
/****************************
End Outer Cursor and Clean Up
****************************/
Close Vendor_Cursor
Deallocate Vendor_Cursor
No comments:
Post a Comment