From: David Durst <ddurst@larubber.com>
To: pgsql-sql@postgresql.org
Subject: LONG - Question on dealing w/ numerics
Date: Tue, 28 Jan 2003 16:54:42 -0800 (PST)
Message-ID: <34281.216.86.192.34.1043801682.squirrel@www.la-rubber.com> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ddurst@larubber.com
Subject: Re: LONG - Question on dealing w/ numerics
In-Reply-To: <34281.216.86.192.34.1043801682.squirrel@www.la-rubber.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox