Cost Plan Data Missing When DWH Cost Plan Settings Are Disabled

Products

• Clarity PPM SaaS

Issue/Introduction

Cost plan actuals are underreported in the Data Warehouse (DWH) compared to Clarity database
when the cost plan settings are disabled. 

Steps To Reproduce:

• Navigate to System Options > DWH Options
• Remove "Include only financial Plan of Record". Click Save
• Set enable_plan_actuals to disabled (as per article Data Warehouse Enable Plan Actuals Toggle for Financial Period Plan Facts)
• Run the full Load DWH job.
• Compare actual cost totals between the Clarity and DWH databases using the diagnostic queries referenced in Additional Information 

Expected:
DWH actual cost totals should match Clarity actual cost totals, regardless of settings above being disabled.

Actual: 
The full Load DWH job runs indefinitely, or until the 600-minute/10-hour default timeout.
Cost plan actuals end up underreported in the DWH relative to the Clarity database.

Environment

16.4.3

Cause

DE211491

Resolution

This will be under Engineering review.

Additional Information

Data Warehouse Enable Plan Actuals Toggle for Financial Period Plan Facts

Queries used:

--Confirm both settings are disabled:
SELECT fin_plans_por_only, enable_plan_actuals FROM dwh_settings;

--Get actual cost (Clarity db)
SELECT
    SUM(wv.totalcost) AS actual_cost,
    TO_CHAR(MIN(w.transdate), 'YYYYMMDD') AS min_date
FROM ppa_wip w
INNER JOIN ppa_wip_values wv
    ON wv.transno = w.transno
WHERE wv.currency_type = 'HOME'
  AND w.status = 0;

--Get actual cost DWH db
SELECT
    SUM(TXN_FACT.TRANSACTION_ACTUAL_COST)::float ACTUAL_COST,
    TO_CHAR(MIN(PRD.YEAR_START_DATE), 'YYYYMMDD') MIN_DATE
FROM DWH_FIN_TRANSACTION TXN
INNER JOIN DWH_FIN_TRANSACTION_FACTS TXN_FACT ON TXN.TRANSACTION_KEY = TXN_FACT.TRANSACTION_KEY
INNER JOIN DWH_CMN_PERIOD PRD ON PRD.PERIOD_KEY = TXN_FACT.PERIOD_KEY
WHERE 1 = 1
  AND TXN.TRANSACTION_STATUS_FLAG = 0;