Scribd
Upload
463 views1 page

Email Approval Response Query in Oracle

This SQL query selects data from the wf_notification_in and wf_notifications tables to find the email approval response. It extracts the RESULT value from the xmltype column to get the response, and returns the notification data including time enqueued/dequeued, state, notification ID, role, and xml payload.

Uploaded by

srains123
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
463 views1 page

Email Approval Response Query in Oracle

This SQL query selects data from the wf_notification_in and wf_notifications tables to find the email approval response. It extracts the RESULT value from the xmltype column to get the response, and returns the notification data including time enqueued/dequeued, state, notification ID, role, and xml payload.

Uploaded by

srains123
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

--Query to Find email approval response.

SELECT (select extractvalue((SELECT xmltype(wno.xml)from


dual),'/NOTIFICATIONGROUP/NOTIFICATION/RESPONSE/doc_attribute[@name="RESULT"]')from
dual)xml_type,wno.*
FROM (SELECT o.deq_time deq_time
,o.enq_time enq_time
,decode(o.state
,0
,'PENDING'
,2
,'EMAIL SENT'
,3
,'EXCEPTION'
,o.state) state
,to_number((SELECT str_value
FROM TABLE(o.user_data.header.properties)
WHERE NAME = 'NOTIFICATION_ID')) p_notification_id
,(SELECT str_value
FROM TABLE(o.user_data.header.properties)
WHERE NAME = 'ROLE') p_Role
,o.user_data ,
o. user_data.text_vc xml
FROM applsys.wf_notification_in o) wno
,applsys.wf_notifications wn
WHERE wn.notification_id = wno.p_notification_id;

You might also like