X

Recent Posts

Business Event System

Collecting Workflow Logs - Part 1: Business Event System

Overview This blog is about explaining the steps to collect logs for different workflow components like UI, Business Event System, Server, Directory Services and Workflow Notification Mailer ..etc. This will be explained in series of blogs for each of the component. Part 1 explains about collecting logs for Business Event System. Collecting Agent Listener logs Collect the Workflow agent listener component logs using below steps. Steps are explained for 'Workflow Deferred Agent Listener' component and the same steps applies for other agent listeners like 'Workflow Deferred Notification Agent Listener', 'Workflow Java Deferred Agent Listener' and 'Workflow Inbound Notifications Agent Listener' components. Navigate to the Service Components page as below Workflow Administrator -> Oracle Applications Manager -> Workflow Manager -> Service Components Select the required component for example 'Workflow Deferred Agent Listener' and click on Edit Set the log level to STATEMENT in step 2 and click on 'Finish' button Click on Finish button in Step 4 as well and click on OK button in the Component Details page Bounce the workflow Agent Listener Service container using below steps Navigate to the Service Components page as below Workflow Administrator -> Oracle Applications Manager -> Workflow Manager -> Service Components Click on the container 'Workflow Agent Listener Service' and select 'Stop' from the drop down and click on Go button Wait until the container state is 'Deactivated' Now select 'Start' from the drop down and click on Go button Wait until the state is 'Activated' Get the 'Workflow Agent Listener Service' process id as below Select 'Workflow Agent Listener Service' container and click on 'View Processes' button, it shows the processes page Click on 'ICM Log' button, it opens the log page On the top left, you can see the string like ICM Log: 2153550: wf122dv3 The id in the middle '2153550' is the process id and note down it Reproduce the reported issue Collect the Agent Listener log file using below steps Go to the below location in the concurrent tier node cd $APPLCSF/logs Get the latest Agent Listener log file by appending the process id as noted in step 6.4 to the log file prefix 'FNDCPGSC' as below ls -ltr FNDCPGSC<processId>.txt  where <processId> is the 'Workflow Agent Listener Service' process id. For example: ls -ltr FNDCPGSC2153550.txt  This represents the latest Agent Listener log file If you are looking for the Agent Listener log file containing a specific string for example event or exception, you can grep for that string and get the required log file as below. grep -i "NullPointerException" FNDCPGSC*.txt grep -i "oracle.apps.wf.notification.send" FNDCPGSC*.txt   Collecting E-Business Suite logs Please refer to blog Collecting E-Business Suite Logs for Workflow for collecting Business Event System logs. E-Business Suite logs for specific Business Event System package say 'wf_event' can be collected by setting the AFLOG_MODULE profile option value as 'wf_event%'. You can also specify multiple packages by using comma separated values as 'WF_EVENT_SUBSCRIPTIONS_PKG%','wf_rule%','fnd.wf.bes.BusinessEvent%' ..etc Additional Resources Refer to Agent Listeners section in Oracle Workflow Administrator guide Refer to Logging Framework Overview section in Oracle E-Business Suite Developer's Guide    

Overview This blog is about explaining the steps to collect logs for different workflow components like UI, Business Event System, Server, Directory Services and Workflow Notification Mailer...

Server

Collecting E-Business Suite logs in workflow

Overview The Oracle E-Business Suite Logging Framework provides the ability to store and retrieve log messages for debugging, error reporting, and alerting purposes. This blog explains about collecting E-Business Suite Logs in workflow. Logging Configuration Parameters Set the below logging configuration parameters to collect logs in workflow. Set the logging configuration parameters Navigate to the Profiles page as below Functional Administrator -> Core Services -> Profiles Set the below specified profile option values at Site Level or at the User level. AFLOG_ENABLED - Yes Specifies if logging is enabled or not. The default value is NULL (False). AFLOG_MODULE - %wf% Specifies which modules are logged. The default value is NULL (%). AFLOG_LEVEL - Statement Specifies the logging level. The default value is NULL (Log.UNEXPECTED). AFLOG_FILENAME - << NO Value>> Specifies the file where middle-tier log messages are written. After setting the above values, clear the cache by using below  steps. Navigate to the Caching Framework page as below Functional Administrator -> Core Services -> Caching Framework Click on Total Caching components link Search for code : PROFILE%OPTION% Select check boxes for PROFILE_OPTION_CACHE & PROFILE_OPTION_VALUE_CACHE Click on clear cache button Logout from the application Steps to collect E-Business Suite Logs E-Business Suite Logs for Workflow can be collected using the below steps. Set the above specified profile option values at Site Level or at the user level. Get the max log sequence using below query and note down this number and refer as SEQ1. select max(log_sequence) from fnd_log_messages -- <SEQ1> Run your test case to reproduce the issue Now get all the relevant log messages using below query select * from fnd_log_messages where module like '%wf%' and log_sequence >= <SEQ1> order by log_sequence; where <SEQ1> is the max log sequence number as noted in step3. Upload the query o/p to an excel sheet. Additional Resources     Refer to Logging Framework Overview section in Oracle E-Business Suite Developer's Guide

Overview The Oracle E-Business Suite Logging Framework provides the ability to store and retrieve log messages for debugging, error reporting, and alerting purposes. This blog explains about collecting...

Mobile Approvals

Enterprise Distribution Of Approvals App With Custom Branding

Overview Pre-requisite reading before proceeding - Enterprise Distribution of Approvals App without Custom Branding Enterprise distribution of E-Business Suite mobile app without Custom Branding is very easy and straightforward as described in the previous blog post. Now, you can move forward and change branding of the mobile app to use your corporate look and feel where applicable. Following areas of the mobile app can be modified with your own corporate branding standards. App logo - Logo that appears above the app name in mobile device's home screen and in the about page of the app Company logo - Logo that appears in Server URL and Login page Splash screen or Launch screen - The screen shown when app is launched App name - Name of the app as it appears on device's home screen, springboard, server URL page, login page and about page End User License Agreement - Information shown to users to accept before using the app Copyright - Company's copyright information in About page You can continue with the same JDeveloper MAF project from Enterprise Distribution without Custom Branding that you created based on previous blog post. Preparation Before you implement custom branding for E-Business Suite mobile app, it is important to prepare required graphic assets to be used. Complete list of graphic assets and their dimensions are documented in chapter Implementing Corporate Branding and Deploying Your Apps in E-Business Suite Mobile Developer Guide. The graphic assets are required based on Mobile platform - iOS and/or Android Mobile devices - For iOS such as iPhone 5S, iPhone 6, iPhone 6S and for Android such as Medium Density, High Density, Extra High Density, Extra Extra High Density and so on. Once all the graphic assets are created, copy them to appropriate folders in the JDeveloper MAF application. Following are the target folders, All app logos for deployment profile use - resources/ios and resources/android EULA, app logo for about page, corporate logo for server URL page - .adf/META-INF/ebs Launch screen - ApplicationController/public_html/resources/html Corporate logo for login page - ApplicationController/public_html/resources/images Download sample artifacts used for demo purposes. This download does not include all the required artifacts but only the minimum required ones. It is important that during actual custom branding exercise, all the artifacts are created. New Deployment Profile Most of the graphic assets used in branding a mobile app are setup in the JDeveloper application's deployment profile. It is important to create a new deployment profile for your enterprise application to setup your corporate graphics. Right click on the application and create a new deployment profile - iOS and/or Android Setup required platform specific attributes such as For iOS - Application Bundle Id, Application Archive Name, Minimum iOS Version, Family and so on. For Android - Package Name, Application Name, Version Name, Version Code, Minimum SDK API Level and so on. Set the App Logos in the deployment profile under Application Images option For iOS - Images for App Icons, Spotlight and Settings are configured in the deployment profile For Android - Images for App Icon and Splash Screens are configured in the deployment profile EBS Mobile Properties E-Business Suite mobile specific properties are stored in ./adf/META-INF/ebs/ebs.properties file. Update following properties specific to corporate branding. Location of app logo to be displayed on About page - oracle.ebs.login.branding.applogo.location Size of the corporate logo to be shown on Server URL and Login Page - oracle.ebs.login.branding.corporatelogo.style App Name in Server URL page, Springboard and About page - oracle.ebs.login.appname Custom Android EULA - oracle.ebs.login.android.eulahtmllocation Custom iOS EULA - oracle.ebs.login.ios.eulahtmllocation Copyright link in About page - oracle.ebs.login.branding.copyrightfilelocation Additional Changes for Login Page Since login page is displayed from a HTML file, some additional changes specific to login page are required. Add new app name in ebs-LoginBundle.js file in ApplicationController -> Web Content -> resources -> js Change code in ebs-Login.js to read the new app name Distribution Now the EBS mobile app with corporate branding is ready to be distributed to users. References E-Business Suite Mobile Release Notes E-Business Suite Mobile Developer Guide

Overview Pre-requisite reading before proceeding - Enterprise Distribution of Approvals App without Custom Branding Enterprise distribution of E-Business Suite mobile app without Custom Branding is...

Business Event System

Interaction Between Workflow Engine and Business Event System - Part 2

Overview Previous blog post Interaction Between Workflow Engine and Business Event System - Part 1 described declarative methods using Workflow Standard activities for interaction between Workflow Engine and Business Event System. These are the simplest methods but they are not the only methods. Interaction between the Workflow Engine and Business Event System is also possible using API calls within function activity or subscription rule function implementations. Workflow Engine Calling Business Event System APIs There are public APIs available in WF_EVENT package, the most straightforward of which is the WF_EVENT.Raise() API that simply raises a specific event. The figure below shows a workflow process that raises an event using the WF_EVENT.Raise API called within a function activity. This EVT_TEST_PKG.Event_Call() API is listed in the Sample APIs section. This works exactly like the standard EVENT activity with Raise option. The flexibility of using an API within function activity implementation is that it may perform many other tasks before and/or after raising an event including raising other events. Business Event System Calling Workflow Engine APIs An event subscription could also call on APIs under the WF_ENGINE package such as WF_ENGINE.Event() to pass along the event information to a workflow process. The figure below shows such an custom event subscription. The referenced EVT_TEST_PKG.Event_Rule() API is listed in the Sample APIs section. Sample APIs The APIs mentioned above are contained in a hypothetical package listed below. create or replace package body EVT_TEST_PKG as procedure Event_Call(itemtype in varchar2, itemkey in varchar2, actid in varchar2, funcmode in varchar2, resultout in out nocopy varchar2) is l_cnt number; l_seq number; l_parameters wf_parameter_list_t := wf_parameter_list_t(); begin if (funcmode <> wf_engine.eng_run) then resultout := wf_engine.eng_null; return; end if; select count(*) into l_cnt from user_sequences where sequence_name = 'EVT_TEST_SEQ'; if (l_cnt < 1) then execute immediate 'create sequence EVT_TEST_SEQ nocache'; end if; execute immediate 'select EVT_TEST_SEQ.nextval from dual' into l_seq; wf_event.raise(p_event_name => 'oracle.apps.wf.mailer.unsolicited', p_event_key => 'unsolicited_test_' || l_seq, p_event_data => NULL, p_parameters => l_parameters, p_send_date => sysdate); resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null; exception when others then Wf_Core.Context('EVT_TEST_PKG', 'Event_Call', itemtype, itemkey, to_char(actid), funcmode); raise; end Event_Call; function Event_Rule(p_subscription_guid in raw, p_event in out nocopy wf_event_t) return varchar2 is l_wftype varchar2(30); l_wfname varchar2(30); l_ikey varchar2(240); lparamlist wf_parameter_list_t; begin select wf_process_type, wf_process_name into l_wftype, l_wfname from wf_event_subscriptions where guid = p_subscription_guid; lparamlist := p_event.Parameter_List; wf_event.AddParameterToList('SUB_GUID', p_subscription_guid,lparamlist); p_event.Parameter_List := lparamlist; l_ikey := nvl(p_event.Correlation_ID, p_event.Event_Key); wf_engine.Event(itemtype => l_wftype, itemkey => l_ikey, process_name => l_wfname, event_message => p_event); return 'SUCCESS'; exception when others then Wf_Core.Context('EVT_TEST_PKG', 'Event_Rule', p_event.getEventName(), p_subscription_guid); wf_event.setErrorInfo(p_event, 'ERROR'); return 'ERROR'; end Event_Rule; end EVT_TEST_PKG; / show errors; commit; exit; Additional Resources For more information on the Workflow Engine, Business Event Systems, and Workflow Builder refer to below documents. Oracle Workflow Administrator's Guide Oracle Workflow Developer's Guide

Overview Previous blog post Interaction Between Workflow Engine and Business Event System - Part 1 described declarative methods using Workflow Standard activities for interaction between Workflow...

Server

Partitioning WF Tables Using wfpart.sql Script

