Monday, February 28, 2011

Sending Remittance Emails Through Great Plains


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/

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.
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)
  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>&nbsp;</td>
                                     <td align="right">$' + Convert(varchar,@VendorTotal,1) + '</td>
                                     <td>&nbsp;</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