ALTER PROCEDURE [dbo].[sp_remPrevired]
(
--@unineg INT=0,
@DEP     INT = 0, 
@SEC     INT = 0, 
@PERIODO INT = 0, 
@MES     INT = 0
)
AS
    BEGIN
        SET NOCOUNT ON;

        --DECLARE @PERIODO INT
        --DECLARE @MES INT
        DECLARE @CAJA INT;
        DECLARE @MUTUAL INT;
        --SELECT @PERIODO=PERIODO,@MES=MES,@CAJA=CAJA FROM REMPARAMETROS
        SELECT @CAJA = caja
        FROM remParametros;
        SELECT @MUTUAL = mutual
        FROM remParametros;
        SELECT idPrevision, 
               codprevired, 
               idRegimen
        INTO #remPrevisiones
        FROM remPrevisiones P
        WHERE P.periodo = @PERIODO
              AND P.mes = @MES;
        SELECT idIsapre, 
               codprevired
        INTO #remisapres
        FROM remIsapre P
        WHERE P.periodo = @PERIODO
              AND P.mes = @MES;
        SELECT ficha AS '000', 
               rut AS '001', 
               dv AS '002', 
               apePaterno AS '003', 
               apeMaterno AS '004', 
               nombres AS '005',
               CASE sexo
                   WHEN 0
                   THEN 'F'
                   ELSE 'M'
               END AS '006',
               CASE extranjero
                   WHEN 1
                   THEN 0
                   ELSE 1
               END AS '007', 
               1 AS '008', 
               RIGHT('00' + CONVERT(NVARCHAR, mes), 2) + CONVERT(NVARCHAR, periodo) AS '009', 
               RIGHT('00' + CONVERT(NVARCHAR, mes), 2) + CONVERT(NVARCHAR, periodo) AS '010',
               CASE regimenPrevisional
                   WHEN 1
                   THEN 'AFP'
                   WHEN 2
                   THEN 'INP'
                   ELSE 'SIP'
               END AS '011', 
			   CASE tipoTrabajador 
				WHEN 4  THEN 8
				ELSE
					tipoTrabajador 
			   END AS '012', 
               dbo.RemDiasTrabajados(ficha, periodo, mes, fechaContrato, fechaFiniquito, tipoJornada) AS '013', 
               '00' AS '014', 
               COALESCE(
				(
					SELECT TOP (1) CASE
									   WHEN remMovPersonal.codMovimiento = 99
									   THEN 2
									   ELSE remMovPersonal.codMovimiento
								   END
					FROM remMovPersonal
						 INNER JOIN remParametros ON remMovPersonal.periodo = remParametros.periodo
													 AND remMovPersonal.mes = @MES
					WHERE(remMovPersonal.ficha = remTrabajadores.ficha)
					ORDER BY 
						CASE 
							WHEN remMovPersonal.codMovimiento IN (3, 6) THEN 0 -- Prioridad más alta
							ELSE 1 -- Prioridad más baja
						END,
						fechaInicio
				), 0) AS '015', 
               COALESCE(
				(
					SELECT TOP (1) CASE
						WHEN remMovPersonal.codMovimiento = 2 THEN CONVERT(DATE, remMovPersonal.fechaRetiroPrevired, 105)
						WHEN remMovPersonal.codMovimiento = 99 THEN CONVERT(DATE, remMovPersonal.fechaRetiroPrevired, 105)
						ELSE CONVERT(DATE, remMovPersonal.fechaInicio, 105)
					END AS 'fechaInicio'
					FROM remMovPersonal
					INNER JOIN remParametros ON remMovPersonal.periodo = remParametros.periodo
											AND remMovPersonal.mes = @MES
					WHERE(remMovPersonal.ficha = remTrabajadores.ficha)
					ORDER BY 
						CASE 
							WHEN remMovPersonal.codMovimiento IN (3, 6) THEN 0 -- Prioridad más alta
							ELSE 1 -- Prioridad más baja
						END,
						fechaInicio
				), CONVERT(DATE, '', 105)) AS '016', 

			COALESCE(
				(
					SELECT TOP (1)
					CASE
						WHEN remMovPersonal.codMovimiento = 2 THEN CONVERT(DATE, remMovPersonal.fechaRetiroPrevired, 105)
						WHEN remMovPersonal.codMovimiento = 99 THEN CONVERT(DATE, remMovPersonal.fechaRetiroPrevired, 105)
						ELSE CONVERT(DATE, remMovPersonal.fechaTermino, 105)
					END AS 'fechaTermino'
					FROM remMovPersonal
					INNER JOIN remParametros ON remMovPersonal.periodo = @PERIODO
											AND remMovPersonal.mes = @MES
					WHERE(remMovPersonal.ficha = remTrabajadores.ficha)
					ORDER BY 
						CASE 
							WHEN remMovPersonal.codMovimiento IN (3, 6) THEN 0 -- Prioridad más alta
							ELSE 1 -- Prioridad más baja
						END,
						fechaInicio
				), CONVERT(DATE, '', 105)) AS '017',
               CASE tramoasigfamiliar
                   WHEN 1
                   THEN 'A'
                   WHEN 2
                   THEN 'B'
                   WHEN 3
                   THEN 'C'
                   ELSE 'D'
               END AS '018', 
               CargaNormal AS '019', 
               CargaMaternal AS '020', 
               CargaInvalida AS '021', 
               dbo.remValorResultado(periodo, mes, ficha, 10) AS '022', 
               dbo.remValorResultado(periodo, mes, ficha, 11) AS '023', 
               dbo.remValorResultado(periodo, mes, ficha, 15) AS '024', 
               'N' AS '025',
               CASE
                   WHEN tipoTrabajador = 0
                        AND remTrabajadores.InstCesantia > 0
                        AND segcesantia <> 0
                        AND regimenPrevisional = 2
                   THEN
        (
            SELECT COALESCE((MIN(codprevired)), 0)
            FROM #remPrevisiones P
            WHERE P.idRegimen = 1
                  AND P.idPrevision = remTrabajadores.InstCesantia
        )
                   WHEN tipoTrabajador = 2
                        AND montoAhorroAFP > 0
                        AND remTrabajadores.PrevisionCuenta2 > 0
                   THEN
        (
            SELECT COALESCE((MIN(codprevired)), 0)
            FROM #remPrevisiones P
            WHERE P.idRegimen = 1
                  AND P.idPrevision = remTrabajadores.PrevisionCuenta2
        )
                   WHEN regimenPrevisional = 1
                        AND remTrabajadores.idPrevision > 0
                   THEN
        (
            SELECT COALESCE((MIN(codprevired)), 0)
            FROM #remPrevisiones P
            WHERE P.idRegimen = 1
                  AND P.idPrevision = remTrabajadores.idPrevision
        )
				  WHEN (tipoTrabajador = 1 OR tipoTrabajador = 3)
                        AND remTrabajadores.InstCesantia > 0
                        AND segcesantia <> 0
				  THEN
		(
			SELECT COALESCE((MIN(codprevired)), 0)
            FROM #remPrevisiones P
            WHERE P.idRegimen = 1
                  AND P.idPrevision = remTrabajadores.InstCesantia
		)
                   ELSE '00'
               END AS '026', 
               dbo.remValorResultado(periodo, mes, ficha, 36) AS '027',
               CASE regimenPrevisional
                   WHEN 2
                   THEN 0
                   ELSE dbo.remValorResultado(periodo, mes, ficha, 4)
               END AS '028', 
               dbo.remValorResultado(periodo, mes, ficha, 38) AS '029', 
               dbo.remValorResultado(periodo, mes, ficha, 6) AS '030', 
               0 AS '031', 
               '00,00' AS '032', 
               0 AS '033', 
               0 AS '034', 
               '' AS '035', 
               '' AS '036', 
               '' AS '037', 
               0 AS '038', 
               0 AS '039', 
        (
            SELECT COALESCE((MIN(codprevired)), 0)
            FROM #remPrevisiones P
            WHERE P.idRegimen = 1
                  AND P.idPrevision = remTrabajadores.idApvi1
        ) AS '040', 
               contratoApvi1 AS '041',
               CASE idApvi1
                   WHEN 0
                   THEN 0
                   ELSE formapagoApvi1
               END AS '042', 
               dbo.remValorResultado(periodo, mes, ficha, 39) AS '043', 
               0 AS '044', 
               idApvc1 AS '045', 
               contratoApvc1 AS '046',
               CASE contratoApvc1
                   WHEN 0
                   THEN 0
                   ELSE formapagoApvc1
               END AS '047', 
               dbo.remValorResultado(periodo, mes, ficha, 42) AS '048', 
               dbo.remValorResultado(periodo, mes, ficha, 43) AS '049', 
               0 AS '050', 
               '' AS '051', 
               '' AS '052', 
               '' AS '053', 
               '' AS '054', 
               '00' AS '055',
        --CONVERT(DATE, '', 105) AS '056', 
        --CONVERT(DATE, '', 105) AS '057', 
               '' AS '056', 
               '' AS '057', 
               0 AS '058', 
               0 AS '059', 
               0 AS '060', 
               0 AS '061',
               CASE regimenPrevisional
                   WHEN 2
                   THEN
        (
            SELECT COALESCE((MIN(codprevired)), 0)
            FROM #remPrevisiones P
            WHERE P.idRegimen = 2
                  AND P.idPrevision = remTrabajadores.idPrevision
        )
                   ELSE 0
               END AS '062',
               CASE regimenPrevisional
                   WHEN 2
                   THEN porcPrevision
                   ELSE 0
               END AS '063',
               CASE regimenSalud
                   WHEN 1
                   THEN dbo.remValorResultado(periodo, mes, ficha, 36)
                   WHEN 2
                   THEN 0
                   ELSE 0
               END AS '064',
               CASE regimenPrevisional
                   WHEN 2
                   THEN dbo.remValorResultado(periodo, mes, ficha, 4)
                   ELSE 0
               END AS '065', 
               0 AS '066', 
               0 AS '067', 
               0 AS '068', 
               0 AS '069',
               CASE regimenSalud
                   WHEN 1
                   THEN dbo.remValorResultado(periodo, mes, ficha, 7)
                   ELSE 0
               END AS '070', 
               0 AS '071', 
               0 AS '072',
        --0 AS '073', 
               CASE
                   WHEN(@CAJA >= 1)
                   THEN 0
                   ELSE dbo.remValorResultado(periodo, mes, ficha, 46)
               END AS '073', 
               0 AS '074',
               CASE regimenSalud
                   WHEN 1 THEN 7
				   WHEN 3 THEN 0
                   ELSE
					(
						SELECT COALESCE((MIN(codprevired)), 0)
						FROM #remIsapres P
						WHERE P.idIsapre = remTrabajadores.instSalud
					)
               END AS '075', 
               0 AS '076',
               CASE regimenSalud
                   WHEN 2
                   THEN dbo.remValorResultado(periodo, mes, ficha, 36)
                   ELSE 0
               END AS '077', 
               1 AS '078',
               CASE regimenSalud
                   WHEN 2
                   THEN dbo.remValorResultado(periodo, mes, ficha, 7) + dbo.remValorResultado(periodo, mes, ficha, 8)
                   ELSE 0
               END AS '079',
               CASE regimenSalud
                   WHEN 2
                   THEN dbo.remValorResultado(periodo, mes, ficha, 7)
                   ELSE 0
               END AS '080',
               CASE regimenSalud
                   WHEN 2
                   THEN dbo.remValorResultado(periodo, mes, ficha, 8)
                   ELSE 0
               END AS '081', 
               0 AS '082',

