Inversiones/Recursos/SP_Ingresos_Master.sql

USE [Inversiones]
GO
/****** Object:  StoredProcedure [dbo].[SP_Ingresos_Master]    Script Date: 6/24/2019 9:07:54 PM ******/
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='18000101'
	,@FechaF datetime = '99991231'
	,@SoloPendientes bit = 0

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)
)


/*  ACCIONES  */
SET @query='
SELECT
	T1.CodInv
	,T1.Correlativo	
	,T1.Fecha
	,T1.IngresoNeto
	,ISNULL(T1.Estado,''-'')
	,''ACC0''
	,''ACC1''
FROM [dbo].[ACC1] T1
INNER JOIN [dbo].[ACC0] T0 ON T0.CodInv=T1.CodInv
INNER JOIN [dbo].[INV0] INV ON INV.CodInv = T1.CodInv
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 (T1.Estado<>'+char(39)+'R'+char(39)+')'
END

print @query

INSERT INTO @ResultadoTMP(	
	[CodInv]
	,[IngresoID]
	,[Fecha]
	--,[FechaVenc]
	,[IngresoNeto]
	,[Estado]
	--,[Validacion]
	,INS0
	,INS1
)EXEC(@query)







/* DEPOSITOS A PLAZO  */
SET @query='
SELECT	
	T1.CodInv
	,T1.Correlativo
	,T1.Fecha
	,T1.IngrNeto
	,ISNULL(T1.Estado,''-'')
	,''DAP0''
	,''DAP1''
FROM [dbo].[DAP1] T1
INNER JOIN [dbo].[DAP0] T0 ON T0.CodInv=T1.CodInv
INNER JOIN [dbo].[INV0] INV ON INV.CodInv = T1.CodInv
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 T1.Estado<>'+char(39)+'R'+char(39)
END

--print @query

INSERT INTO @ResultadoTMP(
	[IngresoID]
	,[CodInv]
	,[Fecha]
	--,[FechaVenc]
	,[IngresoNeto]
	,[Estado]
	,INS0
	,INS1
) 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.[FechaVenc]
	,T0.[IngresoNeto]
	,T0.[Estado] 'CodEstado'
	,CASE T0.[Estado]
		WHEN 'A' THEN 'Abierto'
		WHEN 'C' THEN 'Cerrado'
		ELSE ''
	END 'Estado'
	--,T0.[Validacion] 'CodValidacion'
	,('N') 'Contab'
	,INV.CodEmis
	,(SELECT S0.Descrip FROM [dbo].[EMIS] S0 WHERE S0.CodEmis=INV.CodEmis) 'Emisor'
	,INV.CodManIF
	,INV.NombreManIF
	,ISNULL(INV.NumManInv,'') 'NumManInv'
	,T0.[INS0]
	,T0.[INS1]
FROM @ResultadoTMP T0
INNER JOIN [dbo].[INV0] INV ON INV.CodInv=T0.CodInv
END