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 #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