Troubleshooting UTL_HTTP call (iPayment connection) from Database to EBS R12.1.x

Posted byIndraneil SealPosted inUncategorized

Oracle EBS use iPayment Database API package IBY_PAYMENT_ADAPTER_PUB to make requests to the iPayment application. After some validations, this API makes an HTTP request to the iPayment Engine Servlet (oracle.apps.iby.ecservlet.ECServlet, common alias ECApp) located on the middle tier.

We have SSL configuration for our EBS 12.1.x running on database 19c. This document will provide steps for testing the setup.

Step 1. Make sure that following profile options are correct.

  • IBY: ECAPP URL
  • ICX: Oracle Payment Server URL
  • Database Wallet Directory
  • IBY : HTTP Proxy

Run this Query & verify the results:

select substr(e.profile_option_name,1,35) Profile,
decode(a.level_id,10001,’Site’,10002,’Application’,10003,’Resp’,10004,’User’)L,
decode(a.level_id,10001,’Site’,10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue,
nvl(a.profile_option_value,’Is Null’) Value
from fnd_profile_option_values a, fnd_responsibility_tl b, fnd_application c,fnd_user d, fnd_profile_options e
where e.profile_option_name in (‘ICX_PAY_SERVER’,’IBY_ECAPP_URL’,’IBY_WALLET_LOCATION’,’IBY_WALLET_PASSWD’,’FND_DB_WALLET_DIR’, ‘IBY_HTTP_PROXY’)
and e.profile_option_id = a.profile_option_id
and a.level_value = b.responsibility_id (+)
and a.level_value = c.application_id (+)
and a.level_value = d.user_id (+);

PROFILELLVALUEVALUE
IBY_HTTP_PROXYSiteSite
FND_DB_WALLET_DIRSiteSite<DB wallet location>
IBY_ECAPP_URLSiteSitehttps://<>/OA_HTML/ibyecapp
ICX_PAY_SERVERSiteSitehttps://<>/OA_HTML/ibyecapp

Step 2. Make sure the CA-signed certificates(root & intermediate) has been imported into database wallet directory

You can also check this by running below command –

cd $ORACLE_HOME/appsutil/sslwallet ( Assuming this is the ssl wallet directory on the DB)
orapki wallet display -wallet .

Step 3. Make sure firewall is open from DB to Apps Tier/LoadBalancer on active web port(443) by doing telnet

Step 4. Execute UTL_HTTP.

Run the below query –

SQL> select utl_http.request(‘&value_of_IBY_ECAPP_URL’,null,’file:&value_of_FND_DB_WALLET_DIR’, null) from dual;

or

select utl_http.request(
URL => ‘&value_of_IBY_ECAPP_URL’,
proxy => hr_util_web.proxyForURL(fnd_profile.value(‘APPS_FRAMEWORK_AGENT’)),
wallet_path => ‘file:&value_of_FND_DB_WALLET_DIR’,
wallet_password => fnd_preference.eget(‘#INTERNAL’,’WF_WEBSERVICES’,’EWALLETPWD’,’WFWS_PWD’)
) from dual;

where
value_of_IBY_ECAPP_URL = https://test.domain/OA_HTML/ibyecapp
value_of_FND_DB_WALLET_DIR = $ORACLE_HOME/appsutil/sslwallet

Make sure to execute this UTL_HTTP from ALL RAC Nodes to verify this is working fine from all RAC nodes. If you have multiple application tiers, you must verify all application tiers separately.

If it meets all the above conditions, the result will be shown below

In our case, we encountered below issue. As a result, automatic remittance batch, which does payment that goes to recipient, is failing currently.

We later found out that database host was unable to reach the app server.

telnet test.domain 443
Trying 198.123.45.30…
telnet: connect to address 198.123.45.30: Connection timed out

When we raised this issue with the network team, we were told opening port 443 is against the security control in our environment, DB tier is not allowed to go back to App tier.

However after the above configuration change was explained & firewall opened, the above issue was resolved.

Published by Indraneil Seal

I originally hail from Kolkata, India, and I've dedicated a significant portion of my professional journey to both India and the United States before relocating to Canada during the pandemic. I’m a member of MongoDB's esteemed Technical Services team. Before joining this exceptional group, I held the role of Senior Apps DBA at the Government of Ontario. Prior to that, I spent many years honing my technical(DBA/Cloud) and soft skills with TCS, KBACE Technologies(which later got acquired by Cognizant Technology Solutions) and Oracle Corporation. Throughout my career, I was deeply immersed in day-to-day operations and spearheaded significant projects, including the modernization of platforms, Oracle application and database upgrades. In my last stint at the Government of Ontario, I was also responsible for overseeing various automation initiatives including out-of-place patching, automated EBS Application patching. Outside of my professional life, I have a strong passion for reading, blogging, spending quality time with my family, and my feline buddies, Smokey & Louis. I also relish solitary walks and jogging as personal interests. As a proponent of open source technologies, I'm looking forward to sharing my knowledge and expertise as well as contribute as much as possible to the success of the IT fraternity thereby perpetually expanding my skillset.

Leave a comment