R12: OIE: WF: Status Monitor when Viewing the Activity History Errors with java.sql.SQLSyntaxErrorException: ORA-01722: Invalid Number for Completed Notifications (Doc ID 1939750.1)

Symptoms

On : 12.1.3 version, in Status Monitor when viewing the Activity History button for a Completed AME related workflow sub-process notification the following error occurs:


ERROR (main error excerpts only:)
-----------------------
Exception Details:
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT wias.item_type AS ITEM_TYPE,
wias.item_key AS ITEM_KEY,
wias.process_activity AS PROCESS_ACTIVITY,
wias.ROWID AS ROW_ID,
'R' AS ROW_SOURCE,
wias.NOTIFICATION_ID AS NOTIF_ID,
decode(wn.STATUS, 'OPEN', nvl(wn.more_info_role,wias.ASSIGNED_USER),wias.ASSIGNED_USER) AS ASSIGNED_USER,
wias.ACTIVITY_STATUS AS ACTIVITY_STATUS,
wias.ACTIVITY_RESULT_CODE AS ACTIVITY_RESULT_CODE,
wias.EXECUTION_TIME AS EXECUTION_TIME,
wias.BEGIN_DATE AS BEGIN_DATE,
wias.END_DATE AS END_DATE,
wias.DUE_DATE AS DUE_DATE,
wl.meaning AS STATUS_DISPLAY,
wa.name AS ACTIVITY_NAME,
wa.display_name AS ACTIVITY_DISPLAY,
wi.user_key AS USER_KEY,
wa2.name AS PARENT_ACTIVITY,
decode(wa2.name, 'ROOT', '', wa2.display_name) AS PARENT_ACTIVITY_DISPLAY_NAME,
wa.type AS ACTIVITY_TYPE,
wf_fwkmon.getroleemailaddress(decode(wn.STATUS, 'OPEN', nvl(wn.more_info_role,wias.ASSIGNED_USER),wias.ASSIGNED_USER)) AS ROLE_EMAIL_ADDRESS, wf_directory.getroledisplayname2(decode(wn.STATUS, 'OPEN', nvl(wn.more_info_role,wias.ASSIGNED_USER),wias.ASSIGNED_USER)) AS ROLE_DISPLAY_NAME,
decode(wias.ACTIVITY_RESULT_CODE, '#NULL', 'WfNoCloseDate',
'WfCloseDate') AS END_DATE_COL_SWITCH,
decode(wias.ACTIVITY_STATUS, 'ERROR', 'WfStatusErrorText',
'WfStatusNoterrText') AS STATUS_COLUMN_SWITCH,
decode(wias.ACTIVITY_STATUS, 'ERROR', 'WfStatusError', 'COMPLETE', 'WfStatusComplete',
'SUSPEND', 'WfStatusSuspended', 'WAITING', 'WfStatusWaiting', 'DEFERRED', 'WfStatusDeferred',
'NOTIFIED', 'WfStatusNotified', 'WfStatusActive') AS IMAGE_COLUMN_SWITCH,
wf_core.activity_result(wa.result_type,
decode(wias.ACTIVITY_RESULT_CODE, '#NULL', NULL,wias.ACTIVITY_RESULT_CODE)) AS RESULT_DISPLAY,
wpa.activity_item_type AS ACTIVITY_ITEM_TYPE,
  decode(wa.type, 'NOTICE', decode(wias.ACTIVITY_STATUS, 'NOTIFIED','WfReassignEnabled',
  'ERROR', 'WfReassignEnabled',
  'WfReassignDisabled'),
  'WfReassignDisabled') AS REASSIGN_SWITCHER,
  decode(wias.ACTIVITY_STATUS, 'NOTIFIED', 'N', 'ACTIVE', 'N', 'ERROR', 'N', 'WAITING', 'N', 'DEFERRED', 'N', 'Y') AS SELECT_DISABLED,
  decode(wa.type, 'PROCESS', decode(wias.ACTIVITY_STATUS, 'SUSPEND', 'WfResumeEnabled', 'COMPLETE', 'WfSuspResDisabled', 'WfSuspendEnabled'), 'WfSuspResDisabled') AS SUSPEND_SWITCHER,
  wa.expand_role AS EXPAND_ROLE,
  decode(wn.STATUS, 'OPEN', nvl2(wn.more_info_role,WF_CORE.Translate('WFNTF_MOREINFO_REQUESTED'),wnl.MEANING), wnl.MEANING) AS NOTIFICATION_STATUS
FROM WF_ITEM_ACTIVITY_STATUSES wias
left join wf_notifications wn on wias.notification_id = wn.notification_id
left join wf_lookups wnl on wnl.lookup_code = wn.STATUS AND wnl.lookup_type = 'WF_NOTIFICATION_STATUS',
  wf_lookups wl,
wf_items wi,
wf_activities_vl wa,
wf_process_activities wpa,
wf_activities_vl wa2
WHERE wl.lookup_code = wias.ACTIVITY_STATUS
AND wl.lookup_type = 'WFENG_STATUS'
AND wias.ITEM_TYPE = wi.item_type
AND wias.ITEM_KEY = wi.item_key
AND wias.PROCESS_ACTIVITY = wpa.instance_id
AND wpa.activity_name = wa.name
AND wpa.activity_item_type = wa.item_type
AND wi.begin_date between wa.begin_date and nvl(wa.end_date, wi.begin_date)
AND wpa.process_name = wa2.name
AND wpa.process_item_type = wa2.item_type
AND wpa.process_version = wa2.version
AND wias.ITEM_TYPE = :1
AND wias.ITEM_KEY = :2
UNION ALL
SELECT wiash.item_type AS ITEM_TYPE,
wiash.item_key AS ITEM_KEY,
wiash.process_activity AS PROCESS_ACTIVITY,
wiash.ROWID AS ROW_ID,
'H' AS ROW_SOURCE,
wiash.NOTIFICATION_ID AS NOTIF_ID,
decode(wn.STATUS, 'OPEN', nvl(wn.more_info_role,wiash.ASSIGNED_USER),wiash.ASSIGNED_USER) AS ASSIGNED_USER,
wiash.ACTIVITY_STATUS AS ACTIVITY_STATUS,
wiash.ACTIVITY_RESULT_CODE AS ACTIVITY_RESULT_CODE,
wiash.EXECUTION_TIME AS EXECUTION_TIME,
wiash.BEGIN_DATE AS BEGIN_DATE,
wiash.END_DATE AS END_DATE,
wiash.DUE_DATE AS DUE_DATE,
wl.meaning AS STATUS_DISPLAY,
wa.name AS ACTIVITY_NAME,
wa.display_name AS ACTIVITY_DISPLAY,
wi.user_key AS USER_KEY,
wa2.name AS PARENT_ACTIVITY,
decode(wa2.name, 'ROOT', '', wa2.display_name) AS PARENT_ACTIVITY_DISPLAY_NAME,
wa.type AS ACTIVITY_TYPE,
wf_fwkmon.getroleemailaddress(decode(wn.STATUS, 'OPEN', nvl(wn.more_info_role,wiash.ASSIGNED_USER),wiash.ASSIGNED_USER)) AS ROLE_EMAIL_ADDRESS,
wf_directory.getroledisplayname2(decode(wn.STATUS, 'OPEN', nvl(wn.more_info_role,wiash.ASSIGNED_USER),wiash.ASSIGNED_USER)) AS ROLE_DISPLAY_NAME,
decode(wiash.ACTIVITY_RESULT_CODE, '#NULL', 'WfNoCloseDate',
'WfCloseDate') AS END_DATE_COL_SWITCH,
decode(wiash.ACTIVITY_STATUS, 'ERROR', 'WfStatusErrorText',
'WfStatusNoterrText') AS STATUS_COLUMN_SWITCH,
decode(wiash.ACTIVITY_STATUS, 'ERROR', 'WfStatusError', 'COMPLETE', 'WfStatusComplete',
'SUSPEND', 'WfStatusSuspended', 'WAITING', 'WfStatusWaiting', 'DEFERRED', 'WfStatusDeferred',
'NOTIFIED', 'WfStatusNotified', 'WfStatusActive') AS IMAGE_COLUMN_SWITCH,
wf_core.activity_result(wa.result_type,
decode(wiash.ACTIVITY_RESULT_CODE, '#NULL', NULL, wiash.ACTIVITY_RESULT_CODE)) AS RESULT_DISPLAY,
wpa.activity_item_type AS ACTIVITY_ITEM_TYPE,
  decode(wa.type, 'NOTICE', decode(wiash.ACTIVITY_STATUS, 'NOTIFIED','WfReassignEnabled',
  'ERROR', 'WfReassignEnabled',
  'WfReassignDisabled'),
  'WfReassignDisabled') AS REASSIGN_SWITCHER,
  decode(wiash.ACTIVITY_STATUS, 'NOTIFIED', 'N', 'ACTIVE', 'N', 'ERROR', 'N', 'WAITING', 'N', 'DEFERRED', 'N', 'Y') AS SELECT_DISABLED,
  decode(wa.type, 'PROCESS', decode(wiash.ACTIVITY_STATUS, 'SUSPEND', 'WfResumeEnabled', 'COMPLETE', 'WfSuspResDisabled', 'WfSuspendEnabled'), 'WfSuspResDisabled') AS SUSPEND_SWITCHER,
  wa.expand_role AS EXPAND_ROLE,
  decode(wn.STATUS, 'OPEN', nvl2(wn.more_info_role,WF_CORE.Translate('WFNTF_MOREINFO_REQUESTED'),wnl.MEANING), wnl.MEANING) AS NOTIFICATION_STATUS
