Category: Dynamics GP

How to apply the payroll tax updates – Dynamics GP

Microsoft has released the most recent tax rate changes for GP Payroll, and you want to get them in your system.  No problem!

It’s not as hard as you think but it does have some requirements and precautions that come along with the process.

Below is a quick step by step guide on how to get this task accomplished, along with some words of ‘wisdom’ (ok, I know, humor me!)

Continue reading “How to apply the payroll tax updates – Dynamics GP”

Dynamics GP Workflow Types – By Version Release

Since the release of Dynamics GP 2013 R2, Microsoft has been reinventing Workflow and adding new Workflow functionality to each subsequent release.

Since a lot of GP Users are not on the most recent version of GP, I am often asked – “What workflows do I have available to me in my current version?”.

I put together a list of Workflows along with their release version, for your reference (and mine LOL).

Continue reading “Dynamics GP Workflow Types – By Version Release”

1099 Year End Edit – SQL View for Dynamics GP

There are many ways to look at 1099 data – here is my recent pass at looking at the actual 1099 Period Detail Table as well as having the 1099 Type and Box information.

Apply this to your company database and bring into SmartLists.

CREATE view [dbo].[TEN99_Audit_List]
as

SELECT A.VENDORID Vendor_ID,
 B.VENDNAME Vendor_Name,
 B.VNDCHKNM Vendor_Check_Name,
 B.VNDCLSID Class_ID,
 case B.VENDSTTS
 when 1 then 'Active'
 when 2 then 'Inactive'
 when 3 then 'Temporary'
 end Vendor_Status,
 case A.TEN99TYPE
 when 1 then 'Not a 1099 Vendor'
 when 2 then 'Dividend'
 when 3 then 'Interest'
 when 4 then 'Miscellaneous'
 end Type,
 case --Set 1099 Box Number Values
 when A.TEN99TYPE = 1 then 'Not a 1099 Vendor' 
 when A.TEN99BOXNUMBER = 0 then 'Not a 1099 Vendor'
 --1099 Type Dividend Box Numbers
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 1 then '1a Ordinary Dividends'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 2 then '1b Qualified Dividends'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 3 then '2a Capital Gain Dist.'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 4 then '2b Unrecap. 1250 Gain'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 5 then '2c Section 1202 Gain'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 6 then '2d 28% Rate Gain'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 7 then '3 Nontaxable Dist.'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 8 then '4 Federal Tax Withheld'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 9 then '5 Investment Expense'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 10 then '6 Foreign Tax Paid'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 11 then '8 Cash'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 12 then '9 Noncash'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 13 then '10 Exempt Interest Dividend'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 14 then '11 Specified Private Activity'
 when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 15 then '14 State Tax Withheld'
 --1099 Type Interest Box Numbers
 when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 1 then '1 Interest'
 when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 2 then '2 Early Withdrawal'
 when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 3 then '3 U.S. Savings Bond Int.'
 when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 4 then '4 Federal Tax Withheld'
 when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 5 then '5 Investment Expense'
 when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 6 then '6 Foreign Tax Paid'
 when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 7 then '8 Tax-Exempt Interest'
 when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 8 then '9 Private Activity Bond Int.'
 when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 9 then '10 Market Discount'
 when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 10 then '11 Bond Premium'
 when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 11 then '12 Bond premium on Treasury obligations'
 when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 12 then '13 Bond premium on Tax-exempt bond' 
 when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 13 then '17 State Tax Withheld'
 --1099 Type Miscellaneous Box Numbers
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 1 then '1 - Rents'
 When A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 2 then '2 - Royalties'
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 3 then '3 - Other Income'
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 4 then '4 - Federal Tax Withheld'
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 5 then '5 - Fishing Boat Proceeds'
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 6 then '6 - Medical Payments'
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 7 then '7 - Nonemployee Compensation'
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 8 then '8 - Substitue Payments'
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 9 then '10 – Crop Insurance'
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 10 then '13 – Golden Parachute'
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 11 then '14 – Attorney Proceeds'
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 12 then '15a – Section 409A Deferrals'
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 13 then '15b – Section 409A Income'
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 14 then '16 - State Tax Withheld'
 when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 15 then '18 - State Income'
 end [1099_Box_Number],
 
 B.TXRGNNUM Tax_Registration_Number,
 B.PYMTRMID Payment_Terms_ID,
 A.YEAR1 [Year],
 sum(A.TEN99AMNT) Amount_Paid_1099,
 B.ADDRESS1 Address_1,
 B.ADDRESS2 Address_2,
 B.ADDRESS3 Address_3,
 B.CITY City,
 B.[STATE] [State],
 B.ZIPCODE Zip_Code,
 B.COUNTRY Country,
 B.TXIDNMBR Tax_ID
 
