dim_customer_dunning_configuration


Description

Dimension table exposing the dunning and billing stop configuration per customer. Each customer has at most one row. All four stop types are sourced from a single record configured manually by studio operators.

Stop types use a three-value LimitType pattern: - DISABLED — the stop is not active - UNLIMITED — the stop is permanently active (until_date is NULL) - LIMITED — the stop is active until until_date (inclusive)

To test whether a stop is currently active as of a reference date, apply: _limit_type = ‘UNLIMITED’ OR (_limit_type = ‘LIMITED’ AND _until_date >= <reference_date>)

Important: This table covers only manually configured stops set by studio operators. Dunning-engine-triggered collection stops (automatically set when a dunning level is reached) are stored in fct_dunning_step (collection_stop column). Combine both sources for a complete collection stop picture:

-- All customers with an active collection stop as of a reference date:
SELECT customer_id FROM dim_customer_dunning_configuration
WHERE collection_stop_limit_type = 'UNLIMITED'
   OR (collection_stop_limit_type = 'LIMITED' AND collection_stop_until_date >= '<ref_date>')
UNION
SELECT customer_id FROM fct_dunning_step
WHERE collection_stop IS TRUE
  AND dunning_date <= '<ref_date>'
  AND (withdrawn_date IS NULL OR withdrawn_date::date > '<ref_date>')

Use cases: - Active billable member counts: exclude customers where collection_stop_limit_type is UNLIMITED or (LIMITED and collection_stop_until_date >= reference_date) - Dunning portfolio analysis: identify customers where dunning is paused - Debt collection oversight: track customers blocked from external collection handover - Invoice audit: identify customers where invoice generation is suppressed

Columns

Column Type Size Nulls Auto Default Children Parents Comments
customer_dunning_configuration_id int8 19 null

Primary key of this table. Uniquely identifies this customer’s dunning configuration.
customer_id int8 19 √ null
dim_customer.customer_id dim_customer_dunning_configuration_customer_id_fkey R

The customer this dunning configuration belongs to. One configuration record exists per customer.
created_date timestamptz 35 √ null

Timestamp when this configuration record was created. Use as a lower-bound guard in historical queries: a stop can only have been active as of dates on or after this timestamp.
collection_stop_limit_type varchar 10 √ null

Whether payment collection runs are blocked for this customer. When active, the customer will not be included in payment collection runs and will appear as an EXCLUDED_BOOKING with reason COLLECTION_STOP in fct_payment_run.

Values

  • DISABLED — no collection stop active
  • UNLIMITED — collection permanently stopped
  • LIMITED — collection stopped until collection_stop_until_date (inclusive)
collection_stop_until_date date 13 √ null

The date until which the collection stop is active (inclusive). Only populated when collection_stop_limit_type is LIMITED. NULL for DISABLED and UNLIMITED.
dunning_stop_limit_type varchar 10 √ null

Whether the dunning process (payment reminder escalation) is paused for this customer.

Values

  • DISABLED — dunning proceeds normally
  • UNLIMITED — dunning permanently paused
  • LIMITED — dunning paused until dunning_stop_until_date (inclusive)
dunning_stop_until_date date 13 √ null

The date until which the dunning stop is active (inclusive). Only populated when dunning_stop_limit_type is LIMITED. NULL for DISABLED and UNLIMITED.
debt_collection_stop_limit_type varchar 10 √ null

Whether handover of this customer’s debt to an external debt collection agency is blocked.

Values

  • DISABLED — debt collection handover proceeds normally
  • UNLIMITED — handover permanently blocked
  • LIMITED — handover blocked until debt_collection_stop_until_date (inclusive)
debt_collection_stop_until_date date 13 √ null

The date until which the debt collection stop is active (inclusive). Only populated when debt_collection_stop_limit_type is LIMITED. NULL for DISABLED and UNLIMITED.
invoice_stop_limit_type varchar 10 √ null

Whether invoice generation is suppressed for this customer.

Values

  • DISABLED — invoices are generated normally
  • UNLIMITED — invoice generation permanently suppressed
  • LIMITED — invoice generation suppressed until invoice_stop_until_date (inclusive)
invoice_stop_until_date date 13 √ null

The date until which the invoice stop is active (inclusive). Only populated when invoice_stop_limit_type is LIMITED. NULL for DISABLED and UNLIMITED.
invoice_stop_on_delete bool 1 √ null

Whether an invoice stop is automatically applied when the customer record is deleted. This is a system-level configuration flag set per customer. NULL when not explicitly configured.
last_updated timestamptz 35 √ null

System column. UTC Timestamp at which entry was calculated (lags shortly behind source system).
data_landing_time timestamptz 35 getdate()

Relationships

Close relationships within degrees of separation