1. Developing SOA Applications with Oracle SOA Suite
  2. Sharing Functionality Across Service Components
  3. Working with Cross References

43 Working with Cross References

This chapter describes how to use the cross referencing feature of Oracle SOA Suite to associate identifiers for equivalent entities created in different applications. It includes a reference of the XRef functions you can use to populate, view, and maintain entries in the cross reference tables.

This chapter includes the following sections:

43.1 Introduction to Cross References

Cross references enable you to dynamically map values for equivalent entities created in different applications.

Note:

The cross referencing feature enables you to dynamically integrate values between applications, whereas domain value maps enable you to specify values at design time and edit values at runtime. For more information about domain value maps, see Working with Domain Value Maps and Using Oracle SOA Composer with Domain Value Maps.

When you create or update objects in one application, you may also want to propagate the changes to other applications. For example, when a new customer is created in an SAP application, you may want to create an entry for the same customer in your Oracle E-Business Suite application named EBS. However, the applications that you are integrating may be using different entities to represent the same information. For example, for each new customer in an 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, the new row should be inserted with different identifiers such as EBS_1001 and SBL001. In such cases, you need some type of functionality to map these identifiers with each other so that they can be interpreted by different applications to be referring to the same entity. This can be done by using cross references.

43.2 Introduction to Cross Reference Tables

Cross references are stored in the form of tables. Table 43-1 shows a cross reference table containing information about customer identifiers in different applications.

Table 43-1 Cross Reference Table Sample

SAP EBS SBL

SAP_001

EBS_1001

SBL001

SAP_002

EBS_1002

SBL002

The identifier mapping is also required when information about a customer is updated in one application and the changes must be propagated to other applications. You can 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 named Common to the cross reference table shown in Table 43-1. The updated cross reference table then appears, as shown in Table 43-2.

Table 43-2 Cross Reference Table with Common Column

SAP EBS SBL Common

SAP_001

EBS_1001

SBL001

CM001

SAP_002

EBS_1002

SBL002

CM002

Figure 43-1 shows how you can use common value integration patterns to map identifiers in different applications.

Figure 43-1 Common Value Integration Pattern Example

Description of Figure 43-1 follows
Description of "Figure 43-1 Common Value Integration Pattern Example"

A cross reference table consists of two parts: metadata and actual data. The metadata is saved as the .xref file created in Oracle JDeveloper, and is stored in the Metadata Services (MDS) repository as an XML file. By default, the actual data is stored in the XREF_DATA table of the database in the SOA Infrastructure database schema. You can also generate a custom database table for each cross reference entity. The database table depends on the metadata of the cross reference entity.

Consider the following two cross reference entities:

  • ORDER with cross reference columns SIEBEL, COMMON, and EBS, as shown in Table 43-3

  • CUSTOMER with cross reference columns EBS, COMMON, and PORTAL, as shown in Table 43-4

Table 43-3 ORDER Table

Column Name SIEBEL COMMON EBS

Column Value

SBL_101

COM_100

EBS_002

Column Value

COM_110

EBS_012

Table 43-4 CUSTOMER Table

Column Name EBS COMMON PORTAL

Column Value

EBS_201

COM_200

P2002

If you chose to save all the runtime data in one generic table, then the data is stored in the XREF_DATA table, as shown in Table 43-5.

Table 43-5 XREF_DATA Table

XREF_TABLE_NAME XREF_COLUMN_NAME ROW_NUMBER VALUE IS_DELETED

ORDER

SIEBEL

100012345

SBL_101

N

ORDER

COMMON

100012345

COM_100

N

ORDER

EBS

100012345

EBS_002

N

ORDER

COMMON

110012345

COM_110

N

ORDER

EBS

110012345

EBS_012

N

CUSTOMER

EBS

200212345

EBS_201

N

CUSTOMER

COMMON

200212345

COM_200

N

CUSTOMER

PORTAL

200212345

P2002

N

This approach has the following advantages:

  • The process of adding, removing, and modifying the columns of the cross reference entities is simple.

  • The process of creating and deleting cross reference entities from an application is straightforward.

However, this approach has the following disadvantages:

  • A large number of rows are generated in the database because each cross reference cell is mapped to a different row in the database. This reduces the performance of the queries.

  • In the generic table, the data for the columns XREF_TABLE_NAME and XREF_COLUMN_NAME is repeated across a large number of rows.

To overcome these problems, you can generate a custom database table for each cross reference entity. The custom database tables depend on the metadata of the cross reference entities. For example, for the XREF_ORDER table and XREF_CUSTOMER table, you can generate the custom database tables shown in Table 43-6 and Table 43-7.

Table 43-6 XREF_ORDER Table

ROW_ID SIEBEL COMMON EBS

100012345

SBL_101

COM_100

EBS_002

110012345

COM_110

EBS_012

Table 43-7 XREF_CUSTOMER Table

ROW_ID EBS COMMON PORTAL

200212345

EBS_201

COM_200

P2002

This approach requires you to execute Data Definition Language (DDL) scripts to generate the custom database tables. For more information about custom database tables, see How to Create Custom Database Tables.

43.3 Oracle Data Integrator Support for Cross Referencing

Oracle Data Integrator (ODI) achieves data integration through an E-LT (extract, load, transform) model. You can use ODI to help with your cross-referencing needs. ODI provides three Knowledge Modules for handling SOA cross references that perform the following functions: Populate the cross-reference table, create a common ID for the target table, push the common ID and the source primary key to the cross-reference table, and create and push a unique row number that creates the cross reference between the source primary key and the common ID. With the modules, you can create an integration interface that both loads a target table from several source tables and handles cross-references between one of the sources and the target.

For more information about ODI and cross referencing, see Oracle SOA Suite Cross References in Connectivity and Knowledge Modules Guide for Oracle Data Integrator Developer's Guide.

43.4 Creating and Modifying Cross Reference Tables

You can create cross references tables in a SOA composite application and then use it with a BPEL process service component or an Oracle Mediator service component during transformations.

Note:

You can also create cross-reference tables in Service Bus projects and use them in message flows during transformations.

43.4.1 How to Create Cross Reference Metadata

To create cross reference metadata:

  1. In Oracle JDeveloper, select the SOA project in which you want to create the cross reference.
  2. Right-click the project and select New.

    The New Gallery dialog is displayed.

  3. Select SOA Tier from the Categories section, and then select Transformations.
  4. Select Cross Reference(XREF) from the Items section.
  5. Click OK.

    The Create Cross Reference(XREF) File dialog is displayed.

  6. In the File Name field, specify the name of the cross reference file. For example, specify Customer.

    A cross reference name is used to uniquely identify a cross reference table. Two cross reference tables cannot have same name in the cross reference repository. The cross reference file name is the name of the cross reference table with an extension of .xref.

  7. In the Description field, enter a description for the cross reference. For example:
    Cross reference of Customer identifiers.
  8. In the End System fields, enter the end system names.

    The end systems map to the cross reference columns in a cross reference table. For example, you can change the first end system name to SAP and the second end system name to EBS. Each end system name must be unique within a cross reference

    A sample Create Cross Reference(XREF) File dialog is displayed in Figure 43-2.

    Figure 43-2 Create Cross Reference(XREF) File Dialog

    Description of Figure 43-2 follows
    Description of "Figure 43-2 Create Cross Reference(XREF) File Dialog"
  9. Click OK.

    The Cross Reference Editor is displayed, as shown in Figure 43-3. You can use this editor to modify the cross reference.

    Figure 43-3 Cross Reference Editor

    Description of Figure 43-3 follows
    Description of "Figure 43-3 Cross Reference Editor"

43.4.2 What Happens When You Create a Cross Reference

A file with extension .xref gets created and appears in the Applications window. All .xref files are based on the schema definition (XSD) file shown in the following example: 

<?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="description" type="string" minOccurs="0"
                     maxOccurs="1"/>
            <element name="columns" type="tns:columnsType" minOccurs="0"
                     maxOccurs="1"/>
            <element name="rows" type="tns:rowsType" maxOccurs="1"
                     minOccurs="0"/>
          </sequence>
          <attribute name="name" type="string" use="required"/>
        </complexType>
      </element>
    </sequence>
  </complexType>
  
  <complexType name="columnsType">
    <sequence>
      <element name="column" minOccurs="1" maxOccurs="unbounded">
        <complexType>
          <attribute name="name" type="string" use="required"/>
        </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>
                <attribute name="colName" type="string" use="required"/>
              </complexType>
            </element>
          </sequence>
        </complexType>
      </element>
    </sequence>
  </complexType>
</schema>

43.4.3 How to Create Custom Database Tables

As mentioned previously, all the runtime data by default gets stored in the XREF_DATA table. If you want to create custom database tables, then perform the following steps.

To create custom database tables:

  1. From the Optimize list, select Yes in the Cross Reference Editor.

    The name of the custom database table to be generated is displayed in the Table Name field, as shown in Figure 43-4.

    Figure 43-4 Generating Custom Database Tables

    Description of Figure 43-4 follows
    Description of "Figure 43-4 Generating Custom Database Tables"

    The Table Name field is editable and you can change the name of the custom table. The custom database table name should be prefixed with xref_. If you do not prefix your table name with xref_, then while generating the table, you receive the following error message: 

    Table name should begin with 'xref_' and cannot be 'xref_data' or 
'xref_deleted_data' which are reserved table names for XREF runtime.
  2. Click Generate Table DDL. The Optimize XREF dialog is displayed.
  3. Select the Generate Drop DDL check box to drop the table and associated indexes, if a table with the same name already exists. If you select this option and click Run, then the Running Drop DDL Warning dialog is displayed with the following message:
    Running the Drop DDL will remove the table and indexes, do you want to
continue?
  4. Click Run. The Run Table DDL dialog is displayed.
  5. From the Connection list, select the database connection to use.

    If there is no available connection, then click Create a new database connection to open the Create Database Connection dialog, as shown in Figure 43-5. If you want to edit an existing connection, then select the connection and click Edit selected database connection to open the Edit Database Connection dialog.

    Figure 43-5 Create Database Connection Dialog

    Description of Figure 43-5 follows
    Description of "Figure 43-5 Create Database Connection Dialog"
  6. Enter all the required details and click OK. The Connection list of the Run Table DDL dialog is now populated.

    Note:

    Create the database table in the soainfra schema of the database.

  7. Click OK on the Run Table DDL dialog to run the DDL script.

    The Table DDL Run Results dialog displays the execution status of your DDL scripts.

For custom database tables, two additional attributes, namely mode and dbtable, are added to the schema definition mentioned in What Happens When You Create a Cross Reference. They are added for the table element in the following way: 

 <attribute name="mode" type="string" default="generic" />
 <attribute name="dbtable" type="string" default="xref_data"/>

43.4.4 How to Add an End System to a Cross Reference Table

To add an end system to a cross reference table:

  1. Click Add.

    A new row is added.

  2. Double-click the newly-added row.
  3. Enter the end system name. For example, SBL.

43.5 Populating Cross Reference Tables

You can create a cross reference table in a SOA composite application in Oracle JDeveloper and then use it to look up column values at runtime. However, before using a cross reference to look up a particular value, you must populate it at runtime. You can use the cross reference XPath functions to populate the cross-reference tables. The XPath functions enable you to populate a cross reference column, perform lookups, and delete a column value. These XPath functions can be used in the Expression Builder dialog to create an expression or in the XSLT Mapper to create transformations. For example, you can use the xref:populateXRefRow function to populate a cross reference column with a single value and the xref:populateXRefRow1M function to populate a cross reference column with multiple values.

You can access the Expression Builder dialog through an assign activity, an XSL transformation, or the filtering functionality of a BPEL process service component or an Oracle Mediator service component. Figure 43-6 shows how you can select the cross reference functions in the Expression Builder dialog.

Figure 43-6 Expression Builder Dialog with Cross Reference Functions

Description of Figure 43-6 follows
Description of "Figure 43-6 Expression Builder Dialog with Cross Reference Functions"

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

Figure 43-7 XSLT Mapper Dialog with Cross Reference Functions

