EBS Custom Servlet Error: Error 404-- server has not found anything matching the Request-URI

 

Scenario:

Add a new custom servlet to EBS. Try to access the servlet from UI (Oracle Applications Front-End). The screen displays error below:

Error 404--Not Found
From RFC 2068 Hypertext Transfer Protocol -- HTTP/1.1:
10.4.5 404 Not Found
The server has not found anything matching the Request-URI. No indication is given of whether the condition is temporary or permanent.

We use R12.2.8. Error can happen in any version of R12 Integrated SOA Gateway (ISG) Web Services.

 

 

Cause:

The Custom Servlet needs to be mapped to Web.XML File.

In Orion based servers (12.2.8 or higher), the Web.xml file will get overwritten by Custom Tmp file contents during Middle Tier bouncing. In this cases the custom/ oacore_web_xml_FMW.tmp file needs to be mapped.

Relevant Folders:

Web.xml à $OA_HTML/WEB-INF

          Eg: /appsR122/DEV/fs2/FMW_Home/Oracle_EBS-app1/applications/oacore/html/WEB-INF

oacore_web_xml_FMW.tmp à $RUN_BASE/EBSapps/appl/fnd/12.0.0/admin/template/custom

          Eg: /appsR122/DEV/fs2/EBSapps/appl/fnd/12.0.0/admin/template/custom

 

Note: Create custom folder, if it is not present

Another error (no permissions to resource) is caused due to missing mapping for the resource. This is done by Resource Import command. It is mentioned in another post.

Solution:

1.    Create new mapping for the Servlet in oacore_web_xml_FMW.tmp file:

<!-- XXABC Entry for Custom Payment Configurations to Cyber Source - Begin -->
 
    <servlet-mapping>
    <servlet-name>XxabcLoopBackServletCys</servlet-name>
    <url-pattern>/oramipp_cys/*</url-pattern>
    </servlet-mapping>
 
     <servlet>
     <servlet-name>XxabcLoopBackServletCys</servlet-name>
     <servlet-class>xxabc.oracle.apps.iby.bep.loop.XxabcLoopBackServletCys</servlet-class>
       <init-param>
         <param-name>debug</param-name>
         <param-value>true</param-value>
       </init-param>
    </servlet>
    
<!-- XXABC Entry for Custom Payment Configurations to Cyber Source - End -->
2.    Bounce middle tier

3.    Make sure web.xml is updated and the mapping is present in web.xml file

 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications, ASADMIN, ISG, Integrated SOA Gateway, Web Services, REST, RestFul, oacore_web_xml_FMW.tmp

EBS JarSigner: Example Usages

 

What is JarSigner ?

A Custom Java JAR file needs to be signed using digital signature before it to be used for Oracle Application Server. This is to ensure file integrity and improve security. JarSigner command is used to create the signed JAR file.

This page give some examples of using JarSigner command.

JarSigner Examples:

Please substitute Password123 with your own Store Pass and Key Pass. Please substitute DEV_devappsap01 with your Middle Tier / Application Server name.

 

1) Generate Keypair for code signing certificate

 

2) Import root certificate to cacerts

 

keytool -import -alias YOUR_ALIAS -file root.crt -trustcacerts -v -keystore cacerts

 

3) Import intermediate certificate to adkeystore.dat

 

keytool -import -file intermediate.crt -trustcacerts -alias YOUR_ALIAS -keystore adkeystore.dat

 

4) Import Code Signing Cert (code signed cert would have been copied to adkeystore.crt)

 

adjkey -import -file adkeystore.crt -trustcacerts

 

5) On your User Desktop import the certificates in this order to Java

 

 
 
command -
$adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar $CONTEXT_NAME 1 CUST jarsigner -storePass Password123 -keyPass Password123
 
Error
 
ERROR: JarSigner subcommand exited with status 1
 
JarSigner standard output:
jarsigner: Certificate chain not found for: DEV_devappsap01.  DEV_devappsap01 must reference a valid KeyStore key entry containing a private key and corresponding public key certificate chain.
 
Flag
 
adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar $CONTEXT_NAME 1 CUST jarsigner -storePass <KeyStore Password> -keyPass <Key Password>
 
 
adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar $CONTEXT_NAME 1 CUST jarsigner -storePass "Password123" -keyPass "Password123"
 
 
adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar "DEV_devappsap01" 1 CUST jarsigner -storePass "Password123" -keyPass "Password123"
adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar $CONTEXT_NAME 1 CUST jarsigner -storePass "Password123" -keyPass "Password123"
 
 
 
adjava oracle.apps.ad.jri.adjmx -areas ./customprod.zip -outputFile ./customprod.jar -jar "DEV_devappsap01" 1 CUST jarsigner -storePass "<pwd>" -keyPass "<pwd>"
 
 
 
LD_LIBRARY_PATH=/appsR122/DEV/fs1/EBSapps/10.1.2/jdk/jre/lib/sparc:/appsR122/DEV/fs1/EBSapps/10.1.2/jdk/jre/lib/sparc/native_threads:/appsR122/DEV/fs1/EBSapps/appl/cz/12.0.0/bin:/appsR122/DEV/fs1/EBSapps/10.1.2/lib32:/appsR122/DEV/fs1/EBSapps/10.1.2/lib:/usr/dt/lib:/usr/openwin/lib:/appsR122/DEV/fs1/EBSapps/10.1.2/jdk/jre/lib/sparc:/appsR122/DEV/fs1/EBSapps/10.1.2/jdk/jre/lib/sparc/native_threads:/appsR122/DEV/fs1/EBSapps/appl/sht/12.0.0/lib:/appsR122/DEV/fs1/EBSapps/10.1.2/jdk/jre/lib/sparc/client
 
adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar "DEV_devappsap01" 1 CUST jarsigner -storePass "Password123" -keyPass "Password123"
 

 

 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications, JarSigner, ASADMIN, ISG, Integrated SOA Gateway, Web Services, REST, RestFul, $JAVA_TOP

EBS REST WS Error: java.lang.SecurityException: User: ASADMIN~~, failed to be authenticated.

 

Scenario:

Typically the error comes when accessing/deploying/undeploying a web service in a test instance after cloning.

We use R12.2.8. Error can happen in any version of R12 Integrated SOA Gateway (ISG) Web Services.

  

Cause:

The ASADMIN can not login to create/deploy web service

Solution:

Here are the possible causes:

• Ø ASADMIN password is not reset after cloning from production instances

Check ASADMIN user can login with the previous password from instance front-end link

Make sure the ASADMIN user has required permissions and responsibilities based on business requirements

ADASMIN is associated with proper MOAC (Multi Org Access Control) defined with User or Responsibility

• Ø ASADMIN user or responsibility is inactivated, locked or end dated
• Ø ISG Setup files are not properly configured

o   isgagent.properties

o   isg_contextfile.properties

 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications, ASADMIN, ISG, Integrated SOA Gateway, Web Services, REST, RestFul

DIY steps - oaf personalization page error resolve

 

This page gives a few easy DIY (Do It Yourself) steps to resolve Oracle Application Framework (OAF) personalization page

Problem:

- OAF personalization done

- Page gives error and not able to recover

 

 

Solution:

 

1. Identify personalization

 

SELECT PATH.PATH_DOCID PERZ_DOC_ID,

jdr_mds_internal.getdocumentname(PATH.PATH_DOCID) PERZ_DOC_PATH, PATH.*

FROM JDR_PATHS PATH

WHERE PATH.PATH_DOCID IN

(SELECT DISTINCT COMP_DOCID FROM JDR_COMPONENTS

WHERE COMP_SEQ = 0 AND COMP_ELEMENT = 'customization'

AND COMP_ID IS NULL)

and path.creation_date > sysdate-.04

ORDER BY PERZ_DOC_PATH;

 

97016 /oracle/apps/iby/fundcapture/transaction/request/webui/customizations/site/0/AuthorizationsSearchPG     AuthorizationsSearchPG

 

 

2. Delete customization

 

 

 begin 

   jdr_utils.deleteDocument('/oracle/apps/iby/fundcapture/transaction/request/webui/customizations/site/0/AuthorizationsSearchPG'); 

 end; 

/

 

commit;

 

 

3. Bounce OA CORE Server

 

 

{ echo Password123 ; }| admanagedsrvctl.sh abort oacore_server1 @-nopromptmsg

{ echo Password123; }| admanagedsrvctl.sh start oacore_server1 @-nopromptmsg

 

The changes normally do not require bouncing or clearing cache. But if the steps fails to resolve the error, try after bouncing middle tier.

 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications, OAF, OA Framework, Personalization, Customization, HTML UI Page, page.xml

PL/SQL: XML Parsing CyberSource Response Example

 

PL/SQL: XML Parsing CyberSource Response Example

 

Business Need:

Just an example of XML Parsing within PL/SQL.

This can be used to parse any XML within PL/SQL. Make sure APEX JSON is installed in database.

Note: We are using Oracle 12.1.0.0 database version. Some objects and parser are not available in older versions of Oracle database.

 

Program / PL/SQL Script:

set serveroutput on;
     -- Insert data into staging table
    DECLARE
             xAuthRespXML XMLType;
             V_AUTH_RESP_XML VARCHAR2(5000);
             sAuthResponseNameSpace  VARCHAR2(5000);
             v_item_count NUMBER;
       BEGIN
 
-- Substitute your XML Text here
V_AUTH_RESP_XML := '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
       <soap:Header>
             <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
                    <wsu:Timestamp xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" wsu:Id="Timestamp-1489510174">
                           <wsu:Created>2017-07-23T04:27:08.680Z</wsu:Created>
                    </wsu:Timestamp>
             </wsse:Security>
       </soap:Header>
       <soap:Body>
             <c:replyMessage xmlns:c="urn:schemas-cybersource-com:transaction-data-1.165">
                    <c:merchantReferenceCode>EbsONT122052844.27</c:merchantReferenceCode>
                    <c:requestID>5954784279986442603008</c:requestID>
                    <c:decision>ACCEPT</c:decision>
                    <c:reasonCode>100</c:reasonCode>
             <c:requestToken>Axj/7wSTQs87kNkwJAoAABsZLnsZc6HPnObVOMojhEbUIoBURwiNqEVpBK6PcDMhk0ky9GLDX7uBOTQs87kNkwJAoAAAXAhY</c:requestToken>
                    <c:purchaseTotals>
                           <c:currency>USD</c:currency>
                    </c:purchaseTotals>
                    <c:ccAuthReply>
                           <c:reasonCode>100</c:reasonCode>
                           <c:amount>1907.11</c:amount>
                           <c:authorizationCode>831000</c:authorizationCode>
                           <c:avsCode>Y</c:avsCode>
                           <c:avsCodeRaw>Y</c:avsCodeRaw>
                           <c:authorizedDateTime>2017-07-23T04:27:08Z</c:authorizedDateTime>
                           <c:processorResponse>00</c:processorResponse>
                           <c:reconciliationID>KO1KNCON9ZSF</c:reconciliationID>
                    <c:authRecord>0110322000000E10000200000000000019071107230427080517684B4F314B4E434F4E395A53463833313030303030000159004400103232415050524F56414C0022313457303136313530373033383032303934473036340006564943524120</c:authRecord>
                    <c:paymentNetworkTransactionID>016150703802094</c:paymentNetworkTransactionID>
                    </c:ccAuthReply>
                    <c:decisionEarlyReply>
                           <c:reasonCode>100</c:reasonCode>
                           <c:rcode>1</c:rcode>
                           <c:activeProfileReply/>
                    </c:decisionEarlyReply>
                    <c:card>
                           <c:cardType>001</c:cardType>
                    </c:card>
                    <c:acquirerMerchantNumber>000225130042990</c:acquirerMerchantNumber>
                    <c:pos>
                           <c:terminalID>06011497</c:terminalID>
                    </c:pos>
             </c:replyMessage>
       </soap:Body>
</soap:Envelope>';
 
               dbms_output.put_line('Before FOR loop through Customer XML');
 
               xAuthRespXML :=  XMLType(V_AUTH_RESP_XML);
               sAuthResponseNameSpace := ' xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:c="urn:schemas-cybersource-com:transaction-data-1.141"';
 
               v_item_count := 0;
               FOR r IN (
                    SELECT
                           ExtractValue(Value(p),'/c:replyMessage/c:merchantReferenceCode/text()', sAuthResponseNameSpace) as merchantReferenceCode
                    FROM   TABLE(XMLSequence(Extract(xAuthRespXML,'/soap:Envelope/soap:Body/c:replyMessage', sAuthResponseNameSpace))) p
                    ) LOOP
 
                    v_item_count := v_item_count + 1;
                    dbms_output.put_line('CustomerXML Loop Number: ' || v_item_count || '; merchantReferenceCode:' || r.merchantReferenceCode);
 
 
 
               END LOOP;
               dbms_output.put_line('After FOR loop through Customer XML');
 
 
 
 
 
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Exception while inserting Customer Data to Staging table; SQLCODE:' || SQLCODE || '; SQLERRM:' || SQLERRM);
    END;
/
 
 

 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications

XML, PL/SQL, Parser, APEX JSON, Patch

Basic SQLs – RegEx Regular Expression – SQL Split String

 

Business Need:

Oracle Database SQL, SQL PLUS

Split a sting into multiple parts – name, EDI Inputs, Comma Separated Values, Character delimiters, etc.

 

Tables:

Any …

 

SQLs:

Split based on “_”:

SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 1)) FROM DUAL;        -- 4811             - Order Number / Receipt Number
SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 2)) FROM DUAL;        -- ONT / AR - application name
SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 3)) FROM DUAL;        -- 127044    - Payment Order Number
SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 4)) FROM DUAL;        -- 5798526   - Order Header ID
 

“_” can be replaced with COMMA, SPACE, HYPEN, TAB or any other character to split based on the given character.

  

Eg: Consider a name string with “<First> <Middle><Last>” format. Here is the query to get First, Middle and Last names:

select regexp_substr(name, '[^,]+', 1, 1) as lastname,
       regexp_substr(name, '[^ ,]+', 1, 2) as firstname,
       regexp_substr(name, '[^ ,]+', 1, 3) as middle 
 

 

Keywords:

Oracle EBS, R12, R12.2.8, AR, Accounts Receivables, Financials, Oracle Applications, Query, SQL, SQLPLUS, regexp_substr, RegEx

Basic SQLs – Currently Open EBS Forms

 Business Need:

Find who is logged into EBS Forms and have open (non-navigator) forms. Must be run and inform users before bouncing middle tier. Or else they will be kicked out with unsaved data lost.

 

Query:

SELECT USR.USER_NAME, S.SID, S.SERIAL# "SER#", L.PROCESS_SPID "OS PID", S.PROCESS,
   S.OSUSER, RSP.RESPONSIBILITY_NAME RESP_NAME, FRM.USER_FORM_NAME, TO_CHAR(NVL(F.START_TIME, NVL(R.START_TIME, L.START_TIME)), 'MonDD hh24:mi') AS START_TIME,
   VA.NAME "COMMAND", S.STATUS, W.EVENT "WAITING FOR", S.MACHINE
FROM APPS.FND_RESPONSIBILITY_VL RSP, APPS.FND_FORM_VL FRM, APPS.FND_USER USR, APPS.FND_LOGINS L,
   APPS.FND_LOGIN_RESPONSIBILITIES R, APPS.FND_LOGIN_RESP_FORMS F, SYS.V_$SESSION S , SYS.V_$SESSION_WAIT W, SYS.AUDIT_ACTIONS VA
WHERE R.LOGIN_ID = F.LOGIN_ID
AND R.LOGIN_RESP_ID = F.LOGIN_RESP_ID
AND L.LOGIN_ID = R.LOGIN_ID
-- AND L.END_TIME IS NULL
AND R.END_TIME IS NULL
AND F.END_TIME IS NULL
AND L.USER_ID = USR.USER_ID
AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
AND R.RESP_APPL_ID = RSP.APPLICATION_ID
AND F.FORM_ID = FRM.FORM_ID
AND F.FORM_APPL_ID = FRM.APPLICATION_ID
AND F.AUDSID = S.AUDSID
AND S.SID = W.SID
AND VA.ACTION = S.COMMAND
--and S.SID = '1234'
ORDER BY USR.USER_NAME, START_TIME

 
 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Forms, Oracle Applications, Query, SQL, SQLPLUS, Logged Users

Basic SQLs – AR Invoice, Receipt, Receipt Application

 

Business Need:

Oracle EBS Financials

AR – Account Receivables

Analysis, Troubleshooting, Reporting, Bug Fixing, User Tracking , <you name it!>

 

Tables:

RA_CUSTOMER_TRX_ALL TRX – AR Transactions (=Customer Invoices)

AR_CASH_RECEIPTS_ALL RCT – AR Receipts

AR_RECEIVABLE_APPLICATIONS_ALL APP – AR Receipt Applications

 

Receipts have Many-to-Many relationship with Transactions. I.e. a single invoice can be applied with 10 receipts. Also a single receipt can be used against 10 invoices.

 

SQLs:

 
SELECT RCT.RECEIPT_NUMBER, TRX.TRX_NUMBER INVOICE_NUM, TRX.CT_REFERENCE SALES_ORDER, TRX.CUSTOMER_TRX_ID, RCT.CASH_RECEIPT_ID, APP.RECEIVABLE_APPLICATION_ID, APP.AMOUNT_APPLIED, TO_CHAR(TRX.CREATION_DATE,'MonDD hh24mi') TRX_CREATION_DATE, TO_CHAR(RCT.CREATION_DATE,'MonDD hh24mi') RCT_CREATION_DATE
    --,  TRX.*, RCT.*, APP.*
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP, RA_CUSTOMER_TRX_ALL TRX, AR_CASH_RECEIPTS_ALL RCT
WHERE APP.APPLIED_CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND TRX.CT_REFERENCE IN ( '4955')
--AND RCT.CREATION_DATE > SYSDATE-1.02
--AND RCT.RECEIPT_NUMBER = '1000026'
--AND TRX.TRX_NUMBER = '20000151'
--AND RCT.CASH_RECEIPT_ID = 1000128
AND APP.CASH_RECEIPT_ID = RCT.CASH_RECEIPT_ID;
 
 
SELECT RCT.RECEIPT_NUMBER, TRX.TRX_NUMBER INVOICE_NUM, TRX.CT_REFERENCE SALES_ORDER, TRX.CUSTOMER_TRX_ID, RCT.CASH_RECEIPT_ID, TO_CHAR(RCT.CREATION_DATE,'MonDD hh24mi') RCT_CREATION_DATE, TO_CHAR(TRX.CREATION_DATE,'MonDD hh24mi') TRX_CREATION_DATE
    -- ,  TRX.*, RCT.*
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP, RA_CUSTOMER_TRX_ALL TRX, AR_CASH_RECEIPTS_ALL RCT
WHERE APP.APPLIED_CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND RCT.CREATION_DATE > SYSDATE-1.02
--AND RCT.RECEIPT_NUMBER = '1000026'
--AND TRX.TRX_NUMBER = '20000151'
--AND TRX.CT_REFERENCE = '4958'
--AND RCT.CASH_RECEIPT_ID = 1000128
AND APP.CASH_RECEIPT_ID = RCT.CASH_RECEIPT_ID;
 
SELECT ARC.RECEIPT_NUMBER, TRX_NUMBER INVOICE_NUM, CT_REFERENCE SALES_ORDER, RAC.*, ARC.*
  FROM AR_RECEIVABLE_APPLICATIONS_ALL APP,       RA_CUSTOMER_TRX_ALL            RAC,        AR_CASH_RECEIPTS_ALL           ARC
 WHERE APP.APPLIED_CUSTOMER_TRX_ID = RAC.CUSTOMER_TRX_ID
   AND APP.CASH_RECEIPT_ID = ARC.CASH_RECEIPT_ID
   AND ARC.RECEIPT_NUMBER = '1000026';
 
 
-- get order number from TX127 ... Merchant Ref Number
 
SELECT NVL(MAX(RAC.CT_REFERENCE),0) SALES_ORDER
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP, RA_CUSTOMER_TRX_ALL RAC, AR_CASH_RECEIPTS_ALL ARC, IBY_FNDCPT_TX_EXTENSIONS IFTE
WHERE APP.APPLIED_CUSTOMER_TRX_ID = RAC.CUSTOMER_TRX_ID
AND IFTE.ORDER_ID = ARC.RECEIPT_NUMBER
AND IFTE.ORIGIN_APPLICATION_ID = 222
AND IFTE.TRXN_EXTENSION_ID = REGEXP_REPLACE('AR127248', '[^0-9]+', '') 
AND APP.CASH_RECEIPT_ID = ARC.CASH_RECEIPT_ID;
 

Keywords:

Oracle EBS, R12, R12.2.8, AR, Accounts Receivables, Financials, Oracle Applications, Query, SQL