How to update Unemployment tax in MS Dynamics GP.

Unemployment tax is not imported or changed by the payroll tax table update process.  You must update this amount manually.

You received an Employer Tax Rate Notice from your state unemployment division.  Typically you will see a box near the top of the form with a label Total Tax Rate is: 1.74% or some number.  Also, you will see Taxable Wage Base is: $8500.00 or some number for your state.

Click on Tools – Setup – Payroll – Unemployment Tax

MS Dynamics GP how to print W2′s at year end.

After all pay runs for the year create the year end wage file.  Tools – Routines – Payroll – Year End Closing.  Input the year and click Process.  In GP you can run this step as many times as needed.  So you can create the file and check reports, make adjustments via payroll manual check or adjusting entries and run the close again.  Here is a sample screen shot where I created the year end file again for 2014.

You can now proceed forward with pay runs in the new year.  You can print your year end wage report, edit w2′s, and print w2′s at any time.

To print the year end wage report go to Tools – Routines – Payroll – Year End Wage Report.

To edit W2′s go to Tools – Routines – Payroll – Edit W2′s.  Why would you need to edit W2′s.  You might need to typically correct a SSN for an employee, an employee address, or a state wage gross amount. If you change an SSA or address you are not correcting this in the master so you should also make those corrections in the payroll master cards.  State gross wages sometimes need to be corrected because at some companies it is popular to pay bonuses and not take federal and state taxes.  If you don’t take state taxes the gross for the state will not be correct on the W2.  I never recommend to run bonus pay without taking state tax.

Next you can print W2′s.  You can print W2′s and the W3 summary to plain paper and check them out if you like.  Or if your feeling lucky you can print a few copies to plain paper and make sure the boxes line up to the W2 forms you purchased for the year.  There are two formats I recommend.  The laser one wide single feed is a six part laser form, with two W2′s on the page.  Using this form you will print the W2′s six times.  If you use this form print the W2′s to the screen first.  Then you will leave the print screen open and just print six times from this screen; it will save you a lot of time.

The other popular format is the four up laser W2  which has four W2′s on each laser page.  If you use this form you will only print the W2′s once.

Here is a sample print screen below:

Make sure to double check your Federal identification number.  Also enter the control number; usually enter and start at 1 each time. Click your desired sort order.  And make sure to click the radio button for W2 forms otherwise you might just print the validation report all day which might become boring.

Once you have printed all your W2 forms run the program one more time and click on the radio button for W3 transmittal.  You can print this to plain paper or use the laser W3 form.

MS Dynamics GP changing deduction rates at the end of the year.

You can use Quick Assignment at the end of the year to enter new payroll deduction rates for employees.  Cards – Payroll – Quick Assignment.  For example enter a new rate and start date of 1/1/2014 as below.  Change the Deduction amount for EPU from $15 to $20 with a starting date of 1/1/2014.

As soon as you click OK, the deduction card is changed for the employee as below.  There is no process like activating post dated pay rates.  The starting date on the deduction is changed and the amount is changed immediately.  So if you run payroll now with a build date earlier than the start date the deduction won’t come out. The immediate result of clicking OK on the above screen is below.

So, lets expand the scenario. For example, your payroll date range for the last payroll of the year is 12/18/2013 through 12/31/2013.  Your check date will be 1/6/2014.  You have new payroll deduction rates effective 1/1/2014.

If you do your payroll build with what you think is the proper date range none of your new deduction amounts will come out.

So what do you do?  Well first, you can not use Quick Assignment. Second, you could just wait until 12/31 and make all the deduction changes in each payroll card.  Or you can use Quick Assignment which is a lot easier when changing large amounts of data for each employee.  Just change the build date range to go from 12/18/2013 to 01/01/2014 as below.

 

Sampe VBA Excel Macro with loop to update MS Dynamics GP table.

Here is a full example of the VBA sample code with a loop.  This VBA code loops through an Excel spreadsheet and inserts records in the MS Dynamics GP Payroll Posting Accounts table UPR40500. In this case we only insert accounts for Gross Pay.

