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
)
AS
BEGIN


	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))


	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'
	INTO #CCAF
	FROM
	remTrabajadores AS T --LEFT OUTER JOIN remIsapre AS ISA
	--ON T.periodo = ISA.periodo AND T.mes = ISA.mes AND T.instSalud = ISA.idIsapre
	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

	SELECT 
		T.ficha,
		ISNULL(MP.codMovimiento, 0) AS Codigo,
		ISNULL(CONVERT(varchar(10), MP.fechaInicio, 103), '') AS FechaIni, -- 103 indica el formato dd-MM-yyyy
		ISNULL(CONVERT(varchar(10), MP.fechaTermino, 103), '') AS FechaFin -- 103 indica el formato dd-MM-yyyy
	INTO #MOV
	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;

	--SELECT * FROM #CCAF AS C INNER JOIN #MOV AS M ON C.ficha = M.ficha
	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
		--INTO #TOP
		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 
		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
		--INTO #TOP1
		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 #TOP1 
		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
		--INTO #TOP2
		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 #TOP2 
		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
		--INTO #TOP3
		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 #TOP2
		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;

END