Introduction Partitioning Workflow tables can improve the performance of Workflow Engine run-time processing. Partitioning a table creates the subset of table based on the specified column value. Workflow tables are partitioned based on ITEM_TYPE. The wfpart.sql script can be used to migrate the non-partitioned workflow tables into partitioned tables. When there is large volume of data, this will increase the performance as it will have to read through a particular set of data instead of the whole table. Tables and Indexes The wfpart.sql script partitions four Workflow tables and recreates the associated indexes as shown in below table. Table Indexes WF_ITEM_ACTIVITY_STATUSES WF_ITEM_ACTIVITY_STATUSES_PK WF_ITEM_ACTIVITY_STATUSES_N1 WF_ITEM_ACTIVITY_STATUSES_N2 WF_ITEM_ACTIVITY_STATUSES_H WF_ITEM_ACTIVITY_STATUSES_H_N1 WF_ITEM_ACTIVITY_STATUSES_H_N2 WF_ITEM_ATTRIBUTE_VALUES WF_ITEM_ATTRIBUTE_VALUES_PK WF_ITEMS WF_ITEMS_PK WF_ITEMS_N1 WF_ITEMS_N2 WF_ITEMS_N3   Before running the partitioning script, take a back up of these four tables so that you can restore them in case the script fails.To run the script, you must have sufficient free space on the table and index table spaces. During the creation of the partitioned tables, the script requires slightly more disk space than the underlying tables, in the same table space where the underlying tables are located. Similarly, sufficient free space is required for the index table space. Usage The wfpart.sql script can be run using below commands. Run the script using below commands sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfpart.sql <fnd_user> <fnd_passwd> <apps_user> <apps_passwd> <ult_dir_location> <fnd_user> - fnd product username. APPLSYS in this case <fnd_passwd> - fnd product password. applsys by default <apps_user> - user name for the APPS user <apps_passwd>  - password for the APPS user <ult_dir_location> - a directory that is included in the UTL_FILE_DIR database initialization parameter. For example: sqlplus apps/apps @wfpart.sql applsys apps apps apps /usr/tmp It generates another script wfpart.sql in the utl directory location eg:/usr/tmp Run the generated wfpart sql script using below command sqlplus apps/<apps_passwd> @<ult_dir_location>/wfpart.sql <fnd_user> <fnd_passwd> <apps_user> <apps_passwd> sqlplus apps/<apps_passwd> @<ult_dir_location>/wfpart.sql <fnd_user> <fnd_passwd> <apps_user> <apps_passwd> For example: sqlplus apps/apps @/usr/tmp/wfpart.sql applsys apps apps apps It gives the below message once the script is completed "Post-install script completed, you may ignore error during create Please recompile the invalid objects after executing this script"   Additional Resources 1. Refer to Partitioning Workflow Tables section in Oracle Workflow Administrator guide

Introduction Partitioning Workflow tables can improve the performance of Workflow Engine run-time processing. Partitioning a table creates the subset of table based on the specified column value....

Mobile Approvals

Enterprise Distribution of Approvals App without Custom Branding

Overview A common method of installing mobile apps on users' mobile devices is to install them from the public stores such as Apple App Store and Google Play. This is called App Store Distribution. Whereas, Enterprise Distribution is a process of distributing mobile apps to your mobile users by hosting the mobile apps in your internal corporate site. Your mobile users instead of installing from public stores, are required to install the apps from internal site. App Store Distribution - The mobile apps are owned by Oracle that are used to connect to EBS servers that belong to each customer. If Oracle makes a new EBS mobile release, then the users might upgrade to the latest version of the mobile app on their devices while the EBS server is still on the older version. While Oracle certifies the latest version of EBS mobile apps with one older version of the server (N-1), the administrator is still required to plan to upgrade the EBS server soon. Enterprise Distribution - The customers own both the mobile apps and EBS servers hence can control their versions at appropriate levels as per their requirement. The administrator has complete control over when they want to upgrade their mobile apps and servers. Enterprise distribution also helps to customize corporate branding if required. Scope In this blog post I will cover steps to distribute Approvals app using Enterprise Distribution model without corporate branding changes. When using these steps, you are creating an enterprise version of a mobile app that looks and works exactly like the corresponding app on the Apple App Store and Google Play, except that your users will install it from your internal corporate site. Downloads Before getting started, download below software. For iOS - If you plan to distribute the enterprise app for iOS, register with Apple Developer Program for your enterprise. Download Xcode, obtain iOS provisioning profile and distribution certificate using which you can package the iOS app to install on iOS devices. Without the provisioning profile and distribution certificate from Apple, you can still test the app on iOS simulator but you cannot install the app on device. For Android - If you plan to distribute the enterprise app for Android, download Android SDK. You only need SDK tools and not studio. JDK 8 - Download latest update of JDK 8 JDeveloper 12.2.1.0.0 - Download JDeveloper 12.2.1.0.0 E-Business Suite Mobile Software - Download Oracle E-Business Suite Mobile Application Archives from Oracle Software Delivery Cloud (OSDC). This contains EBS server patches for 12.1 and 12.2 Mobile Application Archives for all EBS mobile apps that support enterprise distribution. For example, for Mobile Release 7.0, for Approvals app use p25974387_R12_GENERIC.zip Mobile Application Framework corresponding to the EBS mobile release. For example, for Mobile Release 7.0, use maf-2.4000.20170417-0215.zip Documentation for the corresponding  EBS mobile release Preparation Steps to prepare your development environment before enterprise distribution. Server Apply EBS Mobile Release Patches - If you are using E-Business 12.2.7, it already includes Mobile Release 7.0. If not, refer to list of patches to apply in Oracle E-Business Suite Mobile Foundation Release Notes (Doc ID 1642431.1) Create and Configure Mobile Application - Login to EBS as user with responsibility Mobile Applications Manager, create a new definition for the enterprise mobile app. Note the Application Bundle Id used to create the definition, for example com.acme.ebs.MyApprovals, since the same value should be used in the MAF app in a later step. Once the application definition is created, use Configure icon to enable and configure the application. Client Install Xcode -  Install Xcode and launch it first. Install the provisioning profile to Xcode. Android SDK - Unzip the Android SDK tools to your computer. Install JDK 8 -  Install JDK 8 required for MAF development. Install JDeveloper 12.2.1.0.0 -  Install JDeveloper 12.2.1.0.0 using JDK 8. Install MAF Extension -  Software downloaded from OSDC that contains the MAF extension as a zip file maf-*.zip. Launch JDeveloper -> Help -> Check for Updates and use Install From Local File option to install the MAF extension downloaded from OSDC. Configure JDeveloper - Go to JDeveloper's Preferences -> Mobile Application Framework section. Configure Android and iOS options to make sure they point to the underlying installation of Xcode, enterprise provisioning profile and Android SDK location. MAF internally uses this configuration to package the iOS and Android app to install to devices. Distribution Create MAF Application Project - Launch JDeveloper and create a new MAF project by importing the Mobile Application Archive (MAA) file corresponding to the EBS mobile app that you want to distribute in your enterprise. List of MAA file names for each EBS mobile app are documented in chapter Downloading Mobile Application Archives Files in EBS mobile dev guide. Make Required Changes - Complete the minimum changes to distribute the app to your enterprise by changing only the following in the MAF project. maf-application.xml - Id should be EXACTLY same as what you used to register the app in Mobile Applications Manager responsibility, for example com.acme.ebs.MyApprovals. ebs.properties - Update link to your enterprise privacy policy link Deployment Profile - Right click on MAF application -> Application Properties -> Deployment. Update the iOS and Deployment profiles with appropriate Bundle ID and Package Name respectively that is needed to package the apps to be installed on corresponding devices. For iOS - The application bundle id used in deployment profile should match with the iOS provisioning profile you received from Apple For Android - The package name could be any valid value based on reverse domain naming convention such as com.acme.ebs.MyApprovals, but note that it should be unique for apps installed on users' Android device Note that these values for iOS and Android in deployment profiles need not be same as Id value in maf-application.xml. Deploy App and Test - Right click on MAF application and deploy the apps to either simulator or to a device and test Host App for Users to Install - Once apps are deployed as packages (for iOS - .ipa and for Android - .apk), host them on an internal server for your users to install on their devices. References E-Business Suite Mobile Release Notes E-Business Suite Mobile Developer Guide

Overview A common method of installing mobile apps on users' mobile devices is to install them from the public stores such as Apple App Store and Google Play. This is called App Store...

Business Event System

Interaction Between Workflow Engine and Business Event System - Part 1

Overview The blog post Workflow Engine vs Business Event System provided a comparison between the two major workflow execution engines. This blog post will demonstrate methods where one execution engine may utilize the other. Using the standard EVENT activity, it is possible to raise, send, and receive an event in a workflow process. Workflow Engine Dealing With Events The option to raise or send an event would be set in the Event Action field under the Event properties tab. The figure below shows a workflow process that raises an event whose name and key are determined via item attributes. Alternatively, the figure below shows a workflow process that sends an event whose name and key are determined via item attributes. Note that additional information, such as Out Agent and To Agent, could be provided in this case. Business Event System Initiating Workflow For business events to be of value they have to cause some other actions to happen. Those actions could include launching of workflow processes. By creating a subscription for the business event, this subscription could have an action type of launching a workflow process. The figure below shows such an event and subscription. But the workflow process has to have an WFSTD EVENT activity to receive an event. The figure below shows a workflow process that is actually initiated by any event that has a subscription to launch this process. The details of the event, such as its name and key, are captured by the EVENT activity via item attributes and reported in the FYI notification. To receive a specific event, an event name has to be entered in the Event Filter field under the Event properties tab. The figure below shows a workflow process that could be initiated by 3 specific events of interest, provided each have a subscription to launch this process. Each Receive event activity in the below figure uses a specific event name in the Event Filter field. Additional Resources For more information on the Workflow Engine, Business Event Systems, and Workflow Builder please consult the Oracle Workflow Administrator's Guide and the Oracle Workflow Developer's Guide.

Overview The blog post Workflow Engine vs Business Event System provided a comparison between the two major workflow execution engines. This blog post will demonstrate methods where one execution...

Mailer

Connection Security Protocols In Workflow Mailer

The Workflow notification mailer supports PLAIN, LOGIN, and DIGEST-MD5 authentication methods. If PLAIN or LOGIN authentication methods are used, it is recommended to connect to the SMTP server through TLS security protocol to encrypt user credentials details that are sent to the server. The TLS security protocol provides encrypted connections for sending data between the mailer and the mail servers, for enhanced security. By default the DIGEST-MD5 authentication mechanism is used which uses the JavaMail API to encrypt the user name and password before sending the data to the SMTP sever. The enhanced workflow mailer supports below security protocols. You may use different protocols among the given options for secure connections to the SMTP/IMAP server. None  -  If you do not want to use a secure protocol. SSL/TLS  -   To use TLS directly. STARTTLS  - To upgrade to an encrypted TLS connection using STARTTLS A brief description of the different secure protocols is as follows: SSL/TLS TLS (Transport Layer Security) provides encryption path between systems (e.g. a computer machine and server). The Transport Layer Security (TLS)  protocol emerged from SSL and has superseded it. The SSL/TLS is the most used security protocol today. Since TLS supports large number of secure algorithms so it is more secure and efficient protocol than SSL. STARTTLS STARTTLS protocol makes already existing insecure connection to a secure one using security protocol TLS. STARTTLS means "explicit TLS" i.e : a connection establishes on regular port and then STARTTLS issues a command which initiates TLS handshake and switch to protection mode. Default Port Numbers for the selected 'Connection Security' value Security Protocol Outbound Port Inbound Port  NONE  25  143  SSL/TLS  465  993  STARTTLS  587  143 To override the default port, enter it in server name as hostname:port in mailer configuration page as shown in below Figure. 1. For example if you want to use the port 489 as custom port while configuring the mailer please give it as servername:489 in server name filed of mailer configuration page. Figure. 1 As explained here a user can configure the workflow mailer with security protocols that are available. Additional Resources Oracle Workflow Administrator's Guide Configuring Notification Mailer using GMail's SMTP and IMAP servers (Using new certificates)

The Workflow notification mailer supports PLAIN, LOGIN, and DIGEST-MD5 authentication methods. If PLAIN or LOGIN authentication methods are used, it is recommended to connect to the SMTP...

Server

Controlling Delegation to the Initiator of a WF Process or Notification

Overview It is generally not a good business practice to have the owner or initiator of a WF transaction to be able to approve it.  There are may common sense examples, including the case when a user submits an expense report for business expenses and then that user is able to approve it.  Such cases may not go well with the audit team.  Hence, workflow implemented a fix that prevents the approver from reassigning the approval notification to the owner of the transaction or to the sender of the current notification.  This fix came about in these patches: Patch 24411221:R12.OWF.B for the 12.1.3+ codeline. Patch 16207672 as the 12.2.2 RUP. However, depending on business needs it may be necessary to allow manual delegation or automatic delegation via vacation rule of a notification to the owner of a WF transaction or the sender of a current notification. The former scenario (preventing delegation) would be more secure and provides a higher degree of legitimacy as there would be no way for the initiator to be involved in the approval process.  Thus, the application of this scenario is quite obvious. The latter scenario (allowing delegation) would allow flexibility in an approval process and a speedier run through it especially in the case of an unavailable approver as there would be no waiting on that approver to respond.  A typical example of this scenario is in the case where the highest-level approver is unavailable due to vacation or any other reason.  If that highest-level approver is not permitted to delegate to a subordinate while away, then how are requests going to get though and the business kept moving?  Thus, the need to allow delegation to the next level down, which could either be the owner or the previous sender. Therefore, in Oracle EBS 12.1.3+ and 12.2.4+ there are 1-off patches that give a WF Functional Administrator the ability to control delegation or reassignment of a notification to the owner or previous sender via a profile parameter. Patch 24294590:R12.OWF.B for the 12.1.3+ codeline. Patch 24621637:R12.OWF.C for the 12.2.4+ codeline. Usage Log into Oracle EBS as a user with the Functional Administrator responsibility and select the Functional Administrator responsibility.  Then select the Core Services tab and Profiles sub-tab. Enter "WF: Disable Reassign to Submitter" in the name field (code: WF_DISABLE_REASSIGN_SUBMITTER) and click on Go.  The profile parameter should appear which is defaulted to a value of No, i.e. delegation or reassignment to the submitter is allowed. Update that value to Yes or No as needed, keeping in mind the security vs. flexibility scenarios.

Overview It is generally not a good business practice to have the owner or initiator of a WF transaction to be able to approve it.  There are may common sense examples, including the case when a...

