My Oracle Support Banner

ETL 9.2 Elastic Search Gives HC_TL_GBL_TIMESHEET Index SQL Error (Doc ID 2833817.1)

Last updated on FEBRUARY 01, 2022

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

On : 8.58 version, Elastic Search

Unable to index HC_TL_GBL_TIMESHEET.

ERROR
-----------------------
File: E:\PT858P10B_2102170501-retail\peopletools\src\pssys\qpm.cppSQL error. Stmt #: 8495 Error Position: 0 Return: 8602 - [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string.
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Warning: Null value is eliminated by an aggregate or other SET operation. (SQLSTATE 01003) 8153
Failed SQL stmt: SELECT A.EMPLID, A.EMPL_RCD, (CONVERT(CHAR(10),A.DUR,121)), A.SEQ_NBR, A5X.XLATLONGNAME, A.TRC, A7X.XLATLONGNAME, A.TL_QUANTITY, A.LASTUPDDTTM, B10X.XLATLONGNAME, D11X.XLATLONGNAME, B.WORKGROUP, CASE WHEN COALESCE(A.LASTUPDDTTM,'1900-01-01 00:00:00.000') > COALESCE(C.LASTUPDDTTM,'1900-01-01 00:00:00.000') THEN COALESCE(A.LASTUPDDTTM,'1900-01-01 00:00:00.000') WHEN COALESCE(C.LASTUPDDTTM,'1900-01-01 00:00:00.000') > COALESCE(A.LASTUPDDTTM,'1900-01-01 00:00:00.000') THEN COALESCE(C.LASTUPDDTTM,'1900-01-01 00:00:00.000') ELSE GETDATE() END, E.LAST_NAME_SRCH, E.FIRST_NAME_SRCH, E.NAME_DISPLAY, C.DEPTID, C.JOBCODE, C.POSITION_NBR, C.LOCATION, C.COMPANY, C.BUSINESS_UNIT, C.DEPT_DESCR, C.POSN_DESCR, C.LOCATION_DESCR, C.COMPANY_DESCR, C.JOBCODE_DESCR, 'psp/EMPLOYEE/HRMS/c/HR_SRCH_GLOBAL.TL_SRCH_LAUNCH.GBL?Page=TL_SRCH_LAUNCH&Action=U&EMPLID=%A.EMPLID%&EMPL_RCD=%A.EMPL_RCD%&DUR=%A.DUR%&SEQ_NBR=%A.SEQ_NBR%', C.DESCR, CASE WHEN ( A.TRC <> ' ') THEN ( A.TRC + '-' + G.DESCR) ELSE A.TRC END, D.DESCR, CONVERT(VARCHAR(4), DATEPART(YEAR, (CONVERT(CHAR(10),A.DUR,121)))) + '-' + CAST(DATEPART(MM, (CONVERT(CHAR(10),A.DUR,121))) AS nvarchar), DATEADD(DAY, ((DATEDIFF(DAY,(CONVERT(DATETIME, 1900 + 01 + 01)), (CONVERT(CHAR(10),A.DUR,121)))) - 7 * FLOOR((DATEDIFF(DAY,(CONVERT(DATETIME, 1900 + 01 + 01)), (CONVERT(CHAR(10),A.DUR,121)))) / 7)*-1), (CONVERT(CHAR(10),A.DUR,121))), J.XLATLONGNAME, L.XLATLONGNAME, CONVERT(VARCHAR(4), DATEPART(YEAR, (CONVERT(CHAR(10),A.DUR,121)))) + '-' + CAST(DATEPART(MM, (CONVERT(CHAR(10),A.DUR,121))) AS nvarchar)+ '-15', G.DESCR, (CONVERT(CHAR(10),A.DUR,121)) + '-00.00.00', CASE WHEN A.LASTUPDDTTM IS NOT NULL THEN CONVERT(DATETIME, CONVERT(CHAR(10),A.LASTUPDDTTM,121)) + '-00.00.00' ELSE '1900-01-01-00.00.00.00' END, CASE WHEN COALESCE(A.EMPLID,'No Value') = ' ' THEN 'No Value' ELSE COALESCE(A.EMPLID,'No Value') END , CASE WHEN COALESCE(A.TRC,'No Value') = ' ' THEN 'No Value' ELSE COALESCE(A.TRC,'No Value') END , CASE WHEN COALESCE(B.WORKGROUP,'No Value') = ' ' THEN 'No Value' ELSE COALESCE(B.WORKGROUP,'No Value') END , CASE WHEN COALESCE(C.BUSINESS_UNIT,'No Value') = ' ' THEN 'No Value' ELSE COALESCE(C.BUSINESS_UNIT,'No Value') END , CASE WHEN COALESCE(C.COMPANY,'No Value') = ' ' THEN 'No Value' ELSE COALESCE(C.COMPANY,'No Value') END , CASE WHEN COALESCE(C.DEPT_DESCR,'No Value') = ' ' THEN 'No Value' ELSE COALESCE(C.DEPT_DESCR,'No Value') END , CASE WHEN COALESCE(C.JOBCODE_DESCR,'No Value') = ' ' THEN 'No Value' ELSE COALESCE(C.JOBCODE_DESCR,'No Value') END , CASE WHEN COALESCE(C.LOCATION_DESCR,'No Value') = ' ' THEN 'No Value' ELSE COALESCE(C.LOCATION_DESCR,'No Value') END , CASE WHEN COALESCE(E.NAME_DISPLAY,'No Value') = ' ' THEN 'No Value' ELSE COALESCE(E.NAME_DISPLAY,'No Value') END , CASE WHEN COALESCE(G.DESCR,'No Value') = ' ' THEN 'No Value' ELSE COALESCE(G.DESCR,'No Value') END FROM ((PS_TL_RPTD_TIME A LEFT OUTER JOIN PS_TL_SRCH_EXP_VW F ON A.EMPLID = F.EMPLID AND A.EMPL_RCD = F.EMPL_RCD AND A.DUR = F.DUR ) LEFT OUTER JOIN PS_TL_TRC_TBL G ON G.TRC = A.TRC AND G.EFF_STATUS = 'A' AND G.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_TL_TRC_TBL G_ED WHERE G.TRC = G_ED.TRC AND G_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)) ) LEFT OUTER JOIN ( SELECT * FROM PSXLATITEM TA WHERE TA.FIELDNAME='PUNCH_TYPE' AND TA.EFF_STATUS = 'A' AND TA.EFFDT = (SELECT MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME='PUNCH_TYPE' AND TA.FIELDVALUE=TB.FIELDVALUE AND TB.EFF_STATUS = 'A' AND TB.EFFDT <= { fn CURDATE() })) A5X ON A5X.FIELDVALUE = A.PUNCH_TYPE AND A5X.FIELDNAME='PUNCH_TYPE' LEFT OUTER JOIN ( SELECT * FROM PSXLATITEM TA WHERE TA.FIELDNAME='REPORTED_STATUS' AND TA.EFF_STATUS = 'A' AND TA.EFFDT = (SELECT MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME='REPORTED_STATUS' AND TA.FIELDVALUE=TB.FIELDVALUE AND TB.EFF_STATUS = 'A' AND TB.EFFDT <= { fn CURDATE() })) A7X ON A7X.FIELDVALUE = A.REPORTED_STATUS AND A7X.FIELDNAME='REPORTED_STATUS' , (PS_TL_EMPL_DATA B LEFT OUTER JOIN PS_TL_WRKGRP_TBL D ON D.WORKGROUP = B.WORKGROUP AND D.EFFDT = (SELECT MAX(D_ED.EFFDT) FROM PS_TL_WRKGRP_TBL D_ED WHERE D.WORKGROUP = D_ED.WORKGROUP AND D_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)) ) LEFT OUTER JOIN ( SELECT * FROM PSXLATITEM TA WHERE TA.FIELDNAME='TIME_RPTG_TYPE' AND TA.EFF_STATUS = 'A' AND TA.EFFDT = (SELECT MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME='TIME_RPTG_TYPE' AND TA.FIELDVALUE=TB.FIELDVALUE AND TB.EFF_STATUS = 'A' AND TB.EFFDT <= { fn CURDATE() })) D11X ON D11X.FIELDVALUE = D.TIME_RPTG_TYPE AND D11X.FIELDNAME='TIME_RPTG_TYPE' LEFT OUTER JOIN ( SELECT * FROM PSXLATITEM TA WHERE TA.FIELDNAME='TIME_RPTR_IND' AND TA.EFF_STATUS = 'A' AND TA.EFFDT = (SELECT MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME='TIME_RPTR_IND' AND TA.FIELDVALUE=TB.FIELDVALUE AND TB.EFF_STATUS = 'A' AND TB.EFFDT <= { fn CURDATE() })) B10X ON B10X.FIELDVALUE = B.TIME_RPTR_IND AND B10X.FIELDNAME='TIME_RPTR_IND' , PS_TL_SRCH_JOB_VW C, PS_HR_SRCH_NAME_VW E, PSXLATITEM J, PSXLATITEM L WHERE ( A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_TL_EMPL_DATA B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.EMPL_RCD = B_ED.EMPL_RCD AND B_ED.EFFDT <= A.DUR) AND A.EMPLID = C.EMPLID AND A.EMPL_RCD = C.EMPL_RCD AND A.EMPLID = E.EMPLID AND E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_HR_SRCH_NAME_VW E_ED WHERE E.EMPLID = E_ED.EMPLID AND E.NAME_TYPE = E_ED.NAME_TYPE AND E_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)) AND ( A.LASTUPDDTTM >= :1 OR B.EFFDT >= :2 OR C.EFFDT >= :3 OR C.LASTUPDDTTM >= :4 OR F.ACTION_DTTM >= :5) AND C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_TL_SRCH_JOB_VW C_ED WHERE C.EMPLID = C_ED.EMPLID AND C.EMPL_RCD = C_ED.EMPL_RCD AND C_ED.EFFDT <= A.DUR) AND C.EFFSEQ = (SELECT MAX(C_ES.EFFSEQ) FROM PS_TL_SRCH_JOB_VW C_ES WHERE C.EMPLID = C_ES.EMPLID AND C.EMPL_RCD = C_ES.EMPL_RCD AND C.EFFDT = C_ES.EFFDT) AND J.FIELDVALUE = CAST(DATEPART(MM, A.DUR) AS nvarchar) AND J.FIELDNAME = 'BGN_MTH' AND J.EFF_STATUS = 'A' AND J.EFFDT = (SELECT MAX( K.EFFDT) FROM PSXLATITEM K WHERE J.FIELDNAME = K.FIELDNAME AND J.FIELDVALUE = K.FIELDVALUE AND J.EFF_STATUS = K.EFF_STATUS) AND L.FIELDVALUE = DATEPART(dw, A.DUR) AND L.FIELDNAME = 'DAY_OF_WK' AND L.EFF_STATUS = 'A' AND L.EFFDT = (SELECT MAX( M.EFFDT) FROM PSXLATITEM M WHERE L.EFF_STATUS = M.EFF_STATUS AND L.FIELDNAME = M.FIELDNAME AND L.FIELDVALUE = M.FIELDVALUE))

Error in running query because of SQL Error, Code=8602, Message=[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string.
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Warning: Null value is eliminated by an aggregate or other SET operation (50,380)



STEPS TO REPLICATE
-----------------------
1. Try to create index HC_TL_GBL_TIMESHEET
2. It will fail with the above error.


Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


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