-- =============================================
-- Author:		<Alessandro Tringali>
-- Create date: <25052010>
-- Description:	< SEZIONE 103>
-- =============================================
--EXEC [C6Mart].[PL_D_S168TabellaEmittenti]  'F','FF@7181'
CREATE procedure [C6Mart].[zzzPL_D2_S168TabellaEmittenti]
	-- 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 @patr_compl decimal(18,3)
select @patr_compl=sum(ctv_prodotto) from 
c6martperiodico.patrimonio_emittenti 
where  cod_fiscale =@codicefiscale
and rete=@rete
and ctv_prodotto > 0
SELECT 
		COD_FISCALE,
		RETE,
		RATING_AAA,
		RATING_AAp,
		RATING_AA,
		RATING_AAm,
		RATING_Ap,
		RATING_A,
		RATING_Am,
		RATING_BBBp,
		RATING_BBB,
		RATING_BBBm,
		RATING_BBp,
		RATING_BB,
		RATING_BBm,
		RATING_Bp,
		RATING_B,
		RATING_Bm,
		RATING_CCCp,
		RATING_CCC,
		RATING_CCCm,
		RATING_CC,
		RATING_C,
		RATING_D,
		RATING_na,
		FLG_RISKCLASS1,
		FLG_RISKCLASS2,
		FLG_RISKCLASS3,
		FLG_RISKCLASS4,
		FLG_RISKCLASS5,
		FLG_RISKCLASS6,
		FLG_RISKCLASS7,
		FLG_RISKCLASS8,
		FLG_RISKCLASS9,
		FLG_RISKCLASS10,
		INTERMEDIARIO,
		ISSUER,
		STOCKSCounterValue,
		bondsCounterValue,
		CONCENTRATION_ISSUER,
		CONCENTRATION,
		NC
