Inversiones/Recursos/SP_SBO_DispBanc_Master.sql

USE [Inversiones]
GO
/****** Object:  StoredProcedure [dbo].[SP_SBO_DispBanc_Master]    Script Date: 05/13/2019 18:49:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Bladimir Cortez
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[SP_SBO_DispBanc_Master] 
	-- Add the parameters for the stored procedure here
	@Agrupacion varchar(4) = 'E'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @CodEmpr varchar(50),@NombreEmpr varchar(100), @EMPRESA_DB nvarchar(100)
DECLARE @SQL nVarchar(4000)

DECLARE @DISP_BANC TABLE(
	CodEmpr varchar(50)
	,NombreEmpresa varchar(100)
	,BankCode nVarchar(30)
	,BankName nVarchar(250)
	,Account nVarchar(50)
	,GLAccount nVarchar(15)
	,Segment_0 nVarchar(20)
	,Segment_1 nVarchar(20)
	,AcctName nVarchar(100)
	,Country nVarChar(3)
	,CountryName nVarChar(100)
	,Currency nVarChar(3)
	,CurrTotal numeric(19,6)
	,CurrTotalSYS numeric(19,6)
	,Saldo numeric(19,6)
	,SaldoSYS numeric(19,6)

)


DECLARE EMPRESAS_CUR CURSOR STATIC FOR
SELECT
	T0.CodEmpr
	,T0.Descrip 'NombreEmpr'
	,T0.[SAP_DB]
  FROM [dbo].[EMPR] T0
  WHERE LEN(ISNULL(T0.SAP_DB,''))>0

OPEN EMPRESAS_CUR
FETCH NEXT FROM EMPRESAS_CUR INTO @CodEmpr,@NombreEmpr ,@EMPRESA_DB
WHILE @@fetch_status = 0
BEGIN
	PRINT @EMPRESA_DB
	IF EXISTS (SELECT COUNT(1) FROM master.sys.databases WHERE name=@EMPRESA_DB)
	BEGIN
		PRINT 'DEMOLE'
		SET @SQL='
		SELECT
			'+CHAR(39)+@CodEmpr+CHAR(39)+'
			,'+CHAR(39)+@NombreEmpr+CHAR(39)+'
			,T1.BankCode
			,(SELECT S0.BankName FROM ['+@EMPRESA_DB+'].[dbo].[ODSC] S0 WHERE S0.BankCode = T1.BankCode)
			,T1.Account
			,T1.GLAccount
			,T2.Segment_0
			,T2.Segment_1
			,T2.AcctName
			,T1.Country
			,(SELECT S0.Name FROM ['+@EMPRESA_DB+'].[dbo].[OCRY] S0 WHERE S0.Code = T1.Country)
			,T1.Currency
			,T2.CurrTotal
		FROM ['+@EMPRESA_DB+'].[dbo].[DSC1] T1
		INNER JOIN ['+@EMPRESA_DB+'].[dbo].[OACT] T2 ON T2.AcctCode = T1.GLAccount
		WHERE T2.FrozenFor=''N'''

		INSERT INTO @DISP_BANC(
			CodEmpr
			,NombreEmpresa
			,BankCode
			,BankName
			,Account
			,GLAccount
			,Segment_0
			,Segment_1
			,AcctName
			,Country
			,CountryName
			,Currency
			,CurrTotal
		) EXEC(@SQL)


	END
	ELSE
	BEGIN
		PRINT 'NADA'
	END

FETCH NEXT FROM EMPRESAS_CUR INTO @CodEmpr,@NombreEmpr ,@EMPRESA_DB
END
CLOSE EMPRESAS_CUR
DEALLOCATE EMPRESAS_CUR

SELECT * FROM @DISP_BANC

--IF @Agrupacion = 'D' --DETALLADO
--BEGIN
--	SELECT * FROM @DISP_BANC
--END

--IF @Agrupacion = 'E' --DISPONIBILIDAD POR EMPRESA
--BEGIN
--	SELECT
--	T0.CodEmpr
--	,(SELECT S0.Descrip FROM [dbo].[EMPR] S0 WHERE S0.CodEmpr = T0.CodEmpr) 'NombreEmpr'
--	,SUM(T0.CurrTotal) 'DispContable'
--	FROM @DISP_BANC T0
--	GROUP BY T0.CodEmpr
--END
--IF @Agrupacion = 'P' --DISPONIBILIDAD POR PAIS
--BEGIN
--	SELECT T0.Country ,SUM(T0.CurrTotal) 'CurrTotal'
--	FROM @DISP_BANC T0
--	GROUP BY T0.Country

--END
--IF @Agrupacion = 'EP' --DISPONIBILIDAD POR EMPRESA
--BEGIN
--	SELECT T0.CodEmpr,T0.Country ,SUM(T0.CurrTotal) 'CurrTotal'
--	FROM @DISP_BANC T0
--	GROUP BY T0.CodEmpr,T0.Country
--	ORDER BY T0.CodEmpr
--END

--SELECT T0.CodEmpr
--, SUM(T0.CurrTotal) 'CurrTotal'
--FROM @DISP_BANC T0
--GROUP BY T0.CodEmpr
--ORDER BY T0.CodEmpr

--SELECT T0.* FROM @DISP_BANC T0 WHERE T0.CodEmpr='INV'

-- PAIS

--DECLARE @CodPais varchar(3)

--PRINT '@CodEmpr ' + @CodEmpr
--SELECT T0.* FROM @DISP_BANC T0 WHERE T0.Country=ISNULL(@CodEmpr,T0.CodEmpr)

--SELECT T0.* FROM @DISP_BANC T0








END