{$page}

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.

code_formatsql

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 code ===== Version Information ===== * Entered : 8/2010 * Version : 4.0+ ===== Related SQLs ===== * Backlinks include_pagepage_componentbacklinks