Data Warehouse Job Incremental Load failing Frequently Couldn't execute SQL: CALL DWH_FIN_PLAN_PER_FACTS_LOAD

Products

• Clarity PPM SaaS

Issue/Introduction

When an incremental Load Data Warehouse job is run after a financial time slice rollover occurs the job fails. 

This is due to the forecast amounts (for the previous and current fiscal period) being updated. 

  

STEPS TO REPRODUCE:

1. Review the Time Slices and observe the Financial Time Slices rollover to the next period 

2. Run the Load Data Warehouse job without selecting the 'Full Load' so that it runs incrementally 

Expected Results: The job to complete successfully.

Actual Results: The job intermittently fails with the following error:

2020/09/01 00:17:42 - set variables - oracle - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry : 2020/09/01 00:17:42 - set variables - oracle - Couldn't execute SQL: CALL DWH_FIN_PLAN_PER_FACTS_LOAD(P_LAST_LOAD_DATE => to_timestamp('2020/08/31 20:00:27', 'yyyy/MM/dd HH24:mi:ss')::timestamp, P_CURRENT_DIM_LOAD_DATE => to_timestamp('2020/09/01 00:00:26', 'yyyy/mm/dd HH24:MI:SS')::timestamp); 2020/09/01 00:17:42 - set variables - oracle - 2020/09/01 00:17:42 - set variables - oracle - ERROR: ENCOUNTERED EXCEPTION WHILE MERGING INTO DWH_FIN_PLAN_PERIOD_FACTS. column "plan_fcst_units_var" is of type numeric but expression is of type timestamp without time zone 2020/09/01 00:17:42 - set variables - oracle - Where: PL/pgSQL function dwh_fin_plan_per_facts_load(timestamp without time zone,timestamp without time zone) line 174 at RAISE null

Workaround: Run a Full Load Data Warehouse job.

Environment

Release : SAAS

Component : CA PPM SAAS TIME SLICING

Resolution

DE58320, Fixed in 15.9