Performing Data Translation

Use data translation to modify message content rather than structure, although you can also make local structural changes. It’s most appropriate when the sending and receiving systems use different field values, or different combinations of fields and their values, to represent the same information.

Following is a sample scenario:

• Application A transmits customer names in four fields: Title, First, Middle, and Last.

• Application B uses two fields: Last and First. It doesn’t use a title, but includes the middle name as part of the First field.

• Application C uses only one field: AccountID.

Clearly the representation used by one application won’t be understood by either of the other two. PeopleSoft Integration Broker can apply a transform program to translate each of these representations into a version appropriate to the receiving application.

One Integration Broker node can store in its codeset repository the equivalent fields and values used by another node. When it receives a message from the other node containing a customer name, it can use its codeset repository to translate the information into the form it prefers. It can likewise reverse the process for messages outbound to the other node.

For a given integration, you can allocate the responsibility for performing data translation in different ways. You can distribute the translation activity among the participating nodes, or you can designate one Integration Broker node to do all the data translation as a hub, whether the messages are inbound, outbound, or being redirected between the other nodes. Using a single node, if possible, can reduce the need for duplicating repository data.

Use the Codeset Groups page (IB_CODESETGRPVAL) in the Codeset Groups component (IB_CODESETGROUP) to define codeset groups. To access the Codeset Groups page, select PeopleTools > Integration Broker > Integration Setup > Codesets > Codeset Groups.

Image: Codeset Groups page

This example illustrates the Codeset Groups page.

To define a codeset group:

1. Add a new value, enter a codeset group name, and click Add.

   Enter a name that reflects a common quality of the nodes you plan to assign to this group; for example, the name of the software they all use to manage shipping.

   The Codeset Group page appears.

2. Add a new row.

3. Enter a match name.

   This is the name of a data field that might be part of a message sent by a node belonging to this codeset group. You don’t have to create an entry for every field, just the ones that you’ll need to translate or use for reference in a translation.

4. Enter a match value.

   This is one of the possible values of the data field represented by the match name.

5. Repeat steps 2 through 4 for each significant name/value pair that you expect to appear in a message.

   This doesn’t need to be all possible values of all of the message fields, just the names and values you expect to require translation.

6. Assign one or more nodes to this codeset group.

   Every initial and result node involved in a data translation must belong to a codeset group. You must assign each participating node to an appropriate codeset group by an entry in its node properties.

   Note: The assignment for each node is required only in the database of the node performing the data translation. This translating node needn’t be either the source or the target. Multiple nodes that represent data the same way may be assigned to the same codeset group.

Use the Codesets page (IB_CODESET) in the Codesets component. (IB_CODESET). Select PeopleTools > Integration Broker > Integration Setup > Codesets > Codesets to access the Codeset page.

Image: Codesets page

This page illustrates the Codesets page.

To define a codeset:

1. Add a new value and enter a codeset group name on which to base this codeset.

2. Enter a codeset name and click Add.

   Enter a name that reflects the purpose of this codeset; for example, SAP_SHIP_METHOD, to translate the representation of a shipping method in a message. The Codesets page appears.

3. Add a new row.

4. Select a match name from the set defined for the associated codeset group.

5. Select a match value from the set defined for the selected match name.

   Note: You can leave the value blank. If so, you should do the same for each match name in this codeset, in addition to any other values you select for them. A combination consisting of all blank values is treated as a wild card by PeopleSoft Integration Broker, which enables it to respond to unanticipated values specified in your translation program with default behavior that you define.

6. Repeat steps 3 through 5 to enter all the name/value pairs that may need to be matched.

   The name/value pairs you select should encompass only the possible value combinations that your translation program needs to match for a single translation. You define a different codeset for each translation based on this codeset group.

Use the Codeset Values page (IB_CODESETVAL) in the Codeset Values component (IB_CODESETVAL). Select PeopleTools > Integration Broker > Integration Setup > Codesets > Codeset Values to access the Codeset Values page.

Image: Codeset Values page

This example illustrates the Codeset Values page.

To define codeset values:

1. Add a new value and select a codeset group name for the From group.

   This is the codeset group to which the initial node belongs.

2. Select a codeset name from the codesets based on the group you selected.

   This is the codeset whose match name/match value permutations you wish to match.

