Database tables

The table contains the interpreted work time data for a person, according to the valid rules.

When extracting interpreted events, it is advisable to keep the extraction period as short as possible to avoid long data retrieval times. We recommend a maximum extraction period of two months forward and backward.

The table contains events recorded for a person as they were logged.

When extracting recorded events, the extraction period can be longer than when extracting interpreted events. We recommend a maximum extraction period of one year forward and backward.

Yes, excluding the settings that define the timing of the workday.

UserCostGroup information is not available. The data can be found in the UserInfoData table.

Yes, excluding the settings that define the timing of the workday.

UserCostGroup information is not available. The data can be found in the UserInfoData table.

The table contains personal and employment information with history values included. It can be joined with queries to the UserSalaryData and UserEventData tables.

Organization-specific additional data fields are also available by default. A detailed list of available data can be found in 8 - UserInfoData.

Yes, excluding sensitive information:

• RFIDTagNumber
• SocialSecurityNumber

Available only for data from the SalaryData table.

• "BasicTime" - Performed regular working hours and hour-to-hour compensated time
• "ReducedRateTime" - Time with reduced compensation. Certain absences and sick leaves may earn reduced pay
• "UnpaidBasicTime" - Basic working time not compensated. E.g. unpaid absences only generate time up to the work obligation.
• "AdditionalWork" - Additional work earned after regular working hours (length of workday/week) but before the maximum length of the day/week.
• "OvertimeDaily50" - 50% daily overtime.
• "OvertimeDaily100" - 100% daily overtime.
• "OvertimeWeekly50" - 50% weekly overtime.
• "OvertimeWeekly100" - 100% weekly overtime.
• "OvertimeSunday50" - 50% Sunday overtime, earned based on Sunday overtime settings.
• "OvertimeSunday100" - 100% Sunday overtime, earned based on Sunday overtime settings
• "OvertimeBaseFixed" - Overtime 1, used in some Nordic collective agreements (TES)
• "OvertimeExtraFixed" - Overtime 2, used in some Nordic collective agreements
• "TESOvertime50" - TES-based 50% overtime used in some agreements, earned separately from weekly overtime. Only created if setting 'TES overtime earned separately from weekly overtime' is enabled; otherwise, TES-based overtime is regular overtime with IsTesOvertime = "Y".
• "TESOvertime100" - TES-based 100% overtime used in some agreements, earned separately from weekly overtime. Same conditions as above.
• "LevellingPeriodAdditional" - Additional work earned at the end of the leveling period.
• "LevellingPeriodOvertime50" - 50% overtime earned at the end of the leveling period.
• "LevellingPeriodOvertime100" - 100% overtime earned at the end of the leveling period.
• "LevellingPeriodUnderTarget" - If less work is done than the target for the leveling period, includes the under-target time.
• "UnpaidOvertime" - Overtime that is marked Do not compensate and thus not paid.
• "WorkRise" - Work rise earned for work during certain times, e.g. evening or Sunday compensation
• "WorkRisePercentagePart" - Calculated percentage or minute-based work rise compensation per hour
• "WorkIncrement" - Manually recorded work supplement, e.g. manager supplement.
• "WorkIncrementPercentagePart" - Calculated percentage of work supplement contributing to an accrual.
• "PublicHoliday" - Public holiday. Compensation depends on how much it shortens the workweek.
• "YearLeave" - Annual leave

• "WeeklyRestTime" -

  Weekly rest period. Note: when transferring weekly rest compensation to another system using extraction rules, this setting can be defined using the "basePartTarget" field.

• "ProjectOverAllocation" - Time recorded to a project that exceeds the actual event duration.
• "StartupWork" - Startup work compensated as regular working time
• "StartupWorkAdditionalWork" - Startup work compensated as additional work
• "StartupWorkOvertime50" - Startup work compensated as 50% overtime
• "StartupWorkOvertime100" - Startup work compensated as 100% overtime
• "UnpaidStartupWork" - Time marked as startup work that is not compensated.
• "TerminationWork" - Termination work compensated as regular working time
• "TerminationWorkAdditionalWork" - Termination work compensated as additional work
• "TerminationWorkOvertime50" - Termination work compensated as 50% overtime
• "TerminationWorkOvertime100" - Termination work compensated as 100% overtime
• "UnpaidTerminationWork" - Time marked as termination work that is not compensated
• "Accrual" - Ongoing/cumulative value for a specific accrual, e.g. balance or bank
• "AccrualChange" - Change in accrual value from the previous day
• "MarkedTime" - Time recorded for an event. EventStartTime and EventEndTime fields show the timestamps. Not generated for the following:
  • Public holiday events
  • Accrual reset and change events (e.g. balance or annual leave)
• "Lunch" - Lunch break. Indicates how the break is algorithmically placed into the workday
• "OnCall" - Includes actual on-call time. By default, all events recorded over the on-call period are excluded from it, but settings can define exceptions for certain event types
• "PayOverAccrualLimit" - Includes hours automatically trimmed from accrual when the behavior for exceeding the positive accrual limit is Transfer excess to salary
• "UnpaidOverMinAccrualLimit" - Includes hours automatically trimmed from accrual when the behavior for exceeding the negative accrual limit is Transfer excess to salary

• "U" - integer
• "D" - decimal
• "S" - seconds
• "H" - hours
• "J" - days
• "C" - currency

Start date and time when salary data was earned (if available) Note: Long entries, such as sick leave, are distributed across calculation days. Format: YYYY-MM-DD HH:MM:SS

Time zone: User's local time zone

End date and time when salary data was earned (if available) Note: Long entries, such as sick leave, are distributed across calculation days. Format: YYYY-MM-DD HH:MM:SS

Time zone: User's local time zone

Start date and time when the event related to interpreted working time is marked in the calendar (if available). Format: YYYY-MM-DD HH:MM:SS

Time zone: User's local time zone

End date and time when the event related to interpreted working time is marked in the calendar (if available) Format: YYYY-MM-DD HH:MM:SS

Time zone: User's local time zone

Date and time when the event related to this row was created. Format: YYYY-MM-DD HH:MM:SS

Time zone: Finnish time zone

Date and time when the event related to the row was last modified. Format: YYYY-MM-DD HH:MM:SS

Time zone: Finnish time zone

For leveling period and weekly rest compensation, the latest modification time of an event from the final week of the period is used

Unique identifier of the event from the Events table. Useful if you want to group times from the same event (e.g., annual leave salary).

ActivityId does not change when an event is modified.

If ActivityId is negative, it means the event is generated based on settings (e.g., an assumed event or a shift from a work schedule). In this case, ActivityId may change.

Target for work rise compensation.

Supported options:

• "salary" - Directed to salary
• "annualbalance" - Directed to annual absence accrual
• "balance" - Directed to balance
• "bank" - Directed to bank
• "flexiblehours" - Directed to flexible bank
• "flexibleworkhours" - Directed to flexible work hours balance
• "holidaypayleave" - Directed to holiday pay leave accrual
• "levellingperiodhours" - Directed to levelling period hours accrual
• "overtimebalance" - Directed to overtime balance
• "savingsleave" - Directed to savings leave accrual
• "weeklyrest" - Directed to weekly rest accrual
• "worktimeshortenigleave" - Directed to work time shortening leave accrual

The target may also be selected from the base or increased portion of the event’s overtime target.

• "normal" - Normal supplement type for normal event types
• "correction" - Supplements that only appear on Correction events
• "cafe" - Supplements that only appear in terminal cafeteria mode
• "expense" - Supplements that only as a travel invoice expense
• "allowance" - Supplements that only appear as a travel invoice daily allowance
• "tripReimbursement" - Supplements that only appear as a travel invoice trip/transportation reimbursement
• null - not a supplement

The internal identifier of the first project attached to the event.

This column repeats for each linked project, so the first project has number 1, the second has number 2, and so on. The column appears for linked project numbers 1–6.

System-internal identifier for the relationship between the event and the project. Can be joined in a query with: UserSalaryData.EventProjectRowId1 = UserEventProjectData.Id

This column repeats for each linked project, appearing for project numbers 1–6

External identifier of the first project attached to the event.

This column repeats for each linked project, appearing for project numbers 1–6.

Code of the first project attached to the event.

This column repeats for each linked project, appearing for project numbers 1–6.

Name of the first project attached to the event.

This column repeats for each linked project, appearing for project numbers 1–6.

Internal name for the type of the first attached project.

Examples: "location", "project_work", "unit"

This column repeats for each linked project, appearing for project numbers 1–6.

Work that exceeds the length of the workday is split between the BasePartTarget (base portion) and OvertimeTarget (overtime portion).

BasePartTarget defines how the base portion is allocated.

