Tuesday Apr 26, 2016

Testing Integrated SOA Gateway's Service Invocation Framework (SIF)

Overview

Integrated SOA Gateway's Service Invocation Framework id based on the Business Event System capability to subscribe to java function Invoke Web Service (WebServiceInvokerSubscription.java) in order to invoke a web service.

 This blog goes over the series of steps on how to deploy one of the standard database packages (FND_ USER_PKG) as a web service so that it can be consumed by the subscription to Invoke Web Service, and perform the invocation from the Business Event System administrator page.

Note: This content applies to EBS versions 12.1.3 and 12.2. 

1. Enable and deploy the interface to be published as web service

Connect to EBS with the responsibility Integrated SOA Gateway and go to the Repository. There, expand Application Technology and then Application Object Library. Select User.

Now the list of interfaces for User are displayed. Click on the one with internal name FND_USER_PKG:


From the list of PLSQL interfaces select 'Test User Name' and hit generate WSDL

Select the box of Test User Name and click on Generate and then Deploy. After the Generation is complete a link to the WSDL file is enabled and it is to be used to consume the service. Copy the WSDL link or simply click on it:

2. Test the invocation from outside EBS

SOAP UI is a nice tool to quickly test the services proper deployment, and it only requires the URL to the WSDL generated above. 


Notice that upon invocation the response of the Web Service can be seen on the panel on the right.

3. Define a business event with a subscription to Invoke Web Service

Now test the invocation from within EBS by using the Business Event System itself. Simply create a business event and create a subscription to Web Service Invocation. Then follow the train of configuration. 


Select the service, port and operation to be invoked by this subscription:


and then

4. Test the invocation from Business Event System

Now that the event and the subscription are created, query the event and click on the Test event icon/link

On these form three parameters are required:

- Event Key, which can be anything

- Event parameter WFBES_INPUT_HEADER set to the following:

<wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" 
  env:mustUnderstand="1" xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
  <wsse:UsernameToken xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" 
    wsu:Id="UsernameToken-12423818"> 
    <wsse:Username>sysadmin</wsse:Username> 
    <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">sysadmin</wsse:Password> 
  </wsse:UsernameToken> 
</wsse:Security>

- Event payload (XML)
<tes:InputParameters xmlns:tes="http://xmlns.oracle.com/apps/fnd/soaprovider/plsql/fnd_user_pkg/testusername/"> 
  <tes:X_USER_NAME>SYSADMIN</tes:X_USER_NAME> 
</tes:InputParameters>

 

By clicking on Raise on Java the subscription is executed in the same session and the response is provided immediately:

Notice the response is the same as the one seen in SOAP UI

Conclusion

Oracle Business Event System provides the capability to invoke SOAP web services and also the means to test them by using its own features.

Thursday Apr 21, 2016

Configuring Office365 cloud server for Notification Mailer Inbound and Outbound

Introduction

The Office365 cloud server SMTP and IMAP services can be used for Workflow Notification Mailer Outbound and Inbound configuration. This blog explains the SMTP/IMAP server configuration, SSL certificate installation in Concurrent Tier and configuring Office365 IMAP and SMTP servers for Workflow Notification Mailer Inbound and Outbound.

1. Configure Mailer Outbound/Inbound with Office365 servers

Mailer Outbound and Inbound can be configured with Office365 SMTP and IMAP servers using below configuration details.

SMTP server 

Server smtp.office365.com
Port 587
Protocol TLS(startTLS)
User Name User name on Office365 server along  with email address
Password Password for the account used above

IMAP server 

Server outlook.office365.com
Port 993
Protocol SSL
User Name User name on Office365 server along with email address
Password Password for the account used above

2. Apply the Workflow 1OFF patches 

Apply the below workflow 1OFF patches for the respective codeline.

21044145:R12.OWF.C: Office365 cloud server support for Mailer Outbound and Inbound for R12.2.4 code line

21040057:R12.OWF.B:  Office365 cloud server support for Mailer Outbound and Inbound for R12.1.3 code line

Note: The above patches will be applicable for any SMTP/IMAP server that supports SSL/TLS protocols 

3. Install Office365 server certificates

The following is the procedure to install the Office365 server SSL certificates

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

                       <customLocation> -- directory in instance where the certificate need to be installed

  • Install the IMAP server 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 office_imap-lnx_chainnedcert 
            -file office365_imap.cer 
  • Install into a custom location
          # keytool -import -trustcacerts -keystore <customLocation> -storepass changeit 
            -alias office_imap-lnx_chainnedcert -file office365_imap.cer
  • After installing the certificates using the above commands you can see the following response, type 'yes' to complete.
     Trust this certificate? [no]:  yes
     Certificate was added to keystore

4. Mailer Command line diagnostics

The Mailer connectivity with Office365 SMTP and IMAP servers can be verified by running the Mailer command line diagnostics as below. We can also verify the Mailer logs by reviewing the generated log file.

Outbound:

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

Inbound:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc 
-Dserver=outlook.office365.com -Dport=993 -Dsecurity=SSL 
-Dtruststore=$AF_JRE_TOP/lib/security/cacerts-Daccount=<username> -Dpassword=<password>
-Dfolder=Inbox -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=IMAPSSLTest.log 
oracle.apps.fnd.wf.mailer.Mailer  


Sunday Apr 10, 2016

Configuring Notification Mailer using GMail's SMTP and IMAP servers (Using new certificates)

