API to view the email sent by workflow notification in Oracle apps R12 (WF_MAIL.GETMESSAGE)

API to view the email sent by workflow notification in Oracle apps R12 (WF_MAIL.GETMESSAGE) 

SET SERVEROUPUT ON;

DECLARE ln_nid NUMBER; lv_node VARCHAR2(100); lv_agent VARCHAR2(100); lv_replyto VARCHAR2(100); lv_subject VARCHAR2(2000); lv_text_body VARCHAR2(32000); lv_html_body VARCHAR2(32000); lv_body_atth VARCHAR2(32000); lv_error_result VARCHAR2(100); z NUMBER; ln_body_length NUMBER; -- Notification id for which we need the email message content ln_notification_id NUMBER DEFAULT 123073; PROCEDURE display_big_value( piv_message VARCHAR2) IS BEGIN z := 1; ln_body_length := 0; ln_body_length := LENGTH(piv_message); IF ln_body_length > 0 THEN LOOP IF ln_body_length < z + 255 THEN dbms_output.put_line(SUBSTR(piv_message, z, ln_body_length - z + 1)); ELSE dbms_output.put_line(SUBSTR(piv_message, z, 255)); END IF; EXIT WHEN z > ln_body_length; z := z + 255; END LOOP; END IF; END; BEGIN -- Temporarily set notification status as MAIL UPDATE wf_notifications SET status = 'OPEN', mail_status = 'MAIL' WHERE notification_id = ln_notification_id; dbms_output.enable(100000); wf_mail.getmessage(ln_notification_id, 'node', NULL, 'replyto@node.com', lv_subject, lv_text_body, lv_html_body, lv_body_atth, lv_error_result); dbms_output.put_line('**************** SUBJECT IS ******************'); display_big_value(lv_subject); dbms_output.put_line('**************** TEXT BODY IS *****************'); display_big_value(lv_text_body); dbms_output.put_line('**************** HTML BODY IS *******************'); display_big_value(lv_html_body); dbms_output.put_line('**************** HTML ATTH IS ********************'); display_big_value(lv_body_atth); ROLLBACK; -- This is to rollback the temporary assignment. END;