Posts tonen met het label Integration. Alle posts tonen
Posts tonen met het label Integration. Alle posts tonen

woensdag 1 juni 2016

Call Webservice with Proxy from ADF

Following the example from the UI Accelerator app to call a web service in my fusion cloud environment (http://www.oracle.com/technetwork/indexes/samplecode/cloud-samples-2203466.html) I will build the call to the findSelfUserDetails service in ERP Cloud, which I want to use to verify the user.
With many thanks to Hakan Biroglu, for figuring this stuff out in his spare time :-)!

This services does not receive any parameters (like the opportunity examples), so it should be a lot simpler. All we want to do is verify the user information. Like all tutorials it describes the solution bottom up (since you have to develop it bottom up), but it's easier to understand top down. So I'll first try to explain what end result we are trying to achieve and how to get there.

Overview
Eventually we want to create a simply page displaying the user information.



This page actually points to a task flow and the task flow contains a page fragment.

The data control however is based on our web service proxy. To be able to call a web service and show it on the page, we will create a web service with proxy project. This will generate a java class that actually calls the web service (which we will overwrite). But next to that we will need two custom java classes to create a view object based on our web service and a java class that fetches the web service results and puts it in the view object.
The view object in its turn will be exposed as a datacontrol and it's that data control that is displayed on our page fragment.


So these are the components we are going to create.

Step 1: Web service with Proxy project

The first step is to create a new project with web service with proxy client. You can create a new application or a project in an existing application.
In your project choose New, Web Services, Web Service Client and Proxy.


As WSDL you refer to your fusion environment (HCM) and make sure you check Copy WSDL into project so you can review the XSD files in your project as well.


In our case we use our own fusion ERP environment, but this can also be sales cloud. It will be something like oraclecloud.com:nnnn, where nnnn is the port number.
On the next page uncheck Generate as Async.

Move on till you get to this page and check Don't generate any asynchronous methods.

On the policy settings uncheck the Show only the compatible client policies. Make sure the port is set correctly and use wss-username-token-client-policy.
And on the next page with the handlers choose the correct port.


Now you can generate the project. This will create a lot of java classes and the definition files.
To give you an idea open the UserDetails.xsd under apps/hcm/people/roles/userdetailsservicev2.


Here you can see the result is UserDetailsResult with a value containing the fields that it will return.
You can verify the return in SoapUI first of course so you have an idea of the structure. As you can see it contains a Value and UserPersonDetails within the value component.




Step 2: Complete Web Service Proxy Java Class

By creating the web service proxy, the system also generates a java class to call our web service. We are not going to overwrite that, since it gets overwritten when the proxy is regenerated. You will find this file under apps/hcm/people/roles/userdetailsservicev2 in UserDetailsServiceSoapHttpPortClient.java.


So the first thing we will do is create our own PortClient.


Choose New, Java Class.


This will generate the following code.

package wsproxysample.custom;

public class UserDetailsServicePortClientCustom {
    public UserDetailsServicePortClientCustom() {
        super();
    }
}

Now we will change this code as follows.

package wsproxysample.custom;

import com.oracle.xmlns.apps.hcm.people.roles.userdetailsservicev2.UserDetails;
import com.oracle.xmlns.apps.hcm.people.roles.userdetailsservicev2.UserDetailsResult;
import com.oracle.xmlns.apps.hcm.people.roles.userdetailsservicev2.UserDetailsService;
import com.oracle.xmlns.apps.hcm.people.roles.userdetailsservicev2.UserDetailsService_Service;

import com.sun.xml.ws.developer.WSBindingProvider;

import java.util.List;
import java.util.Map;

import javax.xml.ws.BindingProvider;
import javax.xml.ws.WebServiceRef;

import weblogic.wsee.jws.jaxws.owsm.SecurityPolicyFeature;

// Here our class starts

public class UserDetailsServicePortClientCustom {
    
// Copy the web service reference annotation with the associated properties (will import or use Alt-Enter). And create two private variables to hold the web service.

    @WebServiceRef
    private static UserDetailsService_Service userDetailsService_Service;
    private UserDetailsService userDetailsService;
    
    public UserDetailsServicePortClientCustom() {
        super();
    }
    
//Next step is to create a list of the user details. The type UserDetails is what we saw earlier in our XSD.
    @SuppressWarnings("unchecked")
    public List<UserDetails> findUserDetails() throws Exception {
    /* Initialize/authenticate service */

//Initialize the service and set the policy.
    UserDetailsService_Service userDetailsService_Service = new UserDetailsService_Service();
    //we are using basic authorizat ion to call a webservice usin g https protocol, so that we have to use ssl_client_policy OWSMpolicy
    SecurityPolicyFeature[] m_securityFeature =
    new SecurityPolicyFeature[] { new SecurityPolicyFeature("oracle/wss_username_token_over_ssl_client_policy") };
    //initialize binding object by setting up security policy
    userDetailsService = userDetailsService_Service.getUserDetailsServiceSoapHttpPort(m_securityFeature);
    //OpportunityService opportunityService = opportunityService_Service.getOpportunityServiceSoapHttpPort(m_securityFeature);
    //retrieve request context object to setup http header
    WSBindingProvider wsbp = (WSBindingProvider)userDetailsService;
    Map<String, Object> requestContext = wsbp.getRequestContext();

//Here you add the actual service endpoint and the username/password you use to connect.

    String serviceEndpoint = "https://{your fusion cloud environment}/hcmPeopleRolesV2/UserDetailsService";
    String serviceUsername = "{your username}";
    String servicePassword = "{your password}";
    requestContext.put (BindingProvider.ENDPOINT_ADDRESS_PROPERTY,serviceEndpoint );
    requestContext.put (WSBindingProvider.USERNAME_PROPERTY,serviceUsername);    
    requestContext.put (WSBindingProvider.PASSWORD_PROPERTY,servicePassword);
    /* busines logic */

//Again check the XSD to see it returns a userdetailsresult object and a value within that. So we use getValue to get the actual result.

    UserDetailsResult userDetailsResult = userDetailsService.findSelfUserDetails();

        return userDetailsResult.getValue();
    }
}


So what we've build now is a java program that calls the web service and fetches the results and returns that. Next step is we want to call this service from another java class that puts the result in a view object. Both the view object and the java class to call the service and put the result in the view object is what we will create next.

Note that our example is very simple without any parameters. The opportunity service example is much more elaborate.

Step 3: Create View Object Java Class

Next step is to create a java class for a view object. Go to your View Controller project and choose New, Java Class.




We call this class UserDetailsVO, since it's the view object for our user details. Since this is a custom view object we need to create our own setters and getters for each element we want to display. We will refer of course to the elements in the UserDetails object we've seen in the XSD.

package test.proxysample.view;

import java.io.Serializable;

public class UserDetailsVO implements Serializable {
    
    @SuppressWarnings("compatibility:5889044184363220877")
    private static final long serialVersionUID = 1771059748298259803L;
    private String personNumber;
    private String displayName;
    private String firstName;
    private String emailAddress;
    
    public UserDetailsVO() {
        super();
    }
    
    public void setPersonNumber(String personNumber) {
    this.personNumber = personNumber;
    }
    public String getPersonNumber() {
    return personNumber;
    }

    public void setFirstName(String firstName) {
    this.firstName = firstName;
    }
    public String getFirstName() {
    return firstName;
    }

    public void setEmailAddress(String emailAddress) {
    this.lastName = emailAddress;
    }
    public String getEmailAddress() {
    return emailAddress;
    }

    public void setDisplayName(String displayName) {
    this.displayName = displayName;
    }
    public String getDisplayName() {
    return displayName;
    }
    
    
}


So now we can display first name, last name, display name and the person number. If we want to display more values, we need to add them here.
Next step is to call the web service and fill our view object with data.

Step 4: Create Java Class to call Webservice and fill View Object




We create another java class in our View Controller project called UserDetailsBean.




The code for this bean is as follows. Note that the structure of our result (Value and PersonDetails) is flattened in our view object.

import com.oracle.xmlns.apps.hcm.people.roles.userdetailsservicev2.UserDetails;
import com.oracle.xmlns.apps.hcm.people.roles.userdetailsservicev2.UserPersonDetails;

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

import test.proxySample.model.custom.UserDetailsServicePortClientCustom;

