Rupatil's Oracle Apps Technical Hub: November 2019

Calling Fusion Soap Web Service in PL/SQL

Step 1 : Get Soap web service wsdl

Find the SOAP web service url i.e.wsdl from Oracle Enterprise Repository (OER). Below are the steps given to get the soap web service wsdl.

Go to OER and click on ‘Cloud Applications’

Select product offering. Here we are looking for journal import soap web service. Hence we have selected Financials product offering.

Select the cloud applications release from given dropdown. Here we selected release 19D. Click on ‘Integrate’

Click on ‘Review SOAP web services’ under Discover Web Services section.

Click on ‘Business Object Services’

Search for business object ‘Journal’ and click on Journal Interface

We get the soap web service wsdl here to import journal into interface table of oracle fusion application.

Modify the above Service WSDL URL to include the URL path of your oracle cloud/fusion instance. Here we are using fusion demo instance provided by Oracle.

Original WSDL:

https://(FinancialDomain,Ledger)/finGlDesktopEntry/JournalImportService?WSDL

After Modification:

https://ucf6-fap1522-fa-ext.oracledemos.com/finGlDesktopEntry/JournalImportService?WSDL

Step 2: Create SOAP Project

We need SoapUI tool installed. You can get it on google for free since it is a opensource Soap Client. Create new project in SoapUI.

Enter the project name. Provide the modified WSDL url and click on OK.

Find mandatory attributes for importing journal and create a request payload. Sample request payload is given below (Only mandatory fields are included in the request payload) -

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/generalLedger/journals/desktopEntry/journalImportService/types/" xmlns:jour="http://xmlns.oracle.com/apps/financials/generalLedger/journals/desktopEntry/journalImportService/" xmlns:acc="http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/publicFlex/Account/">

<soapenv:Header/>

<soapenv:Body>

<typ:importJournals>

<typ:interfaceRows>

<jour:BatchName>RP_TestBatch</jour:BatchName>

<jour:BatchDescription>RP_TestBatch</jour:BatchDescription>

<jour:LedgerId>300000046975971</jour:LedgerId>

<jour:AccountingPeriodName>07-17</jour:AccountingPeriodName>

<jour:AccountingDate>2017-07-14</jour:AccountingDate>

<jour:UserSourceName>Manual</jour:UserSourceName>

<jour:UserCategoryName>Manual</jour:UserCategoryName>

<jour:ErrorToSuspenseFlag>True</jour:ErrorToSuspenseFlag>

<jour:SummaryFlag>True</jour:SummaryFlag>

<jour:ImportDescriptiveFlexField>N</jour:ImportDescriptiveFlexField>

<jour:GlInterface>

<jour:LedgerId>300000046975971</jour:LedgerId>

<jour:PeriodName>Jul-17</jour:PeriodName>

<jour:AccountingDate>2017-07-14</jour:AccountingDate>

<jour:UserJeSourceName>Manual</jour:UserJeSourceName>

<jour:UserJeCategoryName>Manual</jour:UserJeCategoryName>

<jour:GroupId></jour:GroupId>

<jour:ChartOfAccountsId></jour:ChartOfAccountsId>

<jour:BalanceType>A</jour:BalanceType>

<jour:CodeCombinationId></jour:CodeCombinationId>

<jour:Segment1>101</jour:Segment1>

<jour:Segment2>10</jour:Segment2>

<jour:Segment3>11504</jour:Segment3>

<jour:Segment4>000</jour:Segment4>

<jour:Segment5>000</jour:Segment5>

<jour:Segment6>000</jour:Segment6>

<jour:CurrencyCode>USD</jour:CurrencyCode>

<jour:EnteredCrAmount currencyCode="USD">777</jour:EnteredCrAmount>

<jour:ManagementSegmentValue>?</jour:ManagementSegmentValue>

<jour:ObjectVersionNumber>1</jour:ObjectVersionNumber>

</jour:GlInterface>

</typ:interfaceRows>

</typ:importJournals>

</soapenv:Body>

</soapenv:Envelope>

Step 3: Create ACL in oracle database

To call Soap web service from pl/sql, we first need to create ACL (Access Control List) to allow access to external netwrok.

There are two steps: First create an ACL, and add a host and port range to the ACL.

Here in our example, we need to allow access to www.oracle.com, you could use the following code block -

--Create an ACL

BEGIN

DBMS_NETWORK_ACL_ADMIN.create_acl (

acl => 'acl_oracle.xml',

description => 'Allow access to oracle.com for testing',

principal => 'SCOTT',

is_grant => TRUE,

privilege => 'connect',

start_date => SYSTIMESTAMP,

end_date => NULL);

--Assign Port

DBMS_NETWORK_ACL_ADMIN.assign_acl (

acl => 'acl_oracle.xml',

host => 'www.oracle.com',

lower_port => 80,

upper_port => NULL);

COMMIT;

END;

Step 4 : Create wallet on application server

We also need to create a secure wallet on oracle application server. For this we first need to download trusted certificates for web service. To download the certificates follow belwo steps –

1. Open the WSDL in web browser. Click on ‘lock’ icon in address bar.

2. Click on certificate.

3. Under Certification Path tab, all the certificates are listed. We need to download all these certificates.

4. Under Details tab, click on button Copy to File… and then click on Next.

5. Select file type and click on next. Click on Browse button to browse the file location where we want to save the certificate.

6. Keep filenames same as certificate names as listed in Certification Path tab.

7. Click on Save button. Click on Finish.

8. Download all other certificates in similar manner.

Step 5 : Create wallet on application server

After downloading .CER certificates for target web service, create wallet on application’s server (unix) using following commands –

First of all, place the downloaded certificates on application server at your preferred location.

i. Create Wallet (Syntax):

$ orapki wallet create -wallet <wallet directory> -auto_login

For eg :- $ orapki wallet create -wallet $ORACLE_HOME/owm/wallets/oracle -auto_login

ii. Add certificate to your wallet (Syntax):

$ orapki wallet add -wallet <wallet directory> -trusted_cert -cert <certificate file path> -pwd <wallet password>

For eg :- $ orapki wallet add -wallet $ORACLE_HOME/owm/wallets/oracle -trusted_cert -cert /home/oracle/oracledemos.cer -pwd Welcome123

Step 6 : Write a PL/SQL code to call Fusion journal import soap web service

After creating ACL and wallet in the database, we can call soap web service using UTIL_HTTP package. Sample code for above request payload is attached below.

DECLARE

-- soap requests/response

soap_req_msg VARCHAR2 (32767);

soap_resp_msg VARCHAR2 (32767);

-- http request/response

http_req UTL_HTTP.req;

http_resp UTL_HTTP.resp;

x_clob CLOB;

l_buffer VARCHAR2 (8000);

BEGIN

--set wallet directory

UTL_HTTP.set_wallet ('file:/u02/oracle/VIS/db/tech_st/11.2.0/appsutil/wallet', 'welcome123');

-- create soap request via http

soap_req_msg := 'Place the sample request payload given above';

--WSDL url for fusion journal import web service is placed in UTL_HTTP.begin_request, web service handler as 'POST' and protocol version HTTP/1.1

http_req := UTL_HTTP.begin_request ('https://ucf6-fap1522-fa-ext.oracledemos.com/finGlDesktopEntry/JournalImportService?WSDL', 'POST', 'HTTP/1.1');

UTL_HTTP.set_header (http_req, 'Content-Type', 'text/xml;charset=UTF-8');--type of input data i.e. request payload

UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (soap_req_msg));

UTL_HTTP.set_header (http_req, 'SOAPAction', 'https://ucf6-fap1522-fa-ext.oracledemos.com/finGlDesktopEntry/JournalImportService');

UTL_HTTP.set_header (http_req, 'Connection', 'Keep-Alive');

--Specify fusion user's username and password

UTL_HTTP.set_authentication (http_req, 'casey.brown', 'XBp58877');

UTL_HTTP.write_text (http_req, soap_req_msg);

-- invoke request and get response.

http_resp := UTL_HTTP.get_response (http_req);

DBMS_LOB.createtemporary (x_clob, FALSE);

DBMS_LOB.OPEN (x_clob, DBMS_LOB.lob_readwrite);

LOOP

DBMS_OUTPUT.put_line ('inside');

UTL_HTTP.read_text (http_resp, l_buffer);

DBMS_LOB.writeappend (x_clob, LENGTH (l_buffer), l_buffer);

END LOOP;

DBMS_OUTPUT.put_line ('Response Status: ' || http_resp.status_code || 'Reason : ' || http_resp.reason_phrase);

DBMS_OUTPUT.put_line (x_clob);

dbms_lob.freetemporary (x_clob);

UTL_HTTP.end_response (http_resp);

EXCEPTION

WHEN UTL_HTTP.end_of_body

THEN

UTL_HTTP.end_response (http_resp);

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Exception Occurred : ' || SQLERRM);

END;

After pl/sql code execution completes successfully, Journal entry information is imported in gl_interface table.

Step 7: Schedule Import Journals ESS Job

Finally, we have to schedule “Import Journals” ESS job (concurrent program) in fusion applications.

After “Import Journals” ESS job completes successfully, Journal entry is created in gl_je_headers table. We can check this by creating a data model in ‘BI Reports and Analytics’ section.

Thursday, November 28, 2019

Calling Fusion Soap Web Service in PL/SQL

Calling Fusion Soap Web Service in PL/SQL