Oracle WebADI Setup in EBS R12

Oracle Web Application Desktop Integrator (Web ADI) is Oracle E-Business Suite's solution for integrating E-Business Suite applications with desktop applications such as Microsoft Excel, Word and Projects.Following are the steps to create custom integrator in R12

Creation of the custom integrator

Custom integrator will be used to upload the excel values into database tables.
For Example the user wants to upload the data from an excel sheet with a formatted way, and when it uploads, the data should be validated and should be returned back if any error occurs or data should do some other calculation with other tables, that can be done.
The template excel can have defaulted values, LOV values and POPList values to select the values.
Once we have the custom integrator we can define as a function and assign to the FND_USER

Steps to create the custom integrator.
Step 1.Create the Integrator

Step 2.Create the Interface

Step 3.Create the Layout

Step 4.Create Lov Columns

  

These steps mentioned above will be done by System Administrator

Step 5. Change the excel column prompts, by default the column headers will come as Database column name

Set the following profiles at site level

1. BNE Allow No Security Rule – Yes
2. BNE Allow Set Parameters – Yes

IE Setup for  WEBADI

1. Launch IE,Select Tools->Internet Options->Security->Internet->Custom Level
2. Enable scripting for all options

 Microsoft Excel Setup

1. Launch Microsoft Excel and select File->Excel Options
2. Enable “Show developer Tab in the Ribbon"
3. Select Developer Tab and click on Macro Security 
4. Select “Enable all Macros”  and “Trust Access to VBA Project Object Model” and Click OK.

  

SOA Gateway Issues and Resolution

1. While modifying the PL\SQL specs make sure that the param names, Object name is correct. 
2. While creating the ildt file if an error is thrown related to appearance of “;” and “,” make sure the PL\SQL script doesn’t have any extra ‘/’.
3. While creating the ildt file if an error is thrown as ”Unknown command END” then check for empty spaces and tabs in the pls file. 
4. For deploying the SOA Gateway Service if permission denied error is shown for the following commands:

soagenerate.sh and soadeploy.sh

Contact people with ADMIN privileges to run this command.

 5.      Once the WSDL is created and accessible, if while invoking the same results in the following issue:

                                 User not authorized to execute service

Then check the grants provided for the service in Integration Repository. Sometimes even if the grants are given this error is shown. In that case, delete the grants and give the grants again.

  

6.      If there are changes in PL/SQL scripts then make sure to regenerate and redeploy the WSDL. Also the grants have to be given again. Only regeneration of WSDL will not result in modified WSDL.

Steps to expose custom PL SQL APIs as a SOA Gateway Webservice

SOA Gateway is an integral part of Ebusiness Suite R12. IT can expose EBS Functionality as SOAP based webservices which includes XML gateway, Business Service Objects, Concurrent Programs, PL/SQL API's, Business Events, E Commerce Gateway, Open Interface Tables/Views etc.

The steps to be followed to expose a  PL/SQL script as SOA Gateway webservice is shown below:

1.      Include the annotations at the package level and procedure/function level to the PL\SQL Spec.

Example:

create or replace <Package_Name> AS

/* $Header: $ */

/*#

* @rep:scope public

* @rep:product <Object Name>

* @rep:lifecycle active

* @rep:displayname <Name of the package>

* @rep:compatibility S

* @rep:category BUSINESS_ENTITY <Object>

*/

PROCEDURE <procedure_name>(

Input1  IN  VARCHAR2,

output1   OUT VARCHAR2,

)

/*#

* @param Input1 <description of input>

* @param output1 Return <output1 details>

* @rep:displayname <procedure_name>

*/;

END <procedure_name>;

/

SHOW ERRORS

EXIT

2. Move the PL\SQL Package(.pls files) to the Unix Machine using winscp or putty.
3. Ensure to have irep_parser.pl to be present in Unix Machine.
4. Execute the following command to create an iLdt file for package

$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=<admin username> <Object name>:patch/115/sql:<Package Name>.pls:12.0=<pls file location>/< Package Name.pls>

Where

Object name is the name of the object where the services will be registered.

            patch/115/sql: stands for the path
             <package_name>.pls: file name 

            It is not required to physically place the file in that path.

