Implement Intermediate Service for Custom Correlation

Technology and Tools
Primary Technology used for Development: Oracle Fusion Middleware – BPEL
Tools used for Development: Jdeveloper 12C or 11g and SQL developer
Tools used for Testing: Weblogic Enterprise Manager

Technical Architecture
Given a scenario where we have a service awaiting an asynchronous response from the third party and the correlation is happening on a variable declared as . We create an Intermediate Service with the name by creating a DB Adapter as a reference in composite.xml. While setting up the DB Adapter, we need to insert the below SQL with an appropriate variable name.
NOTE: One can expose multiple operations in the same intermediate service, and each operation can have its correlation id variables.

select count(*) from dlv_subscription
where composite_name = #variable1
and operation_name = #variable2
and state = #variable3
and cikey in (select cikey from cube_instance
where composite_name = #variable4 and state = #variable5)
and properties like concat(‘%correlationId%=’,concat(#variable6,’}’))

The variables mentioned in the above SQL are operations, composite names, and state of the instance.
• variable1 and variable4 are the composite names used in an asynchronous service
• variable2 is the operation name used in an asynchronous service
• variable3 is the state of BPEL mid-process instances. Here we set it to zero(‘0’)
• variable5 is the state of BPEL cube instance. Here we set it to one(‘1’)
• variable6 is the actual correlation value coming from the third party
• correlationId is the name of the property in an asynchronous service on which correlation set is applied

Post DB Adapter creation in composite.xml, we now need to write if and else condition based on the select query count within the .bpel file.
• If the count is greater than or equal to ‘1’ then proceed ahead with the correlation and send the response to service
• If the count is zero, then terminate the Intermediate service instance

NOTE: The detailed explanation for different states of dlv_subscription and cube_instance table is:

States of cube_instance tables
WHEN STATE=1 THEN ‘OPEN AND RUNNING’
WHEN STATE=2 THEN ‘OPEN AND SUSPENDED’
WHEN STATE=3 THEN ‘OPEN AND FAULTED’
WHEN STATE=4 THEN ‘CLOSED AND PENDING’
WHEN STATE=5 THEN ‘CLOSED AND COMPLETED’
WHEN STATE=6 THEN ‘CLOSED AND FAUTED’
WHEN STATE=7 THEN ‘CLOSED AND CANCELLED’
WHEN STATE=8 THEN ‘CLOSED AND ABORTED’
WHEN STATE=9 THEN ‘CLOSED AND STALE’
WHEN STATE=10 THEN ‘NON-RECOVERABLE’

States of cube_instance tables
WHEN STATE=0 THEN ‘State Unresolved’
WHEN STATE=1 THEN ‘State Resolved’
WHEN STATE=2 THEN ‘State Handled’
WHEN STATE=3 THEN ‘State Cancelled’