webERP Forum
Aged Debtor Analysis - Printable Version

+- webERP Forum (http://www.weberp.org/forum)
+-- Forum: webERP Discussion (/forumdisplay.php?fid=1)
+--- Forum: Development Discussion & Specification (/forumdisplay.php?fid=10)
+--- Thread: Aged Debtor Analysis (/showthread.php?tid=2833)

Pages: 1 2


Aged Debtor Analysis - daveparrish - 08-11-2016 12:51 PM

When running the Aged Debtor Analysis there are invoices showing up with 0.00 balances I have attached an example.
How can I change this the invoices are showing marked as settled.
Thanks
Dave


RE: Aged Debtor Analysis - agaluski - 08-12-2016 11:03 PM

If you run the Statements program it should take care of this.
Sometimes the float issue in the fields used to store amounts leaves tiny little decimals out there.
There is a block around line 52 in the PrintCustStatements.php that takes care of this
PHP Code:
/* Do a quick tidy up to settle any transactions that should have been settled at the time of allocation but for whatever reason weren't */
    
$ErrMsg _('There was a problem settling the old transactions.');
    
$DbgMsg _('The SQL used to settle outstanding transactions was');
    
$sql "UPDATE debtortrans SET settled=1
            WHERE ABS(debtortrans.ovamount+debtortrans.ovdiscount+debtortrans.ovfreight+debto​rtrans.ovgst-debtortrans.alloc)<0.009"
;
    
$SettleAsNec DB_query($sql,$db$ErrMsg$DbgMsg); 



RE: Aged Debtor Analysis - daveparrish - 08-13-2016 01:52 AM

(08-12-2016 11:03 PM)agaluski Wrote:  If you run the Statements program it should take care of this.
Sometimes the float issue in the fields used to store amounts leaves tiny little decimals out there.
There is a block around line 52 in the PrintCustStatements.php that takes care of this
PHP Code:
/* Do a quick tidy up to settle any transactions that should have been settled at the time of allocation but for whatever reason weren't */
    
$ErrMsg _('There was a problem settling the old transactions.');
    
$DbgMsg _('The SQL used to settle outstanding transactions was');
    
$sql "UPDATE debtortrans SET settled=1
            WHERE ABS(debtortrans.ovamount+debtortrans.ovdiscount+debtortrans.ovfreight+debto​rtrans.ovgst-debtortrans.alloc)<0.009"
;
    
$SettleAsNec DB_query($sql,$db$ErrMsg$DbgMsg); 
I ran the statement program and did not resolve I ran for all customers and I tried one at a time and that did not resolve.
I reviewed the lines in PrintCustStatements.php and they are there.


RE: Aged Debtor Analysis - daveparrish - 08-13-2016 12:00 PM

(08-13-2016 01:52 AM)daveparrish Wrote:  
(08-12-2016 11:03 PM)agaluski Wrote:  If you run the Statements program it should take care of this.
Sometimes the float issue in the fields used to store amounts leaves tiny little decimals out there.
There is a block around line 52 in the PrintCustStatements.php that takes care of this
PHP Code:
/* Do a quick tidy up to settle any transactions that should have been settled at the time of allocation but for whatever reason weren't */
    
$ErrMsg _('There was a problem settling the old transactions.');
    
$DbgMsg _('The SQL used to settle outstanding transactions was');
    
$sql "UPDATE debtortrans SET settled=1
            WHERE ABS(debtortrans.ovamount+debtortrans.ovdiscount+debtortrans.ovfreight+debto​rtrans.ovgst-debtortrans.alloc)<0.009"
;
    
$SettleAsNec DB_query($sql,$db$ErrMsg$DbgMsg); 
I ran the statement program and did not resolve I ran for all customers and I tried one at a time and that did not resolve.
I reviewed the lines in PrintCustStatements.php and they are there.

After looking at the data it looks like the issue is with the debtortrans.ovgst having 5 decmenl spaces when you calculate debtortrans.ovamount+debtortrans.ovdiscount+debtortrans.ovfreight+debtortrans.ov​gst there is a difference between the total and debtortrans.alloc.
For example ovamount 2193.24 + ovgst 180.9423 = 2374.1823 alloc is 2374.18.
Is there a script that I can run to fix the ones that do not balance.
And also how can I fix this from happening in the future?


RE: Aged Debtor Analysis - agaluski - 08-18-2016 07:46 AM

Dave,
The logic only runs if certain input conditions are met. Unfortunately I can't troubleshoot easily not being on your system Smile.
Do you get any results if you run this query on the DB directly?
SELECT * from debtortrans WHERE ABS(debtortrans.ovamount+debtortrans.ovdiscount+debtortrans.ovfreight+debto​rtra​ns.ovgst-debtortrans.alloc)<0.009


RE: Aged Debtor Analysis - daveparrish - 08-18-2016 12:16 PM

(08-18-2016 07:46 AM)agaluski Wrote:  Dave,
The logic only runs if certain input conditions are met. Unfortunately I can't troubleshoot easily not being on your system Smile.
Do you get any results if you run this query on the DB directly?
SELECT * from debtortrans WHERE ABS(debtortrans.ovamount+debtortrans.ovdiscount+debtortrans.ovfreight+debto​rtra​ns.ovgst-debtortrans.alloc)<0.009
Strange ran in DB directly and error
0 1 20:52:50 SELECT * from debtortrans WHERE ABS(debtortrans.ovamount+debtortrans.ovdiscount+debtortrans.ovfreight+debto​rtra​​ns.ovgst-debtortrans.alloc)<0.009 Error Code: 1054. Unknown column 'debto​rtra​ns.ovgst' in 'where clause' 0.000 sec

Pasted in notepad and here is what it looks like
0 1 20:52:50 SELECT * from debtortrans WHERE ABS(debtortrans.ovamount+debtortrans.ovdiscount+debtortrans.ovfreight+debto?rtra​?ns.ovgst-debtortrans.alloc)<0.009 Error Code: 1054. Unknown column 'debto?rtra?ns.ovgst' in 'where clause' 0.000 sec

re typed debtortrans.ovgst and returned

3 2 20:55:27 SELECT * from debtortrans WHERE ABS(debtortrans.ovamount+debtortrans.ovdiscount+debtortrans.ovfreight+debtortran​s.ovgst-debtortrans.alloc)<0.009 12101 row(s) returned 0.000 sec / 0.141 sec


RE: Aged Debtor Analysis - agaluski - 08-19-2016 12:16 AM

My mistake - you said they were already settled. I was looking to close them out - which they already are closed.

What version are you running?
In AgedDebtors.php my version has a line around 362 in the show detail section that limits showing these fractions.
AND ABS(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc)>0.004";

You can also add "AND settled=0" to your detailed query. This will hide settled transactions from the details.


RE: Aged Debtor Analysis - daveparrish - 08-23-2016 12:33 AM

(08-19-2016 12:16 AM)agaluski Wrote:  My mistake - you said they were already settled. I was looking to close them out - which they already are closed.

What version are you running?
In AgedDebtors.php my version has a line around 362 in the show detail section that limits showing these fractions.
AND ABS(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc)>0.004";

You can also add "AND settled=0" to your detailed query. This will hide settled transactions from the details.

Version 4.12.3

What I did is run this script below on lab server and seems to clean up all the issues with .01 -.01 also there are some that had -0.00 show up.
Also there were some on the statements showing .01 and -.01 that the script cleared up.
Would this have any other affects on the data that I am missing all seems well after running.

UPDATE debtortrans
SET debtortrans.alloc=debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount
WHERE debtortrans.settled=1

Thanks for all the help


RE: Aged Debtor Analysis - phil - 08-23-2016 08:54 AM

It shouldn't be possible to have settled=1
if debtortrans.alloc NOT == (debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount)
these miserable rounding issues normally arise out of currency exchange arithmetic.
So, your
Code:
UPDATE debtortrans
SET debtortrans.alloc=debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount
WHERE debtortrans.settled=1
SHOULD be ok ... but just check your debtors control account (from company preferences) agrees to the total of all balances as converted to local currency from DebtorsAtPeriodEnd.php script with a period end after today.


RE: Aged Debtor Analysis - daveparrish - 08-25-2016 05:42 AM

(08-23-2016 08:54 AM)phil Wrote:  It shouldn't be possible to have settled=1
if debtortrans.alloc NOT == (debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount)
these miserable rounding issues normally arise out of currency exchange arithmetic.
So, your
Code:
UPDATE debtortrans
SET debtortrans.alloc=debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount
WHERE debtortrans.settled=1
SHOULD be ok ... but just check your debtors control account (from company preferences) agrees to the total of all balances as converted to local currency from DebtorsAtPeriodEnd.php script with a period end after today.

Thanks for all the help I am using only one currency so that should not be an issue.