Incident Template - Full History

Last modified on 2023/02/09 09:53
Contents
Notes_InstallationTemplate

  Notes:

  • New customers with the on-premises solution installed from Service Manager version 185.2 or later: The template is shipped with databases 50004 and 50005. If this is the case, only the configuration should be defined to adapt the template to its environment.
  • For other customers, all of the files required for installing the template are available in the package to be downloaded for the procedure.

  You can check the version of your starting database via the [EMPTY_DATABASE_VERSION] other parameter.

The Incident template with full history enables you to process an incident from the time it was reported by a user to its resolution by the Support team, followed by approval from the recipient.

  • It is used to send all notifications from workflows.
  •  Emails are stored in the history of actions, i.e. the timeline for the Support team used to store full traceability.

Operating principle

  • It uses several business rules, some of which are triggered by alerts.
  • You can view reports to check that alerts were triggered correctly.
  • You can customize the template in different ways based on customer requirements.

Best Practice

  • Check and adapt workflows and business rules to your environment, i.e. roles, SQL conditions, email contents, etc.
  • By default, all business rules are enabled. You should disable the ones you do not want to use.
  • Define the frequency of alerts to enable them.
     

How to choose your Incident template

  • Use the Full history template if you are required to justify and audit all exchanges for a given incident.
  • If this is not the case, use the Simplified history template that shortens the history of actions and displays only processing actions and emails sent by wizards (e.g. Send Email to Requestor).

Notes

  • Integration files are found in fichiers_csv.rar.
  • Languages L1 to L6 are not supported in the multilingual mechanisms. English is the default language.
  • You can install and use the two templates, Full history and Simplified history at the same time. Workflows and business rules are configured in order to avoid sending duplicate emails.

Procedure: How to install and configure the template

Step 1: Import the package.

1. Download the file below to your workstation.

  Package with full history

2. Extract the RAR file in a folder.

  • The (exp)_incident_template_timeline_full_emails_via_wf.zip file contains all the EXP files.
  • The csv_fichiers folder contains examples of CSV files that you can use to integrate data, such as groups, roles, Support persons and incident catalogs. You can use these files together with the integration models in the ZIP file.
             RAR file - Contents 1.png  RAR file - Contents 2.png

3. Import the file called (exp)_incident_template_timeline_full_emails_via_wf.zip to Service Manager by selecting Administration > Import/Export > Import in the menu.
 

Step 2 (optional): Configure the processes associated with REST business rules.

Business rules:

  • (BR) Automatic closing of tickets | REST
  • (BR) Automatic reopening of on hold incidents | REST

1. Declare the REST connection.

Note: The service and resources used by processes associated with business rules have already been configured. You only need to create the connection.

  • Select Administration > REST > Connections in the menu.
  • Click + New in the top banner to create an EasyVista connection.
  • Specify the host name, account, login and password.
             Procedure - Create REST - connexion.png

2. Configure the process associated with the (BR) Automatic closing of tickets | REST business rule.

  • Select Administration > Business Rules > Related Processes in the menu.
  • Edit the process called (BR) Automatic closing of tickets | REST.
    The graphic editor will appear.
  • Double-click the step called REST_CloseRequest.
  • Select the EasyVista connection and the Close incident or request resource.
  • Specify the parameters. Open url.png See the configuration of the business rule.

3. Configure the process associated with the (BR) Automatic reopening of on hold incidents | REST business rule.

  • Edit the process called (BR) Automatic reopening of on hold incidents | REST.
    The graphic editor will appear.
  • Double-click the step called REST_RestartRequest.
  • Select the EasyVista connection and the Restart incident or request resource.
  • Specify the parameters. Open url.png See the configuration of the business rule.
     

Step 3 (optional): Configure the mail component for approving or rejecting the incident solution.

1. Select Administration > Parameters > Mail Components in the menu.

2. Configure the different sections of the mail component.

MailComponentConfiguration_Procedure


Step 3.a: Configuration of the header

1. Select the _HEADER mail component and run the Update wizard.

2. Select the logo and click Insert/Edit Image Image icon.png.

3. Modify the Source code field by replacing the variables in yellow with the values corresponding to your context.

  • You are using an on-premises version:

http(s)://<server_name>/Styles/Easyvista/Images/logo_ezv.png

  • You are using an SaaS-based version:

https://<server_name>/Styles/CLIENT_1/Easyvista/Images/logo_ezv.png

4. Click OK.


Step 3.b: Configuration of the footer

1. Copy mailpart_pictures that you imported to the web server in step 1 and paste it in the www/resources/public folder.

2. Select the _FOOTER mail component and run the Update wizard.

3. Select the three icons and click Insert/Edit Image Image icon.png.

4. Modify the Source code field by replacing the variables in yellow with the values corresponding to your context.

http(s)://localhost/resources/public/mailpart_pictures/picto1.png

http(s)://localhost/resources/public/mailpart_pictures/picto2.png

http(s)://localhost/resources/public/mailpart_pictures/picto3.png


Step 3.c: Configuration of the body

1. Select the _BODY_VALIDATION mail component and run the Update wizard.

2. Modify the Accept and Reject links for the request by replacing the variables in yellow with the email address of the Technical Support Agent in charge of processing incoming emails.

  • Accept link: 

mailto:easyvista@no-reply.com?subject=Approval%20accepted%20(#RFC_NUMBER#)&body=To%20accept%20the%20approval%20of%20your%20request,%20you%20can%20simply%20click%20the%20button%27to%20send%20this%20email.%0ADo%20not%20modify%20the%20text%20found%20below.%20%0A%0A%0A@OPERATION@%3D%27SOLVE%27%0A@RFC_NUMBER@%3D%27#RFC_NUMBER#%27%0A@CHOICE@%3D%271%27

  • Reject link: 

mailto:easyvista@no-reply.com?subject=Approval%20rejected%20(#RFC_NUMBER#)&body=To%20reject%20the%20approval%20of%20your%20request,%20you%20can%20simply%20click%20the%20button%27to%20send%20this%20email.%0ADo%20not%20modify%20the%20text%20found%20below.%20%0A%0A%0A@OPERATION@%3D%27SOLVE%27%0A@RFC_NUMBER@%3D%27#RFC_NUMBER#%27%0A@CHOICE@%3D%270%27

3. Note: Only for the Oxygen version You can add a link to the customer portal in the approval email.

  • Select Administration > Parameters > Other Parameters in the menu.
    • Select the parameter called {ADMIN} Enable auto connection link in emails for individual users.
    • Run the Update wizard.
    • Set the value of the parameter to False.
  • Select the _BODY_VALIDATION mail component and run the Update wizard.
    • Delete the Accept and Reject links.
    • Insert the link to the customer portal in the following format. You should replace the variables in yellow with the values corresponding to your context.

https://<Apps server name>/index.php?name=com.csm.5a0c7491bb0d1%7C57eccd6de8713&SearchValue=#RFC_NUMBER#

EndMailComponentConfiguration_Procedure

Step 4 (automatic): Configure wizards after importing EXP files.

Transfer wizard. Open url.png See the configuration.

  • Only the Group and Support Person fields will appear.
  • The Comment field will be deleted.
     

Approval wizard. Open url.png See the configuration.

  • The sending of an email after user rejection will be deleted.

Description of template components

Workflows

[INCIDENT] Processing | Workflow notifications

   Open url.png See the configuration.

Workflow - Notifications included.png

Zoom

  • This workflow enables the Support team to process all incidents while storing all exchanges among Support persons.
  • It ensures that different actions for users and Support persons are generated, e.g. creation, analysis, information request, approval, rejection, closing, etc.
  • It also ensures that notification emails are sent to the relevant users and Support persons, e.g. creation acknowledgment email, notification for action, approval email, etc.

[INCIDENT] Immediate Solution

   Open url.png See the configuration.

WF_ImmediateResolution_Description

Workflow - Immediate resolution.png

Zoom

  • This workflow enables the Support team to manage the resolution of incidents in a ticket.
  • It is run if you find a solution as soon as the ticket is created and click Resolve.
  • It sends a closing notification email to the requestor and to the recipient if they are different individuals.

[INCIDENT] Creation through FrontOffice / TSA

   Open url.png See the configuration.

WF_CreationViaFOAndAST_Description

Workflow - Creation via Front Office and AST.png

Zoom

  • This workflow is used to create an incident via the user portal. The status of the incident will be New.
  • It sends a creation notification email to the requestor and to the recipient if they are different individuals.
  • It does not ensure the full processing of the incident. It sends an incident requalification request to the Support team.
  • Once the incident is requalified:
    • The incident will use the [INCIDENT] Processing | Workflow notifications or [INCIDENT] Processing | Email notifications workflow.
    • Its status will change to In Progress.

Business rules

(BR) Recalculate SLA when rejected

   Open url.png See the configuration.

BR_RejectNotificationSLARecalculate_Description

BR - Reject validation - SLA recalculate.png

Zoom

  • This business rule is used to update information in the database after the recipient has rejected the solution of the incident and denied approval.
  • Affected fields: The incident solution date is reinitialized and the SLA target resolution date is recalculated.

(BR) Automatic closing of tickets | REST

   Open url.png See the configuration.

BR_CloseRequest_Description

BR - Close request - REST.png

Zoom

  • This business rule is used to automatically close tickets when user approval is not performed within a given deadline.
  • It is run using the alert called Automatic closing of tickets after 5 days.
  • A Service Manager REST connection must be configured.
  • You can configure the deadline for the automatic closing of tickets.
  • An email will be sent to users informing them of the automatic closing of their tickets.

(BR) Merging all the questions into a form field

   Open url.png See the configuration.

BR_MergeQuestionFormField_Description

BR - Merge questions form field.png

Zoom

Note: Optional business rule

  • This business rule is used to merge all questions and answers from a questionnaire into a single field. These questions and answers can be seen in the Questions/Responses section in incidents.
  • Values are stored in six languages, i.e. FR, EN, SP, GE, IT and PO. This enables you to display questions in the logged-in user language, as well as answers with fixed values.
  • Answers entered manually by users cannot be translated. They are displayed in the language in which they were submitted.
example English Technical Support persons will see the questions in their language. Once the incident is transferred, French Technical Support persons will see the questions and answers with fixed values in their language. Answers manually entered in English by English Technical Support persons will continue to be displayed in English.

(BR) Automatic reopening of on hold incidents

   Open url.png See the configuration

BR_RestartRequest_Description

BR - Restart request - REST.png

Zoom

Note: Optional business rule

  • This business rule is used to reopen incidents that were suspended.
  • It can be run:
    • By the Automatic reopening of suspended incidents alert that compares the current date with the incident resumption date.
    • When a suspended incident is updated via a user email sent to the Technical Support Agent (Note: In Other Parameters, the {AST} Support return email address parameter must be enabled).

(BR) Notification upon processing incident - Hand icon

   Open url.png See the configuration.

BR_TakeChargeNotificationHandIcon_Description

BR - Take charge notification - Hand icon.png

Zoom

Note: Optional business rule

  • This is used to send an email alerting the recipient that the ticket is being processed by a Support person who self-assigned the action by clicking Assign icon.png in the notification bar.
  • It is run only for the first processing action.
  • It will not be run again even if the ticket is escalated.

(BR) Escalation notification - Transfer Wizard

   Open url.png See the configuration.

BR_EscalateNotificationTransferWizard_Description

BR - Escalate notification - Transfer wizard.png

Zoom

  • This is used to send an email to the Support team in charge of the next action when the ticket is escalated or redirected.
  • It is run in the following three cases:
    • Redirect to a Group of the same Level
    • Escalate to a Group of a higher Level
    • Redirect to a Group of a lower Level

Alerts

Alert | Automatic closing of tickets after 5 days

   Open url.png See the configuration.

Alert_AutomaticClose_Description
  • This alert triggers the business rule called (BR) Automatic closing of tickets.
  • The alert is usually run every evening. The administrator can check the log to ensure that the alert updates the corresponding records correctly.

Alert | Automatic reopening of suspended incidents

   Open url.png See the configuration.

Alert_AutomaticRestart_Description
  • Note: Optional alert
  • This alert triggers the business rule called (BR) Automatic reopening of on hold incidents | Due date.
  • The alert is usually run hourly. The administrator can check the log to ensure that the alert updates the corresponding records correctly.

Reporting

Reporting | Automatic closing of tickets after 5 days

   Open url.png See the configuration.

Reporting_AutomaticClose_Description
  • This is used to check all tickets to be closed after five days when user approval has not been performed.
  • It uses the same filter as the alert called Automatic closing of tickets after 5 days.

Reporting | Automatic reopening of suspended incidents

   Open url.png See the configuration.

Reporting_AutomaticRestart_Description
  • Note: Optional reporting
  • This is used to check all incidents to be reopened after being suspended.
  • It uses the same filter as the alert called Automatic reopening of suspended incidents.

Configuration of template components

Workflows

[INCIDENT] Processing | Workflow notifications

   Open url.png See the description.
         Workflow - Notifications included.png

Configuration of the process

(1) Step Name: 1 mail only (even upon requalification)

Workflow - Notifications included - step 1.png

Zoom

Objective: Send one email only when the incident is created.

SELECT a.REQUEST_ID
FROM SD_REQUEST a
INNER JOIN AM_ACTION b ON a.REQUEST_ID = b.REQUEST_ID
WHERE ACTION_TYPE_ID = 18 AND DONE_BY_ID IS NULL AND GROUP_ID IS NULL
AND a.REQUEST_ID = @@ID@@;

(2) Step Name: Email ack sent to the end user upon creation

Workflow - Notifications included - step 2.png

Zoom

  • Action Type: Send Email
  • Role: @Requestor
  • CC: @Recipient
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Your incident has been created (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_CREATION]#
    #[MAIL_PART._FOOTER]#

(3) Step Name: Support Analysis

Workflow - Notifications included - step 3.png

Zoom

  • Action Type: Closing Operation
  • Role: @Catalog Group
  • Status: In Progress
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] New Incident for "#RECIPIENT#" (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_PROCESS]#
    #[MAIL_PART._FOOTER]#

(4) Step Name: Self Service Approval by the end user

Caution: Remember to modify the wizard and delete the email to be sent at the end of processing because this will duplicate the email sent in step (7). To do this, you should hide all of the fields in the step. Open url.png See the configuration of wizards.

Workflow - Notifications included - step 4.png

Zoom

Objective: Offer three choices to the user: Accept, Reject and Information Request.

  • Action Type: Self Service Approval (Yes, No, Information Request)

    Note: The Self Service Approval with Survey action type offers only two choices: Accept or Reject. If you use it, you must delete steps (5) and (7).

  • Role: @Recipient
  • Status: Solved
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Could you please approve your incident (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_VALIDATION]#
    #[MAIL_PART._FOOTER]#

(5) Step Name: More info (RISK_DESCRIPTION)

Workflow - Notifications included - step 5.png

Zoom

Objective: Store the user comment in the information request to update SD_REQUEST.RISK_DESCRIPTION and send it by email to the closing group.

UPDATE SD_REQUEST
SET RISK_DESCRIPTION =
(SELECT COALESCE(NULLIF(RTRIM(DESCRIPTION),''),'N/A')
FROM (SELECT MAX(ACTION_ID) MAX_ACTION_ID FROM AM_ACTION
WHERE REQUEST_ID  = @@ID@@ AND ACTION_TYPE_ID IN (1, 31, 38)) A
INNER JOIN AM_ACTION B ON A.MAX_ACTION_ID = B.ACTION_ID)
WHERE REQUEST_ID = @@ID@@;

(6) Step Name: Approval rejected (RISK_DESCRIPTION)

Workflow - Notifications included - step 6.png

Zoom

Objective: Store the user comment in the approval rejection to update SD_REQUEST.RISK_DESCRIPTION and send it by email to the closing group.

UPDATE SD_REQUEST
SET RISK_DESCRIPTION =
(SELECT COALESCE(NULLIF(RTRIM(DESCRIPTION),''),'N/A')
FROM (SELECT MAX(ACTION_ID) MAX_ACTION_ID FROM AM_ACTION
WHERE REQUEST_ID  = @@ID@@ AND ACTION_TYPE_ID IN (1, 31, 38)) A
INNER JOIN AM_ACTION B ON A.MAX_ACTION_ID = B.ACTION_ID)
WHERE REQUEST_ID = @@ID@@;

(7) Step Name: More info required by the end user

Workflow - Notifications included - step 7.png

Zoom

  • Action Type: Closing Operation
  • Role: @Closing Group
  • Status: Solved
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] The end user "#RECIPIENT#" has asked for more information (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_MORE_INFOS]#
    #[MAIL_PART._FOOTER]#

(8) Step Name: Processing end user rejection

Note: At this stage of the workflow, the reinitialization of the initial solution date (SD_REQUEST.END_DATE_UT) and the recalculation of the SLA target date (SD_REQUEST.MAX_RESOLUTION_DATE_UT) will be performed via the business rule called (BR) Recalculate SLA when rejected.

Workflow - Notifications included - step 8.png

Zoom

  • Action Type: Closing Operation
  • Role: @Closing Group
  • Status: Reopened
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Incident solution rejected by "#RECIPIENT#" (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_DENIED]#
    #[MAIL_PART._FOOTER]#

(9) Step Name: Email ack sent to the end user upon closing

Workflow - Notifications included - step 9.png

Zoom

  • Action Type: Send Email
  • Role: @Requestor
  • CC: @Recipient
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Your incident is now closed (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_CLOSED]#
    #[MAIL_PART._FOOTER]#

[INCIDENT] Immediate Solution

   Open url.png See the description.

WF_ImmediateResolution_Configuration

          Workflow - Immediate resolution.png

Configuration of the process

(1) Step Name: Email ack sent to the end user upon closing

Workflow - Immediate resolution - step.png

Zoom

  • Action Type: Send Email
  • Role: @Requestor
  • CC: @Recipient
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Your incident is now closed (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_CLOSED]#
    #[MAIL_PART._FOOTER]#

[INCIDENT] Creation through FrontOffice / TSA

   Open url.png See the description.

WF_CreationViaFOAndAST_Configuration

          Workflow - Creation via Front Office and AST.png

Configuration of the process

(1) Step Name: Email ack sent to the end user upon creation

Workflow - Creation via Front Office and AST - step 1.png

Zoom

  • Action Type: Send Email
  • Role: @Requestor
  • CC: @Recipient
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Your incident has been created (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_CREATION]#
    #[MAIL_PART._FOOTER]#

 

(2) Step Name: Requalification

Workflow - Creation via Front Office and AST - step 2.png

Zoom

  • Action Type: Closing Operation
  • Role: @Catalog Group
  • Action Type: Closing Operation
  • Status: New
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Action needed on incident for "#RECIPIENT#" (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_PROCESS]#
    #[MAIL_PART._FOOTER]#

Business rules

(BR) Recalculate SLA when rejected

   Open url.png See the description.

BR_RejectNotificationSLARecalculate_Configuration

          BR - Reject validation - SLA recalculate.png

Configuration of the process

The SLA target date (SD_REQUEST.MAX_RESOLUTION_DATE_UT) will be recalculated:

  • The start date will be equal to the end date of the user action, i.e. the date on which the user rejected the solution.
  • The smoAddTime function is used.
  • New SLA target date = End date of the user action + Duration defined in the SLA applied to the incident catalog.
     

(2) Step Name: Update END_DATE_UT (SD_REQUEST)

BR - Reject validation - SLA recalculate - step 2.png

Zoom

Objective: Reinitialize the end date of the incident (SD_REQUEST.END_DATE_UT)

UPDATE SD_REQUEST SET END_DATE_UT = null
FROM SD_REQUEST
INNER JOIN AM_ACTION ON SD_REQUEST.REQUEST_ID = AM_ACTION.REQUEST_ID
WHERE ACTION_ID = @@ID@@;

 

(3) Step Name: Variable (DURATION)

BR - Reject validation - SLA recalculate - step 3.png

Zoom

Objective: Retrieve the SLA of the incident.

SELECT SD_SLA.DELAY AS DURATION FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
INNER JOIN SD_SLA ON SD_REQUEST.SLA_ID = SD_SLA.SLA_ID
WHERE ACTION_ID = @@ID@@;

 

(4) Step Name: Variable (WORKING_HOURS_ID)

BR - Reject validation - SLA recalculate - step 4.png

Zoom

Objective: Retrieve the SLA service hours.

SELECT WORKING_HOURS_ID FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
INNER JOIN SD_SLA ON SD_REQUEST.SLA_ID = SD_SLA.SLA_ID
WHERE ACTION_ID = @@ID@@;

 

(5) Step Name: Recalculate SLA (MAX_RESOLUTION_DATE_UT)

BR - Reject validation - SLA recalculate - step 5.png

Zoom

Objective: Recalculate the new SLA target date.

SELECT {@SD_REQUEST.MAX_RESOLUTION_DATE_UT = smoAddTime(AM_ACTION.END_DATE_UT, #[VAR.WORKING_HOURS_ID]#, #[VAR.DURATION]#, SD_REQUEST.LOCATION_ID, SD_REQUEST.SLA_ID)@}
FROM SD_REQUEST
INNER JOIN AM_ACTION ON SD_REQUEST.REQUEST_ID = AM_ACTION.REQUEST_ID
WHERE ACTION_ID = @@ID@@;

Conditions for triggering the business rule

BR - Reject validation - SLA recalculate - Trigger condition.png

Zoom

  • The SLA is recalculated using the following algorithm:

    End date of the user action, i.e. solution rejected + Duration defined in the SLA = New SLA target date

  • Trigger: AM_ACTION.EXIT_VALUE | On Update

IF EXISTS (SELECT ACTION_ID FROM INSERTED
WHERE ACTION_TYPE_ID in (1, 31, 38) AND EXIT_VALUE = 0)
BEGIN
@@FIRETRIGGER@@
END

(BR) Automatic closing of tickets | REST

   Open url.png See the description.

BR_CloseRequest_Configuration

          BR - Close request - REST.png

Configuration of the process

(2) Step Name: REST_CloseRequest

BR - Close request - REST - step 2.png

Zoom

Objective: Configure the REST_CloseRequest method.
  • Specify the following parameters:
    • ticket_id: #[DB_FIELDS.RFC_NUMBER]#
    • statusGUID_value: C3D9DFA7-7A21-46C2-B3A3-8BC50C9FF4F3
  • Note: You must first create the connector by selecting Administration > REST > Connections in the menu. Open url.png See step 2 of the installation procedure for the template.

 

(3) Step Name: Variable (SD_E-MAIL_TAG)

BR - Close request - REST - step 3.png

Zoom

Objective: Retrieve the text string, /** Ticket closed automatically **/ via a mail component. The string will be added to the header of the solution to notify that the ticket was closed using a web service.

SELECT CASE
WHEN LANGUAGE_ID = 1 THEN (SELECT TAG_LABEL_EN FROM SD_E-MAIL_TAG WHERE TAG = '_WS_CLOSE_HEADER')
WHEN LANGUAGE_ID = 2 THEN (SELECT TAG_LABEL_FR FROM SD_E-MAIL_TAG WHERE TAG = '_WS_CLOSE_HEADER')
WHEN LANGUAGE_ID = 3 THEN (SELECT TAG_LABEL_SP FROM SD_E-MAIL_TAG WHERE TAG = '_WS_CLOSE_HEADER')
WHEN LANGUAGE_ID = 4 THEN (SELECT TAG_LABEL_GE FROM SD_E-MAIL_TAG WHERE TAG = '_WS_CLOSE_HEADER')
WHEN LANGUAGE_ID = 5 THEN (SELECT TAG_LABEL_IT FROM SD_E-MAIL_TAG WHERE TAG = '_WS_CLOSE_HEADER')
WHEN LANGUAGE_ID = 6 THEN (SELECT TAG_LABEL_PO FROM SD_E-MAIL_TAG WHERE TAG = '_WS_CLOSE_HEADER')
ELSE (SELECT TAG_LABEL_EN FROM SD_E-MAIL_TAG WHERE TAG = '_WS_CLOSE_HEADER')
END AS MESSAGE
FROM SD_REQUEST
INNER JOIN AM_EMPLOYEE ON SD_REQUEST.RECIPIENT_ID = AM_EMPLOYEE.EMPLOYEE_ID
WHERE REQUEST_ID = @@ID@@;

 

(4) Step Name: Update DESCRIPTION

BR - Close request - REST - step 4.png

Zoom

Objective: Update the Solution field with the value of the mail component retrieved in the previous step in the language of the recipient.

UPDATE SD_REQUEST
SET SD_REQUEST.DESCRIPTION = '#[VAR.WS_CLOSE_HEADER]#'
+'<br /><br />'
+ISNULL(DESCRIPTION,'')
FROM SD_REQUEST WHERE SD_REQUEST.REQUEST_ID = @@ID@@;

Conditions for triggering the business rule

BR - Close request - REST - Trigger condition.png

Zoom

  • It is run using the alert called Automatic closing of tickets after 5 days.
  • Trigger: SD_REQUEST.WAVE_ID_TARGET | On Update
  • The value of the SD_REQUEST.WAVE_ID_TARGET field will be updated to 1 (currently unused field).

IF EXISTS (SELECT REQUEST_ID FROM INSERTED
WHERE WAVE_ID_TARGET = '1')
BEGIN
@@FIRETRIGGER@@
END

(BR) Merging all the questions into a form field

   Open url.png See the description.

BR_MergeQuestionFormField_Configuration

          BR - Merge questions form field.png

Configuration of the process

How the business rule works:

  • Store various variables in internal update steps to:
    • Take the user language into account.
    • Translate messages into the user language when merging questions in the DYNAMIC_DETAILS field.
  • Update the SD_REQUEST.DYNAMIC_DETAILS field using previously stored variables.
  • Use the variables in the Update field DYNAMIC_DETAILS (SD_REQUEST) step to display messages in the user language.
  • Note: The merging of questions takes into account the language of the group or Support person in charge of the action.

    example    English Technical Support persons will see the questions in their language. Once the incident is transferred, French Technical Support persons will see the questions in their language.

(1) Step Name: Variable (QUESTION)

BR - Merge questions form field - step 1.png

Zoom

Objective: Retrieve questions from the questionnaire in the Support person's language.

SELECT CASE
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 1) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 1) THEN 'question_en'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 2) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 2) THEN 'question_fr'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 3) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 3) THEN 'question_sp'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 5) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 5) THEN 'question_it'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 6) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 6) THEN 'question_po'
ELSE 'question_en'
END
AS QUESTION
FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
LEFT OUTER JOIN AM_GROUP ON AM_ACTION.GROUP_ID = AM_GROUP.GROUP_ID
LEFT OUTER JOIN AM_EMPLOYEE ON AM_ACTION.DONE_BY_ID = AM_EMPLOYEE.EMPLOYEE_ID
WHERE AM_ACTION.END_DATE_UT is null AND AM_ACTION.ACTION_ID = @@ID@@;

 

(2) Step Name: Variable (ANSWER)

BR - Merge questions form field - step 2.png

Zoom

Objective: Retrieve answers from the questionnaire in the Support person's language.

SELECT CASE
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 1) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 1) THEN 'No answer'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 2) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 2) THEN 'Pas de réponse'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 3) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 3) THEN 'Ninguna respuesta'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 5) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 5) THEN 'Nessuna risposta'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 6) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 6) THEN 'Sem resposta'
ELSE 'No answer'
END
AS ANSWER
FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
LEFT OUTER JOIN AM_GROUP ON AM_ACTION.GROUP_ID = AM_GROUP.GROUP_ID
LEFT OUTER JOIN AM_EMPLOYEE ON AM_ACTION.DONE_BY_ID = AM_EMPLOYEE.EMPLOYEE_ID
WHERE AM_ACTION.END_DATE_UT is null AND AM_ACTION.ACTION_ID = @@ID@@;

 

(3) Step Name: Variable (TRUE)

BR - Merge questions form field - step 3.png

Zoom

Objective: Retrieve the TRUE value of a Boolean answer in the Support person's language.

SELECT CASE
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 1) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 1) THEN 'True'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 2) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 2) THEN 'Vrai'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 3) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 3) THEN 'Verdadero'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 5) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 5) THEN 'Vero'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 6) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 6) THEN 'Verdadeiro'
ELSE 'True'
END
AS TRUE
FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
LEFT OUTER JOIN AM_GROUP ON AM_ACTION.GROUP_ID = AM_GROUP.GROUP_ID
LEFT OUTER JOIN AM_EMPLOYEE ON AM_ACTION.DONE_BY_ID = AM_EMPLOYEE.EMPLOYEE_ID
WHERE AM_ACTION.END_DATE_UT is null AND AM_ACTION.ACTION_ID = @@ID@@;

 

(4) Step Name: Variable (FALSE)

BR - Merge questions form field - step 4.png

Zoom

Objective: Retrieve the FALSE value of a Boolean answer in the Support person's language.

SELECT CASE
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 1) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 1) THEN 'False'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 2) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 2) THEN 'Faux'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 3) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 3) THEN 'Falso'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 5) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 5) THEN 'Falso'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 6) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 6) THEN 'Falso'
ELSE 'False'
END
AS TRUE
FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
LEFT OUTER JOIN AM_GROUP ON AM_ACTION.GROUP_ID = AM_GROUP.GROUP_ID
LEFT OUTER JOIN AM_EMPLOYEE ON AM_ACTION.DONE_BY_ID = AM_EMPLOYEE.EMPLOYEE_ID
WHERE AM_ACTION.END_DATE_UT is null AND AM_ACTION.ACTION_ID = @@ID@@;

 

(5) Step Name: Variable (HOUR)

BR - Merge questions form field - step 5.png

Zoom

Objective: Retrieve the Hour(s) string in the Support person's language.

SELECT CASE
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 1) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 1) THEN 'hour(s)'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 2) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 2) THEN 'heure(s)'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 3) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 3) THEN 'hora(s)'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 5) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 5) THEN 'ora(e)'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 6) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 6) THEN 'hora(s)'
ELSE 'hour(s)'
END
AS HOUR
FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
LEFT OUTER JOIN AM_GROUP ON AM_ACTION.GROUP_ID = AM_GROUP.GROUP_ID
LEFT OUTER JOIN AM_EMPLOYEE ON AM_ACTION.DONE_BY_ID = AM_EMPLOYEE.EMPLOYEE_ID
WHERE AM_ACTION.END_DATE_UT is null AND AM_ACTION.ACTION_ID = @@ID@@;

 

(6) Step Name: Variable (MINUTE)

BR - Merge questions form field - step 6.png

Zoom

Objective: Retrieve the Minute(s) string in the Support person's language.

SELECT CASE
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 1) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 1) THEN 'minute(s)'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 2) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 2) THEN 'minute(s)'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 3) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 3) THEN 'minuto(s)'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 5) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 5) THEN 'minuto(i)'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 6) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 6) THEN 'minuto(s)'
ELSE 'minute(s)'
END
AS HOUR
FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
LEFT OUTER JOIN AM_GROUP ON AM_ACTION.GROUP_ID = AM_GROUP.GROUP_ID
LEFT OUTER JOIN AM_EMPLOYEE ON AM_ACTION.DONE_BY_ID = AM_EMPLOYEE.EMPLOYEE_ID
WHERE AM_ACTION.END_DATE_UT is null AND AM_ACTION.ACTION_ID = @@ID@@;

 

(7) Step Name: Variable (FILE)

BR - Merge questions form field - step 7.png

Zoom

Objective: Retrieve the See file in attachments string in the Support person's language.

SELECT CASE
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 1) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 1) THEN 'See file in attachments'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 2) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 2) THEN 'Voir fichier en pièce jointe'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 3) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 3) THEN 'Ver archivo adjunto'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 5) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 5) THEN 'Vedi il file allegato'
WHEN (DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 6) OR (DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 6) THEN 'Veja o arquivo anexado'
ELSE 'See file in attachments'
END
AS UPLOAD_FILE
FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
LEFT OUTER JOIN AM_GROUP ON AM_ACTION.GROUP_ID = AM_GROUP.GROUP_ID
LEFT OUTER JOIN AM_EMPLOYEE ON AM_ACTION.DONE_BY_ID = AM_EMPLOYEE.EMPLOYEE_ID
WHERE AM_ACTION.END_DATE_UT is null AND AM_ACTION.ACTION_ID = @@ID@@;

 

(8) Step Name: Variable (HEADER)

BR - Merge questions form field - step 8.png

Zoom

Objective: Retrieve the Merging header in the Support person's language via the _SQL_MERGE_HEADER mail component.

