Script saca Estados de cuenta de Pagos del cliente

Creada por Juan Villalobos, Modificado el Lun., 13 Jul. a las 9:23 A. M. por Hernán Azofeifa
-- Saldos actuales de la operacion
SELECT LC.IdF_oritCliente as conCliente, 
       SOP.IdT_portSaldoOperacion  as conOperacion,
     ISNULL(TRIM(UPPER(CONCAT(PER.PrimerNombre, ' ', PER.SegundoNombre, ' ', PER.PrimerApellido, ' ', PER.SegundoApellido))), '') as nomCliente,
     ISNULL(ID.NumeroIdentificacion, '') as desIdentificacion,
     lower(ISNULL(CTC.Detalle, '')) as desEmail,
     ISNULL(MND.Descripcion, '') as NomMoneda,
     CASE 
      WHEN PF.Descripcion IS NULL THEN ''  -- Manejo de valores NULL
      WHEN PF.Descripcion LIKE '%(%' AND PF.Descripcion LIKE '%)%' 
      THEN 
        NULLIF(  TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(STUFF(PF.Descripcion, CHARINDEX('(', PF.Descripcion), CHARINDEX(')', PF.Descripcion) - CHARINDEX('(', PF.Descripcion) + 1, ''), 'NO HIT', ' '), 'NOHIT', ' '), 'No Hit', ''), 'NoHit', ' '),'- ', ' ')), ' ')  -- Elimina guiones seguidos de espacio
      ELSE 
        NULLIF( TRIM( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(UPPER(PF.Descripcion), 'NO HIT', ''), 'NOHIT', ''), 'No Hit', ''), 'NoHit', ''), '- ', '')), '')  -- Elimina guiones seguidos de espacio
    END AS desPlanFinanciero,
     SOP.FechaCreacion,--ISNULL(CONVERT(VARCHAR(30), SOP.FechaCreacion, 103), '') as fecCreacion,
     CONCAT(ISNULL(SOP.Plazo, 0), ' Meses') as desPlazoMeses,                                  --plazo credito
       CONCAT(ISNULL(SOP.Plazo, 0), ' Cuotas') as desPlazoCuotas,                                 --cuotas credito
     ISNULL(CONCAT(MND.SimboloMoneda, ' ', FORMAT(SOP.MontoOriginal, 'N2')), '0.00') as mtoOriginal, --montoInicial credito
       ISNULL(CONCAT(SOP.TasaInteresNormal, ' %'), '') as porInteresNominal,
     ISNULL(CONCAT(SOP.TasaInteresNormal, ' %'), '') as porInteresEfectiva,
       ISNULL(CONCAT(SOP.TasaInteresMora, ' %'), '') as porInteresMora,
       CONCAT(MND.SimboloMoneda, ' ', FORMAT(SOP.SaldoFinal, 'N2')) as mtoSaldoPrincipal,
       CI.FechaProximoCorte as fecProximoPago,
       ISNULL(CONCAT(MND.SimboloMoneda, ' ', FORMAT(SOP.CuotaActual, 'N2')), '0.00') as mtoCuotaActual,
     ISNULL(CONCAT(MND.SimboloMoneda, ' ', FORMAT(CCS.CuotaMensualSeguro, 'N2')), '0.00') mtoCuotaSeguro,
     ISNULL(CONCAT( MND.SimboloMoneda, ' ', FORMAT(SOP.CuotaActual + CCS.CuotaMensualSeguro, 'N2') ), '0.00') as mtoCuotaConSeguro,
     ISNULL(SOP.NumeroPagoVencido, 0) as numCuotasAtrasadas,
     ISNULL(CONCAT(MND.SimboloMoneda, ' ', FORMAT(CCA.MontoCuotasAtraso, 'N2')), '0.00') as mtoCuotasAtrasadas,
     CONCAT(MND.SimboloMoneda, ' ', ISNULL( FORMAT( SOP.CuotaActual + CCS.CuotaMensualSeguro + CCA.MontoCuotasAtraso, 'N2'), '0.00')) as mtoTotalCuotaPagar,
       ISNULL(TRIM(GEN.Descripcion), '') as desGenero,
     ISNULL(TRIM(UPPER(REPLACE(CONCAT(PER.PrimerNombre, ' ', PER.SegundoNombre),'N/A',''))), '') as desNombre,
     ISNULL(TRIM(UPPER(REPLACE(REPLACE(CONCAT(PER.PrimerApellido, ' ', PER.SegundoApellido),'SIN-SEGUNDO-APELLIDO',''),'N/A',''))), '') as desApellido,
     0 as indEnviado