Mailer

Truly Understanding the Inbound Unsolicited Threshold

What is parameter INBOUND_UNSOLICITED_THRESHOLD? There may be some misconceptions and misinterpretations wrt. the Workflow mailer parameter INBOUND_UNSOLICITED_THRESHOLD. First, note that the WF documentation such as the WF Administrator's Guide or WF  Developer's Guide makes no mention of such threshold because it is not configurable from the front end. Second, the examples in the documentation are not meant to be read literally!  For instance, in the WF Administrator's Guide, the reason it mentions raising of the event oracle.apps.wf.mailer.unsolicited on the "second" unsolicited E-mail is not because it is supposed to raise the event for every unsolicited E-mail but because the default threshold (INBOUND_UNSOLICITED_THRESHOLD) is 2.  Notice too the documentation does not mention "second, third, fourth, etc." because that is not the intention of that parameter.  Thus, the documentation is correct in reflecting what the code should do by default.  And if INBOUND_UNSOLICITED_THRESHOLD is changed (obviously from the back end) to a value other than 2, then the documentation would seem broken. The reason INBOUND_UNSOLICITED_THRESHOLD has a value of 2 is just to bring into attention that there are one too many unsolicited E-mails coming from a particular E-mail address because the mailer would only send one E-mail warning and would not be sending warnings forever. i.e.   1st unsolicited E-mail -> warning E-mail   2nd unsolicited E-mail -> raise event   other unsolicited E-mails -> ignored   What about parameter INBOUND_MAX_RET_EMAIL_SIZE? There may also be some misconceptions and misinterpretations that parameter INBOUND_MAX_RET_EMAIL_SIZE is somehow tied to the parameter INBOUND_UNSOLICITED_THRESHOLD.  That is not so.  Parameter INBOUND_MAX_RET_EMAIL_SIZE is a maximum list size of unsolicited E-mail addresses and it is for a circular list.  It has nothing to do with triggering events for unsolicited E-mails.  Using it for any purpose other than what it was intended is a hack and should be avoided.

What is parameter INBOUND_UNSOLICITED_THRESHOLD? There may be some misconceptions and misinterpretations wrt. the Workflow mailer parameter INBOUND_UNSOLICITED_THRESHOLD.First, note that the WF...

Mailer

Enabling WF GUEST Access: Admin Discretion Advised!

Overview Oracle Workflow guest access is a feature that has existed for a long time and is documented in the WF Administrator's Guide.  A quick run through could be obtained from MOS Note 277839.1.  Basically, WF guest access is enabled by setting the profile parameter "WF: GUEST Access to Notification" (WF_VALIDATE_NTF_ACCESS) to Enabled and assigning proper grants to the GUEST user. WF guest access enables anyone, whether an EBS user or not, even outside the business or entity to respond to a notification without having to sign in to EBS.  That is possible if the original recipient is an EBS user registered with WF's directory services and thus was provided an access key embedded in the URL to notification details page.   Caution Enabling WF guest access may be a convenience but there are consequences to be very careful about: The obvious consequence is there is no way of identifying who actually responded since anyone could be GUEST.  Especially if the original recipient was a role which could have multiple users and thus any one of those users could respond as GUEST. If the notification was for an approval, with options to Approve, Reject, or Request More Info, and the approver selects Requests More Info, when the submitter receives that Request More Info notification e-mail and opens the notification link that user will have access to all those options as well.  Thus the submitter could self-approve the request rather than respond to the Request More Info.  Furthermore, the legitimate approver may have no way of knowing the request was self-approved since further notifications (i.e. about the approval) goes to the submitter as well and not the legitimate approver.  The action history will show GUEST as the one approving, not specifically the submitter, thus it cannot be determined who really responded. It is for at least the above two reasons and the potential for misuse that WF guest access is highly discouraged.

Overview Oracle Workflow guest access is a feature that has existed for a long time and is documented in the WF Administrator's Guide.  A quick run through could be obtained from MOS Note 277839.1. ...

Mobile Approvals

EBS Mobile Release 6.1 - Approvals REST APIs in IREP

Overview Starting from Oracle E-Business Suite Mobile Foundation Release 6.1, Oracle provides APIs corresponding to portions of the Oracle E-Business Suite mobile apps' functionality. These APIs are available in the Oracle Integration Repository where you can search, view, and deploy them as REST services. You can also develop and publish additional custom APIs to Oracle Integration Repository that can be deployed as REST services. The standard REST services along with additional custom REST services can be used to develop custom mobile apps on Oracle E-Business Suite either using E-Business Suite Mobile Foundation Login Component or using a mobile application development framework of your choice. Refer to REST API Patches for Custom App Development in Oracle E-Business Suite Mobile Foundation Release 6.1in Oracle E-Business Suite Mobile Apps Administrator's Guide, Release 12.1 and 12.2 for the list of patches to apply to get the EBS Mobile Release APIs. Using EBS Mobile APIs In order to search and manage EBS Mobile APIs easily, they are categorized under a common Business Entity called Mobile Optimized API. You can access the EBS Mobile APIs using below steps. Search Log in to the Oracle Integration Repository through the Integrated SOA Gateway responsibility and click Search. In the Search page, use business entity as Mobile Optimized API to search for all EBS mobile apps' APIs.   In addition to using business entity value as Mobile Optimized API, you can use additional search values to limit the search results. For example, to retrieve the APIs for the Approvals app, you can enter Applications Technology as the product family and search. Browse You can also access required mobile APIs by browsing the list of interfaces using the navigation tree on the left pane. Select Product Family in the View By field and then expand the tree nodes to locate your desired product family, then product, and then business entity. For example, expand the Applications Technology product family node, and then Workflow and select the Mobile Optimized API business entity. Deploy Once required mobile APIs are located in Oracle Integration Repository, you can create grants to allow only authorized users to access these APIs and deploy them as REST services. Once the APIs are deployed as REST services you can use View WADL link to review the REST API definition using the WADL (Web Application Definition Language) file that provides details of the REST resources, input and output data types. For more information about deploying REST services, refer to Chapter 3 Administering Native Services, Oracle E-Business Suite Integrated SOA Gateway Implementation Guide. Mobile Approvals APIs Oracle Workflow publishes a number of APIs that are used by Mobile Approvals app. Mobile Approvals APIs are grouped into two different interfaces. Approvals Management Approvals Details and Actions Approvals Management Provides methods to retrieve Oracle Workflow approval types, get pending approvals for a given type, and get open notifications. Get Open Notifications - Gets a list of all open notifications for the current approver, including both response-required and FYI notifications. To retrieve the list in batches, specify the page size and page number of the batch to retrieve for the current request Get Approval Types - Gets a list of the approval types for which the current approver has at least one pending approval. For example, Expense Reports, Purchase Requisitions, Purchase Orders are example of approval types. Get Pending Approvals by Type - Gets a list of pending approvals for the current approver for a given approval type. Approvals Details and Actions Provides methods to retrieve Oracle Workflow approval details, retrieve approval participants, and perform actions such as requesting information, providing information, and respond to the approval. Get Approval Header - Gets header attributes for a given approval notification. Get Approval Details - Gets additional approval details for a given approval notification and the content for a specific detail region for that notification. Get Approval Participants - Gets a list of users who participated in the approval process for the current approval notification to request more information from. Request Information - Submits a request for more information from a specified approval participant for a given approval notification. Provide Information - Submits information provided by another participant as a reply to the approver who requested the information. Reassign Approval - Reassigns the approval notification to another user. Submit Approval - Submits the Approve or Reject decision for an approval notification, with an optional comment.   References Oracle E-Business Suite Mobile Foundation Release Notes (Doc ID 1642431.1) Oracle E-Business Suite Mobile Apps Administrator's Guide Oracle E-Business Suite Mobile Apps Developer's Guide Oracle E-Business Suite Integrated SOA Gateway Implementation Guide

Overview Starting from Oracle E-Business Suite Mobile Foundation Release 6.1, Oracle provides APIs corresponding to portions of the Oracle E-BusinessSuite mobile apps' functionality. These APIs are...

Mailer

Workflow Notification Mailer - Troubleshooting Outbound Processing

  Overview Oracle E-Business Suite's Workflow Notification Mailer sends an email notification in a multi-step process. If an email notification is not sent, the following needs to be checked. After a workflow notification is sent, it immediately appears in the recipient's EBS Worklist UI. For each workflow notification, business event oracle.apps.wf.notification.send is raised to send the same notification as email. This business event, through its event group oracle.apps.wf.notification.send.group, has the following subscriptions:    Type Source Phase  Rule Function  WF Process  Group  ERROR 50  WF_XML.ERROR_RULE  WFERROR  Group  LOCAL 100   WF_XML.Send_Rule (null)  For a workflow notification to be e-mailed, following statements should be true:   The notification's STATUS is OPEN or CANCELED The notification's MAIL_STATUS is MAIL or INVALID The recipient role has a valid email address  The recipient role's notification preference must be MAILTEXT, MAILATTH, MAILHTML or MAILHTM2 The Workflow Deferred Agent Listener is running The Workflow Notification Mailer is running       Most of the information above can be obtained by running the diagnostic script $FND_TOP/sql/wfmlrdbg.sql. It takes the notification id as input. Tracing Outbound Email Notification After the business event oracle.apps.wf.notification.send is raised, it is processed through two queues before it is actually delivered as email to the recipient's Inbox: WF_DEFERRED LOCAL subscription to the event is responsible for processing the email notification content. Since it's phase is 100, it is enqueued to WF_DEFERRED queue as soon as it is raised. Workflow Deferred Agent Listener dequeues the event and performs below processing Executes generate function associated to the send event - WF_XML.Generate. This generates the email notification payload. Executes the rule function associated to the LOCAL subscription - WF_XML.Send_Rule. This determines if the email is eligible to be sent and enqueues to WF_NOTIFICATION_OUT queue. If an error occurs while processing the send event, the ERROR subscription is executed and the notification event message is placed on WF_ERROR queue. The Workflow Error Agent Listener dispatches the ERROR subscription for oracle.apps.wf.notification.send that executes WF_XML.Error_Rule. WF_NOTIFICATION_OUT The Workflow Notification Mailer dequeues the send event messages from this queue and dispatches it through the designated SMTP server.     To determine at a given time where the email notification is being processed, run $FND_TOP/sql/wfmlrdbg.sql for the notification id. Here are possible stages:     Status of message in queue Explanation or suggested action Message in neither queue Business event was not raised. Check if Business Event System itself is enabled Event group oracle.apps.wf.notification.send.group is ENABLED Event  oracle.apps.wf.notification.send is ENABLED LOCAL subscription to the event group is ENABLED READY in WF_DEFERRED Make sure Workflow Deferred Agent Listener is running PROCESSED in WF_DEFERRED If event was processed successfully, the message should be now in WF_NOTIFICATION_OUT queue or if there was an error, it should be in WF_ERROR queue READY  in WF_NOTIFICATION_OUT Make Workflow Notification Mailer running PROCESSED in WF_NOTIFICATION_OUT The e-mail notification should have been sent to its recipient role READY in WF_ERROR There was an error when processing message from WF_DEFERRED queue. Make sure Workflow Error Agent Listener is running to send the error details to SYSADMIN user's worklist     Email Notification XML Payload When the event is processed from WF_DEFERRED queue, WF_XML.Generate function generates an XML payload that is later used by Workflow Notification Mailer to create the email notification. The XML payload can be seen in the output of wfmlrdbg.sql as it is generated again when this script is run. It is recommended to actually review the XML payload of the email notification already existing in queue WF_NOTIFICATION_OUT which is the source of truth for what the Workflow Notification Mailer would process. To retrieve the original XML payload, the following SQL query can be used: select tab.user_data.text_lob text_lob from applsys.aq$wf_notification_out  tab where tab.USER_DATA.get_string_property('BES_EVENT_KEY') = &NID;  where NID is the notification id in question. Related Articles Increase throughput of your Workflow Mailers Asynchronous Business Event Subscriptions - Troubleshooting Tips

  Overview Oracle E-Business Suite's Workflow Notification Mailer sends an email notification in a multi-step process. If an email notification is not sent, the following needs to be checked. After a...

Mobile Approvals

Customizing Seeded Approvals from Approvals Data Services Manager

Introduction From Oracle E-Business Suite Mobile Foundation Release 6.0 onward, administrators can customize seeded approvals metadata defined while configuring messages from Approvals Data Services Manager. Customization allows administrators to change the way the configured data displays on the Approvals app. Customization support helps in : Protecting Oracle Development seeded meta-data from being inappropriately updated by customers. Protecting custom configuration changes done by customers from being overwritten by standard updates provided by Oracle Development. The protection offered by customization depends on the customization level (Core, Limit or User) at which a particular component is set. Approvals metadata has various components which can be customized like Message/Header attributes, Regions, Views and View attributes. Understanding customization levels Core : No changes can be made to components set at this level. Component's properties can be viewed but update is not allowed. Limit : Component at this level can be enabled/disabled for display in the approvals app, but no other changes can be made to the component. User : Component set at this level are allowed to be modified fully. Most of its properties can be customized. This customization level is automatically set while defining custom configurations. Approvals component hierarchy effect on customization levels In Approvals metadata, there is a parent child relationship maintained between Regions to Views and Views to View attributes. Customization levels also honor this hierarchy such that a child definition is at least as restricted as parents'. Table(a) describes possible value of customization levels: Parent level Possible child level Core Core Limit Core, Limit User Core, Limit, User                                                                           (a) Applying customized configuration There are two cases which occur when uploading approvals metadata into target instance : Customization levels are changed : When Oracle Development updates seeded approvals metadata, then components in that metadata can have changed customization levels. In this case, modification of customization level only from strict to less strict level is allowed, but not the other way because configuration can become less customizable than at existing less strict level. Table(b) describes customization level changes allowed.  Data is changed : Data is uploaded into target instance considering existing customization levels and is uploaded such that configuration doesn't loose any existing customized data based on table(b). Existing value Possible new value Data upload rule Core Limit, User Whole new data is uploaded for the components with this customization level, as no customization was allowed. Limit User Except the Render and Status properties (if present) for the component with this customization level, data is uploaded as these properties were customizable and could have changed. User No Value No new data is uploaded for components with this customization level.                                                                            (b)  References Configuring Messages for Approvals Data Services.

