Error worksheet can't be shown After Removing All Aggregation Levels In A Worksheet

(Doc ID 1921042.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Demantra Real-Time Sales and Operations Planning - Version 12.2.3 and later
Information in this document applies to any platform.

Symptoms

Customer is on 12.2.3 and mentions that in a worksheet of they go to a worksheet and remove all the levels from the Aggregation tab, they get an error message :
Error worksheet can't be shown. Please contact an administrator to review system log files for details.

Found that if a user has a filter attached to the user in the user definition( Business Modeler--> Security--Create/modify user) and if that user removes all the levels from the aggregation they get the message

If they have say 5 levels selected in aggregation, the error only happens when the last of the filter is removed. If they remove say 1 or 2 filter the issue does not occur.

At that point the java console shows

2014-06-19 10:29:18,670 PDT http://thread applet-com.demantra.partner.client.main.PartnerApplet.class-2 INFO tunnel.general :: ODPM-50016: No proxy selected
2014-06-19 10:29:28,876 PDT Keep_Session_Alive_Thread INFO dpweb.connection :: ODPM-50002: Connectivity status is: 'Connected'
2014-06-19 10:29:31,745 PDT Get_Meta_Data_Thread INFO dpweb.query.data :: ODPM-50008: Note: Query 02-PLM Update Forecast loaded, contains 2896 combinations
2014-06-19 10:29:41,337 PDT DataLoaderThread WARN dpweb.iconLoader :: ODPM-40329: The URL http://localhost:8080/demantra/portal/icons/ep_image.gif is unreachable.
2014-06-19 10:30:01,467 PDT Get_Meta_Data_Thread ERROR dpweb.query.data :: Error: Worksheet data can't be shown. Please contact an administrator to review system log files for details.
com.demantra.applicationServer.appServerExceptions.ApplicationServerException: java.sql.SQLSyntaxErrorException: ORA-00904: "MDP_MATRIX"."T_EP_XXPLCM_LVL3_EP_ID": invalid identifier

at com.demantra.applicationServer.services.queryrun.QueryDataHandler.setQueryData(QueryDataHandler.java:292)
at com.demantra.applicationServer.services.queryrun.QueryDataHandler.setWorksheetData(QueryDataHandler.java:269)
at com.demantra.applicationServer.services.queryrun.BaseQueryDataHandler.loadQueryData(BaseQueryDataHandler.java:140)
at com.demantra.applicationServer.services.queryrun.QueryDataHandler.loadQueryData(QueryDataHandler.java:148)
at com.demantra.applicationServer.services.queryrun.BaseDataRunner.loadQueryData(BaseDataRunner.java:85)
at com.demantra.applicationServer.services.queryrun.BaseDataRunner.setData(BaseDataRunner.java:70)
at com.demantra.applicationServer.services.queryrun.ExtendedDataRunner.run(ExtendedDataRunner.java:107)
at java.lang.Thread.run(Thread.java:680)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "MDP_MATRIX"."T_EP_XXPLCM_LVL3_EP_ID": invalid identifier

The following aggregation levels are removed

Product Model
Sales Region
Customer type
Customer
Fiscal Quarter

And then rerun the worksheet, they get

Error: Worksheet data can't be shown. Please contact an administrator to review system log files for details


collaborator_log_20140619-1029.txt

0 END) AS IS_XXPLCM_OPPTY FROM SALES_DATA BRANCH_DATA ,ITEMS
,T_DATE_13327_558_954573528 T_DATES_LIST WHERE BRANCH_DATA.SALES_DATE =
T_DATES_LIST.SALES_DATE AND BRANCH_DATA.ITEM_ID = ITEMS.ITEM_ID AND
ITEMS.IS_LINKED = 1 AND ITEMS.T_EP_XXPLCM_LVL3_EP_ID IN (3,2) GROUP BY
T_DATES_LIST.FROM_SALES_DATE ORDER BY SDATE 2014-06-19 10:30:46,855 PDT
DataRunner_39 ERROR appserver.thread: java.sql.SQLSyntaxErrorException:
ORA-00904: "MDP_MATRIX"."T_EP_XXPLCM_LVL3_EP_ID": invalid identifier at
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) at
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at
oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) at
oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) at
oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) at
oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)

None of the series defined in the worksheet has any extra from or extra where configured
The mdp_matrix table has the column T_EP_XXPLCM_LVL3_EP_ID



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms