SQL & PL/SQL

First off I'd like to apologise for my lack of PL/SQL experience, what follows may seem naive, but I guess we all have to start somewhere.

I'm trying to create a function which determines an employee's Annual Holiday Entitlement by comparing their length of service against a lookup of entitlement values. Due to the complexity of the application I'm targeting there are a number of other factors to be take into consideration, but this is the basic idea of what I want to achieve.

I've started by taking a working SQL Server 2005 function and converted this using the translation scratch pad in SQL Developer 1.5.1 I'm now hand cranking the subtleties into the script but when I debug the code I'm getting "ORA-01403: no data found" at almost every SELECT. In fact the only successful select is from the ABS_SYSTEM_CONTROLS table. I can handle the first few failures by setting default values in the variables that I'm trying to load, but this isn't an option when it comes to selecting the DATE_OF_START from the PERSON table.

I've sure I've probably made some fundamental error, but can't spot it myself. Any help would be most welcome.

I've included the entire script below for your entertainment ;-)

Thanks

Phil.

create or replace
function mygetannualholidayentitlement
( v_empNo in varchar2
, v_inputDate in DATE
) return number as
   -- Declare variables here
   v_entitlement FLOAT(53);
   v_holidayPlan VARCHAR2(20);
   v_contServiceOption VARCHAR2(255);
   v_postNo CHAR(16);
   v_lengthOfService NUMBER(10,2) ; -- Need to give this a default value else the CURSOR (below) won't initialise.
   v_empStartDate DATE;
   -- Load up our loacal variable for each row in the loop and the final value is the one we want.
   v_selectedLOF FLOAT(53);
   v_selectedDateEffective DATE;
   v_selectedAmount FLOAT(53);
   v_effectiveDate DATE;
   
   -- Cursor declaration
   -- Holiday plan details are also keyed on DATE_EFFECTIVE.  Need to identify which row is active as at our @effectiveDate.
   
   CURSOR holDefCursor
     IS SELECT length_of_service,
   date_effective,
   amount
     FROM holiday_plan_def
      WHERE hplan_code = v_holidayPlan
     AND length_of_service < v_lengthOfService
     ORDER BY date_effective ASC,
   length_of_service ASC;

BEGIN
   -- Initialise variables --
   -- truncate any hours/Mins & secs from effective date
   v_effectiveDate := trunc(v_inputdate);
   v_entitlement := 0;
   v_contServiceOption := 0;
   v_lengthOfService := 0;

   BEGIN
   SELECT HPLAN_CODE
     INTO v_holidayPlan
     FROM APPT_HOLIDAY_BALANCE
      WHERE emp_no = v_empNo
              --          post_no = @postNo and
              AND ( v_effectiveDate BETWEEN HOLIDAY_YEAR_START AND HOLIDAY_YEAR_END );
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
      v_holidayplan:=NULL;
   WHEN OTHERS THEN
       raise_application_error(-20011,'Unknown Exception in MyGetAnnualHolidayEntitlement function.');
   END;

   -- Still no hoildayPlan? Then use the default plan code from ABS_SYSTEM_CONTROLS.
   IF v_holidayPlan IS NULL THEN
      SELECT HPLAN_CODE
        INTO v_holidayPlan
        FROM ABS_SYSTEM_CONTROLS ;
   END IF;

   BEGIN
    SELECT OPTION_VALUE
      INTO v_contServiceOption
      FROM PS_OPTIONS
       WHERE OPTION_NAME = 'CONTINUOUS_SERVICE_OPTION';
   EXCEPTION 
   WHEN NO_DATA_FOUND THEN
      v_contServiceOption := 'N' ;
   WHEN OTHERS THEN
       raise_application_error(-20011,'Unknown Exception in MyGetAnnualHolidayEntitlement function.');
   END;
   
   IF v_contServiceOption = 'Y' THEN
   BEGIN
      -- Need to take into account the employees CONTINUOUS_SERVICE_DATE when calculating length of service
      BEGIN
      SELECT CONTINUOUS_SERVICE_DATE
        INTO v_empStartDate
        FROM person
         WHERE emp_no = v_empNo;
      EXCEPTION   
        WHEN NO_DATA_FOUND THEN
          v_empStartDate := NULL;
        WHEN OTHERS THEN
          raise_application_error(-20011,'Unknown Exception in MyGetAnnualHolidayEntitlement function.');
      END;
      -- If we can't get a CONTINUOUS_SERVICE_DATE we will fall back to PERSON.DATE_OF_START
      IF v_empStartDate IS NULL THEN
      BEGIN
         SELECT DATE_OF_START
           INTO v_empStartDate
           FROM PERSON
            WHERE emp_no = v_empNo;
      EXCEPTION   
        WHEN OTHERS THEN
          raise_application_error(-20011,'Unknown Exception in MyGetAnnualHolidayEntitlement function.');
      END;
      END IF;
   END;
   ELSE
   BEGIN
      -- Need to use employees DATE_OF_START when calculating length of service
      SELECT DATE_OF_START
        INTO v_empStartDate
        FROM PERSON
         WHERE emp_no = v_empNo;

   END;
   END IF;

   -- Now we can get length of service
   v_lengthOfService := sqlserver_utilities.datediff('MONTH', v_empStartDate, v_effectiveDate) / 12.00;

   OPEN holDefCursor;
   
   FETCH holDefCursor INTO v_selectedLOF,v_selectedDateEffective,v_selectedAmount;
   
   WHILE ( sqlserver_utilities.fetch_status(holDefCursor%FOUND) = 0 )
   LOOP
      BEGIN
         IF v_selectedDateEffective < v_effectiveDate THEN
            v_entitlement := v_selectedAmount;
   
         END IF;
   
         -- Get the next value.
         FETCH holDefCursor INTO v_selectedLOF,v_selectedDateEffective,v_selectedAmount;
   
      END;
   END LOOP;
   
   CLOSE holDefCursor;

   -- Return the result of the function
   RETURN v_entitlement;

END;

Edited by: user4395499 on 27-Oct-2008 04:04

Edited by: user4395499 on 27-Oct-2008 04:05

Edited by: user4395499 on 27-Oct-2008 07:10