Dunning Letters
Dunning Letters are formal written communications used by businesses to remind or notify customers of overdue payments. These letters are typically part of a sequence, growing progressively firmer in tone as the debt remains unpaid. The dunning process aims to recover the debts in a systematic and professional manner without alienating or losing the customer. Dunning Letters serve not only as reminders but can also be used as evidence in case legal action becomes necessary.
The BMS-Paperless app implements 4 stages in the dunning process, which include:
1. Friendly Reminder: The first letter is often polite and friendly, giving the customer the benefit of the doubt that the non-payment could be an oversight. It generally includes information about the outstanding invoice, the amount due, and a request for payment.
2. More Urgent Reminder: If the payment is not received within a certain time frame, a second letter is sent which is somewhat more firm. It reminds the customer of the overdue payment and might include a warning of the potential consequences of non-payment.
3. Final Notice: This letter is more stern and makes it clear that this is the last communication before further action is taken. It should include a final request for payment, a deadline, and a notice of the actions that will be taken if the payment is not received, such as late fees, reporting to credit agencies, or legal action.
4. Letter Before Action or Legal Notice: This is typically the final letter in the dunning sequence and indicates that the company is prepared to take legal action to recover the debt. It is meant to serve as a final warning to the debtor.
In the BMS-Paperless app, part of the logic for the Dunning Letters is embedded inside the actual Dunning Letters Report Template (RDL File). You can define different groups based on the number of 'Days Overdue' and then assign different wording to each group. See example below.
Dunning Letter vs. Statement
It's important to distinguish between a dunning letter and a month-end statement. A statement is a summary of all unpaid invoices sent to customers at the end of each month, regardless of whether the invoices are overdue. Unlike dunning letters, statements are not considered harassment but are still effective tools for prompting payment.
Special Fields available within the main grid
The following fields provide deeper insights into overdue invoices for each customer. These fields are hidden by default but can be made visible using the Field Chooser from the Grid.
1. Min Number Of Days Overdue (MinNumberOfDaysOverdue)
Description: Shows the minimum number of days any overdue invoice is past due for a customer.
Importance: Helps identify customers with slightly overdue invoices. For example, if this value is low, the customer might need a gentle reminder rather than a strict escalation.
2. Max Number Of Days Overdue (MaxNumberOfDaysOverdue)
Description: Shows the maximum number of days any overdue invoice is past due for a customer.
Importance: Highlights the most overdue invoice, making it easier to spot critical cases requiring immediate attention.
3. Avg Number Of Days Overdue (AvgNumberOfDaysOverdue)
Description: Displays the average number of days invoices are overdue for a customer.
Importance: Provides an overall view of the customer's payment delays, helping you assess whether delays are consistent or sporadic.
4. Total Number Of Days Overdue (TotalNumberOfDaysOverdue)
Description: The sum of all overdue days across overdue invoices for a customer.
Importance: Indicates the overall severity of overdue payments. A higher value suggests a more significant issue with payment delays.
You can use these fields in the Logic Extender to create custom rules, such as automatically selecting the appropriate Report Template for each customer based on MinNumberOfDaysOverdue or AvgNumberOfDaysOverdue, or setting thresholds to trigger actions like sending follow-ups or prioritizing collections based on overdue severity.
RDL Report Template
Sql Query inside the rdl file:
--declare @oPARAMETER1 varchar (40)
--declare @oPARAMETER2 varchar (40)
--declare @oPARAMETER3 varchar (40)
--declare @oPARAMETER4 varchar (40)
--set @oPARAMETER1 ='CENGRO' -- CUSTOMER_ID
--set @oPARAMETER2 ='(USD) $' -- CURRENCY_ID
--set @oPARAMETER3 ='MMC' -- SITE_ID
--set @oPARAMETER4 ='2023/06/10' --CUT-OFF-DATE STRING
declare @TimeBucket1 int
declare @TimeBucket2 int
declare @TimeBucket3 int
set @TimeBucket1 = 1 --Less than this is considered current
set @TimeBucket2 = 14
set @TimeBucket3 = 30
--Logic for DaysOverdueGroup
-- Less than @TimeBucket1 is is considered current ==> DaysOverdueGroup = 100
-- Between TimeBucket1 and @TimeBucket2 ==> DaysOverdueGroup = 1
-- Between TimeBucket2 and @TimeBucket3 ==> DaysOverdueGroup = 2
-- Greater than @TimeBucket4 ==> DaysOverdueGroup = 3
SELECT
RECEIVABLE.CUSTOMER_ID,
RECEIVABLE.CURRENCY_ID,
RECEIVABLE.SITE_ID,
RECEIVABLE.INVOICE_ID,
CUSTOMER.NAME AS CUST_NAME,
RECEIVABLE.INVOICE_DATE,
RECEIVABLE.TERMS_NET_DAYS,
DATEADD(day, ISNULL(RECEIVABLE.TERMS_NET_DAYS, 0), RECEIVABLE.INVOICE_DATE) AS DUE_DATE,
DATEDIFF(d, DATEADD(day, ISNULL(RECEIVABLE.TERMS_NET_DAYS, 0), RECEIVABLE.INVOICE_DATE), CONVERT(DATETIME, @oPARAMETER4)) AS DAYS_OVERDUE,
CASE
WHEN DATEDIFF(d, DATEADD(day, ISNULL(RECEIVABLE.TERMS_NET_DAYS, 0), RECEIVABLE.INVOICE_DATE), CONVERT(DATETIME, @oPARAMETER4)) < @TimeBucket1 THEN 100
WHEN DATEDIFF(d, DATEADD(day, ISNULL(RECEIVABLE.TERMS_NET_DAYS, 0), RECEIVABLE.INVOICE_DATE), CONVERT(DATETIME, @oPARAMETER4)) BETWEEN @TimeBucket1 AND @TimeBucket2 THEN 1
WHEN DATEDIFF(d, DATEADD(day, ISNULL(RECEIVABLE.TERMS_NET_DAYS, 0), RECEIVABLE.INVOICE_DATE), CONVERT(DATETIME, @oPARAMETER4)) BETWEEN @TimeBucket2 AND @TimeBucket3 THEN 2
WHEN DATEDIFF(d, DATEADD(day, ISNULL(RECEIVABLE.TERMS_NET_DAYS, 0), RECEIVABLE.INVOICE_DATE), CONVERT(DATETIME, @oPARAMETER4)) > @TimeBucket3 THEN 3
END AS DaysOverdueGroup,
RECEIVABLE.TOTAL_AMOUNT - RECEIVABLE.PAID_AMOUNT AS Balance,
CUSTOMER.ADDR_1,
CUSTOMER.ADDR_2,
CUSTOMER.ADDR_3,
CUSTOMER.CITY,
CUSTOMER.STATE,
CUSTOMER.ZIPCODE,
CUSTOMER.COUNTRY,
Aux_1.PO_REF
FROM
RECEIVABLE
INNER JOIN CUSTOMER ON RECEIVABLE.CUSTOMER_ID = CUSTOMER.ID
LEFT OUTER JOIN (
SELECT
TOP (100) PERCENT RECEIVABLE_LINE.INVOICE_ID,
MIN(CUSTOMER_ORDER.CUSTOMER_PO_REF) AS PO_REF
FROM
RECEIVABLE_LINE
INNER JOIN CUSTOMER_ORDER ON RECEIVABLE_LINE.CUST_ORDER_ID = CUSTOMER_ORDER.ID
GROUP BY RECEIVABLE_LINE.INVOICE_ID
ORDER BY RECEIVABLE_LINE.INVOICE_ID DESC
) AS Aux_1 ON RECEIVABLE.INVOICE_ID = Aux_1.INVOICE_ID
WHERE
RECEIVABLE.STATUS <> 'X'
AND RECEIVABLE.TOTAL_AMOUNT - RECEIVABLE.PAID_AMOUNT <> 0
AND RECEIVABLE.CUSTOMER_ID LIKE @oPARAMETER1
AND RECEIVABLE.CURRENCY_ID LIKE @oPARAMETER2
AND RECEIVABLE.SITE_ID LIKE @oPARAMETER3
ORDER BY DaysOverdueGroup, RECEIVABLE.INVOICE_DATE
Note: in version v23.9.x a timestamp is automatically added to the Customer Statements and Dinning Letters
