While building integrations with Oracle EBusiness Suite, quite a few times we use the Event based patterns where we consume events based on Oracle R12 Workflow.

These events are stored in WF_BPEL_Q which in turn uses the WF_BPEL_QTAB table.

But the event XML is stored as Clob making it difficult to read in the set of data which forms the USER_DATA column which is consumed by SOA as part of the event.

To read the exact XML payload we can use the below PLSQL Query which can be executed on the Oracle R12 instance

SELECT wd.user_data.event_name,
       wd.user_data.event_key,
       n.NAME parameter_name,
       n.VALUE parameter_value,
  FROM apps.wf_deferred wd, TABLE(wd.user_data.parameter_list) n
 WHERE lower(wd.user_data.event_name) LIKE 'oracle.apps.per.api.organization.create_organization'
AND   
 wd.user_data.send_date > SYSDATE - .1
 AND wd.user_data.event_name LIKE '%'
 ORDER BY wd.user_data.send_date DESC,
          wd.user_data.event_name,
          wd.user_data.event_key,
          n.NAME