Posted by Garnishment management can be a complex process for companies that manage their Payroll in house. One client of Systematica uses Dynamics GP for both Payroll and Payables and uses the Payroll to Payables integration in order to pay employee garnishments. The government requires that the garnishment checks include the employee’s last name and the check-date together on the remittance. This data is not available out-of-the-box, GP simply creates vouchers for each garnishment without passing any data that ties the payables voucher back to the payroll records that created them. The client was currently opening each garnishment voucher and replacing the document ID (which is a sequentially generated field just used for reference) with the employee name and date. Our goal was to replace this with a simple command button that would update all of the garnishment document IDs with the employee ID and check date.
Our solution required the following steps:
1) Modify ALL employee garnishment deductions to pass the EmployeeID and Deduction code to payables, this can be done by going to the “Payroll Vendor Setup” window (on the HR Home screen this is under Setup à Payables Integration à Vendors). After choosing the Employee, and the Garnishment deduction, select “Emp ID + Ded Code” from the “Voucher Description” dropdown list.
2) Modify the “Vouchers” search window by adding a command button that will execute a SQL stored procedure. We keep all SQL stored procedures in a separate database so that we are not changing GP company databases.
3) Create a stored procedure that accepts the company DB as a parameter and then uses dynamic SQL to update the DocumentID field in the PM10000 table with the data that the client needs to appear on the remittance. Here are the details of the sproc:
- Create a temp table that keeps the Dex_Row_ID and the Voucher Number as the primary keys. The temp table also includes the parsed employeeID and garnishment code and other data that the user may want as feedback. The parsed employeeID and garnishment code are inner joined to the UPR00100 (employee master table) and UPR40900 (deduction setup table) so that any records that don’t match up to employees and garnishments (respectively) just won’t be included in the temp table. This is necessary since the voucher table (PM10000) will have other vouchers that we don’t want to touch and whose transaction description fields may be blank or contain data that won’t match with employee and garnishment codes.
- Update the Document ID field in the PM10000 table with the document ID that was concatenated into the temp table. Use an inner join on the two primary keys in the temp table to update the proper records. For those of you who are curious for how to do a SQL update statement across two table, this is an appropriate example… you just have to use a table alias.
- Select from the temp table and present the updated data to the user in a listbox on a user form in GP. They are also presented with the VendorID to verify that the proper records were updated and the total $ of garnishments so that they can compare the results with their payroll report. If the totals don’t match it may be due to improper garnishment setup… if the garnishment is not set up to send the EmployeeID and the garnishment code, the records will fail to transfer from payables.
As a side note, I use a function to build the ADODB connection to either the test server or to the production server based on the IP address of the client. For example all test clients have IP addresses like 10.6.XX.XXX, so I check the client IP address and if it begins with 10.6 I create a connection string that is to the test server, otherwise I build a connection string to live. This is done both to ensure that I am not hitting a live server with test data and to prevent failed connections since the IPs are isolated. If you would like the code sample for how I do this, please add a comment and I’ll send you the code!
Option Explicit
Private Sub RenameGarns_Changed()
Dim cn As Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim dblTotalAmount As Double
Dim intNameLength As Integer
dblTotalAmount = 0
Set cn = New ADODB.Connection
cn.ConnectionString = GDLINTF_GPConnStr()
On Error GoTo CantConnect
cn.Open
On Error GoTo MiscError
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
cmd.CommandType = adCmdStoredProc ‘Define the ADODB command
cmd.ActiveConnection = cn ‘Set the command connection string
cmd.CommandText = “gp_spUpdateGarnishmentsPayablesDocID_upd”
With cmd
.Parameters.Append cmd.CreateParameter(“@GPDB”, adVarChar, adParamInput, 10, UserInfoGet.IntercompanyID)
End With
‘Execute stored procedure and return to a recordset
Set rst = cmd.Execute
frmGarnUpdateMessage.lstGarnRows.AddItem (“Old Doc #” & vbTab & vbTab & “EmployeeID” & vbTab & “CheckDate” & vbTab & “New Doc #” & vbTab & vbTab & “Amount” & vbTab & “Vendor ID”)
Do Until rst.EOF
intNameLength = Len(rst!EmpName)
frmGarnUpdateMessage.lstGarnRows.AddItem (rst!OldDocNumber & String(20 – intNameLength, ” “) & vbTab & rst!EmployeeID & vbTab & vbTab & rst!CheckDate & vbTab & vbTab & rst!NewDocNumber & String(20 – intNameLength, ” “) & vbTab & “$” & rst!Amount & vbTab & rst!VendorID)
dblTotalAmount = dblTotalAmount + rst!Amount
rst.MoveNext
Loop
If CBool(cn.State And adStateOpen) = True Then cn.Close
Set cn = Nothing
frmGarnUpdateMessage.lstGarnRows.AddItem (“”)
frmGarnUpdateMessage.lstGarnRows.AddItem (“Total garnishments: $” & dblTotalAmount)
frmGarnUpdateMessage.Show
Exit Sub
MiscError:
If CBool(rst.State And adStateOpen) = True Then rst.Close
Set rst = Nothing
If CBool(cn.State And adStateOpen) = True Then cn.Close
Set cn = Nothing
MsgBox “There was a problem running the update.”
Exit Sub
CantConnect:
MsgBox “There was a problem connecting to the data.”
End Sub
CREATE PROCEDURE [dbo].[gp_spUpdateGarnishmentsPayablesDocID_upd] (@GPDB As VarChar(10))
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQLScript As VarChar(Max)
Declare @Space As VarChar(10)
Set @Space = ”” + ‘ ‘+ ””
/*
The first Select statement will parse the Transaction Description passed by payroll into both the employeeID
and the Garn type. An inner join, joins the parsed garnishments with all garn types in the deduction table
so that (by INNER joining) only vouchers with a parsed garnishment (DEDTYPE = 2) will be inserted into the temp table
this temp table includes the voucher number, the dex row ID, the old document number, the employee name and the
new document number so that all data needed for the update statement and the feedback
for the vba code will be available in the temp table
*/
Set @SQLScript =
‘Select PM.VCHNUMWK,PM.DEX_ROW_ID,SUBSTRING(PM.TRXDSCRN,0,CHARINDEX(‘ + @Space + ‘,PM.TRXDSCRN)) As EmployeeID,
Left(convert(varchar(10),PM.PSTGDATE, 101),5) As CheckDate,
PM.DOCNUMBR As OldDocNumber,
RTRIM(Emp.LASTNAME) As EmpName,
LEFT(RTRIM(Emp.LASTNAME) ,14) + ‘ + @Space + ‘+ Left(convert(varchar(10),PM.PSTGDATE, 101),5) As NewDocNumber,
DocAmnt As Amount,
VendorID
INTO #VOUCH
From ‘ + @GPDB + ‘.dbo.PM10000 PM (NOLOCK)
INNER JOIN ‘ + @GPDB + ‘.dbo.UPR40900 Deds (NOLOCK) On SUBSTRING(PM.TRXDSCRN,CHARINDEX(‘ + @Space + ‘,PM.TRXDSCRN)+1,10) = Deds.DEDUCTON AND Deds.DEDTYPE = 2
INNER JOIN ‘ + @GPDB + ‘.dbo.UPR00100 Emp (NOLOCK) ON Emp.EmployID = SUBSTRING(PM.TRXDSCRN,0,CHARINDEX(‘ + @Space + ‘,PM.TRXDSCRN))
‘
/* Update the voucher work table with the values in the temp table */
Set @SQLScript = @SQLScript +
‘UPDATE PM Set PM.DOCNUMBR= Vouch.NewDocNumber
From ‘ + @GPDB + ‘.dbo.PM10000 PM (NOLOCK)
INNER Join #VOUCH Vouch ON PM.VCHNUMWK = Vouch.VCHNUMWK AND PM.DEX_ROW_ID = Vouch.Dex_Row_ID
‘
/* Select all the data in the temp table so that it can be returned to VBA and displayed on screen*/
Set @SQLScript = @SQLScript + ‘SELECT * FROM #VOUCH
‘
Set @SQLScript = @SQLScript + ‘DROP TABLE #VOUCH’
–Print (@SQLScript)
Execute(@SQLScript)