Running I-9 Receipt/Expiration Report Fails With Microsoft SQL Server

(Doc ID 2384769.1)

Last updated on APRIL 10, 2018

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

While running I-9 Receipt/Expiration Report the customer is getting the following error:
ERROR
-----------------------

File: e:\pt85521b-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1723 Error Position: 0 Return: 8601 - [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Conversion failed when converting the varchar value 'A' to data type smallint. (SQLSTATE 22005) 245
Failed SQL stmt: INSERT INTO PS_HR_I9_VRF_TAO (PROCESS_INSTANCE ,EMPLID ,I9_EE_SIGN_DT ,I9_XLATLONG_A ,XLATLONGNAME ,I9_DOC_TYPE ,SEQ_NO ,NAME ,COMPANY ,HIRE_DT ,I9_UPDATE_DOC_FLG ,I9_EXP_DOC_FLG ,I9_DOC_EXP_DT_A1 )
SELECT 169421 ,A.EMPLID ,A.I9_EE_SIGN_DT ,B.XLATLONGNAME ,B.XLATLONGNAME ,'3' ,'A' ,A.NAME ,A.COMPANY ,A.HIRE_DT ,'N' ,'Y' ,A.I9_REC_EXP_DT_A3 FROM PS_HR_I9_PERSON A , PSXLATITEM B WHERE A.COMPANY = 'GBI' AND A.I9_REVERIFIED_DT IS NULL AND A.I9_REC_EXP_DT_A3 >= '2017-08-01'
AND A.I9_REC_EXP_DT_A3 <= '2017-12-31' AND A.I9_RECEIPT_FLG_A3 = 'Y' AND A.I9_DOC_NBR_A3 = ' ' AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_HR_I9_PERSON A1 WHERE A.EMPLID = A1.EMPLID AND A1.EFFDT <= { fn CURDATE() }) AND B.FIELDNAME = 'I9_DOC_TITLE_A3'
AND B.FIELDVALUE = A.I9_DOC_TITLE_A3 AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PSXLATITEM B1 WHERE B.FIELDNAME = B1.FIELDNAME AND B.FIELDVALUE = B1.FIELDVALUE AND B1.EFFDT <= A.I9_ER_SIGN_DT)

Process 169421 ABENDED at Step HR_I9_VERIFY.InsExp.InsRecA3 (SQL) -- RC = 8601 (108,524)

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

PSAESRV completed service request at 15.39.21 2018-04-10


 

REPLICATION STEPS 
-----------------------
1. Navigate to Workforce Administration>Personal Information>Form I-9>I-9 Receipt/Expiration Report and Add a New Value 
2. Set From/Thru Date and Select a company 
3. Run the process 
4. Note the process fails with the above message

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot run the I-9 Receipt/Expiration Report.

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