select loan_id, withd_date, sum(withd_amount * rate/100) tran_total from t_withdrawal tw, t_rate
where tw.withd_date = t_rate.rate_date 
group by loan_id, withd_date;

create table t_rate 
  (
    Rate_date date,
    rate number(15,3)
  );


create table t_withdrawal
  (
    Loan_id number,
    withd_date date,
    Withd_amount number
  );


create table t_interest
  (
    Loan_id number,
    Interest_date date,
    Interest_amount number(15,3)
  );

create table t_ledger_trans
  (
    Loan_id number,
    Trans_date date,
    tran_total number(15,3)
  );

insert all 
  INTO t_rate values (to_date('01-01-2000','dd-mm-yyyy'), 1.5)
  INTO t_rate values (to_date('02-01-2000','dd-mm-yyyy'), 2.5)
  INTO t_rate values (to_date('03-01-2000','dd-mm-yyyy'), 0.5)
  INTO t_rate values (to_date('04-01-2000','dd-mm-yyyy'), 0.5)
  INTO t_withdrawal values (1, to_date('01-01-2000','dd-mm-yyyy'), 120)
  INTO t_withdrawal values (2, to_date('02-01-2000','dd-mm-yyyy'), 170)
  INTO t_withdrawal values (3, to_date('03-01-2000','dd-mm-yyyy'), 55)
  INTO t_withdrawal values (4, to_date('01-01-2000','dd-mm-yyyy'), 310)
  INTO t_withdrawal values (5, to_date('03-01-2000','dd-mm-yyyy'), 110)
  INTO t_withdrawal values (1, to_date('01-01-2000','dd-mm-yyyy'), 70)
  INTO t_withdrawal values (2, to_date('02-01-2000','dd-mm-yyyy'), 695)
  INTO t_withdrawal values (5, to_date('03-01-2000','dd-mm-yyyy'), 153)
  INTO t_withdrawal values (1, to_date('04-01-2000','dd-mm-yyyy'), 320)
select * from dual;

-- TESTPAC1 specification
CREATE OR REPLACE PACKAGE testPac1 AS

  TYPE REC_COMtest IS RECORD
    (
      Loan_id number,
      T_date date,
      t_amount number(15,3)
    );


  -- Define cursor and table(array) of to hold COM CHARGE data
  TYPE CUR_COMtest IS REF CURSOR RETURN REC_COMtest;
  TYPE ARY_COMtest IS TABLE OF REC_COMtest;

  PROCEDURE P_GET_DATA_TEST
      (
        ar_My_Array out ARY_COMtest
      );

  PROCEDURE P_insert_DATA_TEST 
  ;
   
end testPac1;

CREATE OR REPLACE PACKAGE BODY testPac1 AS


  PROCEDURE P_GET_DATA_TEST
      (
        ar_My_Array out ARY_COMtest
      ) 
      AS 
       MyCur    CUR_COMtest;
       MyCur1   Cur_COMtest;
       I_total_sum number;
     BEGIN
  
      OPEN MyCur FOR
      select loan_id, withd_date, withd_amount * rate/100 Interest_amount from t_withdrawal tw, t_rate
      where tw.withd_date = t_rate.rate_date ;   
      
      FETCH MyCur BULK COLLECT INTO ar_My_Array;
      
      close MyCur;

  END;

  PROCEDURE P_insert_DATA_TEST 
  IS
    AR_MY_ARRAY ARY_COMtest;
 
  BEGIN

   testPac1.P_GET_DATA_TEST( AR_MY_ARRAY);

   FOR I IN 1..ar_My_Array.COUNT LOOP
     INSERT INTO t_interest (Loan_id, Interest_date, Interest_amount) 
          SELECT ar_My_Array(I).Loan_id, ar_My_Array(I).t_date, ar_My_Array(I).t_amount
     FROM DUAL; 
   END LOOP; 


  END;

end testPac1;