For example, when time is compensated hour-for-hour to balance, no increase portion is paid. The recorded time includes the following allocations: BasePartTarget = 'balance' and OvertimeTarget = 'uncompensated'.

Supported values:

• "salary" - Basepart allocated to salary
• "balance" - Basepart allocated to balance
• "bank" - Basepart allocated to bank
• "overtimebalance" - Basepart allocated to overtime balance
• "uncompensated" - Basepart is not compensated
• "flexiblehours" - Basepart allocated to flexible bank

Work that exceeds the length of the workday is split between the BasePartTarget (base portion) and OvertimeTarget (overtime portion).

OvertimeTarget defines how the overtime portion is allocated.

If work is compensated as overtime, OvertimeTarget has a value other than NULL or "uncompensated". For instance, overtime compensated to bank would result in: BasePartTarget = 'bank' and OvertimeTarget = 'bank'.

Supported values:

• "salary" - Overtime-part allocated to salary
• "balance" - Overtime-part allocated to balance
• "bank" - Overtime-part allocated to bank
• "overtimebalance" - Overtime-part allocated to overtime balance
• "uncompensated" - Overtime portion not compensated
• "flexiblehours" - Overtime-part allocated to flexible bank
• NULL - No overtime portion (used when work is performed during regular hours)

Service’s internal identifier for user.

Not visible in the UI. Use other identifiers whenever possible.

UserId of the person who approved the event.

For leveling period and weekly rest compensation, the last approver of the final week of the period is used.

• "hourly" - hourly wage
• "monthly" - monthly salary

Is the person employed on the specified date

• "Y" - Yes
• "N" - No

If an employee with flexible work time is not working on a day when they have work obligation, hours are borrowed from the balance accrual.

• "Y" - Yes
• "N" - No

Is it TES overtime?

• "Y" - Yes
• "N" - No

Is it emergency work?

• "Y" - Yes
• "N" - No

Is the event approved?

• "Y" - Yes
• "N" - No

Sick leave compensation

• 1 = paid
• 2 = unpaid
• 3 = reduced salary

Sick leave reason

• 1 = sick
• 2 = child sick
• 3 = accident at work
• 4 = accident while commuting
• 5 = accident in free time

Medical certificate

• 0 = not known
• 1 = not received
• 2 = received
• 3 = permission from supervisor

Absence compensation

• 1 = paid
• 2 = unpaid
• 3 = reduced salary

Does the event accrue annual leave days. Value comes from either annual leave settings or event-specific data.

• "Y" - Yes
• "N" - No

May also be null if not relevant to work time data.

• "Y" = Time has been created due to rounding
• "N" = Time has not been created due to rounding

Comma-separated list of event type codes that overlap with clock-ins. These are intentionally configured to fall beneath another clock-in—e.g., on-call events. Must be handled in WHERE clauses like:

WHERE overlapsunderlyingactivitytypeids LIKE '%,33,%'

Unique identifier for uninterrupted time periods interpreted as a single work cycle. Multiple events can be grouped in the same cycle if interpreted payroll doesn’t break.

By grouping by this field, you can get paid blocks from long clock-ins, split by unpaid intervening days.

Example: Absence from May 2–22, 2016

• 2. - 4.5.2016 - Absence Monday–Wednesday
• 6.5.2016 - Absence on Friday (Ascension Day on May 5)
• 9. - 13.5.2016 - Absence Monday–Friday
• 16. - 20.5.2016 - Absence Monday–Friday

Unique identifier of the event from the Events table. Useful if you want to group times from the same event (e.g., annual leave salary).

ActivityId does not change when an event is modified.

If ActivityId is negative, it means the event is generated based on settings (e.g., an assumed event or a shift from a work schedule). In this case, ActivityId may change.

Work that exceeds the length of the workday is split between the BasePartTarget (base portion) and OvertimeTarget (overtime portion).

BasePartTarget defines how the base portion is allocated.

For example, when time is compensated hour-for-hour to balance, no increase portion is paid. The recorded time includes the following allocations: BasePartTarget = 'balance' and OvertimeTarget = 'uncompensated'.

Supported values:

• "salary" - Basepart allocated to salary
• "balance" - Basepart allocated to balance
• "bank" - Basepart allocated to bank
• "overtimebalance" - Basepart allocated to overtime balance
• "uncompensated" - Basepart is not compensated
• "flexiblehours" - Basepart allocated to flexible bank

