I am trying to create a procedure that will update the sum totals for
an invoice by getting the sum of the invoice detail rows for that
invoice.
My procedure is:
CREATE PROCEDURE UPDATE_INVOICE_TOTALS (INVOICE_NO INTEGER) AS
DECLARE VARIABLE TPRICE NUMERIC(14,2);
DECLARE VARIABLE TCOST NUMERIC(14,2);
BEGIN
SELECT SUM(TOTAL_PRICE),SUM(TOTAL_COST) FROM INVOICE_DETAIL WHERE
INVOICE_NUM = :INVOICE_NO INTO :TPRICE,:TCOST;
IF (TPRICE IS NULL) THEN TPRICE=0;
IF (TCOST IS NULL) THEN TCOST=0;
UPDATE INVOICE SET SUBTOTAL_AMT = :TPRICE,TOTAL_COST
= :TCOST,TOTAL_AMT=(:TPRICE+SHIPPING_AMT+TAX_AMT) WHERE INVOICE_NUM
= :INVOICE_NO;
SUSPEND;
END;
This works most of the time, but sometimes I get a seemingly random
TOTAL_AMT. Can anyone comment on why that happens or what I am doing
wrong? I appreciate all help. Thanks.

To give more info, what I do is run this procedure in the application
code after the user updates, inserts or deletes a row in the
INVOICE_DETAIL table for the invoice they are working on. I then
refresh the displayed invoice totals to the user and sometimes the
TOTAL_AMT is incorrect.

Here is the schema in case that helps.

create domain T_MONEY_TYPE as NUMERIC(14,2) default 0 not null;

create table INVOICE (
INVOICE_NUM INTEGER not null,
CREATE_TS TIMESTAMP default
CURRENT_TIMESTAMP not null,
USER_ID VARCHAR(15) not null,
INVOICE_DATE DATE not null,
CUSTOMER_ID VARCHAR(8) not null,
PAYMENT_TYPE VARCHAR(12) not null,
SUBTOTAL_AMT T_MONEY_TYPE not null,
SHIPPING_AMT T_MONEY_TYPE not null,
TAX_AMT T_MONEY_TYPE not null,
TOTAL_AMT T_MONEY_TYPE not null,
TOTAL_COST T_MONEY_TYPE not null,
SALESPERSON_ID VARCHAR(6),
END_DATE DATE,
PAYMENT_DATE DATE,
SHIPPED_DATE DATE,
PAID T_BOOLEAN_TYPE not null,
PRICING_CODE CHAR(2) not null,
TOTAL_WEIGHT NUMERIC(7,2) default 0 not
null,
PACKAGED_TS TIMESTAMP,
PACKAGED_BY_USER_ID VARCHAR(15),
RMA VARCHAR(8),
NOTE VARCHAR(254),
RMA_NOTE VARCHAR(254),
constraint INVOICE_PK primary key (INVOICE_NUM)
);
create table INVOICE_DETAIL (
INVOICE_NUM INTEGER not null,
STOCK_NUM VARCHAR(12) not null,
QUANTITY NUMERIC(7) default 0 not
null,
DESCRIPTION VARCHAR(50),
PRICE_EACH T_MONEY_TYPE not null,
COST_EACH T_MONEY_TYPE not null,
TOTAL_PRICE T_MONEY_TYPE not null,
TOTAL_COST T_MONEY_TYPE not null,
INFO VARCHAR(12),
PURCHASE_ORDER_ID INTEGER,
QUANTITY_SHIPPED NUMERIC(7) default 0 not
null,
constraint INVOICE_DETAIL_PK primary key (INVOICE_NUM, STOCK_NUM)
);