IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.[CPJ_Conceptos_Rpt_Anual]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
   DROP PROCEDURE dbo.CPJ_Conceptos_Rpt_Anual
END
GO


CREATE PROCEDURE [dbo].[CPJ_Conceptos_Rpt_Anual] @pJson VARCHAR(8000)
AS 
DECLARE @pAnio INT
	,@pCircuito CHAR(1)
	,@pFlag_Apertura TINYINT
	,@pId_Periodo_Caja INT
	,@pNivel	CHAR(1)

SET NOCOUNT ON 
SET @pAnio				= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJson,'Anio')), 0)
SET @pCircuito			= ISNULL(CONVERT(CHAR(1), dbo.CF_JSON_VALUE(@pJson,'Circuito')), 0)
SET @pFlag_Apertura		= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'Flag_Apertura')), 0)
SET @pId_Periodo_Caja	= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJson,'Id_Periodo_Caja')), 0)
SET @pNivel				= ISNULL(CONVERT(CHAR(1), dbo.CF_JSON_VALUE(@pJson,'Nivel')), 'C')

DECLARE @Mes SMALLINT
	,@Id_Concepto INT
	,@Rubro VARCHAR(100) 
	,@SubRubro VARCHAR(100) 
	,@Concepto VARCHAR(100) 
	,@Importe DECIMAL(16,2)
	,@Id_Dest INT 
	,@Destinatario VARCHAR(100)

CREATE TABLE #Reporte (
	Id_Concepto INT
	,Rubro VARCHAR(100) DEFAULT('')
	,SubRubro VARCHAR(100) DEFAULT('')
	,Concepto VARCHAR(100) DEFAULT('')
	,Id_Dest INT DEFAULT(0)
	,Destinatario VARCHAR(100) DEFAULT('')
	,Importe DECIMAL(16,2)  DEFAULT(0)
	,Mes1 DECIMAL(16,2) DEFAULT(0)
	,Mes2 DECIMAL(16,2) DEFAULT(0)
	,Mes3 DECIMAL(16,2) DEFAULT(0)
	,Mes4 DECIMAL(16,2) DEFAULT(0)
	,Mes5 DECIMAL(16,2) DEFAULT(0)
	,Mes6 DECIMAL(16,2) DEFAULT(0)
	,Mes7 DECIMAL(16,2) DEFAULT(0)
	,Mes8 DECIMAL(16,2) DEFAULT(0)
	,Mes9 DECIMAL(16,2) DEFAULT(0)
	,Mes10 DECIMAL(16,2) DEFAULT(0)
	,Mes11 DECIMAL(16,2) DEFAULT(0)
	,Mes12 DECIMAL(16,2) DEFAULT(0)
	,Total DECIMAL(16,2) DEFAULT(0)
)

SELECT Id_Periodo_Caja INTO #Cajas_Periodos FROM Cajas_Periodos WHERE 1=0

IF @pId_Periodo_Caja = 0
	INSERT INTO #Cajas_Periodos SELECT Id_Periodo_Caja FROM Cajas_Periodos 
ELSE
	INSERT INTO #Cajas_Periodos SELECT Id_Periodo_Caja FROM Cajas_Periodos WHERE Id_Periodo_Caja = @pId_Periodo_Caja 


