8 Creating Cross References

8.1 Introduction to Cross References

Many a time, when you create or update objects in one application, you also want to propagate the changes to another application. For example, when a new customer is created in a SAP application, you might want to create a new entry for the same customer in your Oracle E-Business Suite application named as EBS.

However, the applications that you are integrating could be using different entities to represent the same information. For example, for a new customer in a SAP application, a new row is inserted in its Customer database with a unique identifier such as SAP_001. When the same information is propagated to an Oracle E-Business Suite application and a Siebel application, a new row should be inserted with different identifiers, such as EBS_1001 and SBL001. In such cases, you need some kind of functionality to map these identifiers with each other so that they could be interpreted by different applications to be referring to the same entity. This can be done by using cross references tables. Table 8-1 shows a cross reference table containing information about customer identifiers in different applications.

The identifier mapping is also required when information about a customer is updated in one application and the changes need to be propagated in other applications also. You can also integrate different identifiers by using a common value integration pattern, which maps to all identifiers in a cross reference table. For example, you can add one more column Common to the cross reference table shown in Table 8-1. The updated cross reference table would appear as shown in Table 8-2.

Figure 8-1 shows how you can use the common value integration pattern to map identifiers in different applications.

Figure 8-1 Common Value Integration Pattern Example

Description of "Figure 8-1 Common Value Integration Pattern Example"

A cross reference table consists of following two parts, metadata and the actual data. The metadata is created by using the cross reference command line utilities and is stored in the repository as an XML file. The actual data is stored in the database.

You can use a cross reference table to look up column values at run time. However, before using a cross reference to look up a particular value, you need to populate it at run time. This can de done by using the cross reference XPath functions. The XPath functions enable you to populate a cross reference, perform lookups, and delete a column value. These XPath functions can be used in the Expression builder dialog box to create an expression or in the XSLT Mapper dialog box to create transformations.

The Expression builder dialog box is displayed when you click the Invoke Expression Builder icon in the routing rules panel. Figure 8-2 shows how you can select the cross reference functions in the Expression builder dialog box.

Figure 8-2 Expression Builder Dialog Box with Cross Reference Functions

Description of "Figure 8-2 Expression Builder Dialog Box with Cross Reference Functions"

The XSLT Mapper dialog box is displayed when you create an XSL file to transform data from one XML schema to another. Figure 8-3 shows how you can select the cross reference functions in the XSLT Mapper dialog box.

Figure 8-3 XSLT Mapper Dialog Box with Cross Reference Functions

Description of "Figure 8-3 XSLT Mapper Dialog Box with Cross Reference Functions"

8.2 Introduction to the Cross Reference Command-Line Utility

Oracle Enterprise Service Bus provides a set of command line utilities that you can use for cross reference administration. The command line utilities are xreftool, xrefimport, and xrefexport.

The xrefimport and xrefexport utility enables you to import and export cross reference metadata and data.

The xreftool utility enables you to create cross reference metadata such as creating cross reference table and columns. However, you cannot populate the cross reference tables by using the xreftool commands. You can also use the xreftool commands to modify, and delete cross reference tables. To use the xreftool utility, perform the following steps:

1. Create two variables named OC4J_USERNAME and OC4J_PASSWORD as environment variables.

2. Start the command prompt.

3. At the prompt, type the following command:

   cd <ORACLE_HOME>\integration\esb\bin
   

4. At the prompt, type the following command:

   xreftool -shell
   

   This command starts the cross reference shell where you can run xreftool commands.

Table 8-3 lists various xreftool commands.

8.3 Creating, Modifying, and Deleting Cross Reference Table

You can use the xreftool utility to create, modify, and delete cross reference tables. To create a cross reference table, use the following command in the cross reference shell:

createTable TableName

For example, the createTable customers command creates a cross reference table named customers:

To view a list of all cross reference tables present in the repository, you can use the following xreftool command:

listTables

deleteTable TABLENAME

deleteTable orders

8.4 Populating Cross Reference Tables

A cross reference table needs to be populated at run time before being used. This can be done by using the following XPath extension functions:

• xref:populateXRefRow Function

• xref:populateXRefRow1M Function

xref:populateXRefRow(xrefTableName as string, xrefReferenceColumnName as string,
 xrefReferenceValue as string, xrefColumnName as string, xrefValue as string, mode
 as string) as string

xref:populateXRefRow1M(xrefTableName as string, xrefReferenceColumnName as string,
 xrefReferenceValue as string, xrefColumnName as string, xrefValue as string, mode
 as string) as string

8.5 Looking Up Cross Reference Tables

After populating the cross reference table, you can use it to look up for a value. This can be done by using the following XPath extension functions:

• xref:lookupXRef Function

• xref:lookupXRef1M Function

xref:lookupXRef("customers","SAP","SAP_001", "Common", true())

xref:lookupXRef(xrefTableName as string, xrefReferenceColumnName as string,
 xrefReferenceValue as string, xrefColumnName as string, needAnException as
 boolean) as string

xref:lookupXRef1M("customers","EBS","EBS_1001", "Common", true())

xref:lookupXRef1M(xrefTableName as string, xrefReferenceColumnName as string,
 xrefReferenceValue as string, xrefColumnName as string, needAnException as
 boolean) as node-set

8.6 Deleting Cross Reference Table Values

You can use the xref:markForDelete function to delete a value in a cross reference table. The value in the column is marked as deleted. This function returns true if deletion was successful, else it returns false.

