3 Replies Latest reply: Oct 25, 2013 1:12 PM by vishm8 RSS

    Oracle Workflow Notification PLSQL Document Error - Oracle EBS 12.1.1

    vishm8

      Hi,

       

      We are currently on working on the following versions,

       

      Oracle EBS - 12.1.1

      Oracle Workflow for EBS - v2.6.3.5

      Oracle Database -

      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit

      PL/SQL Release 11.1.0.7.0

      CORE 11.1.0.7.0

      TNS for Solaris: Version 11.1.0.7.0

      NLSRTL Version 11.1.0.7.0

       

      We are using PLSQL package to build custom HTML notification body for one of our workflow notifications. The document type being returned is a CLOB and it errors out with following error message,

       

      ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 ORA-06512: at "APPS.WF_NOTIFICATION", line 5725 ORA-06512: at line 5


      The following is the plsql code generating the CLOB document and returning it to the Workflow notification document,

       

      PROCEDURE setRequestMsg (document_id     IN     varchar2,
                               display_type    IN     varchar2,
                               document        IN OUT CLOB,
                               document_type   IN OUT varchar2)
      IS
         MESSAGE_TEXT        CLOB;
         NL                  VARCHAR2 (1) := fnd_global.newline;
         vGrandTotal         NUMBER := 0;
         vReqId              NUMBER;
         vLastCMT            VARCHAR2 (1000);
         preCount            NUMBER;
         ----
         L_ITEM_TYPE         WF_ITEMS.ITEM_TYPE%TYPE;
         L_ITEM_KEY          WF_ITEMS.ITEM_KEY%TYPE;
         L_DOCUMENT          VARCHAR2 (32000);
         L_URL               VARCHAR2 (1000);
         L_GFM_AGENT         VARCHAR2 (255);
         -----
      
      
         table_width         varchar2 (6) := '"100%"';
         table_border        varchar2 (3) := '"0"';
         table_cellpadding   varchar2 (3) := '"3"';
         table_cellspacing   varchar2 (3) := '"1"';
         table_bgcolor       varchar2 (7) := '"white"';
         th_bgcolor          varchar2 (9) := '"#cccc99"';
         th_fontcolor        varchar2 (9) := '"#336699"';
         th_fontface varchar2 (80)
               := '"Arial, Helvetica, Geneva, sans-serif"' ;
         td_bgcolor          varchar2 (9) := '"#f7f7e7"';
         td_fontcolor        varchar2 (7) := '"black"';
         td_fontface varchar2 (80)
               := '"Arial, Helvetica, Geneva, sans-serif"' ;
         l_test_url          VARCHAR2 (2000);
         l_document_max      NUMBER := 25000;
         MESSAGE             VARCHAR2 (32767) := NULL;
      
      
         CURSOR c_line (
            reqId   IN            NUMBER
         )
         IS
              SELECT   r.order_number order_num,
                       r.order_type order_type,
                       r.sbu_Code sbu_code,
                       r.account_number acct_num,
                       r.rule_description rule_desc,
                       l.line_number line_num,
                       l.part_number part_number,
                       iic.product_model prod_model,
                       iic.product_type prod_type,
                       l.quantity quantity,
                       l.item_cost item_cost,
                       l.quantity * item_cost total_cost
                FROM   ontf_so_request_lines l,
                       ontf_so_requests r,
                       inf_item_categories_mv iic
               WHERE       l.request_id = r.request_id
                       AND iic.organization_id = 2
                       AND iic.inventory_item_id = l.inventory_item_id
                       AND r.request_id = reqId
            ORDER BY   line_num;
      
      
         CURSOR c_pre (
            reqId   IN            NUMBER
         )
         IS
              SELECT   r.order_number order_num,
                       r.order_type order_type,
                       r.sbu_code sbu_code,
                       r.account_number acct_num,
                       r.rule_description rule_desc,
                       l.line_number line_num,
                       l.part_number part_number,
                       iic.product_model prod_model,
                       iic.product_type prod_type,
                       l.quantity quantity,
                       l.item_cost item_cost,
                       l.quantity * item_cost total_cost
                FROM   ontf_so_request_lines l,
                       ontf_so_requests r,
                       ontf_so_requests m,
                       inf_item_categories_mv iic
               WHERE       l.request_id = r.request_id
                       AND r.request_id != reqId
                       AND r.order_number = m.order_number
                       AND NVL (r.sbu_code, '?') = NVL (m.sbu_code, '?')
                       AND NVL (r.account_number, '?') = NVL (m.account_number, '?')
                       AND iic.organization_id = 2
                       AND iic.inventory_item_id = l.inventory_item_id
                       AND m.request_id = reqId
                       AND r.request_status != 'REJECTED'
            ORDER BY   line_num;
      
      
         CURSOR c_req (reqId IN NUMBER)
         IS
            SELECT   order_number order_num,
                     requested_amount requested_amount,
                     fu.description requested_by,
                     requested_date requested_date,
                     order_type order_type,
                     sbu_code sbu_code,
                     account_number acct_num,
                     rule_description rule_desc,
                     request_status
              FROM   ontf_so_requests rr, fnd_user fu
             WHERE   rr.requested_by = fu.user_id AND rr.request_id = reqId;
      
      
         CURSOR c_log (
            reqId   IN            NUMBER
         )
         IS
              SELECT   rl.approval_sequence approval_sequence,
                       fu.description approved_by,
                       fuo.description original_approved_by,
                       rl.approval_status approved_code,
                       rl.approval_date approved_date,
                       rl.comments approved_comments
                FROM   ONTF_SO_REQUEST_LOGS rl, fnd_user fu, fnd_user fuo
               WHERE       UPPER (rl.approver) = fu.user_name
                       AND UPPER (rl.original_approver) = fuo.user_name(+)
                       AND rl.request_id = reqId
            ORDER BY   rl.approval_sequence, rl.request_id;
      BEGIN
         document_type := display_type;
      
      
         L_ITEM_TYPE :=
            NVL (SUBSTR (document_id, 1, INSTR (document_id, ':') - 1), 'WFERROR');
         L_ITEM_KEY := SUBSTR (document_id, INSTR (document_id, ':') + 1);
      
      
         DBMS_LOB.createtemporary (MESSAGE_TEXT, FALSE);
      
      
         IF L_ITEM_TYPE = 'OEOH'
         THEN
            BEGIN
               SELECT   request_id
                 INTO   vReqId
                 FROM   ONTF_SO_REQUESTS
                WHERE   ORDER_ID = TO_NUMBER (L_ITEM_KEY);
            EXCEPTION
               WHEN OTHERS
               THEN
                  vReqId := NULL;
            END;
         ELSE
            BEGIN
                 SELECT   request_id
                   INTO   vReqId
                   FROM   ONTF_SO_REQUEST_LINES
                  WHERE   ORDER_LINE_ID = TO_NUMBER (L_ITEM_KEY)
               GROUP BY   request_id;
            EXCEPTION
               WHEN OTHERS
               THEN
                  vReqId := NULL;
            END;
         END IF;
      
      
         vLastCMT :=
            Wf_engine.GetItemAttrText (L_ITEM_TYPE,
                                       L_ITEM_KEY,
                                       'ONTF_SO_LAST_COMMENTS',
                                       TRUE);
      
      
         IF (display_type = 'text/html')
         THEN
            MESSAGE :=
                  MESSAGE
               || '<table bgcolor='
               || table_bgcolor
               || ' width='
               || table_width
               || ' border='
               || table_border
               || ' cellpadding='
               || table_cellpadding
               || ' cellspacing='
               || table_cellspacing
               || '>';
      
      
            MESSAGE := MESSAGE || '<tr bgcolor=' || th_bgcolor || '>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Sequence'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Approver'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Orig. Approver'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Status'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Date'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Comments'
               || '</b></td>';
      
      
            MESSAGE := MESSAGE || '</B></TR>';
      
      
            DBMS_LOB.WRITE (MESSAGE_TEXT,
                            LENGTH (MESSAGE),
                            1,
                            MESSAGE);
      
      
            MESSAGE := NULL;
      
      
            FOR r_log IN c_log (vReqId)
            LOOP
               MESSAGE := MESSAGE || '<TR><FONT SIZE="-2">';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_log.approval_sequence || '</TD>';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_log.approved_by || '</TD>';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_log.original_approved_by || '</TD>';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_log.approved_code || '</TD>';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_log.approved_date || '</TD>';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_log.approved_comments || '</TD>';
      
      
               MESSAGE := MESSAGE || '</FONT></TR>';
      
      
      
      
               DBMS_LOB.writeappend (MESSAGE_TEXT, LENGTH (MESSAGE), MESSAGE);
      
      
               MESSAGE := NULL;
            END LOOP;
      
      
            MESSAGE := MESSAGE || '</TABLE>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<table bgcolor='
               || table_bgcolor
               || ' width='
               || table_width
               || ' border='
               || table_border
               || ' cellpadding='
               || table_cellpadding
               || ' cellspacing='
               || table_cellspacing
               || '>';
      
      
            MESSAGE := MESSAGE || '<tr bgcolor=' || th_bgcolor || '>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Order #'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Line #'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Part Number'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Model'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Product Type'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Order Type'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'SBU Code'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Description'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Quantity'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Cost'
               || '</b></td>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Total'
               || '</b></td>';
      
      
            MESSAGE := MESSAGE || '</B></TR>';
      
      
            vGrandTotal := 0;
      
      
            DBMS_LOB.writeappend (MESSAGE_TEXT, LENGTH (MESSAGE), MESSAGE);
      
      
            MESSAGE := NULL;
      
      
            FOR r_line IN c_line (vReqId)
            LOOP
               MESSAGE := MESSAGE || '<TR><FONT SIZE="-2">';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_line.order_num || '</TD>';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_line.line_num || '</TD>';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_line.part_number || '</TD>';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_line.prod_model || '</TD>';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_line.prod_type || '</TD>';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_line.order_type || '</TD>';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_line.sbu_code || '</TD>';
      
      
               MESSAGE := MESSAGE || '<TD>' || r_line.rule_desc || '</TD>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<TD align="right">'
                  || TO_CHAR (r_line.Quantity)
                  || '</TD>';
      
      
               MESSAGE :=
                  MESSAGE || '<TD align="right">'
                  || TO_CHAR (r_line.item_cost,
                              FND_CURRENCY.Get_Format_Mask ('USD', 22))
                  || '</TD>';
      
      
               MESSAGE :=
                  MESSAGE || '<TD align="right">'
                  || TO_CHAR (r_line.Total_cost,
                              FND_CURRENCY.Get_Format_Mask ('USD', 22))
                  || '</TD>';
      
      
               MESSAGE := MESSAGE || '</FONT></TR>';
      
      
      
      
               vGrandTotal := vGrandTotal + r_line.Total_cost;
      
      
               DBMS_LOB.writeappend (MESSAGE_TEXT, LENGTH (MESSAGE), MESSAGE);
      
      
               MESSAGE := NULL;
            END LOOP;
      
      
            MESSAGE := MESSAGE || '<tr bgcolor=' || th_bgcolor || '>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<TD/><TD/><TD/><TD/><TD/><TD/><TD/><TD/><TD/>'
               || '<td align="left"><font color='
               || th_fontcolor
               || ' face='
               || th_fontface
               || '><b>'
               || 'Total Amount'
               || '</b></TD>';
      
      
            MESSAGE :=
                  MESSAGE
               || '<TD align="right">'
               || TO_CHAR (vGrandTotal, FND_CURRENCY.Get_Format_Mask ('USD', 22))
               || '</TD><TD/>';
      
      
            MESSAGE := MESSAGE || '</TR>';
      
      
            MESSAGE := MESSAGE || '</TABLE>';
      
      
            DBMS_LOB.writeappend (MESSAGE_TEXT, LENGTH (MESSAGE), MESSAGE);
      
      
            MESSAGE := NULL;
      
      
            preCount := 0;
      
      
            BEGIN
               SELECT   COUNT (1)
                 INTO   preCount
                 FROM   ontf_so_request_lines l,
                        ontf_so_requests r,
                        ontf_so_requests m
                WHERE       l.request_id = r.request_id
                        AND r.request_id != vReqId
                        AND r.order_number = m.order_number
                        AND NVL (r.sbu_code, '?') = NVL (m.sbu_code, '?')
                        AND NVL (r.account_number, '?') =
                              NVL (m.account_number, '?')
                        AND r.request_status != 'REJECTED'
                        AND m.request_id = vReqId;
            EXCEPTION
               WHEN OTHERS
               THEN
                  preCount := 0;
            END;
      
      
            IF preCount > 0
            THEN
               MESSAGE := MESSAGE || '<tr bgcolor=' || th_bgcolor || '>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<TD align="center">'
                  || '<b>Other Submitted Order Lines</b>'
                  || '</TD></TR>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<table bgcolor='
                  || table_bgcolor
                  || ' width='
                  || table_width
                  || ' border='
                  || table_border
                  || ' cellpadding='
                  || table_cellpadding
                  || ' cellspacing='
                  || table_cellspacing
                  || '>';
      
      
               MESSAGE := MESSAGE || '<tr bgcolor=' || th_bgcolor || '>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<td align="left"><font color='
                  || th_fontcolor
                  || ' face='
                  || th_fontface
                  || '><b>'
                  || 'Order #'
                  || '</b></td>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<td align="left"><font color='
                  || th_fontcolor
                  || ' face='
                  || th_fontface
                  || '><b>'
                  || 'Line #'
                  || '</b></td>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<td align="left"><font color='
                  || th_fontcolor
                  || ' face='
                  || th_fontface
                  || '><b>'
                  || 'Part Number'
                  || '</b></td>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<td align="left"><font color='
                  || th_fontcolor
                  || ' face='
                  || th_fontface
                  || '><b>'
                  || 'Model'
                  || '</b></td>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<td align="left"><font color='
                  || th_fontcolor
                  || ' face='
                  || th_fontface
                  || '><b>'
                  || 'Product Type'
                  || '</b></td>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<td align="left"><font color='
                  || th_fontcolor
                  || ' face='
                  || th_fontface
                  || '><b>'
                  || 'Order Type'
                  || '</b></td>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<td align="left"><font color='
                  || th_fontcolor
                  || ' face='
                  || th_fontface
                  || '><b>'
                  || 'SBU Code'
                  || '</b></td>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<td align="left"><font color='
                  || th_fontcolor
                  || ' face='
                  || th_fontface
                  || '><b>'
                  || 'Description'
                  || '</b></td>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<td align="left"><font color='
                  || th_fontcolor
                  || ' face='
                  || th_fontface
                  || '><b>'
                  || 'Quantity'
                  || '</b></td>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<td align="left"><font color='
                  || th_fontcolor
                  || ' face='
                  || th_fontface
                  || '><b>'
                  || 'Cost'
                  || '</b></td>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<td align="left"><font color='
                  || th_fontcolor
                  || ' face='
                  || th_fontface
                  || '><b>'
                  || 'Total'
                  || '</b></td>';
      
      
               MESSAGE := MESSAGE || '</B></TR>';
      
      
               DBMS_LOB.writeappend (MESSAGE_TEXT, LENGTH (MESSAGE), MESSAGE);
      
      
               MESSAGE := NULL;
      
      
               vGrandTotal := 0;
      
      
               FOR r_pre IN c_pre (vReqId)
               LOOP
                  MESSAGE := MESSAGE || '<TR><FONT SIZE="-2">';
      
      
                  MESSAGE := MESSAGE || '<TD>' || r_pre.order_num || '</TD>';
      
      
                  MESSAGE := MESSAGE || '<TD>' || r_pre.line_num || '</TD>';
      
      
                  MESSAGE := MESSAGE || '<TD>' || r_pre.part_number || '</TD>';
      
      
                  MESSAGE := MESSAGE || '<TD>' || r_pre.prod_model || '</TD>';
      
      
                  MESSAGE := MESSAGE || '<TD>' || r_pre.prod_type || '</TD>';
      
      
                  MESSAGE := MESSAGE || '<TD>' || r_pre.order_type || '</TD>';
      
      
                  MESSAGE := MESSAGE || '<TD>' || r_pre.sbu_code || '</TD>';
      
      
                  MESSAGE := MESSAGE || '<TD>' || r_pre.rule_desc || '</TD>';
      
      
                  MESSAGE :=
                        MESSAGE
                     || '<TD align="right">'
                     || TO_CHAR (r_pre.Quantity)
                     || '</TD>';
      
      
                  MESSAGE :=
                     MESSAGE || '<TD align="right">'
                     || TO_CHAR (r_pre.item_cost,
                                 FND_CURRENCY.Get_Format_Mask ('USD', 22))
                     || '</TD>';
      
      
                  MESSAGE :=
                     MESSAGE || '<TD align="right">'
                     || TO_CHAR (r_pre.Total_cost,
                                 FND_CURRENCY.Get_Format_Mask ('USD', 22))
                     || '</TD>';
      
      
                  MESSAGE := MESSAGE || '</FONT></TR>';
      
      
                  vGrandTotal := vGrandTotal + r_pre.Total_cost;
      
      
                  DBMS_LOB.writeappend (MESSAGE_TEXT, LENGTH (MESSAGE), MESSAGE);
      
      
                  MESSAGE := NULL;
               END LOOP;
      
      
               MESSAGE := MESSAGE || '<tr bgcolor=' || th_bgcolor || '>';
      
      
               MESSAGE :=
                     MESSAGE
                  || '<TD/><TD/><TD/><TD/><TD/><TD/><TD/><TD/><TD/>'
                  || '<td align="left"><font color='
                  || th_fontcolor
                  || ' face='
                  || th_fontface
                  || '><b>'
                  || 'Total Amount'
                  || '</b></TD>';
      
      
               MESSAGE :=
                  MESSAGE || '<TD align="right">'
                  || TO_CHAR (vGrandTotal,
                              FND_CURRENCY.Get_Format_Mask ('USD', 22))
                  || '</TD><TD/>';
      
      
               MESSAGE := MESSAGE || '</TR>';
      
      
               MESSAGE := MESSAGE || '</TABLE>';
      
      
               DBMS_LOB.writeappend (MESSAGE_TEXT, LENGTH (MESSAGE), MESSAGE);
      
      
               MESSAGE := NULL;
            END IF;
         ELSE
            FOR r_req IN c_req (vReqId)
            LOOP
               MESSAGE :=
                     MESSAGE
                  || 'Last Approver''s Comments:'
                  || CHR (10)
                  || vLastCMT
                  || CHR (10)
                  || CHR (10);
      
      
               MESSAGE :=
                     MESSAGE
                  || '    Order Number: '
                  || r_req.order_num
                  || CHR (10)
                  || '     ORder Type: '
                  || r_req.order_type
                  || CHR (10)
                  || '   SBU Code: '
                  || r_req.sbu_code
                  || CHR (10)
                  || '   Description: '
                  || r_req.rule_desc
                  || CHR (10)
                  || '        Amount: '
                  || TO_CHAR (r_req.requested_amount,
                              FND_CURRENCY.Get_Format_Mask ('USD', 22))
                  || CHR (10)
                  || '  Requested By: '
                  || r_req.requested_by
                  || CHR (10)
                  || 'Requested Date: '
                  || r_req.requested_date
                  || CHR (10)
                  || CHR (10);
      
      
               IF r_req.request_status = 'REJECTED'
               THEN
                  MESSAGE :=
                     MESSAGE
                     || 'Please cancel all the rejected lines.  They will not be resubmitted.'
                     || CHR (10)
                     || CHR (10);
               END IF;
            END LOOP;
      
      
            DBMS_LOB.write (MESSAGE_TEXT,
                            LENGTH (MESSAGE),
                            1,
                            MESSAGE);
      
      
            MESSAGE := NULL;
         END IF;
      
      
         -- document := message_text;
         preCount := DBMS_LOB.getLength (MESSAGE_TEXT);
      
      
         DECLARE
            l_index   NUMBER := 1;
            l_size    NUMBER := 32000;
            l_rsize   NUMBER := 0;
            l_var     VARCHAR2 (32001) := NULL;
         BEGIN
            l_rsize := preCount;
      
      
            WHILE l_index <= preCount
            LOOP
               IF l_rsize < l_size AND l_rsize > 0
               THEN
                  l_var := DBMS_LOB.SUBSTR (MESSAGE_TEXT, l_rsize, l_index);
               ELSE
                  l_var := DBMS_LOB.SUBSTR (MESSAGE_TEXT, l_size, l_index);
               END IF;
      
      
               IF l_var IS NOT NULL AND l_rsize > 0
               THEN
                  wf_notification.writeToClob (document, l_var);
      
      
                  l_index := l_index + l_size;
                  l_rsize := preCount - l_index;
               END IF;
            END LOOP;
         END;
      END setRequestMsg;
      

       

      We've bounced Workflow service components after change to workflow package, still have the same issue.

      Please let me know what i'm doing wrong in my plsql code.

      I've debugged my temporary CLOB creation and entire HTML message body was being created.

      The exception is encountered when copying my temporary CLOB to Oracle Workflow "document" CLOB.

       

      Regards,

      Vishal

        • 1. Re: Oracle Workflow Notification PLSQL Document Error - Oracle EBS 12.1.1
          Hussein Sawwan-Oracle

          Do you have the patch (Patch 6234198: Generic script to open old notifications with out Invalid LOB locator error after applying the patch 5671680) applied? -- Error Message Invalid LOB Locator Specified ORA-22275 ORA-06512 In Approval Notification (Doc ID 743148.1)

           

          Thanks,

          Hussein

          • 2. Re: Oracle Workflow Notification PLSQL Document Error - Oracle EBS 12.1.1
            vishm8

            No we do not have patch 5671680 applied.

            When i ran SQL's from Doc ID 743148.1, no rows were selected or fixed.

             

            Any other suggestions to resolve this issue ?

             

            Regards,

            Vishal

            • 3. Re: Oracle Workflow Notification PLSQL Document Error - Oracle EBS 12.1.1
              vishm8

              This is the error message displayed,

               

              [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-06502: PL/SQL: numeric or value error
              
              
              wf_notification.GetAttrDoc2(844137, ONTF_SO_MESSAGE, text/plain)
              Wf_Notification.GetAttrDoc(844137, ONTF_SO_MESSAGE, text/plain)
              Wf_Notification.GetText(844137, text/plain)
              Wf_Notification.GetBody(844137, text/plain)
              WF_NOTIFICATION.GetFullBody(nid => 844137, disptype => text/plain)
              WF_MAIL.GetLOBMessage3(nid => 844137, r_ntf_pref => MAIL' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK=
              WF_MAIL.GetLOBMessage3(844137, WFMAIL, 2020: Error when getting notification content. Caused by: ORA-06502: PL/SQL: numeric or value error
              
              
              wf_notification.GetAttrDoc2(844137, ONTF_SO_MESSAGE, text/plain)
              Wf_Notification.GetAttrDoc(844137, ONTF_SO_MESSAGE, text/plain)
              Wf_Notification.GetText(844137, text/plain)
              Wf_Notification.GetBody(844137, text/plain)
              WF_NOTIFICATION.GetFullBody(nid => 844137, disptype => text/plain)
              WF_MAIL.GetLOBMessage3(nid => 844137, r_ntf_pref => MAILTEXT), Step -> Getting text/plain body)
              WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 844137)
              WF_XML.Generate(oracle.apps.wf.notification.send, 844137)
              WF_XML.Generate(oracle.apps.wf.notification.send, 844137)
              Wf_Event.setMessage(oracle.apps.wf.notification.send, 844137, WF_XML.Generate)
              Wf_Event.dispatch_internal()
              

               

              Let me know what i'm doing incorrectly in my code.

               

              Regards,

              Vishal