public class UserDetailsBean {
    
    private UserDetailsServicePortClientCustom proxyClient;
    
    public UserDetailsBean() {
        super();
        this.proxyClient = new UserDetailsServicePortClientCustom();
    }
    
// We create a new list for our UserDetails View Object.

    public List<UserDetailsVO> findUserDetails() throws Exception {

// This list is initialized by calling our finduserDetails web service.
    List<UserDetails> list = proxyClient.findUserDetails();
    List<UserDetailsVO> response = new ArrayList<UserDetailsVO>();

// And now we want to parse the result of our web service back to our view object. The top level of our service was the Value. So we loop through the header UserDetails and fetch all the values from the UserDetails object.

    for (UserDetails udBind : list ) {
    //The following at t ributes are returned in the xml payload
    String personNumber = udBind.getPersonNumber().toString();

// Create a new view object instance.
    UserDetailsVO udVO = new UserDetailsVO();

// And set the person number field.
    udVO.setPersonNumber(personNumber);

// Then we get the Person Details, which is a sub of the person. This contains the first name, last name and display name. Review your Soap UI project if you are not sure of the level.

    List<UserPersonDetails> userPersonDetails = udBind.getUserPersonDetails();

// Loop through the results of the details and put the fields in the VO.

    for (UserPersonDetails upd : userPersonDetails) {

        String displayName = upd.getDisplayName().getValue().toString(); 
        String emailAddress  = upd.getEmailAddress().getValue().toString();
        String firstName = upd.getFirstName().getValue().toString();

        udVO.setDisplayName(displayName);
        udVO.setFirstName(firstName);
        udVO.setEmailAddress(emailAddress);
    }

// Return response
    response.add(udVO);
    }
    return response;
    }
}


Step 5: Expose bean as data control



This is simple, right click on the UserDetailsBean and choose Create Data Control at the bottom.




The bean is now exposed as data control and you can drag it onto a page. So we will now create a simple task flow with a page fragment and drag the data control to the page fragment.

Step 6: Create task flow and page fragment



On your View Controller project choose New, ADF Task Flow.


Choose New on your View Controller, ADF Page Fragment. You can base it on a template or for now we simply create a blank page.


Drag the CallWS.jsff onto your task flow callWS-flow.
You can also add page parameters to the task flow, but we won't do that for now.

Now we drag the data control onto our callWS.jsff page fragment.



Our very simple page now looks like this





Step 7: Create task flow and page fragment

The last step is to create our main page and drag the task flow onto that page. To do this create a new page. Add a PanelGroupLayout with vertical alignment. Drag the task flow in the source editor in the group layout and make sure you move it in the group layout.




Step 8: Import certificates

Before you deploy it, we first have to import the certificates from the service.

Go the website of your fusion application in Internet Explorer or Firefox and click on the lock.  Choose Export to export the certificates.

On the mac find the location of your JDK (keytool). In my case it was located in 
/library/java/JavaVirtualMachines/jdk1.8*/contents/home/bin.

Second, save the certificates in some directory on your computer. Let's say this is /users/../myDir
Check the location of the keystone in your Jdeveloper, Preferences. Let's say this is {KeyStorePath}.




keytool -importcert -file /users/../myDir/eccs-test-root.cer -keystore {KeyStorePath} -alias eccs-test-root -storepass DemoTrustKeyStorePassPhrase
keytool -importcert -file /users/../myDir/eccs-test-intermed.cer -keystore {KeyStorePath} -alias eccs-test-intermed -storepass DemoTrustKeyStorePassPhrase
keytool -importcert -file /users/../myDir/eccs-test-fs.em2.oracle.com.cer -keystore {KeyStorePath} -alias eccs-test-fs.em2.oracle.com -storepass DemoTrustKeyStorePassPhrase


Step 9: Set web logic security

Go the project properties of your view controller project.


Choose Run/Debug and edit the Default configuration.
Copy 
-Dweblogic.security.SSL.ignoreHostnameVerification=true
in the java options of the virtual machine.





Step 10: Run on integrated web logic server

Click on the home page and choose Run.


Other

