IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reg_gen_envioCorreo]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[reg_gen_envioCorreo]
GO
CREATE PROCEDURE [reg_gen_envioCorreo]
	@p_id INT
AS
BEGIN
	SELECT 	[id] AS [id],
		[uid] AS [uid],
		[eid] AS [eid],
		[id_usuario_modifico] AS [id_usuario_modifico],
		[id_usuario_creo] AS [id_usuario_creo],
		[fechaModificacion] AS [fechaModificacion],
		[fechaCreacion] AS [fechaCreacion],
		[active] AS [active],
		[envioCorreo] AS [envioCorreo],
		[envioCorreo_codigo] AS [envioCorreo_codigo],
		[estrategia] AS [estrategia],
		[tabla] AS [tabla],
		[id_reporte] AS [id_reporte],
		[correoAdicional] AS [correoAdicional],
		[plantillaAdjunto] AS [plantillaAdjunto],
		[id_subtipoDocumento] AS [id_subtipoDocumento],
		[id_estadoTipoDocumento] AS [id_estadoTipoDocumento],
		[cantidadMensajes] AS [cantidadMensajes],
		[tiempo_por_mensajes] AS [tiempo_por_mensajes],
		[cantidadReintento] AS [cantidadReintento]
	FROM [gen_envioCorreo]
	WHERE	(id = @p_id)

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[view_gen_envioCorreo]')) DROP VIEW [dbo].[view_gen_envioCorreo]
GO
CREATE  VIEW [view_gen_envioCorreo] AS
SELECT 	[gen_envioCorreo].[id] AS [id],
		[gen_envioCorreo].[uid] AS [uid],
		[gen_envioCorreo].[eid] AS [eid],
		[gen_envioCorreo].[id_usuario_modifico] AS [id_usuario_modifico],
		[gen_envioCorreo].[id_usuario_creo] AS [id_usuario_creo],
		[gen_envioCorreo].[fechaModificacion] AS [fechaModificacion],
		[gen_envioCorreo].[fechaCreacion] AS [fechaCreacion],
		[gen_envioCorreo].[active] AS [active],
		[gen_envioCorreo].[envioCorreo] AS [envioCorreo],
		[gen_envioCorreo].[envioCorreo_codigo] AS [envioCorreo_codigo],
		[gen_envioCorreo].[estrategia] AS [estrategia],
		[gen_envioCorreo].[tabla] AS [tabla],
		[gen_envioCorreo].[id_reporte] AS [id_reporte],
[rep_reporte].[reporte] as [rep_reporte_reporte],
[rep_reporte].[reporte_codigo] as [rep_reporte_reporte_codigo],
[rep_reporte].[id_funcionalidad] as [rep_reporte_id_funcionalidad],
[rep_reporte].[esFormato] as [rep_reporte_esFormato],
[rep_reporte].[tieneGraficos] as [rep_reporte_tieneGraficos],
[rep_reporte].[cabecera] as [rep_reporte_cabecera],
[rep_reporte].[orden] as [rep_reporte_orden],
[rep_reporte].[basica] as [rep_reporte_basica],
		[gen_envioCorreo].[correoAdicional] AS [correoAdicional],
		[gen_envioCorreo].[plantillaAdjunto] AS [plantillaAdjunto],
		[gen_envioCorreo].[id_subtipoDocumento] AS [id_subtipoDocumento],
[doc_subtipoDocumento].[subtipoDocumento] as [doc_subtipoDocumento_subtipoDocumento],
[doc_subtipoDocumento].[subtipoDocumento_codigo] as [doc_subtipoDocumento_subtipoDocumento_codigo],
[doc_subtipoDocumento].[prefijo] as [doc_subtipoDocumento_prefijo],
[doc_subtipoDocumento].[consecutivo] as [doc_subtipoDocumento_consecutivo],
[doc_subtipoDocumento].[id_tipoDocumento] as [doc_subtipoDocumento_id_tipoDocumento],
[doc_subtipoDocumento].[programarPlaneadas] as [doc_subtipoDocumento_programarPlaneadas],
[doc_subtipoDocumento].[cargarSolicitante] as [doc_subtipoDocumento_cargarSolicitante],
[doc_subtipoDocumento].[verCentroCosto] as [doc_subtipoDocumento_verCentroCosto],
[doc_subtipoDocumento].[verIncoterm] as [doc_subtipoDocumento_verIncoterm],
[doc_subtipoDocumento].[valUrgente] as [doc_subtipoDocumento_valUrgente],
[doc_subtipoDocumento].[bloquearDespacho] as [doc_subtipoDocumento_bloquearDespacho],
[doc_subtipoDocumento].[cerrarSol] as [doc_subtipoDocumento_cerrarSol],
		[gen_envioCorreo].[id_estadoTipoDocumento] AS [id_estadoTipoDocumento],
[doc_estadoTipoDocumento].[estadoTipoDocumento] as [doc_estadoTipoDocumento_estadoTipoDocumento],
[doc_estadoTipoDocumento].[estadoTipoDocumento_codigo] as [doc_estadoTipoDocumento_estadoTipoDocumento_codigo],
[doc_estadoTipoDocumento].[afectaIndicador] as [doc_estadoTipoDocumento_afectaIndicador],
[doc_estadoTipoDocumento].[editaDocumento] as [doc_estadoTipoDocumento_editaDocumento],
[doc_estadoTipoDocumento].[orden] as [doc_estadoTipoDocumento_orden],
[doc_estadoTipoDocumento].[puedeRegresar] as [doc_estadoTipoDocumento_puedeRegresar],
[doc_estadoTipoDocumento].[color] as [doc_estadoTipoDocumento_color],
[doc_estadoTipoDocumento].[afectaInventario] as [doc_estadoTipoDocumento_afectaInventario],
[doc_estadoTipoDocumento].[id_tipoDocumento] as [doc_estadoTipoDocumento_id_tipoDocumento],
[doc_estadoTipoDocumento].[descripcion] as [doc_estadoTipoDocumento_descripcion],
		[gen_envioCorreo].[cantidadMensajes] AS [cantidadMensajes],
		[gen_envioCorreo].[tiempo_por_mensajes] AS [tiempo_por_mensajes],
		[gen_envioCorreo].[cantidadReintento] AS [cantidadReintento],
		[empresaCodigo].[empresa] As [multiempresa]
FROM [gen_envioCorreo]
	INNER JOIN [seg_usuario] AS [seg_usuario_modifico] ON [seg_usuario_modifico].id=[gen_envioCorreo].[id_usuario_modifico]
	INNER JOIN [seg_usuario] AS [seg_usuario_creo] ON [seg_usuario_creo].id=[gen_envioCorreo].[id_usuario_creo]
	INNER JOIN [rep_reporte] ON [rep_reporte].id=[gen_envioCorreo].[id_reporte]
	INNER JOIN [doc_subtipoDocumento] ON [doc_subtipoDocumento].id=[gen_envioCorreo].[id_subtipoDocumento]
	INNER JOIN [doc_estadoTipoDocumento] ON [doc_estadoTipoDocumento].id=[gen_envioCorreo].[id_estadoTipoDocumento]
	INNER JOIN [gen_empresa] AS empresaCodigo ON [gen_envioCorreo].eid = [empresaCodigo].[codigo] and empresaCodigo.active=1
WHERE [gen_envioCorreo].active=1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sel_gen_envioCorreosXusuario_modifico]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sel_gen_envioCorreosXusuario_modifico]
GO
CREATE PROCEDURE [sel_gen_envioCorreosXusuario_modifico]
	@p_id_usuario_modifico INT
AS
BEGIN
	SELECT * 
	FROM [view_gen_envioCorreo]
	WHERE ([id_usuario_modifico]= @p_id_usuario_modifico)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sel_gen_envioCorreosXusuario_creo]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sel_gen_envioCorreosXusuario_creo]
GO
CREATE PROCEDURE [sel_gen_envioCorreosXusuario_creo]
	@p_id_usuario_creo INT
AS
BEGIN
	SELECT * 
	FROM [view_gen_envioCorreo]
	WHERE ([id_usuario_creo]= @p_id_usuario_creo)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sel_gen_envioCorreosXreporte]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sel_gen_envioCorreosXreporte]
GO
CREATE PROCEDURE [sel_gen_envioCorreosXreporte]
	@p_id_reporte INT
AS
BEGIN
	SELECT * 
	FROM [view_gen_envioCorreo]
	WHERE ([id_reporte]= @p_id_reporte)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sel_gen_envioCorreosXsubtipoDocumento]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sel_gen_envioCorreosXsubtipoDocumento]
