XREF table creation and lookups in SOA Suite 11g

I was working on a requirement on XREF tables in SOA Suite 11g. I would like  to share my experience here.

Sample requirement: I created two dummy tables  
SAP_EMP_TABLE 
R12_EMP_TABLE

The content of SAP_EMP_TABLE was as follows:

EMP_ID	EMPNAME	JOBCODE
1	Sharat	SAP1
2	Neeraj	SAP2
3	Abhishek	SAP1

And I wanted to transfer the data in R12_EMP_TABLE as so that

SAP1=ORC1

SAP2=ORC2

Approach:

So, First I created XREF Table inside a BPEL process(XrefCreationDemo), SAP_ORCL.xref.

I populated this XREF table with Reference values and their respective column names using the function:

xref:populateXRefRow("SAP_ORCL.xref","SAP","SAP2","ORCL","ORC2","ADD") in an assign activity.

At first I got confused because there was nothing inside SOA_INFRA.XREF_DATA table after this. But at the same time I was sure that it is working fine because it was not running second time. Which means that actually the values were getting stored somewhere. For this please check the XREF data source at Weblogic Admin and there you can find out where actual XREF_DATA is present. There I saw that whatever I inserted was present. Here is the content present in XREF_DATA table.

  

After this I created another process XrefDemo, in which I used the following inside transform activity between source table and destination table

xref:lookupXRef("oramds:/deployed-composites/XrefCreationDemo_rev1.0/SAP_ORCL.xref","SAP",ns0:jobcode,"ORCL",true())

Here you can see that the XREF table we inserted is present at oramds:/deployed-composites/XrefCreationDemo_rev1.0/ which is nothing but the project name of the process in which we populated the XREF Table.

After using xref:lookupXRef I was successful in inserting the data in R12_EMP_TABLE and its content was as follows:

EMP_ID	EMPNAME	JOBCODE
1	Sharat	ORC1
3	Abhishek	ORC1
2	Neeraj	ORC2

Conclusion: The Xref tables we create is a one-time activity and we can refer to any XREF table from oramds, if it is properly populated in XREF_DATA. We will be using BPEL process for populating the data.