2772567 - Compensation - Error when applying eligibility rules (Invalid DATE, TIME OR TIMESTAMP value)

When applying eligibility rules, the attempt fails. Downloading the jobResponse.txt file or viewing the job through Provisioning shows the following error messages:

SQLException: [303]: invalid DATE, TIME or TIMESTAMP value: search table error:  [6931] 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.

**Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental**

• SAP SuccessFactors Compensation
• SAP SuccessFactors HXM Suite

1. Go to Compensation Home
2. Select your template
3. Click on Plan Setup > Plan Details > Eligibility
4. Click Apply
5. Go to Actions for All Plans > Monitor Jobs
6. Look for the Apply Eligibility Rule job with the Job Status "Failed"
7. Click on Download Status
   
   2. 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: [303]: invalid DATE, TIME or TIMESTAMP value: search table error:  [6931] 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.
      
      
   3. 
8. Go to Actions for All Plans > All Plans > Employee Data Export
9. Click Specify Compensation Data Updating Options
10. Check  Include User Compensation Data and  Include imported compensation field(s)
11. Select your plan
12. Click Export User File
13. When you open the file, the dates in the custom date field appear to be in the correct format
14. Save the UDF without making changes, and import
15. 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

SAP SuccessFactors Compensation all versions ; SAP SuccessFactors HCM Suite all versions