-- =============================================
-- Author:		<DMO>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--[C6martPeriodico].[PL_MP_S132ProfiloRischioAdeguatezza]F,BRZGLI88S43F839A
CREATE procedure [C6MartPeriodico].[PL_MP_S132ProfiloRischioAdeguatezza_BkUpParall]
	-- Add the parameters for the stored procedure here
	@Rete char(1), 
	@CodiceFiscale varchar(16)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
--	DECLARE @varBF DECIMAL(12,2)
--	DECLARE @coperturaBF DECIMAL(12,2)
--	DECLARE @codiceProfilo SMALLINT
--	DECLARE @dataProfilo DATETIME
--	DECLARE @varProfilo DECIMAL(5,2)
--	DECLARE @nomeProfilo VARCHAR(50)
--    DECLARE @riskclass VARCHAR(50)
--    DECLARE @experience SMALLINT
--
--
--	SELECT
--        @codiceProfilo = M.PROFILO_ASS,
--		@dataProfilo = M.DATA_INIZIO_VAL,
--		@varProfilo = CR.MAX_VAR,
--		@nomeProfilo = ProfiliDiRischio.NOMEPROFILO,
--        @riskclass =  m.riskclass,
--        @experience = m.experience
--	FROM
--		C6MartPERIODICO.MIFID AS M
--		INNER JOIN C6MartPERIODICO.CODIFICA_RISCHIO AS CR
--		ON M.PROFILO_ASS = CR.PROFILO
--		INNER JOIN DBO.ProfiliDiRischio AS ProfiliDiRischio
--		ON M.PROFILO_ASS = ProfiliDiRischio.CODICEPROFILO
--	WHERE
--		M.RETE = @Rete AND
--		M.COD_FISCALE = @CodiceFiscale
--
--	SELECT
--        @codiceProfilo as codiceProfilo,
--		DBO.TOSHORTDATESTRING (@dataProfilo) as dataInizioValidita,
--		@varProfilo as varMassimo,
--		ISNULL(@varBF,0) as varBF,
--		ISNULL(@coperturaBF,0) as coperturaBF,
--		@nomeProfilo as nomeProfilo,
--        @riskclass as riskclass,
--        @experience as experince
	declare @contaCC as int
	select @contaCC = count(*)
	from c6martperiodico.patrimonio_Bf
	where tipo_prodotto = 'CC' and cod_fiscale = @codiceFiscale and rete = @rete
	declare @contaPOS as int
	select @contaPOS = count(*)
	from c6martperiodico.patrimonio_Bf
	where cod_fiscale = @codiceFiscale and rete = @rete
    SELECT 
    PATRBF.RETE,
    PATRBF.COD_FISCALE,
    PATRBF.INSTITUTENAME,
    CTV,
    M.PROFILO_ASS AS CODICEPROFILO,
--CONVERT(DATETIME,M.DATA_INIZIO_VAL) AS  DATAINIZIOVALIDITA,
	DBO.TOSHORTDATESTRING(M.DATA_INIZIO_VAL) AS  DATAINIZIOVALIDITA,
    CR.MAX_VAR AS  VARMAX,
	ProfiliDiRischio.NOMEPROFILO as NOMEPROFILO,
	--V Imposizione VaR a 0 nel caso di presenza di solo cc
	CASE
		WHEN (@contaPOS - @contaCC) = 0 THEN 0.00
		ELSE RISK_AGG.VAR_PERC_PTF
	END AS VAR,
	CASE 
		WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 AND (@contaPOS - @contaCC) <> 0 THEN 'n.c.' 
		ELSE NULL 
	END  AS VARSTRING,
    RISK_AGG.CREDITRISK  RISKCLASS,
        	CASE 
		WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 THEN 'n.a.' 
        WHEN RISK_AGG.creditrisk is null THEN 'n.a.' 
        WHEN RISK_AGG.creditrisk = 0.00 THEN 'n.a.' 
		ELSE NULL 
	END AS  RISKCLASSSTRING,
	CASE
		WHEN (@contaPOS - @contaCC) = 0 THEN 100  
		ELSE ISNULL(RISK_AGG.COPERTURA,0.00) 
	END AS COVERAGE,
--    	CASE 
--		WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 THEN 'n.c.' 
--		ELSE 
	NULL AS COVERAGESTRING,
	--V changed from string value (descrizione) to the numeric (profilo)
    M.RISKCLASS AS RISKCLASSMAX,
    (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 AS PERCENTAGE,
    case when RISK_AGG.VAR_PERC_PTF > CR.MAX_VAR then 0 else 1 end as VarIsOk,
    case when isnull(RISK_AGG.CREDITRISK,0) > Ck.PROFILO then 0 else 1 end as RiskClassIsOk,
    CASE WHEN CMP.COMPLESSITA > M.EXPERIENCE THEN 0 ELSE 1 END  as ComplexityIsOk,
    RISK_AGG.Ully_perc
    FROM
    (
    select 
    PATRBF.RETE,
    PATRBF.COD_FISCALE,
    CASE WHEN PATRBF.RETE='F' THEN 'Patrimonio Banca Fideuram' ELSE 'Patrimonio Sanpaolo Invest' END AS INSTITUTENAME,
    SUM(PATRBF.CTV) AS CTV
    from
    c6martperiodico.patrimonio_bf  PATRBF
    where 
    1=1 
    and cod_fiscale = @codiceFiscale
    and rete= @rete
    GROUP BY 
    PATRBF.RETE,
    PATRBF.COD_FISCALE,
    CASE WHEN PATRBF.RETE='F' THEN 'Patrimonio Banca Fideuram' ELSE 'Patrimonio Sanpaolo Invest' END
    ) 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
    left outer join C6MartPeriodico.COMPLESSITA cmp
    on 
    CMP.COD_FISCALE=PATRBF.COD_FISCALE
    AND
    CMP.RETE=PATRBF.RETE
INNER JOIN DBO.ProfiliDiRischio AS ProfiliDiRischio
		ON M.PROFILO_ASS = ProfiliDiRischio.CODICEPROFILO
   where
    (
      RISK_AGG.COD_AGGREG= 'COMPLESSIVO|BF' 
    ) 
END