Service Request Template - Recipient Manager Validation

Last modified on 2023/07/13 17:03

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 Service request with Recipient Manager Approval template is used to process a service request throughout its life cycle. Initial approval is performed by the recipient's manager.

It is also used to manage multi-cart requests by creating the same number of requests as the number of items in the cart.

Operating principle

Variant: Approval by the recipient's department manager

If the recipient's manager is unspecified or if it has changed, initial approval of the request can be performed by the recipient's department manager.

  • A specific workflow should be used for this type of approval: [SERVICE] New Request | Approval by Dprt. Manager.
  • This workflow is identical to the one used for approval by the recipient's manager. The only difference is in the initial approval steps. The Recipient's Manager role will be replaced with the Recipient's Department Manager role.

Notes

  • In the standard configuration, the multi-cart option is disabled: All cart items will be added to the parent service request and managed by the workflow defined in the main catalog.
  • Languages L1 to L6 are not supported in the multilingual mechanisms. English is the default language.

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 workflow

Procedure: How to install and configure the template

Step 1: Import the package.

1. Download the file below to your workstation.

  Validation manager package

2. Extract the RAR file in a folder.

The (exp)_incident_template_timeline_full_emails_via_wf.zip file contains all the EXP files.
         RAR file - Contents 1.png  RAR file - Contents 2.png

3. Import the file called (exp)_request_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 rule:

  • (BR) Create child service request | Multi-cart
  • (BR) Automatic closing of tickets | REST

1. Declare the REST connection.

Notes:

  • The service and resources used by processes associated with business rules have already been configured. You only need to create the connection.
  • The connection is already created if you installed an Incident template.
  • 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 (BR) Create child service request | Multi-cart business rule.

  • Select Administration > Business Rules > Related Processes in the menu.
  • Edit the (BR) Create child service request | Multi-cart process.
    The graphic editor will appear.
  • Double-click the Create child service requests step.
  • Select the EasyVista connection and the New Request resource.
  • Specify the parameters. Open url.png See the configuration of the business rule.

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

Step 3 (optional): Configure the mail component for approving or rejecting the validation of the request.

Note: The configuration is already defined if you installed an Incident template.

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

[SERVICE] New Request | Approval by User Manager

     Open url.png See the configuration.

Workflow - New request with manager validation.png

Zoom

  • This workflow processes all service requests throughout their life cycle:
    • Approval by the recipient's manager
    • Financial approval
    • Logistics step (orders, taking items from stock)
    • Request processing
    • User approval
    • User rejection and information requests

[SERVICE] New Request | Approval by Dprt. Manager

     Open url.png See the configuration.

Workflow - New request with department manager validation.png

Zoom

  • This workflow processes all service requests throughout their life cycle:
    • Approval by the recipient's department manager
    • Financial approval
    • Logistics step (orders, taking items from stock)
    • Request processing
    • User approval
    • User rejection and information requests

[SERVICE] Multi-cart using a business rule

Workflow - Multi cart via business rule.png

Zoom

  • This workflow generates an empty parent request.
  • No configuration is required.

Business rules

(BR) Create child service request | Multi-cart

     Open url.png See the configuration.

BR - Child request generation - Multi cart.png

Zoom

  • This business rule is used to generate one service request for each item in the cart.
  • Each service request is managed using the workflow defined for the entry in the Service Request Catalog instead of the workflow in the main catalog.
  • Each request may require different levels of approval:
    • Approval only by the recipient's manager
    • Approval by the recipient's manager and by the financial manager

(BR) Update request created from Apps

     Open url.png See the configuration.

BR - Update request created from Apps.png

Zoom

  • This business rule is used to create the new Service Manager service request using data coming from the request created via the Service Apps user portal.
  • The service request starts upon completion of the Standard Request questionnaire via the Service Apps user portal.
  • The following data will be synchronized: recipient, urgency, description, location, department.

(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 coming from the cart

     Open url.png See the configuration.

BR - Merge questions from cart.png

Zoom

Note: Optional business rule

  • This business rule is used to merge all questions and answers from all child requests (items in the cart) in a single field.
  • Values are stored in five languages, i.e. EN, FR, IT, PO and SP. 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 request 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) 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.

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.

Configuration of template components

Workflows

[SERVICE] New Request | Approval by User Manager

     Open url.png See the description.
         Workflow - New request with manager validation.png

Configuration of the process

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

Workflow - New request with manager validation - step 1.png

Zoom

Objective: Send one email only when the service request 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 - New request with manager validation - step 2.png

Zoom

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

 

(3) Step Name: Manager Approval?

Workflow - New request with manager validation - step 3.png

Zoom

Objective: Determine if the recipient has a manager different from the recipient.

SELECT REQUEST_ID, RFC_NUMBER, b.LAST_NAME, c.LAST_NAME
FROM SD_REQUEST a
INNER JOIN AM_EMPLOYEE b ON a.RECIPIENT_ID = b.EMPLOYEE_ID
INNER JOIN AM_EMPLOYEE c ON b.MANAGER_ID = c.EMPLOYEE_ID
WHERE REQUEST_ID = @@ID@@
AND a.RECIPIENT_ID <> b.MANAGER_ID;

 

(4) Step Name: Manager Approval

Workflow - New request with manager validation - step 4.png

Zoom

  • Action Type: Self Service Approval
  • Role: @Recipient Manager
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Could you please approve the following service request (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_MANAGER_VALIDATION]#
    #[MAIL_PART._FOOTER]#

 

(5) Step Name: Manager Validation: Approved (RISK_DESCRIPTION)

Workflow - New request with manager validation - step 5.png

Zoom

Objective: Retrieve the comment made by the recipient's manager when approving the request.

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) AND DESCRIPTION is not null AND DESCRIPTION not like '%@OPERATION@%') A
INNER JOIN AM_ACTION B ON A.MAX_ACTION_ID = B.ACTION_ID)
WHERE REQUEST_ID = @@ID@@;

 

(6) Step Name: Manager Validation: Approved

Workflow - New request with manager validation - step 6.png

Zoom

  • Action Type: Send Email
  • Role: @Recipient
  • CC: @Requestor
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] The service request has been approved by your manager (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_MANAGER_VALIDATION_OK]#
    #[MAIL_PART._FOOTER]#

 

(7) Step Name: More info required: Manager (RISK_DESCRIPTION)

Workflow - New request with manager validation - step 7.png

Zoom

Objective: Retrieve the comment made by the recipient's manager for an information request.

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) AND DESCRIPTION is not null AND DESCRIPTION not like '%@OPERATION@%') A
INNER JOIN AM_ACTION B ON A.MAX_ACTION_ID = B.ACTION_ID)
WHERE REQUEST_ID = @@ID@@;

 

(8) Step Name: More info required: Manager -> Recipient

Workflow - New request with manager validation - step 8.png

Zoom

  • Action Type: Send Email
  • Role: @Recipient
  • CC: @Requestor
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Your manager "#RECIPIENT_MANAGER#" has asked for more information (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_MORE_INFOS]#
    #[MAIL_PART._FOOTER]#

 

(9) Step Name: Manager Validation: Denied

Workflow - New request with manager validation - step 9.png

Zoom

Objective: Retrieve the comment made by the recipient's manager when denying the request.

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) AND DESCRIPTION is not null AND DESCRIPTION not like '%@OPERATION@%') A
INNER JOIN AM_ACTION B ON A.MAX_ACTION_ID = B.ACTION_ID)
WHERE REQUEST_ID = @@ID@@;

 

(10) Step Name: Manager Validation: Denied

Workflow - New request with manager validation - step 10.png

Zoom

  • Action Type: Send Email
  • Role: @Recipient
  • CC: @Requestor
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] The service request has been denied by your manager (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_MANAGER_VALIDATION_KO]#
    #[MAIL_PART._FOOTER]#

 

(11) Step Name: Approval denied

Workflow - New request with manager validation - step 11.png

Zoom

  • The workflow will stop.
  • The status of the request will become Rejected.

 

(12) Step Name: Financial validation?

Workflow - New request with manager validation - step 12.png

Zoom

Objective: Determine if the recipient has a financial manager different from the recipient.

SELECT REQUEST_ID, RFC_NUMBER, b.LAST_NAME, c.LAST_NAME
FROM SD_REQUEST a
INNER JOIN AM_EMPLOYEE b ON a.RECIPIENT_ID = b.EMPLOYEE_ID
INNER JOIN AM_EMPLOYEE c ON b.VALIDATOR_ID = c.EMPLOYEE_ID
WHERE REQUEST_ID = @@ID@@
AND a.RECIPIENT_ID <> b.VALIDATOR_ID;

Note: You can define a threshold below which financial approval is not required. To do so, you should add a condition to the end of the SQL condition.

example Require financial approval only if the amount exceeds €500

SELECT REQUEST_ID, RFC_NUMBER, b.LAST_NAME, c.LAST_NAME , d.NET_PRICE

FROM SD_REQUEST a

INNER JOIN AM_EMPLOYEE b ON a.RECIPIENT_ID = b.EMPLOYEE_ID

INNER JOIN AM_EMPLOYEE c ON b.VALIDATOR_ID = c.EMPLOYEE_ID

INNER JOIN SD_CATALOG d ON a.SD_CATALOG_ID = d.SD_CATALOG_ID

WHERE REQUEST_ID = @@ID@@

AND a.RECIPIENT_ID <> b.VALIDATOR_ID AND d.NET_PRICE >= 500;


 

(13) Step Name: Financial Approval

Workflow - New request with manager validation - step 13.png

Zoom

  • Action Type: Self Service Approval
  • Role: @Manager for Financial Approval
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Could you please approve the following service request (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_MANAGER_VALIDATION]#
    #[MAIL_PART._FOOTER]#

 

(14) Step Name: Financial validation: Approved (RISK_DESCRIPTION)

Workflow - New request with manager validation - step 14.png

Zoom

Objective: Retrieve the comment made by the recipient's financial manager when approving the request.

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) AND DESCRIPTION is not null AND DESCRIPTION not like '%@OPERATION@%') A
INNER JOIN AM_ACTION B ON A.MAX_ACTION_ID = B.ACTION_ID)
WHERE REQUEST_ID = @@ID@@;

 

(15) Step Name: Financial validation: Approved

Workflow - New request with manager validation - step 15.png

Zoom

  • Action Type: Send Email
  • Role: @Recipient
  • CC: @Requestor
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] The service request has been approved by the financial manager (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_MANAGER_VALIDATION_OK]#
    #[MAIL_PART._FOOTER]#

 

(16) Step Name: More info required: Financial validation (RISK_DESCRIPTION)

Workflow - New request with manager validation - step 16.png

Zoom

