SQL & PL/SQL

Hello,
I have defined a sys_refcursor in my procedure. Basically i dont want to give sys_refcusor instead i have to define a type and use here. Can someone tell me how do i replace sys_refcursor and use a type here? what is the alternate changes i can do instead of using sys_refcusor.


PROCEDURE p_merge_employee_info(p_schema_name in VARCHAR2,P_COUNTRY_CODE IN NUMBER) IS


generic_cv sys_refcursor;
generic_cv1 sys_refcursor;


v_e_no NUMBER(7);
v_first_name VARCHAR2(20);
v_last_name VARCHAR2(20);
v_soc_sec_no VARCHAR2(14);
v_es_status NUMBER(2);
v_address1 VARCHAR2(40);
v_address2 VARCHAR2(40);
v_gd_state VARCHAR2(2);
v_gd_county VARCHAR2(4);
v_gd_city VARCHAR2(6);
v_zip_code VARCHAR2(10);
v_phone VARCHAR2(10);
v_day_phone VARCHAR2(10);
v_birth_date DATE;
v_gender VARCHAR2(1);
v_as_no NUMBER(4);
v_user_id NUMBER(10);
v_district_id NUMBER(10);
v_territory_id NUMBER(10);

o_error_Text VARCHAR2(1000);
v_sql_err VARCHAR2(4000);
tname VARCHAR2(32);
o_status VARCHAR2(10);
v_schema_name VARCHAR2(100):=p_schema_name||'.';
v_last_upd_by VARCHAR2(20) :='SYSTEM';
v_created_by VARCHAR2(20) :='SYSTEM';
v_user_check_id NUMBER(10);
v_hire_date DATE;
v_fjc_no VARCHAR2(1);
v_sql VARCHAR2(4000);
v_emp_check NUMBER(1) := 0;

v_ejc_date_last_worked DATE;




BEGIN

OPEN generic_cv FOR 'SELECT
e_no ,
e_first_name ,
e_last_name ,
e_soc_sec_no ,
es_status ,
e_address1 ,
e_address2 ,
gd_state ,
gd_county ,
gd_city ,
e_zip_code ,
e_phone ,
e_day_phone ,
e_birth_date ,
g_gender ,
as_no ,
e_hire_date

FROM ' || p_schema_name || '.employee';


LOOP

o_Error_Text := 'ORA-Error in fetching the cursor values ';
tname:= NULL;

FETCH generic_cv INTO
v_e_no ,
v_first_name ,
v_last_name ,
v_soc_sec_no ,
v_es_status ,
v_address1 ,
v_address2 ,
v_gd_state ,
v_gd_county ,
v_gd_city ,
v_zip_code ,
v_phone ,
v_day_phone ,
v_birth_date ,
v_gender ,
v_as_no ,
v_hire_date ;


EXIT WHEN generic_cv%NOTFOUND;

----
BEGIN

o_Error_Text := 'ORA-Error in executing district table';
tname:= 'district';
---
----
--- etc etc etc