16 Using the Content Management Event Framework

16.2.1 Enqueuing Messages

CMEF enqueues messages to the WWSBR_EVENT_Q queue, specifying AQ message properties that subscriber applications can then use at dequeue time (Table 16-1).

The WWSBR_EVENT_Q queue is a multiconsumer queue. This enables more than one subscriber to consume a single message, allowing for multiple subscribers without having to make multiple copies of each message. Since all messages in the queue have the same priority, this queue works on a first-in first-out basis.

Each message that CMEF enqueues to the WWSBR_EVENT_Q queue contains a payload that contains information about the portal object to which the event relates, such as page ID or page group ID. Subscribers can use this payload information to perform actions on the portal object. The contents of the CMEF message payload are described in more detail in Section 16.3.6, "CMEF Message Payload".

CMEF enqueues messages for immediate consumption, that is, a message is posted on the WWSBR_EVENT_Q queue as soon as an action occurs in the portal; there is no delay. Messages created by CMEF do not have an expiration time.

16.2.2 Subscribers and Dequeuing Messages

On the consuming end, subscribers have various mechanisms to consume the messages produced by CMEF. Subscribers can process messages as they arrive, and thus must wait for the messages to arrive. Alternatively, subscribers can choose to be notified when the messages arrive. These notifications can be Oracle Call Interface (OCI) callback functions, PL/SQL functions, or even e-mails.

subscriber := sys.aq$_agent('JAY', null, null);
dbms_aqadm.add_subscriber(
  queue_name => 'portal.wwsbr_event_q',
  subscriber => subscriber
);

16.2.2.3 Dequeuing Messages

The operation of retrieving messages from a queue is known as dequeuing (Figure 16-1).

Figure 16-1 The Dequeuing Process

Description of "Figure 16-1 The Dequeuing Process"

You use the DBMS_AQ.DEQUEUE procedure for dequeuing messages from the WWSBR_EVENT_Q queue. Example 16-2 illustrates dequeuing for the subscriber JAY.

Example 16-2 Dequeuing Messages

...
dequeue_options.wait          := dbms_aq.NO_WAIT;
dequeue_options.consumer_name := 'JAY';
dequeue_options.navigation    := dbms_aq.FIRST_MESSAGE;
dequeue_options.dequeue_mode  := dbms_aq.BROWSE;
dbms_aq.dequeue(
  queue_name         => 'WWSBR_EVENT_Q',
  dequeue_options    => dequeue_options,
  message_properties => message_properties,
  payload            => message,
  msgid              => message_handle
);
...

• NAVIGATION: Use the NAVIGATION parameter of the DBMS_AQ.DEQUEUE operation to determine the sequence in which you want to dequeue the messages. The default NAVIGATION parameter for the dequeue request is NEXT_MESSAGE. This means that the subsequent dequeue operation will retrieve the messages from the queue based on the snapshot obtained in the first dequeue. In particular, a message that is enqueued after the dequeue command will be processed only after processing all the messages already enqueued before in the queue. This is sufficient for messages enqueued for the WWSBR_EVENT_Q queue since it does not have priority-based ordering.

  
  

  Note: NEXT_MESSAGE with some delay is the optimal way of processing AQ messages. When the first message in the queue needs to be processed by every dequeue command, subscribers must explicitly use the FIRST_MESSAGE navigation option.

  
  

• DEQUEUE_MODE: A dequeue request can either view a message or delete a message. To view a message, the subscriber can use the BROWSE or LOCK modes. To consume a message, the subscriber can use the REMOVE or REMOVE_NODATA modes. If a subscriber browses a message, the message remains available for further processing. Similarly a locked message remains available for further processing after the subscriber releases the lock by performing a transaction commit or rollback. To prevent a viewed message from being dequeued by a concurrent user, you should view the message in locked mode. After a subscriber consumes a message using either of the REMOVE modes, the message is no longer available for dequeue requests.

  When a subscriber dequeues a message using REMOVE_NODATA mode, the request does not retrieve the payload of the message. This mode is useful when the user has already examined the message payload, possibly by means of a previous BROWSE dequeue. In this way, you can avoid the overhead of payload retrieval that can be substantial for large payloads.

  
  

  Note: One event is enqueued for each subscriber. Thus removing an event from one subscriber's queue does not remove it from the queues of other subscribers.

  
  

• CONSUMER_NAME: A subscriber can dequeue a message from the WWSBR_EVENT_Q queue by supplying this queue name:

  • In PL/SQL you supply the consumer name using the CONSUMER_NAME field in the DEQUEUE_OPTIONS_T record.

  • In OCI you supply the consumer name using the OCISetAttr procedure to specify a text string as the OCI_ATTR_CONSUMER_NAME of an OCI_DTYPE_AQDEQ_OPTIONS descriptor.

  • In Visual Basic (OO4O) you supply the consumer name by setting the consumer property of the OraAQ object.

  Multiple processes or operating system threads can use the same to dequeue concurrently from a queue. Unless the message ID of a specific message is specified during dequeue, the consumers can dequeue messages that are in the READY state.

Note: You should not need to use the Search parameters to dequeue CMEF events.

16.2.3 Exception Handling

A message is considered processed when all intended consumers have successfully dequeued the message. If a message cannot be processed for some reason, it moves to an exception queue.

A message is considered expired if one or more consumers does not dequeue it before the expiration time. Expired messages also move to an exception queue. An exception queue is a repository for all expired or unserviceable messages.

Applications cannot directly enqueue into exception queues. Also, an exception queue cannot have subscribers associated with it. However, an application that intends to handle these expired or unserviceable messages must dequeue from the exception queue.

CMEF exceptions are sent to the WWSBR_EVENT_ERR_Q exception queue. Expired messages from the WWSBR_EVENT_Q multiconsumer queue cannot be dequeued by the intended recipients of the message. However, they can be dequeued in REMOVE mode once by specifying a NULL consumer name in the dequeue options. The queue monitor removes expired messages from multiconsumer queues. This allows dequeuers to complete the dequeue operation by not locking the message in the queue table.

Since the queue monitor removes messages that have been processed by all consumers from multiconsumer queues at regular intervals, users may see a delay between when the messages have been completely processed and when they are physically removed from the queue.

16.2.4 Listening for Messages

Oracle Streams AQ can monitor multiple queues for messages with a single LISTEN call. A subscriber can use LISTEN to wait for messages for multiple subscriptions. It can also be used by gateway applications to monitor multiple queues. If the LISTEN call returns successfully, a dequeue must be used to retrieve the message. Without the LISTEN call, an application which sought to dequeue from a set of queues would have to continuously poll the WWSBR_EVENT_Q queue to determine if there is a message.

Alternatively, you could design your subscriber to have a separate dequeue process for each queue. However, if there are long periods with no traffic in any of the queues, including WWSBR_EVENT_Q, these approaches will create unacceptable overhead. The LISTEN call is well suited for such subscribers. When there are messages for multiple agents in the agent list, LISTEN returns with the first agent for whom there is a message.

You can use the LISTEN call to monitor receipt of messages on one or more queues on behalf of a list of agents. The call takes a list of agents as an argument. You specify the queue to be monitored in the address field of each agent listed. You also must specify the name of the agent when monitoring multiconsumer queues.

declare
  agent_w_msg   aq$agent;
  qlist         dbms_aq.agent_list_t;
begin
  -- MYQ1, MYQ2, MYQ3 are multiconsumer queues in the SCOTT schema.
  qlist(1) := aq$agent('agent1', 'scott.MYQ1', null);
  qlist(2) := aq$agent(null, 'scott.MYQ2', null);
  qlist(3) := aq$agent('agent3', 'scott.MYQ3', null);
  -- Listen with a timeout of 100 seconds.
  dbms_aq.listen(
    agent_list => qlist,
    wait       => 100,
    agent      => agent_w_msg
  );
  dbms_output.put_line('MSG in Q: '||agent_w_msg.address||'for '
    ||agent_w_msg.name);
  dbms_output.put_line('');
end;
/

This is a blocking call that returns when there is a message ready for consumption for an agent in the list. If there are messages for more than one agent, only the first agent listed is returned. If there are no messages found when the wait time expires, an error is raised.

A successful return from the call is only an indication that there is a message for one of the listed agents in one of the specified queues. The interested agent should dequeue the relevant message. Example 16-4 illustrates the dequeue process combined with listening. Here, we dequeue the messages for the subscriber, JAY, for a certain time period.

begin
  agent_list(1)     := sys.aq$_agent('JAY', 'WWSBR_EVENT_Q', null);
  wait_time integer := 100;
  loop
    -- Wait for order status messages.
    dbms_aq.listen(
      agent_list => agent_list,
      wait       => wait_time,
      agent      => agent_w_message
    );
    -- If there are messages for JAY, dequeue them.
    if (agent_w_message.name = 'JAY') then
      dequeue_options.wait          := dbms_aq.NO_WAIT;
      dequeue_options.consumer_name := 'JAY';
      dequeue_options.navigation    := dbms_aq.FIRST_MESSAGE;
      dequeue_options.dequeue_mode  := dbms_aq.BROWSE;
      dbms_aq.dequeue(
        queue_name         => 'item_queue',
        dequeue_options    => dequeue_options,
        message_properties => message_properties,
        payload            => message,
        msgid              => message_handle
      );
    end if;
  end loop;