SET @Mes = 1
WHILE @Mes <= 12
BEGIN

	IF @pCircuito IN ('','P')
	BEGIN
		IF @pFlag_Apertura = 0
		BEGIN

			DECLARE MiCurPagos CURSOR FOR 
				SELECT	O.Id_Concepto
						,O2.Descripcion	AS Rubro 
						,O1.Descripcion	AS SubRubro 
						,O.Descripcion	AS Concepto
						,0 AS Id_Dest
						,'' AS Destinatario
						,SUM(ISNULL(CO.Importe,0))
					FROM Cajas_Mov_Conceptos CO
						, Cbtes C
						LEFT JOIN Proveedores P ON C.Id_Proveedor = P.Id_Proveedor
						, Cbtes_Tipos T
						, Conceptos O LEFT JOIN Conceptos O1 ON O1.Id_Concepto = O.Id_Concepto_Padre
						LEFT JOIN Conceptos O2 ON O2.Id_Concepto = O1.Id_Concepto_Padre
				
					WHERE CO.Id_Trans = C.Id_Trans
					AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
					AND CO.Id_Concepto = O.Id_Concepto
					AND C.Id_Estado_Cbte IN (1,2,4)
					AND T.Compromete_Pagos IN (1,-1)
					AND DATEPART(YEAR,C.Fecha) = @pAnio
					AND DATEPART(MONTH,C.Fecha) = @Mes
					AND CO.Id_Periodo_Caja IN (SELECT Id_Periodo_Caja FROM #Cajas_Periodos )
					GROUP BY O.Id_Concepto,O2.Descripcion, O1.Descripcion, O.Descripcion
					ORDER BY O2.Descripcion, O1.Descripcion, O.Descripcion
		END
		ELSE
		BEGIN
			DECLARE MiCurPagos CURSOR FOR 
				SELECT	O.Id_Concepto
						,O2.Descripcion	AS Rubro 
						,O1.Descripcion	AS SubRubro 
						,O.Descripcion	AS Concepto
						,ISNULL(C.Id_Proveedor,0) AS Id_Dest
						,ISNULL(P.Razon_Social,'') AS Proveedor
						,SUM(ISNULL(CO.Importe,0))
					FROM Cajas_Mov_Conceptos CO
						, Cbtes C
						LEFT JOIN Proveedores P ON C.Id_Proveedor = P.Id_Proveedor
						, Cbtes_Tipos T
						, Conceptos O LEFT JOIN Conceptos O1 ON O1.Id_Concepto = O.Id_Concepto_Padre
						LEFT JOIN Conceptos O2 ON O2.Id_Concepto = O1.Id_Concepto_Padre
					WHERE CO.Id_Trans = C.Id_Trans
					AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
					AND CO.Id_Concepto = O.Id_Concepto
					AND C.Id_Estado_Cbte IN (1,2,4)
					AND T.Compromete_Pagos IN (1,-1)
					AND DATEPART(YEAR,C.Fecha) = @pAnio
					AND DATEPART(MONTH,C.Fecha) = @Mes
					AND CO.Id_Periodo_Caja IN (SELECT Id_Periodo_Caja FROM #Cajas_Periodos )
					GROUP BY O.Id_Concepto,O2.Descripcion, O1.Descripcion, O.Descripcion,C.Id_Proveedor,P.Razon_Social
					ORDER BY O2.Descripcion, O1.Descripcion, O.Descripcion,C.Id_Proveedor,P.Razon_Social
		END
	END --IF @pCircuito IN ('','P')


	IF @pCircuito = 'R'
	BEGIN
		IF @pFlag_Apertura = 0
		BEGIN

			DECLARE MiCurPagos CURSOR FOR 
				SELECT	O.Id_Concepto
						,O2.Descripcion	AS Rubro 
						,O1.Descripcion	AS SubRubro 
						,O.Descripcion	AS Concepto
						,0 AS Id_Dest
						,'' AS Destinatario
						,SUM(ISNULL(CO.Importe,0))
					FROM Cajas_Mov_Conceptos CO
						, Cbtes C
						LEFT JOIN Clientes Cli ON C.Id_Cliente = Cli.Id_Cliente
						, Cbtes_Tipos T
						, Conceptos O LEFT JOIN Conceptos O1 ON O1.Id_Concepto = O.Id_Concepto_Padre
						LEFT JOIN Conceptos O2 ON O2.Id_Concepto = O1.Id_Concepto_Padre
				
					WHERE CO.Id_Trans = C.Id_Trans
					AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
					AND CO.Id_Concepto = O.Id_Concepto
					AND C.Id_Estado_Cbte IN (1,2,4)
					AND T.Compromete_Recibos IN (1,-1)
					AND DATEPART(YEAR,C.Fecha) = @pAnio
					AND DATEPART(MONTH,C.Fecha) = @Mes
					AND CO.Id_Periodo_Caja IN (SELECT Id_Periodo_Caja FROM #Cajas_Periodos )
					GROUP BY O.Id_Concepto,O2.Descripcion, O1.Descripcion, O.Descripcion
					ORDER BY O2.Descripcion, O1.Descripcion, O.Descripcion
		END
		ELSE
		BEGIN
			DECLARE MiCurPagos CURSOR FOR 
				SELECT	O.Id_Concepto
						,O2.Descripcion	AS Rubro 
						,O1.Descripcion	AS SubRubro 
						,O.Descripcion	AS Concepto
						,ISNULL(C.Id_Cliente,0) AS Id_Dest
						,ISNULL(Cli.Razon_Social,'') AS Destinatario
						,SUM(ISNULL(CO.Importe,0))
					FROM Cajas_Mov_Conceptos CO
						, Cbtes C
						LEFT JOIN Clientes Cli ON C.Id_Cliente = Cli.Id_Cliente
						, Cbtes_Tipos T
						, Conceptos O LEFT JOIN Conceptos O1 ON O1.Id_Concepto = O.Id_Concepto_Padre
						LEFT JOIN Conceptos O2 ON O2.Id_Concepto = O1.Id_Concepto_Padre
					WHERE CO.Id_Trans = C.Id_Trans
					AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
					AND CO.Id_Concepto = O.Id_Concepto
					AND C.Id_Estado_Cbte IN (1,2,4)
					AND T.Compromete_Recibos IN (1,-1)
					AND DATEPART(YEAR,C.Fecha) = @pAnio
					AND DATEPART(MONTH,C.Fecha) = @Mes
					AND CO.Id_Periodo_Caja IN (SELECT Id_Periodo_Caja FROM #Cajas_Periodos )
					GROUP BY O.Id_Concepto,O2.Descripcion, O1.Descripcion, O.Descripcion,C.Id_Cliente,Cli.Razon_Social
					ORDER BY O2.Descripcion, O1.Descripcion, O.Descripcion,Cli.Razon_Social
		END
	END --IF @pCircuito ='R'


	IF @pCircuito = 'C'
	BEGIN
		IF @pFlag_Apertura = 0
		BEGIN

			DECLARE MiCurPagos CURSOR FOR 
				SELECT	O.Id_Concepto
						,O2.Descripcion	AS Rubro 
						,O1.Descripcion	AS SubRubro 
						,O.Descripcion	AS Concepto
						,0 AS Id_Dest
						,'' AS Destinatario
						,SUM(ISNULL(CO.Importe,0))
					FROM Cbtes_Conceptos CO
						, Cbtes C
						LEFT JOIN Proveedores P ON C.Id_Proveedor = P.Id_Proveedor
						, Cbtes_Tipos T
						, Conceptos O LEFT JOIN Conceptos O1 ON O1.Id_Concepto = O.Id_Concepto_Padre
						LEFT JOIN Conceptos O2 ON O2.Id_Concepto = O1.Id_Concepto_Padre
					WHERE CO.Id_Trans = C.Id_Trans
					AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
					AND CO.Id_Concepto = O.Id_Concepto
					AND C.Id_Estado_Cbte IN (1,2,4)
					AND T.Compromete_Compras IN (1,-1)
					AND DATEPART(YEAR,C.Fecha) = @pAnio
					AND DATEPART(MONTH,C.Fecha) = @Mes
					GROUP BY O.Id_Concepto,O2.Descripcion, O1.Descripcion, O.Descripcion
					ORDER BY O2.Descripcion, O1.Descripcion, O.Descripcion
		END
		ELSE
		BEGIN
			DECLARE MiCurPagos CURSOR FOR 
				SELECT	O.Id_Concepto
						,O2.Descripcion	AS Rubro 
						,O1.Descripcion	AS SubRubro 
						,O.Descripcion	AS Concepto
						,ISNULL(C.Id_Proveedor,0) AS Id_Dest
						,ISNULL(P.Razon_Social,'') AS Proveedor
						,SUM(ISNULL(CO.Importe,0))
					FROM Cbtes_Conceptos CO
						, Cbtes C
						LEFT JOIN Proveedores P ON C.Id_Proveedor = P.Id_Proveedor
						, Cbtes_Tipos T
						, Conceptos O LEFT JOIN Conceptos O1 ON O1.Id_Concepto = O.Id_Concepto_Padre
						LEFT JOIN Conceptos O2 ON O2.Id_Concepto = O1.Id_Concepto_Padre
					WHERE CO.Id_Trans = C.Id_Trans
					AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
					AND CO.Id_Concepto = O.Id_Concepto
					AND C.Id_Estado_Cbte IN (1,2,4)
					AND T.Compromete_Compras IN (1,-1)
					AND DATEPART(YEAR,C.Fecha) = @pAnio
					AND DATEPART(MONTH,C.Fecha) = @Mes
					GROUP BY O.Id_Concepto,O2.Descripcion, O1.Descripcion, O.Descripcion,C.Id_Proveedor,P.Razon_Social
					ORDER BY O2.Descripcion, O1.Descripcion, O.Descripcion,C.Id_Proveedor,P.Razon_Social
		END
	END --IF @pCircuito = 'C'



	IF @pCircuito = 'V'
	BEGIN
		IF @pFlag_Apertura = 0
		BEGIN

			DECLARE MiCurPagos CURSOR FOR 
				SELECT	1
						,'Ventas'	AS Rubro 
						,'Ventas'	AS SubRubro 
						,'Ventas'	AS Concepto
						,0 AS Id_Dest
						,'' AS Destinatario
						,SUM(ISNULL(C.Imp_Total *  T.Compromete_Factura,0))
					FROM Cbtes C
						LEFT JOIN Clientes Cli ON C.Id_Cliente = Cli.Id_Cliente
						, Cbtes_Tipos T
			
					WHERE C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
					AND C.Id_Estado_Cbte IN (1,2,4)
					AND T.Compromete_Factura IN (1,-1) 
					AND DATEPART(YEAR,C.Fecha) = @pAnio
					AND DATEPART(MONTH,C.Fecha) = @Mes
					--GROUP BY 1
					ORDER BY 1
		END
		ELSE
		BEGIN
			DECLARE MiCurPagos CURSOR FOR 
				SELECT	1
						,'Ventas'	AS Rubro 
						,'Ventas'	AS SubRubro 
						,'Ventas'	AS Concepto
						,ISNULL(C.Id_Cliente,0) AS Id_Dest
						,ISNULL(Cli.Razon_Social,'') AS Destinatario
						,SUM(ISNULL(C.Imp_Total * T.Compromete_Factura ,0))
					FROM Cbtes C
						LEFT JOIN Clientes Cli ON C.Id_Cliente = Cli.Id_Cliente
						, Cbtes_Tipos T
						
					WHERE C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
					AND C.Id_Estado_Cbte IN (1,2,4)
					AND T.Compromete_Factura IN (1,-1)
					AND DATEPART(YEAR,C.Fecha) = @pAnio
					AND DATEPART(MONTH,C.Fecha) = @Mes
					GROUP BY C.Id_Cliente,Cli.Razon_Social
					ORDER BY Cli.Razon_Social
		END
	END --IF @pCircuito ='V'

	IF @pCircuito IN ('','N')
	BEGIN
		IF @pFlag_Apertura = 0
		BEGIN

			DECLARE MiCurPagos CURSOR FOR 
				SELECT	O.Id_Concepto
						,O2.Descripcion	AS Rubro 
						,O1.Descripcion	AS SubRubro 
						,O.Descripcion	AS Concepto
						,0 AS Id_Dest
						,'' AS Destinatario
						,SUM(Convert(Decimal(20,2),Abs(ISNULL(CO.Importe,0)) * abs(ISNULL(V.Importe,0))/(ABS(ISNULL(C.Imp_total,0))+ABS(ISNULL(C.Imp_percep,0)))*-1)) AS Importe
				--		,SUM(ISNULL(V.Importe*-1,0)) AS Importe
					FROM Cajas_Mov_Conceptos CO
						, Cbtes C
						LEFT JOIN Proveedores P ON C.Id_Proveedor = P.Id_Proveedor
						, Cbtes_Tipos T
						, Valores V
						  LEFT JOIN Bancos_Ctas_Mov B  on V.ID_TRANS = B.ID_TRANS 
						                          AND CONVERT(VARCHAR(20),V.Valor_Nro) = CONVERT(VARCHAR(20),B.COMPROBANTE_NRO)
												  AND ABS(V.Importe) = ABS(B.Importe)
						, Valores_Mov M
						, Conceptos O LEFT JOIN Conceptos O1 ON O1.Id_Concepto = O.Id_Concepto_Padre
						LEFT JOIN Conceptos O2 ON O2.Id_Concepto = O1.Id_Concepto_Padre
					WHERE CO.Id_Trans = C.Id_Trans
					AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
					AND CO.Id_Concepto = O.Id_Concepto
					AND C.Id_Estado_Cbte IN (1,2,4)
					AND T.Compromete_Pagos IN (1,-1)
					AND M.Id_Trans = C.Id_Trans
					AND V.Id_Valor = M.ID_VALOR
					AND  V.Id_Tipo_Val Not In ('AJU','DES')
					AND ((V.Id_Tipo_Val In ('CHP','DB') AND B.CONCILIADO  = 1 AND B.CONCILIADO IS Not NULL)
					OR V.Id_Tipo_Val In ('EFE','TAR','FIN') )
					AND DATEPART(YEAR,isnull(B.Fecha_Acreditacion,ISNULL(V.Fecha_Acreditacion,C.fecha))) = @pAnio
					AND DATEPART(MONTH,isnull(B.Fecha_Acreditacion,ISNULL(V.Fecha_Acreditacion,C.fecha))) = @Mes
					AND CO.Id_Periodo_Caja IN (SELECT Id_Periodo_Caja FROM #Cajas_Periodos )
					GROUP BY O.Id_Concepto,O2.Descripcion, O1.Descripcion, O.Descripcion
					ORDER BY O2.Descripcion, O1.Descripcion, O.Descripcion
		END
		ELSE
		BEGIN
			DECLARE MiCurPagos CURSOR FOR 
				SELECT	O.Id_Concepto
						,O2.Descripcion	AS Rubro 
						,O1.Descripcion	AS SubRubro 
						,O.Descripcion	AS Concepto
						,ISNULL(C.Id_Proveedor,0) AS Id_Dest
						,ISNULL(P.Razon_Social,'') AS Proveedor
						,SUM(Abs(ISNULL(CO.Importe,0)) * abs(ISNULL(V.Importe,0))/(ABS(ISNULL(C.Imp_total,0))+ABS(ISNULL(C.Imp_percep,0)))*-1)  AS Importe

--						,SUM(ISNULL(V.Importe*-1,0)) AS Importe
					FROM Cajas_Mov_Conceptos CO
						, Cbtes C
						LEFT JOIN Proveedores P ON C.Id_Proveedor = P.Id_Proveedor
						, Cbtes_Tipos T
						, Valores V
						  LEFT JOIN Bancos_Ctas_Mov B  on V.ID_TRANS = B.ID_TRANS 
						                          AND CONVERT(VARCHAR(20),V.Valor_Nro) = CONVERT(VARCHAR(20),B.COMPROBANTE_NRO)
												  AND ABS(V.Importe) = ABS(B.Importe)
						, Valores_Mov M
						, Conceptos O LEFT JOIN Conceptos O1 ON O1.Id_Concepto = O.Id_Concepto_Padre
						LEFT JOIN Conceptos O2 ON O2.Id_Concepto = O1.Id_Concepto_Padre
					WHERE CO.Id_Trans = C.Id_Trans
					AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
					AND CO.Id_Concepto = O.Id_Concepto
					AND C.Id_Estado_Cbte IN (1,2,4)
					AND T.Compromete_Pagos IN (1,-1)
					AND M.Id_Trans = C.Id_Trans
					AND V.Id_Valor = M.ID_VALOR
					AND  V.Id_Tipo_Val Not In ('AJU','DES')
					AND ((V.Id_Tipo_Val In ('CHP','DB') AND B.CONCILIADO  = 1 AND B.CONCILIADO IS Not NULL)
					OR V.Id_Tipo_Val In ('EFE','TAR','FIN') )
					AND DATEPART(YEAR,isnull(B.Fecha_Acreditacion,ISNULL(V.Fecha_Acreditacion,C.fecha))) = @pAnio
					AND DATEPART(MONTH,isnull(B.Fecha_Acreditacion,ISNULL(V.Fecha_Acreditacion,C.fecha))) = @Mes
					AND CO.Id_Periodo_Caja IN (SELECT Id_Periodo_Caja FROM #Cajas_Periodos )
					GROUP BY O.Id_Concepto,O2.Descripcion, O1.Descripcion, O.Descripcion,C.Id_Proveedor,P.Razon_Social
					ORDER BY O2.Descripcion, O1.Descripcion, O.Descripcion,C.Id_Proveedor,P.Razon_Social
		END
	END --IF @pCircuito IN ('','P')


	OPEN MiCurPagos
	FETCH NEXT FROM MiCurPagos INTO @Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe
	WHILE (@@FETCH_STATUS=0)
	BEGIN
		IF @Mes = 1
		BEGIN
			
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Dest,Destinatario,Mes1)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe)
			ELSE
				UPDATE #Reporte SET Mes1	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest
		END
		IF @Mes = 2
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Dest,Destinatario,Mes2)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe)
			ELSE
				UPDATE #Reporte SET Mes2	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest
		END
		IF @Mes = 3
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Dest,Destinatario,Mes3)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe)
			ELSE
				UPDATE #Reporte SET Mes3	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest
		END
		IF @Mes = 4
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Dest,Destinatario,Mes4)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe)
			ELSE
				UPDATE #Reporte SET Mes4	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest
		END
		IF @Mes = 5
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Dest,Destinatario,Mes5)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe)
			ELSE
				UPDATE #Reporte SET Mes5	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest
		END
		IF @Mes = 6
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Dest,Destinatario,Mes6)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe)
			ELSE
				UPDATE #Reporte SET Mes6	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest
		END
		IF @Mes = 7
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Dest,Destinatario,Mes7)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe)
			ELSE
				UPDATE #Reporte SET Mes7	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest
		END
		IF @Mes = 8
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Dest,Destinatario,Mes8)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe)
			ELSE
				UPDATE #Reporte SET Mes8	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest
		END
		IF @Mes = 9
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Dest,Destinatario,Mes9)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe)
			ELSE
				UPDATE #Reporte SET Mes9	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest
		END
		IF @Mes = 10
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Dest,Destinatario,Mes10)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe)
			ELSE
				UPDATE #Reporte SET Mes10	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest
		END
		IF @Mes = 11
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Dest,Destinatario,Mes11)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe)
			ELSE
				UPDATE #Reporte SET Mes11	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest
		END
		IF @Mes = 12
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Dest,Destinatario,Mes12)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe)
			ELSE
				UPDATE #Reporte SET Mes12	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Dest = @Id_Dest
		END


		FETCH NEXT FROM MiCurPagos INTO @Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Dest,@Destinatario,@Importe
	END
	CLOSE MiCurPagos
	DEALLOCATE MiCurPagos
	SET @Mes += 1