GO
CREATE PROCEDURE [sel_gen_envioCorreosXsubtipoDocumento]
	@p_id_subtipoDocumento INT
AS
BEGIN
	SELECT * 
	FROM [view_gen_envioCorreo]
	WHERE ([id_subtipoDocumento]= @p_id_subtipoDocumento)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sel_gen_envioCorreosXestadoTipoDocumento]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sel_gen_envioCorreosXestadoTipoDocumento]
GO
CREATE PROCEDURE [sel_gen_envioCorreosXestadoTipoDocumento]
	@p_id_estadoTipoDocumento INT
AS
BEGIN
	SELECT * 
	FROM [view_gen_envioCorreo]
	WHERE ([id_estadoTipoDocumento]= @p_id_estadoTipoDocumento)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sel_gen_envioCorreos]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sel_gen_envioCorreos]
GO
CREATE PROCEDURE [sel_gen_envioCorreos]
	
	@p_filtro as varchar (8000) = '1=1',
	@p_orden as varchar (8000) = 'id',
	@p_campos as varchar (8000) = '*' AS
BEGIN
exec ('SELECT '+ @p_campos +
' FROM [view_gen_envioCorreo]
WHERE (' + @p_filtro + ') ORDER BY ' + @p_orden)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[lis_gen_envioCorreos]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[lis_gen_envioCorreos]
GO
CREATE PROCEDURE [lis_gen_envioCorreos]
	AS
BEGIN
	SELECT id AS id,
[envioCorreo] AS [gen_envioCorreo]
	FROM [gen_envioCorreo]
	WHERE active = 1
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[upd_gen_envioCorreo]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[upd_gen_envioCorreo]
GO
CREATE PROCEDURE [upd_gen_envioCorreo]
	@p_id int = null,
		@p_uid varchar(500) = null,
		@p_eid varchar(50) = null,
		@p_id_usuario_modifico int = null,
		@p_id_usuario_creo int = null,
		@p_fechaCreacion smalldatetime = null,
		@p_envioCorreo varchar(300) = null,
		@p_envioCorreo_codigo varchar(100) = null,
		@p_estrategia int = null,
		@p_tabla varchar(200) = null,
		@p_id_reporte int = null,
		@p_correoAdicional varchar(1000) = null,
		@p_plantillaAdjunto varchar(200) = null,
		@p_id_subtipoDocumento int = null,
		@p_id_estadoTipoDocumento int = null,
		@p_cantidadMensajes int = null,
		@p_tiempo_por_mensajes int = null,
		@p_cantidadReintento int = null
AS
BEGIN
		UPDATE [gen_envioCorreo]
		SET	[uid] = isnull(@p_uid,[uid]),
			[eid] = isnull(@p_eid,[eid]),
			[id_usuario_modifico] = isnull(@p_id_usuario_modifico,[id_usuario_modifico]),
			[id_usuario_creo] = isnull(@p_id_usuario_creo,[id_usuario_creo]),
			[fechaModificacion] = GETDATE(),
			[envioCorreo] = isnull(@p_envioCorreo,[envioCorreo]),
			[envioCorreo_codigo] = isnull(@p_envioCorreo_codigo,[envioCorreo_codigo]),
			[estrategia] = isnull(@p_estrategia,[estrategia]),
			[tabla] = isnull(@p_tabla,[tabla]),
			[id_reporte] = isnull(@p_id_reporte,[id_reporte]),
			[correoAdicional] = isnull(@p_correoAdicional,[correoAdicional]),
			[plantillaAdjunto] = isnull(@p_plantillaAdjunto,[plantillaAdjunto]),
			[id_subtipoDocumento] = isnull(@p_id_subtipoDocumento,[id_subtipoDocumento]),
			[id_estadoTipoDocumento] = isnull(@p_id_estadoTipoDocumento,[id_estadoTipoDocumento]),
			[cantidadMensajes] = isnull(@p_cantidadMensajes,[cantidadMensajes]),
			[tiempo_por_mensajes] = isnull(@p_tiempo_por_mensajes,[tiempo_por_mensajes]),
			[cantidadReintento] = isnull(@p_cantidadReintento,[cantidadReintento])
		WHERE (id = @p_id)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ins_gen_envioCorreo]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[ins_gen_envioCorreo]
GO
CREATE PROCEDURE [ins_gen_envioCorreo]
	@p_uid varchar(500),
		@p_eid varchar(50),
		@p_id_usuario_modifico int,
		@p_id_usuario_creo int,
		@p_envioCorreo varchar(300),
		@p_envioCorreo_codigo varchar(100),
		@p_estrategia int,
		@p_tabla varchar(200),
		@p_id_reporte int,
		@p_correoAdicional varchar(1000),
		@p_plantillaAdjunto varchar(200),
		@p_id_subtipoDocumento int,
		@p_id_estadoTipoDocumento int,
		@p_cantidadMensajes int,
		@p_tiempo_por_mensajes int,
		@p_cantidadReintento int
AS
BEGIN
	DECLARE @v_id int
		INSERT INTO [gen_envioCorreo]
			([uid],
			[eid],
			[id_usuario_modifico],
			[id_usuario_creo],
			[fechaModificacion],
			[fechaCreacion],
			[envioCorreo],
			[envioCorreo_codigo],
			[estrategia],
			[tabla],
			[id_reporte],
			[correoAdicional],
			[plantillaAdjunto],
			[id_subtipoDocumento],
			[id_estadoTipoDocumento],
			[cantidadMensajes],
			[tiempo_por_mensajes],
			[cantidadReintento])
		VALUES(	@p_uid,
			@p_eid,
			@p_id_usuario_modifico,
			@p_id_usuario_creo,
			GETDATE(),
			GETDATE(),
			@p_envioCorreo,
			@p_envioCorreo_codigo,
			@p_estrategia,
			@p_tabla,
			@p_id_reporte,
			@p_correoAdicional,
			@p_plantillaAdjunto,
			@p_id_subtipoDocumento,
			@p_id_estadoTipoDocumento,
			@p_cantidadMensajes,
			@p_tiempo_por_mensajes,
			@p_cantidadReintento)
		SET @v_id = scope_identity()
UPDATE [gen_envioCorreo] set [uid] = [uid] + '_' + convert(varchar(20),@v_id) where [id]=@v_id
SELECT @v_id as id
RETURN @v_id
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[act_gen_envioCorreo]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[act_gen_envioCorreo]
GO
CREATE PROCEDURE [act_gen_envioCorreo]
	@p_active BIT,
	@p_id INT,
@p_id_usuario_modifico int = null
AS
BEGIN
	UPDATE [gen_envioCorreo]
	SET active=@p_active,
	[id_usuario_modifico] = isnull(@p_id_usuario_modifico,[id_usuario_modifico]),
	[fechaModificacion] = GETDATE()
	WHERE	(id = @p_id)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reg_gen_envioCorreo_m]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[reg_gen_envioCorreo_m]
GO
CREATE PROCEDURE [reg_gen_envioCorreo_m]
	@p_id INT,
	@p_eid as varchar(50) = ''
AS
BEGIN
	SELECT 	[id] AS [id],
		[uid] AS [uid],
		[eid] AS [eid],
		[id_usuario_modifico] AS [id_usuario_modifico],
		[id_usuario_creo] AS [id_usuario_creo],
		[fechaModificacion] AS [fechaModificacion],
		[fechaCreacion] AS [fechaCreacion],
		[active] AS [active],
		[envioCorreo] AS [envioCorreo],
		[envioCorreo_codigo] AS [envioCorreo_codigo],
		[estrategia] AS [estrategia],
		[tabla] AS [tabla],
		[id_reporte] AS [id_reporte],
		[correoAdicional] AS [correoAdicional],
		[plantillaAdjunto] AS [plantillaAdjunto],
		[id_subtipoDocumento] AS [id_subtipoDocumento],
		[id_estadoTipoDocumento] AS [id_estadoTipoDocumento],
		[cantidadMensajes] AS [cantidadMensajes],
		[tiempo_por_mensajes] AS [tiempo_por_mensajes],
		[cantidadReintento] AS [cantidadReintento]
	FROM [gen_envioCorreo]
	WHERE	(id = @p_id)
	AND	(eid LIKE @p_eid+'%')

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sel_gen_envioCorreosXusuario_modifico_m]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sel_gen_envioCorreosXusuario_modifico_m]
GO
CREATE PROCEDURE [sel_gen_envioCorreosXusuario_modifico_m]
	@p_id_usuario_modifico INT,
	@p_eid as varchar(50) = ''
AS
BEGIN
	SELECT *
	FROM [view_gen_envioCorreo]
	WHERE ([id_usuario_modifico]= @p_id_usuario_modifico)
	AND	(eid LIKE @p_eid+'%')
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sel_gen_envioCorreosXusuario_creo_m]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sel_gen_envioCorreosXusuario_creo_m]
GO
CREATE PROCEDURE [sel_gen_envioCorreosXusuario_creo_m]
	@p_id_usuario_creo INT,
	@p_eid as varchar(50) = ''
AS
BEGIN
	SELECT *
	FROM [view_gen_envioCorreo]
	WHERE ([id_usuario_creo]= @p_id_usuario_creo)
	AND	(eid LIKE @p_eid+'%')
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sel_gen_envioCorreosXreporte_m]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sel_gen_envioCorreosXreporte_m]
GO
CREATE PROCEDURE [sel_gen_envioCorreosXreporte_m]
	@p_id_reporte INT,
	@p_eid as varchar(50) = ''
AS
BEGIN
	SELECT *
	FROM [view_gen_envioCorreo]
	WHERE ([id_reporte]= @p_id_reporte)
	AND	(eid LIKE @p_eid+'%')
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sel_gen_envioCorreosXsubtipoDocumento_m]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sel_gen_envioCorreosXsubtipoDocumento_m]
GO
CREATE PROCEDURE [sel_gen_envioCorreosXsubtipoDocumento_m]
	@p_id_subtipoDocumento INT,
	@p_eid as varchar(50) = ''
AS
BEGIN
	SELECT *
	FROM [view_gen_envioCorreo]
	WHERE ([id_subtipoDocumento]= @p_id_subtipoDocumento)
	AND	(eid LIKE @p_eid+'%')
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sel_gen_envioCorreosXestadoTipoDocumento_m]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sel_gen_envioCorreosXestadoTipoDocumento_m]
GO
CREATE PROCEDURE [sel_gen_envioCorreosXestadoTipoDocumento_m]
	@p_id_estadoTipoDocumento INT,
	@p_eid as varchar(50) = ''
AS
BEGIN
	SELECT *
	FROM [view_gen_envioCorreo]
	WHERE ([id_estadoTipoDocumento]= @p_id_estadoTipoDocumento)
	AND	(eid LIKE @p_eid+'%')
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sel_gen_envioCorreos_m]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sel_gen_envioCorreos_m]
GO
CREATE PROCEDURE [sel_gen_envioCorreos_m]
	@p_eid as varchar (50)='',
	@p_filtro as varchar (8000) = '1=1',
	@p_orden as varchar (8000) = 'id',
	@p_campos as varchar (8000) = '*' AS
BEGIN
exec ('SELECT '+ @p_campos +
' FROM [view_gen_envioCorreo]
WHERE  (eid like '''+@p_eid+'%'+''' ) AND (' + @p_filtro + ') ORDER BY ' + @p_orden)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[lis_gen_envioCorreos_m]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[lis_gen_envioCorreos_m]
GO
CREATE PROCEDURE [lis_gen_envioCorreos_m]
	@p_eid as varchar(50) = ''
AS
BEGIN
	SELECT id AS id,
[envioCorreo] AS [gen_envioCorreo]
	FROM [gen_envioCorreo]
	WHERE active = 1
	AND	(eid LIKE @p_eid+'%')
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aud_gen_envioCorreo]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[aud_gen_envioCorreo]
GO
CREATE PROCEDURE [aud_gen_envioCorreo]
	@p_filtro as varchar (8000) = '1=1'
AS
BEGIN
exec(	'SELECT [gen_envioCorreo].id as id_tabla,
	[gen_envioCorreo].id_usuario_modifico as id_usuario_modifico,
	[gen_envioCorreo].id_usuario_creo as id_usuario_creo,
	[gen_envioCorreo].fechaModificacion as fechaModificacion,
	[gen_envioCorreo].fechaCreacion as fechaCreacion,
	[gen_envioCorreo].active as active,
	[gen_envioCorreo].[envioCorreo] as campoPrincipal,
	[seg_usuario_creo].usuario as usuarioCreo,
	[seg_usuario_modifico].usuario as usuarioModifico

	FROM [gen_envioCorreo]
	INNER JOIN [seg_usuario] AS [seg_usuario_modifico] ON [seg_usuario_modifico].id=[gen_envioCorreo].[id_usuario_modifico]
	INNER JOIN [seg_usuario] AS [seg_usuario_creo] ON [seg_usuario_creo].id=[gen_envioCorreo].[id_usuario_creo]
	where ' + @p_filtro)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sel_seg_cargosXenvioCorreo]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sel_seg_cargosXenvioCorreo]
GO
CREATE PROCEDURE [sel_seg_cargosXenvioCorreo]
	@p_id_gen_envioCorreo INT
AS
BEGIN
	SELECT 	[seg_cargo_envioCorreo].[id] AS [I_id],
		[seg_cargo_envioCorreo].[uid] AS [I_uid],
		[seg_cargo_envioCorreo].[eid] AS [I_eid],
		[seg_cargo_envioCorreo].[fechaModificacion] AS [I_fechaModificacion],
		[seg_cargo_envioCorreo].[fechaCreacion] AS [I_fechaCreacion],
		[seg_cargo_envioCorreo].[active] AS [I_active],
		[seg_cargo_envioCorreo].[cargo_envioCorreo] AS [I_cargo_envioCorreo]
	FROM seg_cargo_envioCorreo INNER JOIN seg_cargo
	 ON [seg_cargo_envioCorreo].[id_cargo] = [seg_cargo].id
	WHERE	([seg_cargo_envioCorreo].[id_envioCorreo]= @p_id_gen_envioCorreo)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ins_seg_cargo_envioCorreo]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[ins_seg_cargo_envioCorreo]
GO
CREATE PROCEDURE [ins_seg_cargo_envioCorreo]
	@p_uid varchar(500),
		@p_eid varchar(50),
		@p_id_usuario_modifico int,
		@p_id_usuario_creo int,
		@p_cargo_envioCorreo varchar(300),
		@p_id_cargo int,
		@p_id_envioCorreo int
AS
BEGIN
	DECLARE @v_id int
		INSERT INTO [seg_cargo_envioCorreo]
			([uid],
			[eid],
			[id_usuario_modifico],
			[id_usuario_creo],
			[fechaModificacion],
			[fechaCreacion],
			[cargo_envioCorreo],
			[id_cargo],
			[id_envioCorreo])
		VALUES(	@p_uid,
			@p_eid,
			@p_id_usuario_modifico,
			@p_id_usuario_creo,
			GETDATE(),
			GETDATE(),
			@p_cargo_envioCorreo,
			@p_id_cargo,
			@p_id_envioCorreo)
		SET @v_id = scope_identity()
UPDATE [seg_cargo_envioCorreo] set [uid] = [uid] + '_' + convert(varchar(20),@v_id) where [id]=@v_id
SELECT @v_id as id
RETURN @v_id
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[upd_seg_cargo_envioCorreo]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[upd_seg_cargo_envioCorreo]
GO
CREATE PROCEDURE [upd_seg_cargo_envioCorreo]
	@p_id int = null,
		@p_uid varchar(500) = null,
		@p_eid varchar(50) = null,
		@p_id_usuario_modifico int = null,
		@p_id_usuario_creo int = null,
		@p_fechaCreacion smalldatetime = null,
		@p_cargo_envioCorreo varchar(300) = null,
		@p_id_cargo int = null,
		@p_id_envioCorreo int = null
AS
BEGIN
		UPDATE [seg_cargo_envioCorreo]
		SET	[uid] = isnull(@p_uid,[uid]),
			[eid] = isnull(@p_eid,[eid]),
			[id_usuario_modifico] = isnull(@p_id_usuario_modifico,[id_usuario_modifico]),
			[id_usuario_creo] = isnull(@p_id_usuario_creo,[id_usuario_creo]),
			[fechaModificacion] = GETDATE(),
			[cargo_envioCorreo] = isnull(@p_cargo_envioCorreo,[cargo_envioCorreo]),
			[id_cargo] = isnull(@p_id_cargo,[id_cargo]),
			[id_envioCorreo] = isnull(@p_id_envioCorreo,[id_envioCorreo])
		WHERE (id = @p_id)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON
GO
----------------------
print 'gen_envioCorreo'
-----------------------------------15/09/2017 2:30:46 p. m.