exception
  when NO_MESSAGES then
    dbms_output.put_line('No more messages for Jay');
end;
/

16.3.1 Creating Subscriber Code

Oracle Streams AQ offers a content-based subscription model. Subscriber applications can specify interest based on message content. You should execute CMEF event subscriber code in the OracleAS Portal schema.

create or replace <procedure name> as
  agent_list         dbms_aq.aq$_agent_list_t;
  wait_time          integer       := <time in seconds>;
  agent_w_message    sys.aq$_agent;
  dequeue_options    dbms_aq.dequeue_options_t;
  message_properties dbms_aq.message_properties_t;
  message_handle     raw(16);
  message            <portal schema>.wwsbr_event;
  l_subscriber       varchar2(30)  := '<subscriber name>';
  l_queue            varchar2(30)  := 'PORTAL.WWSBR_EVENT_Q';
  l_mode             binary_integer := dbms_aq.[BROWSE|LOCK|REMOVE|REMOVE_NODATA];
  ...
  <additional parameters>
  ...
BEGIN
  agent_list(1) := sys.aq$_agent(l_subscriber, l_queue, null);
  loop
    -- Listen for messages.
    dbms_aq.listen(
      agent_list => agent_list,
      wait       => wait_time,
      agent      => agent_w_message
    );
    -- If there are messages for the subscriber then dequeue them.
    if (agent_w_message.name = l_subscriber) then
      dequeue_options.wait          := dbms_aq.NO_WAIT;
      dequeue_options.consumer_name := l_subscriber;
      dequeue_options.navigation    := dbms_aq.[NEXT_MESSAGE|FIRST_MESSAGE];
      dequeue_options.dequeue_mode  := l_mode;
      -- Dequeue messages.
      dbms_aq.dequeue(
        queue_name         => l_queue,
        dequeue_options    => dequeue_options,
        message_properties => message_properties,
        payload            => message,
        msgid              => message_handle
      );
      -- Determine the type of event that occurred and act accordingly.
      ...
      <your code here>
      ...
    end if;
  end loop;
  -- Process cache invalidation messages.
  wwpro_api_invalidation.execute_cache_invalidation;
end;
/

16.3.2 Adding a Subscriber to WWSBR_EVENT_Q

A subscriber subscribes to a queue from where it consumes messages. You have to add a subscriber to the WWSBR_EVENT_Q queue in order to process CMEF event messages.

declare
  subscriber sys.aq$_agent;
begin
  subscriber := sys.aq$_agent('<subscriber>', null, null);
  dbms_aqadm.add_subscriber(
    queue_name => '<portal schema>.wwsbr_event_q',
    subscriber => subscriber
  );
end;
/

16.3.3 Enabling CMEF Events at the Page Group Level

In OracleAS Portal, CMEF is enabled or disabled at the page group level. By default, CMEF is enabled when a user creates a page group, and thus, events are triggered whenever changes occur within the OracleAS Portal user interface or WebDAV.

To enable or disable CMEF for a page group:

1. Go to any page of the page group and switch to Edit mode.

2. In the toolbar at the top of the page, click the Properties link next to Page Group.

   
   

   Note: Make sure you click the link next to Page Group and not the one next to Page (Figure 16-2).

   
   

   Figure 16-2 The Page Group Properties Link on the Edit Mode Toolbar

   
   Description of "Figure 16-2 The Page Group Properties Link on the Edit Mode Toolbar"
   
   

3. Click the Configure tab to bring it forward.

4. In the Content Management Event Framework section you can see whether CMEF is enabled or disabled. If you want to change this setting, click the Edit link.

   Figure 16-3 Status of CMEF for a Page Group

   
   Description of "Figure 16-3 Status of CMEF for a Page Group"
   
   

5. To enable CMEF, select the Enable Content Management Event Framework check box. To disable CMEF, clear the check box.

   Figure 16-4 Enabling or Disabling CMEF for a Page Group

   
   Description of "Figure 16-4 Enabling or Disabling CMEF for a Page Group"
   
   

6. Click OK to save your changes.

7. Click Close to return to the page.

16.3.4 Examining CMEF Events

Use the CMEF Events page (Figure 16-5) to examine the subscribers that have been added to the WWSBR_EVENTS_Q queue.

To access the CMEF Events page:

1. Login to your portal as the portal schema owner.

2. Enter the following URL in the browser Address field:

   http://<host>:<port>/portal/pls/<dad>/<schema>.wwsbr_event_dbg.show
   
   

   • host is the machine on which your portal is installed

   • port is the port used by your portal

   • dad is the Database Access Descriptor (DAD) for your OracleAS Portal installation.

   • schema is the schema in which OracleAS Portal is installed.

Figure 16-5 CMEF Events Page

Description of "Figure 16-5 CMEF Events Page"

16.3.5 Running a CMEF Subscriber

To run a subscriber, issue the command shown in Example 16-7 at a SQL prompt.

begin
  <subscriber procedure>();
end;
/

16.3.6 CMEF Message Payload

Every OracleAS Portal user interface or PL/SQL content management action falls under one of the three main CMEF events: INSERT, UPDATE, DELETE. States provide more meaning to events. For example, there are several types of ADD/INSERT ITEM events:

• Add an item and publish it immediately.

• Add an item and publish it at a later date.

• Add an item that requires approval, because the user has Manage Items With Approval privileges.

For a detailed list of the actions and related events and states, refer to Appendix F, "Content Management Event Framework Events".

Each CMEF event has an associated CMEF message payload as shown in Table 16-2.

16.3.7 OracleAS Portal Actions and CMEF Events

This section describes some of the most common portal actions and shows how to include code in your subscriber to detect these actions. These actions may occur through the OracleAS Portal user interface, the OracleAS Portal PL/SQL APIs, or WebDAV.

For a more detailed list of portal actions and the events and message payloads that they generate, refer to Appendix F, "Content Management Event Framework Events".

if ((message.object_class = 'PAGE') and
    (message.raw_event    = wwsbr_event_q_access.EVENT_INSERT) then
. . .
end if;

if ((message.action       = 'UPDATE_PAGE_GROUP_ACL') and
    (message.object_class = 'PAGE_GROUP') and
    (message.raw.event    = wwsbr_event_q_access.EVENT_UPDATE)) then
. . .
end if;

if ((message.object_class = 'PAGE_GROUP') and
    (message.raw_event    = wwsbr_event_q_access.EVENT_UPDATE)) then
  if (message.action = 'ADD_PAGE_GROUP_ACL') then
    . . .
  end if;
  if (message.action = 'DELETE_PAGE_GROUP_ACL') then
    . . .
  end if;
  . . .
end if;

if ((message.action       = 'ADD_ITEM') and
    (message.object_class = 'ITEM') and
    (message.raw_event    = 'INSERT') and
    (message.state        = 'PUBLISHED')) then
   . . .
end if;

if ((message.action       = 'SUBMIT_ITEM_FOR_APPROVAL') and
    (message.object_class = 'ITEM') and
    (message.raw_event    = 'INSERT') and
    (message.state        = 'NOT_PUBLISHED')) then
   . . .
end if;

-- If an item is approved.
if ((message.action       = 'APPROVE_ITEM') and
    (message.object.class = 'ITEM') and
    (message.raw_event    = 'INSERT') and
    (message.state        = 'PUBLISHED')) then
   . . .
-- If an item has passed an approval step.
elsif ((message.action       = 'APPROVE_ITEM_STEP') and
       (message.object_class = 'ITEM') and
       (message.raw_event    = 'UPDATE') and
       (message.state        = 'PUBLISHED')) then
   . . .
end if;

if ((message.action       = 'EDIT_ITEM') and
    (message.object_class = 'ITEM') and
    (message.raw_event    = 'UPDATE') and
    (message.state        = 'GENERAL') then
   . . .
end if;

if ((message.action       = 'DELETE_ITEM') and
    (message.object_class = 'ITEM') and
    (message.raw_event    = 'DELETE')) then
  . . .
  -- If item is in a page group that does not actually delete items.
  if (message.state = 'MARKED_FOR_DELETE') then
      . . .
  -- If item is in a page group that actually deletes items.
  elsif (message.state = 'PURGED') then
      . . .
  end if;
end if;

16.6.1 Additional Code

Example 16-14 shows the CSBR_UTIL package.

create or replace package CSBR_UTIL as
  function file_viewer_hyperlink(
    p_item_id in number,
    p_caid    in number,
    p_text    in varchar2) return varchar2;
end;

create or replace package body CSBR_UTIL as

  function file_viewer_url(
    p_item_id in number,
    p_caid    in number) return varchar2 is

    l_return      varchar2(10000);
    l_item_name   varchar2(100);
    l_folder_id   number;
    l_folder_name varchar2(100);
    l_portal_url  varchar2(1000);
  begin
    select name, folder_id
    into l_return, l_folder_id
    from portal.wwsbr_all_items
    where id   = p_item_id
    and caid   = p_caid
    and active = 1;
    begin
      while 1=1 loop
        select parent_id, name
        into l_folder_id, l_folder_name
        from portal.wwsbr_all_folders
        where id = l_folder_id
        and caid = p_caid;
        l_return := l_folder_name||'/'||l_return;
      end loop;
    exception
      when NO_DATA_FOUND then
        null; -- Exit loop at no rows found, this is expected.
    END;
    -- Set the Portal URL.
    l_portal_url := 'http://<host>:<port>/portal/page/<dad>/';
    return l_portal_url||l_return;
    exception
      when OTHERS then
        dbms_output.put_line(sqlerrm);
        return 'Error';
  end file_viewer_url;

  function file_viewer_hyperlink(
    p_item_id in number,
    p_caid    in number,
    p_text    in varchar2 return varchar2 is
  begin
    return ('<a href="'||file_viewer_url(
      p_item_id => p_item_id,
      p_caid    => p_caid)||'">'||p_text||'</a>');
  end file_viewer_hyperlink;

end;
/

Example 16-15 shows the html_email procedure.

create or replace procedure html_email(
  p_to      in varchar2,
  p_from    in varchar2 default '',
  p_subject in varchar2,
  p_text    in varchar2 default null,
  p_html    in varchar2 default null
)
is
  l_boundary   varchar2(255) default 'a1b2c3d4e3f2g1';
  l_connection utl_smtp.connection;
  l_body_html  clob := empty_clob; -- This LOB will be the e-mail message.
  l_offset     number;
  l_ammount    number;
  l_temp       varchar2(32767) default null;
begin
  l_connection := utl_smtp.open_connection('<Mail Server here>');
  utl_smtp.helo( l_connection, '<Mail Server here>' );
  utl_smtp.mail( l_connection, p_from );
  utl_smtp.rcpt( l_connection, p_to );
  l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
  l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
  l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
  l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
  l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
  l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' || chr(34) || l_boundary || chr(34) || chr(13) || chr(10);
  ------------------------------------------------------------------------------
  -- Write the headers.
  dbms_lob.createtemporary( l_body_html, false, 10 );
  dbms_lob.write(l_body_html, length(l_temp), 1,l_temp);

  -------------------------------------------------------------------------------
  -- Write the text boundary.
  l_offset := dbms_lob.getlength(l_body_html) + 1;
  l_temp   := '--' || l_boundary || chr(13) || chr(10);
  l_temp   := l_temp || 'content-type: text/plain; charset=us-ascii' || chr(13) || chr(10) || chr(13) || chr(10);
  dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);

  ------------------------------------------------------------------------------
  -- Write the plain text portion of the e-mail.
  l_offset := dbms_lob.getlength(l_body_html) + 1;
  dbms_lob.write(l_body_html, length(p_text), l_offset, p_text);

  ------------------------------------------------------------------------------
  -- Write the HTML boundary.
  l_temp := chr(13) || chr(10) || chr(13) || chr(10) || '--' || l_boundary || chr(13) || chr(10);
  l_temp := l_temp || 'content-type: text/html;' || chr(13) || chr(10) || chr(13) || chr(10);
  l_offset := dbms_lob.getlength(l_body_html) + 1;
  dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);

  ------------------------------------------------------------------------------
  -- Write the HTML portion of the message.
  l_offset := dbms_lob.getlength(l_body_html) + 1
  dbms_lob.write(l_body_html, length(p_html), l_offset, p_html);

  ------------------------------------------------------------------------------
  -- Write the final HTML boundary.
  l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
  l_offset := dbms_lob.getlength(l_body_html) + 1;
  dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);

  ------------------------------------------------------------------------------
  -- Send the e-mail in 1900 byte chunks to UTL_SMTP.
  l_offset := 1
  l_ammount := 1900;
  utl_smtp.open_data(l_connection);
  while l_offset < dbms_lob.getlength(l_body_html) loop
    utl_smtp.write_data(l_connection, dbms_lob.substr(l_body_html,l_ammount,l_offset));
    l_offset := l_offset + l_ammount;
    l_ammount := least(1900, dbms_lob.getlength(l_body_html) - l_ammount);
  end loop;
  utl_smtp.close_data(l_connection);
  utl_smtp.quit( l_connection );
  dbms_lob.freetemporary(l_body_html);
end;
/

16.8.1 Integrating Workflow with OracleAS Portal