Objective: Retrieve the comment made by the recipient's financial manager for an information request.

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) AND DESCRIPTION is not null AND DESCRIPTION not like '%@OPERATION@%') A
INNER JOIN AM_ACTION B ON A.MAX_ACTION_ID = B.ACTION_ID)
WHERE REQUEST_ID = @@ID@@;

 

(17) Step Name: More info required: Financial validation

Workflow - New request with manager validation - step 17.png

Zoom

  • Action Type: Send Email
  • Role: @Recipient
  • CC: @Requestor
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] The financial manager "#FINANCIAL_VALIDATOR#" has asked for more information (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_MORE_INFOS]#
    #[MAIL_PART._FOOTER]#

 

(18) Step Name: Financial validation: Denied

Workflow - New request with manager validation - step 18.png

Zoom

Objective: Retrieve the comment made by the recipient's financial manager when denying the request.

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) AND DESCRIPTION is not null AND DESCRIPTION not like '%@OPERATION@%') A
INNER JOIN AM_ACTION B ON A.MAX_ACTION_ID = B.ACTION_ID)
WHERE REQUEST_ID = @@ID@@;

 

(19) Step Name: Financial validation: Denied

Workflow - New request with manager validation - step 19.png

Zoom

  • Action Type: Send Email
  • Role: @Recipient
  • CC: @Requestor
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] The service request has been denied by the financial manager (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_MANAGER_VALIDATION_KO]#
    #[MAIL_PART._FOOTER]#

 

(20) Step Name: Approval denied

Workflow - New request with manager validation - step 20.png

Zoom

  • The workflow will stop.
  • The status of the request will become Rejected.

 

(21) Step Name: Logistics?

Workflow - New request with manager validation - step 21.png

Zoom

Objective: Determine if a logistics step is required. This is used to take items from stock if they are available and to place an order if they are not. Open url.png See the life cycle.

SELECT REQUEST_ID, c.AM_CATALOG_ID, d.CAN_BE_PURCHASED, d.NO_LOGISTIC
FROM SD_REQUEST a
INNER JOIN SD_CATALOG b ON a.SD_CATALOG_ID = b.SD_CATALOG_ID
INNER JOIN SD_CATALOG_AM_CATALOG c ON b.SD_CATALOG_ID = c.SD_CATALOG_ID
INNER JOIN AM_CATALOG d on c.AM_CATALOG_ID = d.CATALOG_ID
WHERE REQUEST_ID = @@ID@@
AND d.CAN_BE_PURCHASED = 1 AND (d.NO_LOGISTIC = 0 or d.NO_LOGISTIC is null);

Note: You can define a threshold above which financial approval is required. To do so, you should add a condition to the end of the SQL condition.

example Require financial approval only if the amount exceeds €1000

SELECT REQUEST_ID, c.AM_CATALOG_ID, d.CAN_BE_PURCHASED, d.NO_LOGISTIC , b.NET_PRICE

FROM SD_REQUEST a

INNER JOIN SD_CATALOG b ON a.SD_CATALOG_ID = b.SD_CATALOG_ID

INNER JOIN SD_CATALOG_AM_CATALOG c ON b.SD_CATALOG_ID = c.SD_CATALOG_ID

INNER JOIN AM_CATALOG d on c.AM_CATALOG_ID = d.CATALOG_ID

WHERE REQUEST_ID = @@ID@@

AND d.CAN_BE_PURCHASED = 1 AND (d.NO_LOGISTIC = 0 or d.NO_LOGISTIC is null) AND b.NET_PRICE >= 1000;


 

(22) Step Name: Logistics

Workflow - New request with manager validation - step 22.png

Zoom

  • Action Type: Logistics
  • Role: @Catalog Group
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Logistics step to be processed for "#RECIPIENT#" (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_PROCESS]#
    #[MAIL_PART._FOOTER]#

 

(23) Step Name: Installation

Workflow - New request with manager validation - step 23.png

Zoom

  • Action Type: Installation Operation
  • Role: @Catalog Group
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Installation step to be processed for "#RECIPIENT#" (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_PROCESS]#
    #[MAIL_PART._FOOTER]#

 

(24) Step Name: Processing the request

Workflow - New request with manager validation - step 24.png

Zoom

  • Action Type: Operation Action
  • Role: @Catalog Group
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] New service request to be processed for "#RECIPIENT#" (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_PROCESS]#
    #[MAIL_PART._FOOTER]#

 

(25) 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 (28). To do this, you should hide all of the fields in the step. Open url.png See the configuration of wizards.

Workflow - New request with manager validation - step 25.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 (26) and (28).

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

 

(26) Step Name: More info (RISK_DESCRIPTION)

Workflow - New request with manager validation - step 26.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@@;

 

(27) Step Name: Approval rejected (RISK_DESCRIPTION)

Workflow - New request with manager validation - step 27.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@@;

 

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

Workflow - New request with manager validation - step 28.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]#

 

(29) Step Name: Processing end user rejection

Workflow - New request with manager validation - step 29.png

Zoom

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

 

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

Workflow - New request with manager validation - step 30.png

Zoom

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

[SERVICE] New Request | Approval by Dprt. Manager

     Open url.png See the description.
         Workflow - New request with department manager validation.png

The steps in this workflow are identical to those in the workflow used for approval by the recipient's manager. The only difference is in the initial approval steps. The Recipient's Manager role will be replaced with the Recipient's Department Manager role.

Configuration of the process

(3) Step Name: Department Manager Approval?

Workflow - New request with department manager validation - step 3.png

Zoom

Objective: Determine if the recipient has a department manager different from the recipient.

SELECT REQUEST_ID, RFC_NUMBER, c.LAST_NAME, d.LAST_NAME
FROM SD_REQUEST a
INNER JOIN AM_DEPARTMENT b ON a.DEPARTMENT_ID = b.DEPARTMENT_ID
INNER JOIN AM_EMPLOYEE c ON a.REQUESTOR_ID = c.EMPLOYEE_ID
INNER JOIN AM_EMPLOYEE d ON b.MANAGER_ID = d.EMPLOYEE_ID
WHERE REQUEST_ID = @@ID@@
AND a.REQUESTOR_ID <> b.MANAGER_ID;

 

(4) Step Name: Department Manager Approval

Workflow - New request with department manager validation - step 4.png

Zoom

  • Action Type: Self Service Approval
  • Role: @Recipient Department Manager
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Could you please approve the following service request (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_MANAGER_VALIDATION]#
    #[MAIL_PART._FOOTER]#

 

(5) Step Name: Department Manager Validation: Approved (RISK_DESCRIPTION)

Workflow - New request with manager validation - step 5.png

Zoom

Objective: Retrieve the comment made by the recipient's department manager when approving the request.

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) AND DESCRIPTION is not null AND DESCRIPTION not like '%@OPERATION@%') A
INNER JOIN AM_ACTION B ON A.MAX_ACTION_ID = B.ACTION_ID)
WHERE REQUEST_ID = @@ID@@;

 

(6) Step Name: Manager Validation: Approved

Workflow - New request with manager validation - step 6.png

Zoom

  • Action Type: Send Email
  • Role: @Recipient
  • CC: @Requestor
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] The service request has been approved by your manager (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_MANAGER_VALIDATION_OK]#
    #[MAIL_PART._FOOTER]#

 

(7) Step Name: More info required: Manager Validation

Workflow - New request with manager validation - step 7.png

Zoom

Objective: Retrieve the comment made by the recipient's department manager for an information request.

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) AND DESCRIPTION is not null AND DESCRIPTION not like '%@OPERATION@%') A
INNER JOIN AM_ACTION B ON A.MAX_ACTION_ID = B.ACTION_ID)
WHERE REQUEST_ID = @@ID@@;

 

(8) Step Name: More info required: Manager -> Recipient

Workflow - New request with manager validation - step 8.png

Zoom

  • Action Type: Send Email
  • Role: @Recipient
  • CC: @Requestor
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] Your manager "#RECIPIENT_MANAGER#" has asked for more information (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_MORE_INFOS]#
    #[MAIL_PART._FOOTER]#

 

(9) Step Name: Manager Validation: Denied

Workflow - New request with manager validation - step 9.png

Zoom

Objective: Retrieve the comment made by the recipient's department manager when denying the request.

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) AND DESCRIPTION is not null AND DESCRIPTION not like '%@OPERATION@%') A
INNER JOIN AM_ACTION B ON A.MAX_ACTION_ID = B.ACTION_ID)
WHERE REQUEST_ID = @@ID@@;

 

(10) Step Name: Manager Validation: Denied

Workflow - New request with manager validation - step 10.png

Zoom

  • Action Type: Send Email
  • Role: @Recipient
  • CC: @Requestor
  • Return Address: easyvista@no-reply.com
  • Subject: [EasyVista] The service request has been denied by your manager (#RFC_NUMBER#)
  • Message:
    #[MAIL_PART._HEADER]#
    #[MAIL_PART._BODY_MANAGER_VALIDATION_KO]#
    #[MAIL_PART._FOOTER]#

 

(11) Step Name: Approval denied

Workflow - New request with department manager validation - step 11.png

Zoom

  • The workflow will stop.
  • The status of the request will become Rejected.

Business rules

(BR) Create child service request | Multi-cart

     Open url.png See the description.
         BR - Child request generation - Multi cart.png

Configuration of the process

(1) Step Name: Log RFC_NUMBER

BR - Child request generation - Multi cart - step 1.png

Zoom

Objective: Retrieve the number of the request.

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) Step Name: Variable (REQUESTOR)

BR - Child request generation - Multi cart - step 2.png

Zoom

Objective: Retrieve the email of the requestor.

SELECT TOP 1 E_MAIL
FROM AM_ACTION INNER JOIN SD_REQUEST
ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
INNER JOIN AM_EMPLOYEE
ON SD_REQUEST.REQUESTOR_ID = AM_EMPLOYEE.EMPLOYEE_ID
WHERE AM_ACTION.ACTION_ID = @@ID@@;

 

(3) Step Name: Variable (RECIPIENT)

BR - Child request generation - Multi cart - step 3.png

Zoom

Objective: Retrieve the email of the recipient.

SELECT TOP 1 E_MAIL
FROM AM_ACTION INNER JOIN SD_REQUEST
ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
INNER JOIN AM_EMPLOYEE
ON SD_REQUEST.RECIPIENT_ID = AM_EMPLOYEE.EMPLOYEE_ID
WHERE AM_ACTION.ACTION_ID = @@ID@@;

 

(4) Step Name: Records left to be processed? (CODE)

BR - Child request generation - Multi cart - step 4.png

Zoom

Objective: Determine the number of child requests to be generated based on the number of items in the cart.

SELECT CODE FROM SD_CATALOG
WHERE SD_CATALOG_ID IN
(SELECT TOP 1 SD_CATALOG_ID
FROM SD_REQUEST_DETAIL
WHERE REQUEST_ID = (SELECT TOP 1 REQUEST_ID FROM AM_ACTION WHERE ACTION_ID = @@ID@@)
AND AVAILABLE_FIELD_1 IS NULL AND CATALOG_ID IS NULL
ORDER BY REQUEST_DETAIL_ID); 

 

(5) Step Name: Create child service requests

BR - Child request generation - Multi cart - step 5.png

Zoom

Objective: Pass the variables from the previous steps to the REST web service for creating requests.
  • Catalog code
  • Requestor's email
  • Recipient's email
  • Request number
  • Origin = 7 = web service

You configure the New Request (cart) resource by selecting Administration > REST > Resources.

{
    "requests" :
    [{
      "Catalog_Code" : "{catalog_code}",
      "Requestor_Mail" : "{requestor_mail}",
      "Recipient_Mail" : "{recipient_mail}",
      "Origin" : "{origin}",
      "ParentRequest" : "{parent_request}"
    }]
}

BR - Child request generation - Multi cart - step 5 - Resource.png


 

(6) Step Name: Flag SD_REQUEST_DETAIL 

BR - Child request generation - Multi cart - step 6.png

Zoom

Objective: Indicate whether the request has been processed in the REST output step.

UPDATE SD_REQUEST_DETAIL SET AVAILABLE_FIELD_1 = 'Done'
WHERE REQUEST_DETAIL_ID IN
(SELECT TOP 1 REQUEST_DETAIL_ID FROM SD_REQUEST_DETAIL
WHERE REQUEST_ID = (SELECT TOP 1 REQUEST_ID FROM AM_ACTION WHERE ACTION_ID = @@ID@@) AND AVAILABLE_FIELD_1 IS NULL
AND CATALOG_ID IS NULL ORDER BY REQUEST_DETAIL_ID);

 

(7) Step Name: Variable (WS_REOPEN_HEADER)

BR - Child request generation - Multi cart - step 7.png

Zoom

Objective: Determine the language to be used to indicate that the parent request was automatically closed once all child requests were generated.

