IF OBJECT_ID('dbo.sp_genera_trans_der') IS NOT NULL
    DROP PROC dbo.sp_genera_trans_der
GO
CREATE PROC dbo.sp_genera_trans_der 
(@chcompania      CHAR(4), 
 @chclase_nomina  CHAR(2), 
 @siid_calendario SMALLINT, 
 @siconcepto      SMALLINT, 
 @chtrabajador    CHAR(10), 
 @chusuario       CHAR(30), 
 @sdtmfecha_hoy   SMALLDATETIME) 
AS 
BEGIN 
/* variable statements */
DECLARE     @chtipo_nomina CHAR (2)       /* Calendario de Procesos */
DECLARE 	@siwconcepto	SMALLINT,	/*active concept */ 
   		@tibANDera 		TINYINT,		 
   		@tibANDera1 	TINYINT,	/*flag 1 */ 
		@tibANDera2 	TINYINT,	/*flag 2 */ 
		@tibANDera3 	TINYINT,	/*flag 3 */  
		@tigenera_trans	TINYINT	/*generate transactions */ 
 
/* getting the calender period date FROM calender process table*/  
DECLARE	@sdtmfecha_inicio    SMALLDATETIME,                  
            @sdtmfecha_termino   SMALLDATETIME, 
		@sicrconcepto        SMALLINT, 
		@sicrconcepto_ant    SMALLINT,
            @siconcepto_derivado SMALLINT, 
  		@sdtmfecha_ingreso   SMALLDATETIME, 
            @sdtmfecha_baja      SMALLDATETIME, 
            @chrelacion_laboral  CHAR(1), 
            @titurno             TINYINT, 
            @tisit_trabajador    TINYINT, 
            @chwtrabajador       CHAR(10), 
		@ientero             INT


DECLARE 	@cond_turno          TINYINT, 
		@cond_rel_laboral    CHAR(1), 
 		@cond_agr_01         CHAR(10), 
            @cond_dato_01_01     CHAR(10), 
            @cond_dato_01_02     CHAR(10), 
            @cond_dato_01_03     CHAR(10), 
            @cond_agr_02         CHAR(10), 
		@cond_dato_02_01     CHAR(10), 
		@cond_dato_02_02     CHAR(10), 
		@cond_dato_02_03     CHAR(10), 
            @cond_agr_03         CHAR(10), 
            @cond_dato_03_01     CHAR(10), 
            @cond_dato_03_02     CHAR(10), 
            @cond_dato_03_03     CHAR(10),
		@siprimera_vez       TINYINT,
		@sec_ns            INT,
		@cond_coleccion	   CHAR(10)

/* variables para que la referencia, el factor1, factor2 y factor3, se tomen del concepto padre
          y se inserten en los conceptos derivados ****** */

  DECLARE @referencia_padre CHAR(10),
          @fac1_padre       DECIMAL(15,6),

        @fac2_padre     DECIMAL(15,6),
         @fac3_padre    DECIMAL(15,6)
  
-- VARIABLES DECLARADAS POR JCL PARA QUE GENERE UN DERIVADO POR CADA CONCEPTO-SECUENCIA
-- JCL NOV-03-06
DECLARE @csi_secuencia smallint

/*  Inicialización de variables **** */


 SELECT @chtipo_nomina = ''
 SELECT 	@siwconcepto	= 0,
   		@tibANDera 		= 0,		 
   		@tibANDera1 	= 0,
		@tibANDera2 	= 0,
		@tibANDera3 	= 0,
		@tigenera_trans	= 0
 
 SELECT	@sdtmfecha_inicio   = '',                  
            @sdtmfecha_termino   = '', 
		@sicrconcepto        = 0, 
		@sicrconcepto_ant    = 0,
            @siconcepto_derivado = 0, 
  		@sdtmfecha_ingreso   = '', 
            @sdtmfecha_baja      = '', 
            @chrelacion_laboral  = '', 
            @titurno             = 0, 
            @tisit_trabajador    = 0, 
            @chwtrabajador       = '', 
		@ientero             = 0


 SELECT 	@cond_turno          = 0, 
		@cond_rel_laboral    = '', 
 		@cond_agr_01         = '', 
            @cond_dato_01_01     = '', 
            @cond_dato_01_02     = '', 
            @cond_dato_01_03     = '', 
            @cond_agr_02         = '', 
		@cond_dato_02_01     = '', 
		@cond_dato_02_02     = '', 
		@cond_dato_02_03     = '', 
            @cond_agr_03         = '', 
            @cond_dato_03_01     = '', 
            @cond_dato_03_02     = '', 
            @cond_dato_03_03     = '',
		@siprimera_vez       = 0,
		@sec_ns            = 0,
		@cond_coleccion	   = ''

  SELECT @referencia_padre = '',
          @fac1_padre       = 0,

        @fac2_padre     = 0,
         @fac3_padre    = 0



 SELECT	@sdtmfecha_inicio = calendario_procesos.fecha_inicio, 
         	@sdtmfecha_termino = calendario_procesos.fecha_termino,
            @chtipo_nomina = calendario_procesos.tipo_nomina
  	 	FROM calendario_procesos 
		WHERE id_calendario = @siid_calendario 

	/*Obtiene el parametro para considerar o no, las bajas del periodo */
