Change Control Report showing objects modified, with no update in Docu tab

[Rick Murray]

Good morning,

In a prior life, a coworker had created (or found) a query that would report all JOBP, JOBS, INC, CALE that were modified in the past few weeks, but didn't have  a matching date as the last update in the Docu tab that started with (MM-DD-YY ....).  This came in very handy to make sure that all objects contained a cross reference to the Support Case that they had for the change.

By any chance, is anyone aware of such a report?

Example:

[Michael Kutswa]

For Oracle we use this SQL:

SELECT OH.OH_NAME Objekt, TO_CHAR(OH.OH_MODDATE, 'DD.MM.YYYY HH24:MI') Datum, USR.USR_FIRSTNAME || ' ' || USR.USR_LASTNAME Name
from OH
left join USR on OH.OH_MODUSERIDNR = USR.USR_OH_IDNR
left join ODOC on OH.OH_IDNR = ODOC.ODOC_OH_IDNR and ODOC.ODOC_NAME = 'Allgemein'
where OH.OH_CLIENT = &$CLIENT#
and OH.OH_DELETEFLAG = 0
and OH.OH_MODDATE >= CAST(FROM_TZ(to_timestamp('&FROMDATE#', 'YYYYMMDD'),sessiontimezone) AT TIME ZONE 'UTC' AS DATE)
and OH.OH_OTYPE in (select OTYP_Type from UC_OTYP where OTYP_Executable = 1)
and (
ODOC.ODOC_CONTENT IS NULL
or dbms_lob.instr(UC4_READ.BLOB2CLOB(ODOC.ODOC_CONTENT),TO_CHAR(OH.OH_MODDATE,'DD.MM.YYYY')) = 0
)
order by OH.OH_MODDATE

It use the function BLOB2CLOB that I found somewhere, else you can't parse the whole Docu content if it's more than 1024 chars:
create or replace function blob2clob(p_blob in blob)
return CLOB
is
-- Byte order marks for UTF-8, UTF-16LE and UTF-16BE
C_BOM_UTF8 constant raw(3) := hextoraw('EFBBBF');
C_BOM_UTF16LE constant raw(2) := hextoraw('FFFE');
C_BOM_UTF16BE constant raw(2) := hextoraw('FEFF');

CSID_UTF8 constant integer := 873;
CSID_UTF16LE constant integer := 2002;
CSID_UTF16BE constant integer := 2000;

l_dest_offset integer := 1;
l_src_offset integer := 1;
l_encoding integer := CSID_UTF8;
l_lang_context number := dbms_lob.default_lang_ctx;

l_bom raw(3);
l_warning integer;
l_start_pos integer := 1;

l_result CLOB;
begin

l_bom := dbms_lob.substr(p_blob, 3);
if l_bom = C_BOM_UTF8 then
l_encoding := CSID_UTF8;
l_start_pos := 4;
else
case utl_raw.substr(l_bom, 1, 2)
when C_BOM_UTF16LE then
l_encoding := CSID_UTF16LE;
l_start_pos := 3;
when C_BOM_UTF16BE then
l_encoding := CSID_UTF16BE;
l_start_pos := 3;
else
l_encoding := CSID_UTF8;
end case;
end if;

DBMS_LOB.createtemporary(l_result, false);

DBMS_LOB.converttoclob(l_result,
p_blob,
DBMS_LOB.GETLENGTH(p_blob)-l_start_pos+1,
l_dest_offset,
l_start_pos,
l_encoding,
l_lang_context,
l_warning);

return l_result;
end;

[Rick Murray]

Thanks Michael,  

Getting BLOB2CLOB loaded probably may take some time on this side, but I can try to work on setting this up.  This will be the first job that I am setting up to directly run a query on the AE database, so that may also take some time  :-)

[Michael Kutswa]

Welcome. We created the function with a read (UC4_READ) user in the database and grant the normal user access to the function, so we can use the SQL in an SQLI Vara Object that we just process with an Call Operator to send a Mail daily.

[Rick Murray]

Sounds like a good setup. 

Myself and a coworker are looking into getting this running, but it make take some time

[Rick Murray]

Michael,

Thanks again for what you provided.  It gave us a great start

SELECT OH.OH_NAME Objekt, TO_CHAR(OH.OH_MODDATE, 'MM-DD-YYYY HH24:MI') Modified_Date, USR.USR_FIRSTNAME || ' ' || USR.USR_LASTNAME Name, TO_CLOB(ODOC.ODOC_CONTENT)
from OH
left join USR on OH.OH_MODUSERIDNR = USR.USR_OH_IDNR
left join ODOC on OH.OH_IDNR = ODOC.ODOC_OH_IDNR
where OH.OH_CLIENT = 99
and ODOC.ODOC_NAME = 'Changes'
--and OH.OH_OTYPE = 'JOBS'
and OH.OH_DELETEFLAG = 0
and OH.OH_MODDATE >= '10-JUL-22'
and OH.OH_OTYPE in (select OTYP_Type from UC_OTYP where OTYP_Executable = 1)
and (
ODOC.ODOC_CONTENT IS NULL
or dbms_lob.instr(TO_CLOB(ODOC.ODOC_CONTENT),TO_CHAR(OH.OH_MODDATE,'MM-DD-YY')) = 0
)
order by OH.OH_MODDATE

[Rick Murray]

Still working on getting BLOB2CLOB loaded.

Attempted several different ways to get the data from ODOC.ODOC_CONTENT, but as you know, none of them seem to work.

Does anyone know why ODOC.ODOC_CONTENT is a BLOB?