Thursday, October 16, 2014

AXF Redirect error from EBS workflow

Recently I had one test regarding viewing the managed attachment from EBS workflow.  During the test there was one error shown as the below when clicking on Managed Attachment.   The issue happened from OAF page to redirect to UCM url.  After some investigation I found one thing which causes this issue.  In AXF schema of EBS database there is table called: OAF_AXF_PROPERTIES.  One property is AXF_SOAP_USER.  Its value is UCM admin user name.  Because the wrong user name is put here it leads to the error.  After the value is corrected the issue is solved. 

Wednesday, September 10, 2014

Responsibility in EBS

In EBS it uses responsibilities to control user access to menus, forms, applications, data, functions and queries in Oracle E-Business Suite.   One EBS user is assigned certain responsibilities so he can access the application or modules to perform the works.  Meanwhile the responsibility also restricts him from other applications which are not open to the user.

Responsibilities are created and assigned through another responsibility called “System Administrator”.

Responsibility is associated with application.     One application can have multiple responsibilities.   One responsibility defines the access level for associated application.

Oracle comes with many pre-defined responsibilities.   Custom responsibility can be created.

A responsibility can be assigned to many users and a user can be assigned many responsibilities.  A user with multiple responsibilities can switch responsibilities after login into EBS.

Behind the scene all these are stored in several database tables:

Here the prefix FND stands for foundation table, suffix TL means that this table is basically the same as the table without TL but with language support.

FND_USER table stores details about the application user information such as name, email, encrypted password and etc.

Name                                                                   Null     Type         
----------------------------- ------------------------------------------------ -------------
USER_ID                                                            NOT NULL NUMBER(15)   
USER_NAME                                                    NOT NULL VARCHAR2(100)
EMAIL_ADDRESS                                                                VARCHAR2(240)

FND_APPLICATION table defines the information about all applications in EBS.

Name                                                 Null               Type        
----------------------                            --------           ------------
APPLICATION_ID                            NOT NULL  NUMBER      
LAST_UPDATE_DATE                    NOT NULL DATE        
LAST_UPDATED_BY                      NOT NULL NUMBER(15)  
CREATION_DATE                           NOT NULL DATE        
CREATED_BY                                  NOT NULL NUMBER(15)  
LAST_UPDATE_LOGIN                 NUMBER(15)  
BASEPATH                                      VARCHAR2(20)
PRODUCT_CODE                          VARCHAR2(50)

Responsibility is stored in the following tables:

FND_RESPONSIBILTY table defines the information about all responsibilities in EBS.   It has responsibility Key which is the short name for responsibility. It also has its associated application and data group which is related to table FND_SECURITY_GROUPS.

Name                                                 Null               Type        
----------------------                            --------           ------------
APPLICATION_ID                            NOT NULL NUMBER(15)  
RESPONSIBILITY_ID                       NOT NULL NUMBER(15) 
DATA_GROUP_ID                          NOT NULL NUMBER(15)      

FND_RESPONSIBILITY_TL is almost same as FND_RESPONSIBILITY.   But it has the name of responsibility, which can is any user understandable name in specified language.

Name                                         Null               Type         
------------------- -------- -----------------------------------------------
APPLICATION_ID                     NOT NULL NUMBER       
LANGUAGE                              NOT NULL  VARCHAR2(4)  
DESCRIPTION                                               VARCHAR2(240)

FND_SECURITY_GROUP Stores information about security groups used to partition data.

Name               Null     Type        
------------------ -------- ------------

----------------- ------------------------------ ---------- ------------- --------------- ---------------- -----------------
                0 STANDARD                                1 09/OCT/98                   2 22/OCT/01                        0

FND_USER_RESP_GROUPS will define the assigned responsibilities for the users.  It is here that user – responsibility association is defined.

Name                          Null     Type      
----------------------------- -------- ----------
USER_ID                                                  NOT NULL NUMBER(15)
RESPONSIBILITY_ID                                                  NUMBER    
SECURITY_GROUP_ID                         NOT NULL NUMBER(15)
START_DATE                                                              DATE      
END_DATE                                                                 DATE      
DESCRIPTION                                                            VARCHAR2()
CREATED_BY                                                             NUMBER    
CREATION_DATE                                                      DATE      
LAST_UPDATED_BY                                                 NUMBER    
LAST_UPDATE_DATE                                               DATE      
LAST_UPDATE_LOGIN                                            NUMBER 

After understanding the schema of the above tables we can have some PL/SQL codes which can be used to query and manipulate the responsibility in the background.

Find the application short name by Responsibility name

                 FND_APPLICATION FA,
                 FND_SECURITY_GROUPS FRG,
      AND    FRT.LANGUAGE = 'US'
      AND    FRT.RESPONSIBILITY_NAME = 'Payables Manager';

Check the user’s responsibilities
              FND_USER fu,
              FND_RESPONSIBILITY_TL frt
   AND    fu.USER_NAME  = 'CHEM307'

Check the user’s responsibilities
set serveroutput on format wrapped;

   v_user_name                  VARCHAR2 (100) := 'CHEM307';
   v_responsibility_name  VARCHAR2 (100) := 'Payables Manager';
   v_application_name      VARCHAR2 (50);
   v_responsibility_key      VARCHAR2 (30);
   v_security_group           VARCHAR2 (30);
   v_description                  VARCHAR2 (240);

   ----   Get the responsibility key, application name, security group and etc. for given responsibility name
      INTO  v_application_name,
                 FND_APPLICATION fa,
                 FND_SCURITY_GROUPS frg,
                 FND_RESPONSIBILITY_TL frt
      AND    frt.LANGUAGE = 'US'
      AND    frt.RESPONSIBILITY_NAME = v_responsibility_name;
    ----   Invoke addresp in fnd_user_pkg to assign the responsibility to the user
    fnd_user_pkg.addresp (username=> v_user_name,
                                               resp_app=> v_application_name,
                                               resp_key=> v_responsibility_key,
                                              security_group => v_security_group,
                                              description=> v_description,
                                              start_date=> SYSDATE,
                                              end_date=> NULL);

   DBMS_OUTPUT.put_line (   'Responsiblity ' || v_responsibility_name || ' is assigned to the user ' || v_user_name);
      DBMS_OUTPUT.put_line(   'Error occurred while assigning responsibility to the user. The error is '|| SQLERRM );

Tuesday, June 24, 2014

Change JDK used by JDeveloper

You can change JDK used by JDeveloper.
The JDK configuration for JDeveloper is defined in the jdev.conf which located in your Jdeveloper folder JDeveloper\jdeveloper\jdev\bin.

Open the file and find the item:  SetJavaHome.  Change its value to your new JDK folder.

In the below example I switch JDK from JDK 7 to JDK 6 by commenting out JDK 7 and adding a new item for JDK 6. 

#SetJavaHome C:\Java\jdk1.7.0_06
SetJavaHome C:\Program Files\Java\jdk1.6.0_45

After saving the changes restart JDeveloper and it will use the new JDK set in jdev,conf.

You can check JDK used in JDeveloper from JDeveloper.   From JDeveloper goes to Help->About.  Then select the tab Properties and will find java.home properties now is set to the JDK specified in jdev.conf.

Monday, June 9, 2014

Set the composite name using function ora:setCompositeInstanceTitle()

ora:setCompositeInstanceTitle() can be used to set the name of the composite instance.  It is very useful because you can set the name of the composite instance using the values with business meaning.  It would be convenient for the support people to search for a particular instance by the business value.

It is can be done in either BPEL component or Mediator component.

Within BPEL component what needs to be done is set one variable using ora:setCompositeInstanceTitle() which can be found from Advanced Functions.  

<variable name="CompositeTitle" type="xsd:string"/>
<assign name="SetInstanceName">
        <from expression="ora:setCompositeInstanceTitle(concat('Test   :', ora:getCompositeInstanceId()) )"/>
        <to variable="CompositeTitle"/>

Within Mediator component it is done by setting one property using ora:setCompositeInstanceTitle().   Clicking on Assign Values button in the below diagram will start to set the value to the property.

In Assign Value popup window To type is selected as property and the property value is select as: tracking.compositeInstanceId.

From type is selected as expression. In the expression Builder select ora:setCompositeInstanceTitle() which can be found from Mediator Expression Functions.

Now the property is assigned which will set the composite title in Mediator.

Sunday, May 25, 2014

Sending email with attachments in Oracle SOA 11g

