Linking a Sales Tax Detail to a Vendor

Follow this method and you can create PM Transactions to the taxing authorities to remit sales tax, no re-keying required.

Recently I responded to a post on the GP Community Forum from a user wanting to tie a vendor to the sales tax details so that he could automatically create a payables document similar to what is done when you pay something with a credit card.

I thought that was a really good idea and set out to find a way to do it. You need Integration Manager to accomplish it, but it’s easy to set up.

Prerequisites:

Tax Details

You must assign a general ledger account to each Tax Detail you use to calculate tax. The GL account must be unique per tax authority payee.  For example, if I pay the city of Dallas for one tax detail, and pay the county tax assessor for another, you must have two different accounts. This is needed because you are going to assign a vendor to the GL account assigned to the Tax Detail. Each account will represent a single tax collector.

You do not need a unique account for each Tax Detail, just a unique account for each vendor.

Account Maintenance

For each liability account to a Tax Detail, record the Vendor ID in one of the User Defined fields on the Account Maintenance window. I used UserDefined1 in my example.

The Join

You are going to create a SQL statement that matches the account on the Tax Details card with the Account on the Account Maintenance screen. You are also going to match UserDefined1 with the Vendor Master. This will marry a vendor to each tax detail. Now, include whatever tables are needed to retrieve the sales tax amount in whatever period you are working with and use the Integration Manager (or eConnect, etc) to create a payables transaction for the resulting amount.

I used the SQL statement below to retrieve the sales tax detail amount on historical SOP Invoices. I hard coded the date range to select documents with an invoice date between the first day of the previous month and the last day of the previous month, you wouldn’t want to do that. I just put it in here to show how it is done. Voided documents were excluded:

/* This query returns the sales tax amount on historical SOP Invoices with an invoice
   date between the first day of the previous month and the last date of the
   previous month. Voided documents are excluded.
  
   It uses the following tables:
  
   SOP30200    Sales Transaction History
   SOP10105    Sales Taxes Work and History
   GL00100    Account Master
   GL00105    Account Index Master
   PM00200    Vendor Master
   TX00201    Sales/Purchases Tax Master  
*/

SELECT    
  CASE SOP30200.SOPTYPE
     WHEN 1 THEN ‘Quote’
     WHEN 2 THEN ‘Order’
     WHEN 3 THEN ‘Fulfillment Order’
     WHEN 4 THEN ‘Invoice’
     WHEN 5 THEN ‘Return’
  END AS Doc_Type
, SOP10105.SOPNUMBE AS SOP_Number
, SOP30200.DOCDATE AS Invoice_Date
, SOP30200.CUSTNMBR AS Customer_ID
, SOP30200.CUSTNAME AS Customer_Name
, GL00100.USERDEF1 AS User_Defined1
, PM00200.VENDNAME AS Vendor_Name
, GL00105.ACTNUMST AS GL_Account_Number
, SOP10105.TAXDTLID AS Tax_Detail_ID
, SOP10105.STAXAMNT AS Sales_Tax_Amt
, SOP10105.FRTTXAMT AS Tax_on_Freight
, SOP10105.MSCTXAMT AS Tax_on_Misc
, DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE())) as FirstDayPrevMo
, DATEADD (d,-DATEPART(d,GETDATE()),GETDATE()) as LastDayPrevMo

FROM

SOP10105 INNER JOIN
        SOP30200 ON SOP10105.SOPTYPE = SOP30200.SOPTYPE
    AND SOP10105.SOPNUMBE = SOP30200.SOPNUMBE
      INNER JOIN GL00100 ON SOP10105.ACTINDX = GL00100.ACTINDX INNER JOIN
                     TX00201 ON SOP10105.TAXDTLID = TX00201.TAXDTLID INNER JOIN
                     PM00200 ON GL00100.USERDEF1 = PM00200.VENDORID INNER JOIN
                     GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX
WHERE    

(SOP30200.SOPTYPE in (3,4)) and  VOIDSTTS = 0 
GO
 
Until next post!

Leslie

View article:
Linking a Sales Tax Detail to a Vendor

September 22, 2013 · Leslie Vail · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 267

First and Last day of Previous Month

In the accounting world I so often need to limit my selection criteria to the first and last day of the previous month. This is certainly not a secret formula, but I now have a place where I can look it up. Sadly, I have not memorized this formula

First Day of Previous Month:

DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE()))

Last Day of Previous Month:

DATEADD(d,-DATEPART(d,GETDATE()),GETDATE())

Example:

SELECT * FROM SOP30200
WHERE  

VOIDSTTS = 0 and DOCDATE between
DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE())) and
DATEADD(d,-DATEPART(d,GETDATE()),GETDATE())

Until next post!

View article:
First and Last day of Previous Month

September 21, 2013 · Leslie Vail · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 165

VST: Updating your Visual Studio Tools Web Client Development Environment

Microsoft Dynamics GP 2013 SP2 (12.00.1482) introduced some really cool new updates to a lot of application components. Yesterday, for example, I talked about the SmartList Designer and how it really takes the SmartList experience to new heights.

