How to find the error when nothing is logged in the logs in R12.2?

Dear Brothers/Sisters,

We were getting these error in few of the pages in AP module and none of the notification worked due to this error.

"You have encountered an unexpected error. Please contact the System Administrator for assistance"

There is an another similar kinda issue i have blogged aboutm

Please read : EBS login page is not working   Few users unable to log into R12


Troubleshooting is very simple technique when you find out the error. All you have to know is where to find that one. Once you found the cause of the trouble, fixing is simple thing.

Main goal of this post to share my ideology to narrow the issue and how we can trace the error stack in OAF page when we have no clue.

I did the below the moment i noticed the error :

- Apache, oacore, oafm, forms logs
- Alert log in DB
- Mount point sizes in app and db server.

Everything seems to be normal and i had to clue to figure out the cause. So i had to trace one of the page as all the ways are closed. Let me summarize the events i had to figure out the error.

- Set FND: Diagnosticts profile option set to yes to user
- Reproduce the error

After i enabled debug option to my user, i could figure at least what was cause of the error.  I saw the additional options in the error page. When i clicked exception detail i can see error stack.

- See about this page or exception detail to find the error. I have highlighted the error in yellow.



Error Stack:

## Detail 0 ## java.sql.SQLException: ORA-20001: SQL_PLSQL_ERROR: N, ROUTINE, MO_GLOBAL.INIT, N, ERRNO, -20001, N, REASON, ORA-20001: APP-FND-02902: Multi-Org profile option is required. Please set either MO: Security Profile or MO: Operating Unit profile option. ORA-06512: at "APPS.PO_DOCUMENT_TOTALS_PVT", line 475 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) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:213) at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1111) at

- This error is specific to our env and cause of the error is one of the profile option (MO: Security profile). Profile option had been set to null value recently due to which few modules went into error. After fixing it, everything became normal. 

How i did?

-  First thing error was in production. 

-  I tried to replicate the error in recent clone which was cloned 3 days ago. Luckily i had the same error

- We had 2 more dev instance left which was not cloned for  a while. I was able to see error pages were working fine when i tried to replicate the error.

- So quickly i looked for profile option value to see what value it had. 

- Error disappeared after i set the same value in production. 

To implement the solution, please execute the following steps: 

1. Go into the responsibility: System Administrator 

2. Navigate to Profiles > System > check Site option 

3. Be sure that either profile for Multi-Org Access Control 'MO: Security Profile' OR 'MO: Operating Unit' is set appropriately. 

4. Retest the issue 

*** Value presented in the profile option was specific to our env. Please make sure with the consultants before you set this value in production***

Main goal of this post to share my ideology to narrow the issue and how we can trace the error stack in OAF page when we have no clue.

We can trace the error from the database as well. Please do the following to figure out the cause from tables.  

A. 

To Enable FND debug: 

Navigate Responsibility: System Administrator > Profile => System 

a. Find the FND profile options for User who is submitting the process to debug. 

User: User submitting the process 

Profile: FND:%Debug% 

b. Set the following Profile values at the user level 

FND: Debug Log Enabled :Yes 

FND: Debug Log Filename : NULL 

FND: Debug Log Level : STATEMENT 

FND: Debug Log Module : % 

c. Save. 

B. 

Get the current log sequence in the FND table. 

select max(log_sequence) from fnd_log_messages ; -- start 

C. 

Reproduce the issue 

D. Get the sequence in the FND log 

select max(log_sequence) from fnd_log_messages ; -- end 

E. Spool the output of the following query to an excel file which gives FND debug log: 

Run this script and replace start and end with the sequences you obtained from steps B and D. 

select module, message_text 

from fnd_log_messages 

where log_sequence between <start> and <end>;

Thanks!
~Sikky