From: | "David Durst" <ddurst(at)larubber(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | LONG - Question on dealing w/ numerics |
Date: | 2003-01-29 00:54:42 |
Message-ID: | 34281.216.86.192.34.1043801682.squirrel@www.la-rubber.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Roberto Mello | 2003-01-29 01:28:22 | Re: Cross-database references |
Previous Message | --CELKO-- | 2003-01-28 23:54:15 | Re: double linked list |