Introduction From Oracle E-Business Suite Mobile Foundation Release 6.0 onward, administrators can customize seeded approvals metadata defined while configuring messages from Approvals Data...

Mobile Approvals

Access Custom Approvals from EBS Mobile Approvals App

Introduction  Starting with Oracle E-Business Suite Mobile Foundation Release 5.0, comes a new framework called "Approvals Data Services Framework" that draws on the Oracle Workflow Notification System infrastructure as well as on specifically configured metadata to make approval notifications available on the Approvals app. As of Mobile Foundation Release 6.0, Approvals app supports the following seeded approval types: Expenses, Requisitions, Purchase Orders, Recruitment, Timecards, Item Changes, Supplier Invoices, Channel Revenue Management (Claims, Budgets and Offers), Human Resources (Absences, Learning and person status change), Inventory (move orders), Maintenance (work orders, work requests and work permits), Order Management (RMAs, sales agreements, and quotes),  Quoting (quotes) and Service Contracts. In addition to these, one can, using Approvals Data Services Framework, configure the Custom approvals that can further be shown from the Approvals app. This enables users to show the custom notification approvals that cater to specific business needs of an organization. Architecture  The following is the architecture of Approvals Data Services Framework:   Approvals Data Services Framework provides tools to create the following artifacts and test them: Configuration Approval Types The following is the menu structure for the same   To have your custom approvals on the Approvals app, all you need to do is follow the following steps: Design Approvals Content Configure Approvals Metadata Create Approval Type Test Approvals Data Services See your approvals from the Approvals App More information on this can be found in Configuring Messages for Oracle Mobile Approvals for Oracle E-Business Suite section of Oracle Workflow Developer Guide. The instructions are common to Oracle E-Business Suite 12.1 and 12.2. Summary To summarize, you can start using the Approvals app for viewing and responding to approval notifications belonging to Approval types listed above. In addition to that, you may want to configure custom approvals that can then be accessed from the Approvals app. The steps for doing the same are provided in the link from Workflow Developer Guide provided earlier. Please stay tuned to this space for more details on configuring your custom approvals on Approvals app. References Oracle Mobile Approvals for Oracle E-Business Suite Release Notes (MOS Doc ID 1642423.1) Oracle E-Business Suite Mobile Foundation Release Notes (MOS Doc ID 1642431.1) Oracle E-Business Suite Mobile Apps, Release 12.1 and 12.2 Documentation Index (MOS Doc ID 1641772.1) Transfer of Information (TOI) Online Training Search for Oracle EBS Mobile Apps on App Store using ‘Oracle EBS America’  

Introduction  Starting with Oracle E-Business Suite Mobile Foundation Release 5.0, comes a new framework called "Approvals Data Services Framework" that draws on the Oracle Workflow Notification...

Mailer

External Address support for Mailer in email notifications

Introduction The links in the email notification points to the value specified in profile option APPS_FRAMEWORK_AGENT. The profile option APPS_FRAMEWORK_AGENT value will be set to an internal server which is the host accessed by users from within network and the user can accesses the email and the links in it from inside corporate network. But when EBS is setup with DMZ, the links in email still points to internal server and cannot be accessed from external server which is the DMZ host accessed by users from outside the firewall. When EBS is setup with DMZ, we need to provide an option to specify external server, and use the same to generate links in the email.  In EBS release 12.2.6, a new feature is provided to support External Address for the users so that Mailer can use this address in the Notification Details html page. Specifying External address A new profile option 'FND_FRAMEWORK_EXTERNAL_AGENT' is introduced to allow the Workflow Administrator to set the External Address value if there is any. Set this profile option value to External address. External and Internal Users The external users who receives the email notifications with external Address and internal users those receives the email notifications with internal Address are identified using the below approach. A new role is defined with name 'WF_EXTERNAL_ROLE' The users that are assigned to this role are treated as External users and others are Internal users The product teams should have logic to assign this role internally when creating their external users. eg: suppliers The administrator or individual user need to assign 'Workflow External User' role manually if they want their users to be treated as external When does the external address will be used in email notification Workflow Notification Mailer uses the external address for the Notification Details page in email notification as per the below scenarios. Scenario 1: FND_FRAMEWORK_EXTERNAL_AGENT profile option value is not setWhen the 'FND_FRAMEWORK_EXTERNAL_AGENT' profile option value is not set, Workflow Notification Mailer uses the APPS_FRAMEWORK_AGENT to construct the base URL Scenario 2: FND_FRAMEWORK_EXTERNAL_AGENT profile option value is set to external addressWhen the 'FND_FRAMEWORK_EXTERNAL_AGENT' profile option value is set, Workflow Notification Mailer verifies whether the recipient is external or internal user. Case i:  Recipient user is assigned to 'WF_EXTERNAL_ROLE' role.      When the Recipient user is assigned to 'WF_EXTERNAL_ROLE' role, then Mailer treats it as external user and uses the  FND_FRAMEWORK_EXTERNAL_AGENT to construct the base URL for Notification Details page Case ii:  Recipient user is not assigned to 'WF_EXTERNAL_ROLE' role.      When the Recipient user is not assigned to 'WF_EXTERNAL_ROLE' role, then Mailer treats it as internal user and uses the APPS_FRAMEWORK_AGENT value to construct the base URL for Notification Details page

Introduction The links in the email notification points to the value specified in profile option APPS_FRAMEWORK_AGENT. The profile option APPS_FRAMEWORK_AGENT value will be set to an internal...

Mailer

Oracle E-Business Suite attachments support in email notifications

Introduction The Oracle E-Business Suite attachments included within OA Framework region appear as separate links for each document alongside the 'Attachment(s)' Label in the 'Notification Header' section of the email notification. The user needs to login to the application in order to view/access the attachments. From EBS release 12.2.5 on wards, Mailer supports the E-Business Suite attachments that would be attached directly to the emails. E-Business Suite attachment types There are 4 types of E-Business Suite attachments that are supported in the email attachments. File - To attach the specified file in the email notification Short Text - To attach the text file of maximum 4000 characters length Long Text - To attach the text file of larger size. It is stored internally as CLOB URL - To attach html files that opens the given URL Defining E-Business Suite Attachments in workflow notifications The E-Business Suite attachments can be added in the workflow notifications by using the below steps. Create a special message attribute with internal name of '#ATTACHMENTS' and source type as 'Document'. The message attribute value should contain the following fields Entity Primary Key 1 Name Primary Key 1 Value Primary Key 2 Name Primary Key 2 Value Primary Key 3 Name Primary Key 3 Value Primary Key 4 Name Primary Key 4 Value Primary Key 5 Name Primary Key 5 Value Optional fields Categories Category_name1 Category_name2   The '#ATTACHMENTS' attribute value should be specified in the format 'FND:entity=<Entity> &pk1name=<Key Name>&pk1value=<Key Value>&categories=CUSTOM105,MISC. The value for 'entity' can be obtained from DATA_OBJECT_CODE column and primary key names can be obtained from PK1_COLUMN, PK2_COLUMN..etc in FND_DOCUMENT_ENTITIES. The primary key values ' pk1value' can be obtained from PK1_VALUE, PK2_VALUE ..etc columns in FND_ATTACHED_DOCUMENTS. The Categories field is optional, if its not specified Oracle E-Business Suite Attachments will be displayed for ALL document categories. The category internal and display names can be obtained from the view FND_DOCUMENT_CATEGORIES_VL columns name and user_name. The internal category names separated with commas should be specified for categories field in the attribute value. Following is the screenshot of email notification with E-Business Suite attachments.    

Introduction The Oracle E-Business Suite attachments included within OA Framework region appear as separate links for each document alongside the 'Attachment(s)' Label in the 'Notification Header'...

Who Started This? - Determining the Initiator of a Notification Thread

IntroductionWhen logging into Oracle EBS as a user who has a part in a workflow process, such as an approver in a purchase order, that user could easily determine the owner or initiator of the request by simply opening the notification and reviewing the action history.  The sysadmin user could also view the action history of the notification.But that is a visual (UI) display of the initiator info and not something that could be captured in hand.  For example, there may be a need for a third-party audit or metrics collection or perhaps that info is to be used in custom code or script such as in another workflow process.  So, how would one obtain that single piece of info quicker and in hand?The user who started a notification thread would be the one listed in the WF_COMMENTS table with an action of SEND_FIRST.  Of course, one could query the WF_COMMENTS table to determine the initiator while correlating with the WF_NOTIFICATIONS table based on the notification.  But it may not be that straightforward and quick because a typical single transaction involving multiple users and notifications (requesting more info, delegating, approving, etc.) would have multiple notification IDs.  Then multiply that by the fact there would be many more than a single transaction in a real-life production system where even a sequential list of notification IDs is not just for a single workflow or notification thread. SolutionA single transaction or notification thread can be identified by its context.  That context could be used to trace back who initiated it and that involves checking both current and non-current activities as there may be multiple related and unrelated transactions of the same workflow process.The context could be obtained from the WF_NOTIFICATIONS table based on a particular notification ID, and then its individual components (item type, item key, and process activity ID) could be extracted via string functions and operators.Based on the components of that context the following query could be used to easily obtain the initiator info.  Note that an activity could either be current or non-current so even with the union all operator there is no chance of getting multiple row returns from this query.select FROM_ROLE(  select FROM_ROLE    from WF_ITEM_ACTIVITY_STATUSES IAS,         WF_COMMENTS C    where IAS.ITEM_TYPE        = <item_type>      and IAS.ITEM_KEY         = <item_key>      and IAS.PROCESS_ACTIVITY = <process_activity_ID>      and IAS.NOTIFICATION_ID  = C.NOTIFICATION_ID      and C.ACTION             = 'SEND_FIRST'  union all  select FROM_ROLE    from WF_ITEM_ACTIVITY_STATUSES_H IAS,         WF_COMMENTS C    where IAS.ITEM_TYPE        = <item_type>      and IAS.ITEM_KEY         = <item_key>      and IAS.PROCESS_ACTIVITY = <process_activity_ID>      and IAS.NOTIFICATION_ID  = C.NOTIFICATION_ID      and C.ACTION             = 'SEND_FIRST');A self-contained PL/SQL block using the above query could be implemented to obtain the initiator of any notification thread from just a single notification ID belonging to that thread. Practical ScenarioThe following approval structure is set up but KWALKER has a vacation/routing rule to BPALMER:[CBAKER] -> [CBROWN] -> [KWALKER] -> [BERICKSO] Steps:- CBAKER creates a request via iProcurement, which is sent to CBROWN.- CBROWN requests info from CBAKER.- CBAKER provides info to CBROWN.- CBROWN approves request and is sent to next level (KWALKER).- KWALKER's routing rule kicks in and delegates approval to BPALMER.- BPALMER approves request and is sent to next level (BERICKSO).- BERICKSO manually delegates approval to WTUCKER.- WTUCKER requests info from BERICKSO.- BERICKSO provides info to WTUCKER.- WTUCKER requests info from DLEWIS (perhaps another department).- DLEWIS provides info to WTUCKER.- WTUCKER finally approves request.- CBAKER receives confirmation of approval. Results:Checking the WF_NOTIFICATIONS and WF_COMMENTS tables:SQL> select notification_id nid, status, from_role, responder  2    from WF_NOTIFICATIONS  3    where notification_id >= 4681928  4    order by notification_id;NID     STATUS FROM_ROLE  RESPONDER------- ------ ---------- ----------4681928 CLOSED CBAKER     CBROWN4681929 CLOSED KWALKER    BPALMER4681930 CLOSED DLEWIS     WTUCKER4681931 CLOSED BERICKSO   CBAKERSQL> select notification_id nid, from_role, to_role, action, user_comment  2    from WF_COMMENTS  3    where notification_id >= 4681928  4    order by comment_date, notification_id, sequence;NID     FROM_ROLE  TO_ROLE    ACTION          USER_COMMENT------- ---------- ---------- --------------- ------------------------------4681928 CBAKER     CBROWN     SEND_FIRST4681928 CBROWN     CBAKER     QUESTION        Request info from CBAKER.4681928 CBAKER     CBROWN     ANSWER          Provide info to CBROWN.4681928 CBROWN     KWALKER    RESPOND4681929 CBROWN     KWALKER    SEND4681929 KWALKER    BPALMER    DELEGATE_RULE   Delegate (route) to BPALMER.4681929 BPALMER    BERICKSO   RESPOND4681930 KWALKER    BERICKSO   SEND4681930 BERICKSO   WTUCKER    DELEGATE        Delegate (manual) to WTUCKER.4681930 WTUCKER    BERICKSO   QUESTION        Request info from BERICKSO.4681930 BERICKSO   WTUCKER    ANSWER          Provide info to WTUCKER.4681930 WTUCKER    DLEWIS     QUESTION        Request info from DLEWIS.4681930 DLEWIS     WTUCKER    ANSWER          Provide info to WTUCKER.4681930 WTUCKER    WF_SYSTEM  RESPOND4681931 BERICKSO   CBAKER     SEND_FIRSTIn this hypothetical scenario we might wonder what is special about this request such that BERICKSO chose to delegate approval to WTUCKER and then that was followed by a few back-and-forth questions and answers.  Thus, we would want to determine who the initiator is and question the user.  If this list was 100's of lines long and mixed up with other transactions it would not be as obvious to determine who the initiator was although it might seem obvious from this short scenario.By obtaining the context from the WF_NOTIFICATIONS table for the notification ID associated with the manual delegation from BERICKSO to WTUCKER (i.e. 4681930) and then plugging in the components of that context into the above query the initiator could be correctly identified as CBAKER.

