По заданию нужно найти список сотрудников с определенными полями, с этим проблем нет, но вот поле дата изменения должности вызывает проблему
select SLRDepartmentapi.GetHeadLIne(SLRDepartment.Id, 'FULL') as sDepartment, -- подразделение
substr(trim(Slrcard.sTabNum),length(SLRCard.sTabNum)-5,6) as sTabNum, --таб. номер
SLRKadr.Ssecondname||' '||SLRKadr.Sfirstname||' '||SLRKadr.Sthirdname as sKadr, --ФИО
lower(SLRDecode('lSex', SLRKadr.lSex, 0)), -- пол
SLRKadr.Dbirthdate as sBirthdte, --дата рождения
USRSLRRep_Utils.GetAge(SLRKADR.Dbirthdate,sysdate,2)as sAge, --возраст
slrrep_utils.GetStaffPos(slrcard.id, sysdate) sStaff, --должность
CardIn.dBegin as sDateEdit, --дата изменения должности

from SLRCard, SLRCardAccept, SLRCategory, SLRCardType, SLRStaffPos, SLRDepartment, SLRGraphic, SLRCardGraphic, SLRKadr,
(select MoveIn.idCard, MoveIn.idDepartment, MoveIn.idStaffPos, MoveIn.idCategory,MoveIn.dBegin,
SLRFnc.GetIdCardAcceptExt(MoveIn.idCard, MoveIn.dBegin) as idCardAccept,
SLRFnc.GetIdCardGraphicExt(MoveIn.idCard, sysdate) as idCardGraphic
from
(select SLRMove.idCard, SLRMove.idDepartment, SLRMove.idStaffPos, SLRMove.idCategory, SLRMove.dBegin
from SLRMovement SLRMove,
---дата изменения должности
(select idCard, Max(dBegin) as dBegin
from SLRMovement
where sysdate between SLRMovement.dBegin and nvl(SLRMovement.dEnd, sysdate)
and SLRMovement.idFactory = 1
group by idCard
) CurMove,
(select SLRMovement.idCard, Max(SLRMovement.dBegin) as dBegin
from SLRMovement
-->>USR
,SLRCardAccept ca
--<<USR
where SLRMovement.dBegin <= sysdate
and SLRMovement.idFactory = 1
-->>USR если не уволен то не показываем работающим там где его давно нет
and SLRMovement.Idcard=ca.Idcard and ca.Ddismissed=SLRMovement.Dend
--<<USR
group by SLRMovement.idCard
) PrevMove,
(select idCard, min(dBegin) as dBegin
from SLRMovement
where SLRMovement.dBegin > sysdate
and SLRMovement.idFactory = 1
group by idCard
) NextMove,
(select SLRDepartment.Id, SLRDepartment.sNm
from SLRDepartment
connect by prior SLRDepartment.Id = SLRDepartment.IdParent
start with SLRDepartment.Id = 2
) Deps
where SLRMove.idCard = CurMove.idCard(+)
and SLRMove.idCard = PrevMove.idCard(+)
and SLRMove.idCard = NextMove.idCard(+)
and SLRMove.dBegin = nvl(CurMove.dBegin, nvl(NextMove.dBegin, PrevMove.dBegin))
and SLRMove.idFactory = 1
and SLRMove.idDepartment = Deps.id
) MoveIn
) CardIn,
(select distinct SLRChangeFIO.IdKadr
from SLRChangeFIO
where upper(SLRChangeFIO.sSecondName) like '%%'
and upper(SLRChangeFIO.sFirstName) like '%%'
and nvl(upper(SLRChangeFIO.sThirdName), '~') like '%%'
) Fio
where CardIn.idCard = SLRCard.id
and CardIn.idDepartment = SLRDepartment.id
and CardIn.idCategory = SLRCategory.id
and CardIn.idStaffPos = SLRStaffPos.id
and CardIn.idCardAccept = SLRCardAccept.id
and SLRCardAccept.idCardType = SLRCardType.id
and CardIn.idCardGraphic = SLRCardGraphic.id
and SLRCardGraphic.idGraphic = SLRGraphic.id
and SLRCard.idKadr = SLRKadr.id
and SLRCard.sTabNum like '%%'
and (('ALL' = 'ALL') or (SLRKadr.lSex = 'ALL')) -- Пол
and ((1 = 0) or
-- по ФИО
and SLRCard.idKadr = FIO.idKadr

Сам код написан с датой изменения должности, но выводит он последнее перемещение по должности, а должен я так думаю именно ту дату на которую стоит назначенная должность
Вот код который выводит ту саму дату изменения должности
select f1.idm,f1.idprof,f2.dbegm from
(
select m.idcard idm,p.idprofession as idprof
from
(select slrmovement.idcard,slrmovement.iddepartment,
slrmovement.idstaffpos,slrmovement.dbegin,slrmovement.dend
from slrmovement,
(select idcard,
max(dbegin) as dmax from slrmovement
where (dbegin <='15.07.2015'
and (dend is null or dend >='15.07.2015'))
group by idcard) ffsp

where ffsp.idcard=slrmovement.idcard
and ffsp.idcard=16
and slrmovement.dbegin=ffsp.dmax ) m,slrstaffpos p

where
m.idcard=16 and
m.idstaffpos=p.id ) f1,

(
select m.idcard idm,m.dbegin dbegm,p.idprofession as idprof
from
(select slrmovement.idcard,slrmovement.iddepartment,
slrmovement.idstaffpos,slrmovement.dbegin,slrmovement.dend
from slrmovement,
(select m.idcard, f.id,
min(m.dbegin) as dmin from slrmovement m, slrstaffpos p, slrprofession f
where (m.dbegin <=SLRUtils.LastDate('15.07.2015')) and
m.idstaffpos=p.id and p.idprofession=f.id and m.idcard=16
group by m.idcard,f.id) ffsp

where ffsp.idcard=slrmovement.idcard
and ffsp.idcard=1695186
and slrmovement.dbegin=ffsp.dmin ) m,slrstaffpos p

where
m.idcard=16 and
m.idstaffpos=p.id) f2
where f1.idm=f2.idm and f1.idprof=f2.idprof
Помогите пожалуйста разобраться почему не работает этот код как функция

function GetDataStaffposCard(p_idCard SLRCard.id%type
,p_Date date
) return varchar2

is
/* vRezId Slrmovement.Id%rowtype
vRezProf Slrmovement.Idprofession%rowtype*/
vResult date;
begin
vResult:= SLRUtils.LastDate(p_Date);

Select /*f1.idm,f1.idprof,*/f2.dbegm
into vResult
from
(
select m.idcard idm,
p.idprofession as idprof
from
(select slrmovement.idcard,
slrmovement.iddepartment,
slrmovement.idstaffpos,
slrmovement.dbegin,
slrmovement.dend
from slrmovement,
(select idcard,
max(dbegin) as dmax
from slrmovement
where (dbegin <=p_Date
and (dend is null or dend >=p_Date))
group by idcard) ffsp

where ffsp.idcard=slrmovement.idcard
and ffsp.idcard=p_idCard
and slrmovement.dbegin=ffsp.dmax ) m,slrstaffpos p
where m.idcard=p_idCard
and m.idstaffpos=p.id ) f1,

(
select m.idcard idm,m.dbegin dbegm,p.idprofession as idprof
from
(select slrmovement.idcard,
slrmovement.iddepartment,
slrmovement.idstaffpos,
slrmovement.dbegin,
slrmovement.dend
from slrmovement,
(select m.idcard,
f.id,
min(m.dbegin) as dmin
from slrmovement m,
slrstaffpos p,
slrprofession f
where (m.dbegin <=SLRUtils.LastDate(p_Date))
and m.idstaffpos=p.id
and p.idprofession=f.id
and m.idcard=p_idCard
group by m.idcard,
f.id) ffsp

where ffsp.idcard=slrmovement.idcard
and ffsp.idcard=p_idCard
and slrmovement.dbegin=ffsp.dmin ) m,slrstaffpos p

where m.idcard=p_idCard
and m.idstaffpos=p.id) f2
where f1.idm=f2.idm
and f1.idprof=f2.idprof;
return vResult;
end;