Table functions

Table function	Scheme	Description
ftApprovers	dbo	Position for determining the approvers of a specific approval step.
ftAttendanceMonthReport	dbo	Function that creates the table for the employee's monthly report
ftCanManageKnowledge	dbo	List of services and articles that the given user may administer.
ftCommaListToTableIds	dbo	Conversion of value list string (ID,ID,...) to value table.
ftCompanySearch	dbo	Search of organizations across all system items.
ftComputerSwManager	dbo	People responsible for software installed on the computer
ftDeviceSearch	dbo	Searching for the object in the tree. Returns a table of objects matching the entered parameters arranged by the path in the tree. The function can be customized. In order to customize the function, create a new function named ftDeviceSearch_Custom2 and assign a permission to run it to the "db_executor" group. For a proper functionality of ALVAO applications, the function must receive the same parameters as the original functions and return the same columns.
ftGetContextInfo	dbo	The function will return current data in the context of a database connection.
ftLicHistRelations	dbo	The function to generate the list of links for the selected license.
ftLicHistSearch	dbo	The function to search for the license.
ftNodeRelations	dbo	Function that generates a list of links for the selected object with disabled tree rights.
ftPersonFromLogin	dbo	Search person according to login name
ftPersonSearchExtended	dbo	Extended search of persons across all system items.
ftRelation	dbo	Linked ticket. The function is used to populate the fields in the dialog for updating selected links (@trId) on the Ticket links tab @ticketId.
ftRelationsTypes	dbo	Types of links between tickets. The function is used to populate the link menu.
ftTicketsStateIntersectList	dbo	The intersection of statuses for @TicketIds tickets, which you can transition to from ticket statuses, with the exception to Resolved.
ftTicketStateList	dbo	The list of statuses for the @TicketIds ticket, which you can transition to from the current status, with the exception to Resolved.
ftTicketStateListIncResolved	dbo	The list of statuses for the @TicketIds ticket, which you can transition to from the current status.
ftTicketStateMultiList	dbo	Function for the return of the intersection of possible states (including Solved/Closed) for multiple tickets.
tfDay	dbo	Calendar of days starting with @Begin to @End (excluding).
tfPersonManager	dbo	Direct managers.
tfSplitString	dbo	The function divides the value based on a preset separator.

dbo.ftApprovers

Position for determining the approvers of a specific approval step.

Parameters

Name	Type	Description
@approvalItemId	int

Return table

Column	Type	Description
iPersonId	int	Approver ID (tPerson.iPersonId).
sPerson	nvarchar(1024)	Approver name.
sPersonEmail	nvarchar(1024)	Approver e-mail.
isDelegateApprover	bit	If 1, it is a delegate of an approver. The approver is currently out of office.

dbo.ftAttendanceMonthReport

Function that creates the table for the employee's monthly report

Parameters

Name	Type	Description
@month	int
@year	int
@personId	int
@curPersonId	int

Return table

Column	Type	Description
id	int
day	datetime
lastMidnight	datetime
nextMidnight	datetime
isWeekend	bit
workTime	float
holiday	nvarchar(255)
doLink	bit
coverWithWorkLoad	bit
tolerance	int

dbo.ftCanManageKnowledge

List of services and articles that the given user may administer.

Parameters

Name	Type	Description
@userId	int

Return table

Column	Type	Description
SectionId	int
ArticleId	int

dbo.ftCommaListToTableIds

Conversion of value list string (ID,ID,...) to value table.

Parameters

Name	Type	Description
@list	nvarchar(max)

Return table

Column	Type	Description
id	int

dbo.ftCompanySearch

Search of organizations across all system items.

Parameters

Name	Type	Description
@sample	nvarchar(100)
@accountId	int

Return table

Column	Type	Description
AccountId	int
CompanyInfo	nvarchar(300)
Address	nvarchar(max)
Address2	nvarchar(max)
ItemOrder	int

dbo.ftComputerSwManager

People responsible for software installed on the computer

Parameters

Name	Type	Description
@ComputerId	int

Return table

Column	Type	Description
ComputerId	int
SwManagerNodeId	int
SwManagerPersonId	int

dbo.ftDeviceSearch

Searching for the object in the tree. Returns a table of objects matching the entered parameters arranged by the path in the tree. The function can be customized. In order to customize the function, create a new function named ftDeviceSearch_Custom2 and assign a permission to run it to the "db_executor" group. For a proper functionality of ALVAO applications, the function must receive the same parameters as the original functions and return the same columns.

Parameters

Name	Type	Description
@sample	nvarchar(300)	Searched string. The search proceeds in the path of the tree, object name, type, serial number, inventory number and asset number. If empty or NULL, the function returns only the assets entrusted to the requester. Otherwise, the function returns also objects that the user is authorized to see.
@nodeId	int	ID of the object currently selected. It will be shown as the first in the result.
@count	int	Number of items in the return table.
@personId	int	Signed-in user ID.
@requesterId	int	ID of the selected ticket requester.
@classIds	nvarchar(max)	ID of object types that limit searching. If the value is NULL, all object types are being searched.

Return table

Column	Type	Description
DeviceId	int	Object ID.
DeviceName	nvarchar(300)	Displayed object name.
DeviceInfo	nvarchar(300)	Displayed name in the menu during searching. Form: “object type: object name; inventory number, asset number, serial number".
DeviceIconId	int	Icon ID.
DevicePath	nvarchar(1024)	Path to object in tree.
ItemOrder	int	The value by which the result is sorted.

dbo.ftGetContextInfo

The function will return current data in the context of a database connection.

Return table

Name	Type	Description
PersonId	int	User ID (tPerson.iPersonId). If not set, the ID of the system user will be used instead.
Date	datetime	Date & time. If not set, the current date and time will be used.

dbo.ftLicHistRelations

The function to generate the list of links for the selected license.

Parameters

Name	Type	Description
@licHistId	int

Return table

Column	Type	Description
id	int
LicHistRelationTypeId	int
ModifiedDate	datetime
ModifiedByPerson	nvarchar(255)
Straight	bit
BeginLicHistId	int
BeginLicHistRelation	nvarchar(32)
BeginDteDate	datetime
BeginTxtLicName	nvarchar(255)
BeginLicenseCount	int
BeginTxtProductName	nvarchar(255)
BeginTxtResellerCompany	nvarchar(255)
BeginTxtLicType	nvarchar(255)
BeginTxtProductType	nvarchar(255)
BeginIntCurrentCount	int
BeginIntAssignedCount	int
BeginDteDateExpire	datetime
BeginTxtActivationKey	nvarchar(1024)
BeginTxtInventoryNum	nvarchar(255)
BeginTxtInvoice	nvarchar(255)
BeginTxtLang	nvarchar(255)
BeginBolDowngrade	bit
BeginTxtMemo	nvarchar(max)
EndLicHistId	int
EndLicHistRelation	nvarchar(32)
EndDteDate	datetime
EndTxtLicName	nvarchar(255)
EndLicenseCount	int
EndTxtProductName	nvarchar(255)
EndTxtResellerCompany	nvarchar(255)
EndTxtLicType	nvarchar(255)
EndTxtProductType	nvarchar(255)
EndIntCurrentCount	int
EndIntAssignedCount	int
EndDteDateExpire	datetime
EndTxtActivationKey	nvarchar(1024)
EndTxtInventoryNum	nvarchar(255)
EndTxtInvoice	nvarchar(255)
EndTxtLang	nvarchar(255)
EndBolDowngrade	bit
EndTxtMemo	nvarchar(max)

dbo.ftLicHistSearch

The function to search for the license.

Parameters

Name	Type	Description
@sample	nvarchar(300)
@licHistId	int
@count	int

Return table

Column	Type	Description
LicHistId	int
LicHistInfo	nvarchar(300)
ItemOrder	int

dbo.ftNodeRelations

Function that generates a list of links for the selected object with disabled tree rights.

Parameters

Name	Type	Description
@nodeId	int
@showHidden	bit

Return table