IntroductionWhen logging into Oracle EBS as a user who has a part in a workflow process, such as an approver in a purchase order, that user could easily determine the owner or initiator of the request...

Mobile Approvals

Announcing Approvals for EBS 1.5.0 and E-Business Suite Mobile Release 6.0

Introduction Oracle E-Business Suite Development announced in August 2016, availability of E-Business Suite Mobile Release 6.0. The new release includes following updates. Functional Updates to Existing Apps Mobile iProcurement: Self-service receiving for better 3-way match compliance for supplier payment Mobile Self-Service Human Resources:  Ability for managers to initiate employee assignment updates, view work history and pending transactions for their team; Ability for employees in United States to update tax forms; Ability to configure menus; Ability to view payslips for 9 additional localizations Mobile Approvals:  Support for approval types for Human Resources, Projects, and Service Contracts; enhanced Quoting approval to support customization Mobile Maintenance: Customer driven enhancements, including completion of operation from work order flow, enhanced work order creation and completion, and more Mobile Product Information: Ability to upload image for item All Mobile Apps: Ability to easily clear sign in; Ability to open links to external websites within app New App Mobile Yard: Yard personnel to quickly view and manage yard activity on the go, including the ability to track appointments, view equipment, verify yard capacity, and process check-in and check-out transactions Deployment and Development Technical Updates with uptake of Oracle Mobile Application Framework 2.3.1 Mobile Approvals E-Business Suite Mobile Release 6.0 includes mobile app Approvals for EBS 1.5.0 with following features: Existing approval type Human Resources was updated to include person status change Introduced new approval types for Projects and Service Contracts Existing approval type Quoting was enhanced to support customization Seeded Approval Types Approvals for EBS mobile app supports following approval types in E-Business Suite Mobile Release 6.0.   Custom Approval Types Apart from leveraging seeded approval types that are shipped by Oracle E-Business Suite applications, Customers can create custom approval types to include custom workflow approvals into the mobile app. In order to manage custom approval types and customize seeded approval types as allowed, Oracle E-Business Suite provides Approvals Data Services Framework. 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. Summary Deploy smartphone apps with EBS 12.1.3 or 12.2 Realize the benefits of going mobile Faster business execution Faster decision making Increased user satisfaction Provide feedback about Oracle E-Business Suite mobile apps from My Oracle Support Community spaces. Mobile Foundation - Mobile Approvals - EBS (MOSC) Related Articles Now Available: Approvals for EBS 1.4.0 for iOS and Android

Introduction Oracle E-Business Suite Development announced in August 2016, availability of E-Business Suite Mobile Release 6.0. The new release includes following updates. Functional Updates to Existing...

Business Event System

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.

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)...

Mailer

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 

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,...

Mailer

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 connectionThe standalone program can be run as below  $java GmailSMTPTest GMailUsername GMailPassword recipientEmailAddress   Standalone program to verify the IMAP connectionIf 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

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...

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: *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) *Ability to view attachments for approval requests *Ability to add custom workflows Enhanced Pending Approvals by Type page: Improved approval type icons (achromatic icons) Real-time pending approvals count Dynamically show only those approval types with pending approvals Improved subject card view in the Details page Enhanced refresh interaction using a Pull to Refresh gesture in place of the Sync icon Mobile Foundation: *User installation and usage metrics for administrators Updated look and feel for selected app interactions Technical updates with uptake 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. Design Approvals Content Configure Approvals Metadata Test Approvals Data Services Create Approval Type 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 Oracle Mobile Approvals for Oracle E-Business Suite Release Notes (MOS Doc ID 1642423.1) Oracle E-Business Suite Mobile Foundation Release Notes (MOS Doc ID 1642431.1) Oracle E-Business Suite Mobile Apps, Release 12.1 and 12.2 Documentation Index (MOS Doc ID 1641772.1) Transfer of Information (TOI) Online Training Search for Oracle EBS Mobile Apps on App Store using ‘Oracle EBS America’

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...

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: Reuse application code to display content inside workflow notifications 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. From the corresponding Apps env's web tier, copy following directories $JAVA_TOP/oracle/apps/fnd/wf/directory to $JDEV_USER_HOME/myglobalclasses/oracle/apps/fnd/wf/directory $JAVA_TOP/oracle/apps/fnd/wf/utilities to $JDEV_USER_HOME/myglobalclasses/oracle/apps/fnd/wf/utilities $JAVA_TOP/oracle/apps/fnd/wf/worklist to $JDEV_USER_HOME/myglobalclasses/oracle/apps/fnd/wf/worklist PLEASE NOTE: These are all the seeded Oracle Workflow's run-time Java class files that are executed by worklist pages when rendering All classes under myglobalclasses directory take precedence over the default libraries loaded by JDeveloper From the corresponding Apps env's web tier, copy directory $FND_TOP/mds/wf/worklist/webui to $JDEV_USER_HOME/myprojects/oracle/apps/fnd/wf/worklist/webui PLEASE NOTE: These are all the seeded Oracle Workflow's run-time OAF page definitions used to render worklist pages All JRAD XML files have to be copied under myprojects. These are automatically pulled by JDeveloper into the project when you refresh it. 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. For 11i, the application.xml is under $JDEV_USER_HOME/system9.0.3.x.x/oc4j-config/ Setup the compile and run directories to use myglobalclasses folder instead of the default myclasses folder Go to Project Properties -> Project Content -> Output Directory. Change myclasses to myglobalclasses Go to Project Properties -> Run/Debug -> Select Default -> Edit -> Run Directory. Change myclasses to myglobalclasses 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. In the same JDeveloper, setup your application specific OAF regions that you want to embed into workflow notifications. Complete required setup to embed the region into Workflow Notifications as per Workflow Developer Guide. 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.

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: Reuse application code...

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

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...

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.txtSQL>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: Workflow item type and itemkey Workflow activities' start time and end time Parent process and child process information Status and result of each activity Start date and End date of each activity Errored Activities and Error Process Activity Statuses Notification details with notification id and notification recipient 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.

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...

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.

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....

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: Oracle Mobile Approvals for Oracle E-Business Suite Implementation Guide, Release 12.1 and Release 12.2 (Doc ID 1642423.1) References Oracle E-Business Suite Mobile Apps, Release 12.1 and 12.2 Documentation (Doc ID 1641772.1) Oracle E-Business Suite Mobile Apps Administrator's Guide - 12.1 and 12.2 (Part No. E64384-02) Oracle E-Business Suite Mobile Apps Frequently Asked Questions (FAQ) (Doc ID 2064887.1) Oracle E-Business Suite Release 12.1 and 12.2 Transfer of Information (TOI) Online Training (Doc ID 807319.1) Related Articles Mobile Apps for Oracle E-Business Suite Accessing Oracle E-Business Suite from Mobile Apps Update New versions of the smartphone app Approvals for EBS has been released. Refer to following posts. Now Available: Approvals for EBS 1.4.0 for iOS and Android Please go to the blog post for latest version of Approvals app and post your comments.

Oracle Mobile Approvals for Oracle E-Business Suite lets you respond on-the-go to your pending approval requests. From your phone, anywhereand anytime, take quick action on approval requests...

Checking for Proper Language Translation in Workflow Directory Services

IntroductionWhen 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 PatchesTo 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 ROLE21538803:R12.OWF.C - 1OFF:12.2.3:21289350 FORWARD PORT: WF_DIRECTORY.ADD_LANGUAGE DOES NOT ADD MISSINGPreliminary SetupAlso, 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 Use appropriate AD tools to add languages, e.g. French and Danish. Log into Oracle EBS as sysadmin using the English interface. 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". Log out. 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: Log into Oracle EBS as sysadmin using one of the other language interfaces, e.g. French. 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". Log out. 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: Log into Oracle EBS as sysadmin using another one of the other languages, e.g. Danish. 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". Log out. 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: 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. After all the language-specific description modifications the WF_LOCAL_ROLES_TL table for this role should look something like the following:

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...

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: Oracle Workflow Administrator's Guide Oracle Workflow Developer's Guide Oracle Workflow User's Guide

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...

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. a.  Worklist Access Link for Proxy Configuration 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 grantedii.    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. Optionof granting access for responsibilities as well the option of granting noneitem 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.   SwitchUser Button for going to the list of users for which proxy has been granted tothe 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 globalheader of switching user, to go to the list of users (grantors). Also, if auser A switches to user B, for it to switch to User C, it has to switch back toitself. Refer fig.(h). the figure shows the info regarding the proxy and current user, h. Global Header Changes

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...

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.

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...

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.

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...

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 Note 1642423.1 - Oracle Mobile Approvals for Oracle E-Business Suite Implementation Guide, Release 12.1 and Release 12.2 Note 1642431.1 - Oracle E-Business Suite Mobile Apps Administrator's Guide for Release 12.1 and Release 12.2 Online Training - Implement and Use Mobile Approvals Update New versions of the smartphone app Approvals for EBS has been released. Refer to following posts. Approvals for EBS 1.3.0 for iOS and Android is now available Now Available: Approvals for EBS 1.4.0 for iOS and Android Please go to the blog post for latest version of Approvals app and post your comments.

About Oracle Mobile Approvals for Oracle E-Business Suite lets you respond on-the-go to your pending approval requests. From your phone, anywhereand anytime, take quick action on approval requests...

Server

Different ways of sending workflow notifications

Introduction There are various ways of sending workflow notifications using Workflow Activities like Notification Activity with 'Expand Roles' flag checked, Notification Activity without 'Expand Roles' flag checked, APIs like WF_NOTIFICATION.Send, WF_NOTIFICATION.SendGroup, and using Business Event System Subscription Action Type 'Send Notification'. Using Workflow Activities A notification can be sent by running a workflow process having the notification activity. The recipient of the notification can be set by using the Performer attribute in the notification activity, and the message template can be set using the Message attribute. If the performer value is a role, then the 'Expand Roles' flag will be used to decide whether to send the same copy to all the users of the role or a different copy. Notification Activity without "Expand Roles"  The 'Expand Roles' flag will be unchecked by default, sends the same copy of notification to all its users and that notification is visible in the notification queue of all the users in that role. If one user in that role responds to or closes that notification, the notification is removed from the notification queue of all other users in that role. Notification Activity with "Expand Roles" The 'Expand Roles' flag when checked, sends a different copy of the notification to all its users. The notification remains in a user's notification queue until the user responds to or closes the notification. By both checking Expand Roles and specifying a post-notification function, you can create a voting activity. A voting activity is a notification activity that first sends a notification message to a group of users in a role and then executes a PL/SQL post-notification function to tally the users' responses and determines the transition to the next activity. Refer to WF dev guide for more details about voting activity. Using APIs A notification with the specified message can be sent to the role by calling the WF_NOTIFICATION.send() and WF_NOTIFICATION.sendGroup() APIs without actually launching the workflow process. The message template that needs to be sent has to be defined within a workflow item type . WF_NOTIFICATION.Send This function sends the specified message to a user/role and returns a notification ID if successful. The specification of the function is shown below. function SEND (role in varchar2, msg_type in varchar2, msg_name in varchar2, due_date in date default null, callback in varchar2 default null, context in varchar2 default null, send_comment in varchar2 default null priority in number default null) return number;Example: declare nid number; msg_type varchar2(100) := 'WFTESTS'; msg_name varchar2(100) := 'PLSQL_MSG'; role varchar2(320) := 'TESTUSER';begin nid := wf_notification.send(role, msg_type, msg_name); commit;end;/ WF_NOTIFICATION.SendGroup This function sends a separate copy of notification to all the users assigned to the specified role and returns the notification group ID if successful. All the notifications have the same group id, which is the first notification id sent. function SendGroup (role in varchar2, msg_type in varchar2, msg_name in varchar2, due_date in date default null, callback in varchar2 default null, context in varchar2 default null, send_comment in varchar2 default null priority in number default null) return number; Example:   declare nid number; msg_type varchar2(100) := 'WFTESTS'; msg_name varchar2(100) := 'PLSQL_MSG'; role varchar2(320) := 'TESTUSER';begin nid := wf_notification.SendGroup(role, msg_type, msg_name); commit;end;/ Using Business Event System Subscription Action Type The notification can also be sent by raising the business event for which the subscription action type is specified as 'Send Notification - Send a notification using a standard or custom message template'. As part of the subscription definition, the workflow item type and message name for the message you want to send and role that should receive the notification must be specified. In this approach, you do not need to define or run a workflow process to send a notification from a subscription. However, you do need to define the message you want to send within a workflow item type. The list of values for the Message Name field includes only the messages within the item type you specify. Following are the subscription mandatory parameters Message Type - An item type that contains the message definition Message Name- An message template for the notification you want to send Recipient  - A role that should receive the notification Priority - Normal, High, or Low as the priority for the message. Following are the subscription optional parameters Callback - Custom callback function you want the Notification System to use for communication of SEND and RESPOND source message attributes. Context - The context information for a workflow process instance that needs to passed to the callback function. The context information consists of the item type, item key, and activity ID in the following format:<itemtype>:<itemkey>:<activityid> Comment - A comment to send with the message

Introduction There are various ways of sending workflow notifications using Workflow Activities like Notification Activity with 'Expand Roles' flag checked, Notification Activity without 'Expand Roles'...

