Invoice Blocking SQL Validation

Validate Customer Has Overdue Invoice (<= Today)

1
2
3
4
5
6
7
8
9
10
11
12

-- Check for outstanding overdue invoices
SELECT
    i.invoice_id,
    i.customer_id,
    i.due_date,
    i.outstanding_amount,
    DATEDIFF(CURRENT_DATE, i.due_date) AS days_overdue
FROM invoices i
WHERE i.customer_id = 'CUST-DEMO-078'
  AND i.status = 'UNPAID'
  AND i.due_date <= CURRENT_DATE
ORDER BY i.due_date ASC;

Expected result:

Returns at least 1 row, confirming that an overdue invoice exists.

Validate Blocking Reason & Audit Trail

1
2
3
4
5
6
7
8
9
10
11

-- Check order blocking log
SELECT
    order_id,
    customer_id,
    status,
    blocking_reason,
    created_at
FROM sales_orders
WHERE customer_id = 'CUST-DEMO-078'
ORDER BY created_at DESC
LIMIT 5;

Expected result:

blocking_reason = 'OVERDUE_INVOICE' or a similar configured blocking reason.

Database Checks

  • Invoice status remains UNPAID before payment is applied.
  • Invoice due date is less than or equal to CURRENT_DATE.
  • Sales Order creation attempt stores a blocked or rejected status.
  • Payment changes invoice state to paid.
  • Customer eligibility is recalculated after payment.

Back to Project