I have a typical problem in my SP and I am not able to resolve it,
friends out there have to help me out.

I recently changed to fb server to fb embed server.

My SP ( given below ) is working fine when the variables are
returned and one extra commit click ( little red tick button in IB
Expert)


I searched on the web for commit. Can't find any solution

Please take a look into my SP. For space consideration I took some
of the repeating code in if else part.

CREATE PROCEDURE UPDATEDENOMINATION(
PENNIES INTEGER,
NICKELS INTEGER,
QUARTERS INTEGER,
DIMES INTEGER,
DOLLAR INTEGER,
FIVE INTEGER,
TEN INTEGER,
TWENTY INTEGER,
FIFTY INTEGER,
HUNDRED INTEGER,
FLAG INTEGER)
AS
DECLARE VARIABLE OPENINGBALANCE DECIMAL(18,2);
DECLARE VARIABLE CASHIN DECIMAL(18,2);
DECLARE VARIABLE CASHOUT DECIMAL(18,2);
DECLARE VARIABLE CLOSINGBALANCE DECIMAL(18,2);
DECLARE VARIABLE ACTIVITYDAY DATE;
BEGIN

IF (:FLAG =1 ) THEN /*OPENING BALANCE*/
BEGIN
UPDATE DENOMINATION SET BILLCOUNT = :PENNIES, FLAG=:FLAG
WHERE BILLTYPE = 'OBPENNIES';
UPDATE DENOMINATION SET BILLCOUNT = :NICKELS, FLAG=:FLAG
WHERE BILLTYPE = 'OBNICKELS';
UPDATE DENOMINATION SET BILLCOUNT = :DIMES, FLAG=:FLAG WHERE
BILLTYPE = 'OBQUARTERS';
UPDATE DENOMINATION SET BILLCOUNT = :QUARTERS, FLAG=:FLAG
WHERE BILLTYPE = 'OBDIMES';
UPDATE DENOMINATION SET BILLCOUNT = :DOLLAR, FLAG=:FLAG WHERE
BILLTYPE = 'OBDOLLAR';
UPDATE DENOMINATION SET BILLCOUNT = :FIVE, FLAG=:FLAG WHERE
BILLTYPE = 'OBFIVE';
UPDATE DENOMINATION SET BILLCOUNT = :TEN, FLAG=:FLAG WHERE
BILLTYPE = 'OBTEN';
UPDATE DENOMINATION SET BILLCOUNT = :TWENTY, FLAG=:FLAG WHERE
BILLTYPE = 'OBTWENTY';
UPDATE DENOMINATION SET BILLCOUNT = :FIFTY, FLAG=:FLAG WHERE
BILLTYPE = 'OBFIFTY';
UPDATE DENOMINATION SET BILLCOUNT = :HUNDRED, FLAG=:FLAG
WHERE BILLTYPE = 'OBHUNDRED';
SUSPEND;
END

ELSE IF (:FLAG =2 ) THEN /*CASH IN*/
BEGIN
SUSPEND;
END

ELSE IF (:FLAG =3 ) THEN /*CASH OUT*/
BEGIN
SUSPEND;
END

ELSE IF (:FLAG =4 ) THEN /*CLOSING BALANCE*/
BEGIN
SUSPEND;
END
/***the above code works, the below code is not working, if my SP
returns the variables used and committed it works, also for example
if I return salesday alone it is returning two rows , one is null
and one is date *********/

EXECUTE PROCEDURE GETDENOMINATIONTOTAL
RETURNING_VALUES
(:OPENINGBALANCE,:CASHIN,:CASHOUT,:CLOSINGBALANCE);

SELECT TODAY FROM CASHACTIVITY WHERE TODAY = CURRENT_DATE
INTO :ACTIVITYDAY;

IF (ACTIVITYDAY IS NULL) THEN
INSERT INTO CASHACTIVITY(TODAY, OPENINGBALANCE, CASHIN,
CASHOUT, CLOSINGBALANCE)
VALUES
(CURRENT_DATE, :OPENINGBALANCE, :CASHIN, :CASHOUT, :CLOSINGBALANCE);
ELSE
UPDATE CASHACTIVITY
SET OPENINGBALANCE =:OPENINGBALANCE,
CASHIN =:CASHIN,
CASHOUT =:CASHOUT,
CLOSINGBALANCE =:CLOSINGBALANCE
WHERE TODAY = CURRENT_DATE;

SUSPEND;
END

Thanks and Regards,

Muthu Annamalai