Here I show one generic BPLE service which will send the email with attachment.  

Generic Email Service Interface

The service interface is defined by the below xsd file.  In the request message apart from email to, from, subject and body elements there is one array of attachment elements which are sent with the email.

<element name="NotificationRequest">
   <element name="From" type="string" minOccurs="0"/>
   <element name="To" type="string"/>
   <element name="CC" type="string" minOccurs="0"/>
   <element name="Subject" type="string"/>
   <element name="Body" type="string" minOccurs="0"/>
   <element name="Attachment" type="tns:AttachmentType" minOccurs="0" maxOccurs="unbounded"/>
<complexType name="AttachmentType">
  <element name="MimeType" type="string"/>
  <element name="AttachmentContent" type="anyType"/>
  <element name="AttachmentName" type="string"/>
  <element name="ContentEncoding" type="string"/>

Oracle Notification Service

This service will use Notification service provided by Oracle SOA 11g. Notification service uses Oracle User Messaging Service to send email, SMS, IM and etc. notification to the recipients.   Notification service can be invoked from BPEL process.   From JDeveloper you can choose the different notification channels.  For sending email you need to choose Email as below.

After dragging and dropping Email into BPEL process, NotificationService.wsdl and NotificationService.xsd are added to the project.  These wsdl and xsd files define the interface to Oracle User Messaging Service.  For request message which sends email is defined as EmailPayloadType.  The key to send the email with attachments is to populate the appropriate values in EmailPayload.

EmailPayloadType defines the request message for invoking Oracle Notification service to send the email.  
<xsd:complexType name="ContentType">
  <xsd:element name="MimeType" type="xsd:string" default="text/plain" minOccurs="0"/>
  <xsd:element name="ContentBody" type="xsd:anyType" nillable="true"/>
  <xsd:element name="ContentEncoding" type="xsd:string" nillable="true"/>

ContentType defines the email content.  Note here that ContentBody is defined as anyType.  In order to include the attachments in email there are two things needed to be done:

  • MimeType should be set as: multipart/mixed.   
  • ContentBody will use MultiPartType.

<xsd:complexType name="MultiPartType">
  <xsd:element name="BodyPart" type="BodyPartType" maxOccurs="unbounded"/>

The MultiPartType is an array of BodyPartType.  First element in thus array is the email body itself and the subsequent elements are the attachments for the email.
<xsd:complexType name="BodyPartType">
  <xsd:element name="MimeType" type="xsd:string" default="text/plain" minOccurs="0"/>
  <xsd:element name="ContentBody" type="xsd:anyType" nillable="true"/>
  <xsd:element name="BodyPartName" type="xsd:string"/>
  <xsd:element name="Disposition" type="dispositionEnum" default="inline" minOccurs="0"/>
  <xsd:element name="ContentId" type="xsd:string" minOccurs="0"/>
  <xsd:element name="ContentEncoding" type="contentEncodingEnum" nillable="true"/>
  <xsd:element name="AttachmentContentEnclosed" type="xsd:boolean" default="true" minOccurs="0"/>

BodyPartType defines the email body and attachment for the email.   MimeType specifies the type of data in email.   If the email body is HTML email the MimeType of email body(first Body Part) would be: "text/html; charset=UTF-8".   Depending on the attachments with the email the MimeType in BodyParts for attachments can be set the values of proper Mime type.   
Some of such values are shown below:
image/tiff;            - tiff image file
image/jpeg;            - jpeg image file   
application/pdf;       - PDF file
application/xml;       - XML file  
application/msword;    - MS Word file

ContentBody in BodyPartType will be the actual data for email body and attachment.  For the attachment the ContentEncoding will be set as base64. BodyPartName will be set as the file name for the attachment.

The below is one example of EmailPayload message which are populated with the data.  The email will have one attachment of .tiff image file whose name is Testing.tif.
<EmailPayload xsi:type="def:EmailPayloadType">
 <Subject>Tesyimg email with one attachement</Subject>
     <ns:MimeType>text/html; charset=UTF-8</ns:MimeType>
      ......  Email body here
      .... Tiff image data

Here use xlst transformation to populate the Content element.
 <from expression="ora:processXSLT('xsl/EmailContentBodyTransform.xsl', $NotificationRequest.payload)"/>

 <to variable="varNotificationReq"

