Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\wf\sql\wfmxmlb.pls

REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
REM dbdrv: checkfile:~PROD:~PATH:~FILE

/*=======================================================================*
 |        Copyright (c) 1996, 2004, Oracle. All rights reserved.         |
 +=======================================================================+
 | FILENAME
 |   wfmxmlb.sql
 | DESCRIPTION
 |   PL/SQL body for package: WF_XML
 | MODIFICATION LOG:
 |   02?/2002 JWSMITH BUG 2001012 - Increased l_schemaName, l_replyto,
 |            l_recipient_role, l_wf_schema to varchar2(320)
 |   07/2004 VSHANMUG BUG 3735752 - Copy of parseContentType taken and 
 |           placed into WF_MAIL_UTIL.
 *=======================================================================*/

SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;

set scan off;

CREATE OR REPLACE package body WF_XML as
/* $Header: wfmxmlb.pls 26.90 2005/06/09 13:04:30 vshanmug ship $ */
   --
   -- Exceptions
   --
   dequeue_timeout exception;
   pragma EXCEPTION_INIT(dequeue_timeout, -25228);

dequeue_disabled exception;
   pragma EXCEPTION_INIT(dequeue_disabled, -25226);

dequeue_outofseq exception;
   pragma EXCEPTION_INIT(dequeue_outofseq, -25237);

no_queue exception;
   pragma EXCEPTION_INIT(no_queue, -24010);

-- g_fist_message
   -- Flag to control the dequeuing of the SMTP queue
   g_first_message boolean := TRUE;

TYPE wf_response_rec_t IS RECORD
   (
    NAME     VARCHAR2(30),
    TYPE     VARCHAR2(8),
    FORMAT   VARCHAR2(240),
    VALUE    VARCHAR2(32000)
   );

TYPE wf_responseList_t IS TABLE OF
     wf_response_rec_t
   INDEX BY BINARY_INTEGER;

TYPE parserStack_t IS TABLE OF
        varchar2(2000) INDEX BY BINARY_INTEGER;

g_LOBTable wf_temp_lob.wf_temp_lob_table_type;

cursor g_urls(p_nid varchar2) is
      select WMA.TYPE, WMA.DISPLAY_NAME, WNA.TEXT_VALUE, WNA.NAME 
      from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, 
           WF_MESSAGE_ATTRIBUTES_VL WMA 
      where WNA.NOTIFICATION_ID = p_nid
      and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID 
      and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE 
      and WN.MESSAGE_NAME = WMA.MESSAGE_NAME 
      and WMA.TYPE = 'URL'
      and WMA.ATTACH = 'N' 
      and WMA.NAME = WNA.NAME;

-- Set these constants as session level varaibles to minumise the
   -- calls to wf_core.
   g_newLine varchar2(1) := wf_core.newLine;
   g_install varchar2(100) := wf_core.Translate('WF_INSTALL');
   g_htmlmessage varchar2(200);
   g_urlNotification varchar2(200);
   g_urlListAttachment varchar2(200);
   -- These are only used in legacy procedures.
   g_webAgent varchar2(200) := wf_core.translate('WF_WEB_AGENT');
   g_wfSchema varchar2(200) := wf_core.translate('WF_SCHEMA');
   g_fndapi_misschr varchar2(1) := FND_API.G_MISS_CHAR;

g_ntfDocText varchar2(30) := wf_notification.doc_text;
   g_ntfDocHtml varchar2(30) := wf_notification.doc_html;

procedure getResponseDetails(message in CLOB, node out NOCOPY varchar2, 
                             version out NOCOPY integer,
                             fromRole out NOCOPY varchar2, 
                             responses in out NOCOPY wf_responseList_t);

-- Return TRUE if the URL points to a image file.
   -- The URL is pretested to ensure that it does NOT contain 
   -- any URL parameters. 
   function isImageReference(url in varchar2) return boolean
   is
      extPos pls_integer;
      extStr varchar2(1000);
      params pls_integer;
   begin
      params := instr(url, '?');
      if (params = 0) then
         extPos := instrb(url, '.', -1, 1) + 1;
         extStr := lower(substrb(url, extPos));
         if extStr in ('gif','jpg','png','tif','bmp','jpeg') then
            return true;
         else
            return false;
         end if;
      else
         return false;
      end if;
   end isImageReference;

-- GetRecipients
   -- IN
   --    Role
   -- OUT
   --    List of recipients with their details contained in a PL/SQL Table.
   -- NOTE
   --    A role will only be resolved to one level. If a role contains has a
   --    role associated to it, then that's as far as we go.
   procedure GetRecipients(p_role in varchar2,
                           p_recipient_list in out NOCOPY WF_DIRECTORY.wf_local_roles_tbl_type)
   is

l_display_name wf_roles.display_name%TYPE;
     l_description wf_roles.description%TYPE;
     l_email_address wf_roles.email_address%TYPE;
     l_notification_preference wf_roles.notification_preference%TYPE;
     l_language wf_roles.language%TYPE;
     l_territory wf_roles.territory%TYPE;

cursor urc1(role varchar2, colon binary_integer) is
     select name
     from wf_users
     where (name, orig_system, orig_system_id) in
        (select user_name, user_orig_system, user_orig_system_id
           from wf_user_roles
              where role_name = urc1.role
                and role_orig_system = substr(urc1.role, 1, urc1.colon-1)
                and role_orig_system_id = substr(urc1.role, urc1.colon+1)
                and user_name <> role_name
                and user_orig_system <> role_orig_system
                and user_orig_system_id <> role_orig_system_id)
        and notification_preference not in ('SUMMARY','QUERY')
         order by notification_preference, language;

cursor urc2(role varchar2) is
     select name
     from wf_users
     where (name, orig_system, orig_system_id) in
        (select user_name, user_orig_system, user_orig_system_id
           from wf_user_roles
              where role_name = urc2.role
              and user_name <> role_name)
        and notification_preference not in ('SUMMARY','QUERY')
       order by notification_preference, language;

i binary_integer := 0;
     colon binary_integer;

begin

-- Get role details.
      wf_directory.GetRoleInfo(p_role, l_display_name, l_email_address, 
                               l_notification_preference, l_language, 
                               l_territory);

-- If the email address is NULL, then look for the members attached to 
      -- the role. Only attach those roles that 'want' a notification.
      if l_email_address is not NULL 
         and l_notification_preference not in ('SUMMARY','QUERY') then
         i := p_recipient_list.COUNT + 1;
         p_recipient_list(i).name            := p_role;
         p_recipient_list(i).display_name    := l_display_name;
         p_recipient_list(i).description     := l_description;
         p_recipient_list(i).notification_preference := 
                        l_notification_preference;
         p_recipient_list(i).language        := l_language;
         p_recipient_list(i).territory       := l_territory;
         p_recipient_list(i).email_address   := l_email_address;
      else
         -- The the ROLE has a blank email address, then we
         -- are interested only in resolving it one level.
         -- If this is a user with a blank email address, then no one
         -- will get a notification.
         colon := instr(p_role, ':');
         if colon > 0 then
            for urrec in urc1(p_role, colon) loop
               wf_directory.GetRoleInfo(urrec.name, l_display_name, 
                                        l_email_address,
                                        l_notification_preference, l_language,
                                        l_territory);
               i := p_recipient_list.COUNT + 1;
               p_recipient_list(i).name            := p_role;
               p_recipient_list(i).display_name    := l_display_name;
               p_recipient_list(i).description     := l_description;
               p_recipient_list(i).notification_preference := 
                        l_notification_preference;
               p_recipient_list(i).language        := l_language;
               p_recipient_list(i).territory       := l_territory;
               p_recipient_list(i).email_address   := l_email_address;
            end loop;
         else
            for urrec in urc2(p_role) loop
               wf_directory.GetRoleInfo(urrec.name, l_display_name,
                                        l_email_address,
                                        l_notification_preference, l_language,
                                        l_territory);
               i := p_recipient_list.COUNT + 1;
               p_recipient_list(i).name            := p_role;
               p_recipient_list(i).display_name    := l_display_name;
               p_recipient_list(i).description     := l_description;
               p_recipient_list(i).notification_preference := 
                        l_notification_preference;
               p_recipient_list(i).language        := l_language;
               p_recipient_list(i).territory       := l_territory;
               p_recipient_list(i).email_address   := l_email_address;
            end loop;
         end if;
      end if;
   exception
      when others then
         wf_core.context('Wf_XML','GetRecipients',p_role);
         raise;
   end GetRecipients;

