Обсуждение: Chart of Accounts

Dear All,

I have just started experimenting with PGSQL, with a view to migrate from the SQL server I use currently.  I am trying
toimplement an "intelligent" Chart of Accounts for an accounting program.  The following is long-winded but please bear
withme: 

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 a
childaccount 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 above
that'Motor Van' was a liability, attempting to change its parent_id from 1 to 2 is erronous and somewhat interesting
becausethe amt for all related accounts are reset to unpredictible values, AND the parent_id does not change anyway. 

The problem lies squarely in the function coa_upd_amt().

Any ideas.

Thank you.

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.
>
>
>
>
>

justin <justin@emproshunts.com> writes:

> 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.

FWIW I think this is wrong. You need to use precisely the number of decimal
places that each datum needs. If you use extra it's just as wrong as if you
use too few.

For example, when you buy gas/petrol at $1.999/gallon and buy 4 gallons you
get charged $8.00 not $7.996. If you fail to round at that point you'll find
that your totals don't agree with the amount of money in your actual bank
account.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

Gregory Stark wrote:

justin <justin@emproshunts.com> writes:
 

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.     

FWIW I think this is wrong. You need to use precisely the number of decimal
places that each datum needs. If you use extra it's just as wrong as if you
use too few.

For example, when you buy gas/petrol at $1.999/gallon and buy 4 gallons you
get charged $8.00 not $7.996. If you fail to round at that point you'll find
that your totals don't agree with the amount of money in your actual bank
account.
 

I agree to a point.  just went through this with our application and had total fits with compound rounding errors as one table stored 4 other stored 6 and 8 and the general ledger table stored 2.  when it came time to balance the transactions to the General Ledger Entries we where off thousands of dollars in different accounts as the GL almost always was higher due to rounding and it was wrong to the detail side.

The entire database uses the same precession as a whole then rounded on the display side.    In our Case we make parts that consume .000113 lbs of a metal that sales for 25.76 a pound = 0.002911.  When the transaction to remove the value from the inventory account in the Generial ledger table has an entry 0.00  not 0.002911.  

We just had to big discussion on this thread about rounding and precession which i kicked off. 

On Mon, Oct 13, 2008 at 6:33 AM, Gregory Stark <stark@enterprisedb.com> wrote:
> justin <justin@emproshunts.com> writes:
>
>> 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.
>
> FWIW I think this is wrong. You need to use precisely the number of decimal
> places that each datum needs. If you use extra it's just as wrong as if you
> use too few.
>
> For example, when you buy gas/petrol at $1.999/gallon and buy 4 gallons you
> get charged $8.00 not $7.996. If you fail to round at that point you'll find
> that your totals don't agree with the amount of money in your actual bank
> account.

I wonder if there's a more general way to say that, something like:
With a transaction between two systems of different precision, the
greater precision system rounds at that point.

If you want to take a particular system out to extra digits, it's
probably good to record the rounding error as a separate component of
the transaction (that is, if you want everything to balance out
perfectly).

merlin

> If you want to take a particular system out to extra digits, it's
> probably good to record the rounding error as a separate component of
> the transaction (that is, if you want everything to balance out
> perfectly).
>
>
I think you have two different problems here. On the one hand you have
rounding errors which are material when aggregated on the other hand
most sales transactions (for example) will come to a dollar and cents
figure. If you have two accounts with different precision then I think
from an accounting perspective you need to say something like this when
posting between the two:

DR   My 2 Decimal Precision Account              2.00
DR   Accumulated Rounding (4 Decimal)          0.0010
    CR                Original 4 Decimal Account                     2.0010

Then at period end you can including your rounding account and
everything will balance.


Craig

