Clarity: OOTB portlets/Jobs failing with datetimeoffset is incompatible with MS SQL Server

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

STEPS TO REPRODUCE

1. Login to Clarity Classic UX
2. Go to a Reports and Jobs page
3. Run the job Delete Process Instance by setting date Finish Date From = 01 Jan 2019 and Finish Date To = 21 Jun 2019
4. Or Go to  Clarity Classic UX -->Financial management -->Transactions Entry and search by Date Parameters  and you will get Contact System Administrator error
5. Go to logs and you will see the error even though Delete Process Instance job completes but no process instances are 

  ERROR 2019-06-21 06:21:40,602 [Dispatch Delete Process Instance : bg@NIKU123 (tenant=clarity)] niku.xql2 (clarity:X186662:213384306__0E8EF4FF-212E-4207-8EEA-F630CDC0BB96:Delete Process Instance) Internal Processing exception

com.niku.union.persistence.PersistenceException:

SQL error code: 206

Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Operand type clash: datetimeoffset is incompatible with int

SQL error code: 8180

Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Statement(s) could not be prepared.

Executed:

 SELECT TOP 1000  INSTANCES.ID INSTANCE_ID

          FROM

                  BPM_DEF_PROCESSES PROCESSES,

                  BPM_DEF_PROCESS_VERSIONS VERSIONS,

            BPM_RUN_PROCESSES INSTANCES,

                  CMN_CAPTIONS_NLS CAPTIONS,

                  CMN_LOOKUPS_V LOOKUP,

            SRM_RESOURCES RESOURCES

          WHERE

            INSTANCES.STATUS_CODE IN ('BPM_PIS_DONE', 'BPM_PIS_ABORTED') AND

            VERSIONS.PROCESS_ID = PROCESSES.ID AND

            CAPTIONS.PK_ID = PROCESSES.ID AND

            INSTANCES.PROCESS_VERSION_ID=VERSIONS.ID AND

            CAPTIONS.LANGUAGE_CODE = ? AND

            CAPTIONS.TABLE_NAME = 'BPM_DEF_PROCESSES' AND

            LOOKUP.LANGUAGE_CODE = ? AND

            LOOKUP.LOOKUP_TYPE = 'BPM_PROCESS_INSTANCE_STATES' AND

            INSTANCES.STATUS_CODE = LOOKUP.LOOKUP_CODE AND

            RESOURCES.USER_ID = INSTANCES.INITIATED_BY  AND ( 1 = ( SELECT NIKU.CMN_SEC_CHECK_RIGHT_FCT (?, 'BPM','PROCESS-INITIATE',

            'BPM_DEF_PROCESSES','READ', versions.ID ) )

              OR

               1 = ( SELECT NIKU.CMN_SEC_CHECK_RIGHT_FCT (?, 'BPM','PROCESS-DELETE',

            'BPM_RUN_PROCESSES','permProcessDelete',versions.ID ) ) )      AND UPPER(CAPTIONS.NAME) LIKE UPPER(?) ESCAPE '\' AND INSTANCES.END_DATE > ? AND INSTANCES.END_DATE < ? + 1

6. Go to logs after performing the Step 4 and you will see error 

java.lang.RuntimeException: com.niku.union.persistence.PersistenceException:

SQL error code: 206

Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Operand type clash: datetimeoffset is incompatible with int

SQL error code: 8180

Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Statement(s) could not be prepared.

Executed:

 SELECT

               WIP.TRANSDATE transactionDate,

               WIP.TRANSTYPE transactionType,

               WIP.TRANSNO transNo,

               INV.NAME investmentName,

               (SELECT PRNAME FROM PRCHARGECODE WHERE PREXTERNALID = WIP.CHARGE_CODE) chargeCode,

               (SELECT PRNAME FROM PRTYPECODE WHERE PREXTERNALID = WIP.INPUT_TYPE) inputType,

Expected Results: The Job and filtering by date should work 
Actual Results: The Job and filtering by date fails 

Environment

Release : 15.6

Component : CA PPM APPLICATION

Cause

It you look at your properties.xml you will see parameter DateTimeInputParameterType=dateTime and if that is missing in your connection string you will get the error 

url="jdbc:clarity:sqlserver://MSSQL.abc.broadcom.net:1433;DatabaseName=SP643568_PPM_08121901;InsensitiveResultSetBufferSize=0;ProgramName=Clarity;DateTimeInputParameterType=dateTime

Resolution

Add the parameter DateTimeInputParameterType=dateTime to your database connection strings and example is below 

url="jdbc:clarity:sqlserver://MSSQL.abc.broadcom.net:1433;DatabaseName=SP643568_PPM_08121901;InsensitiveResultSetBufferSize=0;ProgramName=Clarity;DateTimeInputParameterType=dateTime