select * from ( select * from (
(select (case when (instr(','||wm_concat(type)||',',',develop-plan,')>0) then 'develop-plan' else 'unRead' end) type,
max(id) id,name,node_level,project,end,max(created_date) created_date,
center,year_flg,sequence,
min(isRead) isRead,
MyReplace(wm_concat(msgCreateDate),',') msgCreateDate,wm_concat(group_name),max(planId),0 owe_days,0 owe_money
from (select
'develop-plan' as type,
node.node_id as id,
template_node.node_name as name,
template_node.node_level,
project.project_name as project,
node.schedule_end_date as end,
warning.CREATED_DATE as created_date,
'' as center , '' as year_flg, ROW_NUMBER() OVER(PARTITION BY node.templet_id, project.project_project_id ORDER BY warning.created_date ASC) as sequence,
(select min(t.is_read) from plan6_message_user_read t where t.node_id=node.node_id and t.need_reader='baolm1' group by t.node_id) isRead, '' msgCreateDate,info.name group_name,info.biz_cd planId,0 owe_days,0 owe_money from plan6_warning warning,
plan6_node node,
project_distribution info,project_period pp,
project_project project,
plan6_templet_node template_node
where warning.node_id = node.node_id
and node.plan_id = info.biz_cd
and info.project_period_id = pp.project_period_id and pp.is_enabled=1 and pp.project_project_id = project.project_project_id
and template_node.node_id = node.templet_id
and info.if_in_plan = 1
and (warning.charger_cd = 'baolm1' OR node.charger_cd='baolm1' OR node.center_manager_cd='baolm1' OR project.project_charger_cd='baolm1' OR project.project_charger_cd2='baolm1' OR project.project_charger_cd_bis='baolm1' OR ('wubc'='baolm1' AND template_node.node_level in(1,2) ) OR (('xuhf'='baolm1' OR 'zhanghf'='baolm1' ) and template_node.node_level=1) ) and warning.stat = 2
and node.status = 1 and node.is_enabled<>0 and warning.stat = 2
union
select 'unRead' tyep,node.node_id id,template_node.node_name as name,
template_node.node_level,project.project_name as project,
node.schedule_end_date as end,
to_date('','yyyymmdd hh24:mi:ss') as created_date,
'' as center ,'' as year_flg,
ROW_NUMBER() OVER(PARTITION BY node.templet_id,
project.project_project_id ORDER BY r.created_date ASC) as sequence,
r.is_read isRead,to_char(r.created_date,'yyyymmdd hh24:mi:ss') msgCreateDate,info.name group_name,info.biz_cd planId,0 owe_days,0 owe_money
from plan6_message_user_read r,plan6_node node,
project_distribution info,project_period pp,project_project project,
plan6_templet_node template_node
where r.node_id = node.node_id
and node.plan_id = info.biz_cd
and info.project_period_id = pp.project_period_id and pp.is_enabled=1 and pp.project_project_id = project.project_project_id
and template_node.node_id = node.templet_id
and info.if_in_plan = 1
and node.is_enabled<>0
and r.need_reader='baolm1'
and r.is_read='0' ) warning
where warning.sequence = 1 group by name,node_level,project,end,center,year_flg,sequence )
union (select 'oa_meeting' as type,
metting.oa_meeting_id as id,
TO_CHAR (metting.business) as name,
10 as node_level,
'' as project,
metting.target_date as end,
warning.created_date as created_date,
'' as center, '' as year_flg, 0 as sequence,'' isRead,'' msgCreateDate,'' group_name,'' planId,0 owe_days,0 owe_money
from oa_meeting metting, plan_warning warning
where metting.oa_meeting_id = warning.target
and metting.HIDDEN_FLG = 0
and metting.status in (1,2, 4)
and metting.target_date is not null and instr(';'||warning.responsible_person ,';baolm1;')>0 ) union
(select 'work-center-plan' as type, cen.plan_work_center_id as id, cen.content as name, 10 as node_level, '' as project, cen.target_date as end, warning.created_date as created_date, '' center , '' year_flg, 0 as sequence,'' isRead,'' msgCreateDate,'' group_name,'' planId ,0 owe_days,0 owe_money from plan_work_center cen,plan_warning warning where cen.plan_work_center_id = warning.target and cen.status_cd in (1) and cen.open_type = '0' and cen.target_date is not null and instr(';'||cen.principal ,';baolm1;')>0) union (select 'project_detail' as type, pd.project_detail_id as id, pd.content as name,12 as node_level, '' as project, pd.target_date as end, pd.created_date as created_date,'' as center , '' as year_flag, 0 as sequence, '' isRead, '' msgCreateDate, '' group_name,'' planId,0 owe_days,0 owe_money from project_detail pd where pd.status in ('1','2','4') and pd.project_detail_id in (select pp.project_detail_id from project_principal pp where pp.principal_cd='baolm1'))) warning order by (case when type='work-zc-plan' then -1 else 1 end) asc, (case when type='unRead' then 0 else 1 end) asc, (case when isRead=0 then 0 else 1 end) asc, (case when msgCreateDate is not null then 0 else 1 end) asc, warning.type asc,(case when warning.node_level=0 then 4 else warning.node_level end) asc, trunc(warning.end)-trunc(sysdate) asc,warning.created_date desc,owe_money desc ) where rownum <= :1