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.
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
