Sometimes you want to identify all the faults for the messages that were sitting in BPEL Engine Level recovery as undelivered Invokes.
We can run this SQL on the SOA Infra Schema to obtain this info.
FROM WI_FAULT WIF
WHERE wif.cikey IN
FROM Dlv_Message dlv, cube_instance ci
WHERE dlv.State = 0
AND dlv.Dlv_Type = 1
AND dlv.ecid= ci.ecid
AND dlv.conv_id = ci.conversation_id
AND dlv.Receive_Date >TRUNC(Sysdate));
Once you have investigated them you can then either recover these instances from the em console application or can utilise the SOA API to call the
BPELServiceEngine.recoverInvokeMessages() operation by passing the message GUID’s of the instances returned by the above query.
I will share the details in another post soon.
For details about the various states Please see my post http://nitinaggarwal.wordpress.com/2013/06/12/soa-11g-soa-infra-db-states-for-soa-composites-and-components/
References: Oracle A team