Hi,
I'm using Version 20 of Apex. I have created an Interactive Report. Here I need to calculate a result based on some input parameters. I have created the function and it works fine in the DB.
But I do not know how to call this function in APEX.
Pseudo Code of the function is as below:-
create or replace function calc_rating (in_tolerance in number, in_TOLERANCE_DIRECTION in varchar2, in_quan_result number, in_target in number, in_kpi_type in varchar2)
return varchar2 as
v1_rating number(38,6);
v2_rating number(38,6);
v_inter_rating number(38,6);
v_final_rating varchar2(50);
begin
v1_rating:=(100-((in_quan_result-in_target)/in_target)*100);
v2_rating:=(100+((in_quan_result-in_target)/in_target)*100);
if (in_TOLERANCE_DIRECTION = '+-' and v1_rating > 0)
then
v_inter_rating:= v1_rating;
else v_inter_rating:=v2_rating;
if (in_tolerance = 3 and v_inter_rating >=97) then v_final_rating:= 'Green';
else if (in_tolerance = 3 and v_inter_rating >=95 and v_inter_rating < 95) then v_final_rating:= 'Yellow';
else 'Red';
end if;
return v_final_rating;
--exception when others then raise_application_error(-20001, 'An error was encountered');
end calc_rating;
In Apex, I have a Report where I've created a button, and I want to call the above function on that button click, please see screenshot below:
I'm calling the function at the Execute PL/SQL code as shown in screenshot below:-
When I call the function in the above way, it is not recognized and I get an error that calc_rating needs to be declared.
Do I need to create function within Apex or is there a way to call the DB function within this report to be executed on button click.
Thanks for your help in advance!