47 Working with Cross References

This chapter describes how to use cross referencing feature of Oracle SOA Suite to associate identifiers for equivalent entities created in different applications.

This chapter includes the following sections:

47.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 the domain value maps enables you to specify values at design time. For more information about domain value maps, see Chapter 46, "Working with Domain Value Maps".

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

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

Table 47-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 in 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 47-1. The updated cross reference table would appear, as shown in Table 47-2.

Table 47-2 Cross Reference Table with Common Column

SAP EBS SBL Common

SAP_001

EBS_1001

SBL001

CM001

SAP_002

EBS_1002

SBL002

CM002

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

Figure 47-1 Common Value Integration Pattern Example

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

A cross reference table consists of the following two parts: metadata and the actual data. The metadata is the .xref file created in Oracle JDeveloper, and is stored in Metadata Services (MDS) 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 create a cross reference table in a SOA composite application of Oracle JDeveloper and then use it to look up for column values at runtime. However, before using a cross reference to lookup a particular value, you must populate it at runtime. This can be done by using the cross reference XPath functions. The XPath functions enable you to populate a cross reference, perform lookups, and delete a column value. These XPath functions can be used in the Expression builder dialog to create an expression or in the XSLT Mapper to create transformations.

You can access the Expression builder dialog through Assign activity, XSL transformation, or Filter functionality of a BPEL service component or a Mediator service component. Figure 47-2 shows how you can select the cross reference functions in the Expression builder dialog.

Figure 47-2 Expression Builder Dialog with Cross Reference Functions

Description of Figure 47-2 follows
Description of "Figure 47-2 Expression Builder Dialog with Cross Reference Functions"

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

Figure 47-3 XSLT Mapper Dialog with Cross Reference Functions

Description of Figure 47-3 follows
Description of "Figure 47-3 XSLT Mapper Dialog with Cross Reference Functions"

47.2 Creating and Modifying Cross Reference Tables

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

47.2.1 Creating a Cross Reference Table

To create a cross reference table:

  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 Categories and then select Transformations.

  4. Select Cross Reference(XREF) from Items.

  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 identify a cross reference table uniquely. 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 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 47-4.

    Figure 47-4 Create Cross Reference(XREF) File Dialog

    Description of Figure 47-4 follows
    Description of "Figure 47-4 Create Cross Reference(XREF) File Dialog"

  9. Click OK.

    The Cross Reference(XREF) editor is displayed, as shown in Figure 47-5. You can use this editor to modify the cross reference.

    Figure 47-5 Cross Reference Editor

    Description of Figure 47-5 follows
    Description of "Figure 47-5 Cross Reference Editor"

47.2.1.1 What Happens When You Create a Cross Reference

A file with extension .xref gets created and appears in the Application Navigator. All .xref files are based on the schema definition (XSD) file shown in Example 47-1.

Example 47-1 Cross Reference XSD File

<?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>

47.2.2 Adding 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.

47.3 Populating Cross Reference Tables

A cross reference table needs 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.

47.3.1 About 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 47-3, the Order table has the following columns: EBS, Common, and SBL with values E100, 100, and SBL_001 respectively.

Table 47-3 Cross Reference Table with Single Column Values

EBS Common SBL

E100

100

SBL_001

The syntax of the xref:populateXRefRow function is as follows:

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 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 47-4 describes these modes.

Table 47-4 xref:populateXRefRow Function Modes

Mode Description Exception Reasons

ADD

Adds the reference value and the value to be added.

For example, xref:populateXRefRow("customers.xref","EBS","EBS100", "Common","CM001","ADD") adds the reference value EBS100 in the ESB reference column and value CM001 in the Common column.

Exception can occur because of 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, 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.

Exception can occur because of 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, xref:populateXRefRow("customers.xref","SBL","SBL_001", "SBL","SBL_1001","Update")updates the value SBL_001 in the SBL column to value SBL_1001.

Exception can occur because of 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 the upper case only as shown in Table 47-4.

Table 47-5 describes the xref:populateXRefRow function modes and exception conditions for these modes.

Table 47-5 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

47.3.2 About 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 47-6, SAP_001 and SAP_0011 values refer to one value of the EBS and the SBL application. To populate the columns such as SAP column, you can use the xref:populateXRefRow1M function.

Table 47-6 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 as follows:

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 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 47-7 describes these modes:

Table 47-7 xref:populateXRefRow1M Function Modes

Mode Description Exception Reasons

ADD

Adds the reference value and the value to be added. For example, 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.

Exception can occur because of 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, 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.

Exception can occur because of 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 47-8 describes the xref:populateXRefRow1M function modes and exception conditions for these modes.

Table 47-8 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

47.3.3 How to Populate a Column of a Cross Reference Table

