Workflow EBS Upload Local Forecast From Demantra To VCP ASCP Errors (Doc ID 473383.1)

Last updated on JANUARY 12, 2016

Applies to:

Oracle Demantra Demand Management - Version 7.1.1 and later
Information in this document applies to any platform.
***Checked for relevance on 11-Dec-2013***


Symptoms

On 7.1.1 Demantra with EBS 11.5.10 in CRP:
When attempting to run the Workflow EBS Upload Local Forecast, the following error occurs in the
Collaborator.log:

14:32:03,603 [WFProcess_2] FATAL appserver.workflow.general :: appserver.workflow.general
java.sql.SQLException: ORA-20015: Exception: msd_dem_upload_forecast.upload_forecast - ORA-00904:
"EXP"."LEVEL3": invalid identifier
ORA-06512: at "APPS.MSD_DEM_UPLOAD_FORECAST", line 911
ORA-06512: at line 1

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.T4CCallableStatement.doOall8(T4CCallableStatement.java:184)
at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:873)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1161)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3001)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3093)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4286)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
at com.demantra.workflow.step.StoredProcedureStep.runStoredProcedure(StoredProcedureStep.java:167)
at com.demantra.workflow.step.StoredProcedureStep.doExecute(StoredProcedureStep.java:108)
at com.demantra.workflow.step.LinkedStep.execute(LinkedStep.java:458)
at com.demantra.workflow.process.WFProcess.mainLoop(WFProcess.java:467)
at com.demantra.workflow.process.WFProcess.run(WFProcess.java:267)
at java.lang.Thread.run(Thread.java:595)

We have tried several times deleting the levels and populating them back in the integration
profile but to no avail:

1. Get the 'ID' of the export data profile from the table TRANSFER_QUERY:

select id
from transfer_query
where query_name = 'Local Forecast';

Id = 353

2. Query the table TRANSFER_QUERY_LEVELS where id = <ID from step 1>.

select *
from transfer_query_levels
where id = 353;

Output:

id LEVEL_ID LORDER

353 424 2
353 425 3

3. Check the value in the column LORDER. Check if the value 3 is missing or not? It's there.

4. If Yes, then ask the customer to:
A. delete all the levels from the data profile. save. query the table TRANSFER_QUERY_LEVELS
again. There should be ZERO records for the data profile.

Deleted levels and ran the qry.
No data in transfer_query_levels table.

B. add back the levels again to the data profile. save. query the table TRANSFER_QUERY_LEVELS
again. There should be records for the data profile. Also the LORDER column should now
be populate correctly with no missing numbers in sequence.

Added levels back.
Data is is transfer_query_levels table, same as in step 2 before deleting.

C. restart the application server, and then try running the export workflow again.

Ran the Workflow and got the same error as before.

Note: The setup of the integration profile looks exactly the same in our internal instances as in
the customer's


STEPS
The issue can be reproduced at will with the following steps:
1. Run the EBS Upload Local Forecast workflow
2. Note the error message in the Collaborator Workbench and detailed messages in the
Collaborator.log

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