Absence process – Types of data sources

Applies to version 8.3 and higher; Author: Damian Matuła

WEBCON BPS is capable of quickly generating a leave of absence/vacation process.

This is possible through an ‘Absence process wizard’ available from the ‘Tasks’ menu at the top of Designer Studio.

Generating a process through the wizard is very straightforward, it is made up of ten steps during which you will be asked for some mandatory information.

In this article we will focus on organization structures and data sources – both of which essential components to a successful absence process.


Creating data sources of specific types

The potential of absence processes created WEBCON BPS lies in its ability to present data: Gantt charts, tables summarizing absence days, form fields informing users of their remaining vacation days.

In order to make full use of the mentioned features, supplement the process with the following data sources:

  • Vacation type
  • Vacations history
  • Available vacations
  • Days off

To define theses data sources, go to the ‘Data sources’, create a new data source, give it a name, and then pick one of the above as a template.



The SQL query for each individual data source template should be constructed according to the guidelines found in the contextual help (‘i’ button)



– Vacation type

This data source contains all the types of absences used in the system.

This dictionary will be used to:

  • Supplement the ‘Absence type’ choice field on the form
  • Define different colors for absence types on the Gantt chart (legend/key)
  • Checking the number of available vacation days.




According to the information in the contextual help, the SQL query should return the following columns:

Column name Type Description Required?
VACType_ID <String> ID of the absence type Y
VACType_ColorID <Decimal> ID of the color for the given type Y
VACType_Name <String> Name of the absence type Y
VACType_Description <String> Description for each absence type N
VACType_TakeAvailableDays <Boolean> Defines whether the selected absence type deducts from the pool of available vacation days. N


– Vacations history

This data source is used for constructing Gantt charts that give an overview on absences.


The SQL query should return the following columns:

Column name Type Description Required?
VAC_UserID <String> ID of a user Y
VAC_From <Decimal> Decimal representing a date (YYYYMMDD format, e.g. 20120123) Y
VAC_To <Decimal> Decimal representing a date (YYYYMMDD format, e.g. 20120123) Y
VAC_VACTypeID <String> ID of the absence type (same as in the ‘Absence type’ data source) Y


– Available vacations

This data source is used for presenting the number of available and used vacation days. These numbers can be shown to the individual user on the SharePoint form when they are registering a leave of absence request, or data for multiple users can be shown to their superior in the form of a table – in a dedicated Web Part.

Example of use on a SharePoint form:



Example of a Web Part designed for the supervisor:



The SQL query should return the following columns:

Column name Type Description Required?
USR_UserID <String> ID of a user Y
USR_CurrentYear <Decimal> Decimal representing a year Y
USR_DaysAvailableLastYear <Decimal> Number of vacation days left over from previous year Y
USR_DaysAvailableCurYear <Decimal> Number of vacation days available for the current year Y
USR_DaysUsed <Decimal> The number of vacation days used in the current year. If this column is not returned, the value of this field will be calculated based on the leave of absence requests registered in the system N
USR_DaysAvailable <Decimal> The number of vacation days available in the current year. If this column is not returned, the value of this field will be calculated based on the other columns returned by the data source. N


– Days off

This data source is used by the system to calculate the correct number of vacation days to deduct while taking public holidays into account. These non-working days are also displayed on the Absence Summary Gantt chart.


The SQL query should return the following columns:

Column name Type Description Required?
UFD_Date <Decimal> Decimal representing a date (YYYYMMDD format, e.g. 20120123) Y
UFD_UserID  <String> ID of an employee N


Organization structure for Absence workflow

One of the more important elements of am absence process is the underlying organization structure.

By default, the user hierarchy is created based on the table: CacheOrganizationStructure.

If there is a need for a structure different than default, we can define one using typed data sources.

Such a data source is created analogously as the one used for absences, but with the ‘Data source type’ set to ‘Company structure’.



The SQL query should return the following columns:

Column name Type Description Required?
CSTR_UserID  <String> The ID of a user. The values in this column must be unique, i.e. they can be ID’s from the aforementioned SAP Y
CSTR_Login  <String> Login (in the following format: DOMAIN\login). The values in this column must be unique. Y
CSTR_Name  <String> Username Y
CSTR_Superior  <String> ID of the supervisor (null if user does not have supervisor) Y
CSTR_Company  <String> Business entity name N
CSTR_Email  <String> E-mail address N


As we can see in the table above, we require a data source where the logins of our users are associated with ID’s from an external system. We can use the CacheOrganizationStructure table with a column synchronized with the AD that contains such an identifier.

To learn how synchronize with the AD, please take a look at this article: AD cache basics by Marcin Wiktor


In addition to the above, we should also remember about keeping things like data and user hierarchy consistent, i.e. make sure that no user is also their own superior, and that the user at the top of the organization structure is the only one with NULL in their CSTR_Superior column.



To set a data source as a company structure, select the desired process and go to its Settings tab. From there, find the data source in the Business entities structure dropdown menu.



Such a data source can be used like a ‘Person or group’ field in the process:



By Using the methods mentioned above, we can create a fully functional Leave of Absence Process, which will not only make handling LoA requests easier, but it will also display a concise overview of all employees’ absences.

2 thoughts to “Absence process – Types of data sources”

  1. thanks, but this explanation is seriously lacking.
    1. the wizard-created absence process does not contain any sql statements or actions that are visible in the workflow except approving setup and form (date) validation, therefore it cannot be reproduced for more complex scenarios.

    2. the wizard does not set up the sql tables itself and settting up the tables is a real pain because the field types in the description do not match latest MS SQL field types.

    3. the process does not seem to write into the created tables at all (yet the gantt chart for a leave of absence to be approved) shows the time that is requested. it is not clear where this data actually comes from (if it writes / reads from BPS internal tables, it would be good to know how

    4. the wizard is the only way to create a process with an “absence” template. creating such a process without the wizard is impossible, yet there are scenarios where the leave of absence is more complex than the scenario of the wizard-created process. yet editing a wizard created process is not working because – as listed above – several functions ust “seem to work” without actually showing how.

    – update this guide (and up to date MS SQL field types) OR
    – include automatic automatic setup of sql tables for gantt and absence charts and functions
    – provide us with the sql statements and functional logic / dependencies of the sql tables.

    thank you in advance

    1. also:

      5. half days cannot be requested (which is a common scenario , at least in germany)
      6. how does the process refer to the “working days calendar” ? does it interact at all ?

      thanks again

Leave a Reply

Your email address will not be published. Required fields are marked *