Overview

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.

1. Configure Mailer Outbound/Inbound with GMail servers

Mailer Outbound and Inbound can be configured with GMail SMTP and IMAP servers using below configuration details.

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
imap.gmail.com
 SSL Port
993
TLS/SSL Required
Yes
 User Name
Your full email address (including @gmail.com or @your_domain.com)
 Password Your gmail password

2. Install GMail server SSL Certificates

The following is the procedure to install the GMail SSL certificates

  • Download the GMail SMTP and IMAP server SSL certificates
  • Install the SMTP server 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_smtp-lnx_chainnedcert -file gmail_smtp.cer 
    • Install into a custom location
      # keytool -import -trustcacerts -keystore <customLocation> -storepass changeit 
        -alias gmail_smtp-lnx_chainnedcert -file gmail_smtp.cer
           <customLocation> -- directory in instance where the certificate need to be installed
  • Install the IMAP server 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_imap-lnx_chainnedcert -file gmail_imap.cer 
    • Install into a custom location
      # keytool -import -trustcacerts -keystore <customLocation>  -storepass changeit 
             -alias gmail_imap-lnx_chainnedcert -file gmail_imap.cer
        <customLocation> -- directory in instance where the certificate need to be installed
  •  After installing the certificates using the above commands you can see the following response, type 'yes' to complete.

    Trust this certificate? [no]:  yes

    Certificate was added to keystore


3. GMail Account security setup

The following security configuration needs to performed for the GMail accounts that are used for Mailer Outbound and Inbound configuration.

  •  Turn on less secure access using below URL by logging into account from browser
    https://www.google.com/settings/security/lesssecureapps
  • Create PROCESSED and DISCARD folders in your GMail account if the Mailer is not able to create automatically
  • You will get an email with subject like 'New Sign-in from' or 'Some unknown device or password' when Mailer trying to access your gMail account. You need to allow access for these from by logging into account.

4. Run Mailer Command Line Diagnostics

  •  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=<gmailUsername> 
    -Dpassword=<password> -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailSmtpTest.log 
    -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer
    
  •  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=<gmailUsername> 
    -Dpassword=<password> -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailImapTest.log 
    -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer
    
    

5. Troubleshooting Steps

Standalone program to verify the SMTP connection

If the Mailer fails to connect to GMail SMTP server after performing the above given configuration, 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 message to the given recipient user email address.

Sample program to test GMail SMTP connection

The standalone program can be run as below

 $java GmailSMTPTest GMailUsername GMailPassword recipientEmailAddress   


Standalone program to verify the IMAP connection


If the Mailer fails to connect to GMail IMAP server after performing the above given configuration, 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           

Warnings

  • 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

Wednesday Mar 23, 2016

Now Available: Approvals for EBS 1.4.0 for iOS and Android

Oracle E-Business Suite Development is pleased to announce updates for Oracle E-Business Suite smartphone apps. These updates are delivered as part of Oracle E-Business Suite Mobile Release 5, which supports both Oracle E-Business Suite Release 12.1.3 and 12.2.3 and beyond.  Oracle E-Business Suite Mobile Release 5 is a coordinated release of 17 Oracle E-Business Suite mobile apps, excluding Mobile Expenses and Mobile Field Service, which have their own off-cycle releases.  

For more details on these updates for each app, see specifics below and the Oracle E-Business Suite Mobile Apps Release Notes available from MOS Note 1641772.1.

What's New?

Approvals for EBS smartphone app includes following new features in this release:

  1. *New approval types for these functional areas: Channel Revenue Management (claims, budgets, and offers), Human Resources (absences and learning including class enrollments and certification subscriptions), Inventory (move orders), Maintenance (work orders, work requests, and work permits), Order Management (RMAs, sales agreements, and quotes), and Quoting (quotes)
  2. *Ability to view attachments for approval requests
  3. *Ability to add custom workflows
  4. Enhanced Pending Approvals by Type page:
    1. Improved approval type icons (achromatic icons)
    2. Real-time pending approvals count
    3. Dynamically show only those approval types with pending approvals
  5. Improved subject card view in the Details page
  6. Enhanced refresh interaction using a Pull to Refresh gesture in place of the Sync icon
  7. Mobile Foundation:
    1. *User installation and usage metrics for administrators
    2. Updated look and feel for selected app interactions
    3. Technical updates with u" class="bki-span">ptake of Oracle Mobile Application Framework (MAF) 2.2.2

Note: The latest app version will work with earlier server-side patches. However, the latest server-side patches must be applied to enable new features and fixes that require those patches; such features are marked with an asterisk (*).

See My Oracle Support Note 1641772.1 at https://support.oracle.com

New Feature Highlights

New Approval Types, Enhanced Approval Types Page

New seeded approval types are added for core functional areas such as Human Resources, Channel Revenue Management, Order Management and Maintenance. Pending Approvals by Type page that provides easy way to filter approvals based on approval types is also enhanced to use improved icons and real-time count of pending approvals for each approval type.

Custom Approvals

Until Oracle E-Business Suite Mobile Release 4.0 (Approvals for EBS 1.3.0), only seeded approval types were supported in the app. Starting Mobile Release 5.0 (Approvals for EBS 1.4.0), Customers can add their own approvals to the mobile app using a simple developer configuration.

