There is an error with the spARPostInvoice4 stored procedure on the update statement that starts on line 160 and also the update starting on line 170. This update statement doesn’t take the company into account when determining if new tax information is an update to an existing record or if a new record should be inserted. So if Company A posts an invoice with tax code 11 for 4/20 at 9:00 am a new record will be created and it will appear on the Sales Tax report correctly. If Company B posts and invoice with the same tax code and post date after Company A posts the Sales Tax report will make an additional update to Company A’s sales tax information.

Current Code (ln 160):

----------------------------------------------------------------
/* mark all Sales tax hist records in temp table that WILL BE */
/* updated to status of UpdateFlag=1 */
----------------------------------------------------------------
UPDATE #tciSTH
SET UpdateFlag = 1
FROM tciSTaxHist g
WHERE #tciSTH.STaxCodeKey = g.STaxCodeKey
AND #tciSTH.PostDate = g.PostDate


Updated Code:

----------------------------------------------------------------
/* mark all Sales tax hist records in temp table that WILL BE */
/* updated to status of UpdateFlag=1 */
----------------------------------------------------------------
UPDATE #tciSTH
SET UpdateFlag = 1
FROM tciSTaxHist g
WHERE #tciSTH.STaxCodeKey = g.STaxCodeKey
AND #tciSTH.PostDate = g.PostDate
AND g.CompanyID = @iCompanyID

Current Code (ln 170):

UPDATE tciSTaxHist
SET tciSTaxHist.ARTaxInvc = tciSTaxHist.ARTaxInvc + f.ARTaxInvc,
tciSTaxHist.ExmptSales = tciSTaxHist.ExmptSales + f.ExmptSales,
tciSTaxHist.SubjARFreight = tciSTaxHist.SubjARFreight + f.SubjFreight,
tciSTaxHist.SubjSales = tciSTaxHist.SubjSales + f.SubjSales,
tciSTaxHist.SubjARSalesTax = tciSTaxHist.SubjARSalesTax + f.SubjSalesTax,
tciSTaxHist.NonSubjSales = tciSTaxHist.NonSubjSales + f.NonSubjSales,
tciSTaxHist.UpdateCounter = tciSTaxHist.UpdateCounter + 1
FROM #tciSTH f
WHERE f.STaxCodeKey = tciSTaxHist.STaxCodeKey
AND f.PostDate = tciSTaxHist.PostDate


Updated Code:

UPDATE tciSTaxHist
SET tciSTaxHist.ARTaxInvc = tciSTaxHist.ARTaxInvc + f.ARTaxInvc,
tciSTaxHist.ExmptSales = tciSTaxHist.ExmptSales + f.ExmptSales,
tciSTaxHist.SubjARFreight = tciSTaxHist.SubjARFreight + f.SubjFreight,
tciSTaxHist.SubjSales = tciSTaxHist.SubjSales + f.SubjSales,
tciSTaxHist.SubjARSalesTax = tciSTaxHist.SubjARSalesTax + f.SubjSalesTax,
tciSTaxHist.NonSubjSales = tciSTaxHist.NonSubjSales + f.NonSubjSales,
tciSTaxHist.UpdateCounter = tciSTaxHist.UpdateCounter + 1
FROM #tciSTH f
WHERE f.STaxCodeKey = tciSTaxHist.STaxCodeKey
AND f.PostDate = tciSTaxHist.PostDate
AND tciSTaxHist.CompanyID = @iCompanyID

by: Adam M. | over a year ago | 1 - Financial Management