2554717 - Unexpected results when using time-based filters in effective-dated entities - OData API and Integration Center

You are trying to create a query in one Effective dated entity (in this example we are using EmpJob entity) and the time-based filter is not working as expected and returning inaccurate records. Specifically, the 'Modified Since' filter is not giving your desired results.

• When you use the 'Modified Since' filter in combination with the 'Effective From/To' filter to include all records, you do not get the desired results. This grabs ALL records of a given employee where at least one record has been modified since last run time, and thus does not limit it to only the record(s) recently modified.

• If you remove the 'Effective From/To' filter, this grabs the current record only (even if historical records are recently modified [they fall out of the results], and even if the current record was not recently modified).  

Your desired results is to get all Job Info records (current and/or historical) that have been last modified since the last time the integration ran.

You have not been able to configure the integration filters to accomplish this requirement.

NOTE: The information and behaviors described in this KBA apply for both Integration Center and OData API, (as Integration Center is based on OData API). The terms can be mapped from the context of Integration Center to the context of direct OData API queries as follows:

• 'Effective from' and 'Effective to' filter -> 'fromDate' and 'toDate' parameters
• 'Modified since' filter -> lastModifiedDateTime ge datetimeoffset'yyyy-MM-dd'

• SAP SuccessFactors HXM Suite
• Integration Center
• OData API

1. Navigate to Admin Center > Integration Center > My Integrations
2. Click to Edit the Integration
3. Investigate the filter options selected In the preview (Configure Fields tab)

The basic idea always is that the 'Modified since' query checks if an object was changed, not if a "time slice" was changed. Object in this context means the entire job history. One user has many EmpJob records (each corresponding to one job history time slice). The set of all time slices/records is the object which is filtered by the lastModifiedDateTime.

So, the way that the 'Modified since' and 'Effective from/to' filters work together is as follows:

1. User David, for instance, has many job information time slices (EmpJob records);
2. The integration will compare the lastModifiedDateTime of each EmpJob record in David's job history with the integration's 'Modified since' filter condition. 
3. If ANY of the records/time slices satisfies the filter (was modified since the given date), the integration will return all the records that satisfy the effective from/to parameters. For instance:

• If the interval is 'Effective from 1900-01-01' and 'Effective to 9999-12-31', all of the user's records will be returned;
• If the interval is 'Effective from 2020-11-16' and 'Effective to 9999-12-31', all of the user's records which are effective from Nov 16th 2020 onwards will be returned, and all of the previous ones will not be returned, even if they were modified since the given date;
• If no interval is provided, the record that is currently effective will be returned.

To summarize the logic: IF ANY of the user's EmpJob records satisfy the 'modified since' condition, return all the records which were/are effective in the defined from/to interval. If you do not specify any effective from/to interval, only the record which is curently effective will be returned.

Suggestion 1)

1. Implement one interface that send all the Effective Dated entities data every execution ("Effective From" = 1900-01-01 and "Effective To" = 9999-12-31).
2. Store the values received in some internal staging table.
3. Build an additional logic in your other system, doing a comparison between the internal staging table with the values received from the last run, calculating the "Delta" to see the changes from the last run.

Suggestion 2)

1. Build one OData interfaces using the filters "Effective From" = 1900-01-01 and "Effective To" = 9999-12-31 (fromDate=1900-01-01&toDate=9999-12-31) to retrieve all the registers from the entity.
2. After receiving the OData Response, build an additional logic in the middleware like Dell Boomi or CPI to filter out only the registers with "Last modified Date Time" greater than the last run. 
3. This logic can be implemented in the middlewares, but not in the Integration Center.

Before reporting one case to SAP, kindly check all these other helpful KBAs that can help us understanding the OData behavior and Integration center filters in effective dates entities/records.

• 2470903 - Integration Center is not returning historical/future records
• 2430737 - Unexpected results when using "Modified Since Query Mode" in Time-Based Filters while navigating to other entities
• 2752345 - Integration Center: Using datetime range in Advanced Filters
• 2965443 - Fields from future records (records with a start date in the future) are blank - SuccessFactors Integration Center
• 2925189 - Filters in Integration Center are not filtering correctly: wrong records are being returned

Please also check the handbook document about this behavior in OData Employee Central side:

• lastModifiedDateTime and $filter - SAP Help Portal

effective, from, to, lastModifiedDateTime, datetimeoffset, Modified Since, Last modified Date, Time based filters, entities, like, time-based , KBA , LOD-SF-INT-INC , Integration Center , LOD-SF-INT , Integrations , LOD-SF-INT-ODATA , OData API Framework , Problem