Create Event And Promote To Case Service Is Not Working For New Customer (Doc ID 2928265.1)

Last updated on FEBRUARY 20, 2023

Applies to:

Symptoms

We have a business scenario where we need to create a case for new customer whose details are not there in the cust table. We used "Create Event and Promote to Case" Service to perform the same. This service is working fine if we use existing customer id in the system. The same is not working fine if we provide new customer details. Its giving error in return response.
Attached the both input and response json, logs & screenshot for your reference. Please check and advise.

Changes

 Filename =ummService.log
See the following error:

[31-01-23 23:40:54,174 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [RTCASECREATIONRESTSERVICE] ThreadId = 50, LogMessage = saveEventsAndPromoteToCase service is called from External Client
[31-01-23 23:40:54,174 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [RTCASECREATIONRESTSERVICE] ThreadId = 50, LogMessage =  saveEventsAndPromoteToCase ECM business method call started
[31-01-23 23:40:54,174 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [RTCASECREATIONBUSINESSSERVICE] ThreadId = 50, LogMessage = Entered saveEventsAndPromoteToCase business method
[31-01-23 23:40:54,182 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query to be executed :: select cm_BATCH_RUN_seq.nextval from dual
[31-01-23 23:40:54,182 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query execution started
[31-01-23 23:40:54,182 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query execution including resultset fetch finished
[31-01-23 23:40:54,182 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [RTCASECREATIONBUSINESSSERVICE] ThreadId = 50, LogMessage = Run skey:::260
[31-01-23 23:40:54,182 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query to be executed :: select kro.owner_seq_id from kdd_review_owner kro where upper(kro.owner_id)=upper(?)
[31-01-23 23:40:54,182 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Parameters:: [FCCMADMN]
[31-01-23 23:40:54,182 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = setString(1, FCCMADMN)
[31-01-23 23:40:54,182 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query execution started
[31-01-23 23:40:54,182 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query execution including resultset fetch finished
[31-01-23 23:40:54,182 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [RTCASECREATIONBUSINESSSERVICE] ThreadId = 50, LogMessage = Fetching Event Entity Configuration started
[31-01-23 23:40:54,182 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [RTCASECREATIONBUSINESSSERVICE] ThreadId = 50, LogMessage = Fetching Event Entity Configuration finished
[31-01-23 23:40:54,182 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [RTCASECREATIONBUSINESSSERVICE] ThreadId = 50, LogMessage = Calling processEventsAndGenerateQueries started
[31-01-23 23:40:54,182 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [RTCASECREATIONBUSINESSSERVICE] ThreadId = 50, LogMessage = Entered processEventsAndGenerateQueries method
[31-01-23 23:40:54,188 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query to be executed :: select N_EVENT_SKEY from FCC_EVENT_LOOKUP WHERE V_EVENT_CD=? AND V_DATA_ORIGIN=?
[31-01-23 23:40:54,188 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Parameters:: [655867927, DLY]
[31-01-23 23:40:54,188 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = setString(1, 655867927)
[31-01-23 23:40:54,188 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = setString(2, DLY)
[31-01-23 23:40:54,188 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query execution started
[31-01-23 23:40:54,188 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query execution including resultset fetch finished
[31-01-23 23:40:54,188 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query to be executed :: select cm_event_lookup_seq.nextval from dual
[31-01-23 23:40:54,188 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query execution started
[31-01-23 23:40:54,189 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query execution including resultset fetch finished
[31-01-23 23:40:54,189 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [RTCASECREATIONBUSINESSSERVICE] ThreadId = 50, LogMessage = Finding the look up for Entity:::CUSTOMER
[31-01-23 23:40:54,194 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query to be executed :: Select CUSTOMER_SKEY from FCC_CUSTOMER_LOOKUP WHERE 1=1  AND CUST_INTRL_ID =?
[31-01-23 23:40:54,194 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Parameters:: [CIF-XXXXXX]
[31-01-23 23:40:54,194 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = setString(1, CIF-XXXXXXX)
[31-01-23 23:40:54,194 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query execution started
[31-01-23 23:40:54,195 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query execution including resultset fetch finished
[31-01-23 23:40:54,195 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [RTCASECREATIONBUSINESSSERVICE] ThreadId = 50, LogMessage = Found entry in lookup:::0
[31-01-23 23:40:54,195 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [RTCASECREATIONBUSINESSSERVICE] ThreadId = 50, LogMessage = sequenceName::::null
[31-01-23 23:40:54,195 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query to be executed :: select null.nextval from dual
[31-01-23 23:40:54,195 GMT PM] [DEBUG] [WEB]  [UMM]  [NA] [DBUTIL] ThreadId = 50, LogMessage = Query execution started
[31-01-23 23:40:54,196 GMT PM] [ERROR] [WEB]  [UMM]  [NA] [LOGGER] ORA-00923: FROM keyword not found where expected

java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)
at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:121)
at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:121)
at com.ofss.reveleus.cm.util.DBUtil.getSequenceNextValueWithLocale(DBUtil.java:182)
at com.ofss.reveleus.cm.service.RTCaseCreationBusinessService.getEntitySkeyForEntity(RTCaseCreationBusinessService.java:420)
at com.ofss.reveleus.cm.service.RTCaseCreationBusinessService.processEventsAndGenerateQueries(RTCaseCreationBusinessService.java:271)
at com.ofss.reveleus.cm.service.RTCaseCreationBusinessService.saveEventsAndPromoteToCase(RTCaseCreationBusinessService.java:854)
at com.ofss.reveleus.cm.restService.RTCaseCreationRestService.saveEventsAndPromoteToCase(RTCaseCreationRestService.java:247)

Response_jason.txt

{
   "message": "Sequence retrieval failed for Lookup Table as V_LOOKUP_SEQUENCE_NAME is configured as null in FCC_RT_EVENT_ENTITY_TABLES.",
   "status": "FAILED"
}

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.