Skip to Content
0
Former Member
Apr 16, 2014 at 11:41 AM

Statement of Account

190 Views

Hi experts,

I would like to create a query for a Statement of Account report.

The report contains all invoices and payments made by a customer.

The report would look like this

Date Invoice #
Payment # Particulars Charges Credit Line Total Open Item 01.03.14 100001 For the service 1000 1000 1000 01.24.14 100005 For the 2nd service 5000 5000 02.16.14 20007 For the payment of Inv 100005 5000 (5000) 01.30.14 100012 Invoice for 3rd service 3000 3000 03.01.14 20034 For the payment of Inv 100012 1800 (1800) 1200 02.20.14 100035 Invoice for the service rendered 2000 2000 2000

Cumulative Balance 4200

Columns Explained

Date: Invoice Date or Payment Date

Invoice #: sales invoice no

Pyment #: incoming payment no.

Particulars: remarks from either invoice doc or incoming payment doc

Charges: Invoice Total

Credit: Incoming Payment Total/payment made

Line Total: Charge/Credit amount depending on nature of transaction. positive if charges, negative if credit

Open Item: balance

Parameter: As of Date

So a query that would get all the invoices and its corresponding payment.

Take into consideration its reconciling items.

Like if a sales invoice has a credit memo, if the credit memo is within the As of Date, the amount in Charges column will reflect net of credit memo amouont. But if the credit memo is after As of Date then it would reflect Original Invoice Amount.

This goes with the Incoming Payment as well. If the paid is made after the As of Date then it will not be in the report.

It is like a Customer Receivables Aging report, to be displayed like the table above.

Thank you in advance to all who would help. Appreciate it.

Regards,

Khristine