--Hash:F6CB82DF5E78D0CF083F52247545CD86
SET XACT_ABORT ON
BEGIN TRANSACTION;

GO

SET DATEFORMAT mdy;

--Validar que no existan scripts que no se hayan ejecutado
--//
IF EXISTS
(
	SELECT a.NumVersion + 1 AS HuecoInicio
	FROM ZiurVersiones a
	LEFT JOIN ZiurVersiones b ON a.NumVersion + 1 = b.NumVersion
	WHERE a.NumVersion>830 AND b.NumVersion IS NULL
	AND a.NumVersion + 1 < 966
)
RAISERROR('Error : hay un script que no se ejecuto , debe ejecutarlo manualmente.', 20, -1) with log
--//

--Colocar contenido de script diferencial aqui (Ziur.Database.publish.sql)

GO
/*
 Plantilla de script anterior a la implementación							
--------------------------------------------------------------------------------------
 Este archivo contiene instrucciones de SQL que se ejecutarán antes del script de compilación	
 Use la sintaxis de SQLCMD para incluir un archivo en el script anterior a la implementación			
 Ejemplo:      :r .\miArchivo.sql								
 Use la sintaxis de SQLCMD para hacer referencia a una variable en el script anterior a la implementación		
 Ejemplo:      :setvar TableName miTabla							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/
GO

GO
PRINT N'Altering Procedure [dbo].[GetMediosMagneticos]...';

GO
ALTER FUNCTION ufnMediosMagneticos_CuantiasMenores_Saldos 
(
   @FechaFin NVARCHAR(10) = '', 
   @IdEmpresas NVARCHAR(1000) = '',	
   @IdDocsExc NVARCHAR(1000) ='',
   @IdVersion INT = NULL
)
RETURNS @Results TABLE(IdVersion INT, IdCliente INT, EsCuantiaMayor INT)
AS
BEGIN
    INSERT @Results


SELECT T.IdVersion, T.IdCliente, MAX(T.EsCuantiaMayor) AS EsCuantiaMayor
FROM
(
	SELECT M_Ppal.IdVersion, M_Ppal.IdAgrupMM, M_ConcAgrup.IdAgrup, M_Ppal.IdCliente, SUM(M_Ppal.PagoTotal) AS SumPagoTotal, CASE WHEN ABS(SUM(M_Ppal.PagoTotal)) >= MAX(M_Agrup.ValorBase) THEN 1 ELSE 0 END AS EsCuantiaMayor
	FROM 
	dbo.VMediosMag_Procesos_Principal AS M_Ppal INNER JOIN dbo.MediosMag_ConceptosAgrupacion AS M_ConcAgrup ON M_Ppal.IdVersion = M_ConcAgrup.IdVersion AND M_Ppal.IdConcepto = M_ConcAgrup.IdConcepto INNER JOIN dbo.MediosMag_Agrupaciones AS M_Agrup ON M_ConcAgrup.IdAgrup = M_Agrup.IdAgrup
	WHERE
	DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) <= CAST(CONVERT(DATETIME, @FechaFin, 103) + ' 23:59:59' AS DATETIME) AND
	M_Ppal.IdEmpresa IN(select number from iter$simple_intlist_to_tbl(@IdEmpresas)) AND
	M_Ppal.IdDocumento NOT IN(select nstr from iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
	(@IdVersion IS NULL OR M_Ppal.IdVersion = @IdVersion)
	GROUP BY M_Ppal.IdVersion, M_Ppal.IdAgrupMM, M_ConcAgrup.IdAgrup, M_Ppal.IdCliente
) T
GROUP BY T.IdVersion, T.IdCliente

RETURN

END
GO

GO
ALTER PROCEDURE [dbo].[GetMediosMagneticos]
   @IdVersion INT = 0,
	@FechaIni NVARCHAR(10) = '',
	@FechaFin NVARCHAR(10) = '',
	@IdEmpresas NVARCHAR(1000) = '',
	@IdDocsExc NVARCHAR(1000) ='',
	@IdCliInf INT = 0,
	@IdCliExc NVARCHAR(1000) = '',
	@Detallar BIT = 0
AS
BEGIN

DECLARE

@FechaIniT DATETIME,
@FechaFinT DATETIME,
@IdFormato INT,
@CodVersion INT,
@Vigencia INT,
@IdEmpFirst INT,
@TarifaIca NUMERIC(28,6),

@CM_Identif NVARCHAR(100),
@CM_TipoIdent INT,
@CM_RazonSocial NVARCHAR(200),
@CM_LongNombres INT,
@CM_LongRazonSocial INT,
@CM_LongDireccion INT


SET @FechaIniT = CAST(CONVERT(DATETIME, @FechaIni, 103) + ' 00:00:00' AS DATETIME)
SET @FechaFinT = CAST(CONVERT(DATETIME, @FechaFin, 103) + ' 23:59:59' AS DATETIME)

--Recuperar el id del formato
--//
SELECT @IdFormato = IdFormato, @CodVersion = CodVersion FROM MediosMag_Versiones WHERE IdVersion = @IdVersion
--//

--Recuperar los parametros de cuantias menores para medios magneticos
--//
SELECT @CM_TipoIdent = CAST(ValParametro AS INT) FROM PARAMETROS WHERE CodParametro = 78
IF @CM_TipoIdent IS NULL
	SET @CM_TipoIdent = 43

SELECT @CM_Identif = ValParametro FROM PARAMETROS WHERE CodParametro = 79
IF @CM_Identif IS NULL
	SET @CM_Identif = '222222222'

SELECT @CM_RazonSocial = ValParametro FROM PARAMETROS WHERE CodParametro = 80
IF @CM_RazonSocial IS NULL
	SET @CM_RazonSocial = 'CUANTIAS MENORES'

SELECT @CM_LongNombres = CAST(ValParametro AS INT) FROM PARAMETROS WHERE CodParametro = 81
IF @CM_LongNombres IS NULL
	SET @CM_LongNombres = 60

SELECT @CM_LongRazonSocial = CAST(ValParametro AS INT) FROM PARAMETROS WHERE CodParametro = 82
IF @CM_LongRazonSocial IS NULL
	SET @CM_LongRazonSocial = 450

SELECT @CM_LongDireccion = CAST(ValParametro AS INT) FROM PARAMETROS WHERE CodParametro = 83
IF @CM_LongDireccion IS NULL
	SET @CM_LongDireccion = 200

--//

SET @FechaIniT = CAST(CONVERT(DATETIME, @FechaIni, 103) + ' 00:00:00' AS DATETIME)
SET @FechaFinT = CAST(CONVERT(DATETIME, @FechaFin, 103) + ' 23:59:59' AS DATETIME)

--//Tarifa ICA primera Empresa
SET @IdEmpFirst= (SELECT top(1) number FROM iter$simple_intlist_to_tbl(@IdEmpresas))

SELECT @TarifaIca=ValorTarifaIca FROM Empresas AS Emp INNER JOIN Empresas_ActividadesEcon Act ON Emp.EmpresaIdActivEconPrinc=Act.IdActividad
WHERE idEmpresa=@IdEmpFirst


--//Vigencia
set @Vigencia=YEAR(@FechaIniT)

--//Para el Formato 1
IF @IdFormato = 1 AND @CodVersion = 8
BEGIN
	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	Cli.DVCliente, 
	SUBSTRING(Cli.Apellido1,1,@CM_LongNombres) AS Apellido1,
	SUBSTRING(Cli.Apellido2,1,@CM_LongNombres) AS Apellido2,
	CASE WHEN NOT(Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21) THEN SUBSTRING(COALESCE(Cli.Nombre1,''),1,@CM_LongNombres) ELSE '' END AS Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE '' END) AS RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	SUBSTRING(Dir.DireccionFormato2,1,@CM_LongDireccion) AS DireccionFormato2,
	'' NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	@TarifaIca AS TarifaIca,
	'' TarifaAplicada,
	'' NombreCompleto,
	'' CodCiudad,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	RIGHT('000' + CAST(COALESCE(Dir.CodMunicipio,0) AS VARCHAR),3) AS CodMunicipio,
	COALESCE(Dir.CodigoDian,0) AS CodigoPais,
	M_Conc.CodConcepto,
	U.IdConcepto,
	CASE WHEN U.IdCliente = 0 THEN COALESCE(@IdCliInf,0) ELSE U.IdCliente END AS IdCliente, 
	U.EsCuantiaMayor, 
	'' ConceptoPagoCuenta,
	0.0 AS ValorTotalDevRebDes,
	ABS(ROUND(COALESCE(U.M1_Col1_PagoDeducible, 0.0),0)) AS M1_Col1_PagoDeducible,
	ABS(ROUND(COALESCE(U.M1_Col2_PagoNoDeducible, 0.0),0)) AS M1_Col2_PagoNoDeducible,
	ABS(ROUND(COALESCE(U.M1_Col3_IvaDeducible, 0.0),0)) AS M1_Col3_IvaDeducible,
	ABS(ROUND(COALESCE(U.M1_Col4_IvaNoDeducible, 0.0),0)) AS M1_Col4_IvaNoDeducible,
	ABS(ROUND(COALESCE(U.M1_Col5_ReteFteAplicada, 0.0),0)) AS M1_Col5_ReteFteAplicada,
	ABS(ROUND(COALESCE(U.M1_Col6_ReteFteAsumida, 0.0),0)) AS M1_Col6_ReteFteAsumida,
	ABS(ROUND(COALESCE(U.M1_Col7_ReteIvaRegComun, 0.0),0)) AS M1_Col7_ReteIvaRegComun,
	ABS(ROUND(COALESCE(U.M1_Col8_ReteIvaRegSimpC, 0.0),0)) AS M1_Col8_ReteIvaRegSimpC,
	ABS(ROUND(COALESCE(U.M1_Col9_ReteFteIvaNoDomic, 0.0),0)) AS M1_Col9_ReteFteIvaNoDomic,
	0.0 AS M1_Col10_ReteFteCREEAplicada, 
	0.0 AS M1_Col11_ReteFteCREEAsumida, 
	0.0 AS M3_Col1_ValorPagoAbono, 0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev, 0.0 AS M5_Col3_IvaMayorVCG,
	0.0 AS M6_Col1_ImptoGenerado, 0.0 AS M6_Col2_IvaRecup, 0.0 AS M6_Col3_ImptoConsumo,
	0.0 AS M7_Col1_IngBrutOpPropias, 0.0 AS M7_Col2_IngBrutConsorcio, 0.0 AS M7_Col3_IngContMandato,0.0 AS M7_Col4_IngExploracion, 0.0 AS M7_Col5_IngPorFiducia, 0.0 AS M7_Col6_IngRecPorTerceros, 0.0 AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	0.0 AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase, 
	0.0 AS M19_Col2_Retencion
	
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		SUM(T.PagoDeducible) AS M1_Col1_PagoDeducible, 
		SUM(T.PagoNoDeducible) AS M1_Col2_PagoNoDeducible, 
		SUM(T.IvaDeducible) AS M1_Col3_IvaDeducible, 
		SUM(T.IvaNoDeducible) AS M1_Col4_IvaNoDeducible, 
		SUM(T.ReteFteAplicada) AS M1_Col5_ReteFteAplicada, 
		SUM(T.ReteFteAsumida) AS M1_Col6_ReteFteAsumida, 
		SUM(T.ReteIvaRegComun) AS M1_Col7_ReteIvaRegComun, 
		SUM(T.ReteIvaRegSimpC) AS M1_Col8_ReteIvaRegSimpC, 
		SUM(T.ReteFteIvaNoDomic) AS M1_Col9_ReteFteIvaNoDomic
		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, 
			M_Ppal.IdCliente, 
			SUM(M_Ppal.PagoDeducible) AS PagoDeducible, 
			SUM(M_Ppal.PagoNoDeducible) AS PagoNoDeducible, 
			SUM(M_Ivas.IvaDeducible) AS IvaDeducible, 
			SUM(M_Ivas.IvaNoDeducible) AS IvaNoDeducible, 
			SUM(M_ReteFte.ReteFteAplicada) AS ReteFteAplicada, 
			SUM(M_ReteFte.ReteFteAsumida) AS ReteFteAsumida, 
			SUM(M_ReteIvaRCom.ReteIvaRegComun) AS ReteIvaRegComun, 
			SUM(M_ReteIvaRSC.ReteIvaRegSimpC) AS ReteIvaRegSimpC, 
			SUM(M_ReteFteNoDom.ReteFteIvaNoDomic) AS ReteFteIvaNoDomic, 
			MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor
			FROM 
			dbo.VMediosMag_Procesos_Principal AS M_Ppal INNER JOIN dbo.ufnMediosMagneticos_CuantiasMenores(@FechaIni, @FechaFin, @IdEmpresas, @IdDocsExc,@IdVersion) AS M_Cuantias ON M_Ppal.IdVersion = M_Cuantias.IdVersion AND M_Ppal.IdCliente = M_Cuantias.IdCliente LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteFteIvaNoDomic_Fin AS M_ReteFteNoDom ON M_Ppal.IdCliente = M_ReteFteNoDom.IdCliente AND M_Ppal.IdEncab = M_ReteFteNoDom.IdEncab AND M_Ppal.IdAgrup = M_ReteFteNoDom.IdAgrup LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteFuente_Fin AS M_ReteFte ON M_Ppal.IdCliente = M_ReteFte.IdCliente AND M_Ppal.IdEncab = M_ReteFte.IdEncab AND 
			M_Ppal.IdAgrup = M_ReteFte.IdAgrup LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteIvaRegComun_Fin AS M_ReteIvaRCom ON M_Ppal.IdCliente = M_ReteIvaRCom.IdCliente AND M_Ppal.IdEncab = M_ReteIvaRCom.IdEncab AND M_Ppal.IdAgrup = M_ReteIvaRCom.IdAgrup LEFT OUTER JOIN dbo.VMediosMag_Procesos_IvaMayorValor_Fin AS M_Ivas ON M_Ppal.IdCliente = M_Ivas.IdCliente AND M_Ppal.IdEncab = M_Ivas.IdEncab AND M_Ppal.IdAgrup = M_Ivas.IdAgrup AND M_Ppal.IdVersion = M_Ivas.IdVersion LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteIvaRegSimpC_Fin AS M_ReteIvaRSC ON M_Ppal.IdCliente = M_ReteIvaRSC.IdCliente AND M_Ppal.IdEncab = M_ReteIvaRSC.IdEncab AND M_Ppal.IdAgrup = M_ReteIvaRSC.IdAgrup
			WHERE
			M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) BETWEEN @FechaIniT AND @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	RETURN
END

--//Para el Formato 1 Y Version 9
IF @IdFormato = 1 AND @CodVersion = 9
BEGIN
	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	Cli.DVCliente, 
	SUBSTRING(Cli.Apellido1,1,@CM_LongNombres) AS Apellido1,
	SUBSTRING(Cli.Apellido2,1,@CM_LongNombres) AS Apellido2,
	CASE WHEN NOT(Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21) THEN SUBSTRING(COALESCE(Cli.Nombre1,''),1,@CM_LongNombres) ELSE '' END AS Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE '' END) AS RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	SUBSTRING(Dir.DireccionFormato2,1,@CM_LongDireccion) AS DireccionFormato2,
	'' NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	@TarifaIca AS TarifaIca,
	'' TarifaAplicada,
	'' NombreCompleto,
	'' CodCiudad,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	RIGHT('000' + CAST(COALESCE(Dir.CodMunicipio,0) AS VARCHAR),3) AS CodMunicipio,
	COALESCE(Dir.CodigoDian,0) AS CodigoPais,
	M_Conc.CodConcepto,
	U.IdConcepto,
	CASE WHEN U.IdCliente = 0 THEN COALESCE(@IdCliInf,0) ELSE U.IdCliente END AS IdCliente, 
	U.EsCuantiaMayor, 
	'' ConceptoPagoCuenta,
	0.0 AS ValorTotalDevRebDes,
	ABS(ROUND(COALESCE(U.M1_Col1_PagoDeducible, 0.0),0)) AS M1_Col1_PagoDeducible,
	ABS(ROUND(COALESCE(U.M1_Col2_PagoNoDeducible, 0.0),0)) AS M1_Col2_PagoNoDeducible,
	ABS(ROUND(COALESCE(U.M1_Col3_IvaDeducible, 0.0),0)) AS M1_Col3_IvaDeducible,
	ABS(ROUND(COALESCE(U.M1_Col4_IvaNoDeducible, 0.0),0)) AS M1_Col4_IvaNoDeducible,
	ABS(ROUND(COALESCE(U.M1_Col5_ReteFteAplicada, 0.0),0)) AS M1_Col5_ReteFteAplicada,
	ABS(ROUND(COALESCE(U.M1_Col6_ReteFteAsumida, 0.0),0)) AS M1_Col6_ReteFteAsumida,
	ABS(ROUND(COALESCE(U.M1_Col7_ReteIvaRegComun, 0.0),0)) AS M1_Col7_ReteIvaRegComun,
	ABS(ROUND(COALESCE(U.M1_Col8_ReteIvaRegSimpC, 0.0),0)) AS M1_Col8_ReteIvaRegSimpC,
	ABS(ROUND(COALESCE(U.M1_Col9_ReteFteIvaNoDomic, 0.0),0)) AS M1_Col9_ReteFteIvaNoDomic,
	ABS(ROUND(COALESCE(U.M1_Col10_ReteFteCREEAplicada, 0.0),0)) AS M1_Col10_ReteFteCREEAplicada,
	ABS(ROUND(COALESCE(U.M1_Col11_ReteFteCREEAsumida, 0.0),0)) AS M1_Col11_ReteFteCREEAsumida,
	0.0 AS M3_Col1_ValorPagoAbono, 0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev, 0.0 AS M5_Col3_IvaMayorVCG,
	0.0 AS M6_Col1_ImptoGenerado, 0.0 AS M6_Col2_IvaRecup, 0.0 AS M6_Col3_ImptoConsumo,
	0.0 AS M7_Col1_IngBrutOpPropias, 0.0 AS M7_Col2_IngBrutConsorcio, 0.0 AS M7_Col3_IngContMandato,0.0 AS M7_Col4_IngExploracion, 0.0 AS M7_Col5_IngPorFiducia, 0.0 AS M7_Col6_IngRecPorTerceros, 0.0 AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	0.0 AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase, 
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		SUM(T.PagoDeducible) AS M1_Col1_PagoDeducible, 
		SUM(T.PagoNoDeducible) AS M1_Col2_PagoNoDeducible, 
		SUM(T.IvaDeducible) AS M1_Col3_IvaDeducible, 
		SUM(T.IvaNoDeducible) AS M1_Col4_IvaNoDeducible, 
		SUM(T.ReteFteAplicada) AS M1_Col5_ReteFteAplicada, 
		SUM(T.ReteFteAsumida) AS M1_Col6_ReteFteAsumida, 
		SUM(T.ReteIvaRegComun) AS M1_Col7_ReteIvaRegComun, 
		SUM(T.ReteIvaRegSimpC) AS M1_Col8_ReteIvaRegSimpC, 
		SUM(T.ReteFteIvaNoDomic) AS M1_Col9_ReteFteIvaNoDomic,
		SUM(T.ReteFteCREEAplicada) AS M1_Col10_ReteFteCREEAplicada, 
		SUM(T.ReteFteCREEAsumida) AS M1_Col11_ReteFteCREEAsumida
		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, 
			M_Ppal.IdCliente, 
			SUM(M_Ppal.PagoDeducible) AS PagoDeducible, 
			SUM(M_Ppal.PagoNoDeducible) AS PagoNoDeducible, 
			SUM(M_Ivas.IvaDeducible) AS IvaDeducible, 
			SUM(M_Ivas.IvaNoDeducible) AS IvaNoDeducible, 
			SUM(M_ReteFte.ReteFteAplicada) AS ReteFteAplicada, 
			SUM(M_ReteFte.ReteFteAsumida) AS ReteFteAsumida, 
			SUM(M_ReteIvaRCom.ReteIvaRegComun) AS ReteIvaRegComun, 
			SUM(M_ReteIvaRSC.ReteIvaRegSimpC) AS ReteIvaRegSimpC, 
			SUM(M_ReteFteNoDom.ReteFteIvaNoDomic) AS ReteFteIvaNoDomic, 
			SUM(M_ReteFteCREE.ReteFteCREEAplicada) AS ReteFteCREEAplicada, 
			SUM(M_ReteFteCREE.ReteFteCREEAsumida) AS ReteFteCREEAsumida, 
			MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor
			FROM 
			dbo.VMediosMag_Procesos_Principal AS M_Ppal INNER JOIN dbo.ufnMediosMagneticos_CuantiasMenores(@FechaIni, @FechaFin, @IdEmpresas, @IdDocsExc,@IdVersion) AS M_Cuantias ON M_Ppal.IdVersion = M_Cuantias.IdVersion AND M_Ppal.IdCliente = M_Cuantias.IdCliente LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteFuenteCREE_Fin AS M_ReteFteCREE ON M_Ppal.IdEncab = M_ReteFteCREE.IdEncab AND M_Ppal.IdAgrup = M_ReteFteCREE.IdAgrup AND M_Ppal.IdCliente = M_ReteFteCREE.IdCliente LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteFteIvaNoDomic_Fin AS M_ReteFteNoDom ON M_Ppal.IdCliente = M_ReteFteNoDom.IdCliente AND M_Ppal.IdEncab = M_ReteFteNoDom.IdEncab AND M_Ppal.IdAgrup = M_ReteFteNoDom.IdAgrup LEFT OUTER JOIN
            dbo.VMediosMag_Procesos_ReteFuente_Fin AS M_ReteFte ON M_Ppal.IdCliente = M_ReteFte.IdCliente AND M_Ppal.IdEncab = M_ReteFte.IdEncab AND M_Ppal.IdAgrup = M_ReteFte.IdAgrup LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteIvaRegComun_Fin AS M_ReteIvaRCom ON M_Ppal.IdCliente = M_ReteIvaRCom.IdCliente AND M_Ppal.IdEncab = M_ReteIvaRCom.IdEncab AND M_Ppal.IdAgrup = M_ReteIvaRCom.IdAgrup LEFT OUTER JOIN dbo.VMediosMag_Procesos_IvaMayorValor_Fin AS M_Ivas ON M_Ppal.IdCliente = M_Ivas.IdCliente AND M_Ppal.IdEncab = M_Ivas.IdEncab AND M_Ppal.IdAgrup = M_Ivas.IdAgrup AND M_Ppal.IdVersion = M_Ivas.IdVersion LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteIvaRegSimpC_Fin AS M_ReteIvaRSC ON M_Ppal.IdCliente = M_ReteIvaRSC.IdCliente AND M_Ppal.IdEncab = M_ReteIvaRSC.IdEncab AND M_Ppal.IdAgrup = M_ReteIvaRSC.IdAgrup
			WHERE
			M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) BETWEEN @FechaIniT AND @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	RETURN
END

--//Para el Formato 1 Y Version 10
IF @IdFormato = 1 AND @CodVersion = 10 
BEGIN
	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	CASE WHEN NOT(Cli.DVCliente = '') THEN '' END AS DVCliente,
	SUBSTRING(Cli.Apellido1,1,@CM_LongNombres) AS Apellido1,
	SUBSTRING(Cli.Apellido2,1,@CM_LongNombres) AS Apellido2,
	CASE WHEN NOT(Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21) THEN SUBSTRING(COALESCE(Cli.Nombre1,''),1,@CM_LongNombres) ELSE '' END AS Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE '' END) AS RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	SUBSTRING(Dir.DireccionFormato2,1,@CM_LongDireccion) AS DireccionFormato2,
	'' NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	@TarifaIca AS TarifaIca,
	'' TarifaAplicada,
	'' NombreCompleto,
	'' CodCiudad,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	RIGHT('000' + CAST(COALESCE(Dir.CodMunicipio,0) AS VARCHAR),3) AS CodMunicipio,
	COALESCE(Dir.CodigoDian,0) AS CodigoPais,
	M_Conc.CodConcepto,
	U.IdConcepto,
	CASE WHEN U.IdCliente = 0 THEN COALESCE(@IdCliInf,0) ELSE U.IdCliente END AS IdCliente, 
	U.EsCuantiaMayor, 
	'' ConceptoPagoCuenta,
	0.0 AS ValorTotalDevRebDes,
	ABS(ROUND(COALESCE(U.M1_Col1_PagoDeducible, 0.0),0)) AS M1_Col1_PagoDeducible,
	ABS(ROUND(COALESCE(U.M1_Col2_PagoNoDeducible, 0.0),0)) AS M1_Col2_PagoNoDeducible,
	ABS(ROUND(COALESCE(U.M1_Col3_IvaDeducible, 0.0),0)) AS M1_Col3_IvaDeducible,
	ABS(ROUND(COALESCE(U.M1_Col4_IvaNoDeducible, 0.0),0)) AS M1_Col4_IvaNoDeducible,
	ABS(ROUND(COALESCE(U.M1_Col5_ReteFteAplicada, 0.0),0)) AS M1_Col5_ReteFteAplicada,
	ABS(ROUND(COALESCE(U.M1_Col6_ReteFteAsumida, 0.0),0)) AS M1_Col6_ReteFteAsumida,
	ABS(ROUND(COALESCE(U.M1_Col7_ReteIvaRegComun, 0.0),0)) AS M1_Col7_ReteIvaRegComun,
	ABS(ROUND(COALESCE(U.M1_Col9_ReteFteIvaNoDomic, 0.0),0)) AS M1_Col9_ReteFteIvaNoDomic,
    0.0 AS M1_Col8_ReteIvaRegSimpC, 
	0.0 AS M1_Col10_ReteFteCREEAplicada, 
	0.0 AS M1_Col11_ReteFteCREEAsumida, 
	0.0 AS M3_Col1_ValorPagoAbono, 0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev, 0.0 AS M5_Col3_IvaMayorVCG,
	0.0 AS M6_Col1_ImptoGenerado, 0.0 AS M6_Col2_IvaRecup, 0.0 AS M6_Col3_ImptoConsumo,
	0.0 AS M7_Col1_IngBrutOpPropias, 0.0 AS M7_Col2_IngBrutConsorcio, 0.0 AS M7_Col3_IngContMandato,0.0 AS M7_Col4_IngExploracion, 0.0 AS M7_Col5_IngPorFiducia, 0.0 AS M7_Col6_IngRecPorTerceros, 0.0 AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	0.0 AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase, 
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		SUM(T.PagoDeducible) AS M1_Col1_PagoDeducible, 
		SUM(T.PagoNoDeducible) AS M1_Col2_PagoNoDeducible, 
		SUM(T.IvaDeducible) AS M1_Col3_IvaDeducible, 
		SUM(T.IvaNoDeducible) AS M1_Col4_IvaNoDeducible, 
		SUM(T.ReteFteAplicada) AS M1_Col5_ReteFteAplicada, 
		SUM(T.ReteFteAsumida) AS M1_Col6_ReteFteAsumida, 
		SUM(T.ReteIvaRegComun) AS M1_Col7_ReteIvaRegComun, 
		SUM(T.ReteFteIvaNoDomic) AS M1_Col9_ReteFteIvaNoDomic
		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, 
			M_Ppal.IdCliente, 
			SUM(M_Ppal.PagoDeducible) AS PagoDeducible, 
			SUM(M_Ppal.PagoNoDeducible) AS PagoNoDeducible, 
			SUM(M_Ivas.IvaDeducible) AS IvaDeducible, 
			SUM(M_Ivas.IvaNoDeducible) AS IvaNoDeducible, 
			SUM(M_ReteFte.ReteFteAplicada) AS ReteFteAplicada, 
			SUM(M_ReteFte.ReteFteAsumida) AS ReteFteAsumida, 
			SUM(M_ReteIvaRCom.ReteIvaRegComun) AS ReteIvaRegComun, 
			SUM(M_ReteFteNoDom.ReteFteIvaNoDomic) AS ReteFteIvaNoDomic,  
			MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor
			FROM 
			dbo.VMediosMag_Procesos_Principal AS M_Ppal INNER JOIN dbo.ufnMediosMagneticos_CuantiasMenores(@FechaIni, @FechaFin, @IdEmpresas, @IdDocsExc,@IdVersion) AS M_Cuantias ON M_Ppal.IdVersion = M_Cuantias.IdVersion AND M_Ppal.IdCliente = M_Cuantias.IdCliente LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteFuenteCREE_Fin AS M_ReteFteCREE ON M_Ppal.IdEncab = M_ReteFteCREE.IdEncab AND M_Ppal.IdAgrup = M_ReteFteCREE.IdAgrup AND M_Ppal.IdCliente = M_ReteFteCREE.IdCliente LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteFteIvaNoDomic_Fin AS M_ReteFteNoDom ON M_Ppal.IdCliente = M_ReteFteNoDom.IdCliente AND M_Ppal.IdEncab = M_ReteFteNoDom.IdEncab AND M_Ppal.IdAgrup = M_ReteFteNoDom.IdAgrup LEFT OUTER JOIN
            dbo.VMediosMag_Procesos_ReteFuente_Fin AS M_ReteFte ON M_Ppal.IdCliente = M_ReteFte.IdCliente AND M_Ppal.IdEncab = M_ReteFte.IdEncab AND M_Ppal.IdAgrup = M_ReteFte.IdAgrup LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteIvaRegComun_Fin AS M_ReteIvaRCom ON M_Ppal.IdCliente = M_ReteIvaRCom.IdCliente AND M_Ppal.IdEncab = M_ReteIvaRCom.IdEncab AND M_Ppal.IdAgrup = M_ReteIvaRCom.IdAgrup LEFT OUTER JOIN dbo.VMediosMag_Procesos_IvaMayorValor_Fin AS M_Ivas ON M_Ppal.IdCliente = M_Ivas.IdCliente AND M_Ppal.IdEncab = M_Ivas.IdEncab AND M_Ppal.IdAgrup = M_Ivas.IdAgrup AND M_Ppal.IdVersion = M_Ivas.IdVersion LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteIvaRegSimpC_Fin AS M_ReteIvaRSC ON M_Ppal.IdCliente = M_ReteIvaRSC.IdCliente AND M_Ppal.IdEncab = M_ReteIvaRSC.IdEncab AND M_Ppal.IdAgrup = M_ReteIvaRSC.IdAgrup
			WHERE
			M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) BETWEEN @FechaIniT AND @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	RETURN
END

--//Para el Formato 3
--//
IF @IdFormato = 3 AND @CodVersion = 7 
BEGIN
	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	Cli.DVCliente, 
	SUBSTRING(Cli.Apellido1,1,@CM_LongNombres) AS Apellido1,
	SUBSTRING(Cli.Apellido2,1,@CM_LongNombres) AS Apellido2,
	CASE WHEN NOT(Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21) THEN SUBSTRING(COALESCE(Cli.Nombre1,''),1,@CM_LongNombres) ELSE '' END AS Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE '' END) AS RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	SUBSTRING(Dir.DireccionFormato2,1,@CM_LongDireccion) AS DireccionFormato2,
	'' NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	@TarifaIca AS TarifaIca,
	'' TarifaAplicada,
	'' NombreCompleto,
	'' CodCiudad,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	RIGHT('000' + CAST(COALESCE(Dir.CodMunicipio,0) AS VARCHAR),3) AS CodMunicipio,
	COALESCE(Dir.CodigoDian,0) AS CodigoPais,
	M_Conc.CodConcepto,
	U.IdConcepto,
	CASE WHEN U.IdCliente = 0 THEN COALESCE(@IdCliInf,0) ELSE U.IdCliente END AS IdCliente, 
	U.EsCuantiaMayor, 
	'' ConceptoPagoCuenta,
	0.0 AS ValorTotalDevRebDes,
	0.0 AS M1_Col1_PagoDeducible, 0.0 AS M1_Col2_PagoNoDeducible, 0.0 AS M1_Col3_IvaDeducible, 0.0 AS M1_Col4_IvaNoDeducible, 0.0 AS M1_Col5_ReteFteAplicada, 0.0 AS M1_Col6_ReteFteAsumida, 0.0 AS M1_Col7_ReteIvaRegComun, 0.0 AS M1_Col8_ReteIvaRegSimpC, 0.0 AS M1_Col9_ReteFteIvaNoDomic, 0.0 AS M1_Col10_ReteFteCREEAplicada, 0.0 AS M1_Col11_ReteFteCREEAsumida, 
	ABS(ROUND(COALESCE(U.M3_Col1_ValorPagoAbono, 0.0),0)) AS M3_Col1_ValorPagoAbono, 
	ABS(ROUND(COALESCE(U.M3_Col2_Retencion, 0.0),0)) AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev, 0.0 AS M5_Col3_IvaMayorVCG,
	0.0 AS M6_Col1_ImptoGenerado, 0.0 AS M6_Col2_IvaRecup, 0.0 AS M6_Col3_ImptoConsumo,
	0.0 AS M7_Col1_IngBrutOpPropias, 0.0 AS M7_Col2_IngBrutConsorcio, 0.0 AS M7_Col3_IngContMandato,0.0 AS M7_Col4_IngExploracion, 0.0 AS M7_Col5_IngPorFiducia, 0.0 AS M7_Col6_IngRecPorTerceros, 0.0 AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	0.0 AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase, 
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		SUM(T.PagoDeducible) AS M3_Col1_ValorPagoAbono, 
		SUM(T.ReteFteAplicada) AS M3_Col2_Retencion
		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, 
			M_Ppal.IdCliente, 
			SUM(M_Ppal.RetencionBase) AS PagoDeducible, 
			SUM(COALESCE(M_Ppal.SumRetReal,M_Ppal.PagoDeducible)) AS ReteFteAplicada, 
			MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor
			FROM 
			dbo.VMediosMag_Procesos_Principal AS M_Ppal 
			INNER JOIN dbo.ufnMediosMagneticos_CuantiasMenores(@FechaIni, @FechaFin, @IdEmpresas, @IdDocsExc,@IdVersion) AS M_Cuantias ON M_Ppal.IdVersion = M_Cuantias.IdVersion AND M_Ppal.IdCliente = M_Cuantias.IdCliente 
			--INNER JOIN dbo.VMediosMag_Procesos_ReteFuente_Fin AS M_ReteFte ON M_Ppal.IdCliente = M_ReteFte.IdCliente AND M_Ppal.IdEncab = M_ReteFte.IdEncab AND M_Ppal.IdAgrup = M_ReteFte.IdAgrup 
			WHERE
			M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) BETWEEN @FechaIniT AND @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
		HAVING SUM(T.ReteFteAplicada) <> 0

	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	RETURN
END
--//


--Para el formato 7,4,5,6,11
IF @IdFormato = 7 OR @IdFormato = 4 OR @IdFormato = 5 OR @IdFormato = 6 OR @IdFormato = 11
BEGIN 
 

 ;WITH tempMediosCont(IdCuenta,IdEncab,IdAgrup,IdCliente,Fecha,IdEmpresa,IdDocumento,PagoDeducible,PagoNoDeducible,PagoTotal,RetencionBase,SumRetReal)
 AS
 (
  SELECT     IdCuenta,Enc_C.IdEncab,ISNULL(Enc_C.IdEncabContAgrup, 0) AS IdAgrup, Enc_C.IdCliente,
     MAX(Enc.Fecha) AS Fecha, MAX(Enc.IdEmpresa) AS IdEmpresa, MAX(Enc.IdDocumento) AS IdDocumento, 
                         SUM(CASE WHEN Enc_C.EsDeducible = 0 THEN Enc_C.Debito - Enc_C.Credito ELSE 0 END) AS PagoDeducible, SUM(CASE WHEN Enc_C.EsDeducible = 1 THEN Enc_C.Debito - Enc_C.Credito ELSE 0 END) AS PagoNoDeducible, 
                         SUM(Enc_C.Debito - Enc_C.Credito) AS PagoTotal, SUM(CASE WHEN ISNULL(Enc_C.RetencionPorc, 0) > 0 THEN ABS(Enc_C.Debito - Enc_C.Credito) * 100 / Enc_C.RetencionPorc ELSE 0 END) AS RetencionBase, 
                         SUM(ISNULL(Enc_C.RetFuenteReal, 0)) AS SumRetReal
FROM    dbo.Encabezados_Cont AS Enc_C INNER JOIN
         dbo.Encabezados AS Enc ON Enc_C.IdEncab = Enc.IdEncab
  WHERE     
  (Enc.EsAnulado = 0) AND
			DATEADD(dd,DATEDIFF(dd,0,Enc.Fecha),0) BETWEEN @FechaIniT AND @FechaFinT AND
			Enc.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			Enc.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			Enc_C.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
GROUP BY IdCuenta, Enc_C.IdEncab, Enc_C.IdCliente, ISNULL(Enc_C.IdEncabContAgrup, 0)

 ),

 tempMediosPrincipal(IdVersion, IdEncab, IdAgrup, IdCliente, IdClienteAgrupMM, IdConcepto, NumCol, IdAgrupMM,  CodConcepto, PagoDeducible, PagoNoDeducible, PagoTotal, RetencionBase, SumRetReal)
 AS
 (
 SELECT        MM_Cuentas.IdVersion, Enc_C.IdEncab,IdAgrup, Enc_C.IdCliente, MM_Cuentas.IdCliente AS IdClienteAgrupMM, MM_Cuentas.IdConcepto, MM_Cuentas.NumCol, MM_Cuentas.IdAgrupMM, MAX(MM_Conc.CodConcepto) AS CodConcepto,
                         SUM(PagoDeducible), SUM(PagoNoDeducible), 
                         SUM(PagoTotal), SUM(RetencionBase), 
                         SUM(SumRetReal)SumRetReal
FROM            dbo.MediosMag_CuentasFormato AS MM_Cuentas INNER JOIN
                         tempMediosCont AS Enc_C ON MM_Cuentas.IdCuenta = Enc_C.IdCuenta  INNER JOIN
                         dbo.MediosMag_Conceptos AS MM_Conc ON MM_Cuentas.IdConcepto = MM_Conc.IdConcepto
    
GROUP BY MM_Cuentas.IdConcepto, Enc_C.IdEncab, Enc_C.IdCliente,IdAgrup, MM_Cuentas.IdVersion, MM_Cuentas.NumCol, MM_Cuentas.IdCliente, MM_Cuentas.IdAgrupMM
)

,

tempDir(Idcliente,Direccion,DireccionFormato1,DireccionFormato2,Telefonos,EMail,CodDpto,CodMunicipio,CodigoPais)
AS
(
SELECT
tempDirPri.Idcliente,      
	SUBSTRING(Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	SUBSTRING(DireccionFormato2,1,@CM_LongDireccion) AS DireccionFormato2,
	[Telefonos] AS Telefonos,
	EMail AS EMail,
	RIGHT('00' + CAST(ISNULL( dbo.Departamentos.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	RIGHT('000' + CAST(ISNULL(CodMunicipio,0) AS VARCHAR),3) AS CodMunicipio,
	ISNULL(CAST(CodigoDian AS INT),0) AS CodigoPais
FROM          
						
                         VClientesDir_01 AS tempDirPri INNER JOIN
                         dbo.Clientes_Direcciones ON tempDirPri.IdClienteDirMax = dbo.Clientes_Direcciones.IdClienteDir  LEFT JOIN
                         dbo.Clientes_TiposDireccion ON dbo.Clientes_Direcciones.IdTipoDir = dbo.Clientes_TiposDireccion.IdTipoDir LEFT JOIN
						 dbo.Ciudades  ON dbo.Ciudades.IdCiudad = dbo.Clientes_Direcciones.IdCiudad  LEFT JOIN
						 dbo.Departamentos ON dbo.Departamentos.IdDpto = dbo.Ciudades.IdDpto  LEFT JOIN
						 dbo.Paises ON dbo.Departamentos.IdPais = dbo.Paises.IdPais 
)


	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	CASE WHEN (@IdFormato = 7 AND @CodVersion = 9) THEN '' ELSE Cli.DVCliente END AS DVCliente,
	SUBSTRING(Cli.Apellido1,1,@CM_LongNombres) AS Apellido1,
	SUBSTRING(Cli.Apellido2,1,@CM_LongNombres) AS Apellido2,
	CASE WHEN NOT(Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21) THEN SUBSTRING(ISNULL(Cli.Nombre1,''),1,@CM_LongNombres) ELSE '' END AS Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	ISNULL(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE '' END) AS RazonSocial,
	Direccion,
	DireccionFormato1,
	DireccionFormato2,
	'' NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	@TarifaIca AS TarifaIca,
	'' TarifaAplicada,
	'' NombreCompleto,
	'' CodCiudad,
	ISNULL(CodDpto,0)AS CodDpto,
	ISNULL(CodMunicipio,0)AS CodMunicipio,
	ISNULL(CodigoPais,0)AS CodigoPais,
	M_Conc.CodConcepto,
	U.IdConcepto,
	CASE WHEN U.IdCliente = 0 THEN ISNULL(@IdCliInf,0) ELSE U.IdCliente END AS IdCliente, 
	U.EsCuantiaMayor, 
	'' ConceptoPagoCuenta,
	0.0 AS ValorTotalDevRebDes,
	0.0 AS M1_Col1_PagoDeducible, 0.0 AS M1_Col2_PagoNoDeducible, 0.0 AS M1_Col3_IvaDeducible, 0.0 AS M1_Col4_IvaNoDeducible, 0.0 AS M1_Col5_ReteFteAplicada, 0.0 AS M1_Col6_ReteFteAsumida, 0.0 AS M1_Col7_ReteIvaRegComun, 0.0 AS M1_Col8_ReteIvaRegSimpC, 0.0 AS M1_Col9_ReteFteIvaNoDomic, 0.0 AS M1_Col10_ReteFteCREEAplicada, 0.0 AS M1_Col11_ReteFteCREEAsumida, 
	0.0 AS M3_Col1_ValorPagoAbono, 0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	U.M5_Col1_ValorImptoDesc AS M5_Col1_ValorImptoDesc, 
	U.M5_Col2_IvaResDev AS M5_Col2_IvaResDev,
	U.M5_Col3_IvaMayorVCG AS M5_Col3_IvaMayorVCG,
	U.M6_Col1_ImptoGenerado AS M6_Col1_ImptoGenerado, 
	U.M6_Col2_IvaRecup AS M6_Col2_IvaRecup,
	U.M6_Col3_ImptoConsumo AS M6_Col3_ImptoConsumo,
	U.M7_Col1_IngBrutOpPropias AS M7_Col1_IngBrutOpPropias, 
	U.M7_Col2_IngBrutConsorcio AS M7_Col2_IngBrutConsorcio, 
	CASE WHEN (@IdFormato = 7 AND @CodVersion = 9) THEN 0 ELSE U.M7_Col3_IngContMandato END AS M7_Col3_IngContMandato, 
	CASE WHEN (@IdFormato = 7 AND @CodVersion = 9) THEN 0 ELSE U.M7_Col4_IngExploracion END AS M7_Col4_IngExploracion,
	CASE WHEN (@IdFormato = 7 AND @CodVersion = 9) THEN 0 ELSE M7_Col5_IngPorFiducia END AS M7_Col5_IngPorFiducia,
	CASE WHEN (@IdFormato = 7 AND @CodVersion = 9) THEN 0 ELSE M7_Col6_IngRecPorTerceros END AS M7_Col6_IngRecPorTerceros,
	U.M7_Col7_IngTercDevRebDctos AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	U.M9_Col1_Saldo AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase, 
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		ABS(ROUND(ISNULL(SUM(T.Col1), 0.0),0)) AS M5_Col1_ValorImptoDesc,
		ABS(ROUND(ISNULL(SUM(T.Col2), 0.0),0)) AS M5_Col2_IvaResDev,
		ABS(ROUND(ISNULL(SUM(T.Col3), 0.0),0)) AS M5_Col3_IvaMayorVCG,
		ABS(ROUND(ISNULL(SUM(T.Col1), 0.0),0)) AS M6_Col1_ImptoGenerado, 
		ABS(ROUND(ISNULL(SUM(T.Col2), 0.0),0)) AS M6_Col2_IvaRecup, 
		ABS(ROUND(ISNULL(SUM(T.Col3), 0.0),0)) AS M6_Col3_ImptoConsumo, 
		ABS(ROUND(ISNULL(SUM(T.Col1), 0.0),0)) AS M7_Col1_IngBrutOpPropias, 
		ABS(ROUND(ISNULL(SUM(T.Col2), 0.0),0)) AS M7_Col2_IngBrutConsorcio, 
		ABS(ROUND(ISNULL(SUM(T.Col3), 0.0),0)) AS M7_Col3_IngContMandato,
		ABS(ROUND(ISNULL(SUM(T.Col4), 0.0),0)) AS M7_Col4_IngExploracion, 
		ABS(ROUND(ISNULL(SUM(T.Col5), 0.0),0)) AS M7_Col5_IngPorFiducia, 
		ABS(ROUND(ISNULL(SUM(T.Col6), 0.0),0)) AS M7_Col6_IngRecPorTerceros, 
		ABS(ROUND(ISNULL(SUM(T.Col7), 0.0),0)) AS M7_Col7_IngTercDevRebDctos,
		ABS(ROUND(ISNULL(SUM(T.Col1), 0.0),0)) AS M9_Col1_Saldo

		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, M_Ppal.IdCliente, MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor, 
			SUM(CASE WHEN M_Ppal.NumCol = 1 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col1, SUM(CASE WHEN M_Ppal.NumCol = 2 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col2, SUM(CASE WHEN M_Ppal.NumCol = 3 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col3, SUM(CASE WHEN M_Ppal.NumCol = 4 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col4, SUM(CASE WHEN M_Ppal.NumCol = 5 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col5, SUM(CASE WHEN M_Ppal.NumCol = 6 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col6, SUM(CASE WHEN M_Ppal.NumCol = 7 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col7
			FROM         
			tempMediosPrincipal AS M_Ppal INNER JOIN dbo.ufnMediosMagneticos_CuantiasMenores(@FechaIni, @FechaFin, @IdEmpresas, @IdDocsExc,@IdVersion) AS M_Cuantias ON M_Ppal.IdVersion = M_Cuantias.IdVersion AND M_Ppal.IdCliente = M_Cuantias.IdCliente 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT JOIN tempDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	WHERE 
    U.M7_Col1_IngBrutOpPropias + U.M7_Col2_IngBrutConsorcio + U.M7_Col3_IngContMandato + U.M7_Col4_IngExploracion + U.M7_Col5_IngPorFiducia + U.M7_Col6_IngRecPorTerceros + U.M7_Col7_IngTercDevRebDctos <> 0
   OPTION(RECOMPILE)
    RETURN

END

IF @IdFormato IN(8,9,12)
BEGIN
	---Crear Tabla temporal de saldos de cuantias menores
	--//
	IF OBJECT_ID('tempdb..#ufnMediosMagneticos_CuantiasMenores_Saldos') IS NOT NULL
		DROP TABLE #ufnMediosMagneticos_CuantiasMenores_Saldos;

	SELECT *
	INTO #ufnMediosMagneticos_CuantiasMenores_Saldos
	FROM dbo.ufnMediosMagneticos_CuantiasMenores_Saldos(@FechaFin, @IdEmpresas, @IdDocsExc, @IdVersion);

	CREATE NONCLUSTERED INDEX IX_Cuantias_IdCliente ON #ufnMediosMagneticos_CuantiasMenores_Saldos(IdCliente);
	--//
END

--Para el Formato 8
IF @IdFormato = 8
BEGIN 
	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	Cli.DVCliente, 
	SUBSTRING(Cli.Apellido1,1,@CM_LongNombres) AS Apellido1,
	SUBSTRING(Cli.Apellido2,1,@CM_LongNombres) AS Apellido2,
	CASE WHEN NOT(Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21) THEN SUBSTRING(COALESCE(Cli.Nombre1,''),1,@CM_LongNombres) ELSE '' END AS Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE '' END) AS RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	SUBSTRING(Dir.DireccionFormato2,1,@CM_LongDireccion) AS DireccionFormato2,
	'' NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	@TarifaIca AS TarifaIca,
	'' TarifaAplicada,
	'' NombreCompleto,
	'' CodCiudad,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	RIGHT('000' + CAST(COALESCE(Dir.CodMunicipio,0) AS VARCHAR),3) AS CodMunicipio,
	COALESCE(Dir.CodigoDian,0) AS CodigoPais,
	M_Conc.CodConcepto,
	U.IdConcepto,
	CASE WHEN U.IdCliente = 0 THEN COALESCE(@IdCliInf,0) ELSE U.IdCliente END AS IdCliente, 
	U.EsCuantiaMayor, 
	'' ConceptoPagoCuenta,
	0.0 AS ValorTotalDevRebDes,
	0.0 AS M1_Col1_PagoDeducible, 0.0 AS M1_Col2_PagoNoDeducible, 0.0 AS M1_Col3_IvaDeducible, 0.0 AS M1_Col4_IvaNoDeducible, 0.0 AS M1_Col5_ReteFteAplicada, 0.0 AS M1_Col6_ReteFteAsumida, 0.0 AS M1_Col7_ReteIvaRegComun, 0.0 AS M1_Col8_ReteIvaRegSimpC, 0.0 AS M1_Col9_ReteFteIvaNoDomic, 0.0 AS M1_Col10_ReteFteCREEAplicada, 0.0 AS M1_Col11_ReteFteCREEAsumida, 
	0.0 AS M3_Col1_ValorPagoAbono, 0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev, 0.0 AS M5_Col3_IvaMayorVCG,
	0.0 AS M6_Col1_ImptoGenerado, 0.0 AS M6_Col2_IvaRecup, 0.0 AS M6_Col3_ImptoConsumo,
	0.0 AS M7_Col1_IngBrutOpPropias, 0.0 AS M7_Col2_IngBrutConsorcio, 0.0 AS M7_Col3_IngContMandato, 0.0 AS M7_Col4_IngExploracion, 0.0 AS M7_Col5_IngPorFiducia, 0.0 AS M7_Col6_IngRecPorTerceros, 0.0 AS M7_Col7_IngTercDevRebDctos,
	ABS(ROUND(COALESCE(U.M8_Col1_Saldo, 0.0),0)) AS M8_Col1_Saldo,
	0.0 AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase, 
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		SUM(T.Col1) AS M8_Col1_Saldo
		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, COALESCE(M_Ppal.IdClienteAgrupMM, M_Ppal.IdCliente) AS IdCliente, MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor, SUM(CASE WHEN M_Ppal.NumCol = 1 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col1
			FROM         
			dbo.VMediosMag_Procesos_Principal AS M_Ppal INNER JOIN #ufnMediosMagneticos_CuantiasMenores_Saldos AS M_Cuantias ON M_Ppal.IdCliente = M_Cuantias.IdCliente 
            WHERE     
            M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) <= @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConcepto, COALESCE(M_Ppal.IdClienteAgrupMM, M_Ppal.IdCliente)
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	WHERE 
    U.M8_Col1_Saldo <> 0
    RETURN

END

--Para el Formato 9
IF @IdFormato = 9
BEGIN 
	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	Cli.DVCliente, 
	SUBSTRING(Cli.Apellido1,1,@CM_LongNombres) AS Apellido1,
	SUBSTRING(Cli.Apellido2,1,@CM_LongNombres) AS Apellido2,
	CASE WHEN NOT(Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21) THEN SUBSTRING(COALESCE(Cli.Nombre1,''),1,@CM_LongNombres) ELSE '' END AS Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE '' END) AS RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	SUBSTRING(Dir.DireccionFormato2,1,@CM_LongDireccion) AS DireccionFormato2,
	'' NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	@TarifaIca AS TarifaIca,
	'' TarifaAplicada,
	'' NombreCompleto,
	'' CodCiudad,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	RIGHT('000' + CAST(COALESCE(Dir.CodMunicipio,0) AS VARCHAR),3) AS CodMunicipio,
	COALESCE(Dir.CodigoDian,0) AS CodigoPais,
	M_Conc.CodConcepto,
	U.IdConcepto,
	CASE WHEN U.IdCliente = 0 THEN COALESCE(@IdCliInf,0) ELSE U.IdCliente END AS IdCliente, 
	U.EsCuantiaMayor, 
	'' ConceptoPagoCuenta,
	0.0 AS ValorTotalDevRebDes,
	0.0 AS M1_Col1_PagoDeducible, 0.0 AS M1_Col2_PagoNoDeducible, 0.0 AS M1_Col3_IvaDeducible, 0.0 AS M1_Col4_IvaNoDeducible, 0.0 AS M1_Col5_ReteFteAplicada, 0.0 AS M1_Col6_ReteFteAsumida, 0.0 AS M1_Col7_ReteIvaRegComun, 0.0 AS M1_Col8_ReteIvaRegSimpC, 0.0 AS M1_Col9_ReteFteIvaNoDomic, 0.0 AS M1_Col10_ReteFteCREEAplicada, 0.0 AS M1_Col11_ReteFteCREEAsumida, 
	0.0 AS M3_Col1_ValorPagoAbono, 0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev, 0.0 AS M5_Col3_IvaMayorVCG,
	0.0 AS M6_Col1_ImptoGenerado, 0.0 AS M6_Col2_IvaRecup, 0.0 AS M6_Col3_ImptoConsumo,
	0.0 AS M7_Col1_IngBrutOpPropias, 0.0 AS M7_Col2_IngBrutConsorcio, 0.0 AS M7_Col3_IngContMandato, 0.0 AS M7_Col4_IngExploracion, 0.0 AS M7_Col5_IngPorFiducia, 0.0 AS M7_Col6_IngRecPorTerceros, 0.0 AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	ABS(ROUND(COALESCE(U.M9_Col1_Saldo, 0.0),0)) AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase, 
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		SUM(T.Col1) AS M9_Col1_Saldo
		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, M_Ppal.IdCliente, MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor, 
			SUM(CASE WHEN M_Ppal.NumCol = 1 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col1
			FROM         
			dbo.VMediosMag_Procesos_Principal AS M_Ppal INNER JOIN #ufnMediosMagneticos_CuantiasMenores_Saldos AS M_Cuantias ON M_Ppal.IdCliente = M_Cuantias.IdCliente 
            WHERE     
            M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) <= @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	WHERE 
    U.M9_Col1_Saldo <> 0
    RETURN

END


--Para el Formato 10
IF @IdFormato = 10
BEGIN 
	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	Cli.DVCliente, 
	SUBSTRING(Cli.Apellido1,1,@CM_LongNombres) AS Apellido1,
	SUBSTRING(Cli.Apellido2,1,@CM_LongNombres) AS Apellido2,
	CASE WHEN NOT(Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21) THEN SUBSTRING(COALESCE(Cli.Nombre1,''),1,@CM_LongNombres) ELSE '' END AS Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE '' END) AS RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	SUBSTRING(Dir.DireccionFormato2,1,@CM_LongDireccion) AS DireccionFormato2,
	'' NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	@TarifaIca AS TarifaIca,
	'' TarifaAplicada,
	'' NombreCompleto,
	'' CodCiudad,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	RIGHT('000' + CAST(COALESCE(Dir.CodMunicipio,0) AS VARCHAR),3) AS CodMunicipio,
	COALESCE(Dir.CodigoDian,0) AS CodigoPais,
	M_Conc.CodConcepto,
	U.IdConcepto,
	CASE WHEN U.IdCliente = 0 THEN COALESCE(@IdCliInf,0) ELSE U.IdCliente END AS IdCliente, 
	U.EsCuantiaMayor, 
	'' ConceptoPagoCuenta,
	0.0 AS ValorTotalDevRebDes,
	0.0 AS M1_Col1_PagoDeducible, 0.0 AS M1_Col2_PagoNoDeducible, 0.0 AS M1_Col3_IvaDeducible, 0.0 AS M1_Col4_IvaNoDeducible, 0.0 AS M1_Col5_ReteFteAplicada, 0.0 AS M1_Col6_ReteFteAsumida, 0.0 AS M1_Col7_ReteIvaRegComun, 0.0 AS M1_Col8_ReteIvaRegSimpC, 0.0 AS M1_Col9_ReteFteIvaNoDomic, 0.0 AS M1_Col10_ReteFteCREEAplicada, 0.0 AS M1_Col11_ReteFteCREEAsumida, 
	0.0 AS M3_Col1_ValorPagoAbono, 0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev, 0.0 AS M5_Col3_IvaMayorVCG,
	0.0 AS M6_Col1_ImptoGenerado, 0.0 AS M6_Col2_IvaRecup, 0.0 AS M6_Col3_ImptoConsumo,
	0.0 AS M7_Col1_IngBrutOpPropias, 0.0 AS M7_Col2_IngBrutConsorcio, 0.0 AS M7_Col3_IngContMandato, 0.0 AS M7_Col4_IngExploracion, 0.0 AS M7_Col5_IngPorFiducia, 0.0 AS M7_Col6_IngRecPorTerceros, 0.0 AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	0.0 AS M9_Col1_Saldo,
	ABS(ROUND(COALESCE(U.M10_Col1_ValorPatri, 0.0),0)) AS M10_Col1_ValorPatri,
	ABS(ROUND(COALESCE(U.M10_Col2_PorcParti, 0.0),0)) AS M10_Col2_PorcParti,
	ABS(ROUND(COALESCE(U.M10_Col3_PorcPartiDec, 0.0),0)) AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase, 
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		SUM(T.Col1) AS M10_Col1_ValorPatri,
		SUM(T.Col2)*100*10000 AS M10_Col2_PorcParti,
		4 AS M10_Col3_PorcPartiDec
		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, M_Ppal.IdCliente, 1 AS EsCuantiaMayor, 
			SUM(CASE WHEN M_Ppal.NumCol = 1 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col1,
			SUM(CASE WHEN M_Ppal.NumCol = 1 THEN M_Ppal.PagoTotal ELSE 0 END)/MAX(M_Total.SumTotal) AS Col2
			FROM         
			dbo.VMediosMag_Procesos_Principal AS M_Ppal INNER JOIN VMediosMag_Informes_AgrupPorConceptoTotal AS M_Total ON M_Ppal.IdVersion = M_Total.IdVersion 
            WHERE     
            M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) <= @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	WHERE 
    M10_Col1_ValorPatri + M10_Col2_PorcParti + M10_Col3_PorcPartiDec <> 0
    RETURN

END


--Para el Formato 12
IF @IdFormato = 12
BEGIN 
	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	Cli.DVCliente, 
	SUBSTRING(Cli.Apellido1,1,@CM_LongNombres) AS Apellido1,
	SUBSTRING(Cli.Apellido2,1,@CM_LongNombres) AS Apellido2,
	CASE WHEN NOT(Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21) THEN SUBSTRING(COALESCE(Cli.Nombre1,''),1,@CM_LongNombres) ELSE '' END AS Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE '' END) AS RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	SUBSTRING(Dir.DireccionFormato2,1,@CM_LongDireccion) AS DireccionFormato2,
	'' NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	@TarifaIca AS TarifaIca,
	'' TarifaAplicada,
	'' NombreCompleto,
	'' CodCiudad,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	RIGHT('000' + CAST(COALESCE(Dir.CodMunicipio,0) AS VARCHAR),3) AS CodMunicipio,
	COALESCE(Dir.CodigoDian,0) AS CodigoPais,
	M_Conc.CodConcepto,
	U.IdConcepto,
	CASE WHEN U.IdCliente = 0 THEN COALESCE(@IdCliInf,0) ELSE U.IdCliente END AS IdCliente, 
	U.EsCuantiaMayor, 
	'' ConceptoPagoCuenta,
	0.0 AS ValorTotalDevRebDes,
	0.0 AS M1_Col1_PagoDeducible, 0.0 AS M1_Col2_PagoNoDeducible, 0.0 AS M1_Col3_IvaDeducible, 0.0 AS M1_Col4_IvaNoDeducible, 0.0 AS M1_Col5_ReteFteAplicada, 0.0 AS M1_Col6_ReteFteAsumida, 0.0 AS M1_Col7_ReteIvaRegComun, 0.0 AS M1_Col8_ReteIvaRegSimpC, 0.0 AS M1_Col9_ReteFteIvaNoDomic, 0.0 AS M1_Col10_ReteFteCREEAplicada, 0.0 AS M1_Col11_ReteFteCREEAsumida, 
	0.0 AS M3_Col1_ValorPagoAbono, 0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev, 0.0 AS M5_Col3_IvaMayorVCG,
	0.0 AS M6_Col1_ImptoGenerado, 0.0 AS M6_Col2_IvaRecup, 0.0 AS M6_Col3_ImptoConsumo,
	0.0 AS M7_Col1_IngBrutOpPropias, 0.0 AS M7_Col2_IngBrutConsorcio, 0.0 AS M7_Col3_IngContMandato, 0.0 AS M7_Col4_IngExploracion, 0.0 AS M7_Col5_IngPorFiducia, 0.0 AS M7_Col6_IngRecPorTerceros, 0.0 AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	0.0 AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	ABS(ROUND(COALESCE(U.M12_Col1_Valor, 0.0),0)) AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase, 
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		SUM(T.Col1) AS M12_Col1_Valor
		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, M_Ppal.IdCliente, MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor, 
			SUM(CASE WHEN M_Ppal.NumCol = 1 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col1
			FROM         
			dbo.VMediosMag_Procesos_Principal AS M_Ppal INNER JOIN #ufnMediosMagneticos_CuantiasMenores_Saldos AS M_Cuantias ON M_Ppal.IdCliente = M_Cuantias.IdCliente 
            WHERE     
            M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) <= @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) AND
			M_Ppal.IdCliente IN (SELECT IdCliente FROM Clientes_Asociacion where IdTipo = 9)
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	WHERE 
    U.M12_Col1_Valor <> 0
    RETURN

END
/*
--Articulo 1
IF @IdFormato=15
BEGIN 
	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	Cli.DVCliente, 
	SUBSTRING(Cli.Apellido1,1,@CM_LongNombres) AS Apellido1,
	SUBSTRING(Cli.Apellido2,1,@CM_LongNombres) AS Apellido2,
	CASE WHEN NOT(Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21) THEN SUBSTRING(COALESCE(Cli.Nombre1,''),1,@CM_LongNombres) ELSE '' END AS Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE '' END) AS RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	SUBSTRING(Dir.DireccionFormato2,1,@CM_LongDireccion) AS DireccionFormato2,
	TipoIdent.NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	@TarifaIca AS TarifaIca,
	COALESCE (Cli.Nombre1, '') + COALESCE (' ' + Cli.Nombre2, '') + COALESCE (' ' +Cli.Apellido1, '')+ COALESCE (' ' + Cli.Apellido2, '') AS NombreCompleto,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	RIGHT('000' + CAST(COALESCE(Dir.CodMunicipio,0) AS VARCHAR),3) AS CodMunicipio,
	COALESCE(Dir.CodigoDian,0) AS CodigoPais,
	M_Conc.CodConcepto,
	U.IdConcepto,
	CASE WHEN U.IdCliente = 0 THEN COALESCE(@IdCliInf,0) ELSE U.IdCliente END AS IdCliente, 
	U.EsCuantiaMayor, 
	0.0 AS M1_Col1_PagoDeducible, 0.0 AS M1_Col2_PagoNoDeducible, 0.0 AS M1_Col3_IvaDeducible, 0.0 AS M1_Col4_IvaNoDeducible, 0.0 AS M1_Col5_ReteFteAplicada, 0.0 AS M1_Col6_ReteFteAsumida, 0.0 AS M1_Col7_ReteIvaRegComun, 0.0 AS M1_Col8_ReteIvaRegSimpC, 0.0 AS M1_Col9_ReteFteIvaNoDomic, 0.0 AS M1_Col10_ReteFteCREEAplicada, 0.0 AS M1_Col11_ReteFteCREEAsumida, 
	0.0 AS M3_Col1_ValorPagoAbono, 0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	U.M5_Col1_ValorImptoDesc AS M5_Col1_ValorImptoDesc, 
	U.M5_Col2_IvaResDev AS M5_Col2_IvaResDev,
	U.M6_Col1_ImptoGenerado AS M6_Col1_ImptoGenerado, 
	U.M6_Col2_IvaRecup AS M6_Col2_IvaRecup,
	U.M6_Col3_ImptoConsumo AS M6_Col3_ImptoConsumo,
	U.M7_Col1_IngBrutOpPropias AS M7_Col1_IngBrutOpPropias, 
	U.M7_Col2_IngBrutConsorcio AS M7_Col2_IngBrutConsorcio, 
	U.M7_Col3_IngContMandato AS M7_Col3_IngContMandato, 
	U.M7_Col4_IngExploracion AS M7_Col4_IngExploracion, 
	U.M7_Col5_IngPorFiducia AS M7_Col5_IngPorFiducia, 
	U.M7_Col6_IngRecPorTerceros AS M7_Col6_IngRecPorTerceros, 
	U.M7_Col7_IngTercDevRebDctos AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	U.M9_Col1_Saldo AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	U.M7_Col1_IngBrutOpPropias AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase,
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		ABS(ROUND(COALESCE(SUM(T.Col1), 0.0),0)) AS M5_Col1_ValorImptoDesc, 
		ABS(ROUND(COALESCE(SUM(T.Col2), 0.0),0)) AS M5_Col2_IvaResDev, 
		ABS(ROUND(COALESCE(SUM(T.Col1), 0.0),0)) AS M6_Col1_ImptoGenerado, 
		ABS(ROUND(COALESCE(SUM(T.Col2), 0.0),0)) AS M6_Col2_IvaRecup, 
		ABS(ROUND(COALESCE(SUM(T.Col3), 0.0),0)) AS M6_Col3_ImptoConsumo, 
		ABS(ROUND(COALESCE(SUM(T.Col1), 0.0),0)) AS M7_Col1_IngBrutOpPropias, 
		ABS(ROUND(COALESCE(SUM(T.Col2), 0.0),0)) AS M7_Col2_IngBrutConsorcio, 
		ABS(ROUND(COALESCE(SUM(T.Col3), 0.0),0)) AS M7_Col3_IngContMandato,
		ABS(ROUND(COALESCE(SUM(T.Col4), 0.0),0)) AS M7_Col4_IngExploracion, 
		ABS(ROUND(COALESCE(SUM(T.Col5), 0.0),0)) AS M7_Col5_IngPorFiducia, 
		ABS(ROUND(COALESCE(SUM(T.Col6), 0.0),0)) AS M7_Col6_IngRecPorTerceros, 
		ABS(ROUND(COALESCE(SUM(T.Col7), 0.0),0)) AS M7_Col7_IngTercDevRebDctos,
		ABS(ROUND(COALESCE(SUM(T.Col1), 0.0),0)) AS M9_Col1_Saldo

		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, M_Ppal.IdCliente, MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor, 
			SUM(CASE WHEN M_Ppal.NumCol = 1 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col1, SUM(CASE WHEN M_Ppal.NumCol = 2 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col2, SUM(CASE WHEN M_Ppal.NumCol = 3 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col3, SUM(CASE WHEN M_Ppal.NumCol = 4 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col4, SUM(CASE WHEN M_Ppal.NumCol = 5 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col5, SUM(CASE WHEN M_Ppal.NumCol = 6 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col6, SUM(CASE WHEN M_Ppal.NumCol = 7 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col7
			FROM         
			dbo.VMediosMag_Procesos_Principal AS M_Ppal INNER JOIN dbo.ufnMediosMagneticos_CuantiasMenores(@FechaIni, @FechaFin, @IdEmpresas, @IdDocsExc,@IdVersion) AS M_Cuantias ON M_Ppal.IdVersion = M_Cuantias.IdVersion AND M_Ppal.IdCliente = M_Cuantias.IdCliente 
            WHERE     
            M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) BETWEEN @FechaIniT AND @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	LEFT OUTER JOIN dbo.Clientes_TiposIdent AS TipoIdent ON TipoIdent.IdTipoIdent=cli.IdTipoIdent
	WHERE 
    U.M7_Col1_IngBrutOpPropias + U.M7_Col2_IngBrutConsorcio + U.M7_Col3_IngContMandato + U.M7_Col4_IngExploracion + U.M7_Col5_IngPorFiducia + U.M7_Col6_IngRecPorTerceros + U.M7_Col7_IngTercDevRebDctos <> 0
    RETURN

END

--Articulo 2
--//Para el Formato 16
IF @IdFormato = 16
BEGIN
	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	Cli.DVCliente, 
	SUBSTRING(Cli.Apellido1,1,@CM_LongNombres) AS Apellido1,
	SUBSTRING(Cli.Apellido2,1,@CM_LongNombres) AS Apellido2,
	CASE WHEN NOT(Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21) THEN SUBSTRING(COALESCE(Cli.Nombre1,''),1,@CM_LongNombres) ELSE '' END AS Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE '' END) AS RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	SUBSTRING(Dir.DireccionFormato2,1,@CM_LongDireccion) AS DireccionFormato2,
	TipoIdent.NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	@TarifaIca AS TarifaIca,
	COALESCE (Cli.Nombre1, '') + COALESCE (' ' + Cli.Nombre2, '') + COALESCE (' ' +Cli.Apellido1, '')+ COALESCE (' ' + Cli.Apellido2, '') AS NombreCompleto,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	RIGHT('000' + CAST(COALESCE(Dir.CodMunicipio,0) AS VARCHAR),3) AS CodMunicipio,
	COALESCE(Dir.CodigoDian,0) AS CodigoPais,
	M_Conc.CodConcepto,
	U.IdConcepto,
	CASE WHEN U.IdCliente = 0 THEN COALESCE(@IdCliInf,0) ELSE U.IdCliente END AS IdCliente, 
	U.EsCuantiaMayor, 
	0.0 AS M1_Col1_PagoDeducible, 0.0 AS M1_Col2_PagoNoDeducible, 0.0 AS M1_Col3_IvaDeducible, 0.0 AS M1_Col4_IvaNoDeducible, 0.0 AS M1_Col5_ReteFteAplicada, 0.0 AS M1_Col6_ReteFteAsumida, 0.0 AS M1_Col7_ReteIvaRegComun, 0.0 AS M1_Col8_ReteIvaRegSimpC, 0.0 AS M1_Col9_ReteFteIvaNoDomic, 0.0 AS M1_Col10_ReteFteCREEAplicada, 0.0 AS M1_Col11_ReteFteCREEAsumida, 
	0.0 AS M3_Col1_ValorPagoAbono, 0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	U.M5_Col1_ValorImptoDesc AS M5_Col1_ValorImptoDesc, 
	U.M5_Col2_IvaResDev AS M5_Col2_IvaResDev,
	U.M6_Col1_ImptoGenerado AS M6_Col1_ImptoGenerado, 
	U.M6_Col2_IvaRecup AS M6_Col2_IvaRecup,
	U.M6_Col3_ImptoConsumo AS M6_Col3_ImptoConsumo,
	U.M7_Col1_IngBrutOpPropias AS M7_Col1_IngBrutOpPropias, 
	U.M7_Col2_IngBrutConsorcio AS M7_Col2_IngBrutConsorcio, 
	U.M7_Col3_IngContMandato AS M7_Col3_IngContMandato, 
	U.M7_Col4_IngExploracion AS M7_Col4_IngExploracion, 
	U.M7_Col5_IngPorFiducia AS M7_Col5_IngPorFiducia, 
	U.M7_Col6_IngRecPorTerceros AS M7_Col6_IngRecPorTerceros, 
	U.M7_Col7_IngTercDevRebDctos AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	U.M9_Col1_Saldo AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	U.M7_Col1_IngBrutOpPropias AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase,
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		ABS(ROUND(COALESCE(SUM(T.Col1), 0.0),0)) AS M5_Col1_ValorImptoDesc, 
		ABS(ROUND(COALESCE(SUM(T.Col2), 0.0),0)) AS M5_Col2_IvaResDev, 
		ABS(ROUND(COALESCE(SUM(T.Col1), 0.0),0)) AS M6_Col1_ImptoGenerado, 
		ABS(ROUND(COALESCE(SUM(T.Col2), 0.0),0)) AS M6_Col2_IvaRecup, 
		ABS(ROUND(COALESCE(SUM(T.Col3), 0.0),0)) AS M6_Col3_ImptoConsumo, 
		ABS(ROUND(COALESCE(SUM(T.Col1), 0.0),0)) AS M7_Col1_IngBrutOpPropias, 
		ABS(ROUND(COALESCE(SUM(T.Col2), 0.0),0)) AS M7_Col2_IngBrutConsorcio, 
		ABS(ROUND(COALESCE(SUM(T.Col3), 0.0),0)) AS M7_Col3_IngContMandato,
		ABS(ROUND(COALESCE(SUM(T.Col4), 0.0),0)) AS M7_Col4_IngExploracion, 
		ABS(ROUND(COALESCE(SUM(T.Col5), 0.0),0)) AS M7_Col5_IngPorFiducia, 
		ABS(ROUND(COALESCE(SUM(T.Col6), 0.0),0)) AS M7_Col6_IngRecPorTerceros, 
		ABS(ROUND(COALESCE(SUM(T.Col7), 0.0),0)) AS M7_Col7_IngTercDevRebDctos,
		ABS(ROUND(COALESCE(SUM(T.Col1), 0.0),0)) AS M9_Col1_Saldo

		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, M_Ppal.IdCliente, MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor, 
			SUM(CASE WHEN M_Ppal.NumCol = 1 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col1, SUM(CASE WHEN M_Ppal.NumCol = 2 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col2, SUM(CASE WHEN M_Ppal.NumCol = 3 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col3, SUM(CASE WHEN M_Ppal.NumCol = 4 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col4, SUM(CASE WHEN M_Ppal.NumCol = 5 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col5, SUM(CASE WHEN M_Ppal.NumCol = 6 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col6, SUM(CASE WHEN M_Ppal.NumCol = 7 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col7
			FROM         
			dbo.VMediosMag_Procesos_Principal AS M_Ppal INNER JOIN dbo.ufnMediosMagneticos_CuantiasMenores(@FechaIni, @FechaFin, @IdEmpresas, @IdDocsExc,@IdVersion) AS M_Cuantias ON M_Ppal.IdVersion = M_Cuantias.IdVersion AND M_Ppal.IdCliente = M_Cuantias.IdCliente 
            WHERE     
            M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) BETWEEN @FechaIniT AND @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	LEFT OUTER JOIN dbo.Clientes_TiposIdent AS TipoIdent ON TipoIdent.IdTipoIdent=cli.IdTipoIdent
	WHERE 
    U.M7_Col1_IngBrutOpPropias + U.M7_Col2_IngBrutConsorcio + U.M7_Col3_IngContMandato + U.M7_Col4_IngExploracion + U.M7_Col5_IngPorFiducia + U.M7_Col6_IngRecPorTerceros + U.M7_Col7_IngTercDevRebDctos <> 0
    RETURN

END

 
--//Para el articulo 4
--//
IF @IdFormato = 18
BEGIN
	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	Cli.DVCliente, 
	SUBSTRING(Cli.Apellido1,1,@CM_LongNombres) AS Apellido1,
	SUBSTRING(Cli.Apellido2,1,@CM_LongNombres) AS Apellido2,
	CASE WHEN NOT(Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21) THEN SUBSTRING(COALESCE(Cli.Nombre1,''),1,@CM_LongNombres) ELSE '' END AS Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE '' END) AS RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	SUBSTRING(Dir.DireccionFormato2,1,@CM_LongDireccion) AS DireccionFormato2,
	TipoIdent.NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	@TarifaIca AS TarifaIca,
	COALESCE (Cli.Nombre1, '') + COALESCE (' ' + Cli.Nombre2, '') + COALESCE (' ' +Cli.Apellido1, '')+ COALESCE (' ' + Cli.Apellido2, '') AS NombreCompleto,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	RIGHT('000' + CAST(COALESCE(Dir.CodMunicipio,0) AS VARCHAR),3) AS CodMunicipio,
	COALESCE(Dir.CodigoDian,0) AS CodigoPais,
	M_Conc.CodConcepto,
	U.IdConcepto,
	CASE WHEN U.IdCliente = 0 THEN COALESCE(@IdCliInf,0) ELSE U.IdCliente END AS IdCliente, 
	U.EsCuantiaMayor, 
	0.0 AS M1_Col1_PagoDeducible, 0.0 AS M1_Col2_PagoNoDeducible, 0.0 AS M1_Col3_IvaDeducible, 0.0 AS M1_Col4_IvaNoDeducible, 0.0 AS M1_Col5_ReteFteAplicada, 0.0 AS M1_Col6_ReteFteAsumida, 0.0 AS M1_Col7_ReteIvaRegComun, 0.0 AS M1_Col8_ReteIvaRegSimpC, 0.0 AS M1_Col9_ReteFteIvaNoDomic, 0.0 AS M1_Col10_ReteFteCREEAplicada, 0.0 AS M1_Col11_ReteFteCREEAsumida, 
	0.0 AS M3_Col1_ValorPagoAbono, 
	0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev,
	0.0 AS M6_Col1_ImptoGenerado, 0.0 AS M6_Col2_IvaRecup, 0.0 AS M6_Col3_ImptoConsumo,
	0.0 AS M7_Col1_IngBrutOpPropias, 0.0 AS M7_Col2_IngBrutConsorcio, 0.0 AS M7_Col3_IngContMandato,0.0 AS M7_Col4_IngExploracion, 0.0 AS M7_Col5_IngPorFiducia, 0.0 AS M7_Col6_IngRecPorTerceros, 0.0 AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	0.0 AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	ABS(ROUND(COALESCE(U.M18_Col1_ValorBase, 0.0),0)) AS M18_Col1_ValorBase, 
	ABS(ROUND(COALESCE(U.M18_Col2_Retencion, 0.0),0)) AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase,
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		SUM(T.PagoDeducible) AS M18_Col1_ValorBase, 
		SUM(T.ReteFteAplicada) AS M18_Col2_Retencion
		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, 
			M_Ppal.IdCliente, 
			SUM(M_Ppal.RetencionBase) AS PagoDeducible, 
			SUM(COALESCE(M_Ppal.SumRetReal,M_Ppal.PagoDeducible)) AS ReteFteAplicada, 
			MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor
			FROM 
			dbo.VMediosMag_Procesos_Principal AS M_Ppal 
			INNER JOIN dbo.ufnMediosMagneticos_CuantiasMenores(@FechaIni, @FechaFin, @IdEmpresas, @IdDocsExc,@IdVersion) AS M_Cuantias ON M_Ppal.IdVersion = M_Cuantias.IdVersion AND M_Ppal.IdCliente = M_Cuantias.IdCliente 
			--INNER JOIN dbo.VMediosMag_Procesos_ReteFuente_Fin AS M_ReteFte ON M_Ppal.IdCliente = M_ReteFte.IdCliente AND M_Ppal.IdEncab = M_ReteFte.IdEncab AND M_Ppal.IdAgrup = M_ReteFte.IdAgrup 
			WHERE
			M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) BETWEEN @FechaIniT AND @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
		HAVING SUM(T.ReteFteAplicada) <> 0

	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	LEFT OUTER JOIN dbo.Clientes_TiposIdent AS TipoIdent ON TipoIdent.IdTipoIdent=cli.IdTipoIdent
	RETURN
END
--//

--Articulo 6
IF @IdFormato = 19
BEGIN 
	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	Cli.DVCliente, 
	SUBSTRING(Cli.Apellido1,1,@CM_LongNombres) AS Apellido1,
	SUBSTRING(Cli.Apellido2,1,@CM_LongNombres) AS Apellido2,
	CASE WHEN NOT(Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21) THEN SUBSTRING(COALESCE(Cli.Nombre1,''),1,@CM_LongNombres) ELSE '' END AS Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE '' END) AS RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	SUBSTRING(Dir.DireccionFormato2,1,@CM_LongDireccion) AS DireccionFormato2,
	TipoIdent.NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	@TarifaIca AS TarifaIca,
	COALESCE (Cli.Nombre1, '') + COALESCE (' ' + Cli.Nombre2, '') + COALESCE (' ' +Cli.Apellido1, '')+ COALESCE (' ' + Cli.Apellido2, '') AS NombreCompleto,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	RIGHT('000' + CAST(COALESCE(Dir.CodMunicipio,0) AS VARCHAR),3) AS CodMunicipio,
	COALESCE(Dir.CodigoDian,0) AS CodigoPais,
	M_Conc.CodConcepto,
	U.IdConcepto,
	CASE WHEN U.IdCliente = 0 THEN COALESCE(@IdCliInf,0) ELSE U.IdCliente END AS IdCliente, 
	U.EsCuantiaMayor, 
	0.0 AS M1_Col1_PagoDeducible, 0.0 AS M1_Col2_PagoNoDeducible, 0.0 AS M1_Col3_IvaDeducible, 0.0 AS M1_Col4_IvaNoDeducible, 0.0 AS M1_Col5_ReteFteAplicada, 0.0 AS M1_Col6_ReteFteAsumida, 0.0 AS M1_Col7_ReteIvaRegComun, 0.0 AS M1_Col8_ReteIvaRegSimpC, 0.0 AS M1_Col9_ReteFteIvaNoDomic, 0.0 AS M1_Col10_ReteFteCREEAplicada, 0.0 AS M1_Col11_ReteFteCREEAsumida, 
	0.0 AS M3_Col1_ValorPagoAbono, 
	0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev,
	0.0 AS M6_Col1_ImptoGenerado, 0.0 AS M6_Col2_IvaRecup, 0.0 AS M6_Col3_ImptoConsumo,
	0.0 AS M7_Col1_IngBrutOpPropias, 0.0 AS M7_Col2_IngBrutConsorcio, 0.0 AS M7_Col3_IngContMandato,0.0 AS M7_Col4_IngExploracion, 0.0 AS M7_Col5_IngPorFiducia, 0.0 AS M7_Col6_IngRecPorTerceros, 0.0 AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	0.0 AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	ABS(ROUND(COALESCE(U.M19_Col1_ValorBase, 0.0),0)) AS M19_Col1_ValorBase, 
	ABS(ROUND(COALESCE(U.M19_Col2_Retencion, 0.0),0)) AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		SUM(T.PagoDeducible) AS M19_Col1_ValorBase, 
		SUM(T.ReteFteAplicada) AS M19_Col2_Retencion
		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, 
			M_Ppal.IdCliente, 
			SUM(M_Ppal.RetencionBase) AS PagoDeducible, 
			SUM(COALESCE(M_Ppal.SumRetReal,M_Ppal.PagoDeducible)) AS ReteFteAplicada, 
			MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor
			FROM 
			dbo.VMediosMag_Procesos_Principal AS M_Ppal 
			INNER JOIN dbo.ufnMediosMagneticos_CuantiasMenores(@FechaIni, @FechaFin, @IdEmpresas, @IdDocsExc,@IdVersion) AS M_Cuantias ON M_Ppal.IdVersion = M_Cuantias.IdVersion AND M_Ppal.IdCliente = M_Cuantias.IdCliente 
			--INNER JOIN dbo.VMediosMag_Procesos_ReteFuente_Fin AS M_ReteFte ON M_Ppal.IdCliente = M_ReteFte.IdCliente AND M_Ppal.IdEncab = M_ReteFte.IdEncab AND M_Ppal.IdAgrup = M_ReteFte.IdAgrup 
			WHERE
			M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) BETWEEN @FechaIniT AND @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
		HAVING SUM(T.ReteFteAplicada) <> 0

	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	LEFT OUTER JOIN dbo.Clientes_TiposIdent AS TipoIdent ON TipoIdent.IdTipoIdent=cli.IdTipoIdent
	RETURN
	
END
*/


