select distinct (category) categorynum, o.cnname categoryname
from ycoms_askforleave s, s_dic o
where s.approve_status = '997'
and s.actorno = '004685'
and s.category = o.enname
and o.opttype = 'AFL_LEAVESTATE'
and s.askforleaveid in (select oo.askforleaveid
from ycoms_afl_detailed oo
where oo.cancelleaveid is null
group by oo.askforleaveid)
--and to_char(to_date(s.edittime,'yyy-mm-dd'), 'yyyy-mm-dd')
and substr(ltrim(s.creattime), 0, 10) >
--to_char(to_date(
(select t.attendendd
from (select max(attendendd) as attendendd
from ycoms_attend_date_hissta
where state in ('2', '1')
order by attendendd asc) t) --,'yyyy-mm-dd'),'yyyy-mm-dd')
and s.category = (case
when s.category = '01' and
'02' in (select ta.category
from ycoms_askforleave ta
where ta.approve_status = '997'
and ta.actorno = '004685') and
(select count(x.cancelleaveid)
from ycoms_afl_detailed x
left join ycoms_askforleave cc
on x.askforleaveid = cc.askforleaveid
where cc.category = '02'
and cc.approve_status = '997'
and cc.actorno = '004685') in
(select count(*)
from ycoms_afl_detailed x
left join ycoms_askforleave cc
on x.askforleaveid = cc.askforleaveid
where cc.approve_status = '997'
and cc.actorno = '004685'
and cc.category = '02') then
'01'
when s.category = '01' and
'02' in (select ta.category
from ycoms_askforleave ta
where ta.approve_status = '997'
and ta.actorno = '004685') and
(select count(x.cancelleaveid)
from ycoms_afl_detailed x
left join ycoms_askforleave cc
on x.askforleaveid = cc.askforleaveid
where cc.category = '02'
and cc.approve_status = '997'
and cc.actorno = '004685') not in
(select count(*)
from ycoms_afl_detailed x
left join ycoms_askforleave cc
on x.askforleaveid = cc.askforleaveid
where cc.approve_status = '997'
and cc.actorno = '004685'
and cc.category = '02') then
''
else
s.category
end)
from ycoms_askforleave s, s_dic o
where s.approve_status = '997'
and s.actorno = '004685'
and s.category = o.enname
and o.opttype = 'AFL_LEAVESTATE'
and s.askforleaveid in (select oo.askforleaveid
from ycoms_afl_detailed oo
where oo.cancelleaveid is null
group by oo.askforleaveid)
--and to_char(to_date(s.edittime,'yyy-mm-dd'), 'yyyy-mm-dd')
and substr(ltrim(s.creattime), 0, 10) >
--to_char(to_date(
(select t.attendendd
from (select max(attendendd) as attendendd
from ycoms_attend_date_hissta
where state in ('2', '1')
order by attendendd asc) t) --,'yyyy-mm-dd'),'yyyy-mm-dd')
and s.category = (case
when s.category = '01' and
'02' in (select ta.category
from ycoms_askforleave ta
where ta.approve_status = '997'
and ta.actorno = '004685') and
(select count(x.cancelleaveid)
from ycoms_afl_detailed x
left join ycoms_askforleave cc
on x.askforleaveid = cc.askforleaveid
where cc.category = '02'
and cc.approve_status = '997'
and cc.actorno = '004685') in
(select count(*)
from ycoms_afl_detailed x
left join ycoms_askforleave cc
on x.askforleaveid = cc.askforleaveid
where cc.approve_status = '997'
and cc.actorno = '004685'
and cc.category = '02') then
'01'
when s.category = '01' and
'02' in (select ta.category
from ycoms_askforleave ta
where ta.approve_status = '997'
and ta.actorno = '004685') and
(select count(x.cancelleaveid)
from ycoms_afl_detailed x
left join ycoms_askforleave cc
on x.askforleaveid = cc.askforleaveid
where cc.category = '02'
and cc.approve_status = '997'
and cc.actorno = '004685') not in
(select count(*)
from ycoms_afl_detailed x
left join ycoms_askforleave cc
on x.askforleaveid = cc.askforleaveid
where cc.approve_status = '997'
and cc.actorno = '004685'
and cc.category = '02') then
''
else
s.category
end)