A cross reference table row should have at least two mappings. Therefore, if you have only two mappings in a row and you mark one value for deletion, then the value in the other column is also deleted.

Any column value marked for deletion is treated as if the value does not exist. Therefore, you can populate the same column with the xref:populateXRefRow function in the ADD mode.

However, using the column value marked for deletion as a reference value in the LINK mode of the xref:populateXRefRow function, would raise an error.

The syntax for the xref:markForDelete function is as follows:

xref:markForDelete(xrefTableName as string, xrefColumnName as string,
xrefValueToDelete as string) return as boolean

Parameters

• xrefTableName: The name of the cross reference table.

• xrefColumnName: The name of the column from which you want to delete a value.

• xrefValueToDelete: The value to be deleted.

Exception Reasons

An exception can occur due to the following reasons:

• The cross reference table with the given name is not found.

• The specified column name is not found.

• The specified value is empty.

• The specified value is not found in the column.

• Multiple values are found.

Perform the following steps to delete a value from a cross reference table column:

1. In the XSLT Mapper dialog box, expand the trees in the Source and Target panes.

2. Drag and drop the source element to the target element.

3. In the Component Palette, click the down arrow list and then select Advanced Functions.

4. Drag and drop markForDelete onto the line that connects the source object to the target object.

   A markForDelete icon appears on the connecting line.

5. Double-click the markForDelete icon.

   The Edit Function – markForDelete dialog box is displayed, as shown in Figure 8-8.

   Figure 8-8 Edit Function – markForDelete Dialog Box

   
   Description of "Figure 8-8 Edit Function – markForDelete Dialog Box"
   
   

6. Specify the following values for the fields in the Edit Function – markForDelete dialog box:

   1. In the tableName field, enter the name of the cross reference table.

   2. In the columnName field, enter the name of the column.

   3. In the value field, you can manually enter a value or press Ctrl-Space to launch the XPath Building Assistant. Press the Up and Down arrow keys to locate an object in the list and press Enter to select that object.

   A populated Edit Function – markForDelete dialog box is shown in Figure 8-9.

   Figure 8-9 Populated Edit Function – markForDelete Dialog Box

   
   Description of "Figure 8-9 Populated Edit Function – markForDelete Dialog Box"
   
   

7. Click OK.

8.7 Importing and Exporting Cross References

You can import and export the cross reference tables by using the xrefimport and xrefexport utilities. However, before using the xrefimport and xrefexport utilities, you need to create the following environment variables:

• DB_URL: Contains the connection string of the database.

  Example: jdbc:oracle:thin:@stapm21.us.oracle.com:1521:orcl

• DB_USER: The user name of the schema where the cross reference tables are created.

  Example: DB_PASSWORD=oraesb

• DB_PASSWORD: The password associated with the user specified in the DB_USER variable.

  Example: DB_USER=oraesb

The following sections explain how to import and export cross reference tables:

• Exporting Cross Reference Tables

• Importing Cross Reference Tables

xrefexport -file FILENAME -table TABLENAME

xrefimport -file FILENAME [-mode <ignore | overwrite>] [ -generate <columnName>]

8.8 Schema Definition (XSD) File for Cross References

Example 8-1 shows the cross reference XSD file. All imported cross reference XML files are validated against this schema definition file. All functions in the schema definition file should be in the following namespace:

http://www.oracle.com/XSL/Transform/java/oracle.tip.xref.xpath.XRefXPathFunctions

<?xml version="1.0" encoding="UTF-8" ?>
<schema xmlns="http://www.w3.org/2001/XMLSchema" 
        targetNamespace="http://xmlns.oracle.com/xref"
        xmlns:tns="http://xmlns.oracle.com/xref" elementFormDefault="qualified">
  <element name="xref" type="tns:xrefType"/>
  <complexType name="xrefType">
    <sequence>
      <element name="table">
        <complexType>
          <sequence>
            <element name="columns" type="tns:columnsType" minOccurs="0"
                     maxOccurs="1"/>
            <element name="rows" type="tns:rowsType" maxOccurs="1"
                     minOccurs="0"/>
          </sequence>
          <attribute name="name" use="required">
            <simpleType>
             <restriction base="string">
              <minLength value="1"/>
             </restriction>
            </simpleType>
          </attribute>
        </complexType>
      </element>
    </sequence>
  </complexType>
  
  <complexType name="columnsType">
    <sequence>
      <element name="column" minOccurs="1" maxOccurs="unbounded">
        <complexType>
          <attribute name="name" use="required">
            <simpleType>
             <restriction base="string">
               <minLength value="1"/>
             </restriction>
            </simpleType>
          </attribute>
        </complexType>
      </element>
    </sequence>
  </complexType>
  
  <complexType name="rowsType">
    <sequence>
      <element name="row" minOccurs="1" maxOccurs="unbounded">
        <complexType>
          <sequence>
            <element name="cell" minOccurs="1" maxOccurs="unbounded">
              <complexType>
                <simpleContent>
                  <extension base="string">
                    <attribute name="colName" use="required">
                      <simpleType>
                        <restriction base="string">
                           <minLength value="1"/>
                        </restriction>
                      </simpleType>
                    </attribute>
                  </extension>
                </simpleContent>
              </complexType>           
            </element>
          </sequence>
        </complexType>
      </element>
    </sequence>
  </complexType>
</schema>