So if you want to add more fields to display in your web service data control, modify UserDetailsVO.java.
You can also add information from the user session details or user work relationship details for example. For example fetch the language from the user session details. Add this field to your view object.

    
    private String language;

    public void setLanguage(String language) {
    this.language = language;
    }
    public String getLanguage() {
    return language;
    }


Then add it to the UserDetailsBean.java, since we need to fetch it from the web service and put it in the data control.

Add this to the UserDetailsBean.java after the for loop for the person details.

        List<UserSessionDetails> userSessionDetails = udBind.getUserSessionDetails();
        for (UserSessionDetails usd : userSessionDetails) {
            String language = usd.getLanguage().getValue().toString();             
            udVO.setLanguage(language);
        }  


Now go to your page fragment (callWS.jsff) and drag the language on the page. Set the property Behavior Read Only to true.

And when you re-run your page ...


Have fun!

woensdag 27 april 2016

Integrate Procurement Cloud Contracts and eBS Procure to Pay (on premise)


In the previous blog we investigated an integration between Oracle eBS on premise and Oracle Sourcing in the cloud.

http://pamkoertshuis.blogspot.nl/2016/04/procurement-integration-ebusiness-suite.html




The integration scenario is shown above. We performed a direct integration without any middleware starting in eBS on premise with a requisition for a sourcing event. This triggered a web service to create the sourcing event directly in cloud. After rewarding the sourcing event another process is triggered to fetch the awarded event and create the purchase order in eBS (either through the open interface, the API (see for example http://pamkoertshuis.blogspot.nl/2015/11/open-interface-requisition-to-purchase.html)  or a custom exposed web service through Integrated SOA Gateway for example).

Of course the same scenario can be handled using middleware (like Oracle SOA or any other servicebus). The following scenario starts in the cloud environment with a purchasing contract. All contracts are managed centrally in the cloud environment and we want to enforce the contract agreements on our subsystems where procure to pay is handled.
So in generic terms, we are looking for the following integration.


Supplier qualification is in this scenario also done in the cloud, so our cloud environment is the master for our supplier base and the suppliers need to be interfaced to the subsystems (which could be only one, but also multiple).

Our main concern now is how to get the data from our cloud environment to our subsystems. Of course there is always the method of calling web services, but how do we know which contracts and suppliers have been created?
The procurement cloud solution however comes with a very nice solution for this. The cloud solution already contains a standard SOA process that is triggered by the creation/updating of both contracts and suppliers.
For contracts this is called the Purchasing Integration SOA, for suppliers the Supplier Sync Service. Both are BPEL processes that accept events from procurement cloud and which call a set of web services to handle the requests.



The ECM contract fulfillment SOA implementation has attempted to modularize integration with the target procurement application based on the purchasing flow that is derived from the contract type of the given contract:
  • If a contract is created from a contract type with intent as 'Buy' and contract type class as 'Enterprise Contract', then purchase orders can be initiated from the fulfillment lines of the contract.
  • If a contract is created from a contract type with intent as 'Buy', contract type class as 'Agreement', and lines are allowed on the contract type, then blanket purchase agreements can be initiated from the fulfillment lines of the contract.
  • If a contract is created from a contract type with intent as 'Buy', contract type class as 'Agreement', and lines are not allowed on the contract type, then contract purchase agreement can be initiated from the fulfillment line of the contract.

For the internal handling of contracts to purchase orders it calls the same SOA process, but it can also call third party web services (so called intermediary web services). So in a full diagram, this is what we can achieve.
Note that I also described our previous integration scenario in the diagram, where instead of a direct integration from eBS to the cloud we could use an intermediary service in our integration layer.



In the current scenario we do our strategic procurement (contracts management, supplier qualification) in the cloud and we use the Purchasing Integration SOA and Supplier Sync Service to send our data to an integration layer. This integration layer contains the intermediary web services required for the integration, which in their place handle the specific requests necessary for the subsystems. In eBS for example it could fill the open interface tables, call an API or call a custom web service exposed through Integrated SOA Gateway. Of course integration with eBS is something we are already familiair with!

The intermediary web service needs to be of a specific format (the interface/WSDL is fixed, see below), which is described in
Note that in theory we could create this web service also directly in eBS (using Integrated SOA Gateway for example) and do a direct integration.

Now all you have to is register the intermediary web service in Manage Contract and Procurement System Integration.

As you can see here the system supports two methods of integration: Direct and Indirect. With direct integration you call a web service that immediately returns the result and the contract information is updated with the information returned by the service.
Using indirect integration it assumes you use an integration pattern with staging tables (like the open interface of eBS) and you run the ECM Contract Fulfillment Batch program to return the result to the cloud.
You can specify multiple endpoints here, but in our scenario it makes sense to create one intermediary service on the integration layer which handles the transformation to the different subsystems.

For suppliers the setup is more or less similar, except that you can only specify one endpoint (in this case you are likely to use middleware to transfer the supplier data to multiple systems).


So all we need to do in our integration scenario is write the logic for the intermediary service. The triggering of the business events and the invocation of our web services is handled by setup in the cloud!



Interface Intermediary Webservice


<?xml version="1.0" encoding="UTF-8"?>
<wsdl:definitions name="PurchasingInterfaceService" targetNamespace=http://xmlns.oracle.com/apps/contracts/deliverableTracking/purchasingInterfaceService/ xmlns:ns1="http://xmlns.oracle.com/apps/contracts/deliverableTracking/purchasingInterfaceService/contracts PurchaseDocument/types/"
xmlns:plnk="http://schemas.xmlsoap.org/ws/2003/05/partner-link/" xmlns:client=http://xmlns.oracle.com/apps/contracts/deliverableTracking/purchasingInterfaceService/ xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/">
<wsdl:documentation>

<name>PurchasingInterfaceService</name> <docCategories> <category>None</category> </docCategories>
</wsdl:documentation>
<plnk:partnerLinkType name="FusionPurchasingInterfaceProcess"> <plnk:role name="PurchasingInterfaceProcessProvider">

<plnk:portType name="client:PurchasingInterfaceService"/> </plnk:role>
<plnk:role name="PurchasingInterfaceProcessRequester"> <plnk:portType name="client:PurchasingInterfaceServiceResponse"/> </plnk:role>

</plnk:partnerLinkType>
<wsdl:types>
<schema xmlns="http://www.w3.org/2001/XMLSchema">

<import namespace="http://xmlns.oracle.com/apps/contracts/deliverableTracking/purchasingInterfaceService/contract sPurchaseDocument/types/"
schemaLocation="xsd/PurchasingInterfaceService.xsd"/>
</schema>
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<import namespace="http://schemas.xmlsoap.org/ws/2003/03/addressing" schemaLocation="oramds:/apps/org/xmlsoap/schemas/ws/2003/03/addressing/ws-addressing.xsd"/> </schema>
</wsdl:types>
<wsdl:message name="GetPurchaseDocumentRequest">
<wsdl:part name="payload" element="ns1:GetPurchaseDocumentRequest"/>
</wsdl:message>
<wsdl:message name="GetPurchaseDocumentResponse">
<wsdl:part name="payload" element="ns1:GetPurchaseDocumentResponse"/>
</wsdl:message>
<wsdl:message name="CreatePurchaseDocumentRequest">
<wsdl:part name="payload" element="ns1:CreatePurchaseDocumentRequest"/>
</wsdl:message>
<wsdl:message name="GetInterfaceDocDetailsRequest">

<wsdl:part name="payload" element="ns1:GetInterfaceDocDetailsMessage"/> </wsdl:message>
<wsdl:message name="CreatePurchaseDocumentResponse">

<wsdl:part name="payload" element="ns1:CreatePurchaseDocumentResponse"/> </wsdl:message>
<wsdl:message name="GetInterfaceDocDetailsResponse">

<wsdl:part name="payload" element="ns1:GetInterfaceDocDetailsMessage"/> </wsdl:message>
<wsdl:message name="TestRequest">

<wsdl:part name="payload" element="ns1:TestRequest"/> </wsdl:message>
<wsdl:message name="TestResponse">

<wsdl:part name="result" element="ns1:TestResponse"/> </wsdl:message>
<wsdl:portType name="PurchasingInterfaceService">

<wsdl:operation name="getPurchasingActivityDetails">
<wsdl:input message="client:GetPurchaseDocumentRequest"/>

<wsdl:output message="client:GetPurchaseDocumentResponse"/> </wsdl:operation>
<wsdl:operation name="createPurchaseDocument">

<wsdl:input message="client:CreatePurchaseDocumentRequest"/> </wsdl:operation>
<wsdl:operation name="getInterfacedPurchasingDocumentDetails">

<wsdl:input message="client:GetInterfaceDocDetailsRequest"/> </wsdl:operation>
<wsdl:operation name="testIntegration">

<wsdl:input message="client:TestRequest"/>
<wsdl:output message="client:TestResponse"/> </wsdl:operation>
</wsdl:portType>
<wsdl:portType name="PurchasingInterfaceServiceResponse">

<wsdl:operation name="createPurchaseDocumentResponse"> <wsdl:input message="client:CreatePurchaseDocumentResponse"/>
</wsdl:operation>
<wsdl:operation name="getInterfacedPurchasingDocumentDetailsResponse">
<wsdl:input message="client:GetInterfaceDocDetailsResponse"/> page11image1216 page11image1640 page11image1800
</wsdl:operation> </wsdl:portType> </wsdl:definitions> 


woensdag 11 november 2015

Creating webservices in PL/SQL for SOA Gateway eBS R12

Creating webservices in PL/SQL for SOA Gateway eBS R12

To create a webservice in eBS R12 you can use the Integrated SOA Gateway. To do this you have to follow the following steps

Step 1: Create database package with annotation

First create a database package with extension pls (!) and the following annotation


CREATE OR REPLACE PACKAGE XXXORAWS AS
/* $Header: XXXORAWS $ */
/*#
* Read Oracle User information webservice
* @rep:scope public
* @rep:product FND
* @rep:displayname General Oracle Webservices for Service Integration
* @rep:category BUSINESS_ENTITY ORACLEWS
*/


Step 2: Create database package procedure with annotation

Now create a procedure for example to create orders through a webservice. In this example we have an order header and lines in one field (XML input). Note that you can pass tables and record constructs as well,

PROCEDURE W_Order (
    OrderHeaderRec   IN T_OrderHeaderRec ,
    OrderLinesTbl    IN T_OrderLineTbl,


but if you call this from other applications they may not always support that!

/*#
* This procedure is for creating the order web service
* @param Account_Name          Account_Name
* @param Tax_Reference_Number  Tax_Reference_Number
* @param Ship_Account_Number   Ship_Account_Number
* @param Ship_Address      Ship_Address
* @param Ship_PC    Ship_PC
* @param Ship_City       Ship_City
* @param Ship_State       Ship_State
* @param Ship_Country_Code     Ship_Country_Code
* @param Bill_Account_Number Bill_Account_Number
* @param Bill_Address      Bill_Address
* @param Bill_PC    Bill_PC
* @param Bill_City       Bill_City
* @param Bill_State       Bill_State
* @param Bill_Country_Code     Bill_Country_Code
* @param Order_Number          Order_Number
* @param ordered_date          ordered_date
* @param Order_Amount     Order_Amount
* @param Currency              Currency
* @param ORDER_TYPE_CODE       ORDER_TYPE_CODE
* @param Cr_Card_Pay_Status    Cr_Card_Pay_Status
* @param Cr_Card_Acceptance    Cr_Card_Acceptance
* @param Email_Address         Email_Address
* @param Tax_Perc     Tax_Perc
* @param Tax_Amount     Tax_Amount
* @param Discount_codes    Discount_codes
* @param Po_Number             Po_Number
* @param OrderLines Orderlines
* @param x_output XML Return Message
* @rep:displayname Create webshop/TRP order
* @rep:scope public
* @rep:lifecycle active
*/
 Procedure W_OrderWS (
  Account_Name          IN VARCHAR2
, Tax_Reference_Number  IN VARCHAR2
, Ship_Account_Number   IN VARCHAR2
, Ship_Address      IN VARCHAR2
, Ship_PC    IN VARCHAR2
, Ship_City       IN VARCHAR2
, Ship_State   IN VARCHAR2
, Ship_Country_Code     IN VARCHAR2
, Bill_Account_Number IN VARCHAR2
, Bill_Address      IN VARCHAR2
, Bill_PC    IN VARCHAR2
, Bill_City       IN VARCHAR2
, Bill_State   IN VARCHAR2
, Bill_Country_Code     IN VARCHAR2
, Order_Number          IN VARCHAR2
, ordered_date          IN DATE
, Order_Amount      IN NUMBER
, Currency              IN VARCHAR2
, ORDER_TYPE_CODE       IN VARCHAR2
, Cr_Card_Pay_Status    IN VARCHAR2
, Cr_Card_Acceptance    IN VARCHAR2
, Email_Address         IN VARCHAR2
, Tax_Perc       IN NUMBER
, Tax_Amount   IN NUMBER
, Discount_codes  IN VARCHAR2
, PO_Number             IN VARCHAR2
,  OrderLines    IN CLOB
 ,x_output              OUT VARCHAR2);
 


Step 3: Create your database logic

Now your procedure can do anything with the given input data ofcourse. In our case it returns the status using something like this

 TYPE
   T_XML_Outputs IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;


 
 G_XML_OUTPUT   T_XML_Outputs;
 G_XML_OUTPUT_ID NUMBER := 1;
 G_MAX_LENGTH   NUMBER := 32000;
 G_XML_ERROR    VARCHAR2(1000);
 G_XML_LENGTH   NUMBER := 0;
 G_ERROR_PNT    VARCHAR2(240);


...

 XXXORAWS.Output_To_Webservice (XXORACLEWS.xml_header
         || Chr(10) || '  <status>' || l_status || '</status>'
         || Chr(10) || '  <errorcode>' || l_error_code || '</errorcode>'
         || Chr(10) || '  <msg>' || l_msg || '</msg>'
         || Chr(10) || '  <debug_msg>' || l_debug_msg || '</debug_msg>'
                                    || Chr(10) || '  <DEBUG_MESSAGES>' || g_debug_messages || '</DEBUG_MESSAGES>'    
         || Chr(10) || '  <operation>W_OrderWS</operation>'
         || Chr(10) || '   </oracle>');


And the output to webservice function is something like this .. it depends on what you want to output if this is a large text or not. Most write webservices output only the status, but we also creat read services that display a listof something and that output can be quite long.

PROCEDURE Output_To_Webservice(
    p_add_xml VARCHAR2 )
IS
BEGIN
  -- We split the output in pieces. In most cases we only need one piece.
  XXXORAWS.G_XML_LENGTH                                                                            := XXXORAWS.G_XML_LENGTH                       + NVL (LENGTH (p_add_xml),0);
  IF NVL (LENGTH (XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID)),0) + NVL (LENGTH (p_add_xml),0) < XXXORAWS.G_MAX_Length
    --    IF XXXORAWS.G_XML_LENGTH < XXXORAWS.G_MAX_LENGTH * XXXORAWS.G_XML_OUTPUT_ID
    THEN
    NULL;
  ELSE
    XXXORAWS.G_XML_OUTPUT_ID                := XXXORAWS.G_XML_OUTPUT_ID + 1;
    XXXORAWS.G_XML_OUTPUT (G_XML_OUTPUT_ID) := NULL; -- Init
  END IF;
  XXXORAWS.G_ERROR_PNT                             := 'Add XML ' || XXXORAWS.G_XML_OUTPUT_ID || ' size piece is ' || LENGTH (p_add_xml) || ' and length current ' || LENGTH (XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID));
  XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID) := XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID) || p_add_xml;
  XXXORAWS.G_ERROR_PNT                             := 'Added XML ' || XXXORAWS.G_XML_OUTPUT_ID || ' size piece is ' || LENGTH (p_add_xml) || ' and length current ' || LENGTH (XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID));
 END Output_To_Webservice;

