select a.plan_id,
'补充说明：' || a.contents as caozuo,
' ' as remarks,
a.create_on,
a.create_by
from PROMOTION_PLAN_SUPPLEMENT a
where a.plan_id = '28bae9cc089a446db7264a9ba7a71a75'
union all
select plan_id, '归还服务：' || fwRecord, remarks, modifed_on, modifed_by
from (select plan_id,
wmsys.wm_concat(fwRecord) over(partition by modifed_by, modifed_on) as fwRecord,
row_number() over(partition by modifed_by, modifed_on order by modifed_on desc) rn1,
remarks,
modifed_on,
modifed_by
from (select plan_id,
item_name || '*' || count_num as fwRecord,
remarks,
modifed_on,
modifed_by
from (select plan_id,
item_name,
remarks,
modifed_on,
modifed_by,
row_number() over(partition by item_name, modifed_on, modifed_by order by modifed_on desc) rn,
count(item_name) over(partition by item_name, modifed_on, modifed_by) count_num
from (select p.plan_id,
fw.item_name,
p.remarks,
p.modifed_on,
p.modifed_by
from (select b.plan_id,
b.fw_id,
b.remarks,
b.modifed_on,
b.modifed_by
from promotion_fw_record b
where b.fw_record_status = '2'
and b.plan_id =
'28bae9cc089a446db7264a9ba7a71a75') p
left join (select t2.id, t1.item_name
from fw_item t1,
fw_guajie_item t2
where t1.id = t2.fw_item_id) fw
on p.fw_id = fw.id))
where rn = 1))
where rn1 = 1
order by create_on desc
