Integration with Oracle Fusion Applications Enterprise Contracts using web services

During our move at Profource from Oracle E-Business Suite R12 and Oracle Fusion R7 on-premise to Oracle Fusion ERP Cloud R10, we had the challenge to migrate projects and create Enterprise Contracts.
Although Oracle Fusion provides a way to create contracts using File Import Activities, the current functionality and scope of creating contracts was not sufficient for our line of business.

File Import Activities

With Orale Fusion R10 three File Import Mappings are seeded which you copy and modify for contract import.

As you can see you can create contract headers, including parties and contacts.
According to note 2020506.1 more contract objects will be made available for import in upcoming releases:

Starting Release 11 we support the file-based loading of contract objects, such as bill/revenue plan, associated project and billing controls.

Starting Release 13 we planned to support file-based loading of the remainder of the PJB-owned contract objects, consisting of bill rate overrides, internal contract line attributes, and the coming in R12 contract header user currency conversion attributes.

What we needed

We soon realised that using File Import Activities would not work for our line of business, because we had the task of importing the following contract objects:

Through some Oracle contacts we were pointed out to the ContractService web service. Although this web service is not discoverable in Oracle Enterprise Repository, this web service is externally exposed. Also not being present in Oracle Enterprise Repository means that this web service is not documented, and therefor some R&D on our side had to take place.

ContractService

The ContractService contains several operations that allow you to get, create, update, apply hold, remove hold, cancel contract, reopen contract (line), close contract (line), activate a contract and delete a contract.

One of the first steps I always take when integrating with web services, is to invoke the get operation of the web service to have an idea of which elements are used and what the element values should look like.

  

First thing you may have noticed, is that the web service elements all contain Fusion Id's. So when invoking the createContract operation we would first have to obtain all necessary Fusion Id's and map this to the source data. Also when objects are linked, this happens on the Fusion Id's.

Like screenshot above shows a person rate override is linked to a contract line. However when you create a contract you do not have the access to the Fusion Id of a contract line, since it does not exist yet. In order to achieve above complete contract, you will have to create a contract, obtain the created ids from the response and use these in sequential mergeContract calls to complete a contract.

Create integration

Since we where moving from Oracle E-Business Suite R12 and Oracle Fusion R7 on-premise to Oracle Fusion ERP Cloud R10, it was only logical to use the database and PL/SQL as integration scripting. Of course you can use any kind of technology aslong as it provides ability to invoke web services.

In order for an Oracle database to invoke a Fusion Applications web services you need to create a wallet with the Fusion Applications SSL certificates and create an Access Control List.

Wallet

Have your DBA create a wallet and add the Fusion Applications SSL certificates to the wallet.

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

ACL

Have your DBA create a ACL to grant access to use UTL_HTTP to access Oracle Fusion Applications.

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        => '*.crm.[datacenter].oraclecloud.com', 
    lower_port  => NULL,
    upper_port  => NULL);

END;

/

Obtain Fusion Id's

Before actually creating the integration script, we need a way to obtain the Fusion Id's, incorporate this in the integration procedure and map this to the source data.

What we did, was to create a custom datamodel using OBIEE with separate data sets for every id type and extract the result in XML format.

Once you export the data containing the id's, you can load them in a table with a XMLTYPE column.

After this you can create functions using XMLTable to retrieve the Fusion ID's based on the type. 

Invoke ContractService

Now that you have been granted the privilege to invoke the web service, you have to create a procedure to assemble a payload using the Fusion ID's, invoke the web service createContract, read the response id's and call sub sequential the mergeContract operation to complete the contract. 

The code example below have been mainly extracted from the Fusion Applications Developer Relations blog and a few other very helpful blogs, which I do remember the url of. If you do recognise your code, credits go to you! 

  procedure create_contract_ws 

    (p_contract_id in number

    ,p_req in clob)

  is 

    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_contract_id    varchar2(18);

    l_majorversion   varchar2(4);

    l_contract_number varchar2(18);

    l_billplan_id    varchar2(18);

    l_billplan_majorversion   varchar2(4);    

    l_revplan_id     varchar2(18);

    l_ns_map         varchar2(2000) ;

    x_clob             CLOB;

    l_buffer           VARCHAR2(32767);     

    l_chunkStart NUMBER := 1;

    l_chunkData VARCHAR2(32000);

    l_chunkLength NUMBER := 32000;  

    

  BEGIN

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

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

    l_ns_map := l_ns_map ||'xmlns:ns4="http://xmlns.oracle.com/apps/projects/billing/contracts/contractService/" ';

    -- Sets the Oracle wallet used for request, required for HTTPS

    UTL_HTTP.set_wallet('file:[wallet_path]', 'password');

   

    -- Creates new HTTP request

    l_http_request := UTL_HTTP.begin_request('https://[instance].oraclecloud.com/external-contractmanagement-contractsCoreTransaction/ContractService', 'POST','HTTP/1.1');

   

    -- Configure the authentication details on the request

    UTL_HTTP.SET_AUTHENTICATION(l_http_request, '[fusion_account]', '[fusion_pswd]');

   

    -- Configure the request content type to be xml and set the content length

    UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml;charset="UTF-8"');

    --load request chucked, large contracts can exceed 32K limit

    UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(p_req));

dbms_lob.getlength(convert(p_req, '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', 'http://xmlns.oracle.com/apps/contracts/coreAuthoring/transaction/transactionService/createContract');

   

    -- Write the xml payload to the request.

    LOOP

      l_chunkData := NULL;

      l_chunkData := SUBSTR(p_req, l_chunkStart, l_chunkLength);

      UTL_HTTP.write_text(l_http_request, l_chunkData);

      IF (LENGTH(l_chunkData) < l_chunkLength) 

        THEN EXIT; 

      END IF;

      l_chunkStart := l_chunkStart + l_chunkLength;

    END LOOP;

    

    --  Get the response and process it

    l_http_response := UTL_HTTP.get_response(l_http_request);

    -- Create a CLOB to hold web service response

    dbms_lob.createtemporary(x_clob, FALSE );

    dbms_lob.open(x_clob, dbms_lob.lob_readwrite);

  

    begin

  

      loop

        -- Copy the web service response body in a buffer string variable l_buffer

        utl_http.read_text(l_http_response, l_buffer);

  

        -- Append data from l_buffer to CLOB variable

        dbms_lob.writeappend(x_clob

                          , length(l_buffer)

                          , l_buffer);

      end loop;

  

    end;

    UTL_HTTP.end_response(l_http_response);

      

    if l_http_response.status_code = 200 THEN

      l_resp_xml := XMLType.createXML(x_clob);

  

      SELECT  extractValue(l_resp_xml, '/env:Envelope/env:Body/ns0:createContractResponse/ns2:result/ns1:Value/ns1:Id', l_ns_map)

             ,extractValue(l_resp_xml, '/env:Envelope/env:Body/ns0:createContractResponse/ns2:result/ns1:Value/ns1:MajorVersion', l_ns_map)

             ,extractValue(l_resp_xml, '/env:Envelope/env:Body/ns0:createContractResponse/ns2:result/ns1:Value/ns1:ContractNumber', l_ns_map)

             ,extractValue(l_resp_xml, '/env:Envelope/env:Body/ns0:createContractResponse/ns2:result/ns1:Value/ns1:BillPlan/ns4:BillPlanId', l_ns_map)

             ,extractValue(l_resp_xml, '/env:Envelope/env:Body/ns0:createContractResponse/ns2:result/ns1:Value/ns1:BillPlan/ns4:MajorVersion', l_ns_map)

             ,extractValue(l_resp_xml, '/env:Envelope/env:Body/ns0:createContractResponse/ns2:result/ns1:Value/ns1:RevenuePlan/ns4:BillPlanId', l_ns_map)

      INTO  l_contract_id 

           ,l_majorversion

           ,l_contract_number

           ,l_billplan_id

           ,l_billplan_majorversion

           ,l_revplan_id

      FROM dual;

  

      dbms_output.put_line('l_contract_id = '||l_contract_id);

      dbms_output.put_line('l_majorversion = '||l_majorversion);

      dbms_output.put_line('l_contract_number = '||l_contract_number);

      dbms_output.put_line('l_billplan_id = '||l_billplan_id);

      dbms_output.put_line('l_revplan_id = '||l_revplan_id);

      

      commit;

      

      /* loop through contractlines and assign billplan and revplan to contractline */

      FOR R IN (SELECT EXTRACTVALUE(VALUE(P), '/ns1:ContractLine/ns1:Id/text()',l_ns_map) AS ID

                  ,  EXTRACTVALUE(VALUE(P), '/ns1:ContractLine/ns1:MajorVersion/text()',l_ns_map) AS MajorVersion

                  ,  EXTRACTVALUE(VALUE(P), '/ns1:ContractLine/ns1:LineNumber/text()',l_ns_map) as LineNumber

      FROM TABLE(XMLSEQUENCE(EXTRACT(l_resp_xml, '//env:Envelope/env:Body/ns0:createContractResponse/ns2:result/ns1:Value/ns1:ContractLine',l_ns_map))) P)

      LOOP

        update_contract_ws (

                             p_contract_id                  => l_contract_id

                            ,p_contract_majorversion        => l_majorversion

                            ,p_contract_line_id             => r.Id

                            ,p_contract_line_majorversion   => r.MajorVersion

                            ,p_billplan_id                  => l_billplan_id

                            ,p_revplan_id                   => l_revplan_id

                           );

      end loop;

    end if;

    

    dbms_lob.freetemporary(x_clob);

    

  END create_contract_ws;

In a similar way you can invoke the mergeContract operation to add additional objects or to link id's that have been created in previous calls.

Can we add Contract Documents using web services?

Yes, you can!

Using the Activity web service, you can add digital files as contract documents. As with all attachments that reside in web services, all you have to do, is to decode the file (fileserver/blob) to base64 and add it to the payload.

All attachments will be uploaded to Webcenter Content and a reference to the DocumentId will we created in table FND_DOCUMENTS and FND_ATTACHED_DOCUMENTS.