/* cambio para grez ,@CAJA AS '083' caja por trabajador*/

               CASE caja
                   WHEN 0
                   THEN @CAJA
                   ELSE caja
               END AS '083', 
			   case dbo.remValorResultado(periodo, mes, ficha, 45)
			   WHEN 0 then 0
			   ELSE dbo.remValorResultado(periodo, mes, ficha, 36)
			   END as '084', 
               dbo.remValorResultado(periodo, mes, ficha, 28) AS '085', 
               dbo.remValorResultado(periodo, mes, ficha, 29) AS '086', 
               dbo.remValorResultado(periodo, mes, ficha, 30) AS '087', 
               dbo.remValorResultado(periodo, mes, ficha, 32) AS '088', 
               dbo.remValorResultado(periodo, mes, ficha, 34) AS '089', 
               dbo.remValorResultado(periodo, mes, ficha, 45) AS '090',
--dbo.remValorResultado(periodo, mes, ficha, 46) AS '091', 
               CASE
                   WHEN(@CAJA = 0)
                   THEN 0
                   ELSE dbo.remValorResultado(periodo, mes, ficha, 46)
               END AS '091', 
               0 AS '092', 
               0 AS '093', 
               0 AS '094', 
               '' AS '095',
--Se cambia campo 096 para que siempre considere la mutual de parametros.
--,(SELECT
--		mutual
--	FROM remParametros AS remParametros_1
--	WHERE (periodo = remTrabajadores.periodo)
--	AND (mes = remTrabajadores.mes))
--AS '096' 
               @MUTUAL AS '096',
               CASE dbo.remValorResultado(periodo, mes, ficha, 35)
                   WHEN 0
                   THEN 0
                   ELSE dbo.remValorResultado(periodo, mes, ficha, 36)
               END AS '097', 
               dbo.remValorResultado(periodo, mes, ficha, 35) AS '098', 
               0 AS '099',
               CASE
                   WHEN(tipoTrabajador = 2
                        OR segcesantia = 0)
                   THEN 0
                   ELSE dbo.remValorResultado(periodo, mes, ficha, 37)
               END AS '100', 
               dbo.remValorResultado(periodo, mes, ficha, 22) AS '101', 
               dbo.remValorResultado(periodo, mes, ficha, 23) AS '102', 
               COALESCE((CASE
                             WHEN
        (
            SELECT TOP (1) CASE
                               WHEN remMovPersonal.codMovimiento = 99
                               THEN 2
                               ELSE remMovPersonal.codMovimiento
                           END
            FROM remMovPersonal
                 INNER JOIN remParametros ON remMovPersonal.periodo = remParametros.periodo
                                             AND remMovPersonal.mes = @MES
            WHERE(remMovPersonal.ficha = remTrabajadores.ficha)
        ) = 3
                             THEN
        (
            SELECT TOP (1) remIsapre.rut
            FROM remIsapre
            WHERE(idIsapre = instSalud)
        )
                             ELSE 0
                         END), 0) AS '103', 
               COALESCE((CASE
                             WHEN
        (
            SELECT TOP (1) remMovPersonal.codMovimiento
            FROM remMovPersonal
                 INNER JOIN remParametros ON remMovPersonal.periodo = remParametros.periodo
                                             AND remMovPersonal.mes = @MES
            WHERE(remMovPersonal.ficha = remTrabajadores.ficha)
        ) = 3
                             THEN
        (
            SELECT TOP (1) remIsapre.dv
            FROM remIsapre
            WHERE(idIsapre = instSalud)
        )
                             ELSE ''
                         END), '') AS '104', 
               unidadNegocios AS '105',
			   COALESCE(
        (
            SELECT TOP (1) remMovPersonal.codMovimiento
            FROM remMovPersonal
                 INNER JOIN remParametros ON remMovPersonal.periodo = remParametros.periodo
                                             AND remMovPersonal.mes = @MES
            WHERE(remMovPersonal.ficha = remTrabajadores.ficha
			AND remMovPersonal.codMovimiento = 2)
           -- ORDER BY fechaInicio
        ), 0) AS '106', 
               COALESCE(
			(
				SELECT TOP (1) remMovPersonal.idMovimiento
				FROM remMovPersonal
					 INNER JOIN remParametros ON remMovPersonal.periodo = remParametros.periodo
												 AND remMovPersonal.mes = @MES
				WHERE(remMovPersonal.ficha = remTrabajadores.ficha)
				ORDER BY 
					CASE 
						WHEN remMovPersonal.codMovimiento IN (3, 6) THEN 0 -- Prioridad más alta para códigos 3 y 6
						ELSE 1 -- Prioridad normal para otros códigos
					END,
					fechaInicio
			), 0) AS '015_2', 
               departamento, 
               unidadNegocios, 
               [idApvi1], 
               [contratoApvi1], 
               [formapagoApvi1], 
               [modoApvi1], 
               [montoApvi1], 
               [idApvi2], 
               [contratoApvi2], 
               [formapagoApvi2], 
               [modoApvi2], 
               [montoApvi2], 
               [idApvi3], 
               [contratoApvi3], 
               [formapagoApvi3], 
               [modoApvi3], 
               [montoApvi3], 
               ficha, 
               mes, 
               periodo, 
               regimenSalud, 
        (
            SELECT COUNT(ficha)
            FROM remTrabajadores FIC
            WHERE FIC.rut = [remTrabajadores].rut
                  AND remTrabajadores.periodo = FIC.periodo
                  AND remTrabajadores.mes = FIC.mes
                  AND FIC.fecing < remTrabajadores.fecing
        ) AS cantcontrato,
		dbo.remValorResultado(periodo, mes, ficha, 47) AS 'RIMA',
		dbo.remValorResultado(periodo, mes, ficha, 48) AS 'CCI',
		dbo.remValorResultado(periodo, mes, ficha, 49) AS 'espectativaVida',
		dbo.remValorResultado(periodo, mes, ficha, 95) AS 'rentabilidadProtegida',
		dbo.CalcEdad([fechaNac],getdate()) AS edad
        INTO #TODO
        FROM remTrabajadores
        WHERE(periodo = @PERIODO)
             AND (mes = @MES)
             AND (activo = 1)
             AND (@DEP = 0
                  OR departamento = @DEP)
             AND (@SEC = 0
                  OR seccion = @SEC)
        ORDER BY '001';

        --UPDATE #TODO SET [097] = 0 WHERE [098] = 0

        SELECT *
        FROM #TODO;


        SELECT * FROM (
			SELECT remMovPersonal.ficha AS '000', 
				   remMovPersonal.idMovimiento AS '015_2', 
				   remMovPersonal.codMovimiento AS '015', 
				   CONVERT(DATE, remMovPersonal.fechaInicio, 105) AS '016',
				   CONVERT(DATE, remMovPersonal.fechaTermino, 105) AS '017', 
				   COALESCE(remIsapre.rut, 0) AS [103], 
				   COALESCE(remIsapre.dv, '') AS [104],
				   0 AS Prioridad -- Prioridad más alta para código 3
			FROM remMovPersonal
				 INNER JOIN remTrabajadores ON remMovPersonal.periodo = remTrabajadores.periodo
											   AND remMovPersonal.mes = remTrabajadores.mes
											   AND remMovPersonal.ficha = remTrabajadores.ficha
				 INNER JOIN remIsapre ON remTrabajadores.periodo = remIsapre.periodo
										 AND remTrabajadores.mes = remIsapre.mes
										 AND remTrabajadores.instSalud = remIsapre.idIsapre
			WHERE(remMovPersonal.codMovimiento = 3)
				 AND (remMovPersonal.periodo = @PERIODO)
				 AND (remMovPersonal.mes = @MES)
				 AND (activo = 1)
				 AND (@DEP = 0 OR departamento = @DEP)
				 AND (@SEC = 0 OR seccion = @SEC)
				 AND remMovPersonal.codMovimiento > 0

			UNION ALL
			SELECT remMovPersonal.ficha AS '000', 
				   remMovPersonal.idMovimiento AS '015_2', 
				   remMovPersonal.codMovimiento AS '015', 
				   CONVERT(DATE, remMovPersonal.fechaInicio, 105) AS '016',
				   CONVERT(DATE, remMovPersonal.fechaTermino, 105) AS '017', 
				   remParametros.rutmutual AS '103', 
				   remParametros.dvmutual AS '104',
				   1 AS Prioridad -- Prioridad media para código 6
			FROM remParametros
				 INNER JOIN remMovPersonal
				 INNER JOIN remTrabajadores ON remMovPersonal.periodo = remTrabajadores.periodo
											   AND remMovPersonal.mes = remTrabajadores.mes
											   AND remMovPersonal.ficha = remTrabajadores.ficha 
											   ON remParametros.periodo = remTrabajadores.periodo
												 AND remTrabajadores.mes = @MES
			WHERE(remMovPersonal.codMovimiento = 6)
				 AND (remMovPersonal.periodo = @PERIODO)
				 AND (remMovPersonal.mes = @MES)
				 AND (activo = 1)
				 AND (@DEP = 0 OR departamento = @DEP)
				 AND (@SEC = 0 OR seccion = @SEC)
				 AND remMovPersonal.codMovimiento > 0

			UNION ALL
			SELECT remMovPersonal.ficha AS '000', 
				   remMovPersonal.idMovimiento AS '015_2',
				   CASE
					   WHEN remMovPersonal.codMovimiento = 99 THEN 2
					   ELSE remMovPersonal.codMovimiento
				   END AS '015', 
				   CONVERT(DATE, remMovPersonal.fechaInicio, 105) AS '016', 
				   CASE
						WHEN remMovPersonal.codMovimiento = 2 THEN CONVERT(DATE, remMovPersonal.fechaRetiroPrevired, 105)
						WHEN remMovPersonal.codMovimiento = 99 THEN CONVERT(DATE, remMovPersonal.fechaRetiroPrevired, 105)
						ELSE CONVERT(DATE, remMovPersonal.fechaTermino, 105) 
				   END AS '017', 
				   0 AS '103', 
				   '' AS '104',
				   2 AS Prioridad -- Prioridad más baja para otros códigos
			FROM remMovPersonal
				 INNER JOIN remTrabajadores ON remMovPersonal.periodo = remTrabajadores.periodo
											   AND remMovPersonal.mes = remTrabajadores.mes
											   AND remMovPersonal.ficha = remTrabajadores.ficha
			WHERE NOT(remMovPersonal.codMovimiento IN(3, 6))
				 AND (remMovPersonal.periodo = @PERIODO)
				 AND (remMovPersonal.mes = @MES)
				 AND (activo = 1)
				 AND (@DEP = 0 OR departamento = @DEP)
				 AND (@SEC = 0 OR seccion = @SEC)
				 AND remMovPersonal.codMovimiento > 0
		) AS Resultados
		ORDER BY [000], Prioridad, [016];

        /*TABLA ADICIONAL */

		SELECT [000], [040], [042], [043], [085]
			FROM (
				SELECT [000],
					(
						SELECT
						COALESCE((MIN(codprevired)), 0)
						FROM #remPrevisiones P
						WHERE P.idRegimen = 1 AND P.idPrevision = idApvi2
					) AS [040],
					[formapagoApvi2] AS [042],
					dbo.remValorResultado(periodo, mes, ficha, 40) AS [043],
					0 AS [085]
				FROM #TODO
				WHERE idApvi2 > 0

				UNION ALL

				SELECT [000],
					(
						SELECT
						COALESCE((MIN(codprevired)), 0)
						FROM #remPrevisiones P
						WHERE P.idRegimen = 1 AND P.idPrevision = idApvi3
					) AS [040],
					[formapagoApvi3] AS [042],
					dbo.remValorResultado(periodo, mes, ficha, 41) AS [043],
					0 AS [085]
				FROM #TODO
				WHERE idApvi3 > 0

				UNION ALL

				SELECT [000],
					0 AS [040],
					0 AS [042],
					0 AS [043],
					ISNULL(dbo.remValorResultado(periodo, mes, ficha, 96),0) AS [085]
				FROM #TODO
			) AS TablaAdicional
		WHERE NOT ([040] = 0 AND [042] = 0 AND [043] = 0 AND [085] = 0)
    END;