Chart of Accounts

You are making this far to complicated.

I just redid the accounting side of an application we have access to
source code, so been here and done this.

If i was not for the rest of the application i would have completely
redone the accounting table layout something like this

3 Accounting Tables

One has you chart of Accounts
Create table coa (
coa_id serial not null,
parent_id int not null default 0,
doIhaveChildren boolean default false
account_name text null )
primary key(coa_id)

Create Table general_ledger_transactions(
transaction_id serial not null
coad_id integer,
accounting_period integer,
debit numeric(20,10) ,
credit numeric(20,10),
transaction_date datestamp)
primary key (transaction_id)

special note do not use only 2 decimal points in the accounting tables.
If your application uses 10 decimal places somewhere then every table in
the database that has decimals needs to have the same precision.
Nothing is more annoying where a transaction says 1.01 and the other
side says 1.02 due to rounding. Also you want to split out the debit
and credits instead of using one column. Example one column accounting
table to track values entered how do you handle Crediting a Credit
Account Type. is it a negative or positive entry???

Create table accounting_periods (
accounting_period serial not null,
start_date date,
end_date date,
accounting_period_Open boolean)

I would used views and the application to create the tree list view i
think your after. As you also need to know the Open Balances, Debit,
Credits and Closing Balances by accounting period.. One idea is is
create a functions that scans through the general_ledger_transactions
table to get your values So create a View something like this

Example would by
Select Sum(debits) +
Case when coa.doIhaveChildren then
GetChildAccountDebits(coa.coa_id, period_id)
else
0.0
end;
from general_ledger_transactions, coa,
where general_ledger_transactions.coad_id = coa.coa_id
and coa.coa_id = SomPassedAccountID
group by general_ledger_transactions.period_id

What happen is the GetChildAccountDebits() function takes two
parameters. One is the coa_id and the other is accounting period to search

The function would look something like this

return Select Sum(debits) +
Case when coa.doIhaveChildren then
GetChildAccountDebits(coa.coa_id, period_id)
else
0.0
end;
from general_ledger_transactions, coa,
where general_ledger_transactions.coa_id= coa_id
and coa.parent_id = ThePassedAccountID
and general_ledger_transactions.period_id =PassedPeriodID

This creates a loop back which can be dangers if Parent_account is also
a Child_account of itself which creates an endless loop then creates a
stack error.

Outside of that is works great. i do something very similar Bill of
Material and in our Accounting

James Hitz wrote:

justin <justin@emproshunts.com> writes:

special note do not use only 2 decimal points in the accounting tables. If
your application uses 10 decimal places somewhere then every table in the
database that has decimals needs to have the same precision. Nothing is more
annoying where a transaction says 1.01 and the other side says 1.02 due to
rounding.

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

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

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

Gregory Stark wrote:

justin <justin@emproshunts.com> writes:

special note do not use only 2 decimal points in the accounting tables. If
your application uses 10 decimal places somewhere then every table in the
database that has decimals needs to have the same precision. Nothing is more
annoying where a transaction says 1.01 and the other side says 1.02 due to
rounding.

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

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

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

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

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

On Mon, Oct 13, 2008 at 6:33 AM, Gregory Stark <stark@enterprisedb.com> wrote:

justin <justin@emproshunts.com> writes:

special note do not use only 2 decimal points in the accounting tables. If
your application uses 10 decimal places somewhere then every table in the
database that has decimals needs to have the same precision. Nothing is more
annoying where a transaction says 1.01 and the other side says 1.02 due to
rounding.

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

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

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

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

merlin

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

I think you have two different problems here. On the one hand you have
rounding errors which are material when aggregated on the other hand
most sales transactions (for example) will come to a dollar and cents
figure. If you have two accounts with different precision then I think
from an accounting perspective you need to say something like this when
posting between the two:

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

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

Craig

Craig Bennett wrote:

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

I think you have two different problems here. On the one hand you have
rounding errors which are material when aggregated on the other hand
most sales transactions (for example) will come to a dollar and cents
figure. If you have two accounts with different precision then I think
from an accounting perspective you need to say something like this
when posting between the two:

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

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

