ALTER PROCEDURE [dbo].[sp_remPagoImposicionesIsapre]
(
	@PERIODO INT = 0,
	@MES INT = 0,
	@ficini INT = 0,
	@ficfin INT = 0,
	@unineg INT = 0,
	@DEP INT = 0,
	@SEC INT = 0,
	@idIsapre INT =0
)
AS
BEGIN

	SELECT
		T.ficha,
		T.rut, T.dv,
		0 AS 'FUN',
		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, 7) AS  'Cotizacion 7%',
		0 AS 'Ley 18.566',
		dbo.remValorResultado(T.periodo,T.mes,T.ficha, 8) AS  'Cotizacion Adicional',
		0 AS 'Otros Desc. de Salud',
		(dbo.remValorResultado(T.periodo,T.mes,T.ficha, 7) + dbo.remValorResultado(T.periodo,T.mes,T.ficha, 8)) AS  'Cotizacion a Pagar',
		0 AS 'Cotizacion Pactada'
	INTO #IS
	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
		AND	T.instSalud = @idIsapre
		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 (@SEC = 0 or T.seccion = @SEC)
		AND T.activo = 1
		AND	T.instSalud = @idIsapre

	--SELECT * FROM #IS AS I INNER JOIN #MOV AS M ON I.ficha = M.ficha

	SELECT
		ROW_NUMBER() OVER(PARTITION BY F.ficha ORDER BY F.ficha)as numlin,
		1 AS validador,
		F.ficha,
		F.rut,
		F.dv,
		F.[FUN],
		F.apePaterno,
		F.apeMaterno,
		F.nombres,
		F.[Remuneracion Imponible],
		F.[Cotizacion 7%],
		F.[Ley 18.566],
		F.[Cotizacion Adicional],
		F.[Otros Desc. de Salud],
		F.[Cotizacion a Pagar],
		F.[Cotizacion Pactada],
		ISNULL (M.codigo, 0) AS Codigo,
		ISNULL (M.FechaIni, '') AS FechaIni,
		ISNULL (M.FechaFin, '') AS FechaFin
	INTO #TOP
	FROM #IS AS F LEFT OUTER JOIN #MOV AS M ON F.ficha = M.ficha

	UNION ALL

	SELECT
		ROW_NUMBER() OVER(PARTITION BY F.ficha ORDER BY F.ficha)as numlin,
		0 AS validador,
		F.ficha,
		F.rut,
		F.dv,
		0 AS [FUN],
		F.apePaterno,
		F.apeMaterno,
		F.nombres,
		0 AS [Remuneracion Imponible],
		0 AS [Cotizacion 7%],
		0 AS [Ley 18.566],
		0 AS [Cotizacion Adicional],
		0 AS [Otros Desc. de Salud],
		0 AS [Cotizacion a Pagar],
		0 AS [Cotizacion Pactada],
		ISNULL (M.codigo, 0) AS Codigo,
		ISNULL (M.FechaIni, '') AS FechaIni,
		ISNULL (M.FechaFin, '') AS FechaFin
	FROM #IS AS F LEFT OUTER JOIN #MOV AS M ON F.ficha = M.ficha

	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