-- Stored procedure
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
-- [C6MartPeriodico].[PL_S23DettaglioRischio]   'F','BRNDRA65B04B300I'
CREATE procedure [C6MartPeriodico].[PL_S23DettaglioRischio] 
	-- 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 
		isnull(MIFID.PROFILO_ASS,0)  as codiceProfilo,
		CASE 
			WHEN (CODRISCHIO.MAX_VAR is null) THEN 'n.c'
			ELSE null
		END as varProfiloMaxString,
		CODRISCHIO.MAX_VAR as varProfiloMax,
		MIFID.RISKCLASS AS CreditRiskMax,
		CASE
			WHEN MIFID.RISKCLASS IS NULL THEN 'n.c.'
			ELSE NULL
		END AS  CreditRiskMaxString, 
		PT.INTERMEDIARIO AS Intermediario,
		ISNULL(RISCHIO.VAR_PERC_PTF,0) as varIntermediario,
		CASE 
			WHEN RISCHIO.COPERTURA = 0 THEN 'n.c.' 
			WHEN RISCHIO.COPERTURA IS NULL THEN 'n.c.' 
			ELSE NULL 
		END AS varIntermediarioString,
		rischio.copertura as coperturaBF,
		CASE 
			WHEN RISCHIO.COPERTURA = 0 THEN 'n.c.' 
			WHEN RISCHIO.COPERTURA IS NULL THEN 'n.c.' 
			ELSE NULL 
		END AS coperturaBFString,
		CASE 
			-- WHEN RISCHIO.COPERTURA > 0 AND RISCHIO.VAR_PERC_PTF <= CODRISCHIO.MAX_VAR THEN 1
			-- modified by mzaki
			WHEN RISCHIO.COPERTURA > 0 AND RISCHIO.VAR_PERC_PTF <= CODRISCHIO.MAX_VAR 
				AND isnull(RISCHIO.CREDITRISK,0) <= MIFID.RISKCLASS THEN 1
			--
			WHEN RISCHIO.COPERTURA = 0  THEN -1
			ELSE 0 
		END AS rispetto,
		-----------------------
		RISCHIO.CREDITRISK as creditRisk,
		CASE 
			WHEN RISCHIO.CREDITRISK is null and RISCHIO.COPERTURA = 0  THEN 'n.c.' 
			WHEN  RISCHIO.CREDITRISK is null and RISCHIO.COPERTURA IS NULL THEN 'n.c.' 
			WHEN  RISCHIO.CREDITRISK is null then 'n.a.' 
		ELSE null
		END as creditRiskString
	FROM 
		C6MartPeriodico.PATRIMONIO_TERZI PT
	LEFT OUTER JOIN 
		C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO
		ON 
			RISCHIO.RETE = PT.RETE
			AND RISCHIO.COD_FISCALE = PT.COD_FISCALE
			AND RISCHIO.COD_AGGREG = 'COMPLESSIVO|TERZI|' + PT.INTERMEDIARIO
	LEFT OUTER JOIN 
		C6MartPeriodico.MIFID MIFID
		ON 
			MIFID.RETE = PT.RETE
			AND MIFID.COD_FISCALE = PT.COD_FISCALE
	LEFT OUTER JOIN 
		C6MartPeriodico.CODIFICA_RISCHIO CODRISCHIO
		ON 
			MIFID.PROFILO_ASS = CODRISCHIO.PROFILO
	WHERE 1 = 1
		--V we take into account CC --AND PT.TIPO_PRODOTTO <> 'Conti correnti'
		AND PT.RETE = @Rete
		AND PT.cod_fiscale = @CodiceFiscale
END