This report pulls the data that is returned from Avalara and stored in the line item record. It formats this into a report that may be helpful for comparing to Avalara.

WARNING: This report should NOT be used for paying taxes. While it may be helpful for checking, it does NOT pull data from the GL. Therefore, it does NOT properly handle any adjustments to orders made after they are a sale. For example, an order that is marked sale in one month but voided in another will never have the taxes reduced for the void!

None. This is a selection query and no data is modified in the running of it.

DECLARE @startdate datetime;
DECLARE @enddate datetime;
SET @startdate = '6/1/2010';
SET @enddate = '7/1/2010';
SELECT 
   OrderNumber, 
   (SELECT CompanyName FROM Account WHERE ID = AccountID) AS Company,
   (SELECT Description FROM TransHeader WHERE ID = TransHeaderID) AS Description,
   LineItemNumber, 
   Taxes.value('(TaxRate/text())[1]', 'float') AS TaxRate,
   Taxes.value('(TaxAmount/text())[1]', 'money') AS TaxAmount,
   Taxes.value('(TaxCode/text())[1]', 'varchar(25)') AS TaxCode,
   SubTotalPrice, 
   TaxesPrice, 
   TransHeaderID, 
   TransDetailID, 
   AccountID,
   (CASE WHEN TaxesPrice = 0 THEN 0 ELSE (Taxes.value('(TaxAmount/text())[1]', 'money') / TaxesPrice) END) AS PercentOfTaxes
FROM
(
    SELECT TH.OrderNumber, TD.LineItemNumber, TD.SubTotalPrice, TD.TaxesPrice, 
            TH.ID AS TransHeaderID, TD.ID AS TransDetailID, TH.AccountID,
            CAST(TD.TaxItems AS XML) AS TaxXML
    FROM TransDetail TD
    JOIN TransHeader TH ON TD.TransHeaderID = TH.ID
    WHERE TH.SaleDate BETWEEN @startdate AND @enddate
) Temp
-- Cross Join this with a row for each ID in the Variable
CROSS APPLY TaxXML.nodes('//Tax') AS VARIABLES(Taxes)
ORDER BY TransHeaderID, LineItemNumber
  • Entered : 8/2010
  • Version : 4.0+
You could leave a comment if you were logged in.