Saturday, 11 February 2012

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.

1 comment:

  1. can u plz tell us step by step..i'm new to this concept i'm not understanding which is referece table what,which value we have to take willl you plz clarify it.

    ReplyDelete