Hi All,
I have a function that return some static value, based on the grade of a person. It takes the person id as the input.
This is my code
-
create or replace function fnc_get_employee_emoluments (person_id number) return NUMBER as
-
employee_grade varchar2(30);
-
first_position number; -- first occurrence of '.' in the grade
-
last_position number; -- second occurrence of '.' in the grade
-
string_length number;
-
emoluments number := 0;
-
begin
-
SELECT PG.NAME
-
FROM HR.PER_ALL_ASSIGNMENTS_F PAAF, hr.PER_GRADES PG
-
where PAAF.PERSON_ID = 1665
-
and PAAF.GRADE_ID = PG.GRADE_ID
-
and PAAF.effective_end_date =
-
(select max(PAAF1.effective_end_date)
-
from hr.per_all_assignments_f PAAF1
-
where PAAF1.PERSON_ID = PAAF.person_id);
-
first_position := instr(employee_grade,'.',1,1);
-
last_position := instr(employee_grade,'.',1,2);
-
string_length := last_position - (first_position + 1);
-
employee_grade := substr(employee_grade,first_position + 1,string_length);
-
IF upper(employee_grade) = 'IZ1' THEN
-
emoluments := 668;
-
ELSIF upper(employee_grade) = 'IZ2' THEN
-
emoluments := 881;
-
ELSIF upper(employee_grade) = 'IZ3' THEN
-
emoluments := 1404;
-
ELSIF upper(employee_grade) = 'IZ4' THEN
-
emoluments := 1950;
-
ELSIF upper(employee_grade) = 'IZ5' THEN
-
emoluments := 1434 + 954;
-
ELSIF upper(employee_grade) = 'IIZ2' THEN
-
emoluments := 1230 + 1230 + 898;
-
ELSIF upper(employee_grade) = 'IIZ3' THEN
-
emoluments := 1076 + 1186 + 1186 + 1109;
-
ELSIF upper(employee_grade) = 'IIZ4' THEN
-
emoluments := 1215 + 1491 + 1491 + 1368;
-
ELSIF upper(employee_grade) = 'IS3' THEN
-
emoluments := 300 + 641;
-
ELSIF upper(employee_grade) = 'IS4' THEN
-
emoluments := 300 + 695;
-
ELSE
-
emoluments := 0;
-
END IF;
-
return emoluments;
-
end fnc_get_employee_emoluments;
-
Now i compiled this and this runs fine ( returns the correct value), when run in toad.
I registered this in discoverer, and i am using this in calculation item.
It always returns 0. What could be the reason.
I have verified the person id that i am passing in my report. The function return correct value in toad for the same person id but not in my report.
Steps I used to register this function.
1) clicked on register pl/sql function in admin version
2) Clicked on import and searched for this in the available list of functions
3) after adding this, and the parameters, saved the content
In desktop , used the calculation item to retrieve this function.
vamsi