FROM [CF_PORTFOLIO].dbo.t_portSaldoOperacion SOP WITH (NOLOCK)
INNER JOIN [CF_PORTFOLIO].dbo.t_portLineaCredito LC WITH (NOLOCK)    ON LC.IdT_portLineaCredito = SOP.IdF_portLineaCredito
INNER JOIN [CF_CUSTOMER].dbo.t_cstmPersona PER WITH (NOLOCK)           ON PER.IdT_comm_Persona = LC.IdF_oritCliente
  LEFT JOIN [CF_CUSTOMER].dbo.t_cstdIdentificacion ID WITH (NOLOCK)      ON ID.IdF_comm_Persona = PER.IdT_comm_Persona AND ID.IdT_comd_Identificacion = PER.IdentificacionPrimaria
  LEFT JOIN [CF_CUSTOMER].dbo.t_cstdContacto CTC WITH (NOLOCK)        ON CTC.IdF_comm_Persona = PER.IdT_comm_Persona
                                   AND CTC.IdF_comc_TipoContacto = 4
                                   AND CTC.IdF_comc_CategoriaContacto = 13
LEFT JOIN CF_COMMON.dbo.t_cmmcMoneda MND WITH (NOLOCK)               ON MND.IdT_cmmcMoneda = SOP.IdF_porcMoneda
INNER JOIN [CF_PORTFOLIO].dbo.t_porcPlanFinanciero PF WITH (NOLOCK)  ON PF.IdT_porcPlanFinanciero = LC.IdF_porcPlanFinanciero
INNER JOIN [CF_PORTFOLIO].dbo.t_porcCiclo CI WITH (NOLOCK)           ON CI.DiaCorte = SOP.IdF_porcCiclo AND CI.IdFrecuenciaPago = 1
LEFT JOIN CF_CUSTOMER.dbo.t_cstcGenero GEN WITH (NOLOCK)             ON GEN.IdT_comc_Genero = PER.IdF_comc_Genero
OUTER APPLY
        (SELECT ISNULL(SUM(OH.SaldoMora), 0.00) AS MontoCuotasAtraso
            FROM CF_PORTFOLIO.dbo.t_portSaldoOperacion OH WITH (NOLOCK)
            WHERE OH.IdF_portLineaCredito=SOP.IdF_portLineaCredito 
          AND OH.IdT_portSaldoOperacion = SOP.IdT_portSaldoOperacion
          AND OH.IdF_porcTipoOperacion IN ( 1, 3 )
                  AND OH.IdF_porcCartera <> 3
                  AND OH.Estado = 1
                  AND OH.IdF_porcCiclo = SOP.IdF_porcCiclo) CCA
OUTER APPLY
        (SELECT ISNULL(SUM(OH.CuotaMensual), 0.00) AS CuotaMensualSeguro
            FROM CF_PORTFOLIO.dbo.t_portSaldoOperacion OH WITH (NOLOCK)
            WHERE OH.IdF_portLineaCredito=SOP.IdF_portLineaCredito 
          AND OH.IdT_portSaldoOperacion = SOP.IdT_portSaldoOperacion
                  AND OH.IdF_porcTipoOperacion = 3
                  AND OH.IdF_porcCartera <> 3
                  AND OH.Estado = 1
                  AND OH.IdF_porcCiclo = SOP.IdF_porcCiclo) AS CCS
