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!

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

• Customizing Fusion Enterprise Contracts for Purchasing Integration (whitepaper april 2015).
• Fusion Sourcing and Fusion Procurement Contracts Integration Options (Doc ID 1956769.1)
• Supplier Sync Service (Fusion Supplier Sync Service)
• Fusion Sourcing and Procurement Integration (Fusion Sourcing and Procurement Contracts Integratopn paper)

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"/> 

</wsdl:operation> </wsdl:portType> </wsdl:definitions> 

Fetch profile options

To find profile options set on different levels you can use the following query. You can filter on level (application, responsibility, user, etc) if necessary.

SELECT AP.Application_Short_Name

,      OP.PROFILE_OPTION_NAME
,      OP_T.USER_PROFILE_OPTION_NAME
,      DECODE (OV.Level_Id,10001,'Site', 10002,'Applicatie',10003, 'Autorisatie', 10004,'Gebruiker') level_Id
,      DECODE (OV.Level_Id,10002,

(SELECT A.Application_Short_Name FROM FND_APPLICATION A WHERE A.Application_Id = OV.Level_Value)
                          ,10003,

(SELECT R.Responsibility_Name FROM FND_RESPONSIBILITY_TL R, FND_RESPONSIBILITY RR WHERE R.Responsibility_Id = OV.Level_Value   AND OV.Level_Value_Application_Id = R.Application_Id AND R.Language = USERENV ('LANG')

AND R.Responsibility_Id = RR.Responsibility_Id AND R.Application_Id = RR.Application_Id  )
                          ,10004,

(SELECT U.User_Name FROM FND_USER U WHERE U.User_Id = OV.Level_Value)
                          , OV.Level_Value) Level_Value
,      OV.Level_Value
,      OV.Profile_Option_Value
FROM FND_PROFILE_OPTION_VALUES    OV
,    FND_PROFILE_OPTIONS          OP
,    FND_PROFILE_OPTIONS_TL       OP_T
,    FND_APPLICATION              AP
WHERE OV.Profile_Option_id          = OP.Profile_Option_id
AND   OP.Profile_Option_Name        = OP_T.PROFILE_OPTION_NAME
AND   OV.Application_Id             = AP.Application_Id
;

Procurement integration eBusiness Suite R12.1.3 on premise with Fusion Procurement Cloud R10

During one of our demos we investigated the following integration scenario. A requisition for a sourcing request was created in eBS and should automatically be converted to an RFQ in the cloud environment.

In order to do direct integration from the eBS environment on premise to the cloud sourcing environment we need to do some security setup on the eBS database server (a wallet and an ACL).

Find webservice

First we need to investigate which webservice in cloud we can use. So we check out the new Oracle Enterprise Repository

http://www.oracle.com/webfolder/technetwork/docs/HTML/oer-redirect.html

And navigate to Procurement, SOAP Webservices and in our case R10. Choose SOAP Web Services for Oracle Procurement Cloud and navigate to Business Object Services. You will find the following services

·        Purchase Agreement

·        Purchase Order

·        Purchase Order Service Version 2

·        Supplier Negotiation Version 2

·        Update Preparer and Requester

We need the Supplier Negotiation Version 2. If you choose that link

http://docs.oracle.com/cloud/farel10/procurementcs_gs/OESWP/Supplier_Negotiation_Version_2_NegotiationManageService_svc_4.htm#oracle.apps.prc.pon.negotiations.negotiationsServiceV2.NegotiationManageService_OpSect

You can review the operations that are available and the operation we need is initializeNegotation.

You can investigate the service to see which elements we need to pass. Currently you cannot enter a negotiation template so you have to add all requirements (otherwise the system would copy these of the template). For our demo we needed

• Header
• Lines
• Requirements

We don’t add a fixed list of suppliers on forehand, but you could also add those and for example diffent currencies as well.

So now we know which service to invoke, we can setup our security.

Setup wallet

The wallet must be defined on the database server. An example for the setup is shown below (in this case it’s a wallet on my local PC).

orapki wallet create -wallet C:\Oracle\wallet -pwd password -auto_login
orapki wallet add -wallet C:\Oracle\wallet -trusted_cert -cert "[cert_path]\root.cer" -pwd password

orapki wallet add -wallet C:\Oracle\wallet -trusted_cert -cert "[cert_path]\intermed.cer" -pwd password
orapki wallet add -wallet C:\Oracle\wallet -trusted_cert -cert "[cert_path]\[instance].oracle.com.cer" -pwd password

Import certificates into the wallet

After the wallet has been setup we need to import the certificates from the webservice we want to invoke into the wallet. First you need the WSDL to your environment that you want to connect to.

