Appearance
dbo.primeJobs
Info Generated by
scripts/Generate-DataDictionary.ps1- do not edit by hand. Edit descriptions inschemas/tables/dbo.primeJobs.jsonand regenerate.
Columns
| # | Column | Type | Nullable | Flags | Description |
|---|---|---|---|---|---|
| 1 | Id | uniqueidentifier | no | Primary key / unique identifier for this record | |
| 2 | OngoingJob | bit | no | Flag indicating this is an ongoing (non-single-visit) job | |
| 3 | JobNumber | varchar(50) | yes | Human-readable job number/reference used in Prime Ecosystem | |
| 4 | ClientReference | nvarchar(50) | yes | Reference number/code supplied by the client (e.g. insurer claim number) | |
| 5 | AdditionalReference | nvarchar(50) | yes | Secondary/additional reference number supplied by the client | |
| 6 | Description | nvarchar(MAX) | yes | Free-text description | |
| 7 | DivisionId | uniqueidentifier | yes | Foreign key to the business division that owns this record | |
| 8 | PerilId | uniqueidentifier | yes | Foreign key to primePerils - the insurance peril (cause of loss) for this job | |
| 9 | CatastropheCodeId | uniqueidentifier | yes | Foreign key to primeCatastropheCodes - the catastrophe event this job is linked to, if any | |
| 10 | JobType | nvarchar(50) | yes | Type/category of job (e.g. Insurance, Commercial, Make Safe) | |
| 11 | WorkflowId | uniqueidentifier | yes | Foreign key identifying the workflow this job/record follows | |
| 12 | StatusId | uniqueidentifier | yes | Foreign key to primeStatuses - current status of this record | |
| 13 | AddressLine1 | nvarchar(255) | yes | First line of the site/property street address | |
| 14 | Suburb | nvarchar(100) | yes | Suburb of the site/property address | |
| 15 | State | nvarchar(10) | yes | State/territory of the site/property address | |
| 16 | Country | nvarchar(100) | yes | Country of the address | |
| 17 | Postcode | nvarchar(20) | yes | Postcode of the address | |
| 18 | CustomerId | uniqueidentifier | yes | Foreign key to primeContacts - the customer/policyholder for this job | |
| 19 | ClientId | uniqueidentifier | yes | Foreign key to primeContacts - the client (e.g. insurer) for this job | |
| 20 | AssignedId | uniqueidentifier | yes | Foreign key to primeUsers - the user this record is assigned to | |
| 21 | ExcessAmount | decimal(18,2) | yes | Insurance excess amount payable by the customer | |
| 22 | CaseManagerId | uniqueidentifier | yes | Foreign key to primeUsers - the case manager responsible for this job | |
| 23 | SupervisorId | uniqueidentifier | yes | Foreign key to primeUsers - the supervisor responsible for this job | |
| 24 | EstimatorId | uniqueidentifier | yes | Foreign key to primeUsers - the estimator responsible for this job | |
| 25 | PrimaryContactId | uniqueidentifier | yes | Foreign key to primeContacts - the primary contact for this job | |
| 26 | BusinessId | uniqueidentifier | yes | Foreign key to the business entity/branch this record belongs to | |
| 27 | ClientPortalId | uniqueidentifier | yes | Identifier used to expose this record in the client portal | |
| 28 | Notes | nvarchar(MAX) | yes | Free-text internal notes | |
| 29 | AuthorisedTotalIncludingTax | money | yes | Total authorised (approved) job value including tax | |
| 30 | AuthorisedTotalExcludingTax | money | yes | Total authorised (approved) job value excluding tax | |
| 31 | AuthorisedTotalExcludingTaxExcludingMargin | money | yes | Total authorised job value excluding tax and margin | |
| 32 | AuthorisedTotalExcludingTaxExcludingMarginExcludingMarkup | money | yes | Total authorised job value excluding tax, margin and markup | |
| 33 | FilterTags | nvarchar(500) | yes | Comma-separated display list of filter tags applied to this record | |
| 34 | FilterTagIds | nvarchar(500) | yes | Comma-separated list of primeFilterTags identifiers applied to this record | |
| 35 | Region | nvarchar(100) | yes | Geographic region this record belongs to | |
| 36 | IncidentDate | datetime | yes | Date the insured incident/loss occurred | |
| 37 | AllocatedDate | datetime | yes | Date the job was allocated | |
| 38 | BrokerReference | nvarchar(100) | yes | Reference number supplied by the insurance broker | |
| 39 | StrataReference | nvarchar(100) | yes | Reference number for the strata/body corporate managing the property | |
| 40 | AdjusterReference | nvarchar(100) | yes | Reference number supplied by the loss adjuster | |
| 41 | StartDate | datetime | yes | Start date of this record/job | |
| 42 | EndDate | datetime | yes | End date of this record/job | |
| 43 | PrimeUrl | nvarchar(255) | yes | Deep link URL to this record in Prime Ecosystem | |
| 44 | CreatedAt | datetime | yes | Timestamp the record was created | |
| 45 | CreatedBy | nvarchar(100) | yes | User/system that created the record | |
| 46 | UpdatedAt | datetime | yes | Timestamp the record was last updated | |
| 47 | UpdatedBy | nvarchar(100) | yes | User/system that last updated the record | |
| 48 | Version | bigint | yes | Optimistic-concurrency row version number | |
| 49 | InitialStartDate | datetime | yes | Originally scheduled/planned start date, before any rescheduling | |
| 50 | InitialEndDate | datetime | yes | Originally scheduled/planned end date, before any rescheduling |
Indexes
| Name | Type | Unique | PK | Key columns | Included | Filter |
|---|---|---|---|---|---|---|
| PK__primeJob__056690C20762C2B9 | CLUSTERED | yes | yes | Id | ||
| IX_primeJobs_JobNumber_State | NONCLUSTERED | JobNumber | State | |||
| IX_primeJobs_jobNumber | NONCLUSTERED | JobNumber | AssignedId, AuthorisedTotalIncludingTax, EstimatorId, Id, PrimeUrl, State, SupervisorId | |||
| IX_primeJobs_supervisor | NONCLUSTERED | SupervisorId | AssignedId, AuthorisedTotalIncludingTax, EstimatorId, Id, JobNumber, State | |||
| IX_primeJobs_State | NONCLUSTERED | State | Id, JobNumber, SupervisorId | |||
| IX_primeJobs_Estimator | NONCLUSTERED | EstimatorId | Id | |||
| IX_primeJobs_Assigned | NONCLUSTERED | AssignedId | Id | |||
| IX_primeJobs_AuthTotal | NONCLUSTERED | AuthorisedTotalIncludingTax | Id | |||
| IX_primeJobs_clientreference | NONCLUSTERED | ClientReference | ||||
| nci_msft_1_primeJobs_C716A663EDA051FA3CF7219DB322CAE0 | NONCLUSTERED | State, JobNumber | AuthorisedTotalExcludingTax, ClientId, EstimatorId, StatusId, SupervisorId |
Relationships (inferred)
Referenced by
Views: dbo.vEthKpiResults_Deprecated, dbo.vEthKpiResultsActive_Deprecated, dbo.vEthKpiResultsClosed_Deprecated, dbo.vMakeSafePhonecalls, dbo.vMakeSafePhonecallsNoAlerts, dbo.VPrimeAllestimatevalueObjectivedb, dbo.VPrimeCompletiontimesObjectivedb, dbo.vprimeEstimatorQuoteConversions, dbo.vprimeFinancialschedule_1_jobandinvoicedetails, dbo.vprimeFinancialschedule_1point2_jobdetails, dbo.vprimeFinancialschedule_2_estimates, dbo.vprimeFinancialschedule_3_work_orders, dbo.vprimefirstquote, dbo.VPrimeJobcategoriesObjectivedb, dbo.vPrimeJobinvoicesObjectivedb, dbo.vprimejobsthismonthObjectivedb, dbo.vPrimeRawEstimates, dbo.vPrimeSalesinvoicesObjectivedb, dbo.VPrimeSalesPipelineObjectivedb, dbo.VPrimeSwmshrdatesconstructiondbHighriskjobs, dbo.VPrimeTimetobuildObjectivedb, dbo.vWIPSkippedJobReasons, dbo.vWIPUpdateDrilldownByWeek, dbo.vwWIPSkippedJobReasons
Procedures: dbo.spAddMissingJobsToKpiQueue, dbo.spElevenLabsGetConversationInitialization, dbo.spEndataEstimateUploads, dbo.spEthAIGetAllPrimeJobSummaries, dbo.spEthAIGetPrimeJobSummariesPaged, dbo.spEthAIPrimeGetJobHash, dbo.spEthAIPrimeGetJobSummaryQueue_Depcrecated, dbo.spEthAIPrimeJobSummaryQueueUpdateApptTask, dbo.spEthAIPrimeUpsertJobSummaryQueue, dbo.spEthBookingClear, dbo.spEthBookingClear_Deprecated, dbo.spEthBookingCreate, dbo.spEthBookingEstimatorJobDistanceQueue, dbo.spEthBookingGet, dbo.spEthBookingGetAvailability, dbo.spEthBookingGetAvailabilityByEstimator, dbo.spEthBookingGetAvailabilityDetails, dbo.spEthBookingGetAvailabilityGeo, dbo.spEthBookingGetByCode, dbo.spEthBookingGetByID, dbo.spEthBookingGetByJobID, dbo.spEthBookingGetOnBook, dbo.spEthBookingGetOnCancel, dbo.spEthBookingGetOnCreate, dbo.spEthBookingGetSMSContactDetails, dbo.spEthBookingJobJobDistanceQueue, dbo.spEthBookingParseandCreate, dbo.spEthBookingSetDate, dbo.spEthBookingSetDate_Depcrecated, dbo.spEthBookingSMSOnBook, dbo.spEthBookingSMSOnCancel, dbo.spEthBookingSMSOnCreate, dbo.spEthBookingUpsert, dbo.spEthGetAllianzOrChuJobs, dbo.spEthGetEstimateReview, dbo.spEthGetQueuedIDs, dbo.spEthKpiResultsActive, dbo.spEthKpiResultsActiveAll, dbo.spEthKpiResultsbyJobId, dbo.spEthKpiTagJobs, dbo.spEthNexusECDQueueTasks, dbo.spEthPopulatePeerReviewStatus, dbo.spEthQueueAccountsPayableInvoices, dbo.spEthQueueMissingJobs, dbo.spEthQueueMissingKpis, dbo.spEthQueueMissingStatusHistoriesJob, dbo.spEthRepairlineJobDatesQueueTasks, dbo.spEthResolveAppointmentContext, dbo.spEthSedgwickJobDatesqueueTasks, dbo.spEthSyncQueueMissingObjects, dbo.spEthSyncQueueMissingObjectsByJobID, dbo.spEthTaskInsertbyJobNumber, dbo.spEthWIPCaptureJobWeeklySnapshot, dbo.spEthWIPJobClientNoteQueue, dbo.spMakesafeGetAllMessages, dbo.spMakesafeGetGeoLocationPhoneCalls, dbo.spMakesafeGetMessagesPaged, dbo.spMakesafeGetPhoneCallbyID, dbo.spMakesafeGetPhoneCallStatusbyID, dbo.spMakeSafePhoneCallRelatedPrimeJobs, dbo.spMakesafeUpsertPhoneCallFromPrimeJob, dbo.spMCPGetPrimeJob, dbo.spMCPSearchTrades, dbo.spOptimoRouteGetAllowAdvancedBookings, dbo.spOptimoRouteGetAllowSpecificTimeBookings, dbo.spOptimoRouteGetAppointmentCompletionQueue, dbo.spOptimoRouteGetAppointmentQueue, dbo.spOptimoRouteGetAppointmentScheduledEstimatorQueue, dbo.spOptimoRouteGetCountOrderFixedTime_Deprecated, dbo.spOptimoRouteGetLogs, dbo.spOptimoRouteGetLogsPaged, dbo.spOptimoRouteGetNearbyJobs, dbo.spOptimoRouteGetNotScheduledOrders, dbo.spOptimoRouteGetNotScheduledOrdersEmailQueue, dbo.spOptimoRouteGetScheduledOrders, dbo.spOptimoRouteGetWorkOrderQueue, dbo.spOptimoRouteOrderGetActiveByJob, dbo.spOptimoRouteOrderGetRefreshQueue, dbo.spOptimoRouteUpdateOrderTravelMins, dbo.spOptimoRouteUpsertStop, dbo.spPopulateInvoiceCheck, dbo.spPrimeBuildLockedEstimatesThisYear, dbo.spPrimeGetAllocationTypesByPhoneCallID, dbo.spPrimeGetECDUpdate, dbo.spPrimeGetJobContactRelationships, dbo.spPrimeGetJobDates, dbo.spPrimeGetJobIDbyJobNumber, dbo.spPrimeGetJobInformation, dbo.spPrimeGetJobLocation, dbo.spPrimeGetJobsLocationQueue, dbo.spPrimeGetJobsLocationTaskQueue, dbo.spPrimeGetJobSummary, dbo.spPrimeGetJobswithoutHistory, dbo.spPrimeGetJobVersion, dbo.spPrimeGetSupervisors, dbo.spPrimeGetWIPUpdatesApproved, dbo.spPrimeGetWIPUpdatesClients, dbo.spPrimeGetWIPUpdatesDateBoundaries, dbo.spPrimeGetWIPUpdatesQueue, dbo.spPrimeGetWIPUpdatesWorksCompleted, dbo.spPrimeGetWIPUpdatesWorksInProgress, dbo.spPrimeGetWIPUpdatesWorksUpcoming, dbo.spPrimeJobSearch, dbo.spPrimePeerReviewEstimatedetails, dbo.spPrimePeerReviewformdetails, dbo.spPrimeReprocessJobsLocation, dbo.spPrimeUpsertJob, dbo.spPrimeUpsertJobsLocation, dbo.spPrimeUpsertStatusHistory_Job, dbo.spRefreshEstimatorStageSummary