SQL> desc transactions;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ACCT_ID                                            NUMBER
 KIND                                               CHAR(1)
 AMOUNT                                             FLOAT(63)
 TIME_TAG                                           DATE
 STATUS                                             VARCHAR2(50)

SQL> desc journal;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ACCT_ID                                            NUMBER
 KIND                                               CHAR(1)
 AMOUNT                                             FLOAT(63)
 JOURNAL_DATE                                       DATE

SQL> desc accounts;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ACCT_ID                                            NUMBER
 BALANCE                                            NUMBER
====================================================================================

-- CREATE PACKAGE--
create or replace package bank_transactions AS
minimum_balance CONSTANT REAL := 100.00;
 
procedure apply_transactions;
procedure enter_transactions(acct INT,kind CHAR,amount REAl);
end bank_transactions;


-- PACKAGE BOSY--

create or replace package body bank_transactions AS							
      new_status varchar2(50) := 'UNKNOWN';							
     procedure credit_account(acct INT, credit REAL);						
     procedure debit_account(acct INT, debit REAL);
	
--procedure apply_transactions--					
     PROCEDURE apply_transactions							
     IS		     					
     cursor trans_cursor IS							
       select acct_id, kind, amount from transactions					
      where status = 'Pending'							
       order by time_tag							
       FOR UPDATE OF status;							
     BEGIN
      							
      for trans IN trans_cursor							
      LOOP							
       IF trans.kind = 'D' THEN							
           debit_account(trans.acct_id, trans.amount);						
        ELSEIF trans.kind = 'C' THEN							
           credit_account(trans.acct_id, trans.amount);						
        ELSE							
           new_status := 'Rejected';							
        END IF;							
           UPDATE transactions set status = new_status							
           WHERE current OF trans_cursor;							
      END LOOP;	
      					
     END apply_transactions;

-- procedure enter_transactions --							
     PROCEDURE enter_transactions(acct INT, kind CHAR, amount REAL)					
     IS							
     BEGIN							
       insert into transactions							
        values(acct, kind, amount, 'Pending', sysdate);			
     END enter_transactions;

-- procedure do_journal_entry --							
     PROCEDURE do_journal_entry (acct INT, kind CHAR, new_bal REAL)					
     IS							
     BEGIN							
        insert into journal							
        values(acct, kind, new_bal, sysdate);				
        IF kind = 'D' THEN							
           new_status := 'Debit Applied';							
        ELSE							
           new_status := 'Credit Applied';							
        END IF;							
     END do_journal_entry;

-- procedure credit_account --							
     PROCEDURE credit_account(acct INT, credit REAL)						
     IS	     					
     old_balance REAl;							
     new_balance REAL;							
     BEGIN							
        select balance into old_balance from accounts							
        where acct_id = acct							
        for UPDATE OF balance;							
        new_balance := old_balance + credit;							
        UPDATE accounts SET balance = new_balance							
        where acct_is = acct;							
      do_journal_entry(acct, 'C', new_balance);					
      EXCEPTION							
       when NO_DATA_FOUND then							
         new_status := 'BAd Account Number';							
       when OTHERS then							
         new_status := SUBSTR(SQLERRM,1,70);					
     END credit_account;

-- procedure debit_account --							
     PROCEDURE debit_account(acct INT,debit REAL)						
     IS	     					
     old_balance REAl;							
     new_balance REAL;							
     insufficient_funds EXCEPTION;							
     BEGIN							
        select balance into old_balance from accounts							
       where acct_id = acct							
        for UPDATE OF balance;							
        new_balance := old_balance - debit;							
        IF new_balance >= minimum_balance THEN							
          UPDATE accounts SET balance = new_balance							
           where acct_id = acct;							
         do_journal_entry(acct,'D',new_balance);					
        ELSE							
         RAISE insufficient_funds;							
        END IF;							
        EXCEPTION							
          when NO_DATA_FOUND then							
               new_status := 'Bad Account Number';							
          when insufficient_funds then							
               new_status := 'Insufficient Funds';							
          when OTHERS then							
               new_status := SUBSTR(SQLERRM,1,70);					
     END debit_account;							
END bank_transactions;