CREATE PROCEDURE [dbo].[sp_conBalanceGeneral_Excel]
(
@mesi INT = 6,
@mesf INT = 6,
@periodo INT = 2013,
@soloSaldo BIT = 1,
@soloMovimiento Bit = 1,
@codctaini VARCHAR(50) = '0',
@codctafin VARCHAR(50) = '99999999',
@unineg INT = 0,
@clasificacion INT = 0
)	
AS 

DECLARE @A TABLE (cuenta varchar(50), Debitos decimal(18,4), Creditos decimal(18,4), Deudor decimal(18,4), Acreedor decimal(18,4))

INSERT INTO @A
SELECT     conComprobantes.cuenta, 
	COALESCE(SUM(conComprobantes.debe),0) AS Debitos, 
	COALESCE(SUM(conComprobantes.haber),0) AS Creditos,   
	CASE 
	WHEN SUM(debe) > SUM(haber) THEN SUM(debe) - SUM(haber) 
	ELSE 0
	END AS Deudor,
	
	CASE
	WHEN SUM(debe) < SUM(haber)	THEN SUM(haber) - SUM(debe)
	ELSE 0
	END AS Acreedor
	--CASE WHEN SUM(debe) > SUM(haber) THEN SUM(debe) - SUM(haber) ELSE 0 END AS Deudor,
	--CASE WHEN SUM(debe) < SUM(haber) THEN SUM(haber) - SUM(debe) ELSE 0 END AS Acreedor
FROM         conComprobantes
WHERE  (conComprobantes.periodo =@periodo)
AND    (conComprobantes.mes BETWEEN @mesi AND @mesf)
AND    (conComprobantes.cuenta BETWEEN @codctaini AND @codctafin)
AND    (COALESCE(conComprobantes.uni_negocio,0) = @unineg OR @unineg=-1) 
AND    (conComprobantes.clasificacion =1 OR conComprobantes.clasificacion=@clasificacion)

GROUP BY	conComprobantes.cuenta
ORDER BY  conComprobantes.cuenta--convert(int,conComprobantes.cuenta) asc


--DROP TABLE #B

SELECT      PC.cuenta, 
			PC.descripcion, 
			COALESCE (A.Debitos, 0) AS Debitos, 
			COALESCE (A.Creditos, 0) AS Creditos, 
			COALESCE (A.Deudor, 0) AS Deudor, 
			COALESCE (A.Acreedor, 0) AS Acreedor, 
			CASE WHEN Naturaleza.tipoNaturaleza <= 1 AND Debitos - Creditos > 0 THEN Debitos - Creditos ELSE 0 END AS Activo, 
			CASE WHEN Naturaleza.tipoNaturaleza <= 1 AND Creditos - Debitos > 0 THEN Creditos - Debitos ELSE 0 END AS Pasivo, 
			CASE WHEN Naturaleza.tipoNaturaleza >= 2 AND Debitos - Creditos > 0 THEN Debitos - Creditos ELSE 0 END AS Perdida, 
			CASE WHEN Naturaleza.tipoNaturaleza >= 2 AND Creditos - Debitos > 0 THEN Creditos - Debitos ELSE 0 END AS Ganancia, 
			PC.naturaleza, 
			PC.clase, 
			PC.subclase, 
			PC.imputacion,
			naturaleza.marcaCtacte
INTO #B
FROM       conPlanCuentas AS Naturaleza INNER JOIN
           conPlanCuentas AS PC ON Naturaleza.naturaleza = PC.naturaleza LEFT OUTER JOIN
           @A AS A ON PC.cuenta = A.cuenta		
WHERE    PC.periodo = @periodo
AND		(Naturaleza.periodo = @periodo) 
AND		(Naturaleza.clase = 0) 
AND		(Naturaleza.subclase = 0) 
AND		(Naturaleza.imputacion = 0)
--AND		  ((A.Debitos + A.Creditos) > 0)
AND		  (((A.Debitos + A.Creditos) > 0) OR (@soloMovimiento = 0))
AND		  ((A.Debitos <> A.Creditos) OR (@soloSaldo = 0))

--DROP TABLE #D

SELECT		'' as cuenta,
			'SUMAS PARCIALES' as descripcion, 
			SUM(debitos) as debitos, 
			SUM(creditos) as creditos, 
			SUM(deudor) as deudor, 
			SUM(acreedor) as acreedor, 
			SUM(Activo) as activo , 
			SUM(Pasivo) as pasivo, 
			SUM(Perdida)as perdida, 
			SUM(Ganancia) as ganancia
