ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Incremental Load DWH is failing with error DWH_FIN_TRANSACTION_FACTS_FK3 in Oracle

book

Article ID: 218482

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

Incremental Load Data Warehouse (DWH) is failing when transactions are getting updated with an Oracle database.

STEPS TO REPRODUCE: 

Note: To reproduce this defect you will need to have a large volume of transactions (50+ Million) in the system

  1. Schedule to run the load data warehouse inclemently every 4 hours
  2. During the run, create a process to perform the below steps 

    • Run the below jobs using the process
      • Post Transactions to Financial
      • Post to WIP
      • Import Financial Actuals
    • Once those jobs are completed: Create negative transactions via xog to zero out actuals in the cost plan. For example, if actuals of cost plan for a month is 500 EUR then a negative transaction is created of -500 EUR which is xogged to zero actuals.

    • Run the below jobs again
      • Post Transactions to Financial
      • Post to WIP
      • Import Financial Actuals

Expected Results: The load data warehouse job should finish successfully 

Actual Results: The load data warehouse fails with the below error 

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ERROR ENABLING CONSTRAINT. SQLERRM : ORA-02298: cannot validate (CLARITYDWHP.DWH_FIN_TRANSACTION_FACTS_FK3) - parent keys not found
ORA-06512: at "CLARITYDWHP.CMN_ENABLE_CONSTRAINTS_SP", line 27
ORA-06512: at line 2

Cause

This is logged as defect DE61541

Environment

Release :  15.8, 15.8.1,15.9,15.9.1,15.9.2 

Component : CLARITY DATA WAREHOUSE

Resolution

This defect is fixed in 15.9.3 

Workaround:

  • Set the process to be run via job as incompatible to load data warehouse job 
  • After failure, ensure you run the full load data warehouse job
  • Replace the attached stored procedure DWH_FIN_TRAN_FACTS_LOAD which has the fix for version 15.8 and above for Oracle Database only.  

Additional Information

Reference also:

Attachments

1626954402377__DWH_FIN_TRAN_FACTS_LOAD.sql get_app