https://{server}-prc.{domain}/prcPonNegotiations/NegotiationManageServiceV2?WSDL

Open this URL in Internet Explorer and choose Internet Options, Content, Certificates. Here you can download the certificates. Use the Base64 encoded X.509 version. Now you can upload these in the wallet using the wallet manager.

Trusted Certificates:

Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

Subject:        CN=VeriSign Class 3 Public Primary Certification Authority - G5,OU=(c) 2006 VeriSign\, Inc. - For authorized use only,OU=VeriSign Trust Network,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        CN=Entrust.net Secure Server Certification Authority,OU=(c) 2000 Entrust.net Limited,OU=www.entrust.net/SSL_CPS incorp. by ref. (limits liab.),O=Entrust.net

Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        CN=Entrust.net Certification Authority (2048),OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS_2048 incorp. by ref. (limits liab.),O=Entrust.net

Subject:        CN=Symantec Class 3 Secure Server CA - G4,OU=Symantec Trust Network,O=Symantec Corporation,C=US

Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US

Subject:        CN=Entrust.net Secure Server Certification Authority,OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS incorp. by ref. (limits liab.),O=Entrust.net,C=US

Define Access Control List

After the wallet you create an ACL in the eBS database under SYSTEM user. Here [user] must be replaced with the user that is granted access (in our case it would be APPS). Also make sure you change the host to point to the correct endpoint.

BEGIN

  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'acl_fusion_file.xml',
    description  => 'ACL UTL_HTTP to Fusion Cloud',
    principal    => [user],
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);


  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('acl_fusion_file.xml' ,[user], TRUE, 'resolve');

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'acl_fusion_file.xml',
    host        => '*.prc.[datacenter].oraclecloud.com',
    lower_port  => NULL,
    upper_port  => NULL);

END;

/

Test if your setup is correct!

You can use SQL to test if the setup of your wallet and ACL is correct. Call this procedure passing the WSDL, the file path to your wallet and the password of the wallet. If necessary you can add the proxy also as parameter.

select utl_http.request('https://{host}-prc.{domain}/prcPonNegotiations/NegotiationManageServiceV2?WSDL', '','file:/db/rdbms12/ssl','welcome1') from dual;

If you get this

ORA-29273: HTTP request failed

ORA-29024: Certificate validation failure

ORA-06512: at "SYS.UTL_HTTP", line 1491

ORA-06512: at line 1

29273. 00000 -  "HTTP request failed"

*Cause:    The UTL_HTTP package failed to execute the HTTP request.

*Action:   Use get_detailed_sqlerrm to check the detailed error message.

           Fix the error and retry the HTTP request.

or

ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1577

ORA-28750: unknown error

ORA-06512: at line 1

29273. 00000 -  "HTTP request failed"

*Cause:    The UTL_HTTP package failed to execute the HTTP request.

*Action:   Use get_detailed_sqlerrm to check the detailed error message.

           Fix the error and retry the HTTP request

It usually means you did not import the correct certificates. Make sure this line is not in the wallet

Subject:        CN=*.{domain},O=Oracle Corporation,L=Redwood Shores,ST=California,C=US

Technical overview

Now our solution consists of the following components

•  initializeNegotiationWS: Procedure to call the webservice from the database given the correct parameters.

• Create_RFQ : Procedure to fetch the parameters for the webservice based on the requisition header.

• Trigger on requisition header to submit procedure Create_RFQ. 

initializeNegotationWS

This procedure is the main part of our solution. The procedure receives parameters filled by Create_RFQ. We hardcode the sections and requirements in this call using the cursor C_Requirement_Sections and C_Requirements. Currently there does not seem to be a webservice to get the requirements from the template.

Otherwise you should first call the webservice to fetch these values and pass them to this webservice. In the example we have two sections. Each section has one requirement and both weigh for 50%. One is a numeric internal, the other a text manual.

I've build it so you can call it using Create_RFQ either for testing only (it displays the payload, but does NOT make the actual call) or for real.

In the requirements section we added some formatting to make sure you can pass enters (use ![CDATA[...<br />]]) and the first line is made bold by using <b> ... </b> within the requirements text.

CREATE OR REPLACE PROCEDURE initializeNegotiationWS
(
     p_title              IN VARCHAR2
    ,p_currency             IN VARCHAR2
    ,p_doctype             IN VARCHAR2
    ,p_outcome            IN VARCHAR2
    ,p_business_unit      IN VARCHAR2
    ,p_style             IN VARCHAR2
    ,p_response_currency IN VARCHAR2
    ,p_approved_date     IN VARCHAR2
    ,p_layout             IN VARCHAR2
    ,p_line_num             IN VARCHAR2
    ,p_item_description  IN VARCHAR2
    ,p_UOM_Code             IN VARCHAR2
    ,p_line_type_id         IN NUMBER
    ,p_category             IN VARCHAR2
    ,p_unit_price        IN NUMBER
    ,p_quantity          IN NUMBER
    ,p_test_only         IN VARCHAR2 DEFAULT 'N'   
    ,x_result            OUT NUMBER
    ,x_msg               OUT VARCHAR2
    )
