Proposal Summary
This proposal outlines the solution designed for automating the quarterly distribution of “Grant Certificates” to the relevant member associates via email. The grant certificates will be in PDF format and attached to the email.
Requirement
Client requires a statement of account for each MA pertaining to the financial year within a specified date range. This statement or certificate is intended to display the payments made to each MA throughout the year. As the MAs have email accounts in NetSuite, client wishes to automate the process of generating these certificates/reports and sending them via email quarterly and at the end of the year. Currently, this task is performed manually outside of the system, resulting in a significant amount of time being consumed. Client aims to implement this automated process for all MAs for every year.
Deliverables
- We will develop a schedule-based functionality that will be executed once every quarter to automatically send the “Grant Certificates” to the appropriate member associates.
- The execution of our script will be specifically scheduled for the following dates: April 20th, July 20th, October 20th, and January 20th. So, on these dates, the MA Grant Certificate will be sent to corresponding MAs.
- To ensure accuracy and confidentiality, each MA will only receive their own grant certificate. This means that the certificates will be personalized and delivered specifically to the intended recipients, maintaining privacy and individuality for each MA.
- On April 20th, the Grant Certificate will include the report of MAs from the first three months of the current year, spanning from January 1st to March 31st. On July 20th, the certificate will encompass the report from the first six months, up to June 30th. Similarly, on October 20th, the certificate will contain the report until September 30th, starting from January 1st. Lastly, on January 20th of the following year, the certificate will encompass all the report from the previous year.
- As part of our solution, we will create a custom PDF template for the MA Grant Certificate.
- Attached below is a sample MA Grant Certificate:
- The sections highlighted in red (points 1,2,3,4,5 and 6) will be varied based on the date when the certificate is issued.
- For example, if the certificate is sent to an MA on April 20th, then point 1 will be 20/04/2023, point 2 will be 2023, point 3 will be 1 January to 31 March 2023 and point 4 will be March 2023, point 5 will be 2023, and point 6 will be 10 days after the certificate issued date.
- The table listing the MA report (Point 7) will be based on the saved search: MA Grant Certificates | Do Not Modify
- The columns ‘References’, ‘Period’, ‘Currency’, ‘Amount’, and ‘Description’ in the PDF document will be populated with values obtained from the search results, specifically fetched based on each MA. Subsequently, the certificate will be sent separately to each MA.
- Attaching an image of the saved search results for reference:
Line level field mapping between PDF Template and the saved search result:
| Saved Search Result | |
| Reference | Activity Reference |
| Period | Period |
| Currency | Fixed Currency |
| Amount | Sum of Amount |
| Description | Description |
- The subtotal will be calculated by grouping the references and summing up the corresponding amounts. This means that all the references of an MA, along with their respective amounts, will be displayed consecutively. Additionally, a subtotal amount will be shown below these lines, which represents the sum of amounts for each line above with identical references.
- Attached is a screen shot for your reference:
- As indicated in the provided image, negative amounts will be displayed in red colour and enclosed within braces.
Assumptions
- In production environment, we utilize the saved search MA Grant Certificates | Do Not Modify to generate the Grant Certificate for each MA. It is important not to make any modifications to this search once our functionality for issuing the grant certificates has been implemented.
- The grant certificates will be issued to appropriate MAs 4 times a year, on January 20th (MA report of previous year), on April 20th (MA report from January 1 to March 31 of current year), on July 20th (MA report from January 1 to June 30 of current year), on October 20th (MA report from January 1 to September 30 of current year).
- We assume that when sending the grant certificate on January 20, 2024, for the duration of January 1 to December 31, 2023, the year on certificate (Point 2) should be 2023.
- In the PDF, negative amounts will be displayed in red colour and enclosed within braces.