Deltek Ajera On-Premise Integrated Data
Field mapping reference for Deltek Ajera On-Premise
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 |
| 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
AxVECtable filtered to records wherevecIsEmployee = TRUE. Clients are stored in the same table undervecIsClient = TRUE— see Clients.
| Mosaic Field | Source |
|---|---|
| Employee ID | AxVEC.vecKey |
| First Name | AxVEC.vecFirstName |
| Middle Name | AxVEC.vecMiddleName |
| Last Name | AxVEC.vecLastName |
AxVEC.vecEmail | |
| Is Archived | AxVEC.vecStatus — Inactive = archived |
| Created Date | AxVEC.vecLastModifiedDate |
Filter:
AxVEC.vecIsEmployee = TRUE. ThevecTitleandvecDescriptioncolumns are replicated but not surfaced as Mosaic fields.
Deltek Documentation
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
AxVECtable includes thevecEmployeeTypecolumn. If the column is not present in your database, member-to-role assignments will not appear in Mosaic.
| Mosaic Field | Source |
|---|---|
| Member Role ID | AxVEC.vecKey • AxVEC.vecEmployeeType |
| Employee | AxVEC.vecKey |
| Role | AxVEC.vecEmployeeType (links to AxEmployeeType.etKey) |
Deltek Documentation
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
AxVECtable filtered to records wherevecIsClient = TRUE.
| Mosaic Field | Source |
|---|---|
| Client ID | AxVEC.vecKey |
| Client Title | AxVEC.vecDescription |
| Created Date | AxVEC.vecLastModifiedDate |
| Is Archived | Always false |
Filter:
AxVEC.vecIsClient = TRUE. The Project → Client relationship is resolved via theAxInvoiceGrouptable (igProject→igClient).
Deltek Documentation
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.prjStatusvalue to a Mosaic status using a default table, with configurable overrides for the three "ambiguous" Ajera statuses:holdStatusMappingForProject,workHoldStatusMappingForProject, andclosedStatusMappingForProject. Equivalent phase-side overrides exist (holdStatusMappingForPhase,workHoldStatusMappingForPhase,closedStatusMappingForPhase). Contact your Customer Success Manager to adjust these mappings.
| Mosaic Field | Source |
|---|---|
| Project ID | AxProject.prjKey |
| Project Title | AxProject.prjDescription |
| Project Number | AxProject.prjID |
| Client | AxInvoiceGroup.igClient (joined via igProject → prjKey) |
| Company Name | AxCompany.cmpDescription (joined via prjCompany → cmpKey) |
| Project Manager | AxProject.prjProjectManager (links to AxVEC.vecKey) |
| Fee | AxProject.prjLaborContractAmount (+ prjConsultantContractAmount per Fee Source) |
| Billing Type | Role when every AxRateTableDetail row for AxProject.prjRateTable has billing type = Role; otherwise null (mixed billing types or non-role rate tables resolve to null) |
| Rate Group | AxProject.prjRateTable (when billing type is Role) |
| Is Billable | Derived from AxProject.prjBillingType — see Billable Status |
| Status | AxProject.prjStatus (configurable mapping) |
| Is Archived | AxProject.prjStatus starting with Closed |
| Start Date | AxProject.prjActualStartDate > AxProject.prjEstimatedStartDate |
| End Date | AxProject.prjActualCompletionDate > AxProject.prjEstimatedCompletionDate |
| Modified Date | AxProject.prjLastModifiedDate |
Filter:
AxProject.prjKey = AxProject.prjProject(parent project records only). Phase records useprjKey != prjProject— see Phases.
Deltek Documentation
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 (
prjPhaseLevel1throughprjPhaseLevel6). 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.
Phase Dates
Mosaic only syncs a phase's dates when the phase has both a start date and an end date. Ajera auto-fills the actual start date as soon as time or cost is charged to a phase, so a phase can easily have a start date and no completion date — such a phase still syncs into Mosaic, just with both dates left blank. Add the missing date in Ajera if you want the phase's dates to sync.
| Mosaic Field | Source |
|---|---|
| Phase ID | AxProject.prjKey |
| Phase Title | AxProject.prjDescription |
| Phase Number | AxProject.prjID |
| Parent | Derived from AxProject.prjPhaseLevel1 … prjPhaseLevel6 (deepest non-null level above the current phase) |
| Project | AxProject.prjProject |
| Company Name | AxCompany.cmpDescription > AxEntity.eDescription |
| Fee | AxProject.prjLaborContractAmount (+ prjConsultantContractAmount per Fee Source setting) |
| Billing Type | Role when every AxRateTableDetail row for AxProject.prjRateTable has billing type = Role; otherwise null |
| Rate Group | AxProject.prjRateTable (when billing type is Role) |
| Is Billable | Derived from AxProject.prjBillingType |
| Status | AxProject.prjStatus (configurable mapping) |
| Is Archived | AxProject.prjStatus starts with Closed |
| Start Date | AxProject.prjActualStartDate > AxProject.prjEstimatedStartDate |
| End Date | AxProject.prjActualCompletionDate > AxProject.prjEstimatedCompletionDate |
| Modified Date | AxProject.prjLastModifiedDate |
| Order | AxProject.prjOrder (conditionally selected — only synced if the column exists in your replica) |
Filter:
AxProject.prjKey != AxProject.prjProject(phase records only).
Deltek Documentation
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 |
| Fee Source Setting | Ajera Field(s) |
|---|---|
LaborContractAmount | AxProject.prjLaborContractAmount |
LaborContractAmountAndConsultantContractAmount | AxProject.prjLaborContractAmount + AxProject.prjConsultantContractAmount |
Configured via the
projectFeeSourceintegration setting.
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
timeEntryApprovalTypesetting:
tTimeRejectedBy(rejection)tsSubmittedBy(submission)tsSupervisorApprovedBy(supervisor approval)tsAccountingApprovedBy(accounting approval)Approval modes: ApprovedBySupervisor, ApprovedByAccounting, or ApprovedBySupervisorOrAccounting.
| Mosaic Field | Source |
|---|---|
| Time Entry ID | AxTransaction.tKey |
| Date | AxTransaction.tDate |
| Employee | AxTransaction.tEmployee |
| Project | AxProject.prjProject (joined via tProject → prjKey) |
| Phase | AxProject.prjKey when prjKey != prjProject, otherwise null |
| Work Category | AxTransaction.tActivity |
| Hours | AxTransaction.tUnits or AxTransaction.tBilledUnits (configurable) |
| Description | AxTransaction.tNotes |
| Hourly Rate | AxTransaction.tBilledRate or AxTransaction.tEarnedRate (configurable) |
| Billable | AxTransaction.tStatus = 0 (Billable); defaults true when null |
| Status | Derived from AxTransaction.tTimeRejectedBy, AxTimesheet.tsSubmittedBy, AxTimesheet.tsSupervisorApprovedBy, AxTimesheet.tsAccountingApprovedBy |
| Is Archived | !AxTransaction.tIsCurrent (when column present) |
| Is Deleted | AxTransaction.tIsDeleted |
Filters:
tActivityType = 1,tType IN (0, 2),tIsCurrent = TRUE,tEmployee/tActivity/tProject IS NOT NULL. ThetTypeandtIsCurrentfilters are skipped if those columns are absent in older Ajera schemas.
Configurable settings
timeEntryHoursSource—Hours(usestUnits) orBilledHours(usestBilledUnits)timeEntryRateSource—BilledRate(usestBilledRate) orSpentRate(usestEarnedRate)timeEntryApprovalType—ApprovedBySupervisor,ApprovedByAccounting, orApprovedBySupervisorOrAccounting
Deltek Documentation
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.
| Mosaic Field | Source |
|---|---|
| Activity ID | AxActivity.actKey |
| Activity Code | AxActivity.actKey |
| Activity Title | AxActivity.actDescription |
| Is Billable | Always null |
Filter:
AxActivity.actType = 1(Labor activities only).
Deltek Documentation
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.
| Mosaic Field | Source |
|---|---|
| Role ID | AxEmployeeType.etKey |
| Role Name | AxEmployeeType.etDescription |
Filter:
AxEmployeeType.etStatus = 1(Active).
Deltek Documentation
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.
| Mosaic Field | Source |
|---|---|
| Rate Group ID | AxRateTable.rtKey |
| Rate Group Name | AxRateTable.rtDescription |
| Active Entity Type | Always Role |
Filter:
AxRateTable.rtStatus = 1(Active).
Deltek Documentation
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.
| Mosaic Field | Source |
|---|---|
| Rate ID | AxRateTableDetail.rtdRateTable • AxRateTableDetail.rtlEmployeeType • AxRateTableDetail.rtlKey |
| Rate Group | AxRateTableDetail.rtdRateTable (links to AxRateTable.rtKey) |
| Role | AxRateTableDetail.rtlEmployeeType (links to AxEmployeeType.etKey) |
| Rate Amount | AxRateTableDetail.rtlRate |
| Description | AxRateTable.rtDescription • AxEmployeeType.etDescription • AxRateTableDate.rtdStartDate |
| Start / End Dates | AxRateTableDate.rtdStartDate, AxRateTableDate.rtdEndDate |
| Is Cost Rate | Always false |
Filter:
rtlEmployee IS NULL AND rtlActivity IS NULL AND rtlActivityType = 1(Labor, role-based bill rate rows only).
Deltek Documentation
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 |
Related Documentation
Updated 23 days ago