oracle，在一条sql语句里面的wmsys.wm_concat()、row_number()、union all 的用法

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