On premise to Cloud integration with Oracle Enterprise Contracts

During several conversion projects towards Oracle Enterprise Contracts we have used the method of integration through the database using UTL_HTTP.
The same method can be used for a full integration between for example Oracle eBusiness Suite on premise and Oracle Contracts Cloud. This article describes the contracts integration we've setup for several of our customers to integrate between Oracle eBusiness Suite on premise and Oracle Cloud Contracts. Note that you could also use any other (Oracle) database to implement the same, but in most of our cases we've used eBS. I hope this article will help to get you started on your own integration with Cloud Contracts as well!

The method consists of a number of webservices we call in a specific order to upload the contract, activate the contract and upload documents (attachments), like the signed contract.

To find information on the webservice itself you check Fusion Enterprise Repository (OER)
http://www.oracle.com/webfolder/technetwork/docs/HTML/oer-redirect.html
You should check under the sales section
https://docs.oracle.com/cloud/farel12/salescs_gs/OESWS/toc.htm
for the Contracts Service
https://docs.oracle.com/cloud/farel12/salescs_gs/OESWS/Contract_Service_ContractService_svc_9.htm#oracle.apps.contracts.coreAuthoring.transaction.transactionService.ContractService

The URL for your WSDL is
https://(CRMDomain,Contract Management)/external-contractmanagement-contractsCoreTransaction/ContractService?WSDL

Something we encode as generic parameters in our service

 g_fs_user           varchar2(200)     default '...;
  g_fs_pswd           varchar2(200)     default '...';
  g_activity_ws       varchar2(200)     default 'https://....oraclecloud.com:443/appCmmnCompActivitiesActivityManagement/ActivityService';
  g_contract_ws_r10   varchar2(200)     default 'https://.../.oraclecloud.com/external-contractmanagement-contractsCoreTransaction/ContractService';

Usually we use lookups in eBS for this purpose. The lookup code in that case is the name of the environment, the description is the URL to the service. This way, post clone, the values will still be correct.

Generic method

In general what we did is
a) Create a global variable that contains the payload for the webservice with replaceable tags
b) Loop through the transactions you like to process
c) Transform, validate the data into what is required
d) Replace the tag in the payload with your value
e) Call the webservice

So for example the contract creation itself is a variable like

  g_contract_header_start varchar2(4000) default '' ||
    '     <typ:contractHeader>
            <tran:OrgId>[OrgId]</tran:OrgId>
            <tran:ContractTypeId>[ContractTypeId]</tran:ContractTypeId>
            <tran:ContractNumber>[ContractNumber]</tran:ContractNumber>
            <tran:StartDate>[StartDate]</tran:StartDate>
            <tran:EndDate>[EndDate]</tran:EndDate>
            <tran:BuyOrSell>[BuyOrSell]</tran:BuyOrSell>
            <tran:CurrencyCode>[CurrencyCode]</tran:CurrencyCode>
            <tran:Cognomen>[Cognomen]</tran:Cognomen>
            <tran:Description>[Description]</tran:Description>
            <tran:LegalEntityId>[LegalEntityId]</tran:LegalEntityId>
            <tran:StsCode>DRAFT</tran:StsCode>
            <tran:WebServiceFlag>true</tran:WebServiceFlag>
            <tran:AgreementEnabledFlag>true</tran:AgreementEnabledFlag>
            <tran:EstimatedAmount currencyCode="[CurrencyCode]">[AgreedAmount]</tran:EstimatedAmount>
            <tran:VersionDescription>[VersionDescription]</tran:VersionDescription>
            '; 

During creation we loop through the contracts we need to create and fetch the necessary values. Now you may notice we need the contract type id for example. This is a value that exists in the cloud environment and not in the eBS environment. So how do we get that?

Get Translation Data

In order to get "translation data" like that, we create a datamodel in the BI environment of cloud that provides us with all necessary internal values and setup.
So this queries the legal entities that have been setup, the contract types, etc. We download this into an XML file and upload it to a table to use for conversions.

To upload the file we place the XML file on the server and upload it to a conversion table using SQL Loader

LOAD DATA
INFILE 'content.dat'
  INTO TABLE xxconv_test
  FIELDS TERMINATED BY '#'
  (
    fname   filler char(80),
    c       LOBFILE(fname CHARACTERSET UTF8) TERMINATED BY EOF
  )

Then we convert the clob into XML and put it in our translation table. We've used the same method for several clients, hence we also use the client to see for which client this transformation was used.


DECLARE
  l_xml XMLTYPE;
  l_clob CLOB;
  
BEGIN
DELETE FROM XXCONV_OKC_XML_ALL WHERE Client = 'CLIENT'; COMMIT;
SELECT C INTO l_clob FROM XXCONV_TEST;
l_xml := xmltype.createxml (l_clob);
  INSERT INTO XXCONV_OKC_XML_ALL (Ids,Client) VALUES (l_xml,'CLIENT');
  COMMIT;
END;
/

Now we have all our data in a table we can use queries like this. This shows all valuesets we have used for the flexfields for example, since we also want to validate the values in the flexfields before uploading.

select flex_value
, description
    from 
    (
      SELECT Flex_Value_Set_Name, Flex_Value,Description
      FROM xxconv_okc_xml_all t
         , XMLTable('/DATA_DS/DFF_VALUESETS'
             passing t.IDS
             columns 
               FLEX_VALUE_SET_NAME       varchar2(240)     path 'FLEX_VALUE_SET_NAME'
             , FLEX_VALUE       varchar2(240)  path 'FLEX_VALUE'
             , DESCRIPTION    varchar2(240)  path 'DESCRIPTION'
           )
           WHERE t.client = G_CONV_CLIENT
    ) pt
    where pt.flex_value_set_name = 'Your_Valueset'
    ;

Step 1: Creating the contract

Now all the pieces are in place we can start creating contracts. So we loop through our transactions and for each we transform, validate the data. The main idea is shown below. You get the internal values and replace the tags in the generic header.

      l_bu := get_bu(...);
      l_contract_header := g_contract_header_start;
      l_contract_header := replace(l_contract_header, '[OrgId]',  l_bu);


To get the business unit we use a query on our transformation data, for example

select bu.ORGANIZATION_ID
    , bu.DEFAULT_LEGAL_CONTEXT_ID
    into l_bu_id
    , x_le_id
    from 
    (
      SELECT ORGANIZATION_ID
      ,      NAME
      ,      DEFAULT_LEGAL_CONTEXT_ID
      FROM xxconv_okc_xml_all t
         , XMLTable('/DATA_DS/BU'
             passing t.IDS
             columns 
               ORGANIZATION_ID    number(18)     path 'ORGANIZATION_ID'
             , NAME               varchar2(240)  path 'NAME'
             , DEFAULT_LEGAL_CONTEXT_ID number(18) path 'DEFAULT_LEGAL_CONTEXT_ID'
           )
    where t.client = G_CONV_CLIENT
    ) bu
    where upper(bu.name) like upper(l_bu_name || '%');

Depending on whether this is a BUY or SELL contract (which can be found in the setup of the contract type which we downloaded in our pre-liminary step), we also add suppliers or customers and their contacts. Or even other sub-parties can be added.

We also add the roles on the contract, like contractmanager, owner, buyer, etc with  their access level (* Note that during our conversion the access role READ did not seem to work).
A contract party could be something like

  g_contract_party_sell varchar2(2000) default ''||          
' <tran:ContractPartyContact> 
                   <tran:ContactRoleCode>CONTRACT_ADMIN</tran:ContactRoleCode> 
                   <tran:ContactId>[ContactId]</tran:ContactId> 
                   <tran:OwnerFlag>[OWNERFLAG]</tran:OwnerFlag> 
                   <tran:AccessLevel>[ACCESSLEVEL]</tran:AccessLevel> 
                 </tran:ContractPartyContact> ';

with its own tags to be replaced. Note that we downloaded the suppliers also first before we migrate the data.

Flexfields have a complexity of their own. Especially context dependent flexfields. So depending on the context (usually determine by the contract type), we add a generic flexfield structure and replace the tags. But we always validate the fields in the flexfields before uploading them.

For example a flexfield with the name of the legal rep.

l_context_iden :=   validate_dff (p_dff=> 'SG_LEGALREP',p_value=> r_cur.legal_rep,p_dff_desc=> 'Legal Rep);
l_contract_header_dff := replace(l_contract_header_dff, '[LegalRep]',l_context_iden);

Once we've build up our payload we call the actual webservice

We set the mapping

    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/contracts/coreAuthoring/contractService/types/" ';    
    l_ns_map := l_ns_map ||'xmlns:ns2="http://xmlns.oracle.com/apps/contracts/coreAuthoring/contractService/" ';
    l_ns_map := l_ns_map ||'xmlns:ns1="http://xmlns.oracle.com/apps/contracts/coreAuthoring/contractService/types/" ';


Set the wallet

UTL_HTTP.set_wallet(g_ora_wallet, g_ora_wallet_pwsd);

I've described wallets before in
http://pamkoertshuis.blogspot.nl/search/label/Wallet

Then the header and authentication

l_http_request := UTL_HTTP.begin_request(g_contract_ws, 'POST','HTTP/1.1');
UTL_HTTP.SET_AUTHENTICATION(l_http_request, g_fs_user, g_fs_pswd);

We configure the header

    UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml;charset="UTF-8"');
    UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(p_req));
    UTL_HTTP.set_header(l_http_request, 'Transfer-Encoding', 'chunked');
    UTL_HTTP.set_header(l_http_request, 'SOAPAction', 'http://xmlns.oracle.com/apps/contracts/coreAuthoring/contractService/createContract');
 
Then we write the data in chunks

UTL_HTTP.write_text(l_http_request, l_chunkData);

And perform the call

 l_http_response := UTL_HTTP.get_response(l_http_request);

To read back the response we also use a temporary lob.

 dbms_lob.createtemporary(x_clob, FALSE );
    dbms_lob.open( x_clob, dbms_lob.lob_readwrite );
  
   l_info := 'read text';
    begin
      loop
        utl_http.read_text(l_http_response, l_buffer);
        dbms_lob.writeappend(x_clob
                          , length(l_buffer)
                          , l_buffer);
      end loop;

End the response

UTL_HTTP.end_response(l_http_response);

On errors we can subtract the fault string

l_resp_xml := XMLType.createXML(x_clob);
  
      SELECT  extractValue(l_resp_xml, '/env:Envelope/env:Body/env:Fault/faultstring', l_ns_map)
      INTO    l_fault_string 
      FROM    dual;

And finally we save the response in our progress table for reporting purposes.


Step 2: Activating the contract

The second step is to activate the contract IF it should be activated of course (usually depending on start and end date). We do the same stuff as for creating the contract, but just a different operation.

 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/contracts/coreAuthoring/transaction/transactionService/types/" ';
    l_ns_map := l_ns_map ||'xmlns:ns2="http://xmlns.oracle.com/apps/contracts/coreAuthoring/transaction/transactionService/types/" ';
    l_ns_map := l_ns_map ||'xmlns:ns1="http://xmlns.oracle.com/apps/contracts/coreAuthoring/transaction/transactionService/" ';

And operation

UTL_HTTP.set_header(l_http_request, 'SOAPAction', 'http://xmlns.oracle.com/apps/contracts/coreAuthoring/contractService/updateContractToActive');

   

Step 3: Uploading documents 

The documents may be more complex, depending on where the documents reside. If they are on the server we need to load them into BLOBs before sending. To do that we need to create a directory in DBA_DIRECTORIES to read it.

An attachment payload looks something like this

l_req := '
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/crmCommon/activities/activityManagementService/types/" xmlns:obj="http://xmlns.oracle.com/apps/crmCommon/objects/objectsService/">
   <soapenv:Header/>
   <soapenv:Body>
      <typ:createAttachment>
         <typ:attachmentRows>
            <obj:EntityName>OKC_CONTRACT_DOCS</obj:EntityName>
            <obj:Pk1Value>[ContractId]</obj:Pk1Value>
            <obj:Pk2Value>[ECM_BUY]</obj:Pk2Value>
            <obj:Pk3Value>[MajorVersion]</obj:Pk3Value>
            <obj:DatatypeCode>FILE</obj:DatatypeCode>
            <obj:FileName>[Filename]</obj:FileName>
            <obj:Description>[Description]</obj:Description>
            <obj:Title>[Title]</obj:Title>
            <obj:UsageType>S</obj:UsageType>
         <obj:DownloadStatus>N</obj:DownloadStatus>
            <obj:CategoryName>OKC_DOCUMENTS_SUPPORTING_DOC</obj:CategoryName>
            <obj:UploadedFileName>[UploadedFileName]</obj:UploadedFileName>
            <obj:UploadedFile>[UploadedFile]</obj:UploadedFile>
         </typ:attachmentRows>
         <typ:commitData>Y</typ:commitData>
      </typ:createAttachment>
   </soapenv:Body>
</soapenv:Envelope>';

Depending on whether it's a buy or sell contract we replace ECM_BUY with ECM_BUY or ECM_SELL.

We get the file from the file server, escape all XML in the filename, description etc. And then open the wallet and do our call again.

 UTL_HTTP.set_header(l_http_request, 'SOAPAction', 'http://xmlns.oracle.com/apps/crmCommon/activities/activityManagementService/createAttachment');
       
To get the file from the server we use something like this. So each file is encoded into base64 before sending.

procedure get_file
  ( p_dir  IN VARCHAR2
  , p_file IN VARCHAR2
  , p_clob IN OUT NOCOPY CLOB
  , x_skip out varchar2
  )
  is
    l_bfile BFILE;
    l_step  PLS_INTEGER := 12000;
  BEGIN
    l_bfile := BFILENAME(p_dir, p_file);
  
    DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
    if dbms_lob.getlength( l_bfile ) > 0
    then
      FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(l_bfile) - 1 )/l_step) LOOP
        p_clob := p_clob || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(l_bfile, l_step, i * l_step + 1)));
      END LOOP;
      x_skip := 'N';
    else
      x_skip := 'Y';
    end if;
  
    DBMS_LOB.fileclose(l_bfile);
  
  exception
    when others
    then
      ...
  end get_file;    


Some tips 

In the current release (11 and I also think 12) deliverables were not available in the webservice.

We also used a static transformation table, but of course it's possible that you first need to fetch data before you send up your contract information. In order to do that you would have to call a reporting webservice first, fetch the XML data, place it in your transformation table and then upload your contract.
Depending on how often the data changes this may or may not work. For example, downloading all suppliers/customers before uploading a contract is not what you want to do right? So you make sure this is synchronized in an earlier stage and you focus on the contract itself.

Integration OPA Cloud and Oracle eBS (12.1.3) iProcurement - part 2

We described earlier some of the initial operations we need to do to integrate OPA with Oracle eBS. Below you see an overview of the rest of the operations. So we have a model for OPA, which we create in the model designer. A model needs to be deployed to be usuable as runtime.
In the model we invoke the GetMetaData to get the data structure and the valuesets we like to use in our interview.

So each time something changes in your data structure or valuesets, you need to do a GetMetaData in the model and deploy the model to the runtime version.

During runtime you have two options
1. Start
2. Resume
These options use their own URLs to start the interview and also invoke other services. The start invokes the LoadRequest, where the resume invokes the GetCheckPoint to resume an existing interview from the point you saved.

LoadRequest
The load request loads initial data from your datamodel, which can be used as input parameters for the interview. When you called the interview we can pass an initial parameter in the URL as well. We used that to indicate the user, responsibility and a unique ID to identify the record in our table. Of course you want to encode those parameters, so we used DBMS_OBFUSCATION_TOOLKIT.Desencrypt to encrypt these parameters into one connection string.
When you then get the LoadRequest, it passes back your initial parameter so you can decrypt it and identify the user, authorization and the record we are creating/updating.

First thing we do then is a fnd_global.apps_initialize, because the user calling our webservice through the Integrated SOA Gateway is a generic user.

Our LoadRequest procedure looks something like

procedure LoadRequest
(
  root                 IN  VARCHAR2
, region               IN  VARCHAR2
, language             IN  VARCHAR2
, timezone             IN  VARCHAR2
, request_context      IN  xxgr_opa_t_RequestContext
, seedDataDescription  IN  xxgr_opa_t_Tables
, loadData             OUT xxgr_opa_t_LoadData
, error_msg            OUT VARCHAR2
, Status               OUT varchar2
)

Here the seedDataDescription contains a list of entities and fields the interview is requesting from us. So you have to check which specific fields the service wants and pass values for those back. You cannot pass more values (service will fail).

We also use a translation for booleans, because Y/N values in the database should be translated to true/false values for the service and return type boolean, where we cannot use booleans in our data structures directly.

What we actually did is draw a sequence number and pass that as parameter on the interview. As soon as we got the LoadRequest back we created our record in the database with that sequence (because we were creating entities using the interview and until we get some feedback back from the interview we do not actually need the record).
During SetCheckpoint and SaveRequest we continue processing our record.

Information we pass on the load is stuff like the name of the user that called the interview, his organization, etc.

Drawback is that we can return an error to the webservice, but OPA cannot handle that. It will not show the error message we send back (just a generic error).


SetCheckPoint
During your interview you can save the data so you can resume later. Those are called checkpoints. The hub call call the SetCheckpoint to save a base64 encoded zip file of the interview. That zip file contains an XML containing variables entered in the interview; so if you want to you could unzip and decode the information and actually store structured data in between save points.

procedure SetCheckpoint
(
  request_context   IN xxgr_opa_t_RequestContext
, checkPointData    IN xxgr_opa_r_CheckPoint_Data
, checkpointId      OUT varchar2
, error_msg         OUT varchar2
, Status                 OUT varchar2
)

So setting a checkpoint is merely saving the blob data given our context (request_context). That contains again the parameter(s) we passed to the initial URL, which is our encrypted key with user information.

In our specific case we would create purchase requisitions from our interviews, so this was the moment we actually created the requisition and related our interview (using a custom table) to it.


SaveRequest
When you are done with your interview you can submit the data using a save request operation.


procedure SaveRequest
(
  root              IN  VARCHAR2
, region            IN  VARCHAR2
, language          IN  VARCHAR2
, timezone          IN  VARCHAR2
, request_context   IN  xxgr_opa_t_RequestContext
, submitData        IN  xxgr_opa_t_submit_data
, attachments       IN  xxgr_opa_t_attachments
, auditReport       IN  xxgr_opa_t_audit_report_list
, updateData        OUT xxgr_opa_t_UpdateData
, error_msg         OUT VARCHAR2
, Status            OUT varchar2
)

This is the most complex operation, because now we get all the structured data in the submitData including attachments and auditreports.

There are some restrictions on attachments in the service. You can restrict the size of files in OPA, but the SOA Gateway also may have its own restrictions. We also had a service bus in between with memory restrictions, so we had a limit of max 40MB on files. But note that on each setcheckpoint it would send any attachments in the base64 encoded zip that you already uploaded. So it's good practice to add your attachments as late as possible in the interview to avoid a lot of data traffic.

The submitData contains two parts
- The input fields
- Request for output fields

The latter is a request after the submit (which you can use in the OPA model) to pass back some information. So we capture the fields that are requested to pass them back later after we are done (for example to pass a requisition number).

 <<OutputFields>>
        FOR i in 1 .. submitData(l_det_ind).submitRow(1).outputfield.count 
        LOOP  
          L_Outputs (submitData(l_det_ind).submitRow(1).outputfield(i).name) := submitData(l_det_ind).submitRow(1).outputfield(i).name;
        END LOOP OutputFields;

Then we loop through all the input fields, validate the input and store the data. Of course we could store the names as indexes in a table, but we still need to identify per field what we want to do with it.

 <<InputFields>>
      FOR i in 1 .. submitData(l_det_ind).submitRow(1).inputfield.count
      LOOP

               IF   submitData(l_det_ind).submitRow(1).inputfield(i).name = 'DESCRIPTION' 

               AND  submitData(l_det_ind).submitRow(1).rowAction = 'update'

               THEN

                  l_description := submitData(l_det_ind).submitRow(1).inputfield(i).data_val;

               END IF;

Note that we can have multiple entities (l_det_ind), with multiple rows and multiple fields. In our specific case we only had one main record, but multiple sub records. So we used submitRow (1) here, but otherwise we would have used  loop.

Attachments

Now we get 2 types of attachments. You get the main report IN the data and a separate attachments parameter.

submitData(l_det_ind).submitRow(1).attachments

contains the attachments on our main entity.

So finally we validate our input, update our record and return a message back. This message can be shown, if you use the LoadAfterSubmit option in OPA. It loads information you can give back, so we can show error and warning messages if necessary.

In our current release you can only submit once. After that you need to close the interview and re-open it to make changes.


GetCheckPoint

Then finally the get check point operation, which is used if you resume an interview. We simply read the base 64 encoded string (zip file) and pass that back to the webservice.


procedure GetCheckpoint
(
  request_context   IN xxgr_opa_t_RequestContext
, checkPointData    OUT xxgr_opa_r_CheckPoint_Data
, error_msg         OUT varchar2
, Status                 OUT varchar2
)

So this is pretty straightforward. The only thing we also do, on all operations, is check whether the user is allowed to do this. For example you cannot do a Load if you aren't logged in right now. And you cannot submit if you were not logged in today.

Integration OPA Cloud and Oracle eBS (12.1.3) iProcurement - part 1

In my current project we've created a custom integration between Oracle iProcurement (12.1.3) and Oracle Policy Automation in the cloud (release 12.2.5). I like to share some lessons learned from this project and help along others who might need to build an integration between eBS and OPA Cloud.

First you need to understand the basic integration between OPA cloud and its environment. You call the cloud environment using an URL with some parameters. Then OPA cloud performs calls to your environment using the connection framework you have to develop based on predefined WSDLs OPA is prescribing.

Your connection framework needs to be able to receive SOAP calls and answer appropriately. Since we want to connect to Oracle eBS and we started out with creating APIs in the eBS environment (PL/SQL packages) which could be exposed to OPA.

To expose them we could have build BPEL processes using SOA Suite calling our API's directly of course, but since we could not use SOA Suite (domain restrictions), we've used the Integrated SOA Gateway in eBS.
Unfortunately the SOAP responses generated by Integrated SOA Gateway were not exactly what OPA Cloud expects and you are very limited in steering the respons created by the SOA Gateway because the WSDL is automatically generated based on your PL/SQL packages. So therefore we needed a translation between the SOA Gateway and OPA, the ESB.

Integrated SOA Gateway
Now there are some points which are important when you use the Integrated SOA Gateway here .. First, the input and output parameters to the procedures are complex datatypes, which all kinds of nested tables of records of tables of records. You cannot define these as types WITHIN your PL/SQL package (otherwise you can't get you package deployed as a webservice), so you have to make object types like

create
type xxx_opa_t_metatable is object
( name                     varchar2(80)
, can_be_input               varchar2(10)
, can_be_output               varchar2(10)
, description               varchar2(240)
, accepts_attachments         varchar2(10)
, table_fields               xxx_opa_t_MetaTableFields
, table_links               xxx_opa_t_MetaTableLinks
);

as separate types in the database.
For each service you can find the input and output parameters described in the OPA Documentation: http://documentation.custhelp.com/euf/assets/devdocs/august2016/PolicyAutomation/en/Default.htm#Guides/Developer_Guide/Connector_Framework/Expose_application_metadata.htm%3FTocPath%3DDeveloper%2520Guide%7CConnector%2520framework%7C_____2

Our package header is stored in a pls file with the following annotations

create or replace package                xxx_opa_wsep_pkg as
/* $header: apps.apps.apps.xxx_opa_wsep_pkg $ */
/*#
* ebs opa webservice connector
* @rep:scope public
* @rep:product XXX
* @rep:lifecycle active
* @rep:displayname eBS OPA Webservice Connector EBS Endpoint
* @rep:compatibility S
* @rep:category BUSINESS_ENTITY XXX_OPA_WSEP_PKG
*/

Including functions for each of the operations
CheckAlive
GetMetaData
LoadRequest
SaveRequest
SetCheckpoint
GetCheckpoint

which we will describe in more detail later.

CheckAlive
The checkalive function is used in the OPA Cloud environment to check if there is a valid connection. It sends back a very simple SOAP response

<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
    <S:Body>
        <check-alive-response xmlns="http://xmlns.oracle.com/policyautomation/hub/12.2.2/metadata/types"/>
    </S:Body>
</S:Envelope>

To give you an idea on the required translation when using Integrated SOA Gateway, this is what our CheckAlive function returns by default

<env:Envelope
  xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
 <env:Header/>
 <env:Body>
  <OutputParameters
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns="http://xmlns.oracle.com/apps/xxgr/soaprovider/plsql/xxgr_opa_wsep_pkg/checkalive/">
   <ERROR_MSG
     xsi:nil="true"/>
   <STATUS>S</STATUS>
  </OutputParameters>
 </env:Body>
</env:Envelope>

So this needs to be translated into the response required by OPA.
If this service works, you can see the connection is green in the OPA Hub.

In our case we did not directly communicate with the service bus, but we had to pass a load balancer, an open tunnel, some firewalls and then we reach the service bus, which connects through some firewalls with the Oracle eBS environment.

GetMetaData
The GetMetaData service exposes the datamodel from eBS that can be used in OPA to map fields. OPA is aware of a lot more types than Oracle including currency, time, etc. We cannot create object types with booleans either, so if we want to indicate that some field is a boolean we define it as text (which may hold values like true/false) and we have to tell OPA this field is a boolean.

The metadata also contains any list of values you want to use in OPA. Note that OPA should not be used as a form to enter values (like select a supplier, select an employee), but you can use select lists for smaller lists to choose an answer from.

In our example we added list of values for line types, unit of measure and item categories, where we made dependent valuesets between the segments.
Important to understand in dependent valuesets is that the the main valueset contains all its children. So if you have one segment Animals including Mammal, Hoofed animal, etc and Hoofed animal includes Cow, Horse, etc, you would send list one Animals with child Hoofed animal and all its children and then a second list of Hoofed animals (and a third with mammals), etc.

For each field in your data element you specify whether it is an input or output field, whether it is required and the type. And as said, since we only have text types in Oracle for our booleans, we have to indicate this is actually a boolean to OPA so it can treat it as a boolean and pass true/false back.

Just an example of how our code was build up. We fetched all columns from a given view and passed them in our case all as input/output, but of course you could make this more complex by defining in a lookup which fields can be inputs or outputs to OPA.

<<Fields>>
  FOR F IN C_Fields (cp_table_name => 'XXX_REQUISITION_DETAILS_V')
  LOOP
       nr_of_cols := nr_of_cols + 1;
--
       lt_tablefields.extend;

       l_can_be_input := 'true';
       l_can_be_output := 'true';
       l_is_required   := F.Is_Required;

--
  l_data_type := F.Data_Type;
   IF F.Column_Name = ( my list of boolean columns ) THEN l_data_type := 'boolean'; END IF;

     l_tableFields := xxx_opa_r_MetaTableFields (F.Column_Name,l_data_type,null,l_Can_Be_Input,l_Can_Be_Output,l_Is_Required,Initcap (F.Column_Name));
     lt_tablefields (nr_of_cols) := l_tablefields;
--
  END LOOP Fields;
  Metatable := xxx_opa_t_metatables();
  MetaTable.extend;
  MetaTable (1) := xxx_opa_t_metatable ('XXX_REQUISITION_DETAILS_V','true','true','Request','true',lt_tablefields,null);

Next time we will describe the Load and Saverequests in more detail and the checkpoints.

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> 

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