Load Data Warehouse Job Fails with error message DWH_INV_STATUS_REPORT is not existing in database

Document ID : KB000046080
Last Modified Date : 14/02/2018
Show Technical Document Details

Issue:

The Load Data Warehouse Job fails with an error messagethat  DWH_INV_STATUS_REPORT does not exist in the database.

Full error message below:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist ORA-06512: at "PPM_DWH.DWH_CFG_ADDIN_EXTRAS_SP", line 24 

DWH_INV_STATUS_REPORT is not existing in database 

EXECUTE IMMEDIATE 'CREATE VIEW DWH_INV_STATUS_REPORT_LATEST_V AS SELECT I.INVESTMENT_KEY, R.CLARITY_STATUS_REPORT_KEY STATUS_REPORT_KEY, ' || 
'ROW_NUMBER() OVER (PARTITION BY R.INVESTMENT_KEY ORDER BY R.REPORT_DATE DESC, R.CLARITY_STATUS_REPORT_KEY DESC) REPORT_ORDER ' || 
'FROM DWH_INV_INVESTMENT I INNER JOIN DWH_INV_STATUS_REPORT R ON I.INVESTMENT_KEY = R.INVESTMENT_KEY ' || 
'WHERE NVL(R.SR_REPORT_STATUS_KEY,''FINAL'') = ''FINAL'''

Environment:

Applies to all supported PAS environments for PPM 14.2 and higher.
 
Cause: 
 
The Status Report object must be included in the Data Warehouse (at least during the first successful run of the Load Data Warehouse job in the environment). 
 

Resolution: 

Include the Status Report in the Data Warehouse using  the below steps: 

1. In PPM go to Administration->Objects->Status Report 
2. Check the option 'Include in the Data Warehouse' and click 'Save '
3. Rerun the Load Data Warehouse job in full mode