When applying eligibility rules, the attempt fails. Downloading the jobResponse.txt file or viewing the job through Provisioning shows the following error messages:
SQLException: : invalid DATE, TIME or TIMESTAMP value: search table error:  attribute value is not a date or wrong syntax;attribute value is not a date or wrong syntax
Downloading and re-uploading the User Directory File (UDF) via Employee Data Import with zero changes will clear up the issue. However, after an update (such as a user update via ODATA API), the problem returns.
*NOTE: All screenshots are taken from demo environment *
SAP SuccessFactors HCM Core
- SuccessFactors Compensation Management
Reproducing the Issue
- Go to Compensation Home
- Select your template
- Click on Plan Setup > Plan Details > Eligibility
- Click Apply
- Go to Actions for All Plans > Monitor Jobs
- Look for the Apply Eligibility Rule job with the Job Status "Failed"
- Click on Download Status
- The error message will read:
This scheduled job failed to complete. For details, please contact SuccessFactors customer support.
com.successfactors.jobscheduler.ScheduledJobExecutionException: com.successfactors.compensation.util.exception.CompensationEJBException: Exception while CompMiscUtil.applyEligRule: ; nested exception is:
com.successfactors.sca.service.ServiceLegacyRollbackException: Wrapped Exception: DAOException caught: DAOException caught: Rule: [Rule Name], SQLException: : invalid DATE, TIME or TIMESTAMP value: search table error:  attribute value is not a date or wrong syntax;attribute value is not a date or wrong syntax
Where: [Rule Name] will be the name of the Eligibility Rule triggering the error.
- The error message will read:
- Go to Actions for All Plans > All Plans > Employee Data Export
- Click Specify Compensation Data Updating Options
- Check Include User Compensation Data and Include imported compensation field(s)
- Select your plan
- Click Export User File
- When you open the file, the dates in the custom date field appear to be in the correct format
- Save the UDF without making changes, and import
- Applying eligibility will go through without errors.
The Eligibility Engine expects custom dates to follow the format mm/dd/yyyy. In this particular case, the custom date field is being updated via API to a different date format (yyyy-mm-dd). The Eligibility Engine doesn't translate it; instead it is upated as is and is thus counted as invalid.
The succeeding UDF export/import does not show the problem, as Microsoft Excel and most spreadsheet programs reading a date field is designed to recognize multiple date formats, and will display the date in the appropriate format for the user's local (mm/dd/yyyy). Saving the file in Excel with no changes will also correct the date format issue.
To actually see the discrepancy in the date format, perform a fresh Employee Data Export after getting the Eligbility error, and open the UDF with a plain text editor instead of a spreadsheet program. You will see that while the spreadsheet program automatically shows the correct date format (mm/dd/yyyy), the text editor will show the actual data format, and it will be incorrect:
Always make sure that any custom date fields being uploaded into the system, whether via UDF or API, that it follows the mm/dd/yyyy format expected by the eligibility engine. Saving the UDF in Excel as a CSV - comma separated values file will automatically sanitize your dates and bring them in line with mm/dd/yyyy. If your business requires that employee data is updated via API, then use mm/dd/yyyy for any custom compensation fields that are being updated.
2084628 - Eligibility Engine - Eligibility Rules - Compensation
2084530 - Eligibility Engine - Eligibility Rules using Custom Fields - Compensation
2409580 - Compensation – Eligibility rules failed due to DAOException - ORA-00001
2371908 - Compensation Management: “A non-numeric character was found where a numeric was expected” error when applying Eligibility Rules
KBA , LOD-SF-CMP-ADM , Admin Tools, Settings, Permissions , Problem