Schema HADES_ORG in Database DB-HADES

select T1.first_name,t1.last_name,phone_fax(T3.phone_prefix,t1.phone) as phone,
phone_fax(T3.phone_prefix,t1.fax) as fax,t1.e_mail,
T3.name as department,t4.name as institute,T4.country,NVL(T3.town,T4.town) as town
from people T1, Group_people T2, Departments T3, Institutions T4
where T2.group_id='CB' and T1.people_id=t2.people_id and t3.dept_id=T1.dept_id
and T4.inst_id=T3.inst_id

select T1.dept_id,T1.name,T1.phone_prefix,T1.homepage,
NVL(T2.inst_id,T1.inst_id) as INST_ID,T2.task,T2.homepage as group_homepage
from departments T1, hades_depts T2 where T1.dept_id(+)=T2.dept_id

select distinct T1.inst_id,T1.name,T1.country,T1.zip_code,T1.town, T1.ADDRESS,T1.homepage
from institutions T1, hades_depts T2
where T1.inst_id=T2.inst_id and (T2.expire_date is Null or T2.expire_date>Sysdate)

Select
T1.ENTRY_DATE, T1.EXPIRE_DATE, T1.TASK, T1.PROJECT, T1.PROJECT2,T1.TYPE_ID,
T2.FIRST_NAME, T2.LAST_NAME, T2.TITLE,T2.PHONE,T2.FAX, T2.E_MAIL,
T2.HOMEPAGE,T2.INST_ID,T2.DEPT_ID,T1.PEOPLE_ID,T2.HAS_PIC
from HADES T1, PEOPLE T2
where T1.PEOPLE_ID= T2.PEOPLE_ID

select first_name, last_name, inst_id
  from hades_org.hades_all_people
  where ( expire_date is null or expire_date > sysdate or type_id = 'F' )
    and ( entry_date is null or entry_date < sysdate or type_id = 'F' )
    and ( type_id is null or type_id = 'F' )

Select Distinct COUNTRY, T1.INST_ID, NAME, ZIP_CODE||' '||TOWN as TOWN, ADDRESS
from INSTITUTIONS T1,HADES_DEPTS T2
where T1.inst_id=T2.inst_id and (T2.expire_date is Null or T2.expire_date>Sysdate)

select distinct t2.inst_id,
                t2.name,
                nvl(t3.country,t2.country),
                nvl(t3.town,t2.town)
  from hades_org.hades t4,
       hades_org.people t1,
       hades_org.institutions t2,
       hades_org.departments t3
  where ( t4.expire_date is null or t4.expire_date > sysdate or t4.type_id = 'F' )
    and ( t4.entry_date is null or t4.entry_date < sysdate or t4.type_id = 'F' )
    and ( t4.type_id is null or t4.type_id = 'F' )
    and t4.people_id = t1.people_id and t2.inst_id = t1.inst_id and t3.dept_id(+) = t1.dept_id

Select
T1.FIRST_NAME, T1.LAST_NAME,T1.PEOPLE_ID,
PHONE_FAX(T3.PHONE_PREFIX,T1.PHONE) AS PHONE, PHONE_FAX(T3.PHONE_PREFIX,T1.FAX) AS FAX, 
T1.E_MAIL,T1.HOMEPAGE,T1.HAS_PIC,T4.ENTRY_DATE,
T4.EXPIRE_DATE, T4.PROJECT,T4.PROJECT2,T4.Type_id,
T2.NAME as institution, T2.INST_ID,T3.name as department,T3.dept_id,
NVL(T3.COUNTRY,T2.COUNTRY) AS COUNTRY,
NVL(T3.ZIP_CODE,T2.ZIP_CODE) AS ZIP_CODE,
NVL(T3.TOWN,T2.TOWN) AS TOWN,
NVL(T3.ADDRESS,T2.ADDRESS) as ADDRESS
from PEOPLE T1, INSTITUTIONS T2, DEPARTMENTS T3, HADES T4
where ( T2.INST_ID = T1.INST_ID And T3.dept_id(+)=T1.dept_id And T4.PEOPLE_ID = T1.PEOPLE_ID )