Oracle Workflow provides a new tool called Approvals Data Services Framework that helps a developer perform following steps to include custom approvals in the mobile app starting Mobile Release 5.0.

  1. Design Approvals Content
  2. Configure Approvals Metadata
  3. Test Approvals Data Services
  4. Create Approval Type
  5. Use from Approvals for EBS App

Refer to chapter Configuring Messages for Oracle Mobile Approvals for Oracle E-Business Suite in Oracle Workflow Developer Guide. Although this link points to Oracle E-Business Suite 12.2, the same instructions apply for Oracle E-Business Suite 12.1 with Mobile Release 5.0.

View Attachments

Ability to view attachments for an approval from the mobile app is one of the very critical features. This provides ability for the approver to review app relevant approval content before making a decision on the go. Approvals for EBS smartphone app consolidates all attachment content that were displayed on the Worklist web pages under Attachments and Related Applications regions and shows them in one single Attachments page.


Pull Down to Refresh

In Pending Approvals page, the refresh icon on the header is now removed to introduce a more intuitive "Pull Down to Refresh" gesture. This is a common gesture used in many consumer apps such as email. To use this feature, press on the Pending Approvals page, drag down and release to trigger refresh of the page.

Enhanced Subject Card

In order to improve the content in details pages, the default notification header attributes such as Sender, Sent Date, Due Date, Notification ID are moved from the body of the page to within the Subject card. The Sender, Sent Date are displayed above the Subject, similar to how it is shown in Pending Approvals page. The Due Date is shown below the Subject as number of days that the approval becomes due in.

References

  1. Oracle Mobile Approvals for Oracle E-Business Suite Release Notes (MOS Doc ID 1642423.1)
  2. Oracle E-Business Suite Mobile Foundation Release Notes (MOS Doc ID 1642431.1)
  3. Oracle E-Business Suite Mobile Apps, Release 12.1 and 12.2 Documentation Index (MOS Doc ID 1641772.1)
  4. Transfer of Information (TOI) Online Training
  5. Search for Oracle EBS Mobile Apps on App Store using ‘Oracle EBS America’

Monday Mar 07, 2016

Setting up JDeveloper to Develop and Test Embedded Framework Regions

Overview

Oracle Workflow Notification system provides ability to use OA Framework regions to show content within a notification body in Worklist. This provides following benefits:

  1. Reuse application code to display content inside workflow notifications
  2. Consistent look and feel of notification content with OA Framework pages

Refer to Oracle Workflow Developer Guide for more information.

A challenge for application developers had been to setup JDeveloper to develop, embed and test their OA Framework regions using OA Framework JDeveloper Extension within workflow notifications before deploying it to an EBS environment. Most developers complete development of a standalone OA Framework region, migrate it to EBS, embed to workflow notifications and test it from actual workflow notification pages. Troubleshooting issues with such embedded regions had been extremely difficult.

This blog post provides instructions to develop, embed, test and troubleshoot embedded framework regions within JDeveloper. 

Using OA Extension

Refer to chapter "Setting Up Your Development Environment" in OA Framework Developer's Guide for details on setting up OA Framework JDeveloper project against an E-Business Suite env.

Instructions 

Use below instructions to setup the OA Framework JDeveloper environment with required Worklist run-time files.

  1. From the corresponding Apps env's web tier, copy following directories
    1. $JAVA_TOP/oracle/apps/fnd/wf/directory to $JDEV_USER_HOME/myglobalclasses/oracle/apps/fnd/wf/directory
    2. $JAVA_TOP/oracle/apps/fnd/wf/utilities to $JDEV_USER_HOME/myglobalclasses/oracle/apps/fnd/wf/utilities
    3. $JAVA_TOP/oracle/apps/fnd/wf/worklist to $JDEV_USER_HOME/myglobalclasses/oracle/apps/fnd/wf/worklist
    4. PLEASE NOTE:
      1. These are all the seeded Oracle Workflow's run-time Java class files that are executed by worklist pages when rendering
      2. All classes under myglobalclasses directory take precedence over the default libraries loaded by JDeveloper
  2. From the corresponding Apps env's web tier, copy directory
    1. $FND_TOP/mds/wf/worklist/webui to $JDEV_USER_HOME/myprojects/oracle/apps/fnd/wf/worklist/webui
    2. PLEASE NOTE:
      1. These are all the seeded Oracle Workflow's run-time OAF page definitions used to render worklist pages
      2. All JRAD XML files have to be copied under myprojects. These are automatically pulled by JDeveloper into the project when you refresh it.
  3. Update config file application.xml under $JDEV_USER_HOME/system/oracle.j2ee./embedded-oc4j/config to remove the line containing wf.zip. This zip file is very old and contains Workflow UI files that may not be compatible with the target EBS env you are working against.
  4. For 11i, the application.xml is under $JDEV_USER_HOME/system9.0.3.x.x/oc4j-config/
  5. Setup the compile and run directories to use myglobalclasses folder instead of the default myclasses folder
    1. Go to Project Properties -> Project Content -> Output Directory. Change myclasses to myglobalclasses
    2. Go to Project Properties -> Run/Debug -> Select Default -> Edit -> Run Directory. Change myclasses to myglobalclasses
  6. To verify that the worklist is setup correctly to run from JDeveloper, you can now run AdvancWorklistPG.xml from the project. This should display the Advanced Worklist page.
  7. In the same JDeveloper, setup your application specific OAF regions that you want to embed into workflow notifications.
  8. Complete required setup to embed the region into Workflow Notifications as per Workflow Developer Guide.
  9. Now run AdvancWorklistPG.xml again and test your notification.