Description of Figure 43-7 follows
Description of "Figure 43-7 XSLT Mapper Dialog with Cross Reference Functions"

A cross reference table must be populated at runtime before using it. By default, the data is stored in the XREF_DATA table under the SOA Infrastructure database schema. You can use the xref:populateXRefRow function to populate a cross reference column with a single value and the xref:populateXRefRow1M function to populate a cross reference column with multiple values.

Note:

You can also store the data in a different database schema by configuring a data source in the following way:

  • The JNDI name of the data source should be jdbc/xref.

  • The ORACLE_HOME/rcu/integration/soainfra/sql/xref/createschema_xref_oracle.sql file should be loaded to create the XREF_DATA table in this data source.

43.5.1 About the xref:populateXRefRow Function

The xref:populateXRefRow function populates a cross reference column with a single value. The xref:populateXRefRow function returns a string value, which is the cross reference value being populated. For example, as shown in Table 43-8, the Order table has the following columns: EBS, Common, and SBL with values E100, 100, and SBL_001 respectively.

Table 43-8 Cross Reference Table with Single Column Values

EBS Common SBL

E100

100

SBL_001

Note:

If you find you have concurrency issues when using this function, you can also use the populateLookupXRefRow function. The populateLookupXRefRow function should only be used in cases where simultaneous updates are being made, resulting in unique constraint violations. This function is described under About the xref:populateLookupXRefRow Function.

The syntax of the xref:populateXRefRow function is shown in the following example: 

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

Parameters

  • xrefLocation: The cross reference table URI.

  • xrefReferenceColumnName: The name of the reference column.

  • xrefReferenceValue: The value corresponding to the reference column name.

  • xrefColumnName: The name of the column to be populated.

  • xrefValue: The value to be populated in the column.

  • mode: The mode in which the xref:populateXRefRow function populates the column. You can specify any of the following values: ADD, LINK, or UPDATE. Table 43-9 describes these modes.

Table 43-9 xref:populateXRefRow Function Modes

Mode Description Exception Reasons

ADD

Adds the reference value and the value to be added.

For example, the following mode:

xref:populateXRefRow("customers.xref"
,"EBS","EBS100", "Common","CM001",
"ADD")

Adds the reference value EBS100 in the ESB reference column and the value CM001 in the Common column.

Exceptions can occur for the following reasons:

  • The specified cross reference table is not found.

  • The specified columns are not found.

  • The values provided are empty.

  • The value being added is not unique across that column for that table.

  • The column for that row already contains a value.

  • The reference value exists.

LINK

Adds the cross reference value corresponding to the existing reference value.

For example, the following mode:

