Integration Broker Again - FTPTARGET Connector

For my current client, I had de develop an interface between Peoplesoft and a provider.  A huge XML file containing benefit data must be generated, validated with a XSD Schema and sent using the SFTP protocol.

This process is a little bit more complicated that a simple XML generated and sent using PutAttachement global function.  The first option I though about was to develop a Java Library to be able to validate XML with XSD Schema, to be used as a JavaObject, and then try to send the file using PutAttachment.  I think this solution is too hard to implement and to maintain.

I asked Oracle for a XSD validation global function, and they redirected me to Integration Broker.  I also remembered it was possible to attach (or generate) a schema on a message.  I also knew that we could use other protocol than PSFTTARGET.

 So I started by adding a new external node.  I chose to use the FTPTARGET connector; there is a tool I find useful : the password is encrypted and it is not displayed in clear, so it is a little bit more secure.

Then, I created a Non-Rowset message, because the XML generated is not based on Peoplesoft Tables; there is also some information included to Rowset Messages that we do not want, that are used when transferring to another Peoplesoft instance.  The message definition is almost empty, it only contains the XSD to validate the XML.

I also had to create a queue, a service and a service operation, and configure the routing, but this is trivial, so I will not describe that part.

After the configuration was done, I tested it to be sure that messages are going trough.

One of my requirements was to be able to rename the filename.  I tried several ways, to add a connector property and try to modify it using Peoplecode, before sending the file, but I was not able.  I finally found a workaround, by selecting all FTPTARGET properties and add them back to the IBConnectorInfo object associated to the object using the AddConnectorProperties method (&MSG.IBInfo.IBConnectorInfo.AddConnectorProperties); I was then able to add the Filename property with the correct value (filename).

&MSG = CreateMessage(Operation.JB_XSD_MSG_3);

/* Set ConnectorName and Connector ClassName */
&MSG.IBInfo.IBConnectorInfo.ConnectorName = "FTPTARGET";
&MSG.IBInfo.IBConnectorInfo.ConnectorClassName = "FTPTargetConnector";


&sql1 = CreateSQL(&strQuery);
&aany = CreateArrayAny();

While &sql1.Fetch(&aany)
   If &aany [1] = "HEADER" Then
      &nRet = &MSG.IBInfo.IBConnectorInfo.AddConnectorProperties(&aany [2], &aany [3], %Header);
      &nRet = &MSG.IBInfo.IBConnectorInfo.AddConnectorProperties(&aany [2], &aany [3], %Property);

&nRet = &MSG.IBInfo.IBConnectorInfo.AddConnectorProperties("FILENAME", "xsd_ok_" | DateTimeToLocalizedString(%Datetime, "yyyyMMddhhmmss") | ".XML", %Property);

To generate easily XML, I wanted to use a table structure, such as File Layouts or Rowset.  I finally opted for Rowsets, because it seemed to be not to complicated and enough flexible for me.  Each element (nodes) of the XML file will correspond to a Record (Table or View) and its attributes (leafs) will be associated to a Field.

First I had to create the rowset structure.  I followed this article : 

&rs0 = CreateRowset(Record.BEN_LOADER);
&rs1 = CreateRowset(Record.BEN_MEMBER);
&rs2 = CreateRowset(Record.ADDRESSES);
&rs12 = CreateRowset(&rs1, &rs2);
&rs = CreateRowset(&rs0, &rs12);

For &i = 1 To &rs.ActiveRowCount
   &rs(&i).GetRowset(Scroll.BEN_MEMBER).Fill("WHERE POLICY_NBR = :1", &rs(&i).GetRecord(Record.BEN_LOADER).GetField(Field.POLICY_NBR).Value);
   For &j = 1 To &rs(&i).GetRowset(Scroll.BEN_MEMBER).ActiveRowCount
      &rs(&i).GetRowset(Scroll.BEN_MEMBER).GetRow(&j).GetRowset(Scroll.ADDRESSES).Fill("Where EMPLID = :1", &rs(&i).GetRowset(Scroll.BEN_MEMBER).GetRow(&j).GetRecord(Record.BEN_MEMBER).GetField(Field.EMPLID).Value);

I tried to find a simple way to generate XML from a rowset to a view.  I thought about creating a function to generate one, but I found that we can create a XMLDoc object from a rowset using the CopyRowset method.

&xmlRequestDoc = CreateXmlDoc("");
&ret = &xmlRequestDoc.CopyRowset(&rs, "JB_MSG_XSD_3", "VERSION_2");
JB_TST_IB_FTP.HTMLAREA = &xmlRequestDoc.GenFormattedXmlString();

The generated XMLDocument tries to match the structure of the message in parameters, but it seems it is not able when the message is of type "non-rowset".  So the result was not the expected XML, it looked like a Rowset-based message, with unwated tags (see highlighted below)

<?xml version="1.0"?>
    <PSCAMA class="R">
      <LANGUAGE_CD type="CHAR"/>
      <AUDIT_ACTN type="CHAR"/>
      <BASE_LANGUAGE_CD type="CHAR"/>
      <MSG_SEQ_FLG type="CHAR"/>
      <PUBLISH_RULE_ID type="CHAR"/>
      <MSGNODENAME type="CHAR"/>
      <RECORD1 class="R">
        <RECORD2 class="R">
        <RECORD2 class="R">

Also, the records and fields names are not the tags we want to be present into the file when sending it; anyway, it would not pass the XSD validation step.  I tried to find a delivered way to modify the generated XML so it would be "sendable".  I found the TransformEx and TransformExCache global functions.  Both function are used to apply a XSL to a XML, in order to transform it into a XML with a different structure.  The only difference wetween the two functions is the nature of the parameters.

     &TransformedxmlRequestDoc = TransformExCache(&xmlRequestDoc, "/home/devmgr/xsl1.xsl", "xsl1");
catch Exception &E
   MessageBox(0, "", 0, 0, "Caught exception: " | &E.ToString());

The result is a XMLDoc with the wanted structure.  It is ready to be validated by the XSD :

Local string &retValidateMessage;
&retValidateMessage = %IntBroker.ValidateMsgData("JB_MSG_XSD_3", "VERSION_2", &TransformedxmlRequestDoc.GenFormattedXmlString());

If the mesage is valid, we can publish it.  To be able to modify conector properties, I had to ConnectorRequest method instead of Publish.

If &retValidateMessage = "Validation Successful" Then
   &MSG2 = %IntBroker.ConnectorRequest(&MSG);

No comments:

Post a Comment