Load Data Warehouse job will fail if baseline is updated by OWB on the ahead of time zone from PPM.

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

If a baseline is updated in OWB where OWB time zone is ahead of clarity time zone, so the baseline updated date shows in the future compared to Clarity, Load Data Warehouse job - Full Load will create orphan record in DWH_INV_PROJECT_LN table. After that, the subsequent run of Load Data Warehouse job will fail for a long hours with below error messages.

Couldn't execute SQL: ALTER TABLE DWH_INV_PROJECT_LN ENABLE VALIDATE CONSTRAINT DWH_INV_PROJECT_LN_FK1
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (XXXXXXX.DWH_INV_PROJECT_LN_FK1) - parent keys not found ...

Steps to Reproduce:

1. Configure CA PPM with Data Warehouse and app/db on same date/time/timezone
2. Ensure Load Data Warehouse runs fine with no errors
3. Now prepare a separate server with OWB installed
4. Check the app/db time and set the server time for OWB server to be AHEAD in time.
   For example, the time difference should be 16 hours ahead (PST to JST).
   In those cases the date will be sometimes on the next day.
5. Now restart OWB and open a project from CA PPM. Go to Project Tab.
6. Select Scope Project, Baseline Settings - Set, click OK
7. When prompted if you would like to continue, confirm with Yes
8. Now check in PRJ_BASELINE_DETAILS for the same project (example query below, replace the internal id with the correct one):
   select LAST_UPDATED_DATE
from PRJ_BASELINE_DETAILS
where object_type ='PROJECT'
and object_id =XXXXXXXX
9. Note the time saved there is the actual time in future, which is set on the OWB client server
10. Now run Load Data Warehouse job - Full Load

Expected result: The project to be brought in Data Warehouse investments successfully

Actual result: Load Data Warehouse job fails, with error: 

ORA-02298: cannot validate (XXXXXXX.DWH_INV_PROJECT_LN_FK1) - parent keys not found.

Any subsequent runs will fail too, until the future date from step 9 is reached.

Cause

DE33583 (CLRT-80691)

Resolution

Fixed in 15.4.