Author(s)

Verification

Approval

Perla Chagas
BO EU Data & Analysis Operator

Luís Dias
SRM Team Leader

XXXXXXX
BO Data & Analysis Process Leader
XXXXXX
Data & Analysis Process Expert

03.08.2018

Date

Date

Version

Date

Description

00

03.08.2018

New document

#

Main activities

Tips / Best practices

Key points

1

Execute the query

Don't insert any date

2

Select "Export"

3

Select "Spreadsheet" type

4

Click on "Select from All Available Formats" > select "Excel (in Office 2007 XLSX Format)

5

Confirm

6

Enter a name

Insert any name. Example: 03.08.2018

The previous extractions can be deleted. Just select the line and then the trash can

7

Select "Choose" button

#

Main activities

Tips / Best practices

Key points

8

Open de file

• The file will appear in a different window. Just close it after.
• Also, it will have a different name. This name doesn't matter as it will be changed after saved.

#

Main activities

Tips / Best practices

Key points

9

Go to File and select "Save As"

10

Search for "SC Automation" folder and select the file "Today-Extract"

Find on 2.2 how "to setup the folder SC Automation on Drive H"

11

Save

12

Select YES to replace the existent file

#

Main activities

Tips / Best practices

Key points

13

Go to Computer > Home Drive H and create a folder "SC Automation"

Never change the name of any file or folder

14

Save the files "MasterFile-SC", "Today-Extract" and "Region"

• MasterFile-SC: the macro to create the report;
  
  
  • Today-Extract: extraction file done previously via link;*
  • Region: contains the companies by region (file related to second reminder sent to Provisioning teams).*

15

Then create the folders "Attachments" and "Reports"

• "Attachments" is the folder where the attachments will be created to be sent after;
• "Reports" is the folders of previous reports done.

#

Main activities

Tips / Best practices

Key points

16

Open "MasterFile-SC.xlsm" and check "Current Day" and "Last Report"

• Current Day: this is programmed to appear the next day of reporting in 7 days after the last report. I must be the day which the report is been created;
• Last Report: make sure the file is correct as the last report ran.

17

Press "RUN" button

• The current report will be created in the folder "Reports" as "SC_DD-MM-YYYY.xlsx";
• The attachments (per approver) will be created in the folder "Attachments".

#

Main activities

Tips / Best practices

Key points

18

Don't insert any recipient

19

Subject: Shopping Cart pending approvals

20

Copy the layout below

Leave it as "saved" (draft)

#

Main activities

Tips / Best practices

Key points

21

Go to "Attachments" folder on Drive H and drop all the files in "Attachments" folder on GDrive

22

Go to "YAMM" sheet in the current report

YAMM: Yet Another Mail Merge (add-on of Google)

• Sheet 1: all the line items in need of action, including the past ones. This will be used to verify the file of the following week;
• Reminders: the line items that need action but don't have a reminder yet (which will be sent by email).
• Reminders' rules:
  
  
  Days from Creation > 15 AND Days to Delivery > 14
  
  Days from Creation > 10 AND Days to Delivery < 16
• YAMM: information needed to run YAMM and send the emails via GDrive.

23

Copy and paste all the lines to YAMM sheet on GDrive

Start the selection from the second one

To access this sheet on GDrive:
My Drive > SRM7 Team > ROUTINES > SC Approval Monitor > Automation

#

Main activities

Tips / Best practices

Key points

24

In YAMM sheet on GDrive, select "Add-ons" button

25

Go to "Yet Another Mail Merge" option

26

Select "Start Mail Merge" option

#

Main activities

Tips / Best practices

Key points

27

Name of the sender : PtP D&A

Setup done only once.

28

Email template: select the one created as draft

Setup done only once.

29

Unflag option "Track emails opened, clicked or bounced"

Setup done only once.

30

Click on "+Alias, filters, personalized attachments…"

Setup of sender email: done only once;
Setup of attachments: to do every time.

#

Main activities

Tips / Best practices

Key points

31

Send from: ptp-data.analysis@solvay.com

Setup done only once.

32

Flag the option "Attach files in column D to emails sent"

Setup done every time.

33

Click on "Import from Drive folder"

34

Select the folder "Attachments" of GDrive

Setup done every time.

35

Select the option "Attachment"

Setup done every time.

36

Click on "Import files"

Make sure that the quantity of imported attachments is the same of emails (rows).
The alert message will appear after importation.

It will creates a link (in column D) to the attachment and the file will be sent in the email as an excel file.

#

Main activities

Tips / Best practices

Key points

37

Click on "Back"

38

Send the emails

The emails will be sent and the tickets will be automatically created on Freshdesk

#

Main activities

Tips / Best practices

Key points

39

Click on "Settings"

40

Go to "Scenario Automations"

41

Select "New Scenario"

#

Main activities

Tips / Best practices

Key points

42

Scenario Name: "SC reminders" is an example

43

Select Action: "Set Status as" > Closed

44

Click on "Add new action";

Select Action: "Assign to Agent" > agent name

"Available for" will be by default for the agent.

45

Save

#

Main activities

Tips / Best practices

Key points

46

Make a filter in column "Priority Description"

47

Select the priorities "DO NOT USE", "Delivery follow up is required" and "Important follow-up is required"

The priorities have been updated in SRM7 tool.
For now, select the option "DO NOT USE" until further information.

48

Click ok

#

Main activities

Tips / Best practices

Key points

49

Check with previous report if there is any pending shopping cart that wasn't sent to Provisioning team previously

Selected in red on previous file: the Provisioning teams (by region) was already notified in order to contact the approver.

If the same shopping cart appears, don't send again to Provisioning.

50

Separate by company the shopping carts to be sent according to the region

NAM:

• • don't send SCs from Cytec;
  • SCs from Mexico send to Provisioning LAM.