Inversiones/Recursos/SP_IngresosDetalle.sql

USE [Inversiones_20190620]
GO
/****** Object:  StoredProcedure [dbo].[SP_IngresosDetalle]    Script Date: 18/6/2019 14:50:35 ******/
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)
		,[Estado] varchar(2)
		,[CodEmpr] varchar(20)
		,[CodEmis] int
		,[CodECalRi] varchar(8)
		,[NombreManIF] varchar(100)
		,[CodManIF] varchar(50)
		,[FechaVencimiento] datetime
		,[IngresoBruto] numeric(18,6)
		,[ISR] numeric(18,6)
		,[IngresoNeto] numeric(18,6)
		,[Correlativo] int
	)

	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]
	,[Estado]
	,[CodEmpr]
	,[CodEmis]
	,[CodECalRi]
	,[NombreManIF]
	,[CodManIF]
	,[FechaVencimiento]
	,[IngresoBruto]
	,[ISR]
	,[IngresoNeto]
	,[Correlativo]
	)
	SELECT
		T0.CodInv
		,T0.DocEst
		,T0.CodEmpr
		,T0.CodEmis
		,T0.CodECalRi
		,T0.NombreManIF
		,T0.CodManIF
		,T1.FechaVencimiento
		,T1.IngresoBruto
		,T1.ISR
		,T1.IngresoNeto
		,T1.Correlativo
	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





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



END