goliaf,
--1 инициализация wm_concat
--declare r number := rtl.open(); begin executor.set_context('WM_CONCAT_DELIM',chr(10)); end;
/*
create or replace procedure wm_concat_init(p_separator in varchar2 default ',')
is
r number := rtl.open();
begin
executor.set_context('WM_CONCAT_DELIM', p_separator);
end;
*/
/*
begin
wm_concat_init(chr(10));
end;
/
*/
--2 скрипт получения объектов

select
'VER2'||chr(10)||
'REM Список элементов'||chr(10)||
'REM IBS@IBSODC'||chr(10)||
'REM Версия ТЯ: 7.4.5.4'||chr(10)||
'REM Версия Администратора проектов: 6.93.0.107'||chr(10)||
''||chr(10)||
wm_concat(c_type_obj||' '||c_class||' '||regexp_replace(c_obj,'chr(13)|chr(10)',' ')) c_pck
from
(

select
c_modified_time as c_modified_time,
user_modified,
c_class_id as c_type_obj,
meth_class as c_class,
case
when c_class_id in ('ATR','IDX', 'TYPE') then
c_text
else meth_name
end as c_obj,
c_text
from
(
select
x.sname as c_class_meth
, x.c_modified_time as c_modified_time
, x.Class_Id as c_Class_Id
, x.meth_class as meth_class
, x.meth_name as meth_name
, x.action as c_action
, x.topic as c_topic
, x.what as c_what
, x.text as c_text
, user_modified

from
(
with ls as
(
select /*+ MATERIALIZE*/ *
from
(
SELECT
a1.CODE ID, a1.O_TYPE Class_Id,
m.user_modified user_modified,
a1.USER_ID C_USER,
a1.TIME C_TIME,
nvl(m.modified, a1.TIME) c_modified_time,
case when a1.WHAT in ('DROP','DELETE') then 'DROP' when a1.WHAT in ('CREATE','INSERTED') then 'ADD' when a1.WHAT in ('ERROR','COMPILE','UPDATED','REBUILDED') then 'EDIT' else 'OTHER' end ACTION,
a1.TOPIC TOPIC,
a1.SNAME SNAME,
a1.WHAT WHAT,
a1.TEXT TEXT,
a1.meth_class,
a1.meth_name
from (
select b1.TIME TIME, b1.USER_ID USER_ID, b1.CODE CODE, b1.TOPIC TOPIC, b1.O_TYPE O_TYPE, b1.SNAME SNAME, b1.TEXT TEXT, b1.WHAT WHAT, FIRST_VALUE(b1.WHAT) over (partition by b1.USER_ID, b1.O_TYPE, b1.CODE order by case when b1.WHAT in ('DROP','DELETE','CREATE','INSERTED') then 1 when b1.WHAT in ('ERROR','COMPILE','UPDATED','REBUILDED') then 2 else 5 end asc, b1.TIME desc) MADE, FIRST_VALUE(b1.TIME) over (partition by b1.USER_ID, b1.O_TYPE, b1.CODE, b1.WHAT order by b1.TIME desc) MADETIME
, trim(regexp_replace(replace(replace(b1.SNAME, chr(10),''), chr(13), ''),'([a-zA-Z0-9_#]*)\.([a-zA-Z0-9_#]*)(.*)|( - .*)','\1')) meth_class
, trim(regexp_replace(replace(replace(b1.SNAME, chr(10),''), chr(13), ''),'([a-zA-Z0-9_#]*)\.([a-zA-Z0-9_#]*)(.*)|( - .*)','\2')) meth_name
from (
select c1.TIME TIME, c1.USER_ID USER_ID, c1.METHOD_ID CODE, 'M' TOPIC, 'METH' O_TYPE, c1.SNAME SNAME, c1.NAME TEXT, c1.ACTION WHAT
from DIARY_METHODS c1
union all
select d1.TIME TIME, d1.USER_ID USER_ID, d1.METHOD_ID CODE, d1.TOPIC TOPIC, 'METH' O_TYPE, d1.MNAME SNAME, d1.TEXT TEXT, d1.ACTION WHAT
from DIARY_PARAM_VARS d1
union all
select e1.TIME TIME, e1.USER_ID USER_ID, e1.METHOD_ID CODE, 'F' TOPIC, 'METH' O_TYPE, e1.MNAME SNAME, e1.NAME TEXT, e1.ACTION WHAT
from DIARY_FORMS e1
union all
select f1.TIME TIME, f1.USER_ID USER_ID, f1.CRITERIA_ID CODE, 'C' TOPIC, 'CRIT' O_TYPE, f1.SNAME SNAME, f1.NAME TEXT, f1.ACTION WHAT
from DIARY_CRITERIA f1
union all
select g1.TIME TIME, g1.USER_ID USER_ID, g1.CLASS_ID CODE, 'B' TOPIC, 'TYPE' O_TYPE, g1.CLASS_ID SNAME, g1.NAME TEXT, g1.ACTION WHAT
from DIARY_STORAGE g1
union all
select h1.TIME TIME, h1.USER_ID USER_ID, h1.CLASS_ID CODE, h1.TOPIC TOPIC, 'IDX' O_TYPE, h1.CLASS_ID SNAME, h1.TEXT TEXT, h1.ACTION WHAT
from DIARY_STORAGE_EXT h1
union all
select i1.TIME TIME, i1.USER_ID USER_ID, i1.CLASS_ID CODE, i1.TOPIC TOPIC, 'ATR' O_TYPE, i1.CLASS_ID SNAME, i1.TEXT TEXT, i1.ACTION WHAT
from DIARY_ATTRIBUTES i1
) b1
where (&P_USER_BEG is null or UPPER(b1.USER_ID) = UPPER(&<name = "P_USER_BEG" default ="'IBS.MBelin'"> ))
and b1.time >= &<name = "P_DATE_BEG" default ="to_date('25/09/2015','DD/MM/YYYY')">
order by b1.USER_ID,b1.O_TYPE,b1.CODE,b1.TIME
) a1 , methods m
where a1.WHAT = a1.MADE and a1.MADETIME = a1.TIME
and a1.meth_class = m.class_id (+)
and a1.meth_name = m.short_name (+)
and (m.user_modified is null or instr(a1.USER_ID, m.user_modified) > 0)
)
--where trunc(c_time) = trunc(c_modified_time)
-- where what <> 'COMPILE'
order by c_time
)
select * from ls
where (&<name = "P_NUM_ZI" default ="''"> is null or (class_id <> 'METHODS' or (class_id = 'METHODS' and exists (select 1 from sources b where b.name = ls.id and instr(b.text, &P_NUM_ZI) > 0 ))))
-- не дистрибутив
and not exists(select 1 from OBJECTS_OPTIONS zz where zz.class_id = ls.meth_class and zz.short_name = ls.meth_name and rownum = 1)
and ls.c_modified_time >= &<name = "P_DATE_BEG" default ="to_date('26/09/2017','DD/MM/YYYY')">
order by c_modified_time desc
) x
-- group by x.sname
)
order by c_modified_time desc
)

test