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

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

justin <justin(at)emproshunts(dot)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(at)emproshunts(dot)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(at)enterprisedb(dot)com> wrote:
> justin <justin(at)emproshunts(dot)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.

On Mon, Oct 13, 2008 at 2:57 AM, justin <justin(at)emproshunts(dot)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(at)emproshunts(dot)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(at)emproshunts(dot)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(at)emproshunts(dot)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(at)emproshunts(dot)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(dot)hansen(at)gmail(dot)com> wrote:
> On Mon, Oct 13, 2008 at 2:57 AM, justin <justin(at)emproshunts(dot)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(at)emproshunts(dot)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 columns for 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(at)emproshunts(dot)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 however may transactions there are) have the same Transaction ID? This would then lead to essentially having to split trasactions into 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 it a 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 figure a 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(at)yahoo(dot)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(at)postgresql(dot)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(dot)oleszkiewicz(at)gmail(dot)com> To: hitz(at)jamhitz(dot)com> Subject: Re: [GENERAL] Chart of Accounts> CC: pgsql-general(at)postgresql(dot)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(at)yahoo(dot)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(at)postgresql(dot)org)> > To make changes to your subscription:> > http://www.postgresql.org/mailpref/pgsql-general> >> > -- > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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(at)hotmail(dot)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(dot)oleszkiewicz(at)gmail(dot)com
>> To: hitz(at)jamhitz(dot)com
>> Subject: Re: [GENERAL] Chart of Accounts
>> CC: pgsql-general(at)postgresql(dot)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(at)yahoo(dot)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(at)postgresql(dot)org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-general
>> >
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>