Re: Chart of Accounts

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