WHERE SOP.Estado = 1
  AND (CTC.Detalle IS NULL OR CTC.Detalle NOT LIKE 'NO_ENVIAR_%')
 -- and ID.NumeroIdentificacion = '402400584' --Numero de identificacion
  and SOP.MontoOriginal > 0
  order by SOP.FechaCreacion;

--  -- Verificacion de saldos actuales
--  select SOP.IdF_portLineaCredito, sop.IdT_portSaldoOperacion, * 
--  from [CF_PORTFOLIO].dbo.t_portSaldoOperacion SOP
--  where SOP.MontoOriginal > 0
--  AND sop.IdT_portSaldoOperacion = 8105621;

--  -- Verificacion de linea de credito
--  select IdF_oritCliente,* 
--  from [CF_PORTFOLIO].dbo.t_portLineaCredito LC
--  where IdT_portLineaCredito =  '16208'
 
--  --verificacion de la informacion del cliente
--  select * from [CF_CUSTOMER].dbo.t_cstmPersona
--  where IdT_comm_Persona = 16207;
---- Query para obtener los movimientos de las operaciones
 
 -- Movimientos de la operacion
declare @vIdentificacion varchar(20)= '155807721710',
  @vFechaInicial date = '2026-03-01',
  @vFechaFinal date= '2026-04-03',
  @vIdSaldoOperacion bigint =  0 -- este se calcula

-- Saldos actuales de la operacion
SELECT   @vIdSaldoOperacion = SOP.IdT_portSaldoOperacion
FROM [CF_PORTFOLIO].dbo.t_portSaldoOperacion SOP WITH (NOLOCK)
INNER JOIN [CF_PORTFOLIO].dbo.t_portLineaCredito LC WITH (NOLOCK)    ON LC.IdT_portLineaCredito = SOP.IdF_portLineaCredito
INNER JOIN [CF_CUSTOMER].dbo.t_cstmPersona PER WITH (NOLOCK)           ON PER.IdT_comm_Persona = LC.IdF_oritCliente
  LEFT JOIN [CF_CUSTOMER].dbo.t_cstdIdentificacion ID WITH (NOLOCK)      ON ID.IdF_comm_Persona = PER.IdT_comm_Persona AND ID.IdT_comd_Identificacion = PER.IdentificacionPrimaria
WHERE SOP.Estado = 1
  and ID.NumeroIdentificacion = @vIdentificacion --Numero de identificacion
  and SOP.MontoOriginal > 0;

 -- Movimientos de la operacion