INTO #D
FROM #B

INSERT INTO #D
SELECT		'' as cuenta,
			'RESULTADOS' as descripcion, 
			0 as debitos, 
			0 as creditos, 
			0 as deudor, 
			0 as acreedor, 
			CASE WHEN activo < pasivo THEN pasivo - activo  ELSE 0 END AS activo,
			CASE WHEN activo > pasivo THEN activo - pasivo ELSE 0 END AS pasivo, 
			CASE WHEN perdida < ganancia THEN ganancia - perdida ELSE 0 END,-- AS ganancia, 
			CASE WHEN perdida > ganancia THEN perdida - ganancia ELSE 0 END-- AS perdida,
FROM #D

INSERT INTO #D
SELECT		'' as cuenta,
			'SUMAS IGUALES' as descripcion, 
			SUM(debitos) as debitos, 
			SUM(creditos) as creditos, 
			SUM(deudor) as deudor, 
			SUM(acreedor) as acreedor, 
			SUM(activo) AS activo,
			SUM(pasivo) AS pasivo, 
			SUM(perdida) AS perdida,
			SUM(ganancia) AS ganancia
FROM #D

--DROP TABLE #C

SELECT      B.*, 
            PCNat.marcaCtacte AS TipoNaturaleza, 	
			PCNat.cuenta AS cuentaNaturaleza, 
			PCCla.cuenta AS cuentaClase, 
            PCSub.cuenta AS cuentaSubClase 
           ,PCNat.descripcion AS descripcionNaturaleza, PCCla.descripcion AS descripcionClase, PCSub.descripcion AS descripcionSubClase
INTO #C
FROM         #B B INNER JOIN
                      conPlanCuentas AS PCNat ON B.naturaleza = PCNat.naturaleza INNER JOIN
                      conPlanCuentas AS PCCla ON B.naturaleza = PCCla.naturaleza AND B.clase = PCCla.clase INNER JOIN
                      conPlanCuentas AS PCSub ON B.naturaleza = PCSub.naturaleza AND B.clase = PCSub.clase AND B.subclase = PCSub.subclase
WHERE       (PCNat.clase = 0) 
AND			(PCNat.subclase = 0) 
AND			(PCNat.imputacion = 0) 
AND			(PCNat.periodo = @periodo) 
AND			(PCCla.subclase = 0) 
AND			(PCCla.imputacion = 0) 
AND			(PCCla.periodo = @periodo) 
AND			(PCSub.imputacion = 0) 
AND			(PCSub.periodo = @periodo)

--DROP TABLE #E

SELECT     cuenta, descripcion, Debitos, Creditos, Deudor, Acreedor, Activo, Pasivo, Perdida, Ganancia, TipoNaturaleza, naturaleza, clase, subclase, imputacion
into  #E
FROM         #C

UNION ALL

SELECT     DISTINCT cuentaNaturaleza, descripcionNaturaleza, 0, 0, 0, 0, 0, 0, 0, 0, TipoNaturaleza, naturaleza, 0, 0, 0
FROM         #C

UNION ALL

SELECT     DISTINCT cuentaClase, descripcionClase, 0, 0, 0, 0, 0, 0, 0, 0, TipoNaturaleza, naturaleza, clase, 0, 0
FROM         #C

UNION ALL

SELECT     DISTINCT cuentaSubClase, descripcionSubClase, 0, 0, 0, 0, 0, 0, 0, 0, TipoNaturaleza, naturaleza, clase, subclase, 0
FROM         #C

DECLARE @E1 table (cuenta varchar(50), 
                 descripcion varchar(100), 
				 Debitos decimal(18,4), 
				 Creditos decimal(18,4) , 
				 Deudor decimal(18,4),
				 Acreedor decimal(18,4), 
				 Activo decimal(18,4), 
				 Pasivo decimal(18,4), 
				 Perdida decimal(18,4), 
				 Ganancia decimal(18,4))

INSERT @E1
SELECT cuenta, descripcion, Debitos, Creditos, Deudor, Acreedor, Activo, Pasivo, Perdida, Ganancia			 
FROM #E
WHERE imputacion <> 0 OR TipoNaturaleza = 9
ORDER BY 1

SELECT * FROM @E1


UNION ALL

SELECT * FROM #D --order by convert(int, cuenta) asc