If you’re reading this, you probably have SuccessFactors (SFSF) in your company’s environment and further, you probably use Employee Central (EC) as your HRIS repository. Even further, you probably use the SOAP method to obtain user data, which uses EC’s CompoundEmployee API. This setup is probably the best method to gather HR data as it allows for validity dates. If an employee is going to, for example, retire at the end of the month, HR can future date that retirement employment status and that feeds down into IDM. When the retirement date hits, it’s automatically applied. We don’t need a feed from EC right at midnight to pick up something like that. We got it weeks ago so now’s it’s taking place.

The problem with the OOTB solution from SAP is that, when you modify the Initial Load job to function as a Delta Load or Full Pull job, i.e. disabling the portions that create the privileges and account attribute for the repo, the tables that the read passes create are just setup to contain data related to any given user however they come down in the XML response from the SOAP API call. They’re generally all clustered together in the SQL tables but that’s not an absolute given and it’s possible, in fact likely, that a group of attributes with a given start date or end date are not sorted in order. In the project I’m currently working, I was seeing them very much come down out of order.

This creates a problem because the passes that write this information to the user’s in the Identity Store just do a select all when reading the tables the write passes created, i.e. “select * from sapSFSF_job_information”. Also worth noting is that attributes don’t come into those tables individually with these validity dates, they come as a whole set. Only one attribute might be getting changed based on these validity dates but all the attributes in that set are given the validity dates by the To IdentityStore pass.

Here’s an example using my future dated retirement situation.

Current data for user, “bbollin”. Let’s say these values have no validity dates of any kind. When I wrote this, today’s date is 2022-03-30. That will be important in a minute.
MX_FS_EMPLOYMENT_STATUS_ID = A
MX_FS_EMPLOYEE_GROUP_ID = 1

A set of data with the future dated retirement comes down from EC. It also contains the employee group setting. Now both values are set apply on 2022-05-01.
MX_FS_EMPLOYMENT_STATUS_ID = {ValidFrom=2022-05-01}R
MX_FS_EMPLOYEE_GROUP_ID = {ValidFrom=2022-05-01}1

When IDM sees that second line, since our current values have no validity, it considers this overlapping validity and ignores it. Rightly so. The outcome of this in a vacuum is the same so, all good, right? But wait, the, “select *” statement is still returning rows for this account and later in the results, we get this:
MX_FS_EMPLOYMENT_STATUS_ID = {ValidTo=2022-04-30}A
MX_FS_EMPLOYEE_GROUP_ID = {ValidTo=2022-04-30}1

This gets processed for both values because it’s perfectly legitimate, again in a vacuum. But now, we have the employee group ID setup to end date on April 30th and the same value that was supposed to replace it on May 1st was ignored because of overlapping validity so thus, when this ValidTo date arrives, the employee group ID will simply self destruct and there will be no value for that attribute on the user’s account.

If employee group ID has an event processes for delete, it’s going to kick off on this date and we probably don’t want that to happen. Also, if the value of employee group plays a role in any decision making processes you have setup for your IDM environment when a role is added or something else happens, having that value gone could mess up your script’s or process’ logic.

What needs to happen is, we need the Write to Identity Store passes in the EC Load Job to process rows in the tables by oldest end date first. This way, any values that end on a given date will be applied to MX_PERSON accounts in the order they expire. This will prevent the situation I described above. Unfortunately, the tables the Read from EC passes created have a refID (matching IDM accounts’ MSKEYVALUEs) and an information column which has the attribute values along with validity dates in a big, long string. We need the select statement in the Source tab to parse out those dates and sort by them. Luckily, I have figured this out for you! The below example is based on an Oracle DB so if you’re running DB2, MS-SQL, or Sybase, you’ll need to test to see if these SQL functions work for you but if not, your environment should have something similar. Just sub out function calls as needed.

select refid, employmentid, TO_DATE(SUBSTR(information, INSTR(information, 'start_date=') + 11, 10), 'yyyy-mm-dd') StartDate,
TO_DATE(SUBSTR(information, INSTR(information, 'end_date=') + 9, 10), 'yyyy-mm-dd') EndDate, information
FROM sap%$rep.$NAME%_jobinformation
where information is not null
order by enddate

So there you go! Hopefully headache and hours of troubleshooting this issue saves someone else some time. Have a great day!