Deltek Ajera On-Premise Integrated Data

What this page covers: A complete reference mapping every Deltek Ajera On-Premise database field to its Mosaic equivalent. Use this guide when configuring your integration, verifying synced data, or troubleshooting discrepancies.


Integration type	On-Premise (Direct Database Access via SQL Server replication)
Setup complexity	Moderate — requires SQL Server replication
Configurable fields	Limited — fee source, time entry hours/rate source, status mappings
Prerequisite	Ajera On-Premise Integration Setup

How Data Flows

Ajera Database (Direct DB access via SQL replication) → Mosaic Sync Engine (field mapping) → Mosaic (10 synced data types)

The On-Premise integration reads directly from a Mosaic-managed replica of your Ajera SQL Server database. Mosaic replicates a subset of Ajera tables and maps them into Mosaic entities. See SQL Server Integration Requirements for setup instructions.

Data flows one way only — Ajera → Mosaic. Time entries and other records are read-only from Mosaic's perspective.

Integrated Data Types

The integration syncs 10 data categories from Ajera On-Premise into Mosaic:

Data Type	Description
Members	Employee records (name, email, status)
Member Roles	Member-to-role assignments (when employee type is replicated)
Clients	Client records (title from `AxVEC` filtered to client records)
Projects	Project records (description, fee, dates, billing type, status, project manager, company)
Phases	Phase breakdowns under projects (up to 6 levels deep, flattened to single-parent in Mosaic)
Time Entries	Posted labor transactions with hours, rates, billability, and approval status
Work Categories	Activity records used for time entry categorization (Labor activities only)
Roles	Active employee types from Ajera
Rate Groups	Active rate tables
Rates	Role-based labor bill rates from rate table detail rows

This list may expand as new features are added. When a new feature is added to Mosaic, your Customer Success Manager will reach out requesting access to your server to update your replication.

Not Synced

Data Type	Description
Member Cost Rates	Employee cost rates are not synced from Ajera
Member Project Bill Rates	Employee-specific (per-project) rate assignments are not synced
Member Project Roles	Per-project role overrides are not synced
Standard Bill Rates (cost)	Cost rates from rate tables are not synced (only role-based bill rates are)
Activity-based bill rates	Rate table rows keyed by activity rather than employee type are not synced
Employee-specific bill rates	Rate table rows keyed by individual employee are not synced
Work Plans / Budget Estimates	Resource planning data is not available in the On-Premise integration
Project Type, Department, Region, Office	Loaded into the replica but not surfaced as Mosaic fields

Reading the Field Mapping Tables

Symbol legend for technical tables

The Technical Reference tabs below use a shorthand notation for database sources. Read this legend before reviewing the mappings.

Symbol	Meaning	Example
`.`	Database table field reference	`AxProject.prjDescription` = the prjDescription column from the AxProject table
`>`	Fallback priority — use the first available value	`prjActualStartDate` > `prjEstimatedStartDate` = use Actual; if null, use Estimated
`•`	Values combined into one composite value	`tDate` • `tEmployee` = combined into a composite identifier
`or`	Configurable — an integration setting selects which	`tBilledRate` or `tEarnedRate` = your configuration chooses one

Members

What Syncs to Mosaic	Where It Comes From in Ajera
Employee ID	The unique Ajera employee identifier
First Name	The employee's first name
Middle Name	The employee's middle name
Last Name	The employee's last name
Email	The employee's email address
Is Archived	Derived from the employee status — Inactive = archived
Created Date	When the employee record was last modified (used as the create timestamp)

Members are sourced from the AxVEC table filtered to records where vecIsEmployee = TRUE. Clients are stored in the same table under vecIsClient = TRUE — see Clients.

Member Roles

What Syncs to Mosaic	Where It Comes From in Ajera
Member Role ID	A composite of the employee identifier and their employee type
Employee	The employee this role assignment belongs to
Role	The Ajera Employee Type assigned to the employee

When this syncs

Member Roles only sync when your replicated AxVEC table includes the vecEmployeeType column. If the column is not present in your database, member-to-role assignments will not appear in Mosaic.

Clients

What Syncs to Mosaic	Where It Comes From in Ajera
Client ID	The unique Ajera client identifier
Client Title	The client description
Created Date	When the client record was last modified
Is Archived	Always false — Ajera client status is not used to archive in Mosaic

Clients are sourced from the AxVEC table filtered to records where vecIsClient = TRUE.

Projects

