CREATE procedure [C6Mart].[PL_D2_S160DettaglioProdottiBF]
 -- Add the parameters for the stored procedure here
 @Rete char(1), 
 @CodiceFiscale varchar(16)
AS
BEGIN
-------------------------------------------------------------------------------------
--INIZIO INTERVENTI OMNIA QUIII
	DECLARE @TOTSELFLIQ AS INT
	SELECT @TOTSELFLIQ = COUNT(*)
	FROM C6MART.PATRIMONIO_BF
	WHERE PATRIMONIO_BF.RETE = @Rete
		AND PATRIMONIO_BF.COD_FISCALE = @CodiceFiscale
		AND ISIN in('EURO00000009', 'EURO10000007' )
		AND ID_AREA = 'LIQ'
	DECLARE @TOTLIQ AS INT
	SELECT @TOTLIQ = COUNT(*)
	FROM C6MART.PATRIMONIO_BF
	WHERE PATRIMONIO_BF.RETE = @Rete
		AND PATRIMONIO_BF.COD_FISCALE = @CodiceFiscale
		AND ID_AREA = 'LIQ'
--FINE INTERVENTI OMNIA QUIII
--------------------------------------------------------------------------------------
SELECT 
PATRBF.NOME_PROGETTO AS EE, PATRBF.ID_AREA,
CASE 
					WHEN (PATRBF.NOME_PROGETTO IS NOT NULL)
						THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO+ '|'
					when LEFT(PATRBF.ID_AREA,3)='Na' then 'RISFIN|NA|'
					ELSE 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' 
				END
--		END + PATRBF.POSITION_ID 
		+ PATRBF.POSITION_ID AS TMP,
	CASE 
		WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN 'Pre' 
		ELSE AREA.ID_AREA 
	END AS needarea,
	CASE 
		WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN 'Previdenza'
		WHEN AREA.ID_AREA = 'Na'  THEN 'Non allocate'
		ELSE AREA.NOME_AREA 
	END AS needareades,
	CASE 
		WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN  3 
		ELSE AREA.ORDINAMENTO 
	END AS areasortorder,
	CASE 
		WHEN AREA.ID_AREA = 'Inv' AND PATRBF.NOME_PROGETTO IS NULL THEN 'Prodotti non associati a progetti'
		ELSE PATRBF.NOME_PROGETTO
	END AS nome_progetto,
	SUM(PATRBF.CTV) OVER (PARTITION BY AREA.ID_AREA) AS somma_controval_needarea,
	SUM(PATRBF.VERSATO_NETTO) OVER (PARTITION BY AREA.ID_AREA) AS versato_needarea,
--	CASE WHEN SUM(PATRBF.VERSATO_NETTO) OVER (PARTITION BY AREA.ID_AREA) IS NULL THEN 'n.d.' ELSE NULL END as versato_needareaString,
	CASE WHEN COUNT(CASE WHEN PATRBF.VERSATO_NETTO IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA) > 0 THEN 'n.d.' ELSE NULL END as versato_needareaString,
	SUM(PATRBF.CTV - PATRBF.VERSATO_NETTO) OVER (PARTITION BY AREA.ID_AREA) AS MinusPlusValenza_needarea,
--	CASE WHEN SUM(PATRBF.CTV - PATRBF.VERSATO_NETTO) OVER (PARTITION BY AREA.ID_AREA) IS NULL THEN 'n.d.' ELSE NULL END as MinusPlusValenza_needareaString,
	CASE WHEN COUNT(CASE WHEN (PATRBF.CTV - PATRBF.VERSATO_NETTO) IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA) > 0 THEN 'n.d.' ELSE NULL END as MinusPlusValenza_needareaString,
	CASE 
		WHEN AREA.ID_AREA = 'Inv' THEN SUM(PATRBF.CTV) OVER (PARTITION BY AREA.ID_AREA, NOME_PROGETTO) 
		ELSE NULL	
	END	AS somma_controval_need_prog,
	CASE 
		WHEN AREA.ID_AREA = 'Inv' THEN SUM(PATRBF.VERSATO_NETTO) OVER (PARTITION BY AREA.ID_AREA, NOME_PROGETTO) 
		ELSE NULL
	END	AS versato_need_prog,
	CASE 
--	WHEN AREA.ID_AREA = 'Inv' AND SUM(PATRBF.VERSATO_NETTO) OVER (PARTITION BY AREA.ID_AREA, NOME_PROGETTO) IS NULL
	WHEN AREA.ID_AREA = 'Inv' AND COUNT(CASE WHEN PATRBF.VERSATO_NETTO IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA, NOME_PROGETTO) > 0
		THEN 'n.d.'
		ELSE NULL
	END	AS versato_need_progString,
	CASE 
	WHEN AREA.ID_AREA = 'Inv' 
		THEN SUM(PATRBF.CTV - PATRBF.VERSATO_NETTO) OVER (PARTITION BY AREA.ID_AREA, NOME_PROGETTO) 
		ELSE NULL
	END	AS MinusPlusValenza_need_prog,
	CASE 
--	WHEN AREA.ID_AREA = 'Inv' AND SUM(PATRBF.CTV - PATRBF.VERSATO_NETTO) OVER (PARTITION BY AREA.ID_AREA, NOME_PROGETTO) IS NULL
	WHEN AREA.ID_AREA = 'Inv' AND COUNT(CASE WHEN (PATRBF.CTV - PATRBF.VERSATO_NETTO) IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA, NOME_PROGETTO) > 0
		THEN 'n.d.'
		ELSE NULL
	END	AS MinusPlusValenza_need_progString,
	CASE 
		WHEN AREA.ID_AREA = 'Inv' THEN RISCHIO_AGGR_PROG.VAR_PERC_PTF
		ELSE NULL
	END	AS somma_var_need_prog,
	CASE 
		WHEN AREA.ID_AREA = 'Inv' THEN RISCHIO_AGGR_PROG.COPERTURA
		ELSE NULL
	END	AS somma_copertura_need_prog,
	cast(RISCHIO_AGGR_AREA.VAR_PERC_PTF as decimal(15,2)) AS var_needarea,
	--COALESCE(cast(RISCHIO_AGGR_AREA.VAR_PERC_PTF as decimal(15,2)),'n.c.') AS var_needareaString,
	CASE 
		--WHEN ISNULL(RISCHIO_AGGR_AREA.VAR_PERC_PTF,0.00) = 0.00 THEN 'n.c.' 
		WHEN ISNULL(RISCHIO_AGGR_AREA.COPERTURA,0.00) = 0.00 THEN 'n.c.' 
--------------------------------------------------------------
--INIZIO INTERVENTI OMNIA QUIII				
		WHEN @TOTSELFLIQ = @TOTLIQ and AREA.ID_AREA = 'LIQ' 
			THEN 'n.c.' 
--FINE INTERVENTI OMNIA QUIII
--------------------------------------------------------------			
		ELSE NULL 
	END as var_needareaString,
	CAST(RISCHIO_AGGR_AREA.COPERTURA AS DECIMAL(5,2)) AS copertura_needarea,
	CASE 
		WHEN RISCHIO_AGGR_AREA.copertura > 0 AND RISCHIO_AGGR_AREA.copertura < 100
			--Valerio danilo THEN '(*) Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIO_AGGR_AREA.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'  
			THEN 'Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIO_AGGR_AREA.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'  
		ELSE NULL  
	END AS copertura_needareaString,
	RISCHIO_AGGR_PROG.VAR_PERC_PTF AS var_need_prog,
	--CASE WHEN ISNULL(RISCHIO_AGGR_PROG.VAR_PERC_PTF,0.00) = 0.00 THEN 'n.c.' ELSE NULL END as var_need_progString,
	CASE 
		--WHEN ISNULL(RISCHIO_AGGR_PROG.VAR_PERC_PTF,0.00) = 0.00 THEN 'n.c.' 
		WHEN ISNULL(RISCHIO_AGGR_PROG.COPERTURA,0.00) = 0.00 THEN 'n.c.' 
		ELSE NULL 
	END as var_need_progString,
	RISCHIO_AGGR_PROG.COPERTURA AS copertura_need_prog,
	dbo.TOSHORTDATESTRING(PATRBF.DATA_SOTTOSCRIZIONE) AS dataSottoscrizione, 
	PATRBF.ID_CONTRATTO AS ContrattoDossier, 
	--ANAGPROD.DESCR_PRODOTTO AS Descrizione,
	--V
	CASE WHEN PATRBF.TIPO_PRODOTTO <> 'CC' THEN ANAGPROD.DESCR_PRODOTTO
		 ELSE ANAGPROD.DESCR_PRODOTTO+' '+SUBSTRING(COD_CC,4,2) + '/' + SUBSTRING(COD_CC,6,LEN(ANAG_CC.COD_CC))
	END AS Descrizione,
	PATRBF.CTV AS Controvalore, 
	PATRBF.VERSATO_NETTO AS VersatoNetto, 
	CASE WHEN PATRBF.VERSATO_NETTO IS NULL THEN 'n.d.' ELSE NULL END as VersatoNettoString,
	PATRBF.CTV - PATRBF.VERSATO_NETTO AS MinusPlusValenza, 
	CASE WHEN PATRBF.VERSATO_NETTO IS NULL THEN 'n.d.' ELSE NULL END as MinusPlusValenzaString,
	--RISCHIO_PROD.VAR_PERC_PTF AS VaRprodotto,  --commentata per assenza o,oo per i cc fideuram il 29/4/2020
	case when (patrbf.tipo_prodotto ='CC') then 0.00 
	else RISCHIO_PROD.VAR_PERC_PTF
	end AS VaRprodotto, 
--	CASE WHEN ISNULL(RISCHIO_PROD.Copertura,0.00) = 0.00 THEN 'n.c.' --commentata per assenza o,oo per i cc fideuram il 29/4/2020
--	     WHEN patrbf.isin in('EURO00000009', 'EURO10000007' ) THEN 'n.c.'
--	ELSE NULL END as varProdottoString,
	CASE WHEN ISNULL(RISCHIO_PROD.Copertura,0.00) = 0.00 and patrbf.tipo_prodotto <>'CC' THEN 'n.c.' 
		 WHEN patrbf.isin in('EURO00000009', 'EURO10000007') THEN 'n.c.'
	ELSE NULL END as varProdottoString,
	RISCHIO_PROD.Copertura AS Coperturaprodotto, 
	PATRBF.REND_ANNO AS PerformanceYTD, 
	PATRBF.REND_SOTTOSCRIZIONE AS performanceDS,
	PARTVIA_DISINV AS partitaViaggiante,
	CASE 
		WHEN RISCHIO_AGGR_AREA.copertura > 0 AND RISCHIO_AGGR_AREA.copertura < 100 THEN '*' 
		ELSE NULL 
	END AS nota_needarea,
   RISCHIO_PROD.CREDITRISK as creditrisk,
CASE 
	WHEN ISNULL(RISCHIO_prod.COPERTURA,0.00) = 0.00 THEN 'n.c.' 
	WHEN RISCHIO_PROD.CREDITRISK IS NULL THEN 'n.a.'
		ELSE NULL 
	END as creditriskstring,
	RISCHIO_PROD.CREDITRISKEMIT as creditriskemit,
	patrbf.tipo_prodotto as Tipo_Prodotto,
	isAPC = 'N'
FROM C6MART.PATRIMONIO_BF PATRBF
INNER JOIN C6MART.ANAG_PRODOTTI ANAGPROD 
	ON ANAGPROD.COD_PRODOTTO = PATRBF.COD_PRODOTTO
INNER JOIN C6MART.AREA_BISOGNO AREA 
	ON LEFT(PATRBF.ID_AREA,3) = AREA.ID_AREA 
LEFT JOIN C6MART.RISCHIO_AGGREGATO RISCHIO_PROD 
	ON PATRBF.RETE= RISCHIO_PROD.RETE
	AND PATRBF.COD_FISCALE= RISCHIO_PROD.COD_FISCALE
	AND RISCHIO_PROD.COD_AGGREG = 
--		CASE 
--			WHEN AREA.ID_AREA = 'Na' THEN 'RISORSENONASSOCIATE|' 
--			ELSE 
				CASE 
					WHEN (PATRBF.NOME_PROGETTO IS NOT NULL)-- AND (LEFT(PATRBF.ID_AREA,3)<>'Na' Or PATRBF.NOME_PROGETTO <> 'Default')
						THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO+ '|'
					when LEFT(PATRBF.ID_AREA,3)='Na' then 'RISFIN|NA|'
					ELSE 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' 
				END
--		END + PATRBF.POSITION_ID 
		+ PATRBF.POSITION_ID 
LEFT JOIN C6MART.RISCHIO_AGGREGATO RISCHIO_AGGR_PROG
	ON PATRBF.RETE= RISCHIO_AGGR_PROG.RETE
	AND PATRBF.COD_FISCALE= RISCHIO_AGGR_PROG.COD_FISCALE
	AND RISCHIO_AGGR_PROG.COD_AGGREG = 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO
LEFT JOIN C6MART.RISCHIO_AGGREGATO RISCHIO_AGGR_AREA
	ON PATRBF.RETE= RISCHIO_AGGR_AREA.RETE
	AND PATRBF.COD_FISCALE= RISCHIO_AGGR_AREA.COD_FISCALE
	AND RISCHIO_AGGR_AREA.COD_AGGREG = 
    case 
		when LEFT(PATRBF.ID_AREA,3) = 'Na' then 'RISFIN|NA'
         else  'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) 
		end
--	AND RISCHIO_AGGR_AREA.COD_AGGREG = 
--		CASE AREA.ID_AREA 
--			WHEN 'Na' THEN 'RISORSENONASSOCIATE' 
--			ELSE 'COMPLESSIVO|BF|' + LEFT(PATRBF.ID_AREA,3) 
--		END
LEFT OUTER JOIN C6Mart.ANAG_CC
ON 
	ANAG_CC.COD_FISCALE = PATRBF.COD_FISCALE
	AND ANAG_CC.RETE = PATRBF.RETE
	AND cast(ANAG_CC.cod_cc as integer ) = substring(PATRBF.position_id,72,9)
WHERE 1 = 1
	AND PATRBF.RETE = @Rete
	AND PATRBF.COD_FISCALE = @CodiceFiscale
	--V  
	AND PATRBF.CTV > 0
--Bido
    --AND ( 
    --PATRBF.CTV > 0 
    --OR  ( PATRBF.CTV < 0 AND  patrbf.tipo_prodotto = 'cc' ) 
    --)
--
ORDER BY AREASORTORDER, nome_progetto, PATRBF.CTV DESC
END
PRINT N'Altering [C6Mart].[PL_D2_S171PatrimonioNonRappresentabileAssettClass]'