Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, August 7, 2008

How to find out the file through which an Apps standard package is created

Veera Boda pinged me today. He gave me this error which was coming during adconfig:

Cause: afpgdbcf failed due to ORA-04063: package body "APPS.FND_PROFILE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APPS.FND_PROFILE"
ORA-06512: at "APPS.FND_WEB_CONFIG", line 269
ORA-06512: at "APPS.FND_SECURITY_PKG", line 350

He said that he had followed Metalink Note 178001.1 to drop obsolete objects. On checking 178001.1, I found that it was related to 11.5.2 and advised running a script $FND_TOP/admin/sql/fnddold.sql which dropped obsolete objects. Apparently that script also dropped some objects like FND_PROFILE and FND_USER_PKG. Boda wanted to know how he could create those dropped objects. I thought of Electronic Technical Reference Manual available thru which requires metalink login. However etrm has definitions of objects starting from 11.5.7. They don't have 11.5.2 objects. Then I thought of an easy way which I had discovered for fixing invalid objects. If you query a package's source code, you can find the line which has the version information. The version information line has the name of the file through which the package was created. For example:

select line,text from dba_source where object_name='FND_USER_PKG' and line < style="font-weight: bold;">/* $Header: AFSCUSRB.pls 115.150 2007/06/01 16:37:07 fskinner ship $ */

From the above query I come to know that AFSCUSRB.pls file created the package body FND_USER_PKG. The naming convention of the files is like this:

If the file's primary name ends with an S then it creates the package specification.
If the file's primary name ends with a B then it creates the package body.

For example AFSCUSRS.pls creates package specification of FND_USER_PKG and AFSCUSRB.pls creates the package body.

After tracing the files which created the dropped packages, Boda was able to recreate all of them and restore normalcy to his instance.

No comments: