Get the list of BPEL Instance and Corelation Identifier value using Corelation identifier name

Problem: For asynchronous BPEL process where 2 way communication happening, couple of time due to various issue the response which was expected to come at BPEL for an order it won’t come and BPEL process is keep waiting and won’t get resumed until unless will get the response.

To elaborate this problem more in details I want to give one example here e.g. there is one business scenario in prepay/post-paid connection provisioning, Customer has to pass the Credit Check before order provisioning.

Flow is like that BPEL will invoke CRM system (SAP/Siebel/PSFT) and will get immediate response Customer account Id and will set as Customer account id value as correlation id and then will wait for further notification from CRM system for Credit Check Completion. In the mean time CRM system will further interact with Decision Point system which exactly does Credit check then Decision point will send the response to CRM system and CRM system will send the response to BEPL but due to multiple issue CRM to BPEL notification won’t reach till BPEL and order in BPEL is keep waiting.

Solution:

To fix all these orders which are waiting in BPEL for external system response, external system again need to send the notification back to BPEL, for that external system might ask the list of correlation id values for all the orders which is waiting in BPEL, so that external system can send the notification again.

In BPEL any instance which is waiting for response a subscription will be written out in dlv_subscription table along with correlation identifier and its value. Below query will pull out correlation id value based on correlation identifier name for all the waiting instance in BEPL.

select CBI.CIKEY as "BPEL_INSTANCE_ID",

       substr(Properties, instr(Properties,'CustId',1,2)+7,9) as "Customer_ACCOUNT_ID",

       CBI.Modify_date as "LAST_MODIFY_DATE"

from   ORABPEL.CUBE_INSTANCE CBI,

       ORABPEL.DLV_SUBSCRIPTION DS

where  DS.STATE =0 and

       CBI.process_id = 'cm_cpm_submitcreditCheck' and CBI.Revision_tag='1.1.0' and

       DS.CIKEY=CBI.CIKEY

Properties: This is column name in dlv_subscription table which store the correlation identifier and its value but the format is very messy and user has to use the string format function to exactly pull out the correlation identifier values.

Also in above query correlation identifier name need to change based upon project design.

Process_Id: BPEL processName and Revision tag also need to change based up project design.

Note: In the above query many thing are project perspective which need be modified for other project.  The objective of this post is to give the idea how to pull the CIKEY details  where BPEL process is waiting for the response from external system.