New Features to Global Worklist Header in 12.2.4

Enhancements to Global Worklist Header In 12.2.4 Skyros UI, a new branding profile "FND_BRANDING_SIZE" is introduced to control the layout of the global header. Worklist header is part of the universal global header and hence is controlled by this profile. The default value for "FND_BRANDING_SIZE" is "Icons Only". This renders the worklist header as bell icon along with badge of open notifications count. On click of the bell icon, the worklist menu is displayed.  If value for "FND_BRANDING_SIZE" is "Links only", the worklist header renders as "Worklist(<open notifications count>)" text link. On click of the "Worklist" link, the worklist menu is displayed.  If value for "FND_BRANDING_SIZE" is "Both Links and Icons", the worklist header renders as a bell icon along with a badge of open notifications count of the user and the "Worklist" link below the icon. On click of the bell icon or "Worklist" link, the worklist menu is displayed.  The layout of the worklist menu is enhanced and the recent 8 open notifications of the user are displayed. Each notification section has From user and Sent Date in the first row, Subject and Due Date in the second row. The worklist menu has a better look and feel with enhanced styles compared to its older 12.2.3 version. On page reload or refresh, the worklist header is rendered to display the current open notification count of the user. Availability The worklist header enhancements are available across all OAF and JTT pages by default from Oracle E-Business Suite Release 12.2.4 It is i18n enabled and also supports more accessibility features. User can open the worklist menu by pressing keys like Enter, Space Bar, Up and Down Arrow on the worklist icon/link in the global header. The Global worklist header enhancements are available in all supported browsers of Oracle E-Business Suite Release 12.2.4 Screenshot

Enhancements to Global Worklist Header In 12.2.4 Skyros UI, a new branding profile "FND_BRANDING_SIZE" is introduced to control the layout of the globalheader. Worklist header is part of the universal...

New Version of Approvals Smartphone App is Available - Version 1.1.0

What's New? A new version (1.1.0) of Approvals for EBS smartphone app is available on Apple App Store. This new version provides following updates. - Improvements to login and configuration flow- Ability to change server URL without reinstalling the app.- *Diagnostics improvements.- After updating to version 1.1.0, you are required to reconfigure the app to implement the configuration and login improvements included in this version.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, marked by an asterisk (*).For more information on these updates, see My Oracle Support Note 1641772.1. Support for Demilitarized Zone (DMZ) If your mobile users need to access the Oracle E-Business Suite mobile apps over the Internet, your Oracle E-Business Suite environment must be set up in a DMZ configuration. For DMZ configuration instructions on Oracle E-Business Suite Release 12.1, see My Oracle Support Note 380490.1. For DMZ configuration instructions on Oracle E-Business Suite Release 12.2, see My Oracle Support Note 1375670.1. Additionally, when setting up the configuration file for your mobile app as described in Section 2.2.1, ensure that the service endpoint (or server host URL in Oracle E-Business Suite Mobile Foundation releases earlier than Release 2.1) is set to your external web entry point. Update New versions of the smartphone app Approvals for EBS has been released. Refer to following posts. Now Available: Approvals for EBS 1.2.0 on iOS and Android Approvals for EBS 1.3.0 for iOS and Android is now available Now Available: Approvals for EBS 1.4.0 for iOS and Android Please go to the blog post for latest version of Approvals app and post your comments.

What's New? A new version (1.1.0) of Approvals for EBS smartphone app is available on Apple App Store. This new version provides following updates. - Improvements to login and configuration flow-...

e-Signing a Workflow Notification fails on Internet Explorer on Windows 7

With the launch of Windows 7 Operating system, Microsoft has ceased to support CAPICOM DLL. CAPICOM is an ActiveX control used to digitally sign data, display certificates from browser and to encrypt and decrypt data. In Oracle Workflow Notification System, CAPICOM has been used to digitally sign Notification data in Workflow Notifications. This is only used when signing a workflow notification from Internet Explorer. Now that this DLL is not included in Windows 7 and further versions of Windows OS, the signing process of a Workflow Notification, requiring certificate based signature, errors out with the following message: "Verification of signature has failed" Oracle Workflow provides a solution to this issue. The patch numbers for obtaining this solution on the following code-lines are as follows: Release 11.5.10.7RUP - 18921580 Release 12.1.3 - 18921687:R12.OWF.B Release 12.2.3 - 19410235:R12.OWF.C Due to the removal of dependency on an ActiveX control, there are certain subliminal changes in the user interface for signing a workflow notification. The following are two changes: 1. Change in the look of Certificate Selection window. The list of certificates to be selected are now loaded in an Applet. This is as shown below: 2. A small time lag after clicking on Sign button. A informative message conveying the same is displayed. The screenshot of the same is as shown below.Please wait for the applet to be loaded after clicking on "Sign". There would be an applet loaded as shown in earlier image a user can select certificate for signing the notification data. Apart from these negligible changes, the whole signing experience is in parity with that which existed before Windows 7 plus Internet Explorer as browser.

With the launch of Windows 7 Operating system, Microsoft has ceased to support CAPICOM DLL. CAPICOM is an ActiveX control used to digitally sign data, display certificates from browser and to encrypt...

Scheduling Workflow Mailer Stop and Start events as good practice

The Workflow Notification Mailer runs on the generic service called Workflow Mailer Service, which in turn uses the Standard workshift, active 24 hours every day. This means the notification mailer runs non-stop.  Not allowing the mailer to restart periodically may present some issues: The corresponding log file corresponding to the Workflow Mailer Service will grow several gigabytes large and eventually the OS will error due to I/O file handling. Some SMTP servers and IMAP servers used for outbound and inbound processing respectively, have time limits to the sessions connected to them. If the mailer reaches those time limits it will encounter a connection error and will be abnormally brought down. Similarly the same might happen to the session the mailer establishes to retrieve HTML and framework content from the application server. This is less likely though but it has been seen. The size of the INBOX folder in the IMAP server won't get expunged as long as the mailer does not refreshes the session. For a purged INBOX folder it takes the mailer to restart - and for parameter Expunge Inbox on Close to be set to Yes. In order for the issues above to be minimized, a good practice is to schedule the Mailer to automatically go down and start up periodically, daily preferably. This can be done by using the event schedule feature provided in the Workflow Mailer configuration. This can be done as follows: First, connect to EBS using the responsibility Workflow Administrator Web Applications and go to Workflow Manager Then edit the Notification Mailer configuration. Continue going through the train stages until you reach the Schedule Events stage: Use the corresponding link/button to create two events, one for stopping the mailer and one for starting it up: In the screen shot above the stopping event is set to happen at 11:50 PM and the starting event at 11:55 PM, and these are set to repeat every using 1440 minutes as the interval. If no interval value is specified the stop/start events are raised just once.

The Workflow Notification Mailer runs on the generic service called Workflow Mailer Service, which in turn uses the Standard workshift, active 24 hours every day. This means the notification mailer...

Server

Improving worklist notification response performance by deferring the processing

Overview When the user responds to a notification from self-service worklist, the control will be returned back to theresponder only after processing the subsequent function activities and othersynchronous activities until the next blocking activity. If there is any costly activity defined after the notification activity user can experience performance problems. There is a feature available from E-Business Suite Release 12.2 onwards to defer the response processing to a later time and the control will be returned back immediately so that the response time will be faster. When a notification is responded in a deferred mode, a message with event name 'oracle.apps.wf.notification.wl.response.message' and other required properties will be enqueued in to WF_NOTIFICATION_IN queue for later processing, and the notification will be closed immediately. The 'Workflow Inbound Notification AgentListener' dequeues the message fromWF_NOTIFICATION_IN queue and executes its subscription to complete the notification activity and the subsequent activities. Notification Response mode The Workflow Administrator actually determines the response processing mode for their application whether to process all the workflows synchronously or to defer all the workflows or only some specific workflow types. There is a section available in the Workflow Administration page to select the required processing mode. There are 3 options available NONE - None of the workflows response processing will be deferred. This is the default behavior. ALL - All the workflows will be deferred for processing SPECIFIC - Only the specified workflows will be deferred. There are buttons available to Add or Delete or Search for the required item types Tracing the responded notification The responded notification processing can be traced in the below sequence. Check the STATUS column in WF_NOTIFICATIONS table                                                                                                The notification status will be changed to CLOSED once the control returns back to the worklist and a message will be enqueued to WF_NOTIFICATION_IN queue with READY state                       select notification_id, status from wf_notifications where notification_id = <nid>; Check the message state in WF_NOTIFICATION_IN queue                                                                                               The 'Workflow Inbound Notification Agent Listener' component processes the messages in WF_NOTIFICATION_IN queue and the message state will be changed to PROCESSED                                       select win.user_data.GET_STRING_PROPERTY('NOTIFICATION_ID') NID, msg_state, enq_time, deq_time from applsys.aq$wf_notification_in win where win.user_data.GET_STRING_PROPERTY('NOTIFICATION_ID') = <nid>; Check the notification activity status in WF_ITEM_ACTIVITY_STATUSES table                                                             The notification activity status will be COMPLETE once the message is processed by the 'Workflow Inbound Notification Agent Listener' component                                                                                                                         SELECT notification_id, activity_status FROM wf_item_activity_statuses where notification_id = <nid>; Handling Errors When there is any error occurs during the deferredresponse processing, WFERROR/NTF_DEFER_RESP_PROCESS_ERROR workflow process will be launched and anerror notification will be sent to the Workflow Administrator role. The ERROR notification contains the information about the Errored notification details, Error message and Error stack. The Error notification contains two response attributes 'Retry' and 'Resolved'. Retry When the problem that is causing the error is fixed and no longer exists, submit the 'Retry' response so that the actual notification will be enqueued to WF_NOTIFICATION_IN queue and will be processed again. Resolved When the problem that is causing the error is resolved now and the notification is COMPLETE, then submit the 'Resolved' response so that the error notification will also be closed. IMPORTANT NOTE: The 'Workflow Inbound Notification Agent Listener' component should be up and running for the above feature to work

Overview When the user responds to a notification from self-service worklist, the control will be returned back to the responder only after processing the subsequent function activities and othersynchro...

Global Worklist Header - New Feature on 12.2.3

About Global worklist header is a new feature available from Oracle E-Business Suite Release 12.2.3 in Skyros look and feel. Worklist header icon is the bell icon which is displayed by default in all HTML based pages in the global header, the worklist header icon is an alternative to including a worklist region in the Oracle E-Business Suite home page. Users can select the icon to view a summarized list of recent notifications, and then click to view and respond to each notification in the notification details page. With this feature, users can view the recent notifications from any HTML page by default instead of navigating back to homepage and checking the homepage worklist for any recent notifications. The open notification count is displayed next to worklist header icon in the global header and the count gets updated on page reload or refresh. On click of the worklist header icon, the worklist menu opens up, which displays the recent 10 notifications. Each notification section shows from user, notification subject link,  sent date and due date of the notification. Users can navigate to notification details page on click of the notification subject link and can review and respond to the notification. The subject text is displayed up to 45 characters and on hover of the subject link, the full subject text is shown. The worklist menu also displays the ‘Go to Full Worklist’ link at the bottom and on click of link, user is taken to the advanced worklist page. The worklist menu can be closed by clicking anywhere outside the menu. If the open notification count for the user is zero, then on click of the worklist header icon, the worklist menu still opens up and displays the message ‘No open notifications found for the user’ along with the ‘Go to Full Worklist’ link at the bottom. Configuration Though worklist header icon is rendered by default, the icon rendering in the global header can be controlled at user, responsibility, application, site levels using profile ‘WF_ENABLE_WORKLIST_HEADER’. By setting the profile value, the worklist header icon can be rendered on/off at various levels. Availability  The worklist header feature is available across all OAF and JTT pages by default from Oracle E-Business Suite Release 12.2.3 It is i18n enabled and also supports accessibility features. The opening and closing of the worklist menu and the navigation to each notification component, full worklist link, navigation to notification details page, advanced worklist page from the worklist menu links can be fully controlled by keyboard. The navigation with the keyboard is the same as in any HTML page. In addition, user can navigate through the notifications using key up and key down as well. The global worklist header feature is available in all supported browsers of Oracle E-Business Suite Release 12.2.3 Screenshot Additional Resources Release12.2.3 is currently available on My Oracle Support as Patch17020683. This patch needs to be applied on top of Oracle E-Business SuiteRelease 12.2.2. Instructions for downloading and applying the patch are in the OracleE-Business Suite Release 12.2.3 Readme, Note 1586214.1. 

About Global worklist header is a new feature available from Oracle E-Business Suite Release 12.2.3 in Skyros look and feel. Worklistheader icon is the bell icon which is displayed by default in...

'The signing operation has failed' - common error with Password Based Signatures in Workflow Notifications