END

--ALTER TABLE #Reporte DROP COLUMN Importe
UPDATE #Reporte SET Total = Mes1 + Mes2 + Mes3 + Mes4 + Mes5 + Mes6 + Mes7 + Mes8 + Mes9 + Mes10 + Mes11 + Mes12
UPDATE #Reporte SET Concepto = '' WHERE Concepto IS NULL
UPDATE #Reporte SET SubRubro = Concepto WHERE SubRubro IS NULL OR SubRubro = ''
UPDATE #Reporte SET Rubro = SubRubro WHERE Rubro IS NULL OR Rubro = ''


IF @pNivel = 'R'
	SELECT Rubro,SUM(Importe)	AS Importe
				,SUM(Mes1 )		AS Mes1 
				,SUM(Mes2 )		AS Mes2 
				,SUM(Mes3 )		AS Mes3 
				,SUM(Mes4 )		AS Mes4 
				,SUM(Mes5 )		AS Mes5 
				,SUM(Mes6 )		AS Mes6 
				,SUM(Mes7 )		AS Mes7 
				,SUM(Mes8 )		AS Mes8 
				,SUM(Mes9 )		AS Mes9 
				,SUM(Mes10)		AS Mes10
				,SUM(Mes11)		AS Mes11
				,SUM(Mes12)		AS Mes12
				,SUM(Total)		AS Total
			FROM #Reporte GROUP BY Rubro ORDER BY Rubro
