Re: Chart of Accounts

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:
> 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 <justin(at)emproshunts(dot)com>
> To: hitz(at)jamhitz(dot)com
> Cc: pgsql-general(at)postgresql(dot)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,
>> 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.
>>>
>>>
>>>
>>>
>>>
>>>
>> --
>> 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
>>
>> No virus found in this incoming message.
>> Checked by AVG - http://www.avg.com
>> Version: 8.0.173 / Virus Database: 270.8.0/1721 - Release Date: 10/12/2008
>>
> 12:00 PM
>
>> No virus found in this incoming message.
>> Checked by AVG - http://www.avg.com
>> Version: 8.0.173 / Virus Database: 270.8.0/1721 - Release Date: 10/12/2008
>>
> 12:00 PM
>
>>