An example is a service that displays all countries in Oracle


------------------------------------------------------------
  -- This procedure lists all countries in Oracle.
  ------------------------------------------------------------
  Procedure R_CountryWS
  IS

    CURSOR C_Countries
    IS
    SELECT t.territory_code
    , Initcap (t.nls_territory) nls_territory
    , T.Territory_Short_Name
    , T.Description
    FROM   FND_TERRITORIES_VL T
    WHERE T.Obsolete_Flag = 'N'
    ORDER BY t.territory_short_name
    ;

  BEGIN
    l_status := Fnd_Api.g_ret_sts_success;
    l_msg    := null;

       XXXORAWS.Output_To_Webservice (xml_header || Chr(10) ||
                '  <status>' || l_status || '</status>' || Chr(10) ||
                '  <errorcode>' || l_error_code || '</errorcode>' || Chr(10) ||
                '  <msg>' || l_msg || '</msg>' || Chr(10) ||
                '  <operation>R_CountryWS</operation>' || Chr(10) ||
                '  <countries>' || Chr(10));


    FOR C IN C_Countries
      LOOP

   XXXORAWS.Output_To_Webservice ('    <country>' ||  Chr(10) ||
          '      <code>' || C.Territory_Code || '</code>' || Chr(10) ||
          '      <nlscode>' || C.NLS_Territory || '</nlscode>' || Chr(10) ||
          '      <name>' || C.Territory_Short_Name || '</name>' || Chr(10) ||
          '      <description>' || C.Description || '</description>' || Chr(10) ||
          '    </country>' || Chr(10));
      END LOOP; -- Countries


   XXXORAWS.Output_To_Webservice ('  </countries>' || Chr(10) || xml_footer);
    exception
        when others then
           l_status := fnd_api.g_ret_sts_error;
           l_msg    := sqlerrm;
           l_error_code := G_COUNTRY_ERROR;
           XXXORAWS.Error_To_Webservice (xml_header || Chr(10) ||
                '  <status>' || l_status || '</status>' || Chr(10) ||
                '  <errorcode>' || l_error_code || '</errorcode>' || Chr(10) ||
                '  <msg>' || l_msg || '</msg>' || Chr(10) ||
                '  <operation>R_CountryWS</operation>' || Chr(10) ||
              xml_footer);

  END; -- R_CountryWS