The below is the snippet of XSLT transformation.
    <ns:MimeType >text/html; charset=UTF-8</ns:MimeType>
     <xsl:value-of select="/*[local-name()='NotificationRequest']/*[local-name()='Body']"/>
   <xsl:for-each select="/*[local-name()='NotificationRequest']/*[local-name()='Attachment']">
    <ns:BodyPart >
      <xsl:value-of select="./*[local-name()='MimeType']"/>
     <ns:ContentBody >
      <xsl:value-of select="./*[local-name()='AttachmentContent']"/>
      <xsl:value-of select="./*[local-name()='AttachmentName']"/>
      <xsl:value-of select="./*[local-name()='ContentEncoding']"/>

Testing Email service with attachments

In order to test this generic email service I have another BPEL service which will populate email body and attachment and then invoke the generic email service.  The below shows the two assign activities used in the BPEL to set the email body and email attachment.

<assign name="AssignEmailBody">
 <from expression="ora:toCDATA(  ora:processXSLT('xsl/TransformHTMLEmailBody.xsl', $Request.request, bpws:getVariableData('Parameters')) )"/>
 <to variable="EmailNotificationRequest" part="payload"

<assign name="AssignEmailAttachment">
 <from expression="ora:readFile( $ImageFileName) )"/>
 <to variable="EmailNotificationRequest" part="payload"

One assign is used to set the email body. Since the email is HTML format the assign activity uses ora:toCDATA to wrap the html content.   
Another assign activity uses orad:readFile function to read the physical file which is going to attach in the email.

Tuesday, May 20, 2014

Setting and Getting Configuration Property and Preference Variable in Oracle SOA Suite 11g

Configuration property and preference variable can be set in composite.xml.  The advantage of using these is that the values can be set in the design time, deployment time and run time.

Define the Properties and Preference Variables

The configuration property and preference variable is defined in composite.xml
In the component of the example composite one configuration property: ConfigVar is defined.  Its value is set Value123 initially. One preference varaible PrefVar is also defined and its value is set to Value789 initially.

In the reference of the example composite one configuration property: OUT_FILE_PATH is defined.  This property is set when configuring the logical file path for File adpter.   This property value is also defined in .jca file.

Retrive the Values of  the Properties and Preference Variables

The component is the example composite is BPEL process where the configuration properties and preference variables can be retrived from winthin BPEL.  The retrival can be done by using BPEL XPath Extension Functions: ora:getPreference() and ora: 

View and Midify the Values in Enterprise Manager

These values of the configuration properties and preference variables can be viewed and modified in run time with Enterprise Manager.   

After login into EM navigate into the domain and right click on the domain to invoke System MBean Browser.  From System MBean Browser navigate to Application Defined MBeans/oracle.soa.config/Server: SOA_server1/SCAComposite.   Expand it and expand SCAComposite: ConfigPreferenceProject[1.0].  

To view or modify the values defined for BPEL: select SCAComposite.SCAComponent and then click on BPELProcess1.  Then click on properties in Attribute tab.  All the configuration properties and preference variables defined in the BPEL are shown now.  

In order to change the value of one variable or property just type in the new value and then click on Apply button.

To view or modify the property values defined for reference: select SCAComposite.SCARefernce.  All other steps are the same as the above.

Similarily if there is any configuration property defined in SCAComposite.SCAService select SCAComposite,SCAService in order to view or modify the property values. 

Test the Composite with the Modified Values 

When testing the SCA composite using EM testing utility, the response will show the values of configuration and preference variable in BPEL process as :Value123   Value789".(This BPEL process simply concatenate these two values as the response for the process.) 

Next modify the value of configuration property to 123Value and preference value to 789Value and apply these changes.  Then run the testing again.   This time the response will show as the new values:

Deploy the Composite with Environment Specific Values

With configuration properties and preference variables in the component, service and references in SCA composite different deployment plans can be generated for various environments.  In each deployment plan the values for these properties and variables are environment specific.

Monday, May 12, 2014

Develop and Deploy Jersey RESTful service using JDeveloper in Weblogic

