Relate BPEL process instance parent to child relationship

Why I want to relate these?

Before I start I consider this scenario that prompted me to write this stuff.
Well I have a sequence of BPEL processes that get called.
A
--> B
----> C
-------> D
-------> E
----> F

I want a way to relate all the process together in the BPEL console or bespoke application built on top of the BPEL Database.

How to do this?

There are multiple ways to relate these BPEL process.

Title approach

Well the one I prefer is to set the title of the BPEL process instances as some logical value that makes sense to business. What that would mean is all the BPEL processes from "A" to "F" will all be name as "XYZ".
But this is good for the bespoke applications.
Well some times before the actual title of the process is set the BPEL process might go into manual recovery. How do you solve?

Database approach

Well go into the BPEL database and look.
Now how do I relate all the processes in the database.
Well first you need to go the table "ORABPEL"."CUBE_INSTANCE"

There are 3 specific columns that you should know about.
"TITLE" - sets the title of the BPEL process in this column as seen in the BPEL console

"ROOT_ID" - CIKEY (instance id as in BPEL console) of the root parent basically instance ID of A as in BPEL console

"PARENT_ID" - Immediate parents ID i.e the caller process

Sample Scenario

A is the parent
A calls B
B calls C and F
C calls D and E
-------------------------------------------------------
Process Name - Instance ID - Root ID - Parent ID --> comment
-------------------------------------------------------
A - 1 - 1 - --> parent Id blank as no parent for it but root Id is 1
B - 2 - 1 - 1 --> both parent and root ID as same as A
C - 3 - 1 - 2 --> root is still 1, but parent now is B
D - 4 - 1 - 3 --> root is still 1, but parent now is C
E - 5 - 1 - 3 --> root is still 1, but parent now is C
F - 6 - 1 - 2 --> root is still 1, but parent now is B

Sample Query

So sample queries to find all the children in a parent BPEL tree would be to use the root ID
================ Query START ==================================================
select * from cube_instance where root_id = {ROOT aprent instance number}
================ Query END ====================================================

A query to find all root BPEL process i.e BPEL process that are the initiators

================ Query START =================
select * from cube_instance where parent_id is NULL
================ Query END ===================

Why not use Tree Finder?

Itz a good question to ask why not use tree finder.

In the projects that I worked, access to the BPEL console is restricted and access to the BPEL is only through a bespoke J2EE application. But that application does not allow viewing of BPEL instances are that is for functinal people to see work flow tasks related to business errors.

The alternative that was implemented was a read only user to the BPEL database from where these queries are helpful to relate BPEL instances. Especially when performing manual recovery of stales instances and to identify the tree chain that has stalled in the flow.

Hope this answers question raised as a commnet here.