Hi everyone,

I'm trying to recreate the Open Invoices report using SQL queries. Basically, I need the data in the report, but I need it "flattened" and not hierarchical. Furthermore, the debtor code is not included in the report. The following query gives me the list of open invoices, and it includes all fields that I need:

SELECT
 Invoice.CustomFieldDebiteurnummer,
 Invoice.RefNumber,
 Invoice.TxnDate,
 Invoice.DueDate,
 Invoice.Subtotal,
 Invoice.BalanceRemaining,
 Invoice.SalesTaxTotal
FROM
 Invoice Invoice
WHERE
 Invoice.IsPaid = 0
ORDER BY
 Invoice.CustomFieldDebiteurnummer,
 Invoice.RefNumber

However, this obviously does not include credit notes or deposits. I've tried the CreditMemo table, but haven't been able to figure out how to determine if a credit note has been settled or not. The same goes for the Deposit table.

I hope someone out there can help...

Regards,

Maurits

Hi Tom,

The Customer Balance Detail Report seems very promising, but as I said before, I need the data "flattened". The report output has, for each debtor, a first row denoting the debtor and subsequent rows denoting the invoices, but an invoice row does not actually reference the debtor. Simply calling the stored procedure is therefore not an option, so I tried to run the "underlying" query that is listed in the "How do I":

SELECT CustomerRefFullName as RowData, Type, TxnDate as Date, RefNumber as Num,
ARAccountRefFullName as Account, Remaining as Amount FROM Sales NOSYNC
where IsPaid = 0 or Type = 'CreditMemo' Order by CustomerRefFullName, TxnDate, RefNumber

Unfortunately, the Sales table does not seem to exist:

It is the UK version, by the way.

Can you point me in the right direction? TIA!

Rgds/M