Inversiones/Recursos/SP_InventarioMaster_20190714.sql

USE [Inversiones_20190711]
GO
/****** Object:  StoredProcedure [dbo].[SP_InventarioMaster]    Script Date: 7/15/2019 12:36:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		BC
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[SP_InventarioMaster] 
	-- Add the parameters for the stored procedure here

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 @Inventario TABLE(
		CodInv varchar(20)
		,CodIF varchar(10)
		,Estado varchar(4)
		,CodEmpr varchar(10)
		,NombreManIF varchar(50)
		,CodManIF varchar(50)
		,CodEmis int
		,iCasaBanco varchar(1)
		,vCasaBanco varchar(10)
		,TipoMerc varchar(4)
		,CodCalRi varchar(8)
		,CodECalRi varchar(8)
		,ValorNominal numeric(18,6)

	)

/*
	DEPOSITOS A PLAZO
*/
	INSERT INTO @Inventario
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.NombreManIF
		,T0.CodManIF
		,T0.CodEmis	
		,NULL
		,NULL
		,T0.TipoMerc	
		,T0.CodCalRi
		,T0.CodECalRi
		,T1.MontoInv
	FROM [dbo].[INV0] T0
	INNER JOIN [dbo].[DAP0] T1 ON T0.CodInv=T1.CodInv


/*
	FONDOS DE INVERSION
*/
	INSERT INTO @Inventario
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.NombreManIF
		,T0.CodManIF
		,T0.CodEmis	
		,NULL
		,NULL
		,T0.TipoMerc	
		,T0.CodCalRi
		,T0.CodECalRi
		,T1.CuoPart
	FROM [dbo].[INV0] T0
INNER JOIN [dbo].[FINV] T1 ON T0.CodInv=T1.CodInv


/*
	ACCIONES
*/
	INSERT INTO @Inventario
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.NombreManIF
		,T0.CodManIF
		,T0.CodEmis	
		,NULL
		,NULL
		,T0.TipoMerc	
		,T0.CodCalRi
		,T0.CodECalRi
		,T1.ValTransC
	FROM [dbo].[INV0] T0
INNER JOIN [dbo].[ACC0] T1 ON T0.CodInv=T1.CodInv


/*
	BONOS
*/
	INSERT INTO @Inventario
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.NombreManIF
		,T0.CodManIF
		,T0.CodEmis	
		,NULL
		,NULL
		,T0.TipoMerc	
		,T0.CodCalRi
		,T0.CodECalRi
		,T1.ValNomC
	FROM [dbo].[INV0] T0
INNER JOIN [dbo].[BON0] T1 ON T0.CodInv=T1.CodInv

/*
	EURO BONOS
*/
	INSERT INTO @Inventario
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.NombreManIF
		,T0.CodManIF
		,T0.CodEmis	
		,NULL
		,NULL
		,T0.TipoMerc	
		,T0.CodCalRi
		,T0.CodECalRi
		,T1.ValNomC
	FROM [dbo].[INV0] T0
INNER JOIN [dbo].[EURB0] T1 ON T0.CodInv=T1.CodInv

/*
	LETES
*/
	INSERT INTO @Inventario
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.NombreManIF
		,T0.CodManIF
		,T0.CodEmis	
		,NULL
		,NULL
		,T0.TipoMerc	
		,T0.CodCalRi
		,T0.CodECalRi
		,T1.IngrNeto
	FROM [dbo].[INV0] T0
INNER JOIN [dbo].[LET0] T1 ON T0.CodInv=T1.CodInv

/*
	CETES
*/
	INSERT INTO @Inventario
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.NombreManIF
		,T0.CodManIF
		,T0.CodEmis	
		,NULL
		,NULL
		,T0.TipoMerc	
		,T0.CodCalRi
		,T0.CodECalRi
		,T1.IngrNeto
	FROM [dbo].[INV0] T0
INNER JOIN [dbo].[CET0] T1 ON T0.CodInv=T1.CodInv


/*
	CERTIFICADOS DE INVERSION
*/
	INSERT INTO @Inventario
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.NombreManIF
		,T0.CodManIF
		,T0.CodEmis	
		,NULL
		,NULL
		,T0.TipoMerc	
		,T0.CodCalRi
		,T0.CodECalRi
		,T1.ValNomC
	FROM [dbo].[INV0] T0
INNER JOIN [dbo].[CIN0] T1 ON T0.CodInv=T1.CodInv


/*
	PAPEL BURSATIL
*/
	INSERT INTO @Inventario
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.NombreManIF
		,T0.CodManIF
		,T0.CodEmis	
		,NULL
		,NULL
		,T0.TipoMerc	
		,T0.CodCalRi
		,T0.CodECalRi
		,T1.ValNom
	FROM [dbo].[INV0] T0
INNER JOIN [dbo].[PBUR] T1 ON T0.CodInv=T1.CodInv

/*
	REPORTOS
*/
	INSERT INTO @Inventario
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.NombreManIF
		,T0.CodManIF
		,T0.CodEmis	
		,NULL
		,NULL
		,T0.TipoMerc	
		,T0.CodCalRi
		,T0.CodECalRi
		,T1.ValTrans
	FROM [dbo].[INV0] T0
INNER JOIN [dbo].[REP0] T1 ON T0.CodInv=T1.CodInv



/*
	VCN
*/
	INSERT INTO @Inventario
	SELECT
		T0.CodInv
		,T0.CodIF
		,T0.DocEst
		,T0.CodEmpr
		,T0.NombreManIF
		,T0.CodManIF
		,T0.CodEmis	
		,NULL
		,NULL
		,T0.TipoMerc	
		,T0.CodCalRi
		,T0.CodECalRi
		,T1.ValNom
	FROM [dbo].[INV0] T0
INNER JOIN [dbo].[VCN] T1 ON T0.CodInv=T1.CodInv






SELECT * FROM @Inventario

END