How to get the timestamp for ESB router and corresponding BPEL process.

You can check the log files to get the details about the process invoked.

For BPEL you can set the domain.log to debug mode for finer details and for ESB you can set the log.xml in debug mode for finer details.

I already have posted how to set loggers for BPEL and ESB.

you can also analyse SOA_HOME\opmn\logs to get the timestamp of different process.

I am not good in database so i can not help much in writing a query from database side .

As far as i believe as per my post

TAbles used in ESB for instance tracking

depending upon the activity type

10 – A routing rule has been executed successfully

You can create your own query something like

select timestamp from ESB_ACTIVITY where type=10

this is how you can get the timestamp for the process.

Now we will cosider how to convert this timestamp in to date and time.

If you have a look in ESB_ACTIVITY again it is

desc ESB_ACTIVITY
Name Null? Type
----------------------------------------- -------- ---------------------------
ID NOT NULL NUMBER
FLOW_ID NOT NULL VARCHAR2(256)
SUB_FLOW_ID VARCHAR2(48)
SEQ NUMBER
SUB_FLOW_SEQ NUMBER(3)
BATCH_ID VARCHAR2(48)
SOURCE VARCHAR2(48)
OPERATION_GUID VARCHAR2(48)
TIMESTAMP NOT NULL NUMBER
TYPE NOT NULL NUMBER(2)
RR_OUTPUT_STATUS NUMBER(2)
ADDI_INFO VARCHAR2(500)
IS_STALE VARCHAR2(1)

The timestamp here is coded as unix timestamp.

There are so many tools or links in internet for converting the unix timestamp to date and time.

you can use http://www.onlineconversion.com/unix_time.htm for getting the time.

Just provide the value that you get in timestamp in the Unix timestamp field and just say submit to get the time and date.