Register your Pl/SQL procedure as Concurrent program in Oracle apps

IN this article we will try to explore how we can register a pl/sql stored procedure in Oracle apps so that it be can tested from the front end of Oracle apps.


The steps will be as follows

1> Create a procedure
2> Create Executable
3> Create Program
4> Add the program to the request group

we will see each of these steps one by one.


First is to Create a Procedure

There are two important points that one must take care of when creating a procedure for registering as concurrent program.
There are two mandatory parameters that must be passed in the procedure along with other parameters of your choice

They are errbuf and retcode

errbuf is used for printing out the error message and retcode returns the status of the concurrent program

If retcode is 0 it means it a success
If retcode is 1 it means Concurrent program is warning
If retcode is 2 it means Concurrent Program is giving error.

with these thumb rules in mind lets create a simple procedure which will accept Org_ID and Vendor_ID as input parameter

and will return invoice_amount and invoice_number.

So my code will essentially look like this.

CREATE OR REPLACE PROCEDURE getInvoiceInfo(errbuf OUT VARCHAR2,retcode OUT VARCHAR2,P_Org_ID IN VARCHAR2,P_Vendor_ID IN VARCHAR2)
AS
Inv_number VARCHAR2(100);
Inv_amount VARCHAR2(100);
BEGIN
SELECT invoice_num, invoice_amount
INTO Inv_number,Inv_amount
FROM ap_invoices_all
WHERE ORG_ID = P_Org_ID and VENDOR_ID =P_Vendor_Id;
DBMS_OUTPUT.PUT_LINE('test');
fnd_file.put_line(fnd_file.output, 'Parameter received:' || P_Org_ID || P_Vendor_ID);

fnd_file.put_line(fnd_file.log, 'Parameter received:' || P_Org_ID || P_Vendor_ID);
EXCEPTION
WHEN OTHERS THEN
RETCODE := 2;
errbuf := 'Unknown Exception';

fnd_file.put_line(fnd_file.log, 'sqlerrm:' || SQLERRM);
END;



Just to test if the procedure is workin fine you can execute the following
SET SERVEROUTPUT ON
DECLARE
errbuf VARCHAR2(20);
retcode VARCHAR2(10);
BEGIN
getInvoiceInfo(
errbuf=>errbuf,
retcode=>retcode,
P_Org_ID=>'888',
P_Vendor_ID=>'9');
DBMS_OUTPUT.PUT_LINE('Code Executed');
END;

Here in my case i am getting a single record for this particular input but you get more than one row for the query

select * from ap_invoices_all where vendor_id=9 and Org_Id=888

Then this code might not work for you, you will have to use cursor in that case.

Considering you are getting a single value we will go ahead and register this procedure as a concurrent program in Oracle apps.

Next step is to create an executable for your concurrent program

Go to Application Developer Responsibility-->Concurrent-->Executable



In the next screen provide the following info

Execuatable -Give it a logical name

ShortName -Give it a unique name

Application- Ideally executable should be created in Custom application

Execution Method- It will PL/SQL Stored procedure as we are going to call a procedure in this executable.

Execution file Name- This should be the name of the procedure,In case you have your procedure wrapped in a package the name should be

For our program we will pass the following information



Now once you have the Executable ready We will create a program.

Go to following path in Application Navigator Responsibility

Concurrent-->Program

Provide the information as shown below

Program- Give some logical name

Short Name- Provide some unique Name

Application- Enter application name , Ideally it will custom

Executable Name- We will provide the executable we have created earlier.

Method-Automatically populated

OutputFormat- Select XML

Output Style- Select A4

Rest of the options you can keep the default values.




Now click on paramters tab to define the parameter for this concurrent program

Define the two parameters that will be passed as an input to this and save the changes for the concurrent program




Now assign this to a request group.

If you want to assing this to Application developer

then go to following navigation



System Administrator-->Security-->Responsibility-->Define

Double click on Request and get the Request group for the Application developer responsibility as shown below




Now once you have got the request group copy it and then go to following navigation

System Administrator-->Security-->Responsibility-->Request

Search with the group Name

Application Developer Reports

and add your program to this list




Now go to Application developer responsibility and test your code if it is working fine by passing the value for the Org Id and the Vendor ID

Validate the log file and the output file

Calling XDO Data Engine... java.lang.NullPointerException

YOu are trying to run a concurrent program and it is failing with below error

