Skip to main content

​
Overview

The Overdue Payments module automatically identifies installments that are past their due date and calculates late payment penalties (mora). The system applies a 2% daily penalty rate to encourage timely payment.

​
Database View: vw_CuotasVencidas

The overdue payments view (vw_CuotasVencidas) identifies all unpaid installments past their due date: 
CREATE VIEW vw_CuotasVencidas
AS
SELECT 
    v.norden,
    c.nombre AS cliente,
    pp.numeroCuota,
    pp.fechaVencimiento,
    pp.montoCuota,
    DATEDIFF(DAY, pp.fechaVencimiento, GETDATE()) AS diasAtraso,
    (pp.montoCuota * 0.02) * DATEDIFF(DAY, pp.fechaVencimiento, GETDATE()) AS moraCalculada
FROM planPagos pp
INNER JOIN ventas v ON pp.norden = v.norden
INNER JOIN clientes c ON v.codclien = c.codclien
WHERE pp.pagada = 0
AND pp.fechaVencimiento < GETDATE()
AND v.nula = 0

​
View Filters

The condition pp.pagada = 0 ensures only outstanding installments are included. Once an installment is paid, it’s automatically removed from the overdue list.
pp.fechaVencimiento < GETDATE() filters for installments where the due date has passed. Payments due today or in the future are excluded.
Voided sales (v.nula = 1) are excluded to prevent showing penalties on cancelled transactions.

​
View Model Properties

The VwCuotasVencida class in TechCore.Models represents overdue payment data:
​
Norden
string
required
The order number associated with this installment
​
Cliente
string
required
Customer name from the clientes table
​
NumeroCuota
int
required
Installment number (1, 2, 3, etc.) within the payment plan
​
FechaVencimiento
DateOnly
required
The original due date for this installment
​
MontoCuota
decimal
required
The base installment amount before penalties
​
DiasAtraso
int
Number of days past the due date, calculated as DATEDIFF(DAY, fechaVencimiento, GETDATE())
​
MoraCalculada
decimal
Late payment penalty amount calculated using the formula: (montoCuota * 0.02) * diasAtraso

​
Mora Calculation Formula

The system calculates late payment penalties (mora) using a daily compound rate: 
(pp.montoCuota * 0.02) * DATEDIFF(DAY, pp.fechaVencimiento, GETDATE())

​
Penalty Rate Breakdown

1

Daily Rate: 2%

The penalty rate is 2% of the installment amount per day (0.02 in the formula)
2

Days Late Calculation

The system calculates the number of days between the due date (fechaVencimiento) and today (GETDATE())
3

Total Penalty

Multiply the daily penalty (2% of installment) by the number of days late

​
Example Calculation

Example: An installment of $1,000.00 that is 15 days overdue
  • Base installment: $1,000.00
  • Daily penalty: 1,000.00×0.02=1,000.00 × 0.02 = 20.00 per day
  • Days late: 15 days
  • Total mora: 20.00×15=20.00 × 15 = 300.00
  • Amount due: 1,000.00+1,000.00 + 300.00 = $1,300.00
At 2% per day, penalties accumulate quickly:
  • 10 days late = 20% penalty
  • 30 days late = 60% penalty
  • 50 days late = 100% penalty (doubles the amount)
This high rate is designed to strongly incentivize timely payment.

​
Upcoming Payments View

The system also tracks installments that are due soon but not yet overdue:

​
vw_CuotasPorVencer

CREATE VIEW vw_CuotasPorVencer
AS
SELECT 
    v.norden,
    c.nombre AS cliente,
    pp.numeroCuota,
    pp.fechaVencimiento,
    pp.montoCuota
FROM planPagos pp
INNER JOIN ventas v ON pp.norden = v.norden
INNER JOIN clientes c ON v.codclien = c.codclien
WHERE pp.pagada = 0
AND pp.fechaVencimiento >= GETDATE()
AND v.nula = 0
The VwCuotasPorVencer model includes the same fields as overdue payments but without penalty calculations, since these payments are not yet late.

​
Usage Examples

​
Query All Overdue Payments

