-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--[C6Mart].[PL_S32PatrimonioFinanziarioMAssetClass]  'F','QGLLND58R42E897H'
--[C6Mart].[PL_S32PatrimonioFinanziarioMAssetClass]  'F','BRTVTR64T13L219G'
--[C6Mart].[PL_S32PatrimonioFinanziarioMAssetClass]  'F','LNTNNE59L06F205C'
--[C6Mart].[PL_S32PatrimonioFinanziarioMAssetClass]  'S', 'GHGGPP32M04L219F'
--[C6Mart].[PL_S32PatrimonioFinanziarioMAssetClass]  'S','GHGRTI63T44L219D'
CREATE procedure [C6Mart].[PL_S32PatrimonioFinanziarioMAssetClass] 
	-- Add the parameters for the stored procedure here
	-- Add the parameters for the stored procedure here
	@Rete char(1), 
	@CodiceFiscale varchar(16)
AS
BEGIN
	SELECT DISTINCT
		ASSETCLASS.ORDINAMENTO,
		VALORIASSET.ID_ASSETCLASS AS AssetClassId, 
		ASSETCLASS.DESCRIZIONE AS AssetClassName, 
		cast(SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS) as decimal(19,6)) AS Controvalore,
		(
			SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS) /
			CASE
				WHEN SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY 'TOTALE') = 0 THEN 1
				ELSE SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY 'TOTALE')
			END
		) * 100 AS Percentuale,
		cast (SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY 'TOTALE') as decimal(19,6)) AS PatrimonioFinanziarioCTV
	FROM (
		--PRODOTTI TERZI A CATALOGO
		SELECT 	
			ISNULL(ASSETPERC.ID_ASSETCLASS,'AL') AS ID_ASSETCLASS,
			PATRTERZI.CTV * ISNULL(ASSETPERC.PERC,1) AS Controvalore
		FROM C6MART.PATRIMONIO_TERZI PATRTERZI
		LEFT OUTER JOIN C6MART.ASSET_PERC ASSETPERC 
			ON PATRTERZI.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
			AND ASSETPERC.LIVELLO = 1
		WHERE 1 = 1
			AND PATRTERZI.COD_PRODOTTO <> 'PRODOTTO NON IN CATALOGO'
			AND PATRTERZI.RETE = @Rete
			AND PATRTERZI.COD_FISCALE = @CodiceFiscale
		UNION ALL
		--PRODOTTI TERZI NON A CATALOGO
		SELECT 
			ISNULL(ASSETPERC.ID_ASSETCLASS,'AL') AS ID_ASSETCLASS,
			PATRTERZI.CTV*ISNULL(ASSETPERC.PERC,1) AS Controvalore
		FROM C6MART.PATRIMONIO_TERZI PATRTERZI
		LEFT OUTER JOIN C6MART.ASSET_PERC_TERZI ASSETPERC 
			ON PATRTERZI.COD_PRODOTTO_TERZI = ASSETPERC.COD_PRODOTTO_TERZI
			AND ASSETPERC.LIVELLO = 1
		WHERE 1 = 1
			AND PATRTERZI.COD_PRODOTTO = 'PRODOTTO NON IN CATALOGO'
			AND PATRTERZI.RETE = @Rete
			AND PATRTERZI.COD_FISCALE = @CodiceFiscale
	) VALORIASSET
	INNER JOIN C6Mart.ASSETCLASS ASSETCLASS
		ON ASSETCLASS.ID_ASSETCLASS = VALORIASSET.ID_ASSETCLASS
	ORDER BY ASSETCLASS.ORDINAMENTO
END