it is done. Thanks all.
Here is my code :
--------------
select facname
, facsch
, initcap(lastname) lastname
, initcap(replace(firstname,' ','')) firstname
, initcap(firstname) dspfirstname
, homefac
, facspot
, facid
, factype
from (
select genbid_assignmentinfo.facname
, genbid_assignmentinfo.facsch
, initcap(genbid_bidresult.lastname) lastname
, initcap(replace(genbid_bidresult.firstname,' ',''))
firstname
, initcap(genbid_bidresult.firstname) dspfirstname
, genbid_bidresult.homefac
, genbid_assignmentinfo.facspot
, genbid_assignmentinfo.facid
, genbid_assignmentinfo.factype
from genbid_assignmentinfo
left
join genbid_bidresult
on genbid_bidresult.facsch = genbid_assignmentinfo.facsch
and genbid_bidresult.facname = genbid_assignmentinfo.facname
and genbid_assignmentinfo.factype='SGT' and
genbid_bidresult.factype='SGT'
and extract( year from genbid_bidresult.facdate) = extract(
year from sysdate)
and genbid_bidresult.isassigned = 'Y' and
genbid_bidresult.istemp = 'N'
)
where factype='SGT'