DD Table Name | DD Column Name | Data Type | Constraint | Definition |
|---|
spend | SpendId | bigint | PK, not null | |
spend | SubscriberId | int | not null | |
spend | FiscalYear | nvarchar(10) | not null | |
spend | FiscalPeriod | nvarchar(10) | not null | |
spend | FiscalWeek | nvarchar(10) | not null | |
spend | FiscalQuarter | nvarchar(10) | not null | |
spend | LocationId | int | null | |
spend | LocationName | nvarchar(100) | null | |
spend | StoredId | varchar(50) | null | |
spend | District | nvarchar(50) | null | |
spend | Region | nvarchar(50) | null | |
spend | Country | varchar(50) | null | |
spend | RoutineNonRoutine | nvarchar(15) | null | |
spend | ServiceType | nvarchar(100) | null | |
spend | Trade | nvarchar(100) | null | |
spend | Category | varchar(50) | null | |
spend | GLCode | nvarchar(50) | null | |
spend | GLCodeName | nvarchar(50) | null | |
spend | LocationNotesHeader1 | varchar(255) | null | |
spend | LocationNotesHeader1Value | varchar(255) | null | |
spend | LocationNotesHeader2 | varchar(255) | null | |
spend | LocationNotesHeader2Value | varchar(255) | null | |
spend | LocationNotesHeader3 | varchar(255) | null | |
spend | LocationNotesHeader3Value | varchar(255) | null | |
spend | LocationNotesHeader4 | varchar(255) | null | |
spend | LocationNotesHeader4Value | varchar(255) | null | |
spend | LocationNotesHeader5 | varchar(255) | null | |
spend | LocationNotesHeader5Value | varchar(255) | null | |
spend | TrackingNumber | int | null | |
spend | WOStatus | varchar(50) | null | |
spend | WOExtStatus | varchar(50) | null | |
spend | SpendAmountUSD | decimal(30,17) | null | |
spend | ProposalAmountUSD | decimal(30,17) | null | |
spend | CallDate | datetime | null | |
spend | WorkDate | decimal(30,17) | null | |
spend | ScheduledDate | decimal(30,17) | null | |
spend | SpendDate | decimal(30,17) | null | |
spend | UpdatedDate | decimal(30,17) | null | |
spend | SourceName | varchar(100) | not null | |
spend | SourceRecordId | int | not null | |
spend | CreatedDateInDataDirect | datetime | null | |
spend | UpdatedDateInDataDirect | datetime | null | |
StarRatings | Id | int | not null | |
StarRatings | TrackingNumber | int | null | |
StarRatings | ReviewedByUserId | int | null | |
StarRatings | StarScore | int | not null | |
StarRatings | Comment | nvarchar(1000) | null | |
StarRatings | CreatedDate | datetimeoffset | not null | |
StarRatings | UpdatedDate | datetimeoffset | not null | |
StarRatings | ReviewedByEmail | nvarchar(320) | null | |
StarRatings | LocationId | int | not null | |
StarRatings | ProviderId | int | not null | |
StarRatings | CreatedDateInDataDirect | datetimeoffset | not null | |
StarRatings | UpdatedDateInDataDirect | datetimeoffset | null | The date and time that this record was last updated in Data Direct Table. |
statuses | CreatedDateInDataDirect | datetime | null | The date and time that this record was insert in Data Direct Table. |
statuses | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
statuses | status | varchar(200) | not null | Work order's primary status (OPEN, IN PROGRESS, COMPLETED). Could be seen in a lot of places including the work order list and the work order details. |
statuses | extendedStatus | varchar(200) | null | Work order's extended status (INCOMPLETE, ON SITE, PENDING CONFIRMATION, etc). Could be seen in a lot of places including the work order list and the work order details. |
statuses | id | int | not null | Record's Unique identifier |
technician_assigned_accepted | trackingNumber | int | null | WO tracking Number |
technician_assigned_accepted | AssignedTo | varchar(100) | null | Name of the technician assigned |
technician_assigned_accepted | AcceptedBy | varchar(100) | null | Name of the technician accepted |
technician_assigned_accepted | Company | varchar(1000) | null | |
technician_assigned_accepted | UpdatedTechnicianDateUtc | datetime | null | |
technician_assigned_accepted | UpdatedTechnicianDateDto | datetimeoffset(7) | null | |
technician_assigned_accepted | id | int | PK, not null | |
technician_assigned_accepted | locationID | varchar(100) | null | Location System Identifier |
technician_assigned_accepted | locationName | varchar(100) | null | Location Name |
technician_assigned_accepted | WOAssignmentDateDto | datetimeoffset(7) | null | Timestamp when the WO was assigened to the technician |
technician_assigned_accepted | AssignedToUserId | int | null | User id of the technician |
technician_assigned_accepted | AcceptedByUserId | int | null | User id of the technician |
technician_assigned_accepted | CreatedDateInDataDirect | datetime | null | |
technician_assigned_accepted | UpdatedDateInDataDirect | datetime | null | |
technician_availability_log | SubscriberId | int | null | |
technician_availability_log | technicianEmail | nvarchar(128) | null | |
technician_availability_log | technicianFirstName | nvarchar(128) | null | |
technician_availability_log | technicianLastName | nvarchar(128) | null | |
technician_availability_log | id | int | PK, not null | |
technician_availability_log | isAvailable | nvarchar(12) | null | |
technician_availability_log | unavailableUntilDate | datetime | null | |
technician_availability_log | unavailableUntilDateDto | datetimeoffset(7) | null | |
technician_availability_log | createdDate | datetime | null | |
technician_availability_log | createdDateDto | datetimeoffset(7) | null | |
technician_time_tracking | providerID | int | null | |
technician_time_tracking | activityDescription | nvarchar(512) | null | |
technician_time_tracking | activityTypeName | nvarchar(128) | null | |
technician_time_tracking | InternalDepartment | bit | null | |
technician_time_tracking | userid | int | null | |
technician_time_tracking | SubscriberId | int | null | |
technician_time_tracking | technicianEmail | nvarchar(128) | null | |
technician_time_tracking | technicianFirstName | nvarchar(128) | null | |
technician_time_tracking | technicianLastName | nvarchar(128) | null | |
technician_time_tracking | employeeId | nvarchar(100) | null | |
technician_time_tracking | id | int | PK, not null | |
technician_time_tracking | startDate | datetime | null | |
technician_time_tracking | endDate | datetime | null | |
technician_time_tracking | elapsedMinutes | int | null | |
technician_time_tracking | EndMyDay | bit | null | |
technician_time_tracking | startDateTime_dto | datetimeoffset(7) | null | |
technician_time_tracking | endDateTime_dto | datetimeoffset(7) | null | |
technician_time_tracking | CreatedDateInDataDirect | datetime | null | |
technician_time_tracking | UpdatedDateInDataDirect | datetime | null | |
technician_time_tracking_combined | technicianTimeTrackingId | int | PK, not null | |
technician_time_tracking_combined | providerId | int | null | |
technician_time_tracking_combined | technicianEmail | nvarchar(128) | null | |
technician_time_tracking_combined | workActivity | nvarchar(512) | null | |
technician_time_tracking_combined | workActivityType | nvarchar(128) | null | |
technician_time_tracking_combined | startDateTime | datetime | null | |
technician_time_tracking_combined | endDateTime | datetime | null | |
technician_time_tracking_combined | elapsedMinutes | int | null | |
technician_time_tracking_combined | checkInSource | nvarchar(50) | PK, not null | |
technician_time_tracking_combined | technicianFirstName | nvarchar(128) | null | |
technician_time_tracking_combined | technicianLastName | nvarchar(128) | null | |
technician_time_tracking_combined | technicianContractorId | int | null | |
technician_time_tracking_combined | InternalDepartment | bit | null | |
technician_time_tracking_combined | employeeId | nvarchar(100) | null | |
technician_time_tracking_combined | userid | int | null | |
technician_time_tracking_combined | startDateTime_dto | datetimeoffset(7) | null | |
technician_time_tracking_combined | endDateTime_dto | datetimeoffset(7) | null | |
technician_time_tracking_combined | SubscriberId | int | null | |
technician_time_tracking_combined | CreatedDateInDataDirect | datetime | null | |
technician_time_tracking_combined | UpdatedDateInDataDirect | datetime | null | |
technician_time_tracking_history | SubscriberId | int | null | |
technician_time_tracking_history | Id | int | PK, not null | |
technician_time_tracking_history | TechnicianContractorId | int | not null | |
technician_time_tracking_history | ContractorActivityId | int | not null | |
technician_time_tracking_history | OriginalStartTimeUtc | datetime | null | |
technician_time_tracking_history | OriginalEndTimeUtc | datetime | null | |
technician_time_tracking_history | NewStartTimeUtc | datetime | null | |
technician_time_tracking_history | NewEndTimeUtc | datetime | null | |
technician_time_tracking_history | LogTimeUtc | datetime | not null | |
technician_time_tracking_history | Latitude | decimal(12,8) | not null | |
technician_time_tracking_history | Longitude | decimal(12,8) | not null | |
technician_time_tracking_history | OperationType | tinyint | not null | |
technician_time_tracking_history | TechnicianTimeTrackingId | int | null | |
technician_time_tracking_history | Note | nvarchar(256) | null | |
technician_time_tracking_history | OriginalStartTimeDto | datetimeoffset(7) | null | |
technician_time_tracking_history | OriginalEndTimeDto | datetimeoffset(7) | null | |
technician_time_tracking_history | NewStartTimeDto | datetimeoffset(7) | null | |
technician_time_tracking_history | NewEndTimeDto | datetimeoffset(7) | null | |
technician_time_tracking_history | LogTimeDto | datetimeoffset(7) | null | |
technician_time_tracking_history | CreatedDateInDataDirect | datetime | null | |
technician_time_tracking_history | UpdatedDateInDataDirect | datetime | null | |
TechnicianRefrigerantCertifications | providerId | int | null | |
TechnicianRefrigerantCertifications | providerName | varchar(500) | null | |
TechnicianRefrigerantCertifications | TechnicianId | int | not null | |
TechnicianRefrigerantCertifications | CertificationID | int | not null | |
TechnicianRefrigerantCertifications | CertificationNumber | nvarchar(256) | not null | |
TechnicianRefrigerantCertifications | CertificationExpirationDate | datetimeoffset(7) | null | |
TechnicianRefrigerantCertifications | CertificationDescription | nvarchar(256) | null | |
TechnicianRefrigerantCertifications | CertificationFileUrl | nvarchar(256) | null | |
TechnicianRefrigerantCertifications | CertificationFileName | nvarchar(256) | null | |
TechnicianRefrigerantCertifications | CertificationLevelName | nvarchar(100) | null | |
TechnicianRefrigerantCertifications | TechnicianName | nvarchar(260) | not null | |
TechnicianRefrigerantCertifications | TechnicanAuthUserID | bigint | null | |
TechnicianRefrigerantCertifications | CreatedDateInDataDirect | datetimeoffset(7) | null | |
TechnicianRefrigerantCertifications | UpdatedDateInDataDirect | datetimeoffset(7) | null | |
TechnicianRefrigerantCertificationFiles | TechnicianRefrigerantCertificationImageId | int | PK, not null | Unique Identifier for Refrigerant Certification File |
TechnicianRefrigerantCertificationFiles | TechnicianRefrigerantCertificationId | int | not null | Refrigernat Certification Identifier. Reference to the TechnicianRefrigerantCertifications table |
TechnicianRefrigerantCertificationFiles | CertificationFileUrl | nvarchar(256) | null | Refrigerant Certification File Url |
TechnicianRefrigerantCertificationFiles | CertificationFileName | nvarchar(256) | null | Refrigerant Certification File Name |
TechnicianRefrigerantCertificationFiles | CreatedDateInDataDirect | datetimeoffset(7) | null | Timestamp when record was created in Data Direct |
TechnicianRefrigerantCertificationFiles | UpdatedDateInDataDirect | datetimeoffset(7) | null | Timestamp when record was updated in Data Direct |
time_zone_info | timeZoneInfoId | smallint | PK, not null | |
time_zone_info | TimeZoneName | varchar(35) | not null | |
time_zone_info | abbreviation | varchar(6) | null | |
time_zone_info | CreatedDateInDataDirect | datetime | null | |
time_zone_info | UpdatedDateInDataDirect | datetime | null | |
trades | trade | varchar(200) | not null | |
trades | id | int | not null | |
trades | primaryTrade | varchar(200) | null | |
users | id | int | PK, not null | |
users | providerId | int | null | |
users | employeeID | nvarchar(1000) | null | |
users | createdDate | datetime | null | |
users | updatedDate | datetime | null | |
users | name | nvarchar(50) | null | |
users | userId | char(50) | null | |
users | SubscriberId | int | null | |
users | email | nvarchar(510) | null | |
users | phone | nvarchar(1000) | null | |
users | state | nvarchar(1000) | null | |
users | firstName | nvarchar(510) | null | |
users | lastName | nvarchar(510) | null | |
users | receiveEmailOnWoDispatch | nvarchar(1000) | null | |
users | employeeTitle | nvarchar(1000) | null | |
users | employeeTeamCrew | nvarchar(1000) | null | |
users | CreatedDateInDataDirect | datetime | null | |
users | UpdatedDateInDataDirect | datetime | null | |
vendors | providerID | int | not null | |
vendors | vendorNumber | varchar(50) | null | |
vendors | name | varchar(100) | null | |
vendors | address | varcharnvarchar(300) | null | |
vendors | sitecode | varchar(50) | null | |
vendors | order | int | not null | |
vendors | vendorPayeeID | int | null | |
vendors | subscriberID | int | null | |
vendors | CreatedDateInDataDirect | datetime | null | |
vendors | UpdatedDateInDataDirect | datetime | null | |
wo_amount_breakdown | woAmountId | int | not null | System generated unique identifier for the record. |
wo_amount_breakdown | trackingNumber | int | not null | Tracking number of the work order. |
wo_amount_breakdown | subtotal | money | null | Subtotal's value. Displayed on the work order details. |
wo_amount_breakdown | tax | money | null | Tax's value. Displayed on the work order details if the value if available. |
wo_amount_breakdown | tax2type | varchar(50) | null | Secondary tax type (GST, HST, PST, QST). |
wo_amount_breakdown | tax2 | money | null | Secondary tax's value. Displayed on the work order details if available. |
wo_amount_breakdown | currency | varchar(50) | null | Currency code (USD, CAD, etc). |
wo_amount_breakdown | autoinvoice | varchar(50) | not null | Auto invoice setting ('TRUE' or 'FALSE'). If it's 'TRUE' an invoice is auto generated for work orders in COMPLETED/CONFIRMED status by AutoInvoice process. Displayed on the work order details. |
wo_amount_breakdown | autoComplete | varchar(10) | not null | AutoComplete setting ('TRUE' or 'FALSE'). |
wo_amount_breakdown | autoApprovedBY | nvarchar(50) | null | The name of the person who is auto approving this subtotal. |
wo_amount_breakdown | lastModifiedDate | datetime | null | Record's updated date. |
wo_amount_breakdown | id | int | PK, not null | unique identifier for the record. |
wo_amount_breakdown | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
wo_amount_breakdown | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
wo_history_amount_change | id | int | not null | |
wo_history_amount_change | amountChangeId | int | not null | System generated unique identifier for the record. |
wo_history_amount_change | trackingNumber | int | not null | Tracking number of the work order. |
wo_history_amount_change | subtotal | money | null | Subtotal's value. |
wo_history_amount_change | tax | money | null | Tax's value. |
wo_history_amount_change | tax2type | varchar(50) | null | Secondary tax type (GST, HST, PST, QST). |
wo_history_amount_change | tax2 | money | null | Secondary tax's value. |
wo_history_amount_change | currency | varchar(10) | not null | Currency code (USD, CAD, etc). |
wo_history_amount_change | autocomplete | varchar(10) | not null | AutoComplete setting ('TRUE' or 'FALSE'). |
wo_history_amount_change | autoinvoice | varchar(10) | not null | Auto invoice setting ('TRUE' or 'FALSE'). If it's 'TRUE' an invoice is auto generated for work orders in COMPLETED/CONFIRMED status by AutoInvoice process. Displayed on the work order details. |
wo_history_amount_change | datecreated | datetime | not null | Record's created date. The date when the work order amount is changed. |
wo_history_change_category | id | int | PK, not null | System generated record's unique identifier. |
wo_history_change_category | TrackingNumber | int | not null | Tracking number of the work order. |
wo_history_change_category | OriginalCategory | varchar(50) | not null | Original (previous) work order's category. |
wo_history_change_category | NewCategory | varchar(50) | not null | New (current) work order's category. |
wo_history_change_category | CreatedDate | datetime | not null | Record's created date. The date when the work orders category was changed. |
wo_history_change_category | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
wo_history_change_category | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
wo_history_change_nte | nteChangeId | int | not null | System generated unique identifier for the record. |
wo_history_change_nte | trackingNumber | int | not null | Tracking number of the work order. |
wo_history_change_nte | sourceNte | money | not null | Previous NTE value. |
wo_history_change_nte | sourceCurrencyID | int | not null | Previous currency id. |
wo_history_change_nte | targetNte | money | not null | New (current) NTE value. |
wo_history_change_nte | targetCurrencyID | int | not null | New (current) currency id. |
wo_history_change_nte | createdDate | date | not null | Record's created date. The date when the work order's NTE was changed. |
wo_history_change_nte | id | int | not null | Unique Id |
wo_history_change_nte | CreatedDateInDataDirect | datetime | null | The date and time that this record was insert in Data Direct Table. |
wo_history_change_nte | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
wo_history_change_priority | id | int | PK, not null | System generated unique identifier for the record. |
wo_history_change_priority | TrackingNumber | int | not null | Tracking number of the work order. |
wo_history_change_priority | OldPrioritySub | varchar(50) | null | Previous priority. |
wo_history_change_priority | NewPrioritySub | varchar(50) | null | New (current) priority. |
wo_history_change_priority | Updatedby | nvarchar(50) | null | The user's name who changed the priority. |
wo_history_change_priority | UpdatedDate | datetime | not null | Record's created date. The date when the work order's priority was changed. |
wo_history_change_priority | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
wo_history_change_priority | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
wo_history_change_problem | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
wo_history_change_problem | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
wo_history_change_problem | OldAreaName | nvarchar(510) | null | Previous Area name for area entity (created from Dashboard Settings -Area tab) |
wo_history_change_problem | NewAreaName | nvarchar(510) | null | Area name for new added area (created from Dashboard Settings -Area tab) |
wo_history_change_problem | id | int | PK, not null | System generated unique identifier for the record. |
wo_history_change_problem | TrackingNumber | int | not null | Tracking number of the work order. |
wo_history_change_problem | OldAreaId | int | null | Previous Area id (system unique identifier for area in the database table) for area entity |
wo_history_change_problem | OldProblemType | nvarchar(256) | null | Previous Problem type field from Issue List |
wo_history_change_problem | OldEquipment | nvarchar(256) | null | Previous Equipment field from Issue List |
wo_history_change_problem | OldProblem | nvarchar(256) | null | Previous Problem field from Issue List |
wo_history_change_problem | NewAreaId | int | null | Area id(system unique identifier for area in the database table) for new added area |
wo_history_change_problem | NewProblemType | nvarchar(256) | null | Previous Problem type field from Issue List |
wo_history_change_problem | NewEquipment | nvarchar(256) | null | Previous Equipment field from Issue List |
wo_history_change_problem | NewProblem | nvarchar(256) | null | Previous Problem field from Issue List |
wo_history_change_problem | UpdatedDate | datetime | not null | The date when entity changed |
wo_history_change_provider | id | int | PK, not null | System generated unique identifier for the record. |
wo_history_change_provider | TrackingNumber | int | not null | Tracking number of the work order. |
wo_history_change_provider | OriginalProviderID | int | not null | Previous provider's Id(Unique identifier for provider in Servicechannel). |
wo_history_change_provider | NewProviderID | int | not null | New (current) provider's Id (Unique identifier of the provider in servicechannel). |
wo_history_change_provider | CreatedByUserID | int | null | User id who changed the provider. |
wo_history_change_provider | CreatedDate | datetime | not null | The date when the provider was changed on the work order. |
wo_history_change_provider | CreatedDate_dto | datetimeoffset(7) | null | The date (with time zone offset) when the provider was changed on the work order. |
wo_history_change_provider | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
wo_history_change_provider | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
wo_history_change_scheduled_date | schdateChangeId | int | not null | System generated unique identifier for the record. |
wo_history_change_scheduled_date | trackingNumber | int | not null | Tracking number of the work order. |
wo_history_change_scheduled_date | oldScheduledDate | datetime | null | Old schedule date. |
wo_history_change_scheduled_date | newScheduledDate | datetime | null | New (current) schedule date. |
wo_history_change_scheduled_date | updatedby | nvarchar(50) | null | The user's name who changed the schedule date. |
wo_history_change_scheduled_date | updatedDate | datetime | not null | Record's created date. The date when the work order's scheduled date was changed. |
wo_history_change_scheduled_date | id | int | not null | unique identifier for the record. |
wo_history_change_scheduled_date | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
wo_history_change_scheduled_date | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
wo_history_change_scheduled_date | RescheduleReason | nvarchar(256) | null | Text for reschedule reason |
wo_history_change_scheduled_date | UpdatedByUserId | int | null | The id of the user who made the schedule change |
wo_history_change_status | statusChangeId | int | not null | System generated unique identifier for the record. |
wo_history_change_status | trackingNumber | int | not null | Tracking number of the work order. |
wo_history_change_status | oldStatus | varchar(50) | not null | Old primary status. |
wo_history_change_status | oldExtendedStatus | varchar(50) | null | Old extended status. |
wo_history_change_status | newStatus | varchar(50) | not null | New (current) primary status. |
wo_history_change_status | newExtendedStatus | varchar(50) | null | New (current) extended status. |
wo_history_change_status | updatedBy | nvarchar(50) | null | The user's name who changed the status. |
wo_history_change_status | updatedDate | datetime | not null | Record's created date. The date when work order's status was changed. |
wo_history_change_status | createdbyUserId | int | null | The user's id who changed the status. |
wo_history_change_status | updatedDateDto | datetimeoffset(7) | null | Record's created date. The date when work order's status was changed. |
wo_history_change_status | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
wo_history_change_status | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
wo_history_change_status | id | int | not null | |
wo_history_change_trade | OldTrade | varchar(50) | null | Subscriber trade name. |
wo_history_change_trade | NewTrade | varchar(50) | null | Subscriber trade name. |
wo_history_change_trade | id | int | PK, not null | unique identifier for the record. |
wo_history_change_trade | TrackingNumber | int | not null | Tracking number of the work order. |
wo_history_change_trade | OldContractID | int | not null | Old contract id. |
wo_history_change_trade | NewContractID | int | not null | New (current) contract id. |
wo_history_change_trade | UpdatedBy | nvarchar(50) | null | The user's name who changed the contract. |
wo_history_change_trade | UpdatedDate | datetime | not null | Record's created date. The date when work order's trade was changed. |
wo_history_change_trade | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
wo_history_change_trade | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
workorder_additional_approval_codes | WOAdditionalApprovalCodeId | int | not null | System generated unique identifier |
workorder_additional_approval_codes | TrackingNumber | int | not null | WO Tracking Number |
workorder_additional_approval_codes | ApprovalCodeName | varchar(50) | null | Name of the Approval Code |
workorder_additional_approval_codes | ApprovalCodeValue | varchar(300) | null | Approval Code Value |
workorder_additional_approval_codes | IsEnabled | bit | not null | Flag indicating whether this additional approval code is enabled or not |
workorder_additional_approval_codes | Category | varchar(50) | null | Category for the work order |
workorder_additional_approval_codes | SrcCreatedDate | datetime | not null | The date and time that this record was created in source |
workorder_additional_approval_codes | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
workorder_additional_approval_codes | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
workorder_ivr | trackingNumber | int | not null | |
workorder_ivr | ivrID | int | not null | |
workorder_ivr | ivrAction | int | null | ivrAction = 1 means Checkin
ivrAction = 2 means Checkout
ivrAction = 3 means Checkin or Checkout was denied but an entry was made into the ivr log. |
workorder_ivr | ivrTime | datetime | null | |
workorder_ivr | lastModifiedDate | datetime | null | Date when IVR record was updated for this work order in Platform |
workorder_ivr | checkInSource | varchar(10) | null | Source of the Checkin. "ivr" or "gps" or "xml" or "web" |
workorder_ivr | id | int | not null | System generated unique identifier for the record. |
workorder_ivr | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
workorder_ivr | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
workorder_ivr | GpsIVR_IsCompliant | bit | null | Flag indicating whether the check in/ checkout is compliant within the radius |
workorder_ivr | CheckInOutIndicator | varchar(50) | null | Indicates whether the record is for Checkin v/s Checkout |
workorder_ivr | StatusIndicated | varchar(50) | null | Status of the workorder at that time |
workorder_ivr | WorkType | varchar(100) | null | Tyoe of work for the visit |
workorder_ivr | SignatureStatus | varchar(50) | null | Status indicating if Signature was obtained or not. |
workorder_ivr | pin | varchar(50) | null | User Pin |
workorder_ivr | user_id | varchar(50) | null | user_id of the technician |
workorder_ivr | ProviderId | int | null | Provider Identifier of the user associated with the record |
workorder_labor_performed | mchID | int | not null | Primary key for the table |
workorder_labor_performed | TrackingNumber | int | not null | Workorder Tracking Number |
workorder_labor_performed | Record | smallint | null | Record Number |
workorder_labor_performed | Mechanic | nvarchar(80) | null | Mechanic who performed the work |
workorder_labor_performed | WorkDate | Datetime | null | Date of work performed |
workorder_labor_performed | TimeIn | char(8) | null | Time of Check In |
workorder_labor_performed | TimeOut | char(8) | null | Time of Check Out |
workorder_labor_performed | WorkType | varchar(100) | null | Tyoe of work for the visit |
workorder_labor_performed | NumberOfTechs | int | null | Number of technicians |
workorder_labor_performed | RegHrs | real | null | Regular Hours |
workorder_labor_performed | GpsIVR_IsCompliant | bit | null | Flag indicating whether all the check in/ checkout is compliant |
workorder_labor_performed | checkInTime | Datetime | null | Time of Check In (full datetime) |
workorder_labor_performed | checkOutTime | Datetime | null | Time of Check Out (full datetime) |
workorder_labor_performed | CreatedDate | Datetime | null | Created timestamp of the record |
workorder_labor_performed | UpdatedDate | Datetime | null | Updated timestamp of the record |
workorder_labor_performed | t_t_hours | real | null | Travel time hours |
workorder_labor_performed | r_t_hours | real | null | Regular time hours |
workorder_labor_performed | o_t_hours | real | null | Overtime hours |
workorder_labor_performed | d_t_hours | real | null | Double time hours |
workorder_labor_performed | pin | int | null | User Pin |
workorder_labor_performed | User_id | varchar(50) | null | user_id of the technician |
workorder_labor_performed | checkInTime_dto | datetimeoffset | null | Time of Check In (with datetimeoffset) |
workorder_labor_performed | checkOutTime_dto | datetimeoffset | null | Time of Check Out (with datetimeoffset) |
workorder_labor_performed | CreatedDate_dto | datetimeoffset | null | Created timestamp of the record (with datetimeoffset) |
workorder_labor_performed | UpdatedDate_dto | datetimeoffset | null | Updated timestamp of the record (with datetimeoffset) |
workorder_labor_performed | CreatedDateInDataDirect | Datetime | null | Timestamp when record was created in Data Direct |
workorder_labor_performed | UpdatedDateInDataDirect | Datetime | null | Timestamp when record was updated in Data Direct |
workorder_labor_performed | ProviderId | int | null | Provider Identifier of the user associated with the record |
workorder_mechanic | ivrID | int | not null | System generated unique identifier for the ivr record. |
workorder_mechanic | trackingNumber | int | not null | Tracking number of the work order for which the labor record was created. |
workorder_mechanic | ivrAction | int | not null | 1 for mechanic's ivr time in, 2 for mechanic's ivr time out |
workorder_mechanic | ivrTime | datetime | null | The date and time when the labor was performed. |
workorder_mechanic | lastModifiedDate | datetime | | Record's updated date. |
workorder_mechanic | id | int | not null | System generated unique identifier. |
workorder_mechanic | CreatedDateInDataDirect | datetime | null | The date and time that this record was insert in Data Direct Table. |
workorder_mechanic | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
workorder_notes | id | int | not null | System generated record's unique identifier. |
workorder_notes | NoteNum | int | not null | Note's index. |
workorder_notes | Note | nvarchar(4000) | null | Note's content. Displayed on the work order details and in the note list. |
workorder_notes | CreatedBySource | nvarchar(100) | null | The name of the user or automated process who created the note. Displayed in the note list on the work order details. |
workorder_notes | TrackingNumber | int | not null | Tracking number of the work order for which the note was created. |
workorder_notes | ProviderID | int | null | Provider's id. |
workorder_notes | CreatedDate | datetime | null | Created date of the note, with seconds and milliseconds. |
workorder_notes | Date_Created_dto | datetimeoffset(7) | null | Created date of the note. With seconds always zero (:00) and without fractional seconds, but with time zone offset. |
workorder_notes | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
workorder_notes | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
workorder_notes | ActionRequired | varchar(3) | null | Whether further action is required upon this note ('Yes','',null) |
workorder_notes | NoteType | varchar(50) | null | Identifies workorder note as System v/s User Note. Will only be available if this feature is enabled for the customer |
workorder_notes | ActionRequiredResolvedStatusId | tinyint | null | Id for the Action Required Resolved Status |
workorder_notes | ActionRequiredResolvedStatusName | varchar(100) | null | Action Required Resolved Status Name |
workorder_notes | ActionRequiredResolvedDate | datetimeoffset | null | Action Required Resolved Date |
workorder_notes | ActionRequiredResolvedByUserId | int | null | Id of the user who resolved the Action Required |
workorder_notes_new | id | bigint | not null | System generated record's unique identifier. |
workorder_notes_new | NoteNum | int | not null | Note's index. |
workorder_notes_new | Note | nvarchar(4000) | null | Note's content. Displayed on the work order details and in the note list. |
workorder_notes_new | CreatedBySource | nvarchar(100) | null | The name of the user or automated process who created the note. Displayed in the note list on the work order details. |
workorder_notes_new | TrackingNumber | int | not null | Tracking number of the work order for which the note was created. |
workorder_notes_new | ProviderID | int | null | Provider's id. |
workorder_notes_new | CreatedByUserId | int | null | Id of the user who created the Note record |
workorder_notes_new | CreatedDate | datetime | null | Created date of the note, with seconds and milliseconds. |
workorder_notes_new | Date_Created_dto | datetimeoffset(7) | null | Created date of the note. With seconds always zero (:00) and without fractional seconds, but with time zone offset. |
workorder_notes_new | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
workorder_notes_new | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
workorder_notes_new | ActionRequired | varchar(3) | null | Whether further action is required upon this note ('Yes','',null) |
workorder_notes_new | NoteType | varchar(50) | null | Identifies workorder note as System v/s User Note. Will only be available if this feature is enabled for the customer |
workorder_notes_new | ActionRequiredResolvedStatusId | tinyint | null | Id for the Action Required Resolved Status |
workorder_notes_new | ActionRequiredResolvedStatusName | varchar(100) | null | Action Required Resolved Status Name |
workorder_notes_new | ActionRequiredResolvedDate | datetimeoffset | null | Action Required Resolved Date |
workorder_notes_new | ActionRequiredResolvedByUserId | int | null | Id of the user who resolved the Action Required |
workorder_quality_survey | id | int | PK, not null | System generated unique identifier for the record. |
workorder_quality_survey | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
workorder_quality_survey | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
workorder_quality_survey | trackingNumber | int | not null | System generated unique identifier for the record. |
workorder_quality_survey | respondent | nvarchar(100) | null | User name of the user who provided the feedback |
workorder_quality_survey | createdby | nvarchar(50) | null | User ID of the user |
workorder_quality_survey | comment | nvarchar(4000) | null | User notes |
workorder_quality_survey | createdDate | datetime | null | Created date of the survey's answer |
workorder_quality_survey | answer | varchar(100) | not null | Predefined answer text |
workorder_reassignment_reasons | id | int | not null | Unique identifier for the work order reassignment reason record. |
workorder_reassignment_reasons | trackingNumber | int | not null | Tracking number of the work order. |
workorder_reassignment_reasons | actionName | varchar(500) | not null | Reassignment action name |
workorder_reassignment_reasons | actionValue | nvarchar(500) | null | Reassignment reason |
workorder_reassignment_reasons | actionEventID | int | null | Identifier for the unique action events |
workorder_reassignment_reasons | actionTimeStamp | datetime | null | Time stamp of when the action happened |
workorder_reassignment_reasons | actionTimeStamp_dto | datetimeoffset(7) | null | Time stamp of when the action happened with offset to UTC |
workorder_reassignment_reasons | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
workorder_reassignment_reasons | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
workorders | categoryID | int | not null | Unique identifier for the work order category. Related to categories table. |
workorders | equipmentTagID | nvarchar(50) | null | Random unique identifier within a location entered by the client. It can be auto generated by the application if the customer selects "auto generate tag id" setting in AssetType. |
workorders | IVR | bit | null | A flag that indicates that the work order has both a checkin and a checkout and ends with a checkout. |
workorders | SLA | bit | null | 1 = Yes and 0 = NO; A flag to indicate whether this work order met the original ETA-i.e that the first check in, onsite or completion date for the work order was before the original expected ETA.
This is a calculated field that checks if either one of the data elements below is less than the Original ETA Date (in the order given):
If Check-in date is less than Original ETA, then SLA is 1 (meaning, they came on time)
If Check-in date is NULL then the logic checks for the Onsite date. If Onsite date is less than Original ETA, then SLA is 1
If Onsite date is NULL then the logic checks for the Completion date. If Completion date is less than Original ETA, then SLA is 1
If none of the above is true, then SLA = 0 |
workorders | WORecall | nvarchar(3) | null | A flag to check if the work order was recalled. |
workorders | HoursOnSite | real | null | Total time in hours spent by the provider on the work order. |
workorders | FirstTimeCompletionFlag | bit | null | An indication that this work order was completed on the first visit. i.e it has only one check-in and one check-out which were recorded within a 24 hour period. |
workorders | callerIDMatch | bit | null | Whether or not the user's check-in record matches with the location. '1' if there is a CallerID that matches with the callerID listings of this work order's location. '0' otherwise. |
workorders | checkInSource | varchar(20) | null | The source, channel or the medium of the user's check-in events(e.g. web api, gps, ivr ..) |
workorders | resolutionCode | varchar(100) | null | Resolution code name for Completed Processing functionality. If it's configured, providers could move work orders into Completed/Confirmed status using Completed Processing tab on the work orders list and input some data such as resolution code and failing component. |
workorders | failingComponent | varchar(100) | null | Failing component name for Completed Processing functionality. If it's configured, providers could move work orders into Completed/Confirmed status using Completed Processing tab on the work orders list and input some data such as resolution code and failing component. |
workorders | trackingNumber | int | not null | System generated ID that uniquely identifies a work order (displayed in the application as Tracking Number) |
workorders | calldate | datetime | null | Created date (without time) of the work order from creator's opinion. It is user inputted and differs from actual created date of the work order in the system. Could be set in past and future. Could be viewed on the work order details in Created by section. In the workorder's location's time zone. |
workorders | completiondate | datetime | null | The date of the last work order's transition to completed status. |
workorders | locationID | int | not null | Location ID of the work order . |
workorders | providerID | int | not null | Provider ID of the work order. Could be viewed in the provider details modal after clicking on the provider's name on the work order list. |
workorders | priorityID | int | not null | Priority ID of the work order |
workorders | statusID | int | not null | Primary status(OPEN, IN PROGRESS, COMPLETED). |
workorders | scheduledDate | datetime | null | Scheduled date. Due date until when work order must be completed. |
workorders | woNumber | varchar(50) | null | Work order number. The field which could be used for labeling with client's internal number. Could be viewed both on the work order list and the work order details. Could be used in the search on the work order list. |
workorders | reassigneddate | datetime | null | Date of the last reassignment to another provider. |
workorders | createdby | nvarchar(50) | null | The user name of work order's creator (a person or a process). |
workorders | caller | varchar(100) | null | Caller, the person or process who initiated the work order. User inputted field. Filled within work order creation. Could be viewed on the work order details and the work order list. |
workorders | lastModifiedDate | datetime | null | The date the work order has last been updated. |
workorders | source | varchar(20) | null | The source of the work order. The name of the system through which it was created. E.g. Web Request, WebApi, Mobile, ScConnector. Could be viewed on the work order details as Source field. |
workorders | poNumber | varchar(50) | null | Work order number. The field which could be used for labeling with client's internal number. Could be used in the search on the work order list. |
workorders | createdDate | datetime | null | The Date and Time the work order was created. In ET time zone. |
workorders | UserIdUpdatedBy | int | null | The user's id who updated the work order. |
workorders | assignedDate | datetime | null | The date when work order was assigned to assignee. |
workorders | recallTrackingNumber | int | null | Tracking number of the work order for which a recall work order was created. Could be viewed both on the work order list and the work order details. |
workorders | assignees | nvarchar(100) | null | The user name or the user ID of the user who was assigned to the work order. |
workorders | createdByUserID | int | null | Id of User who created the work order |
workorders | ProjectedCompletionDate | datetimeoffset(7) | null | Projected Completion Date (with time zone offset). Could be entered by providers in Add Note modal. |
workorders | tradeID | int | not null | System generated ID that uniquely identifies the trade of the work order. Relates to Trade table in Data Direct schema. |
workorders | glCode | nvarchar(120) | null | GL code value (approval code value). Special code which could be used for accounting. Displayed on the work order details. |
workorders | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
workorders | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
workorders_ext | currency | varchar(50) | null | Currency code (USD, CAD, etc) |
workorders_ext | trackingNumber | int | not null | System generated ID that uniquely identifies a work order (displayed in the application as Tracking Number) |
workorders_ext | nteAmount | money | null | NTE's value of the work order. Could be seen in screens like the work order list and the work order details. |
workorders_ext | problemDescription | varchar(8000) | null | Work order's problem description. Displayed both on the work order details and the work order list |
workorders_ext | problemCode | varchar(256) | null | Work order's problem code. One of the issue list properties required to select when creating a work order via the dashboard. Not filled for work orders created via Classic Service Request. |
workorders_ext | eta | datetime | null | Work order's expiration date. |
workorders_ext | originalEta | datetime | null | Original work order's ETA. This value is set when a work order is created and it could not be changed afterwards. |
workorders_ext | postedDate | datetime | null | Posted date of the work order that was processed via EDI |
workorders_ext | poLoadFlag | int | null | Custom field for poLoadFlag of the work order that was processed via EDI |
workorders_ext | poLoadFlagDate | datetime | null | Custom field for poLoadFlagDate of the work order that was processed via EDI |
workorders_ext | CreatedDateInDataDirect | datetime | null | The date and time that this record was insert in Data Direct Table. |
workorders_ext | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
workorders_ext | area | varchar(256) | null | Area name created from Dashboard Settings (Area tab) |
workorders_ext | problemType | varchar(256) | null | Problem type field which comes from Issue List |
workorders_ext | equipment | varchar(256) | null | Equipment field which comes from Issue List |
workorders_linked | id | int | PK, not null | System generated unique identifier for the record. |
workorders_linked | parent_WO | int | not null | Tracking number of the ORIGINAL work order. |
workorders_linked | child_WO | int | not null | Tracking number of the FOLLOW UP (aka linked) work order. Follow up work orders marked with chain icon on the work order list. Hovering the icon displays the information about the original work order. |
workorders_linked | dateCreated | datetime | not null | The date this record is created. |
workorders_linked | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
workorders_linked | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
workorders_pm_compliance | TrackingNumber | int | PK, not null | Unique Identifier for the workorder |
workorders_pm_compliance | ValidFromDate | datetimeoffset(7) | null | Date from the record is valid from |
workorders_pm_compliance | ValidToDate | datetimeoffset(7) | null | Date from the record is valid to |
workorders_pm_compliance | AttachmentBlobKey | nvarchar(255) | null | Key to the Blob |
workorders_pm_compliance | AttachmentFileName | nvarchar(255) | null | Attachment File Name |
workorders_pm_compliance | Comment | nvarchar(4000) | null | Comments on the record |
workorders_pm_compliance | UpdatedByUserId | int | not null | User who updated the record |
workorders_pm_compliance | UpdatedDate | datetimeoffset(7) | not null | Timestamp when the record was updated |
workorders_pm_compliance | StatusId | tinyint | not null | Unique id representing the status of the compliance record |
workorders_pm_compliance | ComplianceStatus | nvarchar(50) | null | Status of the compliance record |
workorders_pm_compliance | ServiceId | int | null | Identifier for the planned maintenance service record |
workorders_pm_compliance | ServiceDescription | nvarchar(max) | null | Description of the planned maintenance service record |
workorders_pm_compliance | FrequencyId | int | null | Identifier for the planned maintenance frequency group record |
workorders_pm_compliance | FrequencyDescription | nvarchar(max) | null | Description of the planned maintenance frequency group record |
workorders_pm_compliance | CreatedDateInDataDirect | datetime | null | The date and time that this record was inserted in Data Direct Table. |
workorders_pm_compliance | UpdatedDateInDataDirect | datetime | null | The date and time that this record was last updated in Data Direct Table. |