Craig Bennett wrote:
>
>> If you want to take a particular system out to extra digits, it's
>> probably good to record the rounding error as a separate component of
>> the transaction (that is, if you want everything to balance out
>> perfectly).
>>
>>
> I think you have two different problems here. On the one hand you have
> rounding errors which are material when aggregated on the other hand
> most sales transactions (for example) will come to a dollar and cents
> figure. If you have two accounts with different precision then I think
> from an accounting perspective you need to say something like this
> when posting between the two:
>
> DR   My 2 Decimal Precision Account              2.00
> DR   Accumulated Rounding (4 Decimal)          0.0010
>    CR                Original 4 Decimal Account
> 2.0010
>
> Then at period end you can including your rounding account and
> everything will balance.
>
>
> Craig
Thats not the problem its the different tables having different
precision.     We have a WIP tables that notes all the labor and
material consumed by all the jobs for an accounting period.  So you have
some jobs all ways open crossing periods so you need to audit that WIP
process account which means going to the WIP tables and verifying that
the values in the WIP account equal to the jobs in the  WIP tables.  If
the detail differs even a a penny you have a problem you are not allowed
to simply call it rounding error.   Pushing it into another account
called rounding error does not solve the problem.   Values in the wip
tables need to equal the values in the General ledger tables


The problem occurs when the WIP tables store 6 and 8 decimals and the GL
tables have only 2.  it creates all kinds of rounding problems and it
gets worst when you have thousands of transactions a day  a penny
multiplied by 1000 becomes 10 bucks times 30 days in a accounting period
= 300 bucks.   Thats getting pretty big for a rounding mistake and this
is only one account.  Now take that and multiply that by 10 accounts
each going every which way.

There are a couple of ways to solve your problem

Heres my thoughts off the top of my head and what little i know about auctions and how they are run.  Also i hope the formating comes out.

please note these table do not contain all columns i would have in them its just an idea of how i would get all the tables linked together and laid out. 

Create Table contact (
    contact_id serial not null ,
    first_name text,
    last_name text,
    phone text,
    email text,
    company_name text,
    amIaCustomer boolean,
    amIaVendor  boolean)

Create Table AuctionHeader(
    action_id serial not null,
    date_to_have_action date,
    date_to_end_action date,
    auction_description text,
    auction_percent_take_for_each_item_sold numeric (10, 8)
)
   

Create Table AuctionItems (
    auction_id integer,
    item_id serial not null,
    item_description text,
    start_bid money,
    dont_sell_itemprice money,
    sold_price money,
    vendor_id integer,
    who_Brought_id integer,
    other_notes_ text)

Create table InvoiceHeader (
    invoice_id  serial not null,
    item_id integer,
    vendor_id integer,
    customer_id integer,
    invoice_posted_to_gl boolean
    invoice_paid boolean
    payment_terms integer,
    invoice_issue_date date
    Payment_method text (Credit Card, Money, Check)
)

Create Table AR_Header  (
    account_receivable_id serial not null
    invoice_id,
    invoice_total money,
    date_created date,
    notes  text,)

Create Table AR_PaymentsReceived (
    ar_item serial not null,
    account_receivable_id integer,
    payment_method text,
    amount_received money,
    date_received date)

Create Table InvoiceItems(
    item_id serial not null,
    sold_price money,
    actual_price_paid money)

Create Table general_ledger_transactions(
    transaction_id serial not null
    reference_type character,  (Am i a Invoice, JE, Credit Memor, Debit Memo, Inventory )
    reference_id integer, ( the primary key to the reference table)
    journal_entry_id integer, (this is used to keep transctions that  linked to together like You have debit and Credit account and some Journal Entries may hit 100 accounts )
    coa_id integer,
    accounting_period integer,
    debit numeric(20,10) ,
    credit numeric(20,10),
    transaction_date datestamp)
primary key (transaction_id) )

When An item is sold by the auctioneer  sold and an invoice is Created you would sum up the values Put a Debit to Vendors Account into the GL then Credit the Customer Owes Me Account,  then when the money is collected Debit the Customer Owes Me Account credit into a Revenue Account.

the  gl transactions for the Invoice Creation  could look like this
TransAtion_id --- Ref_type---- Reference_id----  Jorunal_ID---Coa_id -------------------------  debit------Credit
5784                  Invoice          Invoice: 785            78485         54 aka  CustomerOwesMe                            $25
5785                  Invoice          Invoice: 785            78485         67 aka I owe Vendor                    $20
5786                  Invoice          Invoice: 785            78485         15 aka Money I could be making   $5          
5787                  AR                AR: 4785               78486         5 aka CustomerOwesMe               $25
5788                  AR                AR: 4785               78486          25 aka BillPaidAccount                                  $25

   
Then Simple selects with joins and a few Case statements can get everything linked together.