Jersey is one of reference implementations of JAX-WS specification which is to describe how REST should be implemented using annotation-driven POJO in Java. Jersey is the one that is most widely used in development and production. WebLogic Server ships with a set of pre-built shared libraries, packaged as Web applications. Jesery is one of these and it is required to run applications that are based on the Jersey JAX-RS RI. The version of Jsersey in weblogic 10.3 is 1.9 which implements JAX-RS 1.1.

This jersey war file is located in:  $Middleware/wlserver_10.3/common/deployable-libraries/jersey-bundle-1.9.war.

Register Jersey as the shared library in Weblogic server 

Before the REST application can be deployed and run on Weblogic the Jersey JAX-RS RI shared libraries needs to be registered in Weblogic Servcre instance. It can be done via Weblogic Console. 
Login into Weblogic Console Click on Deployments in Domain Structure
Click on Lock & Edit button to enable Install button and then click on Install button

The path should be:  /u01/app/fmwsodev/Middleware/wlserver_10.3/common/deployable-libraries
Select jersey-bundle-1.9.war and click on Next button

Select Install this deployment as a library and click on Next button

Select target and click on Next button

Select target and click on Next button

Confirm the library name, version and implementation version as below and click on Finish button

Now Jersey JAX-RS RI is registered as shared library in Weblogic server.  Any application deployed on the server can use this library for implementation of REST service using Jersey.

Develop Jersey REST service using JDeveloper

Create a generic project in JDeveloper

In order to develop Java codes which use Jersey to implement RESTful service, two jar files are needed to add into User Managed Libraries in JDeveloper.

First jar is jersey-bundle-1.9.jar which can be downloaded from:

Since jersey-bundle-1.9.jar doesn't include JAX-RS API classes.  So JAX-RS API jar is also needed.   The jar file can be found from Weblogic server installation folder: $Middleware/modules/

After two jar files are added into the libraries use these two libaries in the project: RESTProject by using Add Library in Libraries and Classpath from Project Properties... menu item.

Now create a POJO which will implement the service:

The source codes of Java class: RESTService are as below:


import com.toic.model.Folder;

import java.util.logging.Logger;


public class RESTService {
    private Logger logger = Logger.getLogger(RESTService.class.getName());
    public RESTService() {
    public Response getFoldersByGUID(@PathParam("folderguid") String folderguid) {
        Folder folder = Folder.makeFolders();
        return Response.status(Response.Status.OK).entity(folder).build();
The below is one domain class Folder which is used in RESTService class.

package com.toic.model;

import java.util.ArrayList;
import java.util.List;

public class Folder {
    private String folderName;
    private String owner;
    private String folderGUID;
    private String folderType;
    private List childFolders = new ArrayList();
    public Folder() {

    public String getFolderName() {
        return folderName;

    public void setFolderName(String folderName) {
        this.folderName = folderName;

    public String getOwner() {
        return owner;

    public void setOwner(String owner) {
        this.owner = owner;

    public String getFolderGUID() {
        return folderGUID;

    public void setFolderGUID(String folderGUID) {
        this.folderGUID = folderGUID;

    public String getFolderType() {
        return folderType;

    public void setFolderType(String folderType) {
        this.folderType = folderType;

    public List getChildFolders() {
        return childFolders;

    public void addChildFolder(Folder childFolder) {
    public static Folder makeFolders() {
        Folder rootFolder = new Folder();
        rootFolder.setFolderName("Root Folder");
        Folder childFolder1 = new Folder();
        Folder childFolder2 = new Folder();

        return rootFolder;

In editor click on Quick Fix to configure web.xml for this service.

web.xml is created under WEB-INF.

Update url-pattern to *

Then create Weblogic Deployment Descriptor

weblogic.xml file is created under WEB-INF as shown as below:

In run time the deployed service will use the shared library of Weblogic.  Here in weblogic.xml the library reference should be created as below:

Next step is to update the deployment profile of the service which is deployed as web application,

The service is called RESTService.

Update the context root as: Demo.

Deploy Jersey REST service and test the service

After all is done the service is ready to deploy to Weblogic server from JDeveloper.

If the deployment is successful RESTService will appear in the deployments of Weblogic Console.

Click on RESTService and click on Testing tab

Click on the Test Point URL to start browser and type in the url as: http://weblogicserverhost:8001/Demo/folders/guid/123456 and the service will respond as below: