Inversiones/Recursos/SP_Ingresos_Master_20190710.sql

USE [Inversiones_20190711]
GO
/****** Object:  StoredProcedure [dbo].[SP_Ingresos_Master]    Script Date: 10/7/2019 20:55:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		BC
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[SP_Ingresos_Master] 
	-- Add the parameters for the stored procedure here
	@FechaI datetime
	,@FechaF datetime
	,@SoloPendientes bit

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
PRINT '@FechaI ' + CONVERT(VARCHAR,@FechaI)
PRINT '@FechaF ' + CONVERT(VARCHAR,@FechaF)

--SELECT @SoloPendientes=ISNULL(@SoloPendientes,'N')

DECLARE @query varchar(MAX)

DECLARE @ResultadoTMP TABLE(	
	[CodEmpr] varchar(10)
	,[IngresoID] int
	,[CodIF] varchar(10)
	,[CodInv] varchar(20)
	,[Fecha] datetime
	--,[FechaVenc] datetime
	,[IngresoNeto] numeric(18,6)
	,[Estado] varchar(1)
	--,[Validacion] varchar(1)
	--,INS0 varchar(10)
	--,INS1 varchar(10)
	,[Registrado] varchar(1)
	,[Validado] varchar(1)
	,[Conciliado] varchar(1)
)


/*  ACCIONES  */
SET @query='
SELECT
	T1.CodInv
	,T1.Correlativo	
	,T1.Fecha
	,T1.IngresoNeto
	,ISNULL(T1.Estado,''P'')
	,ISNULL(T3.Registrado,''N'')
	,ISNULL(T3.Validado,''N'')
	,ISNULL(T3.Conciliado,''N'')
FROM [dbo].[ACC1] T1
INNER JOIN [dbo].[ACC0] T0 ON T0.CodInv=T1.CodInv
INNER JOIN [dbo].[INV0] INV ON INV.CodInv = T1.CodInv
LEFT JOIN [dbo].[INGR] T3 ON T3.CodInv=T1.CodInv AND T3.IngresoId=T1.Correlativo
WHERE 1=1
AND T1.Fecha>=('+char(39)+CONVERT(VARCHAR,@FechaI,112)+char(39)+')
AND T1.Fecha<=('+char(39)+CONVERT(VARCHAR,@FechaF,112)+char(39)+')'
IF @SoloPendientes=1
BEGIN
	SET @query=@query+'
AND (ISNULL(T1.Estado,'''')<>''R'' OR ISNULL(T3.Registrado,'''')<>''S'' OR ISNULL(T3.Validado,'''')<>''S'' OR ISNULL(T3.Conciliado,'''')<>''S'')'
END

--print @query

INSERT INTO @ResultadoTMP(	
	[CodInv]
	,[IngresoID]
	,[Fecha]
	,[IngresoNeto]
	,[Estado]
	,[Registrado]
	,[Validado]
	,[Conciliado]
)EXEC(@query)






PRINT 'DEPOSITOS A PLAZO'
/* DEPOSITOS A PLAZO  */
SET @query='
SELECT	
	T1.CodInv
	,T1.Correlativo
	,T1.Fecha
	,T1.IngrNeto
	,ISNULL(T1.Estado,''P'')
	,ISNULL(T3.Registrado,''N'')
	,ISNULL(T3.Validado,''N'')
	,ISNULL(T3.Conciliado,''N'')
FROM [dbo].[DAP1] T1
INNER JOIN [dbo].[DAP0] T0 ON T0.CodInv=T1.CodInv
INNER JOIN [dbo].[INV0] INV ON INV.CodInv = T1.CodInv
LEFT JOIN [dbo].[INGR] T3 ON T3.CodInv=T1.CodInv AND T3.IngresoId=T1.Correlativo 
WHERE 1=1
AND T1.Fecha>=('+char(39)+CONVERT(VARCHAR,@FechaI,112)+char(39)+')
AND T1.Fecha<=('+char(39)+CONVERT(VARCHAR,@FechaF,112)+char(39)+')'

IF @SoloPendientes=1
BEGIN
	SET @query=@query+'
AND (ISNULL(T1.Estado,'''')<>''R'' OR ISNULL(T3.Registrado,'''')<>''S'' OR ISNULL(T3.Validado,'''')<>''S'' OR ISNULL(T3.Conciliado,'''')<>''S'')'
END

--print @query

INSERT INTO @ResultadoTMP(
	[CodInv]
	,[IngresoID]
	,[Fecha]
	,[IngresoNeto]
	,[Estado]
	,[Registrado]
	,[Validado]
	,[Conciliado]
) EXEC(@query)




/*  BONOS  */
SET @query='
SELECT
	T1.CodInv
	,T1.NumCupon	
	,T1.[FechaCupon]
	,T1.[Liquido]
	,ISNULL(T1.Estado,''P'')
	,ISNULL(T3.Registrado,''N'')
	,ISNULL(T3.Validado,''N'')
	,ISNULL(T3.Conciliado,''N'')
FROM [dbo].[BON1] T1
INNER JOIN [dbo].[BON0] T0 ON T0.CodInv=T1.CodInv
INNER JOIN [dbo].[INV0] INV ON INV.CodInv = T1.CodInv
LEFT JOIN [dbo].[INGR] T3 ON T3.CodInv=T1.CodInv AND T3.IngresoId=T1.NumCupon
WHERE 1=1
AND T1.FechaCupon>=('+char(39)+CONVERT(VARCHAR,@FechaI,112)+char(39)+')
AND T1.FechaCupon<=('+char(39)+CONVERT(VARCHAR,@FechaF,112)+char(39)+')'
IF @SoloPendientes=1
BEGIN
	SET @query=@query+'
AND (ISNULL(T1.Estado,'''')<>''R'' OR ISNULL(T3.Registrado,'''')<>''S'' OR ISNULL(T3.Validado,'''')<>''S'' OR ISNULL(T3.Conciliado,'''')<>''S'')'
END

--print @query

INSERT INTO @ResultadoTMP(	
	[CodInv]
	,[IngresoID]
	,[Fecha]
	,[IngresoNeto]
	,[Estado]
	,[Registrado]
	,[Validado]
	,[Conciliado]
)EXEC(@query)



/*  CERTIFICADOS DE INVERSION  */
SET @query='
SELECT
	T1.CodInv
	,T1.NumCupon	
	,T1.[FechaCupon]
	,T1.[Liquido]
	,ISNULL(T1.Estado,''P'')
	,ISNULL(T3.Registrado,''N'')
	,ISNULL(T3.Validado,''N'')
	,ISNULL(T3.Conciliado,''N'')
FROM [dbo].[CIN1] T1
INNER JOIN [dbo].[CIN0] T0 ON T0.CodInv=T1.CodInv
INNER JOIN [dbo].[INV0] INV ON INV.CodInv = T1.CodInv
LEFT JOIN [dbo].[INGR] T3 ON T3.CodInv=T1.CodInv AND T3.IngresoId=T1.NumCupon
WHERE 1=1
AND T1.FechaCupon>=('+char(39)+CONVERT(VARCHAR,@FechaI,112)+char(39)+')
AND T1.FechaCupon<=('+char(39)+CONVERT(VARCHAR,@FechaF,112)+char(39)+')'
IF @SoloPendientes=1
BEGIN
	SET @query=@query+'
AND (ISNULL(T1.Estado,'''')<>''R'' OR ISNULL(T3.Registrado,'''')<>''S'' OR ISNULL(T3.Validado,'''')<>''S'' OR ISNULL(T3.Conciliado,'''')<>''S'')'
END

--print @query

INSERT INTO @ResultadoTMP(	
	[CodInv]
	,[IngresoID]
	,[Fecha]
	,[IngresoNeto]
	,[Estado]
	,[Registrado]
	,[Validado]
	,[Conciliado]
)EXEC(@query)



/*  LETES  */
SET @query='
SELECT
	T1.CodInv
	,1	
	,T1.[FVenc]
	,T1.[IngrNeto]
	,ISNULL(T1.Estado,''P'')
	,ISNULL(T3.Registrado,''N'')
	,ISNULL(T3.Validado,''N'')
	,ISNULL(T3.Conciliado,''N'')
FROM [dbo].[LET0] T1
INNER JOIN [dbo].[INV0] INV ON INV.CodInv = T1.CodInv
LEFT JOIN [dbo].[INGR] T3 ON T3.CodInv=T1.CodInv AND T3.IngresoId=1
WHERE 1=1
AND T1.FVenc>=('+char(39)+CONVERT(VARCHAR,@FechaI,112)+char(39)+')
AND T1.FVenc<=('+char(39)+CONVERT(VARCHAR,@FechaF,112)+char(39)+')'
IF @SoloPendientes=1
BEGIN
	SET @query=@query+'
AND (ISNULL(T1.Estado,'''')<>''R'' OR ISNULL(T3.Registrado,'''')<>''S'' OR ISNULL(T3.Validado,'''')<>''S'' OR ISNULL(T3.Conciliado,'''')<>''S'')'
END

--print @query

INSERT INTO @ResultadoTMP(	
	[CodInv]
	,[IngresoID]
	,[Fecha]
	,[IngresoNeto]
	,[Estado]
	,[Registrado]
	,[Validado]
	,[Conciliado]
)EXEC(@query)


/*  CETES  */
SET @query='
SELECT
	T1.CodInv
	,1	
	,T1.[FVenc]
	,T1.[IngrNeto]
	,ISNULL(T1.Estado,''P'')
	,ISNULL(T3.Registrado,''N'')
	,ISNULL(T3.Validado,''N'')
	,ISNULL(T3.Conciliado,''N'')
FROM [dbo].[CET0] T1
INNER JOIN [dbo].[INV0] INV ON INV.CodInv = T1.CodInv
LEFT JOIN [dbo].[INGR] T3 ON T3.CodInv=T1.CodInv AND T3.IngresoId=1
WHERE 1=1
AND T1.FVenc>=('+char(39)+CONVERT(VARCHAR,@FechaI,112)+char(39)+')
AND T1.FVenc<=('+char(39)+CONVERT(VARCHAR,@FechaF,112)+char(39)+')'
IF @SoloPendientes=1
BEGIN
	SET @query=@query+'
AND (ISNULL(T1.Estado,'''')<>''R'' OR ISNULL(T3.Registrado,'''')<>''S'' OR ISNULL(T3.Validado,'''')<>''S'' OR ISNULL(T3.Conciliado,'''')<>''S'')'
END

--print @query

INSERT INTO @ResultadoTMP(	
	[CodInv]
	,[IngresoID]
	,[Fecha]
	,[IngresoNeto]
	,[Estado]
	,[Registrado]
	,[Validado]
	,[Conciliado]
)EXEC(@query)


/*  PAPEL BURSATIL  */
SET @query='
SELECT
	T1.CodInv
	,1	
	,T1.[FVenc]
	,T1.[IngrNeto]
	,ISNULL(T1.Estado,''P'')
	,ISNULL(T3.Registrado,''N'')
	,ISNULL(T3.Validado,''N'')
	,ISNULL(T3.Conciliado,''N'')
FROM [dbo].[PBUR] T1
INNER JOIN [dbo].[INV0] INV ON INV.CodInv = T1.CodInv
LEFT JOIN [dbo].[INGR] T3 ON T3.CodInv=T1.CodInv AND T3.IngresoId=1
WHERE 1=1
AND T1.FVenc>=('+char(39)+CONVERT(VARCHAR,@FechaI,112)+char(39)+')
AND T1.FVenc<=('+char(39)+CONVERT(VARCHAR,@FechaF,112)+char(39)+')'
IF @SoloPendientes=1
BEGIN
	SET @query=@query+'
AND (ISNULL(T1.Estado,'''')<>''R'' OR ISNULL(T3.Registrado,'''')<>''S'' OR ISNULL(T3.Validado,'''')<>''S'' OR ISNULL(T3.Conciliado,'''')<>''S'')'
END

print @query

INSERT INTO @ResultadoTMP(	
	[CodInv]
	,[IngresoID]
	,[Fecha]
	,[IngresoNeto]
	,[Estado]
	,[Registrado]
	,[Validado]
	,[Conciliado]
)EXEC(@query)


/*  VALORES COMERCIALES NEGOCIABLES  */
SET @query='
SELECT
	T1.CodInv
	,1	
	,T1.[FVenc]
	,T1.[IngrNeto]
	,ISNULL(T1.Estado,''P'')
	,ISNULL(T3.Registrado,''N'')
	,ISNULL(T3.Validado,''N'')
	,ISNULL(T3.Conciliado,''N'')
FROM [dbo].[VCN] T1
INNER JOIN [dbo].[INV0] INV ON INV.CodInv = T1.CodInv
LEFT JOIN [dbo].[INGR] T3 ON T3.CodInv=T1.CodInv AND T3.IngresoId=1
WHERE 1=1
AND T1.FVenc>=('+char(39)+CONVERT(VARCHAR,@FechaI,112)+char(39)+')
AND T1.FVenc<=('+char(39)+CONVERT(VARCHAR,@FechaF,112)+char(39)+')'
IF @SoloPendientes=1
BEGIN
	SET @query=@query+'
AND (ISNULL(T1.Estado,'''')<>''R'' OR ISNULL(T3.Registrado,'''')<>''S'' OR ISNULL(T3.Validado,'''')<>''S'' OR ISNULL(T3.Conciliado,'''')<>''S'')'
END

print @query

INSERT INTO @ResultadoTMP(	
	[CodInv]
	,[IngresoID]
	,[Fecha]
	,[IngresoNeto]
	,[Estado]
	,[Registrado]
	,[Validado]
	,[Conciliado]
)EXEC(@query)









SELECT
	T0.IngresoID
	,INV.[CodEmpr]
	,(SELECT S0.Descrip FROM [dbo].[EMPR] S0 WHERE S0.CodEmpr=INV.CodEmpr) 'Empresa'
	,INV.[CodIF]
	,(SELECT S0.Descrip FROM [dbo].[INFI] S0 WHERE S0.CodIF=INV.CodIF) 'Instrumento'
	,T0.[CodInv]
	,T0.[Fecha]
	,T0.[IngresoNeto]
	,T0.[Estado] 'CodEstado'
	,CASE T0.[Estado]
		WHEN 'R' THEN 'Recibido'
		WHEN 'P' THEN 'Pendiente'
		ELSE ''
	END 'Estado'
	--,T0.[Validacion] 'CodValidacion'
	,INV.CodEmis
	,(SELECT S0.Descrip FROM [dbo].[EMIS] S0 WHERE S0.CodEmis=INV.CodEmis) 'Emisor'
	,INV.CodManIF
	,INV.NombreManIF
	,ISNULL(INV.NumManInv,'') 'NumManInv'
	,T0.Registrado
	,T0.Validado
	,T0.Conciliado
FROM @ResultadoTMP T0
INNER JOIN [dbo].[INV0] INV ON INV.CodInv=T0.CodInv
END