This article outlines the tables, and fields that are available for querying in Karbon Practice Intelligence. These tables contain information about clients, client groups, tasks, users, work items, utilization and work hours, etc. If you have any other needs, please let us know in the Karbon Community.
Index
DIMN_CLIENT
This table holds attributes about a client.
Field | Description |
CLIENT_ID | The unique identifier of the client. |
CLIENT | The name of the client. |
DATE_CREATED | The date when the client was created in the system. |
CLIENT_TYPE | The type of client. It can be one of Individual (Contact), Organisation, Client Group. |
CLIENT_SUBTYPE | This is a further sub classification of a client. |
CLIENT_PRIMARY_EMAIL_ADDRESS | The primary email address of the client. |
USER_DEFINED_CLIENT_ID | An id created by the user for internal use. |
CLIENT_OWNER_USER_ID | The identifier of the User Profile who owns the responsibilities of this client. |
CLIENT_OWNER_USER_NAME | The name of the User Profile who owns the responsibilities of this client. |
CLIENT_MANAGER_USER_ID | The identifier of the User Profile who manages the responsibilities of this client. |
CLIENT_MANAGER_USER_NAME | The name of the User Profile who manages the responsibilities of this client. |
ACCOUNT_ID | The tenant's unique identifier to whom the Work Item belongs to. |
ACCOUNT_NAME | The tenant's name to whom the Work Item belongs to. |
DIMN_CLIENT_GROUP
This table holds the attributes about the client group.
Field | Description |
CLIENT_GROUP_ID | The unique identifier of a client group. D |
CLIENT_GROUP_NAME | The name of the client group. |
CLIENT_ID | The unique identifier of the client who is a member of this group. |
CLIENT | The name of the client who is a member of this group. |
CLIENT_GROUP_MEMBER_TYPE | The type of client. It can be one of an Individual (Contact), Organisation, or Client Group. |
ACCOUNT_ID | The tenant's unique identifier to whom the Work Item belongs to. |
ACCOUNT_NAME | The tenant's name to whom the Work Item belongs to. |
DIMN_DATE
This is a date table that allows analysis across different time periods
Field | Description |
CALN_DATE | This is the calendar date. |
DAY_OF_WEEK | This is the numerical day of the current week. Sunday is considered to be 1. |
WEEK_STRT_DATE | The current week's start date (Sun - Sat) |
WEEK_END_DATE | The current weekend date. (Sun - Sat) |
ISO_DAY_OF_WEEK | This is the ISO numerical day of the calendar date in the current week. Monday is considered to by 1. |
ISO_WEEK_STRT_DATE | The current week's start date is as per ISO standards. (MON - SUN) |
ISO_WEEK_END_DATE | The current weekend date is as per ISO standards. (MON - SUN) |
DAY_OF_WEEK_NAME | The name of the day. |
DAY_OF_MNTH | The numerical day of the current month. |
DAY_OF_YEAR | The numerical day of the current year. |
WEEK_OF_YEAR | The week number of the current year. |
CALN_MNTH | The numerical month of the current year. |
CALN_MNTH_NAME | The name of the month of the current year. |
MNTH_STRT_DATE | The month start date. |
MNTH_END_DATE | The month-end date. |
PRVS_MNTH_STRT_DATE | The previous month's start date |
PRVS_MNTH_END_DATE | The previous month's end date |
QRTR_OF_YEAR | The current numerical quarter of the year. |
QRTR_STRT_DATE | The quarter start date. |
QRTR_END_DATE | The quarter end date. |
PRVS_QRTR_STRT_DATE | The previous quarter's start date. |
PRVS_QRTR_END_DATE | The previous quarter's end date. |
CALN_YEAR | The calendar year. |
YEAR_STRT_DATE | The year start date. |
YEAR_END_DATE | The year-end date. |
PRVS_YEAR_STRT_DATE | The previous year's start date. |
PRVS_YEAR_END_DATE | The previous year-end date. |
DIMN_TASK_TYPE
This table holds the attributes that define a task type.
Field | Description |
TASK_TYPE_ID | The unique identifier for the task type. |
TASK_TYPE_NAME | The name of the task type. |
IS_TASK_TYPE_BILLABLE | A flag saying whether the task type is billable. |
ACCOUNT_ID | The tenant's unique identifier to whom the Work Item belongs to. |
ACCOUNT_NAME | The tenant's name to whom the Work Item belongs to. |
DIMN_USER
This table holds the attributes that define a user / employee.
Field | Description |
USER_NAME | The name of the user profile. |
USER_ID | The unique identifier of the user profile. |
USER_JOB_TITLE | The job title of the user profile. |
USER_EMAIL_ADDRESS | The email address of the user profile. |
CREATED_DATE | The date when the user profile was created in the system. |
ACTIVATED_DATE | The date when the user profile was activated in the system. |
EXPECTED_BILLABLE_MINUTES | The weekly billable minutes of the user profile is expected to work. |
EXPECTED_NONBILLABLE_MINUTES | The weekly non-billable minutes the user profile is expected to work. |
STATUS | The status of the user profile. |
IS_SUPPORT_USER | A flag indicating whether the user profile is a support user. |
ACCOUNT_ID | The tenant's unique identifier to whom the Work Item belongs to. |
ACCOUNT_NAME | The tenant's name to whom the Work Item belongs to. |
DIMN_WORK_ITEM
This table holds the attributes that define information about a work item.
Field | Description |
WORK_ITEM_ID | The unique identifier for the work item whose details are being provided. |
WORK_TITLE | The title of the work item |
WORK_TYPE | The type of the work item. |
CREATED_DATETIME | The date time the work item was created. |
START_DATETIME | The date time the work item was started. |
DUE_DATETIME | The due DateTime of the work item. |
COMPLETED_DATETIME | The DateTime the work item was completed. |
PRIMARY_STATUS | The primary status of the work item |
SECONDARY_STATUS | The secondary status of the work item |
ACCOUNT_ID | The tenant's unique identifier to whom the Work Item belongs to. |
ACCOUNT_NAME | The tenant's name to whom the Work Item belongs to. |
USER_TIME_ENTRY_DETAIL
This table provides the individual time entries submitted by users
Field | Description |
REPORTING_DATE | The date on which the time entry was submitted for. |
USER_ID | The unique identifier of the user profile who submitted the time entry. |
USER_NAME | The name of the user profile who submitted the time entry. |
MINUTES | The amount of time in minutes submitted with the time entry. |
TOTAL_COST | The calculated cost for the time entry is based on the minutes, role and task type. |
CLIENT_ID | The unique identifier of the client. |
CLIENT | The name of the client. |
TASK_TYPE_ID | The unique identifier for the task type. |
TASK_TYPE | The name of the task type. |
IS_BILLABLE | A flag saying whether the task type is billable. |
ROLE_ID | The unique identifier of the role performed by the user. |
ROLE_NAME | The name of role performed by the user. |
WORK_ITEM_ID | The unique identifier for the work item whose details are being provided. |
WORK_ITEM_TITLE | The title of the work item. |
WORK_ITEM_TYPE | The type of the work item. |
DESCRIPTION | The description added to the submitted time entry. |
TIME_ENTRY_ID | The unique ID of the time entry. |
ACCOUNT_ID | The tenant's unique identifier to whom the Work Item belongs to. |
ACCOUNT_NAME | The tenant's name to whom the Work Item belongs to. |
USER_WEEKLY_UTILIZATION_SUMMARY
This table provides a weekly comparison of the billable/non-billable expected and actual time for user profiles / employees.
Field | Description |
REPORTING_WEEK_START_DATE | The start date of the reporting week. |
REPORTING_WEEK_END_DATE | The end date of the reporting week. |
USER_ID | The unique identifier of the user profile. |
USER_NAME | The name of the user profile. |
ACCOUNT_ID | The tenant's unique identifier to whom the Work Item belongs to. |
ACCOUNT_NAME | The tenant's name to whom the Work Item belongs to. |
EXPECTED_BILLABLE_MINUTES | The weekly billable minutes the user profile is expected to work. |
ACTUAL_BILLABLE_MINUTES | The weekly billable minutes the user profile actually worked. |
EXPECTED_NONBILLABLE_MINUTES | The weekly non-billable minutes the user profile is expected to work. |
ACTUAL_NONBILLABLE_MINUTES | The weekly non-billable minutes the user profile actually worked. |
USER_WORK_HOURS_SUMMARY
This table provides metrics around the actual time entries for user profiles / employees across different time periods.
Field | Description |
REPORTING_DATE | The date as of which the data is representative of. |
INTERNAL_CLIENT_ID | The User Profile to which the work item belongs to. |
INTERNAL_CLIENT | The name of the User to which the work item belongs to. |
USER_ID | The unique identifier of the user profile. |
USER_NAME | The name of the user profile. |
CLIENT_ID | The unique identifier of the client. |
CLIENT | The name of the client. |
ACCOUNT_ID | The tenant's unique identifier to whom the Work Item belongs to. |
ACCOUNT_NAME | The tenant's name to whom the Work Item belongs to. |
TASK_TYPE_ID | The unique identifier for the task type. |
TASK_TYPE | The name of the task type. |
TASK_TYPE_BILLABLE_FLAG | A flag saying whether the task type is billable. |
ROLE_ID | The unique identifier of the role performed by the user. |
ROLE_NAME | The name of role performed by the user. |
WORK_ITEM_TYPE_ID | The type code of the work item. |
WORK_ITEM_TYPE | The type of the work item. |
REPORTING_DATE_TIME_MINUTES | Minutes the user logged for the given reporting date. |
MTD_TIME_MINUTES | Minutes the user logged this month till reporting date. |
QTD_TIME_MINUTES | Minutes the user logged this quarter till reporting date. |
YTD_TIME_MINUTES | Minutes the user logged this year till reporting date. |
PREVIOUS_MONTH_TIME_MINUTES | Minutes the user logged in the previous month. |
PREVIOUS_QUARTER_TIME_MINUTES | Minutes the user logged in the previous quarter. |
PREVIOUS_YEAR_TIME_MINUTES | Minutes the user logged in the previous year. |
REPORTING_DATE_TIME_COST | The total cost amount is based on the minutes logged by the user for the given reporting date. |
MTD_COST | The total cost amount is based on the minutes logged by the user this month till the reporting date. |
QTD_COST | The total cost amount is based on the minutes logged by the user this quarter till the reporting date. |
YTD_COST | The total cost amount is based on the minutes logged by the user this year till the reporting date. |
PREVIOUS_MONTH_COST | The total cost amount is based on the minutes logged by the user in the previous month. |
PREVIOUS_QUARTER_COST | The total cost amount is based on the minutes logged by the user in the previous quarter. |
PREVIOUS_YEAR_COST | The total cost amount is based on the minutes logged by the user in the previous year. |
REPORTING_DATE_RECORD_COUNT | The total number of time entries logged for the given reporting date. |
MTD_RECORD_COUNT | The total number of time entries logged this month till reporting date. |
QTD_RECORD_COUNT | The total number of time entries logged this quarter till reporting date. |
YTD_RECORD_COUNT | The total number of time entries logged this year till reporting date. |
PREVIOUS_MONTH_RECORD_COUNT | The total number of time entries logged in the previous month. |
PREVIOUS_QUARTER_RECORD_COUNT | The total number of time entries logged in the previous quarter. |
PREVIOUS_YEAR_RECORD_COUNT | The total number of time entries logged in the previous year. |
WORK_ITEM_BUDGET_VS_ACTUAL
This table compares the budgeted minutes / cost against the actual minutes and costs for work items.
Field | Description |
REPORTING_DATE | The date of which the data is representative of. |
WORK_ITEM_ID | The unique identifier for the work item whose details are being provided. |
WORK_TITLE | The title of the work item |
WORK_TYPE_ID | The type code of the work item. |
WORK_TYPE | The type of the work item. |
TASK_TYPE_ID | The unique identifier for the task type. |
TASK_TYPE | The name of the task type. |
TASK_TYPE_BILLABLE_FLAG | A flag saying whether the task type is billable. |
ROLE_ID | The unique identifier of the roles performed on the work item. |
ROLE_NAME | The name of the roles performed on the work item. |
ACCOUNT_ID | The tenant's unique identifier to whom the Work Item belongs to. |
ACCOUNT_NAME | The tenant's name to whom the Work Item belongs to. |
USER_ID | The unique identifier of the user profile for whom the comparison is done. |
USER_NAME | The name of the user profile for whom the comparison is done. |
INTERNAL_CLIENT_ID | The User Profile to which the work item belongs to. |
INTERNAL_CLIENT | The name of the User to which the work item belongs to. |
CLIENT_ID | The client's unique identifier to which the work item belongs to. |
CLIENT | The client's name to which the work item belongs to. |
BUDGETED_MINUTES | Total minutes budget for the work item. |
ACTUAL_MINUTES | The actual minutes spent on the work item. |
BUDGETED_COST | The total budgeted cost for the work item. |
ACTUAL_COST | The actual cost incurred on the work item. |
WORK_ITEM_DAYS_IN_STATUS
This table provides information on the number of days a given work items was in a specific status.
Field | Description |
WORK_ITEM_ID | The unique identifier for the work item whose details are being provided. |
WORK_ITEM_TITLE | The title of the work item |
WORK_ITEM_CREATED_AT | The DateTime when the work item was created. |
ASSIGNED_TO_ID | The user identifier to whom the work item was assigned to. |
ASSIGNED_TO | The name of the user to whom the work item was assigned to. |
CLIENT_ID | The client's unique identifier to which the work item belongs to. |
CLIENT_NAME | The client's name to which the work item belongs to. |
USER_ID | The User Profile to which the work item belongs to. |
USER_NAME | The name of the User to which the work item belongs to. |
ACCOUNT_ID | The tenant's unique identifier to whom the Work Item belongs to. |
ACCOUNT_NAME | The tenant's name to whom the Work Item belongs to. |
PRIMARY_STATUS_ID | The primary status code in which the work item currently sits. |
PRIMARY_STATUS_NAME | The primary status name in which the work item currently sits. |
SECONDARY_STATUS_ID | The secondary status code in which the work item currently sits. |
SECONDARY_STATUS_NAME | The secondary status name in which the work item currently sits. |
STATUS_ENTRY_DATE | The date on which the work item entered the given primary and secondary status. |
STATUS_EXIT_DATE | The date on which the work item exited the given primary and secondary status. |
DAYS_IN_STATUS | The number of days the work item was in the given primary and secondary status. |
WORK_ITEM_DETAILS
This table provides all the details about a work item.
Field | Description |
WORK_ITEM_ID | The unique identifier for the work item whose details are being provided. |
REPORTING_DATE | The business date for which the report is run. |
WORK_TITLE | The title of the work item |
CLIENT_ID | The client's unique identifier to which the work item belongs to. |
CLIENT | The client's name to which the work item belongs to. |
CLIENT_TYPE | The client's type to which the work item belongs to. Can be one of - Individual, Organization or Client Group. |
INTERNAL_CLIENT_ID | The User Profile to which the work item belongs to. |
INTERNAL_CLIENT | The name of the User to which the work item belongs to. |
ACCOUNT_ID | The tenant's unique identifier to whom the Work Item belongs to. |
ACCOUNT_NAME | The tenant's name to whom the Work Item belongs to. |
WORK_TYPE_ID | The type code of the work item. |
WORK_TYPE | The type of the work item. |
PRIMARY_STATUS_ID | The primary status code in which the work item currently sits. |
SECONDARY_STATUS_ID | The secondary status code in which the work item currently sits. |
PRIMARY_STATUS | The primary status name in which the work item currently sits. |
SECONDARY_STATUS | The secondary status name in which the work item currently sits. |
CREATED_DATETIME | The date and time when the work item was created. |
CREATED_BY_ID | The user profile's unique identifier who created the work item. |
CREATED_BY | The user profile's name who created the work item. |
START_DATETIME | The date and time when the work item was started. In <timezone> |
ASSIGNED_TO_ID | The user profile's unique identifier to whom the work item is currently assigned to. |
ASSIGNED_TO | The user profile's name to whom the work item is currently assigned to. |
DUE_DATETIME | The date and time when the work item is expected to be completed. |
COMPLETED_DATETIME | The date and time when the work item is completed. In <timezone> |
COMPLETED_BY_ID | The user profile's unique identifier who completed the work item. |
COMPLETED_BY | The user profile's name who completed the work item. |
USER_DEFINED_CLIENT_ID | An id created by the user for internal use. |
FIXED_FEE_ESTIMATED_COST | The estimated fixed fee cost associated with the work item. |
BUDGETED_MINUTES | The budgeted minutes for a work item. |
BUDGETED_COST | The budgeted cost for a work item. |
EXPENSE_AMOUNT | The total expense amount against the work item. |
BILLABLE_EXPENSE_AMOUNT | The billable expense amount against the work item. |
TIME_ENTRY_MINUTES | Total time entries in minutes against the work item. |
TIME_ENTRY_COST | Total time entry costs against the work item. |
WORK_ITEM_STATUS_HISTORY
This table provides the status life cycle of a work item.
Field | Description |
WORK_ITEM_ID | The unique identifier for the work item whose details are being provided. |
CHANGED_AT_DATETIME | The DateTime when the status changed from previous to current. |
WORK_TITLE | The title of the work item |
WORK_TYPE_ID | The id for the work type to which the work item belongs. |
WORK_TYPE | The work type name to which the work item belongs. |
ASSIGNED_TO_ID | The user identifier to whom the work item was assigned to. |
ASSIGNED_TO | The name of the user to whom the work item was assigned to. |
CHANGED_BY_ID | The user identifier who changed the work item status. |
CHANGED_BY | The name of the user who changed the work item status. |
CLIENT_ID | The client's unique identifier to which the work item belongs to. |
CLIENT | The client's name to which the work item belongs to. |
INTERNAL_CLIENT_ID | The User Profile to which the work item belongs to. |
INTERNAL_CLIENT | The name of the User to which the work item belongs to. |
ACCOUNT_ID | The tenant's unique identifier to whom the Work Item belongs to. |
ACCOUNT_NAME | The tenant's name to whom the Work Item belongs to. |
CHANGED_AT_DATE | The date when the status changed from previous to current. |
PREVIOUS_PRIMARY_STATUS_ID | The primary status id of the work item before the change. |
PREVIOUS_PRIMARY_STATUS | The primary status name of the work item before the change. |
PREVIOUS_SECONDARY_STATUS_ID | The secondary status id of the work item before the change. |
PREVIOUS_SECONDARY_STATUS | the secondary status name of the work item before the change. |
PRIMARY_STATUS_ID | The primary status id of the work item after the change |
PRIMARY_STATUS | the primary status name of the work item after the change. |
SECONDARY_STATUS_ID | The secondary status id of the work item after the change. |
SECONDARY_STATUS | the secondary status name of the work item after the change. |
AUTOMATICALLY_UPDATED | Flag indicating if the status change was done by an automator. |
CLIENT_EMAIL_CONVERSATION_RESPONSE_DETAIL
This table provides a detailed summary of email responses.
Field | Description |
CONTACT | The client's name with who the response email interacted. |
CLIENT_MANAGER | The name of the User Profile who manages the responsibilities of this client. Defined on the Details tab of the contact. |
CLIENT_MANAGER_ID | The identifier of the User Profile who manages the responsibilities of this client. |
CLIENT_OWNER | The name of the User Profile who owns the responsibilities of this client. Defined on the Details tab of the contact. |
CLIENT_OWNER_ID | The identifier of the User Profile who owns the responsibilities of this client. |
CONTACT_ID | The client's unique identifier with who the response email interacted. |
CONTACT_TYPE | The type of client: Individual (Contact), Organisation, Client Group. |
CONVERSATION_ID | The ID of the thread of emails in which the response email is included. |
EMAIL_SUBJECT | The subject line of the response email. |
EMAIL_MESSAGE_ID | The identifier of the response email. |
RESPONSE_DATETIME | The timestamp of the response email. |
RESPONSE_DIRECTION | The categorization of response email into "sent" or "received". Sent means the email was sent to the client, and received means the email was received by the client. |
EMAIL_SENT_BY | The name of the user who sent the response email. |
EMAIL_SENT_BY_ID | The identifier of the user who sent the response email. |
RESPONSE_TIME_HOURS | The time (in hours) it took the user to respond. |
RESPONSE_TIME_MINUTES | The time (in minutes) it took the user to respond. |
ACCOUNT_NAME | The tenant's name to whom the email belongs to. |
ACCOUNT_ID | The tenant's unique identifier to whom the email belongs to. |
CLIENT_EMAIL_INTERACTION_DAILY_SUMMARY
This table summarizes daily email interactions.
Field | Description |
INTERACTION_DATE | The date the email was sent. |
INTERACTION_DIRECTION | The categorization of email into "sent" or "received". Sent means the email was sent to the client, and received means the email was received by the client. |
CONTACT_ID | The client's unique identifier to who the email interacted. Defined on the Details tab of the contact. |
EMAIL_SENT_BY_ID | The unique identifier for who sent the email. |
ACCOUNT_ID | The tenant's unique identifier to whom the email belongs to. |
ACCOUNT_NAME | The tenant's name to whom the email belongs to. |
CLIENT_OWNER_ID | The unique identifier of the User Profile who owns the responsibilities of this client. |
CLIENT_OWNER | The name of the User Profile who owns the responsibilities of this client. Defined on the Details tab of the contact. |
CLIENT_MANAGER_ID | The identifier of the User Profile who manages the responsibilities of this client. |
CLIENT_MANAGER | The name of the User Profile who manages the responsibilities of this client. Defined on the Details tab of the contact. |
CONTACT | The client's name with who the email interacted. |
CONTACT_TYPE | The type of client: Individual (Contact), Organisation, Client Group. |
EMAIL_SENT_BY | The name of the user who sent the email. |
TOTAL_INTERACTION_COUNT | The count of emails both sent and received. |