SET @sec_ns = 100
SELECT @ientero = 0
IF exists(SELECT 1 FROM parametros_rh WHERE compania = @chcompania 
	     AND parametro_rh = 'Bajas_Per'
	     AND secuencia_parametro = 1)
   SELECT @ientero = ISNULL(parametros_rh.entero ,0)
   FROM  parametros_rh  
   WHERE compania = @chcompania  
     AND parametro_rh = 'Bajas_Per'
     AND secuencia_parametro = 1


 
/* Borra transacciones derivadas dependiendo del trabajador y concepto especIFicados */  
IF isnull(ltrim(rtrim(@chtrabajador)),' ') != ' ' /* Un solo trabajador  */ 
BEGIN 
   IF @siconcepto <> 0 /* Un solo concepto */ 
	BEGIN	/* Borra transacciones con origen 5, un solo trabajador y un solo concepto */ 
		delete transacciones_ns 
                FROM conceptos_derivados 
		WHERE transacciones_ns.compania           = @chcompania 
		AND   transacciones_ns.clase_nomina       = @chclase_nomina 
		AND   transacciones_ns.id_calendario      = @siid_calendario  
		AND   conceptos_derivados.concepto_derivado = transacciones_ns.concepto 
		AND   conceptos_derivados.compania        = transacciones_ns.compania   
		AND   conceptos_derivados.concepto        = @siconcepto 
		AND   transacciones_ns.trabajador         = @chtrabajador 
		AND   transacciones_ns.origen_transaccion = 5 

    	end 
   ELSE
     	BEGIN /* Todos los conceptos y un trabajador y origen = 5 */ 
		delete transacciones_ns 
		WHERE compania = @chcompania 
		AND clase_nomina = @chclase_nomina 
		AND id_calendario = @siid_calendario 
		AND trabajador = @chtrabajador 
		AND origen_transaccion = 5 
	 end 
end 
ELSE	/* Todos los trabajadores */ 
BEGIN 
	IF @siconcepto <> 0   /* Un solo concepto */
	BEGIN	
		delete transacciones_ns 
                FROM   conceptos_derivados 
		WHERE  transacciones_ns.compania           = @chcompania 
		AND    transacciones_ns.clase_nomina       = @chclase_nomina 
		AND    transacciones_ns.id_calendario      = @siid_calendario 
		AND    conceptos_derivados.concepto_derivado = transacciones_ns.concepto 
		AND    conceptos_derivados.compania        = transacciones_ns.compania 
		AND    conceptos_derivados.concepto        = @siconcepto 
		AND    transacciones_ns.origen_transaccion = 5 

	end	 
	else	/* Todos los conceptos */ 
	BEGIN 
		delete transacciones_ns  
		WHERE compania = @chcompania  
		AND clase_nomina = @chclase_nomina 
		AND id_calendario = @siid_calendario  
		AND origen_transaccion = 5 
	end 
END  
 
