Appreciate all the help. Thank you On Mon, Nov 10, 2008 at 8:36 AM, Michael Black <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] >> To: [EMAIL PROTECTED] >> 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 <[EMAIL PROTECTED]>: >> > 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 > >
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
