Re: Trial Balance not balanced :-(

["Chris Travers" <..hidden..>]

Just so others can learn from this too:

On Sat, Sep 13, 2008 at 6:16 PM, Stephen Anderson
<..hidden..> wrote:

>> Having given the above two disclaimers:
>> 1)  Is this an upgrade from sql-ledger?
>
> No it's a new install..  (after trying and not liking sql-ledger :-) )

There are three reasons I have seen unbalanced transactions in
SQL-Ledger which are prevented in LedgerSMB.

These are:
1)  DOUBLE PRECISION roundoff errors (typically only happens with
high-volume users and only a couple of cents)

2)  NULL chart_id fields in acc_trans.

3)  chart_id field that no longer properly references a chart record.

If this was an upgrade and there were problems during the upgrade
these are things to check.

>> 3)  How big of a difference are we talking about?
>
> $385.13 - so not huge..

In an amount like this we typically look for unbalanced transactions
or causes 2 and 3 above.

>
>> Also:  Can you run the following from the psql prompt:
>> \d acc_trans
>> and send the output to the list?
>
> appstech=# \d acc_trans
>                                 Table "public.acc_trans"
>      Column     |  Type   |
> Modifiers
> ----------------+---------+-----------------------------------------------------
> ---------
>  trans_id       | integer |
>  chart_id       | integer | not null
^^^ rules out NULL chart_id fields.

>  amount         | numeric |
>  transdate      | date    | default ('now'::text)::date
>  source         | text    |
>  cleared        | boolean | default false
>  fx_transaction | boolean | default false
>  project_id     | integer |
>  memo           | text    |
>  invoice_id     | integer |
>  entry_id       | integer | not null default
> nextval('acc_trans_entry_id_seq'::r
> egclass)
> Indexes:
>     "acc_trans_pkey" PRIMARY KEY, btree (entry_id)
>     "acc_trans_chart_id_key" btree (chart_id)
>     "acc_trans_source_key" btree (lower(source))
>     "acc_trans_trans_id_key" btree (trans_id)
>     "acc_trans_transdate_key" btree (transdate)
> Foreign-key constraints:
>     "acc_trans_chart_id_fkey" FOREIGN KEY (chart_id) REFERENCES chart(id)
^^^ Rules out incorrect references.

This leaves one last cause I have seen, and what seemed to be the
cause here:  A transaction was saved somehow with a missing entry.  We
have not been able to fully prevent this yet, but solutions are being
evaluated for 1.4, so once 1.3 is released, I expect it to be a
priority.

To detect which transactions are out of balance, you can run the
following query:

SELECT trans_id FROM acc_trans
GROUP BY trans_id
HAVING sum(amount) <> 0;

You can then look up these ids in the ar, ap, and gl tables.

Best Wishes,
Chris Travers