AS

    -------------------------------------------------------------------------------------------------------------
    -- This is the actual webservice call. We pass all parameters from the Create_RFQ procedure, call the
    -- service and read the response. We return the rfq number as x_result.
    -------------------------------------------------------------------------------------------------------------

    -------------------------------------------------------------------------------------------------------------
    -- Will be parameters from the trigger on the requisition ..
    -------------------------------------------------------------------------------------------------------------

       l_title             VARCHAR2(240) := NVL (p_title,'MW - Laptop Replacement');   
    l_currency            VARCHAR2(240) := NVL (p_currency,'USD');   
    l_doctype            VARCHAR2(240) := NVL (p_doctype,'RFQ');
    l_outcome           VARCHAR2(240) := NVL (p_outcome,'Purchase Order');
    l_business_unit     VARCHAR2(240) := NVL (p_business_unit,'US1 Business Unit');
    l_style                VARCHAR2(240) := NVL (p_style,'Standard Negotiation');
    l_response_currency VARCHAR2(240) := NVL (p_response_currency,'GBP');
    l_approved_date     DATE          := NVL (p_approved_date,sysdate);
    l_layout            VARCHAR2(240) := NVL (p_layout,'Negotiation Layout');
    l_category          VARCHAR2(240) := NVL (p_category,'Computer Supplies');
   
    l_nr_of_reqs        NUMBER;
   
    -------------------------------------------------------------------------------------------------------------
    -- In theory we fetch these from the template first and add them here. It would be better if we could
    -- pass the template in the service ..
    -------------------------------------------------------------------------------------------------------------
    CURSOR C_Requirement_Sections
    IS
    SELECT '<neg:NegotiationSections>' || chr(13) ||
            '<neg:SectionName>Section 1 description</neg:SectionName>' ||chr(13) Requirement_Section
        , 'Section1' section_name
    FROM   DUAL
    UNION
    SELECT '<neg:NegotiationSections>' ||  chr(13) ||
            '<neg:SectionName>Section 2 description</neg:SectionName>' || chr(13) Requirement_Section
            , 'Section 2' section_name
    FROM   DUAL   
    ;
   
   
    CURSOR C_Requirements (cp_section VARCHAR2)
    IS
    SELECT ' <neg:NegotiationRequirements> ' ||chr(13) ||
            ' <neg:MaximumScore>4</neg:MaximumScore> ' ||chr(13) ||
            ' <neg:RequirementText><![CDATA[<b>Some text</b><br /> ' ||chr(13) ||
            ' Some more text: <br />]]></neg:RequirementText> ' ||chr(13) ||
            ' <neg:Datatype>Number</neg:Datatype> ' ||chr(13) ||
            ' <neg:KnockoutScore>1</neg:KnockoutScore> '||chr(13) ||
            ' <neg:ResponseTypeCode>INTERNAL</neg:ResponseTypeCode> ' ||chr(13) ||
            ' <neg:ScoringMethod>Manual</neg:ScoringMethod> ' ||chr(13) ||
            ' <neg:NumberValue>2</neg:NumberValue> ' ||chr(13) ||
            ' <neg:Weight>0.5</neg:Weight> '|| chr(13) Requirement
    FROM   DUAL
    WHERE  cp_section = 'Section1'
        UNION
    SELECT ' <neg:NegotiationRequirements> ' ||chr(13) ||
            ' <neg:RequirementText><![CDATA[<b>Some text.</b> <br />'||chr(13) ||
'Some more text. ]]></neg:RequirementText>' ||chr(13) ||
            ' <neg:Datatype>Text</neg:Datatype> ' ||chr(13) ||
            ' <neg:KnockoutScore>1</neg:KnockoutScore> '||chr(13) ||
            ' <neg:ResponseTypeCode>REQUIRED</neg:ResponseTypeCode> ' ||chr(13) ||
            ' <neg:DisplayTargetFlag>1</neg:DisplayTargetFlag> ' ||chr(13) ||
            ' <neg:ScoringMethod>Automatic</neg:ScoringMethod> ' ||chr(13) ||
            ' <neg:TextValue>Complete</neg:TextValue> ' ||chr(13) ||
            ' <neg:Weight>0.5</neg:Weight> '|| chr(13) ||
            ' <neg:NegotiationRequirementScores> ' || chr(13) ||
            '   <neg:TextValue>Incomplete</neg:TextValue>' || chr(13) ||
            '   <neg:Score>1</neg:Score>' || chr(13) ||
            ' </neg:NegotiationRequirementScores> ' || chr(13) ||
            ' <neg:NegotiationRequirementScores> ' || chr(13) ||
            '   <neg:TextValue>Complete</neg:TextValue>' || chr(13) ||
            '   <neg:Score>2</neg:Score>' || chr(13) ||           
            ' </neg:NegotiationRequirementScores> ' || chr(13) Requirement
    FROM   DUAL
    WHERE  cp_section = 'Section2'
    ;

   
   

    -------------------------------------------------------------------------------------------------------------
    -- Sample from our internal environment, this is environment specific stuff
    -------------------------------------------------------------------------------------------------------------
    g_wallet_path    VARCHAR2(240) := '/.../wallet';
    g_wallet_pwd    VARCHAR2(240) := 'welcome1';
   
    -------------------------------------------------------------------------------------------------------------
    -- Webservice specific stuff
    -------------------------------------------------------------------------------------------------------------
    g_operation     VARCHAR2(240) := 'initializeNegotiation';
    g_namespace     VARCHAR2(240) := 'http://xmlns.oracle.com/apps/prc/pon/negotiations/negotiationsServiceV2';
    g_wsdl          VARCHAR2(240) := 'https://origin-ucf1-fap1781-prc.oracledemos.com:443/prcPonNegotiations/NegotiationManageServiceV2';
    g_fusion_un        VARCHAR2(240) := '[Fusion Username]';
    g_fusion_pwd    VARCHAR2(240) := '[Fusion Password]';
    g_proxy         VARCHAR2(240) := null; -- 'http://dmz-proxy.[host]:80';
    g_soap_action   VARCHAR2(240) := 'http://xmlns.oracle.com/apps/prc/pon/negotiations/negotiationsServiceV2/initializeNegotiation';
   
   
   
    -------------------------------------------------------------------------------------------------------------
    -- General variables
    -------------------------------------------------------------------------------------------------------------
    g_soap_env      VARCHAR2(240) := 'http://schemas.xmlsoap.org/soap/envelope/';   
    l_soap_request  varchar2(30000);
   
    l_result         VARCHAR2(32767) := null;
    l_http_request   UTL_HTTP.req;
    l_http_response  UTL_HTTP.resp;
    l_counter        PLS_INTEGER;
    l_length         PLS_INTEGER;
   
    l_resp_xml       XMLType;
    l_result_nr         varchar2(240);
   
    l_ns_map         varchar2(2000) ;
    x_clob           CLOB;
    l_buffer         VARCHAR2(32767);    
    l_chunkStart      NUMBER := 1;
    l_chunkData      VARCHAR2(32000);
    l_chunkLength      NUMBER := 32000; 
   
    l_error_code     VARCHAR2(240);
    l_error_pnt         VARCHAR2(240);
    l_error_action   VARCHAR2(240);
   
    l_uom_code         VARCHAR2(240);
   
  BEGIN

    l_error_pnt := 'Init';
    l_error_action := 'See error message';
    -------------------------------------------------------------------------------------------------------------
    -- Initialize settings for the HTTP call. This is webservice specific stuff!
    -------------------------------------------------------------------------------------------------------------
   
    l_ns_map := l_ns_map ||' xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" ';
    l_ns_map := l_ns_map ||' xmlns:wsa="http://www.w3.org/2005/08/addressing" ';   
    l_ns_map := l_ns_map ||' xmlns:ns0="http://xmlns.oracle.com/apps/prc/pon/negotiations/negotiationsServiceV2/types/"';
    l_ns_map := l_ns_map ||' xmlns="http://xmlns.oracle.com/apps/prc/pon/negotiations/negotiationsServiceV2/types/"';
   

    IF p_test_only = 'N'
    THEN
   
    -------------------------------------------------------------------------------------------------------------
    -- Sets the Oracle wallet used for request, required for HTTPS
    -------------------------------------------------------------------------------------------------------------
    l_error_pnt := 'setting wallet';
    dbms_output.put_line ('Set wallet: ' || g_wallet_path);
    UTL_HTTP.set_wallet('file:' || g_wallet_path, g_wallet_pwd);
   
    -- If necessary set proxy.
    g_proxy := null;
    IF g_proxy IS NOT NULL
    THEN
      dbms_output.put_line ('Set proxy: ' || g_proxy);
      UTL_HTTP.set_proxy (g_proxy);
    END IF;
   
  
    l_error_pnt := 'creating request based on wallet';
    l_error_action := 'Wallet Manager set permissions on the files, so they can only be read by the user who created them. Either modify the permissions or copy the files to another location. And then in your "set_wallet" reference the new location. ';
    -- Creates new HTTP request. You will get an error if it cannot reaad the wallet.
    l_http_request := UTL_HTTP.begin_request(g_wsdl, 'POST','HTTP/1.1');  
   
    l_error_pnt := 'setting authentication';
    -- Configure the authentication details on the request
    UTL_HTTP.SET_AUTHENTICATION(l_http_request, g_fusion_un, g_fusion_pwd);
  
    -- Configure the request content type to be xml and set the content length
    l_error_pnt := 'setting header';
    UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml;charset="UTF-8"');

    -------------------------------------------------------------------------------------------------------------
    -- Default parameters and build payload
    -------------------------------------------------------------------------------------------------------------
    l_title             := NVL (l_title,'MW - Laptop Replacement');   
    l_currency            := NVL (l_currency,'USD');   
    l_doctype            := NVL (l_doctype,'RFQ');
    l_outcome           := NVL (l_outcome,'Purchase Order');
    l_business_unit     := NVL (l_business_unit,'US1 Business Unit');
    l_style                := NVL (l_style,'Standard Negotiation');
    l_response_currency := NVL (l_response_currency,'GBP');
    l_layout            := NVL (l_layout,'Negotiation Layout');
    l_category          := NVL (p_category,'Computer Supplies');
   
    l_uom_code            := 'zzu'; -- Is each
   
    END IF; -- If we are only testing, we just want to show the payload!
   
    l_error_pnt := 'creating payload';
   
   
   
    dbms_output.put_line (l_doctype || ' ' || l_title || '  for ' || l_category);


l_soap_request :=
'<soapenv:Envelope xmlns:neg="http://xmlns.oracle.com/apps/prc/pon/negotiations/negotiationsServiceV2/" xmlns:neg1="http://xmlns.oracle.com/apps/flex/prc/pon/commonPon/negLine/" xmlns:neg2="http://xmlns.oracle.com/apps/flex/prc/pon/commonPon/negHeader/" xmlns:neg3="http://xmlns.oracle.com/apps/flex/prc/pon/commonPon/negHeaderExt/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/prc/pon/negotiations/negotiationsServiceV2/types/">
   <soapenv:Body>
      <typ:initializeNegotiation>
         <typ:negotiationEntry>
            <neg:AllowOtherResponseCurrencyFlag>0</neg:AllowOtherResponseCurrencyFlag>
            <neg:NegotiationTitle>' || l_title || '</neg:NegotiationTitle>
            <neg:ResponseVisibility>Blind</neg:ResponseVisibility>
            <neg:CloseDate>' || to_char (l_approved_date+15,'YYYY-MM-DD') || 'T00:00:00.000000000</neg:CloseDate>
            <neg:OpenImmediatelyFlag>1</neg:OpenImmediatelyFlag>
            <neg:CurrencyCode>'|| l_currency || '</neg:CurrencyCode>
            <neg:DisplayBestPriceBlindFlag>1</neg:DisplayBestPriceBlindFlag>
            <neg:Doctype>' || l_doctype || '</neg:Doctype>
            <neg:FOB>Origin</neg:FOB>
            <neg:FreightTerms>Buyer pays freight</neg:FreightTerms>
            <neg:DisplayRequirementScoresFlag>1</neg:DisplayRequirementScoresFlag>
            <neg:EnableRequirementWeightsFlag>1</neg:EnableRequirementWeightsFlag>
            <neg:DefaultMaximumRequirementScore>5</neg:DefaultMaximumRequirementScore>
            <neg:NegotiationLayoutName>' || l_layout || '</neg:NegotiationLayoutName>
            <neg:PaymentTerms>Net 30</neg:PaymentTerms>
            <neg:PersonId>300000047340498</neg:PersonId>
            <neg:Outcome>' || l_outcome || '</neg:Outcome>
            <neg:ProcurementBusinessUnit>'|| l_business_unit || '</neg:ProcurementBusinessUnit>
            <neg:ResponseLayoutName>Response Layout</neg:ResponseLayoutName>
            <neg:Style>' || l_style || '</neg:Style>
            <neg:NegotiationLines>
               <neg:Category>' || l_category || '</neg:Category>
               <neg:GroupTypeCode>LINE</neg:GroupTypeCode>
               <neg:ItemDescription>' || nvl (p_item_description,'Test description') || '</neg:ItemDescription>
               <neg:LineNumber>' || nvl (p_line_num,1) || '</neg:LineNumber>
               <neg:LineTypeId>' || nvl(p_line_type_id,1) || '</neg:LineTypeId>
               <neg:Quantity unitCode="' || NVL (l_uom_code,'zzu') || '">'|| p_quantity || '</neg:Quantity>
               <neg:UOMCode>'|| NVL (l_uom_code,'zzu')  || '</neg:UOMCode>
               <neg:CurrentPrice>' || nvl (p_unit_price,100) || '</neg:CurrentPrice>
               <neg:TargetPrice>' || to_char (nvl (p_unit_price,100) * 0.7) || '</neg:TargetPrice>
               <neg:ResponseStartPrice>' || nvl (p_unit_price,100) || '</neg:ResponseStartPrice>
            </neg:NegotiationLines>' || Chr(13);
       
   
        <<Sections>>
        FOR S IN C_Requirement_Sections
        LOOP
       
              l_soap_request := l_soap_request ||
                S.Requirement_Section;

       
            <<Requirements>>
            FOR R IN C_Requirements (cp_section => S.Section_Name)
            LOOP
           
              l_soap_request := l_soap_request ||
                R.Requirement;           
              l_soap_request := l_soap_request  || '  </neg:NegotiationRequirements> ' || chr(13);
            END LOOP Requirements;
           
            l_soap_request := l_soap_request || '  </neg:NegotiationSections> ' || chr(13);
           
        END LOOP Sections;
       

       
