Hi
I Have a table like below
create table TMP_TAX
(
id_fx INTEGER not null,
value_fx NUMBER(22,2),
perc_fx NUMBER(22,2)
);
alter table TMP_TAX
add constraint TMP_TAX_PK primary key (ID_FX)
using index ;
Insert into TMP_TAX
(ID_FX, VALUE_FX, PERC_FX)
Values
(0, 1903.98, 0);
Insert into TMP_TAX
(ID_FX, VALUE_FX, PERC_FX)
Values
(2, 924.4, 15);
Insert into TMP_TAX
(ID_FX, VALUE_FX, PERC_FX)
Values
(1, 922.67, 7.5);
Insert into TMP_TAX
(ID_FX, VALUE_FX, PERC_FX)
Values
(3, 913.93, 22.5);
Insert into TMP_TAX
(ID_FX, VALUE_FX, PERC_FX)
Values
(4, 0, 27.5);
COMMIT;
I must do the sum according to the tax and the ranges framed in this table
there are only 5 ranges from zero to four (ID_FX)
if the value is less than the first range (1903.98) , it must return zero tax
if it is bigger it must make the difference of this value 1903.98 and go to the next track (ID_FX=1) If the new value is greater than the VALUE_FX of this range, you must multiply the VALUE_FX by PERC_FX divided by 100, otherwise you must multiply the new value by PERC_FX divided by 100
I will try to explain better in a plsql block
declare
cursor c1 is
select *
from TMP_TAX T
order by T.ID_FX ASC;
r_c1 c1%rowtype;
v_valor number:=3000;
v_devido number:=0;
begin
open c1;
fetch c1 into r_c1;
while C1%found and V_VALOR>0 loop
CASE r_c1.id_fx
WHEN 0 THEN
V_VALOR := V_VALOR - r_c1.VALUE_FX ;
WHEN 4 THEN
v_devido := v_devido + round(V_VALOR * r_c1.PERC_FX/100,2);
ELSE
v_devido:= v_devido + round(least(r_c1.VALUE_FX , V_VALOR ) * r_c1.PERC_FX/100,2);
V_VALOR := V_VALOR - r_c1.VALUE_FX ;
END CASE;
fetch C1 into r_c1;
end loop;
CLOSE C1;
DBMS_OUTPUT.put_line('TAX DUE '|| v_devido);
end;
I would like to know if there is any way to make this plsql block in a single query
it is necessary to change the variable values to perform the tests
Thank you in advance
Using
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0