XDO Data Engine Version No: 5.6.3
Resp: 20420
Org ID : 204
Request ID: 5917401
All Parameters: p_OrgId=2:p_VendorId=144
Data Template Code: INV
Data Template Application Short Name: XDO
Debug Flag: N
{p_VendorId=144, p_OrgId=2}
Calling XDO Data Engine...
java.lang.NullPointerException
at oracle.apps.xdo.oa.util.DataTemplate.getDataTemplate(DataTemplate.java:379)
at oracle.apps.xdo.oa.util.DataTemplate.(DataTemplate.java:226)
at oracle.apps.xdo.oa.cp.JCP4XDODataEngine.runProgram(JCP4XDODataEngine.java:283)
at oracle.apps.fnd.cp.request.Run.main(Run.java:157)



The reason for this issue is that your Concurrent Program Short name and the Data Definition code are not same.

In my case both the values were same however in concurrent program is was "INV" and in the Data definition code it was "inv"

It is very important that the Concurrent program short name and the Data definition code Should match exactly with case otherwise you will find this issue.

Now in order to resolve this issue you go to your data definition and specify an end date for the data definition and save the changes. This will invalidate the data definition.




Now next create a new data definition and Use the proper case for the code name

Different types of Triggers in Oracle Report

There are basically eight different type of trigger in Oracle reports.

(1) Before Parameter Form

It Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns.

(2) After Parameter Form

It Fires after the Runtime Parameter Form is displayed.

(3) Before Report

It Fires before the report is executed but after queries are parsed and data is fetched.

(4) Between Pages
It Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting.

(5) After Report

It Fires after you exit the Previewer, or after report output is sent to a specified destination, such
as a file, a printer,this trigger always fires, whether or not your report completed successfully.

(6) Validation Triggers

Validation Triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form.

(7) Format Triggers

Format Triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object.

(8) Action Triggers

Action Triggers are PL/SQL procedures executed when a button is selected in the Previewer.This trigger can be used to dynamically call another report or execute any other PL/SQL.


Of all these trigger the most commonly used are before and after report triggers. We will try to see an example and understand how this trigger works.

We will go step by step in this and first create a simple report and then we will try to use trigger in the report.

Lets use ap_invoices_all table in EBS to create a simple report.

We will try to fetch information from this table based on the query

select invoice_id,invoice_num,invoice_currency_code,org_ID,invoice_amount from ap_invoices_all

Further to filter the search criteria we will use two input parameter org_id and vendor_id

So the filtered query will be something like this

select invoice_id,invoice_num,invoice_currency_code,org_ID,invoice_amount from ap_invoices_all WHERE org_id = :p_OrgId AND vendor_id = :p_VendorId

Here p_OrgId and p_VendorId are the parameters for the report.

Now the first things is to create a data template which can be used to create the xml document for our report.

As we know the data template consists of four sections

1>Define parameter
2>Define Triggers
3>Define Data Query
4>Definde Data Structure

We will create a XML basic data template for our requirement and this will be as below