Conclusion

Such JDeveloper setup would save a ton of time for developers since all testing is done within the JDeveloper env itself without going to the EBS env.

Monday Feb 15, 2016

Understanding the Workflow debugging scripts wfmlrdbg.sql and wfbesdbg.sql

Introduction

While logging a bug in Workflow area, it is always advisable to provide output of some of the diagnostic scripts such as wfmlrdbg.sql and wfbesdbg.sql. This blog provides a detailed description about the usage of and the information this script provide. Output of wfmlrdbg.sql provides details about a particular notification that are used to debug any issues with a Workflow Notification. wfbesdbg.sql is used to debug the event and subscription related issues in Business Event System.

1.wfmlrdbg.sql

This script can be used to debug the notification related issues for Mailer. It can be run using the below command.

SQL>sqlplus apps/apps @$FND_TOP/sql/wfmlrdbg.sql <notification_id>

It generates a html file with name format wfmlrdbg<notification ID>.html. You can get the following information from this

    1.Notification information like message type, message name, context, recipient ..etc

    2.Recipient details and its members

    3.Notification attributes

    4.Recipient routing rules

    5.Notification message details in WF_DEFERRED AQ

    6.Notification message details in WF_NOTIFICATION_OUT AQ

    7.Message payload from generate function

    8.Message payload in WF_NOTIFICATION_OUT AQ

    9.Recipient message payload in WF_NOTIFICATION_IN AQ

    10.Notification Message definition in wft file

    11.Notification Mailer component details


The following section explains each item from the output file:

Notification Item Information

It provides the notification item information such as of notification id,message type,message name,recipient role,mail status.

Notification Recipient Role Members


                                                                              Figure - 1.
As seen the figure -1 it provides the Notification Recipient Role members  information such as user_name, email address, notification preferences, etc.

Notification Recipient Role Information



                                                                            Figure - 2 .
As seen the figure -2 it provides the Notification Recipient Role information such as role name, email address, notification preferences, etc.

Notification Recipient Routing Rules

It provides the recipient routing rules information such as Action, Begin Date, End Date, Message Type, Message Name, Action Argument, Name, Type, Value.

Notification Message Attribute Values

It provides Notification message attributes information such as its name, value, format.

Notification Attribute Values

It provides Notification attribute values information such as name, number value, date value, text value.

Notification Message Result Attribute Values

It provides lookup type, lookup code values.

Notification User Comments

It provides from-role, from-user, action, user comment values.

WF_DEFERRED Queue Status

It provides Message Id, Message State, Consumer Name, Queue, Event Name, Event Key, Enqueue Time, Dequeue Time of wf_deferred Queue.

WF_ERROR Queue Status

All the errors in notification goes into this queue and it provides errors information such as Error Message, Error Stack.

Error Notification(s)

It provides the error notification information such as Notification Id, Date, Notification Subject, Error Message, Error, Error Stack.

Profile Option Values

It provides the base url information.

Workflow Notification Tags Value

It provides notification tags information such as name, tag id, action, pattern.

GSC WF_MAILER Component Parameters

It provides the component parameters information such as component status, Inbound agent name, out bound agent name.

WF_NOTIFICATION_OUT AQ

 It provides notification message payload and status information whether it is processed by the Mailer

WF_NOTIFICATION_IN AQ

Contains the recipient payload message and status information when placed by Notification mailer

2. wfbesdbg.sql

This script can be used to debug the event and subscription related issues in BES. It can be run using the below command

  SQL>sqlplus apps/apps @$FND_TOP/sql/wfbesdbg.sql <eventName> <eventKey>

It generates a html file with name format wfevtdbg<evntKey>.html. You can get the following information from this

    1.Event Information

    2.Subscription Information

    3.Agent listeners components status

    4.Event details in WF_DEFERRED ans WF_JAVA_DEFERRED queue

    5.Event details in WF_ERROR ans WF_JAVA_ERROR queue



Understanding the Workflow debugging scripts wfstat.sql

Introduction

While logging a bug in Workflow area, it is always advisable to provide output of some of the diagnostic scripts such as wfstat.sql. This blog provides a detailed description about the usage of and the information this script provide. Output of wfstat.sql script provides workflow activity details for a given workflow process. Output of wfmlrdbg.sql provides details about a particular notification that are used to debug any issues with a Workflow Notification. wfbesdbg.sql is used to debug the event and subscription related issues in Business Event System.

1.wfstat.sql

This script can be used to debug the workflow activity issues for a given workflow. It can be run using the below command. Use spool command to generate the output of this into a text file.


SQL>spool wfstat.txt
SQL>sqlplus apps/apps @$FND_TOP/sql/wfstat.sql <item type> <item key>
SQL>spool off;


It generates a text file called wfstat.txt that contains output of this script. You can get the following information from this:

  1. Workflow item type and itemkey
  2. Workflow activities' start time and end time
  3. Parent process and child process information
  4. Status and result of each activity
  5. Start date and End date of each activity
  6. Errored Activities and Error Process Activity Statuses
  7. Notification details with notification id and notification recipient
  8. Performer of each activity.

The following section explains each item from the output file:

WorkFlow Item

It provides item type, item key, start date and end date for the workflow item. It also provides the root activity details for the same.

Activity Statuses

It provides start date, end date, status, result for each of the activities. In addition to that it provides the performer user and notification ID for Notification activities. It also provides the sequence of execution of the activities.

Activity Statuses History

It provides status history of the all the actions performed on the different activities

Notifications

It provides information about a notification such as Notification ID, status, message name, begin date, end date, from-user, to-user, subject.

Errored Activities

It provides all the activities information that are resulted into ERROR status.

Error Process Activity Statuses

It provides the WF ERROR process activity statuses in case of errors.

Item Attribute Values

It provides information about attribute such as  attribute name, attribute type, attribute value.



Thursday Dec 03, 2015

Support of multiple e-mail addresses for a single user or role

In Oracle EBS 12.0 and 12.1 the Workflow notification system was not enabled to send e-mail notifications to users or roles who happened to have multiple e-mail addresses associated to them. For instance, if user JDOE had two e-mail addresses (jdoe@abc.com and jdoe@123.com) only one of them could be associated to the user for the Workflow notification mailer to send any e-mail notifications.

This limitation has been lifted in EBS 12.2.5 and now both users and roles can define multiple e-mail addresses as needed. This means that still a notification is intended for one recipient but the same notification will be sent to all associated e-mail addresses.

Note: The limit size of the table column holding the list of e-mail addresses is 320 so bear this limitation in mind. This should not be an issue as it is not common to see a single user with as many e-mail addresses so as to pass this limit. Consider the use of a role and multiple users associated to that role if this limit is still an issue.

Defining multiple e-mail addresses for a user or role

  • If the target role is an FND user then this setting takes place in the corresponding user definition form:



  • If the target is an ad-hoc role or ad-hoc user the assignment takes place when the user or role is being created. For instance:
     WF_DIRECTORY.CreateAdHocUser(name => 'ADHOC-USER',
                                  display_name => 'ADHOC-USER',
                                  notification_preference => 'MAILHTML',
                                  email_address => 'jdoe@abc.com, jdoe@123.com',
                                  expiration_date => SYSDATE+1);
  • If the user or role is created from code, the setting will look like:
     ...
       l_params wf_parameter_list_t;
     begin
       WF_EVENT.AddParameterToList('USER_NAME', 'JDOE', l_params);
       WF_EVENT.AddParameterToList('MAIL', 'jdoe@123.com, jdoe@abc.com', l_params);
       WF_LOCAL_SYNCH.propagate_user(p_orig_system => 'FND_USR',
                                     p_orig_system_id => 123435,
                                     p_attributes => l_params);

Notification action history

When the Notification Mailer receives the e-mail response the from e-mail address is compared against the list of addresses corresponding to the target of the notification recipient. If there is a match the responder is set to the value of the recipient. If no matches are found then one of two actions will happen:

  • If the Notification Mailer Parameter Allow Forwarded Response is set to Yes the responder of the notification is set to the value of the from e-mail address, or
  • If not, such e-mail response is treated as an unsolicited e-mail: the notification is not processed and a message is sent to the sender indicating so.

What happens with invalid addresses? 

In the event that a user or role was created using invalid e-mail addresses - subsequently causing the SMTP server to fail to deliver e-mail to them - the following takes place: 

  • If the notification is successfully delivered to at least one e-mail address mail status of the notification is set to SENT and the invalid addresses are added to the in-memory list of invalid addresses so that they are not attempted further on.
  • If all of them are invalid the notification mail status is set to FAILED and the user/role notification preference is set to DISABLED

In both cases a notification is sent to the user SYSADMIN indicating that a user/role contains invalid addresses and require fix.

Wednesday Nov 18, 2015

Approvals for EBS 1.3.0 for iOS and Android is now available

Oracle Mobile Approvals for Oracle E-Business Suite lets you respond on-the-go to your pending approval requests. From your phone, anywhere and anytime, take quick action on approval requests for expenses, requisitions, purchase orders, recruitment vacancies and offers, timecards, item changes, Supplier Invoices, and more.

Oracle Mobile Approvals for Oracle E-Business Suite is compatible with Oracle E-Business Suite 12.1.3 and 12.2.3 and above. To use this app, you must be a user of Oracle E-Business Suite, with mobile services configured on the server side by your administrator. 


What's New in Version 1.3.0

  • Support for enterprise distribution
  • Customization support for corporate branding
  • Mobile application management (MAM) support with Oracle Mobile Security Suite (OMSS), including single sign-on
  • Support for Web SSO authentication to delegate authentication to Oracle Access Manager (OAM)
  • New approval types: Timecards, Item Changes, and Supplier Invoices
  • Enhanced data layout
  • Improved approvals filtering
  • Translations in 10 additional languages including Brazilian Portuguese, Canadian French, Dutch, French, German, Italian, Japanese, Latin American Spanish, Simplified Chinese, and Spanish.

For more information on how to use Mobile Approvals, please refer to the following document:

References

Related Articles

Update

New versions of the smartphone app Approvals for EBS has been released. Refer to following posts.

Please go to the blog post for latest version of Approvals app and post your comments.

Thursday Nov 05, 2015

Checking for Proper Language Translation in Workflow Directory Services

Introduction

When logging into the Oracle EBS pages in any language ideally roles should be viewable with display names and descriptions in that language so the logged in user would understand them.

If there are any doubts about whether or not roles would be translated to a newly activated language, there are some steps that could be followed to check for proper language translation.  These steps could also be used to check that a language has been successfully added.