FROM WF_ITEM_ACTIVITY_STATUSES_H wiash
left join wf_notifications wn on wiash.notification_id = wn.notification_id
left join wf_lookups wnl on wnl.lookup_code = wn.STATUS AND wnl.lookup_type = 'WF_NOTIFICATION_STATUS',
  wf_lookups wl,
wf_items wi,
wf_activities_vl wa,
wf_process_activities wpa,
wf_activities_vl wa2
WHERE wl.lookup_code = wiash.ACTIVITY_STATUS
AND wl.lookup_type = 'WFENG_STATUS'
AND wiash.ITEM_TYPE = wi.item_type
AND wiash.ITEM_KEY = wi.item_key
AND wiash.PROCESS_ACTIVITY = wpa.instance_id
AND wpa.activity_name = wa.name
AND wpa.activity_item_type = wa.item_type
AND wi.begin_date between wa.begin_date and nvl(wa.end_date, wi.begin_date)
AND wpa.process_name = wa2.name
AND wpa.process_item_type = wa2.item_type
AND wpa.process_version = wa2.version
AND wiash.ITEM_TYPE = :3
AND wiash.ITEM_KEY = :4) QRSLT ORDER BY 11 desc, 10 desc
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:153)
at oracle.apps.fnd.framework.server.OAViewObjectImpl.invokeMethod(OAViewObjectImpl.java:1136)
at oracle.apps.fnd.wf.monitor.webui.HistoryFilterCO.doSearch(HistoryFilterCO.java:314)
at oracle.apps.fnd.wf.monitor.webui.HistoryFilterCO.processRequest(HistoryFilterCO.java:219)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:604)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
at oracle.apps.fnd.framework.webui.beans.layout.OATableLayoutBean.processRequest(OATableLayoutBean.java:353)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:976)
at ...
## Detail 0 ##
java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
ORA-06512: at "APPS.WF_FWKMON", line 192
ORA-06512: at "APPS.WF_FWKMON", line 207

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:860)
at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3754)
at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4566)
at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:751)
at ...



STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Complete a workflow subprocess that is AME related, for instance approve a notification from the APEXP Expense Report Approval workflow or approve a notification from the APINV Payables AP iNVOICE APPROVAL workflow so that the Process is flagged as complete. Note that there is no problem accessing or approving the notification.
2. Navigate to Workflow Status Monitor Search=>Workflows=>Search
3. Query the related workflow name (example APEXP or APINV
4. Select the Completed process
5. Select the button for "Activity History"
6. The error occurs

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot review details or activity history for completed processes via the Workflow Status Monitor

Please note it is also seen in the WF_ITEM_ACTIVITY_STATUSES the field ASSIGNED_USER contains a value similiar to the format of email:<email_address>

Changes

 Application of a patch to resolve <Bug 18318416> such as <Patch 18597161>

Cause