create or replace view km_todotasklist as
select "ID","DEVICE_TYPE","SYSTEM_TYPE","TASK_CODE","TASK_TYPE1","TASK_TYPE2","TITLE","STATUS","OWNER_ID","OWNER_NAME","PRIORITY","CREATE_ID","CREATE_TIME","UPDATE_ID","UPDATE_TIME" from （
select t.id_ as id,
CASE
WHEN (select t.cfg_value from SYS_OT_CONFIG t where t.code ='SERVER_H5_MODELID' and INSTR( t.cfg_value ,a.module_id)>0 ) is null THEN
'PC,APP'
WHEN (select t.cfg_value from SYS_OT_CONFIG t where t.code ='SERVER_H5_MODELID' and INSTR( t.cfg_value ,a.module_id)>0 ) is not null THEN
'PC'
ELSE
(select t.cfg_value from SYS_OT_CONFIG t where t.code ='SERVER_H5_MODELID' and INSTR( t.cfg_value ,a.module_id)>0 )
END as device_type,
'KM' as system_type,
'核心业务URL’||utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw((select to_char(t.next_user_id_)
from OA_OT_PROCESS_RECORD t
where t.next_task_id_ = t.id_ and rownum=1)))) as task_code,
'代办' as task_type1,
to_char(mo.name) as task_type2,
to_char(a.title) as title,
'0' as status,
(select to_char(t.next_user_id_)
from OA_OT_PROCESS_RECORD t
where t.next_task_id_ = t.id_ and rownum=1) as owner_id,
(select em.real_name
from IMS_OT_USER t
inner join IMS_OT_EMPLOYEE em
on t.employee_id = em.id
where t.user_login_name =
(select t.next_user_id_
from OA_OT_PROCESS_RECORD t
where t.next_task_id_ = t.id_ and rownum=1) and rownum=1) as owner_name,
'' as priority,
a.creator as create_id,
a.create_time as create_time,
'' as update_id,
'' as update_time
from oa_ot_application a
inner join act_ru_task t
on t.proc_inst_id_ = a.pro_inst_id and t.task_def_key_ <> 'usertask2'
inner join oa_ot_application_ext et
on et.app_id = a.id and et.business_type ='1'
inner join OA_OT_APP_MODULE mo on a.module_id =mo.id
UNION ALL
select t.id as id,
'PC' as device_type,
'KM' as system_type,
'http://10.XX.XXX.XXX'||(select t.oa_req_url from OA_OT_APP_MODULE t where t.id =a.module_id)||'/input.htm?taskId='||t.id||'&appId='||a.id||'&sid='|| utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(to_char(t.user_id)))) as task_code,
'待阅' as task_type1,
to_char(mo.name) as task_type2,
to_char(a.title) as title,
'0' as status,
to_char(t.user_id) as owner_id,
(select em.real_name
from IMS_OT_USER t
inner join IMS_OT_EMPLOYEE em
on t.employee_id = em.id
where t.user_login_name = t.user_id and rownum=1) as owner_name,
'' as priority,
a.creator as create_id,
a.create_time as create_time,
'' as update_id,
'' as update_time
from oa_ot_application a
inner join oa_ru_circulation t
on t.proc_inst_id = a.pro_inst_id
inner join oa_ot_application_ext et
on et.app_id = a.id
inner join OA_OT_APP_MODULE mo on a.module_id =mo.id
） t order by t.create_time desc;
| 
