/* Script to create funciton and View to select "Best" Eo rank for a given element */
/* created by eml for KSNPC July 2003 */

/* CREATE FUNCTION "BEST_EO_RANK" */

-- user-defined function, written by eml 7-8-2003 for Kentucky NHP --
-- designed to retrieve highest EORANK for a given element subnational ID value --
-- treats EOs ranked "E" as better than those ranked "C*" OR "D*" -- 

CREATE OR REPLACE FUNCTION GET_BEST_EORANK(est_id NUMBER)
  RETURN VARCHAR2
  IS
    d_id NUMBER;
	rank varchar2(2);
	e_rank NUMBER;
	this_rank NUMBER;
    CURSOR rank_id IS 
	   SELECT d_basic_eo_rank_id 
	   from eo 
	   where element_subnational_id = est_id
	   and d_basic_eo_rank_id is not null;
BEGIN
--Find out if there is an E Rank anywhere in the list--
    e_rank := 0;
  	BEGIN
	FOR record in rank_id LOOP 
	   FETCH rank_id into this_rank;
	   EXIT when rank_id%NOTFOUND;
	    BEGIN
			IF this_rank = 14 THEN
			   e_rank := 1;
			ELSE 
			   e_rank := e_rank;
			END IF;
		END;
	 END LOOP;
	 END;
	
--Collect the lowest rank id for the element--
      BEGIN
	    SELECT min(d_basic_eo_rank_id)
	    INTO d_id
   	    FROM eo
	    WHERE element_subnational_id = est_id;
      END;
	  
--Rank of E is better than C or D--
       BEGIN
    	IF d_id < 14 AND d_id > 8 AND e_rank = 1 Then
		   d_id := 14;
		ELSE
		   d_id := d_id;
	    END IF;  
	   END;
	   
--Use ID to retrieve EO Rank Code--
      BEGIN
       SELECT basic_eo_rank_cd INTO rank FROM d_basic_eo_rank WHERE d_basic_eo_rank_id = d_id;
      END;
  
  RETURN rank;

  EXCEPTION
    WHEN NO_DATA_FOUND Then
      RETURN null;
    WHEN OTHERS THEN
      RETURN 'ERROR!';

END;
/


/* CREATE VIEW BEST_EO_RANK */

create or replace view BEST_EO_RANK (est_id, elcode, g_rank, best_eorank)
AS 
select a.element_subnational_id,
	   b.elcode, 
	   b.g_rank,
	   substr((get_best_eorank(a.element_subnational_id)),1,2)
	   FROM element_subnational a,
	   bcd_et b
where a.element_subnational_id = b.element_subnational_id;

grant select on best_eo_rank to biotics_report;

commit;