SELECT CASE STATUS_CODE WHEN 'FAILED' THEN 1 ELSE 0 END hasjobfailed FROM
( SELECT STATUS_CODE, END_DATE FROM
(
SELECT R.STATUS_CODE ,R.END_DATE
FROM CMN_SCH_JOB_RUNS R, CMN_SCH_JOBS J, CMN_SCH_JOB_DEFINITIONS D
WHERE R.JOB_ID = j.ID
AND J.JOB_DEFINITION_ID = D.ID
AND D.JOB_CODE = 'DWH_ETL_MASTER'
AND D.IS_ACTIVE = 1
AND R.STATUS_CODE IN ('FAILED', 'COMPLETED')
UNION ALL
SELECT J.STATUS_CODE, J.END_DATE
FROM CMN_SCH_JOBS J, CMN_SCH_JOB_DEFINITIONS D
WHERE J.JOB_DEFINITION_ID = D.ID
AND D.JOB_CODE = 'DWH_ETL_MASTER'
AND D.IS_ACTIVE = 1
AND J.STATUS_CODE IN ('FAILED', 'COMPLETED')
) JOBS ORDER BY JOBS.END_DATE DESC
) WHERE ROWNUM= 1
If it returns 0, you can continue with the upgrade.
If it returns 1, you might be hitting a known defect: DE43502 Upgrade checkinstall fails on check-dwh-load-job-success.xml (Data Warehouse successful run script has to be adjusted) - Oracle
With this defect there is an old job entry with END_DATE = NULL which causes the issue.
Workaround for DE43502 :
1. Identify the job that causes the issue by running:
SELECT R.ID, R.STATUS_CODE ,R.END_DATE
FROM CMN_SCH_JOB_RUNS R, CMN_SCH_JOBS J, CMN_SCH_JOB_DEFINITIONS D
WHERE R.JOB_ID = j.ID
AND J.JOB_DEFINITION_ID = D.ID
AND D.JOB_CODE = 'DWH_ETL_MASTER'
AND D.IS_ACTIVE = 1
AND R.STATUS_CODE IN ('FAILED', 'COMPLETED')
and R.END_DATE is NULL
Option 1:
Connect to CA PPM UI - Home - Jobs - Log
Find and delete the failed job entry
Option 2:
Update the END_DATE returned by the query above with a corresponding END_DATE:
update CMN_SCH_JOB_RUNS set end_date =start_date where id =<the R.ID returned by the query above>
commit
2. Re-run the query, ensure it does not return any null records anymore
3. Resume the upgrade