PDC_REPORT_CreazioneDB/sql/Produzione/procedure/wh_MERGED_PROCEDURE_S170.sql

CREATE procedure [wh].[MERGED_PROCEDURE_S170]
AS
BEGIN
	SET NOCOUNT ON;
	-- Appoggio in tabelle temporanee
	select rete, cod_fiscale, count(*) as contaCC
	into #tContaCC
	from c6martperiodico.patrimonio_Bf
	where tipo_prodotto = 'CC'
	group by rete, cod_fiscale
	select rete, cod_fiscale, count(*) as contaPos
	into #tContaPos
	from c6martperiodico.patrimonio_Bf
	group by rete, cod_fiscale
	SELECT rete, cod_fiscale, isnull(sum(ctv),0) as CTVBF
	into #tCTVBF
	FROM  c6martperiodico.patrimonio_bf  			
	where CTV > 0
	group by rete, cod_fiscale
	SELECT rete, cod_fiscale, isnull(sum(ctv),0) as CTVTerzi
	into #tCTVTerzi
	FROM  c6martperiodico.patrimonio_terzi  			
	where CTV > 0
	group by rete, cod_fiscale
	-- Calcolo Complessità Massima
	DECLARE @DataFineTrim VARCHAR(8)  
    SET @DataFineTrim = c6martperiodico.getTrimestre2(getDate(),0)
	create table #tMaxComplessita (Rete varchar(1), CodiceFiscale varchar(20), MaxComplessita int)
	insert into #tMaxComplessita (Rete, CodiceFiscale, MaxComplessita)
		select rete, 'FF@' + CODMAN as CodiceFiscale, MAXCOMPL  as maxComplessita
		from [C6StagingPeriodico].[WSEIAN2]
		where DTTRIM =  @DataFineTrim
		--	and compl_base <> 10
			and isnull(codman, '') <> ''
		group by rete, 'FF@' + CODMAN,MAXCOMPL
		union
		select rete, CodFis as CodiceFiscale, MAXCOMPL  as maxComplessita
		from [C6StagingPeriodico].[WSEIAN2]
		where DTTRIM =  @DataFineTrim
			--and compl_base <> 10
			and isnull(codman, '') = ''
		group by rete, CodFis,MAXCOMPL
		--select * from #tContaCC where rete = 'F' and cod_Fiscale = 'RSCFNC43M09A965T'
		--select * from #tContaPos where rete = 'F' and cod_Fiscale = 'RSCFNC43M09A965T'
		--select * from #tCTVBF where rete = 'F' and cod_Fiscale = 'RSCFNC43M09A965T'
		--select * from #tCTVTerzi where rete = 'F' and cod_Fiscale = 'RSCFNC43M09A965T'
	insert into wh.PL_D2_S170RischioDiversificazione
		SELECT 'C6MartPeriodico.PL_D2_S170RischioDiversificazione' as ProcedureName, K.Rete as i_Rete, K.Cod_Fiscale as i_CodiceFiscale,
			K.COD_FISCALE,
			K.RETE,
			CASE 
				when cod_Aggreg = 'COMPLESSIVO|BF' then isnull(#tCTVBF.CTVBF, 0)
				when cod_Aggreg = 'COMPLESSIVO|TERZI' then isnull(#tCTVTerzi.CTVTerzi, 0)
				when cod_Aggreg = 'COMPLESSIVO' then isnull(#tCTVBF.CTVBF, 0) + isnull(#tCTVTerzi.CTVTerzi, 0)
				else 0
			END AS CTV_AGGREG,	
			K.CREDITRISK,
			CASE 
				WHEN (isnull(#tContaPOS.contaPOS, 0) - isnull(#tContaCC.ContaCC, 0) = 0) THEN 0
				ELSE VAR_PERC_PTF 
			END AS VAR,
			CASE WHEN ISNULL(COPERTURA,0.00) = 0.00 AND isnull(#tContaPOS.contaPOS, 0) - isnull(#tContaCC.ContaCC, 0) <> 0 THEN 'n.c.' ELSE NULL END as varString,
			CASE 
				WHEN (ISNULL(copertura,100) < 100) AND isnull(#tContaPOS.contaPOS, 0) - isnull(#tContaCC.ContaCC, 0) <> 0
					--THEN 'Grado di copertura: ' + REPLACE(CAST(CAST(COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'  
					THEN REPLACE(CAST(CAST(COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') --+ '%'  
				ELSE null 
			END AS coperturaString,
			-- modifica del 19/6/2018 - gestione indicatore per gli altri ptf del Diagnosi
			beneficio as diversificazione,
			--case 
			--when cod_Aggreg = 'COMPLESSIVO' then null
			--when cod_Aggreg = 'COMPLESSIVO|BF' then beneficio
			--when cod_Aggreg = 'COMPLESSIVO|TERZI' then null
			--END AS diversificazione,
			-- fine modifica del 19/6/2018 
			CASE 
				WHEN (isnull(#tContaPOS.contaPOS, 0) - isnull(#tContaCC.ContaCC, 0) = 0) THEN 100
				ELSE COPERTURA
			END AS COPERTURA,
			case 
			when cod_Aggreg = 'COMPLESSIVO' then 'Patrimonio Complessivo'
			when cod_Aggreg = 'COMPLESSIVO|BF' then 'Patrimonio ' + (case when k.rete ='F' then 'Fideuram' else 'Sanpaolo Invest' end)
			when cod_Aggreg = 'COMPLESSIVO|TERZI' then 'Patrimonio altri Istituti'
			ELSE ''
			END AS PATRIMONIO,
			case 
				when b.profilo is null then -1 
				else b.profilo
			end as profiloCode,
			isnull(b.max_var, 0) as var_profilo,
			------------------------    MIFID2 - MAGGIO 2018
			case #tMaxComplessita.MaxComplessita
        				when 0 then '-'
						when 1 then 'Minima'
						when 2 then 'Bassa'
						when 3 then 'Media'
						when 4 then 'Alta'
						when 5 then 'Molto alta'
						else '-'
					end
					as complessita,
			-------------------------  FINE MIFID2 - MAGGIO 2018
			--V l'ordine serve per la corretta visualizzazione del report: Patrimonio casa, patrimonio terzi, patrimonio complessivo
			--V Monitoraggio prende soltanto la prima riga, diagnosi anche nel caso non ci siano i terzi.
			case 
			when cod_Aggreg = 'COMPLESSIVO' then 3
			when cod_Aggreg = 'COMPLESSIVO|BF' then 1
			when cod_Aggreg = 'COMPLESSIVO|TERZI' then 2
			ELSE ''
			END AS Ordine
		FROM C6MARTperiodico.RISCHIO_AGGREGATO K
			left outer JOIN c6martperiodico.mifid a
			ON k.cod_fiscale = a.cod_fiscale
			and k.rete = a.rete
				left outer join C6Martperiodico.CODIFICA_RISCHIO b
				on A.PROFILO_ASS=B.PROFILO
					left join #tContaCC
					on K.rete = #tContaCC.rete
					and K.cod_fiscale = #tContaCC.cod_fiscale
						left join #tContaPos
						on K.rete = #tContaPos.rete
						and K.cod_fiscale = #tContaPos.cod_fiscale
							left join #tCTVBF
							on K.rete = #tCTVBF.rete
							and K.cod_fiscale = #tCTVBF.cod_fiscale
								left join #tCTVTerzi
								on K.rete = #tCTVTerzi.rete
								and K.cod_fiscale = #tCTVTerzi.cod_fiscale
									left join #tMaxComplessita
									on K.rete = #tMaxComplessita.rete
									and K.cod_fiscale = #tMaxComplessita.codicefiscale
		WHERE
		--A.PROFILO_ASS=B.PROFILO
		--AND K.RETE=A.RETE
		--AND K.COD_FISCALE= A.COD_FISCALE
		COD_AGGREG IN ('COMPLESSIVO|BF', 'COMPLESSIVO', 'COMPLESSIVO|TERZI')
		ORDER BY ordine
END