Building a JD Edwards Interface with Talend Open Studio

To interact with the JD Edwards ERP, developers can use the XML CallObject.  XML CallObject will send messages to JD Edwards, the payload of which is a jdeRequest XML document.  Talend can easily form the XML document using tFileInputMSXML or -- if the jdeRequest is large and relatively static -- a tXSLT.

jdeRequest is an XML document that can be sent to the JD Edwards ERP system.  Like the XML-RPC standard, the jdeRequest is a collection of functions (callMethod) and parameters (param).  There are also sections in jdeRequest for error handling.

Here is a link to the Oracle documentation on CallObject: "Understanding XML CallObject".

XML Components
There are several Talend Open Studio components for handling XML: tAdvancedFileOutputXML, tFileInputMSXML, and tFileOutputXML.  There is overlap in the functionality of these three components.  I find that tFileOutputXML works best when the XML document structure is based entirely on the input flow, where the input record is wrapped in a toplevel tag and each field makes an element.  tAdvancedFileOutputXML is useful because of its "Append mode" which builds up an XML document through multiple subjobs.  tFileInpuMSXML can take more than one input flow.

XSL
Once one of these components forms an XML document, that document can be transformed using XSLT (stylesheets).  XSLT can create HTML from and XML document, but it also assists in XML to XML translations.  To call a stylesheet from Talend Open Studio, use the tXSLT component.  This component is parameterized with an input file, an output file, and the stylesheet.  The stylesheet will process the contents of the XML input file and also will accept passed-in parameters.  These passed-in parameters are ideal for job-level settings like username or environment.

Why not just produce the XML you want using the Talend Open Studio components?

Bringing Systems Together
Two recent consulting jobs gave me an insight into how XSLT can help batch processing.  In the first job, XSLT was used to transform a company standard XML document into several variations, specific to an external interface.  The company supported an XML standard -- and data-wise, the standard could be processed by external systems -- but that standard couldn't be used by all of the systems participating in the data transfer because of format.  In this case, various XSLs were used to quickly integrate systems by adapting the company's output to what their data consumers were able to handle.

Change Control

I discovered another use of XSL when working with a JD Edwards interface: change control.

I produced a series of Talend Open Studio jobs for a client that invoked JD Edwards XML CallObjects using arguments from input files.  In this case, the XSL was a convenient way to tweak the job during integration and testing.  XSL was another place where data-fields could be re-mapped and XSL functions like upper-case() or escape-uri() could be applied without redeploying the TOS job.  It was easier to determine the version of an easy-to-read XSL than a 8Mb JAR file even with version numbering.

This is not a general solution for all problems uncovered during testing.  Fundamental problems with the TOS job will need a redeployment.  If more data is needed from the input sources, an XSL won't help you.  Although the XSL syntax will support programming logic, very complex transformations may also indicate that the TOS job needs to be re-worked.

But if a small change to an XSL moves integration along, then this strategy is helpful.  Integration and testing is a hectic time and if a test-engineer / programmer cycle is eliminated by fixing a problem, then the schedule stays on track which is particularly important if that test to programmer cycle involves multiple timezones.

jdeRequest Output

For an example on how to use XSL with Talend Open Studio, start with a data file like this.

COMPANY_NAME,COMPANY_NO,DEPT_NAME,DEPT_NO,CHARGE_AMOUNT,ITEM_CODE

"MY COMPANY, INC.","1","INFORMATION TECHNOLOGY","999","100.0","41"
"MY COMPANY, INC.","1","INFORMATION TECHNOLOGY","999","1000.0","54"

The XML document that will result (post-XSL) will look like this.  It will be submitted to the JD Edwards XML CallObject.  A jdeRequest XML document could be quite larger.

<?xml version="1.0" encoding="UTF-8"?>

<jdeRequest type="callmethod" user="" pwd="" environment="prd" role="*ALL" session="">

   <callMethod name="openBooks" app="P42101">

    <params>

     <param name="COMPANY_NO">1</param>

    </params>

   </callMethod>

   <callMethod name="addCharge" app="P42101">

    <params>

     <param name="DEPT_NO">999</param>

     <param name="ITEM_CODE">41</param>

     <param name="CHARGE_AMOUNT">100.0</param>

    </params>

   </callMethod>

   <callMethod name="addCharge" app="P42101">

    <params>

     <param name="DEPT_NO">999</param>

     <param name="ITEM_CODE">54</param>

     <param name="CHARGE_AMOUNT">1000.0</param>

    </params>

   </callMethod>

   <callMethod name="closeBooks" app="P42101">

    <params>

     <param name="COMPANY_NO">1</param>

    </params>

   </callMethod>

</jdeRequest>

How

To perform this conversion, add 3 components to a job: tFileInputDelimited, tAdvancedFileOutputXML, and tXSLT.  The tFileInputDelimited is the CSV presented in the previous section (COMPANY_NAME, etc.).  The tAdvancedFileOutputXML will produce an intermediary XML document that groups the input records into an XML structure easily converted to the target XML.  Finally, the tXSLT component will apply the stylesheet and produce the output XML file to be submitted to JD Edwards.

Job Applying XSL to XML Doc

The tFileInputDelimited uses the following.  Since quotes are surrounding the data elements -- there is a comma in "MY COMPANYNAME, INC." -- both Escape char and Text enclosure CSV options are set to "\"".

Schema Used in tFileInputDelimited

The mapping used in the tAdvancedFileOutputXML groups the input records which are denormalized.  COMPANY_NO (and COMPANY_NAME) are repeated for each record of the input.  This schema will support submitting a group of charge entries under a single company.

Schema for Intermediary XML Document

The tAdvancedFileOutputXML does not produce the final XML document.  That's the job of the stylesheet which is applied with the XSLT.  The configuration of the XSLT points to several files: input, output, and XSL.  There is also a a parameter that will be made available to the XSL language that is coming from the TOS context "environment".  This is a global value that is maintained outside of the source CSV data.

tXSLT with an xsl:param Definition

XSL

This document is produced by the tAdvancedFileOutputXML.  The repeating records (COMPANY_NO) have been factored out in the way in which the JD Edwards function call will be made.

<CHARGES COMPANY_NAME="MY COMPANY, INC." COMPANY_NO="1">

   <CHARGE>

    <DEPT_NAME>INFORMATION TECHNOLOGY</DEPT_NAME>

    <DEPT_NO>999</DEPT_NO>

    <CHARGE_AMOUNT>100.0</CHARGE_AMOUNT>

    <ITEM_CODE>41</ITEM_CODE>

   </CHARGE>

   <CHARGE>

    <DEPT_NAME>INFORMATION TECHNOLOGY</DEPT_NAME>

    <DEPT_NO>999</DEPT_NO>

    <CHARGE_AMOUNT>1000.0</CHARGE_AMOUNT>

    <ITEM_CODE>54</ITEM_CODE>

   </CHARGE>

</CHARGES>

Finally, the stylesheet used to process the above XML into the jdeRequest document uses 2 template matches.  The first match is for a pair of begin/end calls.  There is one pair for a company.  The second template match will take the repeating CHARGE elements and invoke the addCharge function.

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:param name="ENVIRONMENT" />

<xsl:template match="/CHARGES">

   <jdeRequest type='callmethod' user='' pwd='' environment='{$ENVIRONMENT}' role='*ALL' session=''>

   <callMethod name='openBooks' app='P42101'>

   <params>

   <param name='COMPANY_NO'><xsl:value-of select="@COMPANY_NO" /></param>

   </params>

   </callMethod>

   <xsl:apply-templates />

   <callMethod name='closeBooks' app='P42101'>

   <params>

   <param name='COMPANY_NO'><xsl:value-of select="@COMPANY_NO" /></param>

   </params>

   </callMethod>

   </jdeRequest>

</xsl:template>

<xsl:template match="CHARGE">

   <callMethod name='addCharge' app='P42101'>

   <params>

   <param name='DEPT_NO'><xsl:value-of select="DEPT_NO/text()" /></param>

   <param name='ITEM_CODE'><xsl:value-of select="ITEM_CODE/text()" /></param>

   <param name='CHARGE_AMOUNT'><xsl:value-of select="CHARGE_AMOUNT/text()" /></param>

   </params>

   </callMethod>

</xsl:template>

</xsl:stylesheet>

The stylesheet pulls {$ENVIRONMENT} from the context variable defined in the TOS context.  COMPANY_NO is an attribute on the outer CHARGES element.  DEPT_NO, ITEM_CODE, and CHARGE_AMOUNT are subelements of CHARGE.

Benefits

This example showed how to use an XSL stylesheet with Talend Open Studio.  The benefits of incorporating XSL into your TOS job are

• Giving external interfaces special XML,
• Fixing easy-to-solve problems uncovered in integration and testing, and
• Rapid development

Specially-presented XML and integration and testing were mentioned in previous sections.  Rapid development is a benefit if you're using a graphical XML editor like Liquid Technologies XML Studio.  The source XML, straight from the data plus groupings, can be brought into the tool and a stylesheet can be repeatedly applied to the XML and walked-through using a debugger.