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:

spoon_pms070mi

Basically we have 3 parts in this transformation:

spoon_pms070mi_2

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

 

spoon_pms070mi_3

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

 

spoon_pms070mi_4

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

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s