LONG - Question on dealing w/ numerics

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)

Responses

Browse pgsql-sql by date

  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