Also note i am not an accountant by any imagination what so ever.  all my stuff is reviewed by CPA and an in house accountant to make sure i get all the debits and credits correct


Jeff Williams wrote:

Hi Justin

I like your method.

A question I am in the process of developing an piece of auction software.

How would you handle all the bidders and vendors so they all come from a 
table called contacts and have a serial number.  Each Purchase/Payment 
needs to recorded against each contact as well in the general ledger.  We 
need to get daily balances about each contact.

Regards
Jeff WIlliams
Australia

----- Original Message -----
From: justin <justin@emproshunts.com>
To: hitz@jamhitz.com
Cc: pgsql-general@postgresql.org
Date: Sun, 12 Oct 2008 20:57:59 -0400
Subject: Re: [GENERAL] Chart of Accounts
 

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 trying to implement an "intelligent" 
Chart of Accounts for an accounting program.  The following is long-winded 
but please bear 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 a child 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$
beginupdate 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$
beginUPDATE 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$
beginIF 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 assigning a 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 above that 'Motor Van' was a 
liability, attempting to change its parent_id from 1 to 2 is erronous and 
somewhat interesting because the amt for all related accounts are reset to 
unpredictible values, AND the parent_id does not change anyway. 

The problem lies squarely in the function coa_upd_amt().

Any ideas.

Thank you.



      

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

No virus found in this incoming message.
Checked by AVG - http://www.avg.com 
Version: 8.0.173 / Virus Database: 270.8.0/1721 - Release Date: 10/12/2008    

12:00 PM 

No virus found in this incoming message.
Checked by AVG - http://www.avg.com 
Version: 8.0.173 / Virus Database: 270.8.0/1721 - Release Date: 10/12/2008    

12:00 PM 

On Mon, Oct 13, 2008 at 2:57 AM, justin <justin@emproshunts.com> wrote:
> [...]  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???

How is crediting a credit account different from crediting any other account?

YMMV, but I think a single amount column makes for a more consistent design.

because a credit account is a liability account aka a negative account so credit a credit account causes it to go UP not down.  Look a your bank statement it says Credit you $500 when you make a deposit  its a debit to you a credit to the bank in a credit account as its a liability to the bank.

to be way over general Credits are negative entries and Debits are positive entries. 

Another Way to think about it is Are you Exporting or Importing, it depends on which side of the equations you are on.  When ever i try to explain importing and exporting to the accountants its my sweet revenge :-). 

Isak Hansen wrote:

On Mon, Oct 13, 2008 at 2:57 AM, justin <justin@emproshunts.com> wrote: 

[...]  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???   

How is crediting a credit account different from crediting any other account?

YMMV, but I think a single amount column makes for a more consistent design. 

Isak Hansen wrote:
> On Mon, Oct 13, 2008 at 2:57 AM, justin <justin@emproshunts.com> wrote:
>
>> [...]  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???
>>
>
> How is crediting a credit account different from crediting any other account?
>
> YMMV, but I think a single amount column makes for a more consistent design.
>
>
My accounting knowledge is really rusty, but I do remember that "credit"
and "debit" have specific meanings in accounting theory and refer to the
left (debit) and right (credit) columns of a "T" account. The sum of the
debit columns across all accounts in the ledger must match the sum of
the credit columns (the books are "balanced"). To keep the ledger
balanced, every transaction requires two (or more) entries into the
appropriate accounts and the debit-side and credit-side entries must match.

So if you are modeling a standard general-ledger double-entry accounting
system, two columns is an appropriate approach.

Cheers,
Steve

On Tue, Oct 14, 2008 at 5:07 PM, justin <justin@emproshunts.com> wrote:
> because a credit account is a liability account aka a negative account so
> credit a credit account causes it to go UP not down.

As you say, "a negative account". Our liability accounts go further
down when credited. I work with accountants all day, and this is what
they expect.

Of course either approach works, but I've come to prefer the single-column one.


> Isak Hansen wrote:
>
> On Mon, Oct 13, 2008 at 2:57 AM, justin <justin@emproshunts.com> wrote:
>
> [...]  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???
>
>
> How is crediting a credit account different from crediting any other
> account?
>
> YMMV, but I think a single amount column makes for a more consistent design.
>

