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.

Name
mboc_addAppointmentResource
mboc_clearResourceFilterValue
mboc_clearTaskFilterValue
mboc_deleteAppointment
mboc_deleteCategory
mboc_deleteExchangeAppointment
mboc_deleteFilterGroup
mboc_deleteFilterValue
mboc_deleteJob
mboc_deleteNotification
mboc_deletePin
mboc_deleteResourceCertificate
mboc_deleteResourceFilterValue
mboc_deleteTask
mboc_deleteTaskContainer
mboc_deleteTaskFilterValue
mboc_deleteTimeMarker
mboc_deleteTimeSlotEntry
mboc_deleteTimeSlotTemplate
mboc_renameCategory
mboc_renameFilterGroup
mboc_renamePin
mboc_renameTimeMarker
mboc_updateAppointmentCategory
mboc_updateAppointmentContent
mboc_updateAppointmentImportance
mboc_updateAppointmentLocked
mboc_updateAppointmentPlanningQty
mboc_updateAppointmentTimeMarker
mboc_updateTaskLocked
mboc_upsertActionUrl
mboc_upsertAppointment
mboc_upsertAppointmentUrl
mboc_upsertCaption
mboc_upsertCategory
mboc-upsertExchangeAppointment
mboc_upsertFilterGroup
mboc_upsertFilterValue
mboc_renameFilterValue
mboc_upsertJob
mboc_upsertNotification
mboc_upsertPin
mboc_upsertResource
mboc_upsertResourceCapacity
mboc_upsertResourceCertificate
mboc_upsertResourceFilterValue
mboc_upsertResourceGpsTracking
mboc_upsertResourceUrl
mboc_upsertTask
mboc_upsertTaskContainer
mboc_upsertTaskFilterValue
mboc_upsertTaskUrl
mboc_upsertTimeMarker
mboc_upsertTimeSlotEntry
mboc_upsertTimeSlotTemplate

ℹ️ 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
ReadOnly	bit	0

Delete job

Deletes a job record. The behavior of this procedure depends on the CheckAppointments flag:

• When CheckAppointments is true: the job is not deleted when there are appointments for any task attached to the job.
• When CheckAppointments is 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
RequestedStartTime	time
RequestedEndTime	time
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 CheckAppointments is true: the task is not deleted when there are appointments for the task.
• When CheckAppointments is 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)		✔️
Email	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)		✔️

Rename filter value

Renames 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)		✔️
NewFilterValue	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 content

Updates the body and subject of the selected appointment.

Name	Data Type	Default Value	Required
SourceApp	nvarchar(30)
SourceType	nvarchar(10)
AppointmentId	bigint
AppointmentGuid	nvarchar(50)	''
SentFromBackoffice	bit	1
Subject	nvarchar(max)
Body	nvarchar(max)

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

Upsert Exchange appointment

Adds an appointment that was created in Exchange.

Name	Data Type	Default Value	Required
AppointmentId	bigint	0	✔️
AppointmentGuid	nvarchar(MAX)		✔️
Subject	nvarchar(MAX)
Body	nvarchar(MAX)
Start	datetime
End	datetime
Importance	nvarchar(1)
ResourceEmail	nvarchar(MAX)
Categories	nvarchar(MAX)

Delete Exchange appointment

Removes the record that was removed in Exchange.

Name	Data Type	Default Value	Required
AppointmentId	bigint	0	✔️
AppointmentGuid	nvarchar(MAX)		✔️
Subject	nvarchar(MAX)
Body	nvarchar(MAX)
Start	datetime
End	datetime
Importance	nvarchar(1)
ResourceEmail	nvarchar(MAX)
Categories	nvarchar(MAX)

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 JobNo and TaskNo will lead to all notification records attached to the TaskNo being deleted.
• Populating JobNo will delete all notification records attached to the JobNo.

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)		✔️