balances/SBO_Balance_Comprobacion_Todas.sql

USE [SAP_INVERLEC_ES_PROGRAMACION_5]
GO
/****** Object:  StoredProcedure [dbo].[SBO_Balance_Comprobacion_Todas]    Script Date: 04/17/2018 15:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Amarelis Ortiz Cañas
-- Create date: 22/02/2018
---modificado: 26/02/2018
-- Description:	Balance de Comprobacion LEGAL NUEVO 
/*
Con parametros de entrada Fecha Inicial y Final,
sin bucles anidados para Guatemala
*/
--============================================================
ALTER PROCEDURE [dbo].[SBO_Balance_Comprobacion_Todas] 
	-- Add the parameters for the stored procedure here
	@FechaI  DateTime
	,@FechaF  DateTime
	,@TIPO_REPORTE INT
	--0: REPORTE NORMAL (TODAS LAS CUENTAS), 1: REPORTE CON SALDOS A LA FECHA, 2: REPORTE LEGAL
	--, @Nivel int --(nada hace solo referencia)
	,@IncPrelim nvarchar(1)='N' ----S incluye preliminares, N no incluye preliminares, P solo preliminares

AS
BEGIN

Declare
	@CuentaINI nvarchar(100)
	,@CuentaFIN nvarchar(100)
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
set @CuentaINI = (select top 1 AcctName from OACT order by AcctCode ASC) 
set @CuentaFIN = (select top 1 AcctName from OACT order by AcctCode DESC) 
-----------------------------------------------------------------------------------	
    -- Insert statements for procedure here
	DECLARE @Balance_Comprobacion 
	TABLE(Codigo varchar(25)--, Nombre_Cuenta varchar(100)
	, Saldo_IniL numeric(20,2), DebitosL numeric(20,2)
	, CreditosL numeric(20,2), Saldo_FinL numeric(20,2)
	, Saldo_IniS numeric(20,2), DebitosS numeric(20,2)
	, CreditosS numeric(20,2), Saldo_FinS numeric(20,2), Mascara int)  
-----------------------------------------------------------------------------
	DECLARE @Balance_Tipo 
	TABLE(Codigo varchar(25), Cuenta varchar(25),Nombre_Cuenta varchar(100)
	, Saldo_IniL numeric(20,2), DebitosL numeric(20,2)
	, CreditosL numeric(20,2), Saldo_FinL numeric(20,2)
	, Saldo_IniS numeric(20,2), DebitosS numeric(20,2)
	, CreditosS numeric(20,2), Saldo_FinS numeric(20,2))  
------------------------------------------------------------------------------	
declare @Registros int
Declare @FechaIni date, @FechaFin date
	SET @FechaIni = convert(date, convert(varchar, YEAR(@FechaI))+'-01-01')
	set @FechaFin = convert(date, convert(varchar, YEAR(@FechaI))+'-01-31')
-------------------PARA SABER SI INICIA OPERACIONES UNA EMPRESA--------------
set @Registros = (select COUNT('A')
	FROM  [JDT1] T0  
		INNER  JOIN [dbo].[OJDT] T1  ON  T1.[TransId] = T0.[TransId]
		INNER JOIN [dbo].[OACT] T2 ON T0.Account = T2.AcctCode    
		WHERE 
		T0.[RefDate] < @FechaI 
		 AND  T0.[TransType] <> ('-3')  
		AND  (T0.[SourceLine] <> (-8)  OR  T0.[SourceLine] IS NULL  ) )

