Inversiones/Recursos/SP_IngresosDetalle_20190710.sql

USE [Inversiones_20190711]
GO
/****** Object:  StoredProcedure [dbo].[SP_IngresosDetalle]    Script Date: 10/7/2019 20:56:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		BC
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[SP_IngresosDetalle] 
	-- Add the parameters for the stored procedure here
	@CodInv varchar(20), 
	@IngresoId int
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 @TipoIF varchar(10)
	DECLARE @CodIF varchar(10)

	DECLARE @IngresoDetalle TABLE(
		[Codinv] varchar(20)
		,[CodIF] varchar(10)
		,[Estado] varchar(2)
		,[CodEmpr] varchar(20)
		,[CodEmis] int
		,[CodECalRi] varchar(8)
		,[NombreManIF] varchar(100)
		,[CodManIF] varchar(50)
		,[FechaVencimiento] datetime
		,[Capital] numeric(18,6)
		,[IngresoBruto] numeric(18,6)
		,[ISR] numeric(18,6)
		,[IngresoNeto] numeric(18,6)
		,[Correlativo] int
		,[EstadoIngreso] varchar(1)
	)

	SELECT @CodIF=T0.CodIF FROM [dbo].[INV0] T0 WHERE T0.CodInv=@CodInv
	SELECT @TipoIF=T0.Tipo FROM [dbo].[INFI] T0 WHERE T0.CodIF = @CodIF

IF @TipoIF='ACC'
BEGIN
	PRINT 'ACCIONES'
	INSERT INTO @IngresoDetalle (
	[Codinv]
	,[CodIF]
	,[Estado]
	,[CodEmpr]
	,[CodEmis]
	,[CodECalRi]
	,[NombreManIF]
	,[CodManIF]
	,[FechaVencimiento]
	,[IngresoBruto]
	,[ISR]
	,[IngresoNeto]
	,[Correlativo]
	,[EstadoIngreso]
	)
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.CodEmis
		,T0.CodECalRi
		,T0.NombreManIF
		,T0.CodManIF
		,T1.Fecha
		,T1.IngresoBruto
		,T1.ISR
		,T1.IngresoNeto
		,T1.Correlativo
		,T1.Estado
	FROM [dbo].[INV0] T0 
	INNER JOIN [dbo].[ACC1] T1 ON T1.CodInv=T0.CodInv
	WHERE 1=1
	AND T0.CodInv=@CodInv
	AND T1.Correlativo=@IngresoId

END

IF @TipoIF='DAP'
BEGIN
	PRINT 'DEPOSITOS A PLAZO'
	INSERT INTO @IngresoDetalle (
	[Codinv]
	,[CodIF]
	,[Estado]
	,[CodEmpr]
	,[CodEmis]
	,[CodECalRi]
	,[NombreManIF]
	,[CodManIF]
	,[FechaVencimiento]
	,[IngresoBruto]
	,[ISR]
	,[IngresoNeto]
	,[Correlativo]
	,[EstadoIngreso]
	)
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.CodEmis
		,T0.CodECalRi
		,T0.NombreManIF
		,T0.CodManIF
		,T1.Fecha
		,T1.IngrBruto
		,T1.MontoImp
		,T1.IngrNeto
		,T1.Correlativo
		,T1.Estado
	FROM [dbo].[INV0] T0 
	INNER JOIN [dbo].[DAP1] T1 ON T1.CodInv=T0.CodInv
	WHERE 1=1
	AND T0.CodInv=@CodInv
	AND T1.Correlativo=@IngresoId

END

IF @TipoIF='BONO'
BEGIN
	PRINT 'BONOS'
	INSERT INTO @IngresoDetalle (
	[Codinv]
	,[CodIF]
	,[Estado]
	,[CodEmpr]
	,[CodEmis]
	,[CodECalRi]
	,[NombreManIF]
	,[CodManIF]
	,[FechaVencimiento]
	,[IngresoBruto]
	,[ISR]
	,[IngresoNeto]
	,[Correlativo]
	,[EstadoIngreso]
	)
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.CodEmis
		,T0.CodECalRi
		,T0.NombreManIF
		,T0.CodManIF
		,T1.[FechaCupon]
		,T1.[Ingreso]
		,T1.[MontoImpuesto]
		,T1.[Liquido]
		,T1.[NumCupon]
		,T1.Estado
	FROM [dbo].[INV0] T0 
	INNER JOIN [dbo].[BON1] T1 ON T1.CodInv=T0.CodInv
	WHERE 1=1
	AND T0.CodInv=@CodInv
	AND T1.[NumCupon]=@IngresoId

END

IF @TipoIF='CINV'
BEGIN
	PRINT 'CERTIFICADOS DE INVERSION'
	INSERT INTO @IngresoDetalle (
	[Codinv]
	,[CodIF]
	,[Estado]
	,[CodEmpr]
	,[CodEmis]
	,[CodECalRi]
	,[NombreManIF]
	,[CodManIF]
	,[FechaVencimiento]
	,[IngresoBruto]
	,[ISR]
	,[IngresoNeto]
	,[Correlativo]
	,[EstadoIngreso]
	)
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.CodEmis
		,T0.CodECalRi
		,T0.NombreManIF
		,T0.CodManIF
		,T1.[FechaCupon]
		,T1.[Ingreso]
		,T1.[MontoImpuesto]
		,T1.[Liquido]
		,T1.[NumCupon]
		,T1.Estado
	FROM [dbo].[INV0] T0 
	INNER JOIN [dbo].[CIN1] T1 ON T1.CodInv=T0.CodInv
	WHERE 1=1
	AND T0.CodInv=@CodInv
	AND T1.[NumCupon]=@IngresoId

END

IF @TipoIF='LETE'
BEGIN
	PRINT 'LETES'
	INSERT INTO @IngresoDetalle (
	[Codinv]
	,[CodIF]
	,[Estado]
	,[CodEmpr]
	,[CodEmis]
	,[CodECalRi]
	,[NombreManIF]
	,[CodManIF]
	,[FechaVencimiento]
	,[IngresoBruto]
	,[ISR]
	,[IngresoNeto]
	,[Correlativo]
	,[EstadoIngreso]
	)
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.CodEmis
		,T0.CodECalRi
		,T0.NombreManIF
		,T0.CodManIF
		,T1.[FVenc]
		,T1.[IngrBruto]
		,0 --T1.[MontoImpuesto]
		,T1.[IngrNeto]
		,1 --T1.[NumCupon]
		,T1.Estado
	FROM [dbo].[INV0] T0 
	INNER JOIN [dbo].[LET0] T1 ON T1.CodInv=T0.CodInv
	WHERE 1=1
	AND T0.CodInv=@CodInv
	AND 1=@IngresoId

END


IF @TipoIF='CETE'
BEGIN
	PRINT 'CETES'
	INSERT INTO @IngresoDetalle (
	[Codinv]
	,[CodIF]
	,[Estado]
	,[CodEmpr]
	,[CodEmis]
	,[CodECalRi]
	,[NombreManIF]
	,[CodManIF]
	,[FechaVencimiento]
	,[IngresoBruto]
	,[ISR]
	,[IngresoNeto]
	,[Correlativo]
	,[EstadoIngreso]
	)
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.CodEmis
		,T0.CodECalRi
		,T0.NombreManIF
		,T0.CodManIF
		,T1.[FVenc]
		,T1.[IngrBruto]
		,0 --T1.[MontoImpuesto]
		,T1.[IngrNeto]
		,1 --T1.[NumCupon]
		,T1.Estado
	FROM [dbo].[INV0] T0 
	INNER JOIN [dbo].[CET0] T1 ON T1.CodInv=T0.CodInv
	WHERE 1=1
	AND T0.CodInv=@CodInv
	AND 1=@IngresoId

END


IF @TipoIF='PBUR'
BEGIN
	PRINT 'PAPEL BURSATIL'
	INSERT INTO @IngresoDetalle (
	[Codinv]
	,[CodIF]
	,[Estado]
	,[CodEmpr]
	,[CodEmis]
	,[CodECalRi]
	,[NombreManIF]
	,[CodManIF]
	,[FechaVencimiento]
	,[IngresoBruto]
	,[ISR]
	,[IngresoNeto]
	,[Correlativo]
	,[EstadoIngreso]
	)
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.CodEmis
		,T0.CodECalRi
		,T0.NombreManIF
		,T0.CodManIF
		,T1.[FVenc]
		,T1.[IngrBruto]
		,0 --T1.[MontoImpuesto]
		,T1.[IngrNeto]
		,1 --T1.[NumCupon]
		,T1.Estado
	FROM [dbo].[INV0] T0 
	INNER JOIN [dbo].[PBUR] T1 ON T1.CodInv=T0.CodInv
	WHERE 1=1
	AND T0.CodInv=@CodInv
	AND 1=@IngresoId

END



IF @TipoIF='VCN'
BEGIN
	PRINT 'VALORES COMERCIALES  NEGOCIABLES'
	INSERT INTO @IngresoDetalle (
	[Codinv]
	,[CodIF]
	,[Estado]
	,[CodEmpr]
	,[CodEmis]
	,[CodECalRi]
	,[NombreManIF]
	,[CodManIF]
	,[FechaVencimiento]
	,[IngresoBruto]
	,[ISR]
	,[IngresoNeto]
	,[Correlativo]
	,[EstadoIngreso]
	)
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.CodEmis
		,T0.CodECalRi
		,T0.NombreManIF
		,T0.CodManIF
		,T1.[FVenc]
		,T1.[IngrBruto]
		,0 --T1.[MontoImpuesto]
		,T1.[IngrNeto]
		,1 --T1.[NumCupon]
		,T1.Estado
	FROM [dbo].[INV0] T0 
	INNER JOIN [dbo].[VCN] T1 ON T1.CodInv=T0.CodInv
	WHERE 1=1
	AND T0.CodInv=@CodInv
	AND 1=@IngresoId

END










SELECT
	T0.[Codinv]
	,T0.[CodIF]
	,ISNULL(T0.Estado,'P')
	,T0.[CodEmpr]
	,(SELECT S0.Descrip FROM [dbo].[EMPR] S0 WHERE S0.CodEmpr=T0.[CodEmpr]) 'Empresa'
	,T0.[CodEmis]
	,(SELECT S0.Descrip FROM [dbo].[EMIS] S0 WHERE S0.CodEmis=T0.[CodEmis]) 'Emisor'
	,T0.[CodECalRi]
	,ISNULL((SELECT S0.Descrip FROM [dbo].[ECALRI] S0 WHERE S0.CodECalRi = T0.[CodECalRi]),'') 'Calificadora'
	,T0.[NombreManIF]
	,T0.[CodManIF]
	,T0.[FechaVencimiento]
	,T0.Capital
	,T0.[IngresoBruto]
	,T0.[ISR]
	,T0.[IngresoNeto]
	,T0.[Correlativo]
	,(SELECT S0.SAP_BD FROM [dbo].[EMPR] S0 WHERE S0.CodEmpr=T0.[CodEmpr]) 'SAP_BD'
	,ISNULL(T0.EstadoIngreso,'P') 'EstadoIngreso'
FROM @IngresoDetalle T0



END