ALTER PROCEDURE [dbo].[sp_remPagoImposicionesCCAF]
(
	@PERIODO INT = 0,
	@MES INT = 0,
	@ficini INT = 0,
	@ficfin INT = 0,
	@unineg INT = 0,
	@DEP INT = 0,
	@SEC INT = 0,
	@caja INT = 0
)
AS
BEGIN
	DECLARE @CAJA1 AS INT = (SELECT caja FROM remParametros)
	DECLARE @CAJA2 AS INT = (SELECT caja2 FROM remParametros)

	DECLARE @TOP TABLE (numlin bigint, validador int, ficha int, rut int, dv varchar(1), apePaterno varchar(30), apeMaterno varchar(30),
						nombres varchar(100), [Remuneracion Imponible Con Isapre] bigint, [Remuneracion Imponible Sin Isapre] bigint, [Cotizacion] bigint, 
						[Dias Trabajados] bigint,	[Carga Normal] int, [Carga Invalida] int, [Carga Maternal] int,	[Asignacion Familiar] bigint,
						[Tramo Asinacion] varchar(100), [Pago Retroactivo] bigint, [Pago Reintegro] bigint, Codigo int, FechaIni VARCHAR(10), FechaFin VARCHAR(10))

	-- Crear las tablas temporales antes del IF
	CREATE TABLE #CCAF (
		ficha int,
		rut int,
		dv varchar(1),
		apePaterno varchar(30),
		apeMaterno varchar(30),
		nombres varchar(100),
		[Remuneracion Imponible Con Isapre] bigint,
		[Remuneracion Imponible Sin Isapre] bigint,
		[Cotizacion 0.6%] bigint,
		[Cotizacion 3.1%] bigint,
		[Cotizacion 6.45%] bigint,
		[Dias Trabajados] bigint,
		[Carga Normal] int,
		[Carga Invalida] int,
		[Carga Maternal] int,
		[Asignacion Familiar] bigint,
		[Tramo Asinacion] varchar(100),
		[Pago Retroactivo] bigint,
		[Pago Reintegro] bigint
	)

	CREATE TABLE #MOV (
		ficha int,
		Codigo int,
		FechaIni varchar(10),
		FechaFin varchar(10)
	)

	-- Si es CAJA2, insertar todo en cero
	IF @caja = @CAJA2
	BEGIN
		INSERT INTO #CCAF
		SELECT 
		T.ficha,
		T.rut, T.dv, T.apePaterno, T.apeMaterno, T.nombres, 
		0 AS 'Remuneracion Imponible Con Isapre',
		0 AS 'Remuneracion Imponible Sin Isapre',
		0 AS 'Cotizacion 0.6%',
		0 AS 'Cotizacion 3.1%',
		0 AS 'Cotizacion 6.45%',
		0 AS 'Dias Trabajados',
		0 AS 'Carga Normal',
		0 AS 'Carga Invalida',
		0 AS 'Carga Maternal',	
		0 AS 'Asignacion Familiar',
		CASE
		WHEN T.tramoasigfamiliar = 0 OR T.tramoasigfamiliar = 1 THEN 'A'
		WHEN T.tramoasigfamiliar = 2 THEN 'B'
		WHEN T.tramoasigfamiliar = 3 THEN 'C'
		WHEN T.tramoasigfamiliar = 4 THEN 'D'
		END AS 'Tramo Asinacion',
		0 AS 'Pago Retroactivo',
		0 AS 'Pago Reintegro'
		FROM
		remTrabajadores AS T
		WHERE T.periodo = @PERIODO 
		AND	T.mes = @MES
		AND	(T.ficha BETWEEN @ficini AND @ficfin)
		AND (@unineg = 0 or T.unidadNegocios = @unineg) 
		AND	(@DEP = 0 or T.departamento = @DEP) 
		AND (@SEC = 0 or T.seccion = @SEC)
		AND	T.activo = 1
		ORDER BY T.apePaterno, T.apeMaterno

		INSERT INTO #MOV
		SELECT 
			T.ficha,
			0 AS Codigo,
			'' AS FechaIni,
			'' AS FechaFin
		FROM
			remMovPersonal AS MP 
		JOIN 
			remTrabajadores AS T ON MP.periodo = T.periodo AND MP.mes = T.mes AND MP.ficha = T.ficha
		WHERE 
			T.periodo = @PERIODO
			AND T.mes = @MES
			AND (T.ficha BETWEEN @ficini AND @ficfin)
			AND (@unineg = 0 OR T.unidadNegocios = @unineg) 
			AND (@DEP = 0 OR T.departamento = @DEP)
			AND (@SEC = 0 OR T.seccion = @SEC)
			AND T.activo = 1;
	END
	ELSE
	BEGIN
		INSERT INTO #CCAF
		SELECT 
		T.ficha,
		T.rut, T.dv, T.apePaterno, T.apeMaterno, T.nombres, 
		CASE
		WHEN T.instSalud <> 7 THEN dbo.remValorResultado(T.periodo,T.mes,T.ficha, 36)
		WHEN T.instSalud = 7 THEN 0
		END AS  'Remuneracion Imponible Con Isapre',
		CASE
		WHEN T.instSalud = 7 THEN dbo.remValorResultado(T.periodo,T.mes,T.ficha, 36)
		WHEN T.instSalud <> 7 THEN 0
		END AS  'Remuneracion Imponible Sin Isapre',
		CASE
		WHEN T.instSalud = 7 THEN dbo.remValorResultado(T.periodo,T.mes,T.ficha, 36) * 0.006
		WHEN T.instSalud <> 7 THEN 0
		END AS  'Cotizacion 0.6%',
		CASE
		WHEN T.instSalud = 7 THEN dbo.remValorResultado(T.periodo,T.mes,T.ficha, 36) * (CONVERT(DECIMAL(18, 4), (SELECT REPLACE(valor, ',', '.') FROM remFactores WHERE periodo = @PERIODO AND mes = @MES AND codigo = 101)) / 100)
		WHEN T.instSalud <> 7 THEN 0
		END AS  'Cotizacion 3.1%',
		CASE
		WHEN T.instSalud = 7 THEN dbo.remValorResultado(T.periodo,T.mes,T.ficha, 36) * 0.0645
		WHEN T.instSalud <> 7 THEN 0
		END AS  'Cotizacion 6.45%',
		dbo.remValorResultado(T.periodo,T.mes,T.ficha, 12) AS  'Dias Trabajados',
		T.CargaNormal AS 'Carga Normal',
		T.CargaInvalida AS 'Carga Invalida',
		T.CargaMaternal AS 'Carga Maternal',	
		dbo.remValorResultado(T.periodo,T.mes,T.ficha, 10) AS  'Asignacion Familiar',
		CASE
		WHEN T.tramoasigfamiliar = 0 OR T.tramoasigfamiliar = 1 THEN 'A'
		WHEN T.tramoasigfamiliar = 2 THEN 'B'
		WHEN T.tramoasigfamiliar = 3 THEN 'C'
		WHEN T.tramoasigfamiliar = 4 THEN 'D'
		END AS 'Tramo Asinacion',
		dbo.remValorResultado(T.periodo,T.mes,T.ficha, 11) AS 'Pago Retroactivo',
		dbo.remValorResultado(T.periodo,T.mes,T.ficha, 15) AS 'Pago Reintegro'
		FROM
		remTrabajadores AS T
		WHERE T.periodo = @PERIODO 
		AND	T.mes = @MES
		AND	(T.ficha BETWEEN @ficini AND @ficfin)
		AND (@unineg = 0 or T.unidadNegocios = @unineg) 
		AND	(@DEP = 0 or T.departamento = @DEP) 
		AND (@SEC = 0 or T.seccion = @SEC)
		AND	T.activo = 1
		ORDER BY T.apePaterno, T.apeMaterno

		INSERT INTO #MOV
		SELECT 
			T.ficha,
			ISNULL(MP.codMovimiento, 0) AS Codigo,
			ISNULL(CONVERT(varchar(10), MP.fechaInicio, 103), '') AS FechaIni,
			ISNULL(CONVERT(varchar(10), MP.fechaTermino, 103), '') AS FechaFin
		FROM
			remMovPersonal AS MP 
		JOIN 
			remTrabajadores AS T ON MP.periodo = T.periodo AND MP.mes = T.mes AND MP.ficha = T.ficha
		WHERE 
			T.periodo = @PERIODO
			AND T.mes = @MES
			AND (T.ficha BETWEEN @ficini AND @ficfin)
			AND (@unineg = 0 OR T.unidadNegocios = @unineg) 
			AND (@DEP = 0 OR T.departamento = @DEP)
			AND (@SEC = 0 OR T.seccion = @SEC)
			AND T.activo = 1;
	END

	-- El resto del código continúa igual...
	IF (@PERIODO = 2020 AND @MES < 12) OR @PERIODO < 2020
	BEGIN
		INSERT INTO @TOP
		SELECT
			ROW_NUMBER() OVER(PARTITION BY C.ficha ORDER BY C.ficha)as numlin,
			1 AS validador,
			C.ficha,
			C.rut,
			C.dv,
			C.apePaterno,
			C.apeMaterno,
			C.nombres,
			C.[Remuneracion Imponible Con Isapre],
			C.[Remuneracion Imponible Sin Isapre],
			C.[Cotizacion 0.6%] as Cotizacion,
			C.[Dias Trabajados],
			C.[Carga Normal],
			C.[Carga Invalida],
			C.[Carga Maternal],
			C.[Asignacion Familiar],
			C.[Tramo Asinacion],
			C.[Pago Retroactivo],
			C.[Pago Reintegro],
			ISNULL (M.codigo, 0) AS Codigo,
			ISNULL (M.FechaIni, '') AS FechaIni,
			ISNULL (M.FechaFin, '') AS FechaFin
		FROM #CCAF AS C LEFT OUTER JOIN #MOV AS M ON C.ficha = M.ficha

		UNION ALL

		SELECT
			ROW_NUMBER() OVER(PARTITION BY C.ficha ORDER BY C.ficha)as numlin,
			0 AS validador,
			C.ficha,
			C.rut,
			C.dv,
			C.apePaterno,
			C.apeMaterno,
			C.nombres,
			0 AS [Remuneracion Imponible Con Isapre],
			0 AS [Remuneracion Imponible Sin Isapre],
			0 AS Cotizacion,
			0 AS [Dias Trabajados],
			0 AS [Carga Normal],
			0 AS [Carga Invalida],
			0 AS [Carga Maternal],
			0 AS [Asignacion Familiar],
			C.[Tramo Asinacion],
			0 AS [Pago Retroactivo],
			0 AS [Pago Reintegro],
			ISNULL (M.codigo, 0) AS Codigo,
			ISNULL (M.FechaIni, '') AS FechaIni,
			ISNULL (M.FechaFin, '') AS FechaFin
		FROM #CCAF AS C LEFT OUTER JOIN #MOV AS M ON C.ficha = M.ficha

		SELECT * FROM @TOP
		WHERE (validador= 0 AND numlin > 1) OR (validador = 1 AND numlin = 1)
		ORDER BY apePaterno, apeMaterno, [Remuneracion Imponible Sin Isapre] DESC,  [Remuneracion Imponible Con Isapre] DESC

		END;

	ELSE IF ((@PERIODO = 2020 AND @MES = 12) OR (@MES = 1 AND @PERIODO = 2022)) OR @PERIODO = 2021
		BEGIN
		INSERT INTO @TOP
		SELECT
			ROW_NUMBER() OVER(PARTITION BY C.ficha ORDER BY C.ficha)as numlin,
			1 AS validador,
			C.ficha,
			C.rut,
			C.dv,
			C.apePaterno,
			C.apeMaterno,
			C.nombres,
			C.[Remuneracion Imponible Con Isapre],
			C.[Remuneracion Imponible Sin Isapre],
			C.[Cotizacion 3.1%] as Cotizacion,
			C.[Dias Trabajados],
			C.[Carga Normal],
			C.[Carga Invalida],
			C.[Carga Maternal],
			C.[Asignacion Familiar],
			C.[Tramo Asinacion],
			C.[Pago Retroactivo],
			C.[Pago Reintegro],
			ISNULL (M.codigo, 0) AS Codigo,
			ISNULL (M.FechaIni, '') AS FechaIni,
			ISNULL (M.FechaFin, '') AS FechaFin
		FROM #CCAF AS C LEFT OUTER JOIN #MOV AS M ON C.ficha = M.ficha

		UNION ALL

		SELECT
			ROW_NUMBER() OVER(PARTITION BY C.ficha ORDER BY C.ficha)as numlin,
			0 AS validador,
			C.ficha,
			C.rut,
			C.dv,
			C.apePaterno,
			C.apeMaterno,
			C.nombres,
			0 AS [Remuneracion Imponible Con Isapre],
			0 AS [Remuneracion Imponible Sin Isapre],
			0 AS Cotizacion,
			0 AS [Dias Trabajados],
			0 AS [Carga Normal],
			0 AS [Carga Invalida],
			0 AS [Carga Maternal],
			0 AS [Asignacion Familiar],
			C.[Tramo Asinacion],
			0 AS [Pago Retroactivo],
			0 AS [Pago Reintegro],
			ISNULL (M.codigo, 0) AS Codigo,
			ISNULL (M.FechaIni, '') AS FechaIni,
			ISNULL (M.FechaFin, '') AS FechaFin
		FROM #CCAF AS C LEFT OUTER JOIN #MOV AS M ON C.ficha = M.ficha

		SELECT * FROM @TOP
		WHERE (validador= 0 AND numlin > 1) OR (validador = 1 AND numlin = 1)
		ORDER BY apePaterno, apeMaterno, [Remuneracion Imponible Sin Isapre] DESC, [Remuneracion Imponible Con Isapre] DESC

		END;
	ELSE IF (@PERIODO = 2022 AND @MES > 1) OR (@PERIODO = 2023 AND @MES < 2)
		BEGIN
		INSERT INTO @TOP
		SELECT
			ROW_NUMBER() OVER(PARTITION BY C.ficha ORDER BY C.ficha)as numlin,
			1 AS validador,
			C.ficha,
			C.rut,
			C.dv,
			C.apePaterno,
			C.apeMaterno,
			C.nombres,
			C.[Remuneracion Imponible Con Isapre],
			C.[Remuneracion Imponible Sin Isapre],
			C.[Cotizacion 6.45%] as Cotizacion,
			C.[Dias Trabajados],
			C.[Carga Normal],
			C.[Carga Invalida],
			C.[Carga Maternal],
			C.[Asignacion Familiar],
			C.[Tramo Asinacion],
			C.[Pago Retroactivo],
			C.[Pago Reintegro],
			ISNULL (M.codigo, 0) AS Codigo,
			ISNULL (M.FechaIni, '') AS FechaIni,
			ISNULL (M.FechaFin, '') AS FechaFin
		FROM #CCAF AS C LEFT OUTER JOIN #MOV AS M ON C.ficha = M.ficha

		UNION ALL

		SELECT
			ROW_NUMBER() OVER(PARTITION BY C.ficha ORDER BY C.ficha)as numlin,
			0 AS validador,
			C.ficha,
			C.rut,
			C.dv,
			C.apePaterno,
			C.apeMaterno,
			C.nombres,
			0 AS [Remuneracion Imponible Con Isapre],
			0 AS [Remuneracion Imponible Sin Isapre],
			0 AS Cotizacion,
			0 AS [Dias Trabajados],
			0 AS [Carga Normal],
			0 AS [Carga Invalida],
			0 AS [Carga Maternal],
			0 AS [Asignacion Familiar],
			C.[Tramo Asinacion],
			0 AS [Pago Retroactivo],
			0 AS [Pago Reintegro],
			ISNULL (M.codigo, 0) AS Codigo,
			ISNULL (M.FechaIni, '') AS FechaIni,
			ISNULL (M.FechaFin, '') AS FechaFin
		FROM #CCAF AS C LEFT OUTER JOIN #MOV AS M ON C.ficha = M.ficha

		SELECT * FROM @TOP
		WHERE (validador= 0 AND numlin > 1) OR (validador = 1 AND numlin = 1)
		ORDER BY apePaterno, apeMaterno, [Remuneracion Imponible Sin Isapre] DESC, [Remuneracion Imponible Con Isapre] DESC
		END;

	ELSE IF DATEFROMPARTS(@PERIODO, @MES, 1) > '2023-01-01'
		BEGIN
		INSERT INTO @TOP
		SELECT
			ROW_NUMBER() OVER(PARTITION BY C.ficha ORDER BY C.ficha)as numlin,
			1 AS validador,
			C.ficha,
			C.rut,
			C.dv,
			C.apePaterno,
			C.apeMaterno,
			C.nombres,
			C.[Remuneracion Imponible Con Isapre],
			C.[Remuneracion Imponible Sin Isapre],
			C.[Cotizacion 3.1%] as Cotizacion,
			C.[Dias Trabajados],
			C.[Carga Normal],
			C.[Carga Invalida],
			C.[Carga Maternal],
			C.[Asignacion Familiar],
			C.[Tramo Asinacion],
			C.[Pago Retroactivo],
			C.[Pago Reintegro],
			ISNULL (M.codigo, 0) AS Codigo,
			ISNULL (M.FechaIni, '') AS FechaIni,
			ISNULL (M.FechaFin, '') AS FechaFin
		FROM #CCAF AS C LEFT OUTER JOIN #MOV AS M ON C.ficha = M.ficha

		UNION ALL

		SELECT
			ROW_NUMBER() OVER(PARTITION BY C.ficha ORDER BY C.ficha)as numlin,
			0 AS validador,
			C.ficha,
			C.rut,
			C.dv,
			C.apePaterno,
			C.apeMaterno,
			C.nombres,
			0 AS [Remuneracion Imponible Con Isapre],
			0 AS [Remuneracion Imponible Sin Isapre],
			0 AS Cotizacion,
			0 AS [Dias Trabajados],
			0 AS [Carga Normal],
			0 AS [Carga Invalida],
			0 AS [Carga Maternal],
			0 AS [Asignacion Familiar],
			C.[Tramo Asinacion],
			0 AS [Pago Retroactivo],
			0 AS [Pago Reintegro],
			ISNULL (M.codigo, 0) AS Codigo,
			ISNULL (M.FechaIni, '') AS FechaIni,
			ISNULL (M.FechaFin, '') AS FechaFin
		FROM #CCAF AS C LEFT OUTER JOIN #MOV AS M ON C.ficha = M.ficha

		SELECT * FROM @TOP
		WHERE (validador= 0 AND numlin > 1) OR (validador = 1 AND numlin = 1)
		ORDER BY apePaterno, apeMaterno, [Remuneracion Imponible Sin Isapre] DESC, [Remuneracion Imponible Con Isapre] DESC
		END;

	-- Limpiar tablas temporales
	DROP TABLE #CCAF
	DROP TABLE #MOV

END