I have created a PL SQL function to calculate amount based of rates stored in the database for different period, The function is supposed to iterate through given month and get the rate of each month and do calculation for all months and obtain the total amount for all month as the result.
Unfortunately the function display different results when run in different computer with different data format. Could i be missing the calculation of months in the logic?
This is the PL/SQL function to calculate amount based on different rate stored in Database declare the function and the amount it accepts
For instance in this function the submission date is today 12 July 2024 and months is 7 it is supposed to iterate in the loop for 7 monthes but in some cases it iterate 7 months, others 6 months and other 4 months. what will i be missing the logic? kindly assist
“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;`