l_soap_request := l_soap_request ||            
         '</typ:negotiationEntry>
      </typ:initializeNegotiation>
   </soapenv:Body>
</soapenv:Envelope>';


    dbms_output.put_line ('Test only: ' || p_test_only);
    IF p_test_only = 'N'
    THEN
      dbms_output.put_line ('Call service and get output.');
    -------------------------------------------------------------------------------------------------------------
    -- In our case the soap request is actually not that long, but your call may have been a blob. In that case
    -- you need to chunk it and pass it to the request.
    -------------------------------------------------------------------------------------------------------------
   
    UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_soap_request));
    --dbms_lob.getlength(convert(l_soap_request, 'UTF-8'));
    UTL_HTTP.set_header(l_http_request, 'Transfer-Encoding', 'chunked');
  
    -- Set the SOAP action to be invoked; while the call works without this the value is expected to be set based on standards
    utl_http.set_header(l_http_request, 'SOAPAction', g_soap_action);
   
    END IF;
   
    -------------------------------------------------------------------------------------------------------------
    -- Write the xml payload to the request.
    -------------------------------------------------------------------------------------------------------------
    l_error_pnt := 'writing payload';
    dbms_output.put_line ('Writing payload ...');
    LOOP
      l_chunkData := NULL;
      l_chunkData := SUBSTR(l_soap_request, l_chunkStart, l_chunkLength);
     
      IF p_test_only = 'N'
      THEN
        UTL_HTTP.write_text(l_http_request, l_chunkData);
      END IF;
     
      -- For us to test later
      dbms_output.put_line (l_chunkdata);
     
      IF (LENGTH(l_chunkData) < l_chunkLength)
        THEN EXIT;
      END IF;
      l_chunkStart := l_chunkStart + l_chunkLength;
    END LOOP;
   
    IF p_test_only = 'N'
    THEN
   
    l_error_pnt := 'getting response';
    l_error_action := null;
    --  Get the response and process it
    l_http_response := UTL_HTTP.get_response(l_http_request);

    -- Create a CLOB to hold web service response
    -- Again, in our case we only get one value, but we like to keep this procedure a little generic.
    dbms_lob.createtemporary(x_clob, FALSE );
    dbms_lob.open(x_clob, dbms_lob.lob_readwrite);
   
    l_error_pnt := 'open lob for getting response';
    l_error_action := null;
   
    dbms_output.put_line ('Reading response');
 
    begin
 
      loop
        -- Copy the web service response body in a buffer string variable l_buffer
        utl_http.read_text(l_http_response, l_buffer);
       
        dbms_output.put_line (l_buffer);
 
        -- Append data from l_buffer to CLOB variable
        dbms_lob.writeappend(x_clob
                          , length(l_buffer)
                          , l_buffer);
      end loop;
     
      EXCEPTION WHEN UTL_HTTP.end_of_body
      THEN
        NULL;
        dbms_output.put_line ('Exception reading response ... ');
    END;
   
    dbms_output.put_line ('End response');
   
    l_error_pnt := 'end response';
    l_error_action := null;

    UTL_HTTP.end_response(l_http_response);
   

    -------------------------------------------------------------------------------------------------------------
    -- Now we have our clob in XML and read the values we need from the XML.
    -------------------------------------------------------------------------------------------------------------
    l_error_pnt := 'reading response';
    l_error_action := null;
    dbms_output.put_line ('Status code response is ' || l_http_response.status_code);
   
    if l_http_response.status_code IN (200) THEN
      l_resp_xml := XMLType.createXML(x_clob);
 
      SELECT  extractValue(l_resp_xml, '/env:Envelope/env:Body/ns0:initializeNegotiationResponse/result', l_ns_map) 
      INTO  l_result_nr
      FROM dual;
 
      dbms_output.put_line('Result = '||l_result_nr);
      x_result := l_result_nr;
     
     
   
    end if;
   
    dbms_lob.freetemporary(x_clob);
   
    END IF;
   
    dbms_output.put_line('End of procedure ...');

   
   
    EXCEPTION 
      WHEN Others THEN 
        l_error_code := SQLERRM;
        DBMS_OUTPUT.Put_Line ('Error: ' || l_error_pnt || ': ' || l_error_code);
        DBMS_OUTPUT.Put_line ('Action: ' || l_error_action);
        x_msg         := l_error_code;
        x_result     := null;

   
END initializeNegotiationWS;
/

Create_RFQ

This procedure fetches all necessary data from the requisition line and passes this to the webservice. Note that we execute the RFQ for each line as long as the line is RFQ required. We pass the data from the requisition header, because we want to call this from a trigger on the requisition header, so we cannot query on the requisition headers in this procedure.

Note: In our query we also look at a specific attribute on the template (now called [Attribute]). You can remove that here of course! If you run it like this it uses the outerjoins to find the requisition regardless of the specific attribute.

CREATE OR REPLACE PROCEDURE Create_RFQ
(
  p_requisition_header_id            IN NUMBER
, p_description                        IN VARCHAR2
, p_approved_date                    IN DATE
, p_test_only                        IN VARCHAR2 DEFAULT 'N'
, x_return                            OUT VARCHAR2
)
IS
 -- You can test this externally using ..
 -- Test: begin Create_RFQ (531792,'Test', sysdate,'Y'); end;

   --
   -- This procedure fetches information it needs to call the webservice like header information
   -- passed by the trigger (we pass this from the trigger because we cannot do a query on
   -- the requisition header if the trigger is firing - mutating table). We fetch line information
   -- and actually fire for each line. For the demo each Req will only have one line, but you could
   -- make an additional restriction here.
   --

    g_attribute            VARCHAR2(240)        := '[Attribute]';
   
    CURSOR C_Requisition
    (
       cp_requisition_header_id        po_requisition_headers.requisition_header_id%TYPE
    )
    IS
    SELECT     pl.requisition_line_id
    ,         pt.template_name
    ,         pta.attribute_name
    ,         pti.attribute_value
    ,       p_description description -- ph.description
    ,       p_approved_date approved_date --ph.approved_date
    ,       pl.line_num
    ,       pl.item_description
    ,       pl.currency_code currency
    ,       pl.line_type_id
    ,       pl.unit_meas_lookup_code
    ,       pl.unit_price
    ,       pl.quantity
    ,       'Computer Supplies'         Category_Segment
    ,       'RFQ'                        DocType
    ,       'US1 Business Unit'            Business_Unit
    ,       'Standard Negotiation'        RFQ_Style
    ,       'Negotiation Layout'        Layout
    ,       'Purchase Order'            Outcome
    FROM     por_templates_v             pt
    ,         por_template_attributes_v     pta
    ,         por_template_info             pti
    ,       po_requisition_lines        pl
    WHERE     pt.template_code (+)                            = pta.template_code
    AND     pta.attribute_code (+)                            = pti.attribute_code
    --AND     pl.requisition_header_id                      = ph.requisition_header_id
    AND     pti.requisition_line_id (+)                        = pl.requisition_line_id
    AND     pl.requisition_header_id                         = cp_requisition_header_id
    AND     NVL (pta.attribute_name,g_attribute)              = g_attribute
    AND     NVL (pl.RFQ_Required_Flag,'N')                     = 'Y'
    ;
   
    l_requisition     C_Requisition%ROWTYPE;

    x_result        NUMBER;
    x_msg            VARCHAR2(240);
    l_exists        NUMBER := 0;
   
