Message-ID: <34281.216.86.192.34.1043801682.squirrel@www.la-rubber.com>
Date: Tue, 28 Jan 2003 16:54:42 -0800 (PST)
Subject: LONG - Question on dealing w/ numerics
From: "David Durst" <ddurst@larubber.com>
To: <pgsql-sql@postgresql.org>
Importance: Normal
Reply-To: ddurst@larubber.com
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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)