SELECT CASE
WHEN LANGUAGE_ID = 1 THEN (SELECT TAG_LABEL_EN FROM SD_MAIL_TAG WHERE TAG = '_WS_CLOSE_HEADER')
WHEN LANGUAGE_ID = 2 THEN (SELECT TAG_LABEL_FR FROM SD_MAIL_TAG WHERE TAG = '_WS_CLOSE_HEADER')
WHEN LANGUAGE_ID = 3 THEN (SELECT TAG_LABEL_SP FROM SD_MAIL_TAG WHERE TAG = '_WS_CLOSE_HEADER')
WHEN LANGUAGE_ID = 4 THEN (SELECT TAG_LABEL_GE FROM SD_MAIL_TAG WHERE TAG = '_WS_CLOSE_HEADER')
WHEN LANGUAGE_ID = 5 THEN (SELECT TAG_LABEL_IT FROM SD_MAIL_TAG WHERE TAG = '_WS_CLOSE_HEADER')
WHEN LANGUAGE_ID = 6 THEN (SELECT TAG_LABEL_PO FROM SD_MAIL_TAG WHERE TAG = '_WS_CLOSE_HEADER')
ELSE (SELECT TAG_LABEL_EN FROM SD_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 SD_REQUEST.RFC_NUMBER = '#[VAR.RFC_NUMBER]#';

 

(8) Step Name: SQL_CloseRequest (Parent Service Request)

BR - Child request generation - Multi cart - step 8.png

Zoom

Objective: Close the parent request using SQL queries.

Note: When you use SQL queries instead of a REST step, this will only close the parent request and not the child requests.

UPDATE SD_WORKFLOW_INSTANCE
SET SD_WORKFLOW_INSTANCE.END_DATE = isnull(END_DATE, getutcdate())
FROM SD_WORKFLOW_INSTANCE
INNER JOIN AM_ACTION ON SD_WORKFLOW_INSTANCE.REQUEST_ID = AM_ACTION.REQUEST_ID
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
WHERE SD_REQUEST.RFC_NUMBER = '#[VAR.RFC_NUMBER]#';

UPDATE AM_ACTION
SET
AM_ACTION.STATUS_ID_ON_TERMINATE = 8,
AM_ACTION.START_DATE_UT = isnull(AM_ACTION.START_DATE_UT, getutcdate()),
AM_ACTION.END_DATE_UT   = isnull(AM_ACTION.END_DATE_UT, getutcdate()),
AM_ACTION.DESCRIPTION   = '#[VAR.WS_CLOSE_HEADER]#'
FROM AM_ACTION
INNER JOIN SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID
WHERE SD_REQUEST.RFC_NUMBER = '#[VAR.RFC_NUMBER]#';

UPDATE SD_REQUEST
SET
SD_REQUEST.STATUS_ID   = 8,
SD_REQUEST.END_DATE_UT = isnull(SD_REQUEST.END_DATE_UT, getutcdate()),
SD_REQUEST.DESCRIPTION = '#[VAR.WS_CLOSE_HEADER]#',
SD_REQUEST.COMMENT     = '#[VAR.WS_CLOSE_HEADER]#'
FROM SD_REQUEST
WHERE SD_REQUEST.RFC_NUMBER = '#[VAR.RFC_NUMBER]#'

 

(9) Step Name: Records left to be processed? (CATALOG_ID)

BR - Child request generation - Multi cart - step 9.png

Zoom

Objective: Determine the child requests where questionnaires should be propagated.

SELECT SD_CATALOG_ID FROM SD_CATALOG
WHERE SD_CATALOG_ID IN
(SELECT TOP 1 SD_CATALOG_ID
FROM SD_REQUEST_DETAIL
WHERE REQUEST_ID = (SELECT TOP 1 REQUEST_ID FROM AM_ACTION WHERE ACTION_ID = @@ID@@)
AND AVAILABLE_FIELD_2 IS NULL AND CATALOG_ID IS NULL
ORDER BY REQUEST_DETAIL_ID);

 

(10) Step Name: Variable (REQUEST_ID)

BR - Child request generation - Multi cart - step 10.png

Zoom

Objective: Retrieve and store the REQUEST_ID field in a variable.

SELECT TOP 1 b.REQUEST_ID
FROM SD_REQUEST_DETAIL a
INNER JOIN SD_REQUEST b ON a.REQUEST_ID = b.PARENT_REQUEST_ID
WHERE b.SD_CATALOG_ID = '#[VAR.CATALOG_ID]#'
AND a.REQUEST_ID = (SELECT TOP 1 REQUEST_ID FROM AM_ACTION WHERE ACTION_ID = @@ID@@);

 

(11) Step Name: Insert questions on child service requests (STEP1)

BR - Child request generation - Multi cart - step 11.png

Zoom

Objective: Insert records in the SD_REQUEST_DETAIL table by passing the REQUEST_ID and CATALOG_ID variables in parameters.

INSERT SD_REQUEST_DETAIL (REQUEST_ID, SD_CATALOG_ID, QTY_REQUESTED, CHARGE_BACK, MONTHLY_CHARGE_BACK, DESTOCKING_LOCATION_ID, AVAILABLE_FIELD_1)
SELECT '#[VAR.REQUEST_ID]#' AS REQUEST_ID, SD_CATALOG_ID, QTY_REQUESTED, CHARGE_BACK, MONTHLY_CHARGE_BACK, DESTOCKING_LOCATION_ID, AVAILABLE_FIELD_1
FROM SD_REQUEST_DETAIL
WHERE REQUEST_ID = (SELECT TOP 1 REQUEST_ID FROM AM_ACTION WHERE ACTION_ID = @@ID@@) AND SD_CATALOG_ID = '#[VAR.CATALOG_ID]#';

 

(12) Step Name: Variable (REQUEST_DETAIL_ID)

BR - Child request generation - Multi cart - step 12.png

Zoom

Objective: Retrieve and store the REQUEST_DETAIL_ID field in a variable.

SELECT REQUEST_DETAIL_ID FROM SD_REQUEST_DETAIL
WHERE REQUEST_ID = '#[VAR.REQUEST_ID]#' AND SD_CATALOG_ID = '#[VAR.CATALOG_ID]#';

 

(13) Step Name: Insert questions on child service requests (STEP2) 

BR - Child request generation - Multi cart - step 13.png

Zoom

Objective: Propagate the questions defined to child requests when creating the parent request.

INSERT SD_QUESTION_RESULT_DETAIL (REQUEST_DETAIL_ID, QUESTION_ID, RESULT, RESULT_STRING_FR, RESULT_DATE, RESULT_NUMBER, RESULT_BIT)
SELECT '#[VAR.REQUEST_DETAIL_ID]#' AS REQUEST_DETAIL_ID, QUESTION_ID, RESULT, RESULT_STRING_FR, RESULT_DATE, RESULT_NUMBER, RESULT_BIT
FROM SD_QUESTION_RESULT_DETAIL WHERE REQUEST_DETAIL_ID = (SELECT REQUEST_DETAIL_ID FROM SD_REQUEST_DETAIL WHERE REQUEST_ID = (SELECT TOP 1 REQUEST_ID FROM AM_ACTION WHERE ACTION_ID = @@ID@@) AND SD_CATALOG_ID = '#[VAR.CATALOG_ID]#' AND CATALOG_ID is NULL)
AND QUESTION_ID IN (SELECT QUESTION_ID FROM SD_QUESTION_RESULT_DETAIL WHERE REQUEST_DETAIL_ID = (SELECT REQUEST_DETAIL_ID FROM SD_REQUEST_DETAIL WHERE REQUEST_ID = (SELECT TOP 1 REQUEST_ID FROM AM_ACTION WHERE ACTION_ID = @@ID@@)  AND SD_CATALOG_ID = '#[VAR.CATALOG_ID]#' AND CATALOG_ID is NULL));

 

(14) Step Name: Flag (SD_REQUEST_DETAIL)

BR - Child request generation - Multi cart - step 14.png

Zoom

Objective: Flag each processed row when propagating questions to child requests.

UPDATE SD_REQUEST_DETAIL
SET AVAILABLE_FIELD_2 = 'Done'
WHERE REQUEST_ID = (SELECT TOP 1 REQUEST_ID FROM AM_ACTION WHERE ACTION_ID = @@ID@@) AND SD_CATALOG_ID = '#[VAR.CATALOG_ID]#';

 

(15) Step Name: Flag (SD_REQUEST_DETAIL)

BR - Child request generation - Multi cart - step 15.png

Zoom

Objective: Load the keyword, Merge, to the SD_REQUEST_DETAIL.AVAILABLE_FIELD_2 available field in order to trigger the (BR) Merging all the questions coming from the cart business rule.

UPDATE SD_REQUEST_DETAIL
SET AVAILABLE_FIELD_2 = 'Merge'
WHERE REQUEST_ID in (
SELECT REQUEST_ID FROM SD_REQUEST
WHERE PARENT_REQUEST_ID in
(SELECT a.REQUEST_ID FROM SD_REQUEST a INNER JOIN AM_ACTION b ON a.REQUEST_ID = b.REQUEST_ID
WHERE ACTION_ID = @@ID@@)
) AND AVAILABLE_FIELD_2 is NULL;

Conditions for triggering the business rule

BR - Child request generation - Multi cart - Trigger condition.png

Zoom

  • Trigger: AM_ACTION.END_DATE_UT | On Insert

IF EXISTS (SELECT ACTION_ID FROM INSERTED
INNER JOIN SD_REQUEST ON INSERTED.REQUEST_ID = SD_REQUEST.REQUEST_ID
INNER JOIN SD_CATALOG ON SD_CATALOG.SD_CATALOG_ID = SD_REQUEST.SD_CATALOG_ID
INNER JOIN AM_REFERENCE SD_CATALOG_REF ON SD_CATALOG.CATEGORY_ID = SD_CATALOG_REF.REFERENCE_ID
WHERE ACTION_TYPE_ID in (24)
AND SD_CATALOG_REF.REFERENCE_GUID = 'DAD06D4C-D4A1-4F26-B715-7FB8D3E88B71')
BEGIN
@@FIRETRIGGER@@
END

(BR) Update request created from Apps

     Open url.png See the description.
         BR - Update request created from Apps.png

Configuration of the process

(1) Step Name: Log RFC_NUMBER

BR - Update request created from Apps - step 1.png

Zoom

Objective: Retrieve the number of the request.

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) Step Name: Update service request

BR - Update request created from Apps - step 2.png

Zoom

Objective: Update the recipient, urgency, description, location and department using data coming from the request created via the Service Apps user portal.

UPDATE SD_REQUEST
SET RECIPIENT_ID = RESULT
FROM SD_REQUEST
INNER JOIN AM_ACTION ON SD_REQUEST.REQUEST_ID = AM_ACTION.REQUEST_ID
INNER JOIN V_SD_QUESTION_RESULT ON SD_REQUEST.REQUEST_ID = V_SD_QUESTION_RESULT.REQUEST_ID
INNER JOIN SD_QUESTION ON V_SD_QUESTION_RESULT.QUESTION_ID = SD_QUESTION.QUESTION_ID
WHERE QUESTION_CODE = 'recipient' AND AM_ACTION.ACTION_ID = @@ID@@;

UPDATE SD_REQUEST
SET URGENCY_ID = RESULT
FROM SD_REQUEST
INNER JOIN AM_ACTION ON SD_REQUEST.REQUEST_ID = AM_ACTION.REQUEST_ID
INNER JOIN V_SD_QUESTION_RESULT ON SD_REQUEST.REQUEST_ID = V_SD_QUESTION_RESULT.REQUEST_ID
INNER JOIN SD_QUESTION ON V_SD_QUESTION_RESULT.QUESTION_ID = SD_QUESTION.QUESTION_ID
WHERE QUESTION_CODE = 'urgency' AND AM_ACTION.ACTION_ID = @@ID@@;

UPDATE SD_REQUEST
SET COMMENT = RESULT
FROM SD_REQUEST
INNER JOIN AM_ACTION ON SD_REQUEST.REQUEST_ID = AM_ACTION.REQUEST_ID
INNER JOIN V_SD_QUESTION_RESULT ON SD_REQUEST.REQUEST_ID = V_SD_QUESTION_RESULT.REQUEST_ID
INNER JOIN SD_QUESTION ON V_SD_QUESTION_RESULT.QUESTION_ID = SD_QUESTION.QUESTION_ID
WHERE QUESTION_CODE = 'description' AND AM_ACTION.ACTION_ID = @@ID@@;

UPDATE SD_REQUEST
SET LOCATION_ID = AM_EMPLOYEE.LOCATION_ID
FROM SD_REQUEST
INNER JOIN AM_ACTION ON SD_REQUEST.REQUEST_ID = AM_ACTION.REQUEST_ID
INNER JOIN V_SD_QUESTION_RESULT ON SD_REQUEST.REQUEST_ID = V_SD_QUESTION_RESULT.REQUEST_ID
INNER JOIN SD_QUESTION ON V_SD_QUESTION_RESULT.QUESTION_ID = SD_QUESTION.QUESTION_ID
INNER JOIN AM_EMPLOYEE ON V_SD_QUESTION_RESULT.RESULT = AM_EMPLOYEE.EMPLOYEE_ID
WHERE QUESTION_CODE = 'recipient' AND AM_ACTION.ACTION_ID = @@ID@@;

UPDATE SD_REQUEST
SET DEPARTMENT_ID = AM_EMPLOYEE.DEPARTMENT_ID
FROM SD_REQUEST
INNER JOIN AM_ACTION ON SD_REQUEST.REQUEST_ID = AM_ACTION.REQUEST_ID
INNER JOIN V_SD_QUESTION_RESULT ON SD_REQUEST.REQUEST_ID = V_SD_QUESTION_RESULT.REQUEST_ID
INNER JOIN SD_QUESTION ON V_SD_QUESTION_RESULT.QUESTION_ID = SD_QUESTION.QUESTION_ID
INNER JOIN AM_EMPLOYEE ON V_SD_QUESTION_RESULT.RESULT = AM_EMPLOYEE.EMPLOYEE_ID
WHERE QUESTION_CODE = 'recipient' AND AM_ACTION.ACTION_ID = @@ID@@;

Conditions for triggering the business rule

BR - Update request created from Apps - Trigger condition.png

Zoom

  • The rule is run when the recipient is updated upon completion of the Standard Request questionnaire via the Service Apps) user portal.
  • Trigger: AM_ACTION.END_DATE_UT | On Update

IF EXISTS
(SELECT ACTION_ID FROM INSERTED
WHERE ACTION_TYPE_ID in (24, 56) AND END_DATE_UT IS NOT NULL)
BEGIN
@@FIRETRIGGER@@
END

Standard request questionnaire.png Standard request questionnaire - Configuration.png

(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 coming from the cart

     Open url.png See the description.
         BR - Merge questions from cart.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 request is transferred, French Technical Support persons will see the questions in their language.

(1) Step Name: Variable (QUESTION)

BR - Merge questions from cart - 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
INNER JOIN SD_REQUEST_DETAIL ON SD_REQUEST.REQUEST_ID = SD_REQUEST_DETAIL.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.ACTION_END_DATE_UT is null AND SD_REQUEST_DETAIL.REQUEST_DETAIL_ID = @@ID@@;

 

(2) Step Name: Variable (ANSWER)

BR - Merge questions from cart - 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
INNER JOIN SD_REQUEST_DETAIL ON SD_REQUEST.REQUEST_ID = SD_REQUEST_DETAIL.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.ACTION_END_DATE_UT is null AND SD_REQUEST_DETAIL.REQUEST_DETAIL_ID = @@ID@@;

 

(3) Step Name: Variable (TRUE)

BR - Merge questions from cart - 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
INNER JOIN SD_REQUEST_DETAIL ON SD_REQUEST.REQUEST_ID = SD_REQUEST_DETAIL.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.ACTION_END_DATE_UT is null AND SD_REQUEST_DETAIL.REQUEST_DETAIL_ID = @@ID@@;

 

(4) Step Name: Variable (FALSE)

BR - Merge questions from cart - 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
INNER JOIN SD_REQUEST_DETAIL ON SD_REQUEST.REQUEST_ID = SD_REQUEST_DETAIL.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.ACTION_END_DATE_UT is null AND SD_REQUEST_DETAIL.REQUEST_DETAIL_ID = @@ID@@;

 

(5) Step Name: Variable (HOUR)

BR - Merge questions from cart - 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
INNER JOIN SD_REQUEST_DETAIL ON SD_REQUEST.REQUEST_ID = SD_REQUEST_DETAIL.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.ACTION_END_DATE_UT is null AND SD_REQUEST_DETAIL.REQUEST_DETAIL_ID = @@ID@@;

 

(6) Step Name: Variable (MINUTE)

BR - Merge questions from cart - 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
INNER JOIN SD_REQUEST_DETAIL ON SD_REQUEST.REQUEST_ID = SD_REQUEST_DETAIL.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.ACTION_END_DATE_UT is null AND SD_REQUEST_DETAIL.REQUEST_DETAIL_ID = @@ID@@;

 

(7) Step Name: Variable (FILE)

BR - Merge questions from cart - 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
INNER JOIN SD_REQUEST_DETAIL ON SD_REQUEST.REQUEST_ID = SD_REQUEST_DETAIL.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.ACTION_END_DATE_UT is null AND SD_REQUEST_DETAIL.REQUEST_DETAIL_ID = @@ID@@;

 

(8) Step Name: Variable (HEADER)

BR - Merge questions from cart - 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
INNER JOIN SD_REQUEST_DETAIL ON SD_REQUEST.REQUEST_ID = SD_REQUEST_DETAIL.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.ACTION_END_DATE_UT is null AND SD_REQUEST_DETAIL.REQUEST_DETAIL_ID = @@ID@@;

 

(9) Step Name: Update field DYNAMIC_DETAILS

BR - Merge questions from cart - 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 SD_REQUEST_DETAIL B ON A.REQUEST_ID = B.REQUEST_ID WHERE REQUEST_DETAIL_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 SD_REQUEST_DETAIL B ON A.REQUEST_ID = B.REQUEST_ID
INNER JOIN SD_QUESTION C ON A.QUESTION_ID = C.QUESTION_ID
WHERE REQUEST_DETAIL_ID = @@ID@@
AND VALUE_TYPE <> 'tbl_header' AND VALUE_TYPE <> 'comment'
AND RESULT_ORDER = @CPTE2),'')
SET @CPTE2 = @CPTE2+1
END

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

Conditions for triggering the business rule

BR - Merge questions from cart - Trigger condition.png

Zoom

  • The rule is run when the SD_REQUEST_DETAIL.AVAILABLE_FIELD_2 available field contains the keyword, Merge.
  • Trigger: SD_REQUEST_DETAIL.AVAILABLE_FIELD_2 | On Update

IF EXISTS (SELECT REQUEST_DETAIL_ID
FROM INSERTED
WHERE AVAILABLE_FIELD_2 = 'Merge')
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.

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

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

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.

Wizards

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

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 [SERVICE] Processing | Approval by User Manager 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