zondag 12 november 2017

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)
You should check under the sales section
for the Contracts Service

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:EstimatedAmount currencyCode="[CurrencyCode]">[AgreedAmount]</tran:EstimatedAmount>

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

INFILE 'content.dat'
  INTO TABLE xxconv_test
    fname   filler char(80),

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.

  l_xml XMLTYPE;
  l_clob CLOB;
l_xml := xmltype.createxml (l_clob);

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
      SELECT Flex_Value_Set_Name, Flex_Value,Description
      FROM xxconv_okc_xml_all t
         , XMLTable('/DATA_DS/DFF_VALUESETS'
             passing t.IDS
               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

    into l_bu_id
    , x_le_id
      ,      NAME
      FROM xxconv_okc_xml_all t
         , XMLTable('/DATA_DS/BU'
             passing t.IDS
               ORGANIZATION_ID    number(18)     path 'ORGANIZATION_ID'
             , NAME               varchar2(240)  path 'NAME'
    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: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

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';
        utl_http.read_text(l_http_response, l_buffer);
                          , length(l_buffer)
                          , l_buffer);
      end loop;

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

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
  , x_skip out varchar2
    l_bfile BFILE;
    l_step  PLS_INTEGER := 12000;
    l_bfile := BFILENAME(p_dir, p_file);
    DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
    if dbms_lob.getlength( l_bfile ) > 0
      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';
      x_skip := 'Y';
    end if;
    when others
  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.