-----------SALDOS INICIALES----------------------------------------------------
IF Upper(@IncPrelim)<>'P'
BEGIN
-------------------PARA EMPRESAS QUE NO HAN INICIADO OPERACIONES--------------
if @Registros = 0 
	BEGIN
	INSERT @Balance_Comprobacion 
	SELECT 
	 T0.[Account]
     ,CASE
            WHEN T2.GroupMask IN (1) THEN SUM(T0.[Debit]-T0.[Credit])
            WHEN T2.GroupMask IN (2,3) THEN SUM(T0.[Credit]-T0.[Debit])
      else 0.00
      END 'SaldoIniL'
   	, 0.00 AS DebitosL   
	, 0.00 AS CreditosL   
	, 0.00 AS Saldos_FinL
     ,CASE
            WHEN T2.GroupMask IN (1) THEN SUM(T0.[SYSDeb]-T0.[SYSCred])
            WHEN T2.GroupMask IN (2,3) THEN SUM(T0.[SYSCred]-T0.[SYSDeb])
      else 0.00
      END 'SaldoIniS'
   	,0.00 AS DebitosS   
	, 0.00 AS CreditosS   
	, 0.00 AS Saldos_FinS
	, T2.GroupMask
	FROM  [dbo].[JDT1] T0  
		INNER  JOIN [dbo].[OJDT] T1  ON  T1.[TransId] = T0.[TransId]
		INNER JOIN [dbo].[OACT] T2 ON T0.Account = T2.AcctCode    
		WHERE 
		T0.[RefDate] >= @FechaIni  AND  T0.[RefDate] <= @FechaFin
		AND  T0.[TransType] <> (-3)  AND  (T0.[SourceLine] <> (-8)  
		OR  T0.[SourceLine] IS NULL  ) AND  T0.[TransType] = (-2)   
		AND T2.GroupMask IN (1,2,3)
	GROUP BY T0.[Account], T2.GroupMask
	END
	ELSE
	BEGIN
	INSERT @Balance_Comprobacion 
	SELECT 
	 T0.[Account]
     ,CASE
            WHEN T2.GroupMask IN (1) THEN SUM(T0.[Debit]-T0.[Credit])
            WHEN T2.GroupMask IN (2,3) THEN SUM(T0.[Credit]-T0.[Debit])
      else 0.00
      END 'SaldoIniL'
   	, 0.00 AS DebitosL   
	, 0.00 AS CreditosL   
	, 0.00 AS Saldos_FinL
     ,CASE
            WHEN T2.GroupMask IN (1) THEN SUM(T0.[SYSDeb]-T0.[SYSCred])
            WHEN T2.GroupMask IN (2,3) THEN SUM(T0.[SYSCred]-T0.[SYSDeb])
      else 0.00
      END 'SaldoIniS'
   	,0.00 AS DebitosS   
	, 0.00 AS CreditosS   
	, 0.00 AS Saldos_FinS
	, T2.GroupMask
	FROM  [dbo].[JDT1] T0  
		INNER  JOIN [dbo].[OJDT] T1  ON  T1.[TransId] = T0.[TransId]
		INNER JOIN [dbo].[OACT] T2 ON T0.Account = T2.AcctCode    
		WHERE 
		T0.[RefDate]  < @FechaI 
		 AND  T0.[TransType] <> (N'-3')  
		AND  (T0.[SourceLine] <> (-8)  OR  T0.[SourceLine] IS NULL  )  
		AND T2.GroupMask IN (1,2,3)
	GROUP BY T0.[Account], T2.GroupMask
	END
-------------------ESTADO DE RESULTADOS--------------------------------
	INSERT @Balance_Comprobacion 
	SELECT 
	 T0.[Account]
     ,CASE
            WHEN T2.GroupMask IN (5,6,8) THEN SUM(T0.[Debit]-T0.[Credit])
            WHEN T2.GroupMask IN (4,7) THEN SUM(T0.[Credit]-T0.[Debit])
      else 0.00
      END 'SaldoIniL'
   	, 0.00 AS DebitosL   
	, 0.00 AS CreditosL   
	, 0.00 AS Saldos_FinL
     ,CASE
            WHEN T2.GroupMask IN (5,6,8) THEN SUM(T0.[SYSDeb]-T0.[SYSCred])
            WHEN T2.GroupMask IN (4,7) THEN SUM(T0.[SYSCred]-T0.[SYSDeb])
      else 0.00
      END 'SaldoIniS'
   	,0.00 AS DebitosS   
	, 0.00 AS CreditosS   
	, 0.00 AS Saldos_FinS
	, T2.GroupMask
	FROM  [dbo].[JDT1] T0  
		INNER  JOIN [dbo].[OJDT] T1  ON  T1.[TransId] = T0.[TransId]
		INNER JOIN [dbo].[OACT] T2 ON T0.Account = T2.AcctCode    
		WHERE 
		T0.[RefDate] >= @FechaIni  AND  T0.[RefDate] <= @FechaI
		 AND  T0.[TransType] <> (N'-3')  
		AND  (T0.[SourceLine] <> (-8)  OR  T0.[SourceLine] IS NULL  )  
		AND T2.GroupMask IN (4,5,6,7,8)
	GROUP BY T0.[Account], T2.GroupMask	