Oracle Workflow is a component of Oracle Application Server and Oracle E-Business Suite that enables you to design internal business processes and store them in a central repository. You can use Oracle Workflow to support a wide variety of compliance mandates, designing processes that are both auditable and repeatable, and enforce pre-set approvals and limits. Oracle's newest compliance solution, Oracle Internal Controls Manager (Oracle ICM) works in conjunction with Oracle Workflow to monitor internal business processes and ensure they are performed as designed.

In general, there are five steps involved in integrating Oracle Workflow with the OracleAS Portal content management system for content approval:

1. Enable approvals and notifications in OracleAS Portal.

2. Grant users the Manage Items With Approval privileges.

3. Create a portal user that will be used by the Oracle Workflow process to either approve or reject items and add that user to the portal approval process.

4. Register the workflow process with Oracle Workflow. This workflow process calls your compliance process, or it may perform your actual compliance process.

5. Create subscriber code and add the subscriber to WWSBR_EVENT_Q to process CMEF events. This subscriber initializes the workflow engine and then calls the workflow process.

We can see how this works by modifying the item validation example described in Section 16.7, "Example: Item Validation" to call a workflow process to perform the item validation.

Figure 16-7 Process Flow for Workflow Integration with OracleAS Portal

Description of "Figure 16-7 Process Flow for Workflow Integration with OracleAS Portal"

Let's take a look at how this process would work, based on the diagram in Figure 16-7.

16.8.2 Example Overview

Figure 16-7 shows the process flow diagram for using CMEF to integrate Oracle Workflow with the OracleAS Portal content management system. When a user with Manage Items With Approval privileges adds an item to the portal, that item is marked as pending. Just as with the ITEM_VALIDATION subscriber in Example 16-16, an Oracle Streams Advanced Queuing subscriber is required to listen for events on the WWSBR_EVENT_Q queue (see Example 16-17). If the event is of type SUBMIT_ITEM_FOR_APPROVAL, the subscriber launches the workflow engine process, WF_CHECKURL, and passes the parameters for the portal item to the workflow process. The workflow process calls an external PL/SQL procedure (see Example 16-18) to perform its business logic, which in this case includes either approving or rejecting the portal item.

If the user specifies a URL, then Oracle Workflow approves the item using the wwsbr_api.approve API. Otherwise, Oracle Workflow rejects the item using the wwsbr_api.reject API.

This example assumes the following prerequisites:

• The CMEFSAMPLES schema exists in the same database as the portal schema.

• The Oracle OWF_MGR workflow schema is installed in the same database as the portal schema.

• The AQ_TM_PROCESSES parameter should be set to at least five. To check this, log in to the database as the SYS user and execute the following query:

  select value
  from   v$parameter
  where  name = 'aq_tm_processes';
  
  

  If the value is less than five, then set the value as follows:

  alter system set aq_tm_processes=5;
  
  

• OracleAS Portal 10.1.4 has been installed.

16.8.3 Detailed Example Description

When a portal user with Manage Items With Approval privileges adds an item to a page, a SUBMIT_ITEM_FOR_APPROVAL event is added to the WWSBR_EVENT_Q queue. The item is marked as pending on the portal page. This user's items must be approved before they are made visible on the page.

The Streams Advanced Queuing subscriber CMEF_WORKFLOW listens for events on WWSBR_EVENT_Q. If the event is of type SUBMIT_ITEM_FOR_APPROVAL, then it launches the workflow engine process and passes the parameters for the portal item to the workflow process.

The workflow process WF_CHECKURL performs its business logic, which in this case includes either approving or rejecting portal items.WF_ CHECKURL uses the views and APIs to approve or reject the item. If it approves the item, then an APPROVE_ITEM event is added to WWSBR_EVENT_Q. If it rejects the item, then a REJECT_ITEM event is added to WWSBR_EVENT_Q.

When the user refreshes the page, he or she will either see the item published on the page (if it was approved), or removed (if it was rejected).

The following six steps occur within this example:

1. Enable Approvals and Notifications in OracleAS Portal

2. Grant Users the Manage Items With Approval Privileges

3. Run Scripts Required for the CMEF Workflow Integration Example

4. Create Subscriber and Check Procedures

5. Register the WF_CHECKURL Process with Oracle Workflow

6. Add the CMEF_WORKFLOW Subscriber to the WWSBR_EVENT_Q Queue

Each of these steps is described in more detail in the following sections.

create or replace procedure workflow_approval is
  MIME_TYPE_TEXT      constant varchar2(30) := 'text/plain';
  agent_list          dbms_aq.aq$_agent_list_t;
  wait_time           integer               := 30;
  begin_time          pls_integer;
  end_time            pls_integer;
  agent_w_message     aq$_agent;
  dequeue_options     dbms_aq.dequeue_options_t;
  message_properties  dbms_aq.message_properties_t;
  message_handle      raw(16);
  message             portal.wwsbr_event;
  l_subscriber        varchar2(30)          := 'CMEF_WORKFLOW';
  l_queue             varchar2(30)          := 'PORTAL.WWSBR_EVENT_Q';
  l_mode              binary_integer        := dbms_aq.REMOVE;
  l_attribute_site_id number                := 0;
  l_event             varchar2(30);
  l_doc               wwdoc_api.document_record;
  l_desc              varchar2(4000);
  l_itemkey           varchar2(100);
  l_job_nr            number;
  l_itemtype          varchar2(100)         := 'WF';
  l_wf_process        varchar2(100)         := 'WF_CHECKURL';
  l_is_indirect       boolean               := TRUE;
  l_portal_user_name  varchar2(30)          := 'portal';
  l_portal_password   varchar2(30)          := '<portal password>';
  l_url               varchar2(4000);       := '';

begin
  begin_time := dbms_utility.get_time;
  agent_list(1) := aq$_agent(l_subscriber, l_queue, null);
  loop
    -- Wait for messages.
    dbms_aq.listen(
      agent_list => agent_list,
      wait       => wait_time,
      agent      => agent_w_message
    );
    if (agent_w_message.name = l_subscriber) then
      dequeue_options.wait          := dbms_aq.NO_WAIT;
      dequeue_options.consumer_name := l_subscriber;
      dequeue_options.navigation    := dbms_aq.FIRST_MESSAGE;
      dequeue_options.dequeue_mode  := l_mode;
      dbms_aq.dequeue(
        queue_name         => l_queue,
        dequeue_options    => dequeue_options,
        message_properties => message_properties,
        payload            => message,
        msgid              => message_handle
      );
      -- If the event is an ITEM INSERT event and marked for later publication.
        if ((message.action       = 'SUBMIT_ITEM_FOR_APPROVAL') and
            (message.object_class = 'ITEM') and
            (message.raw_event    = 'INSERT') and
            (message.state        = 'NOT_PUBLISHED') then
          portal.wwctx_api.set_context(l_portal_user_name, l_portal_password);
          -- Get the URL property for the object.
          select url
          into l_url
          from portal.wwsbr_all_items
          where id = message.object_id
          and caid = message.object_site_id;
          -- Get the nextval of the workflow sequence for the itemkey.
          select wf_seq.nextval
          into l_job_nr
          from dual;
          l_itemkey := lpad(to_char(l_job_nr), 5, '0');
          -- Launch the workflow engine process and pass the paramters for 
          -- the portal item to the workflow process.
          wf_engine.createprocess(l_itemtype, l_itemkey, l_wf_process);
          -- Set the value for the portal URL.
          wf_engine.setitemattrtext(l_itemtype, l_itemkey, 'PORTAL_URL', l__url);
          -- Set the value for the portal item ID.
          wf_engine.setitemattrnumber(l_itemtype, l_itemkey, 'PORTAL_ITEM_ID', message.object_id);
          -- Set the value for the portal page group ID.
          wf_engine.setitemattrnumber(l_itemtype, l_itemkey, 'PORTAL_SITE_ID', message.object_site_id);
          -- Start the workflow process.
          wf_engine.startprocess(l_itemtype, l_itemkey);
        end if;
    end if;
    end_time := dbms_utility.get_time;
    if (end_time - begin_time) > 3000 then
      exit;
    end if;
  end loop;
exception
  when OTHERS then
    dbms_output.put_line(sqlerrm);
end workflow_approval;
/

create or replace procedure check_url(
  itemtype  in  varchar2,
  itemkey   in  varchar2,
  actid     in  number,
  funcmode  in  varchar2,
  resultout out varchar2) is

  MIME_TYPE_TEXT      constant varchar2(30) := 'text/plain';
  agent_list          dbms_aq.aq$_agent_list_t;
  wait_time           integer               := 30;
  begin_time          pls_integer;
  end_time            pls_integer;
  agent_w_message     aq$_agent;
  dequeue_options     dbms_aq.dequeue_options_t;
  message_properties  dbms_aq.message_properties_t;
  message_handle      RAW(16);
  message             portal.wwsbr_event;
  l_subscriber        varchar2(30)      := 'CMEF_WORKFLOW';
  l_queue             varchar2(30)      := 'PORTAL.WWSBR_EVENT_Q';
  l_mode              BINARY_INTEGER    := dbms_aq.REMOVE;
  l_attribute_site_id number            := 0;
  l_event             varchar2(30);
  l_doc               wwdoc_api.document_record;
  l_desc              varchar2(4000);
  l_itemtype          varchar2(100)    := 'WF';
  l_itemkey           varchar2(100);
  l_url               varchar2(100);
  l_siteid            number;
  l_itemid            number;
  l_ignore            boolean          := FALSE;
  l_is_indirect       boolean          := TRUE;
  l_portal_user_name  varchar2(30)     := 'portal';
  l_portal_password   varchar2(30)     := '<portal password>');