3. Select a codeset group name for the To group.

   This is the codeset group to which the result node belongs.

4. Click Add.

   The Codeset Values page appears. The upper grid contains the selected codeset’s match name/match value pairs, and the lower grid contains the return values you specify. Each permutation that you define has its own Description field, which can help you distinguish between permutations that may be subtly different from each other.

   Note: To configure an existing codeset values definition, enter its From group, codeset name and To group on the search page.

5. Select check boxes to define a permutation of match name/match value pairs.

   For each match name, you can select at most one match value.

   A permutation consisting of all blank values serves as a wild card; it matches any input value combination that isn’t matched by any other permutation. However, a permutation with some blank and some non-blank values works differently; it requires the names with blank values to actually match blank field values in the input data.

   Note: You’ll generally define only permutations that you expect the input data to contain, but make sure you allow for unforeseen match values by including permutations with all blank values. You can then specify default return values for those permutations. With a large number of match names in the codeset, you can make sure to catch all unforeseen combinations by defining a permutation with all blank match values.

6. In the Code Set Values grid, enter a return name, and a return value for that name.

   You can use any return name you want, because only your codeset translation program refers to it. Your translation program can use the return value as a field value or as a node name in the output data.

   Important! The set of return names you define must be identical for all of the permutations of match name/match value pairs for the current codeset in this definition. Your translation program invokes the codeset and applies the return names from this definition, but it can’t anticipate which permutations will be matched, or which actual return values it’s applying — just the return names.

7. (Optional.) In the Code Set Values grid, add a new row and repeat step 6.

   Add as many return name/return value pairs as you need for your output based on the current permutation. If the permutation is matched in the input data, the code set values you define for that permutation become available for you to call and insert in the output data.

8. (Optional.) At the top level of this page, add a new row and repeat steps 5 through 7.

   This inserts a new permutation row, in which you can define a different permutation of match name/match value pairs that you expect for the current codeset. For each permutation, you’ll define a separate, independent set of codeset values.

PeopleSoft provides two Data Mover scripts that you can use to import and export codesets between databases:

• CODESET_DELETE_IMPORT.DMS

  Use this script to purge and then import codeset data into a target database.

• CODESET_EXPORT.DMS

  Use this script to export codeset data from a source database to a target database.

Before you delete a codeset, you must delete any codeset values associated with it.

Once you’ve defined the match name/match value permutations for a given codeset and defined the return values for those permutations, you can write an XSLT translation program that invokes the codeset and applies the return values.

An XSLT translation is based on XSLT transformation structure. However, although you could combine both tasks into a single program, it’s better to keep them separate for easier understanding and maintenance.

<psft_function name="codeset" codesetname="PS_SAP_PO_01"
 source="SAP_02" dest="PSFT_03">...</psft_function>

<value name="PS_RET_01" select="." createNodeFromValue="yes"><xsl:value-of
 select="CreditCard"/></value>

The following example of XSLT data translation presents an example input message, the XSLT translation program, and the resulting output message.

<?xml version="1.0"?>
<PurchaseOrder>
   <Destination>
      <Address>123 Vine Street</Address>
      <Contact>
         <Name>Joe Smith</Name>
      </Contact>
      <Delivery type="ground">
         <Business>FedEx</Business>
      </Delivery>
   </Destination>
   <Payment>
      <CreditCard cardtype="visa">9999-9999-9999-9999</CreditCard>
   </Payment>
   <LineItems count="2">
      <Li locale="en_us" number="1">
         <Quantity>15</Quantity>
         <ProductName>pencil</ProductName>
         <UOM>box</UOM>
      </Li>
      <Li locale="en_us" number="2">
         <Quantity>10</Quantity>
         <ProductName>paper</ProductName>
         <UOM>large box</UOM>
      </Li>
   </LineItems>
</PurchaseOrder>

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
   <xsl:template match="PurchaseOrder">
      <po>
         <xsl:apply-templates/>
      </po>
   </xsl:template>
 
   <xsl:template match="Destination">
      <dest>
         <address><xsl:value-of select="Address"/></address>
         <name><xsl:value-of select="Contact/Name"/></name>
         <delivery>
            <type>
               <psft_function name="codeset" codesetname="PS_SAP_PO_03" dest="PSFT_03"><parm name="type"><xsl:value-of select="Delivery/@type"/>
                  </parm><value name="PS_RET_01" select="."/></psft_function>
            </type>
            <carrier><psft_function name="codeset" codesetname="PS_SAP_PO_03"source="SAP_03"><parm name="Business"><xsl:value-of select="Delivery/Business"/></parm><value name="PS_RET_01" select="."/></psft_function>
            </carrier>
         </delivery>
      </dest>
   </xsl:template>
 
   <xsl:template match="Payment">
      <payment><psft_function name="codeset" codesetname="PS_SAP_PO_02"><parm name="cardtype"><xsl:value-of select="CreditCard/@cardtype"/></parm><value name="PS_RET_01" select="."createNodeFromValue="yes"><xsl:value-of select="CreditCard"/></value></psft_function>
      </payment>
   </xsl:template>
 
   <xsl:template match="Li">
      <li><xsl:attribute name="id"><xsl:value-of select="@number"/></xsl:attribute>
         <name><xsl:value-of select="ProductName"/></name>
         <qty><xsl:value-of select="Quantity"/></qty>
         <uom><psft_function name="codeset" codesetname="PS_SAP_PO_01"><parm name="locale"><xsl:value-of select="@locale"/></parm><parm name="uom"><xsl:value-of select="UOM"/></parm><value name="PS_RET_01" select="."/><value name="PS_RET_02" select="../type" createIfDNE="yes"/></psft_function>
         </uom>
      </li>
   </xsl:template>
</xsl:stylesheet>

<po>
   <li id="">
      <name>pencil</name>
      <qty>15</qty>
      <uom>Carton</uom>
      <type>Bic</type>
   </li>
   <li id="">
      <name>paper</name>
      <qty>10</qty>
      <uom>Box</uom>
      <type>Bic</type>
   </li>
   <dest>
      <address>123 Vine Street</address>
      <name>Joe Smith</name>
      <delivery>
         <type>Ground</type>
         <carrier>Federal Express</carrier>
      </delivery>
   </dest>
   <payment>
      <VISA>4024-9920-9892-8982</VISA>
   </payment>
</po>

Although XSLT is the recommended language for using the codeset repository to translate message data, you can use PeopleCode for this purpose as well. Because XSLT works only with XML DOM-compliant message data, you must use PeopleCode if the message you’re translating contains non-XML data, including formats like comma separated values (CSV).

Once you’ve defined the match name/match value permutations for a codeset with respect to a given target codeset group and defined the return values for those permutations, you can write a PeopleCode translation program that invokes that codeset and applies the return values.

<?xml version="1.0"?>
<Header>
  <LANGUAGE_CODE>en_us</LANGUAGE_CODE>
  <STATUS_CODE>0</STATUS_CODE>
</Header>

/* Get the data from the AE Runtime */
Local TransformData &incomingData = %TransformData;

/* Set a temp object to contain the incoming document */
Local XmlDoc &tempDoc = &incomingData.XmlDoc;

/* Find the Language and status codes value*/
Local string &langCode = &tempDoc.DocumentElement.FindNode("LANGUAGE_CODE").
   Node Value;
Local string &statusCode = &tempDoc.DocumentElement.FindNode("STATUS_CODE").
   Node Value;

/* Create an array to hold the name value pairs */
Local array of array of string &inNameValuePairsAry;

/* Load the array with some values */
&inNameValuePairsAry = CreateArray(CreateArray("LANG", &langCode),
 CreateArray("STATUS", &statusCode));

/* Find the codeset values */
&outAry = FindCodeSetValues("STATUS_CHANGE", &inNameValuePairsAry,
 &incomingData.SourceNode, &incomingData.DestNode);

/* Create the new output doc */
If &tempDoc.ParseXmlString("<?xml version=""1.0""?><NewHeader/>") Then

   /* Make sure something was returned */
   If &outAry.Len > 0 Then

      /* Create the new Status Code Node */
      Local XmlNode &statusNode = &tempDoc.DocumentElement.AddElement("STATUS");

      /* Since this is a 2D array, get the Return Value*/
      &statusNode.NodeValue = &outAry [1][2];
   End-If;
End-If;

<?xml version="1.0"?>
<NewHeader>
   <STATUS>Open</STATUS>
</NewHeader>