-------------------------------------------------------
	INSERT @Balance_Comprobacion 
 --select * from @Balance_Comprobacion
------------------------CREDITOS Y DEBITOS-----------------------
	SELECT
      T0.[Account]
      , 0.00 'SaldoIniL'
      , SUM(T0.[Debit])
      , SUM(T0.[Credit])
      , 0.00 AS Saldos_FinL
      , 0.00 'SaldoIniS'
	  , SUM(T0.[SYSDeb]) 
	  , SUM(T0.[SYSCred])
      , 0.00 AS Saldos_FinS
	  ,t2.GroupMask
	FROM  [dbo].[JDT1] T0  
	INNER  JOIN [dbo].[OJDT] T1  ON  T1.[TransId] = T0.[TransId]   
	INNER JOIN [dbo].[OACT] T2 ON T0.Account = T2.AcctCode 
	WHERE
     T0.[RefDate] >= @FechaI AND  T0.[RefDate] <= @FechaF
	AND  T0.[TransType] <> (-3)  AND  (T0.[SourceLine] <> (-8)  
	OR  T0.[SourceLine] IS NULL  ) AND  T0.[TransType] <> (-2)  
	GROUP BY 
      T0.[Account]
	,t2.GroupMask
END
---------------------------------------------------------------------------------
---------------------PRELIMINARES-------------------------------------------------	
IF Upper(@IncPrelim)<>'N' 
BEGIN
	if @Registros = 0 
	BEGIN
	INSERT @Balance_Comprobacion    
	SELECT
      T0.[Account]
     ,CASE
            WHEN T2.GroupMask IN (1) THEN SUM(T0.[Debit]-T0.[Credit])
            WHEN T2.GroupMask IN (2,3) THEN SUM(T0.[Credit]-T0.[Debit])
      else 0.00
      END 'SaldoIniL'
   	, 0.00 AS DebitosL   
	, 0.00 AS CreditosL   
	, 0.00 AS Saldos_FinL
     ,CASE
            WHEN T2.GroupMask IN (1) THEN SUM(T0.[SYSDeb]-T0.[SYSCred])
            WHEN T2.GroupMask IN (2,3) THEN SUM(T0.[SYSCred]-T0.[SYSDeb])
      else 0.00
      END 'SaldoIniS'
   	,0.00 AS DebitosS   
	, 0.00 AS CreditosS   
	, 0.00 AS Saldos_FinS
	,t2.GroupMask
	FROM [BTF1] T0
		INNER JOIN [OBTF] T1 ON T0.[BatchNum] = T1.[BatchNum]
		JOIN [OACT] T2 ON T0.Account = T2.AcctCode
	WHERE
		T0.[RefDate] >= @FechaI  AND  T0.[RefDate] <= @FechaF AND  
		T0.[TransType] <> (-3)  AND  (T0.[SourceLine] <> (-8)  
		OR  T0.[SourceLine] IS NULL  ) AND  T0.[TransType] = (-2)   
		AND  T0.[TransId] = T1.[TransId]  
		AND  T1.[BtfStatus] <> ('C')
		AND T2.GroupMask IN (1,2,3)
	GROUP BY
      T0.[Account],
      T2.GroupMask
	END
	ELSE
	BEGIN
	INSERT @Balance_Comprobacion    
	SELECT
      T0.[Account]
     ,CASE
            WHEN T2.GroupMask IN (1) THEN SUM(T0.[Debit]-T0.[Credit])
            WHEN T2.GroupMask IN (2,3) THEN SUM(T0.[Credit]-T0.[Debit])
      else 0.00
      END 'SaldoIniL'
   	, 0.00 AS DebitosL   
	, 0.00 AS CreditosL   
	, 0.00 AS Saldos_FinL
     ,CASE
            WHEN T2.GroupMask IN (1) THEN SUM(T0.[SYSDeb]-T0.[SYSCred])
            WHEN T2.GroupMask IN (2,3) THEN SUM(T0.[SYSCred]-T0.[SYSDeb])
      else 0.00
      END 'SaldoIniS'
   	,0.00 AS DebitosS   
	, 0.00 AS CreditosS   
	, 0.00 AS Saldos_FinS
	,t2.GroupMask
	FROM
		[BTF1] T0
		INNER JOIN [OBTF] T1 ON T0.[BatchNum] = T1.[BatchNum]
		JOIN [OACT] T2 ON T0.Account = T2.AcctCode
	WHERE
		T0.[RefDate]  < @FechaI 
		 AND  T0.[TransType] <> (N'-3')  
		AND  (T0.[SourceLine] <> (-8)  OR  T0.[SourceLine] IS NULL  )  
		AND  T0.[TransId] = T1.[TransId]  
		AND  T1.[BtfStatus] <> ('C')
		AND T2.GroupMask IN (1,2,3)
	GROUP BY
      T0.[Account],
      T2.GroupMask
	END
-------------------ESTADO DE RESULTADOS--------------------------------
	INSERT @Balance_Comprobacion 
	SELECT 
	 T0.[Account]
     ,CASE
            WHEN T2.GroupMask IN (5,6,8) THEN SUM(T0.[Debit]-T0.[Credit])
            WHEN T2.GroupMask IN (4,7) THEN SUM(T0.[Credit]-T0.[Debit])
      else 0.00
      END 'SaldoIniL'
   	, 0.00 AS DebitosL   
	, 0.00 AS CreditosL   
	, 0.00 AS Saldos_FinL
     ,CASE
            WHEN T2.GroupMask IN (5,6,8) THEN SUM(T0.[SYSDeb]-T0.[SYSCred])
            WHEN T2.GroupMask IN (4,7) THEN SUM(T0.[SYSCred]-T0.[SYSDeb])
      else 0.00
      END 'SaldoIniS'
   	,0.00 AS DebitosS   
	, 0.00 AS CreditosS   
	, 0.00 AS Saldos_FinS
	, T2.GroupMask
	FROM
		[BTF1] T0
		INNER JOIN [OBTF] T1 ON T0.[BatchNum] = T1.[BatchNum]
		JOIN [OACT] T2 ON T0.Account = T2.AcctCode
		WHERE 
		T0.[RefDate] >= @FechaIni  AND  T0.[RefDate] <= @FechaI
		 AND  T0.[TransType] <> (N'-3')  
		AND  (T0.[SourceLine] <> (-8)  OR  T0.[SourceLine] IS NULL  )  
		AND  T0.[TransId] = T1.[TransId]  
		AND  T1.[BtfStatus] <> ('C')
		AND T2.GroupMask IN (4,5,6,7,8)
	GROUP BY T0.[Account], T2.GroupMask	
-------------------------------------------------------
	
 --select * from @Balance_Comprobacion
------------------------CREDITOS Y DEBITOS-----------------------
	INSERT @Balance_Comprobacion    
	SELECT
      T0.[Account]
      , 0.00 'SaldoIniL'
       , SUM(T0.[Debit])
		, SUM(T0.[Credit])
      , 0.00 AS Saldos_FinL
      , 0.00 'SaldoIniS'
	  , SUM(T0.[SYSDeb]) 
	  , SUM(T0.[SYSCred])
      , 0.00 AS Saldos_FinS
	  ,t2.GroupMask
	FROM  
		[BTF1] T0
		INNER JOIN [OBTF] T1 ON T0.[BatchNum] = T1.[BatchNum]
		JOIN [OACT] T2 ON T0.Account = T2.AcctCode
	WHERE
     T0.[RefDate] >= @FechaI AND  T0.[RefDate] <= @FechaF
	AND  T0.[TransType] <> (-3)  AND  (T0.[SourceLine] <> (-8)  
	OR  T0.[SourceLine] IS NULL  ) AND  T0.[TransType] <> (-2)  
		AND  T0.[TransId] = T1.[TransId]  
		AND  T1.[BtfStatus] <> ('C')
		--AND T2.GroupMask IN (1,2,3)
	GROUP BY 
      T0.[Account]
	,t2.GroupMask