<pls file location>/< Package Name.pls> - Physical file path

5.      Execute the following command to upload the iLdt file to Intergrated SOA Gateway Responsibility:

$FND_TOP/bin/FNDLOAD <UNAME>/<PWD> 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct <pls file name>.ildt CUSTOM_MODE=FORCE

6.                 To generate and deploy the service in multi node environment please login to each node and execute the following commands (with admin privileges)

$FND_TOP/bin/soagenerate.sh irepname=<internal name or package name>

7.      Check the ServiceGenerator.log file for service generated successfully or not. (with admin priveleges)

$FND_TOP/bin/soadeploy.sh irepname=<internal name or package name>

8.      Check the ServiceDeploy.log file for service is deployed successfully or not.

9.      In R12 front End Application select Intergrated SOA  Gateway Responsibility, search for the object where this API was deployed.

10.  Select the Deployed Service and open the details of the same. Now give the grant for the Procedure listed. Select the checkbox for the procedure listed and select “Create Grant”. In the next screen we can give the individual grant access or to all the users.

11.  Click on view WSDL and check if the WSDL is accessible.

Steps to setup Email notification in Human Task activity in BPEL for SOA Suite 11g

Set up for Human task email notification:

1.       In Enterprise Manager make changes as mentioned in the previous post(Easy

       Steps to setup Email Notification in SOA Suite 11g).

2.       In Weblogic console change the Email ID of the user to point to the required mail id

3.       Start JDeveloper and create Synchronous or asynchronous BPEL Project.

4.       The BPEL editor opens up. You will see the basic structure of the BPEL process with a receive and a reply(or callback) activity. Drag a Human Task activity from the Component Palette and drop it after the Recieve Activity. Do the basic settings for the Human task.

5.       Right Click on the Human Task and Create Taskflow project for the same.

6.       Open the taskflow.xml file which will have taskdetail_jspx, to the same file drag and drop View from the component palette and name it as EmailPage.

7.       Drag and drop Router in the taskflow.xml. Right click and select Mark activity->Default Activity

8.       Next Update the following Router properties in Property inspector:

·         Default Outcome: default

·         Add expression : #{pageFlowScope.bpmClientType=="notificationClient"}

9.       Connect Router to taskdetail_jspx and Router to Email page using control Flow case.

10.   Double click Emailpage view and create Emailpage as jspx.

11.   In email page drag and drop task from datacontrols and select taskflow with Email.

12.   Deploy and instantiate the App. This should send an email notification with the task details page as the contents when any of the actions are done to the task.

Easy Steps to setup Email Notification in SOA Suite 11g

Prerequisites:

1.       Install SOA Suite 11g. Start the server and keep it running.

2.       Compatible JDeveloper 11g installed with SOA extension.

Steps:

1.       To Set up email notification UMS email driver has to be configured. 

      

      Steps to configure email driver: Launch Oracle Enterprise Manager and open the User Messaging Service node. Right click and select Email Driver Properties.

Set the following properties for sending emails:

·         OutgoingMailServer – <<Outgoing Mail Server>>

·         OutgoingMailServerPort – 25

·         OutgoingMailServerSecurity – None

·         OutgoingUsername – <domain_name>\<user>

·         OutgoingPassword – type:use cleartext password and give mail pwd

                Click Apply.

2.       Configure the SOA Suite Workflow Notification properties, to make sure that (email) notifications are really sent to the email server.  Steps to do the same.

·         In Oracle Enterprise Manager select soa-infra->Right click and select SOA  Administration->Workflow Notification Properties.

·         Set Notification Mode (default value is None) to either All or Email.

·         Optionally provide Email From and Actionable Address.

·         Restart Admin and SOA Server.

3.       Creation of a BPEL process to send email notifications:

·         Start JDeveloper and create Synchronous or asynchronous BPEL Project.

·         The BPEL editor opens up. You will see the basic structure of the BPEL process with a receive and a reply(or callback) activity. Drag an Email activity from the component Palette and drop it after the receive Activity.Set the following values in Email activity:

                                Account: Default

                                To: To address (ex:name@<domain>.com)

                                Subject: Any string static or dynamic

                                Body: Any string static or dynamic

4.       Deploy the composite and create instance of the same. Check mail.