SELECT REPLACE(REPLACE(REPLACE(
CASE
WHEN AM_ACTION.DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 1 THEN (SELECT TAG_LABEL_EN FROM SD_MAIL_TAG WHERE TAG = '_SQL_MERGE_HEADER')
WHEN AM_ACTION.DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 2 THEN (SELECT TAG_LABEL_FR FROM SD_MAIL_TAG WHERE TAG = '_SQL_MERGE_HEADER')
WHEN AM_ACTION.DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 3 THEN (SELECT TAG_LABEL_SP FROM SD_MAIL_TAG WHERE TAG = '_SQL_MERGE_HEADER')
WHEN AM_ACTION.DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 4 THEN (SELECT TAG_LABEL_GE FROM SD_MAIL_TAG WHERE TAG = '_SQL_MERGE_HEADER')
WHEN AM_ACTION.DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 5 THEN (SELECT TAG_LABEL_IT FROM SD_MAIL_TAG WHERE TAG = '_SQL_MERGE_HEADER')
WHEN AM_ACTION.DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 6 THEN (SELECT TAG_LABEL_PO FROM SD_MAIL_TAG WHERE TAG = '_SQL_MERGE_HEADER')
WHEN AM_ACTION.DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 1 THEN (SELECT TAG_LABEL_EN FROM SD_MAIL_TAG WHERE TAG = '_SQL_MERGE_HEADER')
WHEN AM_ACTION.DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 2 THEN (SELECT TAG_LABEL_FR FROM SD_MAIL_TAG WHERE TAG = '_SQL_MERGE_HEADER')
WHEN AM_ACTION.DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 3 THEN (SELECT TAG_LABEL_SP FROM SD_MAIL_TAG WHERE TAG = '_SQL_MERGE_HEADER')
WHEN AM_ACTION.DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 4 THEN (SELECT TAG_LABEL_GE FROM SD_MAIL_TAG WHERE TAG = '_SQL_MERGE_HEADER')
WHEN AM_ACTION.DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 5 THEN (SELECT TAG_LABEL_IT FROM SD_MAIL_TAG WHERE TAG = '_SQL_MERGE_HEADER')
WHEN AM_ACTION.DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 6 THEN (SELECT TAG_LABEL_PO FROM SD_MAIL_TAG WHERE TAG = '_SQL_MERGE_HEADER')
ELSE (SELECT TAG_LABEL_EN FROM SD_MAIL_TAG WHERE TAG = '_SQL_MERGE_HEADER')
END
, '<p>', ''), '</p>', ''), '''', '´') AS HEADER
FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
LEFT OUTER JOIN AM_GROUP ON AM_ACTION.GROUP_ID = AM_GROUP.GROUP_ID
LEFT OUTER JOIN AM_EMPLOYEE ON AM_ACTION.DONE_BY_ID = AM_EMPLOYEE.EMPLOYEE_ID
WHERE AM_ACTION.END_DATE_UT is null AND AM_ACTION.ACTION_ID = @@ID@@;

 

(9) Step Name: Update field DYNAMIC_DETAILS

BR - Merge questions form field - step 9.png

Zoom

Objective: Merge the questions in the SD_REQUEST.DYNAMIC_DETAILS field while taking into account the different languages.

DECLARE @CPTE1 INT, @CPTE2 INT, @TEXT NVARCHAR(4000)
SELECT
@CPTE1 = MAX(RESULT_ORDER),
@CPTE2 = MIN(RESULT_ORDER) FROM V_SD_QUESTION_RESULT A INNER JOIN AM_ACTION B ON A.REQUEST_ID = B.REQUEST_ID WHERE ACTION_ID = @@ID@@
SET @TEXT=''
WHILE @CPTE2 <= @CPTE1
BEGIN
SET @TEXT=@TEXT+ISNULL((
SELECT
REPLACE(REPLACE(
CASE

when value_type = 'radio' then '<b>'+#[VAR.QUESTION]#+' : </b>'+coalesce(nullif(rtrim(result_string_fr),''),'#[VAR.ANSWER]#')+'<br/>'
when value_type = 'checkbox' and result = 1 then '<b>'+#[VAR.QUESTION]#+' : </b>'+'#[VAR.TRUE]#'+'<br/>'
when value_type = 'checkbox' and result = 0 then '<b>'+#[VAR.QUESTION]#+' : </b>'+'#[VAR.FALSE]#'+'<br/>'
when value_type = 'date' and result_date is null then '<b>'+#[VAR.QUESTION]#+' : </b> '+'#[VAR.ANSWER]#'+'<br/>'
when value_type = 'date' and result_date is not null then '<b>'+#[VAR.QUESTION]#+' : </b> '+convert(nvarchar(20),result_date,103)+'<br/>'
when value_type = 'datetime' and result_date is null then '<b>'+#[VAR.QUESTION]#+' : </b> '+'#[VAR.ANSWER]#'+'<br/>'
when value_type = 'datetime' and result_date is not null then '<b>'+#[VAR.QUESTION]#+' : </b> '+convert(nvarchar(20),result_date,103)+' - '+convert(nvarchar(20),result_date,114) + ' (UTC)'+'<br/>'
when value_type = 'time' and result = '' then '<b>'+#[VAR.QUESTION]#+' : </b>'+'#[VAR.ANSWER]#'+'<br/>'
when value_type = 'time' and result < 60 then '<b>'+#[VAR.QUESTION]#+' : </b>'+result+' #[VAR.MINUTE]#'+'<br/>'
when value_type = 'time' and result >= 60 then '<b>'+#[VAR.QUESTION]#+' : </b>'+cast(result/60 as nvarchar(10))+' #[VAR.HOUR]#'+' '+cast (result%60 as nvarchar(10))+' #[VAR.MINUTE]#'+'<br/>'
when value_type = 'float' then '<b>'+#[VAR.QUESTION]#+' : </b>'+coalesce(nullif(rtrim(result),''),'#[VAR.ANSWER]#')+'<br/>'
when value_type = 'grid' then '<b>'+#[VAR.QUESTION]#+' : </b>'+coalesce(nullif(rtrim(result_string_fr),''),'#[VAR.ANSWER]#')+'<br/>'
when value_type = 'list' then '<b>'+#[VAR.QUESTION]#+' : </b>'+coalesce(nullif(rtrim(result_string_fr),''),'#[VAR.ANSWER]#')+'<br/>'
when value_type = 'sql' then '<b>'+#[VAR.QUESTION]#+' : </b>'+coalesce(nullif(rtrim(result_string_fr),''),'#[VAR.ANSWER]#')+'<br/>'
when value_type = 'memo' then '<b>'+#[VAR.QUESTION]#+' : </b>'+coalesce(nullif(rtrim(result),''),'#[VAR.ANSWER]#')+'<br/>'
when value_type = 'int' then '<b>'+#[VAR.QUESTION]#+' : </b>'+coalesce(nullif(rtrim(result),''),'#[VAR.ANSWER]#')+'<br/>'
when value_type = 'text' then '<b>'+#[VAR.QUESTION]#+' : </b>'+coalesce(nullif(rtrim(result),''),'#[VAR.ANSWER]#')+'<br/>'
when value_type = 'signature' and result is null then '<b>'+#[VAR.QUESTION]#+' : </b>'+'#[VAR.ANSWER]#'+'<br/>'
when value_type = 'signature' and result is not null then '<b>'+#[VAR.QUESTION]#+' : </b>'+'#[VAR.FILE]#'+' "'+(select document_name from am_document where document_id = result)+'"'+'<br/>'
when value_type = 'document' and result = '' then '<b>'+#[VAR.QUESTION]#+' : </b>'+'#[VAR.ANSWER]#'+'<br/>'
when value_type = 'document' and result is not null then '<b>'+#[VAR.QUESTION]#+' : </b>'+'#[VAR.FILE]#'+' "'+(select document_name from am_document where document_id = result)+'"'+'<br/>'
else '<b>'+#[VAR.QUESTION]#+' : </b>'+coalesce(nullif(rtrim(result),''),'#[VAR.ANSWER]#')+'<br/>'

END
, '[', ''), ']', '')
FROM V_SD_QUESTION_RESULT A
INNER JOIN AM_ACTION B ON A.REQUEST_ID = B.REQUEST_ID
INNER JOIN SD_QUESTION C ON A.QUESTION_ID = C.QUESTION_ID
WHERE ACTION_ID = @@ID@@
AND ISNULL(A.QUESTION_DISPLAYED,1) = 1
AND RESULT_ORDER = @CPTE2),'')
SET @CPTE2 = @CPTE2+1
END

UPDATE SD_REQUEST
SET DYNAMIC_DETAILS = '#[VAR.HEADER]#'+'<br>'+@TEXT
FROM SD_REQUEST
INNER JOIN AM_ACTION ON SD_REQUEST.REQUEST_ID = AM_ACTION.REQUEST_ID
WHERE ACTION_ID = @@ID@@;

Conditions for triggering the business rule

BR - Merge questions form field - Trigger condition.png

Zoom

  • Trigger: AM_ACTION | On Insert

IF EXISTS (SELECT ACTION_ID FROM INSERTED
INNER JOIN V_SD_QUESTION_RESULT ON INSERTED.REQUEST_ID = V_SD_QUESTION_RESULT.REQUEST_ID
WHERE INSERTED.END_DATE_UT is null AND ACTION_TYPE_ID in (20, 34))
BEGIN
@@FIRETRIGGER@@
END

(BR) Automatic reopening of on hold incidents | REST

   Open url.png See the description.

BR_RestartRequest_Configuration

          BR - Restart request - REST.png

This business rule is used to reopen incidents that were suspended. Two possible scenarios:

  • (BR) Automatic reopening of on hold incidents | Due date
    • Incidents whose resumption date is exceeded will automatically be reopened.
    • The AM_ACTION.EXPECTED_END_DATE_UT field is compared with the current date via an alert and the REST_RestartRequest method.
    • The value of Available Field 6 (AM_ACTION.AVAILABLE_FIELD_6) will be 1.
  • (BR) Automatic reopening of on hold incidents | Incoming email 
    • The suspended incident is updated via a user email sent to the Technical Support Agent.
    • The incident will be reopened via the alert.
    • The (BR) Automatic reopening of on hold incidents business rule will be run.

Configuration of the process

(2) Step Name: REST_RestartRequest

BR - Restart request - REST - step 2.png

Zoom

Objective: Configure the REST_RestartRequest method.
  • incident_id parameter: #[DB_FIELDS.REQUEST_ID.SD_REQUEST.RFC_NUMBER]#
  • Note: You must first create the connector by selecting Administration > REST > Connections in the menu. Open url.png See step 2 of the installation procedure for the template.

(3) Step Name: Variable (WS_REOPEN_HEADER)

BR - Restart request - REST - step 3.png

Zoom

Objective: Retrieve the text string, /** Incident reopened automatically **/ via a mail component. It will be added to the AM_ACTION.COMMENT field to notify that the incident was reopened.

SELECT REPLACE(REPLACE(
CASE
WHEN AM_ACTION.DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 1 THEN (SELECT TAG_LABEL_EN FROM SD_E-MAIL_TAG WHERE TAG = '_WS_REOPEN_HEADER')
WHEN AM_ACTION.DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 2 THEN (SELECT TAG_LABEL_FR FROM SD_E-MAIL_TAG WHERE TAG = '_WS_REOPEN_HEADER')
WHEN AM_ACTION.DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 3 THEN (SELECT TAG_LABEL_SP FROM SD_E-MAIL_TAG WHERE TAG = '_WS_REOPEN_HEADER')
WHEN AM_ACTION.DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 4 THEN (SELECT TAG_LABEL_GE FROM SD_E-MAIL_TAG WHERE TAG = '_WS_REOPEN_HEADER')
WHEN AM_ACTION.DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 5 THEN (SELECT TAG_LABEL_IT FROM SD_E-MAIL_TAG WHERE TAG = '_WS_REOPEN_HEADER')
WHEN AM_ACTION.DONE_BY_ID is null AND AM_GROUP.LANGUAGE_ID = 6 THEN (SELECT TAG_LABEL_PO FROM SD_E-MAIL_TAG WHERE TAG = '_WS_REOPEN_HEADER')
WHEN AM_ACTION.DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 1 THEN (SELECT TAG_LABEL_EN FROM SD_E-MAIL_TAG WHERE TAG = '_WS_REOPEN_HEADER')
WHEN AM_ACTION.DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 2 THEN (SELECT TAG_LABEL_FR FROM SD_E-MAIL_TAG WHERE TAG = '_WS_REOPEN_HEADER')
WHEN AM_ACTION.DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 3 THEN (SELECT TAG_LABEL_SP FROM SD_E-MAIL_TAG WHERE TAG = '_WS_REOPEN_HEADER')
WHEN AM_ACTION.DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 4 THEN (SELECT TAG_LABEL_GE FROM SD_E-MAIL_TAG WHERE TAG = '_WS_REOPEN_HEADER')
WHEN AM_ACTION.DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 5 THEN (SELECT TAG_LABEL_IT FROM SD_E-MAIL_TAG WHERE TAG = '_WS_REOPEN_HEADER')
WHEN AM_ACTION.DONE_BY_ID is not null AND AM_EMPLOYEE.LANGUAGE_ID = 6 THEN (SELECT TAG_LABEL_PO FROM SD_E-MAIL_TAG WHERE TAG = '_WS_REOPEN_HEADER')
ELSE (SELECT TAG_LABEL_EN FROM SD_E-MAIL_TAG WHERE TAG = '_WS_REOPEN_HEADER')
END,

'<p>', ''), '</p>', '') AS WS_REOPEN_HEADER
FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
INNER JOIN AM_GROUP ON AM_ACTION.GROUP_ID = AM_GROUP.GROUP_ID
LEFT OUTER JOIN AM_EMPLOYEE ON AM_ACTION.DONE_BY_ID = AM_EMPLOYEE.EMPLOYEE_ID
WHERE ACTION_ID in (SELECT MAX(ACTION_ID) FROM AM_ACTION
WHERE ACTION_TYPE_ID = 5 AND REQUEST_ID = (SELECT REQUEST_ID FROM AM_ACTION WHERE ACTION_ID = @@ID@@) GROUP BY AM_ACTION.REQUEST_ID);

 

(4) Step Name: Update field COMMENT (AM_ACTION)

BR - Restart request - REST - step 4.png

Zoom

Objective: Update the AM_ACTION.COMMENT field of the last resumption action with the following comment: /** Incident reopened automatically **/. The language of the group or Support person in charge of the processing action will be taken into account.

UPDATE AM_ACTION
SET COMMENT = '**#[VAR.WS_REOPEN_HEADER]#**'
WHERE ACTION_ID IN
(SELECT MAX(ACTION_ID) FROM AM_ACTION
WHERE ACTION_TYPE_ID = 6
AND REQUEST_ID = (SELECT REQUEST_ID FROM AM_ACTION WHERE ACTION_ID = @@ID@@)
GROUP BY REQUEST_ID);

 

(5) Step Name: Email ack upon reopening the incident

BR - Restart request - REST - step 5.png

Zoom

  • Action Type: Send Email
  • Role: #[CUSTOM_ROLE.Groups with an Action to complete]#
  • CC: #[CUSTOM_ROLE.List of Support Persons with an Action to be executed]#
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Automatic reopening of a suspended incident (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_REOPEN]#
    #[MAIL_PART._FOOTER]#

Conditions for triggering the business rule

Via the Automatic reopening of suspended incidents alert that compares the current date with the incident resumption date to check whether the AM_ACTION.EXPECTED_END_DATE_UT field is exceeded.

BR - Restart request - REST - Trigger condition - Via alert.png

Zoom

  • Trigger: AM_ACTION.AVAILABLE_FIELD_6 | On Update
  • The value of the AM_ACTION.AVAILABLE_FIELD_6 field will be updated to 1.

IF EXISTS (SELECT ACTION_ID FROM INSERTED
WHERE ACTION_TYPE_ID = 5
AND EXPECTED_END_DATE_UT is not null
AND AVAILABLE_FIELD_6 = '1')
BEGIN
@@FIRETRIGGER@@
END

 

When a suspended incident is updated via a user email sent to the Technical Support Agent (Note: In Other Parameters, the {AST} Support return email address parameter must be enabled).

BR - Restart request - REST - Trigger condition - Via TSA email.png

Zoom

  • Trigger: AM_ACTION | On Insert

IF EXISTS (SELECT ACTION_ID FROM INSERTED
INNER JOIN SD_REQUEST ON INSERTED.REQUEST_ID = SD_REQUEST.REQUEST_ID
WHERE ACTION_TYPE_ID = 76
AND SD_REQUEST.STATUS_ID = 5)
BEGIN
@@FIRETRIGGER@@
END

(BR) Notification upon processing incident - Hand icon

   Open url.png See the description.

BR_TakeChargeNotificationHandIcon_Configuration

          BR - Take charge notification - Hand icon.png

Configuration of the process

(1) Step Name: Email ack sent to the end user upon handling the incident

BR - Take charge notification - Hand icon - step 1.png

Zoom

  • Action Type: Send Email
  • Role: @Requestor
  • CC: @Recipient
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Your incident is now being handled by the Support Team (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_IN_PROGRESS]#
    #[MAIL_PART._FOOTER]#

 

(2) Objective of the step: Store a trace of the ticket number.

SELECT TOP 1 RFC_NUMBER FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
WHERE ACTION_ID = @@ID@@;.

(3) Objective of the step: Store the initial description of the ticket.

SELECT TOP 1 SD_REQUEST.COMMENT FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
WHERE ACTION_ID = @@ID@@;

Conditions for triggering the business rule

BR - Take charge notification - Hand icon - Trigger condition.png

Zoom

  • This business rule is run for action types 20 (Operation Processing) and 34 (Closing Operation).
  • Trigger: AM_ACTION.DONE_BY_ID | On Update

IF EXISTS (SELECT ACTION_ID FROM INSERTED WHERE START_DATE_UT IS NOT NULL AND ACTION_ID = (SELECT MIN(AM_ACTION.ACTION_ID) FROM AM_ACTION
WHERE AM_ACTION.ACTION_TYPE_ID in (20, 34) AND REQUEST_ID = INSERTED.REQUEST_ID))
BEGIN
@@FIRETRIGGER@@
END

(BR) Escalation notification - Transfer Wizard

   Open url.png See the description.

BR_EscalateNotificationTransferWizard_Configuration

          BR - Escalate notification - Transfer wizard.png

Configuration of the process

(1) Objective of the step: Store a trace of the ticket number.

SELECT TOP 1 RFC_NUMBER FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
WHERE ACTION_ID = @@ID@@;

(2) Objective of the step: Retrieve the escalation comment to update SD_REQUEST.RISK_DESCRIPTION and use it in a mail component (_BODY_ESCALATED).

UPDATE SD_REQUEST
SET RISK_DESCRIPTION =
(SELECT COALESCE(NULLIF(RTRIM(REPLACE(DESCRIPTION,'''','´')),''),'N/A')
FROM AM_ACTION WHERE ACTION_ID
IN (SELECT MAX(ACTION_ID) AS MAX_ACTION_ID
FROM AM_ACTION WHERE ACTION_TYPE_ID in (20, 34)
AND DONE_BY_ID IS NOT NULL AND START_DATE_UT IS NOT NULL AND
END_DATE_UT IS NOT NULL AND
REQUEST_ID = (SELECT REQUEST_ID FROM AM_ACTION WHERE ACTION_ID = @@ID@@) GROUP BY REQUEST_ID))
FROM SD_REQUEST
INNER JOIN AM_ACTION ON SD_REQUEST.REQUEST_ID = AM_ACTION.REQUEST_ID
WHERE ACTION_ID = @@ID@@;