begin
  -- Get the values for the attributes stored in the procedure.
  l_url := wf_engine.getitemattrtext(itemtype,itemkey,'PORTAL_URL',l_ignore);
  l_itemid := wf_engine.getitemattrnumber(itemtype,itemkey,'PORTAL_ITEM_ID',l_ignore);
  l_siteid := wf_engine.getitemattrnumber(itemtype,itemkey,'PORTAL_SITE_ID',l_ignore);
  -- Set the portal context.
  portal.wwctx_api.set_context(l_portal_user_name, l_portal_password);
  -- If the item type is a URL item and no URL is specified.
  if l_url is null then
    begin
      -- Update the item to indicate that the URL needs to be specified.
      l_desc := '<font color=RED>NO URL SPECIFIED</font>';
      -- Reset the CMEF global variables.
      wwsbr_api.clear_cmef_context;
      wwsbr_api.set_attribute(
        p_site_id           => l_siteid,
        p_thing_id          => l_itemid,
        p_attribute_site_id => l_attribute_site_id,
        p_attribute_id      => wwsbr_api.attribute_description,
        p_attribute_value   => l_desc
        p_log_cmef_message  => FALSE
      );
      -- Reject the item.
      wwsbr_api.reject(
        p_item_id     => l_itemid,
        p_site_id     => l_siteid,
        p_is_indirect => l_is_indirect,
        p_comment     => 'Rejected'
      );
      -- Reset the CMEF global variables.
      wwsbr_api.clear_cmef_context;
    end;
  else
    -- Approve the item.
    begin
      wwsbr_api.approve(
        p_item_id => l_itemid,
        p_site_id => l_siteid,
        p_comment => 'Approved'
      );
      -- Reset the CMEF global variables.
      wwsbr_api.clear_cmef_context;
    end;
  end if;
  commit;
  -- Process cache invalidation messages.
  wwpro_api_invalidation.execute_cache_invalidation;
exception
  when OTHERS then
    dbms_output.put_line(sqlerrm);
end check_url;
/

grant execute on workflow_approval to owf_mgr;
grant execute on check_filename to owf_mgr;

16.8.3.5 Register the WF_CHECKURL Process with Oracle Workflow

The WF_CHECKURL process actually approves or rejects the portal item and sends the e-mail notification. You need to register this process with Oracle Workflow using the Oracle Workflow Builder.

Figure 16-11 The CHECK_FILE Oracle Workflow Process

Description of "Figure 16-11 The CHECK_FILE Oracle Workflow Process"

Use the following steps to install the WF_CHECKURL process into the OWF_MGR schema:

1. Open the Oracle Workflow Builder.

   
   

   Tip: After installing the Workflow Builder, you may have to run it from the command line because you will have to set the ORACLE_HOME environment variable to that of the Workflow Builder. For example: set ORACLE_HOME=c:\oraclewf

   
   

2. Start a new workflow project.

3. Save the workflow project as wf_checkurl.wft.

4. Close the Oracle Workflow Builder.

5. Open wf_checkurl.wft in a text editor, for example, Notepad.

6. Copy and paste the WF_CHECKURL code into the file. You can download this code from Portal Center:

   http://www.oracle.com/technology/products/ias/portal/files/cm_overview_10g1014_cmef_samples.zip
   
   

7. Save the file and close the text editor.

8. Open wf_checkurl.wft in Oracle Workflow Builder.

9. Save wf_checkurl.wft into your portal database using the OWF_MGR schema.

   
   

   Tip: If you do not know the OWF_MGR password, see MetaLink Note 198800.1.

   
   

declare
  subscriber sys.aq$_agent;
begin
  subscriber := sys.aq$_agent('CMEF_WORKFLOW', null, null);
  dbms_aqadm.add_subscriber(
    queue_name => 'portal.wwsbr_event_q',
    subscriber => subscriber
  );
end;
/