xref:populateXRefRow("customers.xref"
,"Common","CM001","SBL","SBL_
001","LINK")

Links the value CM001 in the Common column to the SBL_001 value in the SBL column.

Exceptions can occur for the following reasons:

  • The specified cross reference table is not found.

  • The specified columns are not found.

  • The values provided are empty.

  • The reference value is not found.

  • The value being linked exists in that column for that table.

UPDATE

Updates the cross reference value corresponding to an existing reference column-value pair.

For example, the following mode:

xref:populateXRefRow("customers.xref"
,"SBL","SBL_001", "SBL","SBL_
1001","UPDATE")

Updates the value SBL_001 in the SBL column to the value SBL_1001.

Exceptions can occur for the following reasons:

  • The specified cross reference table is not found.

  • The specified columns are not found.

  • The values provided are empty.

  • Multiple values are found for the column being updated.

  • The reference value is not found.

  • The column for that row does not have a value.

Note:

The mode parameter values are case-sensitive and should be specified in upper case only, as shown in Table 43-9.

Table 43-10 describes the xref:populateXRefRow function modes and exception conditions for these modes.

Table 43-10 xref:populateXRefRow Function Results with Different Modes

Mode Reference Value Value to be Added Result

ADD

Absent

Present

Present

Absent

Absent

Present

Success

Exception

Exception

LINK

Absent

Present

Present

Absent

Absent

Present

Exception

Success

Exception

UPDATE

Absent

Present

Present

Absent

Absent

Present

Exception

Exception

Success

43.5.2 About the xref:populateLookupXRefRow Function

Like the xref:populateXRefRow function, the xref:populateLookupXRefRow function populates a cross reference column with a single value. Unlike the xref:populateXRefRow function, the xref:populateLookupXRefRow function does not throw a unique constraint violation error when records with the same ID are added simultaneously. Instead, it behaves as a lookup and returns the existing source value that caused the error and does not stop the processing flow. Use this function to resolve any concurrency issues that could arise when using the xref:populateXRefRow function.

The xref:populateLookupXRefRow function returns a string value, which is the cross reference value being populated or, with a unique constraint violation, the cross reference value that was already populated by the first committed thread. For example, as shown in Table 43-8, the XREF_CUSTOMER_DATA table has the following columns: EBS, Common, and SBL. The xref:populateLookupXRefRow function is invoked by two threads in parallel with following values:

  • Thread One: xref: populateLookupXRefRow ("default/xref/example.xref", "EBS", "EBS100", "Common" "CM001", "ADD")

  • Thread Two: xref: populateLookupXRefRow ("default/xref/example.xref", "EBS", "EBS100", "Common" "CM002", "ADD")

The table is populated as shown in Table 43-11. Since thread one is committed first, thread two returns "CM001" to the caller.

Table 43-11 Cross Reference Table Populated by xref:populateLookupXRefRow

EBS Common SBL

EBS100

CM001

The syntax of the xref:populateLookupXRefRow function is shown in the following example: 

xref:populateLookupXRefRow(xrefMetadataURI as string, xrefReferenceColumnName as
 string, xrefReferenceValue as string, xrefColumnName as string, xrefValue as
 string, mode as string) as string

Parameters

  • xrefMetadataURI: The cross reference table URI.

  • xrefReferenceColumnName: The name of the reference column.

  • xrefReferenceValue: The value corresponding to the reference column name.

  • xrefColumnName: The name of the column to be populated.

  • xrefValue: The value to be populated in the column.

  • mode: The mode in which the xref:populateXRefRow function populates the column. You can specify ADD or LINK. Table 43-10 describes these modes and exception conditions for the modes.

Note:

The mode parameter values are case-sensitive and should be specified in upper case only.

Table 43-12 xref:populateLookupXRefRow Function Results with Different Modes

Mode Reference Value Value to be Added Result

ADD

Absent

Present

Present

Absent

Absent

Present

Success

Exception (Success only when Exception is Unique constraint violation)

Exception (Success only when Exception is Unique constraint violation)

LINK

Absent

Present

Present

Absent

Absent

Present

Exception

Success

Exception

Usage Notes

  • When using a custom table approach, you must add the primary constraint on the columns that must be unique in the cross-reference table. Using Table 43-11 as an example, the SQL statement is similar to the following: 

    alter table xref_customer_data add constraint xref_vnx_data_pk
 primary key (common, ebs);

    Populate the primary constraint columns first and then populate the remaining columns in subsequent calls.

  • This function should not be used for inserting cross references for primary objects, since this could mask data inconsistency issues. Only use the function for secondary objects to a main dependent object. For example, do not use the function to determine whether an account already exists when creating customer accounts; but do use it if the addresses in those customer accounts are being synchronized.

43.5.3 About the xref:populateXRefRow1M Function

Two values in an end system can correspond to a single value in another system. In such a scenario, you should use the xref:populateXRefRow1M function to populate a cross reference column with a value. For example, as shown in Table 43-13, the SAP_001 and SAP_0011 values refer to one value of the EBS and SBL applications. To populate columns such as SAP, you can use the xref:populateXRefRow1M function.

Table 43-13 Cross Reference Table with Multiple Column Values

SAP EBS SBL

SAP_001

SAP_0011

EBS_1001

SBL001

SAP_002

EBS_1002

SBL002

The syntax of the xref:populateXRefRow1M function is shown in the following example: 

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

Parameters

  • xrefLocation: The cross reference URI.

  • xrefReferenceColumnName: The name of the reference column.

  • xrefReferenceValue: The value corresponding to the reference column name.

  • xrefColumnName: The name of the column to be populated.

  • xrefValue: The value to be populated in the column.

  • mode: The mode in which the xref:populateXRefRow function populates the column. You can specify either of the following values: ADD or LINK. Table 43-14 describes these modes:

Table 43-14 xref:populateXRefRow1M Function Modes

Mode Description Exception Reasons

ADD

Adds the reference value and the value to be added.

For example, the following mode:

xref:populateXRefRow1M("customers.xref","
EBS","EBS_1002", "SAP","SAP_0011","ADD")

Adds the reference value EBS_1002 in the reference column EBS and the value SAP_0011 in the SAP column.

Exceptions can occur for the following reasons:

  • The specified cross reference table is not found.

  • The specified columns are not found.

  • The values provided are empty.

  • The value being added is not unique across that column for that table.

  • The reference value exists.

LINK

Adds the cross reference value corresponding to the existing reference value.

For example, the following mode:

xref:populateXRefRow1M("customers.xref","
EBS","EBS_1002", "SAP","SAP_002","LINK")

Links the value SAP_002 in the SAP column to the EBS_1002 value in the EBS column.

Exceptions can occur for the following reasons:

  • The specified cross reference table is not found.

  • The specified columns are not found.

  • The values provided are empty.

  • The reference value is not found.

  • The value being added is not unique across the column for that table.

Table 43-15 describes the xref:populateXRefRow1M function modes and exception conditions for these modes.

Table 43-15 xref:populateXRefRow1M Function Results with Different Modes

Mode Reference Value Value to be Added Result

ADD

Absent

Present

Present

Absent

Absent

Present

Success

Exception

Exception

LINK

Absent

Present

Present

Absent

Absent

Present

Exception

Success

Exception

43.5.4 How to Populate a Column of a Cross Reference Table

To populate a column of a cross reference table:

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

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

  3. In the Components window, select Advanced.

  4. Select XREF Functions.

  5. Drag and drop the populateXRefRow function to the line that connects the source object to the target object.

    A populateXRefRow icon appears on the connecting line.

  6. Double-click the populateXRefRow icon.

    The Edit Function – populateXRefRow dialog is displayed, as shown in Figure 43-8.

    Figure 43-8 Edit Function – populateXRefRow Dialog

    Description of Figure 43-8 follows
    Description of "Figure 43-8 Edit Function – populateXRefRow Dialog"

  7. Specify the following values for the fields in the Edit Function – populateXRefRow dialog:

    1. In the xrefLocation field, enter the location URI of the cross reference file.

      Click Browse to the right of the xrefLocation field to select the cross reference file. You can select an already-deployed cross reference from MDS and also from a shared location in MDS using the Resource Palette.

    2. In the referenceColumnName field, enter the name of the cross reference column.

      Click Browse to the right of the referenceColumnName field to select a column name from the columns defined for the cross reference you previously selected.

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

    4. In the columnName field, enter the name of the cross reference column.

      Click the Browse icon to the right of the columnName field to select a column name from the columns defined for the cross reference you previously selected.

    5. In the value field, you can manually enter a value or press Ctrl-Space to launch the XPath Building Assistant.

    6. In the mode field, enter a mode in which you want to populate the cross reference table column. For example, enter ADD.

      You can also click Browse to select a mode. The Select Populate Mode dialog is displayed from which you can select a mode.

  8. Click OK.

    A populated Edit Function – populateXRefRow dialog is shown in Figure 43-9.

    Figure 43-9 Populated Edit Function – populateXRefRow Dialog

    Description of Figure 43-9 follows
    Description of "Figure 43-9 Populated Edit Function – populateXRefRow Dialog"

43.6 Looking Up Cross Reference Tables

After populating the cross reference table, you can use it to look up a value. The xref:lookupXRef and xref:lookupXRef1M functions enable you to look up a cross reference for single and multiple values, respectively.

43.6.1 About the xref:lookupXRef Function

You can use the xref:lookupXRef function to look up a cross reference column for a value that corresponds to a value in a reference column. For example, the following function looks up the Common column of the cross reference tables described in Table 43-2 for a value corresponding to the SAP_001 value in the SAP column. 

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

The syntax of the xref:lookupXRef function is shown in the following example: 

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

Parameters

  • xrefLocation: The cross reference URI.

  • xrefReferenceColumnName: The name of the reference column.

  • xrefReferenceValue: The value corresponding to the reference column name.

  • xrefColumnName: The name of the column to be looked up for the value.

  • needAnException: When the value is set to true, an exception is thrown if the value is not found. Otherwise, an empty value is returned.

Exception Reasons

At runtime, an exception can occur for the following reasons:

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

  • The specified column names are not found.

  • The specified reference value is empty.

  • Multiple values are found.

43.6.2 About the xref:lookupXRef1M Function

You can use the xref:lookupXRef1M function to look up a cross reference column for multiple values corresponding to a value in a reference column. The xref:lookupXRef1M function returns a node-set containing multiple nodes. Each node in the node-set contains a value.

For example, the following function looks up the SAP column of Table 43-13 for multiple values corresponding to the EBS_1001 value in the EBS column: 

xref:lookupXRef1M("customers.xref","EBS","EBS_1001","SAP",true())

The syntax of the xref:lookupXRefRow1M function is shown in the following example: 

xref:lookupXRef1M(xrefLocation as String, xrefReferenceColumnName as String,
 xrefReferenceValue as String, xrefColumnName as String, needAnException as
 boolean) as node-set

Parameters

  • xrefLocation: The cross reference URI.

  • xrefReferenceColumnName: The name of the reference column.

  • xrefReferenceValue: The value corresponding to the reference column name.

  • xrefColumnName: The name of the column to be looked up for the value.

  • needAnException: If this value is set to true, an exception is thrown when the referenced value is not found. Otherwise, an empty node-set is returned.

Example of the xref:lookupXRefRow1M Function

Consider the Order table shown in Table 43-16 with the following three columns: Siebel, Billing1, and Billing2.

Table 43-16 Order Table

Siebel Billing1 Billing2

100

101

102

110

111

112

For 1:1 mapping, the xref:lookupPopulatedColumns("Order","Siebel","100","false") method returns the values shown in the following example: 

<column name="BILLING1">101</column>
<column name="BILLING2">102</column>

In this case, both the columns, Billing1 and Billing2, are populated.

For 1:M mapping, the xref:lookupPopulatedColumns("Order","Siebel","110","false") method returns the values shown in the following example: 

<column name="BILLING2">111</column>
<column name="BILLING2">112</column>

In this case, Billing1 is not populated.

Exception Reasons

An exception can occur for the following reasons:

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

  • The specified column names are not found.

  • The specified reference value is empty.

43.6.3 About the xref:lookupPopulatedColumns Function

You can use the xref:lookupPopulatedColumns function to look up all the populated columns for a given cross reference table, a cross reference column, and a value. The xref:lookupPopulatedColumns function returns a node-set with each node containing a column name and the corresponding value.

The syntax of the xref:LookupPopulatedColumns function is shown in the following example: 

xref:LookupPopulatedColumns(xrefTableName as String,xrefColumnName as
 String,xrefValue as String,needAnException as boolean)as node-set

Parameters

  • xrefTableName: The name of the reference table.

  • xrefColumnName: The name of the reference column.

  • xrefValue: The value corresponding to the reference column name.

  • needAnException: If this value is set to true, then an exception is thrown when no value is found in the referenced column. Otherwise, an empty node-set is returned.

Exception Reasons

An exception can occur for the following reasons:

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

  • The specified column names are not found.

  • The specified reference value is empty.

43.6.4 How to Look Up a Cross Reference Table for a Value

To look up a cross reference table column:

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

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

  3. In the Components window, select Advanced.

  4. Select XREF Functions.

  5. Drag and drop the lookupXRef function to the line that connects the source object to the target object.

    A lookupXRef icon appears on the connecting line.

  6. Double-click the lookupXRef icon.

    The Edit Function – lookupXRef dialog is displayed, as shown in Figure 43-10.

    Figure 43-10 Edit Function – lookupXRef Dialog

    Description of Figure 43-10 follows
    Description of "Figure 43-10 Edit Function – lookupXRef Dialog"

  7. Specify the following values for the fields in the Edit Function – lookupXRef dialog:

    1. In the xrefLocation field, enter the location URI of the cross reference file.

      Click Browse to the right of the xrefLocation field to select the cross reference file. You can select an already deployed cross reference from MDS and also from a shared location in MDS by using the Resource Palette.

    2. In the referenceColumnName field, enter the name of the cross reference column.

      Click Browse to the right of the referenceColumnName field to select a column name from the columns defined for the cross reference you previously selected.

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

    4. In the columnName field, enter the name of the cross reference column.

      Click Browse to the right of the columnName field to select a column name from the columns defined for the cross reference you previously selected.

    5. Click Browse to the right of needException field. The Need Exception dialog is displayed. Select Yes to raise an exception if no value is found. Otherwise, select No.

  8. Click OK.

    A populated Edit Function – lookupXRef dialog is shown in Figure 43-11.

    Figure 43-11 Populated Edit Function – lookupXRef Dialog

    Description of Figure 43-11 follows
    Description of "Figure 43-11 Populated Edit Function – lookupXRef Dialog"

43.7 Deleting a Cross Reference Table Value

You can use the xref:markForDelete function to delete a value in a cross reference table. The row, containing the column value passed to the function, is deleted from the XREF_DATA table and moved to the XREF_DELETED_DATA table. This function returns true if the deletion is successful. Otherwise, it returns false.

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

The syntax for the xref:markForDelete function is shown in the following example: 

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

Parameters

  • xrefTableName: The cross reference table name.

  • xrefColumnName: The name of the column that contains the value to be deleted.

  • xrefValueToDelete: The value to be deleted.

Exception Reasons

An exception can occur for 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.

43.7.1 How to Delete a Cross Reference Table Value

To delete a cross reference table value:

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

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

  3. In the Components window, select Advanced.

  4. Select XREF Functions.

  5. Drag and drop the markForDelete function to the line that connects the source object to the target object.

    A markForDelete icon appears on the connecting line.

  6. Double-click the markForDelete icon.

    The Edit Function – markForDelete dialog is displayed, as shown in Figure 43-12.

    Figure 43-12 Edit Function – markForDelete Dialog

    Description of Figure 43-12 follows
    Description of "Figure 43-12 Edit Function – markForDelete Dialog"

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

    1. In the xrefLocation field, enter the location URI of the cross reference file.

      Click the Search icon to the right of the xrefLocation field to select the cross reference file. You can select an already deployed cross reference from MDS and also from a shared location in MDS by using the Resource Palette.

    2. In the columnName field, enter the name of cross reference table column.

      Click the Search icon to the right of the columnName field to select a column name from the columns defined for the cross reference you previously selected.

    3. In the Value field, manually enter a value or press Ctrl-Space to launch the XPath Building Assistant. Press the up and down keys to locate an object in the list and press Enter to select that object.

    A populated Edit Function – markForDelete dialog is shown in Figure 43-13.

    Figure 43-13 Populated Edit Function – markForDelete Dialog

    Description of Figure 43-13 follows
    Description of "Figure 43-13 Populated Edit Function – markForDelete Dialog"

  8. Click OK.

43.8 Creating and Running the Cross Reference Use Case

This cross reference use case implements an integration scenario between Oracle EBS, SAP, and Siebel instances. In this use case, when an insert, update, or delete operation is performed on the SAP_01 table, the corresponding data is inserted or updated in the EBS and SBL tables. Figure 43-14 provides an overview of this use case.

Figure 43-14 XrefCustApp Use Case in SOA Composite Editor

Description of Figure 43-14 follows
Description of "Figure 43-14 XrefCustApp Use Case in SOA Composite Editor"

43.8.1 How to Create the Use Case

This section provides the design-time tasks for creating, building, and deploying your SOA Composite application. These tasks should be performed in the order in which they are presented.

43.8.1.1 Task 1: How to Configure the Oracle Database and Database Adapter

To configure the Oracle database and database adapter:

  1. You need the SCOTT database account with password TIGER for this use case. You must ensure that the SCOTT account is unlocked.

    You can log in as SYSDBA and then run the setup_user.sql script available in the XrefOrderApp1M/sql directory to unlock the account.

  2. Run the create_schema.sql script available in the XrefOrderApp1M/sql directory to create the tables required for this use case.
  3. Run the create_app_procedure.sql script available in the XrefOrderApp1M/sql directory to create a procedure that simulates the various applications participating in this integration.
  4. Run the createschema_xref_oracle.sql script available in the OH/rcu/integration/soainfra/sql/xref/ directory to create a cross reference table to store runtime cross reference data.
  5. Copy the ra.xml and weblogic-ra.xml files from $BEAHOME/META-INF to the newly created directory called META-INF on your computer.
  6. Edit the weblogic-ra.xml file available in the $BEAHOME/META-INF directory as follows:

    • Modify the property to xADataSourceName as follows: 

      <property>
 <name>xADataSourceName</name>
 <value>jdbc/DBConnection1</value>
</property>

    • Modify the jndi-name as follows: 

      <jndi-name> eis/DB/DBConnection1</jndi-name>

    This sample uses eis/DB/DBConnection1 to poll the SAP table for new messages and to connect to the procedure that simulates Oracle EBS and Siebel instances.

  7. Package the ra.xml and weblogic-ra.xml files as a RAR file and deploy the RAR file by using Oracle WebLogic Server Administration Console.
  8. Create a data source using the Oracle WebLogic Server Administration Console with the following values:

    • jndi-name=jdbc/DBConnection1

    • user=scott

    • password=tiger

    • url=jdbc:oracle:thin:@host:port:service

    • connection-factory factory-class=oracle.jdbc.pool.OracleDataSource

  9. Create a data source using the Oracle WebLogic Server Administration Console with the following values:

    • jndi-name=jdbc/xref

    • user=scott

    • password=tiger

    • url=jdbc:oracle:thin:@host:port:service

    • connection-factory factory-class=oracle.jdbc.pool.OracleDataSource

43.8.1.2 Task 2: How to Create an Oracle JDeveloper Application and a Project

To create an Oracle JDeveloper application and a project:

  1. In Oracle JDeveloper, click File and select New.

    The New Gallery dialog appears.

  2. In the New Gallery, expand the General node, and select the Applications category.
  3. In the Items list, select SOA Application and click OK.

    The Create SOA Application wizard appears.

  4. In the Application Name field, enter XrefCustApp, and then click Next.

    The Name your SOA project page appears.

  5. In the Project Name field, enter XrefCustApp and click Next.

    The Configure SOA settings page appears.

  6. From the Composite Template list, select Empty Composite and then click Finish.

    The Applications window of Oracle JDeveloper is updated with the new application and project and the SOA Composite Editor contains a blank composite.

  7. From the File menu, select Save All.
43.8.1.3 Task 3: How to Create a Cross Reference

After creating an application and a project for the use case, you must create a cross reference table.

To create a cross reference table:

  1. In the Applications window, right-click the XrefCustApp project and select New.
  2. In the New Gallery dialog, expand the SOA Tier node, and then select the Transformations category.
  3. In the Items list, select Cross Reference(XREF) and click OK.

    The Create Cross Reference(XREF) File dialog is displayed.

  4. In the File Name field, enter customer.xref.
  5. In the End System fields, enter SAP_01 and EBS_i76.
  6. Click OK.

    The Cross Reference Editor is displayed.

  7. Click Add.

    A new row is added.

  8. Enter SBL_78 as the end system name in the newly added row.
  9. Click Add and enter Common as the end system name.

    The Cross Reference Editor appears, as shown in Figure 43-15.

    Figure 43-15 Customer Cross Reference

    Description of Figure 43-15 follows
    Description of "Figure 43-15 Customer Cross Reference"
  10. From the File menu, select Save All and close the Cross Reference Editor.
43.8.1.4 Task 4: How to Create a Database Adapter Service

To create a database adapter service:

  1. In the Oracle JDeveloper Components window, select SOA.
  2. Select Database and drag it to the Exposed Services swimlane.

    The Adapter Configuration wizard Welcome page is displayed.

  3. Click Next.

    The Service Name page is displayed.

  4. In the Service Name field, enter SAP.
  5. Click Next.

    The Service Connection page is displayed.

  6. In the Application Connection field, select DBConnection1.
  7. In the JNDI Name field, enter eis/DB/DBConnection1.
  8. Click Next.

    The Operation Type page is displayed.

  9. Select Poll for New or Changed Records in a Table and click Next.

    The Select Table page is displayed.

  10. Click Import Tables.

    The Import Tables dialog is displayed.

  11. Select Scott from Schema.
  12. In the Name Filter field, enter %SAP% and click Query.

    The Available field is populated with SAP_01 table name.

  13. Double-click SAP_01.

    The selected field is populated with SAP_01.

  14. Click OK.

    The Select Table page now contains the SAP_01 table.

  15. Select SAP_01 and click Next.

    The Define Primary Key page is displayed.

  16. Select ID as the primary key and click Next.

    The Relationships page is displayed.

  17. Click Next.

    The Attribute Filtering page is displayed.

  18. Click Next.

    The After Read page is displayed.

  19. Select Update a Field in the [SAP_01] Table (Logical Delete) and click Next.

    The Logical Delete page is displayed.

  20. In the Logical Delete field, select LOGICAL_DEL.
  21. In the Read Value field, enter Y.
  22. In the Unread Value field, enter N.

    Figure 43-16 shows the Logical Delete page of the Adapter Configuration wizard.

    Figure 43-16 Logical Delete Page: Adapter Configuration Wizard

    Description of Figure 43-16 follows
    Description of "Figure 43-16 Logical Delete Page: Adapter Configuration Wizard"
  23. Click Next.

    The Polling Options page is displayed.

  24. Click Next.

    The Define Selection Criteria page is displayed.

  25. Click Next.

    The Finish page is displayed.

  26. Click Finish.

    A database adapter service named SAP is created, as shown in Figure 43-17.

    Figure 43-17 SAP Database Adapter Service in SOA Composite Editor

    Description of Figure 43-17 follows
    Description of "Figure 43-17 SAP Database Adapter Service in SOA Composite Editor"
  27. From the File menu, select Save All.
43.8.1.5 Task 5: How to Create EBS and SBL External References

To create EBS and SBL external references:

  1. In the Components window, select SOA.
  2. Select Database Adapter and drag it to the External References swimlane.

    The Adapter Configuration wizard Welcome page is displayed.

  3. Click Next.

    The Service Name page is displayed.

  4. In the Service Name field, enter EBS.
  5. Click Next.

    The Service Connection page is displayed.

  6. In the Application Connection field, select DBConnection1.
  7. In the JNDI Name field, enter eis/DB/DBConnection1.
  8. Click Next.

    The Operation Type page is displayed.

  9. Select Call a Stored Procedure or Function and click Next.

    The Specify Stored Procedure page is displayed.

  10. Select Scott from Schema.
  11. Click Browse.

    The Stored Procedures dialog is displayed.

  12. Select POPULATE_APP_INSTANCE, as shown in Figure 43-18.

    Figure 43-18 Stored Procedure Dialog

    Description of Figure 43-18 follows
    Description of "Figure 43-18 Stored Procedure Dialog"
  13. Click OK.

    The Specify Stored Procedure page appears, as shown in Figure 43-19.

    Figure 43-19 Specify Stored Procedure Page of Adapter Configuration Wizard

    Description of Figure 43-19 follows
    Description of "Figure 43-19 Specify Stored Procedure Page of Adapter Configuration Wizard"
  14. Click Next.

    The Finish page is displayed.

  15. Click Finish.

    Figure 43-20 shows the EBS reference in the SOA Composite Editor.

    Figure 43-20 EBS Reference in SOA Composite Editor

    Description of Figure 43-20 follows
    Description of "Figure 43-20 EBS Reference in SOA Composite Editor"
  16. From the File menu, select Save All.
  17. Repeat Step 2 through Step 16 to create another external reference named SBL.

    After completing this task, the SOA Composite Editor appears, as shown in Figure 43-21.

    Figure 43-21 SBL Reference in SOA Composite Editor

    Description of Figure 43-21 follows
    Description of "Figure 43-21 SBL Reference in SOA Composite Editor"
43.8.1.6 Task 6: How to Create the Logger File Adapter External Reference

To create the Logger file adapter external reference:

  1. From the Components window, select SOA.
  2. Select File Adapter and drag it to the External References swimlane.

    The Adapter Configuration wizard Welcome page is displayed.

  3. Click Next.

    The Service Name page is displayed.

  4. In the Service Name field, enter Logger.
  5. Click Next.

    The Operation page is displayed.

  6. In the Operation Type field, select Write File.
  7. Click Next.

    The File Configuration page is displayed.

  8. In the Directory for Outgoing Files (physical path) field, enter the name of the directory in which you want to write the files.
  9. In the File Naming Convention field, enter output.xml and click Next.

    The Messages page is displayed.

  10. Click Search.

    The Type Chooser dialog is displayed.

  11. Navigate to Type Explorer > Project Schema Files > SCOTT_POPULATE_APP_INSTANCE.xsd, and then select OutputParameters.
  12. Click OK.
  13. Click Next.

    The Finish page is displayed.

  14. Click Finish.

    Figure 43-22 shows the Logger reference in the SOA Composite Editor.

    Figure 43-22 Logger Reference in SOA Composite Editor

    Description of Figure 43-22 follows
    Description of "Figure 43-22 Logger Reference in SOA Composite Editor"
  15. From the File menu. select Save All.
43.8.1.7 Task 7: How to Create an Oracle Mediator Service Component

To create an Oracle Mediator service component:

  1. Drag and drop a Mediator icon from the Components window to the Components section of the SOA Composite Editor.

    The Create Mediator dialog is displayed.

  2. From the Template list, select Define Interface Later.
  3. Click OK.

    An Oracle Mediator with name Mediator1 is created.

  4. Connect the SAP service to the Mediator1, as shown in Figure 43-23.

    Figure 43-23 SAP Service Connected to Mediator1

    Description of Figure 43-23 follows
    Description of "Figure 43-23 SAP Service Connected to Mediator1"
  5. From the File menu, select Save All.
  6. Drag and drop another Mediator icon from the Components window to the Components section of the SOA Composite Editor.

    The Create Mediator dialog is displayed.

  7. From the Template list, select Interface Definition From WSDL.
  8. Deselect Create Composite Service with SOAP Bindings.
  9. To the right of the WSDL File field, click Find Existing WSDLs.
  10. Navigate to and then select the Common.wsdl file. The Common.wsdl file is available in the Samples folder.
  11. Click OK.
  12. Click OK.

    An Oracle Mediator with name Common is created.

43.8.1.8 Task 8: How to Specify Routing Rules for an Oracle Mediator Service Component

You must specify routing rules for the following operations:

  • Insert

  • Update

  • UpdateID

  • Delete

43.8.1.8.1 To create routing rules for an insert operation:

  1. Double-click the Mediator1 Oracle Mediator.

    The Mediator Editor is displayed.

  2. In the Routing Rules section, click the Create a new Routing Rule icon.

    The Target Type dialog is displayed.

  3. Select Service.

    The Target Services dialog is displayed.

  4. Navigate to XrefCustApp > Mediators > Common, Services > Common.

  5. Select Insert and click OK.

  6. Click the Filter icon.

    The Expression Builder dialog is displayed.

  7. In the Expression field, enter the following expression: 

    $in.Sap01Collection/top:Sap01Collection/top:Sap01/top:operation='INSERT'

  8. Click OK.

  9. Next to the Transform Using field, click the Transformation icon.

    The Request Transformation map dialog is displayed.

  10. Select Create New Mapper File and enter SAP_TO_COMMON_INSERT.xsl.

  11. Click OK.

    An SAP_TO_COMMON_INSERT.xsl file is displayed in the XSLT Mapper.

  12. Drag and drop the top:SAP01 source element to the inp1:Customer target element.

    The Auto Map Preferences dialog is displayed.

  13. From the During Auto Map options, deselect Match Elements Considering their Ancestor Names.

  14. Click OK.

    The transformation is created, as shown in Figure 43-24.

    Figure 43-24 SAP_TO_COMMON_INSERT.xsl Transformation

    Description of Figure 43-24 follows
    Description of "Figure 43-24 SAP_TO_COMMON_INSERT.xsl Transformation"

  15. From the Components window, select Advanced.

  16. Select XREF Functions.

  17. Drag and drop the populateXRefRow function from the Components window to the line connecting the top:id and inp1:id elements.

  18. Double-click the populateXRefRow icon.

    The Edit Function-populateXRefRow dialog is displayed.

  19. Click Search to the right of the xrefLocation field.

    The SOA Resource Lookup dialog is displayed.

  20. Select customer.xref and click OK.

  21. In the referenceColumnName field, enter "SAP_01" or click Search to select the column name.

  22. In the referenceValue column, enter /top:Sap01Collection/top:Sap01/top:id.

  23. In the columnName field, enter "Common" or click Search to select the column name.

  24. In the value field, enter oraext:generate-guid().

  25. In the mode field, enter "Add" or click Search to select this mode.

    Figure 43-25 shows the populated Edit Function – populateXRefRow dialog.

    Figure 43-25 Edit Function – populateXRefRow Dialog: XrefCustApp Use Case

    Description of Figure 43-25 follows
    Description of "Figure 43-25 Edit Function – populateXRefRow Dialog: XrefCustApp Use Case"

  26. Click OK.

  27. From the File menu, select Save All and close the SAP_TO_COMMON_INSERT.xsl file.

    The Routing Rules section appears, as shown in Figure 43-26.

    Figure 43-26 Routing Rules Section with Insert Operation

    Description of Figure 43-26 follows
    Description of "Figure 43-26 Routing Rules Section with Insert Operation"
43.8.1.8.2 To create routing rules for an update operation:

  1. In the Routing Rules section, click the Create a new Routing Rule icon.

    The Target Type dialog is displayed.

  2. Select Service.

    The Target Services dialog is displayed.

  3. Navigate to XrefCustApp > Mediators > Common, Services > Common.

  4. Select Update and click OK.

  5. Click the Filter icon.

    The Expression Builder dialog is displayed.

  6. In the Expression field, enter the following expression: 

    $in.Sap01Collection/top:Sap01Collection/top:Sap01/top:operation='UPDATE'

  7. Click OK.

  8. Next to the Transform Using field, click the Transformation icon.

    The Request Transformation map dialog is displayed.

  9. Select Create New Mapper File and enter SAP_TO_COMMON_UPDATE.xsl.

  10. Click OK.

    An SAP_TO_COMMON_UPDATE.xsl file is displayed.

  11. Drag and drop the top:Sap01 source element to the inp1:Customer target element.

    The Auto Map Preferences dialog is displayed.

  12. Click OK.

  13. From the Components window, select Advanced.

  14. Select XREF Functions.

  15. Drag and drop the lookupXRef function from the Components window to the line connecting the top:id and inp1:id elements.

  16. Double-click the lookupXRef icon.

    The Edit Function-lookupXRef dialog is displayed.

  17. To the right of the xrefLocation field, click Search.

    The SOA Resource Lookup dialog is displayed.

  18. Select customer.xref and click OK.

  19. In the referenceColumnName field, enter "SAP_01" or click Search to select the column name.

  20. In the referenceValue column, enter /top:Sap01Collection/top:Sap01/top:id.

  21. In the columnName field, enter "COMMON" or click Search to select the column name.

  22. In the needException field, enter true() or click Search to select this mode.

    Figure 43-27 shows the populated Edit Function – looupXRef dialog.

    Figure 43-27 Edit Function – lookupXRef Dialog: XrefCustApp Use Case

    Description of Figure 43-27 follows
    Description of "Figure 43-27 Edit Function – lookupXRef Dialog: XrefCustApp Use Case"

  23. Click OK.

  24. From the File menu, select Save All and close the SAP_TO_COMMON_UPDATE.xsl file.

    The Routing Rules section appears, as shown in Figure 43-28.

    Figure 43-28 Insert Operation and Update Operation

    Description of Figure 43-28 follows
    Description of "Figure 43-28 Insert Operation and Update Operation"
43.8.1.8.3 To create routing rules for an updateID operation:

Perform the following tasks to create routing rules for an updateID operation:

  1. In the Routing Rules section, click the Create a new Routing Rule icon.

    The Target Type dialog is displayed.

  2. Select Service.

    The Target Services dialog is displayed.

  3. Navigate to XrefCustApp > Mediators > Common, Services > Common.

  4. Select updateid and click OK.

  5. Click the Filter icon.

    The Expression Builder dialog is displayed.

  6. In the Expression field, enter the following expression: 

    $in.Sap01Collection/top:Sap01Collection/top:Sap01/top:operation = 'UPDATEID'

  7. Click OK.

  8. Next to the Transform Using field, click the Transformation icon.

    The Request Transformation map dialog is displayed.

  9. Select Create New Mapper File and enter SAP_TO_COMMON_UPDATEID.xsl.

  10. Click OK.

    An SAP_TO_COMMON_UPDATEID.xsl file is displayed.

  11. Drag and drop the top:Sap01 source element to the inp1:Customer target element.

    The Auto Map Preferences dialog is displayed.

  12. Click OK.

  13. From the Components window, select Advanced.

  14. Select XREF Functions.

  15. Drag and drop the populateXRefRow function from the Components window to the line connecting the top:id and inp1:id elements.

  16. Double-click the populateXRefRow icon.

    The Edit Function-populateXRefRow dialog is displayed.

  17. To the right of the xrefLocation field, click Search.

    The SOA Resource Lookup dialog is displayed.

  18. Select customer.xref and click OK.

  19. In the referenceColumnName field, enter "SAP_01" or click Search to select the column name.

  20. In the referenceValue column, enter /top:Sap01Collection/top:Sap01/top:refId.

  21. In the columnName field, enter "SAP_01" or click Search to select the column name.

  22. In the value field, enter /top:Sap01Collection/top:Sap01/top:Id.

  23. In the mode field, enter "UPDATE" or click Search to select this mode.

    Figure 43-29 shows a populated Edit Function – populateXRefRow dialog.

    Figure 43-29 Edit Function – populateXRefRow Dialog: XrefCustApp Use Case

    Description of Figure 43-29 follows
    Description of "Figure 43-29 Edit Function – populateXRefRow Dialog: XrefCustApp Use Case"

  24. Drag and drop the lookupXRef function from the Components window to the line connecting the top:id and inp1:id elements.

  25. Double-click the lookupXRef icon.

    The Edit Function-lookupXRef dialog is displayed.

  26. To the right of the xrefLocation field, click Search.

    The SOA Resource Lookup dialog is displayed.

  27. Select customer.xref and click OK.

  28. In the referenceColumnName field, enter "SAP_01" or click Search to select the column name.

  29. In the referenceValue column, enter the following: 

    xref:populateXRefRow("customer.xref","SAP_
01",/top:Sap01Collection/top:Sap01/top:refId,"SAP_
01",/top:Sap01Collection/top:Sap01/top:id,"UPDATE").

  30. In the columnName field, enter "COMMON" or click Search to select the column name.

  31. In the needException field, enter false() or click Search to select this mode.

    Figure 43-30 shows a populated Edit Function – lookupXRef dialog.

    Figure 43-30 Edit Function – lookupXRef Dialog: XrefCustApp Use Case

    Description of Figure 43-30 follows
    Description of "Figure 43-30 Edit Function – lookupXRef Dialog: XrefCustApp Use Case"

  32. Click OK.

  33. From the File menu, select Save All and close the SAP_TO_COMMON_UPDATEID.xsl file.

    The Routing Rules section appears, as shown in Figure 43-31.

    Figure 43-31 Insert, Update, and UpdateID Operations

    Description of Figure 43-31 follows
    Description of "Figure 43-31 Insert, Update, and UpdateID Operations"
43.8.1.8.4 To create routing rules for a delete operation:

  1. In the Routing Rules section, click the Create a new Routing Rule icon.

    The Target Type dialog is displayed.

  2. Select Service.

    The Target Services dialog is displayed.

  3. Navigate to XrefCustApp > Mediators > Common, Services > Common.

  4. Select delete and click OK.

  5. Click the Filter icon.

    The Expression Builder dialog is displayed.

  6. In the Expression field, enter the following expression: 

    $in.Sap01Collection/top:Sap01Collection/top:Sap01/top:operation = 'DELETE'

  7. Click OK.

  8. Next to the Transform Using field, click the Transformation icon.

    The Request Transformation map dialog is displayed.

  9. Select Create New Mapper File and enter SAP_TO_COMMON_DELETE.xsl.

  10. Click OK.

    A SAP_TO_COMMON_DELETE.xsl file is displayed.

  11. Right-click <sources> and select Add Parameter.

    The Add Parameter dialog is displayed.

  12. In the Local Name field, enter COMMONID.

  13. Select Set Default Value.

  14. Select Expression.

  15. In the XPath Expression field, enter 

    xref:lookupXRef("customer.xref","SAP_
01",/top:Sap01Collection/top:Sap01/top:id,"COMMON",false()).

  16. Click OK.

  17. Drag and drop the top:Sap01 source element to the inp1:Customer target element.

    The Auto Map Preferences dialog is displayed.

  18. Click OK.

  19. Delete the line connecting top:id and inp1:id.

  20. Connect COMMONID to inp1:id.

  21. Right-click inp1:id and select Add XSL node and then if.

    A new node if is inserted between inp1:customer and inp1:id.

  22. Connect top:id to the if node.

  23. From the Components window, select Advanced.

  24. Select XREF Functions.

  25. Drag and drop the markForDelete function from the Components window to the line connecting top:id and the if node.

  26. Double-click the markForDelete icon.

    The Edit Function-markForDelete dialog is displayed.

  27. Click Search to the right of the xrefLocation field.

    The SOA Resource Lookup dialog is displayed.

  28. Select customer.xref and click OK.

  29. In the columnName field, enter "SAP_01" or click Search to select the column name.

  30. In the value field, enter /top:Sap01Collection/top:Sap01/top:Id.

    Figure 43-32 shows a populated Edit Function – markForDelete dialog.

    Figure 43-32 Edit Function – markForDelete Dialog: XrefCustApp Use Case

    Description of Figure 43-32 follows
    Description of "Figure 43-32 Edit Function – markForDelete Dialog: XrefCustApp Use Case"

  31. Click OK.

    The SAP_TO_COMMON_DELETE.xsl file appears, as shown in Figure 43-33.

    Figure 43-33 SAP_TO_COMMON_DELETE.xsl

    Description of Figure 43-33 follows
    Description of "Figure 43-33 SAP_TO_COMMON_DELETE.xsl"

  32. From the File menu, select Save All and close the SAP_TO_COMMON_DELETE.xsl file.

    The Routing Rules section appears, as shown in Figure 43-34.

    Figure 43-34 Insert, Update, UpdateID, and Delete Operations

    Description of Figure 43-34 follows
    Description of "Figure 43-34 Insert, Update, UpdateID, and Delete Operations"
43.8.1.9 Task 9: How to Specify Routing Rules for the Common Oracle Mediator

You must specify routing rules for the following operations of the Common Oracle Mediator:

  • Insert

  • Delete

  • Update

  • UpdateID

43.8.1.9.1 To create routing rules for the insert operation:

  1. Double-click the Common Oracle Mediator.

    The Mediator Editor is displayed.

  2. In the Routing Rules section, click the Create a new Routing Rule icon.

    The Target Type dialog is displayed.

  3. Select Service.

    The Target Services dialog is displayed.

  4. Navigate to XrefCustApp > References > SBL.

  5. Select SBL and click OK.

  6. Next to the Transform Using field, click the Transformation icon.

    The Request Transformation map dialog is displayed.

  7. Select Create New Mapper File and enter COMMON_TO_SBL_INSERT.xsl.

  8. Click OK.

    A COMMON_TO_SBL_INSERT.xsl file is displayed.

  9. Drag and drop the inp1:Customers source element to the db:InputParameters target element.

    The Auto Map Preferences dialog is displayed.

  10. Click OK.

    The transformation is created, as shown in Figure 43-35.

    Figure 43-35 COMMON_TO_SBL_INSERT.xsl Transformation

    Description of Figure 43-35 follows
    Description of "Figure 43-35 COMMON_TO_SBL_INSERT.xsl Transformation"

  11. From the File menu, select Save All and close the COMMON_TO_SBL_INSERT.xsl file.

  12. In the Synchronous Reply section, click Browse for target service operations.

    The Target Type dialog is displayed.

  13. Select Service.

    The Target Services dialog is displayed.

  14. Navigate to XrefCustApp > References > Logger.

  15. Select Write and click OK.

  16. Next to the Transform Using field, click the Transformation icon.

    The Reply Transformation map dialog is displayed.

  17. Select Create New Mapper File and enter SBL_TO_COMMON_INSERT.xsl.

  18. Select Include Request in the Reply Payload.

  19. Click OK.

    A SBL_TO_COMMON_INSERT.xsl file is displayed.

  20. Connect the inp1:Customers source element to db:X:APP_ID.

  21. Drag and drop the populateXRefRow function from the Components window to the connecting line.

  22. Double-click the populateXRefRow icon.

    The Edit Function-populateXRefRow dialog is displayed.

  23. Enter this information in the following fields:

    • xrefLocation: "customer.xref"

    • referenceColumnName: "Common"

    • referenceValue: $initial.Customers/inp1:Customers/inp1:Customer/inp1:Id

    • columnName: "SBL_78"

    • value: /db:OutputParameters/db:X_APP_ID

    • mode: "LINK"

  24. Click OK.

    The SBL_TO_COMMON_INSERT.xsl file appears, as shown in Figure 43-36.

    Figure 43-36 SBL_TO_COMMON_INSERT.xsl Transformation

    Description of Figure 43-36 follows
    Description of "Figure 43-36 SBL_TO_COMMON_INSERT.xsl Transformation"

  25. From the File menu, select Save All and close the SBL_TO_COMMON_INSERT.xsl file.

  26. In the Synchronous Reply section, click the Assign Values icon.

    The Assign Values dialog is displayed.

  27. Click Add.

    The Assign Value dialog is displayed.

  28. In the From section, select Expression.

  29. Click the Invoke Expression Builder icon.

    The Expression Builder dialog is displayed.

  30. In the Expression field, enter the following expression and click OK. 

    concat('INSERT-',$in.OutputParameters/db:OutputParameters/db:X_APP_ID,'.xml')

  31. In the To section, select Property.

  32. Select the jca.file.FileName property and click OK.

  33. Click OK.

    The insert operation section appears, as shown in Figure 43-37.

    Figure 43-37 Insert Operation with SBL Target Service

    Description of Figure 43-37 follows
    Description of "Figure 43-37 Insert Operation with SBL Target Service"

  34. From the File menu, select Save All.

  35. Repeat Step 2 through Step 34 to specify another target service named EBS and its routing rules.

    Figure 43-38 shows the insert operation section with SBL and EBS target services.

    Figure 43-38 Insert Operation with SBL and EBS Target Services

    Description of Figure 43-38 follows
    Description of "Figure 43-38 Insert Operation with SBL and EBS Target Services"
43.8.1.9.2 To create routing rules for a delete operation:

Perform the following tasks to create the routing rules for a delete operation.

  1. In the Routing Rules section, click the Create a new Routing Rule icon.

    The Target Type dialog is displayed.

  2. Select Service.

    The Target Services dialog is displayed.

  3. Navigate to XrefCustApp > References > SBL.

  4. Select SBL and click OK.

  5. Next to the Transform Using field, click the Transformation icon.

    The Request Transformation map dialog is displayed.

  6. Select Create New Mapper File and enter COMMON_TO_SBL_DELETE.xsl.

  7. Click OK.

    A COMMON_TO_SBL_DELETE.xsl file is displayed.

  8. Drag and drop the inp1:Customers source element to the db:InputParameters target element.

    The Auto Map Preferences dialog is displayed.

  9. Click OK.

    The transformation is created, as shown in Figure 43-39.

    Figure 43-39 COMMON_TO_SBL_DELETE.xsl Transformation

    Description of Figure 43-39 follows
    Description of "Figure 43-39 COMMON_TO_SBL_DELETE.xsl Transformation"

  10. Drag and drop the lookupXRef function from the Components window to the line connecting inp1:id and db:XCUSTOMER_ID.

  11. Double-click the lookupXRef icon.

    The Edit Function: lookupXRef dialog is displayed.

  12. Enter this information in the following fields:

    • xrefLocation: "customer.xref"

    • referenceColumnName: "Common"

    • referenceValue: /inp1:Customers/inp1:Customer/inp1:Id

    • columnName: "SBL_78"

    • needException: false()

  13. Click OK.

  14. From the File menu, select Save All and close the COMMON_TO_SBL_DELETE.xsl file.

  15. In the Synchronous Reply section, click Browse for target service operations.

    The Target Type dialog is displayed.

  16. Select Service.

    The Target Services dialog is displayed.

  17. Navigate to XrefCustApp > References > Logger.

  18. Select Write and click OK.

  19. Next to the Transform Using field, click the Transformation icon.

    The Reply Transformation map dialog is displayed.

  20. Select Create New Mapper File and enter SBL_TO_COMMON_DELETE.xsl.

  21. Click OK.

    The SBL_TO_COMMON_DELETE.xsl file is displayed.

  22. Connect the db:X_APP_ID source element to the db:X:APP_ID target.

  23. Drag and drop the markForDelete function from the Components window to the connecting line.

  24. Double-click the markForDelete icon.

    The Edit Function-markForDelete dialog is displayed.

  25. Enter this information in the following fields:

    • xrefLocation: "customer.xref"

    • columnName: "SBL_78"

    • value: /db:OutputParameters/db:X_APP_ID

  26. Click OK.

  27. From the File menu, select Save All and close the SBL_TO_COMMON_DELETE.xsl file.

  28. In the Synchronous Reply section, click the Assign Values icon.

    The Assign Values dialog is displayed.

  29. Click Add.

    The Assign Value dialog is displayed.

  30. In the From section, select Expression.

  31. Click the Invoke Expression Builder icon.

    The Expression Builder dialog is displayed.

  32. In the Expression field, enter the following expression, and click OK. 

    concat('DELETE-',$in.OutputParameters/db:OutputParameters/db:X_APP_ID,'.xml')

  33. In the To section, select Property.

  34. Select the jca.file.FileName property and click OK.

  35. Click OK.

    The delete operation section appears, as shown in Figure 43-40.

    Figure 43-40 Delete Operation with SBL Target Service

    Description of Figure 43-40 follows
    Description of "Figure 43-40 Delete Operation with SBL Target Service"

  36. From the File menu, select Save All.

  37. Repeat Step 1 through Step 36 to specify another target service named EBS and specify the routing rules.

    Figure 43-41 shows the delete operation section with SBL and EBS target services.

    Figure 43-41 Delete Operation with SBL and EBS Target Service

    Description of Figure 43-41 follows
    Description of "Figure 43-41 Delete Operation with SBL and EBS Target Service"
43.8.1.9.3 To create routing rules for the update operation:

Perform the following tasks to create routing rules for the update operation.

  1. In the Routing Rules section, click the Create a new Routing Rule icon.

    The Target Type dialog is displayed.

  2. Select Service.

    The Target Services dialog is displayed.

  3. Navigate to XrefCustApp, References > SBL.

  4. Select SBL and click OK.

  5. Click the Transformation icon next to the Transform Using field.

    The Request Transformation map dialog is displayed.

  6. Select Create New Mapper File and enter COMMON_TO_SBL_UPDATE.xsl.

  7. Click OK.

    A COMMON_TO_SBL_UPDATE.xsl file is displayed.

  8. Drag and drop the inp1:Customers source element to the db:InputParameters target element.

    The Auto Map Preferences dialog is displayed.

  9. Click OK.

    The transformation is created, as shown in Figure 43-39.

  10. Drag and drop the lookupXRef function from the Components window to the line connecting inp1:id and db:XCUSTOMER_ID.

  11. Double-click the lookupXRef icon.

    The Edit Function: lookupXRef dialog is displayed.

  12. Enter this information in the following fields:

    • xrefLocation: "customer.xref"

    • referenceColumnName: "Common"

    • referenceValue: /inp1:Customers/inp1:Customer/inp1:Id

    • columnName: "SBL_78"

    • needException: true()

  13. Click OK.

  14. From the File menu, select Save All and close the COMMON_TO_SBL_UPDATE.xsl file.

  15. In the Synchronous Reply section, click Browse for target service operations.

    The Target Type dialog is displayed.

  16. Select Service.

    The Target Services dialog is displayed.

  17. Navigate to XrefCustApp > References > Logger.

  18. Select Write and click OK.

  19. Next to the Transform Using field, click the Transformation icon.

    The Reply Transformation map dialog is displayed.

  20. Select Create New Mapper File and enter SBL_TO_COMMON_UPDATE.xsl.

  21. Click OK.

    A SBL_TO_COMMON_UPDATE.xsl file is displayed.

  22. Connect the db:X:APP_ID source element to db:X:APP_ID.

  23. From the File menu, select Save All and close the SBL_TO_COMMON_UPDATE.xsl file.

  24. In the Synchronous Reply section, click the Assign Values icon.

    The Assign Values dialog is displayed.

  25. Click Add.

    The Assign Value dialog is displayed.

  26. In the From section, select Expression.

  27. Click the Invoke Expression Builder icon.

    The Expression Builder dialog is displayed.

  28. In the Expression field, enter the following expression and click OK. 

    concat('UPDATE-',$in.OutputParameters/db:OutputParameters/db:X_APP_ID,'.xml')

  29. In the To section, select Property.

  30. Select the jca.file.FileName property and click OK.

  31. Click OK.

    The update operation section appears, as shown in Figure 43-42.

    Figure 43-42 Update Operation with SBL Target Service

    Description of Figure 43-42 follows
    Description of "Figure 43-42 Update Operation with SBL Target Service"

  32. From the File menu, select Save All.

  33. Repeat Step 1 through Step 32 to specify another target service named EBS and its routing rules.

    Figure 43-43 shows the update operation section with SBL and EBS target services.

    Figure 43-43 Update Operation with SBL and EBS Target Service

    Description of Figure 43-43 follows
    Description of "Figure 43-43 Update Operation with SBL and EBS Target Service"
43.8.1.9.4 To create routing rules for the UpdateID operation:

Perform the following tasks to create routing rules for the UpdateID operation.

  1. In the Routing Rules section, click the Create a new Routing Rule icon.

    The Target Type dialog is displayed.

  2. Select Service.

    The Target Services dialog is displayed.

  3. Navigate to XrefCustApp > References > SBL.

  4. Select SBL and click OK.

  5. Next to the Transform Using field, click the Transformation icon.

    The Request Transformation map dialog is displayed.

  6. Select Create New Mapper File and enter COMMON_TO_SBL_UPDATEID.xsl.

  7. Click OK.

    The COMMON_TO_SBL_UPDATEID.xsl file is displayed.

  8. Drag and drop the inp1:Customers source element to the db:InputParameters target element.

    The Auto Map Preferences dialog is displayed.

  9. Click OK.

    The transformation is created, as shown in Figure 43-39.

  10. Drag and drop the lookupXRef function from the Components window to the line connecting inp1:id and db:X_CUSTOMER_ID.

  11. Double-click the lookupXRef icon.

    The Edit Function: lookupXRef dialog is displayed.

  12. Enter this information in the following fields:

    • xrefLocation: customer.xref

    • referenceColumnName: Common

    • referenceValue: /inp1:Customers/inp1:Customer/inp1:Id

    • columnName: SBL_78

    • needException: false()

  13. Click OK.

  14. From the File menu, select Save All and close the COMMON_TO_SBL_UPDATEID.xsl file.

  15. In the Synchronous Reply section, click Browse for target service operations.

    The Target Type dialog is displayed.

  16. Select Service.

    The Target Services dialog is displayed.

  17. Navigate to XrefCustApp > References > Logger.

  18. Select Write and click OK.

  19. Next to the Transform Using field, click the Transformation icon.

    The Reply Transformation map dialog is displayed.

  20. Select Include Request in the Reply Payload.

  21. Click OK.

    The SBL_TO_COMMON_UPDATEID.xsl file is displayed.

  22. Connect inp1:Customers source element to the db:X:APP_ID.

  23. Drag and drop the populateXRefRow function from the Components window to the connecting line.

  24. Double-click the populateXRefRow icon.

    The Edit Function-populateXRefRow dialog is displayed.

  25. Enter this information in the following fields:

    • xrefLocation: customer.xref

    • referenceColumnName: Common

    • referenceValue: $initial.Customers/inp1:Customers/inp1:Customer/inp1:Id

    • columnName: SBL_78

    • value: /db:OutputParameters/db:X_APP_ID

    • mode: UPDATE

  26. Click OK.

  27. From the File menu, select Save All and close the SBL_TO_COMMON_UPDATEID.xsl file.

  28. In the Synchronous Reply section, click the Assign Values icon.

    The Assign Values dialog is displayed.

  29. Click Add.

    The Assign Value dialog is displayed.

  30. In the From section, select Expression.

  31. Click the Invoke Expression Builder icon.

    The Expression Builder dialog is displayed.

  32. In the Expression field, enter the following expression and click OK. 

    concat('UPDATEID-',$in.OutputParameters/db:OutputParameters/db:X_APP_ID,'.xml')

  33. In the To section, select Property.

  34. Select the jca.file.FileName property and click OK.

  35. Click OK.

    The updateid operation section appears, as shown in Figure 43-44.

    Figure 43-44 Updateid Operation with SBL Target Service

    Description of Figure 43-44 follows
    Description of "Figure 43-44 Updateid Operation with SBL Target Service"

  36. From the File menu, select Save All.

  37. Repeat Step 1 through Step 36 to specify another target service named EBS and specify the routing rules.

    Figure 43-45 shows the updateid operation section with the SBL and EBS target services.

    Figure 43-45 Updateid Operation with SBL and EBS Target Service

    Description of Figure 43-45 follows
    Description of "Figure 43-45 Updateid Operation with SBL and EBS Target Service"
43.8.1.10 Task 10: How to Configure an Application Server Connection

An application server connection is required for deploying your SOA composite application. For information on creating an application server connection, see Creating an Application Server Connection.

43.8.1.11 Task 11: How to Deploy the Composite Application

Deploying the XrefCustApp composite application consists of the following steps:

  • Creating an application deployment profile

  • Deploying the application to the application server

For detailed information about these steps, see How to Deploy a Single SOA Composite in Oracle JDeveloper.

43.8.2 How to Run and Monitor the XrefCustApp Application

After deploying the XrefCustApp application, you can run it by using any command from the insert_sap_record.sql file present in the XrefCustApp/sql folder. On successful completion, the records are inserted or updated in the EBS and SBL tables and the Logger reference writes the output to the output.xml file.

For monitoring the running instance, you can use the Oracle Enterprise Manager Fusion Middleware Control at the following URL: 

http://hostname:port_number/em

where hostname is the host on which you installed the Oracle SOA Suite infrastructure and port_number is the port running the service.

43.9 Creating and Running Cross Reference for 1M Functions

The cross reference use case implements an integration scenario between two end-system Oracle EBS and SAP instances. In this use case, the order passes from SAP to EBS. SAP represents the orders with a unique ID, whereas EBS splits the order into two orders: ID1 and ID2. This scenario is created using database adapters. When you poll the SAP table for updated or created records, an SAP instance is created. In EBS, the instance is simulated by a procedure and the table is populated. Figure 43-46 provides an overview of this use case.

Figure 43-46 XrefOrderApp Use Case in SOA Composite Editor

Description of Figure 43-46 follows
Description of "Figure 43-46 XrefOrderApp Use Case in SOA Composite Editor"

43.9.1 How to Create the Use Case

This section provides the design-time tasks for creating, building, and deploying your SOA composite application. These tasks should be performed in the order in which they are presented.

43.9.1.1 Task 1: How to Configure the Oracle Database and Database Adapter

To configure the Oracle database and database adapter:

  1. You need the SCOTT database account with password TIGER for this use case. You must ensure that the SCOTT account is unlocked.

    You can log in as SYSDBA and then run the setup_user.sql script available in the XrefOrderApp1M/sql folder to unlock the account.

  2. Run the create_schema.sql script available in the XrefOrderApp1M/sql folder to create the tables required for this use case.
  3. Run the create_app_procedure.sql script available in the XrefOrderApp1M/sql folder to create a procedure that simulates the various applications participating in this integration.
  4. Run the createschema_xref_oracle.sql script available in the Oracle_Home/rcu/integration/soainfra/sql/xref/ folder to create a cross reference table to store runtime cross reference data.
  5. Copy the ra.xml and weblogic-ra.xml files from $BEAHOME/META-INF to the newly created directory called META-INF on your computer.
  6. Edit the weblogic-ra.xml file, which is available in the $BEAHOME/src/oracle/tip/adapter/db/test/deploy/weblogic/META-INF folder for your SOA application, as follows:

    • Modify the property to xADataSourceName as follows: 

      <property>
 <name>xADataSourceName</name>
 <value>jdbc/DBConnection1</value>
</property>

    • Modify the jndi-name as follows: 

      <jndi-name> eis/DB/DBConnection1</jndi-name>

    This sample uses eis/DB/DBConnection1 to poll the SAP table for new messages and to connect to the procedure that simulates Oracle EBS and Siebel instances.

  7. Package the ra.xml and weblogic-ra.xml files as a RAR file and deploy the RAR file by using Oracle WebLogic Server Administration Console.
  8. Create a data source using the Oracle WebLogic Server Administration Console with the following values:

    • jndi-name=jdbc/DBConnection1

    • user=scott

    • password=tiger

    • url=jdbc:oracle:thin:@host:port:service

    • connection-factory factory-class=oracle.jdbc.pool.OracleDataSource

  9. Create a data source using the Oracle WebLogic Server Administration Console with the following values:

    • jndi-name=jdbc/xref

    • user=scott

    • password=tiger

    • url=jdbc:oracle:thin:@host:port:service

    • connection-factory factory-class=oracle.jdbc.pool.OracleDataSource

43.9.1.2 Task 2: How to Create an Oracle JDeveloper Application and a Project

To create an Oracle JDeveloper application and a project:

  1. In Oracle JDeveloper, click File and select New.

    The New Gallery dialog appears.

  2. In the New Gallery, expand the General node, and select the Applications category.
  3. In the Items list, select SOA Application and click OK.

    The Create SOA Application wizard appears.

  4. In the Application Name field, enter XRefOrderApp, and then click Next.

    The Name your project page appears.

  5. In the Project Name field, enter XRefOrderApp and click Next.

    The Configure SOA Settings page appears.

  6. In the Composite Template list, select Empty Composite and then click Finish.

    The Applications window of Oracle JDeveloper is updated with the new application and project and the SOA Composite Editor contains a blank project.

  7. From the File menu, select Save All.
43.9.1.3 Task 3: How to Create a Cross Reference

After creating an application and a project for the use case, you must create a cross reference table.

To create a cross reference table:

  1. In the Applications window, right-click the XRefOrderApp project and select New.
  2. In the New Gallery dialog, expand the SOA Tier node, and then select the Transformations category.
  3. In the Items list, select Cross Reference(XREF) and click OK.

    The Create Cross Reference(XREF) File dialog is displayed.

  4. In the File Name field, enter order.xref.
  5. In the End System fields, enter SAP_05 and EBS_i75.
  6. Click OK.

    The Cross Reference Editor is displayed.

  7. Click Add.

    A new row is added.

  8. Enter COMMON as the End System name.

    The Cross Reference Editor appears, as shown in Figure 43-47.

    Figure 43-47 Customer Cross Reference

    Description of Figure 43-47 follows
    Description of "Figure 43-47 Customer Cross Reference"
  9. From the File menu, select Save All and close the Cross Reference Editor.
43.9.1.4 Task 4: How to Create a Database Adapter Service

To create a database adapter service:

  1. In the Components window, select SOA.
  2. Select Database Adapter and drag it to the Exposed Services swimlane.

    The Adapter Configuration wizard Welcome page is displayed.

  3. Click Next.

    The Service Name page is displayed.

  4. In the Service Name field, enter SAP.
  5. Click Next.

    The Service Connection page is displayed.

  6. In the Connection field, select DBConnection1.
  7. In the JNDI Name field, enter eis/DB/DBConnection1.
  8. Click Next.

    The Operation Type page is displayed.

  9. Select Poll for New or Changed Records in a Table and click Next.

    The Select Table page is displayed.

  10. Click Import Tables.

    The Import Tables dialog is displayed.

  11. Select Scott from the Schema.
  12. In the Name Filter field, enter %SAP% and click Query.

    The Available field is populated with the SAP_05 table name.

  13. Double-click SAP_05.

    The selected field is populated with SAP_05.

  14. Click OK.

    The Select Table page now contains the SAP_05 table.

  15. Select SAP_05 and click Next.

    The Define Primary Key page is displayed.

  16. Select ID as the primary key and click Next.

    The Relationships page is displayed.

  17. Click Next.

    The Attribute Filtering page is displayed.

  18. Click Next.

    The After Read page is displayed.

  19. Select Update a Field in the [SAP_05] Table (Logical Delete) and click Next.

    The Logical Delete page is displayed.

  20. In the Logical Delete field, select LOGICAL_DEL.
  21. In the Read Value field, enter Y.
  22. In the Unread Value field, enter N.

    Figure 43-16 shows the Logical Delete page of the Adapter Configuration wizard.

  23. Click Next.

    The Polling Options page is displayed.

  24. Click Next.

    The Define Selection Criteria page is displayed.

  25. Click Next.

    The Advanced Options page is displayed.

  26. Click Next.

    The Finish page is displayed.

  27. Click Finish.

    A database adapter service named SAP is created, as shown in Figure 43-48.

    Figure 43-48 SAP Database Adapter Service in SOA Composite Editor

    Description of Figure 43-48 follows
    Description of "Figure 43-48 SAP Database Adapter Service in SOA Composite Editor"
  28. From the File menu, select Save All.
43.9.1.5 Task 5: How to Create an EBS External Reference

To create an EBS external reference:

  1. In the Components window, select SOA.
  2. Select Database Adapter and drag it to the External References swimlane.

    The Adapter Configuration wizard Welcome page is displayed.

  3. Click Next.

    The Service Name page is displayed.

  4. In the Service Name field, enter EBS.
  5. Click Next.

    The Service Connection page is displayed.

  6. In the Connection field, select DBConnection1.
  7. In the JNDI Name field, enter eis/DB/DBConnection1.
  8. Click Next.

    The Operation Type page is displayed.

  9. Select Call a Stored Procedure or Function and click Next.

    The Specify Stored Procedure page is displayed.

  10. Select Scott from the Schema.
  11. Click Browse.

    The Stored Procedures dialog is displayed.

  12. Select POPULATE_APP_INSTANCE_IM, as shown in Figure 43-49.

    Figure 43-49 Stored Procedure Dialog

    Description of Figure 43-49 follows
    Description of "Figure 43-49 Stored Procedure Dialog"
  13. Click OK.

    The Specify Stored Procedure page appears, as shown in Figure 43-50.

    Figure 43-50 Specify Stored Procedure Page of Adapter Configuration Wizard

    Description of Figure 43-50 follows
    Description of "Figure 43-50 Specify Stored Procedure Page of Adapter Configuration Wizard"
  14. Click Next.

    The Advanced Options page is displayed.

  15. Click Next.The Finish page is displayed.
  16. Click Finish.

    Figure 43-51 shows the EBS reference in the SOA Composite Editor.

    Figure 43-51 EBS Reference in SOA Composite Editor

    Description of Figure 43-51 follows
    Description of "Figure 43-51 EBS Reference in SOA Composite Editor"
  17. From the File menu, select Save All.
43.9.1.6 Task 6: How to Create a Logger File Adapter External Reference

To create a Logger file adapter external reference:

  1. From the Components window, select SOA.
  2. Select File Adapter and drag it to the External References swimlane.

    The Adapter Configuration wizard Welcome page is displayed.

  3. Click Next.

    The Service Name page is displayed.

  4. In the Service Name field, enter Logger.
  5. Click Next.

    The Adapter Interface page is displayed.

  6. Click Define from operation and schema (specified later).

    The Operation page is displayed.

  7. In the Operation Type field, select Write File.
  8. Click Next.

    The File Configuration page is displayed.

  9. In the Directory for Outgoing Files (physical path) field, enter the name of the directory in which you want to write the files.
  10. In the File Naming Convention field, enter output.xml and click Next.

    The Messages page is displayed.

  11. Click Search.

    The Type Chooser dialog is displayed.

  12. Navigate to Type Explorer > Project Schema Files > SCOTT_POPULATE_APP_INSTANCE_1M.xsd, and then select OutputParameters.
  13. Click OK.
  14. Click Next.

    The Finish page is displayed.

  15. Click Finish.

    Figure 43-52 shows the Logger reference in the SOA Composite Editor.

    Figure 43-52 Logger Reference in SOA Composite Editor

    Description of Figure 43-52 follows
    Description of "Figure 43-52 Logger Reference in SOA Composite Editor"
  16. From the File menu. select Save All.
43.9.1.7 Task 7: How to Create an Oracle Mediator Service Component

To create an Oracle Mediator service component:

  1. Drag and drop a Mediator icon from the Components window to the Components swimlane.

    The Create Mediator dialog is displayed.

  2. From the Template list, select Define Interface Later.
  3. Click OK.

    An Oracle Mediator with name Mediator2 is created.

  4. Connect the SAP service to Mediator2, as shown in Figure 43-53.

    Figure 43-53 SAP Service Connected to Mediator2

    Description of Figure 43-53 follows
    Description of "Figure 43-53 SAP Service Connected to Mediator2"
  5. From the File menu. select Save All.
  6. Drag and drop a Mediator icon from the Components window to the Components section of the SOA Composite Editor.

    The Create Mediator dialog is displayed.

  7. From the Template list, select Interface Definition From WSDL.
  8. Deselect Create Composite Service with SOAP Bindings.
  9. To the right of the WSDL File field, click Find Existing WSDLs.
  10. Navigate to and then select the Common.wsdl file. The Common.wsdl file is available in the Samples folder.
  11. Click OK.
  12. Click OK.

    An Oracle Mediator named Common is created.

43.9.1.8 Task 8: How to Specify Routing Rules for an Oracle Mediator Component

You must specify routing rules for following operations:

  • Insert

  • Update

43.9.1.8.1 To create routing rules for the insert operation:

  1. Double-click the Mediator2 Oracle Mediator.

    The Mediator Editor is displayed.

  2. In the Routing Rules section, click the Create a new Routing Rule icon.

    The Target Type dialog is displayed.

  3. Select Service.

    The Target Services dialog is displayed.

  4. Navigate to XrefOrderApp > Mediators > Common, Services > Common.

  5. Select Insert and click OK.

  6. Click the Filter icon.

    The Expression Builder dialog is displayed.

  7. In the Expression field, enter the following expression: 

    $in.Sap05Collection/top:Sap05Collection/top:Sap05/top:operation='INSERT'

  8. Click OK.

  9. Next to the Using Transformation field, click the Transformation icon.

    The Request Transformation map dialog is displayed.

  10. Select Create New Mapper File and enter SAP_TO_COMMON_INSERT.xsl.

  11. Click OK.

    An SAP_TO_COMMON_INSERT.xsl file is displayed.

  12. Drag and drop the top:SAP05 source element to the inp1:Order target element.

    The Auto Map Preferences dialog is displayed.

  13. From the During Auto Map options list, deselect Match Elements Considering their Ancestor Names.

  14. Click OK.

    The transformation is created, as shown in Figure 43-54.

    Figure 43-54 SAP_TO_COMMON_INSERT.xsl Transformation

    Description of Figure 43-54 follows
    Description of "Figure 43-54 SAP_TO_COMMON_INSERT.xsl Transformation"

  15. From the Components window, select Advanced.

  16. Select XREF Functions.

  17. Drag and drop the populateXRefRow1M function from the Components window to the line connecting the top:id and inp1:id elements.

  18. Double-click the populateXRefRow1M icon.

    The Edit Function-populateXRefRow dialog is displayed.

  19. To the right of the xrefLocation field, click Search.

    The SOA Resource Lookup dialog is displayed.

  20. Select Order.xref and click OK.

  21. In the referenceColumnName field, enter "SAP_05" or click Search to select the column name.

  22. In the referenceValue column, enter /top:Sap05Collection/top:Sap05/top:id.

  23. In the columnName field, enter "Common" or click Search to select the column name.

  24. In the value field, enter orcl:generate-guid().

  25. In the mode field, enter "Add" or click Search to select this mode.

    Figure 43-55 shows the populated Edit Function – populateXRefRow1M dialog.

    Figure 43-55 Edit Function – populateXRefRow1M Dialog: XrefOrderApp Use Case

    Description of Figure 43-55 follows
    Description of "Figure 43-55 Edit Function – populateXRefRow1M Dialog: XrefOrderApp Use Case"

  26. Click OK.

  27. From the File menu, select Save All and close the SAP_TO_COMMON_INSERT.xsl file.

    The Routing Rules section appears, as shown in Figure 43-56.

    Figure 43-56 Routing Rules Section with Insert Operation

    Description of Figure 43-56 follows
    Description of "Figure 43-56 Routing Rules Section with Insert Operation"
43.9.1.8.2 To create routing rules for the update operation:

Perform the following tasks to create routing rules for the update operation.

  1. In the Routing Rules section, click the Create a new Routing Rule icon.

    The Target Type dialog is displayed.

  2. Select Service.

    The Target Services dialog is displayed.

  3. Navigate to XrefOrderApp > Mediators > Common, Services > Common.

  4. Select Update and click OK.

  5. Click the Filter icon.

    The Expression Builder dialog is displayed.

  6. In the Expression field, enter the following expression: 

    $in.Sap05Collection/top:Sap05Collection/top:Sap05/top:operation='UPDATE'

  7. Click OK.

  8. Next to the Transform Using field, click the Transformation icon.

    The Request Transformation map dialog is displayed.

  9. Select Create New Mapper File and enter SAP_TO_COMMON_UPDATE.xsl.

  10. Click OK.

    An SAP_TO_COMMON_UPDATE.xsl file is displayed.

  11. Drag and drop the top:Sap05 source element to the inp1:Order target element.

    The Auto Map Preferences dialog is displayed.

  12. Click OK.

  13. From the Components window, select Advanced.

  14. Select XREF Functions.

  15. Drag and drop the lookupXRef function from the Components window to the line connecting the top:id and inp1:id elements.

  16. Double-click the lookupXRef icon.

    The Edit Function-lookupXRef dialog is displayed.

  17. To the right of the xrefLocation field, click Search.

    The SOA Resource Lookup dialog is displayed.

  18. Select customer.xref and click OK.

  19. In the referenceColumnName field, enter "SAP_05" or click Search to select the column name.

  20. In the referenceValue column, enter /top:Sap05Collection/top:Sap05/top:id.

  21. In the columnName field, enter "COMMON" or click Search to select the column name.

  22. In the needException field, enter true() or click Search to select this mode.

    Figure 43-57 shows the populated Edit Function – looupXRef dialog.

    Figure 43-57 Edit Function – looupXRef Dialog: XRefOrderApp Use Case

    Description of Figure 43-57 follows
    Description of "Figure 43-57 Edit Function – looupXRef Dialog: XRefOrderApp Use Case"

  23. Click OK.

  24. From the File menu, select Save All and close the SAP_TO_COMMON_UPDATE.xsl file.

    The Routing Rules section appears, as shown in Figure 43-58.

    Figure 43-58 Insert Operation and Update Operation

    Description of Figure 43-58 follows
    Description of "Figure 43-58 Insert Operation and Update Operation"
43.9.1.9 Task 9: How to Specify Routing Rules for the Common Oracle Mediator

You must specify routing rules for the following operations of the Common Oracle Mediator:

  • Insert

  • Update

43.9.1.9.1 To create routing rules for the insert operation:

  1. Double-click the Common Oracle Mediator.

    The Mediator Editor is displayed.

  2. In the Routing Rules section, click the Create a new Routing Rule icon.

    The Target Type dialog is displayed.

  3. Select Service.

    The Target Services dialog is displayed.

  4. Navigate to XrefOrderApp > References > EBS.

  5. Select EBS and click OK.

  6. Next to the Transform Using field, click the Transformation icon.

    The Request Transformation map dialog is displayed.

  7. Select Create New Mapper File and enter COMMON_TO_EBS_INSERT.xsl.

  8. Click OK.

    A COMMON_TO_EBS_INSERT.xsl file is displayed.

  9. Drag and drop the inp1:Order source element to the db:InputParameters target element.

    The Auto Map Preferences dialog is displayed.

  10. Set the value of the db:X_APP_INSTANCE node on the right side to EBS_i75.

    Click OK.

    The transformation is created, as shown in Figure 43-59.

    Figure 43-59 COMMON_TO_EBS_INSERT.xsl Transformation

    Description of Figure 43-59 follows
    Description of "Figure 43-59 COMMON_TO_EBS_INSERT.xsl Transformation"

  11. From the File menu, select Save All and close the COMMON_TO_EBS_INSERT.xsl file.

  12. In the Synchronous Reply section, click Browse for target service operations.

    The Target Type dialog is displayed.

  13. Select Service.

    The Target Services dialog is displayed.

  14. Navigate to XrefOrderApp > References > Logger.

  15. Select Write and click OK.

  16. Next to the Transform Using field, click the Transformation icon.

    The Reply Transformation map dialog is displayed.

  17. Select Create New Mapper File and enter EBS_TO_COMMON_INSERT.xsl.

  18. Select Include Request in the Reply Payload.

  19. Click OK.

    An EBS_TO_COMMON_INSERT.xsl file is displayed.

  20. Connect the inp1:Order source element to db:X:APP_ID.

  21. Drag and drop the populateXRefRow function from the Components window to the connecting line.

  22. Double-click the populateXRefRow icon.

    The Edit Function-populateXRefRow dialog is displayed.

  23. Enter this information in the following fields:

    • xrefLocation: order.xref

    • referenceColumnName: Common

    • referenceValue: $initial.Customers/inp1:Customers/inp1:Order/inp1:Id

    • columnName: EBS_75

    • value: /db:OutputParameters/db:X_APP_ID

    • mode: LINK

  24. Click OK.

    The EBS_TO_COMMON_INSERT.xsl file appears, as shown in Figure 43-60.

    Figure 43-60 EBS_TO_COMMON_INSERT.xsl Transformation

    Description of Figure 43-60 follows
    Description of "Figure 43-60 EBS_TO_COMMON_INSERT.xsl Transformation"

  25. From the File menu, select Save All and close the EBS_TO_COMMON_INSERT.xsl file.

  26. In the Synchronous Reply section, click the Assign Values icon.

    The Assign Values dialog is displayed.

  27. Click Add.

    The Assign Value dialog is displayed.

  28. In the From section, select Expression.

  29. Click the Invoke Expression Builder icon.

    The Expression Builder dialog is displayed.

  30. In the Expression field, enter the following expression and click OK. 

    concat('INSERT-',$in.OutputParameters/db:OutputParameters/db:X_APP_ID,'.xml')

  31. In the To section, select Property.

  32. Select the jca.file.FileName property and click OK.

  33. Click OK.

    The insert operation section appears, as shown in Figure 43-61.

    Figure 43-61 Insert Operation with EBS Target Service

    Description of Figure 43-61 follows
    Description of "Figure 43-61 Insert Operation with EBS Target Service"

  34. From the File menu, select Save All.

43.9.1.9.2 To create routing rules for the update operation:

Perform the following tasks to create routing rules for the update operation.

  1. In the Routing Rules section, click the Create a new Routing Rule icon.

    The Target Type dialog is displayed.

  2. Select Service.

    The Target Services dialog is displayed.

  3. Navigate to XrefOrderApp > References > EBS.

  4. Select EBS and click OK.

  5. Click the Transformation icon next to the Transform Using field.

    The Request Transformation map dialog is displayed.

  6. Select Create New Mapper File and enter COMMON_TO_EBS_UPDATE.xsl.

  7. Click OK.

    The COMMON_TO_EBS_UPDATE.xsl file is displayed.

  8. Drag and drop the inp1:Orders source element to the db:InputParameters target element.

    The Auto Map Preferences dialog is displayed.

  9. Click OK.

    The transformation is created, as shown in Figure 43-39.

  10. Drag and drop the lookupXRef function from the Components window to the line connecting inp1:id and db:X_APP_ID.

  11. Double-click the lookupXRef icon.

    The Edit Function: lookupXRef dialog is displayed.

  12. Enter this information in the following fields:

    • xrefLocation: order.xref

    • referenceColumnName: Common

    • referenceValue: /inp1:Customers/inp1:Order/inp1:Id

    • columnName: EBS_i75

    • needException: true()

  13. Click OK.

  14. From the File menu, select Save All and close the COMMON_TO_EBS_UPDATE.xsl file.

  15. In the Synchronous Reply section, click Browse for target service operations.

    The Target Type dialog is displayed.

  16. Select Service.

    The Target Services dialog is displayed.

  17. Navigate to XrefOrderApp > References > Logger.

  18. Select Write and click OK.

  19. Next to the Transform Using field, click the Transformation icon.

    The Reply Transformation map dialog is displayed.

  20. Select Create New Mapper File and enter EBS_TO_COMMON_UPDATE.xsl.

  21. Click OK.

    The EBS_TO_COMMON_UPDATE.xsl file is displayed.

  22. Connect the db:X:APP_ID source element to db:X:APP_ID.

  23. From the File menu, select Save All and close the EBS_TO_COMMON_UPDATE.xsl file.

  24. In the Synchronous Reply section, click the Assign Values icon.

    The Assign Values dialog is displayed.

  25. Click Add.

    The Assign Value dialog is displayed.

  26. In the From section, select Expression.

  27. Click the Invoke Expression Builder icon.

    The Expression Builder dialog is displayed.

  28. In the Expression field, enter the following expression, and click OK. 

    concat('UPDATE-',$in.OutputParameters/db:OutputParameters/db:X_APP_ID,'.xml')

  29. In the To section, select Property.

  30. Select the jca.file.FileName property and click OK.

  31. Click OK.

    The update operation section appears, as shown in Figure 43-62.

    Figure 43-62 Update Operation with EBS Target Service

    Description of Figure 43-62 follows
    Description of "Figure 43-62 Update Operation with EBS Target Service"

  32. From the File menu, select Save All.

43.9.1.10 Task 10: How to Configure an Application Server Connection

An application server connection is required for deploying your SOA composite application. For information about creating an application server connection, see Creating an Application Server Connection.

43.9.1.11 Task 11: How to Deploy the Composite Application

Deploying the XrefOrderApp composite application to the application server consists of the following steps:

  • Creating an application deployment profile

  • Deploying the application to the application server

For detailed information about these steps, see How to Deploy a Single SOA Composite in Oracle JDeveloper.