Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Aged Debtor Analysis
08-11-2016, 12:51 PM,
#1
Aged Debtor Analysis
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


Attached Files Thumbnail(s)
   
Reply
08-12-2016, 11:03 PM,
#2
RE: Aged Debtor Analysis
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+debtortrans.ovgst-debtortrans.alloc)<0.009"
;
    
$SettleAsNec DB_query($sql,$db$ErrMsg$DbgMsg); 
Reply
08-13-2016, 01:52 AM,
#3
RE: Aged Debtor Analysis
(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+debtortrans.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.
Reply
08-13-2016, 12:00 PM,
#4
RE: Aged Debtor Analysis
(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+debtortrans.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.ovgst 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?
Reply
08-18-2016, 07:46 AM,
#5
RE: Aged Debtor Analysis
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​rtrans.ovgst-debtortrans.alloc)<0.009

Reply
08-18-2016, 12:16 PM,
#6
RE: Aged Debtor Analysis
(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​rtrans.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+debtortrans.ovgst-debtortrans.alloc)<0.009 12101 row(s) returned 0.000 sec / 0.141 sec
Reply
08-19-2016, 12:16 AM,
#7
RE: Aged Debtor Analysis
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.
Reply
08-23-2016, 12:33 AM,
#8
RE: Aged Debtor Analysis
(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
Reply
08-23-2016, 08:54 AM,
#9
RE: Aged Debtor Analysis
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.
Phil Daintree
webERP Admin
Logic Works Ltd
http://www.logicworks.co.nz
Reply
08-25-2016, 05:42 AM,
#10
RE: Aged Debtor Analysis
(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.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)