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

CREATE  PROCEDURE dbo.CP_CtrlPresup 
		 @Fec_Desde		DATETIME	--  PERIODO A SOLICITAR
		,@Fec_Hasta		DATETIME    --	PERIODO A SOLICITAR
		,@Centro_Costo	VARCHAR(10) --	CODIGO DEL CENTRO DE COSTO A SOLICITAR / VACIO PARA TODOS
		,@Detalle		VARCHAR(1)  --	1 = DETALLADO (POR DEFECTO), 2 = CENTRO COSTO, 
									--	3 = CUENTA CONTABLE, 4 = PARTIDA PRESUPUESTARIA
AS
begin

/*DECLARE */
		
DECLARE @C_Costo_D varchar(25)
		,@C_Costo_H varchar(25)

IF ISNULL(@Detalle,'') = '' SET @Detalle = '1'
		
if ISNULL(@centro_costo,'') = ''
begin
	SET @C_Costo_D = ''
	SET @C_Costo_H = 'ÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞ'
end
else
begin
	SET @C_Costo_D = @centro_costo 
	SET @C_Costo_H = @centro_costo 
end;
IF OBJECT_ID (N'tempdb..#Centros_c', N'U') IS NOT NULL 	DROP TABLE #Centros_c;
WITH TCentros(id_centro_costo,id_centro_costo_padre,descripcion,level) AS
    (
        SELECT top 1 CC1.id_centro_costo,CC1.id_centro_costo_padre,CC1.descripcion,0 as level
        FROM dbo.centro_costos CC1 
        WHERE CC1.id_centro_costo = @centro_costo
        UNION ALL
        SELECT top 100 percent CC2.id_centro_costo,CC2.id_centro_costo_padre,CC2.descripcion,level + 1
        FROM dbo.centro_costos CC2 
        INNER JOIN TCentros AS CC3 ON CC3.id_centro_costo = CC2.id_centro_costo_padre  
        WHERE CC2.id_centro_costo <> @centro_costo 
    )
 
    SELECT TOP 100 PERCENT TC.id_centro_costo,TC.id_centro_costo_padre,TC.descripcion,level
    INTO #Centros_c FROM TCentros TC
    WHERE id_centro_costo BETWEEN @C_Costo_D AND @C_Costo_H OR
			id_centro_costo_padre BETWEEN @C_Costo_D AND @C_Costo_H
    ORDER BY id_centro_costo, id_centro_costo_padre

IF OBJECT_ID (N'tempdb..#AUX01', N'U') IS NOT NULL 	DROP TABLE #AUX01;
SELECT '1' AS LINEA,Id_Presup_Tit ,PRESUPUESTO ,Nro_Cta_Contable ,Descripcion,Id_Centro_Costo,/* Padre,*/ Des_Padre,C_Costo
,SUM(DEBE) AS DEBE, SUM(HABER) AS HABER, SUM(DEBE-HABER) AS MONTO 
INTO #AUX01 FROM (select PCCC.Id_Presup_Tit,PCP.Descripcion AS PRESUPUESTO
				, ai.Id_Asiento ,AI.Item,CCC.Id_Cta_Contable,PCCC.Nro_Cta_Contable ,PCCC.Descripcion
				,CB.Id_Centro_Costo,ccost.id_centro_costo_padre as Padre ,ccost.descripcion  as Des_Padre 
				,ccost.Descripcion as C_Costo,CCC.FECHA,AI.Id_Alias_Cta_Cont,AI.Debe,AI.Haber,CB.Id_Trans,CB.Id_Tipo_Cbte     
				,CB.Fecha AS FECHA_VEN, CB.Periodo_Iva 
			,CASE WHEN CBT.Compromete_Factura IN (-1,1) OR CBT.Compromete_Recibos IN (-1,1) THEN CB.Fecha ELSE CB.Periodo_Iva END AS FECHA_DOC
 from asientos asi, Asientos_Items ai, Cbtes_Ctas_Contables ccc, Pla_Ctas_Cuentas_Contables PCCC,CBTES CB
,#Centros_c ccost, Plan_Ctas_Presupuestos_Tit PCP, Cbtes_Tipos CBT
where ISNULL(ccc.id_asiento,0) != 0
AND Asi.BALANceado=1
and asi.id_asiento=ai.id_asiento
AND AI.ID_ASIENTO=CCC.Id_Asiento 
AND CCC.Id_Cta_Contable = PCCC.Id_Cta_Contable 
AND CCC.Id_Cta_Contable = AI.Id_Alias_Cta_Cont 
AND ISNULL(PCCC.Id_Presup_Tit,0) !=0
AND CB.Id_Trans = CCC.Id_Trans 
AND CB.Id_Estado_Cbte IN (1,2,4)
AND CB.Id_Centro_Costo = ccost.Id_Centro_Costo 
AND PCCC.Id_Presup_Tit = PCP.Id_Presup_Tit
AND (CB.Fecha BETWEEN @FEC_DESDE AND @FEC_HASTA OR CB.Periodo_Iva BETWEEN @FEC_DESDE AND @FEC_HASTA)
AND CB.Id_Tipo_Cbte = CBT.Id_Tipo_Cbte 
AND ((CBT.Compromete_Factura IN (1,-1) OR CBT.Compromete_Recibos IN (1,-1) OR
 CBT.Compromete_Pagos IN (-1,1) OR CBT.Compromete_Compras IN (-1,1)) AND (CBT.Compromete_Asientos IN (1,-1))) 
) res
where Id_Centro_Costo BETWEEN @C_Costo_D AND @C_Costo_H  
GROUP BY Id_Presup_Tit ,PRESUPUESTO ,Nro_Cta_Contable ,Descripcion,Id_Centro_Costo,Des_Padre  ,C_Costo
 order by 1