<?xml version=”1.0″ encoding=”UTF-8″ ?>
<dataTemplate name =”Invoice” description=”AP Invoice Data” Version=”1.0″>
<parameters>
<parameter name=”p_OrgId” dataType=”number” />
<parameter name=”p_VendorId” dataType=”number” />
</parameters>
<dataQuery>
<sqlStatement name=”invoiceQuery″>
<![CDATA[
select invoice_id,invoice_num,invoice_currency_code,org_ID,invoice_amount
from ap_invoices_all
WHERE org_id = :p_OrgId
AND vendor_id = :p_VendorId
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name=”INV_GROUP” source=”invoiceQuery″>
<element name=”INVOICE_ID” value=”invoice_id” />
<element name=”INVOICE_NUMBER” value=”invoice_num” />
<element name=”INVOICE_CURRENCY_CODE” value=”invoice_currency_code” />
<element name=”ORGANISATION_ID” value=”org_ID”/>
<element name=”INVOICE_AMOUNT” value=”invoice_amount”/>
</group>
</dataStructure>
</dataTemplate>


Now first is to load the XML template in to your EBS.

Log in to Oracle apps and go to following navigation



Go to data definition tab and create a new data definition



And update the data template that we have created



Now we will create a concurrent program to call this template.

Go to System Administer responsibility-->Concurrent -->Program-->Define



Now create a program and make sure you are using Short name same as you have the code name for Data definition



Define the parameters for them



Go to security-->responsibility-->request



And add your program to it




Now actually to know the repsonsibility you can go to the following page

SEcurity-->Responsibility-->Define

Copy the Request group name and say CTRL+F11 and you will get the responsibility as shown



Now go to that responsibility and try to run the concurrent program.

Once you are able to validate the data output of the concurrent program we can go ahead and use the same template to add trigger in to it.

We will use before and after trigger in this case to do some logging.

Again it is always a good practice to add following component to your data template

<properties>
<property name="xml_tag_case" value="upper"/>
<property name="debug mode" value="on"/>
<property name="scalable_mode" value="on"/>
</properties>



Now to achieve some logging we will write a package and two fucntion for before and after trigger call. This will help us to log some error data in the concurrent program output.

This is not a great example but through this post we just want to cover how the tigger functionality can be use. You can come up with a much better logic for the trigger, something like before triggering the report you should commit the data or update some records or may be some other better logic. But in this exercise we will create a simple function to see the use case on how before and after trigger can be used.


SO My pacakge goes like this

create or replace
PACKAGE Custom_error AS
p_OrgId varchar2(20);
p_VendorId varchar2(20);
FUNCTION Initialize return BOOLEAN ;
FUNCTION Finalize return BOOLEAN ;
END Custom_error;


And my package body is

create or replace PACKAGE BODY Custom_error AS

FUNCTION Finalize RETURN BOOLEAN IS
BEGIN
fnd_file.put_line(fnd_file.LOG, 'After triggering every thing is fine');
return true;
END;

FUNCTION Initialize RETURN BOOLEAN IS
BEGIN
fnd_file.put_line(fnd_file.LOG, 'Before triggering every thing is fine');
return true;
END;

END Custom_error;


Compile this procedure and now we will make an entry in the data template to call these funciton in before and after trigger.

Now my revised Code will be

<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name ="Invoice" defaultPackage="Custom_error" description="AP Invoice Data" Version="1.0">
<properties>
    <property name="xml_tag_case" value="upper" />
    <property name="debug mode" value="on" />
    <property name="scalable_mode" value="on" />
  </properties> 
<parameters>
<parameter name="p_OrgId" dataType="CHAR" />
<parameter name="p_VendorId" dataType="CHAR" />
</parameters>
<dataQuery>
<sqlStatement name="invoiceQuery">
<![CDATA[
select invoice_id,invoice_num,invoice_currency_code,org_ID,invoice_amount
from ap_invoices_all
WHERE org_id = :p_OrgId
AND vendor_id = :p_VendorId
]]>
</sqlStatement>
</dataQuery>
<dataTrigger name="beforeReport" source="Custom_error.Initialize()" />
<dataStructure>
<group name="INV_GROUP" source="invoiceQuery">
<element name="INVOICE_ID" value="invoice_id" />
<element name="INVOICE_NUMBER" value="invoice_num" />
<element name="INVOICE_CURRENCY_CODE" value="invoice_currency_code" />
<element name="ORGANISATION_ID" value="org_ID"/>
<element name="INVOICE_AMOUNT" value="invoice_amount"/>
</group>
</dataStructure>
<dataTrigger name="afterReport" source="Custom_error.Finalize()"/>
</dataTemplate>


As you can see we have added two tags here

<dataTrigger name="beforeReport" source="Custom_error.Initialize()" />
<dataTrigger name="afterReport" source="Custom_error.Finalize()"/>

AS you can see these tags are actually calling the fucntions that we have defined.

Now once again go back to your concurrent program and try to run this

And this time you can find the following as output



So now i believe you might have got the idea on how to use the trigger in xml report.

You can also use procedure and pass the input parameters also to the call. Rest is for you all to experiment.

Privileges required for a user to read events using apps adapter

NOrmally in a develoment environment when we create apps apdater then we try to access the events using apps schema.
HOwever in production environment apps user password are not provided and then comes the issue at last moment.

Hence it is better to use a user with limited access from the begining so that at later point of time you do not get issue with access.

First of all create a user READ_ONLY_USER

Following set of queries needs to be executed for the user in order to retrieve events from apps adpater

GRANT ALL ON WF_BPEL_QTAB TO READ_ONLY_USER;

GRANT EXECUTE ON apps.(Package Name) TO READ_ONLY_USER;

This package will contain the procedure and funtions used in your interface.

oracle.apps.fnd.framework.OAException: java.lang.ClassCastException: oracle.jbo.domain.Number

You are trying to run a OAF page and after submitting data to OAF page you get following error in the page


Error Page
Exception Details.
oracle.apps.fnd.framework.OAException: java.lang.ClassCastException: oracle.jbo.domain.Number
at oracle.apps.fnd.framework.OAException.wrapperException(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
at _OA._jspService(_OA.java:71)
at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
at java.lang.Thread.run(Thread.java:595)
## Detail 0 ##
java.lang.ClassCastException: oracle.jbo.domain.Number
at xxtrn.oracle.apps.fnd.ar.createLab.webui.xxtrnEmployeeCO.processFormRequest(xxtrnEmployeeCO.java:70)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
at _OA._jspService(_OA.java:71)
at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
at java.lang.Thread.run(Thread.java:595)
java.lang.ClassCastException: oracle.jbo.domain.Number
at xxtrn.oracle.apps.fnd.ar.createLab.webui.xxtrnEmployeeCO.processFormRequest(xxtrnEmployeeCO.java:70)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
at _OA._jspService(_OA.java:71)
at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
at java.lang.Thread.run(Thread.java:595)




The reason for this issue is that the program was not able to convert the employee number in to number format. As per the error details the error was on this following code

Number employeeNumber = (Number)vo.getCurrentRow().getAttribute("EmployeeId");

As you can observer we are typecasting the values retrieved from a view object to a number value. This was failing in this particular step. So in order to make this work we need to explicitly import the oracle.jbo.domain.Number;

So i just added the following import statement in my controlled Java code

import oracle.jbo.domain.Number;

And this is now working fine for me.

Hello World Project in OAF

Even though ADF is already in to the market but still the importance of OAF can not be ignored as still Oracle EBS uses the conventional forms to do the query and other operations. Again OAF is important as it forms the base for ADF. The current ADF is again an enhanced form of OAF. We will try to create a simple Hello World project in OAF and then try to understand how it works.
We will first create a project in Jdeveloper and try to run it in local integrated server. This is where you will have to show a lot of patience as one page takes almost 20-30 to get displayed. However once this code will be moved to the EBS server it will be very fast. But for development there is only one way that is to have patience.We will be using Jdeveloper 10.1.3.3.0.3 version of Jdeveloper as recommended by Oracle.

Open your Jdeveloper and create a new project





Create project





Use repository for design time and select the DBC file that you have got for the EBS server connection.
This file contains the tns entry details for the EBS database and you can connect to EBS backend data using DBC file.

Invoke Web service from Oracle Administrator Workflow

In our previous post

http://soa-bpel-esb.blogspot.in/2014/08/subscribe-business-event-using-oracle.html

We have seen how business events can be raised from Oracle Apps and using an Apps adapter oracle soa suite can subscribe to the message.

There are ways also where in one can directly call a web service from Oracle EBS by using the ACtion Invoke Web service.

In this exercise we will try to explore this option as how we can call a web service directly from Oracle EBS workflow.

The basic will remain the same as we have discussed in the previous post of event subscription. We will first create a one way BPEL service based on the following schmea

<?xml version= '1.0' encoding= 'UTF-8' ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.example.org" targetNamespace="http://www.example.org"
elementFormDefault="qualified">
<xsd:element name="Employee">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EmpName" type="xsd:string"/>
<xsd:element name="EmpId" type="xsd:integer"/>
<xsd:element name="EmpSalary" type="xsd:integer"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

This porcess will only receive the event data and generate a BPEL instance.

I have create a simple one way process "WSDLCall" based on this schema.

Now go ahead and deploy your process and get the WSDL URL of your process from the em console.

To get the WSDL URL

Go to your em console.

Select your process and click on test button this will take you to a page from where you can copy the wsdl url as highlighted below



Now log on to Oracle EBS workflow page.

Go to Business Event tab--> Events and Create Event



Provide an event name and apply to create an event



Now go back to event page and search for the event you have created.

Once you find the Event click on subscription button to create a subscription for the event created.



Create a subscription

Provide the system name from the list of values

SourceType as local

Event Filter will have the name of the event.

Phase value keep it default value 100

Status Enabled

Rule Data is message

ACtion type -->Select this as "Invoke Web Service"

On Error -->Stop and Rollback



Say next and in the next page provide the WSDL URL that you have copied earlier



Next validate that the service name is selected correctly



Validate the port



Validate the operation name



Finally provide the owner name and tag and apply the changes



Now validate where a subscription is create for the Event or Not.



Now we are done with all the changes.

Now raise the event from the Workflow page.

Click on the Test button as shown



we will use the same payload that we have used in our last exercise

<xsd:Employee xmlns:xsd="http://www.example.org">
<xsd:EmpName>Arpit</xsd:EmpName>
<xsd:EmpId>2224</xsd:EmpId>
<xsd:EmpSalary>450000</xsd:EmpSalary>
</xsd:Employee>

Pass this payload in the XML Content as shown below and raise the event in Java as highlighted



Now log in to the em console and validate if the isntance is raised and it has the valid data.



Validate the data in the payload



Using an apps adapter is a better approach then directly calling a web service as tracking is much easier and better in case of apps adapter approach. Further when migrating code among instances the url keeps on changing so using a hard coded value is not a good approach. Or i should say using apps adapter to subscribe to business events is a better approach