webTA: Data Dictionary

Introduction

webTA incorporates the Oracle database management system to store and manage its information. To aid in support for third party reporting, Kronos Federal publishes a Data Guide for Report Writers document that defines the tables and fields within the webTA data schema.

The contents of this data dictionary are based on information extracted from the webTA Data Guide for Report Writers document. The first section of the document provides an overview of the organization of data within webTA, including overviews followed by discussions on accounting and roles within webTA. The next sets of sections provide a description of the tables and fields within the webTA data schema. Descriptions of the table and its individual fields are provided, as well as the field's variable type.

Data Organization

Ideally, each employee in your organization has a single webTA account. For each account in webTA, there is a corresponding record in the TA_USER table. This contains information about the employee that generally doesn't change each pay period. Each record in TA_USER is uniquely identified (internally to the webTA database) by the value of the EMP_ID column.

For each employee, there is a corresponding record in the TA_MASTER table for one or more pay periods. This contains the employee's T&A Profile information for a pay period, a summary of that pay period's transactions, and a summary of the employee's leave activity and balance. Each entry in the TA_MASTER table is uniquely identified by the value of the TA_ID column.

Also for each employee, there may be one or more records in the TA_TRANS table for each corresponding pay period record in the TA_MASTER table. These are the individual transactions that comprise the T&A record. For instance, if during the course of the pay period a given employee had worked some Regular Base Pay, taken some Annual Leave, and used some Comp Time, each of these would have a corresponding line in the TA_TRANS table. Each record in TA_TRANS contains information about the transaction, such as the transaction code, account, etc., the number of hours worked toward that transaction for each day of the pay period, and the weekly totals. The summary of all transactions for the pay period, however, is stored in the TA_MASTER table.

The tables TA_MASTER and TA_TRANS themselves contain only currently active T&A records. Once a record has been built, it is moved to TA_MASTER_HISTORY and TA_TRANS_HISTORY. At that point it will stay in the HISTORY tables unless it is made a correction record, which would cause it to be moved back to TA_MASTER and TA_TRANS. The structures of the HISTORY tables are identical to their active counterpart.

TA_MASTER and TA_TRANS reference a large number of lookup tables. These tables are named TA_LUT_*, where LUT represents LookUpTable.

Except where noted, all time quantities are stored in units of minutes.

Accounting in webTA

Each transaction in TA_TRANS must reference an account. This is achieved in one of four ways:

• Basic Accounting
• Stored Accounting
• Project Accounting
• Activity Accounting

If your organization is not using project- or activity-based accounting, accounts may be set per transaction; or, if all transactions use only a single account, you may use stored accounting. The value of the STORED_ACCT_ID column in TA_MASTER determines how a given employee uses stored accounting. It is a foreign key to lookup table TA_LUT_STOREDACCT.

For an employee using manual entry of accounts, the ACCT_ID column of TA_TRANS contains a reference to table TA_ACCT that specifies to which account the transaction is charged.

For an employee using stored accounting, ACCT_ID contains 0 and STORED_ACCT_ID contains a reference to table TA_LUT_STOREDACCT. The value of STORED_ACCT_ID specifies which column, either ID_OF_LOCAL_STORED_ACCT or ID_OF_NFC_STORED_ACCT, contains the ACCT_ID of the appropriate stored account.

If your agency has the webTA Labor Management Module and uses project-based accounting, then the PROJ_ACCT_ID field in TA_TRANS will reference the row in the TA_PROJ_ACCT table that specifies which project is associated with the transaction and which account is associated with the project at that time.

If your agency uses activity code-based accounting, then the ACTY_ID field in TA_TRANS will reference TA_LUT_ACTY, which contains details regarding the activity. The TA_LUT_ACTY table does not exist for customers who do not use activity code based accounting.

Roles in webTA

Every webTA user possesses the employee role. Records in the TA_ROLE table determine other roles in webTA. For an employee who possesses a role outright, there is a record in the TA_ROLE table that contains the employee's EMP_ID in both the EMP_ID and DELEGATE_FOR columns. A webTA employee who is a delegate will have a record in the TA_ROLE table with their own EMP_ID in the EMP_ID column and the EMP_ID of the employee for whom they are delegate in the DELEGATE_FOR column.

When a timekeeper or supervisor assigns a delegate, the delegate has authority over all of the employees assigned to the delegating official. A project manager, however, can delegate all of their projects, creating a record in the TA_ROLE table, or delegate authority for a single project, creating a record in the TA_PROJ_DELEGATE table.

The timekeeper and supervisor to whom a given employee is assigned are stored in the TA_USER table.

Tables

Table Details