$sql_assinged ="select t.wo_id, 
user_info_class.func_wo_sub(t.wo_id) as wo_subject,
--func_group_member(t.group_id) as members, 
nvl(user_info_class.user_info(t.action_by_id, 'NAME'), func_group_member(t.group_id)) as waiting,

to_date(t.action_date, 'dd/mm/yy hh12:mi:ss') as executedDate,
w.value as dateOfExecution
from user_path t,tblwo w
where t.wo_id = w.wo_id 
and t.status in ('executed', 'verified')
and upper(w.field) like '%DATE%EXECUTION%' 
and t.action_type in (4, 5)
and (t.wo_id,owner) in 
(
select t.wo_id,owner
from tblreport t
where $DEPT_ID in (dept_from, dept_to) 
) 
and trunc(action_date) >= to_date('$sdate', 'dd/mm/yyyy')
and trunc(action_date) <= to_date('$edate', 'dd/mm/yyyy')
order by action_date ";

Procedure function

CREATE OR REPLACE function WO_NEW.func_group_member(pgroup_id number) return varchar2 is
Result varchar2(2000);

cursor CurMember is
select u.user_name
from group_member t, tbluser u
where t.member_id = u.id
and t.group_id = pgroup_id
order by u.user_name;
recv_value varchar2(1000);

begin

for cur_data in CurMember loop

recv_value := cur_data.user_name;
Result := recv_value||','||Result;
end loop;
return(Result);
end func_group_member;

Packages / Class

USER_INFO_CLASS – Declaration

CREATE OR REPLACE package WO_NEW.user_info_class is
function func_WO_sub(pwo_id number) return varchar2;
function func_WO_DESCP(pwo_id number) return varchar2; 
--function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;
function user_info(puser_id number, infotype varchar2) return varchar2; 
function func_issuer(pwo_id number) return varchar2;
function func_deptID(pgroup_id number) return number;
function func_deptName(pdept_id number) return varchar2;
function func_getComments(pwoid number) return varchar2;


end user_info_class;

USER_INFO_CLASS – Body

CREATE OR REPLACE package body WO_NEW.user_info_class is

function func_issuer(pwo_id number) return varchar2 is
Result varchar2(50);
begin 
select user_name into Result from tbluser where id =(
select action_by_id from user_path t where wo_id=pwo_id and t.action_type=1);
return(Result);
end func_issuer; 

/***************************/

function func_WO_sub(pwo_id number) return varchar2 is
Result varchar2(1000);
begin 
select value into Result from tblwo t where wo_id=pwo_id and upper(t.field) like '%SUBJECT';
return(Result);
end func_WO_sub; 
/***************************/

function func_WO_DESCP(pwo_id number) return varchar2 is
Result varchar2(1500);
begin 
select value into Result from tblwo t where wo_id=pwo_id and upper(t.field) like '%DESCRIP%';
return(Result);
end func_WO_DESCP; 
/***************************/

function user_info(puser_id number, infotype varchar2) return varchar2 is
Result varchar2(1000);
cursor user_date is
select * from tbluser t where id=puser_id;
begin 
for recvINFO in user_date loop
if infotype = 'NAME' then 
return recvINFO.user_name; 
elsif infotype = 'MSISDN' then
return recvINFO.msisdn; 
elsif infotype = 'EMAIL' then
return recvINFO.email; 
end if;
end loop; 

return(Result);
end user_info;
/***************************/


function func_group_member(pgroup_id number) return varchar2 is Result varchar2(2000);

cursor CurMember is
select u.user_name
from group_member t, tbluser u
where t.member_id = u.id
and t.group_id = pgroup_id;

recv_value varchar2(1000);
begin 
for cur_data in CurMember loop 
recv_value := cur_data.user_name;
Result := recv_value||','||Result;
end loop;
return(Result);
end func_group_member; 
/*****************************/
function func_deptID(pgroup_id number) return number is Result number; 
begin
select dept_id into Result from group_member t,tbluser u 
where t.member_id=u.id and group_id=pgroup_id group by u.dept_id;
return(Result);
end func_deptID; 
/*********************************/
function func_deptName(pdept_id number) return varchar2 is Result varchar2(100); 
begin
select dept_name into Result from departments where dept_id=pdept_id;
return(Result);
end func_deptName; 

/**************************/

function func_getComments(pwoid number) return varchar2 is
Result varchar2(2000);
cursor CurMember is

select '<b>'||u.user_name||'</b>-->'||t.comments as comments from comments t,tbluser u 
where t.user_id = u.id and t.wo_id=pwoid; 
recv_value varchar2(1000);
begin
for cur_data in CurMember loop 
recv_value := cur_data.comments;
Result := recv_value||'::'||Result;
end loop;
return(Result); 
end func_getComments;

/*****************************/


end user_info_class;