Re: [GENERAL] Chart of Accounts

[WaGathoni]

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

Re: [GENERAL] Chart of Accounts

[Michael Black]

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

Re: [GENERAL] Chart of Accounts

[Blazej]

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

Re: [GENERAL] Chart of Accounts

[James Hitz]

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

--- On Mon, 13/10/08, justin <[EMAIL PROTECTED]> wrote:
> 
> 
> 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.  


> 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




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

Re: [GENERAL] Chart of Accounts]

[justin]

James Hitz wrote:

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 <[EMAIL PROTECTED]> 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, 
general_ledger_transactions.coa_id


I start getting lost : SomPassedAccountID ??? Where is this coming from?
  
I put this in so the select statement would be limited to a specific 
account the user would choose from the UI


it can be left out, it just would get all the accounts grouped by 
accounting period. 

I added to the group by clause the coad_id so it would not sum all the 
accounts as just one value. 

PassedPeriodID ??? ...and this?

  

I hope this clarifies things


Create or replace  Function GetChildAccountDebits(PassedAccountID 
integer, PassedPeriodID integer) returns numeric as

$FunctionCode$
begin
  return  Select Sum(debits) +
   Case when  coa.doIhaveChildren then
   GetChildAccountDebits(coa.coa_id, PassedPeriodID )
else
  0.0
end;
   from general_ledger_transactions, coa,
where general_ledger_transactions.coa_id= coa_id
 and  coa.parent_id = PassedAccountID
and general_ledger_transactions.period_id = PassedPeriodID ;
end;
$FunctionCode$
LANGUAGE 'plpgsql' VOLATILE ;

Same as above one would normally limit account balances by accounting 
Period so only the values posted to that period show up. 



Also note Some people have 12 accounting periods aka calendar year 
others have 13 accounting periods  52 weeks in a year 4 weeks to an 
accounting period. = 13 periods
Also fiscal years don't have 
to match to calendar years this is the reason why accounting periods 
must be identified somehow to group transactions by period.
  

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.

  
The difference between one column or two columns  is personal preference 
like allot things.   I prefer two columns  as it makes more logical 
sense to me to split it out.
But at presently i'm stuck using a system that uses One column in the gl 
table. :-(

Re: [GENERAL] Chart of Accounts

[James Hitz]

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 <[EMAIL PROTECTED]> 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





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

Re: [GENERAL] Chart of Accounts

[Robert Parker]

On Tue, Oct 14, 2008 at 3:50 PM, Isak Hansen <[EMAIL PROTECTED]> wrote:
> On Mon, Oct 13, 2008 at 2:57 AM, justin <[EMAIL PROTECTED]> 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!

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

Re: [GENERAL] Chart of Accounts

[Isak Hansen]

On Tue, Oct 14, 2008 at 5:07 PM, justin <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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.
>

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

Re: [GENERAL] Chart of Accounts

[Steve Crawford]

Isak Hansen wrote:

On Mon, Oct 13, 2008 at 2:57 AM, justin <[EMAIL PROTECTED]> 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




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

Re: [GENERAL] Chart of Accounts

[justin]

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 <[EMAIL PROTECTED]> 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.
  

Re: [GENERAL] Chart of Accounts

[Isak Hansen]

On Mon, Oct 13, 2008 at 2:57 AM, justin <[EMAIL PROTECTED]> 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.

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

Re: [GENERAL] Chart of Accounts

[justin]

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  ARAR: 4785   78486 
   5 aka CustomerOwesMe   $25
5788  ARAR: 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 <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
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,
 

Re: [GENERAL] Chart of Accounts

[justin]

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




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

Re: [GENERAL] Chart of Accounts

[Craig Bennett]

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
   CROriginal 4 Decimal Account 2.0010

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



Craig



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

Re: [GENERAL] Chart of Accounts

[Merlin Moncure]

On Mon, Oct 13, 2008 at 6:33 AM, Gregory Stark <[EMAIL PROTECTED]> wrote:
> justin <[EMAIL PROTECTED]> 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

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

Re: [GENERAL] Chart of Accounts

[justin]

Gregory Stark wrote:

justin <[EMAIL PROTECTED]> 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. 

Re: [GENERAL] Chart of Accounts

[Gregory Stark]

justin <[EMAIL PROTECTED]> 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!

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

Re: [GENERAL] Chart of Accounts

[justin]

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 - 

[GENERAL] Chart of Accounts

[James Hitz]

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