While building integrations with Oracle EBusiness Suite, quite a few times we use the Event based patterns where we consume events based on Oracle R12 Workflow.

These events are stored in WF_BPEL_Q which in turn uses the WF_BPEL_QTAB table.

But the event XML is stored as Clob making it difficult to read in the set of data which forms the USER_DATA column which is consumed by SOA as part of the event.

To read the exact XML payload we can use the below PLSQL Query which can be executed on the Oracle R12 instance

SELECT wd.user_data.event_name,
       wd.user_data.event_key,
       n.NAME parameter_name,
       n.VALUE parameter_value,
  FROM apps.wf_deferred wd, TABLE(wd.user_data.parameter_list) n
 WHERE lower(wd.user_data.event_name) LIKE 'oracle.apps.per.api.organization.create_organization'
AND   
 wd.user_data.send_date > SYSDATE - .1
 AND wd.user_data.event_name LIKE '%'
 ORDER BY wd.user_data.send_date DESC,
          wd.user_data.event_name,
          wd.user_data.event_key,
          n.NAME

When SOA Server was restarted we faced the below issues. The version is 11.1.1.6
Not all the composites were loading correctly and we could see this error message when we go to soa-infra on the EM console.

Resolution:
We raised an SR with Oracle and following were the steps taken to resolve this

1. Stop the managed servers.

2. Take a backup of setDomainEnv.sh and add the following JVM Parameter to JAVA_OPTIONS

 -Djavax.xml.parsers.SAXParserFactory=oracle.xml.jaxp.JXSAXParserFactory 

3. Delete contents in the /tmp and /cache directories 

4. Start the managed server again.

Setting up Oracle Policy Automation

This post is based on Oracle Policy Automation (OPA) release 10.4.4.

The server and client software can be downloaded from http://www.oracle.com/technetwork/apps-tech/policy-automation/downloads/index.html?ssSourceSiteId=null

• Oracle Policy Modeling (282MB) : Client tool used to develop OPA rules and screens.
• Oracle Policy Automation runtime components for Java (76MB): Runtime components which are required to be deployed on the Weblogic server. These are web-determinations.war and determinations server and document-generation-server.

To deploy rules on the Weblogic server
1. Explode the determinations-server.war. This can be renaming the .war file to .zip file.
2. Copy rules in the .zip format from \\Development\output to \user_projects\domains\base_domain\servers\AdminServer\upload\determinations-server\WEB-INF\classes\rulebases. (This is the folder from where you will pick the jar for deployment on weblogic server.)
3. Zip the META-INF and WEB-INF folders into determinations-server.zip file. Note there is no determinations-server folder required to embed the 2 folders. If this is done then the determination-server gives and authorization error when tested during runtime.
4. Rename the determinations-server.zip to determinations-server.war.
5. Deploy the war file as an application on the weblogic server console – Deployments.

Create Sensor and Sensor Actions in Jdeveloper 11.1.1.x

One small change between Jdeveloper 11.1.1.x and 10.1.3.x is different views for updating a bpel file vs setting up sensors for the same.

When we open the bpel file, there is a dropdown at the right side. Select Monitor to create or update sensor and sensor actions.


Sensor and Sensor Actions can be created in the Structure Pane.
Create a Variable Sensor “AuditMessage_Sensor”.


Then create a sensor action “PublishToAuditTopic” to publish the sensor data to the JMS topic “jms/db/AuditTopic”. This JMS topic has been previously configured on the weblogic server.



The Sensor action is configured in the following manner to publish the data to the JMS topic jms/db/AuditTopic.

Assign a bucketset to the output variable of a Business Rule Decision Function

To assign a bucket set to the input variable of a business rule decision function, we can do the same while creating the rule itself. But to assign a bucketset to the output variable, we need to update the fact of the output variable.
1. Open the rules designer
2. Click on Facts.
3. Select the output variable for which we need to assign a bucketset.
4. Click on Edit.
5. In the list of Bucketset, select the appropiate bucketset and click OK.

Now in the rule, in the Actions part, when we assert or modify the output variable message, we should get the bucketset as a dropdown.

How to replace urls and any other text in multiple files

Usually we encounter scenarios where we need to change urls or something else to ensure the composites work in other enviroments as well.
One easy way I managed to do this was by using notepad++. http://notepad-plus-plus.org/

It gives the capability of doing a "Find in files" and then we can replace anything requried at a any folder level.

JDeveloper hangs while using Expression Builder in Assign

Using the Expression Builder to build XPath expressions may cause Oracle JDeveloper to hang. If that happens, perform the following steps:
1. Kill the Oracle JDeveloper process.
2. Restart Oracle JDeveloper.
3. Select Tools > Preferences > SOA, and deselect the Validate Expression checkbox.
After performing these steps, Oracle JDeveloper should no longer hang.