IF @siconcepto <> 0        /* Un solo concepto */
BEGIN 
	IF isnull(ltrim(rtrim(@chtrabajador)),' ') != ' '    /* Un solo trabajador */
	BEGIN
	DECLARE cr_transacciones_ns_der cursor for        /* un concepto un trabajador */
	SELECT distinct cd.concepto,
               concepto_derivado,
               tn.trabajador,
	       ISNULL(fecha_ingreso, ''),
               ISNULL(fecha_baja, ''),
               ISNULL(relacion_laboral, ''), 
	       ISNULL(turno, 0),
               ISNULL(sit_trabajador, 0),
		ccns.cond_coleccion,
               ISNULL(tn.referencia,''),
               ISNULL(tn.factor_01,0),
               ISNULL(tn.factor_02,0),
               ISNULL(tn.factor_03,0),
               tn.secuencia -- JCL NOV-03-06
	FROM   conceptos_derivados cd,
               transacciones_ns tn,
               trabajadores_grales tg,
		con_coleccion_ns ccns
	WHERE  cd.compania    = @chcompania
	AND    cd.compania    = tn.compania
	AND    cd.concepto    = tn.concepto
	AND    tg.compania = tn.compania
	AND    tg.trabajador = tn.trabajador
	AND    tn.clase_nomina = @chclase_nomina
	AND    id_calendario = @siid_calendario
	AND    cd.concepto = @siconcepto			/* Concepto EspecIFicado   */
	AND    tn.trabajador = @chtrabajador		/* Trabajador EspecIFicado */
	AND    cd.compania   *= ccns.compania
	AND    cd.concepto_derivado   *= ccns.concepto
	AND    EXISTS(SELECT 1 
                      FROM rel_conc_tipo_nomina
		      WHERE compania = @chcompania
		      AND tipo_nomina = @chtipo_nomina
		      AND concepto = cd.concepto_derivado)
	ORDER BY cd.concepto, cd.concepto_derivado
	END
	ELSE       /* Todos los trabajadores  */
	BEGIN   
	DECLARE cr_transacciones_ns_der cursor for
	SELECT distinct cd.concepto, concepto_derivado, tn.trabajador,
		ISNULL(fecha_ingreso, ''), ISNULL(fecha_baja, ''), ISNULL(relacion_laboral, ''), 
		ISNULL(turno, 0), ISNULL(sit_trabajador, 0), ccns.cond_coleccion,
                ISNULL(tn.referencia,''),
                ISNULL(tn.factor_01,0),
                ISNULL(tn.factor_02,0),
                ISNULL(tn.factor_03,0), tn.secuencia -- JCL NOV-03-06
	FROM 	conceptos_derivados cd, transacciones_ns tn, trabajadores_grales tg, con_coleccion_ns ccns
	WHERE cd.compania = @chcompania
	AND 	cd.compania = tn.compania
	AND 	cd.concepto = tn.concepto
	AND 	tg.compania = tn.compania
	AND 	tg.trabajador = tn.trabajador
	AND 	tn.clase_nomina = @chclase_nomina
	AND 	id_calendario = @siid_calendario
	AND 	cd.concepto = @siconcepto			/* Concepto EspecIFicado */
	AND   cd.compania *= ccns.compania
	AND   cd.concepto_derivado *= ccns.concepto
	AND 	EXISTS(SELECT 1 FROM rel_conc_tipo_nomina
			 WHERE compania = @chcompania
			 AND tipo_nomina = @chtipo_nomina
			 AND concepto = cd.concepto_derivado)
	ORDER BY cd.concepto, cd.concepto_derivado
	END
END   
ELSE   /* Todos los conceptos */
BEGIN
	IF isnull(ltrim(rtrim(@chtrabajador)),' ') != ' '   /* Un solo trabajador */
	BEGIN
	DECLARE cr_transacciones_ns_der cursor for
	SELECT distinct cd.concepto, concepto_derivado, tn.trabajador,
		ISNULL(fecha_ingreso, ''), ISNULL(fecha_baja, ''), ISNULL(relacion_laboral, ''), 
		ISNULL(turno, 0), ISNULL(sit_trabajador, 0), ccns.cond_coleccion,
                ISNULL(tn.referencia,''),
                ISNULL(tn.factor_01,0),
                ISNULL(tn.factor_02,0),
                ISNULL(tn.factor_03,0), tn.secuencia -- JCL NOV-03-06
	FROM 	conceptos_derivados cd, transacciones_ns tn, trabajadores_grales tg, con_coleccion_ns ccns
	WHERE cd.compania = @chcompania
	AND 	cd.compania = tn.compania
	AND 	cd.concepto = tn.concepto
	AND 	tg.compania = tn.compania
	AND 	tg.trabajador = tn.trabajador
	AND 	tn.clase_nomina = @chclase_nomina
	AND 	id_calendario = @siid_calendario
	AND 	tn.trabajador = @chtrabajador		/* Trabajador EspecIFicado */
	AND   cd.compania   *= ccns.compania
	AND   cd.concepto_derivado   *= ccns.concepto
	AND 	EXISTS(SELECT 1 FROM rel_conc_tipo_nomina
			 WHERE compania = @chcompania
			 AND tipo_nomina = @chtipo_nomina
			 AND concepto = cd.concepto_derivado)
	ORDER BY cd.concepto, cd.concepto_derivado
	END
	ELSE		/* todos los trabajadores */
	BEGIN
	DECLARE cr_transacciones_ns_der cursor for
	SELECT distinct cd.concepto, concepto_derivado, tn.trabajador,
		ISNULL(fecha_ingreso, ''), ISNULL(fecha_baja, ''), ISNULL(relacion_laboral, ''), 
		ISNULL(turno, 0), ISNULL(sit_trabajador, 0), ccns.cond_coleccion,
                ISNULL(tn.referencia,''),
                ISNULL(tn.factor_01,0),
                ISNULL(tn.factor_02,0),
                ISNULL(tn.factor_03,0), tn.secuencia -- JCL NOV-03-06
	FROM 	conceptos_derivados cd, transacciones_ns tn, trabajadores_grales tg, con_coleccion_ns ccns
	WHERE cd.compania = @chcompania
	AND 	cd.compania = tn.compania
	AND 	cd.concepto = tn.concepto
	AND 	tg.compania = tn.compania
	AND 	tg.trabajador = tn.trabajador
	AND 	tn.clase_nomina = @chclase_nomina
	AND 	id_calendario = @siid_calendario
	AND   cd.compania   *= ccns.compania
	AND   cd.concepto_derivado   *= ccns.concepto
	AND 	EXISTS(SELECT 1 FROM rel_conc_tipo_nomina
			 WHERE compania = @chcompania
			 AND tipo_nomina = @chtipo_nomina
			 AND concepto = cd.concepto_derivado)
	ORDER BY cd.concepto, cd.concepto_derivado
	end
