Data share tables

Stefan McNamara Updated by Stefan McNamara

Database Overview

The Time and Attendance database combines your employee personal data with clocking records, work records, absence management and system information. This article is a reference to all the tables that are available through the data platform, and provides a link to an article for each table that digs into the details of what each table does, and the information it typically contains. This article is split into EMPLOYEE specific tables, and SYSTEM tables.

If you are migrating existing SQL scripting to the Data Share Platform, please note that the Platform uses the Table Names defined in column 1 (user friendly names that describe a tables contents). Any internal table names in your queries will need to be altered to use the user-friendly table names instead.

Employee Tables

Table Name

Internal Name

Purpose

Unique Key

EMPLOYEES

TMSEMP

This is the core table for employee data in the T&A system. It contains personal information such as first name, surname, address, and title

EMPREF

EMPLOYEE_TMS_DETAILS

TMSTMS

This table contains the T&A information for each employee that exists in the EMPLOYEES table. Information such as the employees roster code, work rule and badge number

EMPREF

EMPLOYEE_CONTRACT_AND_ENTITLEMENTS

CE

All employees have a unique entry in this table, containing such information as their contract code, start date, continuous service date and contracted weekly hours

EMPREF

ORGANISATION_DETAILS

OD

All employees also have a unique entry in the organisation table that describes their place within the organisation structure. Information such as department, cost centre, and location

EMPREF

EMPLOYEE_CLOCKINGS

TMSCLK

Every clocking made by your employees is stored in this table, including attendance clockings and activity clockings. This one can get very large!

EMPREF;CLKDT;CLKTYPE;UNIQUEKEY

EMPLOYEE_FUTURE_CHANGES

TMSFC

Absences, both planned and historical can be found in here. Additionally if there are planned shift changes for your employees they can be found here

EMPREF;FCTYPE;STARTDATE;CODE;EXTCODE;DETAIL

EMPLOYEE_WORK_RECORDS

TMSWR

The data you will find here consists of one record for each day worked by your employees. The data contains the date of work, the shift that was worked, and any anomalies within those work records

EMPREF;PROCDATE;PROCTIME

WORKED_HOURS

TMSWRHRS

For each record in the work records above you will find multiple corresponding records in this table, representing the hours worked on the day. For example there may be a row for the BASIC hours worked in a day, and another row for the OVERTIME worked on the same day

EMPREF;PROCDATE;PROCTIME;HRSCODE

WORKED_HOURS_BANDS

TMSWRBND

Each worked day is divided into a number of bands, that can be found in here for granular daily reporting. Examples might be a morning band (BASIC hours) followed by a lunch break band (no hours code) ending with an afternoon band (BASIC hours)

EMPREF;PROCDT;STARTBAND;FINISHBAND;HRSCODE;HRSTYPE

JOB_DETAILS

JD

This table contains a unique row for each employee with their job descriptions, including their job grade, position, and whether they are managers or not

EMPREF

PAY_DETAILS

PR

Your employees payment details can be found in here, including an hourly/salary flag, and annual salary broken down into monthly/weekly/hourly figures

EMPREF

EMPLOYEE_SKILLS

TMSEMPSK

Skills defined in the table above can be assigned to employees. This table records multiple rows per employee defining the different skills and competencies that have been assigned to them

EMPREF;CODE

TRAINING_RECORDS

TR

Any training that your employees have been through can be stored in this table, including date of training, an expiry date for certain training types and any skills that the training record contributes to

EMPREF;AUTOKEY

EMPLOYEE_ENTITLEMENTS

TMSEMPEN

Employees entitlement figures are found in this table. These figures represent the starting positions for each entitlement (e.g. 25 days leave this year) and are not representative of realtime data

EMPREF;CODE

WORK_RECORD_JOBS

TMSWRJOBS

Jobs that employees have been assigned to via the Auto-Rostering integration can be found in this table

EMPREF;PROCDT;POSTYPE;FROMTIME;TASCODE

ADDITIONAL_PAYMENTS

TMSWRAD

Work records sometimes contain additional payments, which can be found in here. Examples might include step up payments or meal allowance (depending on your implementation)

EMPREF;PROCDATE;PROCTIME;ADCODE

System Tables

Table Name

Internal Name

Purpose

Unique Key

SUPERVISOR_USERS

USERS

All system users (managers, not employees) can be found in this table. Data includes user name, full name and the date and time of each users last activity

USERNAME

LOOKUP_LISTS

LOOKUP

Dropdown lists in the T&A system can be found in here. Examples include nationality, pay grades, and relationships to next of kin

SETNAME;CODE

ORGANISATION_LOOKUP_LISTS

LUORG

Similar to the table above, you can find dropdown list definitions in here that relate to the organisation structure for your business. Examples would include departments lists, location lists, and cost centres

SETNAME;CODE

SHIFT_DEFINITIONS

TMSWS

All predefined shifts in the system can be found in this table. The data includes information such as start time, end time, and number of standard hours. These shifts are the building blocks of your systems rosters

REF

ABSENCE_CATEGORIES

TMSAC

All absence types that have been defined in the system can be found in this table. You can report on payable vs non-payable absences

CODE

WORKED_HOURS_CODE

TMSHC

This table is similar to the absence table above, but stores the worked hours codes defined in your T&A system. Examples include BASIC hours, any OVERTIME codes you have defined, and any shift premiums

CODE

GENERAL_ABSENCES

TMSGNABS

Absences related to groups of employees are found in here. An example would be a statutory holiday that only applies to employees living in a certain location. A general absence is applied to a group, not an individual employee

AUTOKEY

TERMINAL_DEFINITION

TERMDEF

Hardware clocking terminal information can be found in this table. The data includes unique hardware references numbers, the configuration file each unit is using, and the site on which the hardware is situated

REF

ROSTER_PATTERNS

TMSWP

You can find the definitions of each system roster in your T&A implementation in this table. Each roster has one unique row, and contains information such as the unique identifying code for each roster, and the number of days in the roster pattern

REF

AUDIT_TRAIL

HRAUDIT

All changes to employee data (personal data, work records, clockings, absence management and more!) are recorded in this audit table. You can report on the changes made, the user that made the changes, and when the changes occurred

No Unique Key

SKILLS_LIST

TMSCSKLL

If you have added a list of skills applicable to your business to the T&A application they can be found in this table

CODE

ENTITLEMENT_SETUP

TMSENT

Entitlement schemes, typically holiday or company sick pay schemes, are stored in this table (although this can vary depending on your implementation)

CODE

Was this article useful?

Data Share Guidance

Data Share FAQ

Contact