Error : APP-FND-01926: The custom event WHEN-LOGON-CHANGED raised unhandled exception: ORA-06502: PL

In this Document

Symptoms

Cause

Solution

---

APPLIES TO:

Oracle Application Object Library - Version 11.5.9 to 12.1.3 [Release 11.5 to 12.1]
Oracle Applications Technology Stack - Version 11.5.9 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.

SYMPTOMS

Problem Description:
-----------------------
When attempting to launch forms the following error occurs.

APP-FND-01926: The custom event WHEN-LOGON-CHANGED raised unhandled exception: ORA-06502: PL/SQL: numeric or value error

CAUSE

Site level profile option value for profile option: FND: Debug Log Level was incorrect. It was showing as 'Unexpected' in database where as it should show the numeric value 6 instead of 'Unexpected'. Once we enable level 12 trace for database then this cause of error can be determined. Profile option value can be checked using below sql.
 

SQL> SELECT SUBSTR(e.profile_option_name,1,25) 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,SUBSTR(a.last_update_date,1,25) UPDATED_DATE
 2 FROM fnd_profile_option_values a, fnd_responsibility_tl b, fnd_application c,fnd_user d, fnd_profile_options e
 3 WHERE e.profile_option_name = 'AFLOG_LEVEL'
 4 AND e.profile_option_id = a.profile_option_id AND a.level_value = b.responsibility_id (+)
 5 AND a.level_value = c.application_id (+) AND a.level_value = d.user_id (+)
 6 ORDER BY profile_option_name;

PROFILE                   L               LVALUE               VALUE                              UPDATED_DATE 
------------------------- -----------     -------------        ------------------                 ---------------------------------
AFLOG_LEVEL               Site            Site                 Unexpected                         Mon DD YYYY HH:MM:SS

 Profile option: FND: Debug Log Level should be set correctly. It should not null or should not be showing varchar value in database. It should show the numeric value and this is the reason of error "ORA-06502: PL/SQL: numeric or value error". Default value for this profile option is 'Unexpected' but when it saved in database then it will be save in numeric value which corresponds to 6.

The main cause of the reported error is corrupt or invalid CUSTOM.pll. First we should be making sure that CUSTOM.pll used is able to generate CUSTOM.plx successfully.

For 11i use the below command to generate the CUSTOM.plx from CUSTOM.pll :

f60gen \
module=$AU_TOP/resource/CUSTOM.pll \
userid=APPS/xxx \
output_file=$AU_TOP/resource/CUSTOM.plx \
module_type=library batch=yes compile_all=special

 and For R12.0.x and R12.1.x use the below command :

frmcmp_batch \
module=$AU_TOP/resource/CUSTOM.pll \
userid=APPS/xxx \
output_file=$AU_TOP/resource/CUSTOM.plx \
module_type=library batch=yes compile_all=special

 If there is any error during generation of CUSTOM.plx then it should be rectified first before checking the profile option value explained above. CUSTOM.pll should be correct one and should able to generate CUSTOM.plx as it is being read during launching of forms. If there will be any uncompiled packages written inside CUSTOM.pll then it will not read correctly during forms launch and will end in error.

 

SOLUTION

1. Update the profile option value of profile option: FND: Debug Log Level from Unexpected to 6 using below SQL commands.

Connect to SQLPLUS as APPS User :

SQL> create table fnd_profile_option_values_bkup as select * from fnd_profile_option_values;

SQL> select profile_option_id from fnd_profile_options where profile_option_name='AFLOG_LEVEL';

Take the Profile Option ID.

SQL> select * from fnd_profile_option_values where profile_option_id=3099;

SQL> update fnd_profile_option_values set PROFILE_OPTION_VALUE='6' where profile_option_id=3099 and level_id=10001;

SQL> commit;

SQL> SELECT SUBSTR(e.profile_option_name,1,25) 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,SUBSTR(a.last_update_date,1,25) UPDATED_DATE
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 = 'AFLOG_LEVEL'
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 (+)
ORDER BY profile_option_name;

It should show now the correct profile option value as 6. 

2. Stop all appsTier services.

3. 'Regenerate Form Files' using adadmin utility. It should be successful.

4. Start all appsTier services.

5. Retest the issue.