Create or Replace function Get_znj (
Jfjzrq in Date,
Yje in number,
ZNJBL in number,
ZT in Varchar2,
Sjjfrq in Date,
Mian_znj in Varchar2,
Znj_jmcs in number
) return number is
V_ZNJ number (14,2);
V_date DATE;
V_days number;
Begin
V_znj: = 0;
IF ZT <= ' 4 ' Then
V_date: = sysdate;
END IF;
IF ZT >= ' 5 ' Then
V_date: = Sjjfrq;
END IF;
V_days: = Trunc (v_date)-trunc (JFJZRQ);
IF mian_znj = ' 1 ' then--no reduction of 1
BEGIN
V_ZNJ: = case is v_days <= 0 then 0
ELSE NVL (ROUND (v_days * yje * znjbl,2), 0)
END;
END;
elsif Mian_znj = ' 0 ' then--all 0 remission
BEGIN
V_znj: = 0;
END;
elsif mian_znj = ' 2 ' then--date reduction 2
BEGIN
V_ZNJ: = case is V_days-znj_jmcs <= 0 then 0
ELSE NVL (ROUND ((V_DAYS-ZNJ_JMCS) * yje * znjbl,2), 0)
END;
END;
elsif mian_znj = ' 3 ' then--scale reduction 3
BEGIN
V_ZNJ: = case is v_days <= 0 then 0
ELSE NVL (ROUND (ROUND (v_days * yje * znjbl,2) * (1-ZNJ_JMCS), 2), 0)
END;
END;
elsif mian_znj = ' 4 ' then--quota reduction 4
BEGIN
V_ZNJ: = case is v_days <= 0 then 0
ELSE NVL (ROUND (v_days * yje * znjbl,2), 0)-Znj_jmcs
END;
IF V_znj < 0 Then
V_znj: = 0;
END IF;
END;
ELSE-Non-deductible
BEGIN
V_ZNJ: = case is v_days <= 0 then 0
ELSE NVL (ROUND (v_days * yje * znjbl,2), 0)
END;
END;
END IF;
-Late fee = Amount payable When the late fee is greater than the amount payable
IF v_znj > Yje Then
V_ZNJ: = Yje;
END IF;
return V_ZNJ;
End Get_znj;
This article from "Yang Hailong Blog" blog, reproduced please contact the author!
Calculation of Oracle late fees