SAPC Report Bot

Summary:

This automation has been created to pull reports of MCC revenue from SAPC. There are a total of 15 MCC's that will have reports pulled from them each day. The reports are then fed through an excel macro to create a pivot table and placed in a specific Box folder. For the first 7 days of each month, the bot will run 2 reports for each MCC, meaning there will be 30 reports pulled each day.

Process Owners: Jennifer Kosloski

Bot Owner(s): Zach Fitzgerald

Developers: Zach Fitzgerald

Relevant Systems:

• SAPC
• Excel
• Box

Business Continuity Plan: If Process fails, email to be sent to distribution list informing them that the process failed. The process should be retried once before alerting the business to run it manually. A ticket should then be created in DevOps for the bot issue to be investigated, fixed, tested, and redeployed according to change management practices.

Steps to run the process manually

1. Navigate and log in to https://aviy-prd.callidusondemand.com/SalesPortal/#!/ 
2. Navigate to reports tab on left side of screen.
3. Search for "Custom revenue report".
4. Click "open in new tab" when smaller preview window opens on right side.
5. Enter in start and end dates for the beginning and end of the month.
   1. If it is the first 7 days of the month then the report needs to be run twice, once for previous month and once for current month.
6. Unselect checkmark for all MCC's.
7. Select MCC you want to run report for.
8. Download report to excel using download button on top left of screen.
9. Open file in Excel.
10. Select all data and unmerge cells.
11. Delete column D.
12. Select all data in table and create pivot table.
13. Create PT as follows:
    1. Rows: Deal ID, Deal Name, Primary Broker Name, Compensation Date
    2. Columns: Broker Name
    3. Values: Brokers share of AY revenue (in primary brokers currency)
14. In PT creation click arrow beside values and change number format to currency.
15. Rename cell A4 to Deal ID.
16. Navigate to design tab in ribbon.
17. Select report layout and show in tabular form.
18. Select subtotals (do not show subtotals).
19. Adjust cell width to your liking.
20. Select cell E5 and freeze panes.
21. Open filter in cell A4 and select value filters and then "is not between". Enter -1 to 1.
22. Rename page to CRR Pivot table.
23. Upload file to Box folder for specific MCC. 

Expected Volume: 15 MCC's to run reports for. (30 reports for first 7 days of the month)

Credential Management: 

SLA: The process takes approximately 2 minutes per MCC to complete.  

Scheduling: No schedule, the process is ran on-demand by the end-user 

Triggers: The process is triggered by the end-user clicking the Excel Report button on Avant.

Queues:

Assets:(Shared Folder)

• SMTP From Email
• SMTP Port
• SMTP Host
• SMTP Password
• AGP Credentials
• MCCList

Reporting:

Distribution List: -List of people that the emails should be sent to from UiPath. This list is updated based upon who each MCC report is going to. The overall process finished email gets sent to [e-mail]

KPI's:

Runbook:

The runbook should provide information to support the bot in the case that any issue occurs.

If Bot Fails: An email will be sent saying the bot failed and what period failed to run. There are also messages being logged to the Orchestrator Tenant at the start and end of each major sequence to make it easy to see where the bot is failing.

DevOps Epic: https://dev.azure.com/AY-InnovationCenter/UiPath%20Automations/_workitems/edit/8203Automation Hub Link: