1 Reply Latest reply on Mar 28, 2015 5:41 PM by 801619

    Workflow html Notification Error

    801619

      Hi Experts,

       

      I'm working on 12.2.4 EBS. I have developed custom workflow to use across variable custom forms. I'm calling custom workflow for approval 2 custom oracle forms and 1 custom OAF page.I have created 3 processes,notifications,messages for 3 forms and calling workflow with each respective process in 3 forms, which is working fine. Till now i have plain notification messages for all forms, but i have to change to html notification message for all forms. Followed below process to achieve this (Steps followed to get dynamic HTML message:).

       

      It's working absolutely fine for 2 oracle forms, but for OAF page, it's giving below error.

       

      Error Message: WF_ERROR] ERROR_MESSAGE=3835: Error '-20002 - ORA-20002: 2018: Unable to generate the notification XML. Caused by: 2020: Error when getting notification content. Caused by: ORA-06550: line 1, column 7: PLS-00201: identifier 'SET_PKG.SET_TEXT' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored wf_notification.GetAttrDoc2(652626, AD_TEXT, text/html) Wf_Notification.GetAttrDoc(652626, AD_TEXT, text/html) Wf_Notification.GetText2(652626, text/html) Wf_Notification.GetBody(652626, text/html) WF_NOTIFICATION.GetF' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK= WF_MAIL.GetLOBMessage3(652626, WFMAIL, 2020: Error when getting notification content. Caused by: ORA-06550: line 1, column 7: PLS-00201: identifier 'SET_PKG.SET_TEXT' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored wf_notification.GetAttrDoc2(652626, AD_TEXT, text/html) Wf_Notification.GetAttrDoc(652626, AD_TEXT, text/html) Wf_Notification.GetText2(652626, text/html) Wf_Notification.GetBody(652626, text/html) WF_NOTIFICATION.GetFullBody(nid => 652626, disptype => text/html) WF_MAIL.GetLOBMessage3(nid => 652626, r_ntf_pref => MAILHTML), Step -> Getting text/html body) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 652626) WF_XML.Generate(oracle.apps.wf.notification.send, 652626) WF_XML.Generate(oracle.apps.wf.notification.send, 652626) Wf_Event.setMessage(oracle.apps.wf.notification.send, 652626, WF_XML.Generate) Wf_Event.dispatch_internal()

       

       

      Could somebody help me why it's giving error in one case, i followed same steps for 3 messages, why it's working fine for 2 oracle forms, not working for OAF page.

       

      Steps followed to get dynamic HTML message:

       

      Note: AD_TEXT -- attribute in workflow with type 'Document'

       

      1. created procedure to set HTML message to workflow attribute.

       

      PROCEDURE SET_DTLS(itemtype  IN VARCHAR2

                                            ,itemkey   IN VARCHAR2

                                           ,actid     IN NUMBER

                                           ,funcmode  IN VARCHAR2

                                          ,resultout OUT NOCOPY VARCHAR2

                                           )

      IS

       

      BEGIN

       

        wf_engine.SetItemAttrText(itemtype,

                                                itemkey,

                                                'AD_TEXT',

                                               'PLSQLCLOB:SET_PKG.SET_TEXT/'||itemtype||'|'||itemkey

                                                );

      EXCEPTION

      WHEN OTHERS THEN

      WF_CORE.CONTEXT ('SET_PKG', 'SET_DTLS',itemtype,itemkey,'Error when setting notification attributes:'||SQLERRM);

          RAISE;

      END SET_DTLS;

       

      2. created procedure to develop html notification message dynamically.

       

      PROCEDURE SET_TEXT (p_document_id     in     varchar2,

                              p_display_type    in     varchar2,

                              p_document        in out nocopy clob,

                              p_document_type   in out varchar2)

      IS

        v_itemtype varchar2(200);

        v_itemkey varchar2(200);

        NL VARCHAR2(1) := fnd_global.newline;

       

      BEGIN

          v_itemtype :=  substr(p_document_id,instr(p_document_id,'/')+1,(instr(p_document_id,'|')-instr(p_document_id,'/'))-1);

          v_itemkey := substr(p_document_id,instr(p_document_id,'|')+1);

       

      p_document_type := 'text/html';

        

         p_document := NL || '<P><B>';

          p_document := p_document || '<TABLE border=1 cellpadding=2 cellspacing=1>';

          p_document := p_document || '<TR>' || NL;

          p_document := p_document || '<TH>' || 'Attribute Group' || '</TH>';

          p_document := p_document || '<TH>' || 'Attribute' || '</TH>';

          p_document := p_document || '<TH>' || 'Changes' || '</TH>';

          p_document := p_document || '</TR>' || NL;

        p_document := p_document || '</TABLE>';

         

        p_document_type := 'text/html';

       

      EXCEPTION

      WHEN OTHERS THEN

           WF_CORE.CONTEXT ('SET_PKG','SET_TEXT',v_itemtype,v_itemkey,'Unhandled Exception When setting notification attribute values:'||sqlerrm);

           RAISE;

      END SET_TEXT;