Thursday, August 16, 2012

APP-FND-01564: ORACLE error 942 in afpoload

We are upgrading Oracle applications from Rel 11.5.9 to Rel 12.1.3. I was assigned a task to create a script that can do the following

  1. End date all Oracle Applications users(excluding few critical users) who have any custom responsibility. (All custom responsibilities started with prefix that is not used by any other seeded responsibility.)
  2. Remove end date for all users updated in previous run of the script

This script was needed so that at start of upgrade, no users except IT folks could login to system and when time is right, enable those users.

While testing, i added one additional condition to select only few users to query that selected list of users for update of END_DATE column. In this way i could test enable/disable of few users and fix any issue with the script.

The script worked well. i was very confident, when i migrated this script to system integration test environment, i removed the additional condition used only for testing in development. This script was run in system integration test environment, script worked without errors but IT Users who were not End Dated could not login to system and they got following error.

APP-FND-01564: ORACLE error 942 in afpoload

Cause: afpoload failed due to ORA-00942: table or view does not exist.

I and Gino Impullitti immediately searched the web to see what happened. we found following links


The Link1 gave Gino clue that it has something to do with GUEST user. I had never paid any attention to Guest user. We checked GUEST user and it was end dated. We removed End date from GUEST user and we could log back in.

Why GUEST user was end dated?
Because my query to select users for end dating selected all active users instead of only those who have custom responsibility. In this way GUEST user, who does not have any custom responsibility, too got selected. I modified my script and added a condition to filter out users without custom responsibility and script worked well in production.

Lesson -> Never End Date GUEST user.

No comments:

Post a Comment