The VBA sample code also create a record set named (rs) from the GL00100 table that will provide the GL account index for the full account.  The full GL account is matched to the record in the GL00100 table to get the account index.

Comment lines begin with an apostrophe (‘).

Public Sub GetAccounts()

‘Define ADO connection object and recordset object
Dim cn As ADODB.Connection, rs As ADODB.Recordset

‘set cn (connection) to hold database connection string
‘set rs (recordset) to hold the results for the retrieved record set ‘from SQL
Set cn = New ADODB.Connection
cn.Open “Provider=’SQLOLEDB’;Data Source=’dh-PC’;Initial Catalog=’TWO’;” & _
“Integrated Security=’SSPI’”
Set rs = New ADODB.Recordset

‘strSQL will hold the select SQL statemment to run
‘rs.open opens the record set with strSQL, and connection cn
Dim strSQL As String

‘ declare other variables
Dim strDept As String
Dim strPosition As String
Dim strPayCd As String
Dim strMainAcct As String
Dim strPayType As Integer
Dim strAcctIndx As Integer
Dim strFullAcct As String
Dim rsFullAcct As String

‘Read cells from payroll accounts sheet starting in row 2
Dim i As Integer
i = 2
strDept = ActiveWorkbook.Sheets(“PRA”).Cells(i, 1).Value
strPosition = ActiveWorkbook.Sheets(“PRA”).Cells(i, 2).Value
strPayCd = ActiveWorkbook.Sheets(“PRA”).Cells(i, 3).Value
strMainAcct = ActiveWorkbook.Sheets(“PRA”).Cells(i, 4).Value
strPayType = ActiveWorkbook.Sheets(“PRA”).Cells(i, 5).Value

Let strSQL = “select * from GL00100″
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
‘Use a MsgBox to display a messge and field contents for debuging, ‘remove the apostrophe in from the MsgBox statement below to see ‘how it works.

‘MsgBox strSQL

‘Process all the rows in the spreadsheet
Do Until IsEmpty(Cells(i, 1))

‘step through the record set and perform required actions on the records
‘reference the fields using the column name from the SQL table
Do While Not rs.EOF
‘retrieve the GL account index from the first row of the dataset
strFullAcct = strDept & strMainAcct & strPosition
rsFullAcct = Left(rs!ACTNUMBR_1, 3) & Left(rs!ACTNUMBR_2, 4) & Left(rs!ACTNUMBR_3, 2)
If strFullAcct = rsFullAcct Then intID = rs!ACTINDX
‘move to the next record in the recordset
rs.MoveNext
Loop

‘strAcctIndx holds the account index retrieved from the record set
‘set the department, position, pay code, pay type 1 = gross pay
strAcctIndx = intID

‘adjust department and position code strings to proper length
strDept = Right(strDept, 2)
strPosition = Right(strPosition, 2)

‘create the sql string to insert the record into the Payroll posting accounts table
strSQL = “INSERT INTO UPR40500 (DEPRTMNT,JOBTITLE,PAYROLCD,UPRACTYP,ACTINDX) VALUES”
strSQL = strSQL & “(” & strDept & “,” & strPosition & “,’” & strPayCd & “‘,” & strPayType & “,” & strAcctIndx & “)”
‘MsgBox strSQL
cn.Execute strSQL

i = i + 1
strDept = “”
strPosition = “”
strPayCd = “”
strMainAcct = “”
strPayType = 0
strAcctIndx = 0
strFullAcct = “”
rsFullAcct = “”

strDept = ActiveWorkbook.Sheets(“PRA”).Cells(i, 1).Value
strPosition = ActiveWorkbook.Sheets(“PRA”).Cells(i, 2).Value
strPayCd = ActiveWorkbook.Sheets(“PRA”).Cells(i, 3).Value
strMainAcct = ActiveWorkbook.Sheets(“PRA”).Cells(i, 4).Value
strPayType = ActiveWorkbook.Sheets(“PRA”).Cells(i, 5).Value

