Problem with calculating customer Balance Due

hru71

Hii everyone,
Similar topics has been here, but I seems to still don't get it. Hope some of you guys can give a hint. A customer wants to set diffent "block levels" for customers depending if balance due > 0 at 3 different dates (exam. Level 3 blocking : if balance due > 0 at 20.02.12, Level 2 blocking: if balance due > 0 at 20.03.12 etc.).
I was told by my manager to avoid customer ledger entries, and use the Balance due from customer - which uses the detailed ledg. entries.
However I'm not getting the results I expect in many cases, assuming that I set Date Filter = ..Level3Date I expect to get the sum of Amount for all det. ledger entries with due date from 0D to the specified date, and no matter what the posting date is ?
Example of error checking where the Balance Date is 20.01.12. The calculated Balance Due is 3144,5, even there is a payment done.
Looking at det. ledger entries shows:
Entry No. Cust. Ledger Entry No. Posting Date Initial Entry Due Date Entry Type Document Type Amount Amount (LCY)
28120 1150351 05-12-2011 15-01-2012 Initial Entry Invoice 3.144,50 3.144,50
30375 1150351 23-02-2012 15-01-2012 Application Payment -3.144,50 -3.144,50
I'm running a report for all customers and on afterGetRecord I use this loop:

FOR Level := 3 DOWNTO 0 DO BEGIN
CASE Level OF
1,2,3:
IF PositiveBalanceDue(BillToCustomer,CalculationStartDate - BalanceDates[Level]) THEN BEGIN
UpdateCustomer(SellToCustomer,Level);
CurrReport.SKIP;
END;
0:
IF PositiveBalanceDue(BillToCustomer,CalculationStartDate) THEN
CurrReport.SKIP
ELSE
UpdateCustomer(SellToCustomer,Level);
END;
END;


PositiveBalanceDue(Cust : Record Customer;BalanceDate : Date) : Boolean
WITH Cust DO BEGIN
SETFILTER("Date Filter",'..%1',BalanceDate);
CALCFIELDS("Balance Due (LCY)");
//MESSAGE(FORMAT("Balance Due (LCY)")+Text001,BalanceDate);
EXIT("Balance Due (LCY)" > 0);

Hope anyone can help.

jglathe

Hi,

have a look at the CalcFormula for "Balance Due (LCY)". It also limits the posting date of the entries considered. Since all entries have a due date (strange, had to look it up) it would be sufficient to delimit by due date.
Another issue would be a premature payment. The due date of the invoicing entry isn't corrected at closing, so you can get negative amounts due for an invoice, depending on the date filter. These are hard to explain. To avoid these hassles you would need to walk over all open entries for the calculation.

with best regards

Jens

hru71

Thank Jens, I can see the issue with premature payments, and that going through customers and calculate the Balance Due is not the way to go.
Anyway - as I see it (not very clearly) I will still have a problem with the open entries. I have to check the due balance for several date periods:
Level 3 date 0d..01.04.13 : due balance > 0 - customer has none-paid invoices where due date < 01.04.13
IF NOT Level 3 THEN check:
Level 2 date 0d..01.05.13 : due balance > 0 - customer has none-paid invoices where due date < 01.05.13
ect.
In open entries I can have a invoice with due date in one level, but I can also have a payment from customer with posting date/due date in another level, which has type Initial Entry and not Application. Meaning I have 2 open entries with "no connection" in 2 different levels. Feels kind of stocked in the lack of understanding how I can calculate this.

jglathe

Hi,

the balance due calculation doesn't respect applications. To make the payment date vs. invoice date better readable, I would suggest a "limiter" to a "real" balance due:

- calculate the balance, if there is an overpayment in total then move along, nothing to see. You could filter out the "on hold" entries, but unfortunately these are only in the entries, not detailed entries. Maybe it's a good idea to change this (I would do it to ensure better reporting).
- from the latest Level Date down: always filter 0D...Leveld Date End, calculate balance (due). If the amount represents more invoices than payments, show it. Otherwise, don't show it and set the remaining levels to zero for this customer/vendor, next one please.

This should represent what an accountant / sales rep wants to see, IMO.

with best regards

Jens