BEGIN

  MO_GLOBAL.Init ('PO');

  OPEN  C_Requisition (cp_requisition_header_id => p_requisition_header_id);
  FETCH C_Requisition INTO l_requisition;
      IF C_Requisition%NOTFOUND
      THEN
          CLOSE C_Requisition;
          l_exists := 0;
      ELSE
         CLOSE C_Requisition;
         l_exists := 1;
      END IF;
 
 
  IF l_exists = 1
  THEN
 
  dbms_output.put_line ('Creating ' || l_requisition.DocType || ' for ' || l_requisition.Description || ' with style ' || l_requisition.RFQ_Style);

   initializeNegotiationWS (
     p_title              => l_requisition.Description
    ,p_currency             => l_requisition.Currency
    ,p_doctype             => l_requisition.DocType
    ,p_outcome            => l_requisition.Outcome
    ,p_business_unit      => l_requisition.Business_Unit
    ,p_style             => l_requisition.RFQ_Style
    ,p_response_currency => l_requisition.Currency
    ,p_approved_date     => l_requisition.Approved_Date
    ,p_layout             => l_requisition.Layout
    ,p_line_num             => l_requisition.Line_Num
    ,p_item_description  => l_requisition.Item_Description
    ,p_UOM_Code             => l_requisition.Unit_Meas_Lookup_Code
    ,p_line_type_id         => l_requisition.Line_Type_Id
    ,p_category             => l_requisition.Category_Segment
    ,p_unit_price        => l_requisition.unit_price
    ,p_quantity          => l_requisition.quantity
    ,p_test_only         => p_test_only
    ,x_result            => x_result
    ,x_msg               => x_msg
   );
  
   dbms_output.put_line ('Result: ' || to_char (x_result) || ' - ' || x_msg);
  
  
   --------------------------------------------------------------------------------------------------------
   -- Update requisition header with sourcing id
   --------------------------------------------------------------------------------------------------------
   IF x_result IS NOT NULL
   THEN
      
       x_return := 'Reference ' || l_requisition.DocType || ' ' || to_char (x_result);
      
       -- Do update here to copy back x_return to PO_REQUISITION_LINES_ALL for example
      
  
   END IF;
  
   END IF; -- Exists
  
END;
/

Finally you can call Create_RFQ from a trigger on PO_REQUISITION_HEADERS_ALL.

Create_RFQ (p_requisition_header_id => :New.Requisition_Header_id,p_test_only => 'N',p_description => :New.Description, p_approved_date => :New.Approved_Date,x_return => x_return);
      

Now you can use the return value to store on the requisition header.

You can restrict it to fire only on approval

WHEN (New.Approved_Date IS NOT NULL AND Old.Approved_Date IS NULL)

And you could either check here if there are any lines with RFQ_Required_Flag set to Yes or leave that to Create_RFQ.

Test from database

I created a test script to test the call in the Database rather than having to create a Purchase requisition in eBusiness Suite as well.

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
     g_attribute            VARCHAR2(240)        := '[Attribute]';
   
  CURSOR C_Last_Req
  IS
SELECT     pl.requisition_line_id
    ,   pl.requisition_header_id
    ,         pt.template_name
    ,         pta.attribute_name
    ,         pti.attribute_value
    ,       ph.description
    ,       ph.approved_date
    FROM     por_templates_v             pt
    ,         por_template_attributes_v     pta
    ,         por_template_info             pti
    ,       po_requisition_lines        pl
    ,       po_requisition_headers      ph
    WHERE     pt.template_code (+)                            = pta.template_code
    AND     pta.attribute_code (+)                            = pti.attribute_code
    AND     pti.requisition_line_id (+)                        = pl.requisition_line_id
    AND     NVL (pta.attribute_name,g_attribute)              = g_attribute
    AND     pl.requisition_header_id                        = ph.requisition_header_id
    --AND   ph.requisition_header_id                        = 12345
    ORDER BY ph.requisition_header_id DESC
  ;
 
  l_req C_Last_Req%ROWTYPE;
  x_return VARCHAR2(240);
 
BEGIN
  MO_GLOBAL.Init ('PO');
 
  OPEN  C_Last_Req;
  FETCH C_Last_Req INTO l_req;
  IF C_Last_Req%NOTFOUND
  THEN
    DBMS_OUTPUT.Put_Line ('Cannot find requisition for testing ...');
  ELSE
     DBMS_OUTPUT.Put_Line ('Requisition: ' || l_req.Requisition_Header_Id);
  END IF;
  CLOSE C_Last_Req;
 
  DBMS_OUTPUT.Put_Line ('Requisition description: ' || l_req.Description);
 
  Create_RFQ
    (
      p_requisition_header_id            => l_req.Requisition_Header_Id
    , p_description                        => NVL (l_req.Description,'Test')
    , p_approved_date                    => l_req.Approved_Date
    , p_test_only                        => 'N'
    , x_return                            => x_return
    );
   
  DBMS_OUTPUT.Put_Line ('Resultaat: ' || x_return);
END;
/