On Tue, Oct 14, 2008 at 3:50 PM, Isak Hansen <isak.hansen@gmail.com> wrote:
> On Mon, Oct 13, 2008 at 2:57 AM, justin <justin@emproshunts.com> wrote:
>> [...]  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???
>
> How is crediting a credit account different from crediting any other account?
>
> YMMV, but I think a single amount column makes for a more consistent design.

Absolutely. I worked on computer accounting systems many years ago,
not exactly BC but BPC (Before PC) and such systems had a flag in the
Chart of Accounts records to indicate how to display negative numbers.
The assets and cost records were displayed as recorded and the revenue
and liabilities were negated for display purposes. Naturally offsets
such as credit notes against revenue displayed in the way that humans
expected to read them without any difficulty.
--
In a world without walls who needs Windows (or Gates)? Try Linux instead!

As I said earlier, I am quite green with PGSQL, so please bear with me when I ask "Stupid" questions...


--- On Mon, 13/10/08, justin <justin@emproshunts.com> wrote:

> 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

Ok with the tables

> 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
>

I start getting lost : SomPassedAccountID ??? Where is this coming from?


> 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


PassedPeriodID ??? ...and this?


> 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.

I think this is easy enough to control with a CHECK constraint I think.  Otherwise, I see the sense in using two
columnsfor transactions - If I were writing an application for a bank, then using one column only may have potential
pitfalls.

Regards

Sorry for the silence.  Have been away on official duty.  Please see inline:

--- On Mon, 13/10/08, justin <justin@emproshunts.com> wrote:
> <SNIP>
>
> 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)

A single transaction will often have at least two entities - typically a debit and a credit.  Shouldn't the two (or
howevermay transactions there are) have the same Transaction ID?  This would then lead to essentially having to split
trasactionsinto two tables.  One for the general header information, and another for the line details. 

Ideas on this?


> special note do not use only 2 decimal points in the
> accounting tables.
<SNIP>

> 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
>

I tried your function verbatim, but there were so many errors, the function could not even "compile".  I tinkered with
ita little bit and came up with this slightly modified version which gets "compiled": 

CREATE OR REPLACE  FUNCTION GetChildAccountDebits(PassedAccountID integer, PassedPeriodID integer) RETURNS NUMERIC AS
$FunctionCode$
DECLARE retval NUMERIC := 0.0;
BEGIN
    SELECT SUM(gl_transactions.debit) +
        CASE WHEN coa.doIhaveChildren THEN
            GetChildAccountDebits(coa.coa_id, PassedPeriodID )
        ELSE
                   0.0
        END
    INTO retval
    FROM gl_transactions, coa
    WHERE gl_transactions.coa_id = coa.coa_id
            AND coa.parent_id = PassedAccountID
        AND gl_transactions.period_id = PassedPeriodID;

    RETURN retval;
