Invoking Concurrent programs and working with BizTalk Oracle E-Business Adapter

Invoking Concurrent programs and working with BizTalk Oracle E-Business Adapter 

In my current project I had to call a concurrent program in the Oracle E-Business Suite which would generate a report of all the employs payroll of the month. We were automating the payroll process in our organization and the whole solution involved getting and validate the Employee data through BizTalk and then have the data in an excel file and initiate a Payroll approval workflow which was made in sharepoint. 

I was new to the Oracle E-Business Suite application and had a little hiccups and surprises while connecting to the Oracle E-Business Suite through the WCF Oracle E-Business Suite adapter. The major challenge was to get the xml that is generated at the Oracle Server by the Concurrent Program. 

Generating Oracle EBS Adapter Metadata: 

The first step is to generate the Adapter metadata in order to get the port types and schemas generated for the concurrent programs. Right click on the project and select Add generated items, then select Add adapter metadata. You will get the list of LOB adapters. Select the Oracle EBS adapter and click “Next”. You will see the window where binding type will be oracleEBSBinding. Click “Configure” to configure the adapter URI and binding properties. In the URI Properties tab as shown below give the port number, server name or IP and Service Name from the TNS entry. 

 

Next to configure the Binding Properties the first property in the window is the ClientCredentialType which could be Database or EBusiness. It depends upon your choice which credential type you want to choose and specify those credentials in the Security Tab. 

 

For generating the metadata you need to give the correct credentials of database, Responsibility Key or Name and Organization ID. You can ignore other properties for now and when configuring the Physical port in BizTalk Administration Console we will come to these properties. The Responsibility Key/Name, Organization ID and Credentials are given by the EBusiness Suite guys. Ask them for the correct values if you are having problems in connecting to EBS. 

When you are done click Ok and click Connect if you are getting any errors troubleshoot it with supplying the correct binding properties and credentials if you are lucky then you would be able to see Categories and Operations. 

 

Getting the concurrent program Application Name and parameters from Oracle E-Business application: 

At first the categories and operations might be confusing for you if you have a good team of EBS in your organization they would guide you through this if not you are having the same fate as me. Because you might have been executing the concurrent program from the interface of EBS you might be confused from the categories and operations. I will have a simple walkthrough of the Oracle EBS interface because for getting the parameters and status of the CP you have to get familiar with the EBS interface. 

 

This is the page you will be viewing after logging in. It is a list of EBS Sections. I went to the Processes and Reports group and Selected Submit Process and Reports. Suddenly you will see a popup appear and Oracle Interface would open where you can submit a request for the concurrent program. 

 

Depends upon your request my request was single type so I selected it and went to the second screen. 

 

You can select the name of the Concurrent program the Oracle guys will be more helpful to you on this. Observing the next screens will help you in getting the parameters and finding the concurrent programs in the categories and operations list when generating metadata.  In the screen below you can see the Name of the concurrent program and the Application to which it is associated. The application name will be the category and the concurrent program will be the operation when generating the metadata.  

 

The next critical thing is the parameters which you will pass in the request message of the Concurrent program in BizTalk. This is nearly a riddle and I noticed the values of the parameters after a long trial and error process. You can easily execute the concurrent program from the interface as you can select them from the list available and one would generally assume that these must be the values to be passed to the concurrent program from BizTalk. This is not the case J 

 

 

Finally after selecting all the parameters from the list of available values you will populate all the parameters and ready to execute the concurrent program as shows in the screens below. 

 

In the screen below I observed and discovered that the parameter values are 61 and may-2010 while I was copying the strings from the previous screens. You can see the parameters to the Concurrent program are different than the one from the interface. 

You can refresh the data to see that the execution of the concurrent program is complete and see the status from the interface. 

 

Developing the Solution to Invoke the Concurrent program: 

After getting the parameters, concurrent program name and application name you can map these to the adapter metadata wizard and generate the metadata. After going through the interface you can generate the metadata, design your orchestration and populate your request message with correct values.  My orchestration is below in which I am doing the following. 

