Oracle Applications: EBS, Fusion - Random Scribbles by hila9t

Friday, December 4, 2020

ISG Rest Web Service – 500 Internal Server Error Exception

Symptom/Problem/Error:

When a REST Web Service is invoked, the system immediately return Error Response mentioning “Error 500--Internal Server Error”

<html>
   <head>
      <meta content="HTML Tidy for Java (vers. 26 Sep 2004), see www.w3.org" name="generator"/>
      <title>Error 500--Internal Server Error</title>
   </head>
   <body bgcolor="white">
      <font face="Helvetica">
         <br clear="all"/>
      </font>
      <table cellspacing="5" border="0">
         <tr>
            <td>
               <br clear="all"/>
               <h2>
                  <font size="3" color="black" face="Helvetica">Error 500--Internal Server Error</font>
               </h2>
            </td>
         </tr>
      </table>
      <table cellpadding="10" width="100%" border="0">
         <tr>
            <td bgcolor="white" width="100%" valign="top">
               <h3>
                  <font face="Courier New">
                     <font size="3" face="Helvetica">
                        From RFC 2068
                        <i>Hypertext Transfer Protocol -- HTTP/1.1</i>

The error can happen from SOAP UI, Postman or EBS ISG Test Client as well.

The same error can happen when the input XML is not properly parsed. But the error explained here is unrelated.

This typically happen when the service is invoked several times and very frequently. Due to some internal issue, EBS create multiple records in the resultant table.

Cause:

Duplicate records are created in “fnd_log_transaction_context” table due to Oracle EBS Bug.

Solution:

Step 1: Get Class ID of Problem Web Service

SELECT FSS.SERVICE_ALIAS, FSS.WSDL_URL, FSS.CLASS_ID, TO_CHAR(FSS.CREATION_DATE,'YYYY-MON-DD') AS CREATED_DATE,
ICL.CLASS_NAME, ICL.IREP_NAME, ICL.CLASS_TYPE, ICL.PRODUCT_CODE, ICL.SOURCE_FILE_PATH, ICL.SOURCE_FILE_NAME, ICL.DISPLAY_NAME
FROM APPS.FND_SOA_SERVICES FSS, FND_IREP_CLASSES_VL ICL
WHERE FSS.WSDL_URL LIKE '%<Service Name>%%'
AND FSS.CLASS_ID = ICL.CLASS_ID(+)
--AND WSDL_URL LIKE '%/rest/%' -- get only RESTful Web Services
ORDER BY FSS.SERVICE_ALIAS;

Step 2: Identify any service that has created duplicate records in FND LOG Transaction Context (fnd_log_transaction_context) table

select transaction_id, count(*)
from fnd_log_transaction_context
where transaction_type = 'SOA_INSTANCE'
group by transaction_id
having count(*) > 1;

Step 3: Delete the records: Replace the transaction_id in query below from the able query results.

DELETE from fnd_log_transaction_context
where transaction_type = 'SOA_INSTANCE'
and transaction_id = < transaction_id>;

System will automatically create new record in the table, when the web service is invoked first time after deletion.

No bouncing or Functional Admin Cache Clearing is required for this solution.

Keywords:

Oracle EBS, SOAP, fnd_log_transaction_context, REST, Web Service, R12, R12.2.8 Release, ISG, Integrated SOA Gateway, SOAP UI, Postman, WS

Tuesday, November 3, 2020

Credit Card Settlement: Submit Offline Transactions Wkr Error

Symptom/Problem/Error:

Submit Offline Transactions creates one or more Worker jobs to process the settlement of individual Credit Card transactions. This occasionally give error with payment processor or other issues.

Responsibility: Fund Transfer Process Manager

Main Concurrent Program: Submit Offline Transactions

Spawned Concurrent Program: Submit Offline Transactions Worker; Name “WRKR(01) (Submit Offline Transactions Wkr)”

When job is completed program gives error:

Once the error is generated, all subsequent processes will try to re-settle previous erred transaction. Thus every further job gives error.

Here is an example Concurrent Job Output:

+---------------------------------------------------------------------------+

Payments: Version : 12.2

IBY_FC_SUBMIT_OFFLINE_TXNS_WKR: Submit Offline Transactions Wkr

+---------------------------------------------------------------------------+

Current system time is 11-DEC-2020 15:24:50

+---------------------------------------------------------------------------+

P main executing runProgram for oracle.apps.iby.scheduler.SubmitOfflineTransactionsWkr

instr_type: CREDITCARD

Task Parameter: key: TRXNMID_MAX, value: 32606

Task Parameter: key: P_INSTRUMENT_TYPE, value: CREDITCARD

Task Parameter: key: TRXNMID_MIN, value: 32605

Processing CREDITCARD operations ..

java.sql.SQLException: ORA-20000: IBY_20422#

ORA-06512: at "APPS.IBY_TRANSACTIONCC_PKG", line 6807

ORA-06512: at "APPS.IBY_TRANSACTIONCC_PKG", line 1348

ORA-06512: at line 1

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)

= = =

Total CREDITCARD transaction processed : 2

Total CREDITCARD transaction succesful : 2

Total CREDITCARD transaction failed : 0

Finished processing CREDITCARD

Task Parameter: key: TRXNMID_MAX, value: 32606

Task Parameter: key: P_INSTRUMENT_TYPE, value: CREDITCARD

Task Parameter: key: TRXNMID_MIN, value: 32605

Processing PURCHASECARD operations ..

Finished processing PURCHASECARD

Cause:

Potential Causes of this problem:

Ø Error with bad or incomplete data
Ø Payment Gateway validation errors
Ø Transmission errors during Payment Processing

The steps mentioned is a work-around to remove this error affecting subsequent steps. The original error transaction needs to be settle manually. This may also require a new Credit Card Authorization.

Solution:

Step 1: Identify Problem Credit Card Transaction. May require some tweaking of the WHERE Clauses.

Select x.*

from IBY_TRXN_SUMMARIES_ALL x

where creation_date > sysdate-4.1 -- change based on when problem started

and STATUS = 1 -- Status 1 make the Submit Offline Program reprocess

and TRXNTYPEID = 8 -- use this only if reqd

order by TRXNMID desc;

Step 2: Update status from 1 to 0. (Make the EBS to think this transaction completed successfully !)

Update IBY_TRXN_SUMMARIES_ALL

Set status = 0

Where TRXNMID = <Error TRXNMID Value>;

Further executions will not process the problem transaction.

No bouncing or Functional Admin Cache Clearing is required for this solution.

Keywords:

Oracle EBS, iPayment, Payments, IBY, IBE, CC Credit Card, Payment Gateway, Cyber Source, CYS, SOAP REST Web Service, R12 R12.2.8 Release

Saturday, September 12, 2020

WLDataMigration Tool : Allowed Resources: Custom Servlet Import

Problem:

Create a new JSP or Servlet, place it in $OA_HTML page of Server or have a mapping to the Java Resource in web.xml. When resource is tried from the server, the error “Requested resource or page is not allowed in this site” is displayed on browser.

Here is a screenshot for the error:

Cause:

Even though the Servlet is added to server and mapping is present, the resource is to be added to Allowed Resources using “WLDataMigration” tool to enable it accessible.

This is an additional security introduced in EBS Release 12.2.

Solution Steps:

1. Create *.conf file:

The mapping for the servlet needs to be imported to EBS using java utility. The file with mapping is stored in a file with extension “*.conf” and store in a Unix folder (Assuming application server is Unix).

Here is an example contents for mapping file:

File: cys_servlets.conf

Content:

#/* --- cys_servlets.conf --- */

# Sample entry for a servlet whose URL mapping ends with *

servlet /OA_HTML/oramipp_cys

2. Upload file using “WLDataMigration” utility:

Next step is to upload the file from Unix (OS) to APPS using “WLDataMigration” Utility. Here is the command:

java oracle.apps.fnd.security.resource.WLDataMigration MODE=custom INPUT_FILE=/export/home/<devuser>/cys_servlets.conf DBC=$FND_SECURE/<DEV>.dbc

Please substitue <devuser< with user name of Application Server Unix Box. Change <DEV> to DBC Filename that defines connection.

This is a java command. Make sure $PATH variable has the java file for execution.

Make sure EBS Instance environment variables are loaded properly to the server.

Please provide full path of Input File (/export/home/<devuser>/cys_servlets.conf)

Review the command outputs and check for error messages.

Review Allowed Resource Setup:

Once resource mapping is completed, please verify the mapping is working.

Login User: SYSADMIN

Responsibility: Functional Administrator

Navigation Path: Allowed Resources à Custom

Note: The Green Tick on Web Activity is checked only after the new resource is accessed from the web client (Any browser).

Access Resource (Connect from Browser):

Once resource mapping is completed, please verify the mapping is working.

Note: The above output is proper based on Servlet Logic. This is not an error.

Keywords:

Oracle EBS, “Requested resource or page is not allowed in this site”, WLDataMigration, Custom Servlet, Allowed Resources, Functional Administrator, Resource Mapping, httpd.conf, custom_servlet.conf, EBS Resource Mapping

Friday, August 7, 2020

Credit Card: Authorization Reversal Error

Symptom/Problem/Error:

When Credit Card Authorization Reversal API is called, the EBS returns the following error:

Results: V_REV_reversal_response.Result_Code

INVALID_TXN_EXTENSION Result_Category

INVALID_PARAM Result_MessageTransaction extension does not exist.

Results: V_reauth_response.Result_Code Result_Category Result_Message

This create multiple Credit Card Authorizations occur for a single customer for a single user. This might also create indirect issues like Customer Credit Limit Exposure, Multiple Fund Capture for a single Transaction, etc.

Causes:

Potential Causes of this problem:

> Current Transaction is not completed
> Another payment process is using or accessing current transaction

Solution:

Step 1: Identify Problem Credit Card Transaction. May require some tweaking of the WHERE Clauses.

This happens when the to-be-reversed transaction is being used in a previous transaction. The same API call after a few seconds (Let us say 20 second delay) will work perfectly.

An easier solution is to create a new concurrent program to reverse and subsequently authorize with 20 second delayed start time J

We use Cyber Source Payment Gateway Model for Credit Card Processing. But this issue is entirely within EBS and can happen to any Payment Gateway or Processor.

Keywords:

Oracle EBS, iPayment, Payments, CC Credit Card, Auth, Authorization, Auth Reversal, Auth Void, Payment Gateway, Cyber Source, CYS

Wednesday, July 22, 2020

isgDesigner.xml ant Error: ISGManagementException: Wrong AdminUserName or/and AdminUserPassword

Symptom/Problem/Error:

When the below command is executed from Application Service Unix Box, the error is returned:

Command:

ant -f $JAVA_TOP/oracle/apps/fnd/isg/ant/isgDesigner.xml -Dactions="undeploy" -DserviceType=REST -DirepNames="FND_WEB_SEC"

Result/Error:

[java] Invoking undeploy method on class Id : 2365...

[java] oracle.apps.fnd.isg.mgmt.common.error.ISGManagementException: Wrong AdminUserName or/and AdminUserPassword or/and Server not reachable or/and Unknown jmxUrl

Cause:

Incomplete or incorrect ISG Setup.

Solution:

To implement the solution test the following steps in a development instance and then migrate accordingly:

Step 1. Reset the ASADMIN password:

1. Check that the permission of isgagent.zip are set to 777

2. Follow Oracle Metalink Note 1311068.1, "Section 3: Enabling ASADMIN User with the Integration Administrator Role"

3. On the Web Logic Server, Log into the WebLogic Server Administration Console. This will require special login link to the console. The user details are different from database. Contact Admin for access details.

a. Expand Service -> Data Source -> Select the link of the correct Data Source.

b. Configuration -> Connection Pool

c. Check inside the Properties box:

- The 'User's property would normally be set to 'ASADMIN' e.g. user=ASADMIN

- There should be dbcFile defined

e.g. dbcFile=/<directory path>/<dbc file name>.dbc .

Make sure the <path> and <file name> are absolute, present in the middle tier and has access permissions.

- If the password of the user provided here has been changed on the EBS Side, then update the password parameter with the new(same) password and confirm password.

d. Restart the Data Source if any of the above was updated.

4. Also in Web Logic Server (WLS) complete the following steps:

a. Navigate to bifoundation_domain - Services - Security Realms.

b. Select myrealm.

c. Select User Lockout tab.

d. Disable Lockout Enabled option.

e. Save

5. Use the below SQL statement to check whether ASADMIN user exists in fnd_vault or not. If the user exists in fnd_vault then this statement will return its password, otherwise it will return nothing.

select FND_VAULT.get('ISG', 'ASADMIN') from dual;

If the user does not exist, add it using the following statement:

SQL> @$FND_TOP/sql/afvltput.sql FND ASADMIN <ASADMIN_PASSWORD>

Step 2. Undeploy the web service, then retest the configuration steps from section 6.2 and confirm the complete without error.

Keywords:

Oracle EBS, SOAP, REST, isgDesigner.xml, ant, ISGManagementException, AdminUserName, AdminUserPassword, ASADMIN, Web Service, R12, R12.2.8, ISG, Integrated SOA Gateway

Tuesday, June 16, 2020

Oracle EBS SQLs – Order Hold, Credit Card Details

Business Need:
Oracle Applications (EBS) Support, Analysis, Troubleshooting, Reports, Tracking, etc. I am using R12.2.8. But the SQLs will be applicable most of the recent versions.
Helpful to analyze Credit Card Orders with Oracle Payments (iPayments / IBY) troubleshooting.

Queries:
Order Details – Hold Type, Name, Codes

-- hold types for a given order
SELECT OEH.HEADER_ID, OTT.NAME ORDER_TYPE,OEH.ORDER_NUMBER,OEH.PAYMENT_TYPE_CODE,RTM.NAME PAYMENT_TERM,OOL.LINE_NUMBER,
OOL.ORDERED_ITEM,OOL.SCHEDULE_SHIP_DATE,OOL.FLOW_STATUS_CODE LINE_STATUS,
OHD.NAME,OOH.ORDER_HOLD_ID,OOH.CREATION_DATE,OOH.RELEASED_FLAG
FROM OE_ORDER_HEADERS_ALL OEH, OE_ORDER_LINES_ALL OOL,OE_TRANSACTION_TYPES_TL OTT,
RA_TERMS_TL RTM.,
OE_ORDER_HOLDS_ALL OOH,
OE_HOLD_SOURCES_ALL OHS,
OE_HOLD_DEFINITIONS OHD
WHERE 1=1
AND OEH.HEADER_ID=OOL.HEADER_ID
   AND OEH.HEADER_ID = 5153513
   AND OEH.ORDER_TYPE_ID=OTT.TRANSACTION_TYPE_ID
   AND OEH.PAYMENT_TERM_ID=RTM.TERM_ID
   AND OEH.HEADER_ID=OOH.HEADER_ID(+)
   AND OOH.HOLD_SOURCE_ID=OHS.HOLD_SOURCE_ID(+)
   AND OHS.HOLD_ID=OHD.HOLD_ID(+);

Credit Card Details for the Order

-- orders, lines, credit card details (CC tables not used anymore)
SELECT   OOHA.HEADER_ID,OOLA.LINE_NUMBER,OOLA.LINE_ID,OP.LINE_ID, OOHA.ORDER_NUMBER,
(SELECT HCA.ACCOUNT_NUMBER   FROM HZ_CUST_ACCOUNTS HCA
WHERE HCA.CUST_ACCOUNT_ID=OOHA.SOLD_TO_ORG_ID ) CUSTOMERNUMBER,
OOHA.FLOW_STATUS_CODE "OrderStatus", OOHA.PAYMENT_TYPE_CODE, OOHA.CREDIT_CARD_NUMBER,
OOLA.ORDERED_ITEM,   OOLA.ORDERED_QUANTITY, OOLA.SHIPPED_QUANTITY, OOLA.INVOICRD.D_QUANTITY,
OOLA.TAX_VALUE, (OOLA.UNIT_SELLING_PRICRD. * OOLA.ORDERED_QUANTITY) ITEMTOTALAMOUNT,
OOLA.FLOW_STATUS_CODE "LineStatus", IFTE.TRXN_EXTENSION_ID ,
ICRD.CCNUMBER,   ICRD.CHNAME, ICRD.CARD_OWNER_ID, ICRD.MASKED_CC_NUMBER, ICRD.CARD_ISSUER_CODE, ICRD.EXPIRYDATE, ICRD.INACTIVE_DATE
FROM OE_ORDER_HEADERS_ALL OOHA, OE_ORDER_LINES_ALL OOLA, OE_PAYMENTS OP, IBY_FNDCPT_TX_EXTENSIONS IFTE, IBY_PMT_INSTR_USES_ALL IPUA, IBY_CREDITCARD ICRD
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
--AND OOHA.PAYMENT_TYPE_CODE LIKE 'CREDIT_CARD'
AND OP.LINE_ID(+)        =OOLA.LINE_ID
AND OOHA.HEADER_ID = 5153513
AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)
--AND IFTE.TRXN_EXTENSION_ID=ITSA.INITIATOR_EXTENSION_ID
AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)
AND IPUA.INSTRUMENT_ID      =ICRD.INSTRID(+);

Keywords:
Oracle EBS, R12, R12.2.8, OM, OE, ONT, IBY, Oracle Payments, iPayments, Order Management, Shipping, Ship Confirm, Query, SQL, SQLPLUS