1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Registering a function in discoverer

Discussion in 'Oracle Discoverer' started by vamsioracle, Jul 23, 2009.

  1. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    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
    Code (SQL):

    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
     
  2. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    Hello Vamsi

    I see a few problems here

    1. In your select statement , you have not selected the PG.NAME into a variable. It should be,
    Code (SQL):

    SELECT pg.NAME
         INTO abc
         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);
    2. You have not used Exceptions. You should at least use WHEN NO_DATA_FOUND and WHEN OTHERS. I however suggest you use a variable and set it to different values in each of your if elsif statements and check in WHEN OTHERS.

    3. Another thing when running it in TOAD you should imitate your discoverer connection context. When running the function call the APPS_INITIALIZE procedure first.
    Code (SQL):

    BEGIN
    fnd_global.APPS_INITIALIZE(user_id =>, resp_id =>, resp_appl_id => , security_group_id => );
    END
    ;
    Than on the same session run the call for the function :

    Code (SQL):
    SELECT fnc_get_employee_emoluments (person_id) FROM dual;
    Try these and see if you can nail down the issue.
     
  3. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    I tried this and its working now.


    Thanks a lot
     
  4. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    You are welcome...