;WITH Movimientos AS (
    -- Movimientos históricos
    SELECT IdF_portLineaCredito, IdF_portSaldoOperacion, Descripcion, IdF_porcTransaccion,
           FechaCreacion,
           -- Forzamos que 'fechaMovimiento' sea igual a FechaCreacion
           FechaMovimiento,
           FactorOperacion
    FROM CF_PORTFOLIO_HISTORICAL.dbo.t_portMovimientos_Historica
    WHERE FechaMovimiento BETWEEN @vFechaInicial AND @vFechaFinal
      AND ISNULL(FactorOperacion, 0) IN (-1)
      AND IdF_porcTransaccion IN (90020)

    UNION ALL
    -- Movimientos actuales
    SELECT IdF_portLineaCredito, IdF_portSaldoOperacion, Descripcion, IdF_porcTransaccion,
           FechaCreacion,
           -- Igual aquí
           FechaMovimiento,
           FactorOperacion
    FROM CF_PORTFOLIO.dbo.t_portMovimientos
    WHERE FechaMovimiento BETWEEN @vFechaInicial AND @vFechaFinal
      AND ISNULL(FactorOperacion, 0) IN (-1)
      AND IdF_porcTransaccion IN (90020)
)
SELECT DISTINCT
       SPD.IdF_portLineaCredito AS conCliente, 
       SPO.IdF_portSaldoOperacion AS conOperacion, 
       SPD.IdF_porcMoneda AS conMoneda, 
       MND.Descripcion AS nomMoneda, 
       MND.SimboloMoneda,
       -- Aquí usamos la FechaCreacion (ya igualada en el CTE) para la fecha de pago mostrada
       CONVERT(VARCHAR(50), MV.FechaMovimiento, 103) AS fecPago,
       MV.FechaMovimiento,
       ISNULL(MV.Descripcion, 'N/A') AS desTipoMovimiento,
       CASE 
         WHEN ISNULL(SUC.Descripcion, 'N/A') = 'CREDITFORCE DEMO' THEN 'PAGO RECIBIDO'
         ELSE ISNULL(SUC.Descripcion, 'N/A') 
       END AS desMedioPago,
       ISNULL(CONCAT(MND.SimboloMoneda, ' ', FORMAT(SPO.SaldoFinal, 'N2')), '0.00') AS mtoSaldoPrincipalAnterior,
       ISNULL(CONCAT(MND.SimboloMoneda, ' ', FORMAT(SPO.MontoAplicado, 'N2')), '0.00') AS mtoPagoAplicado,
       ISNULL(CONCAT(MND.SimboloMoneda, ' ', FORMAT(SPO.NvoSaldoPrincipal, 'N2')), '0.00') AS mtoPrincipalAplicado,
       ISNULL(CONCAT(MND.SimboloMoneda, ' ', FORMAT(SPO.NvoSaldoInteresNormal, 'N2')), '0.00') AS mtoInteres,
       ISNULL(CONCAT(MND.SimboloMoneda, ' ', FORMAT(SPO.NvoSaldoInteresMora, 'N2')), '0.00') AS mtoInteresMoraAplicado,
       ISNULL(CONCAT(MND.SimboloMoneda, ' ', FORMAT(SPO.NvoSaldoComision, 'N2')), '0.00') AS mtoComisionAplicado,
       ISNULL(CONCAT(MND.SimboloMoneda, ' ', FORMAT(0.00, 'N2')), '0.00') AS mtoIvaAplicado,
       ISNULL(CONCAT(MND.SimboloMoneda, ' ', FORMAT(SPO.SaldoFinal - SPO.MontoAplicado, 'N2')), '0.00') AS mtoSaldoPrincipalPosterior
FROM [CF_PORTFOLIO].dbo.t_portSaldoOperacionPrecedencia SPO
INNER JOIN CF_PORTFOLIO.dbo.t_portSaldoPrecedencia SPD ON SPD.IdT_portSaldoPrecedencia = SPO.IdF_portSaldoPrecedencia
INNER JOIN CF_COMMON.dbo.t_cmmcMoneda MND ON MND.IdT_cmmcMoneda = SPD.IdF_porcMoneda
INNER JOIN Movimientos MV ON MV.IdF_portLineaCredito = SPD.IdF_portLineaCredito  
                         AND MV.IdF_portSaldoOperacion = SPO.IdF_portSaldoOperacion
                         and CONVERT(VARCHAR(50),MV.FechaCreacion, 103) = CONVERT(VARCHAR(50),SPO.fechaCreacion, 103)
LEFT JOIN CF_PORTFOLIO.dbo.t_porcSucursal SUC WITH (NOLOCK) ON SUC.IdT_porcSucursal = SPD.IdF_porcSucursal
WHERE SPD.Estado = 1
  AND MV.FechaMovimiento BETWEEN @vFechaInicial AND @vFechaFinal
 AND (@vIdSaldoOperacion = 0 OR SPO.IdF_portSaldoOperacion = @vIdSaldoOperacion)
ORDER BY SPD.IdF_portLineaCredito, SPO.IdF_portSaldoOperacion, MV.FechaMovimiento;
Este Scrip permite sacar los estados de cuenta (Pagos) de un cliente. está diseñado paso por paso para poder confeccionar los datos que se requieren para un estado de cuenta.

Ejemplo de los datos:

Modificaciones el script:

1- En el primer paso se debe ingresar la cédula o DUI de la persona: