The cross-referencing feature of Oracle SOA Suite enables you to associate identifiers for equivalent entities created in different applications. For example, you can use cross references to associate a customer entity created in one application (with native id Cust_100) with an entity for the same customer in another application (with native id CT_001).

Cross-referencing (XREF) facilitates mapping of native keys for entities across applications. For example, correlate the same order across different ERP systems.

The implementation of cross-referencing uses a database schema to store a cross reference information to reference records across systems and data stores.

For more information about cross references, see Working with Cross References in the Developer's Guide for Oracle SOA Suite.

The optional ability to update or delete source table data after the data is loaded into the target table is also a need in integration. This requires that the bulk integration provides support for either updating some attributes like a status field or purging the source records once they have been successfully processed to the target system.

The XREF data can be stored in multiple cross reference tables and in two formats:

• Generic (legacy) table - The table name is XREF_DATA and the table structure stores the cross references for all entities. The table format is as follows:

  XREF_TABLE_NAME  NOT NULL VARCHAR2(2000)
  XREF_COLUMN_NAME NOT NULL VARCHAR2(2000)
  ROW_NUMBER NOT NULL VARCHAR2(48)
  VALUE  NOT NULL VARCHAR2(2000)
  IS_DELETED  NOT NULL VARCHAR2(1)
  LAST_MODIFIED NOT NULL TIMESTAMP(6)
  

  This table stores cross references for multiple entities. In this table:

  • XREF_TABLE_NAME is the name of the cross reference table

  • XREF_COLUMN_NAME is the name of the column to be populated. This column name, for example the application name, is used as a unique identifier for the cross reference table.

  • ROW_NUMBER stores a unique identifier (Row Number) for a given entity instance, regardless of the application

  • VALUE is the value of the record identifier for a given entity in this application

  A specific XREF_COLUMN_NAME entry called COMMON exists to store a generated identifier that is common to all applications.

  For example, an ORDER existing in both SIEBEL and EBS will be mapped in a generic table as shown below:

  Table 29-1 Example of an XREF_DATA (Partial)

  XREF_TABLE_NAME	XREF_COLUMN_NAME	ROW_NUMBER	VALUE
  ORDER	SIEBEL	100012345	SBL_101
  ORDER	EBS	100012345	EBS_002
  ORDER	COMMON	100012345	COM_100

• Custom (new) table structure - The table is specific to one entity and has a custom structure. For example:

  ROW_ID  VARCHAR2(48) NOT NULL PK, 
  APP1   VARCHAR2(100), 
  APP2   VARCHAR2(100), 
  ...
  COMMON   VARCHAR2(100), 
  LAST_MODIFIED  TIMESTAMP NOT NULL
  

  Where:

  • Columns such as APP1 and APP2 are used to store PK values on different applications and link to the same source record

  • ROW_ID (Row Number) is used to uniquely identify records within a XREF data table.

  • COM holds the common value for the integration layer and is passed among participating applications to establish the cross reference

  The same ORDER existing in both SIEBEL and EBS would be mapped in a custom XREF_ORDER table as shown below:

  Table 29-2 Example of a Custom Table: XREF_ORDERS (Partial)

  ROW_ID	SIEBEL	EBS	COMMON
  100012345	SBL_101	EBS_002	COM_100

  See Designing a Mapping with the Cross-References KMs and Knowledge Module Options Reference for more information.

The IKM SQL Control Append (SOA XREF) provides the following parameters to handle these two table structures:

• XREF_DATA_STRUCTURE: This option can be set to legacy to use the XREF_DATA generic table, or to new to use the custom table structure.

If using the generic table structure, you must set the following options:

• XREF_TABLE_NAME: Value inserted in the XREF_TABLE_NAME column of the XREF_DATA table. In the example above (See Table 29-1) this option would be ORDER.

• XREF_COLUMN_NAME: Value inserted in the XREF_COLUMN_NAME column of the XREF_DATA table. This value corresponds to the application that is the target of the current mapping. In the example above (See Table 29-1), this option would take either the value SIEBEL or EBS depending on which system is targeted.

If using the custom table structure, you must use the following options:

• XREF_DATA_TABLE: Name of the cross reference table. It defaults to XREF_DATA. In the example above (See Table 29-2), this table name would be XREF_ORDER.

• XREF_DATA_TABLE_COLUMN: Name of the column that stores the cross references for the application that is the target of the current mapping. In the example above (See Table 29-2), this option would take either the value SIEBEL or EBS depending on which system is targeted.

During the loading phase, a Source Primary Key is created using columns from the source table. This Source Primary Key is computed using a user-defined SQL expression that should return a VARCHAR value. This expression is specified in the SRC_PK_EXPRESSION KM option.

For example, for a source Order Line Table (aliased OLINE in the mapping) you can use the following expression:

TO_CHAR(OLINE.ORDER_ID) || '-' || TO_CHAR(OLINE.LINE_ID)

This value will be finally used to populate the cross reference table.

During the integration phase, a Common ID is created for the target table. The value for the Common ID is computed from the expression in the XREF_SYS_GUID KM option. This expression can be for example:

• A database sequence (<SEQUENCE_NAME>. NEXTVAL)

• A function returning a global unique Id (SYS_GUID() for Oracle, NewID() for SQL Server)

This Common ID can also be automatically pushed to the target columns of the target table that are marked with the UD1 flag.

Both the Common ID and the Source Primary Key are pushed to the cross reference table. In addition, the IKM pushes to the cross reference table a unique Row Number value that creates the cross reference between the Source Primary Key and Common ID. This Row Number value is computed from the XREF_ROWNUMBER_EXPRESSION KM option, which takes typically expressions similar to the Common ID to generate a unique identifier.

The same Common ID is reused (and not re-computed) if the same source row is used to load several target tables across several mappings with the Cross-References KMs. This allows the creation of cross references between a unique source row and different targets rows.

This optional phase (parameterized by the SRC_UPDATE_DELETE_ACTION KM option) deletes or updates source records based on the successfully processed source records:

• If SRC_UPDATE_DELETE_ACTION takes the DELETE value, the source records processed by the mapping are deleted.

• If SRC_UPDATE_DELETE_ACTION takes the UPDATE value, a source column of the source table will be updated with an expression for all the processed source records. The following KM options parameterize this behavior:

  • SRC_UPD_COL: Name of the source column to update

  • SRC_UPD_COL_EXPRESSION: Expression used to generate the value to update in the column

It is possible to execute delete and update operations on a table different table from the source table. To do this, you must set the following KM options in the LKM:

• SRC_PK_LOGICAL_SCHEMA: Oracle Data Integrator Logical schema containing the source table to impact.

• SRC_PK_TABLE_NAME: Name of the source table to impact.

• SRC_PK_TABLE_ALIAS: Table alias for this table.