ALTER PROCEDURE [dbo].[sp_rem1887]
  (
      @PERIODO INT = 0,
      @mes     INT = 0
  )
  AS
  SET NOCOUNT ON;
  
  DECLARE @periodoParam INT = (SELECT periodo FROM remParametros)
  DECLARE @mesParam INT = (SELECT mes FROM remParametros)
  --1--Sueldo Bruto
  --2--Cotiz. Previsional + CCI + Expectativa de Vida (Leyes Sociales)
  --3--Renta Afecta
  --4--Impuesto Único
  --5--Mayor Retención
  --6--Renta Exenta
  --7--Renta no Gravada
  --8--Reb. Zonas Extremas
  --9-- 3%'Prestamo tasa 0%'

  --10--FONDO PREVISION COVID (52)
  --11--MONTO SALUD COVID (53)
  --12--CCAF 0.6% COVID (57)

  DECLARE  @mes01 DECIMAL(18,4), @mes02 DECIMAL(18,4), @mes03 DECIMAL(18,4), @mes04 DECIMAL(18,4),
           @mes05 DECIMAL(18,4), @mes06 DECIMAL(18,4), @mes07 DECIMAL(18,4), @mes08 DECIMAL(18,4),
           @mes09 DECIMAL(18,4), @mes10 DECIMAL(18,4), @mes11 DECIMAL(18,4), @mes12 DECIMAL(18,4)

  SELECT  @mes01 = mes01, @mes02 = mes02, @mes03 = mes03, @mes04 = mes04, @mes05 = mes05,
          @mes06 = mes06, @mes07 = mes07, @mes08 = mes08, @mes09 = mes09, @mes10 = mes10,
          @mes11 = mes11, @mes12 = mes12
  FROM conFactores
  WHERE periodo = @PERIODO AND tipo = 1

  DECLARE  @CONCEPTO01 INT, @CONCEPTO02 INT, @CONCEPTO03 INT, @CONCEPTO04 INT,
           @CONCEPTO05 INT, @CONCEPTO06 INT, @CONCEPTO07 INT, @CONCEPTO08 INT,
           @CONCEPTO09 INT, @CONCEPTO10 INT, @CONCEPTO11 INT, @CONCEPTO12 INT

  SET @CONCEPTO01 = (SELECT resultado FROM RemReferencia1887 WHERE CAMPO = 1)
  SET @CONCEPTO02 = (SELECT resultado FROM RemReferencia1887 WHERE CAMPO = 2)
  SET @CONCEPTO03 = (SELECT resultado FROM RemReferencia1887 WHERE CAMPO = 3)
  SET @CONCEPTO04 = (SELECT resultado FROM RemReferencia1887 WHERE CAMPO = 4)
  SET @CONCEPTO05 = (SELECT resultado FROM RemReferencia1887 WHERE CAMPO = 5)
  SET @CONCEPTO06 = (SELECT resultado FROM RemReferencia1887 WHERE CAMPO = 6)
  SET @CONCEPTO07 = (SELECT resultado FROM RemReferencia1887 WHERE CAMPO = 7)
  SET @CONCEPTO08 = (SELECT resultado FROM RemReferencia1887 WHERE CAMPO = 8)
  SET @CONCEPTO09 = (SELECT resultado FROM RemReferencia1887 WHERE CAMPO = 9)

  SET @CONCEPTO10 = (SELECT resultado FROM remDatosPlanilla WHERE periodo = @periodoParam AND mes = @mesParam AND codigo = 52)
  SET @CONCEPTO11 = (SELECT resultado FROM remDatosPlanilla WHERE periodo = @periodoParam AND mes = @mesParam AND codigo = 53)
  SET @CONCEPTO12 = (SELECT resultado FROM remDatosPlanilla WHERE periodo = @periodoParam AND mes = @mesParam AND codigo = 57)

  DECLARE @CONCEPTO_FONDOVEJEZ INT, @CONCEPTO_FONDOSALUD INT, @CONCEPTO_COTADICIONALSALUD INT, @CONCEPTO_SEGCESANTRABAJADOR INT
  
  SET @CONCEPTO_FONDOVEJEZ = (SELECT resultado FROM remDatosPlanilla WHERE periodo = @periodoParam AND mes = @mesParam AND codigo = 4)
  SET @CONCEPTO_FONDOSALUD  = (SELECT resultado FROM remDatosPlanilla WHERE periodo = @periodoParam AND mes = @mesParam AND codigo = 7)
  SET @CONCEPTO_COTADICIONALSALUD  = (SELECT resultado FROM remDatosPlanilla WHERE periodo = @periodoParam AND mes = @mesParam AND codigo = 8)
  SET @CONCEPTO_SEGCESANTRABAJADOR = (SELECT resultado FROM remDatosPlanilla WHERE periodo = @periodoParam AND mes = @mesParam AND codigo = 22)

  -- Conceptos CCI y Expectativa de Vida (vigentes desde Ago-2025)
  DECLARE @CONCEPTO_CCI INT, @CONCEPTO_CEV INT
  SET @CONCEPTO_CCI = (SELECT resultado FROM remDatosPlanilla WHERE periodo = @periodoParam AND mes = @mesParam AND codigo = 48)
  SET @CONCEPTO_CEV  = (SELECT resultado FROM remDatosPlanilla WHERE periodo = @periodoParam AND mes = @mesParam AND codigo = 49)

  SELECT remMovimientosRM.periodo, rut, remMovimientosRM.mes, idConcepto, ISNULL(valor, 0) AS valor
  INTO #resultados
  FROM remMovimientosRM
  INNER JOIN remTrabajadores ON remMovimientosRM.periodo = remTrabajadores.periodo
                            AND remMovimientosRM.mes     = remTrabajadores.mes
                            AND remMovimientosRM.ficha   = remTrabajadores.ficha
                            AND remTrabajadores.activo   = 1
  WHERE remMovimientosRM.periodo = @periodo
  AND   remMovimientosRM.tipoConcepto = 'R'

  DELETE FROM REMinforme1887 WHERE periodo = @PERIODO

  SELECT periodo, mes, rut,
      (SELECT COALESCE(sum(valor),0) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO01 AND r.mes=t.mes) as monto1,

      -- Leyes Sociales: Cotiz. Previsional + CCI + Expectativa de Vida
      --(SELECT COALESCE(sum(valor),0) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO02 AND r.mes=t.mes)
	  COALESCE((SELECT sum(valor) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO_FONDOVEJEZ AND r.mes=t.mes), 0)
      + COALESCE((SELECT sum(valor) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO_FONDOSALUD  AND r.mes=t.mes), 0)
      + COALESCE((SELECT sum(valor) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO_COTADICIONALSALUD  AND r.mes=t.mes), 0)
	  + COALESCE((SELECT sum(valor) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO_SEGCESANTRABAJADOR AND r.mes=t.mes), 0)
      + COALESCE((SELECT sum(valor) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO_CCI AND r.mes=t.mes), 0)
      + COALESCE((SELECT sum(valor) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO_CEV  AND r.mes=t.mes), 0)
      as monto2,

      (SELECT COALESCE(sum(valor),0) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO03 AND r.mes=t.mes) as monto3,
      (SELECT COALESCE(sum(valor),0) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO04 AND r.mes=t.mes) as monto4,
      (SELECT COALESCE(sum(valor),0) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO05 AND r.mes=t.mes) as monto5,
      (SELECT COALESCE(sum(valor),0) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO06 AND r.mes=t.mes) as monto6,
      (SELECT COALESCE(sum(valor),0) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO07 AND r.mes=t.mes) as monto7,
      (SELECT COALESCE(sum(valor),0) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO08 AND r.mes=t.mes) as monto8,
      (SELECT COALESCE(sum(valor),0) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO09 AND r.mes=t.mes) as monto9,
      (SELECT COALESCE(sum(valor),0) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO10 AND r.mes=t.mes) as monto10,
      (SELECT COALESCE(sum(valor),0) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO11 AND r.mes=t.mes) as monto11,
      (SELECT COALESCE(sum(valor),0) FROM #resultados R WHERE r.rut=T.RUT AND idConcepto=@CONCEPTO12 AND r.mes=t.mes) as monto12
  INTO #DATOS
  FROM #resultados T
  GROUP BY periodo, rut, mes

  INSERT INTO reminforme1887(periodo, mes, rut, monto_1, monto_2, monto_3, monto_4, monto_5, monto_6, monto_7, monto_8, monto_9, monto_10, monto_11, monto_12)
  SELECT periodo, mes, rut,
      monto1, monto2, monto3, monto4, monto5, monto6, monto7, monto8, monto9, monto10, monto11, monto12
  FROM #DATOS

  SELECT rut, row_number() OVER (ORDER BY rut) n
  INTO #CORRELTAVIVOS
  FROM REMinforme1887 WHERE periodo = @PERIODO
  GROUP BY rut

  UPDATE REMinforme1887
  SET correlativo = (SELECT n FROM #CORRELTAVIVOS WHERE rut = REMinforme1887.rut),
      factor = CASE mes
          WHEN 1  THEN ISNULL(CONVERT(decimal(18,4), @mes01), 0)
          WHEN 2  THEN ISNULL(CONVERT(decimal(18,4), @mes02), 0)
          WHEN 3  THEN ISNULL(CONVERT(decimal(18,4), @mes03), 0)
          WHEN 4  THEN ISNULL(CONVERT(decimal(18,4), @mes04), 0)
          WHEN 5  THEN ISNULL(CONVERT(decimal(18,4), @mes05), 0)
          WHEN 6  THEN ISNULL(CONVERT(decimal(18,4), @mes06), 0)
          WHEN 7  THEN ISNULL(CONVERT(decimal(18,4), @mes07), 0)
          WHEN 8  THEN ISNULL(CONVERT(decimal(18,4), @mes08), 0)
          WHEN 9  THEN ISNULL(CONVERT(decimal(18,4), @mes09), 0)
          WHEN 10 THEN ISNULL(CONVERT(decimal(18,4), @mes10), 0)
          WHEN 11 THEN ISNULL(CONVERT(decimal(18,4), @mes11), 0)
          WHEN 12 THEN ISNULL(CONVERT(decimal(18,4), @mes12), 0)
      END
  WHERE periodo = @PERIODO

  SELECT rut AS C1, mes,
      sum(monto_3 * factor) as C2,
      sum(monto_4 * factor) as C3,
      sum(monto_5 * factor) as C4,
      sum(monto_7 * factor) as C5,
      sum(monto_6 * factor) as C6,
      sum(monto_8 * factor) as C7,
      sum(monto_9 * factor) as C8,
      min(correlativo) as correlativo, min(CONVERT(decimal(18,4), factor)) AS factor
  INTO #tabla
  FROM REMinforme1887
  WHERE periodo = @PERIODO
  GROUP BY rut, mes

  SELECT C1,
      ISNULL(sum(c2), 0) as c2, ISNULL(sum(c3), 0) as c3, ISNULL(sum(c4), 0) as c4,
      ISNULL(sum(c5), 0) as c5, ISNULL(sum(c6), 0) as c6, ISNULL(sum(c7), 0) as c7,
      ISNULL(sum(c8), 0) as c8,
      CASE WHEN (SELECT COUNT(C1) FROM #tabla WHERE mes=1  AND c1=A.c1) > 0 THEN 'C' ELSE '' END AS C9,
      CASE WHEN (SELECT COUNT(C1) FROM #tabla WHERE mes=2  AND c1=A.c1) > 0 THEN 'C' ELSE '' END AS C10,
      CASE WHEN (SELECT COUNT(C1) FROM #tabla WHERE mes=3  AND c1=A.c1) > 0 THEN 'C' ELSE '' END AS C11,
      CASE WHEN (SELECT COUNT(C1) FROM #tabla WHERE mes=4  AND c1=A.c1) > 0 THEN 'C' ELSE '' END AS C12,
      CASE WHEN (SELECT COUNT(C1) FROM #tabla WHERE mes=5  AND c1=A.c1) > 0 THEN 'C' ELSE '' END AS C13,
      CASE WHEN (SELECT COUNT(C1) FROM #tabla WHERE mes=6  AND c1=A.c1) > 0 THEN 'C' ELSE '' END AS C14,
      CASE WHEN (SELECT COUNT(C1) FROM #tabla WHERE mes=7  AND c1=A.c1) > 0 THEN 'C' ELSE '' END AS C15,
      CASE WHEN (SELECT COUNT(C1) FROM #tabla WHERE mes=8  AND c1=A.c1) > 0 THEN 'C' ELSE '' END AS C16,
      CASE WHEN (SELECT COUNT(C1) FROM #tabla WHERE mes=9  AND c1=A.c1) > 0 THEN 'C' ELSE '' END AS C17,
      CASE WHEN (SELECT COUNT(C1) FROM #tabla WHERE mes=10 AND c1=A.c1) > 0 THEN 'C' ELSE '' END AS C18,
      CASE WHEN (SELECT COUNT(C1) FROM #tabla WHERE mes=11 AND c1=A.c1) > 0 THEN 'C' ELSE '' END AS C19,
      CASE WHEN (SELECT COUNT(C1) FROM #tabla WHERE mes=12 AND c1=A.c1) > 0 THEN 'C' ELSE '' END AS C20,
      ISNULL(min(CONVERT(decimal(18,4), factor)), 0) as factor,
      min(correlativo) as C21,
      ISNULL((SELECT ISNULL(monto_3,0) FROM REMinforme1887 WHERE periodo=@PERIODO AND mes=1  AND rut=A.c1), 0) as C22,
      ISNULL((SELECT ISNULL(monto_3,0) FROM REMinforme1887 WHERE periodo=@PERIODO AND mes=2  AND rut=A.c1), 0) as C23,
      ISNULL((SELECT ISNULL(monto_3,0) FROM REMinforme1887 WHERE periodo=@PERIODO AND mes=3  AND rut=A.c1), 0) as C24,
      ISNULL((SELECT ISNULL(monto_3,0) FROM REMinforme1887 WHERE periodo=@PERIODO AND mes=4  AND rut=A.c1), 0) as C25,
      ISNULL((SELECT ISNULL(monto_3,0) FROM REMinforme1887 WHERE periodo=@PERIODO AND mes=5  AND rut=A.c1), 0) as C26,
      ISNULL((SELECT ISNULL(monto_3,0) FROM REMinforme1887 WHERE periodo=@PERIODO AND mes=6  AND rut=A.c1), 0) as C27,
      ISNULL((SELECT ISNULL(monto_3,0) FROM REMinforme1887 WHERE periodo=@PERIODO AND mes=7  AND rut=A.c1), 0) as C28,
      ISNULL((SELECT ISNULL(monto_3,0) FROM REMinforme1887 WHERE periodo=@PERIODO AND mes=8  AND rut=A.c1), 0) as C29,
      ISNULL((SELECT ISNULL(monto_3,0) FROM REMinforme1887 WHERE periodo=@PERIODO AND mes=9  AND rut=A.c1), 0) as C30,
      ISNULL((SELECT ISNULL(monto_3,0) FROM REMinforme1887 WHERE periodo=@PERIODO AND mes=10 AND rut=A.c1), 0) as C31,
      ISNULL((SELECT ISNULL(monto_3,0) FROM REMinforme1887 WHERE periodo=@PERIODO AND mes=11 AND rut=A.c1), 0) as C32,
      ISNULL((SELECT ISNULL(monto_3,0) FROM REMinforme1887 WHERE periodo=@PERIODO AND mes=12 AND rut=A.c1), 0) as C33
  FROM #tabla A
  GROUP BY C1