Symptoms
The Sales report and the Sales Tax report for the same period of time have different Total and Taxable Amount values.
As Provider I want to get the list of documents which make the reports to show different values.
Cause
The possible reasons of different totals in the Sales and Sales Tax reports are:
Invoices without taxes
Credit/debit memos added without tax
Document adjustments added without tax
- If an order was placed, and then Tax Zone of the customer was changed, then invoice will have different amount and this can cause discrepancy in Sales report
Moreover, Sales Report is affected by two known issues - PBA-50118 (Sales Report does not count details of archived order in consolidated invoice) and PBA-50113 (Sales Report counts all Cancellation Charges Order details as refund).
Resolution
There is no easy way to get the list of documents which make difference in reports in the PBA Control Panel in the current implementation. There is existing request PBA-47964 to make it possible to find reason of discrepancy between the Sales report and the Sales Tax report in the PBA Control Panel.
You can use the following query in the PBA database to find invoices created without taxes within specific period:
SELECT "DocID", "DocNum", "Total_Value", "TaxTotal_Value", "CurrencyID","DocDate"::ABSTIME FROM "ARDoc" WHERE "DocType" = 20 AND "Status" IN (1000,3000) AND "DocDate" >= 1375315200 AND "DocDate" <= 1377993600 AND NOT EXISTS (SELECT 1 FROM "DocTaxDetail" WHERE "DocTaxDetail"."DocID" = "ARDoc"."DocID") AND "Vendor_AccountID" = 1 ORDER BY "DocID";
Replace 'Vendor_AccountID' with the required ID of Provider (1) or reseller, provide the desired 'DocDate' interval in the Unix time stamp format.