END
------------------------------------------------------------------------------------------------
select 
	Codigo
	,(select Segment_0 from OACT where AcctCode = Codigo) 'Cuenta'
	,(select AcctName from OACT where AcctCode = Codigo) 'NombredeCuenta'
	, sum(Saldo_IniL)'Saldo_IniL'
	, sum(DebitosL) 'DebitosL'
	, sum(CreditosL) 'CreditosL'
    ,CASE
           WHEN Mascara IN (1,5,6,8) THEN (sum(Saldo_IniL)+sum(DebitosL)-sum(CreditosL))
           WHEN Mascara IN (2,3,4,7) THEN (sum(Saldo_IniL)+sum(CreditosL)-sum(DebitosL))*-1
     END 'Saldo_FinL'
	, sum(Saldo_IniS)'Saldo_IniS'
	, sum(DebitosS) 'DebitosS'
	, sum(CreditosS) 'CreditosS'
    ,CASE
           WHEN Mascara IN (1,5,6,8) THEN (sum(Saldo_IniS)+sum(DebitosS)-sum(CreditosS))
           WHEN Mascara IN (2,3,4,7) THEN (sum(Saldo_IniS)+sum(CreditosS)-sum(DebitosS))*-1
     END 'Saldo_FinS'
into #TEMP1
from @Balance_Comprobacion
	--where Mascara = @Nivel
	Group by
		Codigo
		, Mascara
	order by Cuenta

--------------------------------------------------------------------------------------------	
---------CUENTAS FALTANTES------------------------------------------------------------------		
INSERT INTO #TEMP1
select B.AcctCode,B.Segment_0,B.AcctName
	,0.00 
	,0.00 
	,0.00 
	,0.00 
	,0.00 
	,0.00 
	,0.00 
	,0.00 
	from  OACT B 
	WHERE
		B.FormatCode IS NOT NULL
		AND B.AcctCode NOT IN(SELECT Codigo FROM @Balance_Comprobacion)
		
--SELECT * FROM #TEMP1
--------------------------------------------------------------------------------------
IF @TIPO_REPORTE =0 
INSERT INTO @Balance_Tipo
	select * 
	from #TEMP1
else 
INSERT INTO @Balance_Tipo
	select * 
	from #TEMP1
	WHERE 
		abs(Saldo_IniL)+ abs(DebitosL)+abs(CreditosL)+ abs(Saldo_FinL) <>0
		or abs(Saldo_IniS)+ abs(DebitosS)+abs(CreditosS)+ abs(Saldo_FinS) <>0

------------------------------------------------------------------------------------------

--IF @TIPO_REPORTE =1
--INSERT INTO @Balance_Tipo
--	select * 
--	from #TEMP1
--	WHERE
--	Codigo IN (SELECT A.AcctCode FROM OACT A WHERE A.CurrTotal<>0)
------------------------------------------------------------------------------------------
	SELECT
		LEFT(N1.AcctCode,1) 'AcctCodeN1'
		,T0.Codigo
		,N1.AcctName 'AcctNameN1'
		,N2.AcctCode 'AcctCodeN2'
		,N2.AcctName 'AcctNameN2'
		,N3.AcctCode 'AcctCodeN3'
		,N3.AcctName 'AcctNameN3'
		,N4.AcctCode 'AcctCodeN4'
		,N4.AcctName 'AcctNameN4'
		,N5.FormatCode
		,T0.Cuenta
		,T0.Nombre_Cuenta
		,T0.Saldo_IniL
		,T0.DebitosL
		,T0.CreditosL
		, T0.Saldo_FinL
		,T0.Saldo_IniS
		,T0.DebitosS
		,T0.CreditosS
		, T0.Saldo_FinS	
		FROM @Balance_Tipo T0
		INNER JOIN OACT N5 ON T0.Codigo=N5.AcctCode
		INNER JOIN OACT N4 ON N4.AcctCode=N5.FatherNum
		INNER JOIN OACT N3 ON N3.AcctCode=N4.FatherNum
		INNER JOIN OACT N2 ON N2.AcctCode=N3.FatherNum
		INNER JOIN OACT N1 ON N1.AcctCode=N2.FatherNum
-------------------------------------------------------	

DROP TABLE #Temp1
end