Current and future dated data extraction using CompoundEmployee API
Context
In this blog, I intend to explain the various data extraction mechanism that can be used to retrieve current snapshot and future dated data from SuccessFactors Employee Central using the compound employee API. Though this is a basic functionality of the API, I think it’s important to understand what query should be used and in which business scenarios.
Business Scenarios
In HR transformation projects, it’s not very uncommon to have full load interfaces in the integration landscape. By full load interfaces, I meant interfaces, where a set of employees ( based on country, company etc..) are sent across to downstream systems on a regular interval ( e.g daily once, weekly once etc..) irrespective of there is data change or not compare to the last interface run.Though it is a good practice to use delta/incremental interface wherever possible, that does not work out always, as sometimes it’s the downstream application system, which is not capable enough to absorb the data in delta/incremental manner, sometimes, it’s the project timeline/scope which does not demand a change in the as-is extraction type.
I will try to explain with examples on how to extract the current snapshot of employee data and also extract both current and future dated data.
I’ve used the tool Soap UI to demonstrate the API query.It does matter which middleware ( e.g Dell Boomi, SCI/HCI, SAP PO, Mulesoft etc.) one uses, the query and the underlying logic would remain same.
How to extract a current snapshot of employee data
In this diagram, the employee has 2 home address records, once current ( valid till 5th Sept) and the other one is a future dated record ( valid from 6th Sept). The requirement here is to extract only the current data and NOT the future record.
To extract current data one can use the below query in CompoundEmployee API in the full transmission query mode ( which is a default query mode – so no need to explicitly use this parameter in the query). Also note, address information is just an example – the API will fetch current data from all the portlets.
SOAP UI query request screenshot
SOAP UI query response screenshot, which has returned just one address record which is a current record.
how to extract a current snapshot+ future dated record
In this diagram, the employee has 2 home address records, once current ( valid till 5th Sept) and the other one is a future dated record ( valid from 6th Sept). The requirement here is to extract both the records.
The query is same as current data query with only difference being there is “>” (greater than) is added along with “=”.
API request for current +future record
API response with both current and future dated records.
For non-effective dated portlets ( e.g email, phone, national id etc), API will always return only one record as these portlets do not store history.
Blog on – How to extract delta data
very useful thanks sir
thanks Sridhar
Hi Biplab,
I have a question for you. We are running into an issue with the compound employee api.
Let's say we have an employee that was changed on January 16th, to add a future termination date of January 19th. When we run the compound employee API on January 17th call to get all employees changed from January 10th (one week before this job running), then the future record is retrieved. No issue here.
The problem we are having is that, when we run the compound employee API a week later on January 24th to get all of the employees changed since January 17th, we expected to get the employee that had the future termination of January 19th. However, this record is not picked up. So it looks like a future dated event does not generate the system event at that given date. Have you seen this behavior? So it looks this event falls through the cracks... It looks like we need to do another query (to get all employees with a termination date within the last week).
Any thoughts? Your feedback would be appreciated. I can also create a question - but I thought you might be able to provide some light since you have this BLOG.
Thanks,
Jay
Actually - i think I found the approach - I need to pass the from and to date:
This way even if the change date itself was outside the period delta, the effective date is within the period delta so we can capture this change.
SELECT person, personal_information, address_information, email_information, phone_information, person_relation, employment_information, global_assignment_information, job_information, alternative_cost_distribution, compensation_information, paycompensation_recurring, paycompensation_non_recurring, payment_information FROM CompoundEmployee WHERE last_modified_on > to_datetime('2018-01-18T00:00:00Z') and fromDate = to_date('2018-01-18','yyyy-MM-dd') and toDate = to_date('2018-01-24','yyyy-MM-dd') ORDER BY start_date DESC
Regards,
Jay
Hi Jay,
Sorry for the late response. Yes, you are correct. You have to use fromDate and toDate. Just using lastModifedDate >= lastRunDate won't in this case because as per your example, termination record was created on 16th Jan ( effective 19th Jan), so if query is run on 24th which checks records changed from last 1 week( i.e 17th), it will never fetch the termination record. However, if you use the fromDate and toDate, query will pick the effective date records which falls within the fromDate/toDate bracket.
Regards
Biplab Das
Hi,
Can we pass content modifier header value for fromDate & toDate?
Yes. You can construct the WHERE part of query using script and append the WHERE condition in the entire query.
Can we fetch Retroactive data(for a period of 1 month in the past) using fromDate and toDate?
Yes
Hello,
very interesting article and i would like to ask for a little issue in filtering future dated terminations in query.
I used :
SELECT person, employment_information, job_information, national_id_card, personal_information FROM CompoundEmployee WHERE last_modified_on > to_datetime('${property.maxDateFromLastRun1}') AND company_territory_code = 'TUR' AND fromDate = to_date('${property.currentDate}','YYYY-MM-DD') AND toDate = to_date('${property.currentDate}','YYYY-MM-DD')
expected result:
I have 2 records which termination date = today
2 records which termination date = tomorrow
2 records which termination date = day after tomorrow
current result:
I see all records extracted but I want to have only those with termination for today visible.
propertycurrentDate - today
Is it possible to filter future terminated redords in query?
can you help me?
br
Pawel G
Hi Biplab,
i have the requirement where and employee's employment date is ending on 2020-04-23 and he will become contingentWorker on 2020-04-25. The job runs in CPI daily picking changes in last 24 hours.
when job runs on 24th with the query in periodDelta mode
WHERE last_modified_on > to_datetime(‘2020-04-23T00:00:00Z’) and fromDate = to_date(‘2020-04-23′,’yyyy-MM-dd’) and toDate = to_date(‘2020-04-24′,’yyyy-MM-dd’)
it picks only the employment_information where employee end_date is marked as 23/04/2020 but do not get the information of his assignment starting as 24/04/2020.
when job runs on 25th then too no records are pulled even the effective change of assignment was on 25th
Any suggestion?
Hi Vikash,
Check when the future dated contingent record was modified, if it was modified before 23rd April then the condition will fail and it won't be returned. Also, check if there are employee class filter which might restrict the record.
If there is no filter on employee class/type and also last modified date condition is true, the query should return both the records.
I would suggest you try the query in Soap UI first... to replicate the issue.
Regards
Biplab
Hi Biplab, very nice!
Can you help me with my query? I don’t know if it’s possible, but I have to select just actual records of dependents and I am not getting it.
I tried this query but I got the error of invalid SFQL.
SELECT person, dependent_information FROM CompoundEmployee where person_id_external = ‘99999017’ and person.dependent_information.last_modified_on > to_DateTime(‘2020-06-12T22:00:00Z’).
Hi,
Biplab Das - good explanation, thanks.
Do you or Neetin Datar have any idea, why we cannot use the equal operator (=) for start_date?
According to note 2318180 - Fields supported in 'Where' clause of Compound Employee API only the operator >= seems to be allowed.
Background: I want to query all changes and inform various receiver systems about employee updates, which become active at this day.
Is something we can request at SAP development and how?
Thanks
Jo
Hi Biplab,
I want to know one query where I will extract the employee record based on personID where a list of personID is maintained in an excel sheet stored in a local drive. how to write the query, can you please help me.
thanks,
Sourav