Required Patches

To make sure language translation takes place as expected there are some required 1-off patches that need to be applied.

21538802:R12.OWF.B - 1OFF:12.1.3:WF_DIRECTORY.SETADHOCUSEREXPIRATION RESETS THE EMAIL ADDRESS OF ROLE
21538803:R12.OWF.C - 1OFF:12.2.3:21289350 FORWARD PORT: WF_DIRECTORY.ADD_LANGUAGE DOES NOT ADD MISSING


Preliminary Setup

Also, for language translation to work properly the ROLE_TL_VIEW value for a particular ORIG_SYSTEM in the WF_DIRECTORY_PARTITIONS table needs to be set to “NOBS”.  While this could easily be set manually by anyone it is really up to the product development teams to set the value through formal patching.  Thus, if necessary a patch should be requested from the appropriate product development team that does just that.


Scenario
  1. Use appropriate AD tools to add languages, e.g. French and Danish.
  2. Log into Oracle EBS as sysadmin using the English interface.
  3. Use the System Administrator or User Management responsibility to create a new role while also entering a brief description, e.g. RM_ROLE_TEST, "RM role test - English".
  4. Log out.
  5. Query the WF_LOCAL_ROLES and WF_LOCAL_ROLES_TL tables for this newly created role and check the description.  It should be the same one entered at creation for all languages, i.e. "RM role test - English", as shown below:
  6. Log into Oracle EBS as sysadmin using one of the other language interfaces, e.g. French.
  7. Use the System Administrator or User Management role to modify the description of the role created earlier to indicate the language, e.g. "RM role test - French".
  8. Log out.
  9. Query the WF_LOCAL_ROLES_TL table for this role and check the description.  It should show the language-specific description, e.g. "RM role test - French" for French, as shown below:

  10. Log into Oracle EBS as sysadmin using another one of the other languages, e.g. Danish.
  11. Use the System Administrator or User Management role to modify the description of the role created earlier to indicate the language, e.g. "RM role test - Danish".
  12. Log out.
  13. Query the WF_LOCAL_ROLES_TL table for this role and check the description.  It should show the language-specific description, e.g. "RM role test - Danish" for Danish, as shown below:

  14. If desired, log into Oracle EBS as sysadmin using the other default languages, Arabic and Korean, and perform the same roles description modifications then log out.
  15. After all the language-specific description modifications the WF_LOCAL_ROLES_TL table for this role should look something like the following:

Friday Sep 11, 2015

What can a Workflow Administrator do?

Introduction

Workflow Administrator is a user who can take various administrative actions on a Workflow Process. In addition to that a Workflow Administrator can view the progress of a work item in a workflow process by connecting to the Workflow Monitor using a standard Web browser that supports Java. The Workflow Monitor displays an annotated view of the process diagram for a particular instance of a workflow process, so that users can get a graphical depiction of their work item status.

Setting up a Workflow Administrator

To setup a user as a Workflow Administrator, you can navigate to Workflow Administrator Web Applications > Administrator Workflow > Administration > Workflow Configuration. Here, there is a field called “Workflow System Administrator” to which a user can be assigned.

WARNING: If the value of this field is set as ‘*’, then every user on that E-Business Suite instance is considered as a Workflow Administrator. Hence, it is advisable not to set the value as ‘*’.

The following section presents us with different areas of Workflow and the actions a Workflow Administrator can perform in those areas:

Notification System

