Oracle Apps Knowledge Sharing

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.

API

WF_MAIL.GETMESSAGE

Tested Instance: R12.2.4

Script: 

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;