Sample Customer and Vendor Statement
Hi Everyone,
A customer statement consists of the following:
- Start Date
- End Date
- comparing dates against document => Account Date
- Open Balance as of Start Date
- Ending Balance as of End Date
- Transactions between dates.
Transaction of interest are:
- Invoices
- Payments
- Allocation Line => Discount and Write-Off Amounts
- Allocation Line => Amount where Charge field is not null
Example of an overly-simplified query:
select * from (
select c_bpartner_id, dateacct, documentno, grandtotal, sum(grandtotal) over (partition by c_bpartner_id order by c_bpartner_id, dateacct, documentno) as cumm_grandtotal
from c_invoice -- you can change this to a view that unions in invoices, payments and appropriate allocation lines
--where dateacct > now()-100
order by c_bpartner_id, dateacct, documentno
) x
where dateacct > now()-100 -- this is where you add your startdate/enddate
and c_bpartner_id = 1000011 -- this is where you limit by BP
If the above is too slow, you can materialize some or all of the above. Just refresh the materialized view before running your report.
Plugin Jasper Statement from Logilite
The purpose of this page is to offer a sample customer statement. To install, download this file add to your iDempiere Felix Console. Please note this report is written with Jasper Studio. If you want to modify the report, you will need to install Jasper Studio on a machine with at least 8 GB+ of RAM. If you do not want to mess with Jasper Studio yourself, you can ask for help from another ERP Academy member or Deepak.
Report Name = "Jasper Statement Report" could be misspelled as "Statment".
I hope this helps!