What Syncs to Mosaic	Where It Comes From in Ajera
Project ID	The unique Ajera project key
Project Title	The project description
Project Number	The project ID/number
Client	The client linked to the project via Invoice Groups
Company Name	The company description (`AxCompany.cmpDescription`) joined via the project's company assignment
Project Manager	The employee assigned as project manager
Fee	The labor contract amount, optionally plus the consultant contract amount (configurable — see Fee Source)
Billing Type	Set to Role when the project's rate table is unambiguously role-based; otherwise null. Activity-based, Employee-based, and mixed rate tables are not surfaced as Mosaic rate groups
Rate Group	The rate table assigned to the project (when billing type is Role)
Is Billable	Derived from the project billing type — see Billable Status
Status	The project status, mapped to Mosaic's status values (configurable)
Is Archived	Derived from the project status — Closed statuses = archived
Start Date	The actual start date, falling back to the estimated start date
End Date	The actual completion date, falling back to the estimated completion date
Modified Date	When the project record was last modified

Project status mappings (configurable)

Mosaic maps the AxProject.prjStatus value to a Mosaic status using a default table, with configurable overrides for the three "ambiguous" Ajera statuses: holdStatusMappingForProject, workHoldStatusMappingForProject, and closedStatusMappingForProject. Equivalent phase-side overrides exist (holdStatusMappingForPhase, workHoldStatusMappingForPhase, closedStatusMappingForPhase). Contact your Customer Success Manager to adjust these mappings.

Phases

What Syncs to Mosaic	Where It Comes From in Ajera
Phase ID	The unique Ajera phase key
Phase Title	The phase description
Phase Number	The phase ID/number
Parent	The parent project, or the parent phase if this phase is nested under another phase
Project	The top-level parent project
Company Name	The company description (or department description as a fallback)
Fee	The labor contract amount, optionally plus the consultant contract amount (same configuration as projects)
Billing Type	Set to Role when the phase's rate table is unambiguously role-based; otherwise null
Rate Group	The rate table assigned to the phase (when billing type is Role)
Is Billable	Derived from the phase billing type
Status	The phase status, mapped to Mosaic's status values (configurable)
Is Archived	Derived from the phase status — Closed statuses = archived
Start Date	The actual start date, falling back to the estimated start date
End Date	The actual completion date, falling back to the estimated completion date
Modified Date	When the phase record was last modified
Order	The phase's display order within its project (only synced when the `prjOrder` column is present in your replica)

Nested Phases

Ajera allows phases to be nested up to 6 levels deep (prjPhaseLevel1 through prjPhaseLevel6). Mosaic preserves your hierarchy by mapping each phase's parent to either the parent phase or the project at the top of its chain. Sub-phases of sub-phases are supported.

Fee Source

Both projects and phases use the same configurable fee source.

Fee Source Option	What It Includes
Labor Contract Amount	Labor contract amount only (default)
Labor Contract Amount + Consultant Contract Amount	Labor contract amount plus the consultant contract amount

Billable Status

The Mosaic Is Billable flag for projects and phases is derived from the Ajera billing type:

Ajera `prjBillingType`	Mosaic Is Billable
TimeAndExpense, FixedFee, PercentComplete, UnitPrice, PercentofConstructionCost, Adjustment	Billable
Nonbillable, Marketing, Overhead	Not Billable

Time Entries

What Syncs to Mosaic	Where It Comes From in Ajera
Time Entry ID	The unique Ajera transaction key
Date	The transaction date of the time entry
Employee	The employee who logged the time
Project	The project the time was logged against
Phase	The phase the time was logged against (if applicable, otherwise null)
Work Category	The activity associated with the time entry
Hours	The hours logged — defaults to entered hours, configurable to use billed hours
Description	The time entry notes
Hourly Rate	The billed rate by default, configurable to use the earned (spent) rate
Billable	Derived from the entry status — Billable status = true; defaults to true if no status is set
Status	Derived from rejection/submission/approval flags on the linked timesheet (configurable)
Is Archived	Derived from `tIsCurrent` — entries marked as not current are archived
Is Deleted	Derived from `tIsDeleted`

Time Entry Sources & Filters

Mosaic syncs labor transactions only — tActivityType = 1. Worksheet entries (tType = 2) and posted entries (tType = 0) are both included. Records must be current (tIsCurrent = TRUE) and have a non-null employee, activity, and project.

Approval Status Mapping

Time entry status is derived from these timesheet fields and the timeEntryApprovalType setting:

tTimeRejectedBy (rejection)

tsSubmittedBy (submission)

tsSupervisorApprovedBy (supervisor approval)

tsAccountingApprovedBy (accounting approval)