Loop

MsgBox “Import Completed”

‘close the open record set
rs.Close:   Set rs = Nothing
‘close the connection to the database
cn.Close:   Set cn = Nothing
End Sub

MS Dynamics GP sql script to update the Direct Deposit master.

Employees may be inactive in the employee master table but show active records in the direct deposit tables. Use this script to identify employees that are inactive in the employee master table UPR00100, and that may have active direct deposit records in the direct deposit master DD00100 table, and the direct deposit account master DD00200 table.

SELECT dbo.DD00100.EMPLOYID, dbo.DD00100.INACTIVE, dbo.DD00200.EMPLOYID, dbo.DD00200.INACTIVE, dbo.UPR00100.EMPLOYID, dbo.UPR00100.INACTIVE FROM
dbo.DD00200 inner join dbo.UPR00100 on dbo.UPR00100.EMPLOYID = dbo.DD00200.EMPLOYID
inner join dbo.DD00100 on dbo.DD00100.EMPLOYID =
dbo.UPR00100.EMPLOYID

Script to set Inactive the records in the DD00100 and DD00200 that should be inactive.

update dbo.DD00100 set dbo.DD00100.INACTIVE = ’1′ where dbo.DD00100.EMPLOYID in (SELECT dbo.DD00100.EMPLOYID FROM dbo.DD00100 inner join dbo.UPR00100 on dbo.UPR00100.EMPLOYID = dbo.DD00100.EMPLOYID
where dbo.UPR00100.INACTIVE = ’1′)

update dbo.DD00200 set dbo.DD00200.INACTIVE = ’1′ where dbo.DD00200.EMPLOYID in (SELECT dbo.DD00200.EMPLOYID FROM dbo.DD00200 inner join dbo.UPR00100 on dbo.UPR00100.EMPLOYID = dbo.DD00200.EMPLOYID
where dbo.UPR00100.INACTIVE = ’1′)

In the Select subquery after the Update only one column reference is allowed.

 

 

MS Dynamics GP error on 941 report line 7a.

If you changed the Social Security rate to 4.2% on January 1, 2011 before the Round 2 Tax update then both the Employee and Employer rate was calculated at 4.2%.

Let’s then say you put on the Round 2 Tax update February 1, 2011.  You therefore have 1 month on which the collected matching employer social security was under reported or under collected.

Line 7a Current quarter’s fractions of cents will show you the uncollected difference.

Simply reverse the sign since it should be negative in this case and add back to line 8. Therefore line 6e and line 10 should be equal.

If you made your tax deposits from the Payroll journal then Line 7a is the amount you still owe.  If you manually adjusted your deposit to account for the 2% difference for the company matching then you should owe nothing.

MS Dynamics GP how to void a payroll check in a prior year.

Here is the trick. Change your GP user date to a date in the last payroll year.

Go to Transactions – Payroll – Void Checks.

Select the check number to void.  As a general rule you would want to void the check from the employee in the year in which the wages were paid, in this case last year.  This is the Check Date field and is required.  Since you changed the user date you can now override the date.

The Posting Date is the general ledger date.  As a general rule that would be a date in your current fiscal year.  Check with your CFO or accounting manager to determine the correct period for posting.

Click the Void check box next to the check and Process.  The wages, deductions, and taxes will be backed out to the previous years payroll information.  The general ledger will be reversed in the current year.

 

MS Dynamics GP payroll deduction sequence not working.

If you are having problems with Employee deductions not calculating properly and/or the net pay not coming out correctly try this trick.  Go to Cards – Employee – Deduction.  Click on Deduction Sequence.  Instead of using the Employee sequence click on Company and Save.

In Tools – Setup – Payroll – Deduction Sequence setup the deduction sequence you want. This should solve the problem.

All TSA, Garnishments, and 401k deductions should be inserted first.  Then insert the other deductions in the order you want.

MS Dynamics GP how to setup a shift code for payroll.

Use a differential shift pay code to automatically add premium pay to a pay code in MS Dynamics GP.