(3) Objective of the step: Update the next action with the comment from the previous action (escalation, same-level or lower-level redirection).

UPDATE AM_ACTION SET DESCRIPTION = '#[DB_FIELDS.REQUEST_ID.SD_REQUEST.RISK_DESCRIPTION]#'
WHERE ACTION_TYPE_ID in (20, 34)
AND START_DATE_UT IS NULL AND END_DATE_UT IS NULL AND REQUEST_ID = (SELECT REQUEST_ID FROM AM_ACTION WHERE ACTION_ID = @@ID@@);

(4) Step Name: Email ack when incident is escalated

BR - Escalate notification - Transfer wizard - step 4.png

Zoom

  • Action Type: Send Email
  • Role: #[CUSTOM_ROLE.Groups with an Action to complete]#
  • CC: #[CUSTOM_ROLE.List of Support Persons with an Action to be executed]#
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] The ticket for "#RECIPIENT#" has been transferred/escalated (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_ESCALATED]#
    #[MAIL_PART._FOOTER]#

Conditions for triggering the business rule

BR - Escalate notification - Transfer wizard - Trigger condition.png

Zoom

  • This business rule is run for action types 13 (Escalation), 14 (Redirection) and 52 (Redirected to a Level Below).
  • Trigger: AM_ACTION | On Insert

IF EXISTS (SELECT ACTION_ID FROM INSERTED
WHERE ACTION_TYPE_ID in (13, 14, 52))
BEGIN
@@FIRETRIGGER@@
END

Alerts

Alert | Automatic closing of tickets after 5 days

   Open url.png See the description.

Alert_AutomaticClose_Configuration

Parent Query: Action Operation

Alert Type: Standard

Frequency: Every day at 00:00 (UT) i.e. 2 am (CET)

DATEDIFF(d,SD_REQUEST.END_DATE_UT,getutcdate()) >= 5)
AND (AM_ACTION.END_DATE_UT IS NULL)
AND (AM_ACTION.ACTION_TYPE_ID IN (1,31,38))
AND (SD_REQUEST_STATUS.STATUS_GUID IN ('{DC97DD1D-0F35-4153-B0E1-0F2E0155365D}'))
AND ({# TREEWHERE('SD_CATALOG', 'CATALOG_GUID', '{932E417B-2798-4E45-9918-7FB6A9CB49D5}', 'SD_CATALOG') #})

UPDATE SD_REQUEST
SET WAVE_ID_TARGET = '1'
FROM SD_REQUEST
INNER JOIN AM_ACTION ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
WHERE (WAVE_ID_TARGET <> '1' or WAVE_ID_TARGET is null)
AND ACTION_ID in (#LIST_ID#);

Alert | Automatic reopening of suspended incidents

   Open url.png See the description.

Alert_AutomaticRestart_Configuration

Parent Query: Action Operation

Alert Type: Standard

Frequency: Hourly

AM_ACTION.ACTION_ID IN (SELECT MAX(ACTION_ID)
FROM AM_ACTION
WHERE ACTION_TYPE_ID = 5
AND EXPECTED_END_DATE_UT is not null
AND EXPECTED_END_DATE_UT < GETUTCDATE() GROUP BY REQUEST_ID)

UPDATE AM_ACTION
SET AVAILABLE_FIELD_6 = '1'
WHERE (AVAILABLE_FIELD_6 <> '1' or AVAILABLE_FIELD_6 is null)
AND ACTION_ID in (#LIST_ID#);

Reporting

Reporting | Automatic closing of tickets after 5 days

   Open url.png See the description.

Reporting_AutomaticClose_Configuration

Used to check tickets to be closed after five days.

          Reporting - Automatic close.png

Reporting | Automatic reopening of suspended incidents

   Open url.png See the description.

Reporting_AutomaticRestart_Configuration

Used to check incidents to be resumed.

          Reporting - Automatic restart.png

Wizards

Transfer wizard in the Action form (HD - Transfer Incident)

The objective of the modifications is to display only the Group and Support Person fields in the Transfer wizard.

The Copy comment from current action to next action and Notifications options are managed by the (BR) Escalation notification - Transfer Wizard business rule.

        Transfer wizard - Configuration 1.png

        Transfer wizard - Configuration 2.png

Approval wizard (WF - Validation)

The objective of the modifications is to hide the user email sent in the rejection notification step. The Support team will be notified by the [INCIDENT] Processing | Workflow notifications workflow.

        Validation wizard - Configuration.png

Email templates

EmailTemplates_Configuration

          Mail component template.png

All emails sent by workflows are based on the HEADER / BODY / FOOTER structure using mail components.

  • Header: Modify the path of the logo based on your context.
    • By default, this is the Service Manager logo found at the top left.
  • Footer: Customize the Support contact information based on your context, e.g. icons, email address, phone number or customer portal URL.

List of files to download

Tags:
Powered by XWiki © EasyVista 2022