Incident Template - Common Text


Description of components

Workflows

[INCIDENT] Immediate Solution

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.
EndWF_ImmediateResolution_Description

[INCIDENT] Creation through FrontOffice / TSA

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.
EndWF_CreationViaFOAndAST_Description

Business rules

(BR) Automatic reopening of on hold incidents

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).
Note:
  • By default, the business rule is based on the (BR) Automatic reopening of on hold incidents | REST process. This process contains a REST step that uses the REST_RestartRequest method in Service Manager. Open url.png See step 2 of the installation procedure.
  • To use a SOAP web service, select (BR) Automatic reopening of on hold incidents | SOAP. Next, configure the EZV_CloseRequest step using the SOAP web service. Open url.png See step 3 of the installation procedure.
EndBR_RestartRequest_Description

(BR) Recalculate SLA when rejected

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.
EndBR_RejectNotificationSLARecalculate_Description

(BR) Merging all the questions into a form field

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.
EndBR_MergeQuestionFormField_Description

Alerts

Alert | Automatic reopening of suspended incidents

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.
EndAlert_AutomaticRestart_Description

Reporting

Reporting | Automatic reopening of suspended incidents

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.
EndReporting_AutomaticRestart_Description

Configuration of components

Workflows

[INCIDENT] Immediate Solution

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]#

EndWF_ImmediateResolution_Configuration

[INCIDENT] Creation through FrontOffice / TSA

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]#

EndWF_CreationViaFOAndAST_Configuration

Business rules

(BR) Automatic reopening of on hold incidents | REST

BR_RestartRequestREST_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.

See the SQL Condition.

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.

See the SQL Condition.

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.

See the SQL Condition.

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

See the SQL Condition.

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

EndBR_RestartRequestREST_Configuration

(BR) Automatic reopening of on hold incidents | SOAP

BR_RestartRequestSOAP_Configuration

         BR - Restart request - SOAP.png

The configuration of the process is identical to the one for the (BR) Automatic closing of tickets | REST business rule. Only step (3) described below is different for the parameters to be specified because it uses a Web Service step instead of the REST method.

(2) Step Name: EZV_RestartRequest

BR - Restart request - SOAP - step 2.png

Zoom

Objective: Configure the EZV_RestartRequest web service.
  • URL: https:///WebService/SmoBridge.php?wsdl
  • incident_id parameter: #[DB_FIELDS.REQUEST_ID.SD_REQUEST.RFC_NUMBER]#
  • Note: You must first create the web service by selecting Administration > Parameters > Web services in the menu. Open url.png See step 3 of the installation procedure for the template.
EndBR_RestartRequestSOAP_Configuration

(BR) Recalculate SLA when rejected

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)

See the SQL Condition.

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.

See the SQL Condition.

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.

See the SQL Condition.

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.

See the SQL Condition.

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

See the SQL Condition.

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

(BR) Merging all the questions into a form field

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.

See the SQL Condition.

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.

See the SQL Condition.

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.

See the SQL Condition.

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.

See the SQL Condition.

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.

See the SQL Condition.

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.

See the SQL Condition.

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.

See the SQL Condition.

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.

See the SQL Condition.

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.

See the SQL Condition.

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

See the SQL Condition.

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
EndBR_MergeQuestionFormField_Configuration

Alerts

Alert | Automatic reopening of suspended incidents

Alert_AutomaticRestart_Configuration

Parent Query: Action Operation

Alert Type: Standard

Frequency: Hourly

See the SQL Condition for filtering.

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)

See the SQL Condition for running the business rule.

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#);
EndAlert_AutomaticRestart_Configuration

Reporting

Reporting | Automatic reopening of suspended incidents

Reporting_AutomaticRestart_Configuration

Used to check incidents to be resumed.

         Reporting - Automatic restart.png

EndReporting_AutomaticRestart_Configuration

Tags:
Last modified by Unknown User on 2020/07/30 16:09
Created by Administrator XWiki on 2020/06/05 12:18

Shortcuts

Recent changes
•  Service Manager
•  Service Apps
•  Self Help

Glossary

Powered by XWiki ©, EasyVista 2020