using (var context = new TechCoreContext())
{
    var overduePayments = context.VwCuotasVencidas
        .OrderByDescending(cv => cv.DiasAtraso)
        .ToList();
        
    foreach (var overdue in overduePayments)
    {
        Console.WriteLine($"Customer: {overdue.Cliente}");
        Console.WriteLine($"Order: {overdue.Norden}, Installment #{overdue.NumeroCuota}");
        Console.WriteLine($"Due Date: {overdue.FechaVencimiento}");
        Console.WriteLine($"Days Late: {overdue.DiasAtraso}");
        Console.WriteLine($"Base Amount: {overdue.MontoCuota:C}");
        Console.WriteLine($"Penalty: {overdue.MoraCalculada:C}");
        Console.WriteLine($"Total Due: {(overdue.MontoCuota + overdue.MoraCalculada):C}");
        Console.WriteLine();
    }
}

​
Calculate Total Penalties

using (var context = new TechCoreContext())
{
    var totalPenalties = context.VwCuotasVencidas
        .Sum(cv => cv.MoraCalculada ?? 0);
        
    Console.WriteLine($"Total Penalties: {totalPenalties:C}");
}

​
Find Critical Overdue Accounts (30+ days)

using (var context = new TechCoreContext())
{
    var criticalAccounts = context.VwCuotasVencidas
        .Where(cv => cv.DiasAtraso >= 30)
        .GroupBy(cv => cv.Cliente)
        .Select(g => new {
            Cliente = g.Key,
            TotalOverdue = g.Sum(x => x.MontoCuota),
            TotalPenalties = g.Sum(x => x.MoraCalculada ?? 0),
            InstallmentCount = g.Count()
        })
        .OrderByDescending(x => x.TotalPenalties)
        .ToList();
        
    foreach (var account in criticalAccounts)
    {
        Console.WriteLine($"Customer: {account.Cliente}");
        Console.WriteLine($"Overdue Installments: {account.InstallmentCount}");
        Console.WriteLine($"Base Amount: {account.TotalOverdue:C}");
        Console.WriteLine($"Penalties: {account.TotalPenalties:C}");
        Console.WriteLine();
    }
}

​
Get Upcoming Payments (Next 7 Days)

using (var context = new TechCoreContext())
{
    var nextWeekDue = context.VwCuotasPorVencers
        .Where(cp => cp.FechaVencimiento <= DateOnly.FromDateTime(DateTime.Today.AddDays(7)))
        .OrderBy(cp => cp.FechaVencimiento)
        .ToList();
        
    Console.WriteLine("Payments due in the next 7 days:");
    foreach (var payment in nextWeekDue)
    {
        Console.WriteLine($"{payment.Cliente} - {payment.MontoCuota:C} due {payment.FechaVencimiento}");
    }
}

​
Payment Status Workflow

1

Payment Plan Created

When a credit sale is made, installments are created in planPagos with pagada = 0
2

Before Due Date

Installment appears in vw_CuotasPorVencer (upcoming payments view)
3

After Due Date (Unpaid)

Installment moves to vw_CuotasVencidas (overdue payments view) and penalty calculation begins
4

Payment Received

When payment is posted to abonosVentas, the TR_ActualizarSaldo trigger sets pagada = 1 and the installment is removed from both views

​
Performance Optimization

The database includes indexes to optimize overdue payment queries: 
-- Index for filtering unpaid installments
CREATE INDEX IDX_planPagos_pagada ON planPagos(pagada) WHERE pagada = 0

-- Index for due date filtering
CREATE INDEX IDX_planPagos_fechaVencimiento ON planPagos(fechaVencimiento)

-- Index for filtering active sales
CREATE INDEX IDX_ventas_nula ON ventas(nula) WHERE nula = 0
These filtered indexes significantly improve query performance since most queries focus on unpaid installments and active sales.

planPagos

Payment schedule containing all installments with due dates and payment status

ventas

Credit sales that generate payment plans and installments

abonosVentas

Payment records that mark installments as paid via the TR_ActualizarSaldo trigger

clientes

Customer information for overdue payment notifications and collection activities

​
See Also

Build docs developers (and LLMs) love

Get started for freeTalk to us