A Workflow Administrator can

  • Search and view any notification in the E-Business Suite application using Notification Search function.
  • Take any action on a Notification such as Respond, More Information Request, Reassign etc. An exception to this is Notifications requiring Digital Signatures (notifications requiring Password Based and Certificate Based Signature) where even a Workflow Administrator cannot respond to as it requires Digital Signatures of the recipient.
  • Respond to a notification that is sent by him/her.
  • Reassign even notifications restricted for reassign action (through #HIDE_REASSIGN attribute).
  • Restrict the list of users to whom a user can reassign a notification or request more information from (using #WF_REASSIGN_LOV).
  • Define vacation rules for any user and can update them appropriately.
  • Add list of item types that can be used in providing Worklist Access to or defining Vacation Rules for those item types
  • Reassign a notification that is not responded to and the workflow process waiting on that.

Business Event System

A Workflow Administrator can

  • Create, modify or test business events
  • Create or modify subscriptions, agents and systems

Workflow Mailer

A Workflow Administrator can
  • Setup Workflow Mailer to
    • Determine which response method should be used for a notification response to Email Notifications (Ex: template response or direct response)
    • Determine how frequently summary notifications are sent

 Status Monitor

A Workflow Administrator can

  • Perform administrative operations on a Workflow Process from Status Monitor screen. An admin can Cancel, Suspend, Retry, Skip a workflow process.
  • Set the display size of Status diagram that shows the current state of a Workflow in a diagrammatic representation.
  • Set default user preference values globally using workflow Configuration page.

More information can be obtained from the following documents:

Thursday Sep 10, 2015

Worklist Access as part of EBS Proxy in 12.2.4

There are a number of business scenarios in which Oracle E-Business Suite users need to grant delegates the ability to act on their behalf (act as proxy users for them) when performing specific Oracle E-Business Suite functions.

In 12.2.4, there was a new feature added EBS Proxy. The proxy user feature in Oracle E-Business Suite allows users to delegate access to specific application functionality to one or more proxy users. The delegator can grant access to specific responsibilities or workflow notification types to the proxy user, rather than having to grant access to all functionality. This provides the flexibility needed to meet typical business scenarios. The documentation for which can be obtained from oracle docs:
http://docs.oracle.com/cd/E26401_01/doc.122/e22952.pdf (3-32).
Pre 12.2.4, the proxy was limited to the access of different Workflow notification types and was granted using WorklistAccess Proxy. This document deals with the changes in the WorklistAccess Proxy with EBS proxy. The major changes between the two have been explained further in the blog.


1.    Granting Worklist Access:  With the advent of 12.2.4, the link for (Worklist Access) has been replaced with the proxy settings in Global header. Refer pictures (a) for pre 12.2.4. and (b) for Post 12.2.4.

WorklistAccess_Pre_12.2.4

a.  Worklist Access Link for Proxy Configuration

ManageProxy_Post_12.2.4
b.    Manage Proxies under the settings icon in Global header post 12.2.4

 2.    The change from Grant Worklist Access button to Add Proxy .

c.      Grant Worklist button has been upgraded to AddProxy.

 3.    On clicking the corresponding (Grant Worklist Access/ Add Proxy) button, the following screen has gone through a major change.  While a User can grant access to its one or more responsibility to another user, the item types for the worklist access (Notification Types) has three options:
i.    None : Access for any notification cannot be granted
ii.    Selected: Access for some notifications (depending on the grantor selection) can be granted.
iii.    All: As the name suggests, Access for all notification types can be granted.

 d.    d. Option of granting access for responsibilities as well the option of granting none item Type.


 e.    e.  Pre-12.2.4 Grant Proxy Page

 4.    Switch User: From 12.2.4, the option of switching to another user, (Grantee to Grantor) has also been changed. Earlier, there used to be Switch User button for grantee to switch to the Grantor.

f.  f.   Switch User Button for going to the list of users for which proxy has been granted to the logged in User.

 g.   Switch User button is upgraded to Switch User Icon post 12.2.4.

 From 12.2.4, the user has to click on the global header of switching user, to go to the list of users (grantors). Also, if a user A switches to user B, for it to switch to User C, it has to switch back to itself. Refer fig.(h). the figure shows the info regarding the proxy and current user,

h. Global Header Changes

Wednesday Jun 24, 2015

Make your workflows context dependent the right way

Overview

Oracle Workflow is context-agnostic. Oracle Workflow does not guarantee a specific application context to be available during the execution of a workflow process.

A workflow process may need certain application context to be initialized in order for a process activity to execute successfully. For example, if an activity submits a concurrent program request, it needs certain application context to be initialized.

Oracle Workflow provides ability for application code to manage application context for their workflow processes. Before executing a workflow process, it first allows to test the context. If the test determines that a specific application context should be initialized, it then allows to set the required context before continuing to execute the workflow process.

In order to manage application context from within a workflow process, Oracle Workflow supports the notion of Callback Function (popularly known as Selector Function). Hence, for a given item type a single function operates as both selector and a callback function.

  • Selector - Selects the appropriate process name to execute when the item type is initiated without specifying the process name.
  • Callback - To test and set application context required for the workflow process to execute.

Callback Function 

In this blog post we will only discuss the function modes for the Callback feature. A function mode is the condition under which the callback function implementation should do a certain processing.

  • TEST_CTX - Before executing a workflow process activity, determine if the current application context is correct for the given item type. In a typical case, the application context is evaluated by checking the values of but not limited to FND_GLOBAL.USER_ID, FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID and so on. These are Application Object Library (FND) global variables that determine if an appropriate application context is initialized or not.
    • NOTSET - Context has not been initialized. For example, if FND_GLOBAL.USER_ID is -1 it indicates an application context was not initialized.
    • FALSE - A context is already initialized but it is not correct for the given workflow item type. Workflow engine should switch context is allowed.
    • TRUE - A context is already initialized and it is valid for the given workflow item type.
    • Null - The item type does not depend on any context.
  • SET_CTX - Establish required application context for the item type.

Refer to Oracle Workflow Developer's Guide for Standard API for an Item Type Selector or Callback Function 

In a given session, Oracle Workflow executes the callback function each time it encounters a new item type and item key combination. For example, when a workflow process (identified by a given item type and item key) is initiated, the workflow engine executes the callback function only once before first activity. For the rest of the activities in the process, the selector function is not executed in that session. Now, in the same session if a new workflow process (identified by the same item type but a different item key) is initiated, the callback function is executed again.

Preserve Context

While it is extremely beneficial to allow application code to manage required application context during execution of a workflow process, it is also important to determine when the application context can be switched and when it cannot be. Oracle Workflow internally maintains a flag WF_ENGINE.PRESERVED_CONTEXT that determines whether context switching is allowed or not.

WARNING: This flag is INTERNAL ONLY and cannot be manipulated by the application code

Oracle Workflow initializes this flag with either TRUE or FALSE based on whether it is required to preserve the current application context or not.

  • TRUE - This value indicates that the workflow engine is required to preserve current context. This is typically when workflow engine executes in the foreground.
    • For example, if an approver signed into Worklist and approved a notification, the workflow engine executes in the foreground. Since it is required to preserve the current approver's context, workflow engine cannot switch to a different application context.
    • If the callback function returns FALSE in TEST_CTX mode, the workflow engine since it is required to preserve existing application context, it will not execute the callback function in SET_CTX mode. Instead, it will defer the workflow process to background engine that will then invoke the callback function in TEST_CTX and SET_CTX modes. 
  • FALSE - This value indicates that the workflow engine is not required to preserve current context. This is typically when the workflow engine executes in the background, such as initiated by Workflow Mailer or Workflow Background Engine concurrent request.

Following table summarizes how Oracle Workflow treats results of TEST_CTX mode in a given preserve context situation.

 Preserve Context NOTSET FALSE TRUE NULL
 TRUE  Set Context  Defer to Background Engine  No Change  No Change
 FALSE  Set Context  Set Context   No Change  No Change

Summary

It is very important to understand the nuances of context management within workflow process with regards to

  • What result to send back to workflow engine in TEST_CTX mode?
    • Clearly understand the difference between NOTSET and FALSE. They have completely different meanings and impact to workflow execution.
  • How workflow engine treats different TEST_CTX results in the context of preserve context flag?
    • Your workflow process may be deferred to workflow background engine and executed later on. If the background engine is not running, your workflow will not be executed.

 

Friday May 29, 2015

Increase throughput of your Workflow Mailers

The Workflow Notification Mailer is a component running on the Workflow Mailer GSM Service (also known as container). This service can host multiple instances of the Workflow Notification Mailer so that the load of sending and receiving e-mails can be shared, improving the overall performance. This is specially true as more and more applications moved from PLSQL-based notifications, that pretty much only required access to the database, to HTML/Framework-based notifications that also require access to the application server to retrieve the content of these good-looking notifications. 

Note: to learn more about Generic Service Management click here.

Creating a new component for the mailer allows the following:

  • Increase the number of processes for inbound and outbound processors - although this can also achieved simply by increasing the number of processors in the existing Notification Mailer 
  • Use a different SMTP account to process outbound messages and/or a different IMAP account for inbound processing
  • Specify a different correlation ID to dedicate message processing to a specific item type
  • Specify a user, responsibility and application different from the default one (SYSADMIN). This is typically done when specific profile values need to be applied for the applications user that the mailer will use to retrieve its content.

Creating another Notification Mailer component

 By creating a new Container a whole new set of components will be added to exclusively manage the components (mailers) and this way provide more scalability. The diagram below depicts what a Component Container is composed by.

.

 To create a new component follow this simple steps:

  • Connect to Oracle Apps with the responsibility Workflow Administrator Web Applications
  • Navigate to Oracle Applications Manager -> Workflow Manager
  • Click on Notification Mailer. Then click on the Create button on the top right part of the page
  • Select the Workflow Mailer service and hit Continue
  • Key in the corresponding parameter values for this new Notification Mailer.
  • Use the Advanced option to specify the Correlation Id and other parameters. The Inbound Agent is always WF_NOTIFICATION_IN while the Outbound Agent is WF_NOTIFICATION_OUT.

Creating a new Workflow Mailer service

A new Workflow Mailer component still has a limitation: the connection to retrieve HTML/Framework content is synchronized and this means that only one Java thread can retrieve the content at a time. Of course, this retrieval happens very fast, but still there is the chance of a bottle neck when the number of this kind of notifications is very high. 

To increase the performance in this scenario a new Workflow Mailer service is the solution, and it can be done as follows:

  • Connect to Oracle Apps with the responsibility Workflow Administrator Web Applications
  • Navigate to Oracle Applications Manager -> Workflow Manager
  • Under column Container, click on Workflow Mailer Service and hit the Create New button
  • Enter the names desired for the new service. The image below provides an example
  • Use the button Add from Available Shits to indicate the availability of this new service. Use the one called Active 24 hours every day.



  • Save the changes

Now new Workflow Notification component can be created with the steps given earlier, but instead of using service Workflow Mailer, use New Workflow Mailer with the desired parameter values.

Wednesday Mar 18, 2015

Now Available: Approvals for EBS 1.2.0 on iOS and Android

About

Oracle Mobile Approvals for Oracle E-Business Suite lets you respond on-the-go to your pending approval requests. From your phone, anywhere and anytime, take quick action on approval requests for expenses, requisitions, purchase orders, recruitment vacancies and offers, and more.

- Quickly filter approval requests by sender or subject
- Review at a glance header and line item details, action history, and comments
- Approve or reject with or without comments, or request more information

Oracle Mobile Approvals for Oracle E-Business Suite is compatible with Oracle E-Business Suite 12.1.3 and 12.2.3 and above. To use this app, you must be a user of Oracle E-Business Suite, with mobile services configured on the server side by your administrator. For information on how to configure mobile services on the server and for app-specific information, see My Oracle Support Note 1641772.1 at https://support.oracle.com.

Availability

Client

Oracle Mobile Approvals for Oracle E-Business Suite 1.2.0 is available on Apple's iTunes Store and Google Play Store for download.


This app works against Oracle E-Business Suite Release 12.1.3 and above and 12.2.3 and above.

Server

Oracle Mobile Approvals requires set up on Oracle E-Business Suite instance to support connections from the mobile app. Following patches are required to be applied.

Android Screens


Additional Resources

Update

New versions of the smartphone app Approvals for EBS has been released. Refer to following posts.

Please go to the blog post for latest version of Approvals app and post your comments.

About

Search

Archives
« July 2016
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
      
Today