Extract absence from SuccessFactors for several month

[gwerner]

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.

1. create a sender with adapter type HTTPS


2. create Local Integration Process (select the employee IDs, name:LIP read User )


3. create Local Integration Process (select the abcense Data, name: LIP Employee time )
   
   
   
   1. create a groovy script to set the last day, first day of a month
   
   
   2. create a Content Enricher (query of  EmpEmployeeCalendar)
   
   
   3. create a groovy script to set the loop counter
   
   
   4. create a groovy script to create the new xml structure
   
   
   
   


4. use the main Integration Process to implement the main steps
   
   
   
   1. Process call (LIP read User)
   
   
   2. create a Iterating Splitter (xPath /User/User)
   
   
   3. 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>
   
   
   4. create a Looping Process Call (LIP Employee time, condition Expression: ${property.loops} < '${property.maxLoops}' )
   
   
   5. create groovy script to root tags
   
   
   6. create Gather (Incoming format: XML (Same Fomat), Aggregation Algorithm: Combine
   
   
   7. create groovy script to remove not used xml tags.
   
   
   8. create XML to CSV to convert the content
   
   
   9. 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!