Craig

Thats not the problem its the different tables having different
precision. We have a WIP tables that notes all the labor and
material consumed by all the jobs for an accounting period. So you have
some jobs all ways open crossing periods so you need to audit that WIP
process account which means going to the WIP tables and verifying that
the values in the WIP account equal to the jobs in the WIP tables. If
the detail differs even a a penny you have a problem you are not allowed
to simply call it rounding error. Pushing it into another account
called rounding error does not solve the problem. Values in the wip
tables need to equal the values in the General ledger tables

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

There are a couple of ways to solve your problem

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Jeff Williams wrote:

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

[...] Also you want to split out the debit and credits instead of
using one column. Example one column accounting table to track values
entered how do you handle Crediting a Credit Account Type. is it a negative
or positive entry???

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

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

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

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

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

Isak Hansen wrote:

Isak Hansen wrote:

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

[...] Also you want to split out the debit and credits instead of
using one column. Example one column accounting table to track values
entered how do you handle Crediting a Credit Account Type. is it a negative
or positive entry???

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

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

My accounting knowledge is really rusty, but I do remember that "credit"
and "debit" have specific meanings in accounting theory and refer to the
left (debit) and right (credit) columns of a "T" account. The sum of the
debit columns across all accounts in the ledger must match the sum of
the credit columns (the books are "balanced"). To keep the ledger
balanced, every transaction requires two (or more) entries into the
appropriate accounts and the debit-side and credit-side entries must match.

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

Cheers,
Steve

On Tue, Oct 14, 2008 at 5:07 PM, justin <justin@emproshunts.com> wrote:

because a credit account is a liability account aka a negative account so
credit a credit account causes it to go UP not down.

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

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

On Tue, Oct 14, 2008 at 3:50 PM, Isak Hansen <isak.hansen@gmail.com> wrote:

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

[...] Also you want to split out the debit and credits instead of
using one column. Example one column accounting table to track values
entered how do you handle Crediting a Credit Account Type. is it a negative
or positive entry???

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

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

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

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

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

I just redid the accounting side of an application we have
access to
source code, so been here and done this.

If i was not for the rest of the application i would have
completely
redone the accounting table layout something like this

Ok with the tables

I would used views and the application to create the tree
list view i think your after. As you also need to know the Open
Balances, Debit, Credits and Closing Balances by accounting period..
One idea is is
create a functions that scans through the
general_ledger_transactions
table to get your values So create a View something like
this

Example would by
Select Sum(debits) +
Case when coa.doIhaveChildren then
GetChildAccountDebits(coa.coa_id,
period_id)
else
0.0
end;
from general_ledger_transactions, coa,
where general_ledger_transactions.coad_id = coa.coa_id
and coa.coa_id = SomPassedAccountID
group by general_ledger_transactions.period_id

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

What happen is the GetChildAccountDebits() function takes
two parameters. One is the coa_id and the other is accounting
period to search

The function would look something like this

return Select Sum(debits) +
Case when coa.doIhaveChildren then
GetChildAccountDebits(coa.coa_id, period_id)
else
0.0
end;
from general_ledger_transactions, coa,
where general_ledger_transactions.coa_id= coa_id
and coa.parent_id = ThePassedAccountID
and general_ledger_transactions.period_id = PassedPeriodID

PassedPeriodID ??? ...and this?

This creates a loop back which can be dangers if
Parent_account is also a Child_account of itself which creates
an endless loop then creates a stack error.

I think this is easy enough to control with a CHECK constraint I think. Otherwise, I see the sense in using two 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@emproshunts.com> wrote:

<SNIP>

Create Table general_ledger_transactions(
transaction_id serial not null
coad_id integer,
accounting_period integer,
debit numeric(20,10) ,
credit numeric(20,10),
transaction_date datestamp)
primary key (transaction_id)

A single transaction will often have at least two entities - typically a debit and a credit. Shouldn't the two (or 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@yahoo.com>:

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

Appreciate all the help. Thank you

On Mon, Nov 10, 2008 at 8:36 AM, Michael Black
<michaelblack75052@hotmail.com> wrote: