How can Java run Groovy script dynamically

 –  This blog shows how to Integrate Groovy into Java

Java is powerful and it can do almost everything.

However, it feels tired sometimes to spend hours on the setting before seeing a ‘Hello World’ to print out.

On the contrast, Groovy is easy to use as a scripting language on top of Java.

Sometimes it gives me an idea if we can merge these 2 together to make the life easier.

This blog shows a way to run Groovy Scripts dynamically inside Java – just do some simple change, we can make it a simple and powerful tool that can be embedded into existing Java projects as a scripting addon.

Following codes uses 2 different kinds of Groovy scripting running method, please refer to Standard Groovy documentation “Integrating Groovy into applications” for the difference.

Transformer1 uses GroovyScriptEngine

Transformer2 uses GroovyShell


import groovy.lang.Binding;
import groovy.lang.GroovyShell;
import groovy.util.GroovyScriptEngine;
import java.io.File;
import java.nio.file.Files;
import org.apache.commons.io.FileUtils;

public class GroovyTransformer {

public static void main(String[] args) throws Exception {
groovyScriptTransformer1();
// groovyScriptTransformer2();
}

public static void groovyScriptTransformer1() throws Exception {
byte[] input = Files.readAllBytes(new File("c:/test/order_sample.csv").toPath());

Binding sharedData = new Binding();
sharedData.setVariable("input", input);

String[] roots = new String[] { "c:/test" };
GroovyScriptEngine gse = new GroovyScriptEngine(roots);
gse.run("csvParser.groovy", sharedData);

byte[] output = (byte[]) sharedData.getVariable("output");
System.out.println(new String(output));

}

public static void groovyScriptTransformer2() throws Exception {

// get the source file bytes
byte[] input = Files.readAllBytes(new File("c:/test/order_sample.csv").toPath());
Binding sharedData = new Binding();
sharedData.setVariable("input", input);

// get the groovy script as a String
String script = FileUtils.readFileToString(new File("c:/test/csvParser.groovy"), "UTF-8");
System.out.println(script);

// create a groovy shell and run the script
GroovyShell shell = new GroovyShell(sharedData);
shell.evaluate(script);

// get the output of the transformation
byte[] output = (byte[]) sharedData.getVariable("output");
System.out.println(new String(output));
}

}

Following is the sample file “order_sample.csv”


Order Number,Date,To Name,
10090,29/10/15,Sharen Shirley,
10091,29/10/15,Justina Jump,
10092,29/10/15,Macie Maxon,
10093,29/10/15,Shamika Solt,
10094,29/10/15,Marcos Mccabe,

Following is the sample Groovy script “csvParser.groovy”, it did nothing but parsed the csv file into lines, it can easily be changed to transform to other file formats – xml, json or marshaling to groovy/java objects.


import java.io.ByteArrayInputStream
import java.io.Reader
import java.io.InputStreamReader
import static com.xlson.groovycsv.CsvParser.parseCsv
def sb = StringBuilder.newInstance()

Reader inputReader = new InputStreamReader(new ByteArrayInputStream(input));
for(line in parseCsv(inputReader,separator:',')){
// println line
sb.append(line)
sb.append("\n")
}

output = sb.toString().getBytes()

Xml Helper Class for Groovy / Java

Using the standard java XML library could be a little bit complex, especially when you just want a quick handling of XML files and don’t want to dive deep into the codes that parse it.

Following code provides a wrapped helper class which helps to do this.


import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import java.io.StringWriter;
import java.io.Writer;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class XmlUtil {
public static Document getXmlDocumentByString(String xmlString) {
Document doc = null;
try {
InputStream is = new ByteArrayInputStream(xmlString.getBytes());

DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
dbFactory.setNamespaceAware(false);
DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
doc = dBuilder.parse(is);

} catch (Exception ex) {
ex.printStackTrace();
}
return doc;
}

public static Document getXmlDocumentByBytes(byte[] inputBytes) {
Document doc = null;
try {
InputStream is = new ByteArrayInputStream(inputBytes);

DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
dbFactory.setNamespaceAware(false);
DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
doc = dBuilder.parse(is);

} catch (Exception ex) {
ex.printStackTrace();
}
return doc;
}

public static NodeList getXmlNodeListByXPath(Document doc, String xpath) throws Exception {
XPath xPath = XPathFactory.newInstance().newXPath();
return (NodeList) xPath.compile(xpath).evaluate(doc, XPathConstants.NODESET);
}

public static NodeList getXmlNodeListByXPath(Node node, String xpath) throws Exception {
XPath xPath = XPathFactory.newInstance().newXPath();
return (NodeList) xPath.compile(xpath).evaluate(node, XPathConstants.NODESET);
}

public static Node getXmlNodeByXPath(Node node, String xpath) throws Exception {
XPath xPath = XPathFactory.newInstance().newXPath();
return (Node) xPath.compile(xpath).evaluate(node, XPathConstants.NODE);
}

public static Document removeNodesByTag(Document xmlDoc, String nodeTag) {
NodeList nl = xmlDoc.getElementsByTagName(nodeTag);
for (int i = nl.getLength() - 1; i >= 0; i--) {
Node n = nl.item(i);
n.getParentNode().removeChild(n);
}

return xmlDoc;
}

public static String prettyPrint(Document xml) throws Exception {
Transformer tf = TransformerFactory.newInstance().newTransformer();
tf.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
tf.setOutputProperty(OutputKeys.INDENT, "yes");
Writer out = new StringWriter();
tf.transform(new DOMSource(xml), new StreamResult(out));
return out.toString();
}

// following file exscapes the xml specific chars inside the string.
public static String escapeXml(String s) {
return s.replaceAll("&", "&")
.replaceAll(">", ">")
.replaceAll("<", "<")
.replaceAll("\"", """)
.replaceAll("'", "'");
}

public static byte[] readBytesFromFile(String filePath) {

FileInputStream fileInputStream = null;
byte[] bytesArray = null;
try {

File file = new File(filePath);
bytesArray = new byte[(int) file.length()];

//read file into bytes[]
fileInputStream = new FileInputStream(file);
fileInputStream.read(bytesArray);

} catch (IOException e) {
e.printStackTrace();
} finally {
if (fileInputStream != null) {
try {
fileInputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return bytesArray;
}
}

 

How to get Oracle ERP Cloud BI Publisher Reports by Groovy / Java

Sometimes we need to export some data from Oracle ERP / SCM Cloud.

However, Oracle didn’t provide us direct connections to the backend database.

There are 2 ways we can get the data back from ERP Cloud, one is through the web services (soap/restful) defined for different data objects, another is through the BI Publisher calls to export a data only report.

This blog shows how to use Groovy / Java to export a BI Publisher Report from Oracle ERP Cloud – Normally we will create some standalone reports within BI Publisher, and make its output to be csv only or xml only.

Following is the code that shows how to extract it.

The code uses standard apache http client package which can be found here: Apache HttpClient

import org.apache.http.HttpResponse;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.HttpClientBuilder;
import com.sun.org.apache.xml.internal.security.utils.Base64;
import java.text.MessageFormat;


String url = "https://YOUR_ERP_CLOUD_ENVIRONMENT/xmlpserver/services/ExternalReportWSSService";
String authStr = "USERNAME:PASSWORD";
HttpClient client = HttpClientBuilder.create().build();
HttpPost post = new HttpPost(url);

// set credential
byte[] authBytes = authStr.getBytes("UTF-8");
String auth = Base64.encode(authBytes);
post.setHeader("Authorization", "Basic " + auth);
post.setHeader("Content-Type", "application/soap+xml;charset=UTF-8");

String payload =
	'''
	<soap:Envelope xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService" xmlns:soap="http://www.w3.org/2003/05/soap-envelope">
		<soap:Body>
			<pub:runReport>
				<pub:reportRequest>
					<pub:attributeFormat>xml</pub:attributeFormat>
					<pub:attributeLocale/>
					<pub:attributeTemplate/>
					<pub:reportAbsolutePath>/Custom/REPORT_PATH/REPORT_NAME.xdo</pub:reportAbsolutePath>
					<pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>
					<pub:parameterNameValues>
						<pub:item>
							<pub:label>PARAMETER1</pub:label>
							<pub:name>PARAMETER1</pub:name>
							<pub:values>
								<pub:item>{0}</pub:item>
							</pub:values>
						</pub:item>
					</pub:parameterNameValues>
				</pub:reportRequest>
				<pub:appParams/>
			</pub:runReport>
		</soap:Body>
	</soap:Envelope>
	'''

payload = MessageFormat.format(payload, "PARAMETER_VALUE");

post.setEntity(new StringEntity(payload));

// execute the request and get the response
HttpResponse response = client.execute(post);

// get the result, you can use apache IOUtils to convert the contents InputStream to string 
BufferedReader rd = new BufferedReader(new InputStreamReader(response.getEntity().getContent()));
StringBuffer result = new StringBuffer();
String line = "";
while ((line = rd.readLine()) != null) {
	result.append(line);
}
print( result.toString());

 

How to import attachments to Oracle ERP Cloud

Sometimes, when doing integration, we need to import attachments to Oracle ERP Cloud.

Oracle ERP Cloud have a common import framework for ERP/SCM Cloud.

We can find the attachment context using following SQL or Oracle Doc 2369923.1

SELECT * FROM FUN_ERP_ATTACHMENT_CONTEXTS

Take AR for example, we can get the SQL defined here for the AR attachment.
The :1, :2, :3, :4 are the keys we need to provide in the web service payload when calling the attachment service:

SELECT ARC.CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_ALL ARC, RA_BATCH_SOURCES_ALL BS
WHERE
ORG_ID = (SELECT BU_ID FROM FUN_ALL_BUSINESS_UNITS_V WHERE BU_NAME = :1)
AND TRX_NUMBER = :2 AND ARC.BATCH_SOURCE_SEQ_ID = BS.BATCH_SOURCE_SEQ_ID
AND BS.SET_ID IN (FND_SetID_Utility.getSetID('AR_TRANSACTION_SOURCE','BU',ARC.ORG_ID), 0)
AND BS.NAME = :3 AND ARC.CUSTOMER_TRX_ID = NVL(:4, ARC. CUSTOMER_TRX_ID)

The import url is similar to following:
https://%5BYOUR_ERP_INSTANCE%5D:443/fscmService/ErpObjectAttachmentService

Following is an example of the payload that we use to import an attachment to an exiting AR Invoices – UserKeyB and UserKeyD needs be populate correctly for the system to find the existing Invoice. Also file contents are encoded to BASE64 string.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/"
xmlns:erp="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/">
<soapenv:Header/>
<soapenv:Body>
<typ:uploadAttachment>
<typ:entityName>RA_CUSTOMER_TRX_ALL</typ:entityName>
<typ:categoryName>CUSTOMER_TRX</typ:categoryName>
<typ:allowDuplicate>yes</typ:allowDuplicate>
<!--Zero or more repetitions:-->
<typ:attachmentRows>
<!--Optional:-->
<erp:UserKeyA>US1 Business Unit</erp:UserKeyA>
<!--Optional:-->
<erp:UserKeyB>88380</erp:UserKeyB>
<!--Optional:-->
<erp:UserKeyC>Distributed Order Orchestration</erp:UserKeyC>
<!--Optional:-->
<erp:UserKeyD>667110</erp:UserKeyD>
<!--Optional:-->
<erp:UserKeyE></erp:UserKeyE>
<!--Optional:-->
<erp:AttachmentType>File</erp:AttachmentType>
<!--Optional:-->
<erp:Title>HelloWorld</erp:Title>
<!--Optional:-->
<erp:Content>aGVsbG93b3JsZA==</erp:Content>
</typ:attachmentRows>
</typ:uploadAttachment>
</soapenv:Body>
</soapenv:Envelope>

After import, we can see following file was attached to the AR Invoice
AR_Attachment

How to get a BU id

Using following SQL can get the BU id by BU name


SELECT hao.organization_id as bu_id,haot.name,hao.business_group_id
FROM
HR_ALL_ORGANIZATION_UNITS_F hao,HR_ORGANIZATION_UNITS_F_TL haot
WHERE
hao.ORGANIZATION_ID = haot.ORGANIZATION_ID
AND hao.EFFECTIVE_START_DATE = haot.EFFECTIVE_START_DATE
AND hao.EFFECTIVE_END_DATE = haot.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN hao.EFFECTIVE_START_DATE
AND hao.EFFECTIVE_END_DATEAND haot.LANGUAGE='US'
AND haot.name like '&BU_NAME%'

Integrate with Groovy and Camel

Groovy is an easy language for its simplicity and the strong support from Java.

And Camel is a popular integration framework that works perfectly for small to medium projects.

However, setting up Camel, especially in Java is a little complex, especially when you just need to do a Demo

This leads us to think of the option of using Groovy to work with Camel, and provide some simple solution.

Following is an example of how basic Camel framework can be set up in Groovy with only 20 lines – well, I did remove empty lines to make it look small 😉

And thanks to Groovy’s Grape dependency handling, it automatically grabs all the dependency packages for us — enables you to focus on the core transformation

Following code prints out a “Hello World!” string every 3 seconds

@Grab('org.apache.camel:camel-core:2.10.0')
@Grab('org.slf4j:slf4j-simple:1.6.6')
import org.apache.camel.*
import org.apache.camel.impl.*
import org.apache.camel.builder.*
def camelContext = new DefaultCamelContext()
camelContext.addRoutes(new RouteBuilder(){
def void configure(){
from("timer://jdkTimer?period=3000")
.to("log://camelLogger?level=INFO")
.process(new Processor(){
def void process(Exchange exchange){
println("Hello World!")
}
})
}
})
camelContext.start()
addShutdownHook{ camelContext.stop() }
synchronized(this){ this.wait() }

Groovy – get file from sftp

Following code shows how to use Groovy to download a file from sftp.

It uses package “com.jcraft”

Thanks to Groovy Grab, we don’t need to manually fetch the library ourselves.


@Grab(group='com.jcraft', module='jsch', version='0.1.46')
import com.jcraft.jsch.*

java.util.Properties config = new java.util.Properties()
config.put "StrictHostKeyChecking", "no"

JSch ssh = new JSch()
Session sess = ssh.getSession "USERNAME", "HOST", PORT

sess.with{
setConfig config
setPassword "PASSWORD"
connect()
Channel chan = openChannel "sftp"
chan.connect()

ChannelSftp sftp = (ChannelSftp) chan;
sftp.get("/DOWNLOADFOLDER/DOWNLOADFILENAME","c:/LOCALFOLDER/LOCALFILENAME")
sftp.rm("/DOWNLOADFOLDER/DOWNLOADFILENAME")
chan.disconnect()
disconnect()
}

Using Groovy to do xslt transformation

Following demo code shows how we can use Groovy and xslt templates to do a simple file transform.

To transform a file, we need to do following before we can get the final result:

  1. Get a source file, here we use a simple xml as a source
  2. Define a .xls file – xslt transformation file
  3. Use Groovy to link the process and create output

Following is the code of the Groovy script that calls:

import javax.xml.transform.TransformerFactory
import javax.xml.transform.stream.StreamResult
import javax.xml.transform.stream.StreamSource

// Load xslt
def xslt = new File("c:/test/product2.xsl").getText()

// create transformer
def transformer = TransformerFactory.newInstance().newTransformer(new StreamSource(new StringReader(xslt)))

// Load xml
def xml = new File("c:/test/product.xml").getText()

// Set output file
def output = new FileOutputStream("c:/test/product_output.xml")

// perform transformation
transformer.transform(new StreamSource(new StringReader(xml)), new StreamResult(output))

Following is the .xsl file defined:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet
   version="1.0"
   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output method="xml" encoding="UTF-8" indent="yes"/>
	<xsl:template match="/">
		<xsl:for-each select="//products/product[not(.=preceding::*)]">
	<li>
				<xsl:value-of select="."/></li>
</xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

Following is the source .xml file:


<items>
  <item>
    <products>
      <product>laptop</product>
      <product>charger</product>
    </products>
  </item>
  <item>
    <products>
      <product>laptop</product>
      <product>headphones</product>
    </products>
  </item>
</items>

After running the groovy code, you got the result as following – not a correct format xml, but it’s what the transformation file requested:

<ul>
	<li>laptop</li>
	<li>charger</li>
	<li>headphones</li>
</ul>

Oracle WMS PO Receive

Oracle EBS WMS provides a powerful receiving and putaway engine to help customers to solve complex putaway scenarios.

Following test shows how to do a PO standard receive using WMS RF
– It shows how to setup a putaway rule to direct the receiving putaway

Following are the main steps

  1. Setup a putaway rule
  2. Assign the putaway rule for the specific Item in the Rules Workbench
  3. Create a PO with Standard receiving control
  4. Use WMS mobile to receive the PO and create LPN
  5. Check the Putaway tasks created for this LPN
  6. Use ‘Move Any LPN’ to trigger putaway the LPN
  7. Check the Location suggested by the system and make sure it’s expected

Detail Steps and screenshots are as following

1. Setup Putawy Rule
This is a simple rule hard coded the putaway subinventory to ‘CASE’
In real time we can use the ‘Quantity Function’ and ‘Sort Criteria’ to setup complex Putaway rules.

2.In Rules Workbench, assign the rule to Item ZY100
We can setup multiple Putaway Rules and group by Putaway Strategy in real life case

3.Create a PO with Standard Routing

4.Now we can use WMS Mobile to trigger a PO Receive
During receiving, we create an LPN ‘LPN109A’ for this receiving and input detail received SKU/Quantity, in this case it’s ZY100 with 10 quantity
In Live we will apply a pre-printed LPN Label to the pallet and scan as the LPN number or use portable printer to print out the Label real time.

5. Check the Tasks workbench in EBS, it will create a Putaway Task
If we have enabled the Task setup, the worker who can accept the same Task Type can automatically accept this Task in Mobie and work on it.
In this test we can simply use ‘Move Any LPN’ with the LPN number to trigger the Putaway

6.Use ‘Move Any LPN’ function to trigger the Putaway
In this step, system will use the Putaway rule we have setup and get the 1st available Locator in the CASE subinventory
The system suggested subinventory is CASE, which means the Rule engine runned correctly.

7. After putaway, the stock will reside in the same locator.

Omni channel SCM – SalesForce Order Transform

Overview

This is a demo of SalesForce Order Transform, it simply uses java multi-threading and activemq(in google engine cloud) to simulate an full transformation process including message inbound, transformation and outbound.

The same transformation can easily migrate to different integration platforms, etc Apache Camel, MuleSoft or Oracle Integration Cloud, or simply use the UI tool to do the same

A summarize diagram on the whole process

This is part of the omni-channel supply chain demo

Suppose we have 2 Sales Order platform, 1 is SalesForce and 1 is Shopify, we can transform different Orders to a standard format (etc, Oracle Cloud format), and then import to ERP.

In this Demo we only transformed SalesForce Order, it will be similar to Shopify Order except we uses REST API to pull down the Order

Following are the parts covered in the Demo

  1. Get Orders from SalesForce using web service (Proxy Client)
  2. Transform SalesForce order to standard sales order format using xslt template
  3. Export to a folder – we can change this to import to Oracle ERP Cloud

Test Result

We create a new Order In SalesForce, and change its Status to ‘Confirmed’, since the program only pulls down orders that is ‘Confirmed’

After some time, the program automatically pull it down, transform it and put it to local folder

Detail of the project

Project Structure

We have 3 java classes implemented runnable interface

We use ActiveMQ for this project, 2 queues were created

DemoMain.java

It starts 3 threads for the project

  1. SalesForceOrder_Input thread, it monitors the SalesForce system, pull down Orders with ‘Confirmed’ status and send to Queue “SalesForceOrders”
  2. SalesForceOrder_Transform thread, it fetch the files in Queue “SalesForceOrders”, uses xslt to transform the SalesForce Order to a standard sales order format, and send the output to Queue “StandardOrders”
  3. StandardOrder_Output thread, it fetches the Order file from Queue “StandardOrders”, and create the Order file in the file system

SalesForceOrder_Input.java

It pulls down the Order and put it to queue “SalesForceOrders”

SalesForceOrder_Transform.java

This thread transforms the file using following xslt and files

Transform Detail

Following is the standard sales order format we want to convert to

We create a xslt template based on the standard sales order format

Inside the xslt template, we do followings

  1. We defined a variable ‘LinesCount’, and used xpath to calculate the total lines after transform, note we ignored Item ‘GC1020’
  2. We defined a loop to loop each Item, but ignored ‘GC1020’, suppose it’s a price only SKU and we don’t want it in the order to warehouse
  3. We used a multiply function to calculate the LineTotal
  4. We output the ‘LinesCount’ variable

After transformation, we got following output, which is same as expectation

StandardOrder_Output.java

Fetch the transformed order from queue and save it to local system

ActiveMQHelper.java

Help to send message and get message from ActiveMQ