API write example out of Pentaho Spoon

This is an example how content can be send to M3 via Pentaho Spoon. I am using a “User defined java class” to perform the API calls.

Here is my transformation:

Basically we have 3 parts in this transformation:

In this first part I am reading informations from a csv file and prepare all stuff that the input is ready for upload via API

In this central part the API is called to write the informations to M3. All stuff is done via Java in a “user defined java class” step

In the 3. part basically I am looking for errors and write a success and error log. finally an email is send to an elaborator with the faulty records.

Here is the content of my “user defined java class” step. In it I call 2 transaction from the PMS070MI. First the GetOperation to retrieve missing informations and then the RptOperation to reports hours on operations of production orders.

import java.util.regex.Pattern;
import java.io.FileWriter;
import java.io.IOException;
import java.util.*;
import java.text.*;
import MvxAPI.*;

private StringBuffer transStr;
private StringBuffer transStr3;
private StringBuffer sErrLine;
private StringBuffer sSucLine;
private StringBuffer sHeader;

private boolean firstErrLogLine;
private boolean firstSucLogLine;

private String mvxCONO; // Company
private String mvxFACI; // Facility
private String mvxPRNO; // Product number
private String mvxMFNO; // Manufacturing order number
private String mvxOPNO; // Operation number
private String mvxWOSQ; // Reporting number
private String mvxPLNO; // Production lot number
private String mvxRPDT; // Reporting date
private String mvxRPTM; // Reporting time
private String mvxPEWA; // Payroll period
private String mvxUMAT; // Used labor run time
private String mvxUMAS; // Used labor setup time
private String mvxUPIT; // Used machine run time
private String mvxUSET; // Used machine setup time
private String mvxRUDI; // Run disturbance
private String mvxSEDI; // Setup disturbance
private String mvxMAQA; // Manufactured quantity
private String mvxMAUN; // Manufacturing U/M
private String mvxSCQA; // Scrap quantity alternative unit
private String mvxSCRE; // Rejection reason
private String mvxSCWC; // Scrap source work center
private String mvxREND; // Manual completion flag
private String mvxEMNO; // Employee number
private String mvxPCTP; // Costing type
private String mvxSHFC; // Shift
private String mvxMSNO; // Resource
private String mvxTRDT; // Transaction date(not used)
private String mvxTRTM; // Transaction time(not used)
private String mvxPRNP; // Planned number of workers – run time
private String mvxSENP; // Planned number of workers – setup
private String mvxREWK; // Rework
private String mvxDPLG; // Deviating work center
private String mvxSEPR; // Setup price
private String mvxSUNO; // Supplier number
private String mvxPIPR; // Unit price
private String mvxTODL; // Total amount direct labor
private String mvxREMK; // Remark
private String mvxWAFA; // Time rate
private String mvxKIWG; // Pay element
private String mvxCHID; // Changed by
private String mvxDSP1; // Warning indicator 1
private String mvxDSP2; // Warning indicator 2
private String mvxDSP3; // Warning indicator 3
private String mvxDSP4; // Warning indicator 4
private String mvxCAMU; // Container
private String mvxBANO; // Lot number
private String mvxRMAQ; // Remaining quantity to manufacture

private String Status;

MvxSockJ obj2, obj3;
int i, j;
String str,str2, str3;
String sendstr;
double ver;
String test, MvxERR;

public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException
{

Object[] r = getRow();

if (r == null) {
setOutputDone();
return false;
}

String M3_System = get(Fields.In, “M3_System”).getString(r);
int M3_Port = get(Fields.In, “M3_Port”).getInteger(r).intValue();
String M3_User = get(Fields.In, “M3_User”).getString(r);
String M3_Pass = get(Fields.In, “M3_Pass”).getString(r);
String OutPath = get(Fields.In, “OutPath”).getString(r);
//TimeStamp
Date dNow = new Date( );
//SimpleDateFormat ft = new SimpleDateFormat (“E yyyy.MM.dd ‘at’ hh:mm:ss a zzz”);
SimpleDateFormat ft = new SimpleDateFormat (“yyyyMMdd_HHmmss”);

Object[] outputRow = RowDataUtil.createResizedCopy(r, data.outputRowMeta.size());

//Initialize Log file first line check
firstErrLogLine = true;
firstSucLogLine = true;

// prepare regex and field helpers
if (first){
first = false;
// get the input and output fields
//assign system variables

// get all rows from the info stream and compile the regex field to patterns
//FieldHelper regexField = get(Fields.Info, “WHLO”);
RowSet infoStream = findInfoRowSet(“api_transactions”);
Object[] infoRow = null;
//Open M3 connection
obj2=new MvxSockJ(M3_System, M3_Port, “”, 0, “”);
obj2.DEBUG = true; // Have MvxSockJ print out debug info

/**
* For old FPW we give LOCALA as the system name, new FPW and NextGen does not care.
* We use the AS400 account USER with password PASSWORD
* We want to connect to program CRS610MI in library MVXBOBJ.
*/

i = obj2.mvxInit(“”, M3_User, M3_Pass, “PMS070MI”);
if(i>0) {
//System.out.println(“mvxInit() returned “+i+” “+obj2.mvxGetLastError());
get(Fields.Out, “Status”).setValue(outputRow, “Error: ” +obj2.mvxGetLastError());
putRow(data.outputRowMeta, outputRow);
return true;
}

ver=obj2.mvxVersion();
//System.out.println(“The current version of MvxSockJ is: ” +ver);
get(Fields.Out, “version”).setValue(outputRow, ver);

//Prepare filenames for logging
String ErrorLogPatch = OutPath + “Log/ErrorLog_B4E2M3_” + new SimpleDateFormat(“yyyyMMdd_HHmmss”).format(Calendar.getInstance().getTime()) + “.txt”;
String SuccessLogPatch = OutPath + “Log/SuccessLog_B4E2M3_” + new SimpleDateFormat(“yyyyMMdd_HHmmss”).format(Calendar.getInstance().getTime()) + “.txt”;

while((infoRow = getRowFrom(infoStream)) != null){

mvxCONO = “001”;
mvxFACI = get(Fields.Info, “FACI”).getString(infoRow);
mvxPRNO = get(Fields.Info, “PRNO”).getString(infoRow);
mvxMFNO = get(Fields.Info, “MFNO”).getString(infoRow);
mvxOPNO = get(Fields.Info, “OPNO”).getString(infoRow);
mvxWOSQ = get(Fields.Info, “WOSQ”).getString(infoRow);
mvxPLNO = null;
mvxRPDT = get(Fields.Info, “RPDT”).getString(infoRow);
mvxRPTM = “”;
mvxPEWA = “”;
mvxUMAT = get(Fields.Info, “UMAT”).getString(infoRow);
mvxUMAS = null;
mvxUPIT = get(Fields.Info, “UPIT”).getString(infoRow);
mvxUSET = “”;
mvxRUDI = “”;
mvxSEDI = “”;
mvxMAQA = get(Fields.Info, “MAQA”).getString(infoRow);
mvxMAUN = “”;
mvxSCQA = “”;
mvxSCRE = “”;
mvxSCWC = “”;
mvxREND = get(Fields.Info, “REND”).getString(infoRow);
if (mvxREND == null) { mvxREND = “0”; }
mvxEMNO = get(Fields.Info, “EMNO”).getString(infoRow);
mvxPCTP = “”;
mvxSHFC = “”;
mvxMSNO = “”;
mvxTRDT = “”;
mvxTRTM = “”;
mvxPRNP = “”;
mvxSENP = “”;
mvxREWK = “”;
mvxDPLG = get(Fields.Info, “DPLG”).getString(infoRow);
mvxSEPR = “”;
mvxSUNO = “”;
mvxPIPR = “”;
mvxTODL = “”;
mvxREMK = “”;
mvxWAFA = “”;
mvxKIWG = “”;
mvxCHID = “”;
mvxDSP1 = “”;
mvxDSP2 = “”;
mvxDSP3 = “”;
mvxDSP4 = “”;
mvxCAMU = “”;
mvxBANO = “”;
//When operation is marked as finished first
if (mvxREND.equals(“1”)) {
/**
* Build the priliminary transaction to catch the remaining quantity, if the operation should be closed
*/

transStr3 = new StringBuffer(1024);
transStr3.setLength(1024);

//Build the transaction string
for(int x=0; x<1024; x++) {
transStr3.setCharAt(x,’ ‘);
}

transStr3.insert(0, “GetOperation”);
transStr3.insert(15, mvxCONO);
//transStr3.insert(15, “002”);
transStr3.insert(18, mvxFACI);
transStr3.insert(21, mvxPRNO);
transStr3.insert(36, mvxMFNO);
transStr3.insert(43, mvxOPNO);

transStr3.setLength(75); // Adjust the length

//Run the transaction

str3=obj2.mvxTrans(transStr3.toString());

if(str3.startsWith(“NOK”)) {
Status = “Error in retrieving open quantity, check reported quantity of operation:” + str3;

} else {
//Extract needed fields for second transaction
mvxRMAQ = str3.substring(385, 402).trim();
mvxMAQA = mvxRMAQ; // Assign the retrieved value to Manufactured quantity
Status = “Operation completed, remaining quantity will be reported”;

}

} else {
Status = “Operation not completed, no quantity reporting!”;
mvxRMAQ = “0”;
mvxMAQA = mvxRMAQ; // Assign the retrieved value to Manufactured quantity

}

/**
* Build the main transaction – reporting of operation
*/

transStr = new StringBuffer(1024);
transStr.setLength(1024);

//Build the transaction string
for(int x=0; x<1024; x++) {
transStr.setCharAt(x,’ ‘);
}

transStr.insert(0, “RptOperation”);
//transStr.insert(15, “003”);
if (mvxCONO != null) { transStr.insert(15, mvxCONO);}
if (mvxFACI != null) { transStr.insert(18, mvxFACI);}
if (mvxPRNO != null) { transStr.insert(21, mvxPRNO);}
if (mvxMFNO != null) { transStr.insert(36, mvxMFNO);}
if (mvxOPNO != null) { transStr.insert(43, mvxOPNO);}
if (mvxWOSQ != null) { transStr.insert(47, mvxWOSQ);}
if (mvxPLNO != null) { transStr.insert(56, mvxPLNO);}
if (mvxRPDT != null) { transStr.insert(66, mvxRPDT);}
if (mvxRPTM != null) { transStr.insert(76, mvxRPTM);}
if (mvxPEWA != null) { transStr.insert(82, mvxPEWA);}
if (mvxUMAT != null) { transStr.insert(88, mvxUMAT);}
if (mvxUMAS != null) { transStr.insert(97, mvxUMAS);}
if (mvxUPIT != null) { transStr.insert(106, mvxUPIT);}
if (mvxUSET != null) { transStr.insert(115, mvxUSET);}
if (mvxRUDI != null) { transStr.insert(124, mvxRUDI);}
if (mvxSEDI != null) { transStr.insert(127, mvxSEDI);}
if (mvxMAQA != null) { transStr.insert(130, mvxMAQA);}
if (mvxMAUN != null) { transStr.insert(147, mvxMAUN);}
if (mvxSCQA != null) { transStr.insert(150, mvxSCQA);}
if (mvxSCRE != null) { transStr.insert(167, mvxSCRE);}
if (mvxSCWC != null) { transStr.insert(169, mvxSCWC);}
if (mvxREND != null) { transStr.insert(177, mvxREND);}
if (mvxEMNO != null) { transStr.insert(178, mvxEMNO);}
if (mvxPCTP != null) { transStr.insert(188, mvxPCTP);}
if (mvxSHFC != null) { transStr.insert(189, mvxSHFC);}
if (mvxMSNO != null) { transStr.insert(192, mvxMSNO);}
if (mvxTRDT != null) { transStr.insert(207, mvxTRDT);}
if (mvxTRTM != null) { transStr.insert(217, mvxTRTM);}
if (mvxPRNP != null) { transStr.insert(223, mvxPRNP);}
if (mvxSENP != null) { transStr.insert(229, mvxSENP);}
if (mvxREWK != null) { transStr.insert(235, mvxREWK);}
if (mvxDPLG != null) { transStr.insert(236, mvxDPLG);}
if (mvxSEPR != null) { transStr.insert(244, mvxSEPR);}
if (mvxSUNO != null) { transStr.insert(262, mvxSUNO);}
if (mvxPIPR != null) { transStr.insert(272, mvxPIPR);}
if (mvxTODL != null) { transStr.insert(290, mvxTODL);}
if (mvxREMK != null) { transStr.insert(308, mvxREMK);}
if (mvxWAFA != null) { transStr.insert(338, mvxWAFA);}
if (mvxKIWG != null) { transStr.insert(356, mvxKIWG);}
if (mvxCHID != null) { transStr.insert(360, mvxCHID);}
if (mvxDSP1 != null) { transStr.insert(370, mvxDSP1);}
if (mvxDSP2 != null) { transStr.insert(371, mvxDSP2);}
if (mvxDSP3 != null) { transStr.insert(372, mvxDSP3);}
if (mvxDSP4 != null) { transStr.insert(373, mvxDSP4);}
if (mvxCAMU != null) { transStr.insert(374, mvxCAMU);}
if (mvxBANO != null) { transStr.insert(386, mvxBANO);}

transStr.setLength(400); // Adjust the length

//Run the transaction
str=obj2.mvxTrans(transStr.toString());

// Start Log file Handling, if NOK write Error Log, if OK write Success Log
if(str.startsWith(“NOK”)) {
Status = Status + “; ” + “Error in time reporting:” + str;

//write errors to Error Log File
try
{
//as first line write the header
if (firstErrLogLine){
firstErrLogLine = false;

sHeader = new StringBuffer(1024);
sHeader.setLength(1024);

//Build the transaction string
for(int x=0; x<1024; x++) {
sHeader.setCharAt(x,’ ‘);
}

sHeader.insert(0, “Product”);
sHeader.insert(16, “MO No.”);
sHeader.insert(24, “OP No.”);
sHeader.insert(31, “Rep.No.”);
sHeader.insert(41, “Rep.Date”);
sHeader.insert(52, “Man.Qty”);
sHeader.insert(70, “Rep.Time”);
sHeader.insert(80, “Compl.”);
sHeader.insert(87, “Emp.No.”);
sHeader.insert(98, “Dev.WC”);
sHeader.insert(106, “Status”);
sHeader.insert(367,”API String”);

FileWriter writer = new FileWriter(ErrorLogPatch, true);
writer.append(sHeader.toString().trim());
writer.append(“\r\n”);
writer.flush();
writer.close();
sHeader.delete(0, sHeader.length());
}

//Write the lines
sErrLine = new StringBuffer(1024);
sErrLine.setLength(1024);

//Build the transaction string
for(int x=0; x<1024; x++) {
sErrLine.setCharAt(x,’ ‘);
}

if (mvxPRNO != null) { sErrLine.insert(0, mvxPRNO);}
if (mvxMFNO != null) { sErrLine.insert(16, mvxMFNO);}
if (mvxOPNO != null) { sErrLine.insert(24, mvxOPNO);}
if (mvxWOSQ != null) { sErrLine.insert(31, mvxWOSQ);}
if (mvxRPDT != null) { sErrLine.insert(41, mvxRPDT);}
if (mvxMAQA != null) { sErrLine.insert(52, mvxMAQA);}
if (mvxUMAT != null) { sErrLine.insert(70, mvxUMAT);}
if (mvxREND != null) { sErrLine.insert(80, mvxREND);}
if (mvxEMNO != null) { sErrLine.insert(87, mvxEMNO);}
if (mvxDPLG != null) { sErrLine.insert(98, mvxDPLG);}
if (Status != null) { sErrLine.insert(106, Status);}
if (transStr != null) { sErrLine.insert(367, transStr);}
FileWriter writer = new FileWriter(ErrorLogPatch, true);
writer.append(sErrLine.toString().trim());
writer.append(“\r\n”);
writer.flush();
writer.close();
sErrLine.delete(0, sErrLine.length());
}
catch(IOException e)
{
e.printStackTrace();
}

} else {
Status = Status + “; ” + “Transaction OK”;

//Write successed transactions to Success Log File
try
{

//as first line write the header
if (firstSucLogLine){
firstSucLogLine = false;

sHeader = new StringBuffer(1024);
sHeader.setLength(1024);

//Build the transaction string
for(int x=0; x<1024; x++) {
sHeader.setCharAt(x,’ ‘);
}

sHeader.insert(0, “Product”);
sHeader.insert(16, “MO No.”);
sHeader.insert(24, “OP No.”);
sHeader.insert(31, “Rep.Number”);
sHeader.insert(41, “Rep.Date”);
sHeader.insert(52, “Man.Qty”);
sHeader.insert(70, “Rep.Time”);
sHeader.insert(80, “Compl.”);
sHeader.insert(87, “Emp.No.”);
sHeader.insert(98, “Dev.WC”);
sHeader.insert(106, “Status”);
//sHeader.insert(367,”API String”);

FileWriter writer = new FileWriter(SuccessLogPatch, true);
writer.append(sHeader.toString().trim());
writer.append(“\r\n”);
writer.flush();
writer.close();
sHeader.delete(0, sHeader.length());
}

//Write the lines
sSucLine = new StringBuffer(1024);
sSucLine.setLength(1024);

//Build the transaction string
for(int x=0; x<1024; x++) {
sSucLine.setCharAt(x,’ ‘);
}

if (mvxPRNO != null) { sSucLine.insert(0, mvxPRNO);}
if (mvxMFNO != null) { sSucLine.insert(16, mvxMFNO);}
if (mvxOPNO != null) { sSucLine.insert(24, mvxOPNO);}
if (mvxWOSQ != null) { sSucLine.insert(31, mvxWOSQ);}
if (mvxRPDT != null) { sSucLine.insert(41, mvxRPDT);}
if (mvxMAQA != null) { sSucLine.insert(52, mvxMAQA);}
if (mvxUMAT != null) { sSucLine.insert(70, mvxUMAT);}
if (mvxREND != null) { sSucLine.insert(80, mvxREND);}
if (mvxEMNO != null) { sSucLine.insert(87, mvxEMNO);}
if (mvxDPLG != null) { sSucLine.insert(98, mvxDPLG);}
if (Status != null) { sSucLine.insert(106, Status);}
//if (transStr != null) { sSucLine.insert(367, transStr);}

FileWriter writer = new FileWriter(SuccessLogPatch, true);
writer.append(sSucLine.toString().trim());
writer.append(“\r\n”);
writer.flush();
writer.close();
sSucLine.delete(0, sSucLine.length());
}
catch(IOException e)
{
e.printStackTrace();
}

}

get(Fields.Out, “TimeStamp”).setValue(outputRow, ft.format(dNow));
get(Fields.Out, “ErrorLogPatch”).setValue(outputRow, ErrorLogPatch);
get(Fields.Out, “SuccessLogPatch”).setValue(outputRow, SuccessLogPatch);
putRow(data.outputRowMeta, outputRow);

}
}

i=obj2.mvxClose(); // close connection
return true;
}

Hope this is may usefull for someone… if you have questions please let me know