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.