-- EncodeEntityReference
   -- IN
   --    Data to be encoded
   -- RETURN
   --    Encoded data.
   -- NOTE
   --    This is needed to encode the HTML data before placing it into
   --    the XML structure. If it is placed in neat, then the XML parser
   --    will not be able to cope.
   function EncodeEntityReference(p_str in varchar2) return varchar2 
   is
      l_str varchar2(32000);
   begin
      l_str := p_str;
      l_str := replace(l_str,'&','&'||'amp;');
      l_str := replace(l_str,'<','&'||'lt;');
      l_str := replace(l_str,'>','&'||'gt;');
      l_str := replace(l_str,'"','&'||'quot;');
      l_str := replace(l_str,'''','&'||'apos;');
      return l_str;
   end EncodeEntityReference;

-- DecodeEntityReference (PRIVATE)
   -- IN
   --    Data to be decoded
   -- RETURN
   --    Decoded data.
   -- NOTE
   --    This is needed to decode the HTML data after extracting it from
   --    the XML structure. 
   function DecodeEntityReference(some_text in varchar2)
   return varchar2 is
     l_amp     varchar2(1) := '&';
     buf       varchar2(32000); 
   begin
     buf := some_text;
     buf := replace(buf, l_amp||'#38;', l_amp);
     buf := replace(buf, l_amp||'lt;', '<');
     buf := replace(buf, l_amp||'#60;', '<');
     buf := replace(buf, l_amp||'gt;', '>');
     buf := replace(buf, l_amp||'#92;', '\');
     buf := replace(buf, l_amp||'#39;', '''');
     buf := replace(buf, l_amp||'apos;', '''');
     buf := replace(buf, l_amp||'quot;', '"');
     buf := replace(buf, l_amp||'amp;', l_amp);
     return buf; 
   exception
     when others then
       wf_core.context('Wf_Notification', 'DecodeEntityReference');
       raise;
   end DecodeEntityReference;

-- EnqueueLOBMessage
   -- IN
   --    Queue Name
   --    Priority of the message
   --    Correlation for the message - the NID of the notification 
   --       for this implementation.
   --    Message - XML encoded.
   procedure EnqueueLOBMessage(p_queue in varchar2, 
                            p_priority number,
                            p_correlation in varchar2,
                            p_message in CLOB) is

l_enqueue_options dbms_aq.enqueue_options_t;
      l_message_properties dbms_aq.message_properties_t;
      l_correlation varchar2(255) := NULL;
      l_msgid raw(16);

l_queueu VARCHAR2(200);
      l_queueName VARCHAR2(30);
      l_queueTable VARCHAR2(200);
      l_schemaName VARCHAR2(320);
      l_msgLength NUMBER;
      l_sqlbuf VARCHAR2(2000);
      
      l_amount binary_integer;
      l_pos pls_integer;

l_dequeue_options dbms_aq.dequeue_options_t;
   begin

/** wf_message_payload_t is obsolete in 2.6.4 onwards **/
     null;

exception
    when others then
       wf_core.context('WF_XML','EnqueueLOBMessage',p_queue, 
                       to_char(p_priority),
                       p_correlation);
       raise;
   end EnqueueLOBMessage;

-- EnqueueMessage
   -- IN
   --    Queue Name
   --    Priority of the message
   --    Correlation for the message - the NID of the notification 
   --       for this implementation.
   --    Message - XML encoded.
   procedure EnqueueMessage(p_queue in varchar2, 
                            p_priority number,
                            p_correlation in varchar2,
                            p_message in VARCHAR2) is

l_enqueue_options dbms_aq.enqueue_options_t;
      l_message_properties dbms_aq.message_properties_t;
      l_correlation varchar2(255) := NULL;
      l_msgid raw(16);

-- l_msgLob CLOB;
      l_msgLobIdx pls_integer;
      l_queueu VARCHAR2(200);
      l_queueName VARCHAR2(30);
      l_queueTable VARCHAR2(200);
      l_schemaName VARCHAR2(320);
      l_msgLength NUMBER;
      l_sqlbuf VARCHAR2(2000);
      
      l_amount binary_integer;
      l_pos pls_integer;

l_dequeue_options dbms_aq.dequeue_options_t;
   begin

/** wf_message_payload_t is obsolete in 2.6.4 onwards **/
     null;

exception
    when others then
        -- just in case, check and free it any way.
       wf_temp_lob.ReleaseLob(g_LOBTable, l_msgLobIdx);
       wf_core.context('WF_XML','EnqueueMessage',p_queue, to_char(p_priority),
                       p_correlation);
       raise;
   end EnqueueMessage;

-- NewLOBTag - Create a new TAG node and insert it into the
   --          Document Tree
   -- IN
   --    document as a CLOB
   --    Position to take the new Tag Node
   --    New Tag to be created
   --    Data to be added between the start and end TAGs
   --    Attribute list to be included in the opening TAG
   -- OUT
   --    The document containing the new TAG.
   function NewLOBTag (p_doc in out NOCOPY CLOB,
                    p_pos in integer,
                    p_tag in varchar2,
                    p_data in varchar2,
                    p_attribute_list IN OUT NOCOPY wf_xml_attr_table_type) 
                    return integer 
   is

-- l_temp CLOB;
      l_tempStr varchar2(32000);
      l_tempIdx pls_integer;
      l_node varchar2(32000);
      l_start varchar2(32000);
      l_end varchar2(250);
      l_pos integer;
      l_nodesize number;
      
      l_size number;
      l_amount number;

begin

-- Create an instance of the node
      -- A Node is deemed to be <TAG>Data</TAG>
      -- dbms_lob.createTemporary(l_temp, TRUE, dbms_lob.CALL);
      l_tempIdx := -1;
      
      l_start := '<' || upper(p_tag);

-- If there are any attributes to add to the tag, then
      -- add them now, otherwise, close off the TAG.
      if p_attribute_list.COUNT = 0 then
         l_start := l_start || '>';
      else
         for i in 1..p_attribute_list.COUNT loop
            l_start := l_start || ' ' || 
                       p_attribute_list(i).attribute || '="' || 
                       p_attribute_list(i).value || '"';
         end loop;
         l_start := l_start || '>';
      end if;

-- Create the end TAG.
      l_end := '</' || upper(p_tag) || '>'||g_newLine;

l_size := dbms_lob.getlength(p_doc);

-- Create the full node to be inserted.
      l_node := l_start || p_data || l_end;
      l_nodesize := length(l_node);

l_amount := 0;      
      if l_size > 1 and l_size <> p_pos then
         -- Copy the tail end of the LOB to a holder.
         l_amount := l_size - p_pos;
         if l_amount < 32000 then
            dbms_lob.read(lob_loc => p_doc, 
                          amount => l_amount,
                          offset => p_pos + 1,
                          buffer => l_tempStr);
         else
            l_tempIdx := wf_temp_lob.GetLob(g_LOBTable);
            dbms_lob.copy(dest_lob => g_LOBTable(l_tempIdx).temp_lob, 
                          src_lob => p_doc, 
                          amount => l_amount, 
                          dest_offset => 1, 
                          src_offset => p_pos +1);
         end if;
      end if;

-- Now insert the new node into the p_pos location      
      dbms_lob.Write(p_doc, l_nodesize, p_pos + 1 , l_node);

-- Append the saved portion of the LOB
      -- If l_tempIdx is still -1, then no LOB was used or initialised
      -- but for the lob, makesure that there is something in it to be 
      -- used (l_amount > 0).
      if l_tempIdx = -1 and l_amount > 0 then
         dbms_lob.write(lob_loc => p_doc, 
                        amount => l_amount, 
                        offset => p_pos + l_nodesize + 1,
                        buffer => l_tempStr);
      elsif l_amount > 0 then
         dbms_lob.copy(dest_lob => p_doc, 
                       src_lob => g_LOBTable(l_tempIdx).temp_lob, 
                       amount => l_amount , 
                       dest_offset => p_pos + l_nodesize + 1);
         wf_temp_lob.ReleaseLob(g_LOBTable, l_tempIdx);
      end if;

l_pos := (p_pos + l_nodesize) - length(l_end);
      -- Free up the use of the temporary LOB
      -- dbms_lob.FreeTemporary(l_temp);

if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
         wf_log_pkg.string(WF_LOG_PKG.level_statement, 
                          'wf.plsql.WF_XML.newLOBTag',
                          'TAG: '||l_start||' POS: '||to_char(l_pos));
      end if;
 
      return l_pos;

exception
      when others then
         wf_temp_lob.ReleaseLob(g_LOBTable, l_tempIdx);
         wf_core.context('WF_XML','NewLOBTag', p_tag);
         raise;
   end NewLOBTag;

-- NewLOBTag - Create a new TAG node and insert it into the
   --          Document Tree
   -- IN
   --    document as a CLOB
   --    Position to take the new Tag Node
   --    New Tag to be created
   --    Data to be added between the start and end TAGs
   --    Attribute list to be included in the opening TAG
   -- OUT
   --    The document containing the new TAG.
   function NewLOBTag (p_doc in out NOCOPY CLOB,
                    p_pos in integer,
                    p_tag in varchar2,
                    p_data in CLOB,
                    p_attribute_list IN OUT NOCOPY wf_xml_attr_table_type) 
                    return integer 
   is

l_tempIdx pls_integer;
      l_nodeIdx pls_integer;
      l_tempStr varchar2(32000);
      l_start varchar2(250);
      l_end varchar2(250);
      l_pos integer;
      l_nodesize number;
      
      l_size number;
      l_dataSize number;
      l_amount number;

begin

-- Create an instance of the node
      -- A Node is deemed to be <TAG>Data</TAG>
      l_tempIdx := -1;
      l_nodeIdx := wf_temp_lob.getLob(g_LOBTable);
      
      l_start := '<' || upper(p_tag);

-- Create the end TAG.
      l_end := '</' || upper(p_tag) || '>'||g_newLine;

l_size := dbms_lob.getlength(p_doc);
      l_dataSize := dbms_lob.getlength(p_data);

-- Create the full node to be inserted.
      dbms_lob.writeAppend(g_LOBTable(l_nodeIdx).temp_lob, length(l_start), 
                           l_start);
      l_nodesize := length(l_start); -- dbms_lob.getLength(g_LOBTable(l_nodeIdx).temp_lob);
      dbms_lob.copy(dest_lob => g_LOBTable(l_nodeIdx).temp_lob,
              src_lob => p_data,
              amount => l_dataSize,
              dest_offset => l_nodesize+1,
              src_offset => 1);
      dbms_lob.writeAppend(g_LOBTable(l_nodeIdx).temp_lob, length(l_end), 
                           l_end);
      l_nodesize := dbms_lob.getLength(g_LOBTable(l_nodeIdx).temp_lob);

l_amount := 0;      
      if l_size > 1 and l_size <> p_pos then
         l_amount := l_size - p_pos;
         -- Copy the tail end of the LOB to a holder.
         if l_amount < 32000 then
            dbms_lob.read(lob_loc => p_doc, 
                          amount => l_amount,
                          offset => p_pos + 1,
                          buffer => l_tempStr);
         else
            l_tempIdx := wf_temp_lob.GetLob(g_LOBTable);
            dbms_lob.copy(dest_lob => g_LOBTable(l_tempIdx).temp_lob, 
                          src_lob => p_doc, 
                          amount => l_amount, 
                          dest_offset => 1, 
                          src_offset => p_pos +1);
         end if;
      end if;

-- Now insert the new node into the p_pos location      
      dbms_lob.copy(dest_lob => p_doc, 
                    src_lob => g_LOBTable(l_nodeIdx).temp_lob, 
                    amount => l_nodesize, 
                    dest_offset => p_pos + 1);

-- Append the saved portion of the LOB
      if l_tempIdx = -1 and l_amount > 0 then
         dbms_lob.write(lob_loc => p_doc,
                        amount => l_amount,
                        offset => p_pos + l_nodesize + 1,
                        buffer => l_tempStr);
      elsif l_tempIdx > 0 then
         if l_amount > 0 then
            dbms_lob.copy(dest_lob => p_doc, 
                          src_lob => g_LOBTable(l_tempIdx).temp_lob, 
                          amount => l_amount, 
                          dest_offset => p_pos + l_nodesize + 1);
         end if;
         wf_temp_lob.releaseLOB(g_LOBTable, l_tempIdx);
      end if;

l_pos := (p_pos + l_nodesize) - length(l_end);
      -- Free up the use of the temporary LOBs
      wf_temp_lob.releaseLOB(g_LOBTable, l_nodeIdx);
      
      if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
         wf_log_pkg.string(WF_LOG_PKG.level_statement, 
                          'wf.plsql.WF_XML.newLOBTag',
                          'TAG: '||l_start||' POS: '||to_char(l_pos));
      end if;

return l_pos;

exception
      when others then
         wf_temp_lob.releaseLOB(g_LOBTable, l_tempIdx);
         wf_temp_lob.releaseLOB(g_LOBTable, l_nodeIdx);
         wf_core.context('WF_XML','NewLOBTag', p_tag);
         raise;
   end NewLOBTag;

-- NewTag - Create a new TAG node and insert it into the
   --          Document Tree
   -- IN
   --    document as a CLOB
   --    Position to take the new Tag Node
   --    New Tag to be created
   --    Data to be added between the start and end TAGs
   --    Attribute list to be included in the opening TAG
   -- OUT
   --    The document containing the new TAG.
   function NewTag (p_doc in out NOCOPY VARCHAR2,
                    p_pos in integer ,
                    p_tag in varchar2,
                    p_data in varchar2,
                    p_attribute_list IN OUT NOCOPY wf_xml_attr_table_type) 
      return integer 
   is

l_temp VARCHAR2(32000);
      l_node varchar2(32000);
      l_start varchar2(250);
      l_end varchar2(250);
      l_pos integer;
      l_nodesize number;
      
      l_size number;
      l_amount number;

begin

-- Create an instance of the node
      -- A Node is deemed to be <TAG>Data</TAG>
      
      l_start := '<' || upper(p_tag);

-- Create the end TAG.
      l_end := '</' || upper(p_tag) || '>'||g_newLine;

l_size := length(p_doc);

-- Create the full node to be inserted.
      l_node := l_start || p_data || l_end;
      l_nodesize := length(l_node);

l_amount := 0;      
      if l_size > 1 and l_size <> p_pos then
         -- Copy the tail end of the LOB to a holder.
         l_amount := l_size - p_pos;
         l_temp := substr(p_doc, p_pos +1, l_amount);
      end if;

-- Now insert the new node into the p_pos location      
      p_doc := substr(p_doc, 1, p_pos)||l_node;

if Length(l_temp) > 1 then
         -- Append the saved portion of the LOB
         p_doc := p_doc||l_temp;
      end if;

l_pos := (p_pos + l_nodesize) - length(l_end);
      
      if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
         wf_log_pkg.string(WF_LOG_PKG.level_statement, 
                          'wf.plsql.WF_XML.newTag',
                          'TAG: '||l_start||' POS: '||to_char(l_pos));
      end if;
      return l_pos;

exception
      when others then
         wf_core.context('WF_XML','NewTag', p_tag);
         raise;
   end NewTag;

-- SkipLOBTag - To move return a pointer past the nominated TAG
   --           starting from a given position in the document.
   -- IN
   --    document
   --    Position to take the new Tag Node
   --    New Tag to be created
   --    Data to be added
   -- RETURN
   --   New position past the </TAG>.
   function SkipLOBTag (p_doc in out NOCOPY CLOB,
                     p_tag in varchar2,
                     p_offset in out NOCOPY integer,
                     p_occurance in out NOCOPY integer) return integer is

l_pos integer;
      l_tag varchar2(250);

begin

l_tag := '</'||upper(p_tag)||'>';   
      l_pos := dbms_lob.instr(p_doc, l_tag, p_offset, p_occurance);
      
      return l_pos + length(l_tag);

exception
      when others then
         wf_core.context('WF_XML','SkipLOBTag', p_tag, to_char(p_offset),
                         to_char(p_occurance));
         raise;
   end SkipLOBTag;

-- SkipTag - To move return a pointer past the nominated TAG
   --           starting from a given position in the document.
   -- IN
   --    document
   --    Position to take the new Tag Node
   --    New Tag to be created
   --    Data to be added
   -- RETURN
   --   New position past the </TAG>.
   function SkipTag (p_doc in out NOCOPY VARCHAR2,
                     p_tag in varchar2,
                     p_offset in out NOCOPY integer,
                     p_occurance in out NOCOPY integer) return integer is

l_pos integer;
      l_tag varchar2(250);

begin

l_tag := '</'||upper(p_tag)||'>';   
      l_pos := instr(p_doc, l_tag, p_offset, p_occurance);
      
      return l_pos + length(l_tag);

exception
      when others then
         wf_core.context('WF_XML','SkipTag', p_tag, to_char(p_offset),
                         to_char(p_occurance));
         raise;
   end SkipTag;

-- GetTagValue - Obtain the value for a given TAG from within the 
   --               Document Tree
   -- IN
   --    document as a CLOB
   --    TAG to find the value of
   --    The position to start looking for the TAG from
   -- OUT
   --    Value of the TAG. ie the value between the start and end TAGs
   --    The position in the CLOB after the find
   --    The list of attributes associated with the TAG (Not implemented as yet)
   procedure GetTagValue(p_doc in out NOCOPY CLOB, 
                         p_tag in varchar2, 
                         p_value out NOCOPY varchar2, 
                         p_pos in out NOCOPY integer,
                         p_attrlist in out NOCOPY wf_xml_attr_table_type) 
   as

l_value varchar2(32000);
      l_length integer;
      l_startTag varchar2(255);
      l_endTag varchar2(255);
      l_startPos integer;
      l_endPos integer;
      l_pos integer;
      l_occurance integer := 1;

begin

-- The idea is to look for the value of a tag from 
      -- a given point (p_pos)
      l_pos := p_pos;
      -- Set the opening TAG. Don't use the '>' as there may be
      -- attributes set on the TAG.
      l_startTag := '<'||upper(p_tag);
      l_endTag := '</'||upper(p_tag)||'>';

l_startPos := dbms_lob.instr(p_doc, l_startTag, l_pos, l_occurance);
      l_startPos := dbms_lob.instr(p_doc, '>', l_startPos, l_occurance) + 1;

l_endPos := dbms_lob.instr(p_doc, l_endTag, l_startpos, l_occurance) - 1;
      l_length := l_endPos - l_startPos + 1;

dbms_lob.read(p_doc, l_length, l_startPos, l_value);

-- Reposition the position pointer to after the end
      -- of the current TAG set.
      p_pos := l_endPos + length(l_endTag) + 1;
      p_value := l_value;
   exception
      when others then
         wf_core.context('WF_XML','GetTagValue',p_tag, to_char(p_pos));
         raise;
   end GetTagValue;

-- AddElementAttribute - Add an Element Attribute Value pair to the attribute
   --                       list.
   -- IN
   --    Name of the attribute
   --    Value for the attribute
   --    The attribute list to add the name/value pair to.
   procedure AddElementAttribute(p_attribute_name IN VARCHAR2,
                                 p_attribute_value IN VARCHAR2,
                                 p_attribute_list IN OUT NOCOPY wf_xml_attr_table_type) 
   is
      l_index integer;
   begin
      l_index := p_attribute_list.COUNT + 1;
      p_attribute_list(l_index).attribute := p_attribute_name;
      p_attribute_list(l_index).value := p_attribute_value;
   exception
      when others then 
         wf_core.context('WF_XML','AddElementAttribute',p_attribute_name, 
                         p_attribute_value);
         raise;
   end;

-- STRPARSER - Parse a string and seperate the elements
   --             into a memeory table based on the content of the 
   --             seperators.
   -- IN
   --    str - The Varchar2 that is to be parsed
   --    sep - The list of SINGLE character seprators that will
   --          segment the str.
   -- RETURN parserStack_t a memory table of Varchar2
   --
   function strParser(str in varchar2, sep in varchar2) return parserStack_t
   is
         quot pls_integer;
         i pls_integer;
         c varchar2(1);
         attr varchar2(2000);
         defv varchar2(2000);
   
         stack parserStack_t;
         buf varchar2(2000);
   
   begin
      if str is not null or str <> '' then
         quot := 1;
         i := 1;
         buf := '';
         while i <= length(str) loop
            c := substrb(str, i, 1);
            if instrb(sep, c,1 ,1)>0 then
               if buf is not null or buf <> '' then
                  -- Push the buffer to the stack and start again
                  stack(quot) := trim(buf);
                  quot := quot + 1;
                  buf := '';
               end if;
            elsif c = '\' then
               -- Escape character. Consume this and the next
               -- character.
               i := i + 1;
               c := substrb(str, i, 1);
               buf := buf ||c;
            else
               buf := buf || c;
            end if;
            i := i + 1;
         end loop;
         if buf is not null or buf <> '' then
            stack(quot) := trim(buf);
         end if;
       end if;
       return stack;
   end strParser;

-- parseContentType
   -- IN
   --    pContentType The Content-Type string to be parsed
   -- OUT
   --    pMimeType The MIME type part of the Content-Type
   --    pFileName The file name part of the Content-Type
   --    pExtn The extention of the file name
   --    pEncoding The content-encoding part of the Content-Type
   --
   -- NOTE: For bug 3735752, a copy of parseContentType was taken and
   --       placed into WF_MAIL_UTIL in order to prevent a dependancy
   --       on WF_XML for WFA_HTML. This copy remains in tact.
   procedure parseContentType(pContentType in varchar2,
                              pMimeType out nocopy varchar2,
                              pFileName out nocopy varchar2,
                              pExtn out nocopy varchar2,
                              pEncoding out nocopy varchar2)
   is
      i pls_integer;
      l_content_type varchar2(255);
      l_paramlist parserStack_t;
      l_sublist parserStack_t;

begin
      -- Derrive the name for the attachment.
      l_content_type := pContentType;
      pExtn := '';
      pFilename := '';
      pMimeType := '';
      pEncoding := '';
      l_paramlist := strParser(l_content_type, ';');
      pMimeType := l_paramlist(1);
      for i in 1..l_paramlist.COUNT loop
         l_sublist := strParser(l_paramlist(i),'/');
         if l_sublist.COUNT = 2 then
            pExtn := l_sublist(2);
         end if;
         l_sublist.DELETE;
         l_sublist := strParser(l_paramList(i),'="');
         for i in 1..l_sublist.COUNT loop
            if lower(l_sublist(i)) = 'name' then
               pFilename := l_sublist(i+1);
            end if;
            if lower(l_sublist(i)) = 'encoding' then
               pEncoding := l_sublist(i+1);
            end if;
         end loop;
         l_sublist.DELETE;
      end loop;
      if lower(pExtn) = 'tab-separated-values' then
         pExtn := 'tsv';
      elsif
         lower(pExtn) = 'comma-separated-values' then
         pExtn := 'csv';
      elsif
         lower(pExtn) = 'plain' then
         pExtn := 'txt';
      elsif
         lower(pExtn) like '%excel' then
         pExtn := 'xls';
      elsif
         lower(pExtn) = 'html' then
         pExtn := 'htm';
      elsif
         lower(pExtn) like '%msword' then
         pExtn := 'doc';
      end if;

end parseContentType;
   
   -- GetAttachment - Create an attachment tag for each of the URLs and 
   --                 DOCUMENT attributes.
   -- IN
   --    Notification ID
   --    Document handle 
   --    MIME Agent for the attachment
   --    Position in the document where to place the attachment.
   -- OUT
   --    New location of the position
   function GetAttachment(p_nid in number,
                          p_doc in out NOCOPY CLOB, 
                          p_agent in varchar2,
                          p_disposition in varchar2,
                          p_doc_type in varchar2,
                          p_pos in out NOCOPY integer) return integer
   is

l_pos integer;
      l_occurance integer := 1;
      l_tmpcontent varchar2(32000);
      -- l_content CLOB;
      l_contentIdx pls_integer;
      l_blob BLOB;
      l_atthname varchar2(255);
      l_display_type varchar2(255) := p_doc_type;
      l_content_type varchar2(255);
      l_attrlist wf_xml_attr_table_type;
      l_cbuf varchar2(32000);
      l_doc_end integer;
      l_doc_length number;
      l_start VARCHAR2(10) := '<![CDATA[';
      l_end VARCHAR2(4) := ']]>';
      l_isURLAttrs boolean;
      l_aname varchar2(30);

l_error_result  varchar2 (2000);
      l_err_name      varchar2(30);
      l_err_message   varchar2(2000);
      l_err_stack     varchar2(4000);

cursor c_attr(p_nid varchar2) is
      select WMA.TYPE, WMA.DISPLAY_NAME, 
             decode(WMA.TYPE, 'URL', WF_NOTIFICATION.GetUrlText(WNA.TEXT_VALUE,
                    p_nid), WNA.TEXT_VALUE) URL, WNA.NAME 
      from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, 
           WF_MESSAGE_ATTRIBUTES_VL WMA 
      where WNA.NOTIFICATION_ID = p_nid
      and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID 
      and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE 
      and WN.MESSAGE_NAME = WMA.MESSAGE_NAME 
      and (WMA.TYPE = 'URL' or WMA.TYPE = 'DOCUMENT') 
      and WMA.ATTACH = 'Y' 
      and WMA.NAME = WNA.NAME;
   
      l_extn varchar2(20);
      l_mimeType varchar2(255);
      l_encoding varchar2(255);
      l_filename varchar2(320);
      l_attr_url varchar2(32000);

begin
      -- dbms_lob.createTemporary(l_content, TRUE, dbms_lob.CALL);
      l_contentIdx := wf_temp_lob.getLOB(g_LOBTable);
      dbms_lob.createTemporary(l_blob, TRUE, dbms_lob.CALL);
      l_pos := p_pos;
      for l_crec in c_attr(p_nid) loop
         dbms_lob.trim(g_LOBTable(l_contentIdx).temp_lob, 0);
         if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then

wf_log_pkg.string(WF_LOG_PKG.level_statement, 
                             'wf.plsql.WF_XML.GetAttachment',
                             'Document URL {'||l_crec.url||'}');
         end if;

if l_crec.type = 'URL' then
            l_isURLAttrs := true;
         else
            /*
            ** The mechanism to obtain PLSQL and PLSQLCLOB documents
            ** as attachments.
            */
            if upper(substr(l_crec.url,1, 6)) = 'PLSQL:' then

-- wf_mail.getDocContent(p_nid, l_crec.name, l_display_type, 
               --                       l_tmpcontent, l_error_result);
               -- bug 2879507. call wf_notification.GetAttrDoc2 to get the 
               -- doc content as well as the document type 
               wf_notification.GetAttrDoc2(p_nid, l_crec.name, l_display_type, 
                                       l_tmpcontent, l_content_type); 
               l_tmpContent := replace(l_tmpContent, g_fndapi_misschr);
               -- default to display type if no document type specified 
               if (l_content_type is null) then 
                  l_content_type := l_display_type; 
               end if;  
               -- Derrive the name for the attachment.
               parseContentType(l_content_type, l_mimeType, l_filename,
                                l_extn, l_encoding);
               if l_filename is null or l_filename = '' then
                  l_filename := l_crec.display_name ||'.'||l_extn;
               end if;

AddElementAttribute('content-type',l_mimeType, l_attrlist);
               l_pos := NewLOBTag(p_doc, l_pos, 'BODYPART', '', l_attrlist);
               l_attrlist.DELETE;
               AddElementAttribute('content-type',l_mimeType, l_attrlist);
               AddElementAttribute('content-disposition',p_disposition, 
                                   l_attrlist);
               AddElementAttribute('filename', l_filename, l_attrlist);
               AddElementAttribute('encoding', l_encoding, l_attrlist);
               dbms_lob.trim(g_LOBTable(l_contentIdx).temp_lob,0);
               dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob, 
                                    length(l_start), l_start);
               dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob, 
                                    length(l_tmpContent), 
                                 l_tmpContent);
               dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob, 
                                    length(l_end), l_end);

l_pos := NewLOBTag(p_doc, l_pos, 'MESSAGE', 
                                  g_LOBTable(l_contentIdx).temp_lob, 
                                 l_attrlist);
               l_pos := SkipLOBTag(p_doc, 'MESSAGE', l_pos, l_occurance);
               l_pos := SkipLOBTag(p_doc, 'BODYPART', l_pos, l_occurance);
            elsif upper(substr(l_crec.url,1, 10)) = 'PLSQLCLOB:' then
               /*
               ** For PLSQLCLOB documents.
               */
               dbms_lob.trim(g_LOBTable(l_contentIdx).temp_lob,0);
               l_content_type := '';

-- 
               -- Getting Content
               -- First we call the existing APIs to render the
               -- content and then we fetch the content into 
               -- the new structure.
               -- 
               wf_mail.getLOBDocContent(p_nid, l_crec.name, l_display_type,
                                        l_content_type, l_error_result);

if l_error_result is not null and l_error_result <> '' then
                  -- Error in getting content
                  wf_core.context('WF_XML', 'GetAttachment', to_char(p_nid),
                                  l_display_type);
                  wf_core.raise('WFMLR_DOCUMENT_ERROR');
               end if;

-- default to display type is no document type is specified 
               if (l_content_type is null) then 
                  l_content_type := l_display_type; 
               end if; 
               -- Derrive the name for the attachment.
               parseContentType(l_content_type, l_mimeType, l_filename,
                                l_extn, l_encoding);
               if l_filename is null or l_filename = '' then
                  l_filename := l_crec.display_name ||'.'||l_extn;
               end if;

if lower(l_mimeType) not like 'text/%' and
                  (l_encoding is null or lower(l_encoding) <> 'base64') then
                  -- Assume that there has been no encoding on a RAW
                  -- type lob. Do not include it here. Defer attaching
                  -- the content.
                  --
                  -- Build the attribute list for the attachment
                  -- including the content-type, file name etc.
                  --

-- First the BODYPART structure to take the MESSAGE
                  AddElementAttribute('content-type',l_mimeType, l_attrlist);
                  l_pos := NewLOBTag(p_doc, l_pos, 'BODYPART', '', l_attrlist);
                  l_attrlist.DELETE;
                  l_attr_url := Wf_Notification.GetText(l_crec.URL, p_nid, l_display_type);
                  AddElementAttribute('src', l_attr_url, l_attrlist);
                  AddElementAttribute('content-type',l_mimeType, l_attrlist);
                  AddElementAttribute('content-disposition',p_disposition,
                                      l_attrlist);
                  AddElementAttribute('filename', l_filename, l_attrlist);

l_pos := NewLOBTag(p_doc, l_pos, 'MESSAGE', '',
                                     l_attrlist);
                  l_pos := SkipLOBTag(p_doc, 'MESSAGE', l_pos, l_occurance);
                  l_pos := SkipLOBTag(p_doc, 'BODYPART', l_pos, l_occurance);
                  WF_MAIL.CloseLob(l_display_type);
               else

-- Fetch the content
                  WF_MAIL.InitFetchLOB(l_display_type, l_doc_length);
                  l_doc_end := 0;
                  dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob, 
                                       length(l_start), l_start);
                  while l_doc_end = 0 loop
                     WF_MAIL.FetchLobContent(l_tmpContent, l_display_type, 
                                    l_doc_end);
                     l_tmpContent := replace(l_tmpContent, g_fndapi_misschr);
                     dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob, 
                                          length(l_tmpContent), l_tmpContent);
                  end loop;
                  WF_MAIL.CloseLob(l_display_type);
                  dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob, 
                                       length(l_end), l_end);
                  --
                  -- Build the attribute list for the attachment
                  -- including the content-type, file name etc.
                  --

-- First the BODYPART structure to take the MESSAGE
                  AddElementAttribute('content-type',l_mimeType, l_attrlist);
                  l_pos := NewLOBTag(p_doc, l_pos, 'BODYPART', '', l_attrlist);
                  l_attrlist.DELETE;
   
                  AddElementAttribute('content-type', l_mimeType, l_attrlist);
                  AddElementAttribute('content-disposition',p_disposition, 
                                      l_attrlist);
                  AddElementAttribute('filename', l_filename, l_attrlist);
                  AddElementAttribute('encoding', l_encoding, l_attrlist);

l_pos := NewLOBTag(p_doc, l_pos, 'MESSAGE', 
                                     g_LOBTable(l_contentIdx).temp_lob, 
                                     l_attrlist);
                  l_pos := SkipLOBTag(p_doc, 'MESSAGE', l_pos, l_occurance);
                  l_pos := SkipLOBTag(p_doc, 'BODYPART', l_pos, l_occurance);
               end if;
            elsif upper(substr(l_crec.url,1, 10)) = 'PLSQLBLOB:' then
               /*
               ** For PLSQLBLOB documents.
               */
               dbms_lob.trim(l_blob,0);

-- 
               -- Getting Content
               -- First we call the existing APIs to render the
               -- content and then we fetch the content into 
               -- the new structure.
               -- 
               begin
                  Wf_Notification.GetAttrBLOB(p_nid, l_crec.name, 
                                              l_display_type,
                                              l_blob, l_content_type,  
                                              l_aname);
               exception
                  when others then
                     if (wf_log_pkg.level_error >= 
                         fnd_log.g_current_runtime_level) then
                        wf_log_pkg.string(WF_LOG_PKG.level_error, 
                          'wf.plsql.WF_XML.GetAttachment',
                          'Error when getting BLOB attachment -> '||sqlerrm);
                     end if;
                     wf_core.context('WF_XML', 'GetAttachment', to_char(p_nid),
                                     l_display_type);
                     wf_core.raise('WFMLR_DOCUMENT_ERROR');
               end;

-- First the BODYPART structure to take the MESSAGE
               AddElementAttribute('content-type',l_mimeType, l_attrlist);
               l_pos := NewLOBTag(p_doc, l_pos, 'BODYPART', '', l_attrlist);
               l_attrlist.DELETE;
               l_attr_url := Wf_Notification.GetText(l_crec.URL, p_nid, l_display_type);
               AddElementAttribute('src', l_attr_url, l_attrlist);
               AddElementAttribute('content-type',l_mimeType, l_attrlist);
               AddElementAttribute('content-disposition',p_disposition,
                                      l_attrlist);
               AddElementAttribute('filename', l_filename, l_attrlist);

l_pos := NewLOBTag(p_doc, l_pos, 'MESSAGE', '',
                                  l_attrlist);
               l_pos := SkipLOBTag(p_doc, 'MESSAGE', l_pos, l_occurance);
               l_pos := SkipLOBTag(p_doc, 'BODYPART', l_pos, l_occurance);

end if;
         end if;
         l_attrlist.DELETE;
      end loop;

-- BUG 3285943 - If this is for a framework notification
      -- then we don't need the attached URLs.

if (WF_NOTIFICATION.isFwkRegion(p_nid)='Y' and g_install='EMBEDDED') then
         l_isURLAttrs := FALSE;
      end if;

if l_isURLAttrs then
         dbms_lob.trim(g_LOBTable(l_contentIdx).temp_lob,0);

wf_mail.GetURLAttachment(p_nid, l_tmpContent, l_error_result);
         dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob, 
                              length(l_start), l_start);
         dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob, 
                              length(l_tmpContent), l_tmpContent);
         dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob, 
                              length(l_end), l_end);

if l_error_result is not null and l_error_result <> '' then
            wf_core.context('WF_XML', 'GetAttachments', to_char(p_nid), 
                            p_agent, to_char(p_pos));
            wf_core.token('SQLERR', l_error_result);
            wf_core.raise('WF_URLLIST_ERROR');
         end if;

l_fileName := g_urlListAttachment||'.html';

-- First the BODYPART structure to take the MESSAGE
         AddElementAttribute('content-type',l_display_type, l_attrlist);
         l_pos := NewLOBTag(p_doc, l_pos, 'BODYPART', '', l_attrlist);
         l_attrlist.DELETE;

AddElementAttribute('content-type',g_ntfDocHtml, 
                             l_attrlist);
         AddElementAttribute('content-disposition',p_disposition, 
                             l_attrlist);
         AddElementAttribute('filename', l_filename, l_attrlist);

l_pos := NewLOBTag(p_doc, l_pos, 'MESSAGE', 
                            g_LOBTable(l_contentIdx).temp_lob, l_attrlist);
         l_pos := SkipLOBTag(p_doc, 'MESSAGE', l_pos, l_occurance);
         l_pos := SkipLOBTag(p_doc, 'BODYPART', l_pos, l_occurance);
 
      end if;
      wf_temp_lob.releaseLob(g_LOBTable, l_contentIdx);
      if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
         wf_log_pkg.string(WF_LOG_PKG.level_procedure, 
                          'wf.plsql.WF_XML.GetAttachment',
                          'END');
      end if;

return l_pos;
   exception
      when others then 
         WF_MAIL.CloseLob(l_display_type);
         wf_temp_lob.releaseLob(g_LOBTable, l_contentIdx);
         wf_core.context('WF_XML', 'GetAttachments', to_char(p_nid), p_agent, 
                         to_char(p_pos));
         l_err_message := sqlerrm;
         raise;
   end GetAttachment;

-- GetAttributes - Create an attribute tag for each of the response
   --                 required attributes.
   -- IN
   --    Notification ID
   --    Document handle
   --    MIME Agent for the attribute
   --    Location in the document to insert the new TAG(s)
   -- RETURN
   --    The new position in the docuemnt.
   function GetAttributes(p_nid in number,
                           p_doc in out NOCOPY CLOB, 
                           p_agent in varchar2,
                           p_pos in out NOCOPY integer) return integer
   is

l_pos integer;
      l_attrlist      wf_xml_attr_table_type;
      l_occurance integer := 1;
      l_error_result    varchar2 (2000);
      l_err_name      varchar2(30);
      l_err_message   varchar2(2000);
      l_err_stack     varchar2(4000);
      l_value varchar2(2000);

cursor c1 is
      select WMA.NAME, WMA.DISPLAY_NAME, WMA.DESCRIPTION, WMA.TYPE, WMA.FORMAT,
             decode(WMA.TYPE,
               'VARCHAR2', decode(WMA.FORMAT,
                             '', WNA.TEXT_VALUE,
                             substr(WNA.TEXT_VALUE, 1, to_number(WMA.FORMAT))),
               'NUMBER', decode(WMA.FORMAT,
                           '', to_char(WNA.NUMBER_VALUE),
                           to_char(WNA.NUMBER_VALUE, WMA.FORMAT)),
               'DATE', decode(WMA.FORMAT,
                         '', to_char(WNA.DATE_VALUE),
                         to_char(WNA.DATE_VALUE, WMA.FORMAT)),
               'LOOKUP', WNA.TEXT_VALUE,
               WNA.TEXT_VALUE) VALUE
      from   WF_NOTIFICATION_ATTRIBUTES WNA,
             WF_NOTIFICATIONS WN,
             WF_MESSAGE_ATTRIBUTES_VL WMA
      where  WNA.NOTIFICATION_ID = p_nid
        and    WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
        and    WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
        and    WN.MESSAGE_NAME = WMA.MESSAGE_NAME
        and    WMA.NAME = WNA.NAME
        and    WMA.SUBTYPE = 'RESPOND'
        and    WMA.TYPE not in ('FORM', 'URL')
       order  by WMA.SEQUENCE;

begin

l_pos := p_pos;
      for rec in c1 loop
         l_attrlist.DELETE;
         AddElementAttribute('name',rec.name, l_attrlist);
         AddElementAttribute('type',rec.type, l_attrlist);
         if rec.format is not NULL then
            AddElementAttribute('format',rec.format, l_attrlist);
         end if;
         l_pos := NewLOBTag(p_doc, l_pos, 'ATTRIBUTE','',l_attrlist);
         l_attrlist.DELETE;
         l_pos := NewLOBTag(p_doc, l_pos, 'NAME', rec.display_name, l_attrlist);
         l_pos := SkipLOBTag(p_doc, 'NAME', l_pos, l_occurance);
         l_value := EncodeEntityReference(rec.value);
         l_pos := NewLOBTag(p_doc, l_pos, 'VALUE', rec.value, l_attrlist);

l_pos := SkipLOBTag(p_doc, 'ATTRIBUTE', l_pos, l_occurance);

end loop;
      return l_pos;
   exception
      when others then
         wf_core.context('WF_XML', 'GetAttributes', to_char(p_nid));
         raise;
   end GetAttributes;

-- GetXMLMessage - Return a CLOB Document containing an XML encoded version of the
   --                 notification. No recipients list will be populated. That
   --                 will be the responsibility of the calling procedure.
   --
   -- IN
   --     notification id
   --     Protocol for the message
   --     List of recipients to recieve the notification
   --     mailer node name
   --     Web Agent for the HTML attachments
   --     Reply to address for the final notification
   --     Language for the notification
   --     Territory for the notification
   -- OUT
   --     Piority for the message
   --     A CLOB Containing the XML encoded message.
   procedure GetXMLMessage (p_nid       in  number,
                        p_protocol  in varchar2,
                        p_recipient_list in WF_DIRECTORY.wf_local_roles_tbl_type,
                        p_node      in  varchar2,
                        p_agent     in  varchar2,
                        p_replyto   in  varchar2,
                        p_nlang     in  varchar2,
                        p_nterr     in varchar2,
                        p_priority out NOCOPY number,
                        p_message in out NOCOPY CLOB)
 is
      -- l_doc             CLOB;
      l_docIdx          pls_integer;
      l_doctype         varchar(100);
      l_pos             integer;
      l_occurance       integer := 1;
      l_item_type       wf_items.item_type%TYPE;
      l_item_key        wf_items.item_key%TYPE;
      l_priority        wf_notifications.priority%TYPE;
      l_access_key      wf_notifications.access_key%TYPE;

l_response        integer;

l_attrlist        wf_xml_attr_table_type;
      l_receiverlist    varchar2 (4000);
      l_status          varchar2 (8);
      l_language        varchar2 (30);
      l_territory       varchar2 (30);
      l_installed_lang  varchar2 (1);
      l_str             varchar2 (250);
      l_subject         varchar2 (2000);
      l_text_body       varchar2 (32000);
      l_html_body       varchar2 (32000);
      l_body_atth       varchar2 (32000);
      l_error_result    varchar2 (2000);
      l_err_name        varchar2 (30);
      l_err_message     varchar2 (2000);
      l_err_stack       varchar2 (4000);

begin

-- Grab the details of the message to be enqueued using the
      -- previous interface of WF_MAIL.GetMessage.
      begin

select installed_flag 
         into l_installed_lang
         from wf_languages
         where nls_language = p_nlang
           and nls_territory = p_nterr
           and installed_flag = 'Y';

l_language := ''''||p_nlang||'''';
         l_territory := ''''||p_nterr||'''';
      exception
         when others then
            l_language := 'AMERICAN';
            l_territory := 'AMERICA';
      end;
      dbms_session.set_nls('NLS_LANGUAGE'   , l_language);
      dbms_session.set_nls('NLS_TERRITORY'   , l_territory);

wf_mail.getmessage(p_nid, p_node, p_agent, p_replyto,
                         l_subject, l_text_body, l_html_body, l_body_atth,
                         l_error_result);
      -- Check for any problems
      if l_error_result is not NULL then
         wf_core.token('LANG', l_language);
         wf_core.token('TERR', l_territory);
         wf_core.token('ERRMSG', l_error_result);
         wf_core.raise('WFXMLERR');
      end if;

-- Instantiate a handle to the new document.
      -- dbms_lob.createTemporary(l_doc, TRUE, dbms_lob.session);
      l_docIdx := wf_temp_lob.getLob(g_LOBTable);

-- Initialise the XML Document and then progressively walk
      -- through the elements. Populating them as we go.
      -- l_pos is crucial as it determines where the next nodes
      -- will be placed.
      l_str := '<?xml version="1.0"?>';
      l_pos := length(l_str);

dbms_lob.write(g_LOBTable(l_docIdx).temp_lob, l_pos, 1, l_str);

l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 
                         'NOTIFICATION','',l_attrlist);

AddElementAttribute('language',p_nlang, l_attrlist);
      AddElementAttribute('territory',p_nterr, l_attrlist);
      l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'HEADER', '', 
                         l_attrlist);
      l_attrlist.DELETE;

-- Attach the NID
      l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'NID', 
                         to_char(p_nid),l_attrlist);
      l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'NID', l_pos,                                    l_occurance);
      
      begin
         select priority, access_key, status 
            into l_priority, l_access_key, l_status 
         from wf_notifications_view
         where notification_id = p_nid;
      exception
         when NO_DATA_FOUND then
            wf_core.raise('WFNTFGM_FAILED');
      end;

l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'PRIORITY',
                         to_char(l_priority), l_attrlist);
      l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'PRIORITY', l_pos, 
                          l_occurance);
      l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'ACCESSKEY', 
                         l_access_key, l_attrlist);
      l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'ACCESSKEY', l_pos, 
                          l_occurance);

-- Register the receivers as a single string of email addresses
      l_receiverlist := NULL;
      for i in 1..p_recipient_list.COUNT loop
         l_receiverlist := l_receiverlist || p_recipient_list(i).NAME || ',';
      end loop;

l_receiverlist := substr(l_receiverlist,1,length(l_receiverlist)-1);
      l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'RECEIVERLIST', 
                         l_receiverlist, l_attrlist);
      l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'RECEIVERLIST', 
                          l_pos, l_occurance);

l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'SENDER', '', 
                         l_attrlist);
      l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'SENDER', l_pos, 
                          l_occurance);

l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'STATUS', 
                         l_status, l_attrlist);
      l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'STATUS', l_pos,
                          l_occurance);

l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'SUBJECT', 
                         l_subject, l_attrlist);
      l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'HEADER', l_pos, 
                          l_occurance);

l_text_body := EncodeEntityReference(l_text_body);
      l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'BODYTEXT', 
                         l_text_body, l_attrlist);
      l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'BODYTEXT', l_pos,                               l_occurance);

AddElementAttribute('content-type','text/html', l_attrlist);
      AddElementAttribute('hmldesc','HTML', l_attrlist);
      AddElementAttribute('htmlagent',p_agent, l_attrlist);

l_html_body := EncodeEntityReference(l_html_body);

l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'BODYHTML', 
                         l_html_body, l_attrlist);
      l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'BODYHTML', l_pos, 
                          l_occurance);
      l_attrlist.DELETE;

l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 
                         'ATTACHMENTLIST', '', l_attrlist);
      -- Next will be to attach all URLs and DOCUMENT attributes
      -- as attachments.
      l_pos := GetAttachment(p_nid, g_LOBTable(l_docIdx).temp_lob, p_agent, 
                             NULL, l_doctype, l_pos);
      l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'ATTACHMENTLIST', 
                          l_pos, l_occurance);

-- Check to see if the notification is a reponse and attach
      -- the response attributes.
      l_response := 0;
      begin
        select 1 into l_response
        from dual
        where exists (select NULL
                  from WF_MESSAGE_ATTRIBUTES MA,
                       WF_NOTIFICATIONS N
                  where N.NOTIFICATION_ID = p_nid
                  and   MA.MESSAGE_TYPE = N.MESSAGE_TYPE
                  and   MA.MESSAGE_NAME = N.MESSAGE_NAME
                  and   MA.SUBTYPE = 'RESPOND');

l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 
                           'ATTRIBUTELIST', '', l_attrlist);
        l_pos := GetAttributes(p_nid, g_LOBTable(l_docIdx).temp_lob, p_agent, 
                               l_pos);

exception
        when NO_DATA_FOUND then
           null;
      end;

p_priority := l_priority;
      dbms_lob.copy(p_message, g_LOBTable(l_docIdx).temp_lob, 
                    dbms_lob.getLength(g_LOBTable(l_docIdx).temp_lob), 1, 1);
      -- dbms_lob.freetemporary(l_doc).temp_lob);
      wf_temp_lob.releaseLob(g_LOBTable, l_docIdx);

exception
      when others then
        wf_temp_lob.releaseLob(g_LOBTable, l_docIdx);
        wf_core.context('WF_XML', 'GetXMLMessage', to_char(p_nid), p_protocol, 
                        p_node, p_nlang, p_nterr);
        raise;
   end getXMLMessage;

-- GetShortLOBXMLMessage - 
   -- Return a CLOB Document containing an XML encoded versi on of the
   --                 notification. No recipients list will be populated. That
   --                 will be the responsibility of the calling procedure.
   --
   -- IN
   --     notification id
   --     Protocol for the message
   --     List of recipients to recieve the notification
   --     mailer node name
   --     Web Agent for the HTML attachments
   --     Reply to address for the final notification
   --     Language for the notification
   --     Territory for the notification
   -- OUT
   --     Piority for the message
   --     A CLOB Containing the XML encoded message.
   procedure GetShortLOBXMLMessage (p_nid       in  number,
                            p_priority out NOCOPY number,
                            p_message in out NOCOPY CLOB)
 is
      -- l_doc             CLOB;
      l_docIdx          pls_integer;
      l_pos             integer;
      l_occurance       integer := 1;
      l_priority        wf_notifications.priority%TYPE;
      l_status          wf_notifications.status%TYPE;
      l_recipient       wf_notifications.recipient_role%TYPE;

l_attrlist        wf_xml_attr_table_type;
      l_str             varchar2 (250);

l_error_result    varchar2 (2000);
      l_err_name        varchar2 (30);
      l_err_message     varchar2 (2000);
      l_err_stack       varchar2 (4000);
      l_more_info_role  varchar2(320);

begin

-- Grab the details of the message to be enqueued using the
      -- previous interface of WF_MAIL.GetMessage.

-- Instantiate a handle to the new document.
      -- dbms_lob.createTemporary(l_doc, TRUE, dbms_lob.session);
      l_docIdx := wf_temp_lob.getLOB(g_LOBTable);

dbms_lob.write(g_LOBTable(l_docIdx).temp_lob, l_pos, 1, l_str);

l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 
                         'NOTIFICATION','',l_attrlist);

l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 
                         'HEADER', '', l_attrlist);
      l_attrlist.DELETE;

begin
         select recipient_role, priority, status, more_info_role
            into l_recipient, l_priority, l_status, l_more_info_role
         from wf_notifications
         where notification_id = p_nid;
      exception
         when NO_DATA_FOUND then
            wf_core.raise('WFNTFGM_FAILED');
      end;

l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 
                         'PRIORITY', to_char(l_priority), l_attrlist);
      l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 
                          'PRIORITY', l_pos, l_occurance);
     
      if (l_more_info_role is not null) then
         l_recipient := l_more_info_role;
      end if;
      l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 
                         'RECIPIENT', l_recipient, l_attrlist);
      l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 
                          'RECIPIENT', l_pos, l_occurance);

l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 
                         'STATUS', l_status, l_attrlist);
      l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 
                          'STATUS', l_pos, l_occurance);

p_priority := l_priority;
      dbms_lob.copy(p_message, g_LOBTable(l_docIdx).temp_lob,
                    dbms_lob.getLength(g_LOBTable(l_docIdx).temp_lob), 1, 1);
      -- dbms_lob.freetemporary(g_LOBTable(l_docIdx).temp_lob);
      wf_temp_lob.releaseLob(g_LOBTable, l_docIdx);

exception
      when others then
        wf_temp_lob.releaseLob(g_LOBTable, l_docIdx);
        wf_core.context('WF_XML', 'GetShortLOBXMLMessage', to_char(p_nid));
        raise;
   end getShortLOBXMLMessage;

-- GetShortXMLMessage - 
   -- Return a VARCHAR Document containing an XML encoded versi on of the
   -- notification. No recipients list will be populated. That
   -- will be the responsibility of the calling procedure.
   --
   -- IN
   --     notification id
   --     Protocol for the message
   --     List of recipients to recieve the notification
   --     mailer node name
   --     Web Agent for the HTML attachments
   --     Reply to address for the final notification
   --     Language for the notification
   --     Territory for the notification
   -- OUT
   --     Piority for the message
   --     A VARCHAR Containing the XML encoded message.
   procedure GetShortXMLMessage (p_nid       in  number,
                            p_priority out NOCOPY number,
                            p_message in out NOCOPY VARCHAR2)
 is
      l_pos             integer;
      l_occurance       integer := 1;
      l_priority        wf_notifications.priority%TYPE;
      l_status          wf_notifications.status%TYPE;
      l_recipient       wf_notifications.recipient_role%TYPE;

l_attrlist        wf_xml_attr_table_type;
      l_str             varchar2 (250);

l_error_result    varchar2 (2000);
      l_err_name        varchar2 (30);
      l_err_message     varchar2 (2000);
      l_err_stack       varchar2 (4000);
      l_more_info_role  varchar2(320);

begin

-- Grab the details of the message to be enqueued using the
      -- previous interface of WF_MAIL.GetMessage.

-- Instantiate a handle to the new document.
      p_message := '';

p_message := p_message||l_str;

l_pos := NewTag(p_message, l_pos, 'NOTIFICATION',NULL,l_attrlist);

l_pos := NewTag(p_message, l_pos, 'HEADER', NULL, l_attrlist);
      l_attrlist.DELETE;

-- Attach the NID
      l_pos := NewTag(p_message, l_pos, 'NID', to_char(p_nid),l_attrlist);
      l_pos := SkipTag(p_message, 'NID', l_pos, l_occurance);

l_pos := NewTag(p_message, l_pos, 'PRIORITY', to_char(l_priority), 
                      l_attrlist);
      l_pos := SkipTag(p_message, 'PRIORITY', l_pos, l_occurance);

if (l_more_info_role is not null) then
         l_recipient := l_more_info_role;
      end if; 
      l_pos := NewTag(p_message, l_pos, 'RECIPIENT', l_recipient, l_attrlist);
      l_pos := SkipTag(p_message, 'RECIPIENT', l_pos, l_occurance);

l_pos := NewTag(p_message, l_pos, 'STATUS', l_status, l_attrlist);
      l_pos := SkipTag(p_message, 'STATUS', l_pos, l_occurance);

p_priority := l_priority;

exception
      when others then
        wf_core.context('WF_XML', 'GetShortXMLMessage', to_char(p_nid));
        raise;
   end getShortXMLMessage;

-- EnqueueFullNotification - 
   --     To push a notification to the outbound notification queue.  
   -- IN
   --    Notification ID
   procedure EnqueueFullNotification(p_nid in number) is

l_queue_name varchar2(255);
      
      l_node varchar2(30);
      l_agent varchar(255) := g_webAgent;
      l_replyto varchar2(320);

l_nlang wf_languages.nls_language%TYPE;
      l_nterr wf_languages.nls_territory%TYPE;
      l_priority number;
      -- l_message CLOB;
      l_messageIdx pls_integer;

l_recipient_role varchar2(320);
      l_ntf_pref varchar2(30);
      l_language varchar2(30);
      l_recipient_list WF_DIRECTORY.wf_local_roles_tbl_type;
      l_recipient_lang WF_DIRECTORY.wf_local_roles_tbl_type;
      l_wf_schema varchar2(320) := g_wfSchema;

l_err_name      varchar2(30);
      l_err_message   varchar2(2000);
      l_err_stack     varchar2(4000);

i binary_integer := 0;
      j binary_integer := 0;

begin

-- Obtain the name of the recipient role, but only if a
      -- notification is needed to go out. ie the mail status 
      -- indicates that a notification should be sent.
      begin
         select recipient_role
            into l_recipient_role
         from wf_notifications
         where notification_id = p_nid
           and ((MAIL_STATUS = 'MAIL' and STATUS <> 'CLOSED') 
            or (MAIL_STATUS = 'INVALID'));
      exception
         when NO_DATA_FOUND then
            l_recipient_role := NULL;
         when others then
            raise;
      end;
      if l_recipient_role is not null then
         -- Determine the total list of individual recipients
         -- ordered by Protocol and then Language.
         l_recipient_list.DELETE;
         GetRecipients(l_recipient_role, l_recipient_list);

end if;

if l_recipient_list.COUNT > 0 then
         -- A valid role has been found for a notification
         -- that is required to go out. 
         -- Prepare a LOB to contain the payload message.
         -- dbms_lob.createTemporary(l_message, TRUE, dbms_lob.SESSION);
         l_messageIdx := wf_temp_lob.getLob(g_LOBTable);
  
         wf_queue.set_queue_names; 
         wf_queue.get_hash_queue_name (p_protocol => 'SMTP',
                                       p_inbound_outbound => 'OUTBOUND',
                                       p_queue_name   => l_queue_name);
         l_language := l_recipient_list(1).language;
         l_ntf_pref := l_recipient_list(1).notification_preference;
         l_recipient_lang.DELETE;
         j := 1;
         i := 1;

-- Walk through the recipient list. This will be sorted by Notification
         -- preference and language. We group the recipients this way to help
         -- minimise the number of Queue messages to the number of recipients.
         loop
         exit when  i > l_recipient_list.COUNT;
            if l_ntf_pref <> l_recipient_list(i).notification_preference then
               wf_queue.get_hash_queue_name (p_protocol => 'SMTP',
                                             p_inbound_outbound => 'OUTBOUND',
                                             p_queue_name   => l_queue_name);
   
            end if;
            loop
            exit when (i > l_recipient_list.COUNT)
              or (l_ntf_pref <> l_recipient_list(i).notification_preference);
              j := 1;
              loop
              exit when (i > l_recipient_list.COUNT)
                     or (l_ntf_pref <> l_recipient_list(i).notification_preference) 
                     or (l_language <> l_recipient_list(i).language);
   
                 l_recipient_lang(j) := l_recipient_list(i);
                 i := i + 1;
                 j := j + 1;
              end loop;
              -- Get an encoded payload.
              getXMLMessage(p_nid, l_ntf_pref,
                           l_recipient_lang, l_node, l_agent,
                           l_replyto, 
                           l_recipient_lang(1).language, 
                           l_recipient_lang(1).territory, l_priority, g_LOBTable(l_messageIdx).temp_lob);
   
              -- Push the message to the queue.
              enqueueLOBMessage(p_queue => l_queue_name, 
                             p_priority => l_priority, 
                             p_correlation => wf_queue.account_name ||':'||
                                              to_char(p_nid), 
                             p_message => g_LOBTable(l_messageIdx).temp_lob);
   
              j := 1;
              l_recipient_lang.DELETE;
              if i <= l_recipient_list.COUNT then
                 l_ntf_pref := l_recipient_list(i).notification_preference;
              end if;
           end loop;
        end loop;
      -- dbms_lob.freetemporary(l_message);
      wf_temp_lob.releaseLob(g_LOBTable, l_messageIdx);
      end if;
   
   exception
      when others then
        wf_temp_lob.releaseLob(g_LOBTable, l_messageIdx);
        wf_core.context('WF_XML', 'EnqueueFullNotification', to_char(p_nid));
        raise;
   end EnqueueFullNotification;

-- EnqueueLOBNotification - To push a notification ID to the outbound
   --                       notification queue.
   -- IN
   --    Notification ID
   procedure EnqueueLOBNotification(p_nid in number) is

l_queue_name varchar2(255);
      l_priority number;

l_recipient_role varchar2(320);
      -- l_message CLOB;
      l_messageIdx pls_integer;

l_err_name      varchar2(30);
      l_err_message   varchar2(2000);
      l_err_stack     varchar2(4000);

begin

-- Obtain the name of the recipient role, but only if a
      -- notification is needed to go out. ie the mail status
      -- indicates that a notification should be sent.
      begin
         select recipient_role
            into l_recipient_role
         from wf_notifications
         where notification_id = p_nid
           and MAIL_STATUS in ('MAIL', 'INVALID');
      exception
         when NO_DATA_FOUND then
            l_recipient_role := NULL;
         when others then
            raise;
      end;
      if l_recipient_role is not null then
         -- A valid role has been found for a notification
         -- that is required to go out.

-- dbms_lob.createTemporary(l_message, TRUE, dbms_lob.CALL);
         l_messageIdx := wf_temp_lob.getLob(g_LOBTable);
         -- Get an encoded payload.
         getShortLOBXMLMessage(p_nid, l_priority,  
                               g_LOBTable(l_messageIdx).temp_lob);
 
         wf_queue.set_queue_names; 
         wf_queue.get_hash_queue_name (p_protocol => 'SMTP',
                                       p_inbound_outbound => 'OUTBOUND',
                                       p_queue_name   => l_queue_name);

-- Push the message to the queue.
         enqueueLOBMessage(p_queue => l_queue_name,
                        p_priority => l_priority,
                        p_correlation => wf_queue.account_name ||':'||
                                         to_char(p_nid),
                        p_message => g_LOBTable(l_messageIdx).temp_lob);
      end if;
      wf_temp_lob.releaseLob(g_LOBTable, l_messageIdx);
  
   exception
      when others then
        wf_temp_lob.releaseLob(g_LOBTable, l_messageIdx);
        wf_core.context('WF_XML', 'EnqueueLOBNotification', to_char(p_nid));
        raise;
   end EnqueueLOBNotification;

-- EnqueueNotification - To push a notification ID to the outbound
   --                       notification queue.
   -- IN
   --    Notification ID
   procedure EnqueueNotification(p_nid in number) is

l_queue_name varchar2(255);
      l_priority number;

l_recipient_role varchar2(320);
      l_message VARCHAR2(32000);

l_err_name      varchar2(30);
      l_err_message   varchar2(2000);
      l_err_stack     varchar2(4000);
      l_more_info_role varchar2(320);
   begin

-- Obtain the name of the recipient role, but only if a
      -- notification is needed to go out. ie the mail status
      -- indicates that a notification should be sent.
      begin
         select recipient_role, more_info_role
            into l_recipient_role, l_more_info_role
         from wf_notifications
         where notification_id = p_nid
           and MAIL_STATUS in ('MAIL', 'INVALID')
           and STATUS <> 'CLOSED';
      exception
         when NO_DATA_FOUND then
            l_recipient_role := NULL;
            l_more_info_role := NULL;
         when others then
            raise;
      end;
      if (l_recipient_role is not null or l_more_info_role is not null) then
         -- A valid role has been found for a notification
         -- that is required to go out.

-- Get an encoded payload.
         getShortXMLMessage(p_nid, l_priority, l_message);
 
         wf_queue.set_queue_names; 
         wf_queue.get_hash_queue_name (p_protocol => 'SMTP',
                                       p_inbound_outbound => 'OUTBOUND',
                                       p_queue_name   => l_queue_name);

-- Push the message to the queue.
         enqueueMessage(p_queue => l_queue_name,
                        p_priority => l_priority,
                        p_correlation => wf_queue.account_name ||':'||
                                         to_char(p_nid),
                        p_message => l_message);
      end if;
  
   exception
      when others then
        wf_core.context('WF_XML', 'EnqueueNotification', to_char(p_nid));
        raise;
   end EnqueueNotification;

-- DequeueMessage - Remove a notification from the queue
   -- IN
   --    Queue name to operate on
   --    Correlation for the message - NID in this implementation 
   -- OUT
   --    The message that is obtained from the queue.
   --    Timeout to signal whether the queue is empty.
   procedure DequeueMessage(p_queue_name in varchar2,
                            p_correlation in varchar2,
                            p_message   in out NOCOPY CLOB,
                            p_timeout out NOCOPY boolean)
   as

l_dequeue_options dbms_aq.dequeue_options_t;
      l_message_properties dbms_aq.message_properties_t;
      l_correlation varchar2(255) := NULL;
      l_message_handle RAW(16);

begin

/** wf_message_payload_t is obsolete in 2.6.4 onwards **/
      null;

exception
      when dequeue_timeout then
         p_timeout := TRUE;
      when others then
         Wf_Core.Context('WF_XML', 'DequeueMessage', p_queue_name, 
                         p_correlation);
         p_timeout := FALSE;
         raise;
   end DequeueMessage;

-- GetMessage - Get email message data
   -- IN
   --    Queue number to operate on
   -- OUT
   --    Notification ID
   --    Comma seperated list of the recipients of the notification
   --    Status of the notification - For the purpose of message templating
   --    Timout. Returns TRUE where the queue is empty.
   --    Error message
   procedure GetMessage(
       p_queue    in  number,
       p_nid          out NOCOPY number,
       p_receiverlist out NOCOPY varchar2,
       p_status      out NOCOPY varchar2,
       p_timeout      out NOCOPY integer,
       p_error_result in out NOCOPY varchar2)
   is

l_nid number;
      l_queue_name   varchar2(255);
      l_status       varchar2(8);
      l_receiverlist varchar2(4000);
      
      l_message CLOB;
      l_timeout BOOLEAN;   
      l_pos integer;
      l_attrlist wf_xml_attr_table_type;

l_err_name      varchar2(30);
      l_err_message   varchar2(2000);
      l_err_stack     varchar2(4000);
      no_program_unit exception;
      pragma exception_init(no_program_unit, -6508);

begin

l_queue_name := g_wfSchema||'.WF_SMTP_O_'||
                      to_char(p_queue)||'_QUEUE';

-- Grab the next available message from the queue.
      DequeueMessage(p_queue_name => l_queue_name, 
                     p_correlation => NULL, 
                     p_message => l_message, 
                     p_timeout => l_timeout);

-- Grab the components of the notification to send
         -- back to the caller

l_pos := 1;

GetTagValue(l_message, 'NID', l_nid, l_pos, l_attrlist);
         GetTagValue(l_message, 'RECEIVERLIST', l_receiverlist, l_pos, 
                     l_attrlist);
         GetTagValue(l_message, 'STATUS', l_status, l_pos, l_attrlist);

end if;

p_nid := to_number(l_nid);
      p_receiverlist := l_receiverlist;
      p_status := l_status;

if l_timeout then
         p_timeout := 1;
      else 
         p_timeout := 0;
      end if;

exception
     when no_program_unit then
       wf_core.context('WF_XML', 'GetMessage', to_char(p_queue));
       raise;

when others then
       -- First look for a wf_core error.
       wf_core.get_error(l_err_name, l_err_message, l_err_stack);

-- If no wf_core error look for a sql error.
       if (l_err_name is null) then
           l_err_message := sqlerrm;
       end if;

p_error_result := l_err_message;
       wf_core.context('WF_XML', 'GetMessage', to_char(p_queue));
       raise;

end GetMessage;

-- GetShortMessage - Get email message data
   -- IN
   --    Queue number to operate on
   -- OUT
   --    Notification ID
   --    Comma seperated list of the recipients of the notification
   --    Status of the notification - For the purpose of message templating
   --    Timout. Returns TRUE where the queue is empty.
   --    Error message
   procedure GetShortMessage(
       p_queue    in  number,
       p_nid          out NOCOPY number,
       p_recipient out NOCOPY varchar2,
       p_status      out NOCOPY varchar2,
       p_timeout      out NOCOPY integer,
       p_error_result in out NOCOPY varchar2)
   is

l_queue_name varchar2(200);

l_err_name      varchar2(30);
      l_err_message   varchar2(2000);
      l_err_stack     varchar2(4000);
      no_program_unit exception;
      pragma exception_init(no_program_unit, -6508);

begin

l_queue_name := g_wfSchema||'.WF_SMTP_O_'||
                      to_char(p_queue)||'_QUEUE';

GetQueueMessage(l_queue_name, p_nid, p_recipient, p_status,
                      p_timeout, p_error_result);
   exception
     when no_program_unit then
       wf_core.context('WF_XML', 'GetShortMessage', to_char(p_queue));
       raise;

when others then
       -- First look for a wf_core error.
       wf_core.get_error(l_err_name, l_err_message, l_err_stack);

-- If no wf_core error look for a sql error.
       if (l_err_name is null) then
           l_err_message := sqlerrm;
       end if;

p_error_result := l_err_message;
       wf_core.context('WF_XML', 'GetShortMessage', to_char(p_queue));
       raise;

end GetShortMessage;

-- GetExceptionMessage - Get email message data
   -- IN
   --    Queue number to operate on
   -- OUT
   --    Notification ID
   --    Comma seperated list of the recipients of the notification
   --    Status of the notification - For the purpose of message templating
   --    Timout. Returns TRUE where the queue is empty.
   --    Error message
   procedure GetExceptionMessage(
       p_queue    in  number,
       p_nid          out NOCOPY number,
       p_recipient out NOCOPY varchar2,
       p_status      out NOCOPY varchar2,
       p_timeout      out NOCOPY boolean,
       p_error_result in out NOCOPY varchar2)
   is
      l_timeout integer;
      l_queue_name varchar2(200);

l_err_name      varchar2(30);
      l_err_message   varchar2(2000);
      l_err_stack     varchar2(4000);
      no_program_unit exception;
      pragma exception_init(no_program_unit, -6508);

begin

l_queue_name := wf_queue.enable_exception_queue(
                         g_wfSchema||
                         '.WF_SMTP_O_'||to_char(p_queue)||'_QUEUE');
      if l_queue_name is not NULL then 
         GetQueueMessage(l_queue_name, p_nid, p_recipient, p_status,
                         l_timeout, p_error_result);
         -- GetQueueMessage returns timeout as an integer for the benefit
         if l_timeout = 0 then
            p_timeout := FALSE;
         else
            p_timeout := TRUE;
         end if;
      else
         p_timeout := TRUE;
      end if;
      
   exception
     when no_program_unit then
       wf_core.context('WF_XML', 'GetExceptionMessage', to_char(p_queue));
       raise;

when others then
       -- First look for a wf_core error.
       wf_core.get_error(l_err_name, l_err_message, l_err_stack);

-- If no wf_core error look for a sql error.
       if (l_err_name is null) then
           l_err_message := sqlerrm;
       end if;

p_error_result := l_err_message;
       wf_core.context('WF_XML', 'GetExceptionMessage', to_char(p_queue));
       raise;

end GetExceptionMessage;

-- GetQueueMessage - Get email message data
   -- IN
   --    Queue name
   -- OUT
   --    Notification ID
   --    Comma seperated list of the recipients of the notification
   --    Status of the notification - For the purpose of message templating
   --    Timout. Returns TRUE where the queue is empty.
   --    Error message
   procedure GetQueueMessage(
       p_queuename    in  varchar2,
       p_nid          out NOCOPY number,
       p_recipient    out NOCOPY varchar2,
       p_status       out NOCOPY varchar2,
       p_timeout      out NOCOPY integer,
       p_error_result in out NOCOPY varchar2)
   is

l_message CLOB;
      l_nid number;
      l_queue_name   varchar2(255);
      l_status       varchar2(8) := NULL;
      l_currstatus   varchar2(8);
      l_recipient WF_NOTIFICATIONS.RECIPIENT_ROLE%TYPE := NULL;
      l_attrlist wf_xml_attr_table_type;
      l_timeout BOOLEAN;
      l_pos integer;
      l_statusOK boolean;

l_err_name      varchar2(30);
      l_err_message   varchar2(2000);
      l_err_stack     varchar2(4000);
      no_program_unit exception;
      pragma exception_init(no_program_unit, -6508);

begin

l_queue_name := p_queuename;

-- Grab the next available message from the queue.
      -- The lob located will contain a reference to a persistent
      -- LOB so there is not need to pre-create a temporary LOB.
      loop
         DequeueMessage(p_queue_name => l_queue_name,
                        p_correlation => NULL,
                        p_message => l_message,
                        p_timeout => l_timeout);

-- If the result is from the queue being empty then we want to
         -- inform the caller to maybe go and do something else a while.
         if NOT l_timeout then
            -- We now have a message as a CLOB. we need to parse it
            -- to reconstruct the DOM Document.
            -- new parser
   
            -- Grab the components of the notification to send
            -- back to the caller
   
            l_pos := 1;
   
            GetTagValue(l_message, 'NID', l_nid, l_pos, l_attrlist);
            GetTagValue(l_message, 'RECIPIENT', l_recipient, l_pos, l_attrlist);
            GetTagValue(l_message, 'STATUS', l_status, l_pos, l_attrlist);

-- Verify that the status of the notification is
            -- still OK.
            begin
               select status into l_currstatus
               from wf_notifications
               where notification_id = l_nid
                  and status in ('OPEN','CANCELED', 'CLOSED')
                  and mail_status in ('MAIL','INVALID','FAILED');
               l_statusOK := TRUE;
            exception
               when no_data_found then
                  l_statusOK := FALSE;
               when others then raise;
            end;
         end if;
         exit when l_timeout or l_statusOK;
      end loop;
      p_nid := to_number(l_nid);
      p_recipient := l_recipient;
      p_status := l_status;

if l_timeout then
         p_timeout := 1;
      else
         p_timeout := 0;
      end if;

exception
     when no_program_unit then
       wf_core.context('WF_XML', 'GetQueueMessage', p_queuename);
       raise;

when others then
       -- First look for a wf_core error.
       wf_core.get_error(l_err_name, l_err_message, l_err_stack);

-- If no wf_core error look for a sql error.
       if (l_err_name is null) then
           l_err_message := sqlerrm;
       end if;

p_error_result := l_err_message;
       wf_core.context('WF_XML', 'GetQueueMessage', p_queuename);
       raise;

end GetQueueMessage;

-- RemoveMessage
   --    To remove any messages associated with a particular Notification ID.
   --    Note that a notification can contain more than one message. Each of 
   --    these messages will have a unique message handle and could be 
   --    enqueued on more than one queue.
   -- IN
   --    Queue Name to remove the messages from
   --    Correlation or the Notification ID of the messages to remove.
   -- OUT
   --    Timeout. Returns TRUE if there was nothing on the Queue
   procedure RemoveMessage(p_queue_name in varchar2, 
                           p_correlation in varchar2, 
                           p_timeout out NOCOPY boolean)
   is
      l_dequeue_options dbms_aq.dequeue_options_t;
      l_message_properties dbms_aq.message_properties_t;
      l_correlation varchar2(255) := NULL;
      l_message_handle RAW(16);

begin

/** wf_message_payload_t is obsolete in 2.6.4 onwards **/
      null;

exception
      when others then
       Wf_Core.Context('WF_XML', 'RemoveMessage', p_queue_name, p_correlation);
       p_timeout := FALSE;
       raise;
   end RemoveMessage;

-- RemoveNotification
   --     To remove all enqueues messages for a given notification.
   -- IN
   --    Notification ID of the message to locate and remove.
   -- NOTE
   --    This is a destructive procedure that's sole purpose is to purge the
   --    message from the queue. We only call this when we do not care for the 
   --    content.
   procedure RemoveNotification (p_nid in number)
   is
      l_protocol varchar2(10);
      l_iobound varchar2(10);
      l_queue_count integer;  
      l_queue_name varchar2(100);
      l_wf_schema varchar2(320);
      l_timeout boolean;
   begin

select protocol, inbound_outbound, queue_count
         into l_protocol, l_iobound, l_queue_count
      from wf_queues
         where protocol = 'SMTP'
           and INBOUND_OUTBOUND = 'OUTBOUND'
           and DISABLE_FLAG = 'N';

-- Walk though ALL queue names to hunt for the notifications
      wf_queue.set_queue_names;
      l_wf_schema := g_wfSchema;
      for i in 1..l_queue_count loop
         -- Build the queue name from the information we know.
         l_queue_name := l_wf_schema||'.WF_'||l_protocol||'_'||
                         substr(l_iobound,1,1)||'_'||to_char(i)||'_QUEUE';
         l_timeout := FALSE;
         while not l_timeout loop
            -- Kill all traces of the message.
            RemoveMessage(l_queue_name, wf_queue.account_name||':'||
                          to_char(p_nid), l_timeout);
         end loop;
      end loop;
      -- Remove the messages from the default Exception queues also
      for i in 1..l_queue_count loop
         -- Build the queue name from the information we know.
         l_queue_name := wf_queue.enable_exception_queue('WF_'||l_protocol||
                         '_'||substr(l_iobound,1,1)||'_'||to_char(i)||'_QUEUE');
         if l_queue_name is not NULL then
            l_timeout := FALSE;
            while not l_timeout loop
               -- Kill all traces of the message.
               RemoveMessage(l_queue_name,  wf_queue.account_name||':'||
                             to_char(p_nid), l_timeout);
            end loop;
         end if;
      end loop;

exception
      when others then
         wf_core.context('WF_XML','RemoveNotification',to_char(p_nid));
         raise;
   end RemoveNotification;

-- setFistMessage
   --    To set the global variable g_first_message for the deqeuing
   --    of the SMTP queue.
   -- IN
   --    'Y' to set the flag to TRUE
   procedure setFirstMessage(p_first_message IN varchar2)
   is
   begin
      if (upper(substrb(p_first_message, 1, 1)) in ('Y','T')) then
         g_first_message := TRUE;
      else
         g_first_message := FALSE;
      end if;
   end;

--
   -- getBodyPart
   -- 
   procedure getBodyPart(p_doc in out NOCOPY CLOB,
                         p_nid in number,
                         p_doctype in varchar2)
   is
      l_message varchar2(32000);
      l_doc_length number := 0;
      l_doc_end integer := 0;
      l_start VARCHAR2(10) := '<![CDATA[';
      l_end VARCHAR2(4) := ']]>';
   begin

WF_MAIL.InitFetchLOB(p_doctype, l_doc_length);
      l_doc_end := 0;
      dbms_lob.writeAppend(p_doc, length(l_start), l_start);
      while l_doc_end = 0 loop
         WF_MAIL.FetchLobContent(l_message, p_doctype, l_doc_end);
         l_message := replace(l_message, g_fndapi_misschr);
         dbms_lob.writeAppend(p_doc, length(l_message), l_message);
      end loop;
      dbms_lob.writeAppend(p_doc, length(l_end), l_end);
      WF_MAIL.CloseLob(p_doctype);

exception
      when others then
         wf_core.context('WF_XML','getBodyPart',to_char(p_nid),p_doctype);
         raise;
   end getBodyPart;

procedure GetNLS(base_lang out NOCOPY varchar2, base_territory OUT NOCOPY VARCHAR2, 
                    base_codeset OUT NOCOPY VARCHAR2)
   is
      nls_base varchar2(100);
      underscore_pos integer;
      dot_pos integer;
      
   begin
      select userenv('LANGUAGE')
      into nls_base
      from sys.dual;
      
      underscore_pos := instr(nls_base, '_');
      dot_pos := instr(nls_base, '.');
      
      base_lang := substr(nls_base, 1, underscore_pos -1);
      base_territory := substr(nls_base, underscore_pos +1, 
                               (dot_pos - underscore_pos)-1);
      base_codeset := substr(nls_base, dot_pos + 1);
      
      
   exception
      when others then
         wf_core.context('WF_XML','GetNLS');
         raise;
   end getNLS;
  
 
   -- SetNLS
   -- To set the NLS lang and territory of the current session
   -- IN
   -- language - a varchar2 of the language code
   -- territory - a varchar2 of the territory code.
   procedure SetNLS(language in VARCHAR2, territory in VARCHAR2)
   is
      l_language varchar2(30);
      l_territory varchar2(30);
      l_installed_flag varchar2(1);
   begin
      begin
         -- If nothing is passed in, force it to AMERICAN_AMERICA
         if language is null or language = '' then
            l_language := 'AMERICAN';
         end if;
         if territory is null or territory = '' then
            l_territory := 'AMERICA';
         end if;

select installed_flag
         into l_installed_flag
         from wf_languages
         where nls_language = language
           and installed_flag = 'Y';

l_language := ''''||language||'''';
         l_territory := ''''||territory||'''';
      exception
         when others then
            l_language := 'AMERICAN';
            l_territory := 'AMERICA';
      end;

dbms_session.set_nls('NLS_LANGUAGE'   , l_language);
      dbms_session.set_nls('NLS_TERRITORY'   , l_territory);
   exception
      when others then
         wf_core.context('WF_XML','SetNLS', language, territory);
         raise;
   end setNLS;
   
   -- Parse the p_doc for the URL attributes and edit
   -- their src= reference in preperation for email.
   function ParseForInlineImages(msgBody IN OUT NOCOPY CLOB, 
                                nid IN number) return boolean
   is

imgStrPos number;
      value varchar2(32000);
      newValue varchar2(32000);
      contentId varchar2(1000);

params pls_integer;
      tmpIdx pls_integer;
      nameStart pls_integer;
      nameEnd pls_integer;
      amount number;
      tmpLength number;

imagesFound boolean := false;

begin
      imagesFound := false;

tmpIdx := wf_temp_lob.getLob(g_LOBTable);
      for url in g_urls(nid) loop
         value := url.text_value;
         value := wf_notification.SetFrameworkAgent(value);
         params := instr(value, '?');
         if isImageReference(value) then
            value := '<IMG SRC="'||value||
                     '" alt="'|| url.display_name||'"></IMG>'; 
            imgStrPos := dbms_lob.instr(msgBody, value, 1, 1);
            if imgStrPos > 0 then
               dbms_lob.trim(g_LOBTable(tmpIdx).temp_lob, 0);
               dbms_lob.copy(g_LOBTable(tmpIdx).temp_lob , msgBody, 
                             imgStrPos-1, 1, 1);

nameStart := instrb(url.text_value, '/', -1, 1);
               if nameStart = 0 then
                  contentId := value;
                  contentId := replace(contentId, '.');
               else
                  contentId := substrb(url.text_value, nameStart+1 );
                  contentId := replace(contentId, '.');
               end if;

newValue := '<IMG SRC="cid:'||contentId||
                     '" alt="'|| url.display_name||
                     '"></IMG>';

dbms_lob.writeAppend(g_LOBTable(tmpIdx).temp_lob, 
                                    length(newValue), newValue);
               amount :=  dbms_lob.getlength(msgBody)-imgStrPos+length(value);
               tmpLength :=  dbms_lob.getlength(g_LOBTable(tmpIdx).temp_lob);
               dbms_lob.Copy(g_LOBTable(tmpIdx).temp_lob, msgBody, amount, 
                             tmpLength+1, imgStrPos+length(value));
               dbms_lob.trim(msgBody, 0);
               dbms_lob.append(msgBody, g_LOBTable(tmpIdx).temp_lob);

imagesFound := TRUE;                             
            end if; -- Image TAG is in the messages body
         end if;    -- The URL is an image reference
      end loop; 
      wf_temp_lob.releaseLob(g_LOBTable, tmpIdx);

return imagesFound; 
   exception
      when others then
         wf_core.context('WF_XML','ParseForInlineImages',to_char(nid));
         raise;
   end ParseForInlineImages;

-- Adds the RESOURCE tags for the inline images.
  procedure addInlineImages(doc IN OUT NOCOPY CLOB, 
                            pos IN OUT NOCOPY number,
                            attrlist IN OUT NOCOPY wf_xml_attr_table_type,
                            nid IN number,
                            disposition in varchar2)
   is
      extPos pls_integer;
      contentType varchar2(1000);
      value varchar2(32000);
      encodedURL varchar2(32000);
      nameStart pls_integer;
      contentId varchar2(1000);
      fileName varchar2(1000);
      occurance number := 1;

begin
      attrlist.DELETE;

for url in g_urls(nid) loop
         value := url.text_value;
         value := wf_notification.SetFrameworkAgent(value);
         if isImageReference(value) then
            nameStart := instrb(value, '/', -1, 1);
            if nameStart = 0 then
               contentId := value;
               filename := contentId;
               contentId := replace(contentId, '.');
            else
               contentId := substrb(url.text_value, nameStart+1 );
               fileName := contentId;
               contentId := replace(contentId, '.');
            end if;
            extPos := instrb(value, '.', -1, 1);
            if (extPos > 0) then
              contentType := 'image/'||substr(value, extPos+1);
            else
               contentType := 'image/*';
            end if;

encodedURL := wf_mail.urlEncode(value);
            AddElementAttribute('content-type', contentType, attrlist);
            AddElementAttribute('src', value, attrlist);
            AddElementAttribute('content-id', contentId, attrlist);
            AddElementAttribute('filename', fileName, attrlist);
            AddElementAttribute('content-disposition', 'inline', attrlist);

pos := NewLOBTag(doc, pos, 'RESOURCE','',attrlist);
            pos := SkipLOBTag(doc, 'NAME', pos, occurance);
            attrlist.DELETE;
                                
         end if;       -- Is this an image reference?
      end loop;

exception
      when others then
         wf_core.context('WF_XML','addInlineImages', to_char(nid));
         raise;
   end addInlineImages;

-- GenerateDoc
   -- To generate the XML content for the enqueued notifications.
   procedure GenerateDoc (p_doc in out NOCOPY CLOB,
                          p_pos in out NOCOPY number,
                          p_recipient_role in varchar2,
                          p_event_name in varchar2,
                          p_event_key in varchar2,
                          p_parameter_list in wf_parameter_list_t) 
   is
      -- message CLOB;
      messageIdx pls_integer;
      nid NUMBER;
      msgbody VARCHAR2(32000);
      end_of_msgbody BOOLEAN;
      doctype VARCHAR2(100);
      
      pos integer;
      amt number;
      installed VARCHAR2(1);
      base_lang VARCHAR2(100);
      base_territory VARCHAR2(100);
      base_codeset VARCHAR2(100);
      language VARCHAR2(100);
      territory VARCHAR2(100);
      codeset VARCHAR2(100);
      orig_system VARCHAR2(100);
      orig_system_id number;

nidStr varchar2(100);
      hdrxml varchar2(32000);
      hdrxmlPos integer;

access_key VARCHAR2(100);
      priority NUMBER;
      recipient_role VARCHAR2(100);
      status VARCHAR2(100);
      subject VARCHAR2(4000);

role VARCHAR2(100);
      display_name VARCHAR2(200);
      email VARCHAR2(1000);
      notification_pref VARCHAR2(100);

occurance       integer := 1;
      attrlist        wf_xml_attr_table_type;
      str             varchar2 (250);
      nodeName        varchar2(100) := '&#NODE';
      agent           varchar2(100) := '&#AGENT';
      replyto         varchar2(100) := '&#REPLYTO';
      fromName        varchar2(100) := '&#FROM';
      disposition     varchar2(100) :=  '&amp;#DISPOSITION';
      directResponse  varchar2(1);
      emailParser     varchar2(100);
      corrId          varchar2(128);
      htmlfilename    varchar2(100);
      urlfilename     varchar2(100);
      inlineAtt       varchar2(10);
      sendAccessKey   varchar2(10);
      stylesheetEnabled varchar2(10); 
      body_atth       varchar2 (32000);
      messageType     varchar2(8);
      messageName     varchar2(30);
      error_result    varchar2 (2000);
      err_name        varchar2 (30);
      err_message     varchar2 (2000);
      err_stack       varchar2 (4000);
      moreInfoRole    varchar2(320);

frameworkContent boolean;
      attachInlineImages varchar2(1);

ntfURL          varchar2(2000);
      imgFound        boolean;

bodyToken       varchar2(1);
   begin
      if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
         wf_log_pkg.string(WF_LOG_PKG.level_procedure, 
                          'wf.plsql.WF_XML.generateDoc', 'BEGIN');
      end if;
      -- dbms_lob.createTemporary(message, TRUE, dbms_lob.CALL);
      messageIdx := wf_temp_lob.getLob(g_LOBTable);
      nid := to_number(p_event_key);

-- Obtain notification information
      begin -- 3741599 no_data_found should exit gracefully.
         select ACCESS_KEY, PRIORITY, STATUS, MESSAGE_TYPE, 
                MESSAGE_NAME, MORE_INFO_ROLE
         into access_key, priority, status, messageType, 
              messageName, moreInfoRole
         from WF_NOTIFICATIONS
         where  NOTIFICATION_ID = nid;
      exception
         when no_data_found then
            -- 4104735 Empty documents now provide a document type and 
            -- if a empty document, must provide a reason.
            AddElementAttribute('nid', nid, attrlist);
            AddElementAttribute('language', 'AMERICAN', attrlist);
            AddElementAttribute('territory', 'AMERICA', attrlist);
            AddElementAttribute('codeset', 'UTF8', attrlist);
            AddElementAttribute('full-document', 'N', attrlist);
            AddElementAttribute('reason', 'no_data_found', attrlist);
            p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATION', '', attrlist);
            attrlist.DELETE;
            p_pos := SkipLOBTag(p_doc, 'NOTIFICATION', p_pos, occurance);
            return;
         when others then
            wf_core.context('WF_XML','GenerateDoc',to_char(nid));
            raise;
      end;

if (wf_mail.test_flag = TRUE) then
          if (status not in ('OPEN','CANCELED','CLOSED')) then
             status := 'OPEN';
          end if;
       end if;
 
      if (moreInfoRole is not null) then
         recipient_role := moreInfoRole;
      else
         recipient_role := p_recipient_role;
      end if;

if notification_pref in ('QUERY', 'SUMMARY','SUMHTML', 'DISABLED') 
         or email is null then
         -- 4104735 Empty documents now provide a document type and 
         -- if a empty document, must provide a reason.
         AddElementAttribute('nid', nid, attrlist);
         AddElementAttribute('language', 'AMERICAN', attrlist);
         AddElementAttribute('territory', 'AMERICA', attrlist);
         AddElementAttribute('codeset', 'UTF8', attrlist);
         AddElementAttribute('full-document', 'N', attrlist);
         AddElementAttribute('reason', 'NOTIFICATION_PREFERENCE:'||
                                       notification_pref, attrlist);
         p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATION', '', attrlist);
         attrlist.DELETE;
         p_pos := SkipLOBTag(p_doc, 'NOTIFICATION', p_pos, occurance);

return;
      end if;

corrId := wf_event.getValueForParameter('Q_CORRELATION_ID',
                                              p_parameter_list);
      -- Obtain the values for the configurable items
      nodename := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
                                                       pCorrId => corrId,
                                                       pName => 'NODENAME');
      agent := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
                                                       pCorrId => corrId,
                                                    pName => 'HTMLAGENT');
      fromName := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
                                                       pCorrId => corrId,
                                                       pName => 'FROM');
      fromName := '<![CDATA['||fromName||']]>'; 
      replyto := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
                                                       pCorrId => corrId,
                                                      pName => 'REPLYTO');
      inlineAtt := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
                                               pCorrId => corrId,
                                               pName => 'INLINE_ATTACHMENT');
      sendAccessKey := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
                                                pCorrId => corrId,
                                                pName => 'SEND_ACCESS_KEY');

stylesheetEnabled := wf_mailer_parameter.getValueForCorr(pNid => nid,
                                                pCorrId => corrId,
                                                pName => 'ENABLE_STYLESHEET');

-- Direct Response is not reconfigurable through the message attributes
      -- Use the standard API to obtain its value.
      directResponse := WF_MAILER_PARAMETER.getValueForCorr(pCorrId => corrId,
                                                pName => 'DIRECT_RESPONSE');

attachInlineImages := WF_MAILER_PARAMETER.GetValueForCorr (
                                                pCorrId => corrId,
                                                pName => 'ATTACH_IMAGES');
      if inlineAtt = 'Y' then
         disposition := 'inline';
      else
         disposition := 'attachment';
      end if;

if sendAccessKey = 'Y' then
         wf_mail.Send_Access_Key_On;
      else
         wf_mail.Send_Access_Key_Off;
      end if;

if directResponse = 'Y' then
         wf_mail.direct_response_on;
      else
         wf_mail.direct_response_off;
      end if;

if (WF_NOTIFICATION.isFwkRegion(nid)='Y' and g_install ='EMBEDDED') then
         frameworkContent := TRUE;
          -- 3803327 Text email with framework is currently disabled
          -- until the issues with the region facet for text can be
          -- fixed.
          -- if (notification_pref = 'MAILTEXT') then
          --    notification_pref := 'MAILHTM2';
          -- elsif (notification_pref = 'MAILATTH') then
          --    notification_pref := 'MAILHTML';
          -- end if;
          -- 3803327 end.
      else
         frameworkContent := FALSE;
      end if;
      
      WF_MAIL.setContext(nid);

-- Set the preferred document type based on the 
      -- notification preference.
      if notification_pref = 'MAILTEXT' then
         doctype := g_ntfDocText;
      elsif notification_pref in ('MAILHTML','MAILATTH','MAILHTM2') then
         doctype := g_ntfDocHtml;
      end if;

-- Get the Base NLS. 
      WF_MAIL.GetSessionLanguage(base_lang, base_territory, base_codeset);

-- Get the language, territory and codeset for this notification
      WF_MAIL.Get_Ntf_Language(nid, language, territory, codeset);

-- Set NLS language and territory for this notification
      setNLS(language, territory);
                                          
      -- Initialise the XML Document and then progressively walk
      -- through the elements. Populating them as we go.
      -- l_pos is crucial as it determines where the next nodes
      -- will be placed.
      pos := p_pos;

if frameworkContent = TRUE then
         -- Get a modified version of the message. All is rendered
         -- except for the &BODY token.
         WF_MAIL.GetLobMessage3(nid, nodeName, agent, replyto, 
                               recipient_role, language, territory,
                               notification_pref, email,
                               display_name,
                               'N',
                               subject, body_atth, 
                               error_result, bodyToken);
 
      else
         WF_MAIL.GetLobMessage3(nid, nodeName, agent, replyto, 
                               recipient_role, language, territory,
                               notification_pref, email,
                               display_name,
                               'Y',
                               subject, body_atth, 
                               error_result, bodyToken);
      
      end if;

if error_result is not null or error_result <> '' then
         wf_core.token('ERROR',error_result);
         wf_core.raise('WFMLR_GENERATE_FAILED');
      end if;

g_htmlmessage := wf_core.translate('WF_HTML_MESSAGE');
      g_urlNotification := wf_core.translate('WF_URL_NOTIFICATION');
      g_urlListAttachment := wf_core.translate('WF_URLLIST_ATTACHMENT');
      
      SetNLS(base_lang, base_territory);
      
      htmlfilename := g_htmlmessage||'.html';
      urlfilename  := g_urlNotification||'.html';

nidStr := 'NID['||to_char(nid)||'/'||access_key||'@'||nodeName||']';
      if directResponse = 'Y' then
         nidStr := nidStr||'[2]';
      end if;

AddElementAttribute('nid', p_event_key, attrlist);
      AddElementAttribute('nidstr', nidStr, attrlist);
      AddElementAttribute('language', language, attrlist);
      AddElementAttribute('territory', territory, attrlist);
      AddElementAttribute('codeset', codeset, attrlist);
      AddElementAttribute('priority', to_char(priority), attrlist);
      AddElementAttribute('item_type', messageType, attrlist);
      AddElementAttribute('message_name', messageName, attrlist);
      AddElementAttribute('full-document', 'Y', attrlist);
      pos := NewLOBTag(p_doc, pos, 'NOTIFICATION', '', attrlist);
      attrlist.DELETE;

hdrxmlPos := 0;
      hdrxml := '';

hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'RECIPIENTLIST', '', attrlist);

recipient_role := EncodeEntityReference(recipient_role);
      AddElementAttribute('name',recipient_role, attrlist);
      AddElementAttribute('type','to', attrlist);
      hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'RECIPIENT', '', attrlist);
      attrlist.DELETE;

display_name := replace(display_name, g_newLine);
      display_name := '<![CDATA['||display_name||']]>';
      hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'NAME', display_name, attrlist);
      hdrxmlPos := SkipTag(hdrxml, 'NAME', hdrxmlPos, occurance);

email := replace(email, g_newLine);
      email := '<![CDATA['||email||']]>'; 
      hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'ADDRESS', email, attrlist);
      hdrxmlPos := SkipTag(hdrxml, 'ADDRESS', hdrxmlPos, occurance);
      
      hdrxmlPos := SkipTag(hdrxml, 'RECIPIENT', hdrxmlPos, occurance);
      hdrxmlPos := SkipTag(hdrxml, 'RECIPIENTLIST', hdrxmlPos, occurance);

hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'FROM', '', attrlist);

fromName := replace(fromName, g_newLine);
      hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'NAME', fromName, attrlist);
      hdrxmlPos := SkipTag(hdrxml, 'NAME', hdrxmlPos, occurance);

replyto := replace(replyto, g_newLine);
      replyto := '<![CDATA['||replyto||']]>'; 
      hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'ADDRESS', replyto, attrlist);
      hdrxmlPos := SkipTag(hdrxml, 'ADDRESS', hdrxmlPos, occurance);
      
      hdrxmlPos := SkipTag(hdrxml, 'FROM', hdrxmlPos, occurance);

hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'STATUS', status, attrlist);
      hdrxmlPos := SkipTag(hdrxml, 'STATUS', hdrxmlPos, occurance);

subject := replace(subject, g_newLine);
      subject := '<![CDATA['||subject||']]>'; 
      hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'SUBJECT', subject, attrlist);

pos := NewLOBTag(p_doc, pos, 'HEADER', hdrxml, attrlist);
      pos := SkipLOBTag(p_doc, 'HEADER', pos, occurance);

/*
      ** Potentially, it could be possible for a text/plain notification
      ** but that would mean looking ahead to ensure that there are
      ** absolutely no attachments of any description.
      */
      /*
      ** The body part section of the XML structure will contain
      ** a <![CDATA ]]> construct which removes the need to URL 
      ** encode the data.
      */      
      if notification_pref in ('MAILTEXT','MAILATTH') then
         /*
         ** MAILTEXT and MAILATTH will have the text/plain as the
         ** primary messasge content. The others will be in the 
         ** from of an attachment.
         */
         dbms_lob.trim(g_LOBTable(messageIdx).temp_lob, 0);
         getBodyPart(g_LOBTable(messageIdx).temp_lob, nid, 
                     g_ntfDocText);
     
         AddElementAttribute('content-type', 'multipart/mixed', attrlist);

pos := NewLOBTag(p_doc, pos, 'CONTENT', '', attrlist);
         attrlist.DELETE;

AddElementAttribute('content-type', g_ntfDocText, 
                             attrlist);
         pos := NewLOBTag(p_doc, pos, 'BODYPART', '', attrlist);

pos := NewLOBTag(p_doc, pos, 'MESSAGE', 
                          g_LOBTable(messageIdx).temp_lob, attrlist);
         pos := SkipLOBTag(p_doc, 'MESSAGE', pos, occurance);
         if frameworkContent = TRUE and bodyToken = 'Y' then
            -- Build the resource section of the XML so that the 
            -- java layer can locate the notification body to 
            -- merge into the template.

ntfURL := wf_mail.urlEncode(wf_notification.getFwkBodyURL(nid,
                                        g_ntfDocText));
            AddElementAttribute('page-type','fwk', attrlist);
            AddElementAttribute('src',ntfURL, attrlist);
            AddElementAttribute('enable-stylesheet', stylesheetEnabled,
                                attrlist);
            AddElementAttribute('language',language, attrlist);
            AddElementAttribute('territory',territory, attrlist);
            AddElementAttribute('token', 'BODY', attrlist);

pos := NewLOBTag(p_doc, pos, 'RESOURCE','',attrlist);
            attrlist.DELETE;
            pos := SkipLOBTag(p_doc, 'RESOURCE', pos, occurance); 
         end if;
         pos := SkipLOBTag(p_doc, 'BODYPART', pos, occurance);
         attrlist.DELETE;
      end if;

if notification_pref in ('MAILHTML','MAILATTH','MAILHTM2') then
         /*
         ** The HTML version of the message is only available
         ** to MAILHTML and MAILATTH recipients.
         ** MAILHTML2 is a text/html message without the additional 
         ** two framed attachment.
         */
         dbms_lob.trim(g_LOBTable(messageIdx).temp_lob, 0);
         getBodyPart(g_LOBTable(messageIdx).temp_lob, nid, 
                     g_ntfDocHtml);
         imgFound := FALSE;
         if frameworkContent = FALSE AND attachInlineImages = 'Y' then
            imgFound := ParseForInlineImages(g_LOBTable(messageIdx).temp_lob, 
                                             nid);
         end if;
         /**
          * The content-type for the CONTENT tag is based on a set of rules.
          * It can only be multiplart/related if it is a framework region,
          * or there is an inline image. Inline images and framework regions are
          * only allowed for HTML type notifications.
          */
         if(frameworkContent = TRUE or imgFound = TRUE) then
            AddElementAttribute('content-type', 'multipart/related', attrlist);
         else
            AddElementAttribute('content-type', 'multipart/mixed', attrlist);
         end if;

pos := NewLOBTag(p_doc, pos, 'CONTENT', '', attrlist);
         attrlist.DELETE;

AddElementAttribute('content-type', 'text/html', attrlist);

pos := NewLOBTag(p_doc, pos, 'BODYPART', '', attrlist);
         attrlist.DELETE;

AddElementAttribute('content-type',
                             g_ntfDocHtml, attrlist);

if notification_pref = 'MAILATTH' then
            /*
            ** The text/html will be an attachment for MAILTEXT
            */
            AddElementAttribute('content-disposition',disposition, attrlist);
            AddElementAttribute('filename',htmlfilename, attrlist);
         end if;
         pos := NewLOBTag(p_doc, pos, 'MESSAGE', 
                          g_LOBTable(messageIdx).temp_lob, attrlist);
         pos := SkipLOBTag(p_doc, 'MESSAGE', pos, occurance);
         if frameworkContent = TRUE and bodyToken = 'Y' then
            -- Build the resource section of the XML so that the 
            -- java layer can locate the notification body to 
            -- merge into the template.

ntfURL := wf_mail.urlEncode(wf_notification.getFwkBodyURL(nid,
                                        g_ntfDocHtml));
            AddElementAttribute('page-type','fwk', attrlist);
            AddElementAttribute('src',ntfURL, attrlist);
            AddElementAttribute('enable-stylesheet', stylesheetEnabled, 
                                attrlist);
            AddElementAttribute('language', language, attrlist);
            AddElementAttribute('territory', territory, attrlist);
            AddElementAttribute('token', 'BODY', attrlist);

pos := NewLOBTag(p_doc, pos, 'RESOURCE','',attrlist);
            attrlist.DELETE;
            pos := SkipLOBTag(p_doc, 'RESOURCE', pos, occurance); 
         elsif (frameworkContent = FALSE AND imgFound = TRUE) then
            -- Add RESOURCE tags for each of the images pased in 
            -- in the ParseForInlineImages
            addInlineImages(p_doc, pos, attrlist, nid, disposition);
         end if;

pos := SkipLOBTag(p_doc, 'BODYPART', pos, occurance);
         attrlist.DELETE;
      end if;
      
      if notification_pref in ('MAILHTML','MAILATTH') then
         /*
         ** Adding the text/html component to the notification.
         ** This is the little two framed representation of the 
         ** notification.
         **
         ** This is only available for the MAILHTML and MAILATTH recipients.
         */
         AddElementAttribute('content-type',g_ntfDocHtml, attrlist);
         pos := NewLOBTag(p_doc, pos, 'BODYPART', '', attrlist);

body_atth := '<![CDATA['||body_atth||']]>';
         AddElementAttribute('content-disposition',disposition, attrlist);
         AddElementAttribute('filename',urlfilename, attrlist);
         pos := NewLOBTag(p_doc, pos, 'MESSAGE', body_atth, attrlist);
         pos := SkipLOBTag(p_doc, 'MESSAGE', pos, occurance);
         pos := SkipLOBTag(p_doc, 'BODYPART', pos, occurance);
         attrlist.DELETE;
      end if;

/* 
      ** Next will be to attach all URLs and DOCUMENT attributes
      ** as attachments. Disposition for all URL and DOCUMENT attributes
      ** with ATTACH=Y should be attachment.
      */
      pos := GetAttachment(nid, p_doc, agent, 'attachment', doctype, pos);
      
      pos := SkipLOBTag(p_doc, 'CONTENT', pos, occurance);
      pos := SkipLOBTag(p_doc, 'NOTIFICATION', pos, occurance);

p_pos := pos;
      wf_temp_lob.releaseLob(g_LOBTable, messageIdx);

if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
         wf_log_pkg.string(WF_LOG_PKG.level_procedure, 
                          'wf.plsql.WF_XML.generateDoc', 'END');
      end if;
   exception
      when others then
        wf_temp_lob.releaseLob(g_LOBTable, messageIdx);
        wf_core.context('WF_XML', 'GenerateDoc', p_event_name, p_event_key);
        raise;
   end GenerateDoc;

-- GenerateGroupDoc
   -- To generate the complete set of notification for a group.
   procedure GenerateGroupDoc(p_doc in out NOCOPY CLOB,
                              p_pos in out NOCOPY number,
                              p_recipient_role in varchar2,
                              p_notification_pref in varchar2,
                              p_orig_system in varchar2,
                              p_orig_system_id in number,
                              p_event_name in varchar2,
                              p_event_key in varchar2,
                              p_parameter_list in wf_parameter_list_t) 
   is

cursor members(rname varchar2, rorig varchar2, rorigid number) is
         select UR.USER_NAME, UR.USER_ORIG_SYSTEM, UR.USER_ORIG_SYSTEM_ID 
                from   WF_USER_ROLES UR 
                where  UR.ROLE_NAME = rname 
                  and    UR.ROLE_ORIG_SYSTEM = rorig 
                  and    UR.ROLE_ORIG_SYSTEM_ID = rorigid 
                  and    ((UR.USER_NAME <> UR.ROLE_NAME) or 
                          (UR.USER_ORIG_SYSTEM <> UR.ROLE_ORIG_SYSTEM  and 
                           UR.USER_ORIG_SYSTEM_ID <> UR.ROLE_ORIG_SYSTEM_ID));
                           
      members_type members%ROWTYPE;
      TYPE members_table_type is TABLE OF members%ROWTYPE
           INDEX BY BINARY_INTEGER;
      i pls_integer := 1;
      
      members_t members_table_type;
      attrlist        wf_xml_attr_table_type;
      
   begin
      if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
         wf_log_pkg.string(WF_LOG_PKG.level_procedure, 
                          'wf.plsql.WF_XML.generateGroupDoc', 'BEGIN');
      end if;
      i := 1;
      for r in members(p_recipient_role, p_orig_system, p_orig_system_id) loop
         members_t(i).user_name := r.user_name;
         members_t(i).user_orig_system:= r.user_orig_system;
         members_t(i).user_orig_system_id:= r.user_orig_system_id;
         i := i + 1;
      end loop;

-- 4104735 Removing the test for "p_notification_pref not in 
      --         ('QUERY','SUMMARY','SUMHTML', 'DISABLED')". The rule to
      -- send or not should come from the individual memebers and not the
      -- parent role. If there are simply no members then the NULL document
      -- should be sent. The testing of the notification preference for 
      -- the members will be performed again in genreateDoc.
      if members_t.count = 0 then
         -- No role members. Only log it for now and change the 
         -- notification preference to QUERY.
         -- ---------------------------------------------------
         -- wf_core.context('WF_XML','GenerateGroup', p_event_name, 
         --                 p_event_key);
         -- wf_core.token('NID', p_event_key);
         -- wf_core.token('ROLE', p_recipient_role);
         -- wf_core.raise('WFMLR_NOROLEMEMBERS');
         -- ---------------------------------------------------
         AddElementAttribute('maxcount', '1', attrlist);
         p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATIONGROUP', '', attrlist);
         attrlist.DELETE;

-- 4104735 Empty documents now provide a document type and 
         -- if a empty document, must provide a reason.
         AddElementAttribute('nid', p_event_key, attrlist);
         AddElementAttribute('language', 'AMERICAN', attrlist);
         AddElementAttribute('territory', 'AMERICA', attrlist);
         AddElementAttribute('codeset', 'UTF8', attrlist);
         AddElementAttribute('full-document', 'N', attrlist);
         AddElementAttribute('reason', 'no_members', attrlist);
         p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATION', '', attrlist);
         attrlist.DELETE;

else

AddElementAttribute('maxcount',to_char(members_t.count), attrlist);
         p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATIONGROUP', '', attrlist);
         attrlist.DELETE;
      
         for i in 1..members_t.count loop
            generateDoc(p_doc, p_pos, members_t(i).user_name,
                        p_event_name, p_event_key, p_parameter_list);
         end loop;
      end if;
      
      if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
         wf_log_pkg.string(WF_LOG_PKG.level_procedure, 
                          'wf.plsql.WF_XML.generateGroupDoc', 'END');
      end if;
   exception
      when others then
         wf_core.context('WF_XML','GenerateGroupDoc',p_event_name, p_event_key);
         raise;
   end GenerateGroupDoc;

-- GenerateMessage
   -- To generate the XML content for a single notification
   procedure GenerateMessage(p_doc in out nocopy CLOB, 
                     p_event_name in varchar2,
                     p_event_key in varchar2,
                     p_parameter_list in wf_parameter_list_t) 
   is
      nid NUMBER;
      
      pos integer;
      amt number;
      
      installed VARCHAR2(1);
      language VARCHAR2(100);
      territory VARCHAR2(100);
      codeset VARCHAR2(100);
      orig_system VARCHAR2(100);
      orig_system_id number;
      
      recipient_role VARCHAR2(100);
      status VARCHAR2(100);
      mail_status VARCHAR2(100);
      str varchar2 (2000);
      
      role VARCHAR2(100);
      display_name VARCHAR2(200);
      email VARCHAR2(1000);
      notification_pref VARCHAR2(100);

attrlist        wf_xml_attr_table_type;

nodename varchar2(100);
      messageType varchar2(8);
      messageName varchar2(30);

begin
      if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
         wf_log_pkg.string(WF_LOG_PKG.level_procedure, 
                          'wf.plsql.WF_XML.generateMessage', 'BEGIN');
      end if;

nid := to_number(p_event_key);
      
      -- Obtain notification information
      begin -- 3741599 If there is no notification, exit gracefully
         select NVL(MORE_INFO_ROLE, RECIPIENT_ROLE), STATUS, MAIL_STATUS, 
                MESSAGE_TYPE, MESSAGE_NAME
         into recipient_role, status, mail_status, messageType, messageName
         from WF_NOTIFICATIONS
         where  NOTIFICATION_ID = nid;
      exception
         when no_data_found then
           str := '<?xml version="1.0" ?>';
           pos := length(str);
           dbms_lob.write(p_doc, pos, 1, str);
           nodename := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
                                                       pCorrId => messageType,
                                                       pName => 'NODENAME');

AddElementAttribute('maxcount','1', attrlist);
           pos := NewLOBTag(p_doc, pos, 'NOTIFICATIONGROUP', '', attrlist);
           attrlist.DELETE;

return;
         when others then
            wf_core.context('WF_XML','GenerateMessage',to_char(nid));
            raise;
      end;

if (wf_mail.test_flag = TRUE) then
          mail_status := 'MAIL';
          if (status not in ('OPEN','CANCELED','CLOSED')) then
             status := 'OPEN';
          end if;
       end if;

if MAIL_STATUS is null or MAIL_STATUS not in ('MAIL','INVALID') then

str := '<?xml version="1.0" ?>';
           pos := length(str);
           dbms_lob.write(p_doc, pos, 1, str);
           nodename := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
                                                       pCorrId => messageType,
                                                       pName => 'NODENAME');

AddElementAttribute('maxcount','1', attrlist);
           pos := NewLOBTag(p_doc, pos, 'NOTIFICATIONGROUP', '', attrlist);
           attrlist.DELETE;

return;
      end if;

if email is not null then
         -- Email address is provided. process for one recipient

if notification_pref not in ('QUERY','SUMMARY','SUMHTML',
                                      'DISABLED') then
           str := '<?xml version="1.0" ?>';
           pos := length(str);
           dbms_lob.write(p_doc, pos, 1, str);

AddElementAttribute('maxcount','1', attrlist);
           pos := NewLOBTag(p_doc, pos, 'NOTIFICATIONGROUP', '', attrlist);
           attrlist.DELETE;
           generateDoc(p_doc, pos, recipient_role,
                       p_event_name, p_event_key, p_parameter_list);
         else
            -- This case should not be reached if the notification system
            -- is working correctly ie that a NULL is placed in the mail_status
            -- However, just in case, generate a blank NOTIFICATION element
            -- all the same.

str := '<?xml version="1.0" ?>';
           pos := length(str);
           dbms_lob.write(p_doc, pos, 1, str);

AddElementAttribute('maxcount','1', attrlist);
           pos := NewLOBTag(p_doc, pos, 'NOTIFICATIONGROUP', '', attrlist);
           attrlist.DELETE;

-- 4104735 Empty documents now provide a document type and 
           -- if a empty document, must provide a reason.
           AddElementAttribute('nid', nid, attrlist);
           AddElementAttribute('language', 'AMERICAN', attrlist);
           AddElementAttribute('territory', 'AMERICA', attrlist);
           AddElementAttribute('codeset', 'UTF8', attrlist);
           AddElementAttribute('full-document', 'N', attrlist);
           AddElementAttribute('reason', 'NOTIFICATION_PREFERENCE:'||
                                          notification_pref, attrlist);
           pos := NewLOBTag(p_doc, pos, 'NOTIFICATION', '', attrlist);
           attrlist.DELETE;
         end if;
      else
         -- No email address is provided. Assume that this is a 
         -- role with members.
         -- Failure to yeild members will result in a notification
         -- being generated to go to the system admin and the
         -- error process to be called.
         str := '<?xml version="1.0" ?>';
         pos := length(str);
         dbms_lob.write(p_doc, pos, 1, str);
         generateGroupDoc(p_doc, pos, recipient_role, notification_pref,
                          orig_system, orig_system_id, 
                          p_event_name, p_event_key, p_parameter_list);
      end if;

if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
         wf_log_pkg.string(WF_LOG_PKG.level_procedure, 
                          'wf.plsql.WF_XML.generateMessage', 'END');
      end if;
   exception
      when others then
         wf_core.context('WF_XML','Generate',p_event_name, p_event_key);
         raise;
   end GenerateMessage;

-- GenerateSummaryDoc
   -- To generate the XML content for the OPEN notifications.

procedure GenerateSummaryDoc (p_doc in out NOCOPY CLOB,
                          p_pos in out NOCOPY number,
                          p_recipient_role in varchar2,
                          p_event_name in varchar2,
                          p_event_key in varchar2,
                          p_parameter_list in wf_parameter_list_t) 
   is
      pos integer;
      attrlist wf_xml_attr_table_type;
      occurance integer;

docType VARCHAR2(100);

recipient_role VARCHAR2(100);
      display_name VARCHAR2(200);
      email VARCHAR2(1000);

notification_pref VARCHAR2(100);
      language VARCHAR2(100);
      territory VARCHAR2(100);
      orig_system VARCHAR2(100);
      orig_system_id number;
      installed VARCHAR2(1);

hdrxml varchar2(32000);
      hdrxmlPos integer;

base_lang VARCHAR2(100);
      base_territory VARCHAR2(100);
      base_codeset VARCHAR2(100);
      codeset VARCHAR2(100);

corrId          varchar2(128);
      nodename        varchar2(100);
      fromName        varchar2(100);
      replyto         varchar2(100);
      subject VARCHAR2(4000);
      
      msgbody VARCHAR2(32000);
      -- message CLOB;
      messageIdx pls_integer;
      l_lob VARCHAR2(1);
      resourceSrc varchar2(4000);
      l_renderBody varchar2(1);
      l_messageName varchar2(30);

begin

recipient_role := p_recipient_role;

corrId := wf_event.getValueForParameter('Q_CORRELATION_ID',
                                              p_parameter_list);
      messageIdx := 0;
      pos := p_pos;
      occurance := 1;

-- Obtain recipient information
      WF_DIRECTORY.GetRoleInfoMail(recipient_role, display_name, email, 
                                   notification_pref, 
                                   language, territory, 
                                   orig_system, orig_system_id, installed);
      if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
         wf_log_pkg.string(wf_log_pkg.level_statement, 
                           'wf.plsql.WF_XML.GenerateSummaryDoc',
                           'ROLE {'||recipient_role||'} LANG {'||language||
                           '} TERR {'||territory||' NTF {'||notification_pref||
                           '} EMAIL {'||email||'}');

end if;

-- XX TODO Sentinal for DISABLED case? Though there is no NID to be
      -- updated, can be used as documentation in the log
      if notification_pref not in ('SUMMARY','SUMHTML') or
         email is null then
         if (wf_log_pkg.level_exception >= fnd_log.g_current_runtime_level) then
            if email is null then
               wf_log_pkg.string(wf_log_pkg.level_exception, 
                                 'wf.plsql.WF_XML.GenerateSummaryDoc',
                                 'Not generating. Recipient has a null '||
                                 'email address');
            else
               wf_log_pkg.string(wf_log_pkg.level_exception, 
                                 'wf.plsql.WF_XML.GenerateSummaryDoc',
                                 'Not generating. Recipient has pref: '||
                                 notification_pref);
            end if;
         end if;
 
         AddElementAttribute('nid', '0', attrlist);
         AddElementAttribute('language', 'AMERICAN', attrlist);
         AddElementAttribute('territory', 'AMERICA', attrlist);
         AddElementAttribute('codeset', 'UTF8', attrlist);
         AddElementAttribute('full-document', 'N', attrlist);
         AddElementAttribute('reason', 'NOTIFICATION_PREFERENCE:'||
                             notification_pref, attrlist);
         p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATION', '', attrlist);
         attrlist.DELETE;
         p_pos := SkipLOBTag(p_doc, 'NOTIFICATION', p_pos, occurance);
         return;

end if;

-- Set the preferred document type based on the 
      -- notification preference.
      if g_install = 'EMBEDDED' then
         l_renderBody := 'N';
         l_messageName := 'SUMHTML';
         if notification_pref = 'SUMMARY' then
            docType := g_ntfDocText;
         elsif notification_pref = 'SUMHTML' then 
            docType := g_ntfDocHtml;
         end if;
      else
         l_renderBody := 'Y';
         l_messageName := 'SUMMARY';
         docType := g_ntfDocText;
      end if;

-- Get the Base NLS. 
      WF_MAIL.GetSessionLanguage(base_lang, base_territory, base_codeset);      
     
      -- If requested language is not installed, use base NLS setting
      if installed = 'N' then
         language := base_lang;
         territory := base_territory;
      end if;
      begin
         select nls_codeset
         into codeset
         from wf_languages
         where nls_language = language;
      exception
         when others then
            codeset := base_codeset;
      end;
      
      setNLS(language, territory);
                                          
      -- Initialise the XML Document and then progressively walk
      -- through the elements. Populating them as we go.
      -- l_pos is crucial as it determines where the next nodes
      -- will be placed.

l_lob := 'N';

WF_MAIL.GetSummary2(
       role      =>  recipient_role,
       dname     =>  display_name,
       node      =>  nodename,
       renderBody=> l_renderBody,
       contType  => docType,
       subject   => subject,
       body_text => msgbody,
       lob       => l_lob);

SetNLS(base_lang, base_territory);
     
      -- NID hardcoded to 0 for Summary
      AddElementAttribute('nid', '0', attrlist);
      AddElementAttribute('language', language, attrlist);
      AddElementAttribute('territory', territory, attrlist);
      AddElementAttribute('codeset', codeset, attrlist);
      -- priority hardcoded to 50
      AddElementAttribute('priority', '50', attrlist);
      -- Not addding accesskey as no response processing is done for summary email
      AddElementAttribute('node', nodename, attrlist);
      -- ItemType / messageType hardcoded to WFMAIL
      AddElementAttribute('item_type', 'WFMAIL', attrlist);
      AddElementAttribute('message_name', l_messageName, attrlist);
      AddElementAttribute('full-document', 'Y', attrlist);
      pos := NewLOBTag(p_doc, pos, 'NOTIFICATION', '', attrlist);
      attrlist.DELETE;

hdrxmlPos := 0;
      hdrxml := '';

hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'RECIPIENTLIST', '', attrlist);

AddElementAttribute('name',recipient_role, attrlist);
      AddElementAttribute('type','to', attrlist);
      hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'RECIPIENT', '', attrlist);
      attrlist.DELETE;

display_name := replace(display_name, g_newLine);
      hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'NAME', display_name, attrlist);
      hdrxmlPos := SkipTag(hdrxml, 'NAME', hdrxmlPos, occurance);

email := replace(email, g_newLine);
      hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'ADDRESS', email, attrlist);
      hdrxmlPos := SkipTag(hdrxml, 'ADDRESS', hdrxmlPos, occurance);
      
      hdrxmlPos := SkipTag(hdrxml, 'RECIPIENT', hdrxmlPos, occurance);
      hdrxmlPos := SkipTag(hdrxml, 'RECIPIENTLIST', hdrxmlPos, occurance);

hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'FROM', '', attrlist);

-- fromName := '&#FROM';
      fromName := WF_MAILER_PARAMETER.GetValueForCorr (pCorrId => corrId,
                                                       pName => 'FROM');
      fromName := replace(fromName, g_newLine);
      fromName := '<![CDATA['||fromName||']]>'; 
      hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'NAME', fromName, attrlist);
      hdrxmlPos := SkipTag(hdrxml, 'NAME', hdrxmlPos, occurance);

-- replyto := '&#REPLYTO';
      replyto := WF_MAILER_PARAMETER.GetValueForCorr (pCorrId => corrId,
                                                      pName => 'REPLYTO');
      replyto := replace(replyto, g_newLine);
      replyto := '<![CDATA['||replyto||']]>'; 
      hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'ADDRESS', replyto, attrlist);
      hdrxmlPos := SkipTag(hdrxml, 'ADDRESS', hdrxmlPos, occurance);
      hdrxmlPos := SkipTag(hdrxml, 'FROM', hdrxmlPos, occurance);

-- Not addding status as not needed for summary email

subject := replace(subject, g_newLine);
      subject := '<![CDATA['||subject||']]>'; 
      hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'SUBJECT', subject, attrlist);

pos := NewLOBTag(p_doc, pos, 'HEADER', hdrxml, attrlist);
      pos := SkipLOBTag(p_doc, 'HEADER', pos, occurance);

AddElementAttribute('content-type', 'multipart/mixed', attrlist);
      pos := NewLOBTag(p_doc, pos, 'CONTENT', '', attrlist);
      attrlist.DELETE;

/*
      ** The body part section of the XML structure will contain
      ** a <![CDATA ]]> construct which removes the need to URL 
      ** encode the data.
      */      
      if (l_renderBody = 'Y') then
         AddElementAttribute('content-type', 'text/plain', attrlist);
      else
         AddElementAttribute('content-type', 'multipart/related', attrlist);
      end if;
      pos := NewLOBTag(p_doc, pos, 'BODYPART', '', attrlist);
      attrlist.DELETE;

-- Set the content-type of the MESSAGE tag to being the
      -- preferred output.
      AddElementAttribute('content-type', docType, attrlist);
      if (l_lob = 'Y') then

-- dbms_lob.createTemporary(message, TRUE, dbms_lob.CALL);
         messageIdx := wf_temp_lob.getLob(g_LOBTable);
         dbms_lob.trim(g_LOBTable(messageIdx).temp_lob, 0);
         getBodyPart(g_LOBTable(messageIdx).temp_lob, 1, 
                     g_ntfDocText);
      
         pos := NewLOBTag(p_doc, pos, 'MESSAGE', 
                          g_LOBTable(messageIdx).temp_lob, attrlist);
         wf_temp_lob.releaseLob(g_LOBTable, messageIdx);
      else

msgbody := '<![CDATA['||msgbody||']]>';

pos := NewLOBTag(p_doc, pos, 'MESSAGE', msgbody, attrlist);

end if;

pos := SkipLOBTag(p_doc, 'MESSAGE', pos, occurance);
      if l_renderBody = 'N' then
         -- If this is a HTML summary notification then
         -- create a RESOURCE tag to obtain the content from
         -- the Applications Framework
         resourceSrc := wf_mail.urlEncode(wf_notification.getSummaryUrl(
                                          recipient_role, docType));
         AddElementAttribute('page-type','fwk', attrlist);
         AddElementAttribute('src', resourceSrc, attrlist);
         AddElementAttribute('language', language, attrlist);
         AddElementAttribute('territory', territory, attrlist);
         AddElementAttribute('token', 'SUMMARY', attrlist);
         pos := NewLOBTag(p_doc, pos, 'RESOURCE', '', attrlist);
         pos := SkipLOBTag(p_doc, 'RESOURCE', pos, occurance);
         attrList.DELETE;
      end if;
      pos := SkipLOBTag(p_doc, 'BODYPART', pos, occurance);
      attrlist.DELETE;

pos := SkipLOBTag(p_doc, 'CONTENT', pos, occurance);
      pos := SkipLOBTag(p_doc, 'NOTIFICATION', pos, occurance);

p_pos := pos;

exception
      when others then
        if (messageIdx > 0) then
           wf_temp_lob.releaseLob(g_LOBTable, messageIdx);
        end if;
        wf_core.context('WF_XML', 'GenerateSummaryDoc', p_event_name, 
                        p_event_key);
        raise;
   end GenerateSummaryDoc;

-- Generate
   procedure GenerateGroupSummaryDoc (p_doc in out NOCOPY CLOB,
                              p_pos in out NOCOPY number,
                              p_recipient_role in varchar2,
                              p_orig_system in varchar2,
                              p_orig_system_id in number,
                              p_event_name in varchar2,
                              p_event_key in varchar2,
                              p_parameter_list in wf_parameter_list_t) 
   is

i pls_integer := 1;
      
      members_t members_table_type;
      attrlist        wf_xml_attr_table_type;
      
   begin

i := 1;
      for r in members(p_recipient_role, p_orig_system, p_orig_system_id) loop
         members_t(i).user_name := r.user_name;
         members_t(i).user_orig_system:= r.user_orig_system;
         members_t(i).user_orig_system_id:= r.user_orig_system_id;
         i := i + 1;
      end loop;

if members_t.count = 0 then
         if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
            wf_log_pkg.string(wf_log_pkg.level_event, 
                              'wf.plsql.WF_XML.GenerateGroupSummaryDoc',
                              'No role members.');
         end if;
 
         AddElementAttribute('maxcount', '1', attrlist);
         p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATIONGROUP', '', attrlist);
         attrlist.DELETE;

-- 4104735 Empty documents now provide a document type and 
         -- if a empty document, must provide a reason.
         AddElementAttribute('nid', '0', attrlist);
         AddElementAttribute('language', 'AMERICAN', attrlist);
         AddElementAttribute('territory', 'AMERICA', attrlist);
         AddElementAttribute('codeset', 'UTF8', attrlist);
         AddElementAttribute('full-document', 'N', attrlist);
         AddElementAttribute('reason', 'no_members', attrlist);
         p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATION', '', attrlist);
         attrlist.DELETE;
      else

AddElementAttribute('maxcount',to_char(members_t.count), attrlist);
         p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATIONGROUP', '', attrlist);
         attrlist.DELETE;
         
         for i in 1..members_t.count loop
            GenerateSummaryDoc (p_doc, p_pos, members_t(i).user_name, 
                        p_event_name, p_event_key, p_parameter_list);
         end loop;
      end if;
      
   exception
      when others then
         wf_core.context('WF_XML','GenerateGroupSummaryDoc ',p_event_name, 
                         p_event_key);
         raise;
   end GenerateGroupSummaryDoc ;

-- GenerateSummary
   -- To generate the XML content for the enqueued Summary notifications.
   procedure GenerateSummary (p_doc in out nocopy CLOB, 
                     p_event_name in varchar2,
                     p_event_key in varchar2,
                     p_parameter_list in wf_parameter_list_t) 
   is
      pos integer;

attrlist        wf_xml_attr_table_type;
      str varchar2 (2000);
      
      recipient_role VARCHAR2(100);
      
      display_name VARCHAR2(200);
      email VARCHAR2(1000);
      notification_pref VARCHAR2(100);
      language VARCHAR2(100);
      territory VARCHAR2(100);
      orig_system VARCHAR2(100);
      orig_system_id number;
      installed VARCHAR2(1);

e_RoleNameNotSpecified 		EXCEPTION;

begin

if p_parameter_list is not null then
            recipient_role := wf_event.getValueForParameter('ROLE_NAME', 
                                                            p_parameter_list);
      end if;

if recipient_role is null then
          raise e_RoleNameNotSpecified;
      end if;

-- Initialise the XML Document and then progressively walk
      -- through the elements. Populating them as we go.
      -- l_pos is crucial as it determines where the next nodes
      -- will be placed.
      str := '<?xml version="1.0" ?>';
      pos := length(str);
      dbms_lob.write(p_doc, pos, 1, str);

if email is not null or email <> '' then

AddElementAttribute('maxcount','1', attrlist);
         pos := NewLOBTag(p_doc, pos, 'NOTIFICATIONGROUP', '', attrlist);
         attrlist.DELETE;
         -- Email address is provided. process for one recipient

GenerateSummaryDoc (p_doc, pos, recipient_role, p_event_name, 
                     p_event_key, p_parameter_list);

else

-- No email address is provided. Assume that this is a 
         -- role with members.
         -- Failure to yeild members will result in a notification
         -- being generated to go to the system admin and the
         -- error process to be called.

GenerateGroupSummaryDoc (p_doc, pos, recipient_role, orig_system, 
                          orig_system_id, p_event_name, p_event_key, 
                          p_parameter_list);

end if;

exception

when e_RoleNameNotSpecified then
         wf_core.context('WF_XML','GenerateSummary',p_event_name, p_event_key);
         raise;

when others then
         wf_core.context('WF_XML','GenerateSummary',p_event_name, p_event_key);
         raise;
   end GenerateSummary;
 
   -- Generate
   -- To generate the XML content for the enqueued notifications.
   function Generate(p_event_name in varchar2,
                     p_event_key in varchar2,
                     p_parameter_list in wf_parameter_list_t) 
                     return clob
   is
      l_doc CLOB;
      l_evt wf_event_t;
      l_parameters  wf_parameter_list_t;
      l_erragt wf_agent_t;

begin
      if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
         wf_log_pkg.string(WF_LOG_PKG.level_procedure, 
                          'wf.plsql.WF_XML.generate', 'BEGIN');
      end if;
      -- We do not use the LOB pooling for the generate as the LOB
      -- is to be returned and not reused.
      dbms_lob.createTemporary(l_doc, TRUE, dbms_lob.call);

begin
         if p_event_name in (wf_xml.WF_NTF_SEND_MESSAGE, wf_xml.WF_NTF_CANCEL,
                            wf_xml.WF_NTF_REASIGN) then

GenerateMessage(l_doc, p_event_name, p_event_key, 
                            p_parameter_list);
         elsif p_event_name = WF_NTF_SEND_SUMMARY then
            GenerateSummary (l_doc, p_event_name, p_event_key, p_parameter_list);
         end if;
      exception 
      when others then
         wf_core.context('WF_XML','Generate',p_event_name, p_event_key);
         raise;
      end;
      if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
         wf_log_pkg.string(WF_LOG_PKG.level_procedure, 
                          'wf.plsql.WF_XML.generate', 'END');
      end if;
      return l_doc;
   end;

-- notificationIsOpen
   -- Return the current status of the notification
   function notificationIsOpen(nid in number) return boolean
   is
      l_open integer := 0;
   begin
      begin
         select 1
         into l_open
         from wf_notifications
         where notification_id = nid
           and status = 'OPEN';
      exception
         when others then l_open := 0;
      end;
      return l_open > 0;
   end;

l_node varchar2(4000);
l_version integer;
l_from_role varchar2(4000);
l_responses wf_responseList_t;

begin

getResponseDetails(message => message, 
                       node => l_node, 
                       version => l_version,
                       fromRole => l_from_role, 
                       responses => l_responses);

end getResponseDetails;

-- GetResponseDetails
-- Gets the response details from the incoming XML Notifiction
-- structure.
--
-- IN
-- message - The XML Notification structure containing the
--           inbound response
procedure getResponseDetails(message in CLOB, node out NOCOPY varchar2, 
                             version out NOCOPY integer,
                             fromRole out NOCOPY varchar2, 
                             responses in out NOCOPY wf_responseList_t) 
IS

p xmlparser.parser;
doc xmldom.DOMDocument;

nl xmldom.DOMNodeList;
len1 number;
len2 number;
n xmldom.DOMNode;
m xmldom.DOMNode;
nnm xmldom.DOMNamedNodeMap;

fromAddress varchar2(4000);
attrname varchar2(4000);
attrval varchar2(4000);

node_data varchar2(32000);
node_name varchar2(4000);
from_node_found boolean;

attribute_found boolean;
response_count number;

response_attr_name varchar2(4000);
response_attr_val varchar2(32000);
response_attr_type varchar2(4000);
response_attr_format varchar2(4000);

begin

from_node_found := FALSE;
   attribute_found := FALSE;
   response_count := 0;
   responses.delete;
   node := '';
   fromRole := '';

-- new parser
   p := xmlparser.newParser;

-- set some characteristics
   xmlparser.setValidationMode(p, FALSE);

-- parse input file
   xmlparser.parseClob(p, message);

-- get document
   doc := xmlparser.getDocument(p);

-- get all elements
   nl := xmldom.getElementsByTagName(doc, '*');
   len1 := xmldom.getLength(nl);

-- loop through elements
   for j in 0..len1-1 loop

n := xmldom.item(nl, j);
      node_name := xmldom.getNodeName(n);

if node_name = 'NOTIFICATION' then

-- get all attributes of element
         nnm := xmldom.getAttributes(n);

if (xmldom.isNull(nnm) = FALSE) then

len2 := xmldom.getLength(nnm);

-- loop through attributes
            for i in 0..len2-1 loop

m := xmldom.item(nnm, i);
               attrname := xmldom.getNodeName(m);

if attrname = 'node' then

attrval := xmldom.getNodeValue(m);
                  node := attrval;
               elsif attrname = 'version' then

attrval := xmldom.getNodeValue(m);
                  begin
                     version  := to_number(attrval);
                  exception
                     when others then 
                        version := 0;
                  end;
               end if;

end loop;

end if;

elsif node_name = 'FROM' then

from_node_found := TRUE;

elsif node_name = 'NAME' then

if from_node_found then

n := xmldom.getFirstChild(n);

if ((not xmldom.isNull(n)) 
                and (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then

node_data := xmlDom.getNodeValue(n);
               fromRole := node_data;

end if;

elsif node_name = 'ADDRESS' then

if from_node_found then

from_node_found := FALSE;

n := xmldom.getFirstChild(n);

if ((not xmldom.isNull(n)) 
                and (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then

node_data := xmlDom.getNodeValue(n);
               fromAddress := node_data;

fromRole := '"'||fromRole||'" <'||fromAddress||'>';

end if;

elsif node_name = 'ATTRIBUTE' then

-- get all attributes of element
         nnm := xmldom.getAttributes(n);

if (xmldom.isNull(nnm) = FALSE) then

attribute_found := FALSE;
            response_attr_name := '';
            response_attr_val := '';
            response_attr_type := '';
            response_attr_format := '';

len2 := xmldom.getLength(nnm);

-- loop through attributes
            for i in 0..len2-1 loop

m := xmldom.item(nnm, i);
               attrname := xmldom.getNodeName(m);

if attrname = 'name' then

attribute_found := TRUE;
                  attrval := xmldom.getNodeValue(m);
                  response_attr_name := attrval;

elsif attrname = 'type' then

attrval := xmldom.getNodeValue(m);
                  response_attr_type := attrval;

elsif attrname = 'format' then

attrval := xmldom.getNodeValue(m);
                  response_attr_format := attrval;

end if;

end loop;

if attribute_found then

n := xmldom.getFirstChild(n);

if ((not xmldom.isNull(n)) and 
                   (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then

node_data := substrb(xmlDom.getNodeValue(n), 1, 32000);
                  response_attr_val := node_data;
               end if;

response_count := response_count + 1;
               responses(response_count).NAME := response_attr_name;
               responses(response_count).TYPE := response_attr_type;
               responses(response_count).FORMAT := response_attr_format;
               responses(response_count).VALUE := response_attr_val;

end if;

end loop;

if (not xmldom.isNull(doc)) then
      xmldom.freeDocument (doc);
   end if;

xmlparser.freeParser (p);

exception

when xmldom.INDEX_SIZE_ERR then
   raise;

when xmldom.DOMSTRING_SIZE_ERR then
   raise;

when xmldom.HIERARCHY_REQUEST_ERR then
   raise;

when xmldom.WRONG_DOCUMENT_ERR then
   raise;

when xmldom.INVALID_CHARACTER_ERR then
   raise;

when xmldom.NO_DATA_ALLOWED_ERR then
   raise;

when xmldom.NO_MODIFICATION_ALLOWED_ERR then
   raise;

when xmldom.NOT_FOUND_ERR then
   raise;

when xmldom.NOT_SUPPORTED_ERR then
   raise;

when xmldom.INUSE_ATTRIBUTE_ERR then
   raise;

when others then
   raise;

end getResponseDetails;

-- sendNotification
   -- This API is a wrapper to the wf_xml.enqueueNotification. It is provided
   -- as forward compatabilty for the original mailer since the call to
   -- wf_xml.enqueueNotification has been removed from 
   -- wf_notification.sendSingle.
   -- To use the original mailer, one must enable the subscription that will 
   -- call this rule function.
   -- IN
   -- p_subscription
   -- p_event
   -- RETURN
   -- varchar2 of the status 
   function SendNotification (p_subscription_guid in raw,
                     p_event in out NOCOPY WF_EVENT_T) return varchar2
   is
      l_eventName varchar2(80);
      l_eventkey varchar(80);
      l_nid number;
   begin
      l_eventkey := p_event.GetEventKey();
      l_nid := to_number(l_eventKey);
      l_eventName := p_event.GetEventName();
      if l_eventName in (wf_xml.WF_NTF_SEND_MESSAGE, wf_xml.WF_NTF_CANCEL, 
                         wf_xml.WF_NTF_REASIGN) then
         wf_xml.enqueueNotification(l_nid);  
      else
         return wf_rule.default_rule(p_subscription_guid, p_event);
      end if;
      return 'SUCCESS';
   exception
      when others then
         wf_core.Context('WF_XML','SendNotification',p_event.getEventName(),
                         p_subscription_guid);
         -- Save error message and set status to INVALID so mailer will
         -- bounce an "invalid reply" message to sender.
         wf_event.SetErrorInfo(p_event, 'ERROR');
         return 'ERROR';
   end sendNotification;

-- handleRecevieEvent
   --
   function handleReceiveEvent(p_subscription_guid in raw,
                     p_event in out NOCOPY WF_EVENT_T) return varchar2
   is
      l_eventName varchar2(80);
      l_eventkey varchar(80);
      l_paramlist wf_parameter_list_t;
      l_eventData CLOB;
      l_node varchar2(30);
      l_version integer;
      l_user varchar2(230);
      l_comment varchar2(4000);
      l_fromAddr varchar2(2000);
      l_nid number;
      l_template varchar2(30);
      l_module varchar2(200);
      l_error varchar2(2000);
      i int;
      l_responses wf_responseList_t;
      lk_type varchar2(240);
      lk_code varchar2(4000);
      lk_meaning varchar2(1000);
      l_value varchar2(4000);
      l_error_result varchar2(4000);
      l_sig_policy varchar2(100);

l_step varchar2(240);
   begin

l_eventkey := p_event.GetEventKey();
      l_nid := to_number(l_eventKey);
      l_eventName := p_event.GetEventName();
      l_paramList := p_event.getParameterList();
      l_eventData := p_event.getEventData();

-- Recieve the message.
      -- Unpack the content and pass it to the 
      -- routine in charge of parsing it.

-- Allow the response handling handle the closed notification
      -- 3736816 Uncommenting and reimplementing this logic to
      -- ensure that responses to CANCELED notifications are processed
      -- but do not update anything.
      l_step := 'Checking the notification status';
      if not notificationIsOpen(l_nid) then
         begin
            lk_type := '';
            lk_code := '';
            wf_log_pkg.string(WF_LOG_PKG.LEVEL_EXCEPTION,
                            'WF_XML.handleReceiveEvent',
                            'Notification is not OPEN. Submitting response '||
                            'to provice user feedback');
            Wf_Notification.Respond(l_nid, NULL, 'email:'||l_fromAddr);
         exception 
            when others then
               wf_core.Context('WF_XML','handleReceiveEvent',
                               p_event.getEventName(), p_subscription_guid);
               -- Save error message and set status to INVALID so mailer will
               -- bounce an "invalid reply" message to sender.
               WF_MAIL.HandleResponseError(l_nid, lk_type, lk_code,
                                           l_error_result);
         end;
         return 'SUCCESS';
      end if;

l_step := 'Getting the response details';
      getResponseDetails(l_eventData, l_node, l_version, l_fromAddr, 
                         l_responses);

l_step := 'Processing the responses';
      if l_responses.COUNT > 0 then
         begin
            -- Check if this notification requires a Signature. Other than DEFAULT
            -- policies, no other policy is processed by the mailer.
            Wf_Mail.GetSignaturePolicy(l_nid, l_sig_policy);
            if (l_sig_policy is not NULL and upper(l_sig_policy) <> 'DEFAULT') then
               -- If a new policy is added, only wfmail.msg needs to be updated
               wf_core.context('WF_XML', 'HandleReceiveEvent',
                               to_char(l_nid), l_node, l_fromAddr);
               wf_core.token('NID', to_char(l_nid));
               wf_core.raise('WFRSPR_' || l_sig_policy);
            end if;

for i in 1..l_responses.COUNT loop
               l_step := 'Processing the responses -> '||l_responses(i).name;
               lk_type := l_responses(i).format;
               lk_code := substrb(l_responses(i).value, 1, 4000);
               if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
                  wf_log_pkg.string(WF_LOG_PKG.level_statement, 
                                    'wf.plsql.WF_XML.handleReceiveEvent',
                                    'Response VALUE ['||
                                    substrb(lk_code, 1, 100)||
                                    '] TYPE ['||lk_type||']');
               end if;

if l_version < 3 then

----------------------------------
                  -- verion < 3 is a normal response
                  ----------------------------------

-- Process the responses for standard responses
                  if l_responses(i).type = 'LOOKUP' then
                     -- Verify the content of the lookup. This will raise
                     -- an exception if it is not matched. GetLovMeaning 
                     -- allows for nulls. This is not acceptable here.

lk_meaning := wf_mail.GetLovMeaning(lk_type, 
                                                         lk_code);
                     if lk_meaning is null then
                        wf_core.token('TYPE', lk_type);
                        wf_core.token('CODE', lk_code);
                        wf_core.raise('WFSQL_LOOKUP_CODE');
                     end if;
                  end if;
                  if l_responses(i).type = 'MOREINFO' then
                     null; -- discard these responses
                  else
                     wf_notification.setAttrText(l_nid, l_responses(i).name,
                                                 lk_code);
                  end if;
               end if;

-- Only process attributes of type MOREINFO on 
               -- version 3 and 4 templates.
               if l_responses(i).type = 'MOREINFO' 
                  and l_version in (3, 4) then
                  if lk_code is not null then 
                     if l_version = 3 then

if l_responses(i).name = 'WFNTF_MOREINFO_FROM' then
                           l_user := DecodeEntityReference(lk_code);
                        elsif l_responses(i).name = 'WFNTF_MOREINFO_QPROMPT' then
                           if length(l_responses(i).value) > 4000 then
                              wf_core.context('WF_XML', 'HandleReceiveEvent',
                                              l_responses(i).type, 
                                              l_responses(i).name, l_step);
                              lk_code := substrb(l_responses(i).value, 1, 
                                                 1000);
                              wf_core.raise('WFNTF_QUESTION_TOO_LARGE');
                           end if;
                           l_comment := lk_code;
                        end if;
                     elsif l_version = 4 then
                        if l_responses(i).name = 'WFNTF_MOREINFO_APROMPT' then
                           if length(l_responses(i).value) > 4000 then
                              wf_core.context('WF_XML', 'HandleReceiveEvent',
                                              l_responses(i).type,
                                              l_responses(i).name, l_step);
                              lk_code := substrb(l_responses(i).value, 1, 
                                                 1000);
                              wf_core.raise('WFNTF_QUESTION_TOO_LARGE');
                           end if;
                           l_comment := lk_code;
                        end if;
                     end if;
                  end if;
               end if;
            end loop;

-- Update the results of the more information to the 
            -- comments table by calling the update API in the
            -- correct mode based on the template version.
            l_step := 'Updating the notification';
            if l_version = 3 then
               -- Question mode
               -- Send a email back to the sender that the More Info User is invalid   
                if (l_user is null or length(trim(l_user)) = 0) then   
                   wf_core.raise('WFNTF_NO_ROLE');
                elsif(l_comment is null or length(trim(l_comment)) = 0) then
                  wf_core.raise('WFNTF_NO_QUESTION');
                else 
                  wf_notification.UpdateInfo2(l_nid, l_user, l_fromAddr,
                                                l_comment);
               end if;
            elsif l_version = 4 then
               -- Answer mode
               l_user := null; 
               if(l_comment is null or length(trim(l_comment)) = 0) then
                 wf_core.raise('WFNTF_NO_ANSWER');
               else 
                 wf_notification.UpdateInfo2(l_nid, l_user, l_fromAddr,
                                               l_comment);
               end if;
            else
               -- Do not need to preserve context
               wf_engine.preserved_context := FALSE;
               Wf_Notification.Respond(nid       => l_nid,   
                                       responder => 'email:'||l_fromAddr); 
            end if;
         exception
            when others then
               wf_core.Context('WF_XML','handleReceiveEvent',
                               to_char(l_nid), 'Step '||l_step);
               wf_core.context('WF_MAIL','HandleRevieveEvent', to_char(l_nid));
               -- Save error message and set status to INVALID so mailer will
               -- bounce an "invalid reply" message to sender.
               WF_MAIL.HandleResponseError(l_nid, lk_type, 
                                           substrb(lk_code, 1, 1000), 
                                           l_error_result);
         end;
      end if;
      return 'SUCCESS';

exception

when others then
         wf_core.Context('WF_XML','handleReceiveEvent',p_event.getEventName(),
                         p_subscription_guid);
         -- Save error message and set status to INVALID so mailer will
         -- bounce an "invalid reply" message to sender.
         wf_event.SetErrorInfo(p_event, 'ERROR');
         return 'ERROR';
   
   end handleReceiveEvent;

-- receive
   -- Handle the notification receive events
   -- This will handle the processing of the inbound responses
   -- IN
   -- p_subscription_guid - The RAW GUID of the event subscription
   -- p_event - The WF_EVENT_T containing the event information
   -- RETURN
   -- varchar2 of the status 
   function receive (p_subscription_guid in raw,
                     p_event in out NOCOPY WF_EVENT_T) return varchar2
   is
      l_eventName varchar2(80);
      result varchar2(30);
      erragt    wf_agent_t;
      l_paramlist wf_parameter_list_t;
      errMsg varchar2(4000);
      errStack varchar2(4000);

begin

l_eventName := p_event.GetEventName();
      l_paramList := p_event.getParameterList();

if l_eventName = WF_NTF_RECEIVE_MESSAGE then

result := handleReceiveEvent(p_subscription_guid, p_event);

elsif l_eventName = WF_NTF_RECEIVE_ERROR then
         errMsg := wf_event.getValueForParameter('ERROR_MESSAGE',
                                              l_paramList);
         errStack := wf_event.getValueForParameter('ERROR_STACK',
                                              l_paramList);
         p_event.setErrorSubscription(p_subscription_guid);
         p_event.setErrorMessage(substrb(errMsg,1,4000));
         p_event.setErrorStack(substrb(errStack,1,4000));
         p_event.addParameterToList('ERROR_NAME', 'WF_NTF_RECEIVE_ERROR');
         p_event.addParameterToList('ERROR_TYPE', 'ERROR');

erragt := wf_agent_t('WF_ERROR', wf_event.local_system_name);

--
         -- sjm - lets just call the API directly
         --
         wf_error_qh.enqueue(p_event, erragt);

result := 'SUCCESS';

else

return wf_rule.default_rule(p_subscription_guid, p_event);

end if;

return result;

exception

when others then
         wf_core.Context('WF_XML','Receive',p_event.getEventName(),
                         p_subscription_guid);
         -- Save error message and set status to INVALID so mailer will
         -- bounce an "invalid reply" message to sender.
         wf_event.SetErrorInfo(p_event, 'ERROR');
         return 'ERROR';
   end receive;

-- SummaryRule
   -- To handle the summary notification request event
   -- and call the approapriate summary generate function for
   -- either the role or the member of the role.
   -- IN
   -- p_subscription_guid The RAW GUID for the subscription
   -- p_event The WF_EVENT_T containing the event details
   -- OUT
   -- VARCHAR2 - The status
   function SummaryRule (p_subscription_guid in raw,
                     p_event in out NOCOPY WF_EVENT_T) return varchar2
   is
      l_eventName varchar2(80);
      l_eventkey varchar(80);
      l_paramlist wf_parameter_list_t;

l_event_paramlist wf_parameter_list_t;

CURSOR c_get_summary_roles is
      SELECT /*+ INDEX(WF_NOTIFICATIONS WF_NOTIFICATIONS_N5) */ 
             distinct recipient_role recipient
      FROM   wf_notifications
      WHERE  mail_status is null
      AND    status = 'OPEN'
      AND    Wf_Directory.GetRoleNtfPref(recipient_role) in ('SUMMARY', 'SUMHTML');
       
   begin

l_eventkey := p_event.GetEventKey();
      l_eventName := p_event.GetEventName();
      l_paramList := p_event.getParameterList();

for rec_summary_role in c_get_summary_roles loop

l_event_paramlist := wf_parameter_list_t();
           wf_event.addParameterToList('ROLE_NAME', 
                                        rec_summary_role.RECIPIENT, 
                                        l_event_paramlist);
           -- Set AQs correlation id to item type i.e. 'WFMAIL'
           wf_event.addParameterToList('Q_CORRELATION_ID', 'WFMAIL', 
                                        l_event_paramlist);

wf_event.raise(WF_NTF_SEND_SUMMARY, 
                          rec_summary_role.RECIPIENT||':'||sysdate, 
                          null, l_event_paramlist);
      end loop;

return 'SUCCESS';

exception

when others then
         wf_core.Context('WF_XML','SummaryRule',p_event.getEventName(),
                         p_subscription_guid);
         wf_event.SetErrorInfo(p_event, 'ERROR');
         return 'ERROR';
   end SummaryRule;

-- Parse the XML message and seperate out the main elements
   -- so that a new notification can be constructed including
   -- the information on the previous email.
   procedure getMessageDetails(pmessage IN CLOB, 
                               pnode OUT NOCOPY varchar2, 
                               planguage OUT NOCOPY varchar2,
                               pterritory OUT NOCOPY varchar2,
                               pcodeset OUT NOCOPY varchar2,
                               pcontentBody OUT NOCOPY varchar2, 
                               psubject OUT NOCOPY varchar2, 
                               pFromRole OUT NOCOPY varchar2, 
                               pFromAddress OUT NOCOPY varchar2)
   is
      p xmlparser.parser;
      doc xmldom.DOMDocument;

nl xmldom.DOMNodeList;
      len1 number;
      len2 number;
      n xmldom.DOMNode;
      m xmldom.DOMNode;
      nnm xmldom.DOMNamedNodeMap;

from_node_found boolean := FALSE;
      fromRole varchar2(4000);
      fromAddress varchar2(4000);

node_data varchar2(4000);
      node_name varchar2(4000);
      attrname varchar2(4000);
      attrval varchar2(4000);

subject varchar2(4000);

contentType varchar2(4000);
      textPlain_found boolean := FALSE;

begin
      
      -- new parser
      p := xmlparser.newParser;

-- set some characteristics
      xmlparser.setValidationMode(p, FALSE);

-- parse input file
      xmlparser.parseClob(p, pmessage);

-- get document
      doc := xmlparser.getDocument(p);

-- get all elements
      nl := xmldom.getElementsByTagName(doc, '*');
      len1 := xmldom.getLength(nl);

-- loop through elements
      for j in 0..len1-1 loop
         n := xmldom.item(nl, j);
         node_name := xmldom.getNodeName(n);

if node_name = 'NOTIFICATION' then
            -- get all attributes of element
            nnm := xmldom.getAttributes(n);
   
            if (xmldom.isNull(nnm) = FALSE) then

len2 := xmldom.getLength(nnm);

-- loop through attributes
               for i in 0..len2-1 loop
                  m := xmldom.item(nnm, i);
                  attrname := xmldom.getNodeName(m);
                  if attrname = 'node' then
                     attrval := xmldom.getNodeValue(m);
                     pnode := attrval;
                     -- exit;
                  elsif attrname = 'language' then
                     attrval := xmldom.getNodeValue(m);
                     planguage := attrval;
                  elsif attrname = 'territory' then
                     attrval := xmldom.getNodeValue(m);
                     pterritory := attrval;
                  elsif attrname = 'codeset' then
                     attrval := xmldom.getNodeValue(m);
                     pcodeset := attrval;
                  end if;
   
               end loop;
            end if;
         elsif node_name = 'SUBJECT' then
            n := xmldom.getFirstChild(n);

if ((not xmldom.isNull(n)) and 
                (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then

node_data := xmlDom.getNodeValue(n);
               psubject := node_data;

end if;

elsif node_name = 'FROM' then
            from_node_found := TRUE;

elsif node_name = 'NAME' then
            if from_node_found then
               n := xmldom.getFirstChild(n);

if ((not xmldom.isNull(n)) and 
                   (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then

node_data := xmlDom.getNodeValue(n);
                  pfromRole := node_data;

end if;
            end if;
         elsif node_name = 'ADDRESS' then
            if from_node_found then

from_node_found := FALSE;

n := xmldom.getFirstChild(n);

if ((not xmldom.isNull(n)) and 
                   (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then

node_data := xmlDom.getNodeValue(n);
                  pfromAddress := node_data;

end if;
            end if;
         elsif node_name = 'MESSAGE' then

nnm := xmldom.getAttributes(n);
   
            if (xmldom.isNull(nnm) = FALSE) then

len2 := xmldom.getLength(nnm);

-- loop through attributes
               for i in 0..len2-1 loop
                  m := xmldom.item(nnm, i);
                  attrname := xmldom.getNodeName(m);
                  if upper(attrname) = 'CONTENT-TYPE' then
                     attrval := xmldom.getNodeValue(m);
                     contentType := attrval;
                     exit;
                  end if;
               end loop;
               textPlain_found := upper(contentType) = 'TEXT/PLAIN';
            end if;
            if textPlain_found then
               n := xmldom.getFirstChild(n);

if ((not xmldom.isNull(n)) and 
                   (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then

node_data := xmlDom.getNodeValue(n);
                  pcontentBody := node_data;
               end if;
            end if; 
         end if;
      end loop;

end getMessageDetails;

FUNCTION error_rule(p_subscription_guid in raw,
                    p_event in out nocopy wf_event_t) return varchar2
   is
      nid number;
      param_list wf_parameter_list_t;
      cb varchar2(240);
      ctx varchar2(2000);
      itemType varchar2(8);
      itemKey varchar2(240);
      items pls_integer;
      role varchar2(320);

colPos1 pls_integer;
      colPos2 pls_integer;

error_name varchar2(4000);
      error_msg varchar2(4000);
      error_stack varchar2(4000);

-- Dynamic sql stuff
      sqlbuf varchar2(120);
      tvalue varchar2(4000) := '';
      nvalue number := '';
      dvalue date := '';
      command varchar2(200) :='ERROR';

status varchar2(8);
      l_dummy varchar2(1);
 
    begin
      param_list := p_event.Parameter_List;
      nid := to_number(wf_event.getValueForParameter('NOTIFICATION_ID',
                                                   param_list));
      error_msg := p_event.getErrorMessage;
      error_stack := p_event.getErrorStack;
      begin
         select MESSAGE_TYPE, CALLBACK, CONTEXT, STATUS
         into   itemType, cb, ctx, status
         from   WF_NOTIFICATIONS
         where  NOTIFICATION_ID = nid;

-- If the notification is closed, then do not bother
         -- to process any errors.
         if status = 'CLOSED' then
            return 'SUCCESS';
         end if;

update wf_notifications
         set mail_status = 'ERROR'
         where notification_id = nid;

if ctx is not null then
            colPos1 := instrb(ctx, ':', 1);
            colPos2 := instrb(ctx, ':', -1);
            if colPos1 > 0 and colPos2 > 0 then 
               itemKey := substrb(ctx, colPos1+1, (colPos2 - colPos1) -1);
            else
               itemType := null;
               itemKey := null;
            end if;
         else
            itemType := null;
            itemKey := null;
         end if;

exception
        when others then
           cb := null;
           ctx := null;
           itemType := null;
           itemKey := null;
           return 'SUCCESS';
      end;

-- Check to see if the item type still exists and is not
      -- complete. If it does not exist or is complete, then
      -- do not bother with processing the error.
      if itemType is not null and itemKey is not null then
         begin
            select ACTIVITY_STATUS
            into status
            from wf_item_activity_statuses ias
                 , wf_process_activities pa
           where ias.item_type = itemType
             and ias.item_key = itemKey
             and ias.process_activity    = pa.instance_id
             and pa.process_name = 'ROOT';

-- This will prevent FYI message that are in error being reported.
           -- if status = wf_engine.eng_completed then
           --    return 'SUCCESS';
           -- end if;

exception
            when no_data_found then 
               return 'SUCCESS';
            when others then
               error_msg := sqlerrm;
               wf_core.context('WF_XML','ERROR_RULE','NID['||to_char(nid)||']',
                               'CTX['||ctx||']');
               raise;
         end;
      else
         return 'SUCCESS';
      end if;

if cb is not null then
        -- Put the error onto the stack.
        begin
            wf_core.token('ERROR_MESSAGE', error_msg);
            wf_core.token('ERROR_STACK', error_stack);
            wf_core.raise('WF_ERROR');
        exception
            when others then null;
        end;
        l_dummy := '';
        -- ### cb is from table
        -- BINDVAR_SCAN_IGNORE
        sqlbuf := 'begin '||cb||
                  '(:p1, :p2, :p3, :p4, :p5, :p6, :p7); end;';        
        begin
           execute immediate sqlbuf using
             in command,
             in ctx,
             in l_dummy,
             in l_dummy,
             in out tvalue,
             in out nvalue,
             in out dvalue;
        exception
           when others then
             error_msg := sqlerrm;
             wf_core.context('WF_XML','ERROR_RULE','NID['||to_char(nid)||']',
                             'CTX['||ctx||']');
             raise;
        end;
      end if;

return wf_rule.error_rule(p_subscription_guid, p_event);

end error_rule;

-- Gets the LOB content for a PLSQLCLOB
   -- IN
   -- pAPI the API to call
   -- pDoc The LOB to take the document
   procedure getDocContent(pNid in NUMBER, pAPI in VARCHAR2, 
                           pDoc in out nocopy CLOB)
   is
      colon pls_integer;
      slash pls_integer;
      procname varchar2(240);
      procarg varchar2(32000);

target   varchar2(240) := '_main';
      disptype varchar2(240) := g_ntfDocHtml;
      doctype varchar2(240);

sqlbuf varchar2(2000);

begin

colon := instr(pAPI, ':');
     slash := instr(pAPI, '/');
     if (slash = 0) then
       procname := substr(pAPI, colon+1);
       procarg := '';
     else
       procname := substr(pAPI, colon+1, slash-colon-1);
       procarg := substr(pAPI, slash+1);
     end if;
   
     -- Dynamic sql call to procedure

if (procarg is null) then
        procarg := '-dummy-';
     elsif pNid > 0 then
        procarg := Wf_Notification.GetText(procarg, pNid, disptype);
     end if;

sqlbuf := 'begin '||procname||'(:p1, :p2, :p3, :p4); end;';
     execute immediate sqlbuf using
        in procarg,
        in disptype,
        in out pDoc,
        in out doctype;
   
   end getDocContent;

-- Gets the LOB content for a PLSQLCLOB
   -- IN
   -- pAPI the API to call
   -- pDoc The LOB to take the document
   procedure getBDocContent(pNid in NUMBER, pAPI in VARCHAR2, 
                           pDoc in out nocopy BLOB)
   is
   
      colon pls_integer;
      slash pls_integer;
      procname varchar2(240);
      procarg varchar2(32000);

target   varchar2(240) := '_main';
      disptype varchar2(240) := g_ntfDocHtml;
      doctype varchar2(240);

sqlbuf varchar2(2000);

begin

if (procarg is null) then
        procarg := '-dummy-';
     elsif pNid > 0 then
        procarg := Wf_Notification.GetText(procarg, pNid, disptype);
     end if;

sqlbuf := 'begin '||procname||'(:p1, :p2, :p3, :p4); end;';
     execute immediate sqlbuf using
        in procarg,
        in disptype,
        in out pDoc,
        in out doctype;
   
   end getBDocContent;

-- gets the size of the current LOB table
   function getLobTableSize return number
   is
   begin
      return g_LOBTable.COUNT;
   end;

end WF_XML;
/
-- show errors;
commit;
exit;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de