Approval modes: ApprovedBySupervisor, ApprovedByAccounting, or ApprovedBySupervisorOrAccounting.

Work Categories

What Syncs to Mosaic	Where It Comes From in Ajera
Activity ID	The unique Ajera activity key
Activity Code	The activity key (used as the display code)
Activity Title	The activity description
Is Billable	Not derived — synced as null

Only Labor-type activities (actType = 1) sync. Other activity types (Expense, Consultant, etc.) are excluded.

Roles

What Syncs to Mosaic	Where It Comes From in Ajera
Role ID	The unique Ajera Employee Type key
Role Name	The Employee Type description

Only active employee types sync (etStatus = 1). Inactive types are excluded.

Rate Groups

What Syncs to Mosaic	Where It Comes From in Ajera
Rate Group ID	The unique Ajera rate table key
Rate Group Name	The rate table description
Active Entity Type	Always Role — Mosaic only surfaces role-based rate tables

Only active rate tables sync (rtStatus = 1). Rate tables keyed by Activity or by individual Employee are not currently surfaced as Rate Groups in Mosaic.

Rates

What Syncs to Mosaic	Where It Comes From in Ajera
Rate ID	A composite of the rate table, role, and rate detail row identifiers
Rate Group	The rate table this rate belongs to
Role	The Employee Type the rate applies to
Rate Amount	The bill rate from the rate table detail row
Description	The rate table name combined with the role name and the start date
Start / End Dates	The effective date range for the rate
Is Cost Rate	Always false — only bill rates are synced

Only role-based bill rates sync. Rate table rows keyed by individual employee or by activity are not surfaced. Cost rates (rtlCost) are not synced.

Replicated Database Tables

Mosaic replicates a subset of the Ajera database tables. The following tables drive the integration:

Table	Purpose
`AxVEC`	Employees and clients (filtered by `vecIsEmployee` / `vecIsClient`)
`AxProject`	Projects (`prjKey = prjProject`) and phases (`prjKey != prjProject`), with phase-level hierarchy
`AxActivity`	Work categories (Labor activities only)
`AxTransaction`	Time entries (labor transactions)
`AxTimesheet`	Approval status for time entries (supervisor / accounting / submission / rejection)
`AxCompany`	Company description, joined onto projects and phases
`AxEntity`	Department description (used as a fallback for phase company name)
`AxProjectType`	Project type metadata (replicated but not surfaced as a Mosaic field)
`AxInvoiceGroup`	Project → Client linkage (`igProject` → `igClient`)
`AxEmployeeType`	Roles (active employee types)
`AxRateTable`	Rate groups
`AxRateTableDate`	Rate effective date ranges
`AxRateTableDetail`	Individual rate rows (filtered to role-based labor bill rates)

Some columns (e.g., AxVEC.vecEmployeeType, AxProject.prjOrder, AxProject.prjRateTable, AxTransaction.tType, AxTransaction.tIsCurrent) are conditionally selected — they only sync if your Ajera schema includes them. Older Ajera installations may not have all columns.

Troubleshooting & FAQ

How does SQL Server replication work for this integration?

Mosaic replicates a subset of your Ajera database tables to a Mosaic-managed SQL Server instance. For setup requirements, see SQL Server Integration Requirements.

Will my replication tables be updated when Mosaic adds new features?

Yes. When a new feature is added to Mosaic, your Customer Success Manager will reach out requesting access to your server to update your replication.

Why isn't my employee's title appearing in Mosaic?

Employee title (AxVEC.vecTitle) is replicated but is not surfaced as a Mosaic field. To assign a member's role in Mosaic, use Member Roles (driven by the Ajera Employee Type).

Why aren't my employee-specific or activity-specific rates syncing?

Only role-based bill rates are currently synced. Rate table rows keyed by individual employee (rtlEmployee IS NOT NULL) or by activity (rtlActivity IS NOT NULL) are not surfaced. Cost rates (rtlCost) are also not synced.

Are work plans or resource planning data available?

No. Work Plans and Budget Estimates are Cloud-only and not available in the On-Premise integration.

Can I rename field labels in Mosaic to match my Ajera custom labels?

No. Custom field name changes in Mosaic to match Ajera labeling is not supported as a standard integration feature.

I need a data source or field that isn't listed here.

Any integration not listed in this documentation is considered a customization and is charged at an hourly rate. For more information, see Integration Requests.

Deltek Documentation Links

A consolidated reference of Deltek Ajera documentation:

Topic	Link
Ajera Online Help	Ajera Help
Deltek Help & Learning	Deltek Help