Re: Chart of Accounts - Mailing list pgsql-general

You are making this far to complicated.

I just redid the accounting side of an application we have access to
source code, so been here and done this.

If i was not for the rest of the application i would have completely
redone the accounting table layout something like this

3 Accounting Tables

One has you chart of Accounts
   Create table coa (
      coa_id serial not null,
      parent_id int not null default 0,
      doIhaveChildren boolean default false
       account_name text null )
primary key(coa_id)

Create Table general_ledger_transactions(
      transaction_id serial not null
      coad_id integer,
      accounting_period integer,
      debit numeric(20,10) ,
      credit numeric(20,10),
      transaction_date datestamp)
primary key (transaction_id)

special note do not use only 2 decimal points in the accounting tables.
If your application uses 10 decimal places somewhere then every table in
the database that has decimals needs to have the same precision.
Nothing is more annoying where a transaction says 1.01 and the other
side says 1.02 due to rounding.  Also you want to split out the debit
and credits instead of using one column.  Example one column accounting
table to track values entered how do you handle Crediting a Credit
Account Type.  is it a negative or positive entry???

Create table  accounting_periods (
   accounting_period serial not null,
   start_date date,
   end_date date,
   accounting_period_Open boolean)


I would used views and the application to create the tree list view i
think your after. As you also need to know the Open Balances, Debit,
Credits and Closing Balances by accounting period..  One idea is is
create a functions that scans through the general_ledger_transactions
table to get your values  So create a View something like this

Example would by
   Select Sum(debits) +
           Case when  coa.doIhaveChildren then
                   GetChildAccountDebits(coa.coa_id, period_id)
            else
                  0.0
            end;
   from general_ledger_transactions, coa,
    where general_ledger_transactions.coad_id = coa.coa_id
              and coa.coa_id = SomPassedAccountID
      group by general_ledger_transactions.period_id

What happen is the GetChildAccountDebits() function takes two
parameters. One is the coa_id and the other is accounting period to search

The function would look something like this

  return  Select Sum(debits) +
           Case when  coa.doIhaveChildren then
                   GetChildAccountDebits(coa.coa_id, period_id)
            else
                  0.0
            end;
   from general_ledger_transactions, coa,
    where general_ledger_transactions.coa_id= coa_id
         and  coa.parent_id = ThePassedAccountID
         and general_ledger_transactions.period_id =PassedPeriodID


This creates a loop back which can be dangers if Parent_account is also
a Child_account of itself which creates an endless loop then creates a
stack error.

Outside of that is works great.  i do something very similar Bill of
Material and in our Accounting

James Hitz wrote:
> Dear All,
>
> I have just started experimenting with PGSQL, with a view to migrate from the SQL server I use currently.  I am
tryingto implement an "intelligent" Chart of Accounts for an accounting program.  The following is long-winded but
pleasebear with me: 
>
> I have a table coa (chart of accounts) with the following schema
>
>   CREATE TABLE coa(
>     coa_id serial not null,
>     parent_id int not null default 0,
>     account_name text not null,
>     amt money default 0,
>     primary key(coa_id)
>   );
>
> After populating the database with basic accounts it resembles this (the hierarchy is mine):
>
>   coa_id, parent_id, account_name,          amt
>   0,        -1,      'Chart of Accounts',    0.00
>   1,         0,         'Assets',            0.00
>   5,         1,           'Fixed Assets',    0.00
>   6,         5,             'Motor Van',     0.00
>  --truncated ---
>   2,         0,       'Liabilities',         0.00
>   3,         0,       'Income',              0.00
>   4,         0,       'Expenses',            0.00
>
> So far, so good.  I would like it so that if the amt of a a child account changes, the parent account is updated, if
achild account is deleted, the amount is reduced off of the parent account etc. 
>
> I have managed to achieve this using the following trigger functions:
>
> CREATE OR REPLACE FUNCTION public.coa_del_amt() RETURNS trigger AS
> $body$
> begin
>     update coa set amt = amt - old.amt where coa_id = old.parent_id;
>     return old;
> end;
> $body$
> LANGUAGE 'plpgsql'
>
> ------------------
>
> CREATE OR REPLACE FUNCTION public.coa_ins_amt() RETURNS trigger AS
> $body$
> begin
>     UPDATE coa SET amt = amt + new.amt WHERE coa_id = new.parent_id;
>     return new;
> end;
> $body$
> LANGUAGE 'plpgsql'
>
> ------------
>
> CREATE OR REPLACE FUNCTION public.coa_upd_amt() RETURNS trigger AS
> $body$
> begin
>     IF new.parent_id = old.parent_id THEN
>         UPDATE coa SET amt = amt + (new.amt - old.amt)
>                 WHERE coa_id = new.parent_id;
>     ELSE
>         UPDATE coa SET amt = amt - old.amt
>                    WHERE parent_id = old.parent_id;
>         UPDATE coa SET amt = amt + new.amt
>                    WHERE parent_id = new.parent_id;
>     END IF;
>     RETURN new;
> end;
> $body$
> LANGUAGE 'plpgsql'
>
> ------------
>
> These have been bound to the respective ROW before triggers.  And they work as expected upto a certain extent. eg
assigninga value to 'Motor Van' updates the relevant parent accounts: 
>
>   UPDATE coa SET amt = 4000 WHERE coa_id = 6;
>
> The problem comes about when one wants to change the parent account for a sub account eg, assuming in the example
abovethat 'Motor Van' was a liability, attempting to change its parent_id from 1 to 2 is erronous and somewhat
interestingbecause the amt for all related accounts are reset to unpredictible values, AND the parent_id does not
changeanyway. 
>
> The problem lies squarely in the function coa_upd_amt().
>
> Any ideas.
>
> Thank you.
>
>
>
>
>