And this would be exposed in our package header like this. Note that you don't have to add the annotation in the package body.

  ------------------------------------------------------------
  -- This procedure lists all countries in Oracle.
  ------------------------------------------------------------
/*#
* This procedure returns a list of countries
* @param x_output XML Return Message
* @rep:displayname Oracle Country List
* @rep:scope public
* @rep:lifecycle active
*/  
  Procedure R_CountryWS (x_output OUT CLOB)
    ;



Step 4 : Install the service

I usually create a driver script where I prompt the user to enter the version of the webservice. If left empty we do not re-install the service.

rm -f L*.log
if [ "${WEB_VERSION}" ]
then
echo "Loading webservice package" >> $LOGFILE
echo "Version is "$WEB_VERSION >> $LOGFILE
#cp XXXORAWS.pls $INSTALLATION_DIR/install/sql
$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=SYSADMIN XXX:install/sql:XXXORAWS.pls:$WEB_VERSION=/$TWO_TASK/apps/apps_st/appl/xxx/12.0.0/install/sql/XXXORAWS.pls
$FND_TOP/bin/FNDLOAD $APPS_USER/$APPS_PASS 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct XXXORAWS_pls.ildt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

for i in `ls -dF L*.log`
  do
   cat $i >> $LOGFILE
  done

else
  echo "Not reinstalling webservices." >> $LOGFILE
fi




Step 5 : Generate and deploy the webservice

Login as Integrated SOA Gateway responsibility as SYSADMIN and navigate to the Integration Repository. Perform a search using the button on the right. Choose Advanced Options and look for interface type Custom. This should show our webservice.


Open the service and click [Generate WSDL]. If you do not see this button you are not logged in as SYSADMIN.

Now choose Deploy button on the left.
If you have added a new function it should be granted to the user you use for accessing the webservices (usually one specific EBS user with no responsibilities). before you can use it. Click on the function you want to change and grant the user.

 
 



 


 Step 6: Test webservice

Use  View WSDL to see the wsdl and remove ?wsdl. Now you get to the test page.
Here you can test the service. It's good practice to create one generic service that actually does nothing except for return the name of the database so you always know you are pointing at the right environment and that the service is working.

You have to pass the username/password and responsibility key. You can see these up in EBS with a responsibility without any menu items so the user can't login.






Choose invoke at the bottom and see the output in P_OUTPUT to see the result.