registering a function in discoverer

[vamsioracle]

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