Visual Studio Tools and Microsoft Dynamics GP 2013 SP2 introduce support for WinForms rendering on the Web Client – more of that in my next article, but before we get into the details, here’s what you need to know to prep your development environment:

1. You must install Visual Studio Tools for Microsoft Dynamics GP Service Pack 2 in your system. The VST msi update can be found under MDGP2013_SP2_DVD_ENUSToolsSDKVS ToolsUpdates folder in the full DVD image available from CustomerSource or PartnerSource. This should bring the VST components to version 12.00.1488.00 while updating all templates in your Visual Studio development environment.

Visual Studio Tools SP2 build number check Control Panel | Programs

2. You must apply Microsoft Dynamics GP 2013 SP2, which in turn should upgrade the Web Client Runtime components on your session host machine(s). The Microsoft Dynamics GP 2013 SP build number is 12.00.1482 and the Web Client Runtime assemblies will be updated to version 1.0.170.0.

Web Client Runtime build number check under About Microsoft Dynamics GP | Additional | Web Client

NOTE: If you are, in addition, doing Dexterity development you must follow the procedures outlined in http://support.microsoft.com/kb/910527 to complete the update of your Dex application, since SP2 will also update the Dynamics dictionary.

3. Finally, you can upgrade the Web Client components themselves, by running the msi found under MDGP2013_SP2_DVD_ENUSAdProdWebClientUpdates on the full DVD image on your IIS server. The Web Client components should be at version 1.0.516.0 after the update is completed.

Web Client build number check under Control Panel | Programs

If you are anything like me, all this stuff runs on your development laptop. However if you are a part of a dev team, you should determine where each of these components reside in your environment and update accordingly.

For more information on Microsoft Dynamics GP 2013 SP2 take a look at my article Microsoft Dynamics GP 2013 Service Pack 2 is now available.

For additional information on updating your Visual Studio Tools applications to Service Pack 2, you will want to follow instructions outlined in the Upgrading an Integration section of the Getting Started chapter of the Visual Studio Tools for Microsoft Dynamics GP content online at MSDN Library.

-//-

In my next article, I will talk about rendering Visual Studio Tools’ WinForms in the Microsoft Dynamics GP Web Client.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Visit site:
VST: Updating your Visual Studio Tools Web Client Development Environment

September 6, 2013 · Mariano Gomez · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 214

Troubleshooting the Microsoft Dynamics GP 2013 Web Client – Wrap Up

Series Wrap Up



It’s been a couple of exciting weeks reviewing the position, procedures, and tools available for troubleshooting the Microsoft Dynamics GP 2013 Web Client and I hope that you walked away with an idea of where to turn and what it takes to resolve your issues.

Troubleshooting may not be glamorous or exciting and frankly a lot of folks down right don’t like it, but I personally find it to be a challenge. I like when stuff breaks – figuratively speaking – knowing that you (and a handful of others) are the only one capable of fixing it. Also, it is awesome when you can showcase your Angus MacGyver (yes, his first name was Angus!) ingenuity by using resources and tools that have been in the public domain for a while, “common knowledge” if you will, and can now be applied in the context of GP.

I want to synthetize the series by providing a link to all the articles covered, below:

Part 1: Microsoft Dynamics GP Support Team’s Posture

Part 2: Resolving Microsoft Dynamics GP 2013 Web Client Implementation Issues

Part 3: Resolving Microsoft Dynamics GP 2013 Web Client Functional Issues

Part 4: Tools for Troubleshooting Web Client issues: the Web Client Diagnostic tool

Part 5: Tools for Troubleshooting Web Client issues: Fiddler

Part 6: Tools for Troubleshooting Web Client issues: Other Tools

Part 7: Tools for Troubleshooting Web Client issues: Command Line Tools

Well, I am preparing for a new series of articles, but in the mean time stay tuned.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More:
Troubleshooting the Microsoft Dynamics GP 2013 Web Client – Wrap Up

July 30, 2013 · Mariano Gomez · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 185

Round 5 of the 2013 U.S. Payroll Tax Update is now available for Microsoft Dynamics GP2010 and GP2013

Microsoft just released round 5 of the 2013 Payroll Tax updates. This update is supported only on GP2010 or GP2013, if you are using version 10, or any previous version, do not attempt to install it.

A summary of the changes in this update are as follows:

Round 5 tax table changes

A summary of all updates for 2013 are as follows:

previous tax table updates in 2013

You can update your system using the following navigation:

Microsoft Dynamics GP | Maintenance | U. S. Payroll Updates | Check for Tax Tables

To find the Last Tax Table Update Date and last Tax Code Update Date, follow this navigation:

Microsoft Dynamics GP | Tools | Setup | System | Payroll Tax

Download addresses if you want to install manually, or read more about this and previous changes go to this web site:

Round 5: https://mbs.microsoft.com/customersource/downloads/taxupdates/tugp2010.htm?printpage=false#download

Enjoy!

Leslie

Visit link:
Round 5 of the 2013 U.S. Payroll Tax Update is now available for Microsoft Dynamics GP2010 and GP2013

July 2, 2013 · Leslie Vail · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 267

The ‘Other List’ is out and I’m in trouble!

 

Well, the ‘Other List’ was released today, and my name was on it. I fear now that my awesomeness has just taken a critical (if not mortal) hit.

help

I need help. The clock is ticking. I need to do something awesome enough for Doug Pitcher to overlook the infraction. OR . . . I understand that cash is always an acceptable bribe. Where shall I fax the money? Smile

Fight the good fight Doug, your public awaits your announcement.

Until next post!

Leslie

View the original here:
The ‘Other List’ is out and I’m in trouble!

May 8, 2013 · Leslie Vail · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 203

Microsoft Convergence New Orleans 2013: Wrap Up

Convergence is over and I am now back to the daily grind. However and as is customary, this wrap up post serves as a venue to provide some insight into this past event, links to all posts, and of course, suggestions for the next.

First and foremost, a big thank you goes out to my friends at Microsoft for inviting me to this year’s Convergence event as a speaker and presenter at the General Session. This is probably one of the highlights of my career in the channel. In particular, I want to send out a big shout-out to the following folks (in no particular order):

  • Aaron Donat, for accepting the co-presenter role with me at GPPC’s PreGAME and to help me in continuing building my understanding and knowledge of the Web Client.
  • Pam Misialek, for all her guidance throughout the process leading up to the General Session presentation.
  • Kevin Racer and Daryl Anderson, for inviting me as a co-presenter in our session CSGP32 – Microsoft Dynamics GP 2013 Web Client For Smaller Companies. Sorry I came down ill on our first go around, but hopefully the second time was great.
  • Errol Schoenfish, for his great introduction of Mark and I and the duel we were about to engage in.
  • Chad Sogge, for his participation at the MVP Breakfast
  • David Musgrave, for his patience in preparing me for the Support Debugging Tool session and for dedicating a great deal of time in making the pre-build 18 release available for the session.

I can’t thank the rest of you at Microsoft enough for your continued camaraderie and support: Jay Manley, Jennifer Ranz, Theresa Nistler, Brian Meier, Sivakumar Venkataraman, Jared Hall, Brian Roney, Sarah Purdy, Kelly Youles, and the list goes on and on, so please forgive me if I omitted your name here. You all contributed to making this a great Convergence for me and my wife.

Special thanks to Brian Graftaas for conceding some time on his busy Convergence calendar to talk about the Certified for Microsoft Dynamics program.

Outside of Microsoft, I would like to extend a big thank you to Kim Peterson and Andy Hefer at DynamicCommunities for inviting me to GPPC PreGAME as a presenter, MVP Mark Polino for accepting the challenge of presenting with me at General Session, though we only had a few hours to rehearse in person, MVP Jivtesh Singh for his guest appearance during my session CSGP30 – The Microsoft Dynamics GP Support Debugging Tool,

Now, the links…

On this site, you can find coverage of Convergence using the following links:

Microsoft Convergence New Orleans 2013 – Getting there

Microsoft Convergence New Orleans 2013 : GPPC PreGAME

Microsoft Convergence New Orleans 2013: Day 1

Microsoft Convergence New Orleans 2013: Day 2

Microsoft Convergence New Orleans 2013: Day 3

Microsoft Convergence New Orleans 2013: Day 4

You can always take a look at some of the pictures I took over at The Dynamics GP Blogster on Facebook:

Microsoft Dynamics Convergence 2013 Album

Over at DynamicAccounting.net, Mark Polino has some thoughts about the event after couple days of review:

Convergence 2013 Monday

Convergence Update from Tuesday

My Convergence 2013 Thoughts

MVP Leslie Vail, over at Dynamics Confessions has some cool pictures and offer some insights as well:

Convergence 2013 Day Zero

David Musgrave, we miss you!

Volunteer Day at Convergence 2013

Kofi Annan Convergence 2013 Keynote Speaker

MVP Belinda Allen, over at The GP CSI has the following entries on Convergence:

So it begins… Microsoft Dynamics GP Convergence

Microsoft Dynamics Convergence for GP begins today

Microsoft Dynamics GP Customer Story – Shock Doctor

Microsoft Convergence Keynote brief

Microsoft Dynamics GP Project Accounting Convergence Session

Microsoft Dynamics Convergence for GP update – FREE EXCEL DASHBOARD

The Learning Resources page of this blog has been updated with latest Convergence 2013 materials I presented for your  consumption. Please download here:

The Dynamics GP Blogster: Learning Resources

Scroll to the end of the page and click on any of the links to download.

Finally, visit the Virtual Convergence site for links to my presentations. As of this article, they all seem to be up and available, including the General Session.

Virtual Convergence 2013

As you already know, Microsoft Convergence 2014 will be held in the beautiful city of Atlanta, my home base. I hope to see you all here next year.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

View the original here:
Microsoft Convergence New Orleans 2013: Wrap Up

March 25, 2013 · Mariano Gomez · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 327