--MEDIOS MAGNÉTICOS
--Artículo 2 (Concep. 1001 - v10)

IF @IdFormato = 19
BEGIN
	SELECT 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_TipoIdent ELSE Cli.IdTipoIdent END AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	'' AS DVCliente,
	'' Apellido1,
	'' Apellido2,
	'' Nombre1,
	'' Nombre2,
	'' RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS  DireccionFormato1,
	'' DireccionFormato2,
	TipoIdent.NomTipoIdent AS  NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	0.0 AS TarifaIca,
	'' TarifaAplicada,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE (COALESCE (Cli.Nombre1, '') + COALESCE (' ' + Cli.Nombre2, '') + COALESCE (' ' +Cli.Apellido1, '')+ COALESCE (' ' + Cli.Apellido2, ''))  END) AS NombreCompleto,
	RIGHT('00000' + CAST(COALESCE(Dir.IdCiudad,0) AS VARCHAR),5) AS CodCiudad,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	'' CodMunicipio,
	0 AS CodigoPais,
	'' CodConcepto,
	0 AS IdConcepto,
	0 AS IdCliente, 
	0 AS  EsCuantiaMayor,	 
	CAST(U.IdConPag AS VARCHAR) AS ConceptoPagoCuenta,
	0.0 AS ValorTotalDevRebDes,
	ABS(ROUND(COALESCE(U.M1_Col1_PagoDeducible, 0.0),0)) AS M1_Col1_PagoDeducible,
	0.0 AS M1_Col2_PagoNoDeducible,
	0.0 AS M1_Col3_IvaDeducible,
	0.0 AS M1_Col4_IvaNoDeducible,
	0.0 AS M1_Col5_ReteFteAplicada,
	0.0 AS M1_Col6_ReteFteAsumida,
	0.0 AS M1_Col7_ReteIvaRegComun,
	0.0 AS M1_Col9_ReteFteIvaNoDomic,
    0.0 AS M1_Col8_ReteIvaRegSimpC,  
	0.0 AS M1_Col10_ReteFteCREEAplicada, 
	0.0 AS M1_Col11_ReteFteCREEAsumida, 
	0.0 AS M3_Col1_ValorPagoAbono, 0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev, 0.0 AS M5_Col3_IvaMayorVCG,
	0.0 AS M6_Col1_ImptoGenerado, 0.0 AS M6_Col2_IvaRecup, 0.0 AS M6_Col3_ImptoConsumo,
	0.0 AS M7_Col1_IngBrutOpPropias, 0.0 AS M7_Col2_IngBrutConsorcio, 0.0 AS M7_Col3_IngContMandato,0.0 AS M7_Col4_IngExploracion, 0.0 AS M7_Col5_IngPorFiducia, 0.0 AS M7_Col6_IngRecPorTerceros, 0.0 AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	0.0 AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase, 
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConPag,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		SUM(T.PagoDeducible) AS M1_Col1_PagoDeducible
		FROM
		(
			SELECT 
			M_Ppal.IdConPag, 
			M_Ppal.IdCliente, 
			SUM(M_Ppal.PagoDeducible) AS PagoDeducible, 
			MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor
			FROM 
			dbo.VMediosMag_Procesos_Principal AS M_Ppal INNER JOIN dbo.ufnMediosMagneticos_CuantiasMenores(@FechaIni, @FechaFin, @IdEmpresas, @IdDocsExc,@IdVersion) AS M_Cuantias ON M_Ppal.IdVersion = M_Cuantias.IdVersion AND M_Ppal.IdCliente = M_Cuantias.IdCliente LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteFuenteCREE_Fin AS M_ReteFteCREE ON M_Ppal.IdEncab = M_ReteFteCREE.IdEncab AND M_Ppal.IdAgrup = M_ReteFteCREE.IdAgrup AND M_Ppal.IdCliente = M_ReteFteCREE.IdCliente LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteFteIvaNoDomic_Fin AS M_ReteFteNoDom ON M_Ppal.IdCliente = M_ReteFteNoDom.IdCliente AND M_Ppal.IdEncab = M_ReteFteNoDom.IdEncab AND M_Ppal.IdAgrup = M_ReteFteNoDom.IdAgrup LEFT OUTER JOIN
                        dbo.VMediosMag_Procesos_ReteFuente_Fin AS M_ReteFte ON M_Ppal.IdCliente = M_ReteFte.IdCliente AND M_Ppal.IdEncab = M_ReteFte.IdEncab AND M_Ppal.IdAgrup = M_ReteFte.IdAgrup LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteIvaRegComun_Fin AS M_ReteIvaRCom ON M_Ppal.IdCliente = M_ReteIvaRCom.IdCliente AND M_Ppal.IdEncab = M_ReteIvaRCom.IdEncab AND M_Ppal.IdAgrup = M_ReteIvaRCom.IdAgrup LEFT OUTER JOIN dbo.VMediosMag_Procesos_IvaMayorValor_Fin AS M_Ivas ON M_Ppal.IdCliente = M_Ivas.IdCliente AND M_Ppal.IdEncab = M_Ivas.IdEncab AND M_Ppal.IdAgrup = M_Ivas.IdAgrup AND M_Ppal.IdVersion = M_Ivas.IdVersion LEFT OUTER JOIN dbo.VMediosMag_Procesos_ReteIvaRegSimpC_Fin AS M_ReteIvaRSC ON M_Ppal.IdCliente = M_ReteIvaRSC.IdCliente AND M_Ppal.IdEncab = M_ReteIvaRSC.IdEncab AND M_Ppal.IdAgrup = M_ReteIvaRSC.IdAgrup
			WHERE
			M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) BETWEEN @FechaIniT AND @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConPag, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConPag,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
	) U
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
    LEFT OUTER JOIN dbo.Clientes_TiposIdent AS TipoIdent ON TipoIdent.IdTipoIdent=cli.IdTipoIdent
	RETURN
