CTM Search/Match/Post Abends With 8012 - Invalid Syntax in SQL Statement If An Emergency Contact Name Contains An Apostrophe After Applying CS 9.0 Bundle 41 (Doc ID 2147298.1)

Last updated on FEBRUARY 06, 2017

Applies to:

PeopleSoft Enterprise CS Recruiting and Admissions - Version 9 to 9.2 [Release 9]
PeopleSoft Enterprise CS Campus Community - Version 9 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

Search/Match/Post for CTM abends if Emergency Contact name has an apostrophe in it.

During testing of Bundle #41 and PT 8.55, the following was performed:
1) Create a new applicant using AAWS who has an Emergency Contact with an apostrophe in the last name (like "Thomas O'Connell").
2) Run the Transaction Management Process to perform search/match/post.

If a user navigates to Student Admissions > Application Transaction Mgmt > Constituent Staging and puts in the temporary ID of the staged application, the following is encountered:
Unexpected data type code: None. (180,605) SCC_COMMON.ENTITY.AbstractEntity.OnExecute Name:generateRowset PCPC:57151 Statement:959
Called from:SCC_COMMON.ENTITY.ChildEntity.OnExecute Name:FillNow Statement:219
Called from:SCC_COMMON.ENTITY.ChildEntity.OnExecute Name:fill Statement:211
Called from:SCC_COMMON.ENTITY.AbstractEntity.OnExecute Name:fillChildren Statement:313
Called from:SCC_COMMON.ENTITY.AbstractEntity.OnExecute Name:fill Statement:279
Called from:SCC_COMMON.ENTITY.ChildEntity.OnExecute Name:FillNow Statement:227
Calle

The evaluator encountered an unexpected data type code.

ERROR
-----------------------
File: /vob/peopletools/src/pspcm/pcmosql.cppSQL error. Stmt #: 760 Error Position: 0 Return: 8012 - Invalid syntax in SQL statement
Failed SQL stmt: SELECT FILL.SCC_TEMP_ID,FILL.CONTACT_NAME,FILL.PHONE_TYPE,FILL.COUNTRY_CODE,FILL.PHONE,FILL.EXTENSION,FILL.SCC_ROW_ADD_OPRID,TO_CHAR(CAST((FILL.SCC_ROW_ADD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),FILL.SCC_ROW_UPD_OPRID,TO_CHAR(CAST((FILL.SCC_ROW_UPD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF') FROM PS_SCC_STG_EMG_PHN FILL WHERE SCC_TEMP_ID=346963 AND CONTACT_NAME='Thomas O'Connell'

SQL error. Function: Rowset.Fill
  Error Position: 0
  Return: 8012 - Invalid syntax in SQL statement
  Statement: SELECT FILL.SCC_TEMP_ID,FILL.CONTACT_NAME,FILL.PHONE_TYPE,FILL.COUNTRY_CODE,FILL.PHONE,FILL.EXTENSION,FILL.SCC_ROW_ADD_OPRID,TO_CHAR(CAST((FILL.SCC_ROW_ADD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),FILL.SCC_ROW_UPD_OPRID,TO_CHAR(CAST((FILL.SCC_ROW_UPD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF') FROM PS_SCC_STG_EMG_PHN FILL WHERE SCC_TEMP_ID=346963 AND CONTACT_NAME='THOMAS O'CONNELL'
  Original Statement: %SelectAll(:1 FILL) WHERE SCC_TEMP_ID=346963 AND CONTACT_NAME='Thomas O'Connell'

Unexpected data type code: None. (180,605) SCC_COMMON.ENTITY.AbstractEntity.OnExecute Name:generateRowset PCPC:57151 Statement:959
Called from:SCC_COMMON.ENTITY.ChildEntity.OnExecute Name:FillNow Statement:219
Called from:SCC_COMMON.ENTITY.ChildEntity.OnExecute Name:fill Statement:211
Called from:SCC_COMMON.ENTITY.AbstractEntity.OnExecute Name:fillChildren Statement:313
Called from:SCC_COMMON.ENTITY.AbstractEntity.OnExecute Name:fill Statement:279
Called from:SCC_COMMON.ENTITY.ChildEntity.On

Process 10496466 ABENDED at Step SCC_SL_SMP.MAIN.Step02 (PeopleCode) -- RC = 24 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1) Create a new applicant through AAWS/CTM who has an Emergency Contact with an apostrophe in the last name (like "Thomas O'Connell").
2) Run Search/Match/Post or the Transaction Management Process.


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot have an emergency contact with an apostrophe in the name.

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