IF OBJECT_ID (N'tempdb..#AUX02', N'U') IS NOT NULL 	DROP TABLE #AUX02;
SELECT '2' AS LINEA,Id_Presup_Tit, MAX(PRESUPUESTO) AS PRESUPUESTO
		,Nro_Cta_Contable,MAX(Descripcion) AS DESCRIPCION
		,max(Id_Centro_Costo) as id_centro_costo,MAX(Des_Padre ) as des_padre
		,('TOTAL '+MAX(PRESUPUESTO)+' / '+MAX(Descripcion)+' / '+MAX(C_Costo)) AS C_COSTO
		,SUM(DEBE) AS DEBE,SUM(HABER) AS HABER,SUM(DEBE-HABER) AS MONTO
		INTO #AUX02 FROM #AUX01 GROUP BY Id_Presup_Tit ,Nro_Cta_Contable ,Des_Padre   

IF OBJECT_ID (N'tempdb..#AUX03', N'U') IS NOT NULL 	DROP TABLE #AUX03;
SELECT '3' AS LINEA,Id_Presup_Tit, ('SUB-TOTAL '+RTRIM(MAX(PRESUPUESTO))+' / '+MAX(Descripcion)) AS PRESUPUESTO
		,Nro_Cta_Contable,'SUB-TOTAL '+RTRIM(MAX(PRESUPUESTO))+' / '+RTRIM(MAX(Descripcion)) AS DESCRIPCION
		,MAX(Id_Centro_Costo) AS ID_CENTRO_COSTO,MAX(des_padre) as des_padre
		,('TOTAL '+MAX(PRESUPUESTO)+' / '+MAX(Descripcion)+' / '+MAX(C_Costo)) AS C_COSTO
		,SUM(DEBE) AS DEBE,SUM(HABER) AS HABER,SUM(DEBE-HABER) AS MONTO
		INTO #AUX03 FROM #AUX01 GROUP BY Id_Presup_Tit,Nro_Cta_Contable 

IF OBJECT_ID (N'tempdb..#AUX04', N'U') IS NOT NULL 	DROP TABLE #AUX04;
SELECT '4' AS LINEA,Id_Presup_Tit, ('TOTAL '+RTRIM(MAX(PRESUPUESTO))) AS PRESUPUESTO
		,999999 AS NRO_CTA_CONTABLE,'TOTAL '+RTRIM(MAX(PRESUPUESTO))+' / '+RTRIM(MAX(Descripcion)) AS DESCRIPCION
		,999999 AS ID_CENTRO_COSTO,MAX(des_padre) as des_padre
		,('TOTAL '+MAX(PRESUPUESTO)+' / '+MAX(Descripcion)) AS C_COSTO
		,SUM(DEBE) AS DEBE,SUM(HABER) AS HABER,SUM(DEBE-HABER) AS MONTO
		INTO #AUX04 FROM #AUX01 GROUP BY Id_Presup_Tit 

IF OBJECT_ID (N'tempdb..#AUX05', N'U') IS NOT NULL 	DROP TABLE #AUX05;
SELECT '5' AS LINEA,999999 AS Id_Presup_Tit, ('TOTAL EJECUCION PRESUPUESTARIA') AS PRESUPUESTO
		,999999 AS NRO_CTA_CONTABLE,'TOTAL EJECUCION PRESUPUESTARIA' AS DESCRIPCION
		,999999 AS ID_CENTRO_COSTO,'TOTAL EJECUCION PRESUPUESTARIA' as des_padre
		,('TOTAL EJECUCION PRESUPUESTARIA') AS C_COSTO
		,SUM(DEBE) AS DEBE,SUM(HABER) AS HABER,SUM(DEBE-HABER) AS MONTO
		INTO #AUX05 FROM #AUX01  


IF @Detalle='1'	
	SELECT * FROM #AUX01 UNION SELECT * FROM #AUX02 UNION SELECT * FROM #AUX03	UNION SELECT * FROM #AUX04 UNION SELECT * FROM #AUX05	
	ORDER BY Id_Presup_Tit ,Nro_Cta_Contable  ,Des_Padre,Id_Centro_Costo,linea

IF @Detalle='2'
	SELECT * FROM #AUX02 UNION SELECT * FROM #AUX03	UNION SELECT * FROM #AUX04 UNION SELECT * FROM #AUX05	
	ORDER BY Id_Presup_Tit ,Nro_Cta_Contable  ,Des_Padre,Id_Centro_Costo,linea

IF @Detalle='3'
	SELECT * FROM #AUX03	UNION SELECT * FROM #AUX04 UNION SELECT * FROM #AUX05	
	ORDER BY Id_Presup_Tit ,Nro_Cta_Contable  ,Des_Padre,Id_Centro_Costo,linea

IF @Detalle='4'
	SELECT * FROM #AUX04 UNION SELECT * FROM #AUX05	
	ORDER BY Id_Presup_Tit ,Nro_Cta_Contable  ,Des_Padre,Id_Centro_Costo,linea

DROP TABLE #AUX01 
DROP TABLE #AUX02 
DROP TABLE #AUX03 
DROP TABLE #AUX04

end
 



