DD TABLE NAME | DD COLUMN NAME | DATA TYPE | CONSTRAINT | DEFINITION |
DD Table Name | DD Column Name | Data Type | Constraint | Definition |
AnticipatedWorkorder | ID | int | PK, not null | |
AnticipatedWorkorder | TrackingNumber | int | null | |
AnticipatedWorkorder | SuscriberId | int | not null | |
AnticipatedWorkorder | ScheduleId | int | not null | |
AnticipatedWorkorder | GroupId | int | not null | |
AnticipatedWorkorder | LocationId | int | not null | |
AnticipatedWorkorder | AssignedLocationId | int | not null | |
AnticipatedWorkorder | ContractId | int | null | |
AnticipatedWorkorder | Trade | nvarchar(255) | null | |
AnticipatedWorkorder | Category | nvarchar(255) | null | |
AnticipatedWorkorder | Priority | nvarchar(255) | null | |
AnticipatedWorkorder | NTE | money | null | |
AnticipatedWorkorder | ProviderId | int | null | |
AnticipatedWorkorder | Description | nvarchar(max) | null | |
AnticipatedWorkorder | CreatedDate | datetime | not null | |
AnticipatedWorkorder | ScheduledDate | datetime | not null | |
AnticipatedWorkorder | ExpirationDate | datetime | null | |
AnticipatedWorkorder | ServiceNumber | int | not null | |
AnticipatedWorkorder | WoLength | int | null | |
AnticipatedWorkorder_Frequency | ID | int | PK, not null | |
AnticipatedWorkorder_Frequency | GroupId | int | not null | |
AnticipatedWorkorder_Frequency | ServiceId | int | not null | |
AnticipatedWorkorder_Frequency | FrequencyType | varchar(50) | null | |
AnticipatedWorkorder_Frequency | NumberOfDaysPerWeek | int | null | |
AnticipatedWorkorder_Frequency | WeekInterval | int | null | |
AnticipatedWorkorder_Frequency | NumberOfMonthsPerYear | int | null | |
AnticipatedWorkorder_Frequency | StartOn | datetime | null | |
AnticipatedWorkorder_Frequency | StopOn | datetime | null | |
AnticipatedWorkorder_Frequency | SubscriberId | int | null | |
assets | EquipmentID | int | PK, not null | PK and should be used to join with other asset tables |
assets | LocationID | int | not null | Client location id from locations table |
assets | Active | bit | not null | Is the asset active flag |
assets | TagID | nvarchar(100) | not null | TagID of the asset, unique for a location and should be used to join with WO table |
assets | AssetType | nvarchar(200) | null | |
assets | Trade | nvarchar(100) | not null | |
assets | Brand | nvarchar(300) | null | |
assets | ModelNo | nvarchar(300) | null | |
assets | SerialNo | nvarchar(300) | null | |
assets | InstallDate | datetime | null | Installation date, can be relevant for Warranty calculation |
assets | PurchaseDate | datetime | null | |
assets | Condition | int | null | |
assets | WarrantyPeriod | int | null | |
assets | WarrantyStartsFromInstallDate | bit | null | Either use installation or purchase date for warranty calculation |
assets | LifeExpectancy | int | null | Optional field on life expectancy |
assets | OriginalValue | money | null | |
assets | EnergyEfficiency | nvarchar(300) | null | Option field on energy efficiency |
assets | Capacity | nvarchar(300) | null | |
assets | WarrantyExpirationDate | datetime | null | Another way to calculate warranty end date |
assets | DeactivatedDate | datetime | null | When the asset was deactivated |
assets | Note | nvarchar(300) | null | |
assets | QRcode | nvarchar(300) | null | New identifier for assets, if QR is available, it willl be stored here |
audit | AuditID | int | PK, not null | Audit PK, should be used to join other audit tables |
audit | AuditType | nvarchar(500) | not null | Audit type from predefined values |
audit | LocationID | varchar(50) | not null | Client location id from locations table |
audit | LocationName | nvarchar(100) | null | Client location name |
audit | Region | nvarchar(50) | null | client region name |
audit | District | nvarchar(50) | null | client district name |
audit | InspectorName | varchar(50) | not null | Who completed the audit, free text |
audit | Date | date | not null | Audit date |
audit | Time | time(7) | not null | Audit time |
audit_check_list_item | AuditCheckListItemID | int | not null | PK |
audit_check_list_item | AuditID | int | not null | PK from audit table |
audit_check_list_item | Area | nvarchar(500) | not null | Audit area |
audit_check_list_item | AuditCheckListItemName | nvarchar(500) | not null | Audit Item Name |
audit_check_list_item | AuditCheckListItemDescription | nvarchar(max) | not null | Audit Item Description |
audit_check_list_item | AuditCheckListItemStatus | int | not null | Audit Item Status |
audit_check_list_item | TrackingNumber | int | null | Tracking Number if one was created for the item |
audit_check_list_item | CommentQuantity | int | not null | Quantity for the item |
audit_issue_attachment | AuditIssueAttachmentId | int | not null | PK for the table |
audit_issue_attachment | AuditCheckListItemID | int | not null | PK from audit_check_list_item |
audit_issue_attachment | AuditID | int | null | PK from Audit table |
audit_issue_attachment | FileName | nvarchar(255) | not null | Filename uploaded during the audit for this item |
budget | BudgetId | bigint | PK, not null | |
budget | SuscriberId | int | not null | |
budget | FiscalYear | nvarchar(10) | not null | |
budget | FiscalPeriod | nvarchar(10) | not null | |
budget | FiscalWeek | nvarchar(10) | null | |
budget | FiscalQuarter | nvarchar(10) | null | |
budget | LocationId | int | null | |
budget | LocationName | nvarchar(100) | null | |
budget | StoreId | varchar(50) | null | |
budget | District | nvarchar(50) | null | |
budget | Region | nvarchar(50) | null | |
budget | Country | nvarchar(50) | null | |
budget | RoutineNonRoutine | nvarchar(15) | null | |
budget | ServiceType | nvarchar(100) | null | |
budget | Trade | nvarchar(100) | null | |
budget | Category | varchar(50) | null | |
budget | GLCode | nvarchar(50) | null | |
budget | GLCodeName | nvarchar(50) | null | |
budget | BudgetAmount | decimal(30,17) | null | |
budget | ForecastAmount | decimal(30,17) | null | |
budget | AccruedAmount | decimal(30,17) | null | |
budget | LocationNoteHeader1 | varchar(255) | null | |
budget | LocationNoteHeader1value | varchar(255) | null | |
budget | LocationNoteHeader2 | varchar(255) | null | |
budget | LocationNoteHeader2value | varchar(255) | null | |
budget | LocationNoteHeader3 | varchar(255) | null | |
budget | LocationNoteHeader3value | varchar(255) | null | |
budget | LocationNoteHeader4 | varchar(255) | null | |
budget | LocationNoteHeader4value | varchar(255) | null | |
budget | LocationNoteHeader5 | varchar(255) | null | |
budget | LocationNoteHeader5value | varchar(255) | null | |
budget | UpdatedDate | datetime | null | |
budget | SourceName | varchar(100) | not null | |
budget | SourceRecordId | int | not null | |
categories | id | int | not null | |
Categories | Category | varchar(200) | not null | |
checklists | id | int | not null | |
checklists | Question | varchar(500) | not null | |
checklists | Answer | varchar(500) | not null | |
checklists | SurveyName | varchar(100) | not null | |
checklists | Attempt | tinyint | not null | |
checklists | TechnicianId | int | not null | |
checklists | TrackingNumber | int | not null | |
checklists | CreatedByUserID | int | not null | |
checklists | ProviderID | int | not null | |
checklists | isActive | int | not null | |
checklists | CreatedDate | datetime | not null | |
checklists | UpdatedDate | datetime | not null | |
checklists | FreeTextAnswer | nvarchar(200) | null | |
CheckListAssetTaskResponses | id | int | not null | |
CheckListAssetTaskResponses | AssetTaskId | int | not null | |
CheckListAssetTaskResponses | AssetId | int | not null | |
CheckListAssetTaskResponses | TrackingNumber | int | not null | |
CheckListAssetTaskResponses | Attempt | int | not null | |
CheckListAssetTaskResponses | StartTime | datetimeoffset | not null | |
CheckListAssetTaskResponses | EndTime | datetimeoffset | null | |
CheckListAssetTaskResponses | UserId | int | not null | |
CheckListTasks | id | int | not null | |
CheckListTasks | CheckListId | int | not null | |
CheckListTasks | TaskTypeName | nvarchar(100) | not null | |
CheckListTasks | TypeId | int | not null | |
CheckListTasks | Name | nvarchar(50) | not null | |
CheckListTasks | Description | nvarchar(4000) | not null | |
CheckListTasks | IsActive | bit | not null | |
CheckListTasks | CreatedDate | datetimeoffset | not null | |
CheckListTasks | CreatedBy | int | not null | |
CheckListTasks | UpdatedDate | datetimeoffset | not null | |
CheckListTasks | UpdatedBy | int | null | |
custom_asset_attributes | equipmentAttributeID | int | not null | |
custom_asset_attributes | equipmentID | int | not null | |
custom_asset_attributes | equipmentType | nvarchar(200) | null | |
custom_asset_attributes | valueText | nvarchar(2000) | null | |
custom_asset_attributes | valueNumber | int | null | |
custom_asset_attributes | valueBool | bit | null | |
custom_asset_attributes | ValueMoney | money | null | |
custom_asset_attributes | valueDate | datetime | null | |
custom_asset_attributes | valueChoice | int | null | |
custom_asset_attributes | equipmentTypeCustomAttributeName | nvarchar(50) | null | |
custom_asset_attributes | defaultValue | nvarchar(150) | null | |
custom_asset_attributes | required | bit | null | |
custom_asset_attributes | position | int | null | |
custom_asset_attributes | UpdatedDate | datetime | null | |
ftm | id | int | PK, not null | |
ftm | trackingNumber | int | not null | |
ftm | ProviderID | int | null | |
ftm | email | nvarchar(128) | null | |
ftm | checkInDateTime | datetime | null | |
ftm | checkOutDateTime | datetime | null | |
ftm | workType | varchar(100) | null | |
ftm | technicianFirstName | nvarchar(128) | null | |
ftm | technicianLastName | nvarchar(128) | null | |
ftm | InternalDepartment | bit | null | |
ftm | checkinTime_dto | datetimeoffset(7) | null | |
ftm | checkOutTime_dto | datetimeoffset(7) | null | |
ftm | employeeId | nvarchar(100) | null | |
ftm | userid | int | null | |
ftm | SubscriberId | int | null | |
invoice_labor | ID | int | PK, not null | |
invoice_labor | InvoiceID | int | not null | |
invoice_labor | SkillLevel | varchar(10) | null | WHEN nameID = 1 THEN 'Supervisor' WHEN nameID = 2 THEN 'Technician' WHEN nameID = 3 THEN 'Helper' |
invoice_labor | Type | varchar(10) | null | WHEN detailsID = 1 THEN 'Regular' WHEN detailsID = 2 THEN 'Overtime' WHEN detailsID = 3 THEN 'DoubleTime' |
invoice_labor | NumberOfTechs | tinyint | not null | Number of techs |
invoice_labor | HourlyRate | money | not null | Hourly rate |
invoice_labor | Hours | decimal(9,2) | null | Hours |
invoice_labor | Amount | money | not null | Amount |
invoice_labor | LineNumber | smallint | null | Line Number |
invoice_materials | ID | int | PK, not null | Table Primary Key |
invoice_materials | InvoiceID | int | not null | Invoice Table PK |
invoice_materials | Description | varchar(100) | null | Material Description |
invoice_materials | PartNumber | varchar(100) | null | Part Number |
invoice_materials | Unit | varchar(5) | null | WHEN ic.detailsID = 1 THEN 'Each' WHEN ic.detailsID = 2 THEN 'Box' WHEN ic.detailsID = 3 THEN 'Feet' WHEN ic.detailsID = 4 THEN 'Sq ft' WHEN ic.detailsID = 5 THEN 'Lbs' WHEN ic.detailsID = 6 THEN 'Gal' WHEN ic.detailsID = 7 THEN 'Oz |
invoice_materials | UnitPrice | money | not null | Unit Price |
invoice_materials | Quantity | decimal(9,2) | null | Quantity |
invoice_materials | Amount | money | not null | Amount in local currency |
invoice_materials | LineNumber | smallint | null | Line Number |
invoice_statuses | id | int | not null | ID used invoice table |
invoice_statuses | status | varchar(50) | not null | Status Name |
invoices | InvoiceID | int | not null | Invoice ID, unique identifier |
invoices | trackingNumber | int | not null | Tracking Number |
invoices | invoiceNumber | varchar(50) | not null | Invoice Number used when creating an invoice, subscriber unique |
invoices | invoiceDate | datetime | not null | InvoiceDate - the date which specified by the client itself at the invoice creation time (it could be specified in both Template or UI). FYI it could be the Future date (default value - equals to PostedDate) |
invoices | invoiceStatusID | int | not null | look up invoice_statuses table |
invoices | currency | varchar(50) | null | Currency for the work order (best to use workorder_ext since invoice might not exist) |
invoices | laborAmount | money | null | |
invoices | materialAmount | money | null | |
invoices | travelAmount | money | null | |
invoices | freightAmount | money | null | |
invoices | taxAmount | money | null | |
invoices | otheramount | money | null | |
invoices | otherdescription | varchar(255) | null | |
invoices | total | money | null | |
invoices | subtotal | money | null | |
invoices | postedDate | datetime | null | the actual date of invoice creation. The date when invoice appears in the database. FYI it is impossible for the Posted Date to have Future date value. |
invoices | TransferDate | datetime | null | Transfer Date - the date when EDI process send the invoice to Subscriber. |
invoices | lastModifiedDate | datetime | null | the date when the invoice was last modified |
invoices | glCode | varchar(40) | null | |
invoices | createdDate | datetime | null | There is no such field for SC and DataDirect. Created date reffers to InvoiceDateSimilar to posted date, when the invoice is inserted into the database |
invoices | isMarkedForAudit | bit | null | |
invoices | vendorPayeeID | int | null | |
invoices | approvedDate | datetime | null | ApprovedDate - the date when invoice gets Approved status |
invoices | approvedDateDto | datetimeoffset | null | ApprovedDateDTO - the date when invoice gets Approved status using datetimeoffset format (showing the Time Zone) |
invoices | approvedBy | varchar(50) | null | |
invoices | lastPaymentDate | datetime | null | |
invoices | LastPaymentDateDto | datetimeoffset | null | |
invoices | postedDateDto | datetimeoffset | null | the actual date of invoice creation. The date when invoice appears in the database, using datetimeoffset format (showing the Time Zone) |
invoices | createddateDto | datetimeoffset | null | There is no such field for SC and DataDirect. Created date reffers to InvoiceDateCreated date using datetimeoffset format (showing the Time Zone) |
invoices | transferredDateDto | datetime | null | Transfer Date - the date when EDI process send the invoice to Subscriber, using datetimeoffset format (showing the Time Zone) |
labels | trackingNumber | int | PK, not null | |
labels | Label | nvarchar(50) | not null | |
locations | locationID | int | not null | |
locations | storeNumber | varchar(50) | not null | |
locations | locationName | varchar(500) | null | |
locations | address | varchar(500) | null | |
locations | city | varchar(500) | null | |
locations | state | varchar(500) | null | |
locations | postalCode | varchar(50) | null | |
locations | country | varchar(200) | null | |
locations | region | varchar(200) | null | |
locations | district | varchar(200) | null | |
locations | openedDate | datetime | null | |
locations | closedDate | datetime | null | |
locations | storeStatus | varchar(50) | null | |
locations | mainContact | varchar(50) | null | |
locations | phoneNumber | varchar(22) | null | |
locations | faxNumber | varchar(22) | null | |
locations | email | varchar(200) | null | |
locations | locationType | varchar(100) | null | |
locations | parentLocationNumber | int | null | |
locations | longitude | decimal(12,8) | null | |
locations | latitude | decimal(12,8) | null | |
locations | numberOfAssignments | int | null | |
locations | timeZoneInfoId | smallint | null | |
locations | timeZoneName | varchar(35) | null | |
locations | timeZoneNameAbbreviation | varchar(6) | null | |
locations | SubscriberId | int | null | |
locations_notes | locationID | int | PK, not null | |
locations_notes | headerID | int | PK, not null | |
locations_notes | note | varchar(7000) | not null | |
locations_notes_headers | id | int | PK, not null | |
locations_notes_headers | header | varchar(500) | not null | |
locations_notes_headers | headerorder | int | not null | |
locations_notes_headers | privacyFlag | int | null | |
locations_notes_headers | SubscriberId | int | null | |
notes_statistics | id | int | PK, not null | |
notes_statistics | userName | varchar(100) | null | |
notes_statistics | noteDate | datetime | not null | |
notes_statistics | notesCreated# | int | not null | |
notes_statistics | woAffected# | int | not null | |
notes_statistics | firstNoteDateTime | datetime | not null | |
notes_statistics | lastNoteDatetime | datetime | not null | |
priorities | id | int | PK, not null | |
priorities | priority | varchar(200) | not null | |
proposal_line_items | id | int | not null | |
proposal_line_items | proposalID | int | not null | |
proposal_line_items | LineItemType | nvarchar(255) | null | |
proposal_line_items | Craft | nvarchar(255) | null | |
proposal_line_items | RepairCategory | nvarchar(255) | null | |
proposal_line_items | RepairType | nvarchar(255) | null | |
proposal_line_items | Description | nvarchar(255) | null | |
proposal_line_items | MaterialThickness | decimal(9,2) | null | |
proposal_line_items | Unit | nvarchar(20) | null | |
proposal_line_items | Quanitity | decimal(9,2) | null | |
proposal_line_items | UnitPrice | money | null | |
proposal_line_items | Total | money | null | |
proposal_status_history | id | int | PK, not null | |
proposal_status_history | proposalID | int | not null | |
proposal_status_history | trackingNumber | int | null | |
proposal_status_history | createdDate | datetime | not null | |
proposal_status_history | createdBy | int | not null | |
proposal_status_history | status | varchar(50) | not null | |
proposal_status_history | extendedStatus | varchar(50) | null | |
proposal_status_history | hierarchyLevel | varchar(200) | null | |
proposal_status_history | assignedTo | varchar(200) | null | |
proposal_status_history | comment | nvarchar(max) | null | |
proposal_status_history | assignedToUserId | int | null | |
proposal_status_history | UserAssignedEmail | varchar(100) | null | |
proposal_status_history | ApprovedDate | datetime | null | |
proposal_status_history | OnHoldDate | datetime | null | |
proposal_status_history | RejectedDate | datetime | null | |
proposal_status_history | VoidDate | datetime | null | |
proposal_status_history | ApprovedBy | varchar(50) | null | |
proposal_status_history | OnHoldBy | varchar(50) | null | |
proposal_status_history | RejectedBy | varchar(50) | null | |
proposal_status_history | VoidBy | varchar(35) | null | |
proposal_status_history | Opendate | datetime | null | |
proposal_status_history | OpenBy | varchar(50) | null | |
proposal_status_history | AssignedDate | datetime | null | |
proposal_status_history | ScheduledDate | datetime | null | |
proposal_status_history | Source | varchar(50) | null | |
proposal_statuses | id | int | not null | |
proposal_statuses | status | varchar(50) | not null | |
proposals | proposalID | int | not null | PK of the table, unique |
proposals | proposalNumber | varchar(50) | not null | Value entered by user at proposal creation |
proposals | proposalDate | datetime | not null | Date proposal was created |
proposals | locationID | int | not null | Location of the client |
proposals | provideId | int | not null | Provider on the proposal |
proposals | tradeID | int | not null | Trade on the proposal |
proposals | trackingNumber | int | null | Tracking number if available that is on the proposal |
proposals | statusID | int | not null | look up table proposal_statuses |
proposals | approvedDate | datetime | null | When proposal got approved |
proposals | approvedBy | varchar(50) | null | Who the proposal was approved by |
proposals | Description | varchar(8000) | null | Description of the proposal |
proposals | currency | varchar(50) | null | Currency of the proposal |
proposals | totalamount | money | not null | Total amount of the proposal |
proposals | laboramount | money | null | Labor breakdown |
proposals | materialamount | money | null | materials entered for the proposal |
proposals | taxamount | money | null | taxes entered on the proposal |
proposals | freightAmount | money | null | freight amount |
proposals | otheramount | money | null | Other amount entered on the proposal |
proposals | otherdescription | varchar(100) | null | Other amount described |
proposals | rfpID | int | null | If created from RFP, rfp id will not be null |
proposals | lastModifiedDate | datetime | null | last touch date, could be used for incremental load |
proposals | rejectedDate | datetime | null | proposal rejection date |
proposals | onHoldDate | datetime | null | proposal on hold date |
proposals | requestedDate | datetime | null | proposal requested date from rfp |
proposals | createdDate | datetime | null | proposal creation date |
proposals | historyID | int | null | |
proposals | approvedByUserID | int | null | Approved by User ID from users table |
provider_communications | providerID | int | not null | |
provider_communications | generalContactName | varchar(300) | null | Contact name entered in Fixxbook for the client as default/general |
provider_communications | generalContactPhone | varchar(300) | null | Contact Phone entered as default/general |
provider_communications | generalContactFax | varchar(300) | null | obsolete |
provider_communications | generalContactEmail | varchar(300) | null | Contact email entered as default/general |
provider_communications | dataExchangeEmail | varchar(300) | null | |
provider_communications | serviceContactName | varchar(300) | null | Contact Info for Service Request |
provider_communications | serviceContactPhone | varchar(300) | null | Contact Info for Service Request |
provider_communications | serviceContactFax | varchar(300) | null | obsolete |
provider_communications | serviceContactEmail | varchar(300) | null | Contact Info for Service Request |
provider_communications | serviceContactShortEmail | varchar(300) | null | obsolete |
provider_communications | proposalsContactName | varchar(300) | null | Contact Info for proposals |
provider_communications | proposalsContactPhone | varchar(300) | null | Contact Info for proposals |
provider_communications | proposalsContactFax | varchar(300) | null | Contact Info for proposals |
provider_communications | proposalsContactEmail | varchar(300) | null | Contact Info for proposals |
provider_communications | proposalsContactName | varchar(300) | null | Contact Info for proposals |
provider_communications | proposalsContactPhone | varchar(300) | null | Contact Info for proposals |
provider_communications | proposalsContactFax | varchar(300) | null | Contact Info for proposals |
provider_communications | proposalsContactEmail | varchar(300) | null | Contact Info for proposals |
provider_communications | trainingEmailAddress | varchar(300) | null | |
provider_communications | SubscriberId | int | null | |
provider_users | id | int | PK, not null | |
provider_users | providerid | int | not null | |
provider_users | firstName | nvarchar(510) | null | |
provider_users | lastName | nvarchar(510) | null | |
provider_users | email | nvarchar(510) | null | |
provider_users | phone | nvarchar(1000) | null | |
provider_users | state | nvarchar(1000) | null | |
provider_users | receiveEmailOnWoDispatch | nvarchar(1000) | null | |
provider_users | employeeID | nvarchar(1000) | null | |
provider_users | employeeTitle | nvarchar(1000) | null | |
provider_users | employeeTeamCrew | nvarchar(1000) | null | |
provider_users | createdDate | datetime | null | |
provider_users | updatedDate | datetime | null | |
providers | providerID | int | not null | |
providers | providerName | varchar(500) | not null | |
providers | vendorNumber | varchar(50) | null | |
providers | address | varchar(500) | not null | |
providers | city | varchar(500) | not null | |
providers | state | varchar(50) | null | |
providers | postalCode | varchar(50) | null | |
providers | country | varchar(200) | null | |
providers | e_mail | varchar(80) | null | |
providers | phone_number | varchar(60) | null | |
providers | fax_number | varchar(22) | null | |
providers | isActive | bit | null | |
real_estate | RealEstateId | bigint | PK, not null | |
real_estate | SubscriberId | int | not null | |
real_estate | trackingNumber | int | null | |
real_estate | FiscalYear | nvarchar(10) | null | |
real_estate | FiscalPeriod | nvarchar(10) | null | |
real_estate | FiscalWeek | nvarchar(10) | null | |
real_estate | FiscalQuarter | nvarchar(10) | null | |
real_estate | ExchangeRate | float | null | |
real_estate | ProposalAmountLocal | decimal(30,17) | null | |
real_estate | ProposalAmountExcludeChinaVAT | decimal(30,17) | null | |
real_estate | SpendAmountLocal | decimal(30,17) | null | |
real_estate | SpendAmountExcludeChinaVAT | decimal(30,17) | null | |
real_estate | InvoiceAmountExcludeChinaVAT | decimal(30,17) | null | |
real_estate | NteAmountExcludeChinaVAT | decimal(30,17) | null | |
real_estate | OriginalNTE | decimal(30,17) | null | |
real_estate | SpendType | nvarchar(15) | null | |
real_estate | TradeCode | nvarchar(15) | null | |
real_estate | ServiceType | nvarchar(100) | null | |
real_estate | ProjectTitle | nvarchar(100) | null | |
real_estate | EstimatedTimeToCompleted | int | null | |
real_estate | LocationId | int | null | |
real_estate | LocationNoteHeaderName | varchar(255) | null | |
real_estate | LocationNoteHeaderValue | varchar(255) | null | |
real_estate | District | nvarchar(50) | null | |
real_estate | Trade | varchar(50) | null | |
real_estate | Category | varchar(50) | null | |
real_estate | Priority | varchar(50) | null | |
real_estate | Currency | nvarchar(20) | null | |
real_estate | WOStatus | varchar(50) | null | |
real_estate | WOExtStatus | varchar(50) | null | |
real_estate | InvoiceStatus | varchar(20) | null | |
real_estate | ProviderId | int | null | |
real_estate | ProviderName | nvarchar(100) | null | |
real_estate | CallDate | datetime | null | |
real_estate | WorkDate | datetime | null | |
real_estate | InvoiceDate | datetime | null | |
real_estate | InvoiceApprovedDate | datetime | null | |
real_estate | SpendDate | datetime | null | |
real_estate | UpdatedDate | datetime | null | |
real_estate | SourceName | varchar(100) | not null | |
real_estate | SourceRecordId | int | not null | |
relationships | contractorID | int | PK, not null | |
relationships | locationID | int | not null | |
relationships | providerID | int | not null | |
relationships | trade | varchar(50) | not null | |
relationships | rank | int | not null | |
relationships | createdDate | datetime | null | |
relationships | updatedDate | datetime | null | |
resolution_codes | resolutionCodeID | int | not null | |
resolution_codes | trackingNumber | int | not null | |
resolution_codes | resolution | nvarchar(100) | not null | |
resolution_codes | createdDateDto | datetimeoffset(7) | not null | |
resolution_codes | assetType | nvarchar(100) | null | |
resolution_codes | primaryTrade | varchar(50) | null | |
resolutions | resID | int | not null | |
resolutions | trackingNumber | int | not null | |
resolutions | resolution | varchar(8000) | null | |
resolutions | lastModifiedDate | datetime | null | |
rfp | rfpID | int | not null | |
rfp | rfpDate | datetime | null | |
rfp | rfpBy | nvarchar(50) | null | |
rfp | locationID | int | null | |
rfp | providerID | int | null | |
rfp | status | nvarchar(50) | null | |
rfp | tradeID | int | null | |
rfp | created | datetime | null | |
rfp | Description | nvarchar(2000) | null | |
rfp | dueHrs | decimal(10,0) | null | |
rfp | dueDate | datetime | null | |
rfp | responseDate | datetime | null | |
rfp | waitingForPrpEmail | nvarchar(200) | null | |
rfp | proEmail | nvarchar(300) | null | |
rfp | rfpNumber | nvarchar(50) | null | |
rfp | lastModifiedDate | datetime | null | |
rfp | isDeleted | bit | null | |
rfp | deletedDate | datetime | null | |
root_causes | trackingNumber | int | not null | |
root_causes | rootCause | nvarchar(100) | not null | |
root_causes | assetType | nvarchar(100) | not null | |
root_causes | primaryTrade | varchar(50) | null | |
statuses | id | int | not null | |
statuses | status | varchar(200) | not null | |
statuses | extendedStatus | varchar(200) | null | |
technician | technicianId | int | PK, not null | |
technician | userid | int | null | |
technician | technicianFirstName | nvarchar(510) | null | |
technician | technicianLastName | nvarchar(510) | null | |
technician | technicianEmail | nvarchar(510) | null | |
technician | technicianPhone | nvarchar(1000) | null | |
technician | technicianState | nvarchar(1000) | null | |
technician | updatedDate | datetime | null | |
technician | createdDate | datetime | null | |
technician | loginName | nvarchar(510) | null | |
technician | receiveEmailOnWoDispatch | nvarchar(1000) | null | |
technician | EmployeeID | nvarchar(1000) | null | |
technician | EmployeeTitle | nvarchar(1000) | null | |
technician | employeeTeamCrew | nvarchar(1000) | null | |
technician_assigned_accepted | id | int | PK, not null | |
technician_assigned_accepted | trackingNumber | int | not null | |
technician_assigned_accepted | AssignedTo | varchar(100) | null | |
technician_assigned_accepted | AcceptedBy | varchar(100) | null | |
technician_assigned_accepted | Company | varchar(1000) | null | |
technician_assigned_accepted | UpdatedTechnicialDateUtc | datetime | null | |
technician_assigned_accepted | locationID | varchar(100) | null | |
technician_assigned_accepted | locationName | varchar(100) | null | |
technician_availability_log | id | int | PK, not null | |
technician_availability_log | technicianEmail | nvarchar(128) | null | |
technician_availability_log | isAvailable | nvarchar(12) | null | |
technician_availability_log | unavailableUntilDate | datetime | null | |
technician_availability_log | createdDate | datetime | null | |
technician_availability_log | technicianFirstName | nvarchar(128) | null | |
technician_availability_log | technicianLastName | nvarchar(128) | null | |
technician_availability_log | SubscriberId | int | null | |
technician_time_tracking | id | int | PK, not null | |
technician_time_tracking | technicianEmail | nvarchar(128) | null | |
technician_time_tracking | activityDescription | nvarchar(512) | null | |
technician_time_tracking | activityTypeName | nvarchar(128) | 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 | technicianFirstName | nvarchar(128) | null | |
technician_time_tracking | technicianLastName | nvarchar(128) | null | |
technician_time_tracking | providerID | int | null | |
technician_time_tracking | employeeId | nvarchar(100) | null | |
technician_time_tracking | InternalDepartment | bit | null | |
technician_time_tracking | userid | int | null | |
technician_time_tracking | startDateTime_dto | datetimeoffset(7) | null | |
technician_time_tracking | endDateTime_dto | datetimeoffset(7) | null | |
technician_time_tracking | SubscriberId | int | 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_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 | SubscriberId | int | null | |
time_zone_info | timeZoneInfoId | smallint | PK, not null | |
time_zone_info | TimeZoneName | varchar(35) | not null | |
time_zone_info | abbreviation | varchar(6) | null | |
trades | id | int | not null | |
trades | trade | varchar(200) | not null | |
users | id | int | PK, not null | |
users | providerId | int | null | |
users | firstName | nvarchar(510) | null | |
users | lastName | nvarchar(510) | null | |
users | email | nvarchar(510) | null | |
users | phone | nvarchar(1000) | null | |
users | state | nvarchar(1000) | null | |
users | receiveEmailOnWoDispatch | nvarchar(1000) | null | |
users | employeeID | nvarchar(1000) | null | |
users | employeeTitle | nvarchar(1000) | null | |
users | employeeTeamCrew | nvarchar(1000) | null | |
users | createdDate | datetime | null | |
users | updatedDate | datetime | null | |
users | name | varchar(50) | null | |
users | userId | char(50) | null | |
users | SubscriberId | int | null | |
vendors | providerID | int | not null | |
vendors | vendorNumber | varchar(50) | null | |
vendors | name | varchar(100) | null | |
vendors | address | varchar(300) | null | |
vendors | sitecode | varchar(50) | null | |
vendors | order | int | not null | |
vendors | vendorPayeeID | int | null | |
wo_amount_breakdown | id | int | PK, not null | |
wo_amount_breakdown | woAmountId | int | not null | |
wo_amount_breakdown | trackingNumber | int | not null | |
wo_amount_breakdown | subtotal | money | not null | |
wo_amount_breakdown | tax | money | not null | |
wo_amount_breakdown | tax2type | varchar(50) | null | |
wo_amount_breakdown | tax2 | money | null | |
wo_amount_breakdown | currency | varchar(50) | null | |
wo_amount_breakdown | autoinvoice | varchar(50) | not null | |
wo_amount_breakdown | autoComplete | varchar(10) | not null | |
wo_amount_breakdown | autoApprovedBY | varchar(50) | null | |
wo_amount_breakdown | lastModifiedDate | datetime | null | |
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 | |
wo_history_amount_change | id | id | not null | |
wo_history_amount_change | amountChangeId | int | not null | |
wo_history_amount_change | trackingNumber | int | not null | |
wo_history_amount_change | subtotal | money | not null | |
wo_history_amount_change | tax | money | not null | |
wo_history_amount_change | tax2type | varchar(50) | null | |
wo_history_amount_change | tax2 | money | null | |
wo_history_amount_change | currency | varchar(10) | not null | |
wo_history_amount_change | autocomplete | varchar(10) | not null | |
wo_history_amount_change | autoinvoice | varchar(10) | not null | |
wo_history_amount_change | datecreated | datetime | not null | |
wo_history_change_category | id | int | PK, not null | |
wo_history_change_category | TrackingNumber | int | not null | |
wo_history_change_category | OriginalCategory | varchar(50) | not null | |
wo_history_change_category | NewCategory | varchar(50) | not null | |
wo_history_change_category | CreatedDate | datetime | not null | |
wo_history_change_nte | id | int | not null | |
wo_history_change_nte | nteChangeId | int | not null | |
wo_history_change_nte | trackingNumber | int | not null | |
wo_history_change_nte | sourceNte | money | not null | |
wo_history_change_nte | sourceCurrencyID | int | not null | |
wo_history_change_nte | targetNte | money | not null | |
wo_history_change_nte | targetCurrencyID | int | not null | |
wo_history_change_nte | createdDate | date | not null | |
wo_history_change_priority | id | int | PK, not null | |
wo_history_change_priority | TrackingNumber | int | not null | |
wo_history_change_priority | OldPrioritySub | varchar(50) | null | |
wo_history_change_priority | NewPrioritySub | varchar(50) | null | |
wo_history_change_priority | Updatedby | varchar(50) | null | |
wo_history_change_priority | UpdatedDate | datetime | not null | |
wo_history_change_problem | id | int | PK, not null | |
wo_history_change_problem | TrackingNumber | int | not null | |
wo_history_change_problem | OldAreaId | int | null | |
wo_history_change_problem | OldAreaName | nvarchar(510) | null | |
wo_history_change_problem | OldProblemType | nvarchar(256) | null | |
wo_history_change_problem | OldEquipment | nvarchar(256) | null | |
wo_history_change_problem | OldProblem | nvarchar(256) | null | |
wo_history_change_problem | NewAreaId | int | null | |
wo_history_change_problem | NewAreaName | nvarchar(510) | null | |
wo_history_change_problem | NewProblemType | nvarchar(256) | null | |
wo_history_change_problem | NewEquipment | nvarchar(256) | null | |
wo_history_change_problem | NewProblem | nvarchar(256) | null | |
wo_history_change_problem | UpdatedDate | datetime | not null | |
wo_history_change_provider | id | int | PK, not null | |
wo_history_change_provider | TrackingNumber | int | not null | |
wo_history_change_provider | OriginalProviderID | int | not null | |
wo_history_change_provider | NewProviderID | int | not null | |
wo_history_change_provider | CreatedByUserID | int | null | |
wo_history_change_provider | CreatedDate | datetime | not null | |
wo_history_change_provider | CreatedDate_dto | datetimeoffset(7) | null | |
wo_history_change_scheduled_date | id | int | not null | |
wo_history_change_scheduled_date | schdateChangeId | int | not null | |
wo_history_change_scheduled_date | trackingNumber | int | not null | |
wo_history_change_scheduled_date | oldScheduledDate | datetime | null | |
wo_history_change_scheduled_date | newScheduledDate | datetime | null | |
wo_history_change_scheduled_date | updatedby | varchar(50) | null | |
wo_history_change_scheduled_date | updatedDate | datetime | not null | |
wo_history_change_status | id | int | not null | |
wo_history_change_status | statusChangeId | int | not null | |
wo_history_change_status | trackingNumber | int | not null | |
wo_history_change_status | oldStatus | varchar(50) | not null | |
wo_history_change_status | oldExtendedStatus | varchar(50) | null | |
wo_history_change_status | newStatus | varchar(50) | not null | |
wo_history_change_status | newExtendedStatus | varchar(50) | null | |
wo_history_change_status | updatedBy | varchar(50) | null | |
wo_history_change_status | updatedDate | datetime | not null | |
wo_history_change_trade | id | int | PK, not null | |
wo_history_change_trade | TrackingNumber | int | not null | |
wo_history_change_trade | OldContractID | int | not null | |
wo_history_change_trade | NewContractID | int | not null | |
wo_history_change_trade | UpdatedBy | varchar(50) | null | |
wo_history_change_trade | UpdatedDate | datetime | not null | |
wo_history_change_trade | OldTrade | varchar(50) | null | |
wo_history_change_trade | NewTrade | varchar(50) | null | |
workorder_ivr | id | int | not null | |
workorder_ivr | ivrID | int | not null | |
workorder_ivr | trackingNumber | int | not null | |
workorder_ivr | ivrAction | int | not null | |
workorder_ivr | ivrTime | datetime | not null | |
workorder_ivr | lastModifiedDate | datetime | null | |
workorder_ivr | checkInSource | varchar(10) | null | |
workorder_mechanic | id | int | not null | |
workorder_mechanic | ivrID | int | not null | |
workorder_mechanic | trackingNumber | int | not null | |
workorder_mechanic | ivrAction | int | not null | |
workorder_mechanic | ivrTime | datetime | not null | |
workorder_mechanic | lastModifiedDate | datetime | null | |
workorder_notes | id | int | not null | |
workorder_notes | NoteNum | int | not null | |
workorder_notes | Note | nvarchar(4000) | null | |
workorder_notes | CreatedBySource | nvarchar(100) | null | |
workorder_notes | TrackingNumber | int | not null | |
workorder_notes | ProviderID | int | null | |
workorder_notes | CreatedDate | datetime | null | |
workorder_notes | Date_Created_dto | datetimeoffset(7) | null | |
workorder_quality_survey | id | int | PK, not null | |
workorder_quality_survey | trackingNumber | int | not null | |
workorder_quality_survey | respondent | varchar(100) | not null | |
workorder_quality_survey | createdby | varchar(50) | not null | |
workorder_quality_survey | comment | varchar(4000) | null | |
workorder_quality_survey | answer | varchar(100) | not null | |
workorder_quality_survey | createdDate | datetime | null | |
workorders | trackingNumber | int | not null | Primary key, unique and is frequently used to join other tables. |
workorders | calldate | datetime | null | When the work order was created |
workorders | completiondate | datetime | null | When the work was completed |
workorders | locationID | int | not null | Location of the workorder, primary key from locations table |
workorders | providerID | int | not null | Provider that is assigned to the work order (see table providers) |
workorders | tradeID | int | not null | Trade of the work order (see table trades for mapping) |
workorders | categoryID | int | not null | Category of the work order (see table categories for mapping) |
workorders | priorityID | int | not null | Priority of the work order (see table priorities) |
workorders | statusID | int | not null | Status of the work order (see table statuses) |
workorders | scheduledDate | datetime | null | Schedule date of the work order |
workorders | woNumber | varchar(50) | null | WO#, originally equal to Tracking number but provider can change |
workorders | reassigneddate | datetime | null | Reassignment date, if work order was reassigned |
workorders | createdby | varchar(50) | null | User Name used to create a work order |
workorders | caller | varchar(100) | null | Call Name typed to create a work order |
workorders | assignees | varchar(50) | null | User Name assigned to this work order (rarely used) |
workorders | assignedDate | datetime | null | When user was assigned (rarely used) |
workorders | lastModifiedDate | datetime | null | When the work order was last modified (can be used for incremental) |
workorders | equipmentTagID | nvarchar(50) | null | EquipmentTagID associated with this work order |
workorders | recallTrackingNumber | int | null | Recall tracking number if recalled |
workorders | callerIDMatch | bit | null | When check in was done from the store |
workorders | source | varchar(20) | null | How this work order was generated |
workorders | resolutionCode | varchar(100) | null | Resolution Code (retired) |
workorders | failingComponent | varchar(100) | null | Failing Component (retired) |
workorders | checkInSource | varchar(20) | null | How check in was completed |
workorders | poNumber | varchar(50) | null | PO number or Purchase number, subscriber could change from original tracking number value |
workorders | createdDate | datetime | null | Work order creation date (different from call date that call date can be set) |
workorders | ProjectedCompletionDate | datetimeoffset(7) | null | (retired) |
workorders | IVR | bit | null | A flag that indicates this WO has both a checkin and a checkout and ends with a checkout |
workorders | SLA | bit | null | A flag to indicate whether this work order met the original ETA - i.e. that the first check in for the work order was before the original scheduled date |
workorders | WORecall | nvarchar(3) | null | If work order was recalled |
workorders | HoursOnSite | real | null | Sum of check ins/out |
workorders | FirstTimeCompletionFlag | bit | null | First Time completion Flag - 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 | createdByUserID | int | null | Created by UserID (see table users) |
workorders | glCode | nvarchar(120) | null | Gl Code (Approval code) assigned to a work order or invoice |
workorders_ext | trackingNumber | int | not null | |
workorders_ext | expirationDate | datetime | null | |
workorders_ext | originalExpirationDate | datetime | null | |
workorders_ext | area | varchar(256) | null | |
workorders_ext | problemType | varchar(256) | null | |
workorders_ext | equipment | varchar(256) | null | |
workorders_ext | problemCode | varchar(256) | null | |
workorders_ext | currency | varchar(50) | null | Workorder Currency |
workorders_ext | nteAmount | money | null | |
workorders_ext | problemDescription | varchar(8000) | null | |
workorders_ext | postedDate | datetime | null | |
workorders_ext | poLoadFlag | int | null | |
workorders_ext | poLoadFlagDate | datetime | null | |
workorders_ext | lastModifiedDate | datetime | null | |
workorders_linked | id | int | PK, not null | |
workorders_linked | parent_WO | int | not null | |
workorders_linked | child_WO | int | not null | |