Work that exceeds the length of the workday is split between the BasePartTarget (base portion) and OvertimeTarget (overtime portion).

OvertimeTarget defines how the overtime portion is allocated.

If work is compensated as overtime, OvertimeTarget has a value other than NULL or "uncompensated". For instance, overtime compensated to bank would result in: BasePartTarget = 'bank' and OvertimeTarget = 'bank'.

Supported values:

• "salary" - Overtime-part allocated to salary
• "balance" - Overtime-part allocated to balance
• "bank" - Overtime-part allocated to bank
• "overtimebalance" - Overtime-part allocated to overtime balance
• "uncompensated" - Overtime portion not compensated
• "flexiblehours" - Overtime-part allocated to flexible bank
• NULL - No overtime portion (used when work is performed during regular hours)

Value in seconds

• "U" - integer
• "D" - decimal
• "S" - seconds
• "H" - hours
• "J" - days
• "C" - currency
•  

Date and time when the event starts.

Format: YYYY-MM-DD HH:MM:SS

Time zone: User's local time zone

Date and time when the event ends.

Format: YYYY-MM-DD HH:MM:SS

Time zone: User's local time zone

Date and time when the event was created.

Format: YYYY-MM-DD HH:MM:SS

Time zone: Finnish time zone

Date and time when the event was last modified.

Note! In the UserEventHistoryData table, if the event is a previous version of a modified event, ModifiedDatetime indicates the time when that particular version was created.

Format: YYYY-MM-DD HH:MM:SS
Time zone: Finnish time zone

Name of the first project attached to the event.

Person's salary type

• "hourly" - hourly wage
• "monthly" - monthly salary

Is the person employed on the specified date

• "Y" - Yes
• "N" - No

Is the event approved?

• "Y" - Yes
• "N" - No

Does the event type require prior approval from a supervisor?

• "Y" - Yes
• "N" - No

Is it emergency work?

• "Y" Yes
• "N" No

Sick leave compensation

• 1 = paid
• 2 = unpaid
• 3 = reduced salary

Sick leave reason

• 1 = sick
• 2 = child sick
• 3 = accident at work
• 4 = accident while commuting
• 5 = accident in free time

Medical certificate

• 0 = not known
• 1 = not received
• 2 = received
• 3 = permission from supervisor

Absence compensation

• 1 = paid
• 2 = unpaid
• 3 = reduced salary

Does the event accrue annual leave days. Value comes from either annual leave settings or event-specific data.

• "Y" - Yes
• "N" - No

Trip start date.
 

Format: YYYY-MM-DD HH:MM:SS Time zone: User's default time zone

Trip end date.

Format: YYYY-MM-DD HH:MM:SS Time zone: User's default time zone

Only used in the UserEventHistoryData table.
 

• Y = Event is active

• N = Event has been deleted or is a previous version of a modified event.

  If the event is a previous version of a modified one, it has an OriginalActivityId assigned.

Only used in the UserEventHistoryData table.

Internal identifier of the modified event for which this event is the previous version. The event’s OriginalActivityId is the same as the ActivityId of its most recent version.

If OriginalActivityId is null, the event is the most recent version.

The ID of the terminal the event was started with.

If the event is started with a terminal but ended using another method - for example, via a mobile device - then the event’s terminalID is removed with the stop entry.

System's internal identifier for the event to which the project entry is linked.

UserId of the person who approved the project entry.

This should not be confused with the approval of the event itself - project-level approvals are often performed by project managers using the Approve project hours tool.

Identifier for the method of transportation used. May include the following values:

• 1 - car
• 2 - airplane
• 3 - ship
• 4 - public transport
• 5 - other

The type of the external identifier. 

Currently, the only supported type is External.

Service’s internal identifier for user.

Not visible in the UI. Use other identifiers whenever possible.

Person's unique identifier for integrations

Formed according to the selected integration. It may be one of the following:

• Identifier set for a specific integration
• Employee number
• Personal identity code

Service’s internal identifier for user.

Not visible in the UI. Use other identifiers whenever possible.

Identifier for personnel data. The table contains additional data fields created for the person.

Value may be, for example:

• AdministrativeAdditionalInfo
• AdditionalReportingInfo
• BIC
• City
• DefaultCostGroupCode
• DefaultProjectId
• DefaultProjectCode_SDSQL
• DefaultProjectName_SDSQL
• Email
• EventsLockedDate
• FinnishTaxNumber
• Firstname
• HolidayScheduleGroupCode_SDSQL
• HolidayScheduleName_SDSQL
• IBAN
• IntegrationIdAastra
• IntegrationIdDLSoftware
• IntegrationIdEmce
• IntegrationIdFivaldi
• IntegrationIdHeeros
• IntegrationIdHogia
• IntegrationIdIFS
• IntegrationIdKieku
• IntegrationIdLessor5
• IntegrationIdLiksa
• IntegrationIdMepco
• IntegrationIdOscar
• IntegrationIdPersonecF
• IntegrationIdPersonecW
• IntegrationIdProCountor
• IntegrationIdScala
• IntegrationIdSedab
• IntegrationIdSonet
• IntegrationIdTikon
• IntegrationIdVisma
• IntegrationIdElisaRing
• IsSalaryAdvanceAllowed
• JobTitle
• Language
• LastWorkingContractBeginDate
• LastWorkingContractEndDate
• LastWorkingContractEndReason
• LastWorkingContractHourlyWage
• LastWorkingContractWeeklyHours
• MicrosoftAxHourlyCost
• NextOfKinName
• NextOfKinTel
• PersonnelNumber
• NeptonPaymentGroupIdentifier
• NeptonPaymentGroupCode_SDSQL
• NeptonPaymentGroupId_SDSQL
• NeptonPaymentGroupName_SDSQL
• Postcode
• PhoneNumber
• PrimarySupervisor_SDSQL
• PrimaryUnit_SDSQL
• RFIDTagNumber
• Role
• RootUserGroupCode_SDSQL
• RootUserGroupId_SDSQL
• RootUserGroupName_SDSQL
• SecondarySupervisor_SDSQL, can have multiple rows
• SecondaryUnit_SDSQL, can have multiple rows
• SettingGroupCode_SDSQL
• SettingGroupId_SDSQL
• SettingGroupName_SDSQL
• SocialSecurityNumber
• StreetAddress
• Surname
• TrialPeriodEndDate
• UserGroupCode_SDSQL
• UserGroupId_SDSQL
• UserGroupName_SDSQL
• Username
• WebPageURL
• WorkArea
• WorkDayLength_SDSQL, can have multiple rows, value is in seconds
• WorkWeekLength_SDSQL, can have multiple rows, value is in seconds
• WorkTimePercentage_SDSQL, can have multiple rows, defaults to 100.00
• WorkScheduleGroupCode_SDSQL
• WorkScheduleName_SDSQL
• WorkTimeRoleId_SDSQL
• WorkTimeRoleName_SDSQL

Things to note:

Work day length and work week length are affected by the work time percentage.
The validities for work day length, work week length and worktime percentage, are not strictly correct. The setting may be valid outside of the period, but it will still have both a validFrom and validTo date that falls within the selected period.

Internal identifier for the type of personal data.

This data should not be used as an identifier for utilizing the information, as it may change.

Date and time from which the personal data is valid. If no start date is set for the personal data, the value is: 1970-01-01 00:00:00 Format: YYYY-MM-DD HH:MM:SS

Date and time until which the personal data is valid. If no end date is set for the personal data, the value is: 2999-12-31 23:59:59 Format: YYYY-MM-DD HH:MM:SS

Field value as a string. The data is presented based on the field type in the following format:

• Boolean (checkbox) - True / False
• Date - YYYY-MM-DD
• Datetime - YYYY-MM-DD HH:MM:SS

Indicates whether the person group is a root-level group, meaning a group with no parent group.

• Y - Yes
• N - No

A numerical value indicating how deep the person group is in the tree structure. For example:

• 0 - root level group
• 1 - subgroup of a root-level group
• 2 - subgroup of the previous level

• etc.

Project end date on the project details page. Format: YYYY-MM-DD

YYYY-MM-DD

Is the project a so-called leaf node, meaning a project that has no subprojects:

• Y - Yes
• N - No

Is the project a root-level project, meaning a project that has no parent project

• Y - Yes
• N - No

Is the project recorded for an event:

• Y - Yes
• N - No

The value is "yes" if the project is directly recorded for the event. The value is "no" if the project is a parent of the project recorded for the event.

A numerical value indicating how deep the project is in the tree structure. For example:

• 0 - root level project
• 1 - subproject of a root level project
• 2 - subproject of the previous level
• etc.