Datamart Rollup - Time Facts and Time Summary job failed with the following error message:
"Stored Procedure application error java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error in CMN_JOB_DATAMART_ROLLUP_SP - ORA-20000: Error in NBI_EXTRACT_TF_TS_SP - Determine current fiscal period: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at ".CMN_JOB_DATAMART_ROLLUP_SP", line 11 ORA-06512: at line 1".
Manual alteration of Fiscal time periods (retrospectively) had resulted in an overlapping timeframe where a single "current period" could not be determined by the rollup stored procedure.
The information in the NBI_DIM_FISCAL_TIME table is not getting updated properly. The Fiscal Time Periods entered by the user through the application are correct, however, the NBI_DIM_FISCAL_TIME table contains records with overlapping start and end period dates so when the Datamart executes the query in the stored procedure it is returning more than one record; which is not expected.
Upon reviewing the data between the source table BIZ_COM_PERIODS and the Datamart destination table NBI_DIM_FISCAL_TIME, we see extra rows in the NBI table that are no longer in the source table, thereby considered orphaned and hence are causing the error message because the correct record and the orphaned record is overlapping or has the same start or end dates causing the error message that there are more rows returned than expected.
The NBI_DIM_FISCAL_TIME table needs to be truncated and the Datamart Job re-executed which will re-populate this table with the correct data as seen by the user in the application.