Understanding CrossReference (XREF) and XREF_DATA

In any system integration project, we always needs to different entities in different systems. e.g. CustomerAccount, BillingAccount, Products etc. Every system has different identifiers for each entity. While integrating these entities we need to make sure we send the correct Ids in the corresponding to each system. How can this be achieved? 

Simplest way to do this would be maintaining a table as below:

CustomerAccountEntity
System 1
System 2
System 3
CA001
CA_01_001
CA_RES_01
CA002
CA_02_001
CA_BUS_01

As and when the entity is created in that system, it add and entry into the respective column. 

In order to maintain consistency, let us add a common identifier between all systems, for handling for each instance of identity. Advantage of this will be middleware or integration layer will always deal with only the common identifier and will use respective identifier only when interacting with the respective system. After adding this above table will look as below:

CustomerAccountEntity
System 1
Common
System 2
System 3
CA001
CUSTACNT_0001
CA_01_001
CA_RES_01
CA002
CUSTACNT_0002
CA_02_001
CA_BUS_01

Please note though above table shows a common identifier which is custom, an easiest way to handle is using a GUID. This should solve the problem of integrating different identifiers for the entities in different systems. But, then we need to create the different tables for each entity where we need to integrate identifiers and separate logic for handling of the different entities.

This can be handled by using a generic approach, let us have a single table where entries in the different table are represented as rows. So with the new approach above values look as below:

TABLE_DATA 
ROW_NUMBER
TABLE_NAME
TABLE_COLUMN
VALUE
001
CustomerAccountEntity
System 1
CA001
001
CustomerAccountEntity
System 2
CA_01_001
001
CustomerAccountEntity
Common
CUSTACNT_001
001
CustomerAccountEntity
System 3
CA_RES_01
002
CustomerAccountEntity
System 1
CA002
002
CustomerAccountEntity
Common
CUSTACNT_002
002
CustomerAccountEntity
System 2
CA_02_001
002
CustomerAccountEntity
System 3
CA_BUS_01

Referring to above table, it can be seen that, different entries in the table CustomerAccountEntity are inserted in above table using Table name, column name and Value. In order to maintain the relation of the different columns in a table we are using Row_number. Now, we can build functions to populate the above table and to search in the above table. 
With this approach, we can add new tables for new entities also new columns without having impact on exiting functionality.  Above explanation forms the base for understanding the Cross-references or XREF in Integration. 

In Oracle SOA suite, XREF_Tables consists of two parts, metadata and actual data. Metadata part is a .xref and is stored in the MDS as XML file. The actual data part is stored in the XREF_DATA (normally part of the SOA_INFRA schema). XREF_DATA table has the below structure.
 
XREF_DATA
Column name
Constraint
Data Type
Description
XREF_TABLE_NAME
NOT NULL
VARCHAR2(2000)
Name of the XREF table
XREF_COLUMN_NAME
NOT NULL
VARCHAR2(2000)
Name of the column in XREF
ROW_NUMBER
NOT NULL
VARCHAR2(48)
Key to identify different entries of the same rows in XREF table
VALUE
NOT NULL
VARCHAR2(2000)
Values in XREF associated with column
IS_DELETED
NOT NULL
VARCHAR2(1)
Indicates whether entry is in the XREF is deleted or not.

Values: Y/N
LAST_MODIFIED
NOT NULL
TIMESTAMP(6)
Timestamp of last modification of the value in XREF
 

Using XPATH expressions, such as xref:populateXRefRow, xref:lookupXRef operations on XREF can be performed.

Leave Comment

Your email address will not be published. Required fields are marked *