CREATE   procedure [C6StagingPeriodico].[agg_wh_tabellone]
	as
	begin
	drop table if exists #appo
	drop table if exists #appo1
	SELECT 
    PATRBF.RETE,
    PATRBF.COD_FISCALE,
    PATRBF.INSTITUTENAME,
    CTV,
    M.PROFILO_ASS AS CODICEPROFILO,
    CR.MAX_VAR AS  VARMAX,
    RISK_AGG.VAR_PERC_PTF AS VAR ,
    	CASE 
		WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 THEN 'n.c.' 
		ELSE NULL 
	END  AS VARSTRING,
    RISK_AGG.CREDITRISK  RISKCLASS,
        	CASE 
		WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 THEN 'n.c.' 
        WHEN RISK_AGG.creditrisk is null THEN 'n.a.' 
        WHEN RISK_AGG.creditrisk = 0.00 THEN 'n.a.' 
		ELSE NULL 
	END AS  RISKCLASSSTRING,
    ISNULL(RISK_AGG.COPERTURA,0.00) AS COVERAGE,
--V La copertura viene valorizzata con un valore da 0 da 100
  --  	CASE 
	--	WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 THEN 'n.c.' 
--		ELSE 
NULL 
--	END 
AS COVERAGESTRING,
    M.RISKCLASS AS RISKCLASSMAX,
    round((SUM(CTV) OVER (PARTITION BY PATRBF.COD_FISCALE,PATRBF.RETE, PATRBF.INSTITUTENAME) / 
    -- 16.07.2011 COLAIANNI CONSOLI GESTIONE DIVISIONE PER ZERO
    CASE WHEN ( SUM(CTV) OVER (PARTITION BY PATRBF.RETE, PATRBF.COD_FISCALE) ) = 0
		 THEN 1
	     ELSE ( SUM(CTV) OVER (PARTITION BY PATRBF.RETE, PATRBF.COD_FISCALE) )
    END)		
    *100 ,2)AS PERCENTAGE,
	--V
	ORDINE,
	RISK_AGG.Ully_perc
	-- 16/7/2018
	into #appo
	-- fine 16/7/2018
    FROM
    (
    select 
    PATRBF.RETE,
    PATRBF.COD_FISCALE,
    CASE WHEN PATRBF.RETE='F' THEN 'Patrimonio Fideuram' WHEN PATRBF.RETE='S' then 'Patrimonio Sanpaolo Invest' WHEN PATRBF.RETE='W' then 'Patrimonio IW Private Inv.' END AS INSTITUTENAME,
    SUM(PATRBF.CTV) AS CTV,
	--V
	1 as ORDINE,
	RISK_AGG.Ully_perc
    from
    c6martperiodico.patrimonio_bf  PATRBF,C6MartPeriodico.RISCHIO_AGGREGATO RISK_AGG
    where 
    1=1
    and PATRBF.COD_FISCALE = RISK_AGG.COD_FISCALE 
    and PATRBF.rete= RISK_AGG.rete    
    and PATRBF.rete+ PATRBF.cod_fiscale in (select chiave_acn from c6martperiodico.tabellone)
    and PATRBF.CTV > 0
    and
    (
      RISK_AGG.COD_AGGREG= 'COMPLESSIVO|BF' --or
      --RISK_AGG.COD_AGGREG= 'COMPLESSIVO|TERZI'
    )
    GROUP BY 
    PATRBF.RETE,
    PATRBF.COD_FISCALE,
	RISK_AGG.Ully_perc,   
    CASE WHEN PATRBF.RETE='F' THEN 'Patrimonio Fideuram' WHEN PATRBF.RETE='S' then  'Patrimonio Sanpaolo Invest' WHEN PATRBF.RETE='W' then 'Patrimonio IW Private Inv.' END
    UNION
    SELECT 
    TERZI.RETE,
    TERZI.COD_FISCALE,
    'Patrimonio altri Istituti' AS INSTITUTENAME,
    SUM(ROUND(TERZI.CTV,2)) AS CTV,
	--V
	2 as ORDINE,
	RISK_AGG.Ully_perc
    FROM
    c6martperiodico.PATRIMONIO_TERZI  TERZI,C6MartPeriodico.RISCHIO_AGGREGATO RISK_AGG
    where 
    1=1
    and TERZI.COD_FISCALE = RISK_AGG.COD_FISCALE 
    and TERZI.rete= RISK_AGG.rete 
       and terzi.rete+ terzi.cod_fiscale in (select chiave_acn from c6martperiodico.tabellone)
    and TERZI.CTV > 0
    and
    (
      --RISK_AGG.COD_AGGREG= 'COMPLESSIVO|BF' or
      RISK_AGG.COD_AGGREG= 'COMPLESSIVO|TERZI'
    )    
    GROUP BY 
    TERZI.RETE,
    TERZI.COD_FISCALE,
    RISK_AGG.Ully_perc
    ) PATRBF
    LEFT OUTER JOIN 
    c6martperiodico.MIFID AS M
    ON 
    PATRBF.COD_FISCALE=M.COD_FISCALE
    AND PATRBF.RETE=M.RETE
	LEFT OUTER JOIN c6martperiodico.CODIFICA_RISCHIO AS CR
	ON M.PROFILO_ASS = CR.PROFILO
    LEFT OUTER JOIN 
    c6martperiodico.CODIFICA_CREDITRISK  CK
    ON 
    M.riskclass=CK.PROFILO
    LEFT OUTER JOIN 
    c6martperiodico.RISCHIO_AGGREGATO  RISK_AGG
    ON 
    PATRBF.COD_FISCALE=RISK_AGG.COD_FISCALE
    AND PATRBF.RETE=RISK_AGG.RETE
    AND 
    (case when  RISK_AGG.COD_AGGREG = 'COMPLESSIVO|BF' and PATRBF.RETE= 'F'  then 'Patrimonio Fideuram' 
          WHEN  RISK_AGG.COD_AGGREG= 'COMPLESSIVO|BF' and PATRBF.RETE= 'S'  then 'Patrimonio Sanpaolo Invest'
		  WHEN  RISK_AGG.COD_AGGREG= 'COMPLESSIVO|BF' and PATRBF.RETE= 'W'  then 'Patrimonio IW Private Inv.'
          else 'Patrimonio altri Istituti' end)= PATRBF.INSTITUTENAME
   where
    (
      RISK_AGG.COD_AGGREG= 'COMPLESSIVO|BF' or
      RISK_AGG.COD_AGGREG= 'COMPLESSIVO|TERZI'
    )
    UNION ALL
    SELECT 
    PATRBF.RETE,
    PATRBF.COD_FISCALE,
    'Patrimonio Complessivo' AS INSTITUTENAME,
    SUM(CTV),
    MAX(M.PROFILO_ASS) AS CODICEPROFILO,
    MAX(CR.MAX_VAR) AS  VARMAX,
    MAX(RISK_AGG.VAR_PERC_PTF) AS VAR ,
    MAX(	CASE 
		WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 THEN 'n.c.' 
		ELSE NULL 
	END) AS VARSTRING,
    MAX(RISK_AGG.CREDITRISK)  RISKCLASS,
    MAX(
         	case	WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 THEN 'n.c.' 
        WHEN RISK_AGG.creditrisk is null THEN 'n.a.' 
        WHEN RISK_AGG.creditrisk = 0.00 THEN 'n.a.' 
		ELSE NULL 
	END )
      AS  RISKCLASSSTRING,
    MAX(ISNULL(RISK_AGG.COPERTURA,0.00)) AS COVERAGE,
    MAX(   	CASE 
		WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 THEN 'n.c.' 
		ELSE NULL 
	END) AS COVERAGESTRING,
    --MAX(CK.DESCRIZIONE) AS RISKCLASSMAX,
	MAX(M.RISKCLASS) AS RISKCLASSMAX,
    100 AS PERCENTAGE,
	--V
	3 AS ORDINE,
	RISK_AGG.Ully_perc
    FROM
    (
    select 
    PATRBF.RETE,
    PATRBF.COD_FISCALE,
    SUM(PATRBF.CTV) AS CTV
    from
    c6martperiodico.patrimonio_bf  PATRBF
    where 
    1=1 
       and rete+cod_fiscale in (select chiave_acn from c6martperiodico.tabellone)
	and PATRBF.CTV > 0    
    GROUP BY 
    PATRBF.RETE,
    PATRBF.COD_FISCALE
    UNION
    SELECT 
    TERZI.RETE,
    TERZI.COD_FISCALE,
    SUM(ROUND(TERZI.CTV,2)) AS CTV
    FROM
    c6martperiodico.PATRIMONIO_TERZI  TERZI
        where 
    1=1 
       and rete+ cod_fiscale in (select chiave_acn from c6martperiodico.tabellone)
	and TERZI.CTV > 0       
     GROUP BY 
    TERZI.RETE,
    TERZI.COD_FISCALE
    ) PATRBF
    LEFT OUTER JOIN 
    c6martperiodico.MIFID AS M
    ON 
    PATRBF.COD_FISCALE=M.COD_FISCALE
    AND PATRBF.RETE=M.RETE
	LEFT OUTER JOIN c6martperiodico.CODIFICA_RISCHIO AS CR
	ON M.PROFILO_ASS = CR.PROFILO
    LEFT OUTER JOIN 
    c6martperiodico.CODIFICA_CREDITRISK  CK
    ON 
      M.riskclass=CK.PROFILO
    LEFT OUTER JOIN 
    c6martperiodico.RISCHIO_AGGREGATO  RISK_AGG
    ON 
    PATRBF.COD_FISCALE=RISK_AGG.COD_FISCALE
    AND PATRBF.RETE=RISK_AGG.RETE
        where  
    (
      RISK_AGG.COD_AGGREG= 'COMPLESSIVO'
    )
    GROUP BY 
    PATRBF.RETE,
    PATRBF.COD_FISCALE,
    RISK_AGG.Ully_perc
	--V
	order by ordine
	select a.RETE,a.COD_FISCALE,a.VARMAX,a.VAR,a.RISKCLASS,a.RISKCLASSMAX,	case 
	when var <= varmax then '1'
	when var > varmax then '0'
	else '-1' end as Rischio_Mercato,case
	--20180820 CR su Rischio Credito: quando non è stato possibile calcolare il rischio credito (quindi il valore di RISKCLASS restituito dalla PL_D2_S169RischiomercatoRischiocredito è NULL)
	-- l'indicatore di adeguatezza Rischio Credito è 1 (Adeguato)
	when RISKCLASS is null then '1'
	--20180820 FINE CR su Rischio Credito
	when RISKCLASS <= convert(int,RISKCLASSMAX) then '1'
	when RISKCLASS > convert(int,RISKCLASSMAX) then '0'
	else -1
	end as Rischio_Credito 
	into #appo1 
	from #appo a where RETE+COD_FISCALE in (select chiave_acn from C6MartPeriodico.tabellone) and ordine=1 order by COD_FISCALE 
	UPDATE [C6StampeCentralizzate].[C6MartPeriodico].[tabellone]
	set corfa_rfa=w.Patrimoniobancafideuramctv, Stato_Cliente= case when w.scaduto=0 then 'Attivo' when w.Scaduto=1 then 'Scaduto' when w.Scaduto=2 then 'Parziale' end 
	from wh.Maledetti w inner join [C6StampeCentralizzate].[C6MartPeriodico].[tabellone] as a on w.rete+w.CodiceFiscale=a.chiave_acn
	UPDATE [C6StampeCentralizzate].[C6MartPeriodico].[tabellone]
	set ade_rischio_credito=a.Rischio_Credito, ade_rischio_mercato= case when a.Rischio_Mercato=1 then 'S' else 'N' end  
	from #appo1 a inner join C6MartPeriodico.tabellone t on a.rete+a.COD_FISCALE=t.chiave_acn
		--ESG Rating 29/09/2022
		update [C6StampeCentralizzate].[C6MartPeriodico].[tabellone] 
		set esg_e_perc=e.esg_e_perc,esg_s_perc=e.ESG_S_perc,esg_g_perc=e.ESG_G_perc
		from C6StagingPeriodico.ESG_Metriche_Cliente_IL e inner join C6MartPeriodico.tabellone t
		on e.Rete+e.CodiceFiscale=t.chiave_acn
		where e.CodiceFiscale<>''
		update [C6StampeCentralizzate].[C6MartPeriodico].[tabellone] 
		set esg_e_perc=e.esg_e_perc,esg_s_perc=e.ESG_S_perc,esg_g_perc=e.ESG_G_perc
		from C6StagingPeriodico.ESG_Metriche_Cliente_IL e inner join C6MartPeriodico.tabellone t
		on e.Rete+e.piva+e.CodMan=t.chiave_cap
		where e.CodiceFiscale=''
		--fine ESG Rating
		update [C6StampeCentralizzate].[C6MartPeriodico].[tabellone] 
		set var_portafoglio=ris.VAR_PERC_PTF
		from C6MartPeriodico.RISCHIO_AGGREGATO ris inner join C6MartPeriodico.tabellone t
		on ris.Rete+ris.COD_FISCALE=t.chiave_acn where COD_AGGREG = 'COMPLESSIVO'
		truncate table C6MartPeriodico.APPREND_DTSX_Tabellone 
insert into C6MartPeriodico.APPREND_DTSX_Tabellone 
select * from C6MartPeriodico.vTabellone
end