Re: Chart of Accounts]

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(at)emproshunts(dot)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 <http://en.wikipedia.org/wiki/Fiscal_year>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. :-(