Load Data Warehouse fails with ORA-00904: "LKP"."LANGUAGE_CODE": invalid identifier on DWH_CMN_MVL

Products

Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse (DWH) job fails during a Full Load. Checking the bg-dwh.log or bg-ca.log reveals the following errors:

Couldn't execute SQL: BEGIN DWH_CMN_MVL_LOAD('...',to_date('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss'),'Y'); END;
ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO THE DWH_CMN_MV_LOOKUP table.
SQLERRM : ORA-00904: "LKP"."LANGUAGE_CODE": invalid identifier
ORA-06512: at "PPM_DWH.DWH_CMN_MVL_LOAD",

Environment

Clarity PPM 16.x

Cause

This error occurs when a Multi-Valued Lookup (MVL) query included in the Data Warehouse has an issue with its language configuration—typically having LANGUAGE_ID in its SELECT clause but missing a valid LANGUAGE_CODE.

Even after correcting the lookup query (e.g., by implementing the @WHERE:PARAM:LANGUAGE@ parameter), the DWH job may continue to fail. This happens when multiple attributes share the same lookup and remain actively enabled for the Data Warehouse. Because these other attributes are still tied to the old configuration, the DWH_META_COLUMNS table fails to update with the new query changes, resulting in persistent errors.

Resolution

To force the DWH metadata to update and accept the corrected lookup query, you must cycle the inclusion of all attributes tied to the problematic lookup.

Follow these steps:

Identify and Disable: Identify all attributes across your objects that utilize the problematic lookup.
Uncheck the Include in Data Warehouse flag for all of these attributes in the Clarity UI.
Clear Stale Metadata: Run the Load Data Warehouse job with the Full Load option checked. Ensure this job completes successfully.
Enable Primary Attribute: Go back into the Clarity UI and enable only one of the newly corrected attributes for the Data Warehouse.
Validate (Optional): You can query the DWH_META_COLUMNS table in the database to validate that the new lookup query configuration has registered (you should see language_code and language_id added as columns to the DWH_LKP_ table).
Enable Remaining Attributes: Re-enable the Include in Data Warehouse flag for the rest of the associated attributes.
Final Load: Run the Load Data Warehouse job (Full Load) one final time. The job should now complete successfully without the LANGUAGE_CODE identifier error.

Additional Information

Queries that Can be used :-

1. Identify attributes associated to same lookup.

select object_name,internal_name from odf_custom_attributes where lookup_type='<lookup_type_code>' and IS_DW_ENABLED = 1;

2. Identify which lookup is causing the MVL load to Fail.

WITH mvl_rows AS (
  SELECT DISTINCT
      c.object_code, c.attribute_code,
      c.dwh_table  AS dwh_base_table_name,
      c1.dwh_table AS dwh_disp_table_name,
      CASE WHEN (c.object_code='ODF_AGREEMENT' AND c.attribute_code IN ('PREDECESSORS','SUCCESSORS')) THEN 1
           ELSE CASE WHEN c.dwh_table = c1.dwh_table THEN 0 ELSE 1 END END AS multi_lang,
      c1.lookup_table AS lkp_dim_table_name,
      CASE WHEN c2.dim_key IS NULL THEN t1.dwh_key_col ELSE c2.dim_key END AS lkp_key_column_name
  FROM        dwh_meta_columns c
  INNER JOIN  dwh_meta_columns c1 ON c.object_code = c1.object_code
                                 AND c.attribute_code || '_CAPTION' = c1.attribute_code
  INNER JOIN  dwh_meta_tables  t  ON c.dwh_table = t.dwh_table
  INNER JOIN  dwh_meta_columns c2 ON c.object_code = c2.object_code
                                 AND c2.attribute_code = c.attribute_code || '_CAPTION'
  LEFT OUTER JOIN dwh_meta_tables t1 ON c2.lookup_table = t1.dwh_table
  WHERE c.is_multivalued = 1
)
SELECT
    m.object_code, m.attribute_code,
    m.dwh_base_table_name, m.dwh_disp_table_name,
    m.multi_lang, m.lkp_dim_table_name, m.lkp_key_column_name,
    CASE
      WHEN m.lkp_dim_table_name IS NULL THEN 'NULL_DIM_TABLE'
      WHEN tc.table_name       IS NULL THEN 'DIM_TABLE_DOES_NOT_EXIST'
      WHEN lc.column_name      IS NULL THEN 'MISSING_LANGUAGE_CODE'
      ELSE 'OK'
    END AS diagnosis
FROM mvl_rows m
LEFT JOIN user_tables       tc ON UPPER(tc.table_name)  = UPPER(m.lkp_dim_table_name)
LEFT JOIN user_tab_columns  lc ON UPPER(lc.table_name)  = UPPER(m.lkp_dim_table_name)
                              AND UPPER(lc.column_name) = 'LANGUAGE_CODE'
WHERE m.multi_lang = 1
  AND (m.lkp_dim_table_name IS NULL OR tc.table_name IS NULL OR lc.column_name IS NULL)
ORDER BY m.object_code, m.attribute_code;