Ir al contenido principal
Explorar temas
Conciliación de pagos

Cómo conciliar registros de pagos con SQL en PostgreSQL

Dos fuentes de pagos se unen en una comparación que conserva tanto los registros emparejados como los presentes en una sola fuente.
FULL OUTER JOIN conserva los pagos emparejados y los que aparecen en una sola fuente.

Cómo conciliar registros de pagos con SQL en PostgreSQL

Una conciliación compara los pagos de dos fuentes para encontrar registros coincidentes, diferencias de importe o moneda y pagos presentes en una sola fuente. En PostgreSQL, FULL OUTER JOIN conserva las filas emparejadas y las que no tienen correspondencia.

Los ejemplos son ilustrativos: no se proporcionaron esquemas ni datos reales y las consultas no se ejecutaron. Adapta los nombres, los períodos y las reglas al proceso de pagos antes de interpretar los resultados.

1. Define qué vas a comparar

Establece una clave compartida, la granularidad de cada fila, las monedas y el período de cada fuente. Por ejemplo, una fuente podría registrar la fecha de pago y otra la fecha de liquidación; sus ventanas deben responder al proceso concreto. Define también cómo tratar comisiones, reembolsos, conversiones, liquidaciones agrupadas y tolerancias cuando correspondan.

El siguiente modelo supone una fila por payment_id en cada fuente, identificadores no nulos e importes y monedas obligatorios. Una coincidencia de identificador e importe, por sí sola, no demuestra que el pago esté correctamente liquidado.

2. Revisa claves ausentes y duplicadas

Busca primero los identificadores ausentes y repetidos en cada fuente. Una clave duplicada puede multiplicar las filas resultantes: la sección 7.2.1.1 de la documentación indica que una unión interna produce «a row for each row in T2» que satisface la condición; FULL JOIN parte de esa unión.

Consulta ilustrativa para pagos internos:

SELECT payment_id, COUNT(*) AS filas
FROM pagos_internos
GROUP BY payment_id
HAVING payment_id IS NULL OR COUNT(*) > 1;

Repite la consulta con pagos_externos y resuelve las excepciones según las reglas del proceso antes de ejecutar la comparación. Si la clave debe ser única y obligatoria, las restricciones UNIQUE y NOT NULL pueden protegerla. Según la sección 5.5.3, «By default, two null values are not considered equal» para UNIQUE; la sección 5.5.2 indica que NOT NULL impide que la columna tome el valor nulo.

3. Filtra cada fuente y compara los pagos

Delimita cada conjunto antes de unirlo. El ejemplo usa parámetros de inicio inclusivo y fin exclusivo; las fechas y su zona horaria deben definirse para cada fuente. Según la sección 7.2.2, WHERE descarta una fila cuando su condición es «false or null». Por tanto, un filtro posterior sobre una columna del lado ausente puede eliminar filas sin correspondencia.

Consulta ilustrativa de conciliación:

WITH internos AS (
SELECT payment_id, amount, currency
FROM pagos_internos
WHERE fecha_pago >= :inicio_interno
AND fecha_pago < :fin_interno
), externos AS (
SELECT payment_id, amount, currency
FROM pagos_externos
WHERE fecha_liquidacion >= :inicio_externo
AND fecha_liquidacion < :fin_externo
)
SELECT COALESCE(i.payment_id, e.payment_id) AS payment_id,
i.amount AS importe_interno,
e.amount AS importe_externo,
i.currency AS moneda_interna,
e.currency AS moneda_externa,
CASE
WHEN i.payment_id IS NULL THEN 'solo_externo'
WHEN e.payment_id IS NULL THEN 'solo_interno'
WHEN i.currency <> e.currency THEN 'moneda_distinta'
WHEN i.amount <> e.amount THEN 'importe_distinto'
ELSE 'coincide'
END AS resultado
FROM internos i
FULL OUTER JOIN externos e ON i.payment_id = e.payment_id;

Los marcadores con dos puntos representan valores que debe proporcionar la herramienta o aplicación que ejecute la consulta. Si esta no admite esa sintaxis, sustitúyelos por sus parámetros correspondientes. La clasificación depende de los supuestos indicados: si se permiten claves, importes o monedas nulos, hay que definir y añadir reglas específicas para ellos.

La sección 7.2.1.1 describe las filas sin pareja de FULL JOIN con «null values in columns of T2» y, de forma simétrica, valores nulos en las columnas de T1. Por eso el ejemplo usa la clave no nula de cada lado para identificar su ausencia.

Árbol de decisión que clasifica los pagos por ausencia de una fuente, diferencia de moneda, diferencia de importe o coincidencia.
La clasificación comprueba primero la presencia en cada fuente y después las monedas y los importes, en ese orden.

Cómo leer el resultado

  • solo_externo o solo_interno: la clave aparece en una fuente dentro de las ventanas seleccionadas.
  • moneda_distinta: ambas claves existen, pero las monedas difieren. Esta comprobación tiene prioridad sobre la diferencia de importe.
  • importe_distinto: las monedas coinciden y los importes difieren exactamente.
  • coincide: coinciden clave, moneda e importe bajo los supuestos del ejemplo.

4. Trata importes y totales con cuidado

Para importes monetarios, PostgreSQL describe numeric como «user-specified precision, exact» en la tabla 8.2, mientras que los tipos de coma flotante son inexactos. Define la precisión, la escala y el redondeo según las monedas y las reglas del negocio; la consulta anterior no aplica tolerancias ni conversiones.

Al resumir resultados, ten en cuenta que, como indica la sección 9.21, «sum of no rows returns null». Puedes usar COALESCE(sum(amount), 0) cuando presentar cero sea la regla deseada, y calcular totales por moneda para evitar combinar importes de monedas distintas.

5. Mantén una vista consistente entre consultas

Si necesitas ejecutar varias consultas sobre una misma vista de la base, puedes usar una transacción de solo lectura con REPEATABLE READ:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;
-- Ejecutar las consultas de revisión y conciliación.
COMMIT;

La sección 13.2.2 sitúa la instantánea al inicio del «first non-transaction-control statement»: las consultas sucesivas ven esa misma instantánea y no ven confirmaciones concurrentes posteriores. Esa vista, por sí sola, no garantiza una ejecución serializable; la tabla 13.1 admite anomalías de serialización en este nivel.

Comentarios

Buscar este blog