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
>
I start getting lost : SomPassedAccountID ??? Where is this coming from?
> 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
PassedPeriodID ??? ...and this?
> 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
columnsfor transactions - If I were writing an application for a bank, then using one column only may have potential
pitfalls.
Regards
