ALTER PROCEDURE [dbo].[sp_remPagoImposicionesAFP]
(
	@PERIODO INT = 0,
	@MES INT = 0,
	@ficini INT = 0,
	@ficfin INT = 0,
	@unineg INT = 0,
	@DEP INT = 0,
	@idPrevision INT =0
)
AS
BEGIN
	SELECT
	T.ficha,
	T.rut,
	T.dv,
	T.apePaterno,
	T.apeMaterno,
	T.nombres,
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 36) AS  'Remuneracion Imponible',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 4) + dbo.remValorResultado(T.periodo,T.mes,T.ficha, 48) AS  'Cotizacion Obligatoria',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 38) AS  'SIS',	
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 5) AS  'Cotizacion Voluntaria',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 39) AS  'APV1',
	T.contratoApvi1 AS ContratoApv1,
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 40) AS  'APV2',
	T.contratoApvi2 AS ContratoApv2,
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 41) AS  'APV3',
	T.contratoApvi3 AS ContratoApv3,
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 24) AS MontoConvenio,
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 6) AS  'Ahorro',
	CASE
	WHEN dbo.remValorResultado(T.periodo,T.mes,T.ficha, 22) = 0 AND dbo.remValorResultado(T.periodo,T.mes,T.ficha, 23) = 0 THEN 0 
	WHEN dbo.remValorResultado(T.periodo,T.mes,T.ficha, 22) > 0 OR dbo.remValorResultado(T.periodo,T.mes,T.ficha, 23) > 0 THEN dbo.remValorResultado(T.periodo,T.mes,T.ficha, 37)
	END AS 'Remuneración Imponible Cesantia',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 22) AS  'Cesantia Trabajador',
	dbo.remValorResultado(T.periodo,T.mes,T.ficha, 23) AS  'Cesantia Empleador'
	INTO #AFP
	FROM
		remTrabajadores AS T INNER JOIN remPrevisiones AS AFP
		ON T.periodo = AFP.periodo AND T.mes = AFP.mes AND T.idPrevision = AFP.idPRevision AND T.regimenPrevisional = AFP.idRegimen
	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 T.activo = 1
		AND T.idPrevision = @idPrevision
	ORDER BY T.apePaterno,
		T.apeMaterno

	SELECT 
		T.ficha,
		ISNULL (MP.codMovimiento, 0) AS Codigo,
		ISNULL (MP.fechaInicio, '') AS FechaIni,
		ISNULL (MP.fechaTermino, '') AS FechaFin
	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 T.activo = 1
		AND T.idPrevision = @idPrevision

	--SELECT  * FROM #AFP AS A RIGHT OUTER JOIN #MOV AS M ON A.ficha = M.ficha


	
	SELECT 
		ROW_NUMBER() OVER(PARTITION BY A.ficha ORDER BY A.ficha)as numlin,
		1 AS validador,
		A.ficha,
		A.rut,
		A.dv,
		A.apePaterno,
		A.apeMaterno,
		A.nombres,
		A.[Remuneracion Imponible],
		A.[Cotizacion Obligatoria],
		A.SIS,
		A.[Cotizacion Voluntaria],
		A.APV1,
		A.ContratoApv1,
		A.APV2,
		A.ContratoApv2,
		A.APV3,
		A.ContratoApv3,
		A.MontoConvenio,
		A.Ahorro,
		A.[Remuneración Imponible Cesantia],
		A.[Cesantia Trabajador],
		A.[Cesantia Empleador],
		ISNULL (M.codigo, 0) AS Codigo,
		ISNULL (M.FechaIni, '') AS FechaIni,
		ISNULL (M.FechaFin, '') AS FechaFin
	 INTO #TOP	
	 FROM #AFP AS A LEFT OUTER JOIN #MOV AS M ON A.ficha = M.ficha
	 
	 UNION ALL
	 SELECT	
		ROW_NUMBER() OVER(PARTITION BY A.ficha ORDER BY A.ficha)as numlin,
		0 AS validador,
		A.ficha,
		A.rut,
		A.dv,
		A.apePaterno,
		A.apeMaterno,
		A.nombres,
		0 as [Remuneracion Imponible],
		0 as [Cotizacion Obligatoria],
		0 as SIS,
		0 as [Cotizacion Voluntaria],
		0 as APV1,
		0 as ContratoApv1,
		0 asAPV2,
		0 as ContratoApv2,
		0 as APV3,
		0 as ContratoApv3,
		0 as MontoConvenio,
		0 as Ahorro,
		0 as [Remuneración Imponible Cesantia],
		0 as [Cesantia Trabajador],
		0 as [Cesantia Empleador],
		ISNULL (M.codigo, 0) AS Codigo,
		ISNULL (M.FechaIni, '') AS FechaIni,
		ISNULL (M.FechaFin, '') AS FechaFin
	 	
	 FROM #AFP AS A LEFT OUTER JOIN #MOV AS M ON A.ficha = M.ficha
	 




	 --SELECT ROW_NUMBER() OVER(PARTITION BY #TOP.ficha ORDER BY #TOP.ficha) AS NumeroFila,
	 --*
		
	--INTO #TOP2
	--FROM #TOP
	--WHERE #TOP.rut  IN (#TOP.rut )


	----ELIMINA EL PRIMER REGISTRO DUPLICADO POR NUMLIN
	----WITH
	----cteFilas AS
	----(
	----	SELECT *, 
	----	ROW_NUMBER() OVER(PARTITION BY #TOP.rut ORDER BY #TOP.ficha) AS NumeroFila
	----	FROM #TOP
	----	WHERE #TOP.rut IN (#TOP.rut)
	----)
	----DELETE FROM cteFilas
	----WHERE NumeroFila = 2;

	
	----DELETE FROM #TOP
	----WHERE #TOP.numlin = 2;


	--SELECT DE LA TABLA FINAL CON DATOS
	SELECT *  FROM #TOP 
	WHERE (validador= 0 AND numlin > 1) OR (validador = 1 AND numlin = 1)
	ORDER BY #TOP.apePaterno, #TOP.apeMaterno, #TOP.[Remuneracion Imponible] DESC

END