To populate a column of a cross reference table:

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

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

  3. In the Component Palette, 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 47-6.

    Figure 47-6 Edit Function – populateXRefRow Dialog

    Description of Figure 47-6 follows
    Description of "Figure 47-6 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 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 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 XPath Building Assistant.

    6. In the mode field, enter a mode in which you want to populate the cross reference table column. For example, 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 47-7.

    Figure 47-7 Populated Edit Function – populateXRefRow Dialog

    Description of Figure 47-7 follows
    Description of "Figure 47-7 Populated Edit Function – populateXRefRow Dialog"

47.4 Looking Up Cross Reference Tables

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

47.4.1 About xref:lookupXRef Function

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

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

The syntax of the xref:lookupXRef function is as follows:

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 reference column name.

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

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

Exception Reasons

At runtime, an exception can occur because of 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.

  • If multiple values are found.

47.4.2 About xref:lookupXRef1M Function

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

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

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

The syntax of the xref:lookupXRefRow1M function is as follows:

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 reference column name.

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

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

Example

Consider the Order table with the following three columns: Siebel, Billing1,Billing2

Table 47-9 Order Table

Siebel Billing1 Billing2

100

101

102

110

  

111

112

For 1:1 mapping, the xref:lookupPopulatedColumns("Order","Siebel","100","false") method returns

<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") returns

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

In this case, Billing1 is not populated.

Exception Reasons

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

47.4.3 About 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 specific values. The xref:lookupPopulatedColumns function returns a nodeset with each node containing a column name and the corresponding value.

The syntax of the xref:LookupPopulatedColumns function is as follows:

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 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 because of 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.

47.4.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 dialog, expand the trees in the Source and Target panes.

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

  3. In the Component Palette, 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 47-8.

    Figure 47-8 Edit Function – lookupXRef Dialog

    Description of Figure 47-8 follows
    Description of "Figure 47-8 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 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 else select No.

  8. Click OK.

    A populated Edit Function – lookupXRef dialog is shown in Figure 47-9.

    Figure 47-9 Populated Edit Function – lookupXRef Dialog

    Description of Figure 47-9 follows
    Description of "Figure 47-9 Populated Edit Function – lookupXRef Dialog"

47.5 Deleting a Cross Reference Table Value

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

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

Note:

Using a column value marked for delete as a reference value in the LINK mode of xref:populateXRefRow function, raises an error.

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 delete, then the value in another column is also deleted.

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

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

Parameters

  • xrefTableName: The cross reference table name.

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

  • xrefValueToDelete: The value to be deleted.

Exception Reasons

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

47.5.1 How to Delete a Cross Reference Table Value

To delete a cross reference table value:

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

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

  3. In the Component Palette, 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 47-10.

    Figure 47-10 Edit Function – markForDelete Dialog

    Description of Figure 47-10 follows
    Description of "Figure 47-10 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 flashlight 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 shared location in MDS by using the Resource Palette.

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

      Click the flashlight 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, you can manually enter a value or press Ctrl-Space to launch 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 47-11.

    Figure 47-11 Populated Edit Function – markForDelete Dialog

    Description of Figure 47-11 follows
    Description of "Figure 47-11 Populated Edit Function – markForDelete Dialog"

  8. Click OK.

47.6 Creating and Running Cross Reference Use Case

The 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 47-12 provides an overview of this use case.

Figure 47-12 XrefCustApp Use Case in SOA Composite Editor

Description of Figure 47-12 follows
Description of "Figure 47-12 XrefCustApp Use Case in SOA Composite Editor"

For downloading the sample files mentioned in this section, visit the following URL:

http://www.oracle.com/technology/sample_code/products/mediator

47.6.1 Step-By-Step Instructions for Creating 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.

47.6.1.1 Task 1: Configuring Oracle Database and Database Adapter

To configure Oracle Database and the Database adapter

  1. You need 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 OH/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, available in the $BEAHOME/META-INF folder 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 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 the Weblogic console.

  8. Create a data source using the Weblogic 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 Weblogic 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

47.6.1.2 Task 2: Creating an Oracle JDeveloper Application and Project

To create an 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 screen appears.

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

    The Configure SOA settings for the SOA project screen appears.

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

    The Applications Navigator of Oracle JDeveloper is updated with the new application and project and the Design tab contains, a blank palette.

  7. From the File menu, click Save All.

47.6.1.3 Task 3: Creating 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 Application Navigator, 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 End System name.

    The Cross Reference Editor would appear as shown in Figure 47-13.

    Figure 47-13 Customer Cross Reference

    Description of Figure 47-13 follows
    Description of "Figure 47-13 Customer Cross Reference"

  10. From the File menu, click Save All and close the Cross Reference Editor.

47.6.1.4 Task 4: Creating a Database Adapter Service

To create a Database adapter service:

  1. In the Component Palette, select SOA.

  2. Select Database Adapter and drag it to the Exposed Services design area.

    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 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 47-14 shows the Logical Delete page of the Adapter Configuration Wizard.

    Figure 47-14 Logical Delete Page: Adapter Configuration Wizard

    Description of Figure 47-14 follows
    Description of "Figure 47-14 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 SAP is created, as shown in Figure 47-15.

    Figure 47-15 SAP Database Adapter Service in SOA Composite Editor

    Description of Figure 47-15 follows
    Description of "Figure 47-15 SAP Database Adapter Service in SOA Composite Editor"

  27. From the File menu, click Save All.

47.6.1.5 Task 5: Creating EBS and SBL External References

To create external references named EBS and SBL:

  1. In the Component Palette, select SOA.

  2. Select Database Adapter and drag it to the External References design area.

    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 47-16.

    Figure 47-16 Stored Procedure Dialog

    Description of Figure 47-16 follows
    Description of "Figure 47-16 Stored Procedure Dialog"

  13. Click OK.

    The Specify Stored Procedure page appears as shown in Figure 47-17.

    Figure 47-17 Specify Stored Procedure Page of Adapter Configuration Wizard

    Description of Figure 47-17 follows
    Description of "Figure 47-17 Specify Stored Procedure Page of Adapter Configuration Wizard"

  14. Click Next.

    The Finish page is displayed.

  15. Click Finish.

    Figure 47-18 shows the EBS reference in SOA Composite Editor.

    Figure 47-18 EBS Reference in SOA Composite Editor

    Description of Figure 47-18 follows
    Description of "Figure 47-18 EBS Reference in SOA Composite Editor"

  16. From the File menu, click Save All.

  17. Repeat Step 2 through Step 16 to create another external references names SBL.

    After completing this task, the SOA Composite Editor would appear as shown in Figure 47-19.

    Figure 47-19 SBL Reference in SOA Composite Editor

    Description of Figure 47-19 follows
    Description of "Figure 47-19 SBL Reference in SOA Composite Editor"

47.6.1.6 Task 6: Creating Logger External Reference

To create a file adapter reference:

  1. From the Component Palette, select SOA.

  2. Select File Adapter and drag it to the External References design area.

    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 where 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 47-20 shows the Logger reference in the SOA Composite Editor.

    Figure 47-20 Logger Reference in SOA Composite Editor

    Description of Figure 47-20 follows
    Description of "Figure 47-20 Logger Reference in SOA Composite Editor"

  15. From the File menu. click Save All.

47.6.1.7 Task 7: Creating Mediator Components

To create a Mediator:

  1. Drag and drop a Mediator from Components Palette to the Components design area.

    The Create Mediator dialog is displayed.

  2. Select Define Interface Later from Template.

  3. Click OK.

    A Mediator with name Mediator1 is created.

  4. Connect the SAP service to the Mediator1 as shown in Figure 47-21.

    Figure 47-21 SAP Service Connected to Mediator1

    Description of Figure 47-21 follows
    Description of "Figure 47-21 SAP Service Connected to Mediator1"

  5. Click Save All.

  6. Drag and drop another Mediator from Components Palette to the Components design area.

    The Create Mediator dialog is displayed.

  7. Select Interface Definition From WSDL from Template.

  8. Deselect Create Composite Service with SOAP Bindings.

  9. Click Find Existing WSDLs to the right of the WSDL File field.

  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.

    A Mediator with name Common is created.

47.6.1.8 Task 8: Specifying Routing Rules for Mediator Component

You must specify routing rules for following operations:

  • Insert

  • Update

  • UpdateID

  • Delete

To create routing rules for insert operation:

  1. Double-click Mediator1 Mediator.

    The Mediator Editor is displayed.

  2. In Routing Rules panel, 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. Enter the following expression in the Expression field:

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

  8. Click OK.

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

    The Request Transformation map dialog is displayed.

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

  11. Click OK.

    A SAP_TO_COMMON_INSERT.xsl tab is displayed.

  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 47-22.

    Figure 47-22 SAP_TO_COMMON_INSERT.xsl Transformation

    Description of Figure 47-22 follows
    Description of "Figure 47-22 SAP_TO_COMMON_INSERT.xsl Transformation"

  15. From the Components Palette, select Advanced.

  16. Select XREF Functions.

  17. Drag and drop the populateXRefRow function from Components Palette to the line connecting 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 xrefLocation field.

    The SCA 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 47-23 shows populated Edit Function – populateXRefRow dialog.

    Figure 47-23 Edit Function – populateXRefRow Dialog: XrefCustApp Use Case

    Description of Figure 47-23 follows
    Description of "Figure 47-23 Edit Function – populateXRefRow Dialog: XrefCustApp Use Case"

  26. Click OK.

  27. From the File menu, click Save All and close the SAP_TO_COMMON_INSERT.xsl tab.

    The Routing Rules panel would appear as shown in Figure 47-24.

    Figure 47-24 Routing Rules Panel with Insert Operation

    Description of Figure 47-24 follows
    Description of "Figure 47-24 Routing Rules Panel with Insert Operation"