ELSE IF @pNivel = 'S'
	SELECT Rubro,SubRubro,SUM(Importe)	AS Importe
				,SUM(Mes1 )		AS Mes1 
				,SUM(Mes2 )		AS Mes2 
				,SUM(Mes3 )		AS Mes3 
				,SUM(Mes4 )		AS Mes4 
				,SUM(Mes5 )		AS Mes5 
				,SUM(Mes6 )		AS Mes6 
				,SUM(Mes7 )		AS Mes7 
				,SUM(Mes8 )		AS Mes8 
				,SUM(Mes9 )		AS Mes9 
				,SUM(Mes10)		AS Mes10
				,SUM(Mes11)		AS Mes11
				,SUM(Mes12)		AS Mes12
				,SUM(Total)		AS Total
			FROM #Reporte GROUP BY Rubro,SubRubro ORDER BY Rubro,SubRubro
ELSE
	SELECT * FROM #Reporte ORDER BY Rubro,SubRubro,Concepto,Destinatario
	

DROP TABLE #Reporte


/*

EXEC CPJ_Conceptos_Rpt_Anual_lh  '{"Anio":"2024","Circuito":"n"}'

EXEC CPJ_Conceptos_Rpt_Anual  '{"Anio":"2024","Circuito":"N","Flag_Apertura":"0","Nivel":"S"}'


*/





