6 Configuring Multiple Oracle Clinical Database Instances

This chapter discusses:

Section 6.1, "Overview"
Section 6.2, "Configuring Additional Oracle Clinical Instances"

6.1 Overview

If you have more than one instances of Oracle Clinical (OC), you can use the following guidance in configuring multiple OC environments and conditional routing of messages from Siebel Clinical (SC) to either of the OC instances.

6.2 Configuring Additional Oracle Clinical Instances

Perform the following steps to configure additional OC instances:

Update AIA System Registry. Perform the following steps:

Navigate to the AIA Console URL: http://<server name>:<portnumber>/AIA.
Log in with the admin user name.
Navigate to Setup > Systems.
Click Create to create an additional row for each instance of OC to be configured. In this example, one additional instance of OC named OC_02 is added (assuming the original instance of OC is OC_01).

Enter values in the following mandatory fields:

Table 6-1 Mandatory Fields

Field Description

Field	Description
Internal ID	The value should be same as the value returned when you run the following query in second Oracle Clinical database instance: `select *from global_name`
System Code	A logical value. For example, OC_02
System Type	Enter Oracle Clinical.

Internal ID

The value should be same as the value returned when you run the following query in second Oracle Clinical database instance:

select *from global_name

System Code

A logical value. For example, OC_02

System Type

Enter Oracle Clinical.

Modify the DVM and Xref entries.

The Domain Value Maps (DVMs) and Cross References (XRefs) are stored under $AIA_HOME/AIAMetaData/ in dvm and xref folders respectively. You need to modify the following DVMs and XRefs:
- STATE.dvm
- COUNTRY.dvm
- CLINICALSTUDYSUBJECT_STATUS.dvm
- CLINICALSTUDYSTUDYSITE.xref
- CLINICALSTUDYSITE.xref
- CLINICALSTUDY_CLINICALSTUDYSUBJECTID.xref
- CLINICALSTUDYINVESTIGATOR.xref
In the XRefs, add an additional column for OC_02 (second instance of OC and any other OC instances, as you may require). In the DVMs, add additional column name, and alter to add the column values appropriately.

The following code is an example for COUNTRY.dvm and CLINICALSTUDYINVESTIGATOR.xref. You may need to modify certain values as per your target environment.

COUNTRY.dvm
```
<?xml version='1.0' encoding='UTF-8'?>
<dvm name="COUNTRY" xmlns="http://xmlns.oracle.com/dvm"><description>Country LoVs</description>
<columns> 
<column name="COMMON"/> 
<column name="SEBLCLIN_01"/> 
<column name="OC_01"/> 
<column name="OC_02"/> 
<column name="SEBL_01"/> 
<column name="ARGUS_01"/>
</columns>
<rows> 
<row> 
<cell>Please populate</cell> 
<cell/> 
<cell/> 
<cell/> 
<cell/> 
<cell/> 
</row>
</rows>
</dvm>
```
CLINICALSTUDYINVESTIGATOR.xref
```
<?xml version="1.0" encoding="UTF-8" ?>

<xref xmlns="http://xmlns.oracle.com/xref">
 
<table name="CLINICALSTUDYINVESTIGATOR">
<description></description>
<columns>
<column name="COMMON"/>
<column name="SEBLCLIN_01"/>
<column name="OC_01"/>
<column name="OC_02"/>
</columns>
</table>
</xref>
```
To upload updated DVMs and XRefs to MDS, perform the following:
1. Navigate to the $AIA_INSTANCE/config directory.
2. Copy UpdateMetaDataDp.xml to a backup file.
3. Edit UpdateMetaDataDp.xml file as follows:
```
<?xml version="1.0" standalone="yes"?><DeploymentPlan component="Metadata" version="3.0"><Configurations><UpdateMetadata wlserver="fp"><fileset dir="${AIA_HOME}/AIAMetaData"><include name="xref/CLINICALSTUDYINVESTIGATOR.xref"/><include name="dvm/COUNTRY.dvm"/></fileset></UpdateMetadata></Configurations></DeploymentPlan>
```
4. Navigate to the $AIA_INSTANCE/bin directory.
5. Execute the following command:
  
  For Linux: source aiaenv.sh
  
  ant -f $AIA_HOME/Infrastructure/Install/config/UpdateMetaData.xml
  
  For Windows: aiaenv.bat
  
  ant -f %AIA_HOME%\Infrastructure\Install\config\UpdateMetaData.xml
6. Wait till you see a build successful result.
  
  Note:
  MetaData gets deleted when you uninstall Foundation Pack. For specific instruction on how to clean the MDS after you uninstall Foundation Pack, see Oracle Fusion Middleware Installation and Upgrade Guide for Oracle Application Integration Architecture Foundation Pack, Cleaning the MDS.
Note:
Any change made to the MDS will overwrite the files. However, the data in the Xref_data table will not be affected.

You can verify DVMs and XRefs by accessing these files in MDS. You can also verify DVMs in the SOA Composer.
Update AIAInstallProperties.xml.
1. Update the AIAInstallProperties.xml file in the $AIA_INSTANCE_HOME/config to add the following OC_02 database entry as a child node of /properties/participatingapplications.
  
  Ensure to replace the variables in the curly braces {}with the actual values.
```
<oc2> 
<version>4.6.0.52</version> 
<db> 
<jdbc-url>{Connect-url}</jdbc-url> 
<host>{host-name}</host> 
<port>{port}</port> 
<internal_id>{SID}</internal_id> 
<username>{username}</username> 
<password>{password}</password> 
</db> 
<server> 
<http> <host>{soa server host name}</host> 
<port>{soa server port}</port> 
</http> 
</server> 
</oc2>
```
2. Encrypt the AIAInstallProperties password by running the following commands either through XTerm or VNC viewer:
  - source $AIA_INSTANCE_HOME/bin/aiaenv.sh
  - cd $AIA_HOME/util
  - ant -f updateStore.xml addToStore -DAdminUsername=< weblogic Admin username> -DAdminPassword=<weblogic admin password>
  A popup window is displayed. Enter values for:
  - User name: rxc
  - Password: for rxc schema of OC_02 database
  - Path: Xpath of the OC_02 database password field in the AIAInstallProperties.xml. For example: /properties/participatingapplications/oc2/db/password
  This updates the encrypted password in the MDS as well as in the $AIA_INSTANCE_HOME/config file.
Create OC_02 database data source.

Use the Oracle WebLogic console to create the OC_02 DB database data source. As an example, a data source OracleClinicalCore2DS is created with jndi value as jdbc/OracleClinicalCore2DS pointing to the OC_02 database. You should deploy your new data source to the managed SOA server instance.
1. Log in to the WebLogic 11g server console as the WebLogic user.
2. Click Services under Domain Structure (in the left-side of the screen), and then Data Sources.
  
  The system displays all existing data sources. The New button becomes active.
3. Click New.
  
  The screen displays three options.
4. For non-RAC:
  
  i. Select the Generic Data Source option.
  
  ii. In the Create Screen, name the Datasource as OracleClinicalCore2DS.
  
  iii. In the JNDI Name column, enter jdbc/OracleClinicalCore2DS.
  
  Note:
  Do not change any other default values.
  
  iv. Click Next.
  
  v. Click Next.
  
  vi. Click Next and enter the database name, host name, and port of your Oracle Clinical 2 database instance. You must enter the user name as rxa_ws and password for this user.
  
  vii. Click Next.
  
  viii. Click Test Configuration. Ensure the connectivity is established.
  
  ix. Click Next.
  
  x. Select your SOA server as the target. For example, soa_server1.
  
  xi. Click Finish.
  
  The Data Source is created and deployed on the target server.
5. For RAC:
  
  i. Select the Multi Data Sources option.
  
  ii. In the Create Screen, name the Datasource as OracleClinicalCore2DS.
  
  iii. In the JNDI Name column, enter jdbc/OracleClinicalCore2DS.
  
  Note:
  Do not change any other default values.
  
  iv. Click Next.
  
  v. Select the SOA server and click Next.
  
  vi. Retain the default, Non-XA Driver, and click Next.
  
  vii. Click Create New Data Source.
  
  viii. In the Create Screen, name the Datasource as OracleClinicalCore2DS-rac0.
  
  where, rac0 is the first RAC node and the number should be incremented for each node.
  
  ix. In the JNDI Name column, enter jdbc/OracleClinicalCore2DS-rac0.
  
  where, rac0 is the first RAC node and the number should be incremented for each node.
  
  Note:
  Do not change any other default values.
  
  x. Click Next.
  
  xi. Enter Oracle as the database type. For the driver type, enter Oracle Driver (Thin) for RAC server-Instance connection Version 10, 11.
  
  xii. Deselect Supports Global Transactions and click Next.
  
  xiii. Click Next and enter the database name, host name, and port of your Oracle Clinical 2 database Instance. You must enter the user name as rxa_ws and password for this user.
  
  xiv. Click Next.
  
  xv. Test the configuration and click Next.
  
  xvi. Select your SOA server as the target. For example, soa_server1.
  
  xvii. Click Finish.
  
  The Data Source is created and deployed on the target server.
  
  xviii. Repeat steps e.vii to e.xvi for each node of the RAC instance.
6. Ensure the RAC data source are attached to the Multi Data Source created in step e.a.
7. Check the list of the Data Sources whether this data source is created successfully.
Customize the OracleClinical.ear.

