On 8/2/07, Charley Tiggs <..hidden..> wrote:
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
Drop the left join to transactions.
1.2.x has a known issue where the rule double-incriments the id sequence and so things don't match. It still provides data integrity and this issue will be corrected in 1.3 (the main issue is just that it prevents you from using this table in a join)..
Chris Travers