Tuesday Nov 13, 2012

Using GMail's SMTP and IMAP servers in Notification Mailer


GMail offers free, reliable, popular SMTP and IMAP services, because of which many people are interested to use it. GMail can be used when there are no in-house SMTP/IMAP servers for testing or debugging purposes. This blog explains how to install GMail SSL certificate in Concurrent Tier, testing the connection using a standalone program, running Mailer diagnostics and configuring GMail IMAP and SMTP servers for Workflow Notification Mailer Inbound and Outbound connections.

GMail servers configuration

SMTP server

Host Name  smtp.gmail.com
SSL Port  465
TLS/SSL required  Yes
User Name  Your full email address (including @gmail.com or @your_domain.com)
Password  Your gmail password

 IMAP server

 Host Name
 SSL Port
TLS/SSL Required
 User Name
 Your full email address (including @gmail.com or @your_domain.com)
 Password Your gmail password

GMail SSL Certificate Installation

The following is the procedure to install the GMail SSL certificate

  • Copy the below GMail SSL certificate in to a file eg: gmail.cer


  • Install the SSL certificate into the default JRE location or any other location using below command
  • Installing into a dfeault JRE location in EBS instance
        # keytool -import -trustcacerts -keystore $AF_JRE_TOP/lib/security/cacerts  -storepass changeit -alias gmail-lnx_chainnedcert -file gmail.cer
  • Install into a custom location

        # keytool -import -trustcacerts -keystore <customLocation>  -storepass changeit -alias gmail-lnx_chainnedcert -file gmail.cer
       <customLocation> -- directory in instance where the certificate need to be installed

  • After running the above command you can see the following response

        Trust this certificate? [no]:  yes
        Certificate was added to keystore

Running Mailer Command Line Diagnostics

  • Run Mailer command line diagnostics from conccurrent tier where Mailer is running, to check the IMAP connection using the below command

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=imap.gmail.com -Dport=993 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=<gmail username> -Dpassword=<password> -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailImapTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

  • Run Mailer command line diagnostics from concurrent tier where Mailer is running, to check the SMTP connection using the below command 

 $AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=smtp.gmail.com -Dport=465 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=<gmail username> -Dpassword=<password> -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailSmtpTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

Standalone program to verify the IMAP connection

Run the below standalone program from the concurrent tier node where Mailer is running to verify the connection with GMail IMAP server. It connects to the GMail IMAP server with the given GMail user name and password and lists all the folders that exist in that account. If the GMail IMAP server is not working for the  Mailer check whether the PROCESSED and DISCARD folders exist for the GMail account, if not create manually by logging into GMail account.

Sample program to test GMail IMAP connection

 The standalone program can be run as below

 $java GmailIMAPTest GMailUsername GMailUserPassword           

Standalone program to verify the SMTP connection

Run the below standalone program from the concurrent tier node where Mailer is running to verify the connection with GMail SMTP server. It connects to the GMail SMTP server by authenticating with the given user name and password  and sends a test email message to the give recipient user email address.

Sample program to test GMail SMTP connection

The standalone program can be run as below

 $java GmailSMTPTest GMailUsername GMailPassword recipientEmailAddress   


  • As gmail.com is an external domain, the Mailer concurrent tier should allow the connection with GMail server
  • Please keep in mind when using it for corporate facilities, that the e-mail data would be stored outside the corporate network

Thursday Aug 30, 2012

Implementing a post-notification function to perform custom validation


Oracle Workflow Notification System can be extended to perform extra validation or processing via PLSQL procedures when the notification is being responded to. These PLSQL procedures are called post-notification functions since they are executed after a notification action such as Approve, Reject, Reassign or Request Information is performed. The standard signature for the post-notification function is

    procedure <procedure_name> (itemtype  in varchar2,
                                itemkey   in varchar2,
                                actid     in varchar2,
                                funcmode  in varchar2,
                                resultout in out nocopy varchar2);


The post-notification function provides the parameter 'funcmode' which will have the following values:

  • 'RESPOND', 'VALIDATE, and 'RUN' for a notification is responded to (Approve, Reject, etc)
  • 'FORWARD' for a notification being forwarded to another user
  • 'TRANSFER' for a notification being transferred to another user
  • 'QUESTION' for a request of more information from one user to another
  • 'QUESTION' for a response to a request of more information
  • 'TIMEOUT' for a timed-out notification
  • 'CANCEL' when the notification is being re-executed in a loop.

Context Variables

Oracle Workflow provides different context information that corresponds to the current notification being acted upon to the post-notification function.

WF_ENGINE.context_nid - The notification ID 

WF_ENGINE.context_new_role - The new role to which the action on the notification is directed

WF_ENGINE.context_user_comment - Comments appended to the notification 

 WF_ENGINE.context_user - The user who is responsible for taking the action that updated the notification's state

WF_ENGINE.context_recipient_role - The role currently designated as the recipient of the notification. This value may be the same as the value of WF_ENGINE.context_user variable, or it may be a group role of which the context user is a member.

WF_ENGINE.context_original_recipient - The role that has ownership of and responsibility for the notification. This value may differ from the value of the WF_ENGINE.context_recipient_role variable if the notification has previously been reassigned. 


Let us assume there is an EBS transaction that can only be approved by a certain people thus any attempt to transfer or delegate such notification should be allowed only to users SPIERSON or CBAKER. The way to implement this functionality would be as follows:

  • Edit the corresponding workflow definition in Workflow Builder and open the notification.
  • In the Function Name enter the name of the procedure where the custom code is handled, for instance, TEST_PACKAGE.Post_Notification
  • In PLSQL create the corresponding package TEST_PACKAGE with a procedure named Post_Notification, as follows:

    procedure Post_Notification (itemtype  in varchar2,
                                 itemkey   in varchar2,
                                 actid     in varchar2,
                                 funcmode  in varchar2,
                                 resultout in out nocopy varchar2) is
      l_count number;
      if funcmode in ('TRANSFER','FORWARD') then
        select count(1) into l_count
        from WF_ROLES
        where WF_ENGINE.context_new_role in ('SPIERSON','CBAKER');
              --and/or any other conditions
        if l_count<1 then
          WF_CORE.TOKEN('ROLE', WF_ENGINE.context_new_role);
        end if;
      end if;
    end Post_Notification;
  • Launch the workflow process with the changed notification and attempt to reassign or transfer it. When trying to reassign the notification to user CBROWN the screen would like like below:

Notification transfer error

Check the Workflow API Reference Guide, section Post-Notification Functions, to see all the standard, seeded WF_ENGINE variables available for extending notifications processing. 

Tuesday Jun 19, 2012

E-Business Suite Proactive Support - Workflow Analyzer


The Workflow Analyzer is a standalone, easy to run tool created to read, validate and troubleshoot Workflow components configuration as well as runtime. It identifies areas where potential problems may arise and based on set of best practices suggests the Workflow System Administrator what to do when such potential problems are found. This tool represents a proactive way to verify Workflow configuration and runtime data to prevent issues ahead of time before they may become of more considerable impact on a production environment.


Since it is standalone there are no pre-requisites and runs on Oracle E-Business applications from 11.5.10 onwards. It is installed in the back-end server and can be run directly from SQL*Plus.

The output of this tool is written in a HTML file friendly formatted containing the following on both workflow Components configuration and Workflow Runtime data:
  • Workflow-related database initialization parameters
  • Relevant Oracle E-Business profile option values
  • Workflow-owned concurrent programs schedule and Workflow components status
  • Workflow notification mailer configuration and throughput via related queues and table
  • Workflow-relevant recommended and critical one-off patches as well as current code level
  • Workflow database footprint by reading Workflow run-time tables to identify aged processes not being purged. It also checks for large open and closed processes or unhealthy looping conditions in a workflow process, among other checks.

See a sample of Workflow Analyzer's output here

Besides performing the validations listed above, the Workflow Analyzer provides clarification on the issues it finds and refers the reader to specific Oracle MOS documents to address the findings or explains the condition for the reader to take proper action.

How to get it?

The Workflow Analyzer can be obtained from Oracle MOS Workflow Analyzer script for E-Business Suite Workflow Monitoring and Maintenance (Doc ID 1369938.1) and the supplemental note How to run EBS Workflow Analyzer Tool as a Concurrent Request (Doc ID 1425053.1) explains how to register and run this tool as a concurrent program. This way the report from the Workflow Analyzer can be submitted from the Application and its output can be seen from the application as well.

Tuesday May 29, 2012

Asynchronous Business Event Subscriptions - Troubleshooting Tips


This blog is intended as an example to help trace an asynchronous business event through it's execution within the Workflow Business Event System. This blog should also help clarify the myth among Oracle Workflow developers and administrators that all business events can be traced through Business Event System AQs.

As a reader of this blog post, it is expected that you understand following steps already.

  • Create a business event using Workflow Administrator Web Applications responsibility
  • Create subscriptions to business events
    • Synchronous subscriptions with phase <= 99
    • Asynchronous subscriptions with phase >100
    • Understand "On Error" subscription attributes such as "Stop and Rollback" and "Skip to Next"

It is strongly recommended that all business events and/or groups that has at least one LOCAL or EXTERNAL subscription should also have one ERROR subscription that launches following seeded error workflow process.

  • Workflow Type - WFERROR
  • Workflow Process - DEFAULT_EVENT_ERROR

If an error subscription is not created event subscription failures may go unnoticed. Create the error subscription as follows.



Sample Local Subscription

For testing purposes, let us assume following PLSQL rule function (in package XXX_PACKAGE) used in a LOCAL event subscription with phase > 99. This will cause the event to be enqueued to WF_DEFERRED queue and Workflow Agent Listener process to execute it in the background. This function inserts records into a temporary table.

    create table temp_table (key_value varchar2(50), result varchar2(20));
    create or replace package xxx_package is
      function subscription1 (p_subscription_guid in raw,
                              p_event in out nocopy wf_event_t) return varchar2;
    end xxx_package;
    create or replace package body xxx_package is
      function subscription1(p_subscription_guid in raw,
                           p_event in out nocopy wf_event_t) return varchar2 is
        l_result varchar2(20);   
        l_result := p_event.GetValueForParameter('OUTCOME');
        if l_result='GOOD' then
          insert into temp_table values (p_event.getEventKey(), l_result);
          return 'SUCCESS';
          insert into temp_table values (p_event.getEventKey(), l_result);
          wf_core.context('xxx_package','function subscription1', p_event.getEventName(), p_event.getEventKey());
          wf_event.setErrorInfo(p_event, 'ERROR');
          return 'ERROR';
        end if;
        when others then
          wf_core.context('xxx_package','function subscription1', p_event.getEventName(), p_event.getEventKey());
          wf_event.setErrorInfo(p_event, 'ERROR');
          return 'ERROR';
      end subscription1;
    end xxx_package;
IMPORTANT NOTE: Return value should be in UPPERCASE, otherwise the Business Event System will not recognize the result.

Test the business event

Write a PLSQL procedure to create and set the event parameters and then raise it. 

      l_event_name varchar2(50) := 'oracle.apps.fnd.wfds.user.userUpdated';
      l_event_key varchar2(50) := to_char(sysdate, 'DD-MM-RRRR HH:MI:SS');
      l_attributes wf_parameter_list_t;
      --Add the logic to be executed when the event occurs. In this case the
      -- creation of a user was signaled.
      -- ...
      --Add all the parameters to the list:
      --WF_EVENT.AddParameterToList('OUTCOME', 'WRONG', l_attributes);
      WF_EVENT.AddParameterToList('OUTCOME', 'GOOD', l_attributes);
      -- Raise the event
      WF_EVENT.Raise(p_event_name => l_event_name,
                     p_event_key => l_event_key,
                     p_parameters => l_attributes);