Locate the original OracleClinical.ear that is shipped with Oracle Clinical. This .ear is installed as OracleClinical application in the Oracle WebLogic console.
1. Extract the OracleClinical.ear file to any location (for example: D:\OracleClinicalEar folder). It contains a META-INFfolder and an OracleClinical-web.war file.
2. Open the OracleClinical-web.war using WinZip or similar tool.
3. Navigate to OracleClinical-web.war\WEB-INF\classes\oracle\pharma\oc\model\services\common\.
4. Open bc4j.xcfg for editing and change the JDBC DataSource to the jndi location of the Data Source defined in the step 4. You need to make this change at four places in the file. A sample configuration change is shown below:
  
  Original:
  
  <Custom JDBCDataSource="jdbc/OracleClinicalCoreDS"/>
  
  Change to:
  
  <Custom JDBCDataSource="jdbc/OracleClinicalCore2DS"/>
5. Navigate to D:\OracleClinicalEar\META-INF folder. Modify the application.xml and change the context-root value to a new value that will be used for the second OC instance. Following is a sample file code:
```
<?xml version ='1.0' encoding ='windows-1252'?> <!DOCTYPE application PUBLIC "-//Sun Microsystems, Inc.//DTD J2EE Application 1.3//EN" "http://java.sun.com/dtd/application_1_3.dtd"> <application> <display-name>OracleClinicalSecond-WS</display-name> <module> <web> <web-uri>OracleClinical-web.war</web-uri> <context-root>OracleClinical-second-context-root</context-root> </web> </module> </application>
```
6. Re-package the contents of OracleClinical folder to an .ear file. You can either use WinZip or a similar tool or the following command from the OracleClinical directory.
  
  i. Execute the following command to zip the OracleClinical-web file:
  
  jar -cvf OracleClinical-web.war META-INF WEB-INF
  
  ii. Delete the OracleClinical-web directory.
  
  The contents of D:\OracleClinicalEar folder are:
  
  META-INF
  
  OracleClinical-web.war
  
  iii. Execute the following command to zip the D:\OracleClinicalEar folder:
  
  jar -cvfm OracleClinical.ear META-INF/MANIFEST.MF META-INF/application.xml OracleClinical-web.war.
  
  The OracleClinical.ear file is generated.
  
  The jar command requires the jre in the system path. If the command is not recognized, ensure to include the jre/bin folder in the system path. (<Oracle_Home>/Middleware/<jdk>/jre/bin).
  
  For example, use the following commands to set the system path:
  
  Unix: export PATH=<absolute path for jdk/jre/bin>:$PATH
  
  Windows: set PATH=< absolute path for jdk/jre/bin>;%PATH%
7. Deploy the newly created .ear file to the Oracle WebLogic server.
  
  i. Navigate to the Oracle WebLogic console and click Deployments in the left navigation bar.
  
  ii. Click Install.
  
  iii. In the Path field, provide the path where the new ear file is uploaded and click Next.
  
  iv. Select the Install this deployment as an application and click Next.
  
  v. Select the SOA server instance and click Next.
  
  vi. Change the deployment name so that it is unique in the deployment list on the WebLogic server console.
  
  vii. Test the deployment by accessing one of the following URLs:
  
  http://{server-name}:{port}/{context-root as defined in step 'c'}/StudySiteServiceSoapHttpPort
  
  http://{server-name}:{port}/{context-root as defined in step 'c'}/SiteServiceSoapHttpPort
  
  http://{server-name}:{port}/{context-root as defined in step 'c'}/InvestigatorServiceSoapHttpPort
  
  vii. Verify that WDSL link is accessible.
  
  where,
  
  server-name refers to the Fusion Middleware (FMW) server name
  
  port refers to the SOA port
  
  viii. Click Finish.

Modify the service configurations.

Navigate to ${AIA_HOME}/aia_instances/inst1/AIAMetaData/config and open AIAConfigurationProperties.xml for modification.
Modify the service configuration for OC Provider ABCS.

The service configurations contain the WSDL to locate the services of various OC instances. Each instance of OC configured in Systems Registry in AIA console has an entry in the service configuration section.
Look for Service Configuration section of UpdateClinicalStudyOCHealthSciencesProvABCSImpl.
There is a default System ID defined. This System ID is used in case there is no Target System defined in the EBM message header. This may happen when the routing rules specifically do not set any Target ID, or no routing rules match. Set this according to your requirements.

There are configurations for each of the OC Web services for investigator, site, and study site (locate and inspect properties such as Routing.StudySiteServiceSoapHttpPort.OC_01.EndpointURI).

You need to add similar properties for each of the OC instance.

See the highlighted sections in the following sample configuration for reference. For each of the OC instances, the WSDL to locate these services should be unique. Note that the system code that is set in the AIA System Console is used to differentiate the OC instance for which the service should be called. Also, the WSDL location should reflect the correct context root as defined for the corresponding OC instance in the step 5.e.

The following is a sample modified configuration:

<ServiceConfiguration serviceName="{http://xmlns.oracle.com/ABCSImpl/OracleClinical/Industry/HealthSciences/UpdateClinicalStudyOCHealthSciencesProvABCSImpl/V1}UpdateClinicalStudyOCHealthSciencesProvABCSImpl">
<Property name="Default.SystemID">OC_01</Property>
<Property name="ABCSExtension.PreXformEBMtoABMClinicalStudyEBM">false</Property>
<Property name="ABCSExtension.PostXformABMtoEBMClinicalStudyEBM">false</Property>  
<Property name="Routing.StudySiteServiceSoapHttpPort.RouteToCAVS">false</Property>
<Property name="Routing.StudySiteServiceSoapHttpPort.CAVS.EndpointURI"> http://{server-name}:{soa server port}/AIAValidationSystemServlet/syncresponsesimulator</Property>
<Property name="Routing.StudySiteServiceSoapHttpPort.OC_01.EndpointURI"> http://{server-name}:{soa server port}/OracleClinical-context-root/StudySiteServiceSoapHttpPort</Property>
<Property name="Routing.StudySiteServiceSoapHttpPort.OC_02.EndpointURI">http://{server-name}:{soa server port}/OracleClinical-second-context-root/StudySiteServiceSoapHttpPort</Property>
<Property name="Routing.StudySiteServiceSoapHttpPort.MessageProcessingInstruction.EnvironmentCode">PRODUCTION</Property>
<Property name="Routing.SiteServiceSoapHttpPort.RouteToCAVS">false</Property>
<Property name="Routing.SiteServiceSoapHttpPort.CAVS.EndpointURI"> http://{server-name}:{soa server port}/AIAValidationSystemServlet/syncresponsesimulator</Property>
<Property name="Routing.SiteServiceSoapHttpPort.OC_01.EndpointURI"> http://{server-name}:{soa server port}/OracleClinical-context-root/SiteServiceSoapHttpPort</Property>
<Property name="Routing.SiteServiceSoapHttpPort.OC_02.EndpointURI"> http://{server-name}:{soa server port}/OracleClinical-second-context-root/SiteServiceSoapHttpPort</Property>
<Property name="Routing.SiteServiceSoapHttpPort.MessageProcessingInstruction.EnvironmentCode">PRODUCTION</Property>
<Property name="Routing.InvestigatorServiceSoapHttpPort.RouteToCAVS">false</Property>
<Property name="Routing.InvestigatorServiceSoapHttpPort.CAVS.EndpointURI"> http://{server-name}:{soa-server-port}/AIAValidationSystemServlet/syncresponsesimulator</Property>
<Property name="Routing.InvestigatorServiceSoapHttpPort.OC_01.EndpointURI"> http://{server-name}:{soa server port}/OracleClinical-context-root/InvestigatorServiceSoapHttpPort</Property>    
<Property name="Routing.InvestigatorServiceSoapHttpPort.OC_02.EndpointURI"> http://{server-name}:{soa server port}/OracleClinical-second-context-root/InvestigatorServiceSoapHttpPort</Property>
<Property name="Routing.InvestigatorServiceSoapHttpPort.MessageProcessingInstruction.EnvironmentCode">PRODUCTION</Property>
<Property name="ABCSExtension.PreInvokeABSStudySiteABM">false</Property>
<Property name="ABCSExtension.PostInvokeABSStudySiteABM">false</Property>
</ServiceConfiguration>

Once the changes to AIAConfigurationProperties file are complete, upload the file to MDS. See Section 5.7.2.2.2.

The following is a sample UpdateMetaDataDP.xml:

<?xml version="1.0" standalone="yes"?><DeploymentPlan component="Metadata" version="3.0"> <Configurations> <UpdateMetadata wlserver="fp" > <fileset dir="${AIA_INSTANCE}/AIAMetaData"> <include name="config/AIAConfigurationProperties.xml"/> </fileset> </UpdateMetadata> </Configurations></DeploymentPlan>

Create the lookup table.

To route the message from Siebel to different Oracle Clinical databases, you need to define the correct routing rule. This example routes the message for a given study to a particular OC instance. This requires creating a lookup table. If you are using a different criteria, name the column accordingly.
1. Run the following script in the AIA schema of the AIA database:
  
  CREATE TABLE RouteLookup (Study VARCHAR2(50) NOT NULL UNIQUE, TargetID VARCHAR2(50) NOT NULL)
2. Insert Study and TargetID mappings into this table using SQL insert statements. The following is an example:
  
  INSERT INTO RouteLookup (Study, TargetID) VALUES ('TESTSTUDY01', 'OC_01');
  
  COMMIT;
3. Note the values in the insert statements. The first value is the study name as per the target OC system. The second value is the target instance name, which must be same as one of the system code values entered in the AIA Console System Registry screen in the step 2.

Create the XSL file. The following is a sample XSL file. This code maps the study in the input EBM message to the Study column in the Routelookup table and fetches the corresponding Target ID. In case the Study is not mapped, the Target ID is set to the default Target ID specified for OC Provider in the AIAServiceConfiguration file.

<?xml version="1.0" encoding="UTF-8" ?>
<?oracle-xsl-mapper
 <!-- SPECIFICATION OF MAP SOURCES AND TARGETS, DO NOT MODIFY. -->
 <mapSources>
 <source type="WSDL">
 <schema location="oramds:/apps/AIAMetaData/AIAComponents/EnterpriseBusinessServiceLibrary/Industry/HealthSciences/EBO/ClinicalStudy/V1/HealthSciencesClinicalStudyEBSV1.wsdl"/>
 <rootElement name="UpdateClinicalStudyEBM" namespace="http://xmlns.oracle.com/EnterpriseObjects/Core/EBO/ClinicalStudy/V1"/>
 </source>
 </mapSources>
 <mapTargets>
 <target type="WSDL">
 <schema location="oramds:/apps/AIAMetaData/AIAComponents/EnterpriseBusinessServiceLibrary/Industry/HealthSciences/EBO/ClinicalStudy/V1/HealthSciencesClinicalStudyEBSV1.wsdl"/>
 <rootElement name="UpdateClinicalStudy" namespace="http://xmlns.oracle.com/EnterpriseObjects/Core/EBO/ClinicalStudy/V1"/>
 </target>
 </mapTargets>
 <!-- GENERATED BY ORACLE XSL MAPPER 11.1.1.4.0(build 110106.1932.5682) AT [TUE MAY 31 17:40:32 IST 2011]. -->
?>
<xsl:stylesheet version="1.0"
 xmlns:svcdoc="http://xmlns.oracle.com/Services/Documentation/V1"
 xmlns:ns1="http://xmlns.oracle.com/EnterpriseServices/ClinicalStudy/V1"
 xmlns:aia="http://www.oracle.com/XSL/Transform/java/oracle.apps.aia.core.xpath.AIAFunctions"
 xmlns:bpws="http://schemas.xmlsoap.org/ws/2003/03/business-process/"
 xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"
 
xmlns:bpel="http://docs.oasis-open.org/wsbpel/2.0/process/executable"
 xmlns:ebs="http://xmlns.oracle.com/EnterpriseServices/Core/ClinicalStudy/V1"
 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:ebo="http://xmlns.oracle.com/EnterpriseObjects/Core/EBO/ClinicalStudy/V1"
 xmlns:coreclinicalstudyabcs="http://xmlns.oracle.com/ABCSImpl/OracleClinical/Industry/HealthSciences/UpdateClinicalStudyOCHealthSciencesProvABCSImpl/V1"
 xmlns:bpm="http://xmlns.oracle.com/bpmn20/extensions"
 xmlns:ns0="http://xmlns.oracle.com/EnterpriseObjects/Core/Custom/EBO/ClinicalStudy/V1"
 xmlns:plnk="http://schemas.xmlsoap.org/ws/2003/05/partner-link/"
 
xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
 xmlns:ns3="http://xmlns.oracle.com/EnterpriseObjects/Core/Custom/Common/V2"
 xmlns:ora="http://schemas.oracle.com/xpath/extension"
 xmlns:socket="http://www.oracle.com/XSL/Transform/java/oracle.tip.adapter.socket.ProtocolTranslator"
 xmlns:ns2="http://schemas.xmlsoap.org/ws/2003/03/addressing"
 xmlns:mhdr="http://www.oracle.com/XSL/Transform/java/oracle.tip.mediator.service.common.functions.MediatorExtnFunction"
 xmlns:oraext="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.ExtFunc"
 xmlns:dvm="http://www.oracle.com/XSL/Transform/java/oracle.tip.dvm.LookupValue"
 xmlns:hwf="http://xmlns.oracle.com/bpel/workflow/xpath"
xmlns:med="http://schemas.oracle.com/mediator/xpath"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:ids="http://xmlns.oracle.com/bpel/services/IdentityService/xpath"
 xmlns:ns5="urn:oasis:names:tc:xacml:2.0:policy:schema:cd:04"
 xmlns:xdk="http://schemas.oracle.com/bpel/extension/xpath/function/xdk"
 xmlns:xref="http://www.oracle.com/XSL/Transform/java/oracle.tip.xref.xpath.XRefXPathFunctions"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
 xmlns:bpelext="http://schemas.oracle.com/bpel/extension"
 xmlns:corecom="http://xmlns.oracle.com/EnterpriseObjects/Core/Common/V2"
 xmlns:ns6="http://xmlns.oracle.com/Services/Documentation/V1.0"
 xmlns:ns4="urn:oasis:names:tc:xacml:2.0:context:schema:cd:04"
 xmlns:ldap="http://schemas.oracle.com/xpath/extension/ldap"
exclude-result-prefixes="xsi xsl svcdoc ns1 ebs ebo ns0 soap ns3 ns2 ns5 xsd corecom ns4 coreclinicalstudyabcs plnk bpelext ns6 aia bpws xp20 bpel bpm ora socket mhdr oraext dvm hwf med ids xdk xref ldap">
 
<xsl:variable name="TargetSystemID"
 select="/ebo:UpdateClinicalStudyEBM/corecom:EBMHeader/corecom:Target/corecom:ID"/>
 <xsl:variable name="StudyID"
 select="/ebo:UpdateClinicalStudyEBM/ebo:DataArea/ebo:UpdateClinicalStudy/corecom:Identification/corecom:ID"/>
 <xsl:template match="@*|node()">
 <xsl:copy>
<xsl:copy-of select="@*"/>
 <xsl:apply-templates/>
 </xsl:copy>
 </xsl:template>
<xsl:template match="/ebo:UpdateClinicalStudyEBM/corecom:EBMHeader/corecom:Target/corecom:ID">
<xsl:choose>
<xsl:when test="oraext:lookup-table('ROUTELOOKUP','STUDY',$StudyID,'TARGETID','jdbc/AIADataSource')">
<corecom:ID><xsl:value-of select="oraext:lookup-table('ROUTELOOKUP','STUDY',$StudyID,'TARGETID','jdbc/AIADataSource')"/></corecom:ID>
</xsl:when>
<xsl:otherwise>
<corecom:ID><xsl:value-of select="aia:getServiceProperty('{http://xmlns.oracle.com/ABCSImpl/OracleClinical/Industry/HealthSciences/UpdateClinicalStudyOCHealthSciencesProvABCSImpl/V1}UpdateClinicalStudyOCHealthSciencesProvABCSImpl','Default.SystemID',true())"/>
</corecom:ID>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>

Create an XSL file Xform_UpdateClinicalStudyEBM_to_UpdateClinicalStudy.xsl with the above code in the ${AIA_HOME}/pips/StudySubjectVisitSyncSCandOC/EBS/ClinicalStudy/HealthSciencesClinicalStudyEBS/xsl directory.

You may select another file name, but ensure that the name matches the path given in the <transform> clause in the EBS routing as explained in the step 10.

If mapping is done on an attribute other than study ID, a corresponding Xpath should be used in the definition of variable StudyID in the XSL code.

If the column name was changed from Study to any other value in the route lookup table (Step 7), an appropriate column name should be used in second parameter passed to oraext:lookuptable function in the sample code.

Modify the routing rules in EBS.

Open ${AIA_HOME}/pips/StudySubjectVisitSyncSCandOC/EBS/ClinicalStudy/HealthSciencesClinicalStudyEBS/HealthSciencesClinicalStudyEBS.mplan in any text editor. You may need to change the file permission from read-only to writeable.

Locate <operation name="UpdateClinicalStudy">. There are various routings defined case wise. Change the code as indicated for Case_1 of this operation.

Important:

You must not copy and paste the whole code from the following code. You must add the section to the correct position.

Original Code:

<case name="Case_1_HealthSciencesClinicalStudyEBS.UpdateClinicalStudy" executionType="direct">
<condition language="xpath" xmlns:ebo="http://xmlns.oracle.com/EnterpriseObjects/Core/EBO/ClinicalStudy/V1" xmlns:corecom="http://xmlns.oracle.com/EnterpriseObjects/Core/Common/V2" xmlns:aia="http://www.oracle.com/XSL/Transform/java/oracle.apps.aia.core.xpath.AIAFunctions">
 <expression>($in.UpdateClinicalStudyEBM/ebo:UpdateClinicalStudyEBM/corecom:EBMHeader/corecom:MessageProcessingInstruction/corecom:EnvironmentCode='PRODUCTION' or not($in.UpdateClinicalStudyEBM/ebo:UpdateClinicalStudyEBM/corecom:EBMHeader/corecom:MessageProcessingInstruction/corecom:EnvironmentCode/text())) and ($in.UpdateClinicalStudyEBM/ebo:UpdateClinicalStudyEBM/corecom:EBMHeader/corecom:Target/corecom:ApplicationTypeCode='Oracle Clinical' or ((not($in.UpdateClinicalStudyEBM/ebo:UpdateClinicalStudyEBM/corecom:EBMHeader/corecom:Target/corecom:ID/text()) or ($in.UpdateClinicalStudyEBM/ebo:UpdateClinicalStudyEBM/corecom:EBMHeader/corecom:Target/corecom:ID/text()='')) and aia:getSystemType($in.UpdateClinicalStudyEBM/ebo:UpdateClinicalStudyEBM/corecom:EBMHeader/corecom:Sender/corecom:ID)!='Oracle Clinical'))</expression>
 </condition>
 <action>
 <assign>
 <copy target="$out.UpdateClinicalStudy" expression="$in.UpdateClinicalStudyEBM"/>
 </assign>
 <invoke reference="UpdateClinicalStudyOCHealthSciencesProvABCS" operation="UpdateClinicalStudy">
 </invoke>
 </action>
 </case>

New Code:

<case name="Case_1_HealthSciencesClinicalStudyEBS.UpdateClinicalStudy" executionType="direct">
 <condition language="xpath" xmlns:ebo="http://xmlns.oracle.com/EnterpriseObjects/Core/EBO/ClinicalStudy/V1" xmlns:corecom="http://xmlns.oracle.com/EnterpriseObjects/Core/Common/V2" xmlns:aia="http://www.oracle.com/XSL/Transform/java/oracle.apps.aia.core.xpath.AIAFunctions">
 <expression>((($in.UpdateClinicalStudyEBM/ebo:UpdateClinicalStudyEBM/corecom:EBMHeader/corecom:MessageProcessingInstruction/corecom:EnvironmentCode='PRODUCTION' or not($in.UpdateClinicalStudyEBM/ebo:UpdateClinicalStudyEBM/corecom:EBMHeader/corecom:MessageProcessingInstruction/corecom:EnvironmentCode/text())) and (((not($in.UpdateClinicalStudyEBM/ebo:UpdateClinicalStudyEBM/corecom:EBMHeader/corecom:Target/corecom:ID/text()) or ($in.UpdateClinicalStudyEBM/ebo:UpdateClinicalStudyEBM/corecom:EBMHeader/corecom:Target/corecom:ID/text()='')) and aia:getSystemType($in.UpdateClinicalStudyEBM/ebo:UpdateClinicalStudyEBM/corecom:EBMHeader/corecom:Sender/corecom:ID)!='Oracle Clinical'))))</expression>
 </condition>
 <action>
<transform>
<part name="$out.UpdateClinicalStudy" function="xslt(xsl/Xform_UpdateClinicalStudyEBM_to_UpdateClinicalStudy.xsl, $in.UpdateClinicalStudyEBM)"/>
</transform>
 <assign/>
 <invoke reference="UpdateClinicalStudyOCHealthSciencesProvABCS" operation="UpdateClinicalStudy">
</invoke>
 </action>
 </case>

Ensure the name selected for the XSL in the step 8.a matches with the transform clause in the new code.

You can deploy the changes to EBS to your environment using an EBS deployment plan. Create a new deployment plan StudySubjectVisitSyncSCandOCEBSDP.xml at <AIA_HOME>/pips/StudySubjectVisitSyncSCandOC/DeploymentPlans and enter content similar to the following example:
```
<DeploymentPlan component="StudySubjectVisitSyncSCandOC" version="3.0"><PreInstallScript/><Configurations><EndpointConfigurator target-server="pips.StudySubjectVisitSyncSCandOC" dir="${AIA_HOME}"> </EndpointConfigurator></Configurations><Deployments><Composite compositeName="HealthSciencesClinicalStudyEBS" compositedir="${AIA_HOME}/pips/StudySubjectVisitSyncSCandOC/EBS/ClinicalStudy/HealthSciencesClinicalStudyEBS" revision="1.0" wlserver="pips.StudySubjectVisitSyncSCandOC" action="deploy" overwrite="true" /></Deployments><PostInstallScript></PostInstallScript></DeploymentPlan>
```
1. Deploy the modified EBS composite using the ant script.
2. Navigate to $AIA_INSTANCE_HOME}/bin/ and execute the following command:
  
  For Linux: source aiaenv.sh
  
  For Windows: aiaenv.bat
3. Execute the following deployment command:
  
  The following is a sample of the ant script.
  
  For Linux: ant -f <AIA_HOME>/Infrastructure/Install/AID/AIAInstallDriver.xml -DDeploymentPlan=<AIA_HOME>/pips/StudySubjectVisitSyncSCandOC/DeploymentPlans/StudySubjectVisitSyncSCandOCEBSDP.xml -DPropertiesFile=<AIA_HOME>/aia_instances/<aia_instance>/config/AIAInstallProperties.xml -l <AIA_HOME>/pips/StudySubjectVisitSyncSCandOC/DeploymentPlans/StudySubjectVisitSyncSCandOCEBS.log
  
  For Windows: ant -f <AIA_HOME>\Infrastructure\Install\AID\AIAInstallDriver.xml -DDeploymentPlan=<AIA_HOME>\pips\StudySubjectVisitSyncSCandOC\DeploymentPlans\StudySubjectVisitSyncSCandOCEBSDP.xml -DPropertiesFile=<AIA_HOME>\aia_instances\<aia_instance>\config\AIAInstallProperties.xml -l <AIA_HOME>\pips\StudySubjectVisitSyncSCandOC\DeploymentPlans\StudySubjectVisitSyncSCandOCEBS.log
4. Restart the SOA Server.
5. Verify the EBS routing by testing the Site (SC to OC) flow for both OC instances.

Configure additional AQConsumer.

For the Subject Flow (OC to SC) to work, there should be an AQConsumer instance per OC instance listening to the message queue. To configure an additional instance of AQ Consumer, create a copy of the folder <AIA_HOME>/services/core/OracleClinical/AdapterServices/UpdateClinicalStudyOCAQConsumer in the same location such as, UpdateClinicalStudyOC2AQConsumer. Make changes to the following files as indicated:

Update composite.xml:

Table 6-2 Updating composite.xml

Old Code	New Code
`composite name="UpdateClinicalStudyOCAQConsumer"`	`composite name="UpdateClinicalStudyOC2AQConsumer"`
`service name="UpdateClinicalStudyOCAQConsumer"`	`service name="UpdateClinicalStudyOC2AQConsumer"`
`<svcdoc:ConnectionFactory>eis/AQ/UpdateClinicalStudyOCDS</svcdoc:ConnectionFactory>`	`<svcdoc:ConnectionFactory>eis/AQ/UpdateClinicalStudyOC2</svcdoc:ConnectionFactory>`
`<source.uri>UpdateClinicalStudyOCAQConsumer</source.uri>`	`<source.uri>UpdateClinicalStudyOC2AQConsumer</source.uri>`

Update ClinicalStudyOCAQConsumer_aq.jca:

Table 6-3 Updating ClinicalStudyOCAQConsumer_aq.jca

Old Code	New Code
`adapter-config name="UpdateClinicalStudyOCAQConsumer"`	`adapter-config name="UpdateClinicalStudyOC2AQConsumer"`
`connection-factory location="eis/AQ/UpdateClinicalStudyOCDS"`	`connection-factory location="eis/AQ/UpdateClinicalStudyOC2"`
`UIConnectionName="OCDB_sun6x13"`	`UIConnectionName="OCDB_<OC2DB_Name>"`

In UIConnectionName, replace <OC2DB_Name> with the actual value of OC2DB SID. Ensure that the connection-factory value matches with UIConnectionName.

Update ClinicalStudyOCAQConsumer.wsdl:

Table 6-4 Updating ClinicalStudyOCAQConsumer.wsdl:

Old Code New Code

wsdl:definitions name="UpdateClinicalStudyOCAQConsumer"

wsdl:definitions name="UpdateClinicalStudyOC2AQConsumer"

Old Code	New Code
`wsdl:definitions name="UpdateClinicalStudyOCAQConsumer"`	`wsdl:definitions name="UpdateClinicalStudyOC2AQConsumer"`

Deploy the new AQConsumer. Create a new deployment plan StudySubjectVisitSyncSCandOC2AQDP.xml at <AIA_HOME>/pips/StudySubjectVisitSyncSCandOC/DeploymentPlans and enter content similar to the following example:

<DeploymentPlan component="StudySubjectVisitSyncSCandOC" version="3.0"> <PreInstallScript/> <Configurations> <Datasource name="UpdateClinicalStudyOC2DS" jndiLocation="jdbc/UpdateClinicalStudyOC2" action="create" database="participatingapplications.oc2.db" xa-enabled="true" wlserver="pips.StudySubjectVisitSyncSCandOC"/><ManagedServer wlserver="pips.StudySubjectVisitSyncSCandOC" action="shutdown" failonerror="true"/> <AqAdapter connection-instance-jndi="eis/AQ/UpdateClinicalStudyOC2" datasource-jndi="jdbc/UpdateClinicalStudyOC2" xa-enabled="true" action="create" wlserver="pips.StudySubjectVisitSyncSCandOC" /><ManagedServer wlserver="pips.StudySubjectVisitSyncSCandOC" action="start" failonerror="true"/> </Configurations><Deployments><Composite compositeName="UpdateClinicalStudyOC2AQConsumer" compositedir="${AIA_HOME}/services/core/OracleClinical/AdapterServices/UpdateClinicalStudyOC2AQConsumer" revision="1.0" wlserver="pips.StudySubjectVisitSyncSCandOC" action="deploy" /></Deployments><PostInstallScript></PostInstallScript></DeploymentPlan>

Ensure that the AqAdapter connection-instance-jndi is exactly the same as the connection factory jndi mentioned in composite.xml and UpdateClinicalStudyOCAQConsumer_aq.jca.
Ensure that the datasource jndi mentioned in AQAdapter tag matches with the one in DataSource tag and is unique across the system.
Ensure that the DataSource name is unique across the WebLogic data sources.
Ensure that the value of the attribute compositedir is the location of the new folder that you have created and modified the files in that.
Ensure to update the compositeName to keep it unique per OC AQConsumer instance deployed.

Source aiaenv.sh.

Source {$AIA_INSTANCE_HOME}/bin/aiaenv.sh

Run the deployment plan using the following ant script:

ant -f <AIA_HOME>/Infrastructure/Install/AID/AIAInstallDriver.xml -DDeploymentPlan=<AIA_HOME>/pips/StudySubjectVisitSyncSCandOC/DeploymentPlans/StudySubjectVisitSyncSCandOC2AQDP.xml -DPropertiesFile=<AIA_HOME>/aia_instances/<aia_instance>/config/AIAInstallProperties.xml -l <AIA_HOME>/pips/StudySubjectVisitSyncSCandOC/DeploymentPlans/StudySubjectVisitSyncSCandOC2.log

EXECUTE DBMS_AQADM.START_QUEUE(queue_name => 'CLINICAL_STUDY_QUEUE',dequeue => true,enqueue => true);
commit;

Restart the SOA server.
Verify the AQ Consumer deployment by testing the Subject (OC to SC) flow from the new OC instance.