Reporting Data Provider: ORA-00936: missing expression when displaying per-period data

Products

• Clarity PPM On Premise

Issue/Introduction

In an environment where the NIKU and DWH databases are hosted on separate Oracle server instances connected via a database link, attempting to display per-period data (such as ETC, actuals) triggers an error.

Steps to Reproduce: 

1. Set up Clarity Schema and Data warehouse schema in different database
2. Ensure configuration of Data warehouse settings are filled under Classic UX--> Administration --> System Options --> Data warehouse options
   • Language
   • Entity For Fiscal Period
   • Include SQL Curve Data
   • Enable Instantaneous Sync
   • Include Weekly Period Data
   • Include Monthly Period Data
   • Include Fiscal Period Data
   • Consistent Fiscal Periods across Entities
   • Include only financial Plan of Record
3. Set the Unit of Measure to either Hours, FTE, Days, or % Availability within the New UI user profile settings.
4. Run the Load Data Warehouse job and confirm it completes successfully.
5. Perform either of the following actions:
   • Action A (MUX Reporting): Navigate to MUX --> Reporting --> Data Provider, make a copy of the Investments Data Provider, go to the Preview Tab, add the Name column, and then add a per-period curve column like ETC or Actuals.
   • Action B (OOTB Reports): Access out-of-the-box Advanced Reporting samples such as 'Capacity vs. Demand by Resource (Sample)' or 'Financial Budget vs. Forecast by Period (Sample)'.

Expected Results:

ETC data should be displayed

Actual Result:

The system fails to process the request, returning errors based on the selected Unit of Measure:

• With Unit of Measure as Hours/FTE/Days
  • Error: Unable to process the request: ORA-00936: missing expression
• With Unit of Measure as %Availability
  • Error: Unable to process the request: ORA-04063: package body "DWH_CRV_SQL_CURVE_PKG" has errors ORA-06508: PL/SQL: could not find program unit being called: "DWH_CRV_SQL_CURVE_PKG" ORA-06512: at "DWH_CRV_CALENDAR_FCT", line 7 ORA-06512: at line 1
• OOTB Reports Execution: The application throws a screen error: "Report time out. please add more filters and try again".

Environment

v16.4.1 Patch, v16.4.2

Cause

Due to DE200878

Resolution

Engineering is reviewing DE200878