FROM PM00204 A
 
INNER JOIN PM00200 B ON A.VENDORID = B.VENDORID
 
 
GROUP BY A.VENDORID, B.VENDNAME, B.VNDCLSID,
 B.VENDSTTS, A.TEN99TYPE, B.PYMTRMID, A.YEAR1, B.ADDRESS1, B.ADDRESS2,
 B.ADDRESS3, B.CITY, B.[STATE], B.ZIPCODE, B.COUNTRY, B.TXIDNMBR, B.VNDCHKNM, B.TXRGNNUM, A.TEN99BOXNUMBER


GO
grant select on [TEN99_Audit_List] TO DYNGRP

GP #LifeHacks 119: Year End Wage Data in a Smartlist using a SQL View

As year end quickly approaches, I wanted to share with my followers a very helpful #GPLifeHack that turns your year end wage file data into a Smartlist for easy auditing and review!

There is an ‘edit’ report for W2’s but it’s in long form and difficult to review – plus it is not easily exported to Excel.  What exports easily to Excel?  SMARTLISTS!!! YEAH THEY DO!

Let’s make this year-end process easier by creating a Smartlist for year end W2 data! 

Continue reading “GP #LifeHacks 119: Year End Wage Data in a Smartlist using a SQL View”

Year End Payroll Data – SQL View for Dynamics GP

CREATE VIEW [dbo].[Year_End_Wage_Data_Payroll]
AS

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Year End Wage Data
-- Run Against Company Database
-- Only provides data populated by the Year End Closing Routine
-- Visit http://GPLifehacks.com for more cool GP Stuff!
-- Updated by: Shawn Dorward - As of 10/06/2017
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
SELECT
A.RPTNGYR Year1,
A.LASTNAME Last_Name,
A.FRSTNAME First_Name,
A.EMPLOYID Employee_ID,
A.ADDRESS1 Address_1,
A.ADDRESS2 Address_2,
A.Address3 Address_3,
A.CITY City,
A.STATE State,
A.ZIPCODE Zip_Code,
A.WGTPCOMP Gross_Wages,
A.FEDITXWH Federal_Tax_Withheld,
A.SSECWAGS SS_Wages,
A.SSTXWHLD SS_Tax_Withheld,
A.MCRWGTPS Medicare_Wages,
A.MDCRTXWH Medicare_Tax_Withheld,
B.STATECD State_Tax_Code,
B.STATEWGS State_Wages,
B.STATINTX State_Tax_Withheld,
C.LOCLCODE Local_Tax_Code,
C.LOCLWGES Local_Wages,
C.LCLINTAX Local_Tax_Withheld

--Payroll Year End Table
FROM UPR10101 A 

-- Adding Payroll Year End State Table
left outer join
UPR10105 B on A.RPTNGYR = B.RPTNGYR and A.EMPLOYID = B.EMPLOYID 

-- Adding Payroll Year End Local Table
left outer join
UPR10106 C on A.RPTNGYR = C.RPTNGYR and A.EMPLOYID = C.EMPLOYID 

go
grant select on [Year_End_Wage_Data_Payroll] TO DYNGRP

GP #LifeHacks 108: Batch Recovery – Tip to manage this like a pro using Startup folder!

Microsoft Dynamics GP has a great feature that captures posting processes that have issues or get interrupted.  This is EXTREMELY helpful and helps protect GP users against many different reasons a batch would fail to post.

Normally we go into Batch Recover (Microsoft Dynamics GP>Tools>Routines>Batch Recovery) for two reasons:  We either receive an error when the batch tries posting (that we notice) or, at month end, when things aren’t necessarily making sense when reviewing our financials or our reconciliations.

Here is a tip to help make sure that postings that go to ‘Batch Recovery’ are noticed much sooner than month end, and helps save us tons of time!

Continue reading “GP #LifeHacks 108: Batch Recovery – Tip to manage this like a pro using Startup folder!”

Attend my GPUG Summit Nashville breakout sessions! #GPUGSummit

GPUG Summit is right around the corner (October 10-13, 2017) – I cannot wait!  Great city, great people, tons of fun and oh yeah, tons of educational sessions about Microsoft Dynamics GP and much more!

It’s going to be a blast and I hope you join me! 

View the entire breakout session schedule online here. Remember, content is the king of GPUG Summit.

I am extremely fortunate (and excited!!!) to be participating in 7 breakout sessions this year!

Below is a list of these sessions and a link to take you to more details about them!

Continue reading “Attend my GPUG Summit Nashville breakout sessions! #GPUGSummit”

GP #LifeHacks 126: Automatically create bank deposits from Cash Receipts

Over the past few years, many new features of Microsoft Dynamics GP were included with each roll-out.  Because of an accelerated cadence of releases, many of these new features go unnoticed and therefore, unused.

I came across this little nugget the other day and I wanted to post a quick write up to my blog site so that I, and my followers, were reminded of this #GPLifeHack.

Normally folks create cash receipts and then, during a second step, create a deposit to match the batch of cash receipts that they took to the bank.  The cash receipts batch is usually a one to one relationship with the deposit.  So turn your two step process into one by implementing “Automatically post deposits from cash receipts”.

Here is a brief overview of how this feature.

Continue reading “GP #LifeHacks 126: Automatically create bank deposits from Cash Receipts”

GP #LifeHacks 112: SQL View turned Excel Report for Fiscal Period Closing data

I have been using Mass Close for a few years now.  Not many Microsoft Dynamics GP users use it (in my experience).  I like to use it because it is origin specific closing, versus Series specific closing – the later is used by most everyone.

The ‘trouble’ with Series closing is that there are times when you need to enter transactions into certain areas for the previous month.  What folks typically do is open and close the series over and over again to achieve this.  Mass Close eliminates this need and adds extra internal controls to the closing process.

In this blog I’ll cover the mass close option, as well as share a SQL view that I turned into an Excel Report for easy managing of origin status!

Continue reading “GP #LifeHacks 112: SQL View turned Excel Report for Fiscal Period Closing data”

Detailed Period Closing List – SQL View for Dynamics GP

CREATE VIEW [dbo].[Detailed_Period_Closing_List]
AS

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Detailed Period Close Data
-- Run Against Company Database
-- Provides Checklist for Fiscal Period Close Settings - Detailed
-- Visit http://GPLifehacks.com for more cool GP Stuff!
-- Updated by: Shawn Dorward - As of 09/14/2017
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

SELECT
YEAR1 Fiscal_Year,
PERNAME as Period, 
PERIODDT as Period_Start_Date, 
PERDENDT as Period_End_Date, 
case sy40100.Series
 when 2 then 'Financial'
 when 3 then 'Sales'
 when 4 then 'Purchasing'
 when 5 then 'Inventory'
 when 6 then 'Payroll'
 when 7 then 'Project'
 else ''
 end Series,
ODESCTN as Origin_Name,
case sy40100.Closed
 when 0 then 'Open'
 When 1 then 'Closed'
 else ”
end Status,
DEX_ROW_ID as Dex_Row_ID

FROM sy40100
WHERE Pername <> 'Beginning Balance' and series <> 1 and series <> 0

-- Adding Permissions to DYNGRP

GO

GRANT SELECT on [Detailed_Period_Closing_List] to DYNGRP