read PS Process Message Log

    

An easy way to review process message log, even if instance is deleted (not purged). 


create or replace type H_PRCS_MSG_LOG is object (msg_seq NUMBER, msg_dttm varchar2(50), msg_txt varchar2(2000));

create or replace type H_PRCS_MSG_DTL is table of H_PRCS_MSG_LOG;

CREATE OR REPLACE FUNCTION H_GET_MSG_DTL(prcsinstance integer) RETURN H_PRCS_MSG_DTL is
  idx NUMBER := 0;
  msgs varchar2(2000);
  parm varchar2(2000);
  sqls varchar2(2000); 
  l_msg_dtls H_PRCS_MSG_DTL:= H_PRCS_MSG_DTL();
  
   cursor csr is 
        select a.PROCESSINSTANCE, to_char(a.MSGLOG_DTTM, 'mm/dd/yy hh24:mi:ss') as MSG_DTTM, MESSAGE_SEQ, a.MESSAGE_SET_NBR, a.MESSAGE_NBR,b.MESSAGE_TEXT 
        from  ps_PMN_MSGLOG_VW a, PSMSGCATDEFN b  
        where a.MESSAGE_SET_NBR = b.MESSAGE_SET_NBR and a.MESSAGE_NBR=b.MESSAGE_NBR 
        and a.PROCESSINSTANCE=prcsinstance 
        union
        select a.PROCESSINSTANCE, to_char(a.MSGLOG_DTTM, 'mm/dd/yy hh24:mi:ss') as MSG_DTTM, MESSAGE_SEQ, a.MESSAGE_SET_NBR, a.MESSAGE_NBR,b.MESSAGE_TEXT 
        from  ps_PMN_MSGLOG_VW a, PSMSGCATDEFN b  
        where a.MESSAGE_SET_NBR = 0 and b.MESSAGE_SET_NBR =65 
        and a.MESSAGE_NBR= 0 and b.MESSAGE_NBR=30 
        and a.PROCESSINSTANCE=prcsinstance
        order by   MESSAGE_SEQ;

  BEGIN
  
  for zz in csr Loop
   
   idx:=idx+1;
   msgs:=zz.MESSAGE_TEXT;
  
  begin
     for x in (select PARM_SEQ, trim(MESSAGE_PARM) as MESSAGE_PARM from PS_MESSAGE_LOGPARM
                   where PROCESS_INSTANCE = zz.PROCESSINSTANCE
                   and MESSAGE_SEQ                = zz.MESSAGE_SEQ order by PARM_SEQ )
     loop
         -- escape quotes adding extra "'" 
         select replace(x.MESSAGE_PARM, '''', '''''') into parm from dual;
         select replace(msgs, '''', '''''') into msgs from dual;
     
         msgs := ''''||msgs ||''',''%' ||  x.PARM_SEQ ||''','''|| parm ||'''';
         sqls:='select replace('|| msgs ||') from dual';

          EXECUTE IMMEDIATE  sqls into msgs;          
      end loop;
      
      SELECT REGEXP_REPLACE(msgs, '%.','') into msgs from dual;
         
       l_msg_dtls.extend();
       
       l_msg_dtls(idx) := H_PRCS_MSG_LOG(zz.MESSAGE_SEQ, zz.MSG_DTTM, msgs); 
         
  exception
  WHEN OTHERS then
    null;    
  end;
  
 -- dbms_output.put_line(idx||':'|| l_msg_dtls(idx) );
  
  END LOOP;
  
  --dbms_output.put_line(idx);
  return l_msg_dtls;
  
END;
/