Introduction Oracle Workflow Notifications can be digitally signed in two ways. Password Based Signature X.509 Certificate Based Signature. Following are the authentication mechanisms categorized based on whether or not a user subscribes to Single Sign-On (SSO). Pure E-Business Suite User (Otherwise called FND User) Pure SSO user Hybrid Mechanism (Both E-Business Suite and SSO) Issues and Reasons Time and again we have encountered a problem with Password Based signatures failing with the error: 'The signing operation has failed'. This happens when the notification is signed using SSO credentials i.e., the logged in user is an SSO user. The profile option APPS_SSO_LOCAL_LOGIN, controls the type of authentication mechanism to be used for a user at the time of user-creation. More information about SSO can be found here.  The possible values of this profile option are: LOCAL - Login is only allowed via Oracle E-Business Suite local login. SSO - Login is only allowed through Single Sign-On. The password is set to ‘EXTERNAL’ after a single sign-on account and an application account are linked. BOTH - Login can be through both Single Sign-On and Oracle E-Business Suite. (Please note this is only a separate authentication mechanism but not a different class of users created for it) Now based on the type of login/authentication mechanism, the signing differs. For a FND User, the validation utility validates the credentials by fetching password from FND_USER table from ENCRYPTED_USER_PASSWORD column. For a pure SSO user, the ENCRYPTED_USER_PASSWORD column is set to 'EXTERNAL' in FND_USER table and the Validation utility fetches the password from OID to validate it. For a user using both types of authentication mechanisms, the Validation utility validates it as it does in the case of a FND User i.e., comparing the ENCRYPTED_USER_PASSWORD from FND_USER table. This implies that when the user uses authentication mechanism for both FND User and SSO user then the password for FND User is used for validation. Also, it is to be noted that the password for SSO user is case sensitive and the password for FND user depends upon a profile option SIGNON_PASSWORD_CASE. In short: An SSO user password is case-sensitive. An FND User password is by default case insensitive. It depends upon the value of profile option 'SIGNON_PASSWORD_CASE' to have it case sensitive or case insensitive. For a user which uses both authentication mechanisms, the password for FND user is used for signing. It would be good if these passwords are maintained in sync. Hence, when one encounters the error mentioned here, the starting point to investigate is to see what type of user it is. Mostly it would be user using authentication mechanism for both FND User and SSO User. In that case the FND User password should be used and not the SSO password. Also the case of the password should be heeded to as that can well be the cause of the issue. 

Introduction Oracle Workflow Notifications can be digitally signed in two ways. Password Based Signature X.509 Certificate Based Signature. Following are the authentication mechanisms categorized based...

Business event does not raise - event raises and does nothing

Frequently, new functionality is created using the Business Event System or existing functionality is changed by adding new subscriptions, changing subscription phases, changing underlying java or PLSQL code, etc, and inexplicably it is found that raising the business event does not produce the desired result, as if the event was not raised. The execution of an event consists basically of two parts: raising the event and do all required validation, and then finding the subscriptions to that event and execute them. The first one barely ever fails while the second one is more pron to issues and it is the one that we are mostly interested in as it has the code/behavior we want to see. The following steps will allow to identify the actual cause of the issue by making use of the FND debugging options provided in EBS. 1. Adjust the subscription phase Since subscriptions with phase higher than 100 are deferred you would need to raise the event on one session and then go to another session and debug it. It is easier if you can set the phase to a value lower than 100, say 99, and then raise the event in the session. This way you will be able to see everything the happens related to this event subscription, a nothing else. Remember to ensure that the business event has an error subscription so that if something happens it does not go silent. By adding an ERROR subscription the system administrator would be able to see as notification explaining any failures. See here for more on error subscription definitions.  2. Raise the event Do so either from the application or from java or PLSQL. Here is an example from PLSQL. Note: you can make use of wf_log_pkg.init to enable the FND: Debug option for this session only, so there is no need to change the corresponding profile options. The last parameter ('wf%') means the debug is enabled for the WF code, which the BES belongs to. declare  l_parameters wf_parameter_list_t := wf_parameter_list_t();begin --Optional: --execute immediate 'truncate table applsys.fnd_log_messages'; --Add your parameters here  wf_log_pkg.init(1, null, 1, 'wf%');  wf_event.addparametertolist(p_name          => 'NOTIFICATION_ID',                              p_value         => '123',                              p_parameterlist => l_parameters);  wf_event.raise(p_event_name => 'oracle.apps.wf.notification.send',                 p_event_key  => '123',                 p_event_data => NULL,                 p_parameters => l_parameters,                 p_send_date  => sysdate); commit;end;/anonymous block completed  3. Find the logging details  Now, within the same session check the log messages generated by the Business Event System: SELECT 'Level: '||log_level||       ' Module: '||module||       ' Time: '||to_char(timestamp, 'DD-MON-RR HH24:MI:SS')||       wf_core.newline||       '>>> Message: '||message_textFROM   fnd_log_messagesWHERE  audsid  =  sys_context('userenv', 'SESSIONID')ORDER BY log_sequence/   The final lines found in the query above and the error notifications sent to the system administrator will point out to the error causing the event to look as it did not fire.

Frequently, new functionality is created using the Business Event System or existing functionality is changed by adding new subscriptions, changing subscription phases, changing underlying java or...

Now Available: Oracle Mobile Approvals for iOS - Version 1.0.0

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. Availability Client Oracle Mobile Approvals for Oracle E-Business Suite 1.0.0 is available on Apple's iTunes Store  for download. Oracle Mobile Approvals on iTunes 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. Patch 18964693:R12.ATG_PF.B: ATG Consolidated Patch for Oracle E-Business Suite Mobile Applications Foundation, Release 12.1 V1 Patch 18964716:R12.ATG_PF.C: ATG Consolidated Patch for Oracle E-Business Suite Mobile Applications Foundation, Release 12.2 V1 Screens Additional Resources Note 1642423.1 - Oracle Mobile Approvals for Oracle E-Business Suite Implementation Guide, Release 12.1 and Release 12.2 Note 1642431.1 - Oracle E-Business Suite Mobile Apps Administrator's Guide for Release 12.1 and Release 12.2 Online Training - Implement and Use Mobile Approvals Update New versions of the smartphone app Approvals for EBS has been released. Refer to following posts. New Version of Approvals Smartphone App 1.1.0 is Available Now Available: Approvals for EBS 1.2.0 on iOS and Android Approvals for EBS 1.3.0 for iOS and Android is now available Now Available: Approvals for EBS 1.4.0 for iOS and Android Please go to the blog post for latest version of Approvals app and post your comments.

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...

Understanding Workflow Errors - Part 2

This post is a follow up to Understanding Workflow Errors - Part 1 that deals with some of the most common errors raised when some of the Workflow Engine APIs are used.  The way to trap and continue processing upon finding these errors is the same: evaluate the content of global variable WF_CORE.error_name. Error message: 3103 Attribute 'ATTRIBUTE' does not exist for item 'TYPE/KEY' Error code: WFENG_ITEM_ATTR The most likely reason for this to happen is a change in the Workflow definition. For instance, an attribute is added to the definition while there are open workflows  not having it. Then you attempt to set a value for it and notice new processes have no issues but the ones created before the change will fail. Whenever possible these regressions should be foreseen and addressed in the design of the Workflow definition. A possible workaround is to add the supporting plsql code to check for the exception: ...EXCEPTION  WHEN OTHERS THEN    if WF_CORE.error_name='WFENG_ITEM_ATTR' then      wf_engine.additemattr(...);    end if;END; Error message: 3120 Activity 'TYPE/ACTID' has no performer Error code WFENG_NOTIFICATION_PERFORMER    All performers of notification activities are taken from view WF_ROLES. The most typical reason for this error is the originally assigned user/role has been expired. While the correct fix is to unexpire the performer and let the workflow complete, there are two possible ways to go around: 1. Change the value to the attribute holding the name of the performer. For instance, if there is an item attribute RECIPIENT pointing to expired user JSMITH then change it to an active user/role, say JHOPKINS. You will need to call this from a SQL*Plus session:    WF_ENGINE.SetItemAttrText(<type>, <key>, 'RECIPIENT', 'JHOPKINS'); 2. The other alternative is to change all references in ALL the workflow runtime tables to change anything related to JSMITH and change it to the taking over user JHOPKINS: declare  l_attributes wf_parameter_list_t;begin  WF_EVENT.AddParameterToList('OLD_USER_NAME', 'JSMITH', l_attributes);  WF_EVENT.AddParameterToList('USER_NAME', 'JHOPKINS', l_attributes);  WF_LOCAL_SYNCH.Propagate_Role(p_orig_system    => <orig system>, --for instance, 'FND_USR' or 'PER'                                p_orig_system_id => l_orig_system_id, --for instance, 29984                                p_attributes     => l_attributes);end; As always, use care and try these APIs on a test environment before using them in production Error message: 3133 Activity instance 'NAME' is not a notified activity for item 'TYPE/KEY'. Error code: WFENG_NOT_NOTIFIED When it is needed that the processing of a workflow stops to wait for something to happen a BLOCK function can be used. When the block function runs the activity goes into NOTIFIED status. Then, to resume the processing, an explicit call to WF_ENGINE.CompleteActivity must be made. But if the activity is not in notified status then the API will raise WFENG_NOT_NOTIFIED. There is no straightforward action to deal with this error. Rather, troubleshooting must be made to see why the activity did not go into NOTIFIED status. Sometimes the reason is that the workflow process continued to the next activity, leaving it complete or did not even reach it. A good way to understand what the workflow process has done is to run the seeded script wfstat.sql. Check the output and follow the execution path.

This post is a follow up to Understanding Workflow Errors - Part 1 that deals with some of the most common errors raised when some of the Workflow Engine APIs are used.  The way to trap and continue...

Understanding Workflow Errors - Part 1

Workflow Engine and other public APIs throw a variety of user-defined exceptions to indicate specific Engine conditions. It is important that Workflow Developers and Administrators understand the meaning of these errors and know how to deal with them. In this post, we will cover some of the important Workflow Engine related errors and understand how to deal with them. Engine Errors 3122: Duplicate item 'ITEM_TYPE/ITEM_KEY' could not be created Error Code: WFENG_ITEM_UNIQUE Cause: This error may be thrown from WF_ENGINE.CreateProcess when you attempt to create a new instance of a workflow item. This error indicates that the Item Type and Item Key combination you have used to launch a new workflow instance already exists. Fix: Use an unique item key for that item type 3136: Item 'ITEM_TYPE/ITEM_KEY' cannot be accessed while synchronous process in progress. Error Code: WFENG_SYNCH_ITEM Cause: This error may be thrown from WF_ENGINE.CreateProcess when attempt to create a new synchronous workflow instance while another synchronous workflow  instance is already running in the same session. All synchronous workflow instances are created with item key #SYNCH to make sure the workflow instances starts and completes in the same session. Fix: Wait till the existing synchronous workflow instance completes before creating another one. 3146: Commit happened in activity/function 'ACTIVITY/FUNCTION' Error Code: WFENG_COMMIT_INSIDE Cause: While a workflow instance was executing, a commit was issued inside a PLSQL procedure associated to a workflow function activity before or after an error occurred in that PLSQL procedure. The Workflow Engine traps errors produced by function activities by setting a savepoint before each function activity. If an activity produces an unhandled exception, the engine performs a rollback to the savepoint, and sets the activity to the ERROR status.  Fix: You should never commit within the PL/SQL procedure of a function activity. The Workflow Engine never issues a commit as it is the responsibility of the calling application to commit. How to Catch these Errors? Each error above is associated to an Error Code. Within your PLSQL code, you should following method to capture the specific error based on the error code to act on it.  begin   Wf_Engine.CreateProcess('ITEM_TYPE', 'ITEM_KEY', 'PROCESS_NAME'); exception    when others then if (wf_core.error_name = 'WFENG_ITEM_UNIQUE') then        -- Item already exists, use a unique item key        Wf_Engine.CreateProcess('ITEM_TYPE', 'ITEM_KEY', 'PROCESS_NAME');      end if; end

Workflow Engine and other public APIs throw a variety of user-defined exceptions to indicate specific Engine conditions. It is important that Workflow Developers and Administrators understand the...

Oracle Workflow - E-Business Suite vs Standalone

Oracle Workflow has following components that together provide business process management capabilities. Workflow Builder - Windows desktop tool to create and customize workflow processes Workflow Loader - Commandline tool to load workflow definition to database Workflow Engine - PLSQL engine to execute workflow processes definitions Workflow Notification Mailer - Background program to send e-mail notifications Workflow Business Event System - PLSQL and Java based engine to process business events Workflow Agent Listeners - Background program to process business events Oracle Workflow has been available in two flavors until about 2007. Workflow Embedded in E-Business Suite Workflow Standalone This post is aimed at clarifying how Oracle Workflow in E-Business Suite and Standalone were versioned and the current status of these two flavors. Workflow Embedded in E-Business Suite In E-Business Suite, Oracle Workflow is an integral part of foundation modules (FND) alongside Profiles, Concurrent Programs, OA Framework and so on and is shipped in ATG patchsets. For example, Workflow's Java classes are available under $JAVA_TOP like any other FND module, it's PLSQL packages are compiled into APPS schema, datamodel is available under APPLSYS schema and so on. This flavor is also called Embedded Workflow, meaning it was embedded in E-Business Suite. Versions Embedded Workflow's packaging, release, installation and configuration follows standard E-Business suite's processes. Embedded Workflow is  not identified with it's own version in E-Business Suite, instead it's codelevel is always identified using the current ATG Patchset level. For example, if a Customer is on E-Business Suite Release 12.1.3, then their Workflow codelevel is R12.ATG_PF.B.Delta.3 (in addition to any Workflow specific one-offs that may have applied on the baseline) which is a standard way of identifying any FND module. Current Status Oracle Workflow embedded in E-Business Suite has the same lifetime as the ATG Patchset in that Oracle E-Business Suite. For example, if a Customer is on Oracle E-Business Suite 12.1.3, the workflow module being integral part of Oracle E-Business Suite installation, has no different lifetime or support policy than the R12.ATG_PF.B Patchset. When looking at Oracle Workflow module from E-Business Suite standpoint, it should not be viewed as a separate product rather at the ATG Patchset level. For more information please refer to  Oracle's Lifetime Support Policy E-Business Suite Error Correction Support Policy (Note 1195034.1) Workflow Standalone All of Oracle Workflow's sub-components and the underlying data model were packaged, released, installed and configured completely independent of E-Business Suite until about late 2007.  A Workflow Standalone installation results in a special database schema like OWF_MGR specific to workflow alone unlike the standard APPS and APPLSYS schemas of E-Business Suite. Oracle Workflow Standalone however was not shipped on it's own but using following release vehicles. Oracle Database - Upto 10gR2 Oracle iAS - Upto 10.1.2 Oracle Warehouse Builder - Still packaged with OWB 11g Oracle Workflow standalone product was available as part of above product offerings which helped non-E-Business Suite Customers to have Workflow (Business Process Management) capabilities such as implementing custom approval flows, document approval routing, sending notifications and so on. Versions Workflow Standalone had it's own versioning mechanism that was completely unrelated to E-Business Suite's. For example, following were some of the recent verisons. Oracle Database 10gR2 - Oracle Workflow 2.6.4 Oracle iAS 10.1.2 - Oracle Workflow 2.6.3.5 Oracle iAS 9.0.4 - Oracle Workflow 2.6.3 Current Status With the advent of better Business Process Execution standards and tools, Oracle Workflow was ceased to be shipped with Oracle Database and Oracle iAS. Oracle BPEL Process Manager (in Oracle SOA Suite 10g) and Oracle SOA Suite 11g are the recommended technologies to be considered by Customers who were using Oracle Workflow Standalone to implement their business processes. To this effect, MOS Doc 391546.1 was published to announce obsolescence of Workflow Standalone with Oracle Database 10gR2 as the terminal release vehicle for the product. This note applies only to Workflow as a standalone product and does not affect Workflow embedded in E-Business Suite. Workflow Standalone Obsolescence Notice MOS Doc 391546.1 clarifies that Workflow as a standalone product will not be shipped in Oracle Database 11g and above. Since Oracle Database 10gR2 is the last release Workflow Standalone was shipped in, existing Oracle Database 10gR2 Customers who also use Workflow Standalone will continue to receive support as per Oracle Database 10gR2 support policies. This obsolescence notice should not be confused with support for Workflow embedded in E-Business Suite. Summary Oracle Workflow embedded in E-Business Suite is being actively maintained and enhancements implemented based on Customer requirements. We have some exciting days ahead of us for Oracle Workflow embedded in E-Business Suite.

Oracle Workflow has following components that together provide business process management capabilities. Workflow Builder - Windows desktop tool to create and customize workflow processes Workflow...

New Worklist features on 12.1.3

Following new Worklist features are available on E-Business Suite 12.1.3 via Patch 13646173. Ability to view comments on top of a notification If an action is performed on a notification such as Reassign, Request for Information or Provide Information, the recipient of the notification will see who performed the last action and the associated comment on top of the notification. Reassigning a request for information notification If an approver requests more information on a notification from it's submitter, the submitter now has two options Answer Request for More Information Transfer Request for More Information If the submitter thinks the requested information can be provided by another user, he/she can transfer the request to the other user. Please note that only Transfer is supported for Request for More Information. Once transferred, the submitter cannot access the notification and provide the requested information. Use actual sent date when reassigning a notification The Sent field in notification header always showed the date on which the notification was first created. If the notification was later reassigned, the Sent date was not updated to show the last action date. This caused problems in following scenario Approval notification was sent to JACK on 01-JAN-2012 JACK waited for 10 days before reassigning to JILL on 10-JAN-2012 JILL does not see the notification as sent on 10-JAN-2012, instead sees it as sent on 01-JAN-2012 Although the notification was originally created on 01-JAN-2012, it was sent to JILL only on 10-JAN-2012 The enhancement now shows the correct sent date in Worklist and Notification Details page. Figure 1 - Depicts all the above 3 features Related Action History for response required notification So far it was possible to embed Action History of an response-required notification into another FYI notification using #RELATED_HISTORY attribute (Please refer to Workflow Developer Guide for details about this attribute). The enhancement now enables developers to embed Action History of one response-required notification into another response-required notification. To embed Action History of one response-required notification into another, create message attribute #RELATED_HISTORY. To this attribute set a value during run-time in the following format. {TITLE}[ITEM_TYPE:ITEM_KEY]PROCESS_NAME:ACTIVITY_LABEL_NAMEThe TITLE, ITEM_TYPE and ITEM_KEY are optional values. TITLE is used as Related Action History header title. If TITLE is not present, then a default title "Related Action History" is shown. If ITEM_TYPE is present and ITEM_KEY is not, For Example: {TITLE}[ITEM_TYPE]PROCESS_NAME:ACTIVITY_LABEL_NAME , the Related Action History is populated from parent item type of the current item. If both ITEM_TYPE and ITEM_KEY is present, For Example: {TITLE}[ITEM_TYPE:ITEM_KEY]PROCESS_NAME:ACTIVITY_LABEL_NAME , the Related Action History is populated from that specific instance activity. Figure 2 - Depicts Related Action History feature

Following new Worklist features are available on E-Business Suite 12.1.3 via Patch 13646173. Ability to view comments on top of a notification If an action is performed on a notification such as...

Mailer

Using GMail's SMTP and IMAP servers in Notification Mailer

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. 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 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 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 -----BEGIN CERTIFICATE-----MIIDWzCCAsSgAwIBAgIKaNPuGwADAAAisjANBgkqhkiG9w0BAQUFADBGMQswCQYDVQQGEwJVUzETMBEGA1UEChMKR29vZ2xlIEluYzEiMCAGA1UEAxMZR29vZ2xlIEludGVybmV0IEF1dGhvcml0eTAeFw0xMTAyMTYwNDQzMDRaFw0xMjAyMTYwNDUzMDRaMGgxCzAJBgNVBAYTAlVTMRMwEQYDVQQIEwpDYWxpZm9ybmlhMRYwFAYDVQQHEw1Nb3VudGFpbiBWaWV3MRMwEQYDVQQKEwpHb29nbGUgSW5jMRcwFQYDVQQDEw5pbWFwLmdtYWlsLmNvbTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAqfPyPSEHpfzvXx+9zGUxoxcOXFrGKCbZ8bfUd8JonC7rfId32t0gyAoLCgM6eU4lN05VenNZUoChL/nrX+ApdMQv9UFV58aYSBMU/pMmK5GXansbXlpHao09Mc8eur2xV+4cnEtxUvzpco/OaG15HDXcr46c6hN6P4EEFRcb0ccCAwEAAaOCASwwggEoMB0GA1UdDgQWBBQj27IIOfeIMyk1hDRzfALz4WpRtzAfBgNVHSMEGDAWgBS/wDDr9UMRPme6npH7/Gra42sSJDBbBgNVHR8EVDBSMFCgTqBMhkpodHRwOi8vd3d3LmdzdGF0aWMuY29tL0dvb2dsZUludGVybmV0QXV0aG9yaXR5L0dvb2dsZUludGVybmV0QXV0aG9yaXR5LmNybDBmBggrBgEFBQcBAQRaMFgwVgYIKwYBBQUHMAKGSmh0dHA6Ly93d3cuZ3N0YXRpYy5jb20vR29vZ2xlSW50ZXJuZXRBdXRob3JpdHkvR29vZ2xlSW50ZXJuZXRBdXRob3JpdHkuY3J0MCEGCSsGAQQBgjcUAgQUHhIAVwBlAGIAUwBlAHIAdgBlAHIwDQYJKoZIhvcNAQEFBQADgYEAxHVhW4aII3BPrKQGUdhOLMmdUyyr3TVmhJM9tPKhcKQ/IcBYUev6gLsB7FH/n2bIJkkIilwZWIsj9jVJaQyJWP84Hjs3kus4fTpAOHKkLqrbIZDYjwVueLmbOqr1U1bNe4E/LTyEf37+Y5hcveWBQduIZnHn1sDE2gA7LnUxvAU=-----END CERTIFICATE----- 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    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

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...

Implementing a post-notification function to perform custom validation

Introduction 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); Modes 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.  Example 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;    begin      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);          WF_CORE.RAISE('WFNTF_TRANSFER_FAIL');        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: Check the Workflow API Reference Guide, section Post-Notification Functions, to see all the standard, seeded WF_ENGINE variables available for extending notifications processing. 

Introduction 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...

E-Business Suite Proactive Support - Workflow Analyzer

Overview 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. Installation 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.

Overview 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...

Asynchronous Business Event Subscriptions - Troubleshooting Tips

Overview 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);         begin        --processing...        l_result := p_event.GetValueForParameter('OUTCOME');        if l_result='GOOD' then          insert into temp_table values (p_event.getEventKey(), l_result);          return 'SUCCESS';        else          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;      exception        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.       declare      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;    begin      --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);      commit;    end;    / 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 1select 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=YESFND: Debug Log Level=STATEMENTFND: 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.declare...begin  fnd_global.apps_initialize(user_id=>0, resp_id=>24240, resp_appl_id=>1);  --...end;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 messagessqlplus apps/***** @$FND_TOP/patch/115/sql/wfdbgprnt wf.plsql% References Oracle Workflow API Reference, section 'Event Subscription Rule Function APIs' Oracle Workflow Developer's Guide, section 'Error Handling for Event Subscription Processing'

Overview 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...

SMTP Authentication Feature in R12.1.3

Overview 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, An attacker could hijack the SMTP connection either pretending the server does not support the Authentication extension or causing all AUTH commands to fail. 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 25Trying 10.11.12.13...Connected to smtp.host.com (10.11.12.13).Escape character is '^]'.220 smtp.host.com ESMTP Sendmail 8.13.8/8.13.8; Tue, 17 Apr 2012 10:11:36 -0400EHLO smtp.host.com250-smtp.host.com Hello my.desktop.com [12.13.14.15], pleased to meet you250-ENHANCEDSTATUSCODES250-PIPELINING250-8BITMIME250-SIZE250-DSN250-ETRN250-AUTH LOGIN PLAIN250-STARTTLS250-DELIVERBY250 HELP

Overview 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...

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. Symptoms 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 Checks 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:  Refer to Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1) for recommendations on setting init.ora parameter JOB_QUEUE_PROCESSES 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. Additional Resources EBS 12.2 Maintenance Guide Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1)

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...

SSL in Oracle Workflow

IntroductionThis 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 NotificationMailer 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, Always verify that the JVM from which Workflow code initiates a SSL connection has the required root certificate installed If the server presents a certificate chain to validate, then the complete chain is installed on the client side. 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, GraphMonitorCO.java - Check connectivity from $OA_JRE_TOP/bin/java by using this JRE's trust-store to the web-server. 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. openssl utility available in Unix based platforms This sample  SSLSocketClientTest.java class can be used to test a handshaking from the Java run-time Download the Java class source code in a directory. There is no package name for this Java class. Compile SSLSocketClientTest.java as javac -classpath $CLASSPATH SSLSocketClientTest.java 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. Navigate to the $OA_JRE_TOP/lib/security directory Backup the existing cacerts file. Copy your ca.crt and server.crt files to this directory. Issue the following command to insure that cacerts has write permissions.    chmod u+w cacerts Add your Apache ca.crt and server.crt to cacerts keytool -import -alias ApacheRootCA -file ca.crt -trustcacerts -v -keystore cacerts keytool -import -alias ApacheServer -file server.crt -trustcacerts -v -keystore cacerts 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. Conclusion 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?

Introduction 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...

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) ORExplicitly mark the activity as blocked by associating a Blocking Function (wf_standard.block), a PLSQL function with itWhen 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: 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.Workflow Page Flow can be used with Train and Navigator bar in a page, which the traditional 'Destination Function' based approach does not support.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.

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 conditionalnavigation...

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 TableSQL> EXEC DBMS_AQADM.create_queue_table( queue_table=>'test_queue_tab', queue_payload_type=>'event_msg_type')PL/SQL procedure successfully completed. --Create QueueSQL> EXEC DBMS_AQADM.create_queue(queue_name =>'test_queue', queue_table=>'test_queue_tab');PL/SQL procedure successfully completed.--Check for locks on statsSQL> 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';OWNER TABLE_NAME STAT---------- ---------------- ----- APPS TEST_QUEUE_TAB ALL --Gather statsSQL> 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' ,force=>TRUE)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 WHERE job_name = 'GATHER_STATS_JOB';JOB_NAME ENABLE------------------------------ ------GATHER_STATS_JOB FALSE 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';OWNER TABLE_NAME STATT LAST_ANAL-------------- --------------- ----- ---------APPLSYS WF_DEFERRED ALL 23-FEB-09SQL> 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 1505ORA-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';OWNER TABLE_NAME STATT LAST_ANALYZED---------------- ---------------- ----- -------------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?

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 choosefrom the various...

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: The process taking too long a time (>40 hours) to complete when notifications were being sent to around 35K users. 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.

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...

Business Event System

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 AQ Payload is SYSTEM.WF_PAYLOAD_T AQ Payload is WF_EVENT_T 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.

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 W...

Business Event System

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 Execute PLSQL rule function synchronously or asynchronously. Oracle Workflow provides a bunch of default rule functions in WF_RULE package for your use Execute Java rule function synchronously or asynchronously. Oracle Workflow provides a default rule function oracle.apps.fnd.wf.bes.WebServiceInvokerSubscription to invoke web services Launch workflow process Send event to an agent (AQ) Event Raise APIs Workflow Business Events can be raised using one of two following ways. PLSQL API WF_EVENT.Raise 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. Execute PLSQL subscriptions with phase < 100 in the same session Enqueue PLSQL subscriptions with phase >= 100 to WF_DEFERRED queue 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. Execute JAVA subscriptions (such as WebServiceInvokerSubscription) with phase < 100 in the same session Enqueue JAVA subscriptions with phase >= 100 to WF_JAVA_DEFERRED queue Execute PLSQL subscriptions with phase < 100 in the same session 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()

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...

Oracle

Integrated Cloud Applications & Platform Services