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'''
Applies to all supported PAS environments for PPM 14.2 and higher.
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).
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