1- Getting the request from the SOAP adapter mapping the values to the request message of the concurrent program.
2- Calling the concurrent program and getting the response.
3- Getting the request id from the response message of concurrent program.
4- Dela y for 2 mins.
5- Calling the Status concurrent program and repeatedly checking the status till the status is successful. 

 

This is the code in my expression shape in which I am constructing the request message for the concurrent program 

xDocRequest = new System.Xml.XmlDocument();

xDocRequest.LoadXml(@"<ns0:XXMARPYRREGXML xmlns:ns0='http://schemas.microsoft.com/OracleEBS/2008/05/ConcurrentPrograms/PER'>
<ns0:SetOptions>
<ns1:Implicit xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>ns3:Implicit_0</ns1:Implicit>
<ns1:Protected xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>ns3:Protected_0</ns1:Protected>
<ns1:Language xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>ns3:Language_0</ns1:Language>
<ns1:Territory xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>ns3:Territory_0</ns1:Territory>
<ns1:ContinueOnFail xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>true</ns1:ContinueOnFail>
</ns0:SetOptions>
<ns0:SetPrintOptions>
<ns1:Printer xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>ns3:Printer_0</ns1:Printer>
<ns1:Style xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>ns3:Style_0</ns1:Style>
<ns1:Copies xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>10</ns1:Copies>
<ns1:SaveOutput xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>true</ns1:SaveOutput>
<ns1:PrintTogether xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>ns3:PrintTogether_0</ns1:PrintTogether>
<ns1:ContinueOnFail xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>true</ns1:ContinueOnFail>
</ns0:SetPrintOptions>
<ns0:SetRepeatOptions>
<ns1:RepeatTime xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>ns3:RepeatTime_0</ns1:RepeatTime>
<ns1:RepeatInterval xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>10</ns1:RepeatInterval>
<ns1:RepeatUnit xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>ns3:RepeatUnit_0</ns1:RepeatUnit>
<ns1:RepeatType xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>ns3:RepeatType_0</ns1:RepeatType>
<ns1:RepeatEndTime xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>ns3:RepeatEndTime_0</ns1:RepeatEndTime>
<ns1:ContinueOnFail xmlns:ns1='http://schemas.microsoft.com/OracleEBS/2008/05/Options'>true</ns1:ContinueOnFail>
</ns0:SetRepeatOptions>
<ns0:Description>Description_0</ns0:Description>
<ns0:StartTime>19-APR-2010 14:24:50</ns0:StartTime>
<ns0:Payroll_x0020_Name>" + MAR.Payroll.Helper.GetConfigurations.ConcurrentProgramID + @"</ns0:Payroll_x0020_Name>
<ns0:Month>" + ClientRequestMessage.Message.PayrollRq.Month + @"</ns0:Month>
</ns0:XXMARPYRREGXML>");

This is the code in my expression shape in which I am getting the RequestID of the concurrent program from the response message and then creating the request for the Status Concurrent program.

RequestID = xpath(PayrollRegisterRs.parameters, "string(/*[local-name()='XXMARPYRREGXMLResponse']/*[local-name()='XXMARPYRREGXMLResult']/text())");
xDoc.LoadXml("<ns0:GetStatusForConcurrentProgram xmlns:ns0=\"<a href="http://schemas.microsoft.com/OracleEBS/2008/05/ConcurrentPrograms/PER/%22%3E%3Cns0:RequestId">http://schemas.microsoft.com/OracleEBS/2008/05/ConcurrentPrograms/PER\"><ns0:RequestId</a>>" + RequestID + "</ns0:RequestId></ns0:GetStatusForConcurrentProgram>");

Getting the status of the Concurrent program: 

Next phase would be to get the status of the concurrent program which you would execute from BizTalk but the concurrent program you executed would take time and after some delay you would be inquiring for the status of the concurrent program and hopefully you will get a complete status. You should have an estimate of the execution time of the concurrent program and set the delay in your orchestration before fetching the status. In my case I had a delay of 2 minutes and my concurrent program would take 90 seconds on an average. 

In order to fetch the status you have to execute another concurrent program which will return the status of the concurrent program you executed based on the request ID.  When getting the status you would require the request ID from the previous concurrent program which you executed. Using XPath we can fetch the request ID from the concurrent program response message and pass it to the Status request message. 

When the status concurrent program request is sent we get a response message in which we can get the status of the concurrent program.  You will be generating the metadata for the status concurrent program as well. The idea is in one application there is only one generic ”Get Status” concurrent program which you can run for any concurrent program in the application and gets its status based on the request ID. See above how to generate the metadata for the Status concurrent program. 

 

Connecting to the Oracle E-Business Suite using BizTalk WCF LOB EBS Adapter: 

The major issue was to establish a connection with the Oracle E-Business application. We were given a URL with a username and password to login to the EBS application. In order to connect with the Oracle EBS with BizTalk you need to have Oracle client installed and having a TNS entry in Oracle TNS file. If you can login into the Oracle EBS database which has different credentials than the Oracle EBS application then you can take one step further and configure the adapter to connect with Oracle EBS. 

When you will be logging into the EBS application with the application URL given you would be prompted again to give the same username and password of the application (not database). Of course you would think what is wrong with my credentials because you are on the same login screen after once you entered the right credentials. Anyway you have to enter them again and login and you would see the screen similar to the one below. 

 

In EBS you can think of the responsibility as roles in SQL Server or Microsoft products. Your user will be a member to one or more responsibilities and those responsibilities would be having the rights over the concurrent program. So if you want to execute the concurrent program you have to make sure that the Application ID, Responsibility and Username combination is correct. If this is not the case then you have to contact the EBS application administrator for you to resolve it. At your end you can make sure you have the correct combination you can execute the query below on the Oracle client tool (Toad/ PL-SQL) and see if your user is in the responsibility and the application in which you want to execute the concurrent program. 

SELECT FNDRESP.* FROM apps.fnd_user fnduser, apps.fnd_user_resp_groups FNDRESPGROUP, apps.fnd_responsibility_TL FNDRESP WHERE fnduser.user_id=FNDRESPGROUP.user_id AND FNDRESP.responsibility_id=FNDRESPGROUP.responsibility_id AND upper(fnduser.user_name) like upper(‘USERNAME’) 

Even if you are connected to the EBS and you execute the concurrent program without ensuring the correct combination of User Id, Responsibility ID and Application ID BizTalk would fail to set the application context and you would get the exception details in the Event Log given below. 

The adapter failed to transmit message going to send port "" with URL "oracleebs://Servername/TNS/Dedicated". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.ConnectionException: Could not retrieve User ID, Responsibility ID, and Application ID. These values are required to set the application context. Make sure that you have specified correct values in the binding properties or the message context properties for setting the application context. 

You would get a good explanation and a tool to resolve this error here but still I was getting the same at runtime and I discovered that the username was to be in Uppercase. I was using the username with lower case and it would work for generating the schemas but I was getting this error at runtime. 

Configuring the WCF Oracle E-Business Adapter: 

To configure the send port select WCF Custom adapter and click configure. In the General tab you have to specify the Endpoint address of the adapter service. It will be in the format oracleebs://[serverip]:[port]/[Service] as shown below. 

 

In the SOAP action header section you have to specify the action and operation name. You can get the action value from the generated schemas in the BizTalk solution. In the schema XML you will find the value something similar to the one in the screenshot above. 

The main configuration is in the binding tab. First select oracleEBSBinding and have the correct values for oracleEBSOrganizationId, oracleEBSResponsibilityKey, oracleUserName and oraclePassword. The username and password here are the database credentials. You can see my configurations below. 

 

If you are selecting clientCredentialType as Ebusiness then you need to enter the EBusiness credentials in the Security Tab.