Re: Chart of Accounts] - Mailing list pgsql-general

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 <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, 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. :-(