smoServer functions

Contents

Notes

  • The names of functions are not case-sensitive.
             Example documentation icon EN.png  getSLADelay and GETSLADELAY are equivalent.
  • Dates and service hours:
    • Are stored in Universal Time (UT = GMT + 0:00).
    • Are displayed in the interface in local time based on the location of the logged-in user.
    • Must be expressed in Universal Time (UT = GMT + 0:00) if they are hard-coded.

Example documentation icon EN.png  Intervention end time = 11:15 (GMT + 0:00)

  • If the location of the logged-in user is Brussels (GMT + 1:00)  -->  time passed as a parameter = "12:15"
  • If the location of the logged-in user is Cape Verde (GMT + 1:00)  -->  time passed as a parameter = "10:15"

Best Practice big icon.pngBest practice

  • When using a smoServer function in a SELECT instruction, you must always respect the following syntax:
    SELECT { * function_name(parameters) * }
    FROM   table
    WHERE  ...
  • To identify the current object, you can use either the @ID@ instruction or the @@ID@@ instruction.
  • To avoid updating the entire database inadvertently, and to restrict processing to the current object, a WHERE clause is automatically added in certain cases when this clause is not present in the SQL instruction.
    • Remember to always add this clause manually because it is only added automatically in specific cases.
    • The presence of duplicate WHERE clauses does not generate any error in the system.

Description of the smoServer functions

getSLADelay

Used to calculate (in minutes) the time interval in process steps (workflows and business rules), by taking service hours (SLA) and location (time zone, calender) into account.

The function is implemented in two phases in process steps where SQL queries are authorized (i.e. internal update steps and conditional steps). Open url.png  See Table of action types and the graphic editor.

1. Calculate the resolution time for the incident/request. Store the result in an instance variable.

Note: 

  • The result stored in the instance variable is retrieved via a tag in the following format, #[VAR.My_Variable]#.
  • Based on the type of instance variable, single quotes may be required.
    • Alphanumeric variable or date: Surround the tag using single quotes.
    • Numeric variable: No quotes required.

    Example documentation icon EN.png

  • My_Variable = 'Hello'  -->  ' #[VAR.My_Variable]# ' tag
  • My_Variable = '09/02/2016'  -->  ' #[VAR.My_Variable]# ' tag
  • My_Variable = 99999  -->  #[VAR.My_Variable]# tag

2. Update the incident/request and save the value of the instance variable in the database. Note: The calculation result can also be sent by email or reused in other process steps.

Open url.png See the examples
Examples
SELECT {* getSLADelay('05/02/2016 10:00:00', '05/02/2016 11:15:00' ) *}

- 05/02/2016 is a Friday

- No SLA

==> Returns 75 minutes: difference between 05/02/2016 10:00 am, and 05/02/2016 11:15 am

SELECT {* getSLADelay('06/02/2016 10:00:00', '06/02/2016 11:15:00' ) *}

- 06/02/2016 is a Saturday

- No SLA, Saturday is considered a working day

==> Returns 75 minutes: difference between 06/02/2016 10:00 am, and 06/02/2016 11:15 am

SELECT {* getSLADelay('06/02/2016 10:00:00', '06/02/2016 11:15:00', 1 ) *}

- 06/02/2016 is a Saturday

- SLA = Open 8 hours/day from Monday to Friday, with two working periods: 8:00 am to 12:00 pm and 2:00 pm to 6:00 pm

==> Returns 0: calculated difference for Saturday which is not a working day

SELECT {* getSLADelay('05/02/2016 10:00:00', '06/02/2016 11:15:00', 1 ) *}

- 05/02/2016 is a Friday and 06/02/2016 is a Saturday

- SLA = Open 8 hours/day from Monday to Friday, with two working periods: 8:00 am to 12:00 pm and 2:00 pm to 6:00 pm

==> Returns 360 minutes: difference between Friday 10:00 am, and Saturday 11:15 am, by applying the SLA = 6 hours on Friday + 0 hours on Saturday which is not a working day

SELECT {* getSLADelay('05/02/2016 10:00:00', '08/02/2016 11:15:00', 1, 68 ) *}

- 05/02/2016 is a Friday, 06/02/2016 is a Saturday, 07/02/2016 is a Sunday and 08/02/2016 is a Monday

- Location = Cape Verde = UT-1 as compared with GMT

- SLA = Open 8 hours/day from Monday to Friday, with two working periods: 8:00 am to 12:00 pm and 2:00 pm to 6:00 pm, expressed in local time, UT-1

==> Returns 555 minutes: difference between Friday 9:00 am (translated into local time UT-1) and Monday 10:15 am (translated into local time UT-1), by applying the SLA (already expressed in local time) = 7 hours on Friday, 0 hours on Saturday and Sunday, which are not working days, and 2 hours 15 minutes on Monday

SELECT
{* getSLADelay(SD_REQUEST.SUBMIT_DATE_UT, AM_ACTION.END_DATE_UT,
          SD_REQUEST.SLA_ID, AM_REQUESTOR.LOCATION_ID) *}
FROM   AM_ACTION
      INNER JOIN SD_REQUEST
              ON SD_REQUEST.REQUEST_ID= AM_ACTION.REQUEST_ID
      INNER JOIN AM_EMPLOYEE AM_REQUESTOR
              ON AM_REQUESTOR.EMPLOYEE_ID = SD_REQUEST.REQUESTOR_ID
WHERE  SD_REQUEST.REQUEST_ID = @@ID@@  
 Calculate the target resolution time for the current incident, between its creation date and the end date of the associated action by taking the SLA and the requestor's location into account

Implementation in a workflow: Calculate the target resolution time for processing an incident. Display the result in the Incident form in Available Field 1
SELECT
{* getSLADelay(SD_REQUEST.CREATION_DATE_UT, '09/11/2016 11:15:00',
          SD_REQUEST.SLA_ID, AM_REQUESTOR.LOCATION_ID) *}
FROM   SD_REQUEST
      INNER JOIN AM_EMPLOYEE AM_REQUESTOR
              ON AM_REQUESTOR.EMPLOYEE_ID = SD_REQUEST.REQUESTOR_ID
WHERE  REQUEST_ID = @@ID@@
 1. Create step 1 of the workflow. This is an internal update step with an SQL query used to:
  • Find the current incident: WHERE REQUEST_ID=@@ID@@
  • Apply the SLA of the incident: SD_REQUEST.SLA_ID
  • Take the location of the requestor into account: AM_REQUESTOR.LOCATION_ID
  • Calculate the time interval between the creation date of the incident, SD_REQUEST.CREATION_DATE_UT, and a specific date, 09/11/2016 11:15:00 --> The result of the SQL query is stored in the My_Variable instance variable.
UPDATE SD_REQUEST
SET    AVAILABLE_FIELD_1 = #[VAR.My_Variable]#
WHERE  REQUEST_ID=@@ID@@
 2. Create step 2 of the workflow. This is an internal update step with an SQL query used to:
  • Update the current incident: REQUEST_ID=@@ID@@
  • Replace the value of Available Field 1, AVAILABLE_FIELD_1, with the value of the #[VAR.My_Variable]# instance variable.

Another example

 Calculate the time interval between the creation date of the current incident and the start date of the action
Syntax

getSLADelay(Start_Date, End_Date, SLA_ID, Location_ID)

  • Returns the time interval between Start_Date and End_Date, by taking service hours (SLA) and location (time zone, calender) into account.
  • If there is no SLA or location, this simply returns the difference between "End_Date - Start_Date", without taking service hours, the SLA or the location into account.
  • If End_Date <= Start_Date, retourne 0.
List of parameters
Mandatory parameters Optional parameters
  • Start_Date: Intervention start date and time, expressed in Universal Time (UT).
  • End_Date: Intervention end date and time, expressed in Universal Time (UT).
  • SLA_ID: ID of the SLA.
  • Location_ID: ID of the location, used to identify the time zone and to express dates in local time.

calculateMaxResolutionDate

Used to calculate the maximum time for processing an incident/request in process steps (workflows and business rules).

The function is implemented in two phases in process steps where SQL queries are authorized (i.e. internal update steps and conditional steps). Open url.png  See Table of action types and the graphic editor.

1. Calculate the target resolution time for the incident/request. Store the result in an instance variable.

Note: The syntax for retrieving the result stored in the instance variable varies depending on the type of instance variable.

  • Alphanumeric variable or date: Surround the tag using single quotes.
  • Numeric variable: No quotes required.

    Example documentation icon EN.png

  • My_Variable = 'Hello'  -->  ' #[VAR.My_Variable]# ' tag
  • My_Variable = '09/02/2016'  -->  ' #[VAR.My_Variable]# ' tag
  • My_Variable = 99999  -->  #[VAR.My_Variable]# tag

2. Update the incident/request and save the value of the instance variable in the database. Note: The calculation result can also be sent by email or reused in other process steps.

Open url.png See the examples
Examples
SELECT {* calculateMaxResolutionDate('123456', '05/02/2016 10:00:00', 1 ) *}

- 05/02/2016 is a Friday

- SLA = Open 8 hours/day from Monday to Friday, with two working periods: 8:00 am to 12:00 pm and 2:00 pm to 6:00 pm, target resolution time of the IT Department = 8 hours

