Re: Chart of Accounts - Mailing list pgsql-general

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@emproshunts.com>
To: hitz@jamhitz.com
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,     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$
beginupdate 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$
beginUPDATE 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$
beginIF 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

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