I have a function that is to create a Accounting JOURNAL entry.
The strange thing is the function works for simple entries such as:
Cash - Debit 100
A/R - Credit 100
But when I try to trick it or break it for testing purposes (IT DOES BREAK
WHEN IT SHOULDN'T) on a entry like this:
Cash - Debit 100
A/R - Credit 100
Cash - Credit 100
A/R - Debit 100
(Which should have a net affect of 0 on both accounts)
But here is the resulting balance on accounts,
Cash Debit Balance 200
A/R Credit Balance 200
Here is the function and I can't seem to figure out what is LOGICALLY
wrong and would produce these results.
create function
create_journal_entry_line(integer,integer,integer,numeric(20,2)) returns
INTEGER as '
DECLARE
eid ALIAS FOR $1;
aid ALIAS FOR $2;
ltype ALIAS FOR $3;
amount ALIAS FOR $4;
new_balance NUMERIC(20,2);
account_type RECORD;
account RECORD;
line RECORD;
BEGIN
select into account * from accounts where account_id = aid;
IF NOT FOUND THEN
return -1;
END IF;
IF account.account_active = ''f'' THEN
return -1;
END IF;
insert into journal_lines (entry_id,account_id,line_type,line_amount)
values (eid,aid,ltype,amount);
select into line * from journal_lines where entry_id = eid AND
account_id = aid AND ltype = ltype;
IF NOT FOUND THEN
return -1;
END IF;
select into account_type * from account_types where account_type_id =
account.account_type;
IF account_type.positive_account_balance_type = line.line_type THEN
new_balance := account.account_balance + amount;
ELSE
new_balance := account.account_balance - amount;
END IF;
UPDATE accounts SET account_balance = new_balance WHERE account_id =
account.account_id;
return line.entry_id;
END;' language 'plpgsql';
P.S. Line type represents 1 = Debit, 2 = Credit. The
positive_account_balance_type tells eithier if the account should have a
DEBIT or CREDIT balance (Represented the same as line type)
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
