CREATE PROCEDURE [dbo].[sp_conSuperEduc_Balance9]
(
@mesDesde INT = 1,
@mesHasta INT = 12,
@periodo INT = 0
)
AS

--DECLARE @periodo INT = (SELECT periodo FROM conParametros)

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
FROM         conComprobantes
WHERE  (conComprobantes.periodo =@periodo)
AND    (conComprobantes.mes between @mesDesde and @mesHasta)

GROUP BY	conComprobantes.cuenta
ORDER BY  conComprobantes.cuenta

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,
			ISNULL(conSuperEduc.clasRepSIE,0) AS clasRepSIE
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 INNER JOIN
		   conSuperEduc ON PC.cuenta = conSuperEduc.cuenta 
		   AND PC.periodo = conSuperEduc.periodo
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))

---- Primera inserción: crea la tabla #D
--SELECT		999999997 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,
--			MAX(clasRepSIE) AS clasRepSIE
--INTO #D
--FROM #B

---- Segunda inserción: RESULTADOS
--INSERT INTO #D (cuenta, descripcion, debitos, creditos, deudor, acreedor, activo, pasivo, perdida, ganancia, clasRepSIE)
--SELECT		999999998 as cuenta,
--			'RESULTADOS' as descripcion, 
--			0 as debitos, 
--			0 as creditos, 
--			0 as deudor, 
--			0 as acreedor, 
--			CASE WHEN SUM(activo) < SUM(pasivo) THEN SUM(pasivo) - SUM(activo) ELSE 0 END AS activo,
--			CASE WHEN SUM(activo) > SUM(pasivo) THEN SUM(activo) - SUM(pasivo) ELSE 0 END AS pasivo, 
--			CASE WHEN SUM(perdida) < SUM(ganancia) THEN SUM(ganancia) - SUM(perdida) ELSE 0 END AS perdida,
--			CASE WHEN SUM(perdida) > SUM(ganancia) THEN SUM(perdida) - SUM(ganancia) ELSE 0 END AS ganancia,
--			0 AS clasRepSIE
--FROM #D

---- Tercera inserción: SUMAS IGUALES
--INSERT INTO #D (cuenta, descripcion, debitos, creditos, deudor, acreedor, activo, pasivo, perdida, ganancia, clasRepSIE)
--SELECT		999999999 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,
--			0 AS clasRepSIE
--FROM #D

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)


SELECT     cuenta, descripcion, Debitos, Creditos, Deudor, Acreedor, Activo, Pasivo, Perdida, Ganancia, clasRepSIE
into  #E
FROM         #C

--UNION ALL

--SELECT     DISTINCT cuentaNaturaleza, descripcionNaturaleza, 0, 0, 0, 0, 0, 0, 0, 0, clasRepSIE
--FROM         #C

--UNION ALL

--SELECT     DISTINCT cuentaClase, descripcionClase, 0, 0, 0, 0, 0, 0, 0, 0, clasRepSIE
--FROM         #C

--UNION ALL

--SELECT     DISTINCT cuentaSubClase, descripcionSubClase, 0, 0, 0, 0, 0, 0, 0, 0, clasRepSIE
--FROM         #C

--UNION ALL

--SELECT * FROM #D

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),
				 clasRepSIE INT)



INSERT @E1
SELECT *
FROM #E
ORDER BY 1

SELECT  cuenta AS 'Codigo Cuenta Contable', descripcion AS 'Nombre Cuenta Contable',
		Debitos, Creditos, Deudor AS 'Saldo Deudor', Acreedor AS 'Saldo Acreedor',
		Activo, Pasivo, Perdida, Ganancia, clasRepSIE AS 'Clasificacion Reportes SIE'
FROM @E1