Automic Workload Automation

 View Only
  • 1.  Change Control Report showing objects modified, with no update in Docu tab

    Posted Aug 11, 2022 06:36 AM

    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:






  • 2.  RE: Change Control Report showing objects modified, with no update in Docu tab

    Posted Aug 11, 2022 08:33 AM
    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;


  • 3.  RE: Change Control Report showing objects modified, with no update in Docu tab

    Posted Aug 11, 2022 11:07 AM
    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  :-)



  • 4.  RE: Change Control Report showing objects modified, with no update in Docu tab

    Posted Aug 11, 2022 12:19 PM
    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.


  • 5.  RE: Change Control Report showing objects modified, with no update in Docu tab

    Posted Aug 11, 2022 12:28 PM

    Sounds like a good setup. 

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




  • 6.  RE: Change Control Report showing objects modified, with no update in Docu tab

    Posted Aug 11, 2022 02:35 PM


    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




  • 7.  RE: Change Control Report showing objects modified, with no update in Docu tab

    Posted Aug 24, 2022 05:54 AM
    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?