Re: Group BY and Chart of Accounts

There was a number of code mistakes in my examples as i was just doing
it off the top of my head, just went through it and got it all working.

I had to change the function around as it was double dipping accounts
just run this and it does work.

--------------------------------------------------------------

Create table coa (
coa_id serial not null,
parent_id int not null default 0,
doIhaveChildren boolean default false,
account_name text null );

Create Table general_ledger_transactions(
transaction_id serial not null,
coa_id integer,
accounting_period integer,
debit numeric(20,10) ,
credit numeric(20,10),
transaction_date timestamp);

Create table accounting_periods (
accounting_period serial not null,
start_date date,
end_date date,
accounting_period_Open boolean);

Insert into coa values (10, default, True, 'ParentAccount1');
Insert into coa values (11, 10, True, 'ChildAccount1');
Insert into coa values (12, 11, false, 'ChildAccount2');
Insert into coa values (13, default, false, 'ChildAccount3');

Insert into Accounting_Periods values ( 1, '2008-10-01', '2008-10-31',
true );
Insert into Accounting_Periods values ( 2, '2008-11-01', '2008-11-30',
true );

Insert into general_ledger_transactions values( default, 11, 1, 30.0,
0.0, current_timestamp);
Insert into general_ledger_transactions values( default, 11, 1, 20.0,
0.0, current_timestamp);
Insert into general_ledger_transactions values( default, 12, 1, 10.0,
0.0, current_timestamp);
Insert into general_ledger_transactions values( default, 12, 1, 50.0,
0.0, current_timestamp);
Insert into general_ledger_transactions values( default, 11, 1, 1.0,
0.0, current_timestamp);
Insert into general_ledger_transactions values( default, 13, 1, 0.0,
111.0, current_timestamp);

Insert into general_ledger_transactions values( default, 11, 2, 0.0,
30.0, current_timestamp);
Insert into general_ledger_transactions values( default, 11, 2, 0.0,
20.0, current_timestamp);
Insert into general_ledger_transactions values( default, 12, 2, 0.0,
10.0, current_timestamp);
Insert into general_ledger_transactions values( default, 12, 2, 0.0,
50.0, current_timestamp);
Insert into general_ledger_transactions values( default, 11, 2, 0.0,
1.0, current_timestamp);
Insert into general_ledger_transactions values( default, 13, 2, 111.0,
0.0, current_timestamp);

CREATE OR REPLACE FUNCTION GetChildAccountDebits(PassedAccountID
integer, PassedPeriodID integer) RETURNS NUMERIC AS
$FunctionCode$
DECLARE
retval NUMERIC = 0.0 ;
begin

return (SELECT
coalesce ( (select Sum(general_ledger_transactions.debit ) from
general_ledger_transactions where general_ledger_transactions.coa_id =
coa.coa_id and general_ledger_transactions.accounting_period =
PassedPeriodID), 0 ) +
(CASE WHEN coa.doIhaveChildren THEN
GetChildAccountDebits(coa.coa_id, PassedPeriodID )
ELSE
0.0
END)
FROM coa
WHERE coa.parent_id = PassedAccountID);

end;
$FunctionCode$
LANGUAGE 'plpgsql' VOLATILE ;

select 10, getchildaccountdebits(10,1)
union
select 11, getchildaccountdebits(11,1)
union
select 12, getchildaccountdebits(12,1);

--------------------------------------------------

WaGathoni wrote:
> Justin was recommending a solution to the Chart of Accounts Problem
> posted by jamhitz:
>
> MQUOTE>
> 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)
>
> ...
>
> Create table accounting_periods (
> accounting_period serial not null,
> start_date date,
> end_date date,
> accounting_period_Open boolean)
>
> </QUOTE>
>
> Would someone please assist me. Why is the following function:...
>
>
> CREATE OR REPLACE FUNCTION GetChildAccountDebits(PassedAccountID
> integer, PassedPeriodID integer) RETURNS NUMERIC AS
> $FunctionCode$
> DECLARE retval NUMERIC :=0.0;
> begin
> SELECT Sum(gl_transactions.debit) +
> CASE WHEN coa.doIhaveChildren THEN
> GetChildAccountDebits(coa.coa_id, PassedPeriodID )
> ELSE
> 0.0
> END
> INTO retval
> FROM gl_transactions, coa
> WHERE gl_transactions.coa_id= coa.coa_id
> AND coa.parent_id = PassedAccountID
> AND gl_transactions.period_id = PassedPeriodID;
>
> RETURN retval;
> end;
> $FunctionCode$
> LANGUAGE 'plpgsql' VOLATILE ;
>
> ....failing with an error to the effect that that that
> coa.doaIhaveChildren and coa.coa_id must be included in the GROUP BY
> clause.... and what is is the recommended course of action.
>
> I have limited Internet access, so forgive me when I raise the same
> question 8 days later.
>
> Thanks
>
>