For example, you have a pay code setup for hourly pay, an employee that works on the weekend is allowed an additional .50 cents per hour additional pay.

Each pay code in MS Dynamics GP can have one attached shift pay code.  If you need multiple shift codes for hourly pay for example, you will need to setup multiple hourly pay codes to cover those varied situations.

Here are the steps to use shift codes in MS Dynamics GP.

1) Go to Tools – Setup – Payroll – Shift Code.  Enter the shift code id, ex. WKEND, enter a description for example Week End Hourly Shift. Click on Amount or Percentage. In this case we will use Amount and enter .50 cents.

2) Now assign the shift code to the pay code.  Here you have two choices.  To permanently assign the shift code to a pay code go to Tools – Setup – Payroll – Pay Code.  Pull up the pay code and fill in the shift code.  Now when you assign the pay code to an employee the shift code will automatically be attached.  As an alternative you can go to Cards – Payroll – Pay Code, pull up the employee, pull up the pay code, then fill in the shift code.

3) When the pay code is processed in the payroll transaction batch in Transactions – Payroll – Transaction Entry the premium hourly pay from the shift code will automatically be included.

4) Of course you can always key in the shift code on the fly as you are doing payroll transaction entry.

MS Dynamics GP – What to do if you overpay an employee in Dynamics GP

These are the proper steps to follow when you overpay an employee.  At this point you have given the check out; it’s to late to get the check or direct deposit back from the employee. 

Problem:

1) How do you correct the employees YTD gross, fica, fed, state, etc?  How do you correct the matching company taxes?

2)If you simply do a payroll tax deduction for the gross amount of the overpayment  in the next pay run you are penalizing the employee and the company.  You are taking the gross amount from him, and he still pays the fed, state, fics, medicare, etc on amount.  The company is still out the matching taxes.

Solution:

1)      For the amount of the over payment, create a payroll transaction batch and key in just the employee and the pr code for the gross amount of the overpayment.

2)      Run a pr build for just that employee, with no deductions and benefits marked. Mark only the batch you just created.  In the ranges for the build select just the employee in quesiton.

3)      Run Calculate Check and print the payroll precheck report for the employee.  You will use this report in a later step.

4)      Remove the build you just did.

5)      Go to Transactions – Payroll – Manual Checks

  1. You will enter a negative manual check that mirrors the precheck report.
  2. Key in all amounts as negative.
  3. Do the gross pay first, make sure to key negative in the gross and all fields.
  4. Do FICA SS, make sure to key the employee amount negative, then in the EFIC key the matching company amount negative at gross times 6.2%.  Also, key the taxable wages amount as negative. This is necessary since the employee rate is 4.2% and the company matching rate is 6.2%
  5. Repeat for FICA Med.  These amounts will be the same for both the employee and the company.
  6. Repeat for State Tax.
  7. Repeat for Fed Tax.
  8. When you are done the front screen should mirror the precheck report exactly except with negatives.  Negative gross and net should match exactly.
  9. Post.
  10. After the result of this you have fixed the employee and company liability.  You will notice the Check Register will show all the taxes and company liability as negative.

6)      Create a new payroll deduction code for the employee.  It should be sheltered from all taxes as you want this deduction to come out first before any taxes are calculated on it.  Enter the single amount of the deduction to be taken back from the employee for the overpayment.

7)      In the new pay run make sure to include this new deduction.

8)      As a result of this the gross amount of the overpayment will be deducted back from the employee, and no FICA or Fed is calculated on it.

9)      In the payroll posting accounts for the deduction make sure to set the gl account you want it to go to if necessary.

 Example:  Assume we have overpaid an employee $200, and deducted all taxes.  This example assumes there was no 401k decution or match.  The taxes calculated for this employee are FICA SS $8.39, FICA Medicare $2.90, Federal Withheld $8.85, and MI State Withheld $5.60.  The employee total deductions are $25.74.  The net check is $174.26. We then proceed to do the negative manual check.

On the final screen we have the following result, then we Post.