END;
$FunctionCode$
 LANGUAGE 'plpgsql' VOLATILE ;

    (I'll RTFM later to figure out what VOLATILE means :-)

When I try to use the function with a simple select, it fails with the error:

    ERROR:  column "coa.doihavechildren" must appear
    in the GROUP BY clause or be used in an aggregate function

None of the proposed solutions make sense to me.  I understand the error message (aggregation blah, blah).  I just
figurea way to get what I want.  How did you manage to get yours working? 

Thanks
James

Hi James,

There is some my publications about SART AML System based on banking
General Ledger (OLAP Data Warehouse and Chart of Accounts as dimension
with 60 000+ items) - may be helpful.

http://www.analyticsql.org/documentation.html
http://www.analyticsql.org/files/AITM-MoneyLaundering.pdf

Regards,
Blazej Oleszkiewicz

2008/10/12 James Hitz <jam_hit@yahoo.com>:
> 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.
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

James,
 
It is not good practice to delete an account with out first transfering the amount in that account to another account.  You will also need to make sure the account has a zero balance before deleting it.  You will also need to log the transactions if funds are moved between accounts with a reason why they were transfred.
 
To me a "intelegent" accounting system means that when you make an entry in one account, the system automatically makes a corresponding entry on the other side of the equal sign.  Example credit Office Supplies the system debits Cash On Hand (or what ever account is used to pay for office supplies).
 
The issue on the update, try using an if statement like
    If new.amt != old.amt Then
        Do Amount Changes that you already have in place
    End if

The database should then go ahead an update the parent wtihout an issues.  If that does not work create a function that drops the trigger, update the table and then creates the trigger.  I am sure that this type of change (moving accounts) will not be a common thing once the COA has been set up and in use for a while.
 
HTH.
Michael

> Date: Mon, 10 Nov 2008 05:24:03 +0100
> From: bl.oleszkiewicz@gmail.com
> To: hitz@jamhitz.com
> Subject: Re: [GENERAL] Chart of Accounts
> CC: pgsql-general@postgresql.org
>
> Hi James,
>
> There is some my publications about SART AML System based on banking
> General Ledger (OLAP Data Warehouse and Chart of Accounts as dimension
> with 60 000+ items) - may be helpful.
>
> http://www.analyticsql.org/documentation.html
> http://www.analyticsql.org/files/AITM-MoneyLaundering.pdf
>
> Regards,
> Blazej Oleszkiewicz
>
> 2008/10/12 James Hitz <jam_hit@yahoo.com>:
> > Dear All,
> >
> > I have just started experimenting with PGSQL, with a view to migrate from the SQL server I use currently. I am trying to implement an "intelligent" Chart of Accounts for an accounting program. The following is long-winded but please bear 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 a child 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 assigning a 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 above that 'Motor Van' was a liability, attempting to change its parent_id from 1 to 2 is erronous and somewhat interesting because the amt for all related accounts are reset to unpredictible values, AND the parent_id does not change anyway.
> >
> > The problem lies squarely in the function coa_upd_amt().
> >
> > Any ideas.
> >
> > Thank you.
> >
> >
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Appreciate all the help. Thank you

On Mon, Nov 10, 2008 at 8:36 AM, Michael Black
<michaelblack75052@hotmail.com> wrote:
> James,
>
> It is not good practice to delete an account with out first transfering the
> amount in that account to another account.  You will also need to make sure
> the account has a zero balance before deleting it.  You will also need to
> log the transactions if funds are moved between accounts with a reason why
> they were transfred.
>
> To me a "intelegent" accounting system means that when you make an entry in
> one account, the system automatically makes a corresponding entry on the
> other side of the equal sign.  Example credit Office Supplies the system
> debits Cash On Hand (or what ever account is used to pay for office
> supplies).
>
> The issue on the update, try using an if statement like
>     If new.amt != old.amt Then
>         Do Amount Changes that you already have in place
>     End if
>
> The database should then go ahead an update the parent wtihout an issues.
> If that does not work create a function that drops the trigger, update the
> table and then creates the trigger.  I am sure that this type of change
> (moving accounts) will not be a common thing once the COA has been set up
> and in use for a while.
>
> HTH.
> Michael
>
>> Date: Mon, 10 Nov 2008 05:24:03 +0100
>> From: bl.oleszkiewicz@gmail.com
>> To: hitz@jamhitz.com
>> Subject: Re: [GENERAL] Chart of Accounts
>> CC: pgsql-general@postgresql.org
>>
>> Hi James,
>>
>> There is some my publications about SART AML System based on banking
>> General Ledger (OLAP Data Warehouse and Chart of Accounts as dimension
>> with 60 000+ items) - may be helpful.
>>
>> http://www.analyticsql.org/documentation.html
>> http://www.analyticsql.org/files/AITM-MoneyLaundering.pdf
>>
>> Regards,
>> Blazej Oleszkiewicz
>>
>> 2008/10/12 James Hitz <jam_hit@yahoo.com>:
>> > Dear All,
>> >
>> > I have just started experimenting with PGSQL, with a view to migrate
>> > from the SQL server I use currently. I am trying to implement an
>> > "intelligent" Chart of Accounts for an accounting program. The following is
>> > long-winded but please bear 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 a child 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 assigning a 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 above that 'Motor Van' was a
>> > liability, attempting to change its parent_id from 1 to 2 is erronous and
>> > somewhat interesting because the amt for all related accounts are reset to
>> > unpredictible values, AND the parent_id does not change anyway.
>> >
>> > The problem lies squarely in the function coa_upd_amt().
>> >
>> > Any ideas.
>> >
>> > Thank you.
>> >
>> >
>> >
>> >
>> > --
>> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-general
>> >
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>