END


OPEN cr_transacciones_ns_der
fetch cr_transacciones_ns_der into @sicrconcepto,@siconcepto_derivado,
@chwtrabajador,@sdtmfecha_ingreso, @sdtmfecha_baja,
@chrelacion_laboral, @titurno, @tisit_trabajador, @cond_coleccion,
@referencia_padre, @fac1_padre, @fac2_padre, @fac3_padre, 
@csi_secuencia -- JCL NOV-03-06

IF @@fetch_status = 0
	SELECT @sicrconcepto_ant = @sicrconcepto

Select @siprimera_vez = 1

WHILE @@fetch_status =0   
BEGIN 
	IF @siprimera_vez = 1 or (@sicrconcepto <> @sicrconcepto_ant)
	BEGIN
		Select @sicrconcepto_ant = @sicrconcepto,
			 @siprimera_vez = 0
		/* Obtiene condicionamiento del concepto */
		SELECT @cond_turno  	 = ISNULL(conceptos_ns.cond_turno,0) , 
      		 @cond_rel_laboral = ISNULL(conceptos_ns.cond_rel_laboral,''), 
	           	 @cond_agr_01 	 = ISNULL(conceptos_ns.cond_agr_01,''), 
      	     	 @cond_dato_01_01  = ISNULL(conceptos_ns.cond_dato_01_01,''), 
           		 @cond_dato_01_02  = ISNULL(conceptos_ns.cond_dato_01_02,''), 
	          	 @cond_dato_01_03  = ISNULL(conceptos_ns.cond_dato_01_03,''), 
      	    	 @cond_agr_02      = ISNULL(conceptos_ns.cond_agr_02,''), 
          		 @cond_dato_02_01  = ISNULL(conceptos_ns.cond_dato_02_01,''), 
	         	 @cond_dato_02_02  = ISNULL(conceptos_ns.cond_dato_02_02,''), 
      	   	 @cond_dato_02_03  = ISNULL(conceptos_ns.cond_dato_02_03,''),  
          		 @cond_agr_03      = ISNULL(conceptos_ns.cond_agr_03,''), 
	         	 @cond_dato_03_01  = ISNULL(conceptos_ns.cond_dato_03_01,''), 
          	 	 @cond_dato_03_02  = ISNULL(conceptos_ns.cond_dato_03_02,''), 
          	 	 @cond_dato_03_03  = ISNULL(conceptos_ns.cond_dato_03_03,'') 
	 	FROM  conceptos_ns  
	 	WHERE compania = @chcompania 
	 	AND concepto = @siconcepto_derivado 
	END	
	
	 SELECT @tigenera_trans = 1 

	IF isnull(ltrim(rtrim(@cond_coleccion)), ' ') != ' '
	begin
		if exists(Select 1 from rel_trab_coleccion
				where compania = @chcompania and
					trabajador = @chtrabajador and
					coleccion =  @cond_coleccion)
			Select @tigenera_trans =  1
		else
			Select @tigenera_trans =  0
	end

	if @tigenera_trans =  1
	begin
	       IF @ientero = 1 
	 	 BEGIN 
			IF  (@tisit_trabajador = 2 AND @sdtmfecha_baja < @sdtmfecha_inicio) OR
			    (@sdtmfecha_ingreso > @sdtmfecha_termino)
				SELECT @tigenera_trans = 0 
	       end 
		 ELSE
		 BEGIN 
		      IF (@sdtmfecha_ingreso > @sdtmfecha_termino) OR
	      	   (@tisit_trabajador = 2 AND @sdtmfecha_baja < @sdtmfecha_inicio) OR
			   (@tisit_trabajador = 2 AND @sdtmfecha_baja <= @sdtmfecha_termino)
				SELECT @tigenera_trans = 0 
	       END 
	end


	if @tigenera_trans =  1
	begin	
		/* Valida Condicionamiento por turno */
		 IF @cond_turno != 0 AND @cond_turno <> @titurno AND @titurno is null 
			SELECT @tigenera_trans = 0 
		 
		/* Valida Condicionamiento por relación laboral */
		 IF isnull(ltrim(rtrim(@cond_rel_laboral)), ' ') != ' '
		 AND @cond_rel_laboral <> @chrelacion_laboral  
			SELECT @tigenera_trans = 0 
	end

		 
	/* Valida Condicionamiento por Agrupación 01 */
	EXECUTE sp_trab_agr_rel @chcompania,  
			   		@chwtrabajador, 
					@cond_agr_01, 
					@cond_dato_01_01, 
					@cond_dato_01_02, 
					@cond_dato_01_03, 
					@tibANDera output 
	SELECT @tibANDera1 = @tibANDera 
		  
	/* Valida Condicionamiento por Agrupación 02 */
	EXECUTE sp_trab_agr_rel @chcompania, 
	 		   		@chwtrabajador, 
					@cond_agr_02, 
					@cond_dato_02_01, 
					@cond_dato_02_02, 
					@cond_dato_02_03, 
					@tibANDera output 
	SELECT @tibANDera2 = @tibANDera 
		  
	/* Valida Condicionamiento por Agrupación 03 */
	EXECUTE sp_trab_agr_rel @chcompania, 
			   		@chwtrabajador, 
					@cond_agr_03, 
					@cond_dato_03_01, 
					@cond_dato_03_02, 
					@cond_dato_03_03, 
					@tibANDera output 
	SELECT @tibANDera3 = @tibANDera 
	 
	IF (@tibANDera1 =1) or (@tibANDera2 = 1) or (@tibANDera3 = 1)  
		SELECT @tigenera_trans = 0 

 	/* Si el trabajador pasa las validaciones anteriores entonces un registro */
	/* para el concepto derivado será generado  */
	IF @tigenera_trans = 1  
	BEGIN 
		/* validate that transaction does not exists */ 
		IF NOT EXISTS ( SELECT 1 
				FROM   transacciones_ns  
				WHERE  compania           = @chcompania  
				AND    clase_nomina       = @chclase_nomina 
				AND    id_calendario      = @siid_calendario  
				AND    concepto           = @siconcepto_derivado 
				AND    trabajador         = @chwtrabajador
				AND    referencia         = ' '
                    and secuencia = @csi_secuencia -- JCL NOV-03-06
				AND    origen_transaccion = 5)
		BEGIN 
			SET @sec_ns = @sec_ns + 1
			EXECUTE sp_alta_transacciones_ns 
			@chcompania, @chclase_nomina, 
			@siid_calendario,
			@siconcepto_derivado, 
	        	@chwtrabajador, 
			@referencia_padre,
			@csi_secuencia, -- JCL NOV-03-06 @sec_ns,
			' ',
        		@sdtmfecha_termino, 
        		NULL, NULL, @fac1_padre, @fac2_padre, @fac3_padre, 
			NULL, NULL, NULL, NULL,  NULL, 
			NULL, NULL, NULL, NULL,  NULL,
			NULL, NULL,
			5, 
	         	@chusuario,  @sdtmfecha_hoy, 
			NULL, NULL, NULL, NULL, 1, 1
		END
	end 	/* (@tigenera_trans = 1) */ 

	fetch cr_transacciones_ns_der into @sicrconcepto,@siconcepto_derivado,
                       @chwtrabajador,@sdtmfecha_ingreso, @sdtmfecha_baja,
                        @chrelacion_laboral, @titurno, @tisit_trabajador, @cond_coleccion,
                          @referencia_padre, @fac1_padre, @fac2_padre, @fac3_padre,
                    @csi_secuencia -- JCL NOV-03-06

END /* sqlstatus */
CLOSE cr_transacciones_ns_der
DEALLOCATE cr_transacciones_ns_der
END	/* sp_genera_trans_der */ 
GO

GRANT EXECUTE ON sp_genera_trans_der TO PUBLIC
GO
