Stored Procedures
In combination with the import service, stored procedures can be modified to implement business logic. In the database, stored procedures with the prefix "mboc_" can be invoked by the import service.
Anatomy of an import stored procedure
In essence, the whole exercise of connecting Dime.Scheduler to a different system is to transfer data from point A to point B. To put it in the words of Joey Tribbiani, the processor guy, it's mostly a matter of "putting numbers from one column into another column".
Let's have a look at a simple one, the pins. The data model of a pin couldn't be any simpler: it contains an id, name and a color. This is reflected in its stored procedure.
The parameters contain the data that should be stored in the database. At this point, you may be wondering why there is no "Id" (or any PK) in there. Recall that data may originate from other systems, which makes identifiers utterly redundant. The best next thing would be to map data using the "Name" field. The function fGetPinId does actually that, querying the Pins table for any record that contains the name that was specified in the @Name parameter. If the record exists, that particular will be updated. If it doesn't, a new item will be created. Hence the name of the stored procedure: "upsert".
CREATE PROCEDURE [dbo].[mboc_upsertPin]
@Name nvarchar(100),
@HexColor nvarchar(50) = '',
@ColorR int = NULL,
@ColorG int = NULL,
@ColorB int = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PinId int
IF @Name = '' BEGIN
RAISERROR(N'Parameter "Name" cannot be empty when creating pins.',16, 0)
RETURN
END
SET @PinId = dbo.fGetPinId(@Name)
IF @ColorR IS NOT NULL AND @ColorG IS NOT NULL AND @ColorB IS NOT NULL
SET @HexColor = '#' + CONVERT(nvarchar(10), CONVERT(VARBINARY(1), @ColorR) + CONVERT(VARBINARY(1), @ColorG) + CONVERT(VARBINARY(1), @ColorB), 2)
IF @PinId IS NOT NULL
IF @HexColor = ''
SELECT @HexColor = Color
FROM [Pins]
WHERE Id = @PinId
IF @PinId IS NULL
INSERT INTO [Pins] ([Name],[Color])
VALUES (@Name,@HexColor)
ELSE
UPDATE [Pins]
SET [Name] = @Name,
[Color] = @HexColor
WHERE [Id] = @PinId
END
CREATE FUNCTION [dbo].[fGetPinId]
(
@PinName nvarchar(100)
)
RETURNS int
AS
BEGIN
IF COALESCE(@PinName,'') = ''
RETURN NULL
RETURN(SELECT TOP 1 [Id] FROM [Pins] WHERE [Name] = @PinName)
END
That's all there is to it. Clearly, there are more complicated sprocs like the ones concerned with jobs and tasks but they basically do the same thing, which is to connect other systems' data with Dime.Scheduler.
Import stored procedures list
The names of the stored procedures and their purpose are fairly self-explanatory. For example, it doesn't take a rocket scientist to figure out that mboc_deleteAppointment is responsible for deleting appointments from the database.
Nonetheless, for the sake of being complete, the table below enlists all the stored procedures which can be invoked by the import service. Of course, feel free to peruse the stored procedures in the database.
ℹ️ You will notice we adopted the acronym "upsert", which refers to a create or update operation, depending on the existence of a particular record in the database.
Jobs
Upsert jobs
Inserts or updates a job record. A job in Dime.Scheduler is the overlying entity that groups tasks. This can be a service order header with multiple lines, a project with several tasks, a cargo transport with multiple containers, etc. A job record is mandatory for a task, you cannot create a task if the corresponding job record does not exist.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ✔️ | |
| JobNo | nvarchar(50) | ✔️ | |
| ShortDescription | nvarchar(50) | ✔️ | |
| Description | nvarchar(MAX) | ||
| Type | nvarchar(50) | ||
| Name | nvarchar(255) | ||
| Category | nvarchar(100) | ||
| TimeMarker | nvarchar(100) | ||
| Pin | nvarchar(100) | ||
| CustomerNo | nvarchar(20) | ||
| CustomerName | nvarchar(50) | ||
| CustomerAddress | nvarchar(MAX) | ||
| CustomerAddressGeoLong | nvarchar(50) | ||
| CustomerAddressGeoLat | nvarchar(50) | ||
| CustomerPhone | nvarchar(50) | ||
| CustomerEmail | nvarchar(50) | ||
| ContactNo | nvarchar(20) | ||
| ContactName | nvarchar(50) | ||
| ContactAddress | nvarchar(MAX) | ||
| ContactAddressGeoLong | nvarchar(50) | '' | |
| ContactAddressGeoLat | nvarchar(50) | '' | |
| ContactPhone | nvarchar(50) | ||
| ContactEmail | nvarchar(50) | ||
| SiteNo | nvarchar(20) | ||
| SiteName | nvarchar(50) | ||
| SiteAddress | nvarchar(MAX) | ||
| SiteAddressGeoLong | nvarchar(50) | '' | |
| SiteAddressGeoLat | nvarchar(50) | '' | |
| SitePhone | nvarchar(50) | ||
| SiteEmail | nvarchar(50) | ||
| SiteRegion | nvarchar(10) | ||
| SiteStreet | nvarchar(255) | ||
| SiteStreetNo | nvarchar(20) | ||
| SitePostcode | nvarchar(20) | ||
| SiteCity | nvarchar(50) | ||
| SiteCounty | nvarchar(50) | ||
| SiteState | nvarchar(50) | ||
| SiteCountry | nvarchar(50) | ||
| SiteFromNo | nvarchar(20) | ||
| SiteFromName | nvarchar(50) | ||
| SiteFromAddress | nvarchar(MAX) | ||
| SiteFromAddressGeoLong | nvarchar(50) | '' | |
| SiteFromAddressGeoLat | nvarchar(50) | '' | |
| SiteFromPhone | nvarchar(50) | ||
| SiteFromEmail | nvarchar(50) | ||
| SiteFromRegion | nvarchar(10) | ||
| SiteFromStreet | nvarchar(255) | ||
| SiteFromStreetNo | nvarchar(20) | ||
| SiteFromPostcode | nvarchar(20) | ||
| SiteFromCity | nvarchar(50) | ||
| SiteFromCounty | nvarchar(50) | ||
| SiteFromState | nvarchar(50) | ||
| SiteFromCountry | nvarchar(50) | ||
| BillNo | nvarchar(20) | ||
| BillName | nvarchar(50) | ||
| BillAddress | nvarchar(MAX) | ||
| BillAddressGeoLong | nvarchar(50) | '' | |
| BillAddressGeoLat | nvarchar(50) | '' | |
| BillPhone | nvarchar(50) | ||
| BillEmail | nvarchar(50) | ||
| BillRegion | nvarchar(10) | ||
| Importance | int | 0 | |
| CreationDateTime | datetime | ||
| CustomerReference | nvarchar(50) | ||
| Language | nvarchar(10) | ||
| Responsible | nvarchar(50) | ||
| Creator | nvarchar(50) | ||
| FreeText1 | nvarchar(100) | ||
| FreeText2 | nvarchar(100) | ||
| FreeText3 | nvarchar(100) | ||
| FreeText4 | nvarchar(100) | ||
| FreeText5 | nvarchar(100) | ||
| FreeText6 | nvarchar(100) | ||
| FreeText7 | nvarchar(100) | ||
| FreeText8 | nvarchar(100) | ||
| FreeText9 | nvarchar(100) | ||
| FreeText10 | nvarchar(100) | ||
| FreeText11 | nvarchar(100) | ||
| FreeText12 | nvarchar(100) | ||
| FreeText13 | nvarchar(100) | ||
| FreeText14 | nvarchar(100) | ||
| FreeText15 | nvarchar(100) | ||
| FreeText16 | nvarchar(100) | ||
| FreeText17 | nvarchar(100) | ||
| FreeText18 | nvarchar(100) | ||
| FreeText19 | nvarchar(100) | ||
| FreeText20 | nvarchar(100) | ||
| FreeDecimal1 | decimal(18, 6) | ||
| FreeDecimal2 | decimal(18, 6) | ||
| FreeDecimal3 | decimal(18, 6) | ||
| FreeDecimal4 | decimal(18, 6) | ||
| FreeDecimal5 | decimal(18, 6) | ||
| FreeDate1 | datetime | ||
| FreeDate2 | datetime | ||
| FreeDate3 | datetime | ||
| FreeDate4 | datetime | ||
| FreeDate5 | datetime | ||
| FreeBit1 | bit | 0 | |
| FreeBit2 | bit | 0 | |
| FreeBit3 | bit | 0 | |
| FreeBit4 | bit | 0 | |
| FreeBit5 | bit | 0 | |
| EnableManualSelection | bit | 0 | |
| AvailableInGantt | bit | 0 | |
| StartDate | datetime | ||
| EndDate | datetime | ||
| AllowDependencies | bit | 1 | |
| Note | nvarchar(max) | ||
| OverRuleGanttPlanning | bit | 0 |
Delete job
Deletes a job record. The behavior of this procedure depends on the CheckAppointments flag:
- When
CheckAppointmentsis true: the job is not deleted when there are appointments for any task attached to the job. - When
CheckAppointmentsis false: the job is deleted without further checks. When a job is deleted, all tasks and appointments attached to the job, if any, are also deleted.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ✔️ | |
| JobNo | nvarchar(50) | ✔️ | |
| CheckAppointments | bit | ||
| SentFromBackoffice | bit | 1 |
Tasks
Upsert task
Inserts or updates a task record. A task is a child entity of a job, and a job is required before any task can be added. A task record for which no appointments exist is considered as an open task, and will therefore be displayed in the open tasks component.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ✔️ | |
| JobNo | nvarchar(50) | ✔️ | |
| TaskNo | nvarchar(50) | ✔️ | |
| TaskType | int | 0 | |
| ShortDescription | nvarchar(50) | ||
| Description | nvarchar(MAX) | ||
| Name | nvarchar(255) | ||
| Type | nvarchar(20) | ||
| Category | nvarchar(100) | ||
| TimeMarker | nvarchar(100) | ||
| Pin | nvarchar(100) | ||
| ServiceNo | nvarchar(50) | ||
| ServiceGroup | nvarchar(20) | ||
| ServiceClass | nvarchar(20) | ||
| ServiceSerialNo | nvarchar(50) | ||
| ServiceName | nvarchar(50) | ||
| IRISFault | nvarchar(10) | ||
| IRISSymptom | nvarchar(10) | ||
| IRISArea | nvarchar(10) | ||
| IRISReason | nvarchar(10) | ||
| IRISResolution | nvarchar(10) | ||
| Skill1 | nvarchar(10) | ||
| Skill2 | nvarchar(10) | ||
| Skill3 | nvarchar(10) | ||
| ContractNo | nvarchar(20) | ||
| ContractType | nvarchar(30) | ||
| ContractDescription | nvarchar(MAX) | ||
| ContractStartDate | date | ||
| ContractEndDate | date | ||
| PartsWarrantyStartDate | date | ||
| PartsWarrantyEndDate | date | ||
| LaborWarrantyStartDate | date | ||
| LaborWarrantyEndDate | date | ||
| Importance | int | 0 | |
| Status | nvarchar(20) | ||
| ExpectedResponseDateTime | datetime | ||
| ActualResponseDateTime | datetime | ||
| RequestedStartDate | datetime | ||
| RequestedEndDate | datetime | ||
| ConfirmedStartDate | datetime | ||
| ConfirmedEndDate | datetime | ||
| ActualStartDate | datetime | ||
| ActualEndDate | datetime | ||
| LocationDescription | nvarchar(MAX) | ||
| Duration | time(7) | ||
| DurationInSeconds | bigint | ||
| Subject | nvarchar(max) | ||
| Body | nvarchar(max) | ||
| InfiniteTask | bit | 0 | |
| TaskOpenAsOf | datetime | ||
| TaskOpenTill | datetime | ||
| RequiredTotalDuration | time(7) | ||
| RequiredNoResources | int | ||
| AppointmentEarliestAllowed | datetime | ||
| AppointmentLatestAllowed | datetime | ||
| FreeText1 | nvarchar(100) | ||
| FreeText2 | nvarchar(100) | ||
| FreeText3 | nvarchar(100) | ||
| FreeText4 | nvarchar(100) | ||
| FreeText5 | nvarchar(100) | ||
| FreeText6 | nvarchar(100) | ||
| FreeText7 | nvarchar(100) | ||
| FreeText8 | nvarchar(100) | ||
| FreeText9 | nvarchar(100) | ||
| FreeText10 | nvarchar(100) | ||
| FreeText11 | nvarchar(100) | ||
| FreeText12 | nvarchar(100) | ||
| FreeText13 | nvarchar(100) | ||
| FreeText14 | nvarchar(100) | ||
| FreeText15 | nvarchar(100) | ||
| FreeText16 | nvarchar(100) | ||
| FreeText17 | nvarchar(100) | ||
| FreeText18 | nvarchar(100) | ||
| FreeText19 | nvarchar(100) | ||
| FreeText20 | nvarchar(100) | ||
| FreeDecimal1 | decimal(18, 6) | ||
| FreeDecimal2 | decimal(18, 6) | ||
| FreeDecimal3 | decimal(18, 6) | ||
| FreeDecimal4 | decimal(18, 6) | ||
| FreeDecimal5 | decimal(18, 6) | ||
| FreeDate1 | datetime | ||
| FreeDate2 | datetime | ||
| FreeDate3 | datetime | ||
| FreeDate4 | datetime | ||
| FreeDate5 | datetime | ||
| FreeBit1 | bit | 0 | |
| FreeBit2 | bit | 0 | |
| FreeBit3 | bit | 0 | |
| FreeBit4 | bit | 0 | |
| FreeBit5 | bit | 0 | |
| url1 | nvarchar(1000) | ||
| urldesc1 | nvarchar(255) | ||
| url2 | nvarchar(1000) | ||
| urldesc2 | nvarchar(255) | ||
| url3 | nvarchar(1000) | ||
| urldesc3 | nvarchar(255) | ||
| CertificateNo | nvarchar(50) | ||
| RequiredTotalDurationInSeconds | bigint | ||
| DoNotCountAppointmentResource | bit | 0 | |
| IsComplete | bit | 0 | |
| PlanningUOM | nvarchar(20) | ||
| PlanningUOMConversion | decimal(18,6) | 0 | |
| PlanningQty | decimal(18,6) | 0 | |
| UseFixPlanningQty | bit | 0 | |
| RoundToUOM | bit | 0 | |
| AppointmentTemplate | nvarchar(100) | ||
| BulkPlanningQty | decimal(18, 6) | ||
| StartDate | datetime | ||
| EndDate | datetime | ||
| PercentDone | int | ||
| SchedulingMode | int | 0 | |
| BaseLineStartDate | datetime | ||
| BaseLineEndDate | datetime | ||
| BaseLinePercentDone | int | ||
| DeadLine | datetime | ||
| Index | int | ||
| ConstraintType | int | 0 | |
| ConstraintDate | datetime | ||
| ParentTaskNo | nvarchar(50) | ||
| CalendarCode | nvarchar(255) | ||
| PredecessorTaskNo | nvarchar(50) | ||
| PredecessorLag | int | ||
| ManuallyScheduled | bit | 0 | |
| Note | nvarchar(max) | ||
| OverRuleGanttPlanning | bit | 0 | |
| IgnoreCalendars | bit | 0 | |
| ContainerName | nvarchar(255) | ||
| ContainerIndex | int |
Delete task
Deletes a task record. The behavior of this procedure depends on the CheckAppointments flag:
- When
CheckAppointmentsis true: the task is not deleted when there are appointments for the task. - When
CheckAppointmentsis false: the task is deleted without further checks. When a task is deleted, all appointments attached to the task, if any, are also deleted.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ✔️ | |
| JobNo | nvarchar(50) | ✔️ | |
| TaskNo | nvarchar(50) | ✔️ | |
| CheckAppointments | bit | ✔️ | |
| SentFromBackoffice | bit | 1 |
Upsert task URI
Inserts or updates a task uri record. A task link contains links to documents, web pages and the back office system.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ✔️ | |
| JobNo | nvarchar(50) | ✔️ | |
| TaskNo | nvarchar(50) | ✔️ | |
| url | nvarchar(1000) | ||
| urldesc | nvarchar(255) |
Update locked task
Locks or unlocks all appointments for the task. A locked appointment can not be modified nor deleted on the planning board.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ✔️ | |
| JobNo | nvarchar(50) | ✔️ | |
| TaskNo | nvarchar(50) | ✔️ | |
| Locked | bit | 0 | |
| SentFromBackoffice | bit | 1 |
Upsert task filter value
Inserts or updates a task value record. This assigns a filter value to a task. The TransferToTemp field indicates whether the task filter value(s) are being sent to Dime.Scheduler before (value = 1 or True) or after (value = 0 or False) the task is sent.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ✔️ | |
| JobNo | nvarchar(50) | ✔️ | |
| TaskNo | nvarchar(50) | ✔️ | |
| FilterGroup | nvarchar(50) | ✔️ | |
| FilterValue | nvarchar(100) | ✔️ | |
| TransferToTemp | bit | 0 |
Delete task filter value
Deletes a task filter value record. This removes a filter value from a task.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ✔️ | |
| JobNo | nvarchar(50) | ✔️ | |
| TaskNo | nvarchar(50) | ✔️ | |
| FilterGroup | nvarchar(20) | ✔️ | |
| FilterValue | nvarchar(20) | ✔️ |
Clear task filter value
Deletes all task filter value records. This removes all filter values from a task.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ✔️ | |
| JobNo | nvarchar(50) | ✔️ | |
| TaskNo | nvarchar(50) | ✔️ |
Upsert task container
Inserts or updates a task container.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ✔️ | |
| JobNo | nvarchar(50) | ✔️ | |
| TaskNo | nvarchar(50) | ✔️ | |
| ContainerName | nvarchar(255) | ✔️ | |
| Index | int |
Delete task container
Removes the task container.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ✔️ | |
| JobNo | nvarchar(50) | ✔️ | |
| TaskNo | nvarchar(50) | ✔️ | |
| ContainerName | nvarchar(255) |
Resources
Upsert resources
Inserts or updates a resource record. A resource in Dime.Scheduler is someone or something that needs to be allocated during a certain timeframe in order for a task to be performed or fulfilled. This can be a service technician, a sales person, an employee, a car, a machine or equipment, a hotel or meeting room, etc.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| ResourceTypeId | int | ||
| SourceApp | nvarchar(30) | ||
| SourceType | nvarchar(10) | ||
| ResourceType | nvarchar(100) | ||
| ResourceName | nvarchar(100) | ||
| DisplayName | nvarchar(100) | ||
| Department | nvarchar(50) | ||
| ResourceNo | nvarchar(50) | ✔️ | |
| nvarchar(100) | |||
| Phone | nvarchar(50) | ||
| MobilePhone | nvarchar(50) | ||
| ReplacementResource | bit | ||
| FieldServiceEmail | nvarchar(100) | ||
| PersonalEmail | nvarchar(100) | ||
| GpsTrackingResourceNo | nvarchar(50) | ||
| HomeAddress | nvarchar(max) | ||
| HomeAddressGeoLong | float | ||
| HomeAddressGeoLat | float | ||
| HomePhone | nvarchar(50) | ||
| HomeEmail | nvarchar(50) | ||
| HomeRegion | nvarchar(10) | ||
| HomePostcode | nvarchar(20) | ||
| HomeCity | nvarchar(50) | ||
| HomeCounty | nvarchar(50) | ||
| HomeState | nvarchar(50) | ||
| HomeCountry | nvarchar(50) | ||
| TeamCode | nvarchar(20) | ||
| TeamName | nvarchar(100) | ||
| TeamType | nvarchar(20) | ||
| TeamSort | int | ||
| TeamMemberType | nvarchar(20) | ||
| TeamMemberSort | int | ||
| FreeText1 | nvarchar(100) | ||
| FreeText2 | nvarchar(100) | ||
| FreeText3 | nvarchar(100) | ||
| FreeText4 | nvarchar(100) | ||
| FreeText5 | nvarchar(100) | ||
| FreeText6 | nvarchar(100) | ||
| FreeText7 | nvarchar(100) | ||
| FreeText8 | nvarchar(100) | ||
| FreeText9 | nvarchar(100) | ||
| FreeText10 | nvarchar(100) | ||
| FreeText11 | nvarchar(100) | ||
| FreeText12 | nvarchar(100) | ||
| FreeText13 | nvarchar(100) | ||
| FreeText14 | nvarchar(100) | ||
| FreeText15 | nvarchar(100) | ||
| FreeText16 | nvarchar(100) | ||
| FreeText17 | nvarchar(100) | ||
| FreeText18 | nvarchar(100) | ||
| FreeText19 | nvarchar(100) | ||
| FreeText20 | nvarchar(100) | ||
| FreeDecimal1 | decimal(18,6) | ||
| FreeDecimal2 | decimal(18,6) | ||
| FreeDecimal3 | decimal(18,6) | ||
| FreeDecimal4 | decimal(18,6) | ||
| FreeDecimal5 | decimal(18,6) | ||
| FreeDecimal6 | decimal(18,6) | ||
| FreeDecimal7 | decimal(18,6) | ||
| FreeDecimal8 | decimal(18,6) | ||
| FreeDecimal9 | decimal(18,6) | ||
| FreeDecimal10 | decimal(18,6) | ||
| FreeDate1 | datetime | ||
| FreeDate2 | datetime | ||
| FreeDate3 | datetime | ||
| FreeDate4 | datetime | ||
| FreeDate5 | datetime | ||
| FreeBit1 | bit | ||
| FreeBit2 | bit | ||
| FreeBit3 | bit | ||
| FreeBit4 | bit | ||
| FreeBit5 | bit | ||
| DoNotShow | bit | ||
| InServiceFrom | date | ||
| InServiceTill | date | ||
| ExchangeIntegrationEnabled | bit | ||
| url1 | nvarchar(1000) | ||
| urldesc1 | nvarchar(255) | ||
| url2 | nvarchar(1000) | ||
| urldesc2 | nvarchar(255) | ||
| url3 | nvarchar(1000) | ||
| urldesc3 | nvarchar(255) | ||
| BulkPlanning | bit | ||
| BulkCapacity | decimal(18,6) | ||
| ResourceGpsTrackingEnabled | bit | ||
| Pin | nvarchar(100) |
Upsert resource filter value
Inserts or updates a resource filter value record. This assigns a filter value to a resource. TransferToTemp indicates whether the resource filter value(s) are being sent to Dime.Scheduler before (value = 1 or True) or after (value = 0 or False) the resource is sent.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ||
| SourceType | nvarchar(10) | ||
| ResourceNo | nvarchar(50) | ✔️ | |
| FilterGroup | nvarchar(50) | ✔️ | |
| FilterValue | nvarchar(100) | ✔️ | |
| TransferToTemp | bit | 0 |
Delete resource filter value
Deletes a resource filter value record. This removes a filter value from a resource.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ✔️ | |
| ResourceNo | nvarchar(50) | ✔️ | |
| FilterGroup | nvarchar(20) | ✔️ | |
| FilterValue | nvarchar(20) | ✔️ |
Clear resource filter value
Deletes all resource filter value records for a resource. This removes all filter values from a resource.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ||
| SourceType | nvarchar(10) | ||
| ResourceNo | nvarchar(50) | ✔️ |
Upsert resource GPS tracking
Inserts or updates a resource gps tracking record. The resource number or GPS resource tracking number is required in order to successfully insert or update a record. When a record is inserted or updated the position on the map in Dime.Scheduler is updated automatically.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| ResourceNo | nvarchar(50) | ||
| GpsTrackingResourceNo | nvarchar(50) | ||
| Latitude | float | ||
| Longitude | float | ||
| Speed | int | ||
| Date | date | ||
| RowId | nvarchar(100) | ||
| Power | nvarchar(50) |
Upsert resource capacity
The resource capacity is set per day. If omitted, default values are set for CapacityUOM (hour by default) and CapacityUOMConversion (3600 by default).
You need to provide either CapacityInSeconds or CapacityQty. The other value is calculated using CapacityUOMConversion.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| ResourceNo | nvarchar(50) | ✔️ | |
| CapacityDate | date | ||
| CapacityInSeconds | bigint | ||
| CapacityQty | decimal(18,6) | ||
| CapacityUOM | nvarchar(20) | ||
| CapacityUOMConversion | decimal(18,6) |
Upsert resource URI
Inserts or updates a resource URI record. Resource URIs contain links to documents, web pages and the back office system.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| ResourceNo | nvarchar(50) | ✔️ | |
| Url | nvarchar(1000) | ||
| UrlDesc | nvarchar(255) |
Upsert resource certificate
Inserts or updates a resource certificate record. A resource certificate contains information on certificates/exams/scores held or passed by the resource. Resource certificates can be searched and matched against a task's certificate in the resource and appointment page when UseCertificate is activated on the resource types.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| ResourceNo | nvarchar(50) | ✔️ | |
| CertificateNo | nvarchar(50) | ✔️ | |
| Score | nvarchar(100) | '' | |
| LastScoreDate | datetime | ||
| ValidUntil | datetime |
Delete resource certificate
Deletes a resource certificate record.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| ResourceNo | nvarchar(50) | ✔️ | |
| CertificateNo | nvarchar(50) | ✔️ |
Filters
Upsert filter group
Inserts or updates a filter group record. A filter group is the base definition for the powerful filtering mechanism of Dime.Scheduler. Filter groups are used to filter on resources and tasks, but also to set up the data-driven part of the security system.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| Id | int | 0 | |
| GroupName | nvarchar(50) | ✔️ | |
| ColumnNo | int | 0 | |
| DataFilter | bit | 0 |
Rename filter group
Renames the filter group.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| GroupName | nvarchar(50) | ✔️ | |
| NewGroupName | nvarchar(50) | ✔️ |
Delete filter group
Removes the filter group.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| GroupName | nvarchar(50) | ✔️ |
Upsert filter value
Inserts or updates a filter value record. A filter value is the child of filter group, and a filter group is required before any filter value can be added.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| FilterGroupName | nvarchar(50) | ✔️ | |
| FilterValue | nvarchar(100) | ✔️ |
Delete filter value
Removes the filter value.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| FilterGroupName | nvarchar(50) | ✔️ | |
| FilterValue | nvarchar(100) | ✔️ |
Appointments
Upsert appointment
Inserts or updates an appointment record. The behavior of this procedure depends on the data you pass to it:
- The appointment id is required if you want to update an existing appointment.
- Otherwise, it inserts an appointment resource record (linking a resource to the appointment) if the record does not exist.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ✔️ | |
| AppointmentId | bigint | 0 | |
| JobNo | nvarchar(50) | ✔️ | |
| TaskNo | nvarchar(50) | ✔️ | |
| Subject | nvarchar(max) | ||
| Body | nvarchar(max) | ||
| Start | datetime | ||
| End | datetime | ||
| IsAllDayEvent | bit | 0 | |
| TimeMarker | nvarchar(100) | ||
| Category | nvarchar(100) | ||
| Importance | int | 0 | |
| Locked | bit | 0 | |
| ResourceNo | nvarchar(50) | ||
| AppointmentGuid | nvarchar(50) | '' | |
| ReplaceResource | bit | 0 | |
| SentFromBackoffice | bit | 1 | |
| BackofficeID | nvarchar(100) | ||
| BackofficeParentID | nvarchar(100) | ||
| PlanningUOM | nvarchar(20) | ||
| PlanningUOMConversion | decimal(18,6) | 0 | |
| PlanningQty | decimal(18,6) | 0 | |
| UseFixPlanningQty | bit | 0 | |
| RoundToUOM | bit | 0 |
Delete appointment
The behavior of this procedure depends on the data you pass to it:
- If a resource number is provided, then the appointment resource record is deleted. The appointment record is also deleted if no more appointment resource records exist for the appointment.
- If the resource number is not provided, then the appointment record and all linked appointment resource records are deleted.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ||
| SourceType | nvarchar(10) | ||
| AppointmentId | bigint | ||
| ResourceNo | nvarchar(50) | '' | |
| AppointmentGuid | nvarchar(50) | '' | |
| SentFromBackoffice | bit | 1 |
Upsert appointment URI
Inserts or updates an appointment URI record. An appointment uri contains links to documents, web pages and the back office system. You need to pass an existing appointment id or appointment guid.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| pSourceApp | nvarchar(30) | ||
| pSourceType | nvarchar(10) | ||
| pAppointmentId | bigint | 0 | |
| pAppointmentGuid | nvarchar(50) | ||
| pUrl | nvarchar(1000) | ✔️ | |
| pUrlDesc | nvarchar(255) |
Add assignment
Add a resource to an existing appointment, thereby creating a linked appointment.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ||
| SourceType | nvarchar(10) | ||
| AppointmentId | bigint | ||
| ResourceNo | nvarchar(50) | ✔️ | |
| AppointmentGuid | nvarchar(50) | '' | |
| SentFromBackoffice | bit | 1 |
Update appointment category
Updates the category assigned to the appointment. Can be used to update status or progress of the appointment.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ||
| SourceType | nvarchar(10) | ||
| AppointmentId | bigint | ||
| Category | nvarchar(100) | ||
| AppointmentGuid | nvarchar(50) | '' | |
| SentFromBackoffice | bit | 1 |
Update appointment time marker
Update the time marker assigned to the appointment. Can be used to update status or progress of the appointment.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ||
| SourceType | nvarchar(10) | ||
| AppointmentId | bigint | ||
| TimeMarker | nvarchar(100) | ||
| AppointmentGuid | nvarchar(50) | '' | |
| SentFromBackoffice | bit | 1 |
Update locked appointment
Locks or unlocks an appointment. A locked appointment can not be modified nor deleted on the planning board.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ||
| SourceType | nvarchar(10) | ||
| AppointmentId | bigint | ||
| Locked | bit = 0 | ||
| AppointmentGuid | nvarchar(50) | '' | |
| SentFromBackoffice | bit | 1 |
Update appointment importance
Sets the importance of an appointment.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ||
| SourceType | nvarchar(10) | ||
| AppointmentId | bigint | ||
| Importance | int | ||
| AppointmentGuid | nvarchar(50) | '' | |
| SentFromBackoffice | bit | 1 |
Update appointment planning quantity
Updates the planning quantity of an appointment.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ||
| SourceType | nvarchar(10) | ||
| AppointmentId | bigint | ||
| PlanningQty | decimal(18,6) | ||
| AppointmentGuid | nvarchar(50) | '' | |
| SentFromBackoffice | bit | 1 |
Captions
Upsert caption
Inserts or updates a caption for a DATABASEFIELD in the specified language. These captions are used in the open tasks grid, the task details pane and in the field templates.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| Context | int | 6 | |
| SourceTable | nvarchar(255) | ||
| FieldName | nvarchar(255) | ||
| Language | nvarchar(10) | ||
| Caption | nvarchar(100) |
Notifications
Upsert notification
Inserts or updates a notification record. The values for notification types are:
- 0 for Information
- 1 for Warning
- 2 for Error.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ✔️ | |
| SourceType | nvarchar(10) | ||
| AppointmentId | bigint | ||
| mboc_id | nvarchar(50) | '' | |
| NotificationType | int | 0 | |
| NotificationCode | nvarchar(20) | '' | |
| NotificationText | nvarchar(max) | '' | |
| NotificationDate | datetime | ||
| JobNo | nvarchar(50) = '' | ||
| TaskNo | nvarchar(50) = '' | ||
| AppointmentGuid | nvarchar(50) | '' | |
| SentFromBackoffice | bit | 1 |
Delete notification
Deletes the notification record. The behavior of this procedure depends on the data passed to it:
- A filled out
JobNoandTaskNowill lead to all notification records attached to theTaskNobeing deleted. - Populating
JobNowill delete all notification records attached to theJobNo.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ||
| SourceType | nvarchar(10) | ||
| AppointmentId | bigint | ||
| mboc_id | nvarchar(50) | '' | |
| AppointmentId | bigint | 0 | |
| JobNo | nvarchar(50) = '' | ||
| TaskNo | nvarchar(50) = '' | ||
| AppointmentGuid | nvarchar(50) | '' | |
| SentFromBackoffice | bit | 1 |
Action URI
Upsert action URI
Inserts or updates an action uri. Action URI's can launch another application while passing parameters as the selected resource and date or time.
Values for UrlType are:
- 0: Url is shown when user right-clicks an empty slot in the planning board
- 1: Url is shown when user right-clicks an appointment;
Only URI's with matching SourceApp and SourceType fields are shown. UrlDesc is the description shown to the user.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| SourceApp | nvarchar(30) | ||
| SourceType | nvarchar(10) | ||
| UrlType | int | ||
| Url | nvarchar(1000) | ✔️ | |
| UrlDesc | nvarchar(255) | ||
| DefaultUrl | bit | 0 |
Indicators
Upsert category
Inserts or updates a category record. A category is the property or attribute of an appointment that determines the color in which an appointment is displayed. The category is used to provide an immediate visual insight in the type or status of appointments.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| CategoryName | nvarchar(100) | ✔️ | |
| DisplayName | nvarchar(100) | ||
| CategoryHexColor | nvarchar(50) | '' | |
| ColorR | int | ||
| ColorG | int | ||
| ColorB | int |
Rename category
Renames the category.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| CategoryName | nvarchar(100) | ✔️ | |
| NewCategoryName | nvarchar(100) | ✔️ |
Delete category
Removes this indicator from the database.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| CategoryName | nvarchar(100) | ✔️ |
Upsert time marker
Inserts or updates a time marker record. A time marker is the property or attribute of an appointment that determines the colored bar displayed below the appointment in the planning board. The time marker is used to provide an immediate visual insight in the type or status of appointments.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| TimeMarker | nvarchar(100) | ✔️ | |
| HexColor | nvarchar(50) | '' | |
| ColorR | int | ||
| ColorG | int | ||
| ColorB | int |
Rename time marker
Renames the time marker.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| TimeMarker | nvarchar(100) | ✔️ | |
| NewTimeMarker | nvarchar(100) | ✔️ |
Delete time marker
Removes this indicator from the database.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| TimeMarker | nvarchar(100) | ✔️ |
Upsert pin
Inserts or updates a pin record. A pin is the property or attribute of an appointment that determines the colored pin on the map.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| Name | nvarchar(100) | ✔️ | |
| HexColor | nvarchar(50) | '' | |
| ColorR | int | ||
| ColorG | int | ||
| ColorB | int |
Rename pin
Renames the pin.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| Name | nvarchar(100) | ✔️ | |
| NewName | nvarchar(100) | ✔️ |
Delete pin
Removes the indicator from the database.
| Name | Data Type | Default Value | Required |
|---|---|---|---|
| Name | nvarchar(30) | ✔️ |