This is the PL/SQL function to calculate amount based on different rate stored in Database declare the function and the amount it accepts

“FUNCTION of_calc_function(ad_submissiondate IN DATE, as_type IN VARCHAR, as_group IN VARCHAR, an_months IN NUMBER,an_tax IN NUMBER, an_result OUT NUMBER, as_message OUT VARCHAR ) RETURN NUMBER IS`

Declaration of variables required for calculation given the request submission date and the month required to iterate

ld_date                            DATE; 
ld_maxdate                      DATE; 
ln_rate                            NUMBER(10,4);
 ln_pn                        NUMBER(30,2); 
ln_pn_supprt                 NUMBER(30,2);
 ln_amount                       NUMBER(30,2); 
ln_spoint              NUMBER(30,2);
 ls_message                   VARCHAR2(4000); 
ld_pn_sdate                  DATE; 
ln_totval                    NUMBER(38,18); 
ln_totval_support            NUMBER(38,18); 
ln_months                    NUMBER; 
ld_datesub  DATE; 
BEGIN
ln_pn := 0;
ln_totval := 0;
ln_totval_support := 0;
ln_amount   := an_tax;
ln_months  := an_months;  
ld_datesub :=trunc(to_date(ad_submissiondate,'dd/mm/yyyy'));
--Minus the month acceptance from the function to get the start date to start  penalty calculation ld_pn_sdate:=trunc(to_date(ADD_MONTHS(ld_datesub,-ln_months),'dd/mm/yyyy'));

The following block is to select the maximim valid date from rate table and specific rates, then Iterates from the current month to the last valid date to get the amount

SELECT TRUNC(MAX(Fromdate))
INTO ld_maxdate
FROM RATES_TABLE
WHERE type=as_type and group =as_group;  
 ----    
FOR li_index  IN  1 .. ln_months 
LOOP

 ld_date :=to_date(ADD_MONTHS(ld_pn_sdate, li_index), 'dd/mm/yyyy');   ---start  compute penalty from next month after startdate 
 
    --get  rates for the dates 
IF TO_DATE(ld_date, 'dd/mm/yyyy') >=TO_DATE(ld_maxdate, 'dd/mm/yyyy')   THEN
  SELECT tr, lsp
  INTO   ln_rate, ln_spoint
  FROM   RATES_TABLE
  WHERE  TO_DATE(Fromdate, 'dd/mm/yyyy') = TO_DATE(ld_maxdate, 'dd/mm/yyyy')
              AND type=as_type and group =as_group;
ELSE
  SELECT tr, lsp
  INTO   ln_rate, ln_spoint
  FROM   RATES_TABLE
  WHERE TO_DATE(Fromdate, 'dd/mm/yyyy') <= TO_DATE(ld_date, 'dd/mm/yyyy') AND TRUNC(VALIDTO) >= TO_DATE(ld_date, 'dd/mm/yyyy')
             AND type=as_type AND group =as_group;
END IF; 

ln_pn_supprt    := ln_spoint;
ln_pn        := ln_amount*ln_rate*0.01;

ln_totval:=ln_totval+ln_pn;
ln_totval_support:=ln_totval_support+ln_pn_supprt;


END LOOP;  

   -- Pick the highest value of the two The higher of ln_totval and ln_totval_support
IF ln_totval <   ln_totval_support THEN
   ln_totval  :=  ln_totval_support;
END IF;

   an_result :=  ln_totval;
END of_calc_function;`