--isnull(RATING,'n.a.') as RATING
FROM
(
-- PER INTERMEDIARIO 
    SELECT
			COD_FISCALE,
			RETE,
			MAX(RATING_AAA) AS RATING_AAA,
			MAX(RATING_AAp) AS RATING_AAp,
			MAX(RATING_AA) AS RATING_AA,
			MAX(RATING_AAm) AS RATING_AAm,
			MAX(RATING_Ap) AS RATING_Ap,
			MAX(RATING_A) AS RATING_A,
			MAX(RATING_Am) AS RATING_Am,
			MAX(RATING_BBBp) AS RATING_BBBp,
			MAX(RATING_BBB) AS RATING_BBB,
			MAX(RATING_BBBm) AS RATING_BBBm,
			MAX(RATING_BBp) AS RATING_BBp,
			MAX(RATING_BB) AS RATING_BB,
			MAX(RATING_BBm) AS RATING_BBm,
			MAX(RATING_Bp) AS RATING_Bp,
			MAX(RATING_B) AS RATING_B,
			MAX(RATING_Bm) AS RATING_Bm,
			MAX(RATING_CCCp) AS RATING_CCCp,
			MAX(RATING_CCC) AS RATING_CCC,
			MAX(RATING_CCCm) AS RATING_CCCm,
			MAX(RATING_CC) AS RATING_CC,
			MAX(RATING_C) AS RATING_C,
			MAX(RATING_D) AS RATING_D,
			MAX(RATING_na) AS RATING_na,
			MAX(FLG_RISKCLASS1) AS FLG_RISKCLASS1,
			MAX(FLG_RISKCLASS2) AS FLG_RISKCLASS2,
			MAX(FLG_RISKCLASS3) AS FLG_RISKCLASS3,
			MAX(FLG_RISKCLASS4) AS FLG_RISKCLASS4,
			MAX(FLG_RISKCLASS5) AS FLG_RISKCLASS5,
			MAX(FLG_RISKCLASS6) AS FLG_RISKCLASS6,
			MAX(FLG_RISKCLASS7) AS FLG_RISKCLASS7,
			MAX(FLG_RISKCLASS8) AS FLG_RISKCLASS8,
			MAX(FLG_RISKCLASS9) AS FLG_RISKCLASS9,
			MAX(FLG_RISKCLASS10) AS FLG_RISKCLASS10,
			--V nel caso sia null è BF???
			isnull(INTERMEDIARIO,'BF') AS INTERMEDIARIO,
			EMITTENTE AS ISSUER,
			SUM(STOCKSCounterValue) AS  STOCKSCounterValue,
			sum(bondsCounterValue) AS bondsCounterValue,
			0 as  CONCENTRATION_ISSUER ,
			--V normalizzo la percentuale per essere rappresentato sul report
			MAX(CONCENTRATION)* 100 AS CONCENTRATION,
			sum(case when copertura > 0 then 0 else 1 end) as nc
			--RATING as RATING
				FROM
				(
					SELECT      V.COD_FISCALE,
								V.RETE,
								INTERMEDIARIO,
								CREDITRISKCLASS,
								EMITTENTE,
								CASE WHEN RATING = 'AAA' THEN 1 ELSE 0 END AS RATING_AAA,
								CASE WHEN RATING = 'AA+' THEN 1 ELSE 0 END AS RATING_AAp,
								CASE WHEN RATING = 'AA' THEN 1 ELSE 0 END AS RATING_AA,
								CASE WHEN RATING = 'AA-' THEN 1 ELSE 0 END AS RATING_AAm,
								CASE WHEN RATING = 'A+' THEN 1 ELSE 0 END AS RATING_Ap,
								CASE WHEN RATING = 'A' THEN 1 ELSE 0 END AS RATING_A,
								CASE WHEN RATING = 'A-' THEN 1 ELSE 0 END AS RATING_Am,
								CASE WHEN RATING = 'BBB+' THEN 1 ELSE 0 END AS RATING_BBBp,
								CASE WHEN RATING = 'BBB' THEN 1 ELSE 0 END AS RATING_BBB,
								CASE WHEN RATING = 'BBB-' THEN 1 ELSE 0 END AS RATING_BBBm,
								CASE WHEN RATING = 'BB+' THEN 1 ELSE 0 END AS RATING_BBp,
								CASE WHEN RATING = 'BB' THEN 1 ELSE 0 END AS RATING_BB,
								CASE WHEN RATING = 'BB-' THEN 1 ELSE 0 END AS RATING_BBm,
								CASE WHEN RATING = 'B+' THEN 1 ELSE 0 END AS RATING_Bp,
								CASE WHEN RATING = 'B' THEN 1 ELSE 0 END AS RATING_B,
								CASE WHEN RATING = 'B-' THEN 1 ELSE 0 END AS RATING_Bm,
								CASE WHEN RATING = 'CCC+' THEN 1 ELSE 0 END AS RATING_CCCp,
								CASE WHEN RATING = 'CCC' THEN 1 ELSE 0 END AS RATING_CCC,
								CASE WHEN RATING = 'CCC-' THEN 1 ELSE 0 END AS RATING_CCCm,
								CASE WHEN RATING = 'CC' THEN 1 ELSE 0 END AS RATING_CC,
								CASE WHEN RATING = 'C' THEN 1 ELSE 0 END AS RATING_C,
								CASE WHEN RATING = 'D' THEN 1 ELSE 0 END AS RATING_D,
								CASE WHEN RATING is null THEN 1 ELSE 0 END AS RATING_na, 
								CASE WHEN CREDITRISKCLASS ='Rischio 1/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS1,
								CASE WHEN CREDITRISKCLASS ='Rischio 2/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS2,
								CASE WHEN CREDITRISKCLASS ='Rischio 3/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS3,
								CASE WHEN CREDITRISKCLASS ='Rischio 4/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS4,
								CASE WHEN CREDITRISKCLASS ='Rischio 5/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS5,
								CASE WHEN CREDITRISKCLASS ='Rischio 6/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS6,
								CASE WHEN CREDITRISKCLASS ='Rischio 7/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS7,
								CASE WHEN CREDITRISKCLASS ='Rischio 8/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS8,
								CASE WHEN CREDITRISKCLASS ='Rischio 9/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS9,
								CASE WHEN CREDITRISKCLASS ='Rischio 10/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS10,
								SUM(CTV_AZ_OB_EMIT) as CTV_AZ_OB_EMIT,
								SUM(CTV_PRODOTTO) AS CTV_PRODOTTO,
								sum(CTV_AZIONARIO) AS STOCKSCounterValue,
								sum(CTV_OBBLIGAZIONARIO) AS bondsCounterValue,
								0 AS  CONCENTRATION_ISSUER ,
								SUM(CTV_AZ_OB_EMIT_INTER)/SUM(CTV_COMPL_NOCC_NEG)  AS CONCENTRATION,
								sum(copertura) as copertura
								--RATING as RATING
				 FROM  C6MARTPERIODICO.PATRIMONIO_EMITTENTI V  
				LEFT JOIN C6MARTPERIODICO.ANAG_PRODOTTI ANAG
					   ON V.COD_PRODOTTO = ANAG.COD_PRODOTTO
				 WHERE 
				 1=1
				   AND V.COD_FISCALE = @CODICEFISCALE
				   AND V.RETE = @RETE
				   AND (CTV_AZIONARIO >0 OR CTV_OBBLIGAZIONARIO > 0)
				GROUP BY
				 V.COD_FISCALE,
				 V.RETE,
				 INTERMEDIARIO,
				 EMITTENTE,
				 CREDITRISKCLASS,
				  RATING
				) Y
GROUP BY 
     COD_FISCALE,
     RETE,
     INTERMEDIARIO,
     EMITTENTE 
--RATING
--V devo rappresentare solo le 3 principali me le ordine e nel codice scorro per le prime 3 dopo aver filtrato per intermediario
--ORDER BY CONCENTRATION DESC
UNION 
--COMPLESSIVO
   SELECT
   COD_FISCALE,
			RETE,
        MAX(RATING_AAA) AS RATING_AAA,
		MAX(RATING_AAp) AS RATING_AAp,
		MAX(RATING_AA) AS RATING_AA,
		MAX(RATING_AAm) AS RATING_AAm,
		MAX(RATING_Ap) AS RATING_Ap,
		MAX(RATING_A) AS RATING_A,
		MAX(RATING_Am) AS RATING_Am,
		MAX(RATING_BBBp) AS RATING_BBBp,
		MAX(RATING_BBB) AS RATING_BBB,
		MAX(RATING_BBBm) AS RATING_BBBm,
		MAX(RATING_BBp) AS RATING_BBp,
		MAX(RATING_BB) AS RATING_BB,
		MAX(RATING_BBm) AS RATING_BBm,
		MAX(RATING_Bp) AS RATING_Bp,
		MAX(RATING_B) AS RATING_B,
		MAX(RATING_Bm) AS RATING_Bm,
		MAX(RATING_CCCp) AS RATING_CCCp,
		MAX(RATING_CCC) AS RATING_CCC,
		MAX(RATING_CCCm) AS RATING_CCCm,
		MAX(RATING_CC) AS RATING_CC,
		MAX(RATING_C) AS RATING_C,
		MAX(RATING_D) AS RATING_D,
		MAX(RATING_na) AS RATING_na,
     MAX(FLG_RISKCLASS1) AS FLG_RISKCLASS1,
     MAX(FLG_RISKCLASS2) AS FLG_RISKCLASS2,
     MAX(FLG_RISKCLASS3) AS FLG_RISKCLASS3,
     MAX(FLG_RISKCLASS4) AS FLG_RISKCLASS4,
	MAX(FLG_RISKCLASS5) AS FLG_RISKCLASS5,
	MAX(FLG_RISKCLASS6) AS FLG_RISKCLASS6,
	MAX(FLG_RISKCLASS7) AS FLG_RISKCLASS7,
	MAX(FLG_RISKCLASS8) AS FLG_RISKCLASS8,
	MAX(FLG_RISKCLASS9) AS FLG_RISKCLASS9,
	MAX(FLG_RISKCLASS10) AS FLG_RISKCLASS10,
	 isnull(INTERMEDIARIO,'BF') AS INTERMEDIARIO,
     EMITTENTE AS ISSUER,
     SUM(STOCKSCounterValue) AS  STOCKSCounterValue,
     SUM(bondsCounterValue) AS bondsCounterValue,
     SUM(isnull(bondsCounterValue,0)+ isnull(STOCKSCounterValue,0))/ max(@patr_compl)  *100  CONCENTRATION_ISSUER ,
     0 AS CONCENTRATION,
	sum(case when copertura > 0 then 0 else 1 end) as nc
--RATING as RATING
FROM
(
SELECT 
COD_FISCALE,
RETE,
'COMPLESSIVO' as INTERMEDIARIO,
CREDITRISKCLASS,
EMITTENTE,
RATING_AAA,
RATING_AAp,
RATING_AA,
RATING_AAm,
RATING_Ap,
RATING_A,
RATING_Am,
RATING_BBBp,
RATING_BBB,
RATING_BBBm,
RATING_BBp,
RATING_BB,
RATING_BBm,
RATING_Bp,
RATING_B,
RATING_Bm,
RATING_CCCp,
RATING_CCC,
RATING_CCCm,
RATING_CC,
RATING_C,
RATING_D,
RATING_na,
FLG_RISKCLASS1,
FLG_RISKCLASS2,
FLG_RISKCLASS3,
FLG_RISKCLASS4,
FLG_RISKCLASS5,
FLG_RISKCLASS6,
FLG_RISKCLASS7,
FLG_RISKCLASS8,
FLG_RISKCLASS9,
FLG_RISKCLASS10,
SUM(CTV_AZ_OB_EMIT) CTV_AZ_OB_EMIT,
SUM(STOCKSCounterValue) AS STOCKSCounterValue,
SUM(bondsCounterValue) AS bondsCounterValue,
0 AS CONCENTRATION_ISSUER,
SUM(CTV_AZ_OB_EMIT_INTER)/SUM(CTV_COMPLessivo) TOT ,
SUM(COPERTURA) AS COPERTURA 
--RATING as RATING
FROM
(
SELECT     V.COD_FISCALE,
                V.RETE,
                'COMPLESSIVO' as INTERMEDIARIO,
                CREDITRISKCLASS,
                EMITTENTE,
                 CASE WHEN RATING = 'AAA' THEN 1 ELSE 0 END AS RATING_AAA,
					CASE WHEN RATING = 'AA+' THEN 1 ELSE 0 END AS RATING_AAp,
					CASE WHEN RATING = 'AA' THEN 1 ELSE 0 END AS RATING_AA,
					CASE WHEN RATING = 'AA-' THEN 1 ELSE 0 END AS RATING_AAm,
					CASE WHEN RATING = 'A+' THEN 1 ELSE 0 END AS RATING_Ap,
					CASE WHEN RATING = 'A' THEN 1 ELSE 0 END AS RATING_A,
					CASE WHEN RATING = 'A-' THEN 1 ELSE 0 END AS RATING_Am,
					CASE WHEN RATING = 'BBB+' THEN 1 ELSE 0 END AS RATING_BBBp,
					CASE WHEN RATING = 'BBB' THEN 1 ELSE 0 END AS RATING_BBB,
					CASE WHEN RATING = 'BBB-' THEN 1 ELSE 0 END AS RATING_BBBm,
					CASE WHEN RATING = 'BB+' THEN 1 ELSE 0 END AS RATING_BBp,
					CASE WHEN RATING = 'BB' THEN 1 ELSE 0 END AS RATING_BB,
					CASE WHEN RATING = 'BB-' THEN 1 ELSE 0 END AS RATING_BBm,
					CASE WHEN RATING = 'B+' THEN 1 ELSE 0 END AS RATING_Bp,
					CASE WHEN RATING = 'B' THEN 1 ELSE 0 END AS RATING_B,
					CASE WHEN RATING = 'B-' THEN 1 ELSE 0 END AS RATING_Bm,
					CASE WHEN RATING = 'CCC+' THEN 1 ELSE 0 END AS RATING_CCCp,
					CASE WHEN RATING = 'CCC' THEN 1 ELSE 0 END AS RATING_CCC,
					CASE WHEN RATING = 'CCC-' THEN 1 ELSE 0 END AS RATING_CCCm,
					CASE WHEN RATING = 'CC' THEN 1 ELSE 0 END AS RATING_CC,
					CASE WHEN RATING = 'C' THEN 1 ELSE 0 END AS RATING_C,
					CASE WHEN RATING = 'D' THEN 1 ELSE 0 END AS RATING_D,
					CASE WHEN RATING is null THEN 1 ELSE 0 END AS RATING_na, 
				CASE WHEN CREDITRISKCLASS ='Rischio 1/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS1,
				CASE WHEN CREDITRISKCLASS ='Rischio 2/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS2,
				CASE WHEN CREDITRISKCLASS ='Rischio 3/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS3,
				CASE WHEN CREDITRISKCLASS ='Rischio 4/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS4,
				CASE WHEN CREDITRISKCLASS ='Rischio 5/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS5,
				CASE WHEN CREDITRISKCLASS ='Rischio 6/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS6,
				CASE WHEN CREDITRISKCLASS ='Rischio 7/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS7,
				CASE WHEN CREDITRISKCLASS ='Rischio 8/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS8,
				CASE WHEN CREDITRISKCLASS ='Rischio 9/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS9,
				CASE WHEN CREDITRISKCLASS ='Rischio 10/10' THEN 1 ELSE 0 END AS FLG_RISKCLASS10,
                SUM(CTV_AZ_OB_EMIT) as CTV_AZ_OB_EMIT,
                SUM(CTV_AZIONARIO) AS STOCKSCounterValue,
                sum(CTV_OBBLIGAZIONARIO) AS bondsCounterValue,
                0 AS  CONCENTRATION_ISSUER ,
                SUM(CTV_AZ_OB_EMIT_INTER) CTV_AZ_OB_EMIT_INTER,
                SUM(CTV_COMPLessivo)  AS CTV_COMPLessivo,
                MAX(copertura) as copertura
				--RATING as RATING
 FROM  C6MARTPERIODICO.PATRIMONIO_EMITTENTI V 
LEFT JOIN C6MARTPERIODICO.ANAG_PRODOTTI ANAG
	   ON V.COD_PRODOTTO = ANAG.COD_PRODOTTO 
 WHERE 
 1=1
   AND V.COD_FISCALE = @CODICEFISCALE
   AND V.RETE = @RETE
   AND (CTV_AZIONARIO >0 OR CTV_OBBLIGAZIONARIO > 0)
GROUP BY
 V.COD_FISCALE,
 V.RETE,
 EMITTENTE,
 INTERMEDIARIO,
 CREDITRISKCLASS,
RATING
) Y
GROUP BY 
COD_FISCALE,
RETE,
INTERMEDIARIO,
CREDITRISKCLASS,
EMITTENTE,
	RATING_AAA,
RATING_AAp,
RATING_AA,
RATING_AAm,
RATING_Ap,
RATING_A,
RATING_Am,
RATING_BBBp,
RATING_BBB,
RATING_BBBm,
RATING_BBp,
RATING_BB,
RATING_BBm,
RATING_Bp,
RATING_B,
RATING_Bm,
RATING_CCCp,
RATING_CCC,
RATING_CCCm,
RATING_CC,
RATING_C,
RATING_D,
RATING_na,
FLG_RISKCLASS1,
FLG_RISKCLASS2,
FLG_RISKCLASS3,
FLG_RISKCLASS4,
FLG_RISKCLASS5,
FLG_RISKCLASS6,
FLG_RISKCLASS7,
FLG_RISKCLASS8,
FLG_RISKCLASS9,
FLG_RISKCLASS10
--RATING
) L
GROUP BY 
     COD_FISCALE,
     RETE,
     INTERMEDIARIO,
     EMITTENTE 
--RATING
--V devo rappresentare solo le 3 principali me le ordine e nel codice scorro per le prime 3 dopo aver filtrato per intermediario
--ORDER BY CONCENTRATION DESC
) U
ORDER BY CONCENTRATION DESC , CONCENTRATION_ISSUER DESC 
END