Dynamics GP - VAT Period Report does not match GL account balances

Reports>Company>Taxes>Tax period report

• TX30000 - Tax History Detail
• Tax period report uses Tax Date field
• GL20000 – Open Year Posted Transactions
• GL20000 - Open year transactions
• GL30000 – Historical Year Transactions
• SOP30200 - SOP document Header

------------------------------------------------------------------------------------------------------------
Compare TX30000 to SOP30200 for specific days or documents to confirm if transactions are missing from the TX30000.

Use this script to rebuild the TX30000 table as required

http://dynamicsgpblogster.blogspot.com/2009/01/rebuilding-tax-history.html

--Adjust doctype for other docs
/* 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)