Technical Articles
Extract absence from SuccessFactors for several month
Hello,
the request is to extract the absence days and target days from SuccessFactors for every employee for several month and year. It is realised with the an IFlow in the Integration Suite. The content should be deliver in CSV format.
It should be looks like:
PersNo,Year,Month,TargetDays,AbsenceDays
200112,”2021″,”12″,”20.00″,”3.00″
200112,”2022″,”01″,”21.00″,”1.00″
200112,”2022″,”02″,”19.00″,”2.00″
200112,”2022″,”03″,”19.00″,”2.00″
200112,”2022″,”04″,”20.00″,”0.00″
200126,”2021″,”12″,”20.00″,”0.00″
200126,”2022″,”01″,”20.00″,”4.00″
200126,”2022″,”02″,”20.00″,”0.00″
200126,”2022″,”03″,”20.00″,”1.00″
200126,”2022″,”04″,”20.00″,”2.00″
Requirement in SuccessFactors
In the database in Successfactors we need a flag that only employees which should taken into consideration. We use the field customString4 in the entity EmpJob.
Used Entities
Entity | Description | Navigation | Query Options |
User | to select userId | empInfo/jobInfoNav/customString4 | $select=userId,firstName,lastName &$filter=empInfo/jobInfoNav/customString4 eq ‘TRUE’ &$orderby=userId |
EmpEmployeeCalendar | used for absence and targed days | not neccesary | $filter=userId eq ‘${property.empId}’ &fromDate=${property.firstDayOfMonth} &toDate=${property.lastDayOfMonth} |
IFlow
I create a IFlow to manage the logic and request.
- create a sender with adapter type HTTPS
- create Local Integration Process (select the employee IDs, name:LIP read User )
- create Local Integration Process (select the abcense Data, name: LIP Employee time )
- create a groovy script to set the last day, first day of a month
- create a Content Enricher (query of EmpEmployeeCalendar)
- create a groovy script to set the loop counter
- create a groovy script to create the new xml structure
- use the main Integration Process to implement the main steps
- Process call (LIP read User)
- create a Iterating Splitter (xPath /User/User)
- create a content Modifier ( preset of properties and Message body)Exchange Property
Action Name Source Type Source Value Data Type Create finalXml Expression Create maxLoops Expression {{maxLoops}} Create empId XPath /User/userId String Create firstDayOfMonth Expression 1900-01-01T00:00:00 String Create lastDayOfMonth Expression 1900-01-01T00:00:00 String Create loops Expression 0 String Message Body
Type: Expression
Body : <Employee></Employee> - create a Looping Process Call (LIP Employee time, condition Expression: ${property.loops} < ‘${property.maxLoops}’ )
- create groovy script to root tags
- create Gather (Incoming format: XML (Same Fomat), Aggregation Algorithm: Combine
- create groovy script to remove not used xml tags.
- create XML to CSV to convert the content
- logs the body
Groovy Scipts
setLastFirsDateOfMonth.gsh
It is necessary to make the query for one month with first and the last day. Here I set the first and last day of a month. This depends on the loop.
import com.sap.gateway.ip.core.customdev.util.Message;
import groovy.time.*
def getLastDay(now) {
def month = now.get(Calendar.MONTH)
def year = now.get(Calendar.YEAR)
def cal = GregorianCalendar.instance
cal.set(year,month,1) // set(year,month,day)
return cal.getActualMaximum(Calendar.DAY_OF_MONTH)
}
def Message processData(Message message) {
def body = message.getBody()
def loops = message.getProperty('loops')
def loopsInt = loops.toInteger();
def now = GregorianCalendar.instance
now.set(Calendar.SECOND,00)
now.set(Calendar.HOUR,24)
now.set(Calendar.MINUTE,00)
now.set(Calendar.MILLISECOND,000)
def addedDate = GregorianCalendar.instance
addedDate.set(Calendar.SECOND,00)
addedDate.set(Calendar.HOUR,24)
addedDate.set(Calendar.MINUTE,00)
addedDate.set(Calendar.MILLISECOND,000)
// set now month
switch(loopsInt) {
case 0:
addedDate.add(Calendar.DAY_OF_MONTH, -(getLastDay(now)) - 1)
break;
case 1:
addedDate.add(Calendar.DAY_OF_MONTH, -1)
break;
case 2:
addedDate.add(Calendar.DAY_OF_MONTH, +getLastDay(now) - 1)
break;
case 3:
addedDate.add(Calendar.DAY_OF_MONTH, +(getLastDay(now)) * 2 - 1)
break;
case 4:
addedDate.add(Calendar.DAY_OF_MONTH, +(getLastDay(now)) * 3 - 1)
break;
default:
break;
}
addedDate.set(Calendar.DAY_OF_MONTH , 1)
//message.setProperty('firstDayOfMonth', addedDate.format("yyyy-MM-dd'T00:00:00.000'"));
message.setProperty('firstDayOfMonth', addedDate.format("yyyy-MM-dd"));
addedDate.set(Calendar.DAY_OF_MONTH , getLastDay(addedDate))
//message.setProperty('lastDayOfMonth', addedDate.format("yyyy-MM-dd'T00:00:00.000'"));
message.setProperty('lastDayOfMonth', addedDate.format("yyyy-MM-dd"));
addedDate.setTimeInMillis(now.getTimeInMillis())
return message
}
setLoopsCounter.gsh
Set the loop counter, which is used in the Looping Process Call 1.
import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
import java.io.Reader
def Message processData(Message message) {
def body = message.getBody(Reader)
//Org-Model-Objekt aus vorhergehendem Script-Step aus Property lesen
def loops = message.getProperty('loops')
def loopsInt = loops.toInteger();
loopsInt += 1
message.setProperty('loops', loopsInt);
return message;
}
createNewXML.gsh
After reading the records for each day, it is essential to build the sum for the absence and target days. After that I build the new xml structure.
Hint:
Here i divide to 8 h/day. This is a little diffuse because the daily work can differ per employee
import com.sap.gateway.ip.core.customdev.util.*;
import groovy.util.XmlSlurper;
import groovy.xml.MarkupBuilder;
import java.io.StringWriter;
class UserDays {
String userId;
String month;
String year;
Double absenceDays;
Double targetDays;
}
def setUserDays (UserDays userDays, Double absence, Double targetDays, id, String month, String year ) {
userDays.setAbsenceDays(absence)
userDays.setTargetDays(targetDays)
userDays.setUserId(id)
userDays.setMonth(month)
userDays.setYear(year)
return userDays;
}
static def addQuotationMark(value) {
return "\"" + value.toString() + "\""
}
def Message processData(Message message) {
def body = message.getBody(java.lang.String) as String;
def finalXml = message.getProperty('finalXml')
def rootXmlProvider = new XmlSlurper().parseText(body)
//asOfDate
def asOfDate = rootXmlProvider.Message2.EmpEmployeeCalendar.EmpEmployeeCalendar[0].asOfDate.text();
if(!asOfDate) {return message};
def sMonth = asOfDate.substring(5,7)
def sYear = asOfDate.substring(0,4)
def empRet = []
def userDaySet = []
// User ermitteln
def users = rootXmlProvider.Message2.EmpEmployeeCalendar.EmpEmployeeCalendar.findAll {it.userId.text()}.each { emp -> empRet.add(emp.userId.text()) }
def userUnique = empRet.unique();
// allocated = AbsenceHours
def userDays = new UserDays();
userUnique.each {
def sum = 0
def id = it;
def user = rootXmlProvider.Message2.EmpEmployeeCalendar.EmpEmployeeCalendar.findAll { it.userId.text().contains(id) }.each { emp -> if (emp.status.text() == 'OK') { sum += emp.allocatedHours.text() as Double }}
def absence = 0.0
if ( sum != 0.0 ) {absence = (sum/8).round(1) }
def targetDays = 0.0
def ud = setUserDays(userDays,absence, targetDays, id, sMonth, sYear)
}
//targetWorkingHours == gearbeitete Zeit
userUnique.each {
def sum = 0
def id = it;
def user = rootXmlProvider.Message2.EmpEmployeeCalendar.EmpEmployeeCalendar.findAll { it.userId.text().contains(id) }.each { emp -> if (emp.status.text() == 'OK') { sum += emp.plannedWorkScheduleHours.text() as Double }}
def targetDays = 0.0
if ( sum != 0.0 ) {targetDays = (sum/8).round(1) }
def absence = userDays.getProperty('absenceDays')
def ud = setUserDays(userDays, absence, targetDays, id,sMonth, sYear )
userDaySet.add(ud)
}
// XML erzeugen
def writer = new StringWriter()
new MarkupBuilder(writer).EmployeeTime {
userDaySet.each { uds ->
"userId"(addQuotationMark(uds.getAt('userId')))
"year"(addQuotationMark(uds.getAt('year')))
"month"(addQuotationMark(uds.getAt('month')))
"absenceDays"(addQuotationMark(uds.getAt('absenceDays')))
"targetDays"(addQuotationMark(uds.getAt('targetDays')))
}
}
def loops = message.getProperty('loops')
def maxLoops = message.getProperty('maxLoops')
finalXml += writer.toString()
message.setProperty('finalXml',finalXml)
message.setBody("<Employee></Employee>");
return message;
}
setXMLToBody.gsh
Add the root tag. This is necessary for the gather step
import com.sap.gateway.ip.core.customdev.util.Message;
import groovy.xml.XmlUtil;
def Message processData(Message message) {
def finalXml = message.getProperty('finalXml')
def finalXMLRoot = "<root>" + finalXml + "</root>"
message.setBody(XmlUtil.serialize(finalXMLRoot))
return message
}
removeTags.gsh
At the end remove all not longer need tags and at the root tag around the body content
import com.sap.gateway.ip.core.customdev.util.Message;
def Message processData(Message message) {
def body = message.getBody(java.lang.String) as String;
body = body.replace("<?xml version=\'1.0\' encoding=\'UTF-8\'?>","")
body = body.replace("<multimap:Messages xmlns:multimap=\"http://sap.com/xi/XI/SplitAndMerge\">","")
body = body.replace("</multimap:Messages>","")
body = body.replace("<multimap:Message1>","")
body = body.replace("</multimap:Message1>","")
body = body.replace("<root>","")
body = body.replace("</root>","")
body = body.replace("\n","")
finalXMLRoot = "<root>" + body + "</root>"
message.setBody(finalXMLRoot)
return message
}
IFlow Screenshots
Screenshot of Main Integration Process
Screenshot of LIP read User
Screenshot of LIP Employee Time
Result:
I request this Iflow for 140 users and it takes 6 minutes and 40 sec. The reason is, that I request every month six times and user to receive the absence days.
Do you have any questions or comments?
If you have any questions, feedback, or comments, don’t hesitate to post them in the comments section below. We’d love to hear from you!