To create routing rules for update operation:

Perform the following tasks to create routing rules for Update operation:

  1. In Routing Rules panel, 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. Enter the following expression in the Expression field:

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

  7. Click OK.

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

    The Request Transformation map dialog is displayed.

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

  10. Click OK.

    A SAP_TO_COMMON_UPDATE.xsl tab 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 Palette, select Advanced.

  14. Select XREF Functions.

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

  16. Double-click the lookupXRef icon.

    The Edit Function-lookupXRef dialog is displayed.

  17. Click Search to the right of xrefLocation field.

    The SCA 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 47-25 shows populated Edit Function – looupXRef dialog.

    Figure 47-25 Edit Function – lookupXRef Dialog: XrefCustApp Use Case

    Description of Figure 47-25 follows
    Description of "Figure 47-25 Edit Function – lookupXRef Dialog: XrefCustApp Use Case"

  23. Click OK.

  24. From the File menu, click Save All and close the SAP_TO_COMMON_UPDATE.xsl tab.

    The Routing Rules panel would appear as shown in Figure 47-26.

    Figure 47-26 Insert Operation and Update Operation

    Description of Figure 47-26 follows
    Description of "Figure 47-26 Insert Operation and Update Operation"

To create routing rules for updateID operation:

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

  1. In Routing Rules panel, 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. Enter the following expression in the Expression field:

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

  7. Click OK.

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

    The Request Transformation map dialog is displayed.

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

  10. Click OK.

    A SAP_TO_COMMON_UPDATEID.xsl tab 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 Palette, select Advanced.

  14. Select XREF Functions.

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

  16. Double-click the populateXRefRow icon.

    The Edit Function-populateXRefRow dialog is displayed.

  17. Click Search to the right of xrefLocation field.

    The SCA 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 47-27 shows a populated Edit Function – populateXRefRow dialog.

    Figure 47-27 Edit Function – populateXRefRow Dialog: XrefCustApp Use Case

    Description of Figure 47-27 follows
    Description of "Figure 47-27 Edit Function – populateXRefRow Dialog: XrefCustApp Use Case"

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

  25. Double-click the lookupXRef icon.

    The Edit Function-lookupXRef dialog is displayed.

  26. Click Search to the right of xrefLocation field.

    The SCA 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 47-28 shows a populated Edit Function – lookupXRef dialog.

    Figure 47-28 Edit Function – lookupXRef Dialog: XrefCustApp Use Case

    Description of Figure 47-28 follows
    Description of "Figure 47-28 Edit Function – lookupXRef Dialog: XrefCustApp Use Case"

  32. Click OK.

  33. Click Save All and close the SAP_TO_COMMON_UPDATEID.xsl window.

    The Routing Rules panel would appear as shown in Figure 47-29.

    Figure 47-29 Insert, Update, and UpdateID Operations

    Description of Figure 47-29 follows
    Description of "Figure 47-29 Insert, Update, and UpdateID Operations"

To create routing rules for delete operation:

  1. In Routing Rules panel, 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. Enter the following expression in the Expression field:

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

  7. Click OK.

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

    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 tab 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 the 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 Palette, select Advanced.

  24. Select XREF Functions.

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

  26. Double-click the markForDelete icon.

    The Edit Function-markForDelete dialog is displayed.

  27. Click Search to the right of xrefLocation field.

    The SCA 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 47-30 shows a populated Edit Function – markForDelete dialog.

    Figure 47-30 Edit Function – markForDelete Dialog: XrefCustApp Use Case

    Description of Figure 47-30 follows
    Description of "Figure 47-30 Edit Function – markForDelete Dialog: XrefCustApp Use Case"

  31. Click OK.

    The SAP_TO_COMMON_DELETE.xsl would appear as shown in Figure 47-31.

    Figure 47-31 SAP_TO_COMMON_DELETE.xsl

    Description of Figure 47-31 follows
    Description of "Figure 47-31 SAP_TO_COMMON_DELETE.xsl"

  32. Click Save All and close the SAP_TO_COMMON_DELETE.xsl tab.

    The Routing Rules panel would appear as shown in Figure 47-32.

    Figure 47-32 Insert, Update, UpdateID, and Delete Operations

    Description of Figure 47-32 follows
    Description of "Figure 47-32 Insert, Update, UpdateID, and Delete Operations"

