Troubleshooting XML Gateway Inbound & Outbound Transactions

Troubleshooting is the main challenge in any issue. This article focuses on both the Inbound and Outbound flows of XML gateway transactions.

Outbound flow is as follows:

1) ECX message is generated.
2) Execution engine enqueues it into ecx_outqueue.
3) Message is dequeued by OTA.
4) Sent to Trading Partner using corresponding protocol (SMTP,HTTP,HTTPS etc).

Inbound flow is as follows:

1) Message is enqueued to ecx_inqueue by OTA.
2) ECX Inbound agent listener moves it from ecx_inqueue to ecx_transaction queue.
3) The ECX transaction agent listener triggers the subscription which moves the data into the open interface tables.
4)Validation APIs are run on these tables and finally data is pushed into the base tables.

Logs for ECX transactions:

As far as logging is concerned , pre-11.5.10, the logging needs to be written to a directory present in utl_file_dir . 11.5.10 onwards, we use fnd logging.
Please follow below steps to enable Logging for ECX and get the log files.

1) Set the following profiles at the Site/Application Level inside the E-Business Suite. In the case of setting log parameters at application level , you need to set the application name also, which would be “XML Gateway”.

FND: Debug Log Module = ecx%
FND: Debug Log Enabled = Yes
FND: Debug Log Level = Statement

2) After launching your transaction/reproducing the issue, you need to login to OAM and use Site Map > Monitoring > Logs and query for module: ecx%.

3) Collect these log files and upload them for review by Support.
OR
1) Run the $ECX_TOP/patch/115/sql/ECXLGINI.sql.

2) Then review the Logging messages in the OAM UI. Navigation: Log in to the self service applications with sysadmin, then select the System Administration responsibility, navigate to Oracle Application Manager -> Logs. Then search for log messages for Module ecx% to see the log file in the transaction monitor.

3) To get the physical ecx log file we will have to query the column Module in fnd_log_messages as follows:
select module from apps.fnd_log_messages where module like ‘ecx%’ order by log_sequence;

4) Once you get the module name then you can get the log file by running $ECX_TOP/patch/115/sql/ECXLGUTL .
This script will prompt for a module name. Provide the name as obtained in step3 and this should generate a log file, the script will finish with the name and location of that log file. Upload that log file to review.

Logs for OTA :
If message is not dequeued from ecx_outqueue or XML delivery fails then the problems is with OTA and troubleshooting for OTA has to be done.  To enable debugging mode for OTA and get relevant log files please do the following:

1. Shut down Apache using the adapcctl.sh (or adstpall.sh) script.
2. Backup the existing files in the following directories:
$IAS_ORACLE_HOME/Apache/Apache/logs
$IAS_ORACLE_HOME/Apache/Jserv/logs
$IAS_ORACLE_HOME/Apache/Jserv/logs/jvm

3. Enable debug logging for Apache
Modify $IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf:
LogLevel debug

4. Enable debug logging for mod_jserv
Modify $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.conf:
ApJServLogLevel debug

5. Enable debug logging for XML Gateway Jserv
Modify $IAS_ORACLE_HOME/Apache/Jserv/etc/xmlsvcs.properties:
log=true
log.channel=true
log.channel.info=true
log.channel.debug=true
wrapper.bin.parameters=-DOXTALogDebugMsg = true

6. Restart Apache for these changes to take effect using the adapcctl.sh (or adstrtal.sh) script.

7. Replicate the problem.

8. Upload the log files for errors and warning messages relevant to the reported issue.  The following files from the $IAS_ORACLE_HOME/Apache/Jserv/logs directory:
a) xmlsvcs_jserv.log
b) jserv.log
c) All files from the $IAS_ORACLE_HOME/Apache/Jserv/logs/jvm directory generated from XML Gateway JVM

These will have filenames of the form XmlSvcsGrp.0.stdout|stderr

Note: OTA from version 11.5.10 CU2 uses the FND logging too. AFLOG parameters are used for enabling logging for OTA. They can be set in the FND profile options

AFLOG_ENABLED= true
DAFLOG_LEVEL= <1-statement,6-Unexpected, Only set at either of these 2 levels).
AFLOG_MODULE= ecx.oxta%
AFLOG_FILENAME= <filename on midtier>.

Basic troubleshooting information needed by support for any SR/Bug logged for ECX transactions:

1) Output of the following 2 scripts:
$ECX_TOP/patch/115/sql/ecxver.sql
$ECX_TOP/patch/115/sql/ECXTEST.sql

2) Check if the ECX agent listeners are up and running (Can be checked from OAM).

3) Collect the log files for ECX as mentioned above.

4 a) For outbound Transactions check if the XML has been generated by querying in the ecx_doclogs table as follows:
select document_number, protocol_address , status , logfile, time_stamp,payload from ecx_doclogs where document_number=‘XXX';
4b) The XML delivery status can be found from a table named ecx_oxta_logmsg. Use the query below:

SELECT edoc.document_number
,decode(eol.result_code, 1000, ‘Success’, ‘Failure’) AS status
,eol.result_text
FROM ecx_oxta_logmsg eol
,ecx_doclogs edoc
,ecx_outbound_logs eog
WHERE edoc.msgid = eol.sender_message_id
AND eog.out_msgid = edoc.msgid
ORDER BY edoc.document_number;

5) For Inbound transactions please reproduce the issue and then run this SQL:

select il.MSGID, il.status, il.error_id, il.logfile, il.time_stamp, er.message,dl.transaction_type, dl.transaction_subtype, dl.document_number, dl.party_site_id,dl.payload, dl.status
from ECX_INBOUND_LOGS il,
ECX_ERROR_MSGS er,
ECX_DOCLOGS dl
where il.error_id = er.error_id(+)
and il.MSGID = dl.msgid(+)
and il.time_stamp between to_date(”, ‘DD-MON-YYYY HH24:MI’) and
to_date(”, ‘DD-MON-YYYY HH24:MI’)
order by il.time_stamp asc;