Select
to_char(ENTRY_DATE, 'DD-MON-YYYY') as entry_date,
to_char( EXPIRE_DATE, 'DD-MON-YYYY') as expire_date,
PEOPLE_ID, PROJECT, PROJECT2, TASK
from HADES

function hades_e_mail_list Return VARCHAR2 is
CURSOR dataset IS 
Select T1.E_MAIL
from PEOPLE T1, HADES T2
where T1.people_id=T2.people_id and
(T2.EXPIRE_DATE is NULL OR T2.EXPIRE_DATE>SYSDATE)
and t1.E_MAIL is not NULL
order by T1.LAST_NAME;
name_list Varchar2(8000) :=NULL;
separator Varchar(2) :='';
BEGIN
FOR row IN dataset LOOP
name_list := name_list||separator||row.e_mail;
separator := '; ';
END LOOP;
return name_list;
END hades_e_mail_list;

function people_count( INSTITUTION_ID IN VARCHAR2) Return NUMBER is
name_count NUMBER(4) :=0;
BEGIN
Select Count(1) into name_count from PEOPLE T1, HADES T2
where T1.inst_id = INSTITUTION_ID and T1.people_id=T2.people_id and
(T2.EXPIRE_DATE is NULL OR T2.EXPIRE_DATE>SYSDATE);
return name_count;
END people_count;

function people_list( INSTITUTION_ID IN VARCHAR2) Return VARCHAR2 is
CURSOR dataset IS 
Select Substr(T1.FIRST_NAME,1,1) ||'. '||T1.LAST_NAME as name, T1.Phone,T1.E_MAIL
from PEOPLE T1, HADES T2
where T1.inst_id = INSTITUTION_ID and T1.people_id=T2.people_id and
(T2.EXPIRE_DATE is NULL OR T2.EXPIRE_DATE>SYSDATE)
order by T1.LAST_NAME;
name_list Varchar2(8000) :=NULL;
seperator Varchar(2) :='';
BEGIN
FOR row IN dataset LOOP
name_list := name_list||seperator||'<a href="mailto: '||row.e_mail||'">'||row.name||'</a>';
seperator := ', ';
END LOOP;
return name_list;
END people_list;

function phone_fax( prefix IN Varchar2, phone IN Varchar2) return Varchar2
is Begin
IF prefix is NULL THEN return phone; 
ELSE IF phone is NULL THEN return NULL; END IF;
END IF;
return prefix||phone;
END phone_fax;

function
project_list(Project_1 IN VARCHAR2, Project_2 IN Varchar2) Return VARCHAR2 is
name_list Varchar(60) :=NULL;
BEGIN
IF Project_1 IS NOT NULL THEN name_list := Project_1;
END IF;
IF Project_2 IS NOT NULL THEN name_list := name_list||', '||Project_2;
END IF;
return name_list;
END project_list;

function proj_people_list(selected_project IN VARCHAR2) Return Varchar2 is
CURSOR dataset IS
Select T1.FIRST_NAME||' '||T1.LAST_NAME as name,T1.PHONE, T1.E_Mail,T3.NAME as INSTITUTE
from PEOPLE T1, HADES T2, INSTITUTIONS T3
where (T2.project = selected_project or T2.project2=selected_project)
and T1.people_id=T2.people_id and T1.INST_ID=T3.INST_ID
and (T2.EXPIRE_DATE is NULL OR T2.EXPIRE_DATE>SYSDATE)
order by T3.NAME,T1.LAST_NAME;
name_list Varchar2(16000) :=NULL;
seperator Varchar2(6) :='';
last_institute institutions.name%type:=' ';
BEGIN
FOR row IN dataset LOOP
IF last_institute != row.institute THEN
last_institute := row.institute;
name_list :=name_list||seperator||'<b><font color=blue>'||last_institute||'</font></b>';
seperator := '<br>';
END IF;
name_list := name_list||seperator||'<b>'||row.name||'</b>, '||row.phone||', '||row.e_mail;
END LOOP;
return name_list;
END proj_people_list;