2638422 - SF OData query in the Employee Central entities - historical records missing in the response

You are trying to build OData API queries in the Employee Central entities and you have some difficulties to find all the records or some specific historical record in the response.

SuccessFactors Employee Central - OData API

Run your SF query in the Integration Center / middlewares or with any REST client like postman and you do not see your expected results.

The Employee Central effective dated entities like EmpJob, PerPerson, PerPersonal, EmpCompensation and others has some specific OData API coding to always return the LATEST record active in the database.

To share the behavior and knowledge, please find below the examples of our SalesDemo SF instance.

The employee userId = 'rlam1' have 3 historical records in the Job Information segment (EmpJob entity).

• Jun 2, 2017 (latest)
• Feb 1, 2010
• May 1, 2007

 

To show easier these 3 records, I created one simple Integration Center interface showing all these 3 records filtering by:

• userId = 'rlam1'
• Effective From 1900-01-01
• Effective to = 9999-12-31

Please notice that the column "Start Date" has the same dates of the user interface screenshots above.

 

Now let's start doing OData API queries using 4 different scenarios.

 

----------------

FIRST:

QUERY = https://apisalesdemo4.successfactors.com/odata/v2/EmpJob?$select=seqNumber,startDate,userId,endDate,company,department,division&$filter=userId eq 'rlam1'&$orderby=endDate desc&$format=json

RESPONSE = The system is returning only the LATEST record (Jun 2, 2017), because we did not specify in the query any filter by dates.

 

SECOND:

QUERY = https://apisalesdemo4.successfactors.com/odata/v2/EmpJob?$select=seqNumber,startDate,userId,endDate,company,department,division&$filter=userId eq 'rlam1'&$orderby=endDate desc&$format=json&fromDate=1900-01-01&toDate=9999-12-31

RESPONSE = The system is returning all the 3 records of the database, because we used the following parameters    fromDate=1900-01-01    and     toDate=9999-12-31

 

THIRD:

https://apisalesdemo4.successfactors.com/odata/v2/EmpJob(seqNumber=1L,startDate=datetime'2010-02-01T00:00:00',userId='rlam1')

RESPONSE = The system is returning one error (Not found!), because we did not specify in the query any filter by dates and BY default the system is trying to find the LATEST record.  This latest record does not match with this query statement.

 

FOURTH:

https://apisalesdemo4.successfactors.com/odata/v2/EmpJob(seqNumber=1L,startDate=datetime'2010-02-01T00:00:00',userId='rlam1')?&toDate=9999-12-31&$format=json

 

RESPONSE = The system is returning the record that we asked in the query (Feb 1, 2010), because we specified in the query the parameter toDate=9999-12-31 and this forced the system to check all the historical records instead of the LATEST.

This behavior explained above works for the following EC entities:

Entity name

EmpJob

EmpCompensation

EmpPayCompRecurring

PerPersonal

PerGlobalInfoUSA, PerGlobalInfoXXX

PerAddressDEFLT

PerPersonRelationship

EmpJobRelationships

FOPayGroup

FOPayComponentGroup

FOPayComponent

FOEventReason

FOBusinessUnit

FOCompany

FOLegalEntityLocalUSA

FOLegalEntityLocalDEFLT

FOCostCenter

FODepartment

FODivision

FOGeozone

FOLocationGroup

FOLocation

FOCorporateAddressDEFLT

FOJobCode

FOJobClassLocalUSA

FOJobClassLocalDEFLT

FOJobFunction

FOPayGrade

FOPayRange

SAP SuccessFactors HCM Suite OData API: Developer Guide
SAP SuccessFactors Employee Central OData API: Reference Guide

Both guides can be found in the link = https://help.sap.com/viewer/p/SAP_SUCCESSFACTORS_HCM_SUITE

SuccessFactors OData API query, Historical records missing in the response, Employee Central (EC) effective dated entities behavior, toDate, fromDate, Latest record , KBA , LOD-SF-INT , Integrations , LOD-SF-INT-ODATA , OData API Framework , LOD-SF-INT-API , API & Adhoc API Framework , LOD-SF-INT-EC , Employee Central SFAPI & OData Entities , How To

SAP SuccessFactors Employee Central all versions ; SAP SuccessFactors HCM Suite all versions