R12: Diagnostic Tests Are Not Available or "A few tests might be corrupted

Symptoms:

After upgrade to R12.1.2, there are not test available to execute.
or

After upgrade to R12.1.3, you will received the following error:


"A few tests might be corrupted.Those tests will not be visible in the HGrid.Please see logs for more details "


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. login as sysadmin ==> Select Application Diagnostics responsibility > click on Diagnose

Changes:

Upgrade to R12.1.X

Cause:

Concurrent program, "Workflow Role Hierarchy Propagation" failed when it was ran.

The output of the concurrent program has errors like  :

[main][9:53:17:179, 6/15/10] Running oracle.apps.fnd.oam.diagnostics.cp.MigrateCustomizationData
[main][9:53:17:180, 6/15/10] $Header: MigrateCustomizationData.java 120.0.12010000.4 2009/06/30 11:40:28 sramados noship $
[9:53:17:244, 6/15/10] oracle.apps.ont.diag.ONTCreditCheck.runTest(oracle.apps.fnd.oam.diagnostics.exec.ExecutionContext)
[9:53:17:439, 6/15/10] Test type xml file location = /u02/oracle/PROD/apps/apps_st/comn/java/classes/oracle/apps/fnd/oam/diagnostics/config/fnddiagTestTypes.xml
[main][9:53:17:453, 6/15/10] ONT:Credit Check:oracle.apps.ont.diag.ONTCreditCheck Test type xml is not available at the expected location. Please refer log for more details.
[9:53:17:457, 6/15/10] oracle.apps.asf.diagnostics.CheckCustTAPSetup
[main][9:53:17:457, 6/15/10] AST:Customer:oracle.apps.asf.diagnostics.CheckCustTAPSetup The executable for the test is not available
[9:53:17:460, 6/15/10] oracle.apps.asf.diagnostics.MulticurrencySetup
[main][9:53:17:460, 6/15/10] AST:General:oracle.apps.asf.diagnostics.MulticurrencySetup The executable for the test is not available
.

Solution:

1.- Run the following query:

select EXTRACTVALUE(XMLType(text),'//JAVA_TOP') , name, node_name
from fnd_oam_context_files
where name not in ('TEMPLATE','METADATA')
and (status is null or status != 'H')
and CTX_TYPE = 'A'
order by LAST_SYNCHRONIZED DESC;

output:

EXTRACTVALUE(XMLTYPE(TEXT),'// NAME NODE_NAME
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------
/u03/oracle/DAILY/apps/apps_st/comn/java/classes DAILY_oebs oebs
/u02/oracle/PROD/apps/apps_st/comn/java/classes PROD_oebs oebs

This query only should return the correct value for the JAVA_TOP, in this case (the first row, this is the correct JAVA_TOP env) , the other/s rows need to be update as point 2.

2. Update all the others rows that not point to the correct JAVA_TOP:
update fnd_oam_context_files
set status='H'
where EXTRACTVALUE(XMLType(text),'//JAVA_TOP')='/u02/oracle/PROD/apps/apps_st/comn/java/classes';

3.
- Rerun "Diagnostics patching CP" DIAGPATCHINGCP Concurrent Program
- Bounce the Middle Tier
- Retry Diagnostics