It's not easy. The Work Centre messages are held hierarchically in table F01131M, where each row is identified by a serial number and also carries the serial number for it's parent so you have to step through the messages to find the actual error you want.

We have one orchestration that looks at the Work Centre messages to return an error message to an external system and uses a recursive SQL to generate all of the error messages for a specific transaction and then returns them in a text file via SFTP.

We use a Database Connector to run the SQL and build the text file. Substitution data is populated in the error message. The following code snippet will hopefully help:

All the best
Jonathan

    // Retrieve the error messages associated with the rejected voucher...

    def sqlErr = " select zmppsrk" +

                      " , zmpsrk" +

                      " , zmserk" +

                      " , frdsca" +

                      " , zmlv" +

                      " , zmtsv" +

                      " , zmtmpi" +

                   " from ${dta}.f01131m" +

                   " left outer join dd920.f9203" +

                     " on frdtai = zmtmpi" +

                  " where (zmppsrk = (select max(zmppsrk)" +

                                      " from ${dta}.f01131m" +

                                     " where zmtmpi = 'LM0012'" +

                                       " and zmtsv like '%${ediBatch}%') or" +

                         " zmppsrk = (select min(zmserk)-1" +

                                      " from ${dta}.f01131m" +

                                     " where zmtmpi in ('LM0002', 'LM0006', 'LM0007', 'LM0008')" +

                                       " and zmtsv like '%${caseID}_${YMD}%'))" +

                    " and zmserk >= (select min(zmserk)-1" +

                                     " from ${dta}.f01131m" +

                                    " where zmtmpi in ('LM0002', 'LM0006', 'LM0007', 'LM0008')" +

                                      " and zmtsv like '%${caseID}_${YMD}%')" +

                  " start with zmpsrk = (select min(zmpsrk)" +

                                         " from ${dta}.f01131m" +

                                        " where zmtmpi in ('LM0002', 'LM0006', 'LM0007', 'LM0008')" +

                                          " and zmtsv like '%${caseID}_${YMD}%')" +

                " connect by nocycle zmpsrk = prior zmserk" +

                  " order by 3, 2, 1";

    if (debug) { orchAttr.writeDebug(dbgPrefix + "0055 - SQL: " + sqlErr); }


    PreparedStatement prpErr = sqlConnection.prepareStatement(sqlErr); 

    ResultSet setErr = prpErr.executeQuery();    

    errMsg = "";

    errCode = "";

    while (setErr.next()) {

      msgText = setErr.getString("frdsca");

      msgData = setErr.getString("zmtsv");

      msgID   = setErr.getString("zmtmpi");


      // Merge any substitution values into the message text...

      String [] cleanData = msgData.split("<")*.trim();

      String [] sub = cleanData[0].split("\\|")*.trim();


      if (msgText.contains("&1")) { msgText = msgText.replace("&1", sub[0].trim()); }

      if (msgText.contains("&2")) { msgText = msgText.replace("&2", sub[1].trim()); }

      if (msgText.contains("&3")) { msgText = msgText.replace("&3", sub[2].trim()); }

      if (msgText.contains("&4")) { msgText = msgText.replace("&4", sub[3].trim()); }

      if (msgText.contains("&5")) { msgText = msgText.replace("&5", sub[4].trim()); }


      // Add to the error message.  This allows for multiple lines...

      errMsg = errMsg.trim() + "  " + msgText.trim();

      errCode = errCode.trim() + " " + msgID.trim() + ";";

    }

    setErr.close();

    prpErr.close();    


    // Check for missing values and default if needed..

    if (errCode == "") { errCode = dftCode; }

    if (errMsg == "")  { errMsg  = dftMsg;  }


    // Build output line for text file...

    output = caseID + sep +

             errCode + sep + 

             errMsg;


    // and write it to the text file...

    if (debug) { orchAttr.writeDebug(output); }

    fw.write(output + crlf);


  }

}