Booking History Data Not Collected Correctly (Doc ID 1371040.1)

Last updated on OCTOBER 17, 2016

Applies to:

Oracle Demantra Demand Management - Version 7.3.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 31-May-2013***

Symptoms


The customer is collecting Booking History and generating Forecasts based on Booked Quantity and Request Date.  The current business process allows changes to the Sales Orders AFTER booking, e.g. changes to Organization, Request Date, Ship-to Location, etc.  
The customer is running History Collections on a daily basis and now found duplicate entries on the following fields:
1. Duplicate data by Request Date
"We suspect, but cannot confirm, that the following sequence of events occurred:
• Original request date for this Sales Order line was in Dec-2010
• Booking History was collected and record was created in Demantra for this original combination with qty of 480
• Request date was changed to 04-Jan-2011
• Booking History was collected and record was created in Demantra for this new combination with qty of 480;  Original record with qty of 480 in Dec-2010 remains in sales_data table"

2. Duplicate data by Organization
"Again, we suspect, but cannot confirm, that the following sequence of events occurred:
• Original shipping organization for both SO lines was PAT.
• Booking History was collected and record was created in Demantra for this original combination with qty of 516
• Shipping Organization was changed to VST
• Booking History was collected and record was created in Demantra for this new combination with qty of 516; Original record with qty of 516 with ship org = PAT remains in sales_data table."

Support suggested the following:

1. Purge the below Demantra staging tables.
t_src_sales_tmpl,
t_src_item_tmpl,
t_src_loc_tmpl

2. Re-run the Shipment and Booking History collections with netchange set to Absolute and specific Date range values.
3. Check the Demantra staging tables.

This took care of the duplicate sales order entries in the staging tables but the customer is now stuck on running EBS Full Download step.  
The collaborator.log shows the following:

2011-06-07 10:59:50,513 [Update_3] ERROR update.sql: UPDATE SALES_DATA SET EBS_SH_SHIP_QTY_SD =  NULL
,EBS_BH_BOOK_QTY_RD =  NULL
,EBS_BH_BOOK_QTY_RD =  NULL
,LAST_UPDATE_DATE = SYSDATE
WHERE EXISTS (SELECT 1
FROM MDP_MATRIX
WHERE MDP_MATRIX.T_EP_ORGANIZATION_EP_ID = 1
AND MDP_MATRIX.ITEM_ID = SALES_DATA.ITEM_ID AND MDP_MATRIX.LOCATION_ID = SALES_DATA.LOCATION_ID)
AND NOT ((SALES_DATA.SALES_DATE >= TO_DATE('10-01-2008','mm-dd-yyyy')
AND SALES_DATA.SALES_DATE <= TO_DATE('10-31-2008','mm-dd-yyyy')))
AND SALES_DATA.SALES_DATE >= TO_DATE('10-01-2008','mm-dd-yyyy')
AND SALES_DATA.SALES_DATE <= TO_DATE('05-31-2011','mm-dd-yyyy')
AND (EBS_SH_SHIP_QTY_SD IS NOT NULL
OR EBS_BH_BOOK_QTY_RD IS NOT NULL
OR EBS_BH_BOOK_QTY_RD IS NOT NULL)
2011-06-07 10:59:50,513 [Update_3] ERROR update.sql:
java.sql.SQLException: ORA-00957: duplicate column name

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:113)
at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:561)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1161)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1585)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at com.demantra.applicationServer.nupdate.process.IntegrationUpdateCombTask.purge(IntegrationUpdateCombTask.java:126)
at com.demantra.applicationServer.nupdate.process.IntegrationUpdateCombTask.execute(IntegrationUpdateCombTask.java:66)
at com.demantra.common.queue.QueueTask.run(QueueTask.java:55)
at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:743)
at java.lang.Thread.run(Thread.java:595)
2011-06-07 10:59:52,080 [Update_3] ERROR update.sql: --Purge In Failed. Update Data: Id: 1, User: dm, WS: 461, Update Table: SALES_DATA, Combination: L425M3

From db_exception_log
06/09/2011 14:34:55 EP_LOAD_SALES "ORA-00936: missing expression
   Error in PROCEDURE EP_LOAD_SALES line 486

      dynamic_ddl( '
            UPDATE SALES_DATA
            SET ITEM_PRICE = ,EBS_SH_SHIP_QTY_RD = ,EBS_SH_SHIP_QTY_SD = ,EBS_BH_REQ_QTY_RD = ,EBS_BH_BOOK_QTY_RD = 0,EBS_BH_REQ_QTY_BD = ,EBS_BH_BOOK_QTY_BD = ,EBS_SH_REQ_QTY_RD = ,SDATA15 = ,SHELF_PRICE_SD = ,INCR_EVT_D_RTL_SD = ,BASE_EVT_D_RTL_SD = ,ACTUAL_QUANTITY = 0,,SDATA5 = ,SDATA7 = ,SDATA8 = ,SDATA9 = ,SDATA10 = ,SDATA11 = ,SDATA12 = ,SDATA13 = ,SDATA14 = ,SDATA6 =  + NVL(SDATA6,0),syncro_sig = -1, last_update_date = SYSDATE,
               load_sig='06-09-2011 14:30:16'
               WHERE item_id = 45741
               AND location_id = 62719
               AND sales_date = '10-01-2008 00:00:00'')"
06/09/2011 14:34:55 DATA_LOAD "ORA-20003:
   Error in PACKAGE BODY DATA_LOAD line 3948
executing ACTIVE_PROC_DYN('EP_LOAD_SALES')"
06/09/2011 13:54:50 DATA_LOAD "ORA-20003:
   Error in PACKAGE BODY DATA_LOAD line 3948
executing ACTIVE_PROC_DYN('EP_LOAD_SALES')"
06/09/2011 13:54:50 EP_LOAD_SALES "ORA-00936: missing expression
   Error in PROCEDURE EP_LOAD_SALES line 486

      dynamic_ddl( '
            UPDATE SALES_DATA
            SET ITEM_PRICE = ,EBS_SH_SHIP_QTY_RD = ,EBS_SH_SHIP_QTY_SD = ,EBS_BH_REQ_QTY_RD = ,EBS_BH_BOOK_QTY_RD = 0,EBS_BH_REQ_QTY_BD = ,EBS_BH_BOOK_QTY_BD = ,EBS_SH_REQ_QTY_RD = ,SDATA15 = ,SHELF_PRICE_SD = ,INCR_EVT_D_RTL_SD = ,BASE_EVT_D_RTL_SD = ,ACTUAL_QUANTITY = 0,,SDATA5 = ,SDATA7 = ,SDATA8 = ,SDATA9 = ,SDATA10 = ,SDATA11 = ,SDATA12 = ,SDATA13 = ,SDATA14 = ,SDATA6 =  + NVL(SDATA6,0),syncro_sig = -1, last_update_date = SYSDATE,
               load_sig='06-09-2011 13:49:38'
               WHERE item_id = 45741
               AND location_id = 62719
               AND sales_date = '10-01-2008 00:00:00'')"
06/07/2011 11:53:18 EP_LOAD_SALES "ORA-00936: missing expression
   Error in PROCEDURE EP_LOAD_SALES line 486

      dynamic_ddl( '
            UPDATE SALES_DATA
            SET ITEM_PRICE = ,EBS_SH_SHIP_QTY_RD = ,EBS_SH_SHIP_QTY_SD = ,EBS_BH_REQ_QTY_RD = ,EBS_BH_BOOK_QTY_RD = 0,EBS_BH_REQ_QTY_BD = ,EBS_BH_BOOK_QTY_BD = ,EBS_SH_REQ_QTY_RD = ,SDATA15 = ,SHELF_PRICE_SD = ,INCR_EVT_D_RTL_SD = ,BASE_EVT_D_RTL_SD = ,ACTUAL_QUANTITY = 0,,SDATA5 = ,SDATA7 = ,SDATA8 = ,SDATA9 = ,SDATA10 = ,SDATA11 = ,SDATA12 = ,SDATA13 = ,SDATA14 = ,SDATA6 =  + NVL(SDATA6,0),syncro_sig = -1, last_update_date = SYSDATE,
               load_sig='06-07-2011 11:48:58'
               WHERE item_id = 45741
               AND location_id = 62719
               AND sales_date = '10-01-2008 00:00:00'')"

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