Business Problem

As an Accounts Receivable Manager at Salesforce, you're working with the finance team to identify and prioritize collection efforts for overdue invoices. The collections team needs a focused list of past-due invoices that belong to customers with active subscriptions, as these represent the highest priority for immediate follow-up.

Your task is to analyze the billing system and identify invoices that are past their due date but are associated with subscriptions that are still active. This analysis is critical because these customers are continuing to use Salesforce services but have outstanding payment obligations that need immediate attention.

The business defines "past-due invoices" as those with outstanding amounts (amount_due_cents > 0) that have passed their due date. However, the collections team should only focus on invoices linked to currently active subscriptions (status = 'active'), as these represent ongoing customer relationships where collection efforts are most likely to succeed.

To help the collections team prioritize their workload, results should be sorted by due date with the most recently overdue invoices first. This allows them to focus on the newest delinquencies before they become more problematic. The team also needs complete invoice and subscription information to facilitate effective customer outreach.

Schema: invoices

Business Problem

Schema: invoices

Column Name	Type
invoice_id	INTEGER
subscription_id	INTEGER
invoice_number	TEXT
period_start	TEXT
period_end	TEXT
issue_date	TEXT
due_date	TEXT
status	TEXT

Column Name	Type
invoice_id	INTEGER
subscription_id	INTEGER
invoice_number	TEXT
period_start	TEXT
period_end	TEXT
issue_date	TEXT
due_date	TEXT
status	TEXT

Column Name	Type
subscription_id	INTEGER
org_id	INTEGER
plan_id	INTEGER
start_date	TEXT
end_date	TEXT
status	TEXT

Column Name	Type
subscription_id	INTEGER
org_id	INTEGER
plan_id	INTEGER
start_date	TEXT
end_date	TEXT
status	TEXT

invoice_id	subscription_id	invoice_number	amount_due_cents	due_date
73	43	INV-2025000073	51284	2025-09-29
95	59	INV-2025000095	133567	2025-09-02
26	15	INV-2025000026	184423	2025-08-13

1	1	INV-2025000001	2025-03-21	2025-04-19	2025-03-26	2025-04-25	paid	0	0	0	0	0	0	USD
2	1	INV-2025000002	2025-04-20	2025-05-19	2025-04-20	2025-05-20	open

2755. Past‑due invoices for active subscriptions

Relevant companies

Tags

2755. Past‑due invoices for active subscriptions

Relevant companies

Tags

Sign in to Practice