Requirement
Client has requested an automated email process for sending account statements to customers with specific requirements. They need the system to send statements only to customers with outstanding balances and automatically include open invoices in the statement. Additionally, they require the ability to pause the email automation, similar to the existing overdue invoice email feature, and to hold specific customers from receiving statements. Lastly, they want the flexibility to set a designated outgoing email date, such as the first of each month, with the option to modify it as needed. These enhancements will streamline communication and improve account statement management.
Statement of Work (SOW)
To address Nationwide Pharmaceuticals’ requirements for automating the account statement email process, the implementor will develop a solution that integrates validation checks, email generation, and scheduling mechanisms to ensure statement emails are sent efficiently and accurately.
The solution will include the following enhancements:
Custom Record Setup
A Custom Record named “Email Automation Settings” will be created to manage the automation process. This record will include the following fields:
- Automate Customer Statement Email (Checkbox) – Enables or disables the automation process.
- Statement Email Send Day (List Field) – Defines the scheduled date for sending statements (e.g., First Day of the Month, Last Day of the Month, Today, Tomorrow).
- Held/Restricted Customers (Multi-Select List) – Allows users to select specific customers who should not receive automated statement emails.
The system will reference these settings to determine when and how the automation should execute.
Include the Customers with Balance in the Email
The implementor will incorporate a balance validation check within the email automation process. A Saved Search will be created to retrieve customers with an outstanding balance greater than zero (Balance > 0). The system will dynamically fetch these customers and ensure that statement emails are triggered only for accounts with unpaid balances. Customers with a zero balance will be automatically excluded from the email distribution, preventing unnecessary communications.
Include Open Invoices in the Email
The system will generate an account statement email that dynamically attaches a CSV containing all open invoices with following details.
- Customer Name
- Document Number
- Invoice Status
- Invoice Date
- Due Date
- Total Amount
- Amount Paid
- Amount Remaining
This will ensure that each statement includes a real-time summary of outstanding invoices, improving the accuracy and transparency of customer communications. The system will automatically retrieve relevant invoice data and format it into a CSV document before sending the email.
Pause Automation Emails
To allow users to pause and resume the automation process, the “Automate Customer Statement Email” checkbox in the Email Automation Settings record will be used. If the setting is disabled, no statement emails will be sent until re-enabled.
Hold Specific Customers from Receiving Statements
The implementor will introduce a custom field on the Customer record, labeled “Held/Restricted Customer”, allowing specific customers to be excluded from receiving automated account statements. The system will reference this field before sending emails, ensuring that flagged customers are automatically skipped from the email distribution while maintaining full automation for all other customers.
Set Outgoing Email Date
The system will check the “Statement Email Send Day” setting before executing the email automation. If the current system date matches the configured send date, the email process will be triggered. If the dates do not match, the process will halt execution, ensuring emails are only sent on the designated schedule.
Prerequisites
- Customer Email Addresses: Customers must have valid email addresses stored in their records to ensure successful email delivery.
- Email Templates & Formatting: The account statement email template must be designed and finalized, including placeholders for dynamically generated invoice details.
- Author of the mail: All account statement emails will be sent from the employee named Kathy
Assumptions
- All Customers must have a valid email address: It is assumed that customers receiving statement has a valid email address.
- Customer Balances Are Accurately Maintained in NetSuite: The saved search will rely on NetSuite balance calculations, assuming the data is accurate and up to date.
- Email System is Functional and Not Blocked: NetSuite’s email sending capabilities are operational, and customer domains do not block automated emails from NetSuite.
- Users Will Actively Manage the Pause Feature: It is assumed that users responsible for pausing and resuming automation will check and update the setting as needed.
- Scheduled Execution Will Be Based on Business Requirements: The recurring statement email send date is expected to align with business needs, and changes to the schedule will be managed through the custom record settings.
- Each email sent to the customer will include two attachments:
- A CSV file listing all open invoices.
- A customer statement PDF, formatted using the “NMS | Customer Statement Template.”
- If there are no open invoices or statements with an outstanding balance, the email will not be sent. Empty files will not be delivered to the customer.
Risks
- If the number of customers with outstanding balances is large, NetSuite governance limits may impact the performance of the Map/Reduce script, requiring optimization or batch processing.
- Users may unintentionally leave the pause automation setting (Automate Customer Statement Email)disabled, leading to a failure in sending necessary statement emails.
- If customer email addresses or invoice records are incomplete or incorrect, some statement emails may fail to be sent, requiring manual intervention.
- If other scheduled scripts are running simultaneously, they may interfere with this automation, delaying or preventing email execution.