Re: Quest for information: Accounts Receivables/Payables Values

[Charley Tiggs <..hidden..>]

Chris Travers wrote:
> On 8/2/07, Charley Tiggs <..hidden..> wrote:
> > Hello all,
> >
> > Yesterday, I ran across an anomaly and I've been scratching my head
> > trying to figure out where the discrepancy is but am having little luck.
> >
> > In a nutshell, the AR/AP values on the balance sheet seems to be grossly
> > over-inflated.  If I go into the db directly and calculate the totals, I
> > get values that are in the $150,000 range.  But if I look at the balance
> > sheet within LSMB, it's saying that there's $750,000 outstanding, which
> > is grossly inaccurate.  Similar situation with AP.
>
>
>
> The reporting SQL code is fairly ugly because of the current database
> design.  I will post an overview a little later.  In the mean time, can you
> tell me what queries you are running in the database to genenerate these?


The query I'm using to get AR due:

SELECT
    ar.id,
    ar.invnumber,
    ar.ordnumber,
    ar.netamount,
    ar.paid,
    c.name AS company,
    c.contact,
    c.phone,
    c.email,
    c.fax,
    c.customernumber,
    t.invoice_ship_date AS transdate,
    '' AS duedate,
    t.purchase_order_number AS po_number,
    cs.terms
FROM ar LEFT JOIN customer c ON ar.customer_id = c.id
    LEFT JOIN transactions t ON ar.invnumber = t.id_transaction
    LEFT JOIN customers cs ON t.id_customer = cs.id_customer
WHERE ar.amount <> ar.paid
ORDER BY c.name, ar.duedate

I realize this may be overly simplistic but client reported that the sum 
totals of this query is accurate as to what they expect to be due.  The 
transactions and customers table are tables belonging to the web 
application. customers.terms is a field that displays the payment terms 
per customer as needed by the business rules of the client (e.g. 3/30/31 
== 3% off if paid in 30 days, net due in 31 days).  duedate is 
calculated by script logic based on cs.terms before displaying to the user.

AP has a similar query.  Don't have access to that one at the moment... 
 If you still need it, I'll send it to you as soon as I can.

> So, my question: Can someone explain to me, at a high level, how these
> > two values are calculated for the balance sheet?
>
>
> I will have to review the code.
>
>
>   It is especially
> > puzzling in the case of AP because none of those invoices are being
> > generated by the web application.  In the case of AR, nearly all of the
> > invoices are being generated by the web application so I'm trying to
> > find out where I may have gone wrong with things.
> >
> > Thanks in advance,
> >
> > Charley
> >
> > -------------------------------------------------------------------------
> > This SF.net email is sponsored by: Splunk Inc.
> > Still grepping through log files to find problems?  Stop.
> > Now Search log events and configuration files using AJAX and a browser.
> > Download your FREE copy of Splunk now >>  http://get.splunk.com/
> > _______________________________________________
> > Ledger-smb-devel mailing list
> > ..hidden..
> > https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
>
>
> ------------------------------------------------------------------------
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems?  Stop.
> Now Search log events and configuration files using AJAX and a browser.
> Download your FREE copy of Splunk now >>  http://get.splunk.com/
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Ledger-smb-devel mailing list
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel