Editable Series Values Not Getting Copied To All Weeks Of The Event When Event Duration (Doc ID 1904402.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Demantra Predictive Trade Planning - Version 12.2.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.3 version, Worksheet

When attempting to copy/paste a series with "most common"
the following error occurs.

ERROR
-----------------------
ERROR update.sql: ODPM-70125: Failed to create temp update table. Update Data: Id: 1398316821389, User: venkat, WS: -1, Update Table: PROMOTION_DATA, Combination: L478M2L477M8L359M57L453M1L464M8570L426M6106
SELECT PROMOTION_DATA.ITEM_ID ,PROMOTION_DATA.LOCATION_ID ,PROMOTION_DATA.SALES_DATE ,PROMOTION_DATA.PROMOTION_ID ,1.0 AS UNIT ,MAX(ROUND(MDP_MATRIX.GLOB_PROP, 10) * NVL(PROMOTION_DATA.PERIOD_ALLOC,INPUTS.PERIOD_ALLOC)) AS GLOB_PROP ,SUM(NVL(PROMOTION_DATA.PERIOD_ALLOC,INPUTS.PERIOD_ALLOC)) AS PERIOD_ALLOC ,SUM(DECODE(PROMOTION_DATA.CBI_EVENT_TYPE_ID, 10, DECODE(SIGN((NVL(PROMOTION_DATA.CBI_DEP_ACT_PD,0)*(NVL(PROMOTION_DATA.CBI_ACTUALS_FLAG,1))) - NVL(PROMOTION_DATA.CBI_EVT_TTL_OVERRIDE,NVL(PROMOTION_DATA.VOLUME_BASE_TTL,0))),1,NVL(PROMOTION_DATA.CBI_DEP_ACT_PD,0),0,NVL(PROMOTION_DATA.CBI_DEP_ACT_PD,0),NVL(PROMOTION_DATA.CBI_EVT_TTL_OVERRIDE,NVL(PROMOTION_DATA.VOLUME_BASE_TTL,0))),DECODE(SIGN(PROMOTION_DATA.SALES_DATE - TO_DATE(GET_MAX_DATE, 'MM/DD/YYYY HH24:MI:SS')),-1, DECODE(PROMOTION_DATA.CBI_ACTUALS_FLAG,0,NVL(PROMOTION_DATA.CBI_EVT_TTL_OVERRIDE,NVL(PROMOTION_DATA.VOLUME_BASE_TTL,0)),NVL(PROMOTION_DATA.CBI_DEP_ACT_PD,0)), NVL(PROMOTION_DATA.CBI_EVT_TTL_OVERRIDE,NVL(PROMOTION_DATA.VOLUME_BASE_TTL,0))))) AS cbi_final_vol ,SUM(NVL(PROMOTION_DATA.BB_AMT,0)) AS bb_amt ,TO_NUMBER(NULL,0) AS volume_incr ,TO_NUMBER(NULL,0) AS c_pred ,SUM(NVL(PROMOTION_DATA.OI_AMT,0)) AS oi_amt FROM PROMOTION_DATA ,PROMOTION_MATRIX ,MDP_MATRIX ,INPUTS WHERE PROMOTION_MATRIX.PROMOTION_ID = 203650 AND PROMOTION_MATRIX.ITEM_ID = MDP_MATRIX.ITEM_ID AND PROMOTION_MATRIX.LOCATION_ID = MDP_MATRIX.LOCATION_ID AND MDP_MATRIX.T_EP_E1_CUST_CAT_4_EP_ID = 2 AND MDP_MATRIX.T_EP_E1_CUST_CAT_2_EP_ID = 8 AND MDP_MATRIX.T_EP_I_ATT_7_EP_ID = 57 AND MDP_MATRIX.T_EP_E1_IT_BR_CAT_1_EP_ID = 1 AND MDP_MATRIX.T_EP_E1_IT_BR_CAT_2_EP_ID = 8570 AND MDP_MATRIX.T_EP_SITE_EP_ID = 6106 AND PROMOTION_DATA.SALES_DATE = INPUTS.DATET AND ((PROMOTION_DATA.SALES_DATE >= TO_DATE(?,'mm-dd-yyyy') AND PROMOTION_DATA.SALES_DATE = TO_DATE(?,'mm-dd-yyyy') AND PROMOTION_DATA.SALES_DATE 2014-05-01 13:56:28,265 EST [Update_7477]
ERROR update.sql: com.demantra.applicationServer.appServerExceptions.ApplicationServerException: java.sql.SQLException: Invalid column index at
com.demantra.applicationServer.services.DBServicesCommon.populateTableAs(DBServicesCommon.java:2019) at
com.demantra.applicationServer.services.DBServices.populateTableAs(DBServices.java:937) at
com.demantra.applicationServer.services.tempTable.TempTable.setTable(TempTable.java:405) at
com.demantra.applicationServer.nupdate.process.TempUpdateTableHandler.createTempUpdateTable(TempUpdateTableHandler.java:155) at
com.demantra.applicationServer.nupdate.process.UpdateCombTask.createTempUpdateTable(UpdateCombTask.java:335) at
com.demantra.applicationServer.nupdate.process.UpdateCombTask.execute(UpdateCombTask.java:87) at
com.demantra.common.queue.QueueTask.run(QueueTask.java:55) at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at
java.lang.Thread.run(Thread.java:662) Caused by: java.sql.SQLException: Invalid column index at
oracle.jdbc.driver.OraclePreparedStatement.setIntInternal(OraclePreparedStatement.java:4859) at


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create a new event and in the population do not select the ship-to level
2. Enter a value in the editable series and save your changes. The preservation type of the series is "most common".
3. Click on the edit event method and increase the end date of the event. Click OK
4. The value in the editable series is maintained for the new duration of the event.
5. Now edit the event again and in the population select the ship-to level and select a few ship-to's. Also increase the end date of the event and click OK.
6. You will find that the value you initially entered in the editable series has changed. On looking in the database you will find that some of the records have null for the editable series.


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
The business just went live with Demantra and this is impacting their process of making event changes. Users are having to wipe out the value and reenter as a work around.

We turned on logging and captured logs during the test. I have uploaded the collaborator log which shows that errors were encountered during the testing. Please review the first error in the file with time stamp "2014-05-01 13:56:28". The query contains "(PROMOTION_DATA.SALES_DATE >= TO_DATE(?,'mm-dd-yyyy') " as part of the where clause. Wondering why there is a question mark within the value passed to the TO_DATE function.

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