END


--Artículo 3  (1007 - 7)		
IF @IdFormato = 20 
BEGIN 
 

 ;WITH tempMediosCont(IdCuenta,IdEncab,IdAgrup,IdCliente,Fecha,IdEmpresa,IdDocumento,PagoDeducible,PagoNoDeducible,PagoTotal,RetencionBase,SumRetReal)
 AS
 (
  SELECT     IdCuenta,Enc_C.IdEncab,ISNULL(Enc_C.IdEncabContAgrup, 0) AS IdAgrup, Enc_C.IdCliente,
     MAX(Enc.Fecha) AS Fecha, MAX(Enc.IdEmpresa) AS IdEmpresa, MAX(Enc.IdDocumento) AS IdDocumento, 
                         SUM(CASE WHEN Enc_C.EsDeducible = 0 THEN Enc_C.Debito - Enc_C.Credito ELSE 0 END) AS PagoDeducible, SUM(CASE WHEN Enc_C.EsDeducible = 1 THEN Enc_C.Debito - Enc_C.Credito ELSE 0 END) AS PagoNoDeducible, 
                         SUM(Enc_C.Debito - Enc_C.Credito) AS PagoTotal, SUM(CASE WHEN ISNULL(Enc_C.RetencionPorc, 0) > 0 THEN ABS(Enc_C.Debito - Enc_C.Credito) * 100 / Enc_C.RetencionPorc ELSE 0 END) AS RetencionBase, 
                         SUM(ISNULL(Enc_C.RetFuenteReal, 0)) AS SumRetReal
FROM    dbo.Encabezados_Cont AS Enc_C INNER JOIN
         dbo.Encabezados AS Enc ON Enc_C.IdEncab = Enc.IdEncab
  WHERE     
  (Enc.EsAnulado = 0) AND
			DATEADD(dd,DATEDIFF(dd,0,Enc.Fecha),0) BETWEEN @FechaIniT AND @FechaFinT AND
			Enc.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			Enc.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			Enc_C.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
GROUP BY IdCuenta, Enc_C.IdEncab, Enc_C.IdCliente, ISNULL(Enc_C.IdEncabContAgrup, 0)

 ),

 tempMediosPrincipal(IdVersion, IdEncab, IdAgrup, IdCliente, IdClienteAgrupMM, IdConcepto, NumCol, IdAgrupMM,  CodConcepto, PagoDeducible, PagoNoDeducible, PagoTotal, RetencionBase, SumRetReal)
 AS
 (
 SELECT        MM_Cuentas.IdVersion, Enc_C.IdEncab,IdAgrup, Enc_C.IdCliente, MM_Cuentas.IdCliente AS IdClienteAgrupMM, MM_Cuentas.IdConcepto, MM_Cuentas.NumCol, MM_Cuentas.IdAgrupMM, MAX(MM_Conc.CodConcepto) AS CodConcepto,
                         SUM(PagoDeducible), SUM(PagoNoDeducible), 
                         SUM(PagoTotal), SUM(RetencionBase), 
                         SUM(SumRetReal)SumRetReal
FROM            dbo.MediosMag_CuentasFormato AS MM_Cuentas INNER JOIN
                         tempMediosCont AS Enc_C ON MM_Cuentas.IdCuenta = Enc_C.IdCuenta  INNER JOIN
                         dbo.MediosMag_Conceptos AS MM_Conc ON MM_Cuentas.IdConcepto = MM_Conc.IdConcepto
    
GROUP BY MM_Cuentas.IdConcepto, Enc_C.IdEncab, Enc_C.IdCliente,IdAgrup, MM_Cuentas.IdVersion, MM_Cuentas.NumCol, MM_Cuentas.IdCliente, MM_Cuentas.IdAgrupMM
)

,

tempDir(Idcliente,Direccion,DireccionFormato1,DireccionFormato2,Telefonos,EMail,CodCiudad,CodDpto,CodMunicipio,CodigoPais)
AS
(
SELECT
tempDirPri.Idcliente,      
	SUBSTRING(Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(DireccionFormato1,1,@CM_LongDireccion) AS DireccionFormato1,
	'' DireccionFormato2,
	[Telefonos] AS Telefonos,
	EMail AS EMail,
	RIGHT('00000' + CAST(ISNULL(dbo.Ciudades.IdCiudad,0) AS VARCHAR),5) AS CodCiudad,
	RIGHT('00' + CAST(ISNULL( dbo.Departamentos.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	'' CodMunicipio,
	0 AS CodigoPais
FROM          
						
                         VClientesDir_01 AS tempDirPri INNER JOIN
                         dbo.Clientes_Direcciones ON tempDirPri.IdClienteDirMax = dbo.Clientes_Direcciones.IdClienteDir  LEFT JOIN
                         dbo.Clientes_TiposDireccion ON dbo.Clientes_Direcciones.IdTipoDir = dbo.Clientes_TiposDireccion.IdTipoDir LEFT JOIN
						 dbo.Ciudades  ON dbo.Ciudades.IdCiudad = dbo.Clientes_Direcciones.IdCiudad  LEFT JOIN
						 dbo.Departamentos ON dbo.Departamentos.IdDpto = dbo.Ciudades.IdDpto  LEFT JOIN
						 dbo.Paises ON dbo.Departamentos.IdPais = dbo.Paises.IdPais 
)

	SELECT 
	0 AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	'' DVCliente,
	'' Apellido1,
	'' Apellido2,
	'' Nombre1,
	SUBSTRING(Cli.Nombre2,1,@CM_LongNombres) AS Nombre2,
	'' RazonSocial,
	Direccion,
	DireccionFormato1,
	DireccionFormato2,
	TipoIdent.NomTipoIdent AS NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	0.0 AS TarifaIca,
	'' TarifaAplicada,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE (COALESCE (Cli.Nombre1, '') + COALESCE (' ' + Cli.Nombre2, '') + COALESCE (' ' +Cli.Apellido1, '')+ COALESCE (' ' + Cli.Apellido2, ''))  END) AS NombreCompleto,
	ISNULL(CodCiudad,0)AS CodCiudad,
	ISNULL(CodDpto,0)AS CodDpto,
	'' CodMunicipio,
	0 AS CodigoPais,
	'' CodConcepto,
	0 AS IdConcepto,
	0 AS IdCliente, 
	0 AS  EsCuantiaMayor,
	'' ConceptoPagoCuenta,
	0.0 AS ValorTotalDevRebDes,
	0.0 AS M1_Col1_PagoDeducible, 0.0 AS M1_Col2_PagoNoDeducible, 0.0 AS M1_Col3_IvaDeducible, 0.0 AS M1_Col4_IvaNoDeducible, 0.0 AS M1_Col5_ReteFteAplicada, 0.0 AS M1_Col6_ReteFteAsumida, 0.0 AS M1_Col7_ReteIvaRegComun, 0.0 AS M1_Col8_ReteIvaRegSimpC, 0.0 AS M1_Col9_ReteFteIvaNoDomic, 0.0 AS M1_Col10_ReteFteCREEAplicada, 0.0 AS M1_Col11_ReteFteCREEAsumida, 
	0.0 AS M3_Col1_ValorPagoAbono, 0.0 AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev, 0.0 AS M5_Col3_IvaMayorVCG,
	0.0 AS M6_Col1_ImptoGenerado, 
	0.0 AS M6_Col2_IvaRecup,
	0.0 AS M6_Col3_ImptoConsumo,
	U.M7_Col1_IngBrutOpPropias AS M7_Col1_IngBrutOpPropias, 
	0.0 AS M7_Col2_IngBrutConsorcio, 
	0.0 AS M7_Col3_IngContMandato, 
	0.0 AS M7_Col4_IngExploracion,
	0.0 AS M7_Col5_IngPorFiducia,
	0.0 AS M7_Col6_IngRecPorTerceros,
	U.M7_Col7_IngTercDevRebDctos AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	0.0 AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase, 
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		ABS(ROUND(ISNULL(SUM(T.Col1), 0.0),0)) AS M7_Col1_IngBrutOpPropias, 
		ABS(ROUND(ISNULL(SUM(T.Col2), 0.0),0)) AS M7_Col2_IngBrutConsorcio, 
		ABS(ROUND(ISNULL(SUM(T.Col3), 0.0),0)) AS M7_Col3_IngContMandato,
		ABS(ROUND(ISNULL(SUM(T.Col4), 0.0),0)) AS M7_Col4_IngExploracion, 
		ABS(ROUND(ISNULL(SUM(T.Col5), 0.0),0)) AS M7_Col5_IngPorFiducia, 
		ABS(ROUND(ISNULL(SUM(T.Col6), 0.0),0)) AS M7_Col6_IngRecPorTerceros, 
		ABS(ROUND(ISNULL(SUM(T.Col7), 0.0),0)) AS M7_Col7_IngTercDevRebDctos,
		ABS(ROUND(ISNULL(SUM(T.Col1), 0.0),0)) AS M9_Col1_Saldo

		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, M_Ppal.IdCliente, MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor, 
			SUM(CASE WHEN M_Ppal.NumCol = 1 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col1, SUM(CASE WHEN M_Ppal.NumCol = 2 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col2, SUM(CASE WHEN M_Ppal.NumCol = 3 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col3, SUM(CASE WHEN M_Ppal.NumCol = 4 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col4, SUM(CASE WHEN M_Ppal.NumCol = 5 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col5, SUM(CASE WHEN M_Ppal.NumCol = 6 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col6, SUM(CASE WHEN M_Ppal.NumCol = 7 THEN M_Ppal.PagoTotal ELSE 0 END) AS Col7
			FROM         
			tempMediosPrincipal AS M_Ppal INNER JOIN dbo.ufnMediosMagneticos_CuantiasMenores(@FechaIni, @FechaFin, @IdEmpresas, @IdDocsExc,@IdVersion) AS M_Cuantias ON M_Ppal.IdVersion = M_Cuantias.IdVersion AND M_Ppal.IdCliente = M_Cuantias.IdCliente 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT JOIN tempDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	LEFT OUTER JOIN dbo.Clientes_TiposIdent AS TipoIdent ON TipoIdent.IdTipoIdent=cli.IdTipoIdent
	WHERE 
    U.M7_Col1_IngBrutOpPropias + U.M7_Col2_IngBrutConsorcio + U.M7_Col3_IngContMandato + U.M7_Col4_IngExploracion + U.M7_Col5_IngPorFiducia + U.M7_Col6_IngRecPorTerceros + U.M7_Col7_IngTercDevRebDctos <> 0
   OPTION(RECOMPILE)
    RETURN

END


--Artículo 4 (1003 - 3)
IF @IdFormato = 21
BEGIN
	SELECT 
	0 AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	'' DVCliente, 
	'' Apellido1,
	'' Apellido2,
	'' Nombre1,
	'' Nombre2,
	'' RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS  DireccionFormato1,
	'' DireccionFormato2,
	TipoIdent.NomTipoIdent AS NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	0.0 AS TarifaIca,
	'966' AS TarifaAplicada,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE (COALESCE (Cli.Nombre1, '') + COALESCE (' ' + Cli.Nombre2, '') + COALESCE (' ' +Cli.Apellido1, '')+ COALESCE (' ' + Cli.Apellido2, ''))  END) AS NombreCompleto,
	RIGHT('00000' + CAST(COALESCE(Dir.IdCiudad,0) AS VARCHAR),5) AS CodCiudad,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	'' CodMunicipio,
	0 AS  CodigoPais,
	'' CodConcepto,
	0 AS IdConcepto,
	0 AS IdCliente, 
	0 AS  EsCuantiaMayor, 
	'' AS ConceptoPagoCuenta, 
	0.0 AS ValorTotalDevRebDes, 
	0.0 AS M1_Col1_PagoDeducible, 0.0 AS M1_Col2_PagoNoDeducible, 0.0 AS M1_Col3_IvaDeducible, 0.0 AS M1_Col4_IvaNoDeducible, 0.0 AS M1_Col5_ReteFteAplicada, 0.0 AS M1_Col6_ReteFteAsumida, 0.0 AS M1_Col7_ReteIvaRegComun, 0.0 AS M1_Col8_ReteIvaRegSimpC, 0.0 AS M1_Col9_ReteFteIvaNoDomic, 0.0 AS M1_Col10_ReteFteCREEAplicada, 0.0 AS M1_Col11_ReteFteCREEAsumida, 
	ABS(ROUND(COALESCE(U.M3_Col1_ValorPagoAbono, 0.0),0)) AS M3_Col1_ValorPagoAbono, 
	ABS(ROUND(COALESCE(U.M3_Col2_Retencion, 0.0),0)) AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev, 0.0 AS M5_Col3_IvaMayorVCG,
	0.0 AS M6_Col1_ImptoGenerado, 0.0 AS M6_Col2_IvaRecup, 0.0 AS M6_Col3_ImptoConsumo,
	0.0 AS M7_Col1_IngBrutOpPropias, 0.0 AS M7_Col2_IngBrutConsorcio, 0.0 AS M7_Col3_IngContMandato,0.0 AS M7_Col4_IngExploracion, 0.0 AS M7_Col5_IngPorFiducia, 0.0 AS M7_Col6_IngRecPorTerceros, 0.0 AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	0.0 AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase, 
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		SUM(T.PagoDeducible) AS M3_Col1_ValorPagoAbono, 
		SUM(T.ReteFteAplicada) AS M3_Col2_Retencion
		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, 
			M_Ppal.IdCliente, 
			SUM(M_Ppal.RetencionBase) AS PagoDeducible, 
			SUM(COALESCE(M_Ppal.SumRetReal,M_Ppal.PagoDeducible)) AS ReteFteAplicada, 
			MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor
			FROM 
			dbo.VMediosMag_Procesos_Principal AS M_Ppal 
			INNER JOIN dbo.ufnMediosMagneticos_CuantiasMenores(@FechaIni, @FechaFin, @IdEmpresas, @IdDocsExc,@IdVersion) AS M_Cuantias ON M_Ppal.IdVersion = M_Cuantias.IdVersion AND M_Ppal.IdCliente = M_Cuantias.IdCliente 
			--INNER JOIN dbo.VMediosMag_Procesos_ReteFuente_Fin AS M_ReteFte ON M_Ppal.IdCliente = M_ReteFte.IdCliente AND M_Ppal.IdEncab = M_ReteFte.IdEncab AND M_Ppal.IdAgrup = M_ReteFte.IdAgrup 
			WHERE
			M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) BETWEEN @FechaIniT AND @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
		HAVING SUM(T.ReteFteAplicada) <> 0

	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	LEFT OUTER JOIN dbo.Clientes_TiposIdent AS TipoIdent ON TipoIdent.IdTipoIdent=cli.IdTipoIdent
	RETURN
END


--Artículo 6 (1003- 3)

IF @IdFormato = 23 
BEGIN
	SELECT 
	0 AS IdTipoIdent, 
	CASE WHEN (@Detallar = 0 AND U.EsCuantiaMayor = 0) THEN @CM_Identif ELSE Cli.Identificacion END AS Identificacion, 
	'' DVCliente, 
	'' Apellido1,
	'' Apellido2,
	'' Nombre1,
	'' Nombre2,
	'' RazonSocial,
	SUBSTRING(Dir.Direccion,1,@CM_LongDireccion) AS Direccion,
	SUBSTRING(Dir.DireccionFormato1,1,@CM_LongDireccion) AS  DireccionFormato1,
	'' DireccionFormato2,
	TipoIdent.NomTipoIdent AS NomTipoIdent,
	dir.[Telefonos] AS Telefonos,
	dir.EMail AS EMail,
	@Vigencia AS Vigencia,
	0.0 AS TarifaIca,
	'966' AS TarifaAplicada,
	COALESCE(CASE WHEN U.EsCuantiaMayor = 0 AND @Detallar = 0 THEN @CM_RazonSocial ELSE NULL END, CASE WHEN Cli.IdTipoIdent = 31 OR Cli.IdTipoIdent = 42 OR Cli.IdTipoIdent = 21 THEN SUBSTRING(Cli.Nombre1,1,@CM_LongRazonSocial) ELSE (COALESCE (Cli.Nombre1, '') + COALESCE (' ' + Cli.Nombre2, '') + COALESCE (' ' +Cli.Apellido1, '')+ COALESCE (' ' + Cli.Apellido2, ''))  END) AS NombreCompleto,
	RIGHT('00000' + CAST(COALESCE(Dir.IdCiudad,0) AS VARCHAR),5) AS CodCiudad,
	RIGHT('00' + CAST(COALESCE(Dir.IdDpto,0) AS VARCHAR),2) AS CodDpto,
	'' CodMunicipio,
	0 AS  CodigoPais,
	'' CodConcepto,
	0 AS IdConcepto,
	0 AS IdCliente, 
	0 AS  EsCuantiaMayor, 
	'' AS ConceptoPagoCuenta, 
	0.0 AS ValorTotalDevRebDes, 
	0.0 AS M1_Col1_PagoDeducible, 0.0 AS M1_Col2_PagoNoDeducible, 0.0 AS M1_Col3_IvaDeducible, 0.0 AS M1_Col4_IvaNoDeducible, 0.0 AS M1_Col5_ReteFteAplicada, 0.0 AS M1_Col6_ReteFteAsumida, 0.0 AS M1_Col7_ReteIvaRegComun, 0.0 AS M1_Col8_ReteIvaRegSimpC, 0.0 AS M1_Col9_ReteFteIvaNoDomic, 0.0 AS M1_Col10_ReteFteCREEAplicada, 0.0 AS M1_Col11_ReteFteCREEAsumida, 
	ABS(ROUND(COALESCE(U.M3_Col1_ValorPagoAbono, 0.0),0)) AS M3_Col1_ValorPagoAbono, 
	ABS(ROUND(COALESCE(U.M3_Col2_Retencion, 0.0),0)) AS M3_Col2_Retencion, 
	0.0 AS M4_Col1_ValorAcumPago, 0.0 AS M4_Col2_ValorDctoTrib,
	0.0 AS M5_Col1_ValorImptoDesc, 0.0 AS M5_Col2_IvaResDev, 0.0 AS M5_Col3_IvaMayorVCG,
	0.0 AS M6_Col1_ImptoGenerado, 0.0 AS M6_Col2_IvaRecup, 0.0 AS M6_Col3_ImptoConsumo,
	0.0 AS M7_Col1_IngBrutOpPropias, 0.0 AS M7_Col2_IngBrutConsorcio, 0.0 AS M7_Col3_IngContMandato,0.0 AS M7_Col4_IngExploracion, 0.0 AS M7_Col5_IngPorFiducia, 0.0 AS M7_Col6_IngRecPorTerceros, 0.0 AS M7_Col7_IngTercDevRebDctos,
	0.0 AS M8_Col1_Saldo,
	0.0 AS M9_Col1_Saldo,
	0.0 AS M10_Col1_ValorPatri, 0.0 AS M10_Col2_PorcParti, 0.0 AS M10_Col3_PorcPartiDec,
	0.0 AS M11_Col1_Saldo,
	0.0 AS M12_Col1_Valor,
	0.0 AS M15_Col1_Valor,
	0.0 AS M16_Col1_ValorBruto, 
	0.0 AS M18_Col1_ValorBase, 
	0.0 AS M18_Col2_Retencion,
	0.0 AS M19_Col1_ValorBase, 
	0.0 AS M19_Col2_Retencion
	FROM
	(
		SELECT 
		T.IdConcepto,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END AS IdCliente, 
		T.EsCuantiaMayor, 
		SUM(T.PagoDeducible) AS M3_Col1_ValorPagoAbono, 
		SUM(T.ReteFteAplicada) AS M3_Col2_Retencion
		FROM
		(
			SELECT 
			M_Ppal.IdConcepto, 
			M_Ppal.IdCliente, 
			SUM(M_Ppal.RetencionBase) AS PagoDeducible, 
			SUM(COALESCE(M_Ppal.SumRetReal,M_Ppal.PagoDeducible)) AS ReteFteAplicada, 
			MAX(M_Cuantias.EsCuantiaMayor) AS EsCuantiaMayor
			FROM 
			dbo.VMediosMag_Procesos_Principal AS M_Ppal 
			INNER JOIN dbo.ufnMediosMagneticos_CuantiasMenores(@FechaIni, @FechaFin, @IdEmpresas, @IdDocsExc,@IdVersion) AS M_Cuantias ON M_Ppal.IdVersion = M_Cuantias.IdVersion AND M_Ppal.IdCliente = M_Cuantias.IdCliente 
			--INNER JOIN dbo.VMediosMag_Procesos_ReteFuente_Fin AS M_ReteFte ON M_Ppal.IdCliente = M_ReteFte.IdCliente AND M_Ppal.IdEncab = M_ReteFte.IdEncab AND M_Ppal.IdAgrup = M_ReteFte.IdAgrup 
			WHERE
			M_Ppal.IdVersion = @IdVersion AND
			DATEADD(dd,DATEDIFF(dd,0,M_Ppal.Fecha),0) BETWEEN @FechaIniT AND @FechaFinT AND
			M_Ppal.IdEmpresa IN(SELECT number FROM iter$simple_intlist_to_tbl(@IdEmpresas)) AND
			M_Ppal.IdDocumento NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdDocsExc,DEFAULT)) AND
			M_Ppal.IdCliente NOT IN(SELECT nstr FROM iter$charlist_to_tbl(@IdCliExc,DEFAULT)) 
			GROUP BY M_Ppal.IdConcepto, M_Ppal.IdCliente
		) T
		GROUP BY 
		T.IdConcepto,
		T.EsCuantiaMayor,
		CASE WHEN (@Detallar = 0 AND T.EsCuantiaMayor = 0) THEN 0 ELSE T.IdCliente END
		HAVING SUM(T.ReteFteAplicada) <> 0

	) U
	INNER JOIN dbo.MediosMag_Conceptos AS M_Conc ON U.IdConcepto = M_Conc.IdConcepto
	LEFT OUTER JOIN dbo.VClientesDir AS Dir ON CASE WHEN U.IdCliente = 0 THEN @IdCliInf ELSE U.IdCliente END = Dir.IdCliente 
	LEFT OUTER JOIN dbo.Clientes AS Cli ON U.IdCliente = Cli.IdCliente
	LEFT OUTER JOIN dbo.Clientes_TiposIdent AS TipoIdent ON TipoIdent.IdTipoIdent=cli.IdTipoIdent
	RETURN
END
END
GO


	/*
	Plantilla de script posterior a la implementación							
	--------------------------------------------------------------------------------------
	 Este archivo contiene instrucciones de SQL que se anexarán al script de compilación.		
	 Use la sintaxis de SQLCMD para incluir un archivo en el script posterior a la implementación.			
	 Ejemplo:      :r .\miArchivo.sql								
	 Use la sintaxis de SQLCMD para hacer referencia a una variable en el script posterior a la implementación.		
	 Ejemplo:      :setvar TableName miTabla							
				   SELECT * FROM [$(TableName)]					
	--------------------------------------------------------------------------------------
	*/


    --CREACION DE NUEVAS VERSIONES
    --//
    IF (NOT EXISTS(SELECT IdVersion FROM MediosMag_Versiones WHERE IdVersion=32))
    INSERT INTO [dbo].[MediosMag_Versiones] ([IdVersion] ,[NomVersion] ,[IdFormato] ,[CodVersion] ,[EsActivo])
    VALUES(32,'Concepto 1004 (V8)', 4, 8, 1)
    GO

    IF (NOT EXISTS(SELECT IdVersion FROM MediosMag_Versiones WHERE IdVersion=33))
    INSERT INTO [dbo].[MediosMag_Versiones] ([IdVersion] ,[NomVersion] ,[IdFormato] ,[CodVersion] ,[EsActivo])
    VALUES(33,'Concepto 1005 (V8)', 5, 8, 1)
    GO

    IF (NOT EXISTS(SELECT IdVersion FROM MediosMag_Versiones WHERE IdVersion=34))
    INSERT INTO [dbo].[MediosMag_Versiones] ([IdVersion] ,[NomVersion] ,[IdFormato] ,[CodVersion] ,[EsActivo])
    VALUES(34,'Concepto 1010 (V9)', 10, 9, 1)
    GO
    --//

    --ELIMINACION DE AGRUPACIONES 
    --//
    delete from MediosMag_ConceptosAgrupacion where IdVersion=15
    --//

	update MediosMag_Versiones set NomVersion = 'Concepto 1012 (V7)' where IdVersion=12
	update MediosMag_Versiones set EsActivo=0 where IdVersion in(1,13,4,16,5,6,7,10)


	--CONCEPTO 1001 V10 (IdVersion = 15)
    --//
    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 806)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (806, '5086', 'Valor retención en la fuente trasladada a terceros por parti', 'Valor retención en la fuente trasladada a terceros por participaciones o dividendos recibidos de sociedades nacionales E.T. art. 242-1 par.1', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 807)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (807, '5087', 'Valor de los puntos premio redimidos en el período que afect', 'Valor de los puntos premio redimidos en el período que afectan el gasto directamente, procedentes de programas de fidelización.', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 808)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (808, '5088', 'Costos y gastos por diferencia en cambio.Se debe repoortar', 'Costos y gastos por diferencia en cambio.Se debe repoortar con el NIT del informante', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 809)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (809, '5089', 'Valor Compra de acciones de sociedades que no cotizan en bo', 'Valor Compra de acciones de sociedades que no cotizan en bolsa', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 810)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (810, '5090', 'Valor Donación de acciones de sociedades que no cotizan en ', 'Valor Donación de acciones de sociedades que no cotizan en bolsa', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 811)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (811, '5091', 'Valor cesión de acciones de sociedades que no cotizan en b', 'Valor cesión de acciones de sociedades que no cotizan en bolsa', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 812)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (812, '5092', 'Valor del pago que constituye ingreso en especie para el b', 'Valor del pago que constituye ingreso en especie para el beneficio Art 29-1E. T', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 813)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (813, '5093', 'Donaciones de bebidas ultraprocesadas y azucaradas a los b', 'Donaciones de bebidas ultraprocesadas y azucaradas a los bancos de alimentos por los responsables del impuesto a estos bienes ET art 513-1, par 5 adicionado por el art 54 L 2277/2022', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 814)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (814, '5094', 'Donaciones de productos comestibles ultraprocesados indust', 'Donaciones de productos comestibles ultraprocesados industrialmente y/o con alto contenido de azucares añadidos, sodio o grasas saturadas a los bancos de alimentos por los responsables del impuesto a estos bienes ET art 513-1, par 5 adicionado por el art 54 L 2277/2022', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 815)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (815, '5095', 'Pago por concepto de regalías pagadas a la Nación u otros ', 'Pago por concepto de regalías pagadas a la Nación u otros entes territoriales por la explotación de hidrocarburos por entes diferentes a los organismos descentralizados. ET art 115 par 1', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 816)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (816, '5096', 'Pago por concepto de regalías pagadas a la Nación u otros ', 'Pago por concepto de regalías pagadas a la Nación u otros entes territoriales por la explotación de gas por entes diferentes a los organismos descentralizados. ET art 115 par 1', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 817)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (817, '5097', 'Pago por concepto de regalías pagadas a la nación u otros ', 'Pago por concepto de regalías pagadas a la nación u otros entes territoriales por la explotación de carbón por entes diferentes a los organismos descentralizados. ET art 115 par 1', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 818)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (818, '5098', 'Pago por concepto de regalías pagadas a la Nación u otros ', 'Pago por concepto de regalías pagadas a la Nación u otros entes territoriales por la explotación de otros minerales y piedras preciosas por entes diferentes a los organismos descentralizados. ET art 115 par 1', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 819)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (819, '5099', 'Pago por concepto de regalías pagadas a la Nación u otros ', 'Pago por concepto de regalías pagadas a la Nación u otros entes territoriales por la explotación de sal y materiales de construcción por entes diferentes a los organismos descentralizados. ET art 115 par 1', 1, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 820)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (820, '5100', 'Valor del Pago o abono en cuenta por prima en colocación d', 'Valor del Pago o abono en cuenta por prima en colocación de acciones y otros conceptos patrimoniales, sin incluir los reembolsos de aportes.', 1, 1, 0)
    --//


	--CONCEPTO 1004 V8 (IdVersion = 32) **NEW**
    --//
    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 821)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (821, '8332', 'Descuento tributario por convenios con Coldeportes para asig', 'Descuento tributario por convenios con Coldeportes para asignar becas de estudio y manutención a deportistas talento o reserva deportiva. E.T., art. 257-1, L. 2010, art. 94', 4, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 822)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (822, '8333', 'Descuento para inversiones realizadas en control, conservaci', 'Descuento para inversiones realizadas en control, conservación y mejoramiento del medio ambiente en actividades turísticas. E.T., art.255, par.2, adicionado L. 2068/2020, art. 42.', 4, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 823)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (823, '8334', 'Descuento por donaciones realizadas a la agencia de emprendi', 'Descuento por donaciones realizadas a la agencia de emprendimiento e innovación del Gobierno Nacional iNNpulsa. E.T., art.256, par.2, inc. 2, adicionado L. 2069/2020, art. 41.', 4, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 824)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (824, '8336', 'Descuento por donaciones recibidas por el Fondo Nacional de ', 'Descuento por donaciones recibidas por el Fondo Nacional de Financiamiento para la Ciencia, la Tecnología y la Innovación, Fondo Francisco José de Caldas, destinadas al financiamiento de Programas y/o Proyectos de Ciencia, Tecnología e Innovación. E.T. art. 158-1, num. I) (sic), modificado L.1955/2019, art.170.', 4, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 825)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (825, '8337', 'Descuento por remuneración correspondiente a la vinculación ', 'Descuento por remuneración correspondiente a la vinculación de personal con título de doctorado en las empresas contribuyentes de renta, E.T., art.158-1, num.iii), modificado L.1955/2019, art. 170.', 4, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 826)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (826, '8338', 'Descuento por donaciones recibidas por intermedio del Icete', 'Descuento por donaciones recibidas por intermedio del Icetex, dirigidas a programas de becas que financien la formación y educación de quienes ingresen a la Fuerza Pública. E.T., art. 58-1, num.iv), adicionado L.2130 del 2021, art.3.', 4, 1, 0)
    --//


    --CONCEPTO 1007 V9 (IdVersion = 17)
    --//
    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 827)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (827, '4018', 'Ingresos por diferencia en cambio. Se debe reportar con el N', 'Ingresos por diferencia en cambio. Se debe reportar con el NIT del informante.', 7, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 828)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (828, '4019', 'Ingresos brutos constitutivos de ganancia ocasional', 'Ingresos brutos constitutivos de ganancia ocasional', 7, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 829)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (829, '4020', 'Ingresos brutos por venta de acciones de sociedades o compa', 'Ingresos brutos por venta de acciones de sociedades o compañías que no cotizan en bolsa que constituyan renta. (incluye las acciones recibidas por cesión y donación).', 7, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 830)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (830, '4021', 'Ingresos brutos por venta de acciones de sociedades o compa', 'Ingresos brutos por venta de acciones de sociedades o compañías que no cotizan en bolsa que constituyan ganancia ocasional. (incluye las acciones recibidas por cesión y donación).', 7, 1, 0)
    --//


    --CONCEPTO 1009 V7 (IdVersion = 9)
    --//
    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 831)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (831, '2216', 'El valor de saldo de los pasivos de entidades financieras do', 'El valor de saldo de los pasivos de entidades financieras donde no sea posible identificar al acreedor nacional. Se debe reportar con el NIT de la entidad informante.', 9, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 832)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (832, '2217', 'El valor de saldo de los pasivos de entidades financieras do', 'El valor de saldo de los pasivos de entidades financieras donde no sea posible identificar al acreedor del exterior. Se debe reportar con el NIT de la entidad informante.', 9, 1, 0)
    --//


    --CONCEPTO 1011 V6 (IdVersion = 11)
    --//
    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 833)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (833, '1105', 'Saldo a 31 de Diciembre en caja', 'Saldo a 31 de Diciembre en caja', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 834)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (834, '1502', 'Valor patrimonial de vehiculos, maquinaria y equipo.', 'Valor patrimonial de vehiculos, maquinaria y equipo.', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 835)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (835, '1512', 'Valor patrimonial de activos fijos intangibles', 'Valor patrimonial de activos fijos intangibles', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 836)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (836, '1513', 'Valor patrimonial de los activos fijos agotables', 'Valor patrimonial de los activos fijos agotables', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 837)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (837, '1519', 'Valor Patrimonial a 31 de diciembre de Activos Biológicos Plantas', 'Valor Patrimonial a 31 de diciembre de Activos Biológicos Plantas', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 838)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (838, '1520', 'Valor Patrimonial a 31 de diciembre de Activos Biológicos Animales', 'Valor Patrimonial a 31 de diciembre de Activos Biológicos Animales', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 839)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (839, '1521', 'Valor Patrimonial de la casa o apartamento de habitación (inciso 2 art', 'Valor Patrimonial de la casa o apartamento de habitación (inciso 2 art 295-3ET)', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 840)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (840, '1522', 'Valor Patrimonial de lotes rurales y fincas', 'Valor Patrimonial de lotes rurales y fincas', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 841)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (841, '1523', 'Valor Patrimonial de lotes urbanos', 'Valor Patrimonial de lotes urbanos', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 842)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (842, '1524', 'Valor Patrimonial de otros bienes inmuebles. (No relacionad', 'Valor Patrimonial de otros bienes inmuebles. (No relacionados en los conceptos 1512, 1513y 1519)', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 843)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (843, '1525', 'Valor Patrimonial de otros bienes muebles.', 'Valor Patrimonial de otros bienes muebles.', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 844)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (844, '1526', 'Valor Patrimonial de otros activos. (No relacionados en los artículos ', 'Valor Patrimonial de otros activos. (No relacionados en los artículos 29 y 30 de la presente resolución y en los conceptos de este articulo)', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 845)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (845, '8405', 'Deducción por deterioro (provisión individual) de cartera de dudoso o ', 'Deducción por deterioro (provisión individual) de cartera de dudoso o difícil cobro. E.T.,art.145, DUR 1625 de 2016 arts.1.2.1.18.19 y 1.2.1.18.20.', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 846)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (846, '8406', 'Deducción por deterioro (provisión general) de cartera de dudoso o dif', 'Deducción por deterioro (provisión general) de cartera de dudoso o difícil cobro. E.T.,art.145, DUR 1625 de 2016 art.1.2.1.18.21.', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 847)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (847, '8407', 'Deducción por inversiones realizadas en investigación, desarrollo tecn', 'Deducción por inversiones realizadas en investigación, desarrollo tecnológico e innovación. E.T., art.158-1, inc. 1', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 848)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (848, '8412', 'Depreciación especial aplicada por contribuyentes del régimen de Mega-', 'Depreciación especial aplicada por contribuyentes del régimen de Mega-inversiones que realicen nuevas inversiones. E.T. art.235-3, num.2.', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 849)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (849, '8413', 'Deducción por provisiones de cartera hipotecaria de dudoso o difícil c', 'Deducción por provisiones de cartera hipotecaria de dudoso o difícil cobro. DUR 1625 de 2016 art.1.2.1.18.26.', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 850)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (850, '8414', 'Deducción deterioro de cartera de Entidades Promotoras de Salud -EPS e', 'Deducción deterioro de cartera de Entidades Promotoras de Salud -EPS en liquidación forzosa intervenidas por la Superintendencia Nacional de Salud. L. 1819/2016, art.375,modificado por L. 2010/ 2019, art.97', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 851)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (851, '8415', 'Deducción deterioro de cartera de dudoso o difícil cobro E.T. art. 145', 'Deducción deterioro de cartera de dudoso o difícil cobro E.T. art. 145. Par. 1.', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 852)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (852, '8421', 'Deducción por pérdida en Ia enajenación de activos. E.T., art. 149', 'Deducción por pérdida en Ia enajenación de activos. E.T., art. 149', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 853)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (853, '8422', 'Deducción por pérdida en Ia enajenación de plusvalía. E.T., art. 154.', 'Deducción por pérdida en Ia enajenación de plusvalía. E.T., art. 154.', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 854)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (854, '8423', 'Deducción por perdida en la enajenación de bonos para la seguridad art', 'Deducción por perdida en la enajenación de bonos para la seguridad art 4 L 345/1996 derogado por el art 96 L 2277/2022', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 855)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (855, '8424', 'Deducción por perdida en la enajenación de bonos para la seguridad par', 'Deducción por perdida en la enajenación de bonos para la seguridad para la paz art 5 L 487/1998 derogado por el art 96 L 2277/2022', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 856)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (856, '8425', 'Deducción del 120% de salarios y prestaciones sociales por contratació', 'Deducción del 120% de salarios y prestaciones sociales por contratación de adulto mayor sin pensión L 2040/2020 art 2.', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 857)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (857, '9003', 'Exclusión de IVA por venta de computadores personales. E.T., art. 424,', 'Exclusión de IVA por venta de computadores personales. E.T., art. 424, num. 3, modificado L.1819/2016, art.175, num. 5.', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 858)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (858, '9070', 'Exclusión de IVA en comercialización de animales vivos, excepto los an', 'Exclusión de IVA en comercialización de animales vivos, excepto los animales domésticos de compañía. ET art 476 núm. 25 modificado ley 2277/2020 art 76, art. 1', 11, 1, 0)

    IF NOT EXISTS (SELECT 1 FROM MediosMag_Conceptos WHERE IdConcepto = 859)
    INSERT INTO MediosMag_Conceptos (IdConcepto, CodConcepto, NomConcepto, Nota, IdFormato, EsActivo, ValorBase)
    VALUES (859, '9072', 'Exclusión de IVA en el servicio de faenamiento ET art 476 núm. 25 A', 'Exclusión de IVA en el servicio de faenamiento ET art 476 núm. 25 A', 11, 1, 0)
    --//


	--CONCEPTO AGRUPACION DE -- 1001 V10 (IdVersion = 15)
    --//

    --Conceptos Anteriores
    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 2)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 2, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 3)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 3, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 4)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 4, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 5)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 5, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 6)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 6, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 7)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 7, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 8)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 8, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 9)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 9, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 10)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 10, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 11)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 11, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 12)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 12, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 13)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 13, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 14)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 14, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 15)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 15, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 16)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 16, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 17)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 17, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 18)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 18, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 20)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 20, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 21)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 21, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 22)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 22, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 23)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 23, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 24)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 24, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 25)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 25, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 26)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 26, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 27)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 27, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 28)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 28, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 29)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 29, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 30)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 30, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 31)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 31, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 32)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 32, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 41)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 41, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 42)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 42, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 43)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 43, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 44)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 44, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 45)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 45, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 50)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 50, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 51)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 51, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 52)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 52, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 53)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 53, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 55)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 55, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 56)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 56, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 57)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 57, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 526)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 526, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 527)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 527, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 528)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 528, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 529)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 529, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 530)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 530, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 531)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 531, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 532)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 532, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 533)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 533, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 534)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 534, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 535)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 535, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 536)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 536, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 537)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 537, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 538)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 538, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 539)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 539, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 540)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 540, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 542)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 542, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 543)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 543, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 544)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 544, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 545)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 545, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 546)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 546, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 547)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 547, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 548)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 548, 0, 0);

    --Conceptos Nuevos
    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 806)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 806, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 807)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 807, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 808)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 808, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 809)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 809, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 810)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 810, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 811)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 811, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 812)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 812, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 813)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 813, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 814)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 814, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 815)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 815, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 816)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 816, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 817)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 817, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 818)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 818, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 819)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 819, 0, 0);

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 15 AND IdConcepto = 820)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (15, 820, 0, 0);
    --//


	--CONCEPTO AGRUPACION - 1004 V8 (IdVersion = 32) **NEW**
    --//

    --Conceptos Anteriores
	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 202)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 202, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 204)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 204, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 549)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 549, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 550)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 550, 0, 0)
	
	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 551)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 551, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 552)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 552, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 553)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 553, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 554)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 554, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 555)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 555, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 556)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 556, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 557)	
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 557, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 558)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 558, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 559)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 559, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 560)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 560, 0, 0)
	
	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 561)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 561, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 562)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 562, 0, 0)

	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 563)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 563, 0, 0)
	
	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 564)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (32, 564, 0, 0)

    --Conceptos Nuevos
    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 821)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (32, 821, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 822)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (32, 822, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 823)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (32, 823, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 824)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (32, 824, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 825)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (32, 825, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 32 AND IdConcepto = 826)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (32, 826, 0, 0)
    --//

    --CONCEPTO AGRUPACION - 1007 V9 (IdVersion = 17)
    --//

    --Concepto Anterior
    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 17 AND IdConcepto = 300)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (17, 300, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 17 AND IdConcepto = 301)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (17, 301, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 17 AND IdConcepto = 302)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (17, 302, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 17 AND IdConcepto = 303)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (17, 303, 0, 0)

    --Concepto Nuevo
    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 17 AND IdConcepto = 827)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (17, 827, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 17 AND IdConcepto = 828)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (17, 828, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 17 AND IdConcepto = 829)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (17, 829, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 17 AND IdConcepto = 830)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (17, 830, 0, 0)
    --//


    --CONCEPTO AGRUPACION - 1009 V7 (IdVersion = 9)
    --//

    --Conceptos anteriores
    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 9 AND IdConcepto = 500)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (9, 500, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 9 AND IdConcepto = 501)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (9, 501, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 9 AND IdConcepto = 502)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (9, 502, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 9 AND IdConcepto = 503)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (9, 503, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 9 AND IdConcepto = 504)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (9, 504, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 9 AND IdConcepto = 505)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (9, 505, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 9 AND IdConcepto = 506)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (9, 506, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 9 AND IdConcepto = 507)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (9, 507, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 9 AND IdConcepto = 508)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (9, 508, 0, 0)

    --Conceptos nuevos
    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 9 AND IdConcepto = 831)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (9, 831, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 9 AND IdConcepto = 832)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (9, 832, 0, 0)
    --//


    --CONCEPTO AGRUPACION - 1010 V9 (IdVersion = 34)
    --//
    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 34 AND IdConcepto = 293)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (34, 293, 0, 0)
    --//


    --CONCEPTO AGRUPACION - 1011 V9 (IdVersion = 11)
    --//
	--Conceptos Nuevos
    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 833)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 833, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 834)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 834, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 835)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 835, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 836)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 836, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 837)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 837, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 838)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 838, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 839)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 839, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 840)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 840, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 841)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 841, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 842)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 842, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 843)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 843, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 844)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 844, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 845)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 845, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 846)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 846, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 847)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 847, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 848)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 848, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 849)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 849, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 850)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 850, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 851)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 851, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 852)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 852, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 853)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 853, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 854)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 854, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 855)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 855, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 856)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 856, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 857)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 857, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 858)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 858, 0, 0)

    IF NOT EXISTS (SELECT * FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 11 AND IdConcepto = 859)
    INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
    VALUES (11, 859, 0, 0)
    --//

	--CONCEPTO AGRUPACION - 1005 V8 (IdVersion = 33) **NEW**
    --//
    --Conceptos Anteriores
	IF NOT EXISTS (SELECT *	FROM MediosMag_ConceptosAgrupacion WHERE IdVersion = 33 AND IdConcepto = 280)
	INSERT INTO MediosMag_ConceptosAgrupacion (IdVersion, IdConcepto, IdAgrup, IdAgrupPers)
	VALUES (33, 280, 0, 0)
	--//
GO

GO
PRINT N'Update complete.';

--Actualizar datos de la version
INSERT INTO ZiurVersiones (IdVersion, NumVersion, Descripcion)
VALUES (376,966,'Conceptos Medios Actuales')
GO

COMMIT
GO
