|ORA-01403: no data found in package error while applying for job [message #510261]
||Fri, 03 June 2011 04:59
Registered: June 2008
We are using Oracle E-Business Suite R12 for HRMS and iRecruitment. We recently posted a job on iRecruitment. However, when a user applies for a job, the following error appears.
ORA-01403: no data found in package irc_notification_prefs_swi Procedure create_notification_prefs
We contacted Oracle Support and they told us to compile all the invalid objects in APPS schema using ADADMIN utility. We used that but still we have four invalid view in APPS Schema. They are as follows.
OWNER OBJECT_NAME OBJECT_TYPE
APPS FND_AUDIT_TABLES_AV2 VIEW
APPS FND_AUDIT_GROUPS_AV2 VIEW
APPS FND_AUDIT_GROUPS_AV3 VIEW
APPS FND_AUDIT_SCHEMAS_AV2 VIEW
Upon analysis, following issues were found with these invalid objects.
1. APPS.FND_AUDIT_TABLES_AV2: This view uses a synonym FND_AUDIT_TABLES_A, which is based on a table/view APPLSYS.FND_AUDIT_TABLES_A - This table/view is not found in APPLSYS schema.
2. APPS.FND_AUDIT_GROUPS_AV2: This view uses a synonym FND_AUDIT_GROUPS_A, which is based on a table/view APPLSYS.FND_AUDIT_GROUPS_A - This table/view is not found in APPLSYS schema.
3. APPS.FND_AUDIT_GROUPS_AV3: Same as 2
4. APPS.FND_AUDIT_SCHEMAS_AV2: This view uses a synonym FND_AUDIT_SCHEMAS_A, which is based on a table/view APPLSYS.FND_AUDIT_SCHEMAS_A - This table/view is not found in APPLSYS schema.
I have raised this issue with Oracle Support but they are still unable to give a solution.
I also recompiled the package and package body of irc_notification_prefs_swi and it compiled successfully. However, these 4 views are still invalid and the users are continuously facing the same error when applying for job.
Please tell me whether it's really related to invalid objects? If yes, how can I create the tables that are missing for these views. Or please suggest any other workaround.