When this block runs with parameter OUTCOME set to 'GOOD', a record is inserted into TEMP_TABLE by the LOCAL subscription's rule function. When a different value is used the function returns ERROR and a record is insert into the temporary table. But since the result is ERROR, the transaction is rolled back and error subscription is executed. The Business Event System uses the error subscription to launch WFERROR:DEFAULT_EVENT_ERROR process to send error notification to SYSADMIN with the details of the exception. These error details shown on the notification are as a result of the calls to WF_EVENT.SetErrorInfo and WF_CORE.Context in the subscription rule function.


Troubleshooting Tips

  • Only Busienss Events with asynchronous subscriptions are enqueued to WF_DEFERRED or WF_JAVA_DeFERRED queues based on whether the subscription is PLSQL based or Java based. All events with synchronous subscriptions are executed in the same thread they are raised in and not enqueued to any AQs.
    NOTE: The event's synchronous subscriptions themselves may enqueue the events to some Out Agents such as WF_JMS_OUT or WF_WS_JMS_OUT and so on which is purely specific to subscription's implementation and not related to core Business Event processing.
  • From Workflow Manager, ensure the Workflow Deferred Agent Listener and the Workflow Java Deferred Agent Listener components are running
  • Ensure an ERROR subscription is created for the business event
  • To trace a business event with Asynchronous Subscription from Raise to Dispatch to Error
    • Ensure an error subscription was created as in the sample above
    • Stop Workflow Deferred Agent Listener and Workflow Error Agent Listener
    • Raise the event
    • Verify the event is enqueued to WF_DEFERRED using below SQL. Assuming the event key is unique across all occurences, it should return 1
      select count(1) from applsys.aq$wf_deferred a where a.user_data.event_name = '&eventName' and a.user_data.event_key = '&eventKey' and a.msg_state = 'READY';
    • Start Workflow Deferred Agent Listener and after few minutes verify the event is processed successfully using above SQL. The count should be 0
    • If the event was not dispatched successfully, check if the event is enqueued to WF_ERROR queue using SQL. If the event errored, it should be in error queue. If this SQL returns 0, the subscription was executed successfully. select count(1) from applsys.aq$wf_error a where a.user_data.event_name = '&eventName'and a.user_data.event_key = '&eventkey'and a.msg_state = 'READY'
    • Start Workflow Error Agent Listener. If there was a record in WF_ERROR queue, after few minutes verify the event is processed successfully using above SQL
    • Now check SYSADMIN's worklist for error notification with details of the error.
  • To obtain E-Business Debug Log messages for business event processing
    • Enable Log profile options for the E-Business Suite user whose session triggers the business event.
      FND: Debug Log Enabled=YES
      FND: Debug Log Level=STATEMENT
      FND: Debug Log Module=wf.plsql%
    • In the case where the event is raised from a PLSQL block you can add the following at begining of the procedure to identify the session and to activate the debug profile options.



        fnd_global.apps_initialize(user_id=>0, resp_id=>24240, resp_appl_id=>1);



      fnd_global.apps_initialize will enable the profile options for this PLSQL block for user SYSADMIN, application 'System Administration' and responsibility 'System Administrator'
    • Then query table APPLSYS.FND_LOG_MESSAGES where column MODULE like 'wf.plsql%' to see the debug messages
      sqlplus apps/***** @$FND_TOP/patch/115/sql/wfdbgprnt wf.plsql%


Oracle Workflow API Reference, section 'Event Subscription Rule Function APIs'

Oracle Workflow Developer's Guide, section 'Error Handling for Event Subscription Processing'

Wednesday May 09, 2012

Oracle E-Busienss Suite RCD - Applications Technology Releases 12.1 and 12.2

Oracle E-Business Suite Release Content Documents for Releases 12.1 and 12.2 are available on MOS. The Release Content Documents (RCDs) communicate information about new or changed functionality introduced in Oracle E-Business Suite Release 12.1 and in subsequent Release Update Packs (RUPs) and off-cycle patches, as well as planned enhancements for the upcoming Release 12.2. For your convenience, they also include new or changed functionality introduced in the RUPs for Release 12, including 12.0.2 through 12.0.7.

Release Content Document - Applications Technology Releases 12.1 and 12.2

Oracle Workflow introduces following new features in Releases 12.1 and 12.2

Release 12.1.1

  • Worklist - Bulk Notification Response and Bulk Notification Close
  • Hijrah/Thai Calendar Support

Release 12.1.3

  • Notification Mailer - SMTP Authentication

Release 12.2

  • E-mail Character Encoding Configuration 
  • Deferring Worklist Response Processing
  • Oracle RAC Affinity for Workflows
  • Worklist Usability Enhancements 

Tuesday Apr 17, 2012

SMTP Authentication Feature in R12.1.3


Oracle E-Business Suite Workflow Notification Mailer leverages the functionality of SMTP and IMAP services to send and receive notification and alert emails respectively. Until E-Business Suite Release 12.1.3, Workflow Notification Mailer supports authentication for only IMAP server connections. Starting Release 12.1.3, Workflow Notification Mailer supports authentication for SMTP servers.

Main reasons to support authentication for SMTP servers are,

  1. An attacker could hijack the SMTP connection either pretending the server does not support the Authentication extension or causing all AUTH commands to fail.
  2. A SMTP server accessible over public domain could be misused by spammers to hide their identify and send spam e-mails.

How to configure

  • Go to Workflow Manager Screen and navigate to Workflow Notification Mailer page.
  • Edit the Workflow Mailer configuration, update "SMTP user" and "SMTP Password" parameters, SAVE and bounce the Workflow Service Container.

Authentication Mechanisms

    Workflow Mailer supports PLAIN, LOGIN and CRAM-MD5 mechanisms with JavaMail version 1.4.

How to check

On a non-SSL enabled SMTP server, it can be checked easily: 

$ - telnet smtp.host.com 25
Connected to smtp.host.com (
Escape character is '^]'.
220 smtp.host.com ESMTP Sendmail 8.13.8/8.13.8; Tue, 17 Apr 2012 10:11:36 -0400
EHLO smtp.host.com
250-smtp.host.com Hello my.desktop.com [], pleased to meet you
250 HELP

Wednesday Aug 04, 2010

Configurable User LOV in Worklist UI

User LOV is a UI component that exposes Workflow Directory Services to end-users in Worklfow pages to select a user or a role while performing actions on a notification such as Reassign, Request Information or setting up rules such as Vacation Rules or Worklist Access. By default the User LOV has always provided complete access to all the users and roles in the Directory Services to the end-users regardless of where they belong.


Oracle Workflow has now released a new feature that uses AOL's Data Security features to provide an option for Customers to configure the User LOV to stripe the data as per current user context. This allows certain users to see only certain group of users in the User LOV while accessing workflow notification pages. More information about how to use this feature is available in below My Oracle Support document.

Configuring the Oracle Workflow User List of Values, Release 12.0.6 and Release 12.1.1

Tuesday Aug 03, 2010

Oracle Business Event and Subsciptions Execution Flow

This blog post contributed by Shivdas Tomar.

Please read earlier post about Workflow Business Event System.

We will see the exact execution flow when an event is raised using a PLSQL API and a Java API.

Event raised using PLSQL API

As soon as event is deferred to WF_DEFERRED or WF_JAVA_DEFERRED, control is returned back to calling program. Workflow Deferred Agent Listener and Workflow Java Deferred Agent Listener continues execution of the subscriptions respectively from these queues. If there is any error during subscription or generate function execution, event will be enqueued to WF_ERROR or WF_JAVA_ERROR queue respectively. The Error Agent Listeners then execute error subscriptions associated to the event.


Event raised using Java API

Once event enqueued to WF_JAVA_DEFERRED queue, further execution will be resumed from the deferred subscription, by Workflow Deferred Java Agent Listener. If any error occurs during processing of a subscription by the Deferred Java Agent Listener, event is enqueued to WF_JAVA_ERROR queue.



Understanding AQs in Workflow

This blog post contributed by Shivdas Tomar.

In Oracle Workflow, Advanced Queues are used for inter-process communication, background processing, sending messages to external agents and so on. Many times the messages may not be dequeued by the consumers such as Workflow Agent Listeners or Workflow Background Engines.


  • Workflow Agent Listeners or Background engines are not able to dequeue messages in READY state.
  • Messages continue to be in WAIT state even after passing the "delay" interval
  • Processed messages are not purged after passing the "retention" interval
  • Queue propagation is not happening


Always check AQ view (AQ$<Queue_Table_Name> for message state and not the under lying table directly.

  • If messages are in READY state but consumers such as Workflow Agent Listeners and Workflow Background Engines are not able to dequeue, then there might be issues with message recipients or AQ subscribers. Check the CONSUMER_NAME and ADDRESS columns value in that AQ and then the subscriber rule sets.
  • If event messages are not moving from one state to another, check AQ_TM_PROCESSES parameter's value to be greater than 0.
Note: In 10gR1 or later database releases, the AQ time management and many other background processes are automatically controlled by a coordinator-slave architecture called Queue Monitor Coordinator (QMNC). QMNC dynamically spawns slaves named qXXX depending on the system load.
If you want to disable the Queue Monitor Coordinator, then you must set AQ_TM_PROCESSES = 0 in your pfile or spfile. Oracle strongly recommends that you DO NOT set AQ_TM_PROCESSES = 0. If you are using Oracle Streams, setting this parameter to zero (which Oracle Database respects no matter what) can cause serious problems. So either remove AQ_TM_PROCESSES parameter or do not set its value as zero.
  • Check if processes, named like 'qmnc' or 'ora_qxxx' are running at the OS level. If there are no such process(es) running then bounce the Oracle Database after removing AQ_TM_PROCESSES or changing its value to a non-zero value as recommended in Oracle Applications setup documentation.

  • If Queue propagation is not happening, job_queue_processes parameter's value should be checked.
Note: In 11gR1 or later database releases, the init.ora parameter JOB_QUEUE_PROCESSES does not need to be set for AQ propagations. AQ propagation is now handled by DBMS_SCHEDULER jobs rather than DBMS_JOBS.
Propagation takes advantage of the event based scheduling features of DBMS_SCHEDULER for better scalability. If the value of the JOB_QUEUE_PROCESSES database initialization parameter is zero, then that parameter does not influence the number of Oracle Scheduler jobs that can run concurrently. However, if the value is non-zero, it effectively becomes the maximum number of scheduler jobs and job queue jobs that can run concurrently. If a non-zero value is set, it should be large enough to accommodate a scheduler job for each Messaging Gateway agent to be started.

SSL in Oracle Workflow


This topic is created to give better understanding of how Oracle Workflow uses SSL in different modules and if in case of an issue how to troubleshoot it.

Secure Sockets Layer (SSL)

SSL is a technology that defines the essential functions of mutual authentication, data encryption, and data integrity for secure transactions. Exchange of data between the client and server in such secure transactions is said to use the Secure Sockets Layer (SSL). SSL uses 2 types of Certificates:

  • User certificates - These are Certificates issued to servers or users to prove their identity in a public key/private key exchange.
  • Trusted certificates - These are Certificates representing entities whom you trust - such as certificate authorities who sign the user certificates they issue.

Read more information in MOS Doc Enabling SSL in Oracle Applications Release 12.

Oracle Workflow as SSL Client

Oracle Workflow modules act as a HTTP/SSL client in different scenarios connecting to the EBS or non-EBS SSL servers. For SSL/TLS connection, the Workflow's client process should have access to the following.

  • Necessary SSL libraries (mostly available)
  • Trusted certificates (ca.crt) used to validate if the server certificate is valid.
  • Client certificates (if client authentication required).

The Key/Trust Store accessible to the Workflow process should have the correct certificates for the client code to participate in SSL handshake with the server. In summary, the SSL client should be able to validate the SSL server certificate's authenticity using it's root certificate and exchange cipher suites with the server.

Workflow as SSL Client

When troubleshooting SSL issues with Workflow, it is important to understand in detail as to where exactly each Workflow's HTTP client process executes so the necessary setup can be verified.

Workflow Manager UI

Workflow Notification Mailer is configured from Oracle Applications Manager >> Workflow Manager screens. When configuring IMAP and SMTP servers with SSL Enabled option checked, the Workflow Manager code attempts to connect to the IMAP and SMTP servers over SSL to validate connectivity before saving the configuration parameters. Since the OAM UI executes within OACORE OC4J container, it would use $OA_JRE_TOP/bin/java. The root certificate in the JRE's store should correspond to the Server Certificates on IMAP and SMTP servers in order for the connection to succeed.

Workflow Notification Mailer

Mailer executes within the Concurrent Manager process in the CM tier. The Java run-time used to run Mailer Service is configured as $AF_JRE_TOP/bin/java. If SSL is enabled, Mailer initiates SSL connection for following three reasons.

  • SMTP server - Establish SMTP connection to send e-mails.
  • IMAP server - Establish IMAP connection to receive e-mails.
  • EBS or non-EBS web server -Establish HTTP connection to a Web server to fetch OAF content or if images are to be embedded, connect to a content server.

Workflow Status Monitor

When Status Monitor page is loaded, there are two separate actions.

  • Loading of the OAF page first
  • Then loading of the Monitor Applet within that above OAF page that shows the diagram

Status Monitor makes HTTP requests during both actions above.

  • OAF controller GraphMonitorCO.java - When status monitor diagram page is loaded, this OAF controller code runs within OC4J? and it acts as HTTP client making a loop back request to Web server to fetch tags to embed the Status Monitor applet. If any exception occurs while loading the status monitor diagram page, it will result in OAF page error. OC4J runs using JRE at $OA_JRE_TOP/bin/java.
  • Monitor Applet - The monitor applet code running in Web Browser JVM (JInitiator or Sun JRE plugin) makes HTTP requests to fetch data to display diagram on the applet. The applet loads only after the status monitor page loads successfully above in (a). Any exceptions within the applet can only be tracked through Java console output on the browser.

Workflow Business Event System

From R12.1, Business Event System supports invoking web services. This includes following steps.

  • Consuming the WSDL - WSDL is consumed in a OAF page to create web service meta-data. The controller makes HTTP(S) request to the WSDL URL. In order for the OAF page to successfully connect to a HTTPS WSDL URL, the OC4J JVM should have access required SSL libraries and root certificate installed.
  • Invoking the web service - Invocation of the earlier consumed web service may occur in one of the following two processes.
    • OC4J - If the web service is invoked from a OAF page using synchronous subscription, then the OC4J process acts as SSL client. Like any OAF page, the process runs using $OA_JRE_TOP/bin/java
    • Concurrent Manager - If the web service is invoked using a asynchronous subscription, it is executed by Java Deferred Agent Listener in Agent Listener Service process. Like Workflow Mailer Service, this runs using $AF_JRE_TOP/bin/java

When there are issues...

In summary, Workflow's SSL client code executes in following run-time environments

  • $OA_JRE_TOP/jre/bin/java (Web Tier)
  • $AF_JRE_TOP/jre/bin/java (Concurrent Tier)
  • JInitiator
  • Sun JRE

For any SSL handshake errors involving Workflow code as client,

  1. Always verify that the JVM from which Workflow code initiates a SSL connection has the required root certificate installed
  2. If the server presents a certificate chain to validate, then the complete chain is installed on the client side.
  3. Most importantly, as part of SSL enablement of EBS, is the trusted certificate/certificate chain installed into internal EBS JVMs that could potentially act as SSL client to our own EBS servers.

How to check SSL connectivity?

SSL connectivity can be verified from the run-time environment where Workflow acts as client to a SSL server to confirm if the setup is correct. This helps troubleshoot general SSL setup without involving Workflow code.

For example, for Status Monitor SSL issues,

  1. GraphMonitorCO.java - Check connectivity from $OA_JRE_TOP/bin/java by using this JRE's trust-store to the web-server.
  2. Status Monitor Applet - Check connectivity from client machine based on appropriate run-time such as Sun JRE or JInitiator. For JInitiator, the certificates are stored under <JInitiator Home>\lib\security\certdb.txt. Java run-time is accessible using <JInitiator Home>\bin\java.exe

For connectivity testing following can help.

  1. openssl utility available in Unix based platforms
  2. This sample 
    class can be used to test a handshaking from the Java run-time
    1. Download the Java class source code in a directory. There is no package name for this Java class.
    2. Compile SSLSocketClientTest.java as
      javac -classpath $CLASSPATH SSLSocketClientTest.java
    3. Run it as below from required Java run-time
      java -classpath . -Dserver=server.com -Dport=443 -Dtruststore=<jre/lib/security /cacerts> SSLSocketClientTest

How to update the JDK Cacerts File?

These steps are mentioned as part of EBS SSL setup MOS Doc Enabling SSL in Oracle Applications Release 12.

  1. Navigate to the $OA_JRE_TOP/lib/security directory
  2. Backup the existing cacerts file.
  3. Copy your ca.crt and server.crt files to this directory. Issue the following command to insure that cacerts has write permissions. 
  4.    chmod u+w cacerts
  5. Add your Apache ca.crt and server.crt to cacerts
  6. keytool -import -alias ApacheRootCA -file ca.crt -trustcacerts -v -keystore cacerts
    keytool -import -alias ApacheServer -file server.crt -trustcacerts -v -keystore cacerts
  7. When prompted enter the keystore password (default password is changeit).

Certificate Chains

A certificate chain establishes as chain of trust. The certificate issued by a CA is not signed by their own root certificate but is signed by another CA's root certificate. For example, VeriSign is the most common CA whose user certificates that all the web browsers trust. This is because, the web browsers are pre-installed with VeriSign's root certificate. If another CA XyZ issues a certificate signed using VeriSign's root certificate, then the browser can trust the certificate from XyZ simply because the root certificate is issued by CA.

The chain of trust is

VeriSign's Root CA Certificate >> XyZ's Intermediate CA Certificate >> Server Certificate

There must be a chain of trust from the server certificate up through intermediate authorities up to one of the trusted Root Certificate in order for the server to be trusted. If the client is unable to build the chain of trust starting from the server certificate up to a trusted Root Certificate, then the SSL handshake fails with X509CertChainIncompleteErr.

How to rectify this?

Concatenate all the certificates in the chain into one single file as per the order in which they appear in the chain. Server Certificate should be the first one in the chain and followed by the intermediate certificates and finally the root certificate. You can verify this order and download the certificates using a Web browser. Import the concatenated certificate into the JDK from which the Workflow's code acts SSL client.


It is just a matter of establishing trust between the client and the server. Does the client have access to the certificates to trust the server?

Leveraging Oracle Workflow for Declarative PageFlow

This blog post contributed by Dilbagh Singh.

Oracle Workflow can be leveraged in Oracle Applications Framework for designing declarative Page Flow, which is used to handle conditional navigation rules.This is a better way to handle page navigations if you would want to avoid static, complex logic in the page controllers to handle different navigation scenarios. This requires a user to create a Pageflow workflow definition which is associated with the OA Framework based pages. Then there are certain APIs which are used in transition flow of the workflow. I would like to focus on creating a Workflow Definition for the page flow and how to interact with it as the transaction proceeds.

1. Create the OA Framework pages
Create the OA Framework pages you would want the page flow to consist of.

2. Design the Workflow Definition for the Page Flow
Define the Workflow in the workflow builder following the below mentioned guidelines:

  • Add a function activity for each page in the transaction. For each activity, decide whether to:

    • Associate the result with this activity (for conditional navigation) OR

    • Explicitly mark the activity as blocked by associating a Blocking Function (wf_standard.block), a PLSQL function with it

      • When the Workflow Engine encounters a blocking activity it stops and waits for some sub-process or external entity to provide the information it needs to proceed. So, with the mapping between transaction pages and the blocking activities in the Workflow, you can query the activity data to find out what page ought to be rendered.

  • Add a FORM attribute to each page-related blocking activity. The FORM attribute would have a value which specifies the next page to be traversed.

3. Start the Page Flow

  • You can start the page flow from a OAF page directly or upon triggered by some event or can start it from a static page by using the API OANavigation which provides createProcess() and startProcess()

  • You can then transition through the workflow by using getNextPage() from OANavigation API. There are different overloaded getNextPage APIs for transitioning through workflow, transitioning to next page, resuming a saved transaction etc. You can get the code snippets in the OAF Dev guide.

4. Clear the Workflow Context
  • Since the Workflow transaction holds an independent local JDBC connection, you must override the OADBTransaction.beforePoolCheckin() method in your root UI application module and call getDBTransaction.clearWorkflowInfo() to release the connection.

Some More features:

  1. Workflow-based page flows automatically support browser Back button navigation at the Workflow technology layer. Whenever user moves between the pages using browser Back button and resubmits, the workflow rewinds itself to the appropriate blocking activity so it stays in synch with the user's current navigation position.

  2. Workflow Page Flow can be used with Train and Navigator bar in a page, which the traditional 'Destination Function' based approach does not support.

  3. For the most part, you handle application errors in your Workflow-based page flows the same way that you do in any other context. That said, however, you need to consider the possibility that the user might fix mistakes and resubmit the form whenever you introduce workflow code.

Gathering Stats for Workflow's Queue Tables

This blog post contributed by Ajay Verma.

Statistics gathering is an important activity in order to enable the cost based optimizer to take a decision on the execution plan to choose from the various possible ones with which a particular SQL statement can be run. It is recommended to gather the statistics for an object when it is in steady state, i.e. when the corresponding tables have a fair amount of data giving out statistics which would lead the best execution plan to be picked by CBO.

From 10g onwards , Oracle database introduced Automatic Optimizer Statistics Collection feature in which a scheduled job GATHER_STATS_JOB runs in the nights and gathers stats for tables with either empty or stale statistics using DBMS_STATS  package APIs. This indeed is a great feature but for highly volatile tables such as AQ/Streams tables  it is quite possible that when the stats collection job runs, these tables may not have the data that is representative of their full load period. One scenario can be stats collection on queue tables when they were empty, and the optimizer, as a result, choosing poor execution plans when they have a lot of data in them during normal workload hours.

As a direct fix to tackle the above issue, from 10gR2 database onwards, AQ table stats are getting locked immediately after its creation so that the auto stats collector doesn't change it in the future. This can be confirmed as below:

SQL> CREATE TYPE event_msg_type AS OBJECT   
                  (  name            VARCHAR2(10), 
                     current_status  NUMBER(5),  
                     next_status     NUMBER(5)  
Type created.
--Create Queue Table
SQL> EXEC DBMS_AQADM.create_queue_table(
PL/SQL procedure successfully completed. 
--Create Queue
SQL> EXEC DBMS_AQADM.create_queue(queue_name =>'test_queue',
PL/SQL procedure successfully completed.
--Check for locks on stats
SQL> select owner, table_name, stattype_locked
     from dba_tab_statistics
     where stattype_locked is not null
     and owner='APPS' and table_name='TEST_QUEUE_TAB';
---------- ---------------- ----- 
--Gather stats
SQL> EXEC dbms_stats.gather_table_stats('APPS','TEST_QUEUE_TAB')
BEGIN dbms_stats.gather_table_stats('APPS','TEST_QUEUE_TAB'); END; 
ERROR at line 1: 
ORA-20005: object statistics are locked (stattype = ALL) 
ORA-06512: at "SYS.DBMS_STATS", line 18408 
ORA-06512: at "SYS.DBMS_STATS", line 18429 
ORA-06512: at line 1

As we can see above , queue table stats are locked and hence further stats gathering fails. We can still go ahead and use force parameter provided in DBMS_STATS package to override any lock on statistics:

SQL>exec dbms_stats.gather_table_stats('APPS','TEST_QUEUE_TAB'
PL/SQL procedure successfully completed.

The recommended use is to gather statistics with a representative queue message load and keep the stats locked to avoid getting picked up by auto stats collector.

What happens in Apps Instance?

From Oracle Apps 11i  (on top of Oracle Database 10gR2) instance onwards, the automatic statistics gathering job GATHER_STATS_JOB is disabled using $APPL_TOP/admin/adstats.sql , as can be confirmed below:

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS 
JOB_NAME                       ENABLE
------------------------------ ------

Oracle Apps provides separate concurrent programs which make use of procedures in FND_STATS package to gather statistics for apps database objects. FND_STATS is basically a wrapper around DBMS_STATS and is recommended by Oracle for stats collection in Oracle Apps environment because of the flexibility it provides in identifying the objects with empty and stale stats. The modification threshold ( % of rows used to estimate) is fixed to 10% in DBMS_STATS while it can vary from 0 to 100% incase of FND_STATS.

But FND_STATS doesn't provide the force parameter to override any lock on statistics and hence fails while gathering statistics for a queue table whose stats are locked :

SQL> select owner, table_name, stattype_locked ,last_analyzed
  2   from dba_tab_statistics where table_name='WF_DEFERRED'
  3  and owner='APPLSYS';
-------------- --------------- ----- ---------
APPLSYS        WF_DEFERRED     ALL   23-FEB-09
SQL> exec fnd_stats.gather_table_stats('APPLSYS','WF_DEFERRED');
BEGIN fnd_stats.gather_table_stats('APPLSYS','WF_DEFERRED'); END;
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "APPS.FND_STATS", line 1505
ORA-06512: at line 1	

The same error is observed on running the Gather Table Statistics concurrent program for such queue tables. Gather Schema Statistics concurrent program in general skips the table incase any error is encountered while gathering its stats and hence the queue tables with locked stats are also skipped.

Hence currently ,  in order to carry out stat collection for Workflow queue tables whose stats are locked,the approach should be to unlock the queue table temporarily using DBMS_STAT's Unlock APIs when the table have a representative load for the correct stats to be gathered, gather the statistics using FND_STATS and lock it again.

SQL> begin
  2    dbms_stats.unlock_table_stats('APPLSYS','WF_DEFERRED');
  3    fnd_stats.gather_table_stats('APPLSYS','WF_DEFERRED');
  4    dbms_stats.lock_table_stats('APPLSYS','WF_DEFERRED');
  5  end;
  6  /
PL/SQL procedure successfully completed.
SQL> select owner, table_name, stattype_locked ,last_analyzed
  2  from dba_tab_statistics where table_name='WF_DEFERRED'
  3  and owner='APPLSYS';
---------------- ---------------- ----- -------------
APPLSYS          WF_DEFERRED      ALL   12-APR-09	

Another approach can be to unlock the WF queue table stats during the adpatch run itself as Oracle's automatic stats collection feature is disabled in Apps environments and the stats collection in general occurs through the Apps provided concurrent programs. What say?

Monday Aug 02, 2010

Looping within a Workflow Process

This blog post contributed by Ajay Verma.

While designing a workflow process there seems to be a general tendency to make the process as granular as possible. Each steps are broken into several simpler steps, which are executed as part of separate workflow activities. It does give a much finer understanding of the business processes to the end user.But sometimes this drive to 'make it simpler' can get us into different kinds of complexities.

Lets see this with one particular example:

Below is a simplified version of a workflow process part we came across recently:


      Fig : Process 1

Here, notifications were being sent using workflow looping wherein completion of an activity causes a transition to another activity that has already been completed earlier. 'Get Recipient' activity in this particular case was determining the suitable notification recipient based on certain business rules while 'Send Notification' activity simply made use of Notification System's Send() API to send the notification to the identified recipient. The loop ended when no more eligible recipient were found.

Two issues were observed in this particular set up:

  1. The process taking too long a time (>40 hours) to complete when notifications were being sent to around 35K users.
  2. The rate at which notifications were being created decreased as the time passed, i.e initially when the process started , around 12-16 notifications(for the respective recipient) got created per second and later this rate got reduced to one notification in 2 minutes.

The issue was reproducible in a separate instance for say 2500 notifications with same observations as mentioned above.It took more than 3 hours for the process to complete and the rate of notification creation did decreased with time.

Now see the below process extract wherein the two activities in Process 1 have been merged into a single activity:


      Fig : Process 2


The activity simply does the job being carried out by those in Process 1 but within a PL/SQL loop rather than the workflow loop:


Loop Starts

    Get Recipient Logic

    Send Notification to the identified recipient

Loop ends


The process 2 completed for 2500 notifications in less than 2 minutes ! And there was no noticeable performance degradation in the rate at which notifications were created.

So , what made the difference?

In general , internally for each execution of a workflow loop , a number of extra DML statements will run while re-executing the same function activity again and again. And workflow never issue any intermediate commits while executing a given flow until it reaches the end of the flow or a blocking activity. Hence the overall DMLs in Process 1 would result in heavy usage of redo logs and rollback segments until a commit happens. The workflow engine in effect is unnecessarily stressed by the loop here leading to performance deterioration when this long running workflow loop can be easily avoided as done in Process 2.

At the end, from a developer perspective use your own judgement and common sense to decide on how granular your workflow process should be such that it doesn't hits us somewhere else as demonstrated above.

Workflow Engine vs Business Event System

Oracle Workflow has two major execution engines.

    • Workflow Engine
    • Workflow Business Event System

Here is a simple comparison of what they process and their associated background components.

Workflow Engine Workflow Business Event System
Executes workflow processes created using Windows based Workflow Builder client Executes subscriptions to business events registered using Event Manager in Workflow Administrator Web Applications Responsibility
Entry point foreground APIs are WF_ENGINE.CreateProcess and WF_ENGINE.StartProcess Entry point foreground API is WF_EVENT.Raise
Execution deferred to background by enqueuing message to AQ WF_DEFERRED_QUEUE_M Execution deferred to background by enqueuing message to AQ WF_DEFERRED
Entry point background API is WF_ENGINE.Background Entry point background API is WF_EVENT.Listen
Background processing is done by Concurrent Program - FNDWFBG (Workflow Background Engine) Background processing is done by GSC Component - Workflow Deferred Agent Listener
Background Engine is submitted as recurring concurrent request from SRS form or Workflow Manager in OAM Agent Listener is a service component managed through Workflow Manager in OAM

This blog post is as a result of confusion about what AQ and corresponding background process comes into picture when troubleshooting a given problem. For example,

  • When troubleshooting issues with Business Event System, users verify that the Workflow Background Engine is running.
  • When troubleshooting deferred workflow processes, users verify that the Workflow Deferred Agent Listener is running.

It is important to understand the two processing engines in Oracle Workflow, the supporting background components and how these two engines integrate with each other.

PLSQL vs Java Business Event System

Oracle Workflow provides Business Event System implementation within the database (PLSQL) and in the middle tier (Java). The implementation is exactly the same in terms of the event subscription processing in both these layers but the only difference is how the Developer wants to leverage Business Event System's capabilities for event processing requirements. With the availability of Business Event System implementation in PLSQL and Java, different subscription processing scenarios can be achieved.

Subscription Scenarios

    1. Execute PLSQL rule function synchronously or asynchronously. Oracle Workflow provides a bunch of default rule functions in WF_RULE package for your use
    2. Execute Java rule function synchronously or asynchronously. Oracle Workflow provides a default rule function oracle.apps.fnd.wf.bes.WebServiceInvokerSubscription to invoke web services
    3. Launch workflow process
    4. Send event to an agent (AQ)

Event Raise APIs

Workflow Business Events can be raised using one of two following ways.

    2. Java method oracle.apps.fnd.wf.BusinessEvent.raise

Testing Business Events

Starting 12.1.1 and 11.5.10.ATG RUP7 releases, Test Business Event page in Workflow Administrator Web Application responsibility is enhanced to test the business event using both PLSQL and Java Raise APIs.



Raise in PLSQL

Clicking button "Raise in PLSQL" for a business event results in the PLSQL API WF_EVENT.Raise being used to raise the event. This results in following behavior for different subscription definitions.

  1. Execute PLSQL subscriptions with phase < 100 in the same session
  2. Enqueue PLSQL subscriptions with phase >= 100 to WF_DEFERRED queue
  3. Enqueue ALL Java subscriptions regardless of phase to WF_JAVA_DEFERRED queue

Raise in Java

Clicking button "Raise in Java" for a business event results in the Java method oracle.apps.fnd.wf.BusinessEvent.raise() being used to raise the event. This results in following behavior for different subscription definitions.

  1. Execute JAVA subscriptions (such as WebServiceInvokerSubscription) with phase < 100 in the same session
  2. Enqueue JAVA subscriptions with phase >= 100 to WF_JAVA_DEFERRED queue
  3. Execute PLSQL subscriptions with phase < 100 in the same session
  4. Enqueue PSLQL subscriptions with phase >= 100 to WF_JAVA_DEFERRED queue

Details of different combinations of subscription types and phases and how the subscription is executed based on where it is raised from is discussed in next section.

Verifying Subscription Execution

As long as the call to the Event Raise APIs does not throw an error, the event is raised successfully. There is a wrong notion among developers and administrators that all the events raised are enqueued to an agent (AQ) such as WF_DEFERRED or WF_JAVA_DEFERRED. No, it is not enqueued to an agent always, but it is driven based on how the subscription is defined. Now how are the event subscriptions dispatched and how to check the status of a subscription execution?

Subscriptions are either synchronous (phase < 100) or asynchronous (phase >= 100) with respect to the layer it is raised such as either PLSQL or Java and that layer's ability to dispatch the subscriptions. Following table gives an idea about how different subscriptions are dispatched.




Scenario Event raised in Subscription type Subscription phase Agent (AQ) Dispatched by
1 PLSQL PLSQL >=100 WF_DEFERRED Workflow Deferred Agent Listener
2 PLSQL PLSQL <100 None Same session as WF_EVENT.Raise
3 PLSQL Java Any WF_JAVA_DEFERRED Workflow Java Deferred Agent Listener
4 Java Java >=100 WF_JAVA_DEFERRED Workflow Java Deferred Agent Listener
5 Java Java <100 None Same session as BusinessEvent.raise()
6 Java PLSQL >=100 WF_JAVA_DEFERRED Workflow Java Deferred Agent Listener
7 Java PLSQL <100 None Same session as BusinessEvent.raise()



This blog is dedicated to bring latest information on Oracle Workflow new features, best practices, troubleshooting techniques and important fixes directly from it's Development Team.


« March 2015