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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
