Home » Listing Details
Top Websites
  1. Dynamics GP Help
    Over 5900 resources listed.
  2. Mark Polino's DynamicAccounting.net
    Over 5100 resources listed.
  3. Rose Business Solutions Blog New
    Over 2200 resources listed.
  4. Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
    Over 1100 resources listed.
  5. Mariano Gomez at The Dynamics GP Blogster
    Over 900 resources listed.
  6. Microsoft Dynamics Partner Community Blog
    Over 900 resources listed.
  7. Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
    Over 700 resources listed.
  8. Mohammad Daoud's Dynamics GP Blog
    Over 600 resources listed.
  9. Vaidy Mohan at Dynamics GP - Learn & Discuss
    Over 500 resources listed.
  10. Inside Microsoft Dynamics GP Official Blog
    Over 500 resources listed.
  11. eOne Business Solutions Blog
    Over 400 resources listed.
  12. About Dynamics, Development and Life
    Over 300 resources listed.
  13. Frank Hamelly at GP2theMax
    Over 300 resources listed.
  14. Dynamics CPM
    Over 300 resources listed.
  15. BKD Dynamics GP Insights Blog
    Over 200 resources listed.
  16. Leslie Vail at Dynamics Confessor Blogspot
    Over 200 resources listed.
  17. Victoria Yudin's Dynamics GP Website
    Over 200 resources listed.
    Victoria Yudin
  18. Janakiram M.P. at DynamicsBlogger
    Over 100 resources listed.
  19. VS Tools Forum
    Over 100 resources listed.
    Your Resource for Visual Studio Tools for Dynamics GP
  20. Inside Microsoft Dynamics GP Official Blog
    Over 100 resources listed.
  21. US Dynamics GP Field Team Blog
    Over 100 resources listed.
  22. Catherine Eibner MBS Developer Evangelist
    Over 100 resources listed.
  23. Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
    Over 100 resources listed.
  24. Dynamics Small Business
    Over 100 resources listed.
  25. Belinda, The GP CSI
    Over 100 resources listed.

ID:11211
Title:Rebuilding Tax History
URL:http://dynamicsgpblogster.blogspot.com/2009/01/rebuilding-tax-history.html
Description:
Business Situation

A few days ago, a user was facing an issue in which apparently, their tax history was not being recorded or was missing for a good number of transactions created in Sales Order Processing. In addition, the user wanted to use Dynamics GP's built-in tax reports as they did exactly what she needed.

Of course, the question came in: How can I rebuild my Tax History?

Solution

One thing I love about Dynamics GP is the fact that information created in the subsidiary modules, flow to other parts of the application tables when GP is instructed to do so. This instruction comes via the configuration options in the system.

In this particular case, when Sales Order Processing transactions are created and taxes are calculated for those documents, the tax detail information is stored in the Sales Tax Work and History table (dbo.SOP10105).

When invoices and returns are posted, and if history is being maintained for the tax details on these documents, the resulting tax records are then maintained in the Tax History table (dbo.TX30000) with tax information calculated from other subsidiaries.

With this in mind, the following T-SQL query will rebuild the missing sales tax information for all invoices that are not currently in the Tax History table.



/* 2009. Created by Mariano Gomez, MVP
This code is provided "AS IS" with no warranties expressed or implied

To be executed against your company database
*/
WITH SOPDocs(SOPNUMBE, SOPTYPE, DOCDATE, Tax_Date, GLPOSTDT, DOCAMNT, ECTRX, VOIDSTTS, CUSTNMBR) AS (
SELECT SOPNUMBE, SOPTYPE, DOCDATE, Tax_Date, GLPOSTDT, DOCAMNT, ECTRX, VOIDSTTS, CUSTNMBR FROM SOP10100 WHERE SOPTYPE = 3
UNION ALL
SELECT SOPNUMBE, SOPTYPE, DOCDATE, Tax_Date, GLPOSTDT, DOCAMNT, ECTRX, VOIDSTTS, CUSTNMBR FROM SOP30200 WHERE SOPTYPE = 3
)
INSERT INTO TX30000 (
DOCNUMBR,DOCTYPE,SERIES,RCTRXSEQ,SEQNUMBR,TAXDTLID,TXDTLPCT,TXDTLAMT,ACTINDX,DOCDATE,Tax_Date,PSTGDATE,TAXAMNT,ORTAXAMT,Taxable_Amount,
Originating_Taxable_Amt,DOCAMNT,ORDOCAMT,ECTRX,VOIDSTTS,CustomerVendor_ID,CURRNIDX,Included_On_Return,Tax_Return_ID,TXORGN,TXDTLTYP,
TRXSTATS,RETNUM,YEAR1,INVATRET,VATCOLCD,VATRPTID,Revision_Number,PERIODID,ISGLTRX)
SELECT a.SOPNUMBE, a.SOPTYPE, 1, 0, ROW_NUMBER() OVER(PARTITION BY a.SOPNUMBE ORDER BY a.TAXDTLID), a.TAXDTLID, b.TXDTLPCT, b.TXDTLAMT, a.ACTINDX, c.DOCDATE, c.Tax_Date, c.GLPOSTDT, a.STAXAMNT, a.ORSLSTAX, a.TAXDTSLS, a.ORTXSLS, c.DOCAMNT, a.ORTOTSLS, c.ECTRX, c.VOIDSTTS, c.CUSTNMBR, a.CURRNIDX, 0, '', 1, 1, '', '', 0, 0, '', '', 0, 0, 0
FROM SOP10105 a
LEFT OUTER JOIN TX00201 b ON (a.TAXDTLID = b.TAXDTLID)
LEFT OUTER JOIN SOPDocs c on (a.SOPNUMBE = c.SOPNUMBE) and (a.SOPTYPE = c.SOPTYPE)
LEFT OUTER JOIN TX30000 d on (a.SOPNUMBE = d.DOCNUMBR) and (a.SOPTYPE = d.DOCTYPE)
WHERE (a.SOPTYPE = 3) and (a.LNITMSEQ = 0) and (d.DOCNUMBR IS NULL)



With minor tweaks, you should be able to get the above code to run for Return documents.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
Category:GP USER INFO: Taxes
Link Owner:
Date Added:June 17, 2010 06:13:02 PM
Number Hits:2
RatingsAverage rating: (0 votes)
Reviews

No Reviews Yet.

 
GPWindow.com

Thank you for your support for GPWindow. It helps us cover part of the hosting costs for GPWindow.