Total Customer Outstanding

pajahir · March 5, 2016, 11:30am

Hi ERP Team,

Do we have any function or table to get to know the total customer outstanding for the given date?
example, If i enter today date, output should be amount like 20,000 like that. Just Total Value in one variable.

thanks
Jahir

vrms · March 9, 2016, 3:26pm

I think such a customer report is essential to have in any accounting solution. It should support to filter by customer/supplier on a period basis as well well on a project basis (project here might be defined by a project, a Sales order, or a Sales Invoice)

Actually I kind off assumed such must exist somewhere. Without having really tried it out so far, could you get such a report though applying a mix of filters to the general ledger maybe?

kolate_sambhaji · March 9, 2016, 6:16pm

@pajahir Its available in General Ledger report, where you can also print Customer Account Statement

Please see general ledger report in Account module

pajahir · March 9, 2016, 6:27pm

Hi, Customer Statement will be individual customer with details of invoice and receipt. But what i was asking entire outstanding amount for the company.
I can sum outstanding column in sales invoice table… not sure this is right solution.

kolate_sambhaji · March 9, 2016, 6:44pm

You can get this into general ledger, just need to add correct filter
Also there is account recivable and payble report present in account module

vrms · March 10, 2016, 6:03am

I think you might get that through

Accounts > Main Reports > Accounts Receivable

in case you don’t apply any filter per customer you should get the entire sum owed to your company

pajahir · March 10, 2016, 6:41am

Hi, let me explain what i wanted as output.
CustomerName Amount
Customer1 2000
Customer2 3000
Customer3 500
Total 5500

Account Receivables report (don’t apply filter) will give output invoice wise outstanding (detail). I am asking for summary in customer level.
If we apply filter filter for customer then one customer OS will come.

I think if we sum outstanding amount in sales invoice table with group by customer above output will come.

thanks
Jahir

kolate_sambhaji · March 10, 2016, 7:56am

You can make such reports on your own

https://frappe.github.io/erpnext/user/manual/en/customize-erpnext/articles/making-custom-reports-in-erpnext.html

pajahir · March 10, 2016, 8:03am

Yes I did. worked fine. thanks.

vrms · March 17, 2016, 2:12am

@pajahir can you share the custom report you designed?

pajahir · March 17, 2016, 2:37am

Hi, Please find the attached screen. There is no filter screen, Intention is to know the company outstanding as on date. Also this will not consider Customer Advances.
Regards
Jahir

vrms · March 17, 2016, 4:28am

@pajahir thanks for the screenshot but that’s not what I meant. What I meant with ‘sharing’ is providing the code or settings, so anybody who might want to have the same thing can rebuild what you have done.

When I say ‘code’ I have to admint that I wouldn’t know what that actually means in terms of a report. Maybe it’s just a bunch of Settings?

pajahir · March 17, 2016, 5:14am

Hi, It is not setting. It is query report. Just make select statement from sales invoice table for outstanding amount field and group by customer.

pajahir · March 17, 2016, 5:16am

select customer as “Customer Code:Data:200”, customer_name as “Customer Name:Data:200”, sum(tabSales Invoice.outstanding_amount) as “Outstanding Amount:Currency:150” from tabSales Invoice where tabSales Invoice.docstatus= “1” and tabSales Invoice.outstanding_amount > 0 group by customer, customer_name order by customer_name