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 <[EMAIL PROTECTED]> 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. :-(