47.6.1.9 Task 9: Specifying Routing Rules for Common Mediator

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

  • Insert

  • Delete

  • Update

  • UpdateID

To create routing rules for insert operation:

  1. Double-click Common Mediator.

    The Mediator Editor is displayed.

  2. In Routing Rules panel, 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. Click the Transformation icon next to the Transform Using field.

    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 tab 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 47-33.

    Figure 47-33 COMMON_TO_SBL_INSERT.xsl Transformation

    Description of Figure 47-33 follows
    Description of "Figure 47-33 COMMON_TO_SBL_INSERT.xsl Transformation"

  11. From the File menu, click Save All and close the COMMON_TO_SBL_INSERT.xsl window.

  12. In the Synchronous Reply panel, 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. Click the Transformation icon next to the Transform Using field.

    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 window is displayed.

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

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

  22. Double-click the populateXRefRow icon.

    The Edit Function-populateXRefRow dialog is displayed.

  23. Enter the 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 would appear as shown in Figure 47-34.

    Figure 47-34 SBL_TO_COMMON_INSERT.xsl Transformation

    Description of Figure 47-34 follows
    Description of "Figure 47-34 SBL_TO_COMMON_INSERT.xsl Transformation"

  25. From the File menu, click Save All and close the SBL_TO_COMMON_INSERT.xsl tab.

  26. In the Synchronous Reply panel, 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. Enter the following expression in the Expression field and click OK.

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

  31. In the To section, select Property.

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

  33. Click OK.

    The insert operation panel would appear as shown in Figure 47-35.

    Figure 47-35 Insert Operation with SBL Target Service

    Description of Figure 47-35 follows
    Description of "Figure 47-35 Insert Operation with SBL Target Service"

  34. From the File menu, click Save All.

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

    Figure 47-36 shows the insert operation panel with SBL and EBS target service.

    Figure 47-36 Insert Operation with SBL and EBS Target Service

    Description of Figure 47-36 follows
    Description of "Figure 47-36 Insert Operation with SBL and EBS Target Service"

To create routing rules for delete operation:

Perform the following tasks to create the routing rules for delete operation:

  1. In Routing Rules panel, 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_DELETE.xsl.

  7. Click OK.

    A COMMON_TO_SBL_DELETE.xsl tab 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 47-37.

    Figure 47-37 COMMON_TO_SBL_DELETE.xsl Transformation

    Description of Figure 47-37 follows
    Description of "Figure 47-37 COMMON_TO_SBL_DELETE.xsl Transformation"

  10. Drag and drop the lookupXRef function from Components Palette 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 the 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, click Save All and close the COMMON_TO_SBL_DELETE.xsl window.

  15. In the Synchronous Reply panel, 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. Click the Transformation icon next to the Transform Using field.

    The Reply Transformation map dialog is displayed.

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

  21. Click OK.

    A SBL_TO_COMMON_DELETE.xsl window is displayed.

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

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

  24. Double-click the markForDelete icon.

    The Edit Function-markForDelete dialog is displayed.

  25. Enter the 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, click Save All and close the SBL_TO_COMMON_DELETE.xsl tab.

  28. In the Synchronous Reply panel, 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. Enter following expression in the Expression field and click OK.

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

  33. In the To section, select Property.

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

  35. Click OK.

    The delete operation panel would appear as shown in Figure 47-38.

    Figure 47-38 Delete Operation with SBL Target Service

    Description of Figure 47-38 follows
    Description of "Figure 47-38 Delete Operation with SBL Target Service"

  36. From the File menu, click Save All.

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

    Figure 47-39 shows the delete operation panel with SBL and EBS target service.

    Figure 47-39 Delete Operation with SBL and EBS Target Service

    Description of Figure 47-39 follows
    Description of "Figure 47-39 Delete Operation with SBL and EBS Target Service"

To create routing rules for update operation:

Perform the following tasks to create routing rules for Update operation:

  1. In Routing Rules panel, 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 tab 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 47-37.

  10. Drag and drop the lookupXRef function from Components Palette 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 the 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, click Save All and close the COMMON_TO_SBL_UPDATE.xsl window.

  15. In the Synchronous Reply panel, 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. Click the Transformation icon next to the Transform Using field.

    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 window is displayed.

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

  23. From the File menu, click Save All and close the SBL_TO_COMMON_UPDATE.xsl tab.

  24. In the Synchronous Reply panel, 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. Enter following expression in the Expression field and click OK.

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

  29. In the To section, select Property.

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

  31. Click OK.

    The update operation panel would appear as shown in Figure 47-40.

    Figure 47-40 Update Operation with SBL Target Service

    Description of Figure 47-40 follows
    Description of "Figure 47-40 Update Operation with SBL Target Service"

  32. From the File menu, click Save All.

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

    Figure 47-41 shows the update operation panel with SBL and EBS target service.

    Figure 47-41 Update Operation with SBL and EBS Target Service

    Description of Figure 47-41 follows
    Description of "Figure 47-41 Update Operation with SBL and EBS Target Service"

To create routing rules for updateID operation:

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

  1. In Routing Rules panel, 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_UPDATEID.xsl.

  7. Click OK.

    A COMMON_TO_SBL_UPDATEID.xsl tab is displayed.

  8. Drag and drop 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 47-37.

  10. Drag and drop the lookupXRef function from Components Palette 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 the 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, click Save All and close the COMMON_TO_SBL_UPDATEID.xsl window.

  15. In the Synchronous Reply panel, 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. Click the Transformation icon next to the Transform Using field.

    The Reply Transformation map dialog is displayed.

  20. Select Include Request in the Reply Payload.

  21. Click OK.

    A SBL_TO_COMMON_UPDATEID.xsl window is displayed.

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

  23. Drag and drop the populateXRefRow function from Component Palette to the connecting line.

  24. Double-click the populateXRefRow icon.

    The Edit Function-populateXRefRow dialog is displayed.

  25. Enter the 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, click Save All and close the SBL_TO_COMMON_UPDATEID.xsl tab.

  28. In the Synchronous Reply panel, 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. Enter following expression in the Expression field and click OK.

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

  33. In the To section, select Property.

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

  35. Click OK.

    The updateid operation panel would appear as shown in Figure 47-42.

    Figure 47-42 Updateid Operation with SBL Target Service

    Description of Figure 47-42 follows
    Description of "Figure 47-42 Updateid Operation with SBL Target Service"

  36. From the File menu, click Save All.

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

    Figure 47-43 shows the updateid operation panel with SBL and EBS target service.

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

    Description of Figure 47-43 follows
    Description of "Figure 47-43 Updateid Operation with SBL and EBS Target Service"

47.6.1.10 Task 10: Configuring Oracle Application Server Connection

An Oracle Application Server connection is required for deploying your SOA composite application. For information on creating Oracle Application Server connection, refer to Oracle Fusion Middleware User's Guide for Technology Adapters.

47.6.1.11 Task 11: Deploying the Composite Application

Deploying the XrefCustApp composite application to Oracle Application Server consists of following steps:

  • Creating an Application Deployment Profile

  • Deploying the Application to Oracle Application Server

For detailed information about these steps, see Section 43.2, "Deploying a Single SOA Composite in Oracle JDeveloper".

47.6.2 Running and Monitoring 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 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 Console at the following URL:

http://hostname:portnumber/em

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

47.7 Creating and Running Cross Reference for 1M Functions

The cross reference use case implements an integration scenario between two end-systems 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 order ID1 and ID2. This scenario is created using Database Adapters. When you poll the SAP table for updated or created records, a SAP instance is created. In EBS, the instance is simulated by a procedure and the table is populated. Figure 47-44 provides an overview of this use case.

Figure 47-44 XrefOrderApp Use Case in SOA Composite Editor

Description of Figure 47-44 follows
Description of "Figure 47-44 XrefOrderApp Use Case in SOA Composite Editor"

For downloading the sample files mentioned in this section, visit the following URL:

http://www.oracle.com/technology/sample_code/products/mediator

47.7.1 Step-By-Step Instructions for Creating 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.

47.7.1.1 Task 1: Configuring Oracle Database and Database Adapter

To configure Oracle Database and the Database adapter

  1. You need 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 OH/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, available in the $BEAHOME/src/oracle/tip/adapter/db/test/deploy/weblogic/META-INF folder from the ADE label that you are using 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 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 the Weblogic console.

  8. Create a data source using the Weblogic 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 Weblogic 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

47.7.1.2 Task 2: Creating an Oracle JDeveloper Application and Project

To create an 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 screen appears.

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

    The Configure SOA Settings screen appears.

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

    The Applications Navigator of Oracle JDeveloper is updated with the new application and project and the Design tab contains, a blank palette.

  7. From the File menu, click Save All.

47.7.1.3 Task 3: Creating 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 Application Navigator, 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 End System name.

    The Cross Reference Editor would appear as shown in Figure 47-45.

    Figure 47-45 Customer Cross Reference

    Description of Figure 47-45 follows
    Description of "Figure 47-45 Customer Cross Reference"

  9. From the File menu, click Save All and close the Cross Reference Editor.

47.7.1.4 Task 4: Creating a Database Adapter Service

