2320830 - Limitations of the Compound Employee API

The Compound Employee API offers a list of fields that can be used in the where clause of the query but it has certain limitations.

And while designing the query these must be considered.

Successfactors

The following limitations must be considered by the consumer:

• Expressions within the ‘WHERE’ clause can only be combined by the logical operator AND.
• The SNAPSHOT_DATE can only be compared with the value following the pattern to_datetime(<date value>) in format ‘YYYY-MM-DD"T"HH:MM:SS"Z"’. No other date formats are possible here.
• The EFFECTIVE_END_DATE can only be compared with the value following the pattern to_date(<date value>) in format ‘YYYY-MM-DD’. No other date formats are possible here.
• Unexpected results might be returned, if select parameters of effective dated segments (like EMPLOYEE_CLASS, DEPARTMENT, DIVISION or COMPANY_TERRITORY_CODE of segment (JOB_INFORMATION) are applied together with EFFECTIVE_END_DATE filter in a ‘WHERE’ clause.

Reason 

• EFFECTIVE_END_DATE is used as a filter criterion of the result data. It is not used like the others as selection parameter (as a where condition in the SQL sense) to read data from data base.
• Filtering by EFFECTIVE_END_DATE applies to the result data and does not lead to a deletion of the employee; at least the segment person is kept in the query response. Therefore, it may happen that an employee is contained in the result set because the specified EMPLOYEE_CLASS is contained in one of the time slices of job_information which is, however, filtered out by the EFFECTIVE_END_DATE condition.

An example: Employees of EMPLOYEE_CLASS ‘Employee’ shall get selected; the result shall be filtered by EFFECTIVE_END_DATE.

<queryString>
  SELECT person, personal_information, address_information, employment_information, job_information 
  FROM CompoundEmployee 
  WHERE  employee_class      =  ‘Employee’ ORDER BY start_date ascending / descending </queryString>

The result is: The employee gets selected because it was once an employee; the current job is of EMPLOYEE_CLASS ‘Contractor’. The first and second job records get filtered by the EFFECTIVE_END_DATE filter.

Compound Employee API now also supports ascending and descending sorting by start date for effective dated entities, if the following expression is added in the where clause (in other words, ORDER BY will only work for effective-dated entities)

Request
<queryString>SELECT person, personal_information, address_information, employment_information, job_information FROM CompoundEmployee WHERE  employee_class =  ‘Employee’ ORDER BY start_date ascending / descending </queryString>

Query response is:
<sfobject>
   <id>202</id>
   <type>CompoundEmployee</type>
   <person>
      <person_id>220</person_id>
      <date_of_birth>1985-07-09</date_of_birth>
      <person_id_external>KJACOB</person_id_external>
      ...
      <employment_information>
         <user_id>KJACOB</user_id>
         <jobNumber>1</jobNumber>
         <start_date>2013-07-09</start_date>
         …
         <last_modified_on>2013-07-09T12:42:06.000Z</last_modified_on>
         <employment_id>221</employment_id>
         <direct_reports>0</direct_reports>
         <job_information>
         <job_code>Developer</job_code>
            <start_date>2013-08-01</start_date>
            <end_date>9999-12-31</end_date>
            <created_on>2013-08-02</created_on>
            <last_modified_on>2013-08-02T15:06:49.000Z</last_modified_on>
            <company>ACE_US</company>
            <business_unit>DEV</business_unit>
            <cost_center>Cost</cost_center>
            <employee_class>Contractor</employee_class>
            <fte>1.0</fte>
            …
         </job_information>
      </employment_information>
   </person>
   <execution_timestamp>2013-10-30T14:33:57.000Z</execution_timestamp>
</sfobject>

2318180 - Fields supported in 'Where' clause of Compound Employee API

Query Response Structure in the CompoundEmployee Developer Guide (section 2.8.3.1)

Limitations of Compound Employee API field combinations supported in where clause , KBA , LOD-SF-INT-CE , Compound Employee API , LOD-SF-INT , Integrations , LOD-SF-INT-API , API & Adhoc API Framework , How To