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_typeRECORD; 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)