Column	Type	Description
id	int
NodeRelationTypeId	int
BeginNodeId	int
BeginNodeRelation	nvarchar(32)
BeginNodeKind	nvarchar(255)
BeginNodeName	nvarchar(255)
BeginNodePath	nvarchar(1024)
EndNodeId	int
EndNodeRelation	nvarchar(32)
EndNodeKind	nvarchar(255)
EndNodeName	nvarchar(255)
EndNodePath	nvarchar(1024)
ThroughNodeId	int
ThroughRelationName	nvarchar(255)
Affects	bit
IsAffected	bit
CreatedDate	datetime
CreatedByPerson	nvarchar(255)
RemovedDate	datetime
RemovedByPerson	nvarchar(255)
Straight	bit
Level	int

dbo.ftPersonFromLogin

Search person according to login name

Parameters

Name	Type	Description
@login	nvarchar(255)

Return table

Column	Type	Description
id	int
Person	nvarchar(255)
Login	nvarchar(255)
Email	nvarchar(255)
Pswd	nvarchar(255)
System	bit
AccountDisabled	bit
Hidden	bit
Guest	bit
SID	nvarchar(255)
AdGuid	nvarchar(255)

dbo.ftPersonSearchExtended

Extended search of persons across all system items.

Parameters

Name	Type	Description
@sample	nvarchar(100)
@accountId	int
@guest	bit
@personId	int

Return table

Column	Type	Description
PersonId	int
PersonInfo	nvarchar(300)
ItemOrder	int

dbo.ftRelation

Linked ticket. The function is used to populate the fields in the dialog for updating selected links (@trId) on the Ticket links tab @ticketId.

Parameters

Name	Type	Description
@trId	int
@ticketId	int

Return table

Column	Type	Description
BeginHdTicketId	int
EndHdTicketId	int
TicketRelationTypeId	int

dbo.ftRelationsTypes

Types of links between tickets. The function is used to populate the link menu.

Parameters

Name	Type	Description
@localeId	int

Return table

Column	Type	Description
relIdDir	int
BeginName	nvarchar(200)
EndName	nvarchar(200)
MenuOrderStr	nvarchar(200)
LocalizedBeginName	nvarchar(200)
LocalizedEndName	nvarchar(200)

dbo.ftTicketsStateIntersectList

The intersection of statuses for @TicketIds tickets, which you can transition to from ticket statuses, with the exception to Resolved.

Parameters

Name	Type	Description
@TicketIds	nvarchar(200)

Return table

Column	Type	Description
id	int
TicketState	nvarchar(64)
Order	int
TicketStateBehaviorId	int

dbo.ftTicketStateList

The list of statuses for the @TicketIds ticket, which you can transition to from the current status, with the exception to Resolved.

Parameters

Name	Type	Description
@TicketId	int

Return table

Column	Type	Description
id	int
TicketState	nvarchar(64)
Order	int
TicketStateBehaviorId	int

dbo.ftTicketStateListIncResolved

The list of statuses for the @TicketIds ticket, which you can transition to from the current status.

Parameters

Name	Type	Description
@TicketId	int

Return table

Column	Type	Description
id	int
TicketState	nvarchar(64)
Order	int
TicketStateBehaviorId	int

dbo.ftTicketStateMultiList

Function for the return of the intersection of possible states (including Solved/Closed) for multiple tickets.

Parameters

Name	Type	Description
@TicketIds	nvarchar(max)

Return table

Column	Type	Description
id	int
TicketState	nvarchar(64)
Order	int
TicketStateBehaviorId	int

dbo.tfDay

Calendar of days starting with @Begin to @End (excluding).

Parameters

Name	Type	Description
@Begin	datetime
@End	datetime

Return table

Column	Type	Description
dDay	datetime

dbo.tfPersonManager

Direct managers.

Return table

Name	Type	Description
liPersonManagerSubordinatePersonId	int
liPersonManagerManagerPersonId	int

dbo.tfSplitString

The function divides the value based on a preset separator.

Parameters

Name	Type	Description
@string	nvarchar(max)
@splitChar	nvarchar(1)

Return table

Column	Type	Description
word	nvarchar(1024)