==> Target resolution time = Monday 08/02/2016, 10 am: calculate resolution time by applying the SLA and the target resolution time of 8 hours = 6 hours on Friday, 0 hours on Saturday and Sunday, which are not working days, and 2 hours on Monday

SELECT
{* calculateMaxResolutionDate (SD_REQUEST.REQUEST_ID, SD_REQUEST.SUBMIT_DATE_UT, SD_REQUEST.SLA_ID) *}
FROM   SD_REQUEST
WHERE  SD_REQUEST.REQUEST_ID = @@ID@@  
 Calculate the target resolution time of the current incident/request by taking its creation date and the SLA of the requestor into account.
Syntax

calculateMaxResolutionDate(Request_ID, Submit_Date, SLA_ID)

  • The intervention date and SLA taken into account are those belonging to the current object, except if there is an override.
List of parameters
Mandatory parameters Optional parameters
  • Request_ID: ID of the incident/request. By default, the calculation takes into account the intervention start date and the SLA of the current object.
  • Submit_Date: Override used to indicate the intervention start date/time to be taken into account, expressed in Universal Time (UT).
  • SLA_ID: Override used to indicate the ID of the SLA to be applied.
 None

getQuestionResult

Used to retrieve the answer (value) to a question.

Open url.png See the examples
Examples
(* getQuestionResult(SD_QUESTION_RESULT.QUESTION_ID, SD_QUESTION_RESULT.DYNAMIC_KEY, SD_QUESTION_RESULT.TABLENAME) *)

Syntax

getQuestionResult(Question_ID, ID, Table_Name, Language_Abbr)
List of parameters
Mandatory parameters Optional parameters
  • Question_ID: ID of the question for which the value must be retrieved.
  • ID: ID of the record in the Table_Name table.
  • Table_Name: Name of the Product name - ev itsm.png table in which the question is stored.
    • SD_QUESTION_RESULT: For standard questions.
    • SD_QUESTION_RESULT_DETAIL: For multi-cart questions.
  • Language_Abbr: If the answer is a character string, the value is stored in the field of the language passed as a parameter.
 None

smo_Tree_Get_Path

Used to retrieve the path for a Tree field, e.g. location, department, type, analytical cost allocation, with the possibility of specifying the levels.

Open url.png See the examples
Examples
(* smo_Tree_Get_Path('SD_CATALOG', SD_CATALOG.SD_CATALOG_ID, '/', 0, 1, 0, 0)*)

Service Request/Full configuration/New Standard Workstation

Display the Location tree field = Europe/France/Paris/Champs Elysees/124
  • Start = 0, end = 0 --> Europe/France/Paris/Champs Elysees/124
  • Start = 1, end = 3 --> Europe/France/Strasbourg
  • Start = 4, end = 5 --> Champs Elysees/124
  • Value Location (Last Level) = 124
Syntax

smo_Tree_Get_Path(Tablename, ID, Separator, IDonly, Ifdomain, Beglevel, Endlevel)
List of parameters
Mandatory parameters Optional parameters
  • Tablename: Name of the Product name - ev itsm.png table in which the Tree record is stored.
  • ID: ID of the record in the Tablename table.
  • Separator: Character used to separate levels in the tree structure, by default, '/'.
  • IDonly: By default, 0.
  • Ifdomain: By default, 1. Takes the current domain into account.
  • Beglevel / Endlevel: Levels from the start to the end of the tree structure to be retrieved. By default, 0/0, which means that all levels are selected.

smoAddTime

Used to add a certain number of hours to a date and retrieve the result as a number of hours.

Syntax

smoAddTime(DateTime, Working_Hours_ID, Increment, Location_ID, SLA_ID)

  • Returns a value that always represents a number of hours.

    Example documentation icon EN.png 1 hour 30 min = 1.5

  • Takes service hours (SLA) and location (time zone, calender) into account.
List of parameters
Mandatory parameters Optional parameters
  • DateTime: Date expressed in Universal Time (UT).
  • Working_Hours_ID: ID of the number of working hours per day.
  • Increment: Number of hours to be added to the date.
  • Location_ID: ID of the location, used to identify the time zone and to express dates in local time.
  • SLA_ID: ID of the SLA.
 None
Tags:
Last modified by Unknown User on 2018/05/28 15:17
Created by Administrator XWiki on 2017/01/03 11:25

Help

SiteMap
How to use Feedback

Shortcuts

Recent Updates

Haven't been here in a while? Here's what changed recently:

-   Product name - ev itsm.png
-   Product name - ev sas.png

Interesting Content

How to Automate Integration
Add a Shortcut to an App
History
Quick Dashboard
Full text search - Stop Words

Powered by XWiki ©, EasyVista 2019