Rebuilding Tax History

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/