To create a Database adapter service:

  1. In the Component Palette, select SOA.

  2. Select Database Adapter and drag it to the Exposed Services design area.

    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 Schema.

  12. In the Name Filter field, enter %SAP% and click Query.

    The Available field is populated with 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 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 47-14 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 SAP is created, as shown in Figure 47-46.

    Figure 47-46 SAP Database Adapter Service in SOA Composite Editor

    Description of Figure 47-46 follows
    Description of "Figure 47-46 SAP Database Adapter Service in SOA Composite Editor"

  28. From the File menu, click Save All.

47.7.1.5 Task 5: Creating EBS External Reference

To create external references named EBS:

  1. In the Component Palette, select SOA.

  2. Select Database Adapter and drag it to the External References design area.

    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 Schema.

  11. Click Browse.

    The Stored Procedures dialog is displayed.

  12. Select POPULATE_APP_INSTANCE_IM as shown in Figure 47-47.

    Figure 47-47 Stored Procedure Dialog

    Description of Figure 47-47 follows
    Description of "Figure 47-47 Stored Procedure Dialog"

  13. Click OK.

    The Specify Stored Procedure page appears as shown in Figure 47-48.

    Figure 47-48 Specify Stored Procedure Page of Adapter Configuration Wizard

    Description of Figure 47-48 follows
    Description of "Figure 47-48 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 47-49 shows the EBS reference in SOA Composite Editor.

    Figure 47-49 EBS Reference in SOA Composite Editor

    Description of Figure 47-49 follows
    Description of "Figure 47-49 EBS Reference in SOA Composite Editor"

  17. From the File menu, click Save All.

47.7.1.6 Task 6: Creating Logger External Reference

To create a file adapter reference:

  1. From the Component Palette, select SOA.

  2. Select File Adapter and drag it to the External References design area.

    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 where 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 47-50 shows the Logger reference in the SOA Composite Editor.

    Figure 47-50 Logger Reference in SOA Composite Editor

    Description of Figure 47-50 follows
    Description of "Figure 47-50 Logger Reference in SOA Composite Editor"

  16. From the File menu. click Save All.

47.7.1.7 Task 7: Creating Mediator Components

To create a Mediator:

  1. Drag and drop a Mediator from Components Palette to the Components design area.

    The Create Mediator dialog is displayed.

  2. Select Define Interface Later from Template.

  3. Click OK.

    A Mediator with name Mediator2 is created.

  4. Connect the SAP service to the Mediator2 as shown in Figure 47-51.

    Figure 47-51 SAP Service Connected to Mediator2

    Description of Figure 47-51 follows
    Description of "Figure 47-51 SAP Service Connected to Mediator2"

  5. Click Save All.

  6. Drag and drop another Mediator from Components Palette to the Components design area.

    The Create Mediator dialog is displayed.

  7. Select Interface Definition From WSDL from Template.

  8. Deselect Create Composite Service with SOAP Bindings.

  9. Click Find Existing WSDLs to the right of the WSDL File field.

  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.

    A Mediator with name Common is created.

47.7.1.8 Task 8: Specifying Routing Rules for Mediator Component

You must specify routing rules for following operations:

  • Insert

  • Update

To create routing rules for insert operation:

  1. Double-click Mediator2 Mediator.

    The Mediator Editor is displayed.

  2. In Routing Rules panel, 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. Enter the following expression in the Expression field:

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

  8. Click OK.

  9. Click the Transformation icon next to the Using Transformation field.

    The Request Transformation map dialog is displayed.

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

  11. Click OK.

    A SAP_TO_COMMON_INSERT.xsl tab is displayed.

  12. Drag and drop top:SAP05 source element to the inp1:Order 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 47-52.

    Figure 47-52 SAP_TO_COMMON_INSERT.xsl Transformation

    Description of Figure 47-52 follows
    Description of "Figure 47-52 SAP_TO_COMMON_INSERT.xsl Transformation"

  15. From the Components Palette, select Advanced.

  16. Select XREF Functions.

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

  18. Double-click the populateXRefRow1M icon.

    The Edit Function-populateXRefRow dialog is displayed.

  19. Click Search to the right of xrefLocation field.

    The SCA 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 47-53 shows populated Edit Function – populateXRefRow1M dialog.

    Figure 47-53 Edit Function – populateXRefRow1M Dialog: XrefOrderApp Use Case

    Description of Figure 47-53 follows
    Description of "Figure 47-53 Edit Function – populateXRefRow1M Dialog: XrefOrderApp Use Case"

  26. Click OK.

  27. From the File menu, click Save All and close the SAP_TO_COMMON_INSERT.xsl tab.

    The Routing Rules panel would appear as shown in Figure 47-54.

    Figure 47-54 Routing Rules Panel with Insert Operation

    Description of Figure 47-54 follows
    Description of "Figure 47-54 Routing Rules Panel with Insert Operation"

To create routing rules for update operation:

Perform the following tasks to create routing rules for Update operation:

  1. In Routing Rules panel, 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. Enter the following expression in the Expression field:

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

  7. Click OK.

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

    The Request Transformation map dialog is displayed.

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

  10. Click OK.

    A SAP_TO_COMMON_UPDATE.xsl tab 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 Palette, select Advanced.

  14. Select XREF Functions.

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

  16. Double-click the lookupXRef icon.

    The Edit Function-lookupXRef dialog is displayed.

  17. Click Search to the right of xrefLocation field.

    The SCA 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 47-55 shows populated Edit Function – looupXRef dialog.

    Figure 47-55 Edit Function – looupXRef Dialog: XRefOrderApp Use Case

    Description of Figure 47-55 follows
    Description of "Figure 47-55 Edit Function – looupXRef Dialog: XRefOrderApp Use Case"

  23. Click OK.

  24. From the File menu, click Save All and close the SAP_TO_COMMON_UPDATE.xsl tab.

    The Routing Rules panel would appear as shown in Figure 47-56.

    Figure 47-56 Insert Operation and Update Operation

    Description of Figure 47-56 follows
    Description of "Figure 47-56 Insert Operation and Update Operation"

47.7.1.9 Task 9: Specifying Routing Rules for Common Mediator

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

  • Insert

  • Update

To create routing rules for insert operation:

  1. Double-click Common Mediator.

    The Mediator Editor is displayed.

  2. In Routing Rules panel, 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. Click the Transformation icon next to the Transform Using field.

    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 tab is displayed.

  9. Drag and drop 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 47-57.

    Figure 47-57 COMMON_TO_EBS_INSERT.xsl Transformation

    Description of Figure 47-57 follows
    Description of "Figure 47-57 COMMON_TO_EBS_INSERT.xsl Transformation"

  11. From the File menu, click Save All and close the COMMON_TO_EBS_INSERT.xsl window.

  12. In the Synchronous Reply panel, 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. Click the Transformation icon next to the Transform Using field.

    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 window is displayed.

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

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

  22. Double-click the populateXRefRow icon.

    The Edit Function-populateXRefRow dialog is displayed.

  23. Enter the 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 would appear as shown in Figure 47-58.

    Figure 47-58 EBS_TO_COMMON_INSERT.xsl Transformation

    Description of Figure 47-58 follows
    Description of "Figure 47-58 EBS_TO_COMMON_INSERT.xsl Transformation"

  25. From the File menu, click Save All and close the EBS_TO_COMMON_INSERT.xsl tab.

  26. In the Synchronous Reply panel, 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. Enter the following expression in the Expression field and click OK.

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

  31. In the To section, select Property.

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

  33. Click OK.

    The insert operation panel would appear as shown in Figure 47-59.

    Figure 47-59 Insert Operation with EBS Target Service

    Description of Figure 47-59 follows
    Description of "Figure 47-59 Insert Operation with EBS Target Service"

  34. From the File menu, click Save All.

To create routing rules for update operation:

Perform the following tasks to create routing rules for Update operation:

  1. In Routing Rules panel, 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.

    A COMMON_TO_EBS_UPDATE.xsl tab is displayed.

  8. Drag and drop 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 47-37.

  10. Drag and drop the lookupXRef function from Components Palette 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 the 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, click Save All and close the COMMON_TO_EBS_UPDATE.xsl window.

  15. In the Synchronous Reply panel, 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. Click the Transformation icon next to the Transform Using field.

    The Reply Transformation map dialog is displayed.

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

  21. Click OK.

    A EBS_TO_COMMON_UPDATE.xsl window is displayed.

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

  23. From the File menu, click Save All and close the EBS_TO_COMMON_UPDATE.xsl tab.

  24. In the Synchronous Reply panel, 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. Enter following expression in the Expression field and click OK.

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

  29. In the To section, select Property.

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

  31. Click OK.

    The update operation panel would appear as shown in Figure 47-60.

    Figure 47-60 Update Operation with EBS Target Service

    Description of Figure 47-60 follows
    Description of "Figure 47-60 Update Operation with EBS Target Service"

  32. From the File menu, click Save All.

47.7.1.10 Task 10: Configuring Oracle Application Server Connection

An Oracle Application Server connection is required for deploying your SOA composite application. For information on creating Oracle Application Server connection, refer to Oracle Fusion Middleware User's Guide for Technology Adapters.

47.7.1.11 Task 11: Deploying the Composite Application

Deploying the XrefOrderApp composite application to Oracle Application Server consists of following steps:

  • Creating an Application Deployment Profile

  • Deploying the Application to Oracle Application Server

For detailed information about these steps, see Section 43.2, "Deploying a Single SOA Composite in Oracle JDeveloper".