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