Saturday, May 1, 2021

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








        In this post, we have given a script which can be used to view the email notification contents like subject, message body, attachment information for a given workflow notification. This helps in troubleshooting workflow notification related mail delivery issues.



Tested Instance: R12.2.4


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, '', 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;

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

Post a Comment


The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.