Oracle Function

CREATE OR REPLACE FUNCTION OFFICE.fbill_getbalance (billid NUMBER, total NUMBER)

   RETURN NUMBER

IS

   paid      NUMBER;

   balance   NUMBER;

 

BEGIN

   balance := total;

 

--get total paid

   SELECT SUM (n_paidamount)

     INTO paid

     FROM tbnk_payoutbillconn i

    WHERE i.n_billid = billid AND i.s_billorpur = 'B';

 

--get balance of bill

   balance := NVL (total, 0) - NVL (paid, 0);

   RETURN balance